Set_A_Q1_Employee_Management.sql

Pasted Oct 8, 2026, 8:01 PM4 files412 lines

Scan to open on your phone

Point a camera at the QR code - it opens this exact paste, no app needed.

https://codepastes.com/adipt

Set_A_Q1_Employee_Management.sqlSQL

-- DBMS PRACTICAL EXAMINATION
-- SET A - Q1: Employee Management System

CREATE DATABASE EmployeeData;
USE EmployeeData;

CREATE TABLE EMPLOYEE (
    Emp_ID INT PRIMARY KEY,
    Emp_Name VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10,2),
    City VARCHAR(50),
    Joining_Date DATE
);

CREATE TABLE PROJECT (
    Project_ID INT PRIMARY KEY,
    Project_Name VARCHAR(100),
    Budget DECIMAL(12,2),
    Emp_ID INT,
    FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)
);

INSERT INTO EMPLOYEE
(Emp_ID, Emp_Name, Department, Salary, City, Joining_Date)
VALUES
(1, 'Aarav Sharma', 'IT', 75000, 'Kathmandu', '2022-01-15'),
(2, 'Bikash Thapa', 'IT', 65000, 'Pokhara', '2021-05-20'),
(3, 'Anisha Rai', 'HR', 55000, 'Kathmandu', '2023-03-10'),
(4, 'Suman KC', 'Finance', 85000, 'Lalitpur', '2020-07-12'),
(5, 'Priya Shrestha', 'IT', 90000, 'Bhaktapur', '2019-11-01'),
(6, 'Rohan Gurung', 'HR', 70000, 'Pokhara', '2022-09-18'),
(7, 'Nabin Lama', 'Finance', 60000, 'Kathmandu', '2023-01-25'),
(8, 'Sneha Joshi', 'Marketing', 45000, 'Lalitpur', '2024-02-14');

INSERT INTO PROJECT
(Project_ID, Project_Name, Budget, Emp_ID)
VALUES
(101, 'Website Development', 500000, 1),
(102, 'Mobile Application', 750000, 2),
(103, 'Recruitment System', 300000, 3),
(104, 'Financial Analysis', 600000, 4),
(105, 'Cloud Migration', 900000, 5),
(106, 'Employee Training', 250000, 6);

-- 1. Display names and salaries of employees whose salary is greater than Rs. 50,000.
SELECT Emp_Name, Salary
FROM EMPLOYEE
WHERE Salary > 50000;

-- 2. Find the maximum, minimum and average salary.
SELECT
    MAX(Salary) AS Maximum_Salary,
    MIN(Salary) AS Minimum_Salary,
    AVG(Salary) AS Average_Salary
FROM EMPLOYEE;

-- 3. Display employee names along with their corresponding project names using JOIN.
SELECT
    EMPLOYEE.Emp_Name,
    PROJECT.Project_Name
FROM EMPLOYEE
JOIN PROJECT
ON EMPLOYEE.Emp_ID = PROJECT.Emp_ID;

-- 4. Find the total number of employees in each department.
SELECT
    Department,
    COUNT(*) AS Total_Employees
FROM EMPLOYEE
GROUP BY Department;

-- 5. Display departments having an average salary greater than Rs. 60,000.
SELECT
    Department,
    AVG(Salary) AS Average_Salary
FROM EMPLOYEE
GROUP BY Department
HAVING AVG(Salary) > 60000;

Set_A_Q2_Online_Bookstore.sqlSQL

-- DBMS PRACTICAL EXAMINATION
-- SET A - Q2: Online Bookstore

CREATE DATABASE BookstoreData;
USE BookstoreData;

CREATE TABLE CUSTOMER (
    Customer_ID INT PRIMARY KEY,
    Customer_Name VARCHAR(50),
    Email VARCHAR(100),
    City VARCHAR(50)
);

CREATE TABLE BOOK (
    Book_ID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(100),
    Price DECIMAL(10,2),
    Stock INT
);

CREATE TABLE ORDERS (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT,
    Order_Date DATE,
    Total_Amount DECIMAL(10,2),
    FOREIGN KEY (Customer_ID) REFERENCES CUSTOMER(Customer_ID)
);

CREATE TABLE ORDER_ITEM (
    Order_ID INT,
    Book_ID INT,
    Quantity INT,
    PRIMARY KEY (Order_ID, Book_ID),
    FOREIGN KEY (Order_ID) REFERENCES ORDERS(Order_ID),
    FOREIGN KEY (Book_ID) REFERENCES BOOK(Book_ID)
);

INSERT INTO CUSTOMER
(Customer_ID, Customer_Name, Email, City)
VALUES
(1, 'Aarav Sharma', 'aarav@gmail.com', 'Kathmandu'),
(2, 'Anisha Rai', 'anisha@gmail.com', 'Pokhara'),
(3, 'Rohan Thapa', 'rohan@gmail.com', 'Lalitpur'),
(4, 'Sneha KC', 'sneha@gmail.com', 'Bhaktapur'),
(5, 'Bikash Gurung', 'bikash@gmail.com', 'Kathmandu');

INSERT INTO BOOK
(Book_ID, Title, Author, Price, Stock)
VALUES
(101, 'Database Systems', 'Raghu Ramakrishnan', 1200, 10),
(102, 'Clean Code', 'Robert Martin', 1500, 5),
(103, 'The Alchemist', 'Paulo Coelho', 800, 20),
(104, 'Atomic Habits', 'James Clear', 1100, 15),
(105, 'Python Basics', 'John Smith', 600, 12),
(106, 'Digital Marketing', 'David Miller', 450, 8);

INSERT INTO ORDERS
(Order_ID, Customer_ID, Order_Date, Total_Amount)
VALUES
(1, 1, '2026-09-01', 2700),
(2, 2, '2026-09-03', 1500),
(3, 1, '2026-09-05', 1100),
(4, 3, '2026-09-07', 800),
(5, 4, '2026-09-10', 2100),
(6, 2, '2026-09-12', 1200);

INSERT INTO ORDER_ITEM
(Order_ID, Book_ID, Quantity)
VALUES
(1, 101, 1),
(1, 102, 1),
(2, 102, 1),
(3, 104, 1),
(4, 103, 1),
(5, 103, 1),
(5, 104, 1),
(6, 101, 1);

-- 1. Display books whose price is between Rs. 500 and Rs. 1,500.
SELECT *
FROM BOOK
WHERE Price BETWEEN 500 AND 1500;

-- 2. Display customer names along with their order details using JOIN.
SELECT
    CUSTOMER.Customer_Name,
    ORDERS.Order_ID,
    ORDERS.Order_Date,
    ORDERS.Total_Amount
FROM CUSTOMER
JOIN ORDERS
ON CUSTOMER.Customer_ID = ORDERS.Customer_ID;

-- 3. Find the total number of orders placed by each customer.
SELECT
    CUSTOMER.Customer_Name,
    COUNT(ORDERS.Order_ID) AS Total_Orders
FROM CUSTOMER
LEFT JOIN ORDERS
ON CUSTOMER.Customer_ID = ORDERS.Customer_ID
GROUP BY CUSTOMER.Customer_ID, CUSTOMER.Customer_Name;

-- 4. Display the customer who has spent the highest total amount.
SELECT
    CUSTOMER.Customer_Name,
    SUM(ORDERS.Total_Amount) AS Total_Spent
FROM CUSTOMER
JOIN ORDERS
ON CUSTOMER.Customer_ID = ORDERS.Customer_ID
GROUP BY CUSTOMER.Customer_ID, CUSTOMER.Customer_Name
ORDER BY Total_Spent DESC
LIMIT 1;

-- 5. Display books that have never been ordered.
SELECT
    BOOK.Book_ID,
    BOOK.Title,
    BOOK.Author
FROM BOOK
LEFT JOIN ORDER_ITEM
ON BOOK.Book_ID = ORDER_ITEM.Book_ID
WHERE ORDER_ITEM.Book_ID IS NULL;

Set_B_Q1_Student_Course_Management.sqlSQL

-- DBMS PRACTICAL EXAMINATION
-- SET B - Q1: Student and Course Management System

CREATE DATABASE StudentData;
USE StudentData;

CREATE TABLE STUDENT (
    Student_ID INT PRIMARY KEY,
    Student_Name VARCHAR(50),
    Program VARCHAR(50),
    Semester INT,
    City VARCHAR(50)
);

CREATE TABLE COURSE (
    Course_ID INT PRIMARY KEY,
    Course_Name VARCHAR(100),
    Credit_Hours INT,
    Instructor VARCHAR(50)
);

CREATE TABLE ENROLLMENT (
    Student_ID INT,
    Course_ID INT,
    Marks DECIMAL(5,2),
    Grade VARCHAR(5),
    PRIMARY KEY (Student_ID, Course_ID),
    FOREIGN KEY (Student_ID) REFERENCES STUDENT(Student_ID),
    FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID)
);

INSERT INTO STUDENT
(Student_ID, Student_Name, Program, Semester, City)
VALUES
(1, 'Aarav Sharma', 'BCA', 3, 'Kathmandu'),
(2, 'Anisha Rai', 'BCA', 3, 'Pokhara'),
(3, 'Bikash Thapa', 'BCA', 2, 'Lalitpur'),
(4, 'Rohan Gurung', 'BIM', 4, 'Kathmandu'),
(5, 'Aayush KC', 'BCA', 3, 'Bhaktapur'),
(6, 'Sneha Shrestha', 'BIM', 2, 'Pokhara');

INSERT INTO COURSE
(Course_ID, Course_Name, Credit_Hours, Instructor)
VALUES
(101, 'Database Management System', 3, 'Dr. Sharma'),
(102, 'Operating System', 3, 'Prof. Thapa'),
(103, 'Computer Networks', 3, 'Dr. Rai'),
(104, 'Web Technology', 3, 'Prof. Gurung');

INSERT INTO ENROLLMENT
(Student_ID, Course_ID, Marks, Grade)
VALUES
(1, 101, 85, 'A'),
(1, 102, 78, 'B+'),
(2, 101, 72, 'B+'),
(2, 103, 68, 'B'),
(3, 102, 65, 'B'),
(3, 104, 75, 'B+'),
(4, 101, 92, 'A+'),
(4, 103, 88, 'A'),
(5, 101, 95, 'A+'),
(5, 104, 82, 'A'),
(6, 102, 70, 'B+'),
(6, 103, 76, 'B+');

-- 1. Display students enrolled in Semester 3.
SELECT *
FROM STUDENT
WHERE Semester = 3;

-- 2. Display students whose names start with the letter 'A'.
SELECT *
FROM STUDENT
WHERE Student_Name LIKE 'A%';

-- 3. Find the average marks for each course.
SELECT
    COURSE.Course_Name,
    AVG(ENROLLMENT.Marks) AS Average_Marks
FROM COURSE
JOIN ENROLLMENT
ON COURSE.Course_ID = ENROLLMENT.Course_ID
GROUP BY COURSE.Course_ID, COURSE.Course_Name;

-- 4. Display courses having an average mark greater than 70.
SELECT
    COURSE.Course_Name,
    AVG(ENROLLMENT.Marks) AS Average_Marks
FROM COURSE
JOIN ENROLLMENT
ON COURSE.Course_ID = ENROLLMENT.Course_ID
GROUP BY COURSE.Course_ID, COURSE.Course_Name
HAVING AVG(ENROLLMENT.Marks) > 70;

-- 5. Display the student(s) who obtained the highest marks.
SELECT
    STUDENT.Student_Name,
    ENROLLMENT.Marks
FROM STUDENT
JOIN ENROLLMENT
ON STUDENT.Student_ID = ENROLLMENT.Student_ID
WHERE ENROLLMENT.Marks = (
    SELECT MAX(Marks)
    FROM ENROLLMENT
);

Set_B_Q2_Hospital_Management.sqlSQL

-- DBMS PRACTICAL EXAMINATION
-- SET B - Q2: Hospital Management System

CREATE DATABASE HospitalData;
USE HospitalData;

CREATE TABLE PATIENT (
    Patient_ID INT PRIMARY KEY,
    Patient_Name VARCHAR(50),
    Gender VARCHAR(10),
    Age INT,
    City VARCHAR(50)
);

CREATE TABLE DOCTOR (
    Doctor_ID INT PRIMARY KEY,
    Doctor_Name VARCHAR(50),
    Specialization VARCHAR(50),
    Salary DECIMAL(10,2)
);

CREATE TABLE APPOINTMENT (
    Appointment_ID INT PRIMARY KEY,
    Patient_ID INT,
    Doctor_ID INT,
    Appointment_Date DATE,
    Fee DECIMAL(10,2),
    Status VARCHAR(20),
    FOREIGN KEY (Patient_ID) REFERENCES PATIENT(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES DOCTOR(Doctor_ID)
);

INSERT INTO PATIENT
(Patient_ID, Patient_Name, Gender, Age, City)
VALUES
(1, 'Aarav Sharma', 'Male', 45, 'Kathmandu'),
(2, 'Anisha Rai', 'Female', 32, 'Pokhara'),
(3, 'Bikash Thapa', 'Male', 55, 'Lalitpur'),
(4, 'Sneha KC', 'Female', 42, 'Bhaktapur'),
(5, 'Rohan Gurung', 'Male', 28, 'Kathmandu'),
(6, 'Priya Shrestha', 'Female', 60, 'Pokhara');

INSERT INTO DOCTOR
(Doctor_ID, Doctor_Name, Specialization, Salary)
VALUES
(101, 'Dr. Raj Sharma', 'Cardiology', 150000),
(102, 'Dr. Sita Rai', 'Neurology', 160000),
(103, 'Dr. Bikash Thapa', 'Orthopedics', 130000),
(104, 'Dr. Anil KC', 'Dermatology', 120000);

INSERT INTO APPOINTMENT
(Appointment_ID, Patient_ID, Doctor_ID, Appointment_Date, Fee, Status)
VALUES
(1, 1, 101, '2026-09-01', 1500, 'Completed'),
(2, 2, 101, '2026-09-02', 1500, 'Completed'),
(3, 3, 101, '2026-09-03', 1500, 'Completed'),
(4, 4, 101, '2026-09-04', 1500, 'Completed'),
(5, 5, 101, '2026-09-05', 1500, 'Completed'),
(6, 6, 101, '2026-09-06', 1500, 'Completed'),
(7, 2, 102, '2026-09-07', 1200, 'Completed'),
(8, 3, 103, '2026-09-08', 1000, 'Completed'),
(9, 4, 104, '2026-09-09', 900, 'Completed');

-- 1. Display patients whose age is greater than 40.
SELECT *
FROM PATIENT
WHERE Age > 40;

-- 2. Display doctors whose specialization is either Cardiology or Neurology.
SELECT *
FROM DOCTOR
WHERE Specialization IN ('Cardiology', 'Neurology');

-- 3. Display patient names along with the doctors they have visited.
SELECT
    PATIENT.Patient_Name,
    DOCTOR.Doctor_Name
FROM PATIENT
JOIN APPOINTMENT
ON PATIENT.Patient_ID = APPOINTMENT.Patient_ID
JOIN DOCTOR
ON APPOINTMENT.Doctor_ID = DOCTOR.Doctor_ID;

-- 4. Find the total number of appointments handled by each doctor.
SELECT
    DOCTOR.Doctor_Name,
    COUNT(APPOINTMENT.Appointment_ID) AS Total_Appointments
FROM DOCTOR
LEFT JOIN APPOINTMENT
ON DOCTOR.Doctor_ID = APPOINTMENT.Doctor_ID
GROUP BY DOCTOR.Doctor_ID, DOCTOR.Doctor_Name;

-- 5. Display doctors who have handled more than 5 appointments.
SELECT
    DOCTOR.Doctor_Name,
    COUNT(APPOINTMENT.Appointment_ID) AS Total_Appointments
FROM DOCTOR
JOIN APPOINTMENT
ON DOCTOR.Doctor_ID = APPOINTMENT.Doctor_ID
GROUP BY DOCTOR.Doctor_ID, DOCTOR.Doctor_Name
HAVING COUNT(APPOINTMENT.Appointment_ID) > 5;

10,867 chars · codepastes.com