Databases: SQL example 3

Given a database to register suppliers and parts. We know the location of the suppliers (Supplier.city) and the parts (Parts.city), and how many parts they have stored at each location. The Part table is a description of a possible existence of a part at a given location, while the SuppliesPart shows if it exists (quantity > 0).

The database consists of three tables with SQL-definitions:

CREATE TABLE Supplier (
    supplierID INT NOT NULL PRIMARY KEY,
    supplier_name CHAR(5),
    status INT,
    city CHAR(6)
);

CREATE TABLE Part (
    partID INT NOT NULL PRIMARY KEY,
    part_name CHAR(5),
    color CHAR(5),
    weight DECIMAL(3,1),
    city CHAR(6)
);

CREATE TABLE SuppliesPart (
    supplierID INT NOT NULL,
    partID INT NOT NULL,
    quantity INT,
    PRIMARY KEY (supplierID, partID),
    FOREIGN KEY (supplierID) REFERENCES Supplier(supplierID),
    FOREIGN KEY (partID) REFERENCES Part(partID)
);

We will:

1. Write an SQL-query which finds all suppliers with a status of more than 15.
2. Write an SQL-query which finds name and location of all suppliers of parts called «Screw».
3. Write an SQL-query which finds partID and name for all parts supplied by more than one supplier.
4. Write an SQL-query which finds the total number of suppliers.
5. Write an SQL-query which finds the cities for all suppliers that supply parts with a weight of more than 10.0.
6. Write an SQL-query which finds name for suppliers for suppliers which do not supply «Screws». Sort the result and keep only distinct values.

SELECT supplier_name FROM Supplier WHERE status > 15;

SELECT supplier_name, Supplier.city
FROM Supplier s, SuppliesPart sp, Part p
WHERE s.supplierID = sp.supplierID AND sp.partID = p.partID AND p.part_name = 'Screw';

Or…

SELECT supplier_name, city
FROM Supplier
WHERE supplierID IN (
	SELECT supplierID
	FROM SupplierPart sp, Part p
	WHERE sp.partID = p.partID AND part_name='Screw');

Or…

SELECT supplier_name, city FROM SuppliesPart AS SP
INNER JOIN Part AS P ON P.partID = SP.partID
INNER JOIN Supplier AS S ON S.supplierID = SP.supplierID 
WHERE part_name = 'Screw';

SELECT partID, part_name FROM SuppliesPart AS SP
INNER JOIN Part AS P ON P.partID = SP.partID
INNER JOIN Supplier AS S ON S.supplierID = SP.supplierID 
GROUP BY partID HAVING COUNT(*) > 1;

SELECT COUNT(*) FROM Supplier; 

SELECT Supplier.city
FROM Supplier s, Part p, SuppliesPart sp
WHERE s.supplierID = sp.supplierID AND sp.partID = p.partID AND p.weight >10;

Or…

SELECT city FROM SuppliesPart AS SP
INNER JOIN Supplier AS S ON S.supplierID = SP.supplierID
INNER JOIN (SELECT partID FROM Part WHERE weight > 10) AS T ON T.partID = SP.partID;

SELECT DISTINCT supplier_name
WHERE supplierID NOT IN (
	SELECT sp.supplierID
	FROM SuppliesPart sp, Part p
	WHERE sp.partID = p.partID AND p.part_name = 'Screw')
ORDER BY supplier_name;

Or…

SELECT DISTINCT supplier_name FROM Supplier
EXCEPT ( 
    SELECT DISTINCT supplier_name FROM SuppliesPart AS SP
    INNER JOIN Part AS P ON P.partID = SP.partID
    INNER JOIN Supplier AS S ON S.supplierID = SP.supplierID WHERE part_name = 'Screw'
)
ORDER BY supplier_name;