1 / 106

SQL Chapter 4

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

titus
Télécharger la présentation

SQL Chapter 4

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 4

  2. SQL or SEQUEL (Structured English Query Language) • Based on relational algebra • First called ‘Square’ • Developed in 1970's released in early 1980's • Standardized - SQL-92 (SQL2), SQL-3, SQL:1999 (SQL-99), 2003 (aka SQL: 200n) current standard - SQL:2008 • 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. OLTP • Will be talking about On Line Transaction Processing OLTP for most of this course

  4. SQL • Is SQL useful? • http://www.langpop.com/

  5. 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>]

  6. 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) • Result of a query is usually considered another relation • This is always true, even if it is a single value (1 row, 1 col) • Results may contain duplicate tuples

  7. Queries • Select specified columns for all rows • Select all columns for some of the rows • Select specified columns for some rows • Select all rows and columns of a relation

  8. SQL> select lname 2 from employee; LNAME ---------- Smith Wong Zelaya Wallace Narayan English Jabbar Borg

  9. Select Clause Select <attribute list> • Attribute list can be: • column names • Constants • arithmetic expressions involving columns, etc. • In Oracle, can also be a select statement (but select can only return 1 column and 1 row) • * lists all attributes in a table • To rename an attribute, use the keyword as Select lname as last_name From employee

  10. Queries • To retrieve all the attribute values of the selected tuples, a * is used: Select * From Employee

  11. From clause From <table list> • Table list can be: • one or more table names • a select statement itself

  12. Where clause Where <search conditions> • You can specify more than one condition in the where clause separated by: • and • or

  13. Where clause Where <search conditions> (s in relational algebra) • 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!

  14. Retrieve the ssn of the employee whose name is 'Smith‘ SQL> select ssn 2 from employee 3 where lname='Smith'; SSN ---------- 123456789

  15. Miscellaneous • SQL is not case sensitive Select from employee select FROM EMPLOYEE • Except when comparing character strings • All character strings in SQL are surrounded by single quotes where lname=‘Smith’

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

  17. HW2 • Posted on Blackboard • And on cs457.cs.ua.edu!!

  18. How do you combine tables?

  19. Combining tuples using where clause • To retrieve data that is in more than one table can use: • a cartesian product X Select * From Empnames, Dependent • A join operation |X| • List all info about each department and its manger   Select * From Empnames, Dependent Where ssn=essn

  20. Combining tuples in from clause • A cartesian product combines each tuple in one table, with all the tuples in the second table (and all columns unless specified in select clause) • A join combines a tuple from the first table with tuple(s) in the second table if the specified (join) condition is satisfied (again, all columns included unless specified in select clause) • A join is also referred to as an inner join

  21. Alternative SQL notation for Join Select lname, relationship    From Employee Join Dependent on ssn=essn Where dno=5

  22. Where clause Select * From Employee, Department Where mgrssn=ssn and sex=‘F’ Mgrssn=ssn is a join condition Sex=‘F’ if a select condition

  23. 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 department.* • Use distinct to eliminate duplicate tuples

  24. Sample queries • Write queries to do the following: • List the lname of all female employees with supervisor ssn=333445555 • List ssn and dname of department employees work for • List the ssn, lname of all female employees working in the ‘Research’ department

  25. 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. • How many join conditions in the above query? • How many selection conditions?

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

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

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

  29. In predicate • The in predicate tests set membership for a single value at a time. • In predicate: expr [not] in (select | val {, val}) Select <attribute list> From <table list> Where expr in (select | val {, val})

  30. In predicate • Select employees who work in departments located in Houston • The outer query selects an Employee tuple if its dno value is in the result of the nested query.

  31. Quantified predicate • Quantified predicate compares a single value with a set according to the predicate. • Quantified predicate: expr § [all | any] (select) Select <attribute list> From <table list> Where expr § [all | any] (select) § is < > = <> <= >=

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

  33. Exists predicate • The exists predicate tests if a set of rows is non-empty • Exists predicate: [not] exists (select) Select <attribute list> From <table list> Where exists (select)

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

  35. Exists predicate • Retrieve all the names of employees who have no dependents. • 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.

  36. 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?

  37. Correlated queries List the name of employees who have dependents with the same birthday as they do. Can this be written as correlated nested and uncorrelated nested?

  38. Single block queries • An Expression written using = or IN may almost always be expressed as a single block query. • Find example where this is not true in your textbook

  39. Outer Join •  Outer Join - extension of join and union • In a regular join, tuples in R1 or R2 that do not have matching tuples in the other relation do not appear in the result. • Some queries require all tuples in R1 (or R2 or both) to appear in the result • When no matching tuples are found, nulls are placed for the missing attributes.

  40. Outer Join • You can use the keywords left, right, full (works in Oracle) • The following is a left outer join Select lname, dname        From Employee Left Outer Join Department on ssn=mgrssn • The keyword Outer is optional

  41. LNAME DNAME ---------- --------------- Wong Research Wallace Administration Borg Headquarters Jabbar English Zelaya Narayan Smith

  42. Outer Join • You can also use a + to indicate an outer join • The following example indicates a left outer join in Oracle         Select lname, dname        From Employee, Department        Where ssn=mgrssn(+)

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

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

  45. More SQL • Anything missing?

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

  47. 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'

  48. 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 • To count the number of distinct salaries. Select COUNT(distinct salary) From Employee • List lname, salary for employees with salaries > average salary

  49. What does this query do? SELECT dno, lname, salary FROM employee x WHERE salary > (SELECT AVG(salary) FROM employee WHERE x.dno=dno); What if we get rid of the ‘x’ in x.dno?

  50. Aggregates • Additional aggregates have been added to RDBMS • Read the Oracle documentation to see what has been added

More Related