Notes – Chapter 12: Simple Queries in SQL (Class 12 CBSE)

Source: Based on Sumita Arora (Chapter: Simple Queries in SQL) + provided PDFs

------------------------------------------------------------
1. Introduction to SQL
------------------------------------------------------------
- SQL (Structured Query Language) is the standard language used to create, manage,
and query relational databases (tables with rows and columns).
- Main uses: define database structure (DDL), manage data (DML), control access
(DCL), and query/retrieve information (DQL).
- Common RDBMS: MySQL, Oracle, PostgreSQL, SQL Server.

Key categories of SQL commands:

- DDL (Data Definition Language): CREATE, ALTER, DROP, RENAME.
- DML (Data Manipulation Language): INSERT, UPDATE, DELETE, TRUNCATE.
- DQL (Data Query Language): SELECT.
- DCL (Data Control Language): GRANT, REVOKE.

------------------------------------------------------------
2. MySQL Elements: Literals, Data Types, NULL, Comments
------------------------------------------------------------
- Literals: fixed constant values used in SQL statements. Examples:
'Delhi' (string), 100 (numeric), 12.56 (real), '2025-09-26' (date). String and
date literals usually enclosed in single quotes.
- Data Types:
- Numeric: INT, SMALLINT, TINYINT, BIGINT, FLOAT, DOUBLE, DECIMAL
- String: CHAR, VARCHAR, TEXT, BLOB
- Date & Time: DATE, TIME, DATETIME, TIMESTAMP, YEAR
- NULL: indicates missing or unknown value. NULL is NOT the same as 0 or empty
string.
- Comments: used for documentation and ignored by SQL engine
- Single-line: -- comment or # comment
- Multi-line: /* comment */

CHAR vs VARCHAR (important)

- CHAR(n): fixed-length string; storage always uses space for n characters; if
input is shorter, it is padded with spaces on the right. Best for fixed-size data
(codes, status 'A'/'B').
- VARCHAR(n): variable-length string; storage equals actual string length + a small
overhead. Best for names, addresses where length varies.
- Example:
CREATE TABLE t1 (c1 CHAR(10), c2 VARCHAR(10));
INSERT INTO t1 VALUES('Cat','Cat');
The stored c1 will be 'Cat ' (padded) while c2 will be 'Cat'.

Note: Oracle has VARCHAR2 which treats empty string as NULL (Oracle-specific
detail).

------------------------------------------------------------
3. Creating and Using a Database and Tables
------------------------------------------------------------
- Create a database and switch to it:
CREATE DATABASE school;
USE school;

- Create a table:
CREATE TABLE Student (
roll INT PRIMARY KEY,
 name VARCHAR(50),
marks INT,
city VARCHAR(30)
);

- Describe table structure:

DESC Student;

- Modify table structure:

ALTER TABLE Student ADD COLUMN class INT;
ALTER TABLE Student DROP COLUMN class;

- Drop table:
DROP TABLE Student;

------------------------------------------------------------
4. INSERT (Adding Records)
------------------------------------------------------------
- Insert values in order of table columns:
INSERT INTO Student VALUES (1, 'Amit', 90, 'Delhi');

- Insert values specifying columns:

INSERT INTO Student (roll, name, marks) VALUES (2, 'Sita', 85);

- Notes:
- String/date literals must appear in quotes.
- If a column is omitted and it has a DEFAULT value, that default is used;
otherwise if nullable, NULL is stored.

------------------------------------------------------------
5. SELECT – Retrieving Data
------------------------------------------------------------
- Basic select:
SELECT * FROM Student; -- All columns & rows
SELECT roll, name FROM Student;-- Specific columns

- DISTINCT:
SELECT DISTINCT city FROM Student; -- Removes duplicate city values

- Column aliases (temporary headings in output):

SELECT name AS StudentName, marks AS "Total Marks" FROM Student;
- Use AS to give aliases; double quotes (or appropriate quoting) can be used if
alias contains spaces.

- Selecting computed columns (calculations in select list):

SELECT name, marks, marks + 5 AS MarksWithBonus FROM Student;

------------------------------------------------------------
6. Performing Calculations (with and without tables)
------------------------------------------------------------
- Simple arithmetic without tables:
SELECT 10 * 2;
SELECT 100 + 200 FROM DUAL; -- DUAL: a dummy table used in some DBMS to run
expressions (MySQL supports SELECT without FROM too)

- Arithmetic with table data:

SELECT name, marks, marks * 12 AS AnnualMarks FROM Student;

------------------------------------------------------------
7. Handling NULLs and IFNULL()
------------------------------------------------------------
- NULL means unknown/missing value.
- Use IFNULL(expression, substitute) (MySQL) to replace NULL for display:
SELECT name, IFNULL(marks, 0) FROM Student;
- COALESCE(expr1, expr2, ...) is SQL-standard alternative: returns first non-NULL
argument.

Notes:
- Aggregate functions (SUM, AVG, COUNT(col)) ignore NULL values in column values
(except COUNT(*) which counts rows).

------------------------------------------------------------
8. Putting Text / Symbols in Output
------------------------------------------------------------
- You can show text and symbols in results using literals or CONCAT:
SELECT name, ' scored ', marks, ' marks' FROM Student;
SELECT name, CONCAT('Rs. ', marks) AS SalaryText FROM Student;

------------------------------------------------------------
9. WHERE Clause – Filtering Rows
------------------------------------------------------------
- Syntax: SELECT ... FROM table WHERE condition;
- Comparison operators: =, <>, !=, >, <, >=, <=
- Logical operators: AND, OR, NOT
- Examples:
SELECT * FROM Student WHERE marks > 80;
SELECT name FROM Student WHERE city = 'Delhi' AND marks >= 70;
SELECT * FROM Student WHERE NOT city = 'Mumbai';

NULL in WHERE:
- To check for NULL, use IS NULL or IS NOT NULL:
SELECT * FROM Student WHERE marks IS NULL;
SELECT * FROM Student WHERE marks IS NOT NULL;

------------------------------------------------------------
10. Important Operators: BETWEEN, IN, LIKE, IS NULL
------------------------------------------------------------
- BETWEEN: inclusive range
SELECT * FROM Student WHERE marks BETWEEN 60 AND 90;

- IN: match any of listed values (alternative to multiple ORs)

SELECT * FROM Student WHERE city IN ('Delhi', 'Mumbai');

- LIKE: pattern matching

- % : matches zero or more characters
- _ : matches exactly one character
SELECT * FROM Student WHERE name LIKE 'A%'; -- names starting with A
SELECT * FROM Student WHERE name LIKE '_i_a'; -- 4-letter names with i as 2nd and
a as last char

- IS NULL: checks for NULL (see above).

------------------------------------------------------------
11. Operator Precedence (Evaluation Order)
------------------------------------------------------------
- When multiple operators are used, expressions are evaluated according to
precedence:
1. Arithmetic: *, /, MOD, DIV
 2. Arithmetic: +, -
3. Comparison: =, <>, <, >, <=, >=, BETWEEN, IN, LIKE, IS
4. NOT
5. AND
6. OR
- Use parentheses () to force a desired order.

------------------------------------------------------------
12. Sorting Results – ORDER BY
------------------------------------------------------------
- Default sort is ascending (ASC). Use DESC for descending.
SELECT * FROM Student ORDER BY marks DESC;
SELECT * FROM Student ORDER BY city ASC, marks DESC; -- sort by city, then by
marks within each city

------------------------------------------------------------
13. SQL Functions (String, Numeric, Date/Time)
------------------------------------------------------------
String functions (examples):
- UCASE(column) or UPPER(column) -> uppercase text
- LCASE(column) or LOWER(column) -> lowercase text
- CONCAT(a, b, c) -> join strings
- LENGTH(column) -> length of string
- SUBSTRING(column, start, length) -> extract substring
- LTRIM(), RTRIM(), TRIM() -> remove spaces

Numeric functions:
- ROUND(number, decimals)
- MOD(a,b) -> remainder of a/b
- POWER(base, exponent)
- SQRT(x) -> square root
- ABS(x) -> absolute value
- TRUNCATE(x, d) -> truncate without rounding (MySQL)

Date & Time functions:

- CURDATE() -> current date
- NOW() -> current date and time
- DATE(expr) -> extract date from datetime
- YEAR(date), MONTH(date), DAYNAME(date), DAYOFMONTH(date)

Note: Behavior and names may vary slightly across DBMS (MySQL/Oracle/Postgres).

------------------------------------------------------------
14. Aggregate Functions (Summary Functions)
------------------------------------------------------------
- SUM(column) -> sum of values (ignores NULL)
- AVG(column) -> average (ignores NULL)
- MAX(column) -> maximum value
- MIN(column) -> minimum value
- COUNT(column) -> counts non-NULL values in the column
- COUNT(*) -> counts rows in result set (including rows with NULL values in
columns)

COUNT(*) vs COUNT(column):
- COUNT(*) counts all rows returned by the query (including rows where specific
columns are NULL).
- COUNT(column) counts only rows where that column is NOT NULL.
Example:
CREATE TABLE sample (id INT, val INT);
 INSERT INTO sample VALUES (1, 10), (2, NULL), (3, 5);
SELECT COUNT(*) FROM sample; -- returns 3
SELECT COUNT(val) FROM sample; -- returns 2 (NULL ignored)
- COUNT(DISTINCT column) returns number of distinct non-NULL values.

Note: All aggregate functions ignore NULL values except COUNT(*) which counts rows.

------------------------------------------------------------
15. GROUP BY and HAVING (Grouping & Filtering Groups)
------------------------------------------------------------
- GROUP BY divides rows into groups based on one or more columns. Aggregate
functions are applied per group.
SELECT city, AVG(marks) FROM Student GROUP BY city;

- HAVING filters groups (useful to restrict aggregated results):

SELECT city, COUNT(*) AS student_count FROM Student GROUP BY city HAVING COUNT(*)
> 2;

- Important rule: In standard SQL, any column in the SELECT list that is not inside
an aggregate function must appear in GROUP BY. (Some databases allow extensions,
but follow this rule for assignments/exams.)

Examples:
- Incorrect (will cause error in strict SQL):
SELECT name, AVG(marks) FROM Student GROUP BY city; -- name is neither grouped
nor aggregated
- Correct:
SELECT city, name, AVG(marks) FROM Student GROUP BY city, name; -- group by both
columns

------------------------------------------------------------
16. Using HAVING vs WHERE
------------------------------------------------------------
- WHERE filters rows BEFORE grouping and cannot contain aggregate functions.
SELECT * FROM Student WHERE marks > 70;
- HAVING filters AFTER grouping and may contain aggregate functions.
SELECT city, AVG(marks) FROM Student GROUP BY city HAVING AVG(marks) > 75;

------------------------------------------------------------
17. UPDATE and DELETE (Modifying Data)
------------------------------------------------------------
- UPDATE table to change rows:
UPDATE Student SET marks = marks + 5 WHERE roll = 2;

- DELETE rows:
DELETE FROM Student WHERE city = 'Delhi';

- Note: Without WHERE, UPDATE and DELETE affect all rows (use carefully).

------------------------------------------------------------
18. DESC (Describe) Command
------------------------------------------------------------
- Use DESC table_name; to view column names, data types, and attributes for the
table.

------------------------------------------------------------
19. Column Constraints (Brief)
------------------------------------------------------------
- PRIMARY KEY: Unique row identifier, NOT NULL by default.
 CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(20));
- UNIQUE: Ensures all values in a column are unique.
- NOT NULL: Column must have a value.
- DEFAULT: Default value when none is provided.
- FOREIGN KEY: Enforces referential integrity (reference to primary key of another
table).

Examples:
CREATE TABLE Student (
roll INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
grade INT DEFAULT 10
);

------------------------------------------------------------
20. Exporting and Importing Databases (MySQL example)
------------------------------------------------------------
- Export all databases to a SQL file using mysqldump (run in command prompt /
terminal):
mysqldump --all-databases > C:\mydb.sql

- Export a single database:

mysqldump myschool > C:\myschool.sql

- Import back into MySQL:

mysql> SOURCE C:\myschool.sql;

(These commands require access to the MySQL server via command line and appropriate
permissions.)

------------------------------------------------------------
21. Useful Examples and Sample Queries (Practice)
------------------------------------------------------------
-- Sample table: emp (empno, name, dept, salary)
-- Sample data:
-- 1, 'Ravi', 'Sales', 24000
-- 2, 'Sunny', 'Sales', 35000
-- 3, 'Shobit', 'IT', 30000
-- 4, 'Vikram', 'IT', 27000
-- 5, 'Nitin', 'HR', 45000
-- 6, 'Krish', 'HR', NULL

1) Select all records:

SELECT * FROM emp;

2) Select employee names and departments:

SELECT name, dept FROM emp;

3) Select unique departments:

SELECT DISTINCT dept FROM emp;

4) Employees with salary > 30000:

SELECT * FROM emp WHERE salary > 30000;

5) Employees in Sales or IT:

SELECT * FROM emp WHERE dept IN ('Sales', 'IT');

6) Name starts with 'S':

 SELECT * FROM emp WHERE name LIKE 'S%';

7) Employees with NULL salary:

SELECT * FROM emp WHERE salary IS NULL;

8) Total salary paid:

SELECT SUM(salary) FROM emp;

9) Average salary in Sales:

SELECT AVG(salary) FROM emp WHERE dept = 'Sales';

10) Count employees (rows) vs count with non-NULL salary:

SELECT COUNT(*) AS total_rows, COUNT(salary) AS salary_count FROM emp;

11) Group by department, show count and average:

SELECT dept, COUNT(*) AS num_emps, AVG(salary) AS avg_sal FROM emp GROUP BY
dept;

12) Only departments with more than 1 employee:

SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 1;

13) Update salary for employee 1:

UPDATE emp SET salary = salary + 1000 WHERE empno = 1;

14) Delete an employee record:

DELETE FROM emp WHERE empno = 6;

------------------------------------------------------------
22. Common Exam Questions (Short list)
------------------------------------------------------------
- Write a query to list names and cities of students scoring more than 75.
SELECT name, city FROM Student WHERE marks > 75;

- Difference between WHERE and HAVING.

- Difference between COUNT(*) and COUNT(column).
- Explain CHAR vs VARCHAR with examples.
- Write a query to display total salary department-wise.

------------------------------------------------------------
23. Final Notes / Tips for Homework & Exams
------------------------------------------------------------
- Remember: string/date literals in single quotes in most DBMS.
- Use parentheses to control operator precedence.
- When using GROUP BY, either group columns or apply aggregate functions on non-
grouped columns.
- Use IFNULL or COALESCE to handle NULLs in outputs.
- Use DESC to verify table structure and column types.
- Practice by creating small tables and running these queries to see results.
------------------------------------------------------------
End of notes.