SQL Programs for Class 12 to B.Tech

How to Use This SQL Practical

This lesson converts the complete Class 12 RDBMS command sequence into one executable MySQL practical. Do not memorise isolated statements. Create the database and sample tables once, run each numbered query in order, predict the result, and then compare it with the stated output.

SQL keywords are shown in uppercase for readability, although MySQL keywords are case-insensitive. Table and column names use a consistent lowercase style.

Queries 1-8: Database, Table and Sample Data

1. View available databases

SHOW DATABASES;

This lists the databases that the current MySQL user is allowed to see.

2. Create a database

CREATE DATABASE IF NOT EXISTS school_practical;

3. Select the database

USE school_practical;

4. View tables in the selected database

SHOW TABLES;

Before creating a table, the result may be empty.

5. Create the Employee table

CREATE TABLE employee (
    ecode  INT PRIMARY KEY,
    ename  VARCHAR(30) NOT NULL,
    gender CHAR(1),
    grade  CHAR(2),
    gross  DECIMAL(10,2)
);

ecode uniquely identifies a row. DECIMAL(10,2) is safer than a floating type for money because it stores an exact two-decimal value.

6. Insert one row

INSERT INTO employee (ecode, ename, gender, grade, gross)
VALUES (1001, 'Ravi', 'M', 'E4', 50000);

7. Insert multiple rows

INSERT INTO employee VALUES
(1002, 'Akash', 'M', 'A1', 35000),
(1004, 'Neela', 'F', 'B2', 38965),
(1005, 'Sunny', 'M', 'A2', 30000),
(1006, 'Ruby',  'F', 'A1', 45000),
(1009, 'Neema', 'F', 'A2', 52000);

8. Insert a NULL value correctly

CREATE TABLE student_marks (
    roll_no INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    marks INT NULL
);

INSERT INTO student_marks VALUES
(1, 'Arun', NULL),
(2, 'Ravi', 56),
(4, 'Sanjay', NULL);

NULL is written without quotes. It means unknown or unavailable; it is not zero and not an empty string.

Queries 9-15: SELECT, WHERE and DISTINCT

9. Display every column and row

SELECT * FROM employee;

10. Display selected columns

SELECT ecode, ename FROM employee;

11. Display name, grade and salary

SELECT ename, grade, gross FROM employee;

12. Select female employees

SELECT *
FROM employee
WHERE gender = 'F';

13. Select salaries above 48000

SELECT ecode, ename, gross
FROM employee
WHERE gross > 48000;

14. See repeated values

SELECT gender FROM employee;

15. Remove duplicates with DISTINCT

SELECT DISTINCT gender FROM employee;

Queries 16-22: Alias, BETWEEN, IN, LIKE and NULL

16. View the table structure

DESCRIBE employee;
-- DESC employee; gives the same result

17. Give columns readable aliases

SELECT ecode AS employee_code,
       ename AS employee_name,
       gross AS monthly_salary
FROM employee;

In MySQL, use backticks or an unquoted alias for identifiers. Single quotes may be accepted as output labels in some contexts, but they are better reserved for string values.

18. Select an inclusive range with BETWEEN

SELECT ecode, ename, grade, gross
FROM employee
WHERE gross BETWEEN 40000 AND 50000;

19. Match any value from a list

SELECT *
FROM employee
WHERE grade IN ('A1', 'A2');

20. Exclude a list of values

SELECT *
FROM employee
WHERE grade NOT IN ('A1', 'A2');

21. Search text patterns with LIKE

-- Names beginning with R
SELECT ename FROM employee WHERE ename LIKE 'R%';

-- Names whose second character is e
SELECT ename FROM employee WHERE ename LIKE '_e%';

-- Names ending with y
SELECT ename FROM employee WHERE ename LIKE '%y';

% matches zero or more characters. _ matches exactly one character.

22. Search for NULL

SELECT name
FROM student_marks
WHERE marks IS NULL;

Never write marks = NULL; comparisons with NULL are unknown. Use IS NULL or IS NOT NULL.

Queries 23-24: Sorting Results

23. Sort names in ascending order

SELECT ecode, ename, gross
FROM employee
ORDER BY ename ASC;

24. Filter, then sort descending

SELECT ename, gross
FROM employee
WHERE gross > 40000
ORDER BY ename DESC;

The logical idea is: choose rows with WHERE, then order the remaining rows with ORDER BY.

Queries 25-29: Correct UPDATE Queries

25. Change one employee's salary

SELECT * FROM employee WHERE ecode = 1009;

UPDATE employee
SET gross = 55000
WHERE ecode = 1009;

26. Update more than one column

UPDATE employee
SET gross = 58000,
    grade = 'B2'
WHERE ecode = 1001;

27. Increase every salary by exactly 1000

UPDATE employee
SET gross = gross + 1000;

This intentionally has no WHERE clause because every employee is receiving the increment.

28. Double salary for two grades

UPDATE employee
SET gross = gross * 2
WHERE grade IN ('A1', 'A2');

29. Update using two correct conditions

UPDATE employee
SET grade = 'A2'
WHERE ecode = 1004
  AND ename = 'Neela';

The employee name must be compared with ename, not with the grade column.

Queries 30-32: DELETE, TRUNCATE and DROP

30. Delete selected rows

DELETE FROM employee
WHERE grade = 'A1';

31. Remove all rows but keep the table

-- DML form; can be filtered when WHERE is present
DELETE FROM employee;

-- Faster DDL-style operation; no WHERE is allowed
TRUNCATE TABLE employee;

32. Remove the table and its structure

DROP TABLE employee;

Queries 33-39: ALTER TABLE Commands

Recreate the Employee table before testing this section if you ran Query 32.

33. Add a column

ALTER TABLE employee
ADD address VARCHAR(100);

34. Rename a column and define its type

ALTER TABLE employee
CHANGE gross salary DECIMAL(10,2);

35. Rename and widen a text column

ALTER TABLE employee
CHANGE ename employee_name VARCHAR(40);

36. Modify only the data type

ALTER TABLE employee
MODIFY grade VARCHAR(5);

37. Delete a column

ALTER TABLE employee
DROP COLUMN address;

38. Add and remove a primary key

ALTER TABLE employee ADD PRIMARY KEY (ecode);
ALTER TABLE employee DROP PRIMARY KEY;

Do not add the key again if it was already declared in CREATE TABLE.

39. Add and remove a named foreign key

CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(40) NOT NULL
);

ALTER TABLE employee ADD dept_id INT;

ALTER TABLE employee
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (dept_id) REFERENCES department(dept_id);

ALTER TABLE employee
DROP FOREIGN KEY fk_employee_department;

MySQL drops a foreign key by its constraint name, not by writing only DROP FOREIGN KEY.

Queries 40-44: Integrity Constraints

40. NOT NULL and DEFAULT

CREATE TABLE learner (
    student_id INT NOT NULL,
    name VARCHAR(40) NOT NULL,
    score INT DEFAULT 80
);

INSERT INTO learner (student_id, name)
VALUES (10, 'Ravi');

41. UNIQUE

CREATE TABLE customer (
    sid INT UNIQUE,
    last_name VARCHAR(30),
    first_name VARCHAR(30)
);

42. CHECK

CREATE TABLE valid_customer (
    sid INT CHECK (sid > 0),
    last_name VARCHAR(30),
    first_name VARCHAR(30)
);

43. Single-column PRIMARY KEY

CREATE TABLE admission (
    sid INT PRIMARY KEY,
    student_name VARCHAR(40) NOT NULL
);

44. Composite PRIMARY KEY

CREATE TABLE branch_student (
    branch_id INT NOT NULL,
    sid INT NOT NULL,
    student_name VARCHAR(40),
    PRIMARY KEY (branch_id, sid)
);

The pair must be unique. The same sid may appear in another branch, but the same branch-and-student pair cannot repeat.

Queries 45-54: Aggregate Functions and GROUP BY

CREATE TABLE empl (
    empno INT PRIMARY KEY,
    ename VARCHAR(30),
    job VARCHAR(20),
    sal DECIMAL(10,2),
    deptno INT
);

INSERT INTO empl VALUES
(8369, 'Smith', 'Clerk',    2985, 10),
(8499, 'Anya',  'Salesman', 9870, 20),
(8566, 'Amir',  'Salesman', 8760, 30),
(8698, 'Bina',  'Manager',  5643, 20),
(8912, 'Sur',   NULL,       3000, 10);

45-49. AVG, COUNT, MAX, MIN and SUM

-- 45. Average salary
SELECT AVG(sal) AS average_salary FROM empl;

-- 46. Count every row
SELECT COUNT(*) AS total_employees FROM empl;

-- 47. Count only non-NULL jobs
SELECT COUNT(job) AS employees_with_job FROM empl;

-- 48. Maximum and minimum salary
SELECT MAX(sal) AS highest, MIN(sal) AS lowest FROM empl;

-- 49. Total salary
SELECT SUM(sal) AS total_salary FROM empl;

50-54. Grouping reports

-- 50. Employees in each job
SELECT job, COUNT(*) AS employee_count
FROM empl
GROUP BY job;

-- 51. Salary total for each department
SELECT deptno, SUM(sal) AS department_salary
FROM empl
GROUP BY deptno;

-- 52. Average salary for each department
SELECT deptno, AVG(sal) AS average_salary
FROM empl
GROUP BY deptno;

-- 53. Departments whose average exceeds 5000
SELECT deptno, AVG(sal) AS average_salary
FROM empl
GROUP BY deptno
HAVING AVG(sal) > 5000;

-- 54. Grouped report in descending total order
SELECT deptno, COUNT(*) AS employees, SUM(sal) AS total_salary
FROM empl
GROUP BY deptno
ORDER BY total_salary DESC;

WHERE filters rows before grouping; HAVING filters completed groups. This distinction is frequently tested in viva and theory exams.

Corrections to Common Wrong Queries

Practice Questions and Viva

1. List employees whose salary is not between 35000 and 50000.
2. Display the two highest salaries without changing the table.
3. Count employees grade-wise and show only grades having at least two employees.
4. Add an email column that cannot contain duplicates.
5. Explain why a foreign key prevents an invalid department number.
6. Predict the difference between COUNT(*), COUNT(job) and COUNT(DISTINCT job).

Revision Summary

• DDL defines structures: CREATE, ALTER, TRUNCATE and DROP.
• DML changes rows: INSERT, UPDATE and DELETE; SELECT retrieves them.
• WHERE filters rows; HAVING filters groups.
• BETWEEN includes both limits, IN matches a list, and LIKE uses % and _.
• NULL requires IS NULL or IS NOT NULL.
• Constraints protect accuracy: NOT NULL, DEFAULT, UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY.
• Always verify UPDATE and DELETE conditions with SELECT first.