Wrong/copied/incomplete answers will be flagged.
Write SQL queries to retrieve data from given tables Consider tables, of a Supplier-Parts DB, shown in Figure 1 Names of tables, columns and identification of primary and foreign keys are shown. As the content of the rows/tuples of tables/relations is transient (it changes over time) and the following questions ask you to write SQL statements to retrieve certain data/information from the tables, sample tuples/rows are not shown Questions in this part ask you to write SQL queries that would retrieve certain information from the tables Figure 1: Relation S... contains information about suppliers SNAME SNO Supplier number (PK) primary key.. varchar STATUS Supplier status (e.g, reliability code between 1 and5 with 1 being best, 5 worst)int CITY Supplier name varchar , supplier's location varchar Relation P..contains information about parts Weight supplier location PNO PNAME Name of the part varchar COLOR Part number (PK) primary key Part color Relation SP ...contains information about which parts are supplied by which suppliers in which quantities SNO Supplier number (PK) (FK) part of primary key, foreign key .. varchar PNO QTY Part color int Part number (PK) (FK) part of primary key, foreign key . varchar MORE COMPACT FORM SN IG SP SNO PNO Example: Find information (write SQL statement to find information) about suppliers who are located in London. Display/show/retrieve the supplier numbers and names Answer: SELECT sno, sname FROM s WHERE city = London Questions: 1 Find information about suppliers located in London who supply part P2. The query (SQL statement) should retrieve the supplier numbers and names. 2 Find information about red parts that are supplied by any suppliers located in Paris. Retrieve the supplier numbers and names and also the corresponding red parts they supply a. To join tables, you may only use the JOIN-USING variation of a join. b. To join tables, you may only use the JOIN-ON variation of a join c. To join tables, you may only use the NATURAL-JOIN variation of a join. d. To join tables, you may only use the CROSS-JOIN version with WHERE clause. In other words, your SQL statement will not have a keyword "JOIN" in it in any form. 3 For each red part, display information about which suppliers supply it, if any. In other words, if a part is not supplied by any supplier, it still must appear in the result. (Hint: Use either LEFT or RIGHT OUTERJOIN.) Write SQL queries to retrieve data from given tables Consider tables, of a Supplier-Parts DB, shown in Figure 1 Names of tables, columns and identification of primary and foreign keys are shown. As the content of the rows/tuples of tables/relations is transient (it changes over time) and the following questions ask you to write SQL statements to retrieve certain data/information from the tables, sample tuples/rows are not shown Questions in this part ask you to write SQL queries that would retrieve certain information from the tables Figure 1: Relation S... contains information about suppliers SNAME SNO Supplier number (PK) primary key.. varchar STATUS Supplier status (e.g, reliability code between 1 and5 with 1 being best, 5 worst)int CITY Supplier name varchar , supplier's location varchar Relation P..contains information about parts Weight supplier location PNO PNAME Name of the part varchar COLOR Part number (PK) primary key Part color Relation SP ...contains information about which parts are supplied by which suppliers in which quantities SNO Supplier number (PK) (FK) part of primary key, foreign key .. varchar PNO QTY Part color int Part number (PK) (FK) part of primary key, foreign key . varchar MORE COMPACT FORM SN IG SP SNO PNO Example: Find information (write SQL statement to find information) about suppliers who are located in London. Display/show/retrieve the supplier numbers and names Answer: SELECT sno, sname FROM s WHERE city = London Questions: 1 Find information about suppliers located in London who supply part P2. The query (SQL statement) should retrieve the supplier numbers and names. 2 Find information about red parts that are supplied by any suppliers located in Paris. Retrieve the supplier numbers and names and also the corresponding red parts they supply a. To join tables, you may only use the JOIN-USING variation of a join. b. To join tables, you may only use the JOIN-ON variation of a join c. To join tables, you may only use the NATURAL-JOIN variation of a join. d. To join tables, you may only use the CROSS-JOIN version with WHERE clause. In other words, your SQL statement will not have a keyword "JOIN" in it in any form. 3 For each red part, display information about which suppliers supply it, if any. In other words, if a part is not supplied by any supplier, it still must appear in the result. (Hint: Use either LEFT or RIGHT OUTERJOIN.)
