OLTP

1. OLTP • Will be talking about On Line Transaction Processing OLTP for most of this course • Operational databases • As opposed to OLAP On Line Analytical Processing • Decision support

2. SQL Chapters 6, 7

3. SQL or SEQUEL (Structured English Query Language) • Based on relational algebra and relational calculus • 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), SQL:2008 SQL:2011 • 2011 includes better support for temporal databases SQL: 2016 • access JSON • https://en.wikipedia.org/wiki/SQL:2016

4. SQL • High-level DB language used in ORACLE, etc. created at IBM with System R •  SQL provides DDL and DML   • DDL - create table, drop table, alter table • DML - Queries in SQL

5. SQL • Is SQL useful? • Languages to learn

6. Give examples of queries in English • What basic keywords would you use for a query language for a DB

7. SQL • Basic building block of SQL is the Select Statement SELECT <attribute list> FROM <table list > [WHERE <search conditions>]

8. Select Statement • Select - chooses columns (project operation p in relational algebra) • From - combines tables if > 1 table (join operation |X| in relational algebra and Cartesian product X) • Where - chooses rows (select operation s in relational algebra) • Result of a query is usually considered another relation • Results may contain duplicate tuples

9. Queries • Select specified columns for all rows of a table • Select all columns for some of the rows of a table • Select specified columns for some rows of a table • Select all rows and columns of a table • All of the above for multiple tables

10. select lname from employee LNAME ---------- Smith Wong Zelaya Wallace Narayan English Jabbar Borg

11. select salary from employee; SALARY ---------- 30000 40000 25000 43000 38000 25000 25000 55000

12. Differences with relational model • Relation not a set of tuples - a multiset or bag of tuples • Therefore, 2 or more tuples may be identical • Use distinct to eliminate duplicate tuple Select distinct salary from employee

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

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

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

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

17. Where clause Where <search conditions> (s in relational algebra) • Search conditions can be: • Comparison predicate: expr § expr2 where § is <, >, <=, etc. in, between, like, is, etc. expr is constant, col, qual.col, aexpr op aexpr expr2 is expr | select statement • expr can be a select statement • expr CANNOT be an aggregate – must appear within a select statement

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

19. 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' • However, tables names in some RDMS (MySQL) are case sensitive

20. What if you want a list of the employee SSNs and department number of the departments they work for? Select SSN, dno From Employee • What if you want a list of the employee SSNs and name of the departments they work for?

21. Combining tuples using where clause • To retrieve data that is in more than one table can use: • a cartesian product X – also called a Cross Join • List ssn and dname of department employee works for Select ssn, dname From Employee, Department • A join operation |X| Select ssn, dname From Employee, Department Where dno=dnumber

22. 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) • This join is also referred to as an inner join

23. Alternative SQL notation for Join Select ssn, dname    From Employee Join Department on dno=dnumber Select lname, relationship    From Employee Join Department on ssn=essn Where dno=5

24. Select * From Employee, Dependent Select * From Employee, Dependent Where ssn=essn

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

26. Select SSN of employees who work in departments located in Houston

27. 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.*

28. Additional characteristics • Aliases are used to rename relations • List dname of each department and its locations Select dname as dept_name, dlocation From Department D, Dept_Locations DL Where DL.dnumber = D.dnumber NOTE: cannot use ‘as’ keyword here in Oracle

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

30. 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})

31. In predicate • Select SSN of 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. • Select SSN of employees who do not work in departments located in Houston

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

33. Quantified predicate • Write using quantified predicate: • Select SSN of employees who work in departments located in Houston • Which predicate should be used? = all, = any, > all, etc.?

34. 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 or != all

35. 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)

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

37. Exists predicate • Write using exists predicate: Select SSN of employees who work in departments located in Houston

38. 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’ also used) and intersection.

39. Exists predicate • Retrieve all the names of employees who do not work in a department located in Houston. • Retrieves the locations of the department Employee works for to see if one of them is Houston. If none exist (the inner query is empty and not exists is true) the Employee tuple is in the result.

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

41. SSN of employees who work for department located in Houston select ssn from employee, dept_locations where dno=dnumber and dlocation=‘Houston’; Select ssn from employee join dept_locations on dno=dnumber Where dlocation=‘Houston’; select ssn from employee where dno in (select dnumber from dept_locations where dlocation='Houston'); select ssn from employee where dno =any (select dnumber from dept_locations where dlocation='Houston'); select ssn from employee where exists (select dnumber from dept_locations where dlocation='Houston' and dno=dnumber);

42. JoinConditions • 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?

43. Join queries queries List the name of employees who have dependents with the same birthday as they do.

44. Join Queries • List all employee names and their supervisor names select e.lname, s.lname from employee e, employee s where e.superssn=s.ssn;

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

46. Set Operations • The Set Operations are: • UNION, INTERSECT and MINUS • NOTE: MySQL does not have minus • 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.

47. Union SELECT bdate FROM employee UNION SELECT bdate FROM dependent

48. List essn of employee who work on both pno=1 and pno=2 Select essn from works_on where pno=1 Intersect Select essn from works_on where pno=2

49. Set operations - Intersect • List all project names for projects that are worked on by an employee whose last name is Smith and has Wong as a manager of the department that controls the project (Select pname From Project, Works_on, Employee Where pnumber=pno and essn=ssn and lname='Smith') Intersect (Select pname From Project, Department, Employee Where dnum=dnumber and mgrssn=ssn and lname='Wong')

50. Minus Example using minus: Select departments that are not located in ‘Houston’ We wrote using ‘in’ predicate: Select dnumber from dept_locations where dnumber not in ( Select dnumber from dept_Locations Where dlocation = 'Houston'); Now use minus: Select dnumber from department Minus Select dnumber from dept_locations Where dlocation = 'Houston';