06-FEB-2025
Submitted To:
Maam Ammara Javed
Course Code: CS-241
Submitted By:
Name: Muhammad Haris Khan
Roll No: 23021519-084
Section: A
Problem 1:
create database avengers1;
use avengers1;
create table students (
StudentID int primary key,
FirstName varchar(50) Not Null,
LastName varchar(50) Not Null,
Age int not null,
City varchar(50) default 'Islambad',
);
insert into students
values
(116,'Gohar', 'Abbas', 21, 'Islambad'),
(147,'Bilal', 'Muhammad', 19, 'saarai'),
(111,'Shoaib', 'Akhtar', 19, 'mohamdi Pur'),
(84,'Haris', 'Khan', 22, 'Kharain'),
(60,'Fasih', 'Rehman', 16, 'Lalamusa');
select* from students;
create table courses (
CourseID varchar(10) primary key,
CourseName VARCHAR(50) NOT NULL,
credits int
);
insert into courses
values
('CS-241', 'Database System', 4),
('CS-305', 'Web System and Technology', 3),
('CS-321', 'SoftwareEngineering', 3),
('CS-353', 'CA', 3),
('MATH-314', 'LinearAlgebra', 3);
select* from courses;
create table Enrollments (
EnrollmentID int primary key,
CourseID varchar(10),
StudentID int,
Grade varchar(2),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
insert into Enrollments
values
(1, 'CS-241',116, 'B'),
(2, 'CS-305',147, 'A+'),
(3, 'CS-321',3, 'B+'),
(4, 'CS-353',84, 'B'),
(5, 'MATH-314',60, 'D');
select* from Enrollments;
update Enrollments
set Grade = 'A'
where StudentID between 18 and 20;
delete from Enrollments
where Grade = 'F' or Grade = 'D';
select* from Students
where City = 'Islambad' OR City = 'Kharian';
SELECT StudentID, FirstName, LastName, Age, City
FROM Students
WHERE Age < 18
UNION
SELECT NULL, NULL, NULL, NULL, Credits
FROM Courses
WHERE Credits = 3;
Problem 2:
create database avengers2;
use avengers2;
create table customers(
CustomerID int primary key,
Name varchar(50),
Email varchar(50),
City varchar(50)
);
insert into customers
values
(111, 'Shoaib','Shoib@gamil.com','Gujranwala'),
(116, 'Gohar','GoharAbbas@gmail.com','Islambad'),
(84, 'haris','hariskhan@gmail.com','kharian'),
(60, 'fasih','mirza.fasih99@gmail.com','lalamusa'),
(147, 'Bilal','Bilal.M@gmail.com','Lahore');
select* from customers;
update customers
set city = 'gujrat'
where city = 'Islambad';
create table products(
ProductID int primary key,
ProductName varchar(50) Not Null,
Category varchar(50),
price int
);
insert into products
values
(1, 'Milk','sweets','200'),
(2, 'Fridge','Electronic','20000'),
(3, 'Underwear','Clothing','500'),
(4, 'Lipstick','Makeup','130'),
(5, 'BArfi','sweets','150');
select* from products;
create table orders(
orderID int primary key,
ProductID int,
OrderDate date,
CustomerID int,
Quantity int not null,
foreign key (CustomerID) REFERENCES customers(CustomerID),
foreign key (ProductID) REFERENCES products(ProductID)
);
insert into orders
values
(1, 1, '2024-02-01', 111, 3),
(2, 2, '2023-09-02', 116, 1),
(3, 3, '2022-04-03', 84, 2),
(4, 4, '2021-12-04', 60, 2),
(5, 5, '2030-01-05', 147, 1);
select* from orders;
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 ('Electronic','Clothing');
select CustomerID, Name, Email, City, NULL AS ProductID, NULL AS ProductName,
NULL AS Category, NULL AS Price
from Customers
where City = 'Gujrat'
union
select NULL, NULL, NULL, NULL, ProductID, ProductName, Category, Price
from Products
where Price < 150;
