1 / 52

Structured Query Language

Structured Query Language . Introduction. Basic Select. Employees Table. EMPID. LNAME. FNAME. SEX. DEPT. PHONE. SALARY. 23. Jones. Mark. M. ITR. 555-1087. 45000. 25. Smith. Sara. F. FINC. 555-2222. 49000. 26. Billings. David. M. ACTG. 555-4356. 42000. 31. Dance.

aman
Download Presentation

Structured Query Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Structured Query Language Introduction

  2. Basic Select Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT lname, fname, phone FROM employees;

  3. Where Clause Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT lname, fname, phone FROM employees WHERE dept = ‘ACTG’;

  4. Virtual Fields Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY SALARY/12 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 3500 31 Dance Ivanna F ACTG 444-4887 60000 5000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 3666 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT lname, fname, phone, salary/12 FROM employees WHERE dept = ‘ACTG’;

  5. Naming Virtual Fields Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY MonthlySal 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 3500 31 Dance Ivanna F ACTG 444-4887 60000 5000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 3666 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT lname, fname, phone, salary/12 AS MonthlySal FROM employees WHERE dept = ‘ACTG’;

  6. Order By Clause LNAME FNAME PHONE MonthlySal Dance Ivanna 444-4887 5000 Barker Bob 555-6565 3666 Billings David 555-4356 3500 SELECT lname, fname, phone, salary/12 as MonthlySal FROM employees WHERE dept = ‘ACTG’ ORDER BY MonthlySal Desc

  7. Order By Clause (multiple keys) secondary sort key primary sort key LNAME FNAME PHONE DEPT SALARY Dance Ivanna 444-4887 ACTG 60000 Barker Bob 555-6565 ACTG 44000 Billings David 555-4356 ACTG 42000 Jones Mary 555-1234 FINC 56000 Smith Sara 555-2222 FINC 49000 Woods Robin 555-9812 ITR 90000 Jones Mary 555-8745 ITR 70000 Jones Mark 555-1087 ITR 45000 SELECT lname, fname, phone, dept, salary FROM employees ORDER BY Dept ASC, Salary DESC

  8. “Like” Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT * FROM employees WHERE fname LIKE ‘M%’;

  9. Combining Conditions Retrieve all Columns for employees working in ITR that have a salary less than 50000.

  10. Combining Conditions Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT * FROM employees WHERE dept = ‘ITR’ AND salary < 50000;

  11. Combining Conditions:English is not very structured Retrieve all Columns for all employees that work in the finance and ITR departments.

  12. Combining Conditions Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT * FROM employees WHERE dept = ‘ITR’ AND dept=‘FINC’;

  13. Combining Conditions Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT * FROM employees WHERE dept = ‘ITR’ OR dept=‘FINC’;

  14. Combining Conditions:Order of Operation Retrieve all Columns for all employees that work in either the finance or ITR departments and that also make less than $50000.

  15. Combining Conditions Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT * FROM employees WHERE dept = ‘ITR’ OR dept=‘FINC’ and salary < 50000

  16. Combining Conditions Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT * FROM employees WHERE (dept = ‘ITR’ OR dept=‘FINC’) and salary < 50000

  17. IN comparator Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT * FROM employees WHERE dept IN(‘ITR’,’FINC’);

  18. Distinct Key Word Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 SELECT DISTINCT dept FROM employees;

  19. Analytic Functions Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000 AVG(SALARY) SELECT AVG(salary) FROM employees; 57000

  20. SQL Analytic Functions AVG( ) MIN( ) MAX( ) SUM( ) COUNT( ) VAR() STD()

  21. Group Functions Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY AVG(SALARY) 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 35 Barker Bob M ACTG 555-6565 44000 48666 37 Jones Mary F FINC 555-1234 56000 25 Smith Sara F FINC 555-2222 49000 52500 23 Jones Mark M ITR 555-1087 45000 32 Jones Mary F ITR 555-8745 70000 36 Woods Robin M ITR 555-9812 90000 68333 SELECT dept, AVG(salary) FROM employees GROUP BY dept;

  22. Grouping by Multiple Attributes DEPT SEX AVG(SALARY) ACTG F 60000 ACTG M 43000 FINC F 52500 FINC M No male finance employees ITR F 70000 ITR M 67500 SELECT dept, sex, AVG(salary) FROM employees GROUP BY dept, sex;

  23. Having Clause Base Table Inter. Result Set 1 Inter. Result Set 2 GROUP BY WHERE HAVING Inter. Result Set 3 Final Results ORDER BY

  24. Having Clause (example) Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY AVG(SALARY) 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 35 Barker Bob M ACTG 555-6565 44000 48666 37 Jones Mary F FINC 555-1234 56000 25 Smith Sara F FINC 555-2222 49000 52500 23 Jones Mark M ITR 555-1087 45000 32 Jones Mary F ITR 555-8745 70000 36 Woods Robin M ITR 555-9812 90000 68333 SELECT dept, AVG(salary) FROM employees GROUP BY dept HAVING AVG(salary)<50000;

  25. Nested Queries Group functions are likely candidates for Nested Queries. For Example: SELECT lname, fname, phone, dept, salary FROM employees WHERE salary = (Select max(salary) from employees); The above query provides information on the employee with the highest salary. The inner query (shown in blue) will execute first and provide a numeric value which can be used to evaluate the condition on the outer query.

  26. Nested Queries (Example 2) A nested query can also be created by replacing the table name in a WHERE clause with a subquery. For example: SELECT count(dept) FROM (SELECT dept, avg(salary) FROM employees GROUP BY dept HAVING avg(salary) > 40000) The above query provides the number of departments that have an average salary greater than $40,000. The inner query produces a table. The outer query counts the rows In that table.

  27. Retrieving Info from Multiple Tables Programs Table Employees Table EMPID LANGUAGE PROFICIENCY EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 COBOL GOOD 23 Jones Mark M ITR 555-1087 45000 23 JAVA GOOD 25 Smith Sara F FINC 555-2222 55000 23 SQL EXCELLENT 26 Billings David M ACTG 555-4356 42000 31 SQL EXCELLENT 31 Dance Ivanna F ACTG 444-4887 60000 32 JAVA EXCELLENT 32 Jones Mary F ITR 555-8745 70000 32 SQL GOOD 35 Barker Bob M ACTG 555-6565 44000 32 VB GOOD 36 Woods Robin M ITR 555-9812 90000 32 COBOL EXCELLENT 37 Jones Mary F FINC 555-1234 56000 36 VB EXCELLENT 36 SQL EXCELLENT 36 JAVA EXCELLENT Languages Table 37 COBOL GOOD 37 SQL FAIR NAME FULLNAME COBOL COmmon Business Oriented Language JAVA JAVA SQL Structured Query Language VB Visual Basic

  28. Key Relational Operators • Join (Product) • Inner Join and Natural Inner Join • Outer Join and Natural Outer Join • Cross Join • Intersect • Minus (Difference) • Union • Divide (not implemented by most DBMS)

  29. Primary Key Foreign Key Pairs: The Basis for Joins Programs Table Employees Table EMPID LANGUAGE PROFICIENCY EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 COBOL GOOD 23 Jones Mark M ITR 555-1087 45000 23 JAVA GOOD 25 Smith Sara F FINC 555-2222 55000 23 SQL EXCELLENT 26 Billings David M ACTG 555-4356 42000 31 SQL EXCELLENT 31 Dance Ivanna F ACTG 444-4887 60000 32 JAVA EXCELLENT 32 Jones Mary F ITR 555-8745 70000 32 SQL GOOD 35 Barker Bob M ACTG 555-6565 44000 32 VB GOOD 36 Woods Robin M ITR 555-9812 90000 32 COBOL EXCELLENT 37 Jones Mary F FINC 555-1234 56000 36 VB EXCELLENT 36 SQL EXCELLENT 36 JAVA EXCELLENT Languages Table 37 COBOL GOOD 37 SQL FAIR NAME FULLNAME COBOL COmmon Business Oriented Language JAVA JAVA SQL Structured Query Language VB Visual Basic

  30. Query Illustrating Join Programs Table Employees Table EMPID LANGUAGE PROFICIENCY EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 COBOL GOOD 23 Jones Mark M ITR 555-1087 45000 23 JAVA GOOD 25 Smith Sara F FINC 555-2222 55000 23 SQL EXCELLENT 26 Billings David M ACTG 555-4356 42000 31 SQL EXCELLENT 31 Dance Ivanna F ACTG 444-4887 60000 32 JAVA EXCELLENT 32 Jones Mary F ITR 555-8745 70000 32 SQL GOOD 35 Barker Bob M ACTG 555-6565 44000 32 VB GOOD 36 Woods Robin M ITR 555-9812 90000 32 COBOL EXCELLENT 37 Jones Mary F FINC 555-1234 56000 36 VB EXCELLENT 36 SQL EXCELLENT 36 JAVA EXCELLENT 37 COBOL GOOD 37 SQL FAIR SELECT language, employees.empid, lname, fname, phone FROM programs INNER JOIN employees ON programs.empID= employees.empID

  31. Join Syntax Programs Table EMPID LANGUAGE PROFICIENCY Employees Table 23 COBOL GOOD 23 JAVA GOOD EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 SQL EXCELLENT 23 Jones Mark M ITR 555-1087 45000 31 SQL EXCELLENT 25 Smith Sara F FINC 555-2222 55000 32 JAVA EXCELLENT 26 Billings David M ACTG 555-4356 42000 32 SQL GOOD 31 Dance Ivanna F ACTG 444-4887 60000 32 VB GOOD 32 Jones Mary F ITR 555-8745 70000 32 COBOL EXCELLENT 35 Barker Bob M ACTG 555-6565 44000 36 VB EXCELLENT 36 Woods Robin M ITR 555-9812 90000 36 SQL EXCELLENT 37 Jones Mary F FINC 555-1234 56000 36 JAVA EXCELLENT 37 COBOL GOOD 37 SQL FAIR SELECT employees.empid, language, lname, fname, phone FROM programs INNER JOIN employees ON programs.empID= employees.empID

  32. Initial result set before projection and selection Programs Table Inner Join Employees Table EMPID LANGUAGE PROFICIENCY EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 COBOL GOOD 23 Jones Mark M ITR 555-1087 45000 23 JAVA GOOD 23 Jones Mark M ITR 555-1087 45000 23 SQL EXCELLENT 23 Jones Mark M ITR 555-1087 45000 31 SQL EXCELLENT 31 Dance Ivanna F ACTG 444-4887 60000 32 JAVA EXCELLENT 32 Jones Mary F ITR 555-8745 70000 32 SQL GOOD 32 Jones Mary F ITR 555-8745 70000 32 VB GOOD 32 Jones Mary F ITR 555-8745 70000 32 COBOL EXCELLENT 32 Jones Mary F ITR 555-8745 70000 36 VB EXCELLENT 36 Woods Robin M ITR 555-9812 90000 36 SQL EXCELLENT 36 Woods Robin M ITR 555-9812 90000 36 JAVA EXCELLENT 36 Woods Robin M ITR 555-9812 90000 37 COBOL GOOD 37 Jones Mary F FINC 555-1234 56000 37 SQL FAIR 37 Jones Mary F FINC 555-1234 56000 SELECT * FROM programs INNER JOIN employees ON programs.empID= employees.empID

  33. Join Result Set LANGUAGE EMPID LNAME FNAME PHONE COBOL 23 Jones Mark 555-1087 JAVA 23 Jones Mark 555-1087 SQL 23 Jones Mark 555-1087 SQL 31 Dance Ivanna 444-4887 JAVA 32 Jones Mary 555-8745 SQL 32 Jones Mary 555-8745 VB 32 Jones Mary 555-8745 COBOL 32 Jones Mary 555-8745 VB 36 Woods Robin 555-9812 SQL 36 Woods Robin 555-9812 JAVA 36 Woods Robin 555-9812 COBOL 37 Jones Mary 555-1234 SQL 37 Jones Mary 555-1234 SELECT language, employees.empid, lname, fname, phone FROM programs INNER JOIN employees ON programs.empID = employees.empID

  34. Further Selection of Joined Data LANGUAGE EMPID LNAME FNAME PHONE SQL 23 Jones Mark 555-1087 SQL 31 Dance Ivanna 444-4887 SQL 32 Jones Mary 555-8745 SQL 36 Woods Robin 555-9812 SQL 37 Jones Mary 555-1234 SELECT language, employees.empid, lname, fname, phone FROM programs INNER JOIN employees ON programs.empID = employees.empID WHERE Language = ‘SQL’

  35. OLD Inner Join Syntax LANGUAGE EMPID LNAME FNAME PHONE SQL 23 Jones Mark 555-1087 SQL 31 Dance Ivanna 444-4887 SQL 32 Jones Mary 555-8745 SQL 36 Woods Robin 555-9812 SQL 37 Jones Mary 555-1234 SELECT language,employees.empid, lname, fname, phone FROM programs, employees WHERE programs.empID= employees.empID AND Language = ‘SQL’ OLD SELECT language,employees.empid, lname, fname, phone FROM programs INNER JOIN employees ON programs.empID= employees.empID WHERE Language = ‘SQL’ NEW

  36. Three Table Inner Join Syntax SELECT programs.empid, name, fullname, phone FROM programs, employees, languages WHERE programs.empID= employees.empID AND languages.name = programs.language AND Proficiency = ‘Excellent’ OLD SELECT programs.empid, name, fullname, phone FROM programs INNER JOIN employees ON programs.empID= employees.empID INNER JOIN languages ONlanguages.name = programs.language WHERE Proficiency = ‘Excellent’ NEW

  37. New Natural Join Syntax (if column names are the same) LANGUAGE EMPID LNAME FNAME PHONE SQL 23 Jones Mark 555-1087 SQL 31 Dance Ivanna 444-4887 SQL 32 Jones Mary 555-8745 SQL 36 Woods Robin 555-9812 SQL 37 Jones Mary 555-1234 SELECT language, empid, lname, fname, phone FROM programs, employees WHERE programs.empID= employees.empID AND Language = ‘SQL’ OLD SELECT language, empid, lname, fname, phone FROM programs NATURAL INNER JOIN employees WHERE Language = ‘SQL’ NEW

  38. Outer Join Result Set LANGUAGE EMPID LNAME FNAME PHONE COBOL 23 Jones Mark 555-1087 JAVA 23 Jones Mark 555-1087 SQL 23 Jones Mark 555-1087 25 Smith Sara 555-2222 26 Billings David 555-4356 SQL 31 Dance Ivanna 444-4887 JAVA 32 Jones Mary 555-8745 SQL 32 Jones Mary 555-8745 VB 32 Jones Mary 555-8745 COBOL 32 Jones Mary 555-8745 35 Barker Bob 555-6565 VB 36 Woods Robin 555-9812 SQL 36 Woods Robin 555-9812 JAVA 36 Woods Robin 555-9812 COBOL 37 Jones Mary 555-1234 SQL 37 Jones Mary 555-1234 SELECT language, employees.empid, lname, fname, phone from programs RIGHT OUTER JOIN employees ON programs.empID = employees.empID

  39. Outer Join Syntax EMPID LNAME FNAME COUNT(Language) 23 Jones Mark 3 25 Smith Sara 0 26 Billings David 0 31 Dance Ivanna 1 32 Jones Mary 4 35 Barker Bob 0 36 Woods Robin 3 37 Jones Mary 2 SELECT employees.empID, lname, fname, count(language) FROM programs, employees WHERE programs.empID(+)= employees.empID GROUP BY employees.empID, lname, fname OLD SELECT employees.empID, lname, fname, count(language) FROM programs RIGHT OUTER JOIN employees ON programs.empID= employees.empID GROUP BY employees.empID, lname, fname NEW (Note: There is new FULL OUTER JOIN also which is a simultaneous LEFT and RIGHT outer JOIN.)

  40. Cross Join Syntax (e.g. JOIN every row in languages table with every row in employees table. 8x4=32 rows returned) SELECT lname, fname, languages.name FROM employees, languages; OLD SELECT lname, fname, languages.name FROM employees CROSS JOIN languages; NEW

  41. When not to join? If a client program needs data from two tables that does not necessarily mean that a join should occur. Unless the data needs to be viewed as a single table it may be appropriate to simple retrieve to result sets.

  42. Which Employees know both JAVA and COBOL?What is the Query? Programs Table Employees Table EMPID LANGUAGE PROFICIENCY EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 COBOL GOOD 23 Jones Mark M ITR 555-1087 45000 23 JAVA GOOD 25 Smith Sara F FINC 555-2222 55000 23 SQL EXCELLENT 26 Billings David M ACTG 555-4356 42000 31 SQL EXCELLENT 31 Dance Ivanna F ACTG 444-4887 60000 32 JAVA EXCELLENT 32 Jones Mary F ITR 555-8745 70000 32 SQL GOOD 35 Barker Bob M ACTG 555-6565 44000 32 VB GOOD 36 Woods Robin M ITR 555-9812 90000 32 COBOL EXCELLENT 37 Jones Mary F FINC 555-1234 56000 36 VB EXCELLENT 36 SQL EXCELLENT 36 JAVA EXCELLENT Languages Table 37 COBOL GOOD 37 SQL FAIR NAME FULLNAME COBOL COmmon Business Oriented Language JAVA JAVA SQL Structured Query Language VB Visual Basic

  43. Intersect Consider: Set R1 { a, b, d, f, h} Set R2 {b, c, f, k, p} Which elements are common between the two sets?

  44. Intersect Consider: Set R1 { a, b, d, f, h} Set R2 {b, c, f, k, p} Which elements are common between the two sets? h c b a k f d p

  45. Intersect Used for Desired Query:Programmers that know JAVA and COBOL JAVA programmers 36 SELECT empid FROM programs WHERE language = 'JAVA' INTERSECT SELECT empid FROM programs WHERE language = 'COBOL'; 32 23 37 COBOL programmers

  46. Minus Used for Desired Query:Programmers that know JAVA but not COBOL JAVA programmers 36 SELECT empid FROM programs WHERE language = 'JAVA' MINUS SELECT empid FROM programs WHERE language = 'COBOL'; 32 23 37 COBOL programmers

  47. Minus Used for Desired Query:Programmers that know COBOL but not JAVA COBOL programmers 37 SELECT empid FROM programs WHERE language = 'COBOL'; MINUS SELECT empid FROM programs WHERE language = 'JAVA' 32 23 36 JAVA programmers

  48. UNION can be used to combine elements (rows) from two sets Example: Construct a single list of customer and employee phone numbers for people that live in Radford. SELECT lname, fname, phone FROM customers WHERE city = 'Radford'; UNION SELECT lname, fname, phone FROM employees WHERE city = 'Radford' Fieldnames do not have to match but the first set of field Names will be used for the result set. The fields must be of the same data type.

  49. Combining Relational Operations Like any math operations, relational operations can be Combined to handle more complex situations. Example: Give the empid, First Name, Last Name, and number of Programming languages knows for all employees: SELECT empid, fname, lname, count(language) FROM employees NATURAL LEFT OUTER JOIN programs GROUP BY empid, fname, lname In this case an OUTER JOIN has been combined with a GROUP BY operation.

  50. Need for CASE statement EMPID PROFICIENCY 23 EXCELLENT 31 EXCELLENT 36 EXCELLENT 37 FAIR 32 GOOD SELECT empID, proficiency FROM programs WHERE Language = 'SQL' ORDER BY proficiency;

More Related