Name: Muhammad Bilal
Roll No: 23021519-147
Section: BSCS23-IV-A
Subject: DBS Lab Task
Submitted to: Ma’am Ammara
Q:1: -
CREATE DATABASE IF NOT EXISTS LabTask;
USE LabTask;
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Age INT,
City VARCHAR(50)
);
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50) UNIQUE NOT NULL,
Credits INT DEFAULT 3
);
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Grade VARCHAR(1),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
INSERT INTO Students (StudentID, FirstName, LastName, Age, City) VALUES
(1, 'Ali', 'Khan', 19, 'Islambad'),
(2, 'Sara', 'Ahmed', 20, 'Kharian'),
(3, 'Omar', 'Rasheed', 22, 'Lahore'),
(4, 'Fatima', 'Zafar', 17, 'Islambad'),
(5, 'Hassan', 'Malik', 18, 'Karachi');
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(101, 'Mathematics', 3),
(102, 'Physics', 4),
(103, 'Chemistry', 3),
(104, 'Biology', 2),
(105, 'Computer Science', DEFAULT); -- Uses default 3 credits
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, Grade) VALUES
(1001, 1, 101, 'B'),
(1002, 2, 102, 'A'),
(1003, 3, 103, 'F'),
(1004, 4, 104, 'D'),
(1005, 5, 105, 'C');
-- Task C: Update Grade to 'A' for students aged 18-20
UPDATE Enrollments
JOIN Students ON Enrollments.StudentID = Students.StudentID
SET Enrollments.Grade = 'A'
WHERE Students.Age BETWEEN 18 AND 20;
DELETE FROM Enrollments WHERE Grade IN ('F', 'D');
SELECT * FROM Students WHERE City IN ('Islambad', 'Kharian');
(SELECT StudentID, FirstName, LastName, Age, City FROM Students WHERE Age < 18)
UNION
(SELECT S.StudentID, S.FirstName, S.LastName, S.Age, S.City
FROM Students S
JOIN Enrollments E ON S.StudentID = E.StudentID
JOIN Courses C ON E.CourseID = C.CourseID
WHERE C.Credits = 3)
ORDER BY StudentID;
Q2: -
USE LabTask;
-- Customers Table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
City VARCHAR(50) DEFAULT 'Lahore',
Email VARCHAR(100) UNIQUE
);
-- Products Table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100) UNIQUE,
Price INT,
Category VARCHAR(50) DEFAULT 'Electronics'
);
-- Orders Table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
ProductID INT,
OrderDate DATE DEFAULT (CURDATE())
);
-- Insert into Customers
INSERT INTO Customers (CustomerID, Name, City, Email) VALUES
(1, 'Ali Khan', 'Lahore', 'ali@example.com'),
(2, 'Sara Ahmed', 'Gujranwala', 'sara@example.com'),
(3, 'Bilal Hassan', 'Gujrat', 'bilal@example.com'),
(4, 'Fatima Raza', 'Karachi', 'fatima@example.com'),
(5, 'Usman Malik', 'Lahore', 'usman@example.com');
-- Insert into Products
INSERT INTO Products (ProductID, ProductName, Price, Category) VALUES
(1, 'Mouse', 200, 'Electronics'),
(2, 'T-Shirt', 150, 'Clothing'),
(3, 'Keyboard', 250, 'Electronics'),
(4, 'Dress', 300, 'Clothing'),
(5, 'Earphones', 80, 'Electronics');
-- Insert into Orders
INSERT INTO Orders (OrderID, CustomerID, ProductID, OrderDate) VALUES
(1, 1, 1, '2024-01-01'),
(2, 2, 2, '2024-01-02'),
(3, 3, 3, '2024-01-03'),
(4, 4, 4, '2024-01-04'),
(5, 5, 5, '2024-01-05');
UPDATE Products
SET Price = Price + 50
WHERE Price BETWEEN 100 AND 300;
DELETE FROM Orders
WHERE CustomerID IN (
SELECT CustomerID
FROM Customers
WHERE City IN ('Lahore', 'Gujranwala')
);
SELECT *
FROM Products
WHERE Category IN ('Electronics', 'Clothing');
SELECT Name AS Item, 'Customer' AS Type
FROM Customers
WHERE City = 'Gujrat'
UNION
SELECT ProductName AS Item, 'Product' AS Type
FROM Products
WHERE Price < 150;
