SET OPERATIONS IN SQL

Views:
 
Category: Education
     
 

Presentation Description

No description available.

Comments

By: sthitipragyandas (83 month(s) ago)

i want to download your ppt on cyber crime.........plz

Presentation Transcript

SET OPERATIONS IN SQL:

SET OPERATIONS IN SQL S.THARANI DEVI 3 rd BCA

INTRODUCTION:

INTRODUCTION SQL set operators allows combine results from two or more SELECT statements combine rows from different queries with strong preconditions - all involved SELECTS must: retrieve the same number of columns and the data types of corresponding columns in each involved SELECT must be compatible.

SET OPERATION-UNION:

SET OPERATION-UNION The UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements . The syntax for a UNION query is: select field1, field2, . field_n from tables UNION select field1, field2, . field_n from tables;

UNION EXAMPLES:

UNION EXAMPLES Example-1: EMPLOYE NORWAY : EMPLOYE USA : E_ID F_NAME 101 ANNA 102 CATRIN 103 VINTA E_ID F_NAME 101 HARIL 102 JACK 103 VINTA

PowerPoint Presentation:

Query: SELECT f_name FROM employe_norway UNION SELECT f_name FROM employe_usa ; EMPLOYE_DETAILS F_NAME ANNA CATRIN VINTA HARIL JACK

PowerPoint Presentation:

Example-2: [UNION ALL] The syntax for a UNION ALL query is: select field1, field2, .. field_n from tables UNION ALL select field1, field2, .. field_n from tables;

PowerPoint Presentation:

EMPLOYE NORWAY : EMPLOYE USA : E_ID F_NAME 101 ANNA 102 CATRIN 103 VINTA E_ID F_NAME 101 HARIL 102 JACK 103 VINTA

PowerPoint Presentation:

Query: SELECT f_name FROM employe_norway UNION ALL SELECT f_name FROM employe_usa ; EMPLOYE_DETAILS F_NAME ANNA CATRIN VINTA HARIL JACK VINTA

PowerPoint Presentation:

Example-3 AUTHOUR CITY STATE ZIP_CODE Nashville TN 37215 Lawrence KS 66044 Corvallis OR 97330

PowerPoint Presentation:

Query: SELECT City, State, Zip_c0de FROM Authors WHERE State IN ('KS', 'TN') UNION SELECT City, State, Zip_code FROM Authors WHERE State IN ('OR' 'TN'); AUTHOUR CITY STATE ZIP_CODE Nashville TN 37215 Lawrence KS 66044 Corvallis OR 97330

PowerPoint Presentation:

Example-4 AUTHOUR CITY STATE ZIP_CODE Nashville TN 37215 Lawrence KS 66044 Corvallis OR 97330

PowerPoint Presentation:

Query: SELECT City, State, Zip_code FROM Authors WHERE State IN ('KS', 'TN') UNION ALL SELECT City, State, Zip _code FROM Authors WHERE State IN ('OR' 'TN'); AUTHOUR CITY STATE ZIP_CODE Nashville TN 37215 Lawrence KS 66044 Corvallis OR 97330 Nashville TN 37215

PowerPoint Presentation:

Example-5 DEPOSITER BORROWER CUS_NAME A/C_NO JOHN A-101 SMITH A-215 JONES A-217 CUS_NAME L_NO JOHN L-17 SMITH L-23 ADAMS L-16

PowerPoint Presentation:

Query: SELECT cus_name FROM depositer UNION SELECT cus_name FROM borrower; CUSTOMER_DETAILS CUS_NAME JOHN SMITH JONES ADAMS

PowerPoint Presentation:

Example-6 DEPOSITER BORROWER CUS_NAME A/C_NO JOHN A-101 SMITH A-215 JONES A-217 CUS_NAME L_NO JOHN L-17 SMITH L-23 ADAMS L-16

PowerPoint Presentation:

Query: SELECT cus_name FROM depositer UNION ALL SELECT cus_name FROM borrower; CUSTOMER_DETAILS CUS_NAME JOHN SMITH JONES JOHN SMITH ADAMS

INTERSECT:

INTERSECT The INTERSECT query allows to return the results of 2 or more "select" queries. It only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. The syntax for an INTERSECT query is: select field1, field2, . field_n from tables INTERSECT select field1, field2, . field_n from tables;

INTERSECT EXAMPLES:

INTERSECT EXAMPLES Example-1 EMPLOYE NORWAY EMPLOYE USA E_ID F_NAME 101 ANNA 102 CATRIN 103 VINTA E_ID F_NAME 101 HARIL 102 JACK 103 VINTA

PowerPoint Presentation:

Query: SELECT f_name FROM employe_norway INTERSECT SELECT f_name FROM employe_usa ; EMPLOYE_DETAILS F_NAME VINTA

PowerPoint Presentation:

Example-2: [INTERSECT ALL] The syntax for a INTERSECT ALL query is: select field1, field2, .. field_n from tables INTERSECT ALL select field1, field2, .. field_n from tables;

PowerPoint Presentation:

EMPLOYE NORWAY EMPLOYE USA E_NO F_NAME 101 SMITH 102 JACK 103 SMITH 104 USHATH 105 SMITH E_NO F_NAME 101 ADAM 102 SMITH 103 CURRY 104 SMITH 105 WILLIAM

PowerPoint Presentation:

Query : SELECT f_name FROM employe_norway INTERSECT ALL SELECT f_name FROM employe_usa ; EMPLOYE_DETAILS F_NAME SMITH SMITH

PowerPoint Presentation:

Example-3 STORE_INFO SALES_INFO NAME SALES DATE ROSE 150 12-09-97 LILLY 200 20-09-97 JASMINE 500 17-09-97 LILLY 370 19-09-97 DATE SALES 19-09-97 370 18-09-97 500 06-09-97 300 14-09-97 250

PowerPoint Presentation:

QUERY: SELECT date FROM store_info INTERSECT SELECT date FROM sales_info ; DATE-DETAILS DATE 19-09-97

PowerPoint Presentation:

Example-4

PowerPoint Presentation:

Query: SELECT value FROM t_1 INTERSECT SELECT value FROM t_2;

PowerPoint Presentation:

Example-5

PowerPoint Presentation:

Query: SELECT value FROM t_1 INTERSECT ALL SELECT value FROM t_2;

EXCEPT:

EXCEPT EXCEPT returns any distinct values from the left query that are not also found on the right query. The syntax for EXCEPT query is: select field1, field2, . field_n from tables EXCEPT select field1, field2, . field_n from tables;

PowerPoint Presentation:

Example-1 EMPLOYE NORWAY EMPLOYE USA E_NO F_NAME 101 SMITH 102 JACK 103 SMITH 104 USHATH 105 SMITH E_NO F_NAME 101 ADAM 102 SMITH 103 CURRY 104 SMITH 105 WILLIAM

PowerPoint Presentation:

Query: SELECT f_name FROM employe_norway EXCEPT SELECT f_name FROM employe_usa ; EMPLOYE_DETAILS F_NAME JACK USHANTH

PowerPoint Presentation:

Example-2 EMPLOYE NORWAY EMPLOYE USA E_NO F_NAME 101 SMITH 102 JACK 103 SMITH 104 USHATH 105 SMITH E_NO F_NAME 101 ADAM 102 SMITH 103 CURRY 104 SMITH 105 WILLIAM

PowerPoint Presentation:

Query: SELECT f_name FROM employe_usa EXCEPT SELECT f_name FROM employe_norway ; EMPLOYE_DETAILS F_NAME ADAM CURRY WILLIAM

PowerPoint Presentation:

Example-3 EMPLOYE NORWAY EMPLOYE USA E_NO F_NAME 101 SMITH 102 JACK 103 SMITH 104 USHATH 105 SMITH E_NO F_NAME 101 ADAM 102 SMITH 103 CURRY 104 SMITH 105 WILLIAM

PowerPoint Presentation:

Query : SELECT f_name FROM employe_norway EXCEPT ALL SELECT f_name FROM employe_usa ; EMPLOYE_DETAILS F_NAME JACK USHANTH ADAM CURRY WILLIAM

PowerPoint Presentation:

Example-4

PowerPoint Presentation:

Query: SELECT value FROM t_1 EXCEPT SELECT value FROM t_2;

PowerPoint Presentation:

Example-5

PowerPoint Presentation:

Query: SELECT value FROM t_2 EXCEPT SELECT value FROM t_1;

PowerPoint Presentation:

Example-6

PowerPoint Presentation:

Query: SELECT value FROM t_1 EXCEPT ALL SELECT value FROM t_2;

authorStream Live Help