Structured Query Language (SQL)

SQL (Structured Query Language): • SQL is the standard language for managing and manipulating relational databases. • It allows users to create, read, update, and delete data; define database structures; and control access. • SQL is declarative — you describe WHAT you want, not HOW to retrieve it. • SQL is case-insensitive for keywords (SELECT = select), but conventionally written in uppercase. Classification of SQL Commands: 1. DDL — Data Definition Language: • Commands that define or modify the database structure (schema). • Changes made are automatically saved (auto-commit). CREATE TABLE: Create a new table. CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Age INT CHECK (Age > 0), Marks FLOAT ); ALTER TABLE: Modify an existing table structure. ALTER TABLE Student ADD COLUMN Email VARCHAR(100); ALTER TABLE Student MODIFY Age INT NOT NULL; ALTER TABLE Student DROP COLUMN Email; DROP TABLE: Permanently delete a table and all its data. DROP TABLE Student; TRUNCATE TABLE: Delete all rows but keep the table structure. TRUNCATE TABLE Student; 2. DML — Data Manipulation Language: • Commands that manage data within the tables. INSERT: Add new rows. INSERT INTO Student VALUES (101, 'Priya', 16, 92.5); INSERT INTO Student (RollNo, Name) VALUES (102, 'Arjun'); SELECT: Retrieve data (also called DQL — Data Query Language). SELECT * FROM Student; SELECT Name, Marks FROM Student WHERE Marks > 80; UPDATE: Modify existing data. UPDATE Student SET Marks = 95 WHERE RollNo = 101; DELETE: Remove rows. DELETE FROM Student WHERE RollNo = 102; DELETE FROM Student; -- deletes all rows (no WHERE) 3. DCL — Data Control Language: • Commands that control access permissions. GRANT: Give privileges to a user. GRANT SELECT, INSERT ON Student TO user1; REVOKE: Remove privileges from a user. REVOKE INSERT ON Student FROM user1; 4. TCL — Transaction Control Language: COMMIT: Save all changes in the current transaction. ROLLBACK: Undo changes since the last COMMIT. SAVEPOINT: Set a point to which you can later ROLLBACK.

SELECT Statement: • The SELECT statement is the most important SQL command — it retrieves data from one or more tables. • Basic syntax: SELECT column1, column2, ... FROM table_name [WHERE condition] [GROUP BY column] [HAVING condition] [ORDER BY column [ASC|DESC]]; Consider a table: Student(RollNo, Name, Class, Marks, City) 1. Selecting Columns: SELECT * FROM Student; -- all columns SELECT Name, Marks FROM Student; -- specific columns SELECT DISTINCT City FROM Student; -- unique values only 2. WHERE Clause — Filtering Rows: • Filters rows based on a condition. SELECT * FROM Student WHERE Marks >= 90; SELECT * FROM Student WHERE Class = '12A' AND Marks > 75; SELECT * FROM Student WHERE City IN ('Delhi', 'Mumbai'); SELECT * FROM Student WHERE Name LIKE 'P%'; -- starts with P SELECT * FROM Student WHERE Marks BETWEEN 70 AND 90; SELECT * FROM Student WHERE Marks IS NULL; 3. ORDER BY Clause — Sorting Results: • Sorts the result set. Default is ASC (ascending); use DESC for descending. SELECT * FROM Student ORDER BY Marks DESC; SELECT * FROM Student ORDER BY Class ASC, Marks DESC; -- sort by two columns 4. GROUP BY Clause — Aggregating Data: • Groups rows with the same value in a column — used with aggregate functions. • Aggregate functions: COUNT(), SUM(), AVG(), MAX(), MIN() SELECT Class, AVG(Marks) AS AvgMarks FROM Student GROUP BY Class; SELECT Class, COUNT(*) AS NumStudents FROM Student GROUP BY Class; SELECT City, MAX(Marks) FROM Student GROUP BY City; 5. HAVING Clause — Filtering Groups: • Filters groups created by GROUP BY. Similar to WHERE, but applied AFTER grouping. • WHERE cannot be used with aggregate functions — HAVING is used instead. SELECT Class, AVG(Marks) FROM Student GROUP BY Class HAVING AVG(Marks) > 75; -- Students with class count > 30: SELECT Class, COUNT(*) AS Students FROM Student GROUP BY Class HAVING COUNT(*) > 30; Order of Execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Aggregate Functions: • Aggregate functions perform a calculation on a set of values and return a single value. • Used with SELECT, GROUP BY, and HAVING clauses. 1. COUNT(column | *): Counts the number of rows / non-NULL values. SELECT COUNT(*) FROM Student; -- total rows SELECT COUNT(Marks) FROM Student; -- rows where Marks is NOT NULL SELECT COUNT(DISTINCT City) FROM Student; -- distinct cities 2. SUM(column): Returns the sum of numeric values. SELECT SUM(Marks) FROM Student WHERE Class = '12A'; 3. AVG(column): Returns the average of numeric values. SELECT AVG(Marks) FROM Student; 4. MAX(column): Returns the highest value. SELECT MAX(Marks) FROM Student; SELECT Name, MAX(Marks) FROM Student; -- name of top scorer 5. MIN(column): Returns the lowest value. SELECT MIN(Marks) FROM Student; SQL Joins: • A JOIN combines rows from two or more tables based on a related column (usually a foreign key relationship). Example Tables: Student(RollNo, Name, Marks) Enrollment(RollNo, CourseID) 1. INNER JOIN (most common): • Returns only rows where the join condition is satisfied in BOTH tables — matched rows only. SELECT Student.Name, Enrollment.CourseID FROM Student INNER JOIN Enrollment ON Student.RollNo = Enrollment.RollNo; -- Only students who are enrolled in at least one course. 2. LEFT JOIN (LEFT OUTER JOIN): • Returns ALL rows from the LEFT table, plus matched rows from the right table. • If no match in the right table, NULL is returned for right table columns. SELECT Student.Name, Enrollment.CourseID FROM Student LEFT JOIN Enrollment ON Student.RollNo = Enrollment.RollNo; -- All students, including those not enrolled in any course (CourseID = NULL). 3. RIGHT JOIN (RIGHT OUTER JOIN): • Returns ALL rows from the RIGHT table, plus matched rows from the left table. -- All enrollment records, including those for students not in the Student table. 4. FULL OUTER JOIN: • Returns rows when there is a match in EITHER table. • All rows from both tables; NULL where there is no match. Cartesian Product (CROSS JOIN): SELECT * FROM Student, Enrollment; -- every Student row × every Enrollment row -- Produces n×m rows (rarely intended; usually a mistake).