SQL : SQL is a standard language for storing, manipulating and retrieving data in databases.

is a language used to communicate with databases and used to write queries.

Cannot store data by itself.

MySQL is a Relational Database Management System (RDBMS) that uses SQL to manage data.

 Software / database system

 Stores data in tables

 Uses SQL to perform operations

MySQL executes SQL queries. SQL is only a language. MySQL and Oracle are databases that
understand SQL and execute the queries.

What is DBMS?

DBMS (Database Management System) is software used to manage data in a database.

 Controls data access

 Provides security and backup

 Data may or may not be stored in tables

What is RDBMS?

RDBMS (Relational Database Management System) is an advanced type of DBMS where data is
stored in tables (rows and columns) and relationships are maintained using keys.

 Data stored in tabular format

 Uses Primary Key & Foreign Key

 Follows relational model

 Supports normalization (Normalization is the process of organizing data in a database to

reduce redundancy and improve data integrity and remove duplicate data).

Index: is a database object that improves the speed of data retrieval.

(Works like an index in a book).

Index stores column values + pointer to actual row.

When a query is executed:( Similar to book index → page number.)

 Database checks index first

 Jumps directly to required rows

When to Use Indexes:

 Columns used in WHERE clause, ORDERBY, GROUP BY, JOINS. Efficient filtering.

Slower INSERT/UPDATE/DELETE, Extra storage.

  Small tables, Frequently updated columns, Columns with duplicate values.

Types:

 Primary Index: An index created automatically on primary key., Ensures unique values.

Employee_ID INT PRIMARY KEY

 Unique Index: No duplicate values allowed, Used for email, username.

CREATE UNIQUE INDEX idx_email

ON Employee (Email);

 Composite Index: Index on multiple columns.

CREATE INDEX idx_dept_salary

ON Employee(Department_ID, Salary);

 Clustered Index: Data is stored in the same order as index. Only one per table because data
can be stored in only one order. Primary key is usually clustered index.

Data is physically stored in this order.

 Non-Clustered Index: Index is stored separately from data. Contains pointer to data.
Multiple non-clustered indexes are allowed. Does not changes data order.

How to Create an Index: index_salary is index name, Salary is column name.

CREATE INDEX index_salary

ON Employee (Salary);

How to See Indexes:

SHOW INDEX FROM Employee;

How to drop an index:

DROP INDEX idx_name ON Employee;

A table is a database object that stores related data in the form of rows and columns.

A row represents one complete record in a table. Each row contains data for one entity.

A column represents a single attribute or field of the table. Each column has a data type

CRUD Operations: CRUD works on data, DDL works on structure.

Create: Insert new data into a table. INSERT INTO Employee VALUES (101, 'Ravi', 50000);

Read: Retrieve data from a table. SELECT * FROM Employee;

Update: Modify existing data. UPDATE Employee SET Salary = 55000 WHERE Employee_ID = 101;

Delete: Remove data from a table. DELETE FROM Employee WHERE Employee_ID = 101;
Datatypes:

INT, TINY INT, BIG INT

FLOAT (precision to 23 Digit), DOUBLE (precision 24 to 53 digits)

CHAR (Fixed-length character), VARCHAR (variable-length character)

DATE: YYYY-MM-DD

TIME: 12:50

BOOLEAN: True (1), False (0).

BINARY: 1\0

BIT: store X bit value, BIT (2) stores 2-bit value.

A database is a container that stores tables, views, procedures, indexes, etc.

CREATE DATABASE company;

SHOW DATABASES;

USE company;

CREATE DATABASE IF NOT EXISTS company;

DROP DATABASE company;

CREATE TABLE tablename (column1 datatype constraint);

CREATE TABLE Employee (emp_id INT PRIMARY KEY, emp_name VARCHAR (50) NOT NULL,

email VARCHAR (100) UNIQUE, salary INT DEFAULT 25000, age INT CHECK (age >= 18));

View Table Structure: DESCRIBE Employee;

DROP TABLE Employee;// Deletes structure + data.

SQL Constraint: is a rule applied on a table column to control the data that can be stored.

Without constraints: Duplicate data can enter, Invalid values can be stored, Table relationships can
break. Null values, Duplicate values, Invalid data are prevented.

Types of SQL Constraints

NULL means no value / unknown value

1. NOT NULL (can’t be NULL, can repeat)

Ensures a column cannot store NULL value. Use when value is mandatory.

2. UNIQUE (can be null, can’t repeat, multiple UNIQUE keys allowed)

Ensures no duplicate values in a column. Allows only one NULL value.

3. PRIMARY KEY (can’t be null, can’t repeat, only one)

 A primary key is a column (or combination of columns) that uniquely identifies each record
in a table. To identify each row uniquely. To connect tables using foreign key.

✔ Unique (no duplicate values)

✔ NOT NULL
✔ One primary key per table
✔ Automatically indexed

Composite Primary Key

When more than one column together makes a primary key.

4. FOREIGN KEY (can be null, can repeat, multiple FOREIGN keys allowed)

A foreign key is a column in one table that refers to the primary key of another table.

It creates relationship between tables. Prevents invalid data.

Parent Table: (the table that contains the PRIMARY KEY)

CREATE TABLE Department (

dept_id INT PRIMARY KEY,

dept_name VARCHAR (50)

);

Child Table: (is the table that contains the FOREIGN KEY)

CREATE TABLE Employee (

emp_id INT PRIMARY KEY,

emp_name VARCHAR (50),

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES Department(dept_id)

FOREIGN KEY (dept_id) newtableone REFERENCES Department(dept_id) oldtableoneboth

);

 Parent table must exist first

 Child table depends on parent

 You cannot insert invalid foreign key in child. (An employee must belong to a valid
department).

 Parent deletion affects child. (You cannot delete department if employees exist)

ON DELETE CASCADE: Deletes child records when parent is deleted.

FOREIGN KEY (dept_id) REFERENCES Department(dept_id)

ON DELETE CASCADE;
 ON UPDATE CASCADE: Updates child records automatically

5. CHECK – (can limit the values allowed in the column). Cannot use sub query.

age INT CHECK (age >= 18)

6. DEFAULT (Provides default value if none given.)

salary INT DEFAULT 25000

DELETE: (data)

is used to remove rows from a table. (DML)

Deletes selected rows, Uses WHERE clause, can be rolled back, Table structure remains.

DELETE FROM Employee WHERE salary < 30000;

TRUNCATE: (all data)

removes all records from a table at once. (DDL)

Deletes all rows, cannot use WHERE, cannot be rolled back, Faster than DELETE

TRUNCATE TABLE Employee;

DROP:(everything)

deletes the entire table or database. (DDL)

Deletes structure + data, cannot be rolled back, Table no longer exists.

DROP TABLE Employee;

TYPES OF SQL COMMANDS:

DDL, DML, DCL, TCL, DQL

DDL – Data Definition Language:

Used to define or change database structure. Affects structure, not data.

 CREATE: defines and create a database and table

 ALTER: modify the structure of existing table

ADD: add a column

ALTER TABLE tablename ADD COLUMN columnname datatype constraint;

MODIFY: modify datatype

ALTER TABLE tablename MODIFY columnname newdatatype newconstraint;

DROP: remove column

ALTER TABLE tablename DROP COLUMN columnname;

 RENAME: rename column

ALTER TABLE tablename RENAME TO newtablename;

 DROP: delete data and table

DROP TABLE tablename;

 TRUNCATE: delete data but not table structure.

TRUNCATE TABLE tablename;

 RENAME

DML – Data Manipulation Language:

Used to insert, update, delete data.

 INSERT: used to add new records

 UPDATE: to modify existing records

SET SQL_SAFE_UPDATES =0;

UPDATE tablename SET column1=value1, column2=value2 WHERE condition;

UPDATE student SET grade=”0” WHERE grade=”A”;

 DELETE: deletes existing rows

DELETE FROM tablename WHERE condition;

DELETE FROM tablename; //total table is deleted.

DCL – Data Control Language:

Used to control access and permissions. GRANT and REVOKE are given to users.

 GRANT:

Allow user to assign specific permission to database objects.

GRANT ALL privileges ON *. * TO user;

First * is for all databases and second * is for all tables.

 REVOKE:

To take access from user – used to revoke granted permissions on objects from users.

REVOKE ALL privileges ON *. * FROM user;

TCL – Transaction Control Language:

Used to manage transactions.

  COMMIT: used to permanently save changes made within a transaction.
SELECT * FROM EXAMS;
After payment to insert data
START TRANSACTION;
INSERT INTO Exams VALUES (….);
COMMIT;
 ROLLBACK: used to undo transaction that haven’t permanently saved in database.
Data is saved if payment is success. But if fails, rollback until it is successful.
 SAVEPOINT: if things go wrong, you can return to that point rather than starting from
scratch again.
START TRANSACTION;
INSERT INTO exams VALUES (….);
SAVEPOINT A;
INSERT INTO exams VALUES (….);
SAVEPOINT B;
INSERT INTO exams VALUES (….);
SAVEPOINT C;
ROLLBACK TO B;
COMMIT;

DQL – Data Query Language:

Used to retrieve data.
 SELECT: only read data.

Type Purpose Commands

DDL Structure CREATE, ALTER, DROP, TRUNCATE

DML Data change INSERT, UPDATE, DELETE

DQL Data fetch SELECT

DCL Permissions GRANT, REVOKE

TCL Transactions COMMIT, ROLLBACK, SAVEPOINT

Clauses in SQL:
Clause is a part of SQL statement that performs a specific function.

Clauses are mainly used with the SELECT statement which retrieve data from database.

FROM- specifies the table from which the data is selected.

 WHERE Clause: selects/filters rows based on a condition.

SELECT * FROM Employee WHERE salary > 50000;

Works on individual rows, cannot use aggregate functions.

 ORDER BY Clause: arrange results in a specific order.

SELECT * FROM Employee ORDER BY salary DESC;

By default, ASC;

 GROUP BY Clause: Groups rows having same values in the specified column.

SELECT dept_id, AVG (salary)

FROM Employee

GROUP BY dept_id;

Used with aggregate functions.

 HAVING Clause: Filters groups based on condition. (after GROUP BY)

SELECT dept_id, AVG (salary)

FROM Employee

GROUP BY dept_id

HAVING AVG (salary) > 60000;

Used with aggregates.

 LIMIT Clause: Limits number of rows returned.

SELECT * FROM Employee LIMIT 5;

 DISTINCT Clause: Removes duplicate records.

SELECT DISTINCT dept_id FROM Employee;

 BETWEEN Clause: Selects values in a range.

SELECT * FROM Employee WHERE salary BETWEEN 40000 AND 60000;

 IN Clause: Matches multiple values.

SELECT * FROM Employee WHERE dept_id IN (10, 20);

 LIKE Clause: Pattern matching.

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

  General Order of SQL Clauses:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

 WHERE vs HAVING

WHERE is used to filter individual rows before grouping happens without aggregate functions.

Works on row-level data. Used with SELECT, UPDATE, DELETE. Filter employees with salary >
30K.

SELECT column_name

FROM table_name

WHERE condition;

HAVING is used to filter grouped data after GROUP BY with aggregate functions.

Works on group-level data. Used with SELECT. Filter departments with avg salary > 50K.

SELECT column_name, aggregate_function

FROM table_name

GROUP BY column_name

HAVING condition;

 Difference between COUNT (*) and COUNT (column)?

COUNT (*) counts all rows in a table, including rows with NULL values.
COUNT (column) counts only non-NULL values in that column.

AGGREGATE FUNCTIONS:

Aggregate functions perform calculations on multiple rows and return a single value.

SELECT COUNT (DISTINCT dept_id) FROM Employee; -avoid duplicate values.

Aggregate Functions Without GROUP BY allowed when aggregation is on whole table.

 To find second highest salary?

SELECT MAX (salary) FROM Employee WHERE salary < (SELECT MAX (salary) FROM Employee);
Function Purpose

COUNT () Counts rows

SUM () Total of values

AVG () Average value

MIN () Smallest value

MAX () Largest value

 SELECT SUM (salary) FROM Employee; -Ignores NULL values.

 SELECT AVG (salary) FROM Employee; -Ignores NULL values, Result is decimal.
 SELECT MIN (salary) FROM Employee; -Works with numbers, dates, strings.
 SELECT MAX (salary) FROM Employee; -Works with numbers, dates, strings.

Only Count includes NULL, other functions ignore.

SQL JOINS:

A JOIN is used to combine data from two or more tables based on a related column (usually
primary key & foreign key).

Joins Are Needed because Data is stored in multiple tables, we need to fetch combined
information.

 INNER JOIN: Returns only matching records from both tables.

INNER JOIN returns NULL values, only if matched columns contain NULL.

Syntax: SELECT column(s) FROM table1 INNERJOIN table2

ON table1.column_name=table2.column_name; (OR)

SELECT column(s) FROM table1 AS A INNERJOIN table2 AS B

ON A. column_name=B.column_name;

 LEFT JOIN: All rows from left table, matching rows from right table.

Syntax: SELECT column(s) FROM table1 LEFTJOIN table2

ON table1.column_name=table2.column_name;

 RIGHT JOIN: All rows from right table, Matching rows from left table.

Syntax: SELECT column(s) FROM table1 RIGHTJOIN table2

ON table1.column_name=table2.column_name;

 FULL JOIN: All matching rows, All non-matching rows from both tables.

Syntax: SELECT column(s) FROM table1 LEFTJOIN table2

ON table1.column_name=table2.column_name

UNION

SELECT column(s) FROM table1 RIGHTJOIN table2

ON table1.column_name=table2.column_name;

 CROSS JOIN: Returns Cartesian product (every row with every row).

SELECT e.emp_name, d.dept_name FROM Employee e CROSS JOIN Department d;

 SELF JOIN: Joining a table with itself. USECASE: Employee–Manager relationship.

SELECT column(s) FROM table AS A JOIN table AS B ON [Link]=B.manager_id;

We have employee table with id, name and manager table with manager id, in which manager is
within employee table to get name of the manager. we use self-join.

JOIN: Combines multiple tables side-by-side.

SUBQUERY: Query inside another query.

Subquery in SQL: A subquery is a query written inside another SQL query. also called nested query.

We use subqueries when:

 A value needed for a query depends on another query

 To filter data based on results of another table

 When JOIN is not required or not preferred

 ✔ Employees earning more than average

✔ Departments with no employees
✔ Highest salary per department
✔ Filtering data without JOIN

Syntax:

SELECT column_name FROM table name

WHERE column_name = (SELECT column_name FROM table_name);

Types of Subqueries:

 Single-Row Subquery - Returns only one row. Uses: =, >, <.

SELECT emp_name FROM Employee

WHERE salary > (SELECT AVG (salary) FROM Employee);

 Multiple-Row Subquery - Returns multiple rows. Uses: IN, ANY, ALL.

SELECT emp_name FROM Employee

WHERE dept_id IN (SELECT dept_id FROM Department);

  Multiple-Column Subquery - Returns multiple columns

SELECT emp_name FROM Employee WHERE (dept_id, salary)

IN (SELECT dept_id, MAX (salary) FROM Employee GROUP BY dept_id);

 Correlated Subquery - Subquery depends on outer query. Executes once for each row.

SELECT emp_name FROM Employee e

WHERE salary > (SELECT AVG (salary) FROM Employee WHERE dept_id = e. dept_id);

SQL OPERATORS: are symbols or keywords used to perform operations on data in SQL queries.

 Arithmetic Operators: used for mathematical calculations.

Operator Meaning

+ Addition

- Subtraction

* Multiplication

/ Division

% Modulus

 Comparison (Relational) Operators: Used to compare values.

Operator Meaning

= Equal

!= / <> Not equal

> Greater than

< Less than

>= Greater or equal

<= Less or equal

 Logical Operators: Used to combine multiple conditions.

Operator Meaning

All conditions
AND
true

Any condition
OR
true

NOT Negates condition

 Special Operators:
IN: Checks multiple values.
SELECT * FROM Employee
WHERE dept_id IN (10, 20, 30);

BETWEEN: Range of values (inclusive)

SELECT * FROM Employee

WHERE salary BETWEEN 40000 AND 80000;

LIKE: Pattern matching.

SELECT * FROM Employee

WHERE name LIKE 'A%';

Pattern Meaning

% Any number of characters

_ Single character
IS NULL / IS NOT NULL: Checks NULL values

SELECT * FROM Employee

WHERE salary IS NULL;

Set Operators: Used to combine results of multiple queries.

Operator Meaning

UNION Combine without duplicates

UNION ALL Combine with duplicates

INTERSECT Common rows

EXCEPT / MINUS Difference

SQL FUNCTIONS:

SQL functions are built-in methods used to perform calculations, manipulate data, and
return a value.

Types of SQL Functions:

 Aggregate Functions: Operate on multiple rows → return single value.

 Scalar Functions: Work on single value → return single value
 String Functions:

Function Use
UPPER () Uppercase
LOWER () Lowercase
LENGTH () Length
CONCAT () Combine strings
SUBSTRING () Extract
Numeric Functions:

Function Use

ROUND () Round value

CEIL () Upper value

FLOOR () Lower value

MOD () Remainder

Conversion Functions:

Function Use

CAST () Convert datatype

CONVERT () Convert datatype

Control Functions:

Function Use

IF() Condition

CASE Multiple conditions

VIEWS IN SQL:
A view is a virtual table created using a SQL SELECT query.
👉 It does not store data itself
👉 It shows data from one or more tables
Why Do We Use Views?
✔ To simplify complex queries
✔ To improve security (hide columns)
✔ To reuse SQL logic
✔ To present data in required format
 Syntax to Create a View:
CREATE VIEW emp_view AS
SELECT emp_id, emp_name, salary
FROM Employee;
 Using a View:
SELECT * FROM emp_view;
Update Data Using View: Possible only if view is simple
UPDATE emp_view
SET salary = salary + 5000
WHERE emp_id = 1;
 Drop a View

DROP VIEW emp_view;

❌ Not possible if view has:

 JOIN
 GROUP BY
 DISTINCT
 Aggregate functions
Types of Views:
🔹 Simple View
 Based on single table
 No GROUP BY, JOIN
✔ Can update data
🔹 Complex View
 Based on multiple tables
 Uses JOIN / GROUP BY
❌ Cannot update data

🔹 Materialized View (Conceptual)

 Stores data physically
 Improves performance
 Needs refresh