1 / 33

SQL

SQL. Todd Bacastow IST 210: Organization of Data. SELECT Statement. SELECT [ DISTINCT | ALL ] {* | [ column_expression [ AS new_name ]] [,...] } FROM table_name [alias] [, ...] [ WHERE condition] [ GROUP BY column_list ] [ HAVING condition ] [ ORDER BY column_list ]

duscha
Télécharger la présentation

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 Todd Bacastow IST 210: Organization of Data

  2. SELECT Statement SELECT [DISTINCT | ALL] {* | [column_expression [ASnew_name]] [,...] } FROMtable_name [alias] [, ...] [WHERE condition] [GROUP BYcolumn_list] [HAVINGcondition] [ORDER BYcolumn_list] • Reserved Keywords and User-defined words • Case insensitive, free format • Proper formatting for readability. Common norms: • Each clause should begin on a new line. • Start of a clause should line up with start of other clauses. • If clause has several parts, should each appear on a separate line and be indented under start of clause.

  3. SELECT Statement FROM Specifies table(s) to be used. WHERE Filters rows. GROUP BY Forms groups of rows with same column value. HAVING Filters groups subject to some condition. SELECT Specifies which columns are to appear in output. ORDER BY Specifies the order of the output.

  4. DreamHome Example DreamHome is a company which specializes in the management of properties for rent on behalf of the owners. Services: Advertising properties Interviewing renters Organizing visits to properties Negotiating lease agreements.

  5. DreamHome Example Database Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) Staff (Sno, FName, LName, Address, Tel_No, Position, Sex, DOB, Salary, NIN, Bno) Property_for_rent (Pno, Street, Area, City, Pcode, Type, Rooms, Rent, Ono, Sno, Bno) Renter (Rno, Fname, Lname, Adress, Tel_No, Pref_Type, Max_Rent, Bno) Owner (Ono, Fname, Lname, Address, Tel_No) Viewing (Rno, Pno, Date, Comment)

  6. All Columns, All Rows • Example: List full details of all staff. SELECT sno, fname, lname, address, tel_no, position, sex, dob, salary, nin, bno FROM staff; • Can use * as an abbreviation for 'all columns': SELECT * FROM staff;

  7. Specific Columns, All Rows • Example Produce a list of salaries for all staff, showing only the staff number, Sno, the first and last names, and the salary details. SELECT sno, fname, lname, salary FROM staff;

  8. SELECT pno FROM viewing; SELECT DISTINCT pno FROM viewing; Use of DISTINCT List the property numbers of all properties that have been viewed.

  9. Calculated Fields • Query Produce a list of monthly salaries for all staff, showing the staff number, the first and last names, and the salary details. SELECT sno, fname, lname, salary/12 FROM staff; Monthly_salary To name column, use AS clause: • SELECT sno, fname, lname, salary/12 AS monthly_salary • FROM staff;

  10. Comparison Search Condition Query:List all staff with a salary greater than 10,000. SELECT sno, fname, lname, position, salary FROM staff WHERE salary > 10000;

  11. Compound Comparison Search Condition Query: 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';

  12. Range Search Condition - BETWEEN Query: List all staff with a salary between 20,000 and 30,000. SELECT sno, fname, lname, position, salary FROM staff WHERE salary BETWEEN 20000 AND 30000;

  13. Range Search Condition (Cont…) • BETWEEN test includes the endpoints of range. Equivalent to SELECT sno, fname, lname, position, salary FROM staff WHERE salary>=20000 AND salary <= 30000; • BETWEEN does not add much to SQL's expressive power • Also a negated version NOT BETWEEN.

  14. Set Membership - IN / NOT IN • Query: List all Managers and Deputy Managers. SELECT sno, fname, lname, position FROM staff WHERE position IN ('Manager', 'Deputy');

  15. Pattern Matching - LIKE • SQL has two special pattern matching symbols: • %: sequence of zero or more characters; • _ (underscore): any single character. • LIKE '%Glasgow%' means a sequence of characters of any length containing 'Glasgow'. Query: Find all staff with the string 'Glasgow' in their address. SELECT sno, fname, lname, address, salary FROM staff WHERE address LIKE '%Glasgow%';

  16. NULL Search Condition • NULL value can not be checked by comparison operators • Have to test for null explicitly using special keyword IS NULL • IS NOT NULL can test for non-null values. • Example query: List details of all viewings on property PG4 where a comment has not been supplied. SELECT rno, date FROM viewing WHERE pno = 'PG4' AND comment IS NULL;

  17. ORDER BY • Example Query: List salaries for all staff, arranged in descending order of salary. SELECT sno, fname, lname, salary FROM staff ORDER BY salary DESC; • When no (ASC/DESC) is indicated, default order is ascending (ASC). • Can sort on multiple columns. SELECT pno, type, rooms, rent FROM property_for_rent ORDER BY type, rent DESC;

  18. SELECT Statement - Aggregates • Five aggregate functions: • COUNT returns number of values in a specified column. • SUM returns sum of values in a specified column. • AVG returns average of values in a specified column. • MIN returns smallest value in a specified column. • MAX returns largest value in a specified column.

  19. Aggregates - Examples • Example 1: How many different properties viewed in May ‘98? SELECT COUNT (DISTINCT pno) AS count FROM viewing WHERE date BETWEEN DATE'1998-05-01’ AND DATE'1998-05-31'; • Example 2: Find number of Managers and sum of their salaries. SELECT COUNT(sno) AS count, SUM(salary) AS sum FROM staff WHERE position = 'Manager'; • Example 3: Find minimum, maximum, and average staff salary. SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg FROM staff;

  20. Aggregates - summary • Each operates on a single column of a table and return single value. • COUNT, MIN, and MAX apply to numeric and non-numeric fields, but SUM and AVG may be used on numeric fields only. • Apart from COUNT(*), each function eliminates nulls first and operates only on remaining non-null values. • COUNT(*) counts all rows of a table, regardless of whether nulls or duplicate values occur. • Can use DISTINCT before column name to eliminate duplicates. • Aggregate functions can be used only in SELECT list and in HAVING clause.

  21. GROUP BY and HAVING • Use GROUP BY clause to get sub-totals. • SELECT and GROUP BY closely integrated: each item in SELECT list must be single-valued per group, and SELECT clause may only contain: • Column names. • Aggregate functions. • Constants. • An expression involving combinations of the above. • If WHERE is used with GROUP BY, WHERE is applied first, then groups are formed from remaining rows satisfying predicate. • Considers two nulls to be equal for purposes of GROUP BY.

  22. Use of GROUP BY Example: Find number of staff in each branch and their total salaries. SELECT bno, COUNT(sno) AS count, SUM(salary) AS sum FROM staff GROUP BY bno ORDER BY bno; 59

  23. Restricted Grouping - HAVING • HAVING clause is designed for use with GROUP BY clause to restrict groups that appear in final result table. • HAVING filters groups. • Column names in HAVING clause must also appear in the GROUP BY list or be contained within an aggregate function. • Example: For each branch with more than 1 member of staff, find number of staff in each branch and 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;

  24. Subqueries • A subselect can be used in WHERE clauses of an outer SELECT, where it is called a subquery or nested query. Example: List staff who work in branch at '163 Main St'. SELECT sno, fname, lname, position FROM staff WHERE bno = (SELECT bno FROM branch WHERE street = '163 Main St');

  25. Subquery with Aggregate • Example: List all staff whose salary is greater than the average salary. SELECT sno, fname, lname, position, salary FROM staff WHERE salary > (SELECT avg(salary) FROM staff); • Cannot write 'WHERE salary > avg(salary)'. • Instead, use subquery to find average salary (17000), and then use outer SELECT to find those staff with salary greater than 17000 • Subquery SELECT list must consist of a single column name or expression, except for subqueries that use EXISTS.

  26. ANY/SOME and ALL • If subquery preceded by ALL, condition will only be true if it is satisfied by all values produced by subquery. • If subquery preceded by ANY, condition will be true if it is satisfied by any values produced by subquery. • If subquery is empty, ALL returns true, ANY returns false.

  27. Example of ANY and ALL • Example1: Find staff whose salary is larger than salary of at least 1 member of staff at branch B3. SELECT sno, fname, lname, position, salary FROM staff WHERE salary >ANY (SELECT salary FROM staff WHERE bno = 'B3'); • Example 2: Find staff whose salary is larger than salary of every member of staff at branch B3. SELECT sno, fname, lname, position, salary FROM staff WHERE salary > ALL (SELECT salary FROM staff WHERE bno = 'B3');

  28. Multi-Table Queries • If result columns come from more than one table must use a join. • To perform join, include more than one table in FROM clause. • Use comma as separator and typically include WHERE clause to specify join column(s).

  29. Sorting a join • Consider: For each branch, list names of staff who manage properties. 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;

  30. Three Table Join • Consider: For each branch, list staff who manage properties, including city in which branch is located and 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;

  31. Outer Joins • With a join, if one row of a table is unmatched, row is omitted from result table. • The outer join operations retain rows that do not satisfy the join condition. • Consider following two simplified tables: BRANCH1 PROPERTY_FOR_RENT1 bno citypno pcity B3 Glasgow PA14 Aberdeen B4 Bristol PL94 London B2 London PG4 Glasgow

  32. Outer Joins • The (inner) join of these two tables: SELECT b.*, p.* FROM branch1 b, property_for_rent1 p WHERE b.bcity = p.pcity; • Left Outer Join SELECT b.*, p.* FROM branch1 b LEFT JOIN property_for_rent1 p ON b.bcity = p.pcity; • Right Outer Join SELECT b.*, p.* FROM branch1 b RIGHT JOIN property_for_rent1 p ON b.bcity = p.pcity;

  33. Full Outer Join • Includes those rows that are unmatched in both tables. • Unmatched columns are filled with NULLs. Consider: List branches and properties in 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;

More Related