SQL

1. SQL Neyha Amar CS 157A, Fall 2006

2. Inserting • The insert statement is used to add a row of data into a table • Strings should be enclosed in single quotes, and numbers should not. • Consider the following table: Employee

3. Example of Insertion insert into Employee values(3, ‘Steve’, ‘Glasses’) OR insert into Employee(EmpID, name, DeptID) values(3, ‘Steve’, ‘Glasses’) OR insert into Employee(name, DeptID, EmpID) values(‘Steve’, ‘Glasses’, 3)  Row inserted

4. Updating • The update statement is used to change a value in a tuple that matches a specified criteria. General Form: update tablename set column = new value, nextcolumn = new value2, … where somecolumn [and | or othercolumn]OPERATOR value Example1 (from textbook): update account set balance = balance * 1.05 where balance >= 1000 • Without the WHERE all values under the specified column will be updated

5. Example 2: Updating Employee updateEmployee set DeptID = ‘Shoes’ wherename = ‘Steve’ updated row 

6. Deletion • The delete statement is used to delete tuples from the table • We can only delete whole tuples, not values in only particular attributes General Form: delete from tablename where somecolumn [and | or othercolumn]OPERATOR value Example 1: delete fromaccount wherebranch_name = ‘Perryridge’ • w/o the WHERE all records will be deleted !!!

7. Example 2: Deletion Employee delete fromEmployee where EmpID = 3

8. Set Operations • SQL operations Union,Intersect,andExcept operate on relations and correspond to relational algebra operations union, intersection and set difference • Relations participating in operations must be compatible, that is they must have the same attributes

9. Union Operation Example: Find all the bank customers having a loan, an account, or both at the bank. (selectcustomer_name fromdepositor) union (selectcustomer_name fromborrower) • Union operation automatically eliminates duplicates If customer has several accounts or loans (or both) at the bank then he/she will appear only ONCE in the result.

10. Continue… Union Operation • If you want to retain all duplicates, you must write UNION ALL in place of UNION (selectcustomer_name fromdepositor) union all (selectcustomer_name fromborrower)

11. Intersect Operation Example: Find all the bank customers having a loan AND an account at the bank. (select distinctcustomer_name fromdepositor) intersect (selectdistinct customer_name fromborrower) • automatically eliminates duplicates • to retain them use INTERSECT ALLin place of INTERSECT

12. Except Operation Example: Find all the bank customers having an account but NO loan at the bank. (selectdistinct customer_name fromdepositor) except (selectdistinct customer_name fromborrower) • automatically eliminates duplicates, • To retain use EXCEPT ALLin place of EXCEPT

13. Aggregate Functions • Functions that take a collection (a set or multiset) of values as input and return a single value. • SQL has five built-in aggregate functions: - avg ( [distinct | all] n) – returns average value of n - min ( [distinct | all] expr) – returns minimum value of expr - max ( [distinct | all] expr) – returns maximum value of expr - sum ( [distinct | all] n) – returns total sum of values in expr -count (* | [distinct | all] expr) - returns # of rows * - return # of rows including NULL values from relation distinct - return # of rows eliminating duplicates and NULL values from expr all - return # of rows including duplicates but no NULL values from expr • The input to sum and avg must be a collection of numbers • Others can operate on collections of nonnumeric data types, such as strings, as well.

14. Example: Aggregate Function Example: Find the average account balance at the Perryridge branch. selectavg (balance) from account [as average] wherebranch_name = ‘Perryridge’ • Result will be a relation with a single attribute, containing a single tuple that equals the average account balance at Perryridge • Can give a name to the attribute by using the AS clause

15. Group By Clause • GROUP BY is used when we are selecting multiple columns from a table (or tables) and at least one arithmetic operator appears in the SELECT statement. • When that happens, we need to GROUP BY all the columns except the one(s) operated on by the arithmetic operator

16. Example: GROUP BY Consider the following relation schema: Store_Information

17. Example GROUP BY… Query: Find the total sales for each store. Answer: select Store_name, sum(Sales) from Store_Information group by store_name Result:

18. HAVING clause • HAVING serves as the WHERE clause for grouped data • This condition does not apply to a single tuple; it applies to each group constructed by the group by clause.

19. Example: HAVING Query: Find the total sales for each store that has a total sale greater than $1500. Answer: select Store_name, sum(Sales) from Store_Information group by store_name having sum(Sales) > 1500 Result:

20. Null Values • SQL allows the use of null values to indicate absence of information about the value of an attribute. • We can use the special keyword nullin a predicate to test for a null value

21. Examples: Null Values Query: Find all loan numbers that appear in the loan relation with null values for amount. Answer: select loan_number from loan where amountis null Query: Find all loan numbers that appear in the loan relation that do not have null values for amount. Answer: select loan_number from loan where amountis not null

22. References • Database System Concepts, 5th edition, Silberschatz, Korth, Sudarshan • http://www.nd.edu/~pmiller/capp_sql/sql_group_functions.ppt • http://databases.about.com/od/sql/l/aaaggregate2.htm