Home › Free notes › Class 12 Computer Science › Structured Query Language (SQL)

Structured Query Language (SQL) – Class 12 Computer Science notes, important questions and MCQs

NCERT Class 12 Computer Science, chapter 9. Notes, key terms, important questions with answers and MCQs.

Chapter overview

Structured Query Language (SQL) is the standard language for creating, managing and querying relational databases. MySQL is a popular open source DBMS that uses SQL. SQL commands are grouped into Data Definition Language (DDL) and Data Manipulation Language (DML) and also include querying with SELECT. SQL supports functions, grouping, joins and operations on multiple tables.

DDL, DML and data types

Data types include CHAR(n) (fixed length), VARCHAR(n) (variable length), INT, FLOAT and DATE; constraints include NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY and FOREIGN KEY.
DDL commands define structure: CREATE DATABASE, CREATE TABLE, ALTER TABLE (ADD, DROP, MODIFY columns), DROP TABLE; DESCRIBE shows the structure.
DML commands manipulate data: INSERT INTO table VALUES (...), UPDATE table SET column = value WHERE condition, DELETE FROM table WHERE condition; without WHERE, UPDATE and DELETE affect all rows.

Queries, functions and joins

SELECT columns FROM table WHERE condition retrieves data; DISTINCT removes duplicates; operators include BETWEEN, IN, LIKE (with % for any characters and _ for one character), IS NULL; ORDER BY sorts results (ASC by default, DESC for descending).
Functions: single-row (math functions like POWER(), ROUND(), MOD(); string functions like UPPER(), LOWER(), LENGTH(), SUBSTRING(), TRIM(); date functions like NOW(), YEAR(), MONTH()) and aggregate functions (COUNT(), SUM(), AVG(), MAX(), MIN()); GROUP BY groups rows and HAVING filters groups.
Operations on relations: UNION, INTERSECT, MINUS and Cartesian product; an equi-join combines tables using a common attribute, for example SELECT * FROM STUDENT, FEES WHERE STUDENT.AdmNo = FEES.AdmNo; NATURAL JOIN joins on columns with the same name.

Key terms

SQL: the standard language for relational databases
DDL: commands that define or change the structure of database objects
DML: commands that insert, update, delete or retrieve data
Aggregate function: a function that works on a group of rows and returns one value
Join: combining rows from two or more tables based on a related column

Important questions with answers

Distinguish between DDL and DML.2 marks

DDL (Data Definition Language) commands define and modify the structure of database objects, such as CREATE, ALTER and DROP. DML (Data Manipulation Language) commands work on the data stored in tables, such as INSERT, UPDATE, DELETE and SELECT.

Distinguish between WHERE and HAVING clauses.3 marks

WHERE filters individual rows before grouping and cannot use aggregate functions, for example WHERE Marks > 60. HAVING filters groups after GROUP BY and can use aggregate functions, for example GROUP BY Class HAVING AVG(Marks) > 70.

For a table EMP(EmpNo, Name, Dept, Salary), write SQL queries for common tasks and explain them.5 marks

(a) To display all employees of the Sales department: SELECT * FROM EMP WHERE Dept = 'Sales'; this filters rows with WHERE. (b) To display names and salaries in descending order of salary: SELECT Name, Salary FROM EMP ORDER BY Salary DESC; ORDER BY sorts the output. (c) To increase the salary of all employees in the HR department by 10 percent: UPDATE EMP SET Salary = Salary * 1.10 WHERE Dept = 'HR'; without WHERE, every row would change. (d) To display the number of employees and the average salary in each department: SELECT Dept, COUNT(*), AVG(Salary) FROM EMP GROUP BY Dept; GROUP BY forms one group per department and aggregate functions summarise each group. (e) To display departments whose total salary exceeds 200000: SELECT Dept, SUM(Salary) FROM EMP GROUP BY Dept HAVING SUM(Salary) > 200000; HAVING filters groups. (f) To display names starting with 'R': SELECT Name FROM EMP WHERE Name LIKE 'R%'. (g) To delete the employee with EmpNo 105: DELETE FROM EMP WHERE EmpNo = 105. (h) To add a column for email: ALTER TABLE EMP ADD Email VARCHAR(50). These queries show filtering, sorting, updating, grouping, pattern matching, deletion and structural change.

Quick revision

Data types and constraints.
DDL: CREATE, ALTER, DROP.
DML: INSERT, UPDATE, DELETE, SELECT.
Functions, GROUP BY, HAVING, joins and set operations.

Exam tips

WHERE filters rows before grouping; HAVING filters groups after GROUP BY.
COUNT(*) counts all rows; COUNT(column) ignores NULL values.

Practice MCQs

The SQL command used to add a new row to a table is
1. INSERT INTO
2. UPDATE
3. ALTER
4. CREATE
Show answer
Answer: (a) INSERT INTO. It is a DML command.
CREATE TABLE is an example of
1. DDL
2. DML
3. a function
4. a join
Show answer
Answer: (a) DDL. It defines structure.
The clause used to sort query results is
1. GROUP BY
2. WHERE
3. ORDER BY
4. HAVING
Show answer
Answer: (c) ORDER BY. ASC is the default order.
The query that removes duplicate values in the output is
1. SELECT UNIQUE
2. SELECT ALL
3. SELECT ONLY
4. SELECT DISTINCT
Show answer
Answer: (d) SELECT DISTINCT. DISTINCT removes repeated rows.
The pattern 'A%' with LIKE matches names that
1. start with A
2. end with A
3. contain exactly one A
4. have two characters
Show answer
Answer: (a) start with A. % stands for any sequence of characters.
The command that changes the structure of an existing table, such as adding a column, is
1. UPDATE
2. MODIFY ROW
3. ALTER TABLE
4. INSERT
Show answer
Answer: (c) ALTER TABLE. ALTER TABLE ADD column.
Which clause is used to filter groups formed by GROUP BY
1. WHERE
2. ORDER BY
3. HAVING
4. DISTINCT
Show answer
Answer: (c) HAVING. WHERE cannot use aggregate conditions on groups.
If a column has values 10, NULL, 20, NULL, then COUNT(column) returns
1. 4
2. 2
3. 0
4. 30
Show answer
Answer: (b) 2. COUNT(column) ignores NULLs.

All chapters of Class 12 Computer Science

Written by Kundali IQ from standard, well-established facts to help with revision. This is not an official NCERT publication; always read your textbook too.