Structured Query

Language
Topic Covered
•Introduction to SQL and SQL Plus
• DDL
• DML
•Aggregate Functions
•Group By and Having clause
SQL
SQL is used to make a request to retrieve data from a
Database.

The DBMS processes the SQL request, retrieves the

requested data from the Database, and returns it.

 This process of requesting data from a Database and

receiving back the results is called a Database Query
and hence the name Structured Query Language.

2
SQL
SQL is a language that all commercial RDBMS
implementations understand.

SQL is a non-procedural language

We would be discussing SQL with respect to oracle

syntax

3
Structured Query Language (SQL)
SQL Request

DBMS
Data Database

01000101
11001010
01001011
Computer System

You can visit the following link for more

information:
Introduction to Oracle8 Architecture
Oracle SQL FAQ
4
Structured Query Language (SQL)
 1979 Oracle Corporation introduces the first commercial RDBMS
 1982 ANSI (American National Standards Institute) forms SQL
Standards Committee
 1983 IBM (International Business Machine) announces DB2 (a
Database)
 1986 ANSI (American National Standards Institute) SQL1 standard is
approved
 1987 ISO (International Organization for Standardization) SQL1
standard is approved
 1992 ANSI (American National Standards Institute) SQL2 standard is
approved
 2000 Microsoft Corporation introduces SQL Server 2000, aimed at
enterprise applications
 2004 SQL: 2003 standard is published

5
Statements
DDL (Data Definition Language)
 Create
 Alter
 Drop
 Truncate

DML (Data Manipulation Language)

 Insert
 Update
 Delete
 Select

DCL (Data Control Language)

 Grant
 Revoke
 Commit
 Rollback

6
Data types
Number
Char
Varchar2
Long
date

7
NULL
Missing/unknown/inapplicable data represented as a
NULL value
NULL is not a data value. It is just an indicator that the
value is unknown

8
Operators
Arithmetic operators: +, -, *, /

Logical operators: AND, OR, NOT

Relational operators: =, <=, >=, < >, < , >

9
SQL Data Definition Language
Types Of Constraints
Column Level

Table level

11
 Types Of Constraints
Primary Key Constraint

Foreign Key Constraint

Unique Constraint

Check Constraint

Not Null Constraint

12
CREATE TABLE EXAMPLE
Customer Table
Implementing PRIMARY KEY ,NOT NULL and UNIQUE

Colum name Datatype Description Constraints

Unique id generated for each Primary Key, Should

CustomerId Varchar2(6) customer start with ‘C’
CustomerName Varchar2(30) Name of the customer Not null
Date on which the customer
DateOfReg Date registered

Decided at the time of

UserId Varchar2(15) registration It should be unique
Decided at the time of
Password Varchar2(15) registration Not Null

13
SQL - CREATE TABLE
Implementing PRIMARY KEY ,NOT NULL and UNIQUE
EXAMPLE :
Create table Customer
(CustomerId varchar2(6) constraint customer_pk PRIMARY KEY constraint
customer_cid check (CustomerId like 'C%'),
CustomerName varchar2(30) constraint customer_customername_nnull NOT NULL,
DateofReg date ,
UserId varchar2(15) constraint customer_userid_uq UNIQUE,
Password varchar2(15) constraint customer_passwd NOT NULL);

14
Create Table (Contd…)
Implementation of Composite Primary Key and Foreign Key Constraints

BankInfo Table

Colum name Datatype Description Constraints

Account no of
AccountNo Number(10) customer

Unique id provided to Foreign key Composite

each customer when referring to Primary key
he/she is registered to customer
CustomerId Varchar2(6) purchase items table

15
Create Table (Contd…)
Implementation of Composite Primary Key and Foreign Key Constraints

EXAMPLE :
Create table Bankinfo
(AccountNo number(10),
CustomerId varchar2(6) constraint bankinfo_fk references
Customer(CustomerId),
constraint bankinfo_pk PRIMARY KEY(AccountNo, CustomerId));

16
Create Table (Contd…)
Implementation of Self Referencing Foreign key in Employee_Details Table

Column Name Data type Constraints

Employee_ID Number(6) Primary key of the table

Employee_Last_Name Varchar2(20)

Employee_Mid_Name Varchar2(3)
Employee_First_Name Varchar2(20)
Employee_Email Varchar2(30)
Employee_Dept Number(2) Default ‘HR’
Manager_ID Varchar2(30) It can take only those values
which are present in
Employee_ID column

17
Create Table (Contd…)
 Implementing Self Referential Foreign Key
EXAMPLE :
CREATE TABLE Employee_Details(
Employee_ID Number(5)
CONSTRAINT Employee_PKey PRIMARY KEY,
Employee_Last_Name Varchar2(20),
Employee_Mid_Name Char(3),
Employee_First_Name Varchar2(20),
Employee_Email Varchar2(30),
Department Varchar2(10) default ‘HR’,
Manager_ID Number(5)
CONSTRAINT Manager_FKey REFERENCES
Employee_Details(Employee_ID)
);

18
 Create Table (Contd…)
Implementing Check
Item Table
Colum
name Datatype Description Constraints

Unique Id provided for each item. (eg STN001

ItemId Varchar2(6) for stationery items) Primary Key
ItemName Varchar2(30) Name of the item Not Null
QtyOnHan Should be greater than ReOrderLevel
d Number(3) Current availability of item in the shop (Table level constraint)
UnitPrice Number(6,2) Sell price of item per unit Greater than 0
Class of Item is ‘A’ if UnitPrice is less
than 100, ‘B’ if UnitPrice is less than
Depending on the UnitPrice, items belongs to 1000, ‘C’ if UnitPrice is 1000 and above
Class Char(1) various Classes. eg: A,B,C etc. (Table level constraint)
UnitOfMea Unit used to measure the quantity ( eg
surement Varchar2(12) Kilogram, dozen etc)
ReOrderLev Minimum Quantity after which the supplier
el Number(3) must be ordered for new stock Greater than 0
Minimum Quantity that can be ordered to the
ReorderQty Number(3) supplier Greater than 0
Percentage discount on the item to the
Discount Number(2) customer

19
Create Table (Contd…)
Implementing Check constraint
EXAMPLE :
Create table Item
(ItemId varchar2(6) constraint Item_pk PRIMARY KEY,
ItemName varchar2(30) constraint Item_Itemname_Nnull NOT NULL,
QtyOnHand Number(3) ,
UnitPrice Number(6,2) constraint Item_unitprice_chk CHECK( UnitPrice > 0 ),
Class char(1),
UnitofMeasurement varchar2(12) ,
ReOrderLevel Number(3) constraint item_reorderlevel CHECK(ReOrderLevel > 0),
ReOrderQty number(3) constraint item_reorderqty CHECK(ReOrderQty >0),
Discount Number(2) ,
CHECK (QtyOnHand > ReOrderLevel),
constraint item_Class CHECK((Class='A' AND UnitPrice<=100) or (Class='B‘ AND
UnitPrice<=1000) or (Class='C‘ AND UnitPrice>1000)) );

20
Create Table (Contd…)
Implementing Composite Foreign key constraint
Billing Table

Colum name Datatype Description Constraints

Unique Id generated for each
BillId Number(4) bill Primary key
Account no which is used to
AccountNo Number(10) pay the bill
Customer id of the customer Composite
who does the purchase of Foreign key to
CustomerId Varchar2(6) items Bank info table
BillDate Date The date of payment of bill Default SYSDATE

Either Creditcard
PaymentType Varchar2(12) Type of Payment or Debitcard

21
Create Table (Contd…)
Implementing Composite Foreign key constraint
EXAMPLE :
Create table Billing
(BillId number(4) constraint billing_billid_pk PRIMARY KEY,
Accountno number(10) ,
CustomerId varchar2(6) ,
BillAmt number(7,2) constraint billing_billamt_Nnull NOT NULL,
Billdate date DEFAULT sysdate,
PaymentType varchar2(12) constraint billing_paymenttype CHECK(PaymentType in
(‘Creditcard', ‘Debitcard') ),
constraint billing_composite_fk FOREIGN KEY(AccountNo, CustomerId) references
BankInfo (AccountNo, CustomerId));

22
SQL - ALTER TABLE– ADD/DROP/MODIFY
Column
Add/Drop/Modify Column
Syntax:
ALTER TABLE tablename (ADD/MODIFY/DROP column_name)

ALTER TABLE Customer

ADD Contact_Phone Char(10);

ALTER TABLE Customer

MODIFY Contact_Phone Char(12);

ALTER TABLE Customer

DROP (Contact_Phone);

23
ALTER TABLE—ADD/DROP/MODIFY Column
 Used to modify the structure of a table by adding and removing
columns

 The ALTER TABLE statement with MODIFY option cannot be used

to change the name of a column or table.

 Column to be modified should be empty to decrease column length

 Column to be modified should be empty to change the data type

 If the table has only one column, the ALTER TABLE statement
cannot be used to drop that column because that would render the
table definition invalid.

24
SQL - ALTER TABLE—ADD/DROP Constraint
 Add/Drop Constraint
ALTER TABLE Customer_Account_Details
ADD CONSTRAINT Pkey1 PRIMARY KEY (Account_No);

ALTER TABLE Customer_Account_Details

ADD CONSTRAINT Pkey2 PRIMARY KEY (Account_No, Cust_ID);

ALTER TABLE Customer_Account_Details

DROP PRIMARY KEY;
Or
ALTER TABLE Customer_Account_Details
DROP CONSTRAINT Pkey1;

25
ALTER TABLE– ADD/DROP Constraint
ALTER TABLE Customer_Transaction
ADD CONSTRAINT Fkey1 FOREIGN KEY (Cust_ID)
REFERENCES Customer_Account_Details (Cust_ID);

ALTER TABLE Customer_Transaction

DROP CONSTRAINT Fkey1;

•A table can have one or more Foreign keys

•Adding a foreign key constraint using ALTER TABLE command will result in an error if the
existing data in master or child table does not support the foreign key restriction.

26
ALTER TABLE – ADD/DROP Constraint
 ALTER TABLE statement can be used to Add or Drop primary key
constraint to / from a table
 ALTER TABLE statement can be used to Add or Drop foreign key
constraint to / from a table
 ALTER TABLE statement can be used to Add or Drop Unique
constraint to / from a table
 ALTER TABLE statement can be used to Add or Drop check constraint
to / from a table

 If a table already has a primary key, then adding a primary key using the
ALTER TABLE statement results in an error.
 The RDBMS will not allow a PRIMARY KEY constraint (using the
ALTER TABLE statement) on column(s) if the column(s) has NULL or
duplicate values

27
SQL - DROP TABLE
DROP TABLE
 Deletes table structure
 Cannot be recovered
 Use with caution

DROP TABLE UnqTable;

28
Truncate Table
Deleting All Rows of a table
TRUNCATE TABLE Customer;

29
SQL -DML
SQL - INSERT INTO
INSERT INTO table-name [ column-name(s) ] VALUES ( ----------- constant (s) -------------)
NULL

Insert into customer values(‘C4',‘Allan','13-Mar-09',’Allan1004',’Allan@123');

C4 Allan 13-Mar-09 Allan1004 Allan@123

CustomerId CustomerNAme DateOfRegistration UserId Password

C1 John 1-Mar-09John1001 John@123
C2 Jack 10-Mar-09Jack1002 Jack@123
C3 Bob 12-Mar-09Bob1003 Bob@123
C4 Allan 13-Mar-09Allan1004 Allan@123

31
SQL - INSERT INTO
Inserting NULL in to the table-Method1

Insert into customer

values('C5','Simon',NULL,'Symon1005','Symon@123');

C5 Simon NULL Symon1005 Symon@123

Inserting NULL in to the table-Method2

Insert into customer( CustomerId, CustomerName, UseId, Password)

values( 'C5', 'Simon‘, 'Symon1005', 'Symon@123');

C5 Simon NULL Symon1005 Symon@123

32
SQL - DELETE FROM
With or without WHERE clause
Syntax: DELETE FROM tablename WHERE condition

Deleting All Rows

DELETE FROM Customer;

Deleting Specific Rows

DELETE FROM Customer

WHERE CustomerId = ‘C1’;

33
Difference Between Delete and Truncate
DELETE TRUNCATE
Data can be recovered Data cannot be recovered.

DML statement DDL statement

DELETE does not release the TRUNCATE releases the memory

memory occupied by the records occupied by the records of the table
of the table

34
SQL - UPDATE
Syntax:
UPDATE tablename SET column_name =value [ WHERE condition]

Updating All Rows

UPDATE Customer
SET DateOfReg = NULL;

Updating Particular rows

UPDATE Customer
SET DateOfReg = NULL
Where CustomerId = 'C1';

35
SQL - UPDATE
Updating Multiple Columns

UPDATE Customer
SET DateOfReg = NULL ,
Pasword = ‘John@321’
WHERE CustomerId = ‘C1’;

36
Retrieving Allnames,
To select set of column columns from a table
Syntax:
SELECT column1, column2,… FROM TableName
Example:

SELECT *
FROM Customer;

OR

SELECT CustomerId, CustomerName, DateOfRegs, UserId,

Password
FROM Customer;

37
 Retrieving Few Columns
Retrieving only CustomerID and UserId from Customer Table

SELECT CustomerId, UserId

FROM Customer;

Implementing Customized Columns Names

SELECT CustomerId AS “Customer Identification”,

UserId AS “User Identification”
FROM Customer;

38
SQL - ALL,
DISTINCT
Get all Customers Name:

SELECT ALL CustomerName

FROM Customer;

OR
SELECT CustomerName
FROM Customer;

Get all distinct Customer Name

SELECT Distinct CustomerName

FROM Customer;

Demos

39
Retrieving Rows based on Condition

Syntax:
SELECT COL1,COL2,.........

FROM TABLE NAME

WHERE < SEARCH CONDITION>;

40
Retrieving a subset of rows (Working of
WHERE Clause)
Problem Statement: To select CustomerId and UserId of the
customer whose date of registration is 13 March 2009.
CustomerId CustomerName DateOfRegistration UserId Password
C1 John 1-Mar-09 John1001 John@123
C2 Jack 10-Mar-09 Jack1002 Jack@123
C3 Bob 12-Mar-09 Bob1003 Bob@123
C4 Allan 13-Mar-09 Allan1004 Allan@123
C5 Simon Symon1005 Symon@123

SELECT CustomerId, UserId FROM Customer

WHERE DateOfReg ='13-Mar-2009';

CustomerId UserId
C4 Allan1004

41
Relational

operators
List all items whose unit price is > 100
SELECT ItemId,ItemName
FROM Item
WHERE UnitPrice > 100;

• List the CustomerId and UserId of ‘Allan’

SELECT CustomerId, UderId

FROM Customer
WHERE CustomerName = ‘Allan’;

Relational operators = , < , > , <= , >= , != or < >

42
Relational operators
List all items where discount is at least 10 percent.

SELECT ItemId,ItemName
FROM Item
WHERE Discount >= 10;

Relational operators = , < , > , <= , >= , != or < >

43
Logical operators
• List all items where Unit Price is less than 100 and Unit of
measurement is ‘Dozen’.
SELECT ItemId, ItemName
FROM Item
WHERE UnitPrice < 100
AND UnitOfMeasurement = ‘Dozen’;

• List all items where either the unit price is less than 100 or Unit of
measurement is ‘Dozen’
SELECT ItemId, ItemName
FROM Item
WHERE UnitPrice < 100
OR UnitOfMeasurement = ‘Dozen’;

Logical operator: AND, OR, and NOT

44
Logical operators
List all items whose Unit Price is not less than 100 .

SELECT ItemId,ItemName
FROM Item
WHERE NOT UnitPrice < 100;

Logical operator: AND, OR, and NOT

45
Retrieval using BETWEEN
test-expression [NOT] BETWEEN low-expression AND high-expression

List all Item with Unit Price in the range 100 to 200.

SELECT ItemId,ItemName
FROM Item
WHERE UnitPrice >= 100
AND UnitPrice <= 200;

OR

SELECT ItemId,ItemName
FROM Item
WHERE UnitPrice
BETWEEN 100 AND 200;

46
Retrieval using IN
test-expression [NOT] IN (constant1, constant2…………)

List all items which have Unit of measurement as ‘Kilogram’ or ’Dozen’.

SELECT ItemId,ItemName
FROM Item
WHERE UnitOfMeasurement = ‘Kilogram’
OR UnitOfMeasurement = ‘Dozen’;

OR
SELECT ItemId,ItemName
FROM Item
WHERE UnitOfMeasurement
IN(‘Kilogram’,‘Dozen’);

47
 Retrieval using LIKE
Column-name [NOT] LIKE pattern ESCAPE escape-character

List all Customers whose name starts with ‘A’ and has ‘l’ as the second
character

SELECT CustomerId,CustomerName
FROM Customer
WHERE CustomerName LIKE ‘Al%’;

List all Customer whoses name has ‘a’ as the second character.

SELECT CustomerId,CustomerName
FROM Customer
WHERE CustomerName LIKE ‘_a%’;

48
SQL - Retrieval using IS NULL
column-name IS [ NOT ] NULL

List customers whose date of registration is not available.

SELECT CustomerId, CustomerName

FROM Customer
WHERE DateOfReg IS NULL;

List customers whose date of registration is known.

SELECT CustomerId, CustomerName

FROM Customer
WHERE DateOfReg IS NOT NULL;

49
 SQL - Sorting your results (ORDER BY)
ORDER BY ---------------Column name1, Column name2, ……….. -------- ASC -------

Column-number1, Column number2,…… DESC

List the Items of the retail application in the increasing order of their unit
price

SELECT ItemId,ItemName
FROM Item
ORDER BY UnitPrice;

• by default the order is ASCENDING

50
Retrieval using ORDER BY
List the items of the retail application in the decreasing order of their
quantity in hand.

SELECT ItemId,ItemName,QtyOnHand
FROM Item
ORDER BY 3 DESC;

51
Retrieval using ORDER BY
List the items in their decreasing order of quantity on hand and increasing
order of discount.

SELECT ItemId ,ItenName ,QtyOnHand ,Discount

FROM Item
ORDER BY QtyOnHand DESC, Discount;
Or
SELECT ItemId ,ItenName ,QtyOnHand ,Discount
FROM Item
ORDER BY 3 DESC, 4;

Demos

52
Aggregate Functions
SQL

- Aggregate functions
Used when information you want to extract from a table
has to do with the data in the entire table taken as a set.
Aggregate functions are used in place of column names
in the SELECT statement
The aggregate functions in sql are :
SUM( ) , AVG( ) , MAX( ) , MIN( ), COUNT( )

SUM ( [ DISTINCT ] column-name / expression )

AVG ( [ DISTINCT ] column-name / expression )

MIN ( expression)

MAX ( expression )

COUNT ( [ DISTINCT ] column-name )

COUNT ( *)

54
Aggregate function - MIN
 Returns the smallest value that occurs in the specified column
 Column need not be numeric type

List the minimum unit price from item table.

SELECT MIN (UnitPrice)

FROM Item;

55
Aggregate function - MAX
 Returns the largest value that occurs in the specified column
 Column need not be numeric type

 Example:

List the maximum Unit price in the item table.

SELECT MAX (Total_Available_Balance_in_Dollars)

FROM Customer_Transaction;

56
 Aggregate function - AVG
 Returns the average of all the values in the specified column
 Column must be numeric data type
Example:

List the average Unit price of Class A items in the item table.

SELECT AVG (UnitPrice)

FROM Item where Class=‘A’;

57
 Aggregate function - SUM
Adds up the values in the specified column
Column must be numeric data type
Value of the sum must be within the range of that data type
Example:
List the minimum and Sum of all the Unit price of items in the
item table.

SELECT MIN(UnitePrice),SUM (UnitPrice)

FROM Item;

58
Aggregate
Returns the numberfunction - COUNT
of rows in the table
List total number of items in the item table.
SELECT COUNT (*)
FROM Item;

List the total number of customer who have their date of registration
information in the customer table.

SELECT COUNT (DateOfReg)

FROM Costomer;

Count(*) = No of rows, regardless of NULLs

Count(ColumnName) = No. of rows that do not have NULL Value

59
Aggregate function - COUNT
List total number of unique Dates on which Bill has been generated.

SELECT COUNT (DISTINCT BillDate)

FROM Bill;

Count(*) = No of rows, regardless of NULLs

Count(ColumnName) = No. of rows that do not have NULL Value

60
Grouping Data with Group By
 SQL - Using GROUP BY
Related rows can be grouped together by GROUP BY clause
by specifying a column as a grouping column.

GROUP BY is associated with an aggregate function

To retrieve the average unit price of all class of items available

in the item table. Column name can come
along with aggregate
function only if it
appears in Group by
clause

SELECT Class, AVG(UnitPrice)

FROM Item
GROUP BY Class;

62
SQL – Group By
SELECT Class, AVG(UnitPrice)
FROM Item
GROUP BY Class;

Item
ItemId ItemName UnitPrice Class SupplierId
STN001 Pen 30 A S1
BAK003 Bread 20 A S4
GRO001 Poteto 10 A S3
ELC001 Mobile 5000 C S2
ELC004 iPod 600 B S2
STN002 Diary 200 B S2

Class AVG(UnitPrice)
A 20
B 400
C 5000

63
Retrieval using GROUP BY
Example:
Invalid SQL statement
SELECT Class, SupplierId, COUNT(ItemId)
FROM Item
GROUP BY Class;

Valid SQL Statement

SELECT Class, SupplierId, COUNT(ItemId)
FROM Item
GROUP BY Class, SupplierId;

64
SQL – Group By
SELECT Class, SupplierId, COUNT(ItemId)
FROM Item GROUP BY Class, SupplierId;
Item
ItemId ItemName UnitPrice Class SupplierId
STN001 Pen 30 A S1
BAK003 Bread 20 A S4
GRO001 Poteto 10 A S3
ELC001 Mobile 5000 C S2
ELC004 iPod 600 B S2
STN002 Diary 200 B S2

Class SupplierId Count(ItemId)

A S1 1
A S3 1
A S4 1
B S2 2
C S2 1

65
Retrieval using HAVING
List all the classes of item whose average unit price is greater than 500

SELECT Class, AVG( UnitPrice)

FROM Item
GROUP BY Class HAVING AVG(UnitPrice) > 400;

66
Retrieval using HAVING
SELECT Class, AVG( UnitPrice) FROM Item
GROUP BY Class HAVING AVG(UnitPrice) >= 400;

Item
ItemId ItemName UnitPrice Class SupplierId
STN001 Pen 30 A S1
BAK003 Bread 20 A S4
GRO001 Poteto 10 A S3
ELC001 Mobile 5000 C S2
ELC004 iPod 600 B S2
STN002 Diary 200 B S2

After Grouping

Class AVG(UnitPrice) After Having Class AVG(UnitPrice)

A 20 B 400
B 400 C 5000
C 5000

67
Can you identify any error…?
SELECT Class, SUM(UnitPrice)
FROM Item
GROUP BY Class
HAVING SupplierId IN (‘S1’,’S2’);

Ans: The Having condition has to be based on some column that

appears in the group by list

68
Summary of basic DDL and
DML
Create , Alter and Drop are the DDL commands

Update, Insert into, Delete from, are the basic DML

commands that add or remove data from tables

Select statement in its various flavors is used to retrieve

information from the table
Aggregate function
Group by
Having

69