SQL

1. SQL 1

2. OBJECTIVES • What is SQL? • Types of SQL commands and their function • Query • Index • Views • Synonyms • Database transactions 2

3. SQL • Sequential Query Language • Used for authenticating,accessing, manipulating of data from database. 3

4. Types of SQL Commands • Data Definition Language(DDL) • Data Manipulation Language(DML) • Data Query Language(DQL) • Data Control Language(DCL) • Data Administration Command • Transactional Control Command 4

5. DDL • Create Table • Alter Table • Drop Table • Create Index • Alter Index • Drop Index 5

6. SQL: DDL Commands • CREATE TABLE: used to create a table. • ALTER TABLE: modifies a table after it was created. • DROP TABLE: removes a table from a database.

7. SQL: CREATE TABLE Statement • Things to consider before you create your table are: • The type of data • the table name • what column(s) will make up the primary key • the names of the columns • CREATE TABLE statement syntax: CREATE TABLE <table name> ( field1 datatype ( NOT NULL ), field2 datatype ( NOT NULL ) );

8. SQL: Attributes Types

9. date food profit date food profit sold date food sold Example: CREATE TABLE FoodCart ( date varchar(10), food varchar(20), profit float ); ALTER TABLE FoodCart ( ADD sold int ); ALTER TABLE FoodCart( DROP COLUMN profit ); DROP TABLE FoodCart; FoodCart FoodCart FoodCart

10. SQL: ALTER TABLE Statement • To add or drop columns on existing tables. • ALTER TABLE statement syntax: ALTER TABLE <table name> ADD attr datatype; or DROP COLUMN attr;

11. SQL: DROP TABLE Statement Has two options: • CASCADE: Specifies that any foreign key constraint violations that are caused by dropping the table will cause the corresponding rows of the related table to be deleted. • RESTRICT: blocks the deletion of the table of any foreign key constraint violations would be created. DROPTABLE statement syntax: DROPTABLE <table name> [ RESTRICT|CASCADE ];

12. DML • Insert • Update • Delete 12

13. date food sold date food sold 02/25/08 pizza 350 02/25/08 pizza 350 02/26/08 hotdog 500 02/26/08 hotdog 500 02/26/08 pizza 70 SQL: INSERT Statement • To insert a row into a table, it is necessary to have a value for each attribute, and order matters. • INSERT statement syntax: INSERT into <table name> VALUES ('value1', 'value2', NULL); Example: INSERT intoFoodCart VALUES (’02/26/08', ‘pizza', 70 ); FoodCart

14. date food sold date food sold 02/25/08 pizza 350 02/25/08 pizza 349 02/26/08 hotdog 500 02/26/08 hotdog 500 02/26/08 pizza 70 02/26/08 pizza 70 SQL: UPDATE Statement • To update the content of the table: UPDATE statement syntax: UPDATE <table name> SET <attr> = <value> WHERE <selection condition>; Example: UPDATE FoodCart SET sold = 349 WHERE date = ’02/25/08’ AND food = ‘pizza’; FoodCart

15. date food sold date food sold 02/25/08 pizza 349 02/25/08 pizza 349 02/26/08 hotdog 500 02/26/08 pizza 70 02/26/08 pizza 70 SQL: DELETE Statement • To delete rows from the table: DELETE statement syntax: DELETE FROM <table name> WHERE <condition>; Example: DELETE FROM FoodCart WHERE food = ‘hotdog’; FoodCart Note: If the WHERE clause is omitted all rows of data are deleted from the table.

16. DQL • Select Statement: Used to select rows from a table • Select contains following main keywords • SELECT • FROM • WHERE • GROUP BY • ORDER BY 16

17. Name Age Weight Harry 34 80 Name Age Weight Sally 28 64 Harry 34 80 George 29 70 Helena 54 54 Helena 54 54 Peter 34 80 Peter 34 80 Weight Weight 80 80 54 54 80 Example:Person 1)SELECT *FROM personWHERE age > 30; 3)SELECTdistinct weightFROM personWHERE age > 30; 2)SELECT weightFROM personWHERE age > 30;

18. Transactional Control Command • Commit • Rollback • Savepoint 18

19. DCL • Alter Password • Grant • Revoke • Create Synonym 19

20. Data Manipulation Language • Inserting into the table INSERT INTO emp ( name , age , doj , phone_no) VALUES(‘Anshuman’, 21 , 12/07/1999, ‘393751’) ; • Updating Existing Data UPDATE emp SET name = ‘Sumit’ age = 31 WHERE name = ‘Anshuman’ ; 20

21. Data Manipulation Language • Deleting data from table DELETE FROM emp WHERE name = ‘Anshuman’ ; 21

22. Managing Tables • Modifying the table ALTER TABLE emp MODIFY (name VARCHAR2(12) NOT NULL) ADD phone_no VARCHAR2(10) ; • Dropping the table DROP TABLE emp CASCADE 22

23. DQL • Selecting all the rows of table emp • SELECT * FROM emp • Selecting phone no. & age of employee Anshuman from table emp • SELECT phone_no , age FROM emp WHERE name = ‘Anshuman’ ; 23

24. Eliminating Duplicates SELECT DISTINCT category FROM Product Compare to: SELECT category FROM Product

25. AGGREGATE FUNCTIONS • Used to provide summarization information for a SQL statement, such as COUNT,TOTAL,AVERAGE . • Aggregate functions to be covered are • COUNT • SUM • MAX • MIN • AVG 25

26. AGGREGATE FUNCTIONS • COUNT • Used to return no. of rows . • Return a numeric value when used with a query. • Can be used with three options: */DISTINCT/ALL. • COUNT(*) counts all the rows of a table including duplicates, whether a NULL value is contained in a column or not. • COUNT(ALL) counts all the rows including the duplicates but excluding the rows containing NULL values. • COUNT(DISTINCT)counts all the rows excluding the duplicate rows and the rows containing NULL values. 26

27. AGGREGATE FUNCTIONS • Usage: • SELECT COUNT(*) FROM emp ; • SELECT COUNT(DISTINCT name) FROM emp ; • SELECT COUNT(ALL name) FROM emp ; 27

28. AGGREGATE FUNCTIONS • SUM • Used to return a total on the values of a column • DISTINCT can be used with SUM where only distinct column values are to be added. • Usage: • SELECT SUM (age) FROM emp ; • SELECT SUM(DISTINCT age) FROM emp ; 28

29. AGGREGATE FUNCTIONS • AVG • Used to find the averages for a group of rows. • Can be used with DISTINCT command when average of distinct rows is to be found out. • Usage: • SELECT AVG(age) FROM emp ; • SELECT AVG(DISTINCT age) FROM emp ; 29

30. AGGREGATE FUNCTIONS • MAX • Used to return the maximum value of a column in a group of rows. • NULL values are ignored . • Usage: • SELECT MAX(age) FROM emp ; 30

31. AGGREGATE FUNCTIONS • MIN • Used to return the minimum value of a column for a group of rows. • NULL values are ignored . • Usage: • SELECT MIN(age) FROM emp ; 31

32. COUNT (*) COUNT ( [DISTINCT] A) SUM ( [DISTINCT] A) AVG ( [DISTINCT] A) MAX (A) MIN (A) Aggregate Operators • Significant extension of relational algebra. single column SELECT COUNT (*) FROM Sailors S SELECT S.sname FROM Sailors S WHERE S.rating= (SELECT MAX(S2.rating) FROM Sailors S2) SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10 SELECT COUNT (DISTINCT S.rating) FROM Sailors S WHERE S.sname=‘Bob’ SELECT AVG ( DISTINCT S.age) FROM Sailors S WHERE S.rating=10

33. Find name and age of the oldest sailor(s) SELECT S.sname, MAX (S.age) FROM Sailors S • The first query is illegal! (We’ll look into the reason a bit later, when we discuss GROUP BY.) • The third query is equivalent to the second query, and is allowed in the SQL/92 standard, but is not supported in some systems. SELECT S.sname, S.age FROM Sailors S WHERE S.age = (SELECT MAX (S2.age) FROM Sailors S2) SELECT S.sname, S.age FROM Sailors S WHERE (SELECT MAX (S2.age) FROM Sailors S2) = S.age

34. ACCESSING DATA FROM MULTIPLE TABLES • By joining the tables • By using nested queries. • By using compound queries 34

35. JOIN • Join combines two or more tables in order to retrieve data from multiple tables. • Example SELECT name , address FROM student , stu where student.stid = stu.stid ; 35

36. Keys and Foreign Keys Company Key Product Foreignkey

37. Joinbetween Productand Company Joins Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all products under $200 manufactured in Japan;return their names and prices. SELECT PName, PriceFROM Product, CompanyWHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200

38. ID State ID Division 1000 CA 1001 IT 1001 MA 1002 Sales 1002 TN 1003 Biotech SQL: Join operation • A join can be specified in the FROM clause which list the two input relations and the WHERE clause which lists the join condition. Example: Emp Dept

39. Emp.ID Emp.State Dept.ID Dept.Division 1001 MA 1001 IT 1002 TN 1002 Sales SQL: Join operation (cont.)‏ • inner join = join SELECT * FROM emp join dept (or FROM emp, dept)‏ on emp.id = dept.id;

40. Emp.ID Emp.State Dept.ID Dept.Division 1000 CA null null 1001 MA 1001 IT 1002 TN 1002 Sales SQL: Join operation (cont.)‏ • left outer join = left join SELECT * FROM emp left join dept on emp.id = dept.id;

41. Emp.ID Emp.State Dept.ID Dept.Division 1001 MA 1001 IT 1002 TN 1002 Sales null null 1003 Biotech SQL: Join operation (cont.)‏ • right outer join = right join SELECT* FROM emp right join dept on emp.id = dept.id;

42. Sub Query • Query within another query • Sub query is used to return data that will be used in the main query as a condition to furthur restrict the data to be retrieved . • Used with Select , Insert , Update , Delete statements. 42

43. Nested Queries Find names of sailors who’ve reserved boat #103: SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103) • A very powerful feature of SQL: a WHERE clause can itself contain an SQL query! (Actually, so can FROM and HAVING clauses.) • To find sailors who’ve not reserved #103, use NOT IN. • To understand semantics of nested queries, think of a nested loops evaluation: For each Sailors tuple, check the qualification by computing the subquery.

44. Nested Queries with Correlation Find names of sailors who’ve reserved boat #103: SELECT S.sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 ANDS.sid=R.sid) • EXISTS is another set comparison operator, like IN. • If UNIQUE is used, and * is replaced by R.bid, finds sailors with at most one reservation for boat #103. (UNIQUE checks for duplicate tuples; * denotes all attributes. Why do we have to replace * by R.bid?) • Illustrates why, in general, subquery must be re-computed for each Sailors tuple.

45. More on Set-Comparison Operators • We’ve already seen IN, EXISTS and UNIQUE. Can also use NOT IN, NOT EXISTSand NOT UNIQUE. • Also available: opANY, opALL, op IN • Find sailors whose rating is greater than that of some sailor called Horatio: SELECT * FROM Sailors S WHERE S.rating > ANY(SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Horatio’)

46. Rewriting INTERSECT Queries Using IN Find sid’s of sailors who’ve reserved both a red and a green boat: SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ AND S.sid IN (SELECT S2.sid FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=‘green’) • Similarly, EXCEPT queries re-written using NOT IN. • To find names (not sid’s) of Sailors who’ve reserved both red and green boats, just replace S.sid by S.sname in SELECT clause. (What about INTERSECT query?)

47. SELECT S.sname FROM Sailors S WHERE NOT EXISTS ((SELECT B.bid FROM Boats B) EXCEPT (SELECT R.bid FROM Reserves R WHERE R.sid=S.sid)) Division in SQL (1) Find sailors who’ve reserved all boats. • Let’s do it the hard way, without EXCEPT: SELECT S.sname FROM Sailors S WHERE NOT EXISTS (SELECT B.bid FROM Boats B WHERE NOT EXISTS (SELECT R.bid FROM Reserves R WHERE R.bid=B.bid AND R.sid=S.sid)) (2) Sailors S such that ... there is no boat B without ... a Reserves tuple showing S reserved B

48. COMPOUND QUERY • Includes two or more SELECT statements • Formed by using UNION,INTERSECT,EXCEPT operators. 48

49. Find sid’s of sailors who’ve reserved a red or a green boat SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’) • UNION: Can be used to compute the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries). • If we replace OR by AND in the first version, what do we get? • Also available: EXCEPT (What do we get if we replace UNION by EXCEPT?) SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNION SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

50. Find sid’s of sailors who’ve reserved a red and a green boat SELECT S.sid FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid AND (B1.color=‘red’ AND B2.color=‘green’) • INTERSECT: Can be used to compute the intersection of any two union-compatible sets of tuples. • Included in the SQL/92 standard, but some systems don’t support it. • Contrast symmetry of the UNION and INTERSECT queries with how much the other versions differ. Key field! SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ INTERSECT SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’