SQL

1. SQL 

3. SQL Relation is 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) Result of a query is a relation Results may contain duplicate tuples

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

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

9. Combining tuples in 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 each department and its manger info� Select * From Employee, Department Where mgrssn=ssn ��������������

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

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

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

13. 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�)

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

15. 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')

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

17. 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')

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

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

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

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

22. 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.�

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

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

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

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

27. Outer Joins Notation specific to DBMS Oracle:You can use a + to indicate an outer join�������The following example indicates a left outer join��������������� Select fname, minit, lname, dname��������������� From Employee, Department��������������� Where ssn=mgrssn(+)

28. Outer Joins You can use the keywords left, right, full �Again the following is a left outer join��������������� Select fname, minit, lname, dname��������������� From Employee Left Outer Join Department on ssn=mgrssn �The keyword Outer is optional

29. Alternative form of join Select fname, lname From (Employee join Department on dno=dnumber) Where dname=�Research�; Select fname, lname From employee, department Whre dno=dnumber and dname=�Research�

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

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

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

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

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

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

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

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

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

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

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

41. Nested or not? Select dname, count(*) From department, employee Where dnumber=dno and salary > 40000 and dno in (select dno from employee group by dno having count(*) > 5) Group by dname Select dname, count(*) From department, employee Where dnumber=dno and salary > 40000 Group by dname Having count(*) > 5

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

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

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

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

46. Set Operations The resulting relations are sets of tuples; duplicate tuples are eliminated. Operations apply only to union compatible relations.

47. Oracle example SELECT * FROM (SELECT ENAME FROM EMP WHERE JOB = 'CLERK' UNION SELECT ENAME FROM EMP WHERE JOB = 'ANALYST');

48. Example Queries Suppose you have created a table QtrSales (ID, Q1, Q2, Q3, Q4) SQL to compute the total sales for each quarter? SQL to compute the total sales for each ID?

49. Evaluation Logical order of evaluation: Apply Cartesian product to tables apply search conditions Apply group by and having Apply the select clause order the result for the display. Actual order of evaluation? More efficient to apply join condition during Cartesian product How can a DBMS implement a join?

50. Sample SQL queries List employee last names for employees who have at least 2 dependents. Increase the salary of employees working in the Administration department by 50%. Delete all employees who work on project 30.

51. Power/Weakness of SQL  �SQL summary: user specifies what is desired rather than how (e.g.� no need to write loop statement to traverse tuples) no implied ordering query optimizer decides how desired result described in one statement Avoids procedural complexities (non-procedural) A language with the power of relational algebra defined as relationally complete (Codd)

52. Weaknesses of SQL? 1) Too many equivalent forms e.g.� in, =any, exists ����������� not in, <> all 2) No non-procedural language can have Turing power (computationally complete), e.g. perform any computational procedure that can be specified in algorithmic terms SQL only relationally complete 3) Some capabilities missing Selects cannot be arbitrarily nested like relational algebra loss of expressive power of a relational algebra expression 4) Can find reports SQL can't create - need embedded SQL e.g.� categorize total sales based on size of sale group by 0-$499, $500-$999, etc.

53. Expansions to SQL 1) Can nest set functions e.g. Average of total dollar sales 2) More statistical functions (besides avg) e.g.� variance, mode 7) Transitive closure - arbitrary number managers of managers 8) Ranking available Retrieve top 5 out of 6 List 20 agents with largest sales

54. Strengths While subselects cannot be arbitrarily nested - have added power in search (where) condition Other strengths of SQL?

55. DBMS market share According to Gartner and IDC DB market was $16.6 B ($14B) in 2006 (2005) - $18.8B in 2007 Oracle � 48.6% (47.9%) market share IBM DB2 � 21.2% (21.6)% market share MS SQL Server � 18.6% (15%) NCR Teradata (number 4 in 2007) Sybase MySQL, PostGreSQL, Ingres 9.9% (10.7%)

56. DDL � Data Definition in SQL Used to CREATE, DROP and ALTER the descriptions of the relations of a database CREATE TABLE Specifies a new base relation by giving it a name, and specifying each of its attributes and their data types CREATE TABLE name (col1 datatype, col2 datatype, ..)

57. Data types: (ANSI SQL vs. Oracle) There are differences between SQL and Oracle, but Oracle will convert the SQL types to its own internal types int, smallint, integer converted to NUMBER Character is char(l) or varchar2(l), varchar(l) still works Float and real converted to number

58. Constraints Constraints are used to specify primary keys, referential integrity constraints, etc. CONSTRAINT constr_name PRIMARY KEY CONSTRAINT constr_name REFERENCES table (col) You can also specify NOT NULL for a column

59. Create table � In line constraint definition Create table Project2 (pname varchar2(9) CONSTRAINT pk PRIMARY KEY, pnumber int not null, plocation varchar2(15), dnum int CONSTRAINT fk REFERENCES�Department (dnumber), phead int);

60. Create Table � out of line constraint definition Create table Project2 (pname varchar2(9), pnumber int not null, plocation varchar2(15), dnum int, phead int, CONSTRAINT pk PRIMAY KEY (pname), CONSTRAINT fk FOREIGN KEY (dnum) REFERENCES�Department (dnumber));

61. Oracle Specifics When you specify a foreign key constraint out of line, you must specify the FOREIGN KEY keywords and one or more columns. When you specify a foreign key constraint inline, you need only the REFERENCES clause.

62. Integrity constraints in Oracle A NOT NULL constraint prohibits a database value from being null. A unique constraint - allows some values to be null. A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. A foreign key constraint requires values in one table to match values in another table. A check constraint requires a value in the database to comply with a specified condition (e.g. between 0-100)

63. DROP TABLE Used to remove a relation and its definition The relation can no longer be used in queries, updates or any other commands since its description no longer exists Drop table dependent;

64. ALTER TABLE To alter the following ways: to add a column to add an integrity constraint to redefine a column (datatype, size, default value) � there are some limits to this to enable, disable or drop an integrity constraint or trigger other changes relate to storage, etc. Useful if 2 tables reference each other Oracle alter

65. Updates (DML) Insert, delete and update INSERT Insert into table_name ( [(col1 {, colj})] values  (val1 {, valj}) | (col1 {, colj}) subselect ) add a single tuple attribute values must be in the same order as the CREATE table

66. Insert Insert into Employee values ('Richard', 'K',  'Marini', '654298343', '30-DEC-52', '98 Oak  Forest, Katy, TX', 'M', 37000, '987654321, 4); Use null for null values in ORACLE

67. Insert To insert multiple tuples from existing table: Create table Depts_Info (dept_name varchar(10), no_of_emps int, total_sal int); Insert into Depts_Info Select dname, count(*), sum(salary) From Department, Employee Where dnumber = dno Group By dname;

68. Delete Delete from table_name [search_condition] If include a where clause to select, tuples are deleted from table one at a time The number of tuples deleted depends on the where clause If no where clause included all tuples are deleted - the table is empty

69. Delete Delete From Employee Where lname = 'Brown�; Delete From Employee Where ssn = '123456789�; Delete from Employee Where dno in (Select dnumber From Department Where dname = 'Research'); Delete from Employee;

70. Update Modifies values of one or more tuples Where clause used to select tuples Set clause specified the attribute and value (new) Only modifies tuples in one relation at a time Update <table name> Set attribute = value {, attribute = value} Where <search conditions>

71. Update Update Project Set plocation = 'Bellaire', dnum = 5 Where pnumber = 10 Update Employee Set salary = salary * 1.1 Where dno in (Select dnumber From department Where dname = 'Research')

72. Violation of Integrity Constraints Insert, delete or update can violate a referential integrity constraint SQL allows qualified options to be specified for the foreign key: Set null Cascade Set default � NOT AVAILABLE in ORACLE On delete or On update (includes insert)

73. Oracle The ON DELETE CASCADE and ON DELETE SET NULL Specify CASCADE if you want Oracle to remove all tuples with dependent foreign key values. Specify SET NULL if you want Oracle to convert dependent foreign key values to NULL.

74. SQL Subselect - formal definition: ������� Select expr {, expr} ������� From tablename [alias] {, tablename [alias]} ������� [Where search_condition] ������� [Group By col {, col}] ������� [Having search_condition] Full Select - formal definition: �����Subselect {Union [all] subselect} ��[Order By result_col [asc | desc] {, result_col [asc | desc]}]