Question
I need part c!!!!!
4. (28 points) Assume the following design for a suppliers-and-parts database deinitions. The and table SP, which represents shipments of parts by suppliers. Each supplier hasa database consists of three tables-table S, which represents suppliers, table P, which represents parts, unique supplier number (S#) and each part has a unique part number (P#). In addition, we assume that it is not possible for two shipments to exist at the same time for the sane supplier and the same part, so that each shipment has a unique supplier-number/part-number combination CREATE TABLE S CHAR (5) NOT NULL, SNAME CHAR (20) NOT NULL, STATUS INT NOT NULL, CITY CHAR(15) NOT NULL, PRIMARY KEY (S#) ); CREATE TABLE P ( PNAME COLOR WEIGHT CITY CHAR(6) NOT NULL, CHAR (20) NOT NULL, CHAR (6) NOT NULL, INT NOT NULL, CHAR(15) NOT NULL, PRIMARY KEY (PM) ); CREATE TABLE SP S# P# QTY CHAR (5) NOT NULL, CHAR (6) NOT NULL, INT NOT NULL, PRIMARY KEY (S#, P#), FOREIGN KEY (S#) REFERENCES S, FOREIGN KEY (P#) REFERENCES P); Please write SQL Statements for following queries. (a) Get supplier numbers for suppliers in Paris with status > 20. (b) Get all combinations of supplier and part information such that the supplier and part in question are located in the same city. stored in the second city. For example, supplier S1 supplies part P1; supplier S1 located in London, and part P1 is stored in Paris; so (London, Paris) is a pair of cities in the result (c) Get all pairs of city names, such that a supplier located in the first city supplies a part (d) Get all pairs of supplier numbers such that the two suppliers concerned are CO-located in the same city.
Explanation / Answer
Solution:
Query:
SELECT S.CITY, P.CITY FROM S INNER JOIN SP ON S.S#= SP.S# INNER JOIN P ON P.P#=SP.P# WHERE S.CITY!=P.CITY GROUP BY S.CITY, P.CITY
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)