1 / 41

SQL

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

dyllis
Download Presentation

SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL

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

  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,city,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 salary>60000;

  5. ISO SQL data types Data typeDeclarations characterCHAR, 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 pno PA14 PG4 PA14 PG36 PG4 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 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 ALL ROWS UPDATE SPECIFIC ROWS Give all staff a 3% raise UPDATE staff SET salary=salary*1.03; 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’;

  14. DELETE SPECIFIC ROWS Delete all viewings that relate to property PG4 DELETE FROM viewing WHERE pno=‘PG4’; 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 salary>10000; Comparison Operators =, <, >, <=, >=, <>, != 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 city=‘London’ OR city=‘Glasgow’;

  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 salary>=20000 AND salary<=30000;

  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 position=‘Manager’ OR position=‘Deputy’;

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

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

  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)>1 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 street=‘163 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 salary> (SELECT avg(salary) FROM staff); SELECT sno,fname,lname,position,salary-17000 AS sal_diff FROM staff WHERE salary>17000

  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 street=‘163 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 salary> SOME (SELECT salary FROM staff WHERE bno=‘B3’);

  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 salary> ALL (SELECT salary FROM staff WHERE bno=‘B3’);

  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.rno=v.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.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;

  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.sno=p.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.bcity=p.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.bcity=p.pcity; bno bcity pno pcity NULL NULL PA14 Aberdeen B3 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.bcity=p.pcity; bno bcity pno pcity NULL NULL PA14 Aberdeen B3 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 WHERE area IS NOT NULL); OR (SELECT * FROM branch WHERE area IS NOT NULL) UNION CORRESPONDING BY area (SELECT * FROM property_for_rent WHERE area IS NOT NULL);

  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;

More Related