GRADE:12
SUB: COMPUTER SCIENCE TOPIC: DBMS
Question: Write the command to display all databases available in your
SHOW DATABASES;
Question: Create a database named "VehicleDB" and then drop it.
CREATE DATABASE VehicleDB;
DROP DATABASE VehicleDB;
Question: After selecting a database, write the command to show all tables in it.
SHOW TABLES;
Question: Create a table named "Students" with columns: RollNo (INT, PRIMARY KEY),
Name (VARCHAR(50)), Age (INT), and Grade (CHAR).
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Grade CHAR
);
Question: Write the command to view the structure of the "Students" table.
DESCRIBE Students;
Question: Add a column "Address" (VARCHAR(100)) to the Students table, then
remove the "Grade" column.
ALTER TABLE Students ADD Address VARCHAR(100);
ALTER TABLE Students DROP COLUMN Grade;
Question: Insert 3 records into the Students table.
INSERT INTO Students VALUES
(1, 'Rahul', 20, 'Delhi'),
(2, 'Priya', 19, 'Mumbai'),
(3, 'Amit', 21, 'Bangalore');
8. Select with WHERE Clause
Question: Select students who are older than 19.
SELECT * FROM Students WHERE Age > 19;
Question: Update Rahul's age to 21.
UPDATE Students SET Age = 21 WHERE Name = 'Rahul';
Question: Delete all students from Mumbai.
DELETE FROM Students WHERE Address = 'Mumbai';
Question: Select students aged between 18 and 20 who live in either Delhi or SELECT
* FROM Students
WHERE Age BETWEEN 18 AND 20
AND (Address = 'Delhi' OR Address = 'Bangalore');
Question: Display unique student ages with the column heading "Student_Age".
SELECT DISTINCT Age AS Student_Age FROM Students;
Question: Find all students who don't have an address recorded.
SELECT * FROM Students WHERE Address IS NULL;
Question: Calculate the average age, maximum age, and count of students.
SELECT AVG(Age) AS AvgAge, MAX(Age) AS MaxAge, COUNT(*) AS TotalStudents
FROM Students;
Question: List students whose names start with 'A', ordered by age descending.
SELECT * FROM Students WHERE Name LIKE 'A%' ORDER BY Age DESC;
Question: Find the average age of students from Delhi, Mumbai, or Bangalore who are
between 18 and 22 years old.
SELECT AVG(Age) AS AvgAge
FROM Students
WHERE Address IN ('Delhi', 'Mumbai', 'Bangalore')
AND Age BETWEEN 18 AND 22;
Question: Display all databases present in your MySQL server.
SHOW DATABASES;
Question: Remove a database named "TempDB" if it exists.
DROP DATABASE IF EXISTS TempDB;
Question: Create a table "Employees" with columns: EmpID (INT, PRIMARY KEY),
Name (VARCHAR(50)), Department (VARCHAR(30)), and Salary (DECIMAL(10,2)).
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(30),
Salary DECIMAL(10,2)
);
Question: Display the structure of the "Employees" table.
DESCRIBE Employees;
Question: Add a "JoinDate" (DATE) column to Employees table, then remove the
"Department" column.
ALTER TABLE Employees ADD JoinDate DATE;
ALTER TABLE Employees DROP COLUMN Department;
Question: Insert 3 records into Employees table with sample data.
INSERT INTO Employees VALUES
(101, 'John Smith', 75000.00, '2020-05-15'),
(102, 'Sarah Johnson', 82000.00, '2019-11-20'),
(103, 'Michael Brown', NULL, '2021-03-10');
Question: Select employees earning more than ₹80,000.
SELECT * FROM Employees WHERE Salary > 80000;
Question: Update Michael Brown's salary to ₹68,500.
UPDATE Employees SET Salary = 68500.00 WHERE Name = 'Michael Brown';
Question: Delete all employees who joined before 2020.
DELETE FROM Employees WHERE JoinDate < '2020-01-01';
Question: Display employee names with their increased salaries (current salary + 10%
bonus).
SELECT Name, Salary, Salary * 1.10 AS IncreasedSalary FROM Employees;
Question: Find employees who earn between ₹70,000 and ₹85,000 or have no salary
recorded.
SELECT * FROM Employees
WHERE (Salary BETWEEN 70000 AND 85000) OR Salary IS NULL;
Question: Find employees whose names start with 'S' and have 'h' as the third letter.
SELECT * FROM Employees WHERE Name LIKE 'S_h%';
Question: Calculate total salary expenditure, average salary, and number of
employees.
SELECT SUM(Salary) AS TotalSalary,
AVG(Salary) AS AverageSalary,
COUNT(*) AS EmployeeCount FROM Employees;
Question: Display all unique salary values in descending order.
SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC;
Question: Count the number of employees in each department.
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees GROUP BY Department;
Expected Output:
Department | EmployeeCount
-----------+--------------
Sales |3
Marketing |2
HR |1
IT |1
Question: Find departments where the average salary is more than ₹75,000.
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 75000;
Expected Output:
Department | AvgSalary
-----------+----------
Marketing | 85000.00
IT | 90000.00
Question: Find departments that have more than 2 employees.
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees GROUP BY Department HAVING COUNT(*) > 2;
Expected Output:
Department | EmployeeCount
-----------+--------------
Sales |3
Query: List employees with their department names
SELECT [Link], [Link], [Link]
FROM Employees e, Departments d
WHERE [Link] = [Link];
Query: Find Marketing employees earning > ₹80,000.
SELECT [Link], [Link]
FROM Employees e, Departments d
WHERE [Link] = [Link] AND [Link] = 'Marketing'
AND [Link] > 80000;
Query: List employees with department info, sorted by salary descending.
SELECT [Link], [Link], [Link]
FROM Employees e, Departments d
WHERE [Link] = [Link]
ORDER BY [Link] DESC;
Query: List employees with their departments
SELECT [Link], [Link], [Link]
FROM Employees e, Departments d
WHERE [Link] = [Link];
Query: Find Sales employees earning < ₹70,000.
SELECT Name, Salary
FROM Employees e, Departments d
WHERE [Link] = [Link]
AND DeptName = 'Sales' AND Salary < 70000;