SQL More than 100 DBMS

SQL

• More than 100 DBMS support SQL – – Used by DBAs and application programmers Structured Query Language or SEQUEL ORACLE-> relation databased 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 (‘PA 14’, ’ 16 Holland’, ’Dee’, ’Arbeen’, ’House’, 6, 650. 00, ’C 640’, ’SA 9’, ’B 7’);

• CREATE TABLE, INSERT, SELECT – e. g: CREATE TABLE staff(sno VARCHAR(5), lname VARCHAR(15), salary DECIMAL(7, 2)); INSERT INTO staff VALUES (‘SG 16’, ’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, TIMESTAMP interval INTERVAL

DDL • Create Table Branch ( Branch. No VARCHAR(6) NOT NULL, Street VARCHAR(15) NOT NULL, City VARCHAR(10) NOT NULL, Postcode VARCHAR(8) NOT NULL, Primary Key(Branch. No));

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, 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);

INSERT. . . VALUES Insert a new record into the staff table supplying data for all columns INSERT INTO staff VALUES (‘SG 16’, ’Alan’, ’Brown’, ’Manager’, ’M’, DATE ‘’ 1957 -0525’, 8300, ’B 3’);

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 PA 14 • Use of DISTINCT List of property numbers of all properties viewed SELECT pno FROM viewing; PG 4 PA 14 PG 36 PG 4 pno PA 14 SELECT DISTINCT pno FROM VIEWING; PG 4 PG 36

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=‘SG 14’) to Manager and change his salary to $28, 000 UPDATE staff SET position=‘manager’, salary=28000 WHERE sno=‘SG 14’;

DELETE SPECIFIC ROWS Delete all viewings that relate to property PG 4 DELETE FROM viewing WHERE pno=‘PG 4’; 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 B 3 SELECT sno, fname, lname, position, salary FROM staff WHERE salary> SOME (SELECT salary FROM staff WHERE bno=‘B 3’);

Use of ALL Find staff whose salary is larger than the salary of every member of the staff at branch B 3 SELECT sno, fname, lname, position, salary FROM staff WHERE salary> ALL (SELECT salary FROM staff WHERE bno=‘B 3’);

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;

BRANCH 1 bno bcity PROPERTY_FOR_RENT 1 pno pcity B 3 Glasgow PA 14 Aberdeen B 4 Bristol PL 94 London B 2 London PG 4 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 B 3 Glasgow PG 4 Glasgow B 2 London PL 94 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 branch 1 b LEFT JOIN property_for_rent 1 p ON b. bcity=p. pcity; bno bcity pno pcity B 3 Glasgow PG 4 Glasgow B 4 Bristol NULL B 2 London PL 94 London

Right Outer Join List the branch offices and properties in the same city and any unmatched properties SELECT b*, p* FROM branch 1 b RIGHT JOIN property_for_rent 1 p ON b. bcity=p. pcity; bno bcity pno pcity NULL PA 14 Aberdeen B 3 Glasgow PG 4 Glasgow B 2 London PL 94 London

Full Outer Join List the branch offices and properties in the same city and any unmatched branches or properties SELECT b*, p* FROM branch 1 b FULL JOIN property_for_rent 1 p ON b. bcity=p. pcity; bno bcity pno pcity NULL PA 14 Aberdeen B 3 Glasgow PG 4 Glasgow B 4 Bristol NULL B 2 London PL 94 London

Use of Union Construct a list of all areas where there is either a property or office. (SELECT area OR (SELECT * FROM branch WHERE area IS NOT NULL) UNION CORRESPONDING BY area (SELECT * FROM property_for_rent 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) INTERSECT CORRESPONDING BY city (SELECT * 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;