📘 SQL Notes (Improved & Structured)

1. AUTO_INCREMENT

Used to automatically generate unique IDs.

CREATE TABLE Customer (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50)

);

2. ALIAS

Temporary name for a column or table (for better readability).

SELECT name AS cust_name FROM Customer;

3. ALTER & RENAME

 Rename Column

ALTER TABLE Customer RENAME COLUMN fname TO lname;

 Add Column

ALTER TABLE Book ADD COLUMN author VARCHAR(20);

 Drop Column

ALTER TABLE Book DROP COLUMN author;

 Rename Table

ALTER TABLE Contact RENAME TO MyContact;

 Change Datatype

ALTER TABLE Student MODIFY name VARCHAR(10);

4. String Functions

 Concatenate

SELECT id, CONCAT(fname, lname) AS fullname FROM Customer;

 Concatenate with Separator

SELECT id, CONCAT_WS('-', fname, lname, pincode) AS details FROM Customer;

 Substring
SELECT SUBSTRING('Sudhir Nishad', 1, 6); -- "Sudhir"

SELECT SUBSTRING('Sudhir Nishad', -5); -- "Nishad"

5. DISTINCT

Remove duplicate values.

SELECT DISTINCT acc_type FROM Customer;

6. ORDER BY

Sort results.

SELECT * FROM Customer ORDER BY fname ASC; -- default ASC

SELECT * FROM Customer ORDER BY fname DESC;

7. LIKE & NOT LIKE

Pattern matching:

SELECT * FROM Customer WHERE name LIKE 'So%'; -- starts with "So"

SELECT * FROM Customer WHERE name LIKE '____'; -- exactly 4 characters

SELECT * FROM Customer WHERE fname NOT LIKE 'S%';

8. LIMIT

Restrict output.

SELECT * FROM Customer LIMIT 0,2; -- start at row 0, fetch 2 rows

9. Aggregate Functions

 MAX / MIN

SELECT MAX(salary) FROM Customer;

SELECT MIN(salary) FROM Customer;

 COUNT

SELECT COUNT(*) FROM Customer;

SELECT COUNT(DISTINCT name) FROM Customer;

 SUM & AVG

SELECT SUM(salary) FROM Customer;

SELECT AVG(salary) FROM Customer;

SELECT dept, SUM(salary)

FROM Employees

GROUP BY dept;

10. Subqueries

SELECT *

FROM Customer

WHERE salary = (SELECT MAX(salary) FROM Customer);

11. Data Types

 DECIMAL(a, b): total a digits, b digits after decimal.

 FLOAT: ~7 digits, 4 bytes.

 DOUBLE: ~15 digits, 8 bytes.

12. Date & Time

CREATE TABLE Joining (

doj DATE,

login_time TIME,

join_datetime DATETIME

);

INSERT INTO Joining VALUES ('2023-01-22','16:15:20','2023-01-22 15:55:10');

 Functions:
CURDATE(), CURTIME(), NOW()

 Extract parts:

SELECT DAYNAME(doj), DAYOFMONTH(doj) FROM Joining;

 Format:

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y') AS date;

 Difference & Add/Subtract:

SELECT DATEDIFF('2025-01-22','2024-01-22');

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

13. Operators

 Relational: >, <, >=, <=, !=

 Logical: AND, OR, NOT

 IN / NOT IN

SELECT * FROM Employees WHERE dept IN ('Cash','Loan');

 BETWEEN

SELECT * FROM Employees WHERE salary BETWEEN 30000 AND 50000;

14. CASE Expression

SELECT emp_id, fname,

CASE

WHEN salary >= 45000 THEN 'High Salary'

ELSE 'Low Salary'

END AS salary_category

FROM Employees;

15. NULL Checks

SELECT * FROM Employees WHERE fname IS NULL;

16. Constraints

 UNIQUE

CREATE TABLE Contacts (mob VARCHAR(10) UNIQUE);

 CHECK

CREATE TABLE Contact (mob VARCHAR(15) CHECK (LENGTH(mob) >= 10));

 Named Constraint

CREATE TABLE Contact (

mob VARCHAR(15) UNIQUE,

CONSTRAINT chk_mob_length CHECK (LENGTH(mob) = 10)

);

 Remove/Add Primary Key

ALTER TABLE your_table ADD PRIMARY KEY (new_column);

17. Foreign Key

CREATE TABLE Orders(

ord_id INT PRIMARY KEY AUTO_INCREMENT,

date DATE,

amount DECIMAL(10,2),

cust_id INT,

FOREIGN KEY (cust_id) REFERENCES Customers(cust_id)

);

ON DELETE CASCADE

CREATE TABLE Orders (

ord_id INT PRIMARY KEY AUTO_INCREMENT,

date DATE,

amount DECIMAL(10,2),

cust_id INT,

FOREIGN KEY (cust_id) REFERENCES Customers(cust_id) ON DELETE CASCADE

);

18. Joins

 Cross Join

SELECT * FROM Customers, Orders;

 Inner Join

SELECT [Link], SUM([Link])

FROM Customers c

INNER JOIN Orders o ON c.cust_id = o.cust_id

GROUP BY [Link];

 Left Join

SELECT [Link], IFNULL(SUM([Link]),0)

FROM Customers c

LEFT JOIN Orders o ON c.cust_id = o.cust_id

 Right Join

SELECT *

FROM Orders o

RIGHT JOIN Customers c ON o.cust_id = c.cust_id;

19. Many-to-Many

CREATE TABLE Authors_Books (

author_id INT,

book_id INT,

FOREIGN KEY (author_id) REFERENCES Authors(author_id),

FOREIGN KEY (book_id) REFERENCES Books(book_id)

);

INSERT INTO Authors_Books VALUES (1,1),(2,2),(3,3),(1,4);

SELECT a.author_name, b.book_name

FROM Authors a

JOIN Authors_Books ab ON a.author_id = ab.author_id

JOIN Books b ON b.book_id = ab.book_id;

20. Views

Virtual table created from queries.

CREATE VIEW Cust_Info AS

SELECT * FROM Customer INNER JOIN Products

ON [Link] = Products.cust_id;

DROP VIEW Cust_Info;

21. HAVING vs WHERE

SELECT cust_id, SUM(price)

FROM Products
GROUP BY cust_id

HAVING SUM(price) > 2000;

22. ROLLUP

SELECT IFNULL(cust_id,'TOTAL') , SUM(price)

FROM Products

GROUP BY cust_id WITH ROLLUP;

23. Stored Routines

 Stored Procedure: reusable block of SQL statements.

 User Defined Function (UDF): custom function for calculations.