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

------------------------------------------------------------
1. Introduction to SQL
------------------------------------------------------------
- SQL = Structured Query Language, used for creating, managing, and querying
databases.
- Categories:
- DDL: CREATE, ALTER, DROP
- DML: INSERT, UPDATE, DELETE
- DQL: SELECT
- RDBMS Examples: MySQL, Oracle, SQL Server.

------------------------------------------------------------
2. MySQL Elements
------------------------------------------------------------
- Literals: constants like 'Delhi', 2025, 12.5, '2025-09-26'.
- Data Types:
- Numeric: INT, FLOAT, DECIMAL
- String: CHAR, VARCHAR
- Date/Time: DATE, TIME, DATETIME, YEAR
- NULL: missing/unknown value (not 0 or blank).
- Comments: -- , # , /* … */
- CHAR vs VARCHAR:
- CHAR = fixed length, padded with spaces.
- VARCHAR = variable length, saves space.

------------------------------------------------------------
3. Database & Table
------------------------------------------------------------
CREATE DATABASE school;
USE school;

CREATE TABLE Student(

roll INT PRIMARY KEY,
name VARCHAR(20),
marks INT,
city VARCHAR(15)
);

DESC Student;

------------------------------------------------------------
4. Inserting Data
------------------------------------------------------------
INSERT INTO Student VALUES(1,'Amit',90,'Delhi');
INSERT INTO Student (roll,name,marks) VALUES(2,'Sita',85);

------------------------------------------------------------
5. SELECT Command
------------------------------------------------------------
SELECT * FROM Student; -- all data
SELECT roll, name FROM Student; -- specific columns
SELECT DISTINCT city FROM Student; -- unique values
SELECT name AS StudentName, marks+5 Bonus FROM Student; -- alias & calculation

------------------------------------------------------------
6. Calculations & NULL Handling
------------------------------------------------------------
SELECT name, marks, marks*12 AnnualMarks FROM Student;
SELECT name, IFNULL(marks,0) FROM Student;

------------------------------------------------------------
7. WHERE Clause
------------------------------------------------------------
- Comparison: =, <>, >, <, >=, <=
- Logical: AND, OR, NOT

Examples:
SELECT * FROM Student WHERE marks > 80;
SELECT * FROM Student WHERE city='Delhi' AND marks>=70;

------------------------------------------------------------
8. Operators
------------------------------------------------------------
BETWEEN:
SELECT * FROM Student WHERE marks BETWEEN 60 AND 90;

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

LIKE (pattern matching, % many chars, _ one char):

SELECT * FROM Student WHERE name LIKE 'A%';

IS NULL:
SELECT * FROM Student WHERE marks IS NULL;

------------------------------------------------------------
9. Operator Precedence
------------------------------------------------------------
1. Arithmetic (*, /, %, +, -)
2. Comparisons (=, <, >, <=, >=, LIKE, IN, BETWEEN, IS)
3. NOT
4. AND
5. OR

------------------------------------------------------------
10. Sorting
------------------------------------------------------------
SELECT * FROM Student ORDER BY marks DESC;
SELECT * FROM Student ORDER BY city ASC, marks DESC;

------------------------------------------------------------
11. Functions
------------------------------------------------------------
String: UCASE(), LCASE(), CONCAT(), LENGTH(), SUBSTRING(), TRIM()
Numeric: ROUND(), POWER(), MOD(), SQRT()
Date/Time: CURDATE(), NOW(), YEAR(), MONTH(), DAYNAME()

------------------------------------------------------------
12. Aggregate Functions
------------------------------------------------------------
SUM(marks), AVG(marks), MAX(marks), MIN(marks)
COUNT(col) → counts non-NULL
COUNT(*) → counts all rows

Examples:
SELECT AVG(marks) FROM Student;
SELECT COUNT(DISTINCT city) FROM Student;

------------------------------------------------------------
13. GROUP BY & HAVING
------------------------------------------------------------
SELECT city, AVG(marks) FROM Student GROUP BY city;
SELECT city, COUNT(*) FROM Student GROUP BY city HAVING COUNT(*) > 2;

- WHERE filters rows before grouping.

- HAVING filters groups after aggregation.

------------------------------------------------------------
14. Record Manipulation
------------------------------------------------------------
UPDATE Student SET marks=95 WHERE roll=1;
DELETE FROM Student WHERE city='Delhi';

------------------------------------------------------------
Key Exam Points
------------------------------------------------------------
- DISTINCT removes duplicates.
- IS NULL instead of = NULL.
- COUNT(*) vs COUNT(col) difference is often asked.
- WHERE vs HAVING is very important.
- Practice queries to get familiar with syntax.