Transcript Document 7431788

SQL
• More than 100 DBMS support SQL
–
–
–
–
Used by DBAs and application programmers
Structured Query Language or SEQUEL
ORACLE-> 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
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,city,type,rooms,rent,ono,sno,bno)
VALUES (‘PA14’,’16Holland’,’Dee’,’Arbeen’,’House’,6,650.00,’C640’,’SA9’,’B7’);
• 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 salary>60000;
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
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));
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
PROPERTY_FOR_RENT
CREATE TABLE property_for_rent (
pno
VARCHAR(5)
NOT NULL,
streetVARCHAR(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);
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-0525’,8300,’B3’);
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;
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;
pno
PA14
• Use of DISTINCT
List of property numbers of all properties viewed
SELECT pno
FROM viewing;
PG4
PA14
PG36
PG4
pno
PA14
SELECT DISTINCT pno
FROM VIEWING;
PG4
PG36
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;
UPDATE ALL ROWS
Give all staff a 3% raise
UPDATE staff
SET salary=salary*1.03;
UPDATE SPECIFIC ROWS
Give all managers a 5% raise
UPDATE staff
SET salary=salary*1.05
WHERE position=‘Manager’;
UPDATE MULTIPLE COLUMNS
Promote David Ford(Sno=‘SG14’) to Manager and change his salary to $28,000
UPDATE staff
SET position=‘manager’, salary=28000
WHERE sno=‘SG14’;
DELETE SPECIFIC ROWS
Delete all viewings that relate to property PG4
DELETE FROM viewing
WHERE pno=‘PG4’;
DELETE ALL ROWS
DELETE FROM viewing;
Comparison Search Condition
List all staff with salary greater than $10,000
SELECT sno,fname,lname,position,salary
FROM staff
WHERE salary>10000;
Comparison Operators
=, <, >, <=, >=, <>, !=
Logical Operators
AND, OR, NOT
Compound Comparison Search Condition
List the addresses of all branch offices in London or Glasgow
SELECT bno,street,area,city,pcode
FROM branch
WHERE city=‘London’ OR city=‘Glasgow’;
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 salary>=20000 AND salary<=30000;
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 position=‘Manager’ OR position=‘Deputy’;
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%’;
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;
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;
Using the SQL Aggregate Functions
• COUNT
• SUM
• AVG
• MIN
• MAX
Specified Column operations
How many properties cost more than $350 p/month
SELECT COUNT(*) AS count
FROM property_for_rent
WHERE rent>350;
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’;
Find the total number of managers and
sum of their salaries
SELECT COUNT (sno) AS count , SUM(salary) AS sum
FROM staff
WHERE position=‘manager’;
Find the minimum, maximum and average staff salary
SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS average
FROM staff;
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)>1
ORDER BY bno;
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 street=‘163 Main St.’);
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 salary>
(SELECT avg(salary)
FROM staff);
SELECT sno,fname,lname,position,salary-17000 AS sal_diff
FROM staff
WHERE salary>17000
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 street=‘163 Main St’));
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 salary> SOME
(SELECT salary
FROM staff
WHERE bno=‘B3’);
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 salary> ALL
(SELECT salary
FROM staff
WHERE bno=‘B3’);
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.rno=v.rno;
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.sno=p.sno;
SELECT s.bno,s.sno,fname,lname,pno
FROM staff s, property_for_rent p
WHERE s.sno=p.sno
ORDER BY s.bno,s.sno,pno;
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.sno=p.sno
ORDER BY b.bno,s.sno,pno;
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;
BRANCH1
PROPERTY_FOR_RENT1
bno
bcity
pno
pcity
B3
Glasgow
PA14 Aberdeen
B4
Bristol
PL94 London
B2
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
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.bcity=p.pcity;
bno
bcity
pno
pcity
B3
Glasgow
PG4
Glasgow
B4
Bristol
NULL
NULL
B2
London
PL94
London
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.bcity=p.pcity;
bno
bcity
pno
pcity
NULL NULL
PA14
Aberdeen
B3
Glasgow
PG4
Glasgow
B2
London
PL94
London
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.bcity=p.pcity;
bno
bcity
pno
pcity
NULL NULL
PA14
Aberdeen
B3
Glasgow
PG4
Glasgow
B4
Bristol
NULL
NULL
B2
London
PL94
London
Use of Union
Construct a list of all areas where there is either a property or office.
(SELECT area
OR
(SELECT *
FROM branch
FROM branch
WHERE area IS NOT NULL)
WHERE area IS NOT NULL)
UNION
UNION CORRESPONDING BY area
(SELECT area
(SELECT *
FROM property_for_rent
FROM property_for_rent
WHERE area IS NOT NULL);
WHERE area IS NOT NULL);
Use of Intersect
Construct a list of all cities where there is both a branch office and
a rental property.
(SELECT city
OR (SELECT *
FROM branch)
FROM branch)
INTERSECT
INTERSECT CORRESPONDING BY city
(SELECT city
(SELECT *
FROM property_for_rent);
FROM property_for_rent);
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;