SQL Last Minute Revision
They are of 5 types:
Primary key constraint
Foreign key constraint
Unique key constraint
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.
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.
Cannot be recovered
Data is lost as structure is deleted.
Doesnot release the memory occupied by records of the table,
Can have WHERE Clause.
Releases the memory
Doesnot have WHERE Clause.
13.Insert to add Multiple Rows
INSERT into tbl_Name VALUES (1,'A'), (2,'B'), (3,'C')
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.
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.
18.Relational Operators =,<,>,<=,>=,!= or <>.
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.
Filters data after grouping
Can be included with WHERE Clause and Group By Clause.
Filters data before grouping
Cannot be included with Group By Clause.
Returns distinct rows,
Select statement must contain same no of columns and data types,
But Col names can be different.
Cartesian Product(Cross Join),
Outer Join(Right and Left),
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.
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
Returns result like Inner Join
Except that it includes NULL values.
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.
Created as SQL server starts,
Dropped as SQL server ends,
Denotions: private(#) or global(##).
They are DB objects,
Finds data quickly,
It uses standard B-Tree(balanced tree are managed n uses same amnt of resource).
only 1 per table,
Rows are sorted,
physical order(order of pages) = logical order(order of rows),
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)
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)
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.
Common Table Expressions,
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.
Rank(), DENSE_RANK(), ROW_NUMBER, NTILE().
Rank is returned for each row based on its values.
Eg: SELECT RANK OVER(ORDER BY Sal) As rank FROM tbl_Name
Same as rank
Removes gaps in records.
Divides rows in partitions.
43.Partition BY clause
Comes with ranking function
Used along with ORDER BY clause.
Rows are transferred to columns,
Converts normalised data to de-normalised
Merges rows into single row set.
Reverse of PIVOT,
Reverting to Original table By Un-PIVOT is not possible as the data is merged by PIVOT.
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.
Retrieve Needed Columns/Rows only,
Minimise no of query compilations.
Use to catch Errors
Always gets reset.
Very useful points for me.
Excellent work. It will really be helpful for all who are just going to the interview and want the last minute preparation to remember the point.
Thanks for sharing.
Dude Naveen Awesome resource thanks a lot for the preparation and sharing with us .
Thanks to helping hand
This is so helpful for quick revision