Lab Cycle 1 — DDL Commands, Schema Management & Constraints

-- 1. Insert records into Supplier table
INSERT INTO Supplier (SID, Sname, branch, city, phone) VALUES
(204001, 'Vandana', 'global', 'Delhi', '9811001122'),
(204002, 'Rajesh', 'local', 'Delhi', '9822002233'),
(204003, 'Mike', 'main', 'Mumbai', '9833003344'),
(204004, 'Alok', 'local', 'Bangalore', '9844004455'),
(204005, 'Veda', 'global', 'Delhi', '9855005566'),
(204006, 'Priya', 'main', 'Chennai', '9866006677'),
(204007, 'Kiran', 'local', 'Kolkata', '9877007788'),
(204008, 'Geeta', 'global', 'Delhi', '9888008899'),
(204009, 'Rohan', 'main', 'Pune', '9899009900'),
(204010, 'Anita', 'local', 'Delhi', '9810101010');

-- Insert records into Part table
INSERT INTO Part (PID, Pname, color, price) VALUES
(101, 'NUT', 'silver', 250.00),
(102, 'BOLT', 'silver', 300.00),
(103, 'SCREW', 'red', 150.00),
(104, 'GEAR_NET', 'black', 550.00),
(105, 'WASHER', 'red', 400.00),
(106, 'BEARING', 'green', 500.00),
(107, 'FILTER_NET', 'blue', 600.00),
(108, 'VALVE', 'red', 400.00),
(109, 'P1', 'yellow', 180.00),
(110, 'PISTON', 'silver', 5200.00);

-- Insert records into Supplies table
INSERT INTO Supplies (SID, PID, qty, date_supplied) VALUES
(204001, 101, 50, '2000-05-01'),
(204001, 102, 30, '2000-12-17'),
(204002, 103, 100, '2021-01-12'),
(204003, 105, 75, '2021-01-10'),
(204003, 106, 40, '2021-02-15'),
(204004, 104, 25, '2021-03-20'),
(204005, 106, 60, '2021-04-10'),
(204005, 108, 80, '2021-04-18'),
(204006, 101, 90, '2021-05-05'),
(204008, 105, 110, '2021-06-12');

-- 2. Show all contents
SELECT * FROM Supplier;
SELECT * FROM Part;
SELECT * FROM Supplies;

-- 3. Find name and city of all suppliers
SELECT Sname, city FROM Supplier;

-- 4. Find name and phone of suppliers who stay in 'Delhi'
SELECT Sname, phone FROM Supplier WHERE city = 'Delhi';

-- 5. Find all distinct branches of suppliers
SELECT DISTINCT branch FROM Supplier;