SQL NOTES

SQL (Structured Query Language) commands can be categorized into different groups based on
their functionality. The most common classifications are DDL (Data Definition Language),
DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction
Control Language). Here's a breakdown of each:

1. DDL (Data Definition Language)

DDL commands are used to define and manage the structure of the database, such as creating,
altering, and deleting tables, schemas, and other database objects.

 Commands in DDL:
o CREATE: Creates a new database, table, or object.
 Example: CREATE TABLE employees (id INT, name VARCHAR(50));
o ALTER: Modifies an existing database object (e.g., table or column).
 Example: ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);
o DROP: Deletes an existing database object, like a table.
 Example: DROP TABLE employees;
o TRUNCATE: Removes all records from a table, but keeps its structure intact.
 Example: TRUNCATE TABLE employees;
o RENAME: Renames a database object.
 Example: RENAME TABLE employees TO staff;
 Purpose: Used to define or modify database schema.

2. DML (Data Manipulation Language)

DML commands are used to manipulate the data within tables. These commands enable users to
insert, update, delete, and retrieve data.

 Commands in DML:
o SELECT: Retrieves data from a database.
 Example: SELECT * FROM employees;
o INSERT: Adds new rows of data into a table.
 Example: INSERT INTO employees (id, name) VALUES (1, 'John Doe');
o UPDATE: Modifies existing records in a table.
 Example: UPDATE employees SET salary = 5000 WHERE id = 1;
o DELETE: Removes rows from a table.
 Example: DELETE FROM employees WHERE id = 1;
 Purpose: Used for data manipulation tasks, such as inserting, updating, and deleting data.

1. CREATING AND USING DATABASE

Concept: Used to create a new database and switch to it.

Syntax: To create a database:

CREATE DATABASE database_name;
To use a database:

USE database_name;
Example:

CREATE DATABASE company_db;

USE company_db;
CREATE TABLE

Concept: Creates a new table in the database.

Syntax:

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
...
);
Example:

CREATE TABLE employees (

employee_id INT,
first_name VARCHAR(50),
salary FLOAT
);
INSERT INTO

Concept: Inserts new rows into a table.

Syntax:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);
Example:

INSERT INTO employees (employee_id, first_name, salary)

VALUES (1, 'John', 5000);

2. SELECT STATEMENT
Concept: Used to retrieve data from one or more tables in a database.
Syntax:
 SELECT column1, column2, ...
FROM table_name;
Example:
SELECT first_name, last_name
FROM employees;

3. WHERE CLAUSE
Concept: Filters records that meet a specified condition.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
SELECT first_name, last_name
FROM employees
WHERE department = 'HR';

4. DISTINCT
Concept: Removes duplicate records from the result set.
Syntax:
SELECT DISTINCT column1, column2, ...
FROM table_name;
Example:
SELECT DISTINCT department
FROM employees;

5. ORDER BY
Concept: Sorts the result set by one or more columns in ascending (`ASC`) or descending (`DESC`)
order.
Syntax:
 SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC];

Example:

SELECT first_name, last_name

FROM employees
ORDER BY last_name DESC;

AGGREGATE FUNCTIONS

6. COUNT, SUM, AVG, MIN, MAX

Concept: Used to perform calculations on data.
Syntax:
SELECT COUNT(column), SUM(column), AVG(column), MIN(column), MAX(column)
FROM table_name
WHERE condition;
Example:
SELECT COUNT(*), SUM(salary), AVG(salary)
FROM employees
WHERE department = 'IT';

7. GROUP BY AND HAVING

Group By:
Concept: Groups rows that have the same values into summary rows.
Syntax:
SELECT column1, COUNT(*)
 FROM table_name
GROUP BY column1;
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Having
Concept: Filters groups of rows after grouping.
Syntax:
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 5;

Example:

SELECT department, COUNT(*)

FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

8. INNER JOIN

Inner Join
Concept: Combines rows from two tables where there is a match between columns in both tables.
Syntax:

SELECT table1.column1, table2.column2, ...

FROM table1
INNER JOIN table2
 ON table1.column = table2.column;

Example:

SELECT employees.first_name, departments.department_name

FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

9. ALIASING
Concept: Assigns temporary names (aliases) to columns or tables.
Syntax:
SELECT column_name AS alias_name
FROM table_name AS alias;

Example:

SELECT e.first_name AS Employee, d.department_name AS Department

FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

10. DELETE
Concept: Removes rows from a table based on a specified condition.
Syntax:
DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees

WHERE employee_id = 5;
Note: Without the `WHERE` clause, all rows in the table will be deleted.

11. DROP
Concept: Deletes an entire table or database.
Syntax:
To drop a table:
DROP TABLE table_name;

To drop a database:
DROP DATABASE database_name;
Example:
DROP TABLE employees;

12. ADD (ALTER TABLE)

Add (Alter Table)
Concept: Adds a new column to an existing table.
Syntax:
ALTER TABLE table_name
ADD column_name datatype;
Example:

ALTER TABLE employees

ADD birth_date DATE;

13. UPDATE
Concept: Updates existing records in a table.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:
 UPDATE employees
SET salary = 5000
WHERE employee_id = 3;

14. TRUNCATE
Concept: Removes all rows from a table without deleting the table itself.
Syntax:
TRUNCATE TABLE table_name;
Example:

TRUNCATE TABLE employees;

Note: `TRUNCATE` is faster than `DELETE` as it does not generate individual row deletions, but it
cannot be rolled back.

15. TOP
Concept: Specifies the number of rows to return in a query.

Syntax:

SELECT TOP number|percent column_name(s)

FROM table_name
WHERE condition;
Example:

SELECT TOP 5 *
FROM employees
ORDER BY salary DESC;
16. LIKE
Concept: Used to search for a specified pattern in a column.

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE column LIKE pattern;
Example:
 SELECT *
FROM employees
WHERE first_name LIKE 'J%';

Some Important Facts about SQL

 SQL is Declarative: Specify what you want; the database engine decides how.
 Case-Insensitive: SQL keywords aren't case-sensitive (e.g., SELECT = select).
 NULL: Represents no value, different from zero or blank.
 Joins: Combine data from multiple tables.

1) INNER JOIN: Returns matching rows.

2) LEFT JOIN: Returns all rows from the left table.
3) RIGHT JOIN: Returns all rows from the right table.
4) FULL JOIN: Returns rows with matches in either table.

 Primary Keys: Unique, non-null identifier for table rows.

 Indexes: Speed up query retrieval but slow down updates.
 Aggregate Functions: Calculate values (SUM, AVG, COUNT, etc.).
 Aliasing: Use AS to rename columns or tables temporarily.
 Foreign Keys: Maintain relationships between tables.