Assignment No.
02
ID: BC220400854
SEMESTER Spring 24
CS403- Database Management System
Question # 1 Solution
1st Normal Form TECHNICIAN table.
Tec_ID Tec_Name Tec_Phone Tec_City
1 Ahmed 03001122334 Multan
1 Ahmed 03021122335 Multan
2 Faisal 03000102034 Karachi
3 Nouman 03211234567 Lahore
3 Nouman 03013216547 Lahore
4 Usman 03133214567 Islamabad
Question # 2 Solution
SQL queries for each statement are given below:
Statement SQL Command
1. Create a table named Technician. You CREATE TABLE Technician (
also have to define a primary key and any TechnicianID INT AUTO_INCREMENT PRIMARY KEY,
foreign key relationships if applicable. FirstName VARCHAR (50) NOT NULL,
LastName VARCHAR (50) NOT NULL,
Email VARCHAR (100) UNIQUE NOT NULL,
PhoneNumber VARCHAR (15),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES Manager (ManagerID)
);
2. Insert a new record into the "Customers" INSERT INTO Customers (CustomerID, Name, Email, Phone,
table with the following details: Address)
CustomerID = "C001", Name = "John VALUES ('C001', 'John Doe', 'john.doe@example.com', '123-456-
Doe", Email = "john.doe@example.com", 7890', '123 Elm Street, Springfield');
Phone = "123-456-7890", and Address =
"123 Elm Street, Springfield".
3. Delete all records from the Orders table DELETE FROM Orders
where the Order Date is older than January WHERE OrderDate < '2020-01-01';
1, 2020.
4. Update the "Email" of a customer in the UPDATE Customers
"Customers" table with "CustomerID" = SET Email = 'new.email@example.com'
'C002' to 'new.email@example.com'. WHERE CustomerID = 'C002';
5. Retrieve the "Name" and "Price" of all SELECT Name, Price
gadgets in the "Gadgets" table where the FROM Gadgets
"Price" is greater than 5000. WHERE Price > 5000;
