UNION set operator: The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values. Syntax: QUERY UNION QUERY; Consider two tables: Table creation: CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY); CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE)); Inserting values: INSERT INTO VENDOR VALUES(123); INSERT INTO VENDOR VALUES(124); INSERT INTO VENDOR VALUES(125); INSERT INTO VENDOR VALUES(126); INSERT INTO PRODUCT VALUES('ABC', 125); INSERT INTO PRODUCT VALUES('DEF', 124); INSERT INTO PRODUCT VALUES('GHI', 124); INSERT INTO PRODUCT VALUES('JKL', 123);

Question

The table STUDENT has the attributes STUDENT_ID, NAME, S_ID, and ADDR. The table SCHOOL has the attributes SCHOOL_ID, NAME, and STATE_CODE. Assume that there is a 1:N relation between SCHOOL and STUDENT where the common attributes are S_ID, and SCHOOL_ID. Which of the following creates a table that shows the STUDENT attributes and the corresponding SCHOOL attributes? a. SELECT * FROM STUDENT JOIN SCHOOL USING(SCHOOL_ID) b. SELECT * FROM STUDENT JOIN SCHOOL USING(S_ID) c. SELECT * FROM STUDENT NATURAL JOIN SCHOOL ON S_ID AND SCHOOL.SCHOOL_ID d. SELECT * FROM STUDENT JOIN SCHOOL ON STUDENT.S_ID = SCHOOL.SCHOOL_ID

Answer 1

Question

Chapter 7, Problem 23RQ

(a)

Program Plan Intro

UNION set operator:

The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.

Syntax: QUERY UNION QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(b)

Program Plan Intro

UNION ALL set operator:

The UNION ALL set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains duplicate values.

Syntax: QUERY UNION ALL QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(c)

Program Plan Intro

INTERSECT set operator:

The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.

Syntax: QUERY INTERSECT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(d)

Program Plan Intro

EXCEPT/ MINUS set operator:

The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.

Syntax: QUERY EXCEPT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Students have asked these similar questions

The table STUDENT has the attributes STUDENT_ID, NAME, S_ID, and ADDR. The table SCHOOL has the attributes SCHOOL_ID, NAME, and STATE_CODE. Assume that there is a 1:N relation between SCHOOL and STUDENT where the common attributes are S_ID, and SCHOOL_ID. Which of the following creates a table that shows the STUDENT attributes and the corresponding SCHOOL attributes? a. SELECT * FROM STUDENT JOIN SCHOOL USING(SCHOOL_ID) b. SELECT * FROM STUDENT JOIN SCHOOL USING(S_ID) c. SELECT * FROM STUDENT NATURAL JOIN SCHOOL ON S_ID AND SCHOOL.SCHOOL_ID d. SELECT * FROM STUDENT JOIN SCHOOL ON STUDENT.S_ID = SCHOOL.SCHOOL_ID

The table STUDENT has the attributes STUDENT_ID, NAME, SCHOOL_ID, and ADDR. The table SCHOOL has the attributes SCHOOL_ID, NAME, and STATE_CODE. Assume that there is a 1:N relation between SCHOOL and STUDENT. Which of the following creates a table that shows the STUDENT attributes and the corresponding SCHOOL attributes? a. SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL b. SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL USING(SCHOOL_ID) c. SELECT * SCHOOL_ID FROM STUDENT NATURAL JOIN SCHOOL d. SELECT * SCHOOL_ID FROM STUDENT, SCHOOL WHERE SCHOOL_ID = SCHOOL_ID

Convert the following relation into First Normal Form. If necessary, split into multiple tables to avoid redundancy anomaly. Product Name Color Maker Price Category ABC Red Yellow X 15 Toy Luxury PQR Blue Green Y 10 Luxury DEF Yellow Green X 15 Gadget Hardware

Answer 2

Question

Chapter 7, Problem 23RQ

(a)

Program Plan Intro

UNION set operator:

The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.

Syntax: QUERY UNION QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(b)

Program Plan Intro

UNION ALL set operator:

The UNION ALL set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains duplicate values.

Syntax: QUERY UNION ALL QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(c)

Program Plan Intro

INTERSECT set operator:

The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.

Syntax: QUERY INTERSECT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(d)

Program Plan Intro

EXCEPT/ MINUS set operator:

The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.

Syntax: QUERY EXCEPT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 3

Question

Chapter 7, Problem 23RQ

(a)

Program Plan Intro

UNION set operator:

The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.

Syntax: QUERY UNION QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(b)

Program Plan Intro

UNION ALL set operator:

The UNION ALL set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains duplicate values.

Syntax: QUERY UNION ALL QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(c)

Program Plan Intro

INTERSECT set operator:

The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.

Syntax: QUERY INTERSECT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(d)

Program Plan Intro

EXCEPT/ MINUS set operator:

The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.

Syntax: QUERY EXCEPT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 4

Question

Chapter 7, Problem 23RQ

(a)

Program Plan Intro

UNION set operator:

The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.

Syntax: QUERY UNION QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(b)

Program Plan Intro

UNION ALL set operator:

The UNION ALL set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains duplicate values.

Syntax: QUERY UNION ALL QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(c)

Program Plan Intro

INTERSECT set operator:

The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.

Syntax: QUERY INTERSECT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(d)

Program Plan Intro

EXCEPT/ MINUS set operator:

The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.

Syntax: QUERY EXCEPT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 5

Chapter 7, Problem 23RQ

(a)

Program Plan Intro

UNION set operator:

The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.

Syntax: QUERY UNION QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(b)

Program Plan Intro

UNION ALL set operator:

The UNION ALL set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains duplicate values.

Syntax: QUERY UNION ALL QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(c)

Program Plan Intro

INTERSECT set operator:

The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.

Syntax: QUERY INTERSECT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

(d)

Program Plan Intro

EXCEPT/ MINUS set operator:

The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.

Syntax: QUERY EXCEPT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 6

Chapter 7, Problem 23RQ

(a)

Program Plan Intro

UNION set operator:

The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.

Syntax: QUERY UNION QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 7

Chapter 7, Problem 23RQ

(a)

Program Plan Intro

UNION set operator:

The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.

Syntax: QUERY UNION QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 8

(b)

Program Plan Intro

UNION ALL set operator:

The UNION ALL set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains duplicate values.

Syntax: QUERY UNION ALL QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 9

(b)

Program Plan Intro

UNION ALL set operator:

The UNION ALL set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains duplicate values.

Syntax: QUERY UNION ALL QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 10

(c)

Program Plan Intro

INTERSECT set operator:

The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.

Syntax: QUERY INTERSECT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 11

(c)

Program Plan Intro

INTERSECT set operator:

The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.

Syntax: QUERY INTERSECT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 12

(d)

Program Plan Intro

EXCEPT/ MINUS set operator:

The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.

Syntax: QUERY EXCEPT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 13

(d)

Program Plan Intro

EXCEPT/ MINUS set operator:

The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.

Syntax: QUERY EXCEPT QUERY;

Consider two tables:

Table creation:

CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);

CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));

Inserting values:

INSERT INTO VENDOR VALUES(123);

INSERT INTO VENDOR VALUES(124);

INSERT INTO VENDOR VALUES(125);

INSERT INTO VENDOR VALUES(126);

INSERT INTO PRODUCT VALUES('ABC', 125);

INSERT INTO PRODUCT VALUES('DEF', 124);

INSERT INTO PRODUCT VALUES('GHI', 124);

INSERT INTO PRODUCT VALUES('JKL', 123);

Answer 14

Expert Solution & Answer

Answer 15

Expert Solution & Answer

Answer 16

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 17

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 18

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 19

Trending nowThis is a popular solution!

Answer 20

Trending nowThis is a popular solution!

Answer 21

Trending nowThis is a popular solution!

Answer 22

Trending now

Answer 23

This is a popular solution!

Answer 24

See solution

Check out sample textbook solution

Answer 25

Ch. 7 - Prob. 1RQ Ch. 7 - Explain why the following command would create an...Ch. 7 - What is a cross join? Give an example of its...Ch. 7 - What three join types are included in the outer...Ch. 7 - Using tables named T1 and T2, write a query...Ch. 7 - Prob. 6RQ Ch. 7 - Rewrite the following WHERE clause without the use...Ch. 7 - Explain the difference between an ORDER BY clause...Ch. 7 - Explain why the following two commands produce...Ch. 7 - What is the difference between the COUNT aggregate...

Solution Summary: The author describes the UNION set operator, which is used to combine the output of two or more queries and produce a result.

Chapter 7 Solutions