1 / 91

SQL Chapter 8

SQL Chapter 8. SQL or SEQUEL - (Structured English Query Language). Based on relational algebra Developed in 1970's released in early 1980's Standardized - SQL-92 (SQL2), SQL-3 current standard - SQL:1999 (SQL-99), future standard - SQL:2003 (aka SQL:200n)

geordi
Download Presentation

SQL Chapter 8

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 Chapter 8

  2. SQL or SEQUEL - (Structured English Query Language) • Based on relational algebra • Developed in 1970's released in early 1980's • Standardized - SQL-92 (SQL2), SQL-3 current standard - SQL:1999 (SQL-99), future standard - SQL:2003 (aka SQL:200n) • High-level DB language used in ORACLE, etc. created at IBM with System R •  SQL provides DDL and DML   • DDL - create table, alter table, drop table • DML - Queries in SQL

  3. SQL • Relation not a set of tuples - a multiset or bag of tuples • Therefore, 2 or more tuples may be identical • Basic building block of SQL is the Select Statement SELECT <attribute list> FROM <table list > WHERE <search conditions>

  4. Select Statement • Select - chooses columns (project operation p in relational algebra) • From - combines tables if > 1 table (join operation |X| in relational algebra) • Where - chooses rows (select operation s in relational algebra) Exs. • Result of a query is a relation • Results may contain duplicate tuples • Can rename a column name using: as • E.g. select lname as last_name from employee

  5. Queries • Retrieve the birthdate and address of the employee whose name is 'Smith‘ Select bdate, address From Employee Where lname = 'Smith' • To retrieve all the attribute values of the selected tuples, a * is used Select * From Employee Where lname = 'Smith'

  6. Queries • To select all rows and columns of a relation  Select * From Employee • To select some of the rows Select * From Employee Where dno = 5 • To select specified columns for all rows Select SSN From Employee

  7. Select Clause Select <attribute list> • Attribute list can be column names, constants, arithmetic expressions involving columns, etc. • Use an asterisk to list all attributes in a table • To rename an attribute, use the keyword as Select lname as last_name From employee • In Oracle, can also be a select statement

  8. From clause From <table list> • Table list can be: • one or more table names • a select statement itself • How is data combined between more than 1 table?

  9. Combining tuples in where clause • To retrieve data that is in more than one table can use: • a cartesian product X Fig7.12 Select SSN, dname From Employee, Department • A join operation |X| Fig7.13 • Retrieve the lname, address and department name of each employee  Select SSN, dname From Employee, Department Where dnumber = dno

  10. Combining tuples in where clause • A cartesian product combines each tuple in one table, with all the tuples in the second table • A join combines a tuple from the first table with tuple(s) in the second table if the specified (join) condition is satisfied

  11. Relational Algebra • Select those who work for the research department? • Similar to select-project-join sequence of relational algebra operations dname = 'Research' is a selection condition dnumber = dno is a join condition

  12. Join Conditions • For every project located in 'Stafford' list the project number, the controlling department number and department manager's last name, address and birthdate.       Select pnumber, dnum, lname, bdate, address From Project, Department, Employee Where dnum = dnumber and mgrssn = ssn and plocation = 'Stafford' • There are 2 join conditions in the above query

  13. Additional characteristics • In SQL we can use the same name for 2 or more attributes in different relations. Must qualify the attributes names: employee.lname • Use distinct to eliminate duplicate tuples: Select distinct salary From Employee

  14. Additional characteristics • Aliases are used to rename relations: Select E.lname, D. dname From Employee E, Department D Where E.dno = D.dnumber • List all employee names and their supervisor names Select E.fname, E.lname, S.fname, S.lname From Employee E, Employee S Where E.superssn = S.ssn

  15. Where clause Where <search conditions> • Search conditions can be: • Comparison predicate: expr § expr2 where § is <, >, <=, etc. in, between, like, etc. expr is constant, col, qual.col, aexpr op aexpr, fn(aexpr), set_fn(aexpr) expr2 is expr | select statement • Note: expr can be a select statement!

  16. Expr as a select statement • You need to be careful using this. Result must be a single value Select lname, dno From employee Where dno = (select dnumber from department where dname = ‘Research’)

  17. Using Predicates • Using where condition - you can nest queries: • In predicate: expr [not] in (select | val {, val}) • Quantified predicate: expr § [all | any] (select) • Exists predicate: [not] exists (select) Other where conditions: • Between predicate: expr [not] between expr2 and expr3 • Like predicate: col [not] like 'pattern' • Null predicate: col is [not] null

  18. Predicates • Predicates evaluate to either T or F. Many of the previous queries can be specified in an alternative form using nesting.

  19. In predicate • The in predicate tests set membership for a single value at a time. • In predicate: expr [not] in (select | val {, val}) Select * From Agents Where city in ('Atlanta', 'Dallas')

  20. In predicate Rewrite to select employees in research dept. Select * From Employee Where dno in (Select dnumber From Department where dname = 'Research') • The outer query selects an Employee tuple if its dno value is in the result of the nested query.

  21. Quantified predicate • Quantified predicate compares a single value with a set according to the predicate. • Quantified predicate: expr § [all | any] (select) Select * From Employee Where dno = any (Select Dnumber From Department Where dname = 'Research')

  22. Quantified predicate What does the following query? Select * From Employee Where salary > all (Select salary From Employee Where sex = 'F') • = any equivalent to in • not in equivalent to <> all

  23. Exists predicate • The exists predicate tests if a set of rows is non-empty • Exists predicate: [not] exists (select) Select * From Employee Where exists (Select * From Department Where dname = 'Research' and dno = dnumber)

  24. Exists predicate • Exists is used to check whether the result of the inner query is empty or not. If the result is NOT empty, then the tuple in the outer query is in the result. • Exists is used to implement difference (‘not in’ used) and intersection.

  25. Exists predicate • Retrieve all the names of employees who have no dependents. • Select fname, lname • From Employee • Where not exists (Select * From Dependent Where ssn = essn) • All of the Dependent tuples related to one Employee tuple are retrieved. If none exist (not exists is true and the inner query is empty) the Employee tuple is in the result.

  26. Nested queries • In general we can have several levels of nested queries. • A reference to an unqualified attribute refers to the relation declared in the inner most nested query. • An outer query cannot reference an attribute in an inner query (like scope rules in higher level languages). • A reference to an attribute must be qualified if its name is ambiguous. 

  27. Will this work? Suppose you want the ssn and dname: Select ssn, dname from employee where dno in (select dnumber from department)

  28. Correlated Nested Queries • Correlated Nested Queries: • If a condition in the where-clause of a nested query references an attribute of a relation declared in an outer query, the two queries are said to be correlated. • The result of a correlated nested query is different for each tuple (or combination of tuples) of the relation in the outer query. • Which takes longer to execute? a correlated nested query or a non-correlated nested query?

  29. Correlated queries List the name of employees who have dependents with the same birthday as they do. Select E.fname, E.lname From Employee E Where E.ssn in (Select essn From Dependent D Where essn = E.ssn and E.bdate = D.bdate) Can this be written as uncorrelated nested?

  30. Single block queries • An Expression written using = or IN may almost always be expressed as a single block query: Select E.fname, E.lname From Employee E, Dependent D Where E.ssn = E.essn and E.bdate = D.bdate

  31. Select statement • Multiple levels of select nesting are allowed. • Like predicate, Between predicate and Null predicate • Can apply arithmetic operations to numeric values in SQL Select fname, lname, 1.1*salary From Employee Select discount_rate*price From products

  32. Aggregate functions •  Aggregate Functions (set functions, aggregates): • Include COUNT, SUM, MAX, MIN and AVG aggr (col) • Find the maximum salary, the minimum salary and the average salary among all employees. Select MAX(salary), MIN(salary), AVG(salary) From Employee

  33. Aggregates • Retrieve the total number of employees in the company Select COUNT(*) From Employee • Retrieve the number of employees in the research department. Select COUNT(*) From Employee, Department Where dno=dnumber and dname='Research'

  34. Aggregates • Note that: Select COUNT(*) from Employee Will give you the same result as: Select COUNT(salary) from Employee Unless there are nulls - not counted for salary • To count the number of distinct salaries. Select COUNT(distinct salary) From Employee

  35. What does this query do? SELECT dno, lname, salary FROM employee x WHERE salary > (SELECT AVG(salary) FROM employee WHERE x.dno = dno) What would happen if you delete the qualification “x.”?

  36. Grouping • We can apply the aggregate functions to subgroups of tuples in a relation. • Each subgroup of tuples consists of the set of tuples that have the same value for the grouping attribute(s). • The aggregate is applied to each subgroup independently. • SQL has a group-by clause for specifying the grouping attributes.

  37. Grouping • For each department, retrieve the department number, the total number of employees and their average salary. Select dno, COUNT(*), AVG(salary) From Employee Group By dno • The tuples are divided into groups with the same dno. • COUNT and AVG are then applied to each group.

  38. Grouping • For each project, retrieve the project number, project name and the number of employees who work on that project. Select pnumber, pname, COUNT(*) From Project, Works_on Where pnumber=pno Group By pnumber, pname • In the above query, the joining of the two relations is done first, then the grouping and aggregates are applied.

  39. Oracle group by • Expressions in the GROUP BY clause can contain any columns of the tables or views in the FROM clause, regardless of whether the columns appear in the SELECT clause. • However, only grouping attribute(s) and aggregate functions can be listed in the SELECT clause.

  40. Having Clause • Sometimes we want to retrieve those tuples with certain values for the aggregates. • The having clause is used to specify a selection condition on a group (rather than individual tuples). • If a having is specified, you must specify a group by.

  41. Having • For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project. Select pnumber, pname, COUNT(*) From Project, Works_on Where pnumber =pno Group By pnumber, pname Having COUNT(*) > 2

  42. To Access Oracle in EE116 • Log on to the machine, using your STUDENTS account • Login:  bama account name • Password:  your CWID • Go to Start, Programs then choose Oracle-OraHome9, Application Development and SQL Plus.  • In SQL Plus • User Name: your CWID • Password: first letter of your last name in lower case followed by your CWID • Host String: students

  43. Using SQL Plus • Type in SQL commands interactively • Each SQL statement must have a semicolon ; at the end • Can also run commands from a file by specifying @filename • Results of the queries are displayed on the screen • To write the screen output to a file, select File, Spool and specify a filename • Use Spool off to stop redirection to the file

  44. Oracle: changes permanent • inserting tuples into a table using SQL Plus, you must: • type in commit; • Or • specify quit; • To make changes permanent

  45. Oracle SQL • Oracle9i SQL reference can be found here

  46. Subselect formal definition • Select called Subselect Select expr {, expr} From tablename [alias] {, tablename [alias]} [Where search_condition] [Group By col {, col}] [Having search_condition]

  47. Order By • To sort the tuples in a query result based on the values of some attribute: Order by col_list • Default is ascending order (asc), but can specify descending order (desc)

  48. Order by • Retrieve a list of the employees each project (s)he works on, ordered by the employee's department, and within each department order the employees alphabetically by last name. Select dname, lname, fname, pname From   Department, Employee, Works_on, Project Where dnumber=dno and ssn=essn and pno=pnumber Order By  dname, lname

  49. Set operations • Also available are Set Operations, such as: • UNION, MINUS and INTERSECT. Subselect {Union [all] subselect}    [Order By col [asc | desc] {, col [asc | desc]}]

  50. Set Operations • The resulting relations are sets of tuples; duplicate tuples are eliminated. • Operations apply only to union compatible relations. The two relations must have the same number of attributes and the attributes must be of the same type.

More Related