SQL Complete Notes

SQL Intro

SQL (Structured Query Language) is the language used to manage and manipulate relational databases.

SQL Syntax

Basic SQL syntax includes commands like SELECT, INSERT, UPDATE, DELETE. Statements end with a

semicolon.

Example:

SELECT * FROM Customers;

SQL Select

Used to select data from a database.

Example:

SELECT name FROM Students;

SQL Select Distinct

Returns only distinct (different) values.

Example:

SELECT DISTINCT country FROM Customers;

SQL Where

Filters records based on a condition.

Example:

SELECT * FROM Customers WHERE age > 30;

SQL Order By

Sorts the result set in ascending or descending order.

Page 1
 SQL Complete Notes

Example:

SELECT * FROM Products ORDER BY price DESC;

SQL And

Combines two conditions.

Example:

SELECT * FROM Users WHERE age > 18 AND city = 'Delhi';

SQL Or

Returns data if any condition is true.

Example:

SELECT * FROM Users WHERE city = 'Delhi' OR city = 'Mumbai';

SQL Not

Negates a condition.

Example:

SELECT * FROM Users WHERE NOT city = 'Delhi';

SQL Insert Into

Inserts new data.

Example:

INSERT INTO Students (name, age) VALUES ('John', 22);

SQL Null Values

Tests for NULL values.

Example:

Page 2
 SQL Complete Notes

SELECT * FROM Orders WHERE delivery_date IS NULL;

SQL Update

Modifies existing records.

Example:

UPDATE Students SET age = 23 WHERE name = 'John';

SQL Delete

Deletes records.

Example:

DELETE FROM Students WHERE name = 'John';

SQL Select Top

Limits the number of returned records.

Example:

SELECT TOP 5 * FROM Products;

SQL Aggregate Functions

Performs calculations on multiple values.

Example:

SELECT AVG(price) FROM Products;

SQL Min and Max

Returns the smallest or largest value.

Example:

SELECT MIN(price), MAX(price) FROM Products;

Page 3
 SQL Complete Notes

SQL Count

Counts rows.

Example:

SELECT COUNT(*) FROM Students;

SQL Sum

Returns the sum of a numeric column.

Example:

SELECT SUM(salary) FROM Employees;

SQL Avg

Calculates average.

Example:

SELECT AVG(age) FROM Students;

SQL Like

Searches for a specified pattern.

Example:

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

SQL Wildcards

Symbols used with LIKE.

Example:

% (any characters), _ (single character)

SQL In

Page 4
 SQL Complete Notes

Specifies multiple possible values.

Example:

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

SQL Between

Selects values within a range.

Example:

SELECT * FROM Products WHERE price BETWEEN 10 AND 100;

SQL Aliases

Renames a column or table.

Example:

SELECT name AS student_name FROM Students;

SQL Joins

Combines rows from two or more tables.

SQL Inner Join

Returns records with matching values in both tables.

Example:

SELECT * FROM A INNER JOIN B ON [Link] = [Link];

SQL Left Join

Returns all records from left, and matched from right.

Example:

SELECT * FROM A LEFT JOIN B ON [Link] = [Link];

Page 5
 SQL Complete Notes

SQL Right Join

Returns all records from right, and matched from left.

Example:

SELECT * FROM A RIGHT JOIN B ON [Link] = [Link];

SQL Full Join

Returns all records when there is a match in one of the tables.

Example:

SELECT * FROM A FULL OUTER JOIN B ON [Link] = [Link];

SQL Self Join

A table is joined with itself.

Example:

SELECT [Link], [Link] FROM Employees A, Employees B WHERE A.manager_id = [Link];

SQL Union

Combines result sets.

Example:

SELECT name FROM A UNION SELECT name FROM B;

SQL Group By

Groups rows with the same values.

Example:

SELECT COUNT(*), city FROM Users GROUP BY city;

SQL Having

Page 6
 SQL Complete Notes

Used with GROUP BY to filter results.

Example:

SELECT city, COUNT(*) FROM Users GROUP BY city HAVING COUNT(*) > 1;

SQL Exists

Returns true if subquery returns rows.

Example:

SELECT name FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE [Link] =

Orders.customer_id);

SQL Any, All

Compares a value to any/all values in another set.

Example:

SELECT name FROM Products WHERE price > ALL (SELECT price FROM OldProducts);

SQL Select Into

Copies data into a new table.

Example:

SELECT * INTO Backup_Customers FROM Customers;

SQL Insert Into Select

Inserts data from one table into another.

Example:

INSERT INTO Archive_Customers SELECT * FROM Customers WHERE city = 'Delhi';

SQL Case

Page 7
 SQL Complete Notes

Adds conditional logic.

Example:

SELECT name, age, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS status FROM Students;

SQL Null Functions

Handles NULL values with functions like ISNULL() or COALESCE().

Example:

SELECT name, ISNULL(phone, 'N/A') FROM Users;

SQL Stored Procedures

A stored procedure is a prepared SQL code.

Example:

CREATE PROCEDURE GetCustomers AS SELECT * FROM Customers;

SQL Comments

Used to add explanations in code.

Example:

-- This is a comment

SQL Operators

Includes arithmetic, comparison, logical operators. Example: +, -, =, <>, AND, OR

SQL Create DB

Creates a new database.

Example:

CREATE DATABASE school;

Page 8
 SQL Complete Notes

SQL Drop DB

Deletes a database.

Example:

DROP DATABASE school;

SQL Backup DB

Backs up a database.

Example:

BACKUP DATABASE school TO DISK = '[Link]';

SQL Create Table

Creates a new table.

Example:

CREATE TABLE Students (id INT, name VARCHAR(50));

SQL Drop Table

Deletes a table.

Example:

DROP TABLE Students;

SQL Alter Table

Modifies a table.

Example:

ALTER TABLE Students ADD age INT;

SQL Constraints

Page 9
 SQL Complete Notes

Used to specify rules. Includes NOT NULL, UNIQUE, PRIMARY KEY, etc.

SQL Not Null

Ensures column can't have NULL values.

Example:

name VARCHAR(50) NOT NULL

SQL Unique

Ensures all values are different.

Example:

email VARCHAR(100) UNIQUE

SQL Primary Key

Uniquely identifies each row.

Example:

id INT PRIMARY KEY

SQL Foreign Key

Links two tables.

Example:

FOREIGN KEY (student_id) REFERENCES Students(id)

SQL Check

Limits the value range.

Example:

CHECK (age >= 18)

Page 10
 SQL Complete Notes

SQL Default

Sets a default value.

Example:

status VARCHAR(10) DEFAULT 'active'

SQL Index

Creates indexes for faster retrieval.

Example:

CREATE INDEX idx_name ON Students(name);

SQL Auto Increment

Auto-generates numbers.

Example:

id INT AUTO_INCREMENT

SQL Dates

Handles date/time values.

Example:

SELECT * FROM Orders WHERE order_date = '2024-01-01';

SQL Views

A virtual table.

Example:

CREATE VIEW view_name AS SELECT name FROM Students;

SQL Injection

Page 11
 SQL Complete Notes

A security vulnerability allowing code injection via input fields. Prevented using prepared statements.

SQL Hosting

Refers to hosting SQL databases on cloud or servers (e.g., AWS RDS, Azure SQL).

SQL Data Types

Defines types of data. Examples: INT, VARCHAR, DATE, FLOAT.

Page 12