Comprehensive SQL Notes General Rules and Conventions • Capitalization: All SQL keywords (e.g., SELECT, WHERE) are capitalized to differentiate them from field or table names. • Namespace Usage: Always use fully qualified names (TABLE.FIELD) in queries involving multiple tables to avoid ambiguity. • Semicolon Usage: – Mandatory in MS SQL Server to terminate statements. – Optional in MS Access SQL. • Wildcards for Pattern Matching: – ? matches exactly one character. – * matches zero or more characters. • NULL Values: – IS NULL: Finds records with no value. – IS NOT NULL: Excludes records with null values. Core SQL Clauses and Usage 1. SELECT • Extracts data from specific fields. • Supports aggregate functions: – AVG, SUM, MIN, MAX, COUNT. – Syntax: AS to alias results (e.g., AS AvgSalary). • Example: SELECT Name, AVG(Salary) AS AvgSalary FROM Employees; 2. FROM • Specifies the source table(s) for data extraction. • Joins: – INNER JOIN: Combines rows with matching values in both tables. – LEFT JOIN: Includes all rows from the left table, matching rows from the right table. 1 – RIGHT JOIN: Includes all rows from the right table, matching rows from the left table. • Example: SELECT STUDENT.Name, COURSE.Title FROM STUDENT INNER JOIN COURSE ON STUDENT.CourseID = COURSE.CourseID; 3. WHERE • Filters data based on conditions. • Operators: – Comparison: =, >, <, >=, <=, !=. – Logical: AND, OR, NOT. – Pattern Matching: LIKE, IN. • Example: SELECT * FROM Employees WHERE Department = ’HR’ AND Salary > 50000; 4. ORDER BY • Sorts query results in ascending (ASC) or descending (DESC) order. • Example: SELECT Name, Salary FROM Employees ORDER BY Salary DESC; SQL Data Manipulation Commands • INSERT INTO: Adds new records to a table. INSERT INTO Employees (Name, Department, Salary) VALUES (’John Doe’, ’HR’, 60000); • UPDATE: Modifies existing records. UPDATE Employees SET Salary = Salary + 5000 WHERE Department = ’HR’; 2 • DELETE: Removes records from a table. DELETE FROM Employees WHERE Department = ’HR’; Table Management Commands • CREATE TABLE: Creates a new table with specified columns and data types. CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100), Department VARCHAR(50), Salary DECIMAL(10, 2) ); Advanced SQL Techniques Subqueries: • Nested queries used to compute intermediate results. • Example: SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees); Indexing: • Speeds up data retrieval by creating an index on columns. • Example: CREATE INDEX idx_salary ON Employees (Salary); Views: • Virtual tables based on SQL queries. • Example: CREATE VIEW HighEarners AS SELECT Name, Salary FROM Employees WHERE Salary > 80000; 3