23b01a4231
CREATE TABLE Supplier(
sid INT ,
Sname VARCHAR(50),
city VARCHAR(50)
);
CREATE TABLE Parts(
pid INT ,
pname VARCHAR(50),
color VARCHAR(50)
);
CREATE TABLE Catalog(
sid INT ,
pid INT,
price FLOAT
);
INSERT INTO Supplier (sid, sname, city) VALUES
(1, 'Acmecorp', 'NewYork');
INSERT INTO Supplier (sid, sname, city) VALUES
(2, 'Global parts', 'Chicago');
INSERT INTO Supplier (sid, sname, city) VALUES
(3, 'XYZsuppliers', 'Los angels');
INSERT INTO Supplier (sid, sname, city) VALUES
(4, 'Universal suppliers', 'Houston');
INSERT INTO Supplier (sid, sname, city) VALUES
(5, 'Mega tools', 'Phoneix');
INSERT INTO Supplier (sid, sname, city) VALUES
(6, 'Quick tools', 'Seattle');
SELECT *From Supplier;
output:
Page no:
23b01a4231
INSERT INTO Parts (pid, pname, color) VALUES
(101, 'Bolt', 'silver');
INSERT INTO Parts (pid, pname, color) VALUES
(102, 'Nut', 'Black');
INSERT INTO Parts (pid, pname, color) VALUES
(103, 'washer','silver');
INSERT INTO Parts (pid, pname, color) VALUES
(104, 'screw', 'Gold');
INSERT INTO Parts (pid, pname, color) VALUES
(105, 'pin', 'bronze');
INSERT INTO Parts (pid, pname, color) VALUES
(106, 'clip', 'silver');
SELECT *From Parts;
output:
INSERT INTO Catalog (sid, pid,price) VALUES
(1, 101,5.00);
INSERT INTO Catalog (sid, pid,price) VALUES
(1, 102, 2.50);
INSERT INTO Catalog (sid, pid,price) VALUES
(2, 101, 4.75);
INSERT INTO Catalog (sid, pid,price) VALUES
(2, 103, 3.00);
INSERT INTO Catalog (sid, pid,price) VALUES
(3, 104, 7.25);
INSERT INTO Catalog (sid, pid,price) VALUES
(4, 105, 3.50);
INSERT INTO Catalog (sid, pid,price) VALUES
(5, 106, 2.25);
INSERT INTO Catalog (sid, pid,price) VALUES
Page no:
23b01a4231
(6, 101, 5.50);
INSERT INTO Catalog (sid, pid,price) VALUES
(6, 103, 3.75);
INSERT INTO Catalog (sid, pid,price) VALUES
(6, 104, 7.00);
SELECT *FROM Catalog;
output:
[Link] All Suppliers in 'Chicago'
SELECT Sname FROM Supplier WHERE city='Chicago';
output:
2. Count the Total Number of Supplier
SELECT COUNT(sid) FROM Supplier;
Output:
[Link] the Total Number of Different Parts
SELECT COUNT(DISTINCT(pid)) FROM Parts;
output:
Page no:
23b01a4231
[Link] All Supplier Name.
SELECT Sname FROM Supplier;
output:
[Link] All Supplier Name.
SELECT pname FROM Parts;
output:
[Link] All Parts That Are Silver in Color
SELECT pname FROM Parts WHERE color='silver';
output:
Page no:
23b01a4231
[Link] the Price of a Specific Part ('Nut')
SELECT [Link],[Link]
FROM Catalog c JOIN Parts p
ON [Link]=[Link]
WHERE [Link]='Nut';
output:
[Link] All Suppliers Located in 'New York'
SELECT Sname FROM Supplier WHERE city='NewYork'
[Link] All Distinct Cities Where Suppliers Are Located
SELECT DISTINCT(city) FROM Supplier
Page no:
23b01a4231
[Link] the Number of Suppliers in 'Seattle'
SELECT COUNT(city) FROM Supplier WHERE city='Seattle'
[Link] All Parts Supplied by 'Global Parts' and Their Prices
SELECT [Link],[Link]
FROM Parts p JOIN Catalog c
ON [Link]=[Link] JOIN Supplier s
ON [Link]=[Link] WHERE [Link]='Global parts';
[Link] the Names of All Parts Supplied by 'QuickParts'
SELECT [Link]
FROM Parts p JOIN Catalog c
ON [Link]=[Link] JOIN Supplier s
ON [Link]=[Link] WHERE [Link]='Quick tools';
Page no:
23b01a4231
13. Find the Average Price of 'Screw'
SELECT AVG(price)
FROM Parts p JOIN Catalog c ON [Link]=[Link]
WHERE [Link]='screw';
[Link] the Suppliers Who Supply Silver-Colored Parts
SELECT [Link]
FROM Supplier s JOIN Catalog c
ON [Link]=[Link] JOIN Parts p
ON [Link]=[Link] WHERE [Link]='silver';
15. Find the Price of the Cheapest 'Bolt'
SELECT MIN(price)
FROM Parts p JOIN Catalog c
ON [Link]=[Link] WHERE [Link]='Bolt';
Page no:
23b01a4231
[Link] parts priced between 100 and 600.
SELECT price FROM Catalog WHERE price BETWEEN 100 AND 600;
[Link] all parts supplied by suppliers in "New York" or "Chicago."
SELECT [Link]
FROM Parts p JOIN Catalog c
ON [Link]=[Link] JOIN Supplier s
ON [Link]=[Link] WHERE [Link]='NewYork' OR [Link]='Chicago';
18 Show all parts, even if they are not supplied by any supplier.
SELECT [Link], [Link]
FROM Parts p
LEFT JOIN Catalog c ON [Link]= [Link];
Page no:
23b01a4231
[Link] all suppliers, even those that do not supply parts.
SELECT [Link]
FROM Supplier s
LEFT JOIN Catalog c ON [Link]= [Link];
[Link] only suppliers that supply more than 1 part.
SELECT [Link], [Link]
FROM Supplier s
JOIN Catalog c ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 1;
Page no:
23b01a4231
[Link] the number of parts supplied by each supplier.
SELECT [Link], [Link], COUNT([Link])
FROM Supplier s
JOIN Catalog c ON [Link] = [Link]
GROUP BY [Link], [Link];
[Link] all parts in alphabetical order.
SELECT pid, pname
FROM Parts
ORDER BY pname ASC;
Page no:
23b01a4231
23. Find the Most Expensive Part
SELECT [Link], [Link]
FROM Parts P JOIN Catalog C ON [Link] = [Link]
ORDER BY [Link] DESC
LIMIT 1;
Page no: