SQL Overview
Hi friends,Find this article which would be useful for SQL Interview.
1 .Constraints
They are of 5 types:
Primary key constraint
Foreign key constraint
Unique key constraint
Check Constraint
Default constraints.
2.View
A virtual table that stores the query to execute and return data (doesnot actually store the data).
3.Extended Stored Procedure
Used to create external routines in prog languages like C.
Apprearance and execution same as SP.
4. Scalar function
Take arguments and returns 'scalar' values.
5. Triggers
It is a special type of Stored proc.
Executed when a certain event occurs in database server.
6. nVarchar, ntext variable length Unicode data types.
7. Types of foreign Key Constraint
On delete set null
On delete cascade
On delete set default.
8. Sparse column
optimizes storage of null values.
Use it when there are high number of NULL values in a column.
9. ALTER table
Can be used to ADD/MODIFY/DROP column.
Cannot use to drop a column with tables having only 1 column.
Used to add/drop a primary/unique/foreign/check constraints.
10.Drop Table
Cannot be recovered
Data is lost as structure is deleted.
11.Delete Table
Logged operation,
DML statement,
Doesnot release the memory occupied by records of the table,
Can have WHERE Clause.
12.Truncate Table
Not Logged
DDL Statement
Releases the memory
Doesnot have WHERE Clause.
13.Insert to add Multiple Rows
INSERT into tbl_Name VALUES (1,'A'), (2,'B'), (3,'C')
14.Identity
Only 1 column(integer), cannot be updated
Not on scale values (ex: numeric(4), decimal(8).where 4 n 8 is scale)
Can be used to increment value
Cannot be NULL.
15.@@Identity
Fetches last inserted value.
SELECT IDENTITYCOL or SELECT @@IDENTITY is used to refer the identity column.
16.Ident_Current('tbl_Name') Used to check the identity value of a table.
17.Logical Operators
ALL,ANY,AND,BETWEEN),EXISTS,IN,LIKE,NOT,OR,SOME,STRING(+),UNARY(+,-,~),BINARY(&,|,^).
18.Relational Operators =,<,>,<=,>=,!= or <>.
19.GROUP BY
Used with aggregate functions
related rows can be grouped together
cannot be used with WHERE Clause.
20.Query: Find avg price for each class
SELECT Class,AVG(price) FROM tbl_Name GROUP BY Class.
21.HAVING Clause
Filters data after grouping
Can be included with WHERE Clause and Group By Clause.
22.WHERE Clause
Filters data before grouping
Cannot be included with Group By Clause.
23.UNION
Set Operations,
Returns distinct rows,
Select statement must contain same no of columns and data types,
But Col names can be different.
24.JOINS
Cartesian Product(Cross Join),
Inner Join,
Equi Join,
Outer Join(Right and Left),
Self Join.
25.Cartesian(Cross) JOIN
Occurs when FROM not specified,
Each row from first table multiples with rows from second table.
Eg: table 1=3cols, table 2=3cols, result= 12cols.
26.Equi(=) Join
combines rows from 2 tables that has equivalent values for specified cols.
Eg: SELECT c1,c2 from tbl1 A JOIN tbl2 B ON A.cno=B.cno
27.Outer Join
Returns result like Inner Join
Except that it includes NULL values.
28.Self Join
Joins a table itself
Eg: SELECT a.eName, b.eName FROM tbl1 a JOIN tbl1 b ON A.mID=B.eID
29.INNER/OUTER query or SUB QUERY
Inner most Query is executed first
Outer query runs on inner query result.
30.EXISTS Return True/False values based on sub query results.
31.Date Functions
Getdate(), DateAdd(datepart,no,date),
DateDiff(datepart,no,date),
DateName(datepart,no,date),
DatePart(datepart,date),
Day,Month,Year,SysDateTime,IsDate().
32.Temporary table
Created as SQL server starts,
Dropped as SQL server ends,
Used tempDB,
Denotions: private(#) or global(##).
33.Indexes
They are DB objects,
Finds data quickly,
It uses standard B-Tree(balanced tree are managed n uses same amnt of resource).
34.Clustered Index
only 1 per table,
Rows are sorted,
physical order(order of pages) = logical order(order of rows),
Unique index,
large no of distinct values, cols used in JOINS, Group BY and Order BY clauses
Eg: CREATE UNIQUE CLUSTERED INDEX in_index_Name ON tbl_Name(col_Name)
35.NonClustered Index
Max 249 per table,
Rows are not sorted,
physical order is not same as logical order,
used on query that doesnot contain large no of distinct values,
Cols used in WHERE clause
Eg: CREATE NON CLUSTERED INDEX in_index_Name ON tbl_Name(col_Name)
36.TOP Clause
specify no of rows or percent,
Used in SELECT,UPDATE,DELETE and INSERT.
37.TOP Clause with TIES
Query generates additional row if it had same value as in TOP records
Eg: SELECT TOP(3) WITH TIES * FROM tbl_Name ORDER BY col_Name
Used if and only if ORDER BY is specified.
38.CTE
Common Table Expressions,
Recursive query
Queries divided in blocks which can be used later(like views)
Temporary named result set
Eg: Define CTE: WITH CTE1(eID,mID) AS (SELECT * FROM tbl1_Name
Call CTE: SELECT * FROM CTE1.
39.Ranking Functions
Rank(), DENSE_RANK(), ROW_NUMBER, NTILE().
40.Rank() function
Rank is returned for each row based on its values.
Eg: SELECT RANK OVER(ORDER BY Sal) As rank FROM tbl_Name
41.DENSE_RANK() function
Same as rank
Removes gaps in records.
42.NTile() function
Divides rows in partitions.
43.Partition BY clause
Comes with ranking function
Used along with ORDER BY clause.
44.PIVOT
Rows are transferred to columns,
Converts normalised data to de-normalised
Merges rows into single row set.
45.Un PIVOT
Reverse of PIVOT,
Reverting to Original table By Un-PIVOT is not possible as the data is merged by PIVOT.
46.Database Tuning
Done to increase performance
Needs understanding of the application.
Done by DBA, Developer, DB Designer, App Designer
47.DB Tuning Stages
Design Stage --> Application development stage --> Testing stage --> Deployment Stage.
48.DB Tuning Strategies Depends on
No of users,
type of problem,
nature of application,
time of day,
Load on system.
49.Application Tuning
Retrieve Needed Columns/Rows only,
Minimise no of query compilations.
50.@@Error
Use to catch Errors
Always gets reset.
1 .Constraints
They are of 5 types:
Primary key constraint
Foreign key constraint
Unique key constraint
Check Constraint
Default constraints.
2.View
A virtual table that stores the query to execute and return data (doesnot actually store the data).
3.Extended Stored Procedure
Used to create external routines in prog languages like C.
Apprearance and execution same as SP.
4. Scalar function
Take arguments and returns 'scalar' values.
5. Triggers
It is a special type of Stored proc.
Executed when a certain event occurs in database server.
6. nVarchar, ntext variable length Unicode data types.
7. Types of foreign Key Constraint
On delete set null
On delete cascade
On delete set default.
8. Sparse column
optimizes storage of null values.
Use it when there are high number of NULL values in a column.
9. ALTER table
Can be used to ADD/MODIFY/DROP column.
Cannot use to drop a column with tables having only 1 column.
Used to add/drop a primary/unique/foreign/check constraints.
10.Drop Table
Cannot be recovered
Data is lost as structure is deleted.
11.Delete Table
Logged operation,
DML statement,
Doesnot release the memory occupied by records of the table,
Can have WHERE Clause.
12.Truncate Table
Not Logged
DDL Statement
Releases the memory
Doesnot have WHERE Clause.
13.Insert to add Multiple Rows
INSERT into tbl_Name VALUES (1,'A'), (2,'B'), (3,'C')
14.Identity
Only 1 column(integer), cannot be updated
Not on scale values (ex: numeric(4), decimal(8).where 4 n 8 is scale)
Can be used to increment value
Cannot be NULL.
15.@@Identity
Fetches last inserted value.
SELECT IDENTITYCOL or SELECT @@IDENTITY is used to refer the identity column.
16.Ident_Current('tbl_Name') Used to check the identity value of a table.
17.Logical Operators
ALL,ANY,AND,BETWEEN),EXISTS,IN,LIKE,NOT,OR,SOME,STRING(+),UNARY(+,-,~),BINARY(&,|,^).
18.Relational Operators =,<,>,<=,>=,!= or <>.
19.GROUP BY
Used with aggregate functions
related rows can be grouped together
cannot be used with WHERE Clause.
20.Query: Find avg price for each class
SELECT Class,AVG(price) FROM tbl_Name GROUP BY Class.
21.HAVING Clause
Filters data after grouping
Can be included with WHERE Clause and Group By Clause.
22.WHERE Clause
Filters data before grouping
Cannot be included with Group By Clause.
23.UNION
Set Operations,
Returns distinct rows,
Select statement must contain same no of columns and data types,
But Col names can be different.
24.JOINS
Cartesian Product(Cross Join),
Inner Join,
Equi Join,
Outer Join(Right and Left),
Self Join.
25.Cartesian(Cross) JOIN
Occurs when FROM not specified,
Each row from first table multiples with rows from second table.
Eg: table 1=3cols, table 2=3cols, result= 12cols.
26.Equi(=) Join
combines rows from 2 tables that has equivalent values for specified cols.
Eg: SELECT c1,c2 from tbl1 A JOIN tbl2 B ON A.cno=B.cno
27.Outer Join
Returns result like Inner Join
Except that it includes NULL values.
28.Self Join
Joins a table itself
Eg: SELECT a.eName, b.eName FROM tbl1 a JOIN tbl1 b ON A.mID=B.eID
29.INNER/OUTER query or SUB QUERY
Inner most Query is executed first
Outer query runs on inner query result.
30.EXISTS Return True/False values based on sub query results.
31.Date Functions
Getdate(), DateAdd(datepart,no,date),
DateDiff(datepart,no,date),
DateName(datepart,no,date),
DatePart(datepart,date),
Day,Month,Year,SysDateTime,IsDate().
32.Temporary table
Created as SQL server starts,
Dropped as SQL server ends,
Used tempDB,
Denotions: private(#) or global(##).
33.Indexes
They are DB objects,
Finds data quickly,
It uses standard B-Tree(balanced tree are managed n uses same amnt of resource).
34.Clustered Index
only 1 per table,
Rows are sorted,
physical order(order of pages) = logical order(order of rows),
Unique index,
large no of distinct values, cols used in JOINS, Group BY and Order BY clauses
Eg: CREATE UNIQUE CLUSTERED INDEX in_index_Name ON tbl_Name(col_Name)
35.NonClustered Index
Max 249 per table,
Rows are not sorted,
physical order is not same as logical order,
used on query that doesnot contain large no of distinct values,
Cols used in WHERE clause
Eg: CREATE NON CLUSTERED INDEX in_index_Name ON tbl_Name(col_Name)
36.TOP Clause
specify no of rows or percent,
Used in SELECT,UPDATE,DELETE and INSERT.
37.TOP Clause with TIES
Query generates additional row if it had same value as in TOP records
Eg: SELECT TOP(3) WITH TIES * FROM tbl_Name ORDER BY col_Name
Used if and only if ORDER BY is specified.
38.CTE
Common Table Expressions,
Recursive query
Queries divided in blocks which can be used later(like views)
Temporary named result set
Eg: Define CTE: WITH CTE1(eID,mID) AS (SELECT * FROM tbl1_Name
Call CTE: SELECT * FROM CTE1.
39.Ranking Functions
Rank(), DENSE_RANK(), ROW_NUMBER, NTILE().
40.Rank() function
Rank is returned for each row based on its values.
Eg: SELECT RANK OVER(ORDER BY Sal) As rank FROM tbl_Name
41.DENSE_RANK() function
Same as rank
Removes gaps in records.
42.NTile() function
Divides rows in partitions.
43.Partition BY clause
Comes with ranking function
Used along with ORDER BY clause.
44.PIVOT
Rows are transferred to columns,
Converts normalised data to de-normalised
Merges rows into single row set.
45.Un PIVOT
Reverse of PIVOT,
Reverting to Original table By Un-PIVOT is not possible as the data is merged by PIVOT.
46.Database Tuning
Done to increase performance
Needs understanding of the application.
Done by DBA, Developer, DB Designer, App Designer
47.DB Tuning Stages
Design Stage --> Application development stage --> Testing stage --> Deployment Stage.
48.DB Tuning Strategies Depends on
No of users,
type of problem,
nature of application,
time of day,
Load on system.
49.Application Tuning
Retrieve Needed Columns/Rows only,
Minimise no of query compilations.
50.@@Error
Use to catch Errors
Always gets reset.
No comments: