SQL Chapter 4

1. SQL Chapter 4

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, 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 specified columns for some rows Select all rows and columns of a relation   Select some of the rows

8. Queries Retrieve the birthdate and address of the employee whose name is 'Smith‘ To retrieve all the attribute values of the selected tuples, a * is used: Select * From Employee

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

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

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

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

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

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

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

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

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

20. Alternative SQL notation for Join        Select lname, relationship    From Employee Join Dependent on ssn=essn

21. 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 they work for List the ssn, lname of all female employees working in the ‘Research’ department

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. 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.    There are 2 join conditions in the above query and 1 select condition

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

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

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

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

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

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

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

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

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

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

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

35. Single block queries An Expression written using = or IN may almost always be expressed as a single block query.

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

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

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

39. 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(+)

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

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

42. To Access Oracle in Houser 108 Log on to the machine, using your COE account Login:  coe account name Password:  your password Go to Start, Programs then choose Oracle-OraHome9, Application Development and SQL Plus.  In SQL Plus User Name: first-name-initial and lastname (lowercase) e.g. svrbsky Password: CWID Host String: XE

43. Using SQL Plus Type in SQL commands interactively Or can cut and paste queries from a .txt file Each SQL statement must have a semicolon ; at the end Results of the queries are displayed on the screen Can cut and paste the results to a file Can also run commands from a file by specifying @filename Copy and paste results to output file

44. Oracle: making changes permanent inserting tuples into a table using SQL Plus, you must: type in commit; Or specify quit; To make changes permanent SQL statements to create company DB tables

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

46. Aggregates Retrieve the total number of employees in the company Retrieve the number of employees in the research department.

47. 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, salarly for employees with salaries > average salary

48. What does this query do? SELECT dno, lname, salary FROM employee x WHERE salary > (SELECT AVG(salary) FROM employee WHERE x.dno = dno)

49. 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. Group By col {, col}

50. Grouping For each department, retrieve the department number, the total number of employees and their average salary. The tuples are divided into groups with the same dno. COUNT and AVG are then applied to each group.

51. Grouping For each project, retrieve the project number, project name and the number of employees who work on that project. In the above query, the joining of the two relations is done first, then the grouping and aggregates are applied.

52. Oracle group by – STANDARD SQL 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. Some DBMS (e.g. MySQL) do not implement standard SQL In this class everyone will use standard SQL

53. Example Compute the average number of dependents over employees with dependents There are several ways to do this, but note that you can do: aggr(aggr(col)) select avg(count(essn)) from dependent,employee where ssn=essn(+) group by ssn; select avg(count(essn)) from dependent,employee where ssn=essn(+) group by ssn;

54. Having Clause Sometimes we want to retrieve those tuples with certain values for the aggregates (Group By). 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. Having search_condition

55. 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 Try this query without using the having

56. Without having… Select pnumber, pname, COUNT(*) From Project, Works_on Where 2 < (select count(*) from works_on where pno=pnumber) and pnumber =pno Group By pnumber, pname Select * From (select pnumber, pname, COUNT(*) as cnt from works_on, project where pno=pnumber group by pnumber, pname) Where cnt > 2

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

58. Select Select is really: Subselect {Set_Operation [all] Subselect}  [Order By col [asc | desc] {, col [asc | desc]}]

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

60. Order by Retrieve names of the employees and each project they work on, ordered by the employee's department, and within each department order the employees alphabetically by last name.

61. Select expr {, expr} From tablename [alias] {, tablename [alias]} [Where search_condition] [Group By col {, col}] [Having search_condition] [Order by col {, col}]

62. Logical order of Evaluation Select pnumber, pname, COUNT(*) From Project, Works_on Where pnumber =pno Group By pnumber, pname Having COUNT(*) > 2 Order by pname Apply Cartesian product to tables, Join and select conditions then group by and having. Apply the select clause order the result for the display.

63. Set Operations The Set Operations are: UNION, MINUS and INTERSECT 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.

64. Union SELECT bdate FROM employee UNION SELECT birthdate FROM dependent

65. Minus Example using minus to list all employees who don’t work on a project: Select ssn from employee Minus Select essn from works_on

66. Is this correct? List ssn of all employees who do not work on project with pno=1 Select essn, pno From works_on Where pno <> 1;

67. Set operations List all project names for projects that is worked on by an employee whose last name is Smith or has a Smith as a manager of the department that controls the project

68. Example queries SQL to list employee name and department name for employees with salary > $32,000. SQL to list department name and average salary SQL to list department name for departments with average salary > $32,000. Can you write these queries?: SQL to list employee name, department name and average salary of employees department SQL to list employee name, department name and average salary for departments with average salary > $32,000.

69. 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, ..)

70. Data Types 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

71. Constraints Constraints are used to specify primary keys, referential integrity constraints, etc. CONSTRAINT constr_name PRIMARY KEY CONSTRAINT constr_name REFERENCES table (col) Constraint names must be unique across database You can also specify NOT NULL for a column

72. 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);

73. Create table To create a table with a composite primary key must use out of line definition: Create table Works_on (essn char(9), pno int, hours number(4,1), CONSTRAINT pk2 PRIMARY  KEY (essn, pno));

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

75. 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));

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

77. ALTER TABLE To alter the definition of a table in 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.

78. How to create a table when? Department mgrssn references employee ssn with mgrssn Employee dno references department dnumber

79. Alter is useful when … You have two tables that reference each other Table must be defined before referenced, so how to define?: department mgrssn references employee ssn with mgrssn Employee dno references department dnumber Create employee table without referential constraint for dno Create department table with reference to mgrssn Alter employee and add dno referential constraint (or when you specify create table you can disable the references, then enable them later)

80. Alter table - Oracle The table you modify must have been created by you, or you must have the ALTER privilege on the table. If used to add an attribute to one of the base relations, the new attribute will have NULLS in all the tuples of the relation after command is executed; hence, NOT NULL constraint is not allowed for such an attribute. Alter table employee add job varchar(12); The database users must still enter a value for the new attribute job for each employee tuple using the update command. Oracle alter

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

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

83. Insert Alternative form - specify attributes and leave out the attributes that are null Insert into Employee (fname, lname, ssn)  values ('Richard', 'Marini', '654298343');   Constraints specified in DDL are enforced when updates are applied.

84. Insert To insert multiple tuples from existing table:

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

86. Delete Examples: 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;

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

88. Update Examples:

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

90. Integrity constraints in Oracle Insert, delete or update can violate a referential integrity constraint 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.

91. 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 - on delete or update (includes insert) Set null Cascade Set default Not all options available in Oracle Set null and Cascade on delete are available SQLServer allows on delete and on update

92. Oracle The ON DELETE clause - If you omit this clause, then Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table. 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.

93. Example of cascade create table tests (id int unique, age int not null, dno int, constraint fk foreign key (dno) references department (dnumber) on delete cascade); Examples

94. Relational Views A view - a virtual table that is derived from other tables (base tables or can be another view) A view can be used to simplify frequent queries e.g. a join condition A view does not necessarily exist in physical form  There is no limit on querying a view (limits on an update to a view)  Views are useful for security and authorization mechanisms

95. Create View View attribute names are inherited from other tables If aggregate functions are the result of arithmetic operations, they must be renamed Views can be defined using other views

96. Create view CREATE VIEW view_name [(col1 {, col2})] AS SELECT col1 {, col2} FROM (table1| view1) {, table2 | view2} WHERE search_condition

97. Multiple table views To create a view from multiple tables Create View Works_on1 As Select fname, lname, pname, hours From Employee, Project, Works_on Where ssn = essn and pno = pnumber;

98. Create View Create a view to list for each department: dname, number of employees and total salary paid out Create view Name (cols) As Select query

99. Views Queries on View - same as queries on base tables Retrieve the last and first names of all employees who work on ProjectX   How to represent derived attributes?

100. Maintain views 2 strategies to maintain views: View is stored as a temporary table for future queries called view materialization view is not realized at the time of the view definition but when specify the query Another strategy is to modify the view query into a query on the underlying base table called query modification DBMS keeps views up-to-date – how?

101. Views A view is removed by using the DROP VIEW command. Drop View Works_on1; Drop View Dept_info; 

102. Updating views  If specify an update to a view, it updates the corresponding tables. Create View Emp As Select fname, lname, ssn, dno From Employee Update Emp Set dno = 1 Where lname = ‘English’

103. Updating views It may not make sense to update some views – why? Cannot always guarantee that a view can be updated

104. Views When would it not make sense? General Rule: (true for ORACLE) View with one defining table is updatable if the view attributes contain a primary key Views with more than one table using joins is not updatable View with aggregate functions are not updatable

105. Logical order of Evaluation Select pnumber, pname, COUNT(*) From Project, Works_on Where pnumber =pno Group By pnumber, pname Having COUNT(*) > 2 Order by pname Apply Cartesian product to tables, Join and select conditions then group by and having. Apply the select clause order the result for the display.

106. Order of evaluation Actual order of evaluation? Which is? More efficient to apply join condition during Cartesian product (join operation) How can a DBMS implement a join?

107. Order of evaluation More efficient to apply join condition during Cartesian product (join operation) How can a DBMS implement a join?

108. Example queries SQL to list employee name and department name for employees with salary > $32,000. SQL to list department name and average salary SQL to list department name for departments with average salary > $32,000. Can you write these queries?: SQL to list employee name, department name and average salary of employee’s department select lname, dname, (select avg(salary) from employee grp where grp.dno = ind.dno) from employee ind, department where dno=dnumber; SQL to list employee name, department name and average salary of employee’s department only for employees who work in departments with average salary > $32,000.

109. Standard SQL What is the deal with MySQL vs. standard SQL? Oracle has standard SQL MySQL does not http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html

110. Metadata To get information about a specific table: Describe employee Lists all attributes and type To get information about all user tables, can query user_tables Select table_name from user_tables

111. System tables user_tables user_tab_columns user_constraints user_cons_columns user_triggers user_views user_tab_privs user_tab_privs_made (lists privileges granted to others) user_col_privs

112. Triggers Suppose you want to make sure values you insert are correct (e.g. hours > 0) You can define a trigger to perform an action when a specific event occurs That event can be an insert, delete, update

113. Example create trigger tr1 after insert on works_on referencing new x for each row when (x.hours < 1) begin raise_application_error(-20003,'invalid hours on insert'); // insert was performed but error printed end;

114. Create Trigger CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | DELETE | UPDATE [OF colname {, colname...}]} ON tablename [REFERENCING corr_name_def {, corr_name_def...}] [FOR EACH ROW | FOR EACH STATEMENT] [WHEN (search_condition)] {statement -- action (single statement) | BEGIN ATOMIC statement; { statement;...} END} -- action (multiple statement.) DROP TRIGGER trigger_name;

115. Triggers The corr_name_def in the REFERENCING clause looks like: {OLD [ROW] [AS] old_row_corr_name | NEW [ROW] [AS] new_row_corr_name | OLD TABLE [AS] old_table_corr_name | NEW TABLE [AS] new_table_corr_name}

116. Using BEFORE BEFORE command fires trigger before UPDATE Example is to add username of person performing update to another table Can write to :new value but not to :old value If use After, can write to either :old or :new value