1 / 29

General Form of the SELECT Statement

General Form of the SELECT Statement. SELECT [DISTINCT | ALL {* | col_expr [AS new_name] [,…]} FROM table_name [,…] [,…] [WHERE condition] [GROUP BY col_list] [HAVING condition] [ORDER BY col_list]

micheal
Télécharger la présentation

General Form of the SELECT Statement

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. General Form of the SELECT Statement SELECT [DISTINCT | ALL {* | col_expr [AS new_name] [,…]} FROM table_name [,…] [,…] [WHERE condition] [GROUP BY col_list] [HAVING condition] [ORDER BY col_list] The order of the clauses of the SELECT statement cannot be changed The order of the col_exp of the SELECT statement is not significant

  2. The Rental Database Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No Staff (Sno, FName, LName, Address, Tel_No, Position, Sex, DOB, Salary, Bno) Prop_For_Rent (Pno, Stree, Area, City, Pcode, Type, Rooms, Rent, Ono, Sno, Bno) Renter (Rno, FName, LName, Address, Tel_No, Pref_Type, Max_Rent) Owner (Ono, FName, LName, address, Tel_No) Viewing (Rno, Pno, Date, Comment)

  3. Foreign KeysReferential Integrity • NO ACTION--Prevents deletion from the parent table if there are references to child table • CASCADE--When parent occurrence is deleted, automatically delete any child occurrences • SET NULL--When parent occurrence deleted, the foreign key values in all of its child occurrences are set to NULL. • SET DEFAULT--When a parent occurrence is deleted, the foreign key values in all of the child occurrences are set to their default (requires setting default value) • NO CHECK--When a parent occurrence is deleted, do nothing to ensure that referential integrity is maintained.

  4. Foreign Key Syntax PRIMARY KEY (...), FOREIGN KEY (…) REFERENCES tablename ON DELETE … ON UPDATE ...

  5. Group By Find the number of staff working in each branch and the total of their salaries SELECT bno, COUNT(Sno) as count, SUM(salary) AS sum FROM staff GROUP BY bno ORDER BY bno;

  6. Nested Queries Find the number of staff working in each branch and the total of their salaries SELECT bno, (SELECT COUNT(sno) AS count FROM staff s WHERE s.bno = b.bno), (SELECT SUM(salary) AS sum FROM staff s WHERE s.bno=b.bno) FROM branch b ORDER BY bno;

  7. SubqueriesCan be used in WHERE or HAVING clauses List the properties that are handled by staff that work at the 163.. branch SELECT pno, street, area, city, pcode, type, rooms, rent FROM Prop_For_Rent WHERE sno IN (SELECT sno FROM staff WHERE bno = (SELECT bno FROM branch WHERE street LIKE ‘163 Main%’));

  8. SubqueriesANY (SOME) and ALL Find staff whose salary is larger than the salary of at least one member of the staff at branch B3. SELECT sno, Fname, Lname, position, salary FROM staff WHERE salary > ANY (SELECT salary FROM staff WHERE bno = ‘B3’); Try the above using ALL instead of ANY.

  9. Branch1 bno bcity B3 Glasgow B4 Bristol B2 London Prop_For_Rent1 pno pcity PA14 Aberdeen PL94 London PG4 Glasgow Joins

  10. Inner Join List the branch offices and properties that are in the same city SELECT b.bno, b.city, p.pno, p.city FROM branch b, Prop_For_Rent p WHERE b.city = p.city;

  11. Left Outer Join List the branch offices and properties that are in the same city along with any unmatched branches SELECT b.bno, b.city, p.pno, p.city FROM branch b LEFT JOIN Prop_For_Rent p ON b.city = p.city;

  12. Right Outer Join List the branch offices and properties that are in the same city along with any unmatched branches SELECT b.bno, b.city, p.pno, p.city FROM branch b RIGHT JOIN Prop_For_Rent p ON b.city = p.city;

  13. Full Outer Join List the branch offices and properties that are in the same city along with any unmatched branches SELECT b.bno, b.city, p.pno, p.city FROM branch b FULL JOIN Prop_For_Rent p ON b.city = p.city;

  14. Default Values CREATE tablename (test_id CHAR(5) NOT NULL, pass_score DECIMAL(6,2) NOT NULL WITH DEFAULT 65, … );

  15. Constraints CREATE tablename (test_id CHAR(5) NOT NULL, gender CHAR(1), pass_score DECIMAL(6,2) NOT NULL WITH DEFAULT 65, … , PRIMARY KEY (keycol), CONSTRAINT gender CHECK (gender IN ( ‘F’, ‘M’));

  16. Union • Construct a list of all areas where there is either a branch office or a rental property • (SELECT area • FROM branch • WHERE area is NOT NULL) • UNION • (SELECT area • FROM Prop_For_Rent • WHERE area is NOT NULL);

  17. INTERSECT (Product) • 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 Prop_For_Rent);

  18. EXCEPT (Difference) • Construct a list of all cities where there is a branch office but no rental property • (SELECT city • FROM branch) • EXCEPT • (SELECT city • FROM Prop_For_Rent);

  19. Triggers A trigger is a set of actions that will be executed when a defined event occurs--namely an INSERT, UPDATE, or DELETE CREATE TRIGGER trigger_name BEFORE | AFTER <trigger event> ON <table_name> [REFERENCING <old_or_new_values> ] [FOR EACH {ROW | STATEMENT}] [WHEN (trigger_condition) ] <trigger-body>

  20. Trigger Example CREATE TRIGGER insert_mailshot_table AFTER INSERT ON prop_for_rent REFERENCING NEW ROW as pfr BEGIN INSERT INTO mailshot (SELECT r.Fname, r.lname, r.address, r.max_rent, pfr.pno, pfr.street, pfr.area, pfr.city, pfr.pcode, pfr.type, pfr.rooms, pfr.rent, FROM renter r WHERE r.bno = pfr.bno AND (r.pref_type = pfr.type AND r.max_rent <= pfr.rent)) END;

  21. Update Example UPDATE staff SET Salary = Salary*1.05 Where Position = ‘Manager’ ;

  22. ALTER Statement • Add a new column to a table • Drop a column from a table • Add a new table constraint • Drop a table constraint • Set a default for a column • Drop a default for a column

  23. ALTER Examples ALTER TABLE inventory ADD location CHAR(4); ALTER TABLE quotations FOREIGN KEY supp(supplrNbr) REFERENCES suppliers; ALTER TABLE quotations DROP FOREIGN KEY supp; ALTER TABLE inventory ADD CONSTRAINT example CHECK (location =‘LOC1’); ALTER TABLE inventory DROP CONSTRAINT example; ALTER TABLE inventory DROP location; ‘generally will not work if data in the column’ ALTER TABLE inventory MODIFY partname char(12); ‘not always supported

  24. Populating a table with Insert and Select DROP TABLE staff_prop_count; CREATE TABLE staff_prop_count (sno char(4) NOT NULL, Fname char(10), Lname char(10), prop_count smallint, PRIMAY KEY (sno)); INSERT INTO staff_prop_count (SELECT s.sno, Fname, Lname, count(*) FROM staff s, prop_for_rent p WHERE s.sno = p.sno GROUP BY s.sno, Fname, Lname); SELECT * FROM Staff_prop_rent;

  25. CASE Expressions SELECT Fname, Lname, (CASE WHEN gender = ‘M’ THEN ‘Male’ WHEN gender = ‘F’ THEN ‘FEMALE’ ELSE ‘Unknown’ END) AS Staff_Gender FROM staff;

  26. DELETE Expressions DELETE FROM table_name [WHERE search_condition]; DELETE FROM viewing WHERE pno=‘PG4’; DELETE FROM viewing;

  27. Creating Views • Views are virtual relations (produced upon request) and can be used to advantage as follows: • Restrict access--create a table without sensitive data • Computed columns • Avoid writing common queries • Make it easier for end users--complex queries written by IS personnel to create a table for easier data for end users

  28. Create View Example CREATE VIEW prop_owner AS (SELECT p.pno, p.street, p.city, o.Fname, o.Lname FROM Prop_for_Rent p, owner o WHERE p.ono = o.ono); SELECT * FROM prop_owner;

  29. Relational Operators

More Related