Can you define Equijoin?

The condition for matching the rows based on the equality of two values, one from each of the tables being joined. So that's what makes it an equijoin: the ON condition is equality. This includes inner joins and all three types of outer joins.

Please ask your Questions in comment box..

Finding Last Seven Days Data

How to find last seven days data and if any of days data is null then gives null.

This explanation about Oracle SQL Query:

Generally most of the people confuse here is how to handle NULL values.

so, here I am going to show you how we can do this.

I am taking Sales Date  and replacing NVL with SYSDATE means it treats like this is the Max or today date greater than or equal to SYSDATE-7 means seven days back date (ex 2017-11-4 - 7 days: 2017-10-29)

Query:

SELECT * FROM Tbl_Daily_Sales WHERE NVL(Sale_Date,SYSDATE) >= (SYSDATE-7);

Please find below Output:

Please give your valuable  comments & questions

How to Delete Duplicate Rows In Below Scenario

I have a sample data where the ID is unique, but there are multiple duplicate rows(excluding ID). The other columns are Name, Age, DOB, Transaction Date, Payment, Amount. I have purposely duplicated few rows, and kept ID unique.

--Deleting Duplicate 

;WITH Cte AS (SELECT ID,Name, Age, DOB, Transaction_Date, Payment, Amount, ROW_NUMBER() OVER(PARTITION BY Name, Age, DOB, Transaction_Date, Payment, Amount ORDER BY ID) Seq FROM #Temp) DELETE FROM Cte WHERE Seq<>1

Please Give Your Comments and Ask Some Questions:

Difference between DTS and SSIS

DTS:

1.  DTS stands for Data Transformation Services

2.  DTS is a set of objects using an ETS tool to extract, transform, and load information to or        from a database

3.  DTS was originally part of the Microsoft SQL Server 2000

4.  Uses Activex Script

5.  No Deployment wizard is available

6.  Limited Set of Transformation available

7.  Does not support BI Functionality

8.  Single Task at a time

9.  It is Unmanaged script

10. DTS can develop through Enterprise manager

11. We can deploy only at local server

12. Designer contains Single Pane

13. No Event Hander

14. No Solution Explorer

15. Connection and other values are static, not controlled at runtime.

SSIS:

1. SSIS stands for Sql Server Integration Services

2. SSIS is an ETL tool provided by Microsoft to extra data from different sources.

3. SSIS is a component of the Microsoft SQL Server 2005

4. Uses Scripting Language

5. Deployment wizard is available

6. Huge of Transformations available

7. Completely supports end to end process of BI

8. Multi Tasks run parallely

9. It is managed by CLR

10. SSIS can develop through Business Intelligence Development Studio (BIDS, nothing but         new version of VS IDE)

11. It can be deployed using multiple server using BIDS

12. SSIS designer contains 4 design panes:

 a) Control Flow

 b) Data Flow

 c) Event Handlers &

 d) Package Explorer.

13. Event Handler Available

14. Solution Explorer is available, with packages, connections and Data Source Views (DSV)

15. It can be controlled dynamically using configuration

How to add new Column to existing table in SQL Server

     I know it's simple statement and most of the people known this query, but some times we forgot the syntax so that I got a thought why should not post this? ... :) :) 

Syntax: 

ALTER TABLE <Table Name> ADD <Column Name> <Data Type>

Example

ALTER TABLE Emp2 ADD E_Addres INT

 Please See below Screenshot 

User Defined Table Type

SQL Server  providing the User Defined Table Type, as of my knowledge this is useful when we need to pass parameter to the Stored Proc or Functions as a table values.

Here we can create different data types of columns like INT, VARCHAR etc.

we can create User Type Table  in two ways, here I am giving only one type simply

/* Create a user-defined table type */
CREATE TYPE LocationTableType AS TABLE
    ( LocationName VARCHAR(50)
    , CostRate INT )
GO

It would create User Defined Table Type

Stored Procedures

Stored Procedure

     Stored Procedures, these are most important or common things in every Data Base these will play main role in lot of cases, now I am going to tell you some points

     Stored Procedures, as of known everyone it's a collection of SQL Statements, Yes its exactly right.

     Stored Procedure takes the multiple parameters and will returns the output as result set to the calling program.

Advantages of Stored Procedure

Reduce the Network Traffic, Yes it will reduce the network traffic you may get a doubt on this, how the network traffic would reduce using the Stored Procedure?, Yes it will reduce, for example if we are calling 10 statements and calculating these 10 statements in application level then passing to the server it takes much time so instead of calling 10 statements we can call just single statement that simply pass to the server and will fire at server level, these 10 statements will execute as batch, I hope you got the logic here.

Security, using stored procedures user can't find the database level objects, because in application level we are calling only stored procedure only, so if any third person (malicious users) in between application and server they cannot see the objects of Data Base

These everyone knows I hope

Re-usability of Code, Yes we can use multiple times which we created as Stored Procedure or collection of SQL statements, no need to write these statement multiple times, if any changes are required we need to Alter the Stored Procedure.

 which may you don't know if you know more information or if anything wrong in below just mail me.

Clustered Indexes

CLUSTERED INDEX

     Yes, Here I am going to explain about Indexes in SQL Server  as of my knowledge, I hope every one heard about Indexes but don't have a complete idea about it.

Ok, Let's start

     Basically Indexes are using for Query performance in SQL Server (actually not in SQL Server in all other Data Bases also using for performance only) , these are on-disk structure, and I read some where we can call as row store Index.

     Clustered Index will creates an Index Key and stored the data rows of the tables in order to clustered Index Key in sorting order (ascending or descending) , flow of the clustered index follows B-Tree structure.

    We can create using SQL Server Management Studio or T-SQL, a clustered Index can be rebuild or reorganize on demand to manage the Index Key order.

     When we create a table with the Primary Key that table will create along with Index Key, if table having the Clustered Index then those kind of tables called Clustered Tables, if that table doesn't have any Clustered Index then that we call as Heap Tables

    There can be only one Clustered Index per table, Table doesn't allow more than one Clustered Index

Syntax:

CREATE CLUSTERED INDEX <Index Name> ON <Table Name> (<Column Name>);   

Example:

CREATE CLUSTERED INDEX INDX_Emp_Tbl_Emp_Num ON dbo.Emp (Emp_Num);   

In Generally If table have more than 8 MB then only it goes to Index to get the data else it directly fetch the data from the table it self.

Soon I will update more Information on this

Please point if anything wrong, you can shoot a mail to : tthirmal@gmail.com

What is the difference between OLTP and OLAP?

I found this information some where, it's very easy to understand, and am sharing the original link below of this.

- OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). 

- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). 


The following table summarizes the major differences between OLTP and OLAP system design.

OLTP System - Online Transaction Processing (Operational System)
OLAP System - Online Analytical Processing (Data Warehouse)

Source of data
OLTP: Operational data; OLTPs are the original source of the data.
OLAP: Consolidation data; OLAP data comes from the various OLTP Databases

Purpose of data
OLTP: To control and run fundamental business tasks
OLAP: To help with planning, problem solving, and decision support

What the data
OLTP: Reveals a snapshot of ongoing business processes
OLAP: Multi-dimensional views of various kinds of business activities

Inserts and Updates
OLTP: Short and fast inserts and updates initiated by end users
OLAP: Periodic long-running batch jobs refresh the data

Queries
OLTP: Relatively standardized and simple queries Returning relatively few records
OLAP: Often complex queries involving aggregations

Processing Speed
OLTP: Typically very fast
OLAP: Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes

Space Requirements
OLTP: Can be relatively small if historical data is archived
OLAP: Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP

DatabaseDesign
OLTP: Highly normalized with many tables
OLAP: Typically de-normalized with fewer tables; use of star and/or snowflake schemas

Backup and Recovery
OLTP: Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability
OLAP: Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery methodsource: 

original source: http://datawarehouse4u.info/OLTP-vs-OLAP.html

Stored Procedures - Auto Executing

You can designate stored procedures to execute every time the SQL Server is started. These types of procedures cannot accept any input parameters and have to be owned by a member of SYSADMIN fixed server role. To designate stored procedures for automatic execution use the sp_procoption system stored procedure.

The only option allowed by this procedure is 'startup'. The procedure to be started automatically MUST reside in the Master database. The following example makes the procedure execute automatically every time the server starts up:

sp_procoption my_procedure, 'startup', 'on'

This option could be useful if you have specific processing requirements or tasks that need to be perform at server startup - for instance you might wish to backup all of your user databases every time SQL Server is started.