Scribd
Upload
6 views5 pages

Comprehensive SQL Guide and Notes

This document provides comprehensive notes on SQL, covering its definition, categories, commands, data types, CRUD operations, and various functionalities such as filtering, sorting, and joins. It also discusses advanced topics like stored procedures, triggers, transactions, user management, and performance tuning. Additionally, it includes practical applications and mini project ideas related to SQL databases.

Uploaded by

routashutosh7878
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views5 pages

Comprehensive SQL Guide and Notes

This document provides comprehensive notes on SQL, covering its definition, categories, commands, data types, CRUD operations, and various functionalities such as filtering, sorting, and joins. It also discusses advanced topics like stored procedures, triggers, transactions, user management, and performance tuning. Additionally, it includes practical applications and mini project ideas related to SQL databases.

Uploaded by

routashutosh7878
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL FULL NOTES

1. Introduction to SQL

- SQL stands for Structured Query Language.

- Used to store, manipulate and retrieve data in relational databases.

- SQL is standard for MySQL, PostgreSQL, SQL Server, Oracle, etc.

2. SQL Categories

- DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE

- DML (Data Manipulation Language): INSERT, UPDATE, DELETE

- DQL (Data Query Language): SELECT

- DCL (Data Control Language): GRANT, REVOKE

- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3. Database & Table Commands

- CREATE DATABASE database_name;

- USE database_name;

- CREATE TABLE table_name (...);

- ALTER TABLE table_name ADD column;

- DROP TABLE table_name;

- TRUNCATE TABLE table_name;

4. Data Types

Numeric: INT, BIGINT, FLOAT, DOUBLE, DECIMAL

String: CHAR, VARCHAR, TEXT

Date/Time: DATE, TIME, DATETIME, TIMESTAMP

Boolean: TRUE/FALSE (TINYINT)

5. CRUD Operations
INSERT INTO table VALUES (...);

UPDATE table SET column=value WHERE condition;

DELETE FROM table WHERE condition;

SELECT columns FROM table;

6. Filtering Data

- WHERE

- Operators: =, >, <, >=, <=, !=

- Logical: AND, OR, NOT

- LIKE, BETWEEN, IN

- IS NULL / IS NOT NULL

7. Sorting and Limiting

ORDER BY column ASC/DESC;

LIMIT number;

OFFSET number;

8. Aggregate Functions

COUNT(), SUM(), AVG(), MIN(), MAX()

9. Grouping

GROUP BY column;

HAVING condition;

10. Joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN (using UNION)


CROSS JOIN

SELF JOIN

11. Subqueries

- In WHERE, SELECT, FROM

- Single-row and multi-row subqueries

12. Constraints

PRIMARY KEY, FOREIGN KEY

UNIQUE, NOT NULL, DEFAULT, CHECK

13. String Functions

CONCAT(), LENGTH(), UPPER(), LOWER(), SUBSTRING(), TRIM()

14. Date Functions

NOW(), CURDATE(), DATEDIFF(), DATE_FORMAT()

15. Views

CREATE VIEW name AS SELECT ...

DROP VIEW name;

16. Indexes

PRIMARY, UNIQUE, INDEX, FULLTEXT

Improves search performance

17. Stored Procedures

CREATE PROCEDURE name() BEGIN ... END;

CALL procedure();

18. Functions
CREATE FUNCTION name() RETURNS datatype BEGIN ... END;

19. Triggers

BEFORE/AFTER INSERT

BEFORE/AFTER UPDATE

BEFORE/AFTER DELETE

20. Transactions

START TRANSACTION;

COMMIT;

ROLLBACK;

SAVEPOINT;

21. User Management

CREATE USER;

GRANT privileges;

REVOKE privileges;

22. Backup & Restore

mysqldump export

Import .sql files

23. Performance Tuning

EXPLAIN queries

Optimize indexes

Normalize tables

24. SQL with Programming Languages

SQL with Python, Java, PHP, [Link]


25. Practice Mini Projects

Student DB

Library DB

E-commerce DB

Hospital DB

You might also like