Advanced+SQL - Advanced SQL Douglas Havelka Miami...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
Advanced SQL Douglas Havelka Miami University Set Operators Use two or more sets (tables) to create new sets (tables) UNION INTERSECT MINUS Require union-compatible tables Same number of attributes Same attribute domains (datatypes) UNION Combines rows from two tables, without duplicate rows Combines the output of two SELECT statements Useful for combining databases Customers Products Example (from C7 problems) SELECT EMP.EMP_NUM, EMP.EMP_TITLE, EMP.EMP_LNAME FROM EMP WHERE EMP.EMP_NUM < 110 This yields 10 rows.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
SELECT EMPLOYEE.EMP_NUM, EMPLOYEE.EMP_TITLE, EMPLOYEE.EMP_LNAME FROM EMPLOYEE WHERE EMPLOYEE.EMP_NUM > 105; This yields 11 To combine the data from these tables, without duplicates: SELECT EMP.EMP_NUM, EMP.EMP_TITLE, EMP.EMP_LNAME FROM EMP WHERE EMP.EMP_NUM < 110 UNION SELECT EMPLOYEE.EMP_NUM, EMPLOYEE.EMP_TITLE, EMPLOYEE.EMP_LNAME FROM EMPLOYEE WHERE EMPLOYEE.EMP_NUM > 105; To combine the tables and keep duplicates, use UNION ALL SELECT EMP.EMP_NUM, EMP.EMP_TITLE, EMP.EMP_LNAME FROM EMP WHERE EMP.EMP_NUM < 110 UNION ALL SELECT EMPLOYEE.EMP_NUM, EMPLOYEE.EMP_TITLE, EMPLOYEE.EMP_LNAME FROM EMPLOYEE WHERE EMPLOYEE.EMP_NUM > 105; INTERSECT
Background image of page 2
This statement will combine two tables, and return only the duplicates SELECT EMP.EMP_NUM, EMP.EMP_TITLE, EMP.EMP_LNAME FROM EMP WHERE EMP.EMP_NUM < 110 INTERSECT SELECT EMPLOYEE.EMP_NUM, EMPLOYEE.EMP_TITLE, EMPLOYEE.EMP_LNAME FROM EMPLOYEE WHERE EMPLOYEE.EMP_NUM > 105; This can also be used to find specific data: SELECT CUSTOMER.CUS_CODE FROM CUSTOMER WHERE CUSTOMER.CUS_AREACODE = '615' intersect SELECT INVOICE.CUS_CODE FROM INVOICE This query will return customer codes for customers that have made a purchase in the 615 area code. MINUS Combines rows from two queries and returns only the rows that appear in the first set and not the second. SELECT EMP.EMP_NUM, EMP.EMP_TITLE, EMP.EMP_LNAME FROM EMP
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
WHERE EMP.EMP_NUM < 110 minus SELECT EMPLOYEE.EMP_NUM, EMPLOYEE.EMP_TITLE, EMPLOYEE.EMP_LNAME FROM EMPLOYEE WHERE EMPLOYEE.EMP_NUM > 105; This returns 6 rows, 100 to 105. Reverse the order to find the employees in second table only. This can also be useful when searching the database for specific data: SELECT CUSTOMER.CUS_CODE FROM CUSTOMER WHERE CUSTOMER.CUS_AREACODE = '615' minus SELECT INVOICE.CUS_CODE FROM INVOICE This results in customers in the 615 area that have NOT bought anything. These results can be achieved using different approaches: SELECT CUSTOMER.CUS_CODE FROM CUSTOMER WHERE CUSTOMER.CUS_AREACODE = '615' and CUSTOMER.CUS_CODE in (SELECT distinct CUSTOMER.CUS_CODE from INVOICE) And for minus SELECT CUSTOMER.CUS_CODE FROM CUSTOMER
Background image of page 4
WHERE CUSTOMER.CUS_AREACODE = '615' and CUSTOMER.CUS_CODE not in (SELECT distinct CUSTOMER.CUS_CODE from INVOICE) Join Operators Joins merge rows from two tables Use several conditions Common value in common attribute Meet a condition = or <> Common values or no match Most common join matches primary and foreign key SELECT * FROM CUSTOMER, INVOICE WHERE CUSTOMER.CUS_CODE =
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 19

Advanced+SQL - Advanced SQL Douglas Havelka Miami...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online