SQL

1
SQL
2

• More than 100 DBMS support SQL
• Used by DBAs and application programmers
• Structured Query Language or SEQUEL
• ORACLE-gt relation database based on SQL
• Standard database language
• SQL
• Reserved Words
• To retrieve data
• insert, update and delete
• SQL and QBE
• Does not contain flow of control commands like
• IF, ELSE, THEN,WHILE,GOTO,DO

3
Writing SQL

• Case insensitive
• exception literal character data
• Data Manipulation
• SELECT
• INSERT
• UPDATE
• DELETE
• All non-numeric data must be enclosed in single
  quotes
• INSERT INTO (property_for_rent(pno,street,area,cit
  y,type,rooms,rent,ono,sno,bno)
• VALUES (PA14,16Holland,Dee,Arbeen,House,
  6,650.00,C640,SA9,B7)

4

• CREATE TABLE, INSERT, SELECT
• e.g
• CREATE TABLE staff(sno VARCHAR(5), lname
  VARCHAR(15), salary DECIMAL(7,2))
• INSERT INTO staff
• VALUES (SG16,Brown,8300)
• SELECT sno,lname,salary
• FROM staff
• where salarygt60000

5
ISO SQL data types

• Data type Declarations
• character CHAR, VARCHAR
• bit BIT, BIT VARYING
• exact numeric NEUMARIC, DECIMAL, INTEGER,
  SMALLINT
• approximate numeric FLOAT, REAL, DOUBLE PRECISION
• datetime DATE, TIME, TIMESTAMP
• interval INTERVAL

6
DDL

• Create Table Branch (
• BranchNo VARCHAR(6) NOT NULL,
• Street VARCHAR(15) NOT NULL,
• City VARCHAR(10) NOT NULL,
• Postcode VARCHAR(8) NOT NULL,
• Primary Key(BranchNo))

7
STAFF

• CREATE TABLE staff (
• staffno VARCHAR(5) NOT NULL,
• fname VARCHAR(15)NOT NULL,
• lname VARCHAR(15)NOT NULL,
• position VARCHAR(10)NOT NULL,
• sex CHAR,
• dob DATE,
• salary DECIMAL(7,2) NOT NULL,
• brano VARCHAR(3) NOT NULL)
• Primary Key (staffno),
• Foreign Key(brano) References branch)
• Describe staff

8
PROPERTY_FOR_RENT

• CREATE TABLE property_for_rent (
• pno VARCHAR(5) NOT NULL,
• street VARCHAR(25) NOT NULL,
• city VARCHAR(15) NOT NULL,
• pcode VARCHAR(8),
• type CHAR NOT NULL,
• rooms SMALLINT NOT NULL,
• rent DECIMAL(6,2) NOT NULL,
• ownerno VARCHAR(5) NOT NULL,
• staffno VARCHAR(5),
• Branch No VARCHAR(3) NOT NULL
• Primary Key (pno)
• Foreign Key (Branch No) References Branch,
• Foreign Key (staffno) References Staff,
• Foreign Key(ownerno) References Owner)

9
INSERT . . . VALUES

• Insert a new record into the staff table
  supplying data for all columns
• INSERT INTO staff
• VALUES (SG16,Alan,Brown, Manager,M,DATE
  1957-05-25,8300,B3)

10
Retrieve all columns, all rows

• List full details of all staff
• SELECT sno,fname,lname,position,sex,dob,salary,bno
  
• FROM staff
• SELECT
• FROM staff

11
RETRIEVE SPECIFIC COLUMNS, ALL ROWS

• List of salaries for all staff with first name,
  last name and Sno.
• SELECT sno,fname,lname,salary
• FROM staff
• Use of DISTINCT
• List of property numbers of all properties viewed
• SELECT pno
• FROM viewing
• SELECT DISTINCT pno
• FROM VIEWING

pno PA14 PG4 PA14 PG36 PG4
pno PA14 PG4 PG36
12
CALCULATED FIELDS

• List of monthly salaries for all staff,the first
  and last names.
• SELECT sno,fname,lname,salary/12
• FROM staff
• SELECT sno,fname,lname,salary/12 AS
  monthly_salary
• FROM staff

13
UPDATE SPECIFIC ROWS
UPDATE ALL ROWS

• Give all staff a 3 raise
• UPDATE staff
• SET salarysalary1.03

Give all managers a 5 raise UPDATE staff SET
salarysalary1.05 WHERE positionManager
UPDATE MULTIPLE COLUMNS
Promote David Ford(SnoSG14) to Manager and
change his salary to 28,000 UPDATE staff SET
positionmanager, salary28000 WHERE snoSG14
14
DELETE SPECIFIC ROWS

• Delete all viewings that relate to property PG4
• DELETE FROM viewing
• WHERE pnoPG4
• DELETE FROM viewing

DELETE ALL ROWS
15
Comparison Search Condition

• List all staff with salary greater than 10,000
• SELECT sno,fname,lname,position,salary
• FROM staff
• WHERE salarygt10000
• Comparison Operators
• , lt, gt, lt, gt, ltgt, !
• Logical Operators
• AND, OR, NOT

16
Compound Comparison Search Condition

• List the addresses of all branch offices in
  London or Glasgow
• SELECT bno,street,area,city,pcode
• FROM branch
• WHERE cityLondon OR cityGlasgow

17
RANGE SEARCH CONDITION

• List all staff with salary between 20,000 and
  30,000
• SELECT sno,fname,lname,position,salary
• FROM staff
• WHERE salary BETWEEN 20000 AND 30000
• SELECT sno,fname,lname,position,salary
• FROM staff
• WHERE salarygt20000 AND salarylt30000

18
Set membership search condition (IN/NOT IN)

• List all Managers and Deputy Managers
• SELECT sno,fname,lname,position
• FROM staff
• WHERE position IN (Manager,Deputy)
• SELECT sno,fname,lname,position
• FROM staff
• WHERE positionManager OR positionDeputy

19
Pattern match search condition (LIKE/ LIKE NOT)

• Find all staff with the string Glasgow in their
  addresses
• SELECT sno,fname,lname,address,salary
• FROM staff
• WHERE address LIKE Glasgow

20
Single Column Ordering

• Produce a list of salaries of all staff in
  descending order of salary
• SELECT sno,fname,lname,salary
• FROM staff
• ORDER BY salary DESC

21
Multiple Column ordering

• Produce an abbreviated list of all properties
  arranged in order of property type.
• SELECT pno,type, rooms,rent
• FROM property_for_rent
• ORDER BY type
• SELECT pno,type, rooms,rent
• FROM property_for_rent
• ORDER BY type, rent DESC

22
Using the SQL Aggregate Functions

• COUNT
• SUM
• AVG
• MIN
• MAX
• Specified Column operations

23

• How many properties cost more than 350 p/month
• SELECT COUNT() AS count
• FROM property_for_rent
• WHERE rentgt350
• How many properties were viewed in May 1998
• SELECT COUNT(DISTINCT pno) AS count
• FROM viewing
• WHERE date BETWEEN 1-May-98 AND 31-May-98

24

• Find the total number of managers and
• sum of their salaries
• SELECT COUNT (sno) AS count , SUM(salary) AS sum
• FROM staff
• WHERE positionmanager
• Find the minimum, maximum and average staff
  salary
• SELECT MIN(salary) AS min, MAX(salary) AS max,
  AVG(salary) AS average
• FROM staff

25
Use of Group By

• Find the number of staff working in each branch
  and the sum of their salaries.
• SELECT bno, COUNT(sno) AS count,SUM(salary) AS
  sum
• FROM staff
• GROUP BY bno
• ORDER BY bno

Use of Having (Filters groups)
For each branch office with more than one member
of staff, find the number of staff working in
each branch and the sum of their salaries SELECT
bno, COUNT(sno) AS count,SUM(salary) AS sum FROM
staff GROUP BY bno HAVING COUNT(sno)gt1 ORDER BY
bno
26
SUBQUERIES

• Using a subquery with equality
• List the staff who work in the branch at 163
  Main St.
• SELECT sno, fname, lname, position
• FROM staff
• WHERE bno
• (SELECT bno
• FROM branch
• WHERE street163 Main St.)

27
Using a subquery with an aggregate function

• List all staff whose salary is greater than the
  average salary, and list by how much.
• SELECT sno,fname,lname,position, salary- (SELECT
  avg(salary) FROM staff)
• AS sal_diff
• FROM staff
• WHERE salarygt
• (SELECT avg(salary)
• FROM staff)
• SELECT sno,fname,lname,position,salary-17000 AS
  sal_diff
• FROM staff
• WHERE salarygt17000

28
Nested queries use of IN

• List the properties that are handled by staff who
  work in the branch at 163 Main St.
• SELECT pno,street,area,city,pcode,type,rooms,rent
• FROM property_for_rent
• WHERE sno IN
• (SELECT sno
• FROM staff
• WHERE bno
• (SELECT bno
• FROM branch
• WHERE street163 Main St))

29
Use of ANY/SOME

• Find staff whose salary is larger than the salary
  of at least one member at branch B3
• SELECT sno,fname,lname,position,salary
• FROM staff
• WHERE salarygt SOME
• (SELECT salary
• FROM staff
• WHERE bnoB3)

30
Use of ALL

• Find staff whose salary is larger than the salary
  of every member of the staff at branch B3
• SELECT sno,fname,lname,position,salary
• FROM staff
• WHERE salarygt ALL
• (SELECT salary
• FROM staff
• WHERE bnoB3)

31
Simple Join

• List the names of all renters who have viewed a
  property along with any comment supplied
• SELECT r.rno,fname,lname,pno,comment
• FROM renter r, viewing v
• WHERE r.rnov.rno

32
Sorting a Join

• For each branch office, list the names of all
  staff who manage properties and the properties
  they manage
• SELECT s.bno,s.sno,fname,lname,pno
• FROM staff s, property_for_rent p
• WHERE s.snop.sno
• SELECT s.bno,s.sno,fname,lname,pno
• FROM staff s, property_for_rent p
• WHERE s.snop.sno
• ORDER BY s.bno,s.sno,pno

33
Three table Join

• For each branch, list the staff who manage
  properties, including the city in which the
  branch is located and the properties they manage
• SELECT b.bno,b.city, s.sno, fname,lname,pno
• FROM branch b, staff s, property_for_rent p
• WHERE b.bno s.bno AND s.snop.sno
• ORDER BY b.bno,s.sno,pno

34
Multiple Grouping Columns

• Find the number of properties handled by each
  staff member
• SELECT s.bno, s.sno, COUNT() AS count
• FROM staff s, property_for_rent p
• WHERE s.sno p.sno
• GROUP BY s.bno, s.sno
• ORDER BY s.bno, s.sno

35
BRANCH1
PROPERTY_FOR_RENT1
bno bcity B3 Glasgow B4 Bristol B2 London
pno pcity PA14 Aberdeen PL94 London PG4 Glasgow
Inner join of these two tables SELECT b,
p FROM branch b, property_for_rent p WHERE
b.bcity p.pcity
bno bcity pno pcity B3 Glasgow PG4 Glasgow B2
London PL94 London
36
Left outer Join

• List the branch offices and properties that are
  in the same cities along with any unmatched
  branches
• SELECT b, p
• FROM branch1 b LEFT JOIN property_for_rent1 p ON
  b.bcityp.pcity

bno bcity pno pcity B3 Glasgow PG4 Glasgow B4
Bristol NULL NULL B2 London PL94 London
37
Right Outer Join

• List the branch offices and properties in the
  same city and any unmatched properties
• SELECT b, p
• FROM branch1 b RIGHT JOIN property_for_rent1 p ON
  b.bcityp.pcity

bno bcity pno pcity NULL NULL PA14 Aberdeen B
3 Glasgow PG4 Glasgow B2 London PL94 London
38
Full Outer Join

• List the branch offices and properties in the
  same city and any unmatched branches or
  properties
• SELECT b, p
• FROM branch1 b FULL JOIN property_for_rent1 p ON
  b.bcityp.pcity

bno bcity pno pcity NULL NULL PA14 Aberdeen B
3 Glasgow PG4 Glasgow B4 Bristol NULL NULL B2
London PL94 London
39
Use of Union

• Construct a list of all areas where there is
  either a property or office.

(SELECT area FROM branch WHERE area IS NOT
NULL) UNION (SELECT area FROM property_for_rent WH
ERE area IS NOT NULL)
(SELECT FROM branch WHERE area IS NOT
NULL) UNION CORRESPONDING BY area (SELECT FROM
property_for_rent WHERE area IS NOT NULL)
OR
40
Use of Intersect

• Construct a list of all cities where there is
  both a branch office and a rental property.

(SELECT city FROM branch) INTERSECT (SELECT
city FROM property_for_rent)
(SELECT FROM branch) INTERSECT CORRESPONDING BY
city (SELECT FROM property_for_rent)
OR
41
REMOVE A TABLE

• DROP TABLE property_for_rent

CREATING AN INDEX
CREATE UNIQUE INDEX sno_ind ON staff
(sno) CREATE UNIQUE INDEX pno_ind ON
property_for_rent (pno)
REMOVING AN INDEX
DROP INDEX rent_id