QUESTION 1:
Write SQL queries to perform the CRUD operations.
Solution
create database studies;
create table Students
(
StudentID int not null primary key,
FirstName varchar(255),
LastName varchar(255),
Email varchar(255),
Phone varchar(255),
);
create table Courses
(
CourseID varchar(255) not null primary key,
CourseName varchar(255),
Credits int,
);
create table Enrollments
(
EnrollmentID varchar(255),
StudentID int,
CourseID varchar(255), Grade varchar(255), foreign key
(StudentID) references Students(StudentID), foreign key
(CourseID) references Courses(CourseID)
);
--now insert a new student in the student table
PART(A)
insert into Students(StudentID , FirstName , LastName , Email , Phone) values(120
, 'Hashir' , 'Sarfaraz' , 'hashirsarfaraz99@gmail.com' , '0329-66613245')
--more entries insert into Courses(CourseID ,
CourseName , Credits) values('CS-222-001-2023-
Spring' , 'Data Structure' , 3)
insert into Courses(CourseID , CourseName , Credits) values('CS-333-001-2023-
Fall' , 'Data Base' , 3)
insert into Students(StudentID , FirstName , LastName , Email , Phone) values(125
, 'Saad' , 'Saleem' , 'saadsaleem66@gmail.com' , '0301-5211324')
insert into Enrollments (EnrollmentID, StudentID, CourseID, Grade)
values('U202310003' , 125 , 'CS-333-001-2023-Fall' , 'C')
insert into Enrollments (EnrollmentID, StudentID, CourseID, Grade)
values('U202310004' , 120 , 'CS-222-001-2023-Spring' , 'A-')
PART(B)
--Read
select e.EnrollmentID, e.StudentID, s.FirstName, s.LastName, s.Email, s.Phone,
c.CourseName, c.CourseID, c.Credits, e.Grade from Enrollments e inner join Students s on
e.StudentID = s.StudentID inner join Courses c on e.CourseID = c.CourseID;
PART(C)
--update
update Enrollments set Grade = 'B' where CourseID = 'CS-333-001-2023-Fall' AND StudentID
= 125
PART(D)
--delete alter table Enrollments add
constraint fk_enrollments_students
FOREIGN KEY (StudentID) REFERENCES Students(StudentID) on delete cascade;--using
cascading delete
delete from Students where StudentID = 125;
QUESTION 2:
(a)Define the following SQL constraints and explain their purpose:
NOT NULL:
It ensures that a column cannot have a NULL value. It means that presence of value.
PURPOSE:
It enforces that every row in the table must have a value.
UNIQUE:
It ensures that each value in a column is different from every other value. It means no repetition of value.
PURPOSE:
Unlike PRIMARY KEY, it allows NULL values. It enforces that every value must be unique.
PRIMARY KEY:
Primary key is a combination of “NOT NULL” and “UNIQUE”.
PURPOSE:
It enforces the uniqueness of values in the specified column(s) and ensures that the column(s) cannot
contain NULL values.
FOREIGN KEY:
Foreign key uniquely identifies a row/record in another table.
PURPOSE:
It establishes a link between two tables by specifying that values in a column (or set of columns) in one
table must correspond to values in a primary key column in another table.
CHECK:
Check ensures that all values in a column satisfies a specific condition.
PURPOSE:
It enforces data only rows that satisfy the specified condition to be inserted or updated.
DEFAULT:
It sets a Default value for a column when no value is specified.
PURPOSE:
If no value is provided for the column, then it helps ensure consistency.
INDEX:
Index is used to create and retrieve data from databases very quickly.
PURPOSE:
It improves query performance by creating an index on one or more columns.
QUESTION 2(B):
Solution
create database emp;
create table Departments
(
DepartmentID int not null primary key,
DepartmentName varchar(255),
);
create table Employees
(
EmployeeID int not null, -- Unique identifier for each employee
FirstName varchar(255),
LastName varchar(255),
Email varchar(255) unique, -- Unique email address for each employee
DepartmentID int,
constraint pk_emp primary key(EmployeeID), -- Primary key constraint
-- Foreign key constraint on DepartmentID, referencing the Departments table constraint
fk_dep foreign key(DepartmentID) references Departments(DepartmentID),
constraint uq_email unique (Email) -- Unique email address for each employee
);
