Quickly Revise SQL used in many popular RDBMS Databases

   RAM RAM Friends,

I make following notes for my personal revision of SQL Queries & when I got the chance to share these notes, I am posting them as it is without making major changes due to lack of time.

Most of the content has been learnt from W3School website's SQL tutorial

I hope it will help those who wish to make quick revision of sql.

Please Note that I have build these notes to quickly revise SQL Query Syntax, commands etc Only.

Since I used to learn syntax by learning/memorizing examples, so I have not mentioned any Syntax rather I use examples with only most used options for syntax.
MS SQL SERVER NOTES

1 SQL

• stands for Structured Query Language
• is an ANSI (American National Standard Institute) standard
• standard language for accessing & manipulating db
• used in various db(RDBMS) like Access, (MS) SQL Server,Oracle, (IBM) DB2, Sybase,MYSQL, etc
• it is not case sensitive
• What can it do? it can create database,sp,tables,views,functions,triggers
• it can retrieve,insert,update,delete records from/in a db
• it can execute queries against a db
• it can set permissions on tables,sp,view
• Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
• Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
• Some DB systems require a semicolon at the end of each SQL stmnt, usually it is required by them to allow more than one SQL stmnt to be executed in the same call to the server.
• it is divided into 2 parts DML & DDL

2 Website using SQL

• We need following to build website using sql to access data
• an RDBMS db program like MS Access,SQL Server, MYSQL etc
• a server-side scripting language like PHP, ASP
• SQL
• HTML/CSS

3 RDBMS

• Relational DataBase Management System
• it is base for SQL & all modern db systems
• it has data(cell) column,row(record),table,db,etc

4 DML

• stands for Data Manipulation Language
• it is formed by Query & Update Commands
• SELECT --->extracts data from a db
• UPDATE--->updates data in a db
• DELETE---> deletes data from a db
• INSERT INTO---->inserts new data into a db

5 DDL

• stands for Data Definition Language
• it permits db tables to be created,deleted
• it also define indexes(keys),specify links b/w tables,& impose constraints b/w tables

DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL  

e.g.

CREATE DATABASE

• USE db1
• CREATE DATABASE --->creates a new db
• CREATE DATABASE db1

ALTER DATABASE

• ALTER DATABASE-------->modifies a db

DROP DATABASE

• used to delete the db
• DROP DATABASE db1
• drop multiple dbs
• DROP DATABASE db1,db2,db3,db4

CREATE TABLE

• CREATE TABLE------------>creates a new table
• 
  
• CREATE TABLE Persons
• (
• P_Id int,
• LastName varchar(255),
• FirstName varchar(255)
• )

sql Constraints

• used to limit the type of data that can go into a table
• it can be specified when a table is created/altered
• NOT NULL constraint
• it enforces a column to NOT accept NULL values
• by default , a table column can hold NULL values
• it enforces a field to always contain a value.so u can't insert a new record or update a record without adding a value to this field
• CREATE TABLE Persons
• (
• P_Id int NOT NULL,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255)
• )

• UNIQUE constraint
• it uniquely identifies each record in a db table
• both UNIQUE & PRIMARY KEY constraints provide a guarantee for uniqueness for a col/set of cols
• PRIMARY KEY constraints automatically has a UNIQUE constraint defined on it
• we may have many UNIQUE constraints per table but only one PRIMARY KEY constraint per table

different Constraints on diff cols 

•  In SQL Server/oracle/access
• CREATE TABLE Persons
•  (
• P_Id int NOT NULL UNIQUE,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255)
• )
• In MYSQL
• CREATE TABLE Persons
• (
• P_Id int NOT NULL,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• City varchar(255),
• UNIQUE (P_Id)
• )

• one time on diff cols (multiple cols)(mysql/sqlserver/oracle/access)
• CREATE TABLE Persons
•  (
•  P_Id int NOT NULL,
•  LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• City varchar(255),
• CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
• )

• Constraints on ALTER TABLE stmnt
• ALTER TABLE Persons ADD UNIQUE (P_Id)
• for multiple cols
• ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

• DROP CONSTRAINT
• to DROP Unique
• In SQLServer/Oracle/MS Access
• ALTER TABLE Persons DROP CONSTRAINT uc_PersonID
• In MySQL
• ALTER TABLE Persons DROP INDEX uc_PersonID

• PRIMARY KEY constraint
• it uniquely identifies each record in a db table
• Primary Key col must contain unique values, can't contain NULL values, ONLY one Primary Key of each table
• each table should have a PK & each table can have only one PK
• In sqlserver/oracle/access
• CREATE TABLE Persons
•  (
• P_Id int NOT NULL PRIMARY KEY,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255)
• )
• for MySQL
• CREATE TABLE Persons
• (
• P_Id int NOT NULL,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• PRIMARY KEY (P_Id)
• )
• use PK(Primary Key) on multiple cols
• CREATE TABLE Persons
• (
• P_Id int NOT NULL,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
• )

• PK on ALTER TABLE
• ALTER TABLE Persons ADD PRIMARY KEY (P_Id)
• on multiple cols
•  ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
• if you use ALTER TABLE stmnt to add PK, PK col(s) must already have been declared to not contain NULL values

• DROP CONSTRAINT
• ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
• In MySQL 
•  ALTER TABLE Persons DROP PRIMARY KEY

• FOREIGN KEY constraint
• FOREIGN KEY in one table points to a PK in another table
• This constraint is used to prevent actions that would destroy link b/w tables.
• it also prevents that invalid data is inserted into the foreign key column, since it has to be one of the values contained in the table it points to.
• In SQL Server,Oracle,MS Access
•  CREATE TABLE Orders
•  (
•  O_Id int NOT NULL PRIMARY KEY,
• OrderNo int NOT NULL,
• P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
• )
• In MYSQL
•  CREATE TABLE Orders
• (
• O_Id int NOT NULL,
• OrderNo int NOT NULL,
• P_Id int,
• PRIMARY KEY (O_Id),
• FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
• )
• allow naming of FOREIGN KEY constraint & apply on multiple 
•  CREATE TABLE Orders 
• (
• O_Id int NOT NULL,
• columns OrderNo int NOT NULL,
• P_Id int,
• PRIMARY KEY (O_Id),
• CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
• REFERENCES Persons(P_Id)
• )

• using ALTER stmnt
• ALTER TABLE Orders
• ADD FOREIGN KEY (P_Id)
• REFERENCES Persons (P_Id)

• allow naming of FOREIGN KEY constraint  & apply on multiple columns
•  ALTER TABLE Orders
• ADD CONSTRAINT fk_PerOrders
• FOREIGN KEY (P_Id)
• REFERENCES Persons (P_Id)
• Drop FK constraint
• ALTER TABLE Orders
• DROP CONSTRAINT fk_PerOrders
• In MySQL
• ALTER TABLE Orders
• DROP FOREIGN KEY fk_PerOrders

• CHECK constraint
• CHECK constraint is used to limit the value range that can be placed in a column/table.
• for col 
• if define on a single col it allow only certain values for this col.
• for table
• if define on a table, it can limit the values in certain cols based on values in other cols in the row.
• E.g.
• Below constraint specifies that col "P_Id" must only include intergers >0
• For SQL Server, Oracle, MS Access
•  CREATE TABLE Persons
•  (
•  P_Id int NOT NULL CHECK (P_Id>0),
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• )
• In MySQL
• CREATE TABLE Persons
• (
• P_Id int NOT NULL,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• CHECK (P_Id>0)
• )

• allow naming of CHECK constraint & apply on multiple columns 
• CREATE TABLE Persons
• (
• P_Id int NOT NULL,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• Address varchar(255),
• City varchar(255),
• CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes' )
• )
• using ALTER stmnt
• ALTER TABLE Persons
• ADD CHECK (P_Id>0)
• for naming & multiple cols
• ALTER TABLE Persons
•  ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
• Drop CHECK constraint 
• ALTER TABLE Persons
• DROP CONSTRAINT chk_Person
• DEFAULT constraint
• used to insert a default value into a col & default value will be added to all new records, if no other value is specified
• E.g. 
• CREATE TABLE Persons
• (
• P_Id int NOT NULL,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• Address varchar(255),
• City varchar(255) DEFAULT 'Delhi'
• )
• it can also be used to insert system values, by using functions like GETDATE()
• CREATE TABLE Orders
• (
• O_Id int NOT NULL,
• OrderNo int NOT NULL,
• P_Id int,
• OrderDate date DEFAULT GETDATE()
• )
• using ALTER stmnt
• ALTER TABLE Persons
• ALTER COLUMN City SET DEFAULT 'Delhi'
• in MySQL
• ALTER TABLE Persons
• ALTER City SET DEFAULT 'Delhi'
• drop DEFAULT constraint
• ALTER TABLE Persons
•  ALTER COLUMN City DROP DEFAULT
• in MySQL
• ALTER TABLE Persons
• ALTER City DROP DEFAULT
• AUTO INCREMENT field 
• Auto-increment allows a unique number to be generated when a new record is inserted into a table.
• very often we would like the value of the PK field to be created automatically every time a new record is inserted.
• In sql server
• ms sql server uses IDENTITY keyword to perform an auto-increment feature.
• by default, starting value for IDENTITY is 1 & it will increment by 1 for each new record
• to sepecify starting value=10 & increment by 5, change the IDENTITY to IDENTITY(10,5)
• To insert a new record , we don't need to specify a value for the AUTO-INCREMENT field, a unique value will be added automatically.
• E.g.
• CREATE TABLE Persons
• (
• P_Id int PRIMARY KEY IDENTITY,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• Address varchar(255),
• City varchar(255) DEFAULT 'Delhi'
• )
• E.g. with different start no
• CREATE TABLE Persons
• (
• P_Id int PRIMARY KEY IDENTITY(10,5),
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• Address varchar(255),
• City varchar(255) DEFAULT 'Delhi'
• ) 
• In ms access
• CREATE TABLE Persons
• (
• P_Id int PRIMARY KEY AUTOINCREMENT,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• Address varchar(255),
• City varchar(255)
• )
• NOTE: use AUTOINCREMENT(10,5) for same purpose as in ms sql server
• In MySQL
• CREATE TABLE Persons
• (
• P_Id int NOT NULL AUTO_INCREMENT,
• LastName varchar(255) NOT NULL,
• FirstName varchar(255),
• Address varchar(255),
• City varchar(255),
• PRIMARY KEY (P_Id)
• )
• In MySQL to start with other value than 1
• ALTER TABLE Persons AUTO_INCREMENT=100
• In Oracle
• in oracle, to use autoincrement field, we will have to create an auto-increment field with the sequence object  (this object generates a number sequence)
• E.g.
• CREATE SEQUENCE seq_person
• MINVALUE 1
• START WITH 1
• INCREMENT BY 1
• CACHE 10
• above code creates a sequence object that starts with 1 & will increment by 1. It will also cache up to 10 values for performance.
•  The cache option specifies how many sequence values will be stored in memory for faster access.
• E.g.
• INSERT INTO Persons (P_Id,FirstName,LastName)
• VALUES(seq_person.nextval,'Ram', 'Mohan')
• To insert a new record, we will have to use the nextval function of seq_person object to retrieve the next value

ALTER TABLE

• used to add,delete or modify cols in an existing table.
• add col
• ALTER TABLE Persons
• ADD DateOfBirth date
• modify(change data type) 
• ALTER TABLE Persons
• ALTER COLUMN DateOfBirth year
• delete a col 
• NOTE: some db not allow deleting the column so check your db
• E.g.
• ALTER TABLE Persons
• DROP COLUMN DateOfBirth

DROP TABLE

• DROP TABLE----------------> deletes a table
• E.g
• DROP TABLE Persons

CREATE INDEX

•  CREATE INDEX------------->creates an index(search key)
• used to create indexes in tables either on single col or on multiple cols to find data more quickly & efficiently.
• it allow db application to find data fast; without reading the whole table
• user/developer can't see the indexes, they are just used to speed up searches/queries.
• NOTE: updating a table with indexes takes more time that updating a table without (since indexes also need an update). So you should only create indexes on cols (& tables) that will be frequently searched against.
• NOTE:systax for creating indexes varies amongst diff db. So check its syntax in your db
• syntax (allow duplicate)
• In below syntax duplicate values are allowed.
• CREATE INDEX a
• ON table1 (col1)
• syntax (duplicate not allowed)
• e.g.
•  CREATE UNIQUE INDEX a
•  ON table1 (col1)
• e,g,
• CREATE INDEX PIndex
• ON Persons (LastName)
• on multiple cols
• CREATE INDEX PIndex
• ON Persons (LastName, FirstName)

DROP INDEX

•  DROP INDEX----------------> deletes an index 
• In MS SQL SERVER 
• DROP INDEX Persons.Pindex
• In MS Access
•  DROP INDEX Pindex ON Persons
• In DB2/Oracle
•  DROP INDEX Pindex
• In MySQL
• ALTER TABLE Persons DROP INDEX Pindex

CREATE VIEW stmnt

• a view is a virtual table based on the result-set of an SQL stmnt either from one or more tables
• We can add SQL functions,WHERE,JOIN etc stmnt to a view & present the data as if the data were coming from one single table.
• a view always shows up-to-date data! The db engine recreates the data, using the view's SQL stmnt, every time a user queries a view.
• If you have NORTHWIND db , u can see that it has several views installed by default. E.g.
• e.g.1
• CREATE VIEW [Products Above Average Price] AS
• SELECT ProductName,UnitPrice
• FROM Products
• WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
• we can query the above view as follows:
• SELECT * FROM [Products Above Average Price]
• e.g.2  
• below view calculates the total sale for each category in 1997.NOTE: it selects its data from another view
• CREATE VIEW [Category Sales For 1997] AS
• SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
• FROM [Product Sales for 1997]
• GROUP BY CategoryName
• accessing the view
• SELECT * FROM [Category Sales For 1997]
• WHERE CategoryName='Beverages'
• modify a view 
• we can update a view by using "CREATE OR REPLACE VIEW" syntax
• below code add a new col "Category" in view "Current Product List"
• CREATE OR REPLACE VIEW [Current Product List] AS
• SELECT ProductID,ProductName,Category
• FROM Products
• WHERE Discontinued='No'
• DROP VIEW stmnt
• delete a view
• DROP VIEW [Current Product List]

DML DML DML DML DML DML DML DML DML DML DML DML DML DML DML 

6 SELECT stmnt

• select stmnt is used to select data from a db & result is stored in a result table called "result-set"
• * the asterisk (*)
• the asterisk (*) is used to select all columns
• SELECT * FROM customers
• selected column
•  it return only those column which are mentioned in SQL stmnt
• SELECT firstname,city FROM customers
• Distinct
• this keyword is used to return only distinct(different) values
• SELECT DISTINCT city FROM customers
• WHERE clause
• used to filter records ie to extract only those records that fulfill a specified criterion
• SQL uses single quotes arount text,date & time values(most db systems will also accept double quotes)
• Numeric values should not be enclosed in quotes
• Operators allowed in it
• (= , <> , > , < , >= , <= )
• note:- in some versions of SQL <> operator may be written as != )
• SELECT Company,Country FROM Customers WHERE country <> 'USA'
• SELECT Company,Country FROM Customers WHERE sal >= 250250
• BETWEEN 
• -> between an inclusive range ( same as >= and <=) 
• used to select a range of data b/w two values (both inclusive).values may be no,text,dates
• SELECT * FROM Persons WHERE LastName BETWEEN 'Arun' AND 'Sachin'
• in different db, either 1st value or 2nd value or both or neither is inclusive , so check your db
• NOT BETWEEN
• SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Arun' AND 'Sachin'
• IN
• -> If you know the exact value you want to return for at least one of the columns
• allow to specify multiple values in a WHERE clause
• SELECT * FROM Persons WHERE LastName IN ( 'Kakkar' , 'Yadav')
• NOT IN
• SELECT * FROM Persons WHERE LastName NOT IN ( 'Kakkar' , 'Yadav')
• LIKE
• ->Search for a pattern, can use wild cards
• used to search a specified pattern in a column
• SELECT * FROM customers WHERE companyname LIKE 'a%'
• SELECT * FROM customers WHERE companyname LIKE '%tav%'
• NOT LIKE
• SELECT * FROM customers WHERE companyname NOT LIKE '%tav%'
• SQL Wildcards
• used to search data in db
• %
• used to search data in db
• _
• substitute for exactly one character
• SELECT * FROM customers WHERE companyname LIKE '_av'
• SELECT * FROM customers WHERE companyname LIKE 'A_u_Kakkar'
• [charlist]
• any single character in charlist
• SELECT * FROM customers WHERE companyname LIKE '[avt]%'
• above will display all records where companyname begins with either a or v or t
• [^charlist] or [!charlist]
• any single character not in charlist
• SELECT * FROM customers WHERE companyname LIKE '[!avt]%'
• above will display all records where companyname not begins with either a or v or t
• sql AND operator
• used to filter records based on more than one conditions
• display a record only if both the first condition & the 2nd condition is TRUE
• SELECT * FROM persons WHERE FirstName='Tove' AND LastName='Svendson'
• sql OR operator
• used to filter records based on more than one conditions
• display a record if either the first condition or the 2nd condition is TRUE
• SELECT * FROM persons WHERE FirstName='Tove' OR LastName='Svendson'
• combining AND & OR
• can be combined by using parenthesis to form complex expressions
• SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola') 
• ORDER BY keyword
• sort result-set by a specified column, by default it sort in ascending order (ASC)
• to sort in descending order use DESC keyword
• SELECT * FROM Persons ORDER BY LastName
• SELECT * FROM Persons ORDER BY LastName DESC
• IS NULL operator
• NOTE: 
• Null
• is an absence of a value.
• NULL isn't allocated any memory
• the string with NUll value is just a pointer which is pointing to nowhere in memory.
• Null has no bounds, it can be used for string, integer, date, etc. fields in a database.
• Null is the database's determination of an absense of a value logically, so to speak
• By default, a table column can hold NULL values.
• it is not possible to compare NULL & 0 . They are not equivalent.
• SELECT * FROM Persons WHERE Address IS NULL
•  An empty string
• is a value, but is just empty.
• Empty IS allocated to a memory location, although the value stored in the memory is "".
•  Empty string is just regarding a string; it's a string like 'asdfasdf' is, but is just has no length.
•  If you have no value for a field, use null, not an empty string.
• IS NOT NULL operator
• SELECT * FROM Persons WHERE Address IS NOT NULL

INSERT INTO stmnt

• used to insert a new row/record in a table
• one way (Method)
• doesn't specify column names where the data will be inserted, only their values
• INSERT INTO Persons VALUES (4,'Nilsen' , 'Johan', 'Bakken2' , 'Stavanger' )
• 2nd way (Method)
• it specifies both the column names & values to be inserted
• INSERT INTO Persons (P_Id, LastName,FirstName) VALUES (5,'Tom', 'Ram')
• 3rd Way (Method)
• INSERT INTO TempTbl(ClientName,Item) SELECT ClientName,Item FROM TempTbl WHERE Srno=948
*=
• in older version , it represent left outer join
• =*
• in older version , it represent right outer join

use Statement

• USE Master
• USE MyDataBase1

E.g of some important/Usefull queries showing use of various operators/keywords

• e.g1
• select productmaster.SHOPNO,productmaster.MODELNO,
• isnull(sum(isnull(ProductMaster.Qty,0)),0) as PQTY,
• case productmaster.MODELNO
• when productmaster.MODELNO
• then (Select isnull(sum(isnull(ComponentMaster.Qty,0)),0) from ComponentMaster 
• where ComponentMaster.MODELNO=ProductMaster.MODELNO and ComponentMaster.SHOPNO=ProductMaster.SHOPNO)
• else 0
• end as SQTY,
• case productmaster.MODELNO
• when productmaster.MODELNO
• then (isnull(sum(isnull(ProductMaster.Qty,0)),0)-(Select isnull(sum(isnull(ComponentMaster.Qty,0)),0) from ComponentMaster
• where ComponentMaster.MODELNO=ProductMaster.MODELNO and ComponentMaster.SHOPNO=ProductMaster.SHOPNO))
• else 0
• end as BALQTY
• From PRDMODELSTKDTL
• left outer JOIN PRODUCTMASTER ON PRDMODELSTKDTL.modelno=productmaster.MODELNO
• group by PRODUCTMASTER.SHOPNO,PRODUCTMASTER.MODELNO

UPDATE stmnt

• used to update existing records in a table
• in it WHERE clause specifies which record or records should be updated.
• if WHERE clause is omitted , all records will be updated
• UPDATE Persons SET Address='Abc' , City='pqr' WHERE LastName='m' AND FirstName='mm'

DELETE stmnt

• used to delete records/rows in a table
• in it WHERE clause specifies which record or records should be deleted.
• if WHERE clause is omitted , all records will be deleted
• DELETE FROM Persons WHERE LastName='abc' AND FirstName='p'
• delete all rows
• one way
• DELETE FROM Persons
• 2nd way
• DELETE * FROM Persons
• 3rd way
• using TRUNCATE stmnt
• delete all rows from the table
• TRUNCATE TABLE Persons

TOP Clause

• used to specify the no of records to return
• Note:->not all db systems support the TOP clause
• SELECT TOP no|percent col1 FROM T1
• SELECT TOP 2 * FROM Persons
• SELECT TOP 50 PERCENT * FROM Persons
• Top in MySQL
• SELECT * FROM Persons LIMIT 5
• Top in Oracle
• SELECT * FROM Persons WHERE ROWNUM <= 5

 AS (SQL Alias)

• an alias name can be given to a table or to a column with alias
• E.g with Alias
• SELECT t1.OrderID,t2.LastName,t2.FirstName 
• FROM Persons AS t2 , Product_Orders AS t1
• WHERE t2.LastName='Ram' AND t2.FirstName='Mohan' 
• E.g without alias
• SELECT Product_Orders.OrderID,Persons.LastName,Persons.FirstName 
• FROM Persons , Product_Orders 
• WHERE Persons.LastName='Ram' AND Persons.FirstName='Mohan' 

 sample tables ->

•  Persons
• P_Id           LastName            FirstName    Address                       City
•  1               Kakkar                Arun             C-13                           Delhi 
•  2              Gupta                   Vikas           H.No. 323                    VikasPuri
• Orders
• O_Id          OrderNo            P_Id          OrderDate          OrderPrice           Customer
•  1                77895                 3              07/05/2010            25000                 Ram 
•  2                44678                 3              05/05/2010                800                 Mohan
•  3                22456                 1              07/04/2010              4100                 Ganesh 
•  4                24562                 1              07/05/2009              8500                 Mohan 
•  5                34764               15              25/03/2010            98500                 Ram
•  Employees_Norway
• E_Id                               E_Name 
•  01                                  Ram 
•  02                                  Krishan 
•  03                                  Mohan
•  04                                  Jesus
•  Employees_USA
• E_Id                                E_Name
•  01                                  Vishnu
•  02                                  Krishan 
•  03                                  Ganesh 
•  04                                  Brahma

 SQL JOINS 

•  JOIN keyword is used to query data from 2 or more tables, based on a relationship b/w certain cols in these tables 

 Types of JOINS

• INNER JOIN
• return rows when there is atleast one match in both tables
• Join
• SELECT p.LastName,p.FirstName, or.OrderNo 
• FROM Persons as p INNER JOIN Orders as or ON p.p_Id=or.P_Id
• ORDER BY p.LastName
•  above query return all the persons with any orders. 
• Self-Join
• Natural-Join

OUTER JOIN

• Left Join (Left Outer Join)
• return all rows from left table, even if there is no matches in right table
• in some db, Left Join is called Left Outer Join
• below query show all persons & their orders if any (if for a persons orderno not present then orderno will be blank
• SELECT P.LastName, Or.OrderNo
• FROM Persons as P LEFT JOIN Orders as Or ON P.P_Id=Or.P_Id
• ORDER BY P.LastName
• it returns all rows from persons even if there are no matches in orders table
• Right Join (Right Outer Join)
• return all rows from right table, even if there is no matches in left table 
•  below query return all orders with containing persons if any
• SELECT P.LastName, Or.OrderNo 
• FROM Persons as P RIGHT JOIN Orders as Or ON P.P_Id=Or.P_Id
• ORDER BY P.LastName
• it returns all rows from Orders even if there are no matches in Persons table
• Full Join
• return rows when there is a match in one of the tables
• below returns all persons & their Orders & all orders with their persons
• SELECT P.LastName, Or.OrderNo 
• FROM Persons as P FULL JOIN Orders as Or ON P.P_Id=Or.P_Id
• ORDER BY P.LastName
• it returns all rows from persons & all rows from orders table 
• CROSS JOIN

sql UNION Operator

• it combines the result-set of two or more SELECT stmnts 
• each SELECT stmnt must have same no of cols,similar data types, & all cols must be in same order
• it selects only distinct values by default
• cols names in result-set of a UNION are always equal to the col names in the first SELECT stmnt in UNION
• Below query list all DIFFERENT employees in Norway & USA 
• SELECT E_Name FROM Employees_Norway
• UNION 
• SELECT E_Name FROM Employees_USA
• above query will return krishan only once 

UNION ALL

• it is used to allow duplicate values, which is not possible in UNION operator
• Below query list all employees in Norway & USA 
• SELECT E_Name FROM Employees_Norway 
• UNION ALL
• SELECT E_Name FROM Employees_USA 
• above query will return krishan Twice 

sql SELECT INTO  stmnt 

• used to create backup copies of tables
• it select data from one table & inserts it into a different table
• copy in same db
• SELECT * INTO Persons_BackUp FROM Persons 
• copy in diff db
• SELECT * INTO Persons_BackUp IN 'Backup.mdb' FROM Persons
• copy only some cols
• SELECT LastName,FirstName INTO Persons_Backup FROM Persons
•  WHERE city='Delhi'
• copy from 2 tables
• SELECT P.FirstName, Or.OrderNo INTO Persons_Orders_Backup 
• FROM Persons as P INNER JOIN Orders as Or ON P.P_Id=Or.P_Id

SQL built-in functions->

• SQL DATES functions
• The most difficult part when working with the dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the db. 
• As long as your data contains only the date portion, your queries will work as expected.However, if a time portion is involved, it gets complicated. 
• In SQL Server built in Date functions
• GETDATE()
• Returns the current date and time  
• DATEPART()
• Returns a single part of a date/time 
• DATEADD() 
• Adds or subtracts a specified time interval from a date 
• DATEDIFF() 
• Returns the time between two dates 
• CONVERT() 
• Displays date/time data in different formats 
• data types for date in sql server
• sql server has following data types for storing a date or a date/time value in the database; 
• DATE -->
•  format YYYY-MM-DD
• DateTime --->
• format: YYYY-MM-DD HH:MM:SS
• SmallDateTime --> 
• format: YYYY-MM-DD HH:MM:SS
• TIMESTAMP -->
• format : a unique number
• In MySQL built-in date functions
• NOW()
• Returns the current date and time
• CURDATE()
• Returns the current date
• CURTIME()
• Returns the current time
• DATE()
• Extracts the date part of a date or date/time expression
• EXTRACT()
• Returns a single part of a date/time
• DATE_ADD()
• Adds a specified time interval to a date
• DATE_SUB()
• Subtracts a specified time interval from a date
• DATEDIFF()
• Returns the number of days between two dates
• DATE_FORMAT()
• Displays date/time data in different formats
• data types for date in MySQL
• DATE --> 
• format YYYY-MM-DD
• DATETIME --->
• format: YYYY-MM-DD HH:MM:SS
• TIMESTAMP -->
•  format: YYYY-MM-DD HH:MM:SS
• YEAR -->
• format YYYY or YY
• E.g
• SELECT * FROM Orders WHERE OrderDate='2008-11-11'
• if orderdate don't have time component, we will get proper result.
• if orderdate have time component, we will not get any resultant row.
• if you want to keep your queries simple & easy to maintain, do not allow time components in your date field.
• sql NULL functions
• ISNULL() ( In SQL Server & MS Access)
• SELECT Name,Price*(Units +ISNULL(extraUnits,0)) FROM OrdersBill
• NVL() (In Oracle) 
• SELECT Name,Price*(Units +NVL(extraUnits,0)) FROM OrdersBill
• IFNULL() (mysql) 
• SELECT Name,Price*(Units +IFNULL(extraUnits,0)) FROM OrdersBill
• COALESCE() (mysql) 
• SELECT Name,Price*(Units +COALESCE(extraUnits,0)) FROM OrdersBill
• SQL Agregate functions
• they return a single value, calculated from values in a column.
• Aggregate functions often need an added GROUP BY stmnt
• AVG() 
• Returns the average value of a numeric col
• SELECT AVG(sal) AS AverageSalary FROM Persons
• below query show all persons whom salary is above the average salary
• SELECT * FROM Persons
• WHERE sal>(SELECT AVG(sal) FROM Persons)
• COUNT()
• returns the number of rows/records
• null values wil not be counted of the specified column
• DISTINCT
• will count the no of distinct values ot the specified col
• E.g
• SELECT COUNT(Ename) AS EmployeeName FROM Persons
• WHERE group='Operator'
• SELECT COUNT(*) FROM Persons
• SELECT COUNT(DISTINCT customer) AS TotalCustomer FROM SaleBillOrders
• SELECT COUNT(Customer) AS Ram FROM Orders WHERE Customer='Ram'
• MAX()
• Returns the largest value of selected col
• SELECT MAX(Age) AS OldestEmplyeeAge FROM Persons
• MIN()
• returns the smallest value of the selected col
• SELECT MIN(Age) AS YoungestEmplyeeAge FROM Persons
• SUM()
• returns the total sum of a numeric col
• SELECT SUM(Sal) As TotalSal FROM Persons
• FIRST()
• return the first value of selected col
• SELECT FIRST(Name) AS PersonName FROM Persons
• if first not work in db
• SELECT Name FROM Persons ORDER BY O_Id LIMIT 1
• LAST()
• return the last value of selected col
• SELECT LAST(Name) AS PersonName FROM Persons
• if Last() not work in db 
• SELECT Name FROM Persons ORDER BY O_Id DESC LIMIT 1
• GROUP BY stmnt
• It is often needed by Aggregate functions to group the result-set by one or more cols
• SELECT Customer,SUM(OrderPrice) FROM Orders
• GROUP BY Customer
• SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
• GROUP BY Customer,OrderDate
• HAVING clause
• mostly used with GROUP BY stmnt to provide conditions as WHERE keyword
• can't be used with aggregate functions.
• SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000 
• SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Ram' OR Customer='Mohan' GROUP BY Customer HAVING SUM(OrderPrice)<2000 
• SQL SCALAR functions 
• return a single value based on the input value 
• UCASE() or UPPER() for SQL server
• converts a value to upper case
• SELECT UCASE(LastName) AS LastName,FirstName FROM Persons 
• UPPER() for SQL server
• SELECT UPPER(LastName) AS LastName,FirstName FROM Persons 
• LCASE() or LOWER() in SQLserver
• converts a value to lower case 
• SELECT LCASE(LastName) AS LastName,FirstName FROM Persons 
• LOWER() in SQLserver 
• SELECT LOWER(LastName) AS LastName,FirstName FROM Persons 
• MID()
• extract characters from a text field syntax
• SELECT MID(col_Name,Start [,length]) FROM table_name 
• start----> starting position (starts as 1)
• SELECT MID(City,1,4) AS SmallCity FROM Persons
• LEN()
• return the length of a text field
• SELECT LEN(Address) AS LengthOfAddress FROM Persons
• ROUND()
• rounds a numeric field to the number of decimals specified
• SELECT ProductName,ROUND(UnitPrice,0) AS UnitPrice FROM Products
• NOW()
• return the current system date & time
• SELECT ProductName,UnitPrice,NOW() AS PerDate FROM Products
• FORMAT()
• formats how a field to be displayed
• SELECT ProductName,UnitPrice,FROMAT(NOW(),'YYYY-MM-DD') as PerDate FROM Products

SQL HOSTING

• If Your web site store & display data from a db, your web server should have access to a db system that uses SQL Language.
• If your web server will be hosted by an ISP (Internet Service Provider) ie you purchase web hosting space (not uses your own pc as webserver & db), you will have to look for SQL Hosting Plans.
• SQL hosting dbs
• most common are MySQL, MS SQL Server,MS Access
• SQLServer,MS Access
• Runs only on Windows OS
• MySQL
• Runs on both Windows and Linux/UNIX OS (very cheap)

SQL COMMENTS

• SINGLE LINE (--)
• --this is SINGLE LINE comment
• MULTIPLE LINES (/* */)
• /* this is multiple line comment method                                                                                          this second line is also commented */

ConnectionString by UDL file (in VB6)

• open udl file & set connection & to see whole connection string
• open udl file in notepad

Miscellaneous

• sysobjects  (In MS SQL Server 2000)
• return list of all tables,procedure,functions etc diffenciated by type
• type=p for sp (stored Procedures) , U for User Defined table in db etc
• E.g
• select * from sysobjects order by type
• Select name from sysobjects where type='U'
• use logmaindata ;select name from sysobjects where type='u' order by name
• above query give list of all tables in db "logmaindata"
• sysdevices
• return all the devices used/created while taking database backup/restore
• Use Master ; select * from sysdevices
• SysDatabases  (In MS SQL Server 2000)
• list of all database. To use it, we must use master db first of all
• E.g
• use master ; select * from sysdatabases order by name
• SELECT COUNT (*) AS CNT FROM SYSDATABASES WHERE NAME='logmaindata'
• Backup a db (In MS SQL Server 2000) using VB6
• Below is a procedure in VB6 to take Backup of  DB
• Public Sub Manual_Backup()
• On Error GoTo xer
• Dim Path As String, MyFile As String, FullPath As String
• Dim num As Integer 
• Set create = New FileSystemObject
• With create
• If FrmTool.Check1.Value = 1 Then 'user select default setting
• Path = "C:\Back Up" 
• MyFile = "LogDryClnBakUp.bak" 
• FullPath = Path & "\" & MyFile
• ElseIf FrmTool.Check1.Value = 0 Then 'if user provide path & file name
• 'MyFile = Trim(FrmTool.TxtName.Text)
• FullPath = Trim(FrmTool.TxtPath.Text)
• If FullPath = "" Then
•  MsgBox "BackUp has not been taken., Please Give the Path and File Name", vbInformation + vbOKOnly
• Exit Sub
• End If
• If InStr(1, FullPath, ".bak") < 1 Then FullPath = FullPath & ".bak" 
• MyFile = FullPath
• num = InStr(1, MyFile, "\")
• If num < 1 Then MsgBox "BackUp has not been taken., Please Give the correct Path and File Name", vbInformation + vbOKOnly: Exit Sub
• Do While num > 0
• MyFile = Mid(MyFile, num + 1, Len(MyFile) - num)
• num = InStr(1, MyFile, "\")
• Loop
• 'MyFile = MyFile & ".bak"
• Path = Mid$(FullPath, 1, InStr(1, FullPath, MyFile, vbTextCompare) - 2)
• End If
• If .FolderExists(Path) = False Then
• CreateFolder (Path)
• End If
• If .FileExists(FullPath) = True Then
• Kill FullPath
• End If
• Connect GSVRNAME, "sa", "", "master"
• If GREC.State Then GREC.Close
• GREC.Open "select * from sysdevices where name='" & MyFile & "'", GCON, adOpenStatic, adLockReadOnly
• If GREC.EOF = False Then
• strg = "exec sp_dropdevice '" & MyFile & "'"             
• GCON.Execute strg         
• End If         
• strg = "EXEC sp_addumpdevice 'disk', '" & MyFile & "','" & FullPath & "'"         
• GCON.Execute strg         
• strg = "BACKUP DATABASE logmaindata TO disk='" & FullPath & "'"         
• GCON.Execute strg
• strg = "exec sp_dropdevice '" & MyFile & "'"         
• GCON.Execute strg     
• End With     
• MsgBox "Backup Database Successfully Saved", vbInformation + vbOKOnly     
• Connect GSVRNAME, "sa", "", "logmaindata"          
• strg = "UPDATE Confrigation SET SettingValue='N' WHERE SettingName='AutoBackup'"     
• GCON.Execute strg     
• Exit Sub 
•  xer:     
• MsgBox "There is an error -> Unable to take BackUp. Please Select Path to Click Brows Button", vbInformation + vbOKOnly     
• Connect GSVRNAME, "sa", "", "logmaindata" 
•  End Sub
• Restore a DB (In MS SQL Server 2000) using VB6
• Below is a procedure in VB6 to take Restore DB from above created backup file
• Private Sub CmdRestore_Click()
• On Error GoTo xer
• If Me.Txtpth <> "" Then 
• If MsgBox("The Restore Facility Required to quit from software," & Chr(13) & " wish to continue", vbQuestion + vbYesNo) = vbYes Then
• Me.CmdRestore.Enabled = False: Me.CmdReset.Enabled = False
• Connect GSVRNAME, "sa", GPWD, "master"
• strg = "RESTORE DATABASE logmaindata FROM disk='" & Me.Txtpth.Text & "'"
• GCON.Execute strg
• MsgBox "Database Has Been Successfully Restored", vbInformation + vbOKOnly
• End
• End If
• Else
• MsgBox "Please select the file", vbInformation + vbOKOnly
• Exit Sub
• End If
• Exit Sub
• xer:
• CritError
• 'MsgBox "Database is Currently in Use Can't Restore", vbInformation + vbOKOnly
• End Sub
• Backup DB compressd  (In MS SQL Server 2000) using VB6
• Below is a procedure in VB6 to take Backup of  DB in compressed format
• Private Sub cmdBackup_Click()
• On Error GoTo xer
• If Not FS.FolderExists("c:\backup") Then
• FS.CreateFolder "c:\backup"
• End If
• If Dir("c:\backup\Tmp", vbDirectory) <> "" Then FS.DeleteFolder "c:\backup\Tmp"
• MkDir "c:\backup\tmp"
• cur_data = Me.ListView1.SelectedItem.SubItems(2)
• Me.ProgressBar1.Value = 10
• cstr1 = GServer.GCON.ConnectionString
• If GServer.GCON.STATE = 1 Then GServer.GCON.Close
• cstr2 = GServer.GXCON.ConnectionString
• If GServer.GXCON.STATE = 1 Then GServer.GXCON.Close
• GServer.ConnectXDB gsvrname, guname, gpwd, "master"
• If Cn.STATE = 1 Then Cn.Close
• Cn.ConnectionString = GServer.GXCON.ConnectionString
• Cn.Open
• Cn.Execute "backup database " & cur_data & " to disk ='C:\Backup\Tmp\" & cur_data & "_Backup" & ".bak'"
• Me.ecCompress1.FileName = "C:\Backup\" & cur_data & "_Backup" & ".cab"
• Me.ecCompress1.Files.Add "C:\Backup\tmp\" & cur_data & "_Backup" & ".bak"
• CheckSpace
• Me.ecCompress1.Compress
• If MsgBox("Backup Completed Successfuly", vbOKOnly) = vbOK Then
• FS.DeleteFolder "c:\backup\tmp"
• GServer.GCON.ConnectionString = cstr1
• GServer.GCON.Open
• GServer.GXCON.ConnectionString = cstr2
• GServer.GXCON.Open
• If Cn.STATE = 1 Then Cn.Close
• Unload Me
• Else
• Unload Me
• End If
• If Cn.STATE = 1 Then Cn.Close
• Exit Sub
• xer:
• If Err.Number = -2147217900 Or Err.Number <> 0 Then
• Resume Next
• Else
• MsgBoxEx "Problem Exist Please Contact RNT COMPUTER SOLUTIONS (P) LTD.", 0, 0, BottomRight, vbCritical + vbOKOnly
• Resume Next
• End If
• End Sub
• Restore DB compressd (In MS SQL Server 2000) using VB6
• Below is a procedure in VB6 to Restore Backup of  DB taken above in compressed format
• Private Sub RestoreBackup(DRV As Variant)
• On Error GoTo er
• If Dir("c:\backup\Tmp", vbDirectory) <> "" Then FS.DeleteFolder "c:\backup\Tmp"
• MkDir "c:\backup\tmp"
• Me.ProgressBar2.Visible = True
• If Dir("C:\backup\" & Me.ListView2.SelectedItem.SubItems(2) & "_Backup.cab", vbNormal) <> "" Then
• Me.ecExtract1.FileName = "c:\backup\" & Trim(Me.ListView2.SelectedItem.SubItems(2) & "_backup.cab")
• Me.ecExtract1.Files.SelectAll
• Me.ecExtract1.ExtractAll "c:\backup\Tmp\"
• cstr1 = GServer.GCON.ConnectionString
• If GServer.GCON.STATE = 1 Then GServer.GCON.Close
• cstr2 = GServer.GXCON.ConnectionString
• If GServer.GXCON.STATE = 1 Then GServer.GXCON.Close
• GServer.ConnectXDB gsvrname, guname, gpwd, "master"
• GServer.Connect gsvrname, guname, gpwd, "master"
• If Cn.STATE = 1 Then Cn.Close
• Cn.ConnectionString = GServer.GXCON.ConnectionString
• Cn.Open
• If GServer.GCON.STATE = 1 Then GServer.GCON.Close
• If GServer.GXCON.STATE = 1 Then GServer.GXCON.Close
• cur_data = Me.ListView2.SelectedItem.SubItems(2)
• Cn.Execute ("Use Master")
• Cn.Execute "restore database " & cur_data & " From disk ='C:\Backup\tmp\" & cur_data & "_Backup" & ".bak'"
• If MsgBox("Restore Completed Successfuly", vbOKOnly) = vbOK Then
• FS.DeleteFolder "c:\backup\tmp"
• GServer.GCON.ConnectionString = cstr1
• GServer.GCON.Open
• GServer.GXCON.ConnectionString = cstr2
• GServer.GXCON.Open
• Else
• Unload Me
• End If
• Else
• GoTo ext
• End If
• If Dir("c:\backup\Tmp", vbDirectory) <> "" Then FS.DeleteFolder "c:\backup\Tmp"
• If Cn.STATE = 1 Then Cn.Close
• Unload Me
• Exit Sub
• ext:
• MsgBox "No Backup Information For '" & Me.ListView2.SelectedItem.Text & "' Company Please Insert Another Disk", vbInformation + vbOKOnly, "Eh!"
• Exit Sub
• er:
• If Err.Number <> 0 Then
• MsgBox Err.Description, vbCritical + vbOKOnly, "Eh!"
• End If
• End Sub
• List of databases (In MS SQL Server 2000)
• Use Master ; SELECT * FROM SYSDATABASES 
• Attach a database with user defined location but within same computer i.e. location must not be in network (In MS SQL Server 2000)
• use master ; EXEC sp_attach_db @dbname = 'TEMPCure', 
•   @filename1 = 'c:\Temp2\TEMPCure.mdf', 
•   @filename2 = 'c:\Temp2\TEMPCure_log.ldf'
• Deattach a database from the server (In MS SQL Server 2000)
• EXEC sp_detach_db 'TEMPCure', 'true'
• Convert a Column Value from "Male" to "Female" & "Female" to "Male" & leave rest as it is in single query
• Its very simple If one know USE of "CASE" stmnt in SQL Query (Its very important as I am unable to clear one of my interview due to lack of its knowledge)
• E.g
• Ist of all I create a temporary table for testing & below are some useful Queries along with main query
• use ArunDatabase1
• create table temp1(sno int Primary Key Identity, sex nvarchar(10) null)
• select * from temp1
• Add new column in the existing table
• alter table temp1 add Age int null
• Insert Multiple rows in one query->1st method use ; 2nd method-> use below UNION ALL and INSERT INTO … SELECT… clauses.
• INSERT INTO Temp1(Sex,age)
• SELECT 'Male',20
•   UNION ALL
• SELECT 'Female',25
•   UNION ALL
• SELECT 'Female',30
•   UNION ALL
• SELECT 'Male',40
• UPDATE Temp1 SET
• Sex=
• CASE 
• WHEN sex='Male' 
• THEN 'Female' 
• WHEN sex='Female'
• THEN 'Male'
• ELSE Sex
• END,
• Age=
• CASE 
• WHEN Age>35 THEN Age+3
• WHEN Age<25 THEN Age+2
• WHEN Age IS NULL THEN 20
• ELSE AGE
• END
• Below are some more Useful examples to make the use of Case statement more clearer
• E.g 1
• SELECT title, price,
•         Budget = CASE price
•          WHEN price > 20.00 THEN 'Expensive'
•           WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
•           WHEN price < 10.00 THEN 'Inexpensive'
•           ELSE 'Unknown'
•         END,
• FROM titles
• below query give same result as above one
• SELECT title, price, 'Expensive'
• FROM titles
• WHERE price > 20.00
• UNION ALL
• SELECT title, price, 'Moderate'
• FROM titles
• WHERE price BETWEEN 10.00 AND 19.99 
• UNION ALL
• SELECT title, price, 'Inexpensive'
• FROM titles
• WHERE price < 10.00
• UNION ALL
• SELECT title, price, 'Unknown'
• FROM titles
• WHERE price IS NULL
• E.g 2 
• Before using below query I create a table to execute below queries
• CREATE TABLE prodsales
•  (product char(3),
•   mnth    smallint,
•   sales   money)
• Now queries using above created tables are as below
• SELECT product,
•   SUM(CASE mnth WHEN 1 THEN sales ELSE NULL END) AS jan,
•   SUM(CASE mnth WHEN 2 THEN sales ELSE NULL END) AS feb,
•   SUM(CASE mnth WHEN 3 THEN sales ELSE NULL END) AS mar,
•   SUM(CASE mnth WHEN 4 THEN sales ELSE NULL END) AS apr,
•   SUM(CASE mnth WHEN 5 THEN sales ELSE NULL END) AS may,
•   SUM(CASE mnth WHEN 6 THEN sales ELSE NULL END) AS jun,
•   SUM(CASE mnth WHEN 7 THEN sales ELSE NULL END) AS jul,
•   SUM(CASE mnth WHEN 8 THEN sales ELSE NULL END) AS aug,
•   SUM(CASE mnth WHEN 9 THEN sales ELSE NULL END) AS sep,
•   SUM(CASE mnth WHEN 10 THEN sales ELSE NULL END) AS oct,
•   SUM(CASE mnth WHEN 11 THEN sales ELSE NULL END) AS nov,
•   SUM(CASE mnth WHEN 12 THEN sales ELSE NULL END) AS dec
• FROM prodsales
• GROUP BY product
• E.g 3
• SELECT product,
•   SUM(CASE WHEN mth BETWEEN 1 AND 3 THEN sales
•       ELSE NULL END) AS q1,
•   SUM(CASE WHEN mth BETWEEN 4 AND 6 THEN sales
•       ELSE NULL END) AS q2,
•   SUM(CASE WHEN mth BETWEEN 7 AND 9 THEN sales
•       ELSE NULL END) AS q3,
•   SUM(CASE WHEN mth BETWEEN 10 AND 12 THEN sales
•       ELSE NULL END) AS q4
• FROM prodsales
• GROUP BY product 
• E.g 4
• SELECT au_lname, au_fname, title, Category =
•         CASE
•          WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
•                            WHERE t.title_id = ta.title_id) > 65
•                  THEN 'Very High'
•          WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
•                            WHERE t.title_id = ta.title_id)
•                                      BETWEEN 55 and 64
•                  THEN 'High'
•          WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
•                            WHERE t.title_id = ta.title_id)
•                                      BETWEEN 41 and 54
•                  THEN 'Moderate'
•          ELSE 'Low'
•        END
• FROM authors a,
•      titles t,
•      titleauthor ta
• WHERE a.au_id = ta.au_id
• AND   ta.title_id = t.title_id
• ORDER BY Category, au_lname, au_fname
• Below query find 2nd Lowest age but if there is duplicacy then it gives wrong result
• SELECT TOP 1 Age FROM
•  (SELECT TOP 2 Age FROM Temp1 ORDER BY Age ASC) AS SeconLowestAge
•  ORDER BY Age DESC
• Below query find 2nd Lowest age but if there is duplicacy then also it gives correct result
• SELECT TOP 1 Age FROM
•  (SELECT TOP 2 Age FROM
• (SELECT DISTINCT Age FROM Temp1) AS T1 
•  ORDER BY Age ASC) AS SeconLowestAge
•  ORDER BY Age DESC
• Above queries run well in SQL Server 2000, but in SQL Server2005, you can't use Order by clause in the subquery, so these would not work there.
• 
  
• Below query find nth highest age using Joins
• Note: here let n=5 to find 5th highest age for table
• 
  
• CREATE TABLE temp1
• (
•  sno INT PRIMARY KEY IDENTITY,
•  sex NVARCHAR(10) NULL,
•  age INT NULL
• )
• 
  
• select * from temp1 t1
• where 5-1=(select count(distinct age) from temp1 t2
• where t2.age>t1.age)
• Below query find nth Lowest age using Joins
• • Note: here let n=5 to find 5th Lowest age for table
  • 
    
  • select * from temp1 t1
  • where 5-1=(select count(distinct age) from temp1 t2
  • where t2.age< t1.age)
• fill table from another table
• 
  
• create table temp2
• (
• id int identity not null,
•  myName nvarchar(20)
•  )
• 
  
• 
  
• insert into temp2(myName)
• select sex from temp1
• 
  
• Make SQL Query to get columns from diff small queries as diff column names
• select (select ReceiptId from FeeReceiptMain where ReceiptNo='REC-1') as FromFeeId,                (select ReceiptId from FeeReceiptMain where ReceiptNo='REC-10') as ToFeeId

          I hope it will help those who wish to make quick revision of sql. Please forgive me if I made any mistake. I made these notes keeping my habit of revising a topic & due to lack of time & length of this blog, I don't add many comments to explain it. Its just notes for quick revision not for any fresher. Of course, there are lots of topic left in it but its just the overview of some of the main topics which are used most frequently.

  I keeps on updating it as I get time & would like & appreciate your comments to improve it.