Netaji Subhas Institute of Technology
th
DBMS Lab Assignment (CSE 5 Semester)
Q1. Create a database named CollegeDB. Create a table named Student with the
following fields:
StudentID (INT, Primary Key), FirstName (VARCHAR), LastName (VARCHAR), Course
(VARCHAR), Marks (INT), and Age (INT).
For Example: CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Course VARCHAR(50),
Marks INT,
Age INT
);
INSERT INTO Student (StudentID, FirstName, LastName, Course, Marks, Age)
VALUES
(1, 'Ali', 'Khan', 'Mathematics', 85, 20),
(2, 'Sara', 'Ahmed', 'Physics', 78, 21),
(3, 'John', 'Smith', 'Mathematics', 92, 19),
(4, 'Mary', 'Johnson', 'Chemistry', 65, 22),
(5, 'Alex', 'Brown', 'Physics', 88, 20),
(6, 'Ram', 'Wilson', 'Mathematics', 79, 21);
(a) Insert at least ten records into the Student table with different courses
and marks.
(b) Write a query to find the average marks of students enrolled in the
Mathematics or Physics course where the student’s age is greater than 20.
(c) Write a query to find the minimum marks of students in the Chemistry
course whose age is less than 25.
(d) Write a query to find the names of students who scored more than the
average marks in their respective courses.
(e) Write a query to display the details of students who have marks greater
than 80 and are enrolled in the Physics course.
(f) Write a query to calculate the total number of students enrolled in the
Mathematics course.
(g) Write a query to display the average age of students enrolled in the
Chemistry course.
Q2: Create a database named CompanyDB. Create a table named Employees with
the following fields:
-EmployeeID (INT, PRIMARY KEY, AUTO_INCREMENT)
-FirstName (VARCHAR(50), NOT NULL)
-LastName (VARCHAR(50), NOT NULL)
-Department (VARCHAR(30), NOT NULL)
-Salary (DECIMAL(10,2), NOT NULL)
- JoiningDate (DATE, NOT NULL)
Insert at least 10 records into the table and display all the inserted records.
Q3: Add a UNIQUE constraint on the FirstName and LastName combination in the
Employees table. Update the salary of all employees in the Finance department by
10%. Delete the records of employees whose salary is less than 30,000.
Q4: Write a query to display the names of employees whose salary is greater than
the average salary.
Q5: Write a query to delete records from Employees where the LastName starts with
'S'.
Q6: Write a query using GROUP BY and HAVING to display department names where
the average salary is greater than 40000.
Q7: Create another table named Departments with the following fields:
- DepartmentID (INT, PRIMARY KEY, AUTO_INCREMENT)
- DepartmentName (VARCHAR(50), UNIQUE, NOT NULL)
Insert records into the table and display all the inserted records.
(a) Write a query using INNER JOIN to display employee names with their
department names.
(b) Write a query using LEFT JOIN to display all employee names and their
department names. If an employee does not have a department, display NULL.
(c) Write a query using RIGHT JOIN to display all department names and their
employee names. If no employee is assigned to a department, display NULL.
(d) Write a query using FULL OUTER JOIN to display all employee names and
department names. Include records even if there is no match.
Q8: Create a view named HighSalaryEmployees that shows the names and salaries
of employees earning more than 70,000.
Q9: Write a query of the following:
(a) Using GRANT statement to provide SELECT and INSERT privileges on the
Employees table to a user named John.
(b) Using a GRANT statement to allow a user named James to perform all
actions on the Departments table.
(c) Using a REVOKE statement to remove the INSERT privilege on the
Employees table from the user john.
(d) Using a CREATE ROLE statement to create a role named ManagerRole
with SELECT, UPDATE, and DELETE privileges on the Employees table.
(e) To assign the ManagerRole to user John.
(f) To check all the privileges assigned to the user John.
Q10: Begin a transaction to update the salary of an employee in the IT department
by 5%. Rollback the transaction.
