CREATE TABLE SUP (
SID INT PRIMARY KEY,
Sname VARCHAR(50),
branch VARCHAR(50),
address VARCHAR(50),
contact VARCHAR(20),
state VARCHAR(50));
DML Commands
1. Insert at least 10 records in tables supplier, part and supplies
2. Show the contents in tables supplier, part and supplies
3. Find the name and city of all suppliers
4. Find the name and phone no of all suppliers who stay in ‘Delhi’
5. Find all distinct branches of suppliers
6. Delete the record of the supplier whose SID is 204001
7. Delete all records of supplier table
8. Delete all records of suppliers whose city starts with capital A.
9. Find the supplier names which have ‘lk’ in any position
10. Find the supplier name where ‘R’ is in the second position
11. Find the name of supplier whose name starts with ‘V’ and ends with ‘A’
12. Change the city of all suppliers to ‘BOMBAY’
13. Change the city of supplier ‘Vandana’ to ‘Goa’ give a code
CODE
1. Supplier Table
INTO Supplier VALUES (204004, 'Priya', 'global', 'Bangalore', '9123456780');
INSERT INTO Supplier VALUES (204005, 'Amit', 'local', 'Delhi', '9234567890');
INSERT INTO Supplier VALUES (204006, 'Geeta', 'global', 'Kolkata',
'9345678901');
INSERT INTO Supplier VALUES (204007, 'Suresh', 'local', 'Pune', '9456789012');
INSERT INTO Supplier VALUES (204008, 'Meena', 'global', 'Hyderabad',
'9567890123');
INSERT INTO Supplier VALUES (204009, 'Vandana', 'local', 'Chennai',
'9678901234');
INSERT INTO Supplier VALUES (204010, 'Neha', 'global', 'Jaipur',
'9789012345');
-- Part Table
INSERT INTO Part VALUES (101, 'Bolt', 'red', 300);
INSERT INTO Part VALUES (102, 'Nut', 'blue', 150);
INSERT INTO Part VALUES (103, 'Washer', 'green', 100);
INSERT INTO Part VALUES (104, 'Screw', 'black', 200);
INSERT INTO Part VALUES (105, 'Clamp', 'red', 250);
INSERT INTO Part VALUES (106, 'Rod', 'blue', 350);
INSERT INTO Part VALUES (107, 'Plate', 'green', 500);
INSERT INTO Part VALUES (108, 'Cap', 'black', 450);
INSERT INTO Part VALUES (109, 'Pin', 'red', 100);
INSERT INTO Part VALUES (110, 'Bracket', 'green', 400);
-- Supplies Table
INSERT INTO SUP VALUES (204001, 101, 50, '2024-01-01');
INSERT INTO SUP VALUES (204002, 102, 60, '2024-02-15');
INSERT INTO SUP VALUES (204003, 103, 70, '2024-03-10');
INSERT INTO SUPs VALUES (204004, 104, 80, '2024-04-05');
INSERT INTO SUP VALUES (204005, 105, 90, '2024-05-01');
INSERT INTO SUP VALUES (204006, 106, 55, '2024-06-12');
INSERT INTO SUP VALUES (204007, 107, 65, '2024-07-08');
INSERT INTO SUP VALUES (204008, 108, 75, '2024-08-19');
INSERT INTO SUP VALUES (204009, 109, 85, '2024-09-20');
INSERT INTO SUP VALUES (204010, 110, 95, '2024-10-21');
2. SELECT * FROM Supplier;
SELECT * FROM Part;
SELECT * FROM SUP;
[Link] Sname, city FROM Supplier;
[Link] Sname, phone FROM Supplier WHERE city = 'Delhi';
[Link] DISTINCT branch FROM Supplier;
[Link] FROM Supplier WHERE SID = 204001;
[Link] FROM Supplier;
[Link] FROM Supplier WHERE city LIKE 'A%';
9. SELECT Sname FROM Supplier WHERE Sname LIKE '%lk%';
[Link] Sname FROM Supplier WHERE Sname LIKE '_R%';
[Link] Sname FROM Supplier WHERE Sname LIKE 'V%A';
[Link] Supplier SET city = 'BOMBAY';
[Link] Supplier SET city = 'Goa' WHERE Sname = 'Vandana';
Queries with Constraints
1. Create the supplier table with Primary Key Constraint
2. Create supplies table with Foreign key Constraint
3. Create a part table with UNIQUE Constraint
4. Create supplier Table with Check Constraints
5. Create Supplier table with Default
CODE
[Link] TABLE Supplier (
SID INT PRIMARY KEY,
Sname VARCHAR(50),
branch VARCHAR(50),
city VARCHAR(50),
phone VARCHAR(15)
);
[Link] TABLE Supplies (
SID INT,
PID INT,
qty INT,
date_supplied DATE,
FOREIGN KEY (SID) REFERENCES Supplier(SID),
FOREIGN KEY (PID) REFERENCES Part(PID)
);
[Link] TABLE Part (
PID INT PRIMARY KEY,
Pname VARCHAR(50) UNIQUE,
color VARCHAR(20),
price DECIMAL(10,2)
);
[Link] TABLE Supplier (
SID INT PRIMARY KEY,
Sname VARCHAR(50),
branch VARCHAR(50),
city VARCHAR(50) CHECK (city IN ('Delhi', 'Mumbai', 'Chennai')),
phone VARCHAR(15)
);
[Link] TABLE Supplier (
SID INT PRIMARY KEY,
Sname VARCHAR(50),
branch VARCHAR(50),
city VARCHAR(50) DEFAULT 'Delhi',
phone VARCHAR(15)
);
Constraint Queries on TCL
1. Create Savepoints
2. Rollback to SavePoints
3. Use Commit to save on
CODE
[Link];
INSERT INTO Supplier VALUES (204011, 'Nikhil', 'Mech', 'Pune', '9999999999');
SAVEPOINT sp1;
INSERT INTO Supplier VALUES (204012, 'Radha', 'IT', 'Hyderabad',
'8888888888');
SAVEPOINT sp2;
2. ROLLBACK TO sp1;
3. COMMIT;
