Arun Kakkar - Software Engineer: 2010

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'

-> 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

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

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

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
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.

Wednesday, October 6, 2010

Quickly Revise SQL used in many popular RDBMS Databases

Thursday, September 30, 2010

Basic Computer Course to be good in Microsoft Computers

Wednesday, August 4, 2010

My TimePass

Thursday, April 8, 2010

Dot Net as whole vs by many institutes