First Exam
CREATE DATABASE universitydb;
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
DateOfBirth DATE,
GPA DECIMAL(3,2)
);
CREATE TABLE Courses (
CourseCode INT PRIMARY KEY,
CourseName VARCHAR(100) NOT NULL,
Credits INT,
CourseDescription TEXT,
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
Major VARCHAR(45),
Salary INT
);
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseCode INT,
EnrollmentDate DATE,
Grade INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseCode) REFERENCES Courses(CourseCode)
);
ALTER TABLE Students
ADD Address VARCHAR(255);
INSERT INTO Students (StudentID, FirstName, LastName, Email, DateOfBirth, GPA,
Address)
VALUES (1, ‘ali’, 'ali', '
[email protected]', '1990-01-15', 3.5, '123 Main St'),
(2, ‘mahdi’, mahdi,
[email protected]'', '1992-05-20', 3.8, '456 Oak Ave'),
(3, ‘zein’, 'zein',
[email protected]'', '1995-11-10', 3.2, '789 Maple Rd');
INSERT INTO Courses (CourseCode, CourseName, Credits, CourseDescription,
InstructorID)
VALUES (101, 'Introduction to Computer Science', 3, 'An overview of computer
science fundamentals.', 1),
(102, 'Data Structures and Algorithms', 4, 'Study of data structures and algorithms.', 2),
(103, 'Database Systems', 3, 'Concepts and technologies of database systems.', 1);
INSERT INTO Instructors (InstructorID, FirstName, LastName, Email, Major, Salary)
VALUES (1, 'Dr.zahraa, 'zahraa', '
[email protected]', 'Computer Science', 80000),
(2, 'fatima', 'fatima', '
[email protected]', 'Computer Science', 75000);
(3, 'sara', 'sara', '
[email protected]', 'Computer Science', 9000);
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseCode, EnrollmentDate,
Grade)
VALUES (1, 1, 101, '2024-09-01', 90),
(2, 1, 102, '2024-09-01', 85),
(3, 2, 101, '2024-09-01', 92);
UPDATE Enrollments
SET Grade = 85
WHERE StudentID = 112233 AND CourseCode = 'CSC330L';
DELETE FROM Enrollments
WHERE EnrollmentDate = '2025-10-01';
SELECT StudentID, FirstName, LastName
FROM Students
WHERE GPA BETWEEN 3.0 AND 3.5 AND FirstName LIKE 'A%';
SELECT *
FROM Courses
WHERE Credits > 3
ORDER BY CourseName DESC;
SELECT StudentID
FROM Enrollments
WHERE CourseCode = 'CSC300'
INTERSECT
SELECT StudentID
FROM Enrollments
WHERE CourseCode = 'CSC300L';
SELECT SUM(Credits)
FROM Courses
JOIN Enrollments ON Courses.CourseCode = Enrollments.CourseCode
WHERE StudentID = 112233;
SELECT CourseCode, COUNT(StudentID) AS NumStudents
FROM Enrollments
GROUP BY CourseCode
ORDER BY NumStudents ASC;
SELECT StudentID
FROM Students
WHERE StudentID NOT IN (SELECT StudentID FROM Enrollments);
SELECT CourseName
FROM Courses
WHERE Credits = (SELECT MIN(Credits) FROM Courses);
SELECT Courses.CourseName, Instructors.FirstName, Instructors.LastName
FROM Courses
JOIN Instructors ON Courses.InstructorID = Instructors.InstructorID;
CREATE VIEW DatabaseView AS
SELECT Students.*
FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
WHERE Enrollments.CourseCode = 'CSC330';
Bouns:
CREATE ROLE uni_manager;
GRANT SELECT ON ALL TABLES TO uni_manager;
GRANT UPDATE ON Enrollments TO uni_manager;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Exam 2:
CREATE DATABASE hospitaldb;
CREATE TABLE Patients (
PatientID INT PRIMARY KEY,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(15),
Email VARCHAR(255),
BirthDate DATE
);
CREATE TABLE Doctors (
DoctorID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(15),
SpecialityID INT,
FOREIGN KEY (SpecialityID) REFERENCES Speciality(SpecialityID)
);
CREATE TABLE Speciality (
SpecialityID INT PRIMARY KEY,
Name VARCHAR(255)
);
CREATE TABLE Appointments (
AppointmentID INT PRIMARY KEY,
PatientID INT,
DoctorID INT,
AppointmentDate DATE,
Reason VARCHAR(500),
Fees DECIMAL(10,2),
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);
ALTER TABLE Patients
ADD Address VARCHAR(255);
INSERT INTO Patients (PatientID, FirstName, LastName, PhoneNumber, Email,
BirthDate, Address) VALUES
(1, ‘ALi’, ‘ALi’, '123-456-7890',
[email protected]', '1995-01-15', '123 Main St'),
(2, ‘Hassan’, 'Hassan', '987-654-3210',
[email protected]', '1998-05-20', '456 Oak
Ave'),
(3, ‘Yasser’, 'Yasser', '555-123-4567', '
[email protected]', '2002-11-10', '789
Maple Rd');
INSERT INTO Doctors (DoctorID, Name, PhoneNumber, SpecialityID) VALUES
(1, 'Dr. Yossef', '111-222-3333', 1),
(2, 'Dr. Moubark', '444-555-6666', 2),
(3, 'Dr. Mohammad', '777-888-9999', 3);
INSERT INTO Speciality (SpecialityID, Name) VALUES
(1, 'Cardiology'),
(2, 'Pediatrics'),
(3, 'Neurology');
INSERT INTO Appointments (AppointmentID, PatientID, DoctorID, AppointmentDate,
Reason, Fees) VALUES
(1, 1, 1, '2024-10-15', 'Chest pain', 100.00),
(2, 2, 2, '2024-10-20', 'Fever', 80.00),
(3, 3, 3, '2024-10-25', 'Headache', 120.00);
UPDATE Appointments
SET AppointmentDate = '2025-02-15'
WHERE PatientID = 12 AND DoctorID = 7;
DELETE FROM Doctors
WHERE SpecialityID = 3;
SELECT FirstName, LastName, PhoneNumber
FROM Patients
WHERE BirthDate BETWEEN '2000-01-01' AND '2005-01-01' AND FirstName LIKE 'Z%';
SELECT *
FROM Doctors
WHERE SpecialityID = 5
ORDER BY Name DESC;
SELECT PatientID
FROM Appointments
WHERE DoctorID = 11
EXCEPT
SELECT PatientID
FROM Appointments
WHERE DoctorID = 17;
SELECT SUM(Fees)
FROM Appointments
WHERE DoctorID = 15;
SELECT DoctorID, COUNT(*) AS NumAppointments
FROM Appointments
GROUP BY DoctorID
ORDER BY NumAppointments ASC;
SELECT PatientID
FROM Patients
WHERE PatientID NOT IN (SELECT PatientID FROM Appointments);
SELECT P.FirstName, P.LastName, D.Name, A.AppointmentDate
FROM Patients P
JOIN Appointments A ON P.PatientID = A.PatientID
JOIN Doctors D ON A.DoctorID = D.DoctorID
WHERE A.Fees = (SELECT MIN(Fees) FROM Appointments);
SELECT *
FROM Appointments
JOIN Doctors ON Appointments.DoctorID = Doctors.DoctorID
WHERE Doctors.Name = 'John Smith';
CREATE VIEW DoctorsInfo AS
SELECT D.*, S.Name AS SpecialityName
FROM Doctors D
JOIN Speciality S ON D.SpecialityID = S.SpecialityID;
Bouns:
CREATE ROLE hospital_manager;
GRANT SELECT ON ALL TABLES TO hospital_manager;
GRANT UPDATE ON Appointments TO hospital_manager;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT hospital_manager TO 'username'@'localhost';
