Introduction to SQL

SQL Data Types

- In SQL, each column must be assigned a data type that
defines the kind of data it can store, such as integers,
dates, text, or binary values
- Benefits of using the right data type:
1) Memory-efficient storage
2) Accurate operations (e.g., calculations, sorting)
3) Consistency in stored values
4) Validation of input data
Basic SQL Data Types
1. Numeric Data Types
2. String Data Types
3. Date and Time Data Types
4. Binary Data Types
5. Boolean Data Type
 SQL Data Types (Numeric Data
Types)
Data Type Description
BIGINT Large integer numbers
INT Standard integer values
SMALLINT Small integers
TINYINT Very small integers
FLOAT(M,D) length (M),decimals (D), Decimal precision can go to 24
places for a FLOAT.

length (M),decimals (D),Decimal precision can go to 53

DOUBLE(M,D)
places for a DOUBLE

DECIMAL(M, Exact (fixed-point), When you need exact values (like

D) money) without rounding errors
 SQL Data Types (String Data Types)
Data Type Description
Char(size) A fixed length string which can have letters, numbers,
and special characters.The size parameter specifies the
column length in characters which can vary from from 0
to 255.
Varchar(size) requires a specified maximum length size at table
creation, defining the upper limit of characters it can
store
Text This holds a string with a maximum length of 65,535
bytes
 SQL Data Types (Date and Time
Data Type)
Data Type Description
DATE stores the data of date (year, month, day)
TIME stores the data of time (hour, minute,second)
DATETIME store both the data and time (year, month, day, hour,
minute, second)
 SQL Data Types (Boolean Data Type)
Data Type Description
BOOLEAN Stores TRUE or FALSE (some systems use 1 and 0
instead)
 SQL Data Types (Binary Data Types)
Binary data types are used to store binary data such as
images, videos or other file types.

Data Type Description

Binary/ Fixed or variable-length binary data
VarBinary(n)
Image Stores binary data as images.
Creating the Database
- The CREATE DATABASE statement is a DDL (Data
Definition Language) statement used to create a new
database in SQL.

Syntax: CREATE DATABASE DatabaseName;

Example: CREATE DATABASE testDB;
Data Definition Commands
- In DBMS, Data Definition Commands are SQL
commands that are part of the Data Definition Language
(DDL).
- They are used to create, change, or remove the
structure of database objects like tables, schemas, views,
and indexes.
- They affect the schema of the database, not just the
data inside it.
- Main Data Definition Commands are :
1) CREATE – Creates new database objects.
2) ALTER – Modifies the structure of an existing object.
3) DROP – Deletes a database object completely.
Data Definition Commands
1)CREATE : This command is used to create a new table
in SQL. The user has to give information like table name,
column names, and their datatypes.
Syntax: Example:
CREATE TABLE CREATE TABLE Employee (
<table_name> EmpID INT PRIMARY
( KEY,
column_1 datatype, Name VARCHAR(50),
column_2 datatype, Salary DECIMAL(10,2)
column_3 datatype, );
....
Data Definition Commands
2.ALTER : Modifies the structure of an existing object.

Syntax:
ALTER TABLE <table_name> ADD <column_name>
datatype;
Example:
ALTER TABLE Employee ADD Department VARCHAR(30);
Data Definition Commands
3) DROP – Deletes a database object completely.
Syntax:
DROP TABLE <table_name>;

Example:
DROP TABLE Employee;
Data Definition Commands
4) TRUNCATE : This command is used to remove all rows
from the table, but the structure of the table still exists.

Syntax: TRUNCATE TABLE <table_name>;

Example: TRUNCATE TABLE Employee;
Data Definition Commands
5. RENAME : It is possible to change name of table using
simple RENAME command.

Syntax: RENAME TABLE <Table Name> To

<New_Table_Name>;
Example: RENAME TABLE Employee To EMP;
SQL Constraints
- Constraints can be specified when the table is created
with the CREATE TABLE statement, or after the table is
created with the ALTER TABLE statement.

CREATE TABLE <table_name> ( ALTER TABLE <table_name>

column1 datatype constraint, ADD CONSTRAINT
column2 datatype constraint, <constraint_name>
constraint_type
column3 datatype constraint,
(column_name);
....
);
SQL Constraints
common constraints include:
• PRIMARY KEY: Uniquely identifies each row in the table and
cannot contain NULL values.
• NOT NULL: Ensures that a column cannot store NULL values.
• UNIQUE: Ensures that all values in a column are distinct.
• DEFAULT value: Assigns a default value to a column if no
value is explicitly provided during insertion.
• FOREIGN KEY: Establishes a link between data in two tables,
referencing the primary key of another table.
• CHECK : the values in a column meet a specific condition
before they are accepted into the table.
SQL Constraints Example
CREATE TABLE Employees (
EmpID INT PRIMARY KEY, -- PRIMARY KEY constraint
Name VARCHAR(50) NOT NULL, -- NOT NULL constraint
Email VARCHAR(100) UNIQUE, -- UNIQUE constraint
Age INT CHECK (Age >= 18), -- CHECK constraint
DeptID INT,
FOREIGN KEY (DeptID) -- FOREIGN KEY constraint
REFERENCES Departments(DeptID)
);
SQL indexes
- An index is a data structure that helps the database find
and retrieve data faster without having to scan the entire
table every time.
- Works by creating a separate data structure that provides
pointers to the rows in a table. Which makes it faster to look
up rows based on specific values.
Benefits of Indexes:
- Speeds up SELECT and WHERE conditiond
- Improves performance in large tables.
- Speeds up sorting (ORDER BY) and joining (JOIN).
SQL indexes
Creating an Index:
- Creating an index allows us to define a quick access path to
data.
- SQL indexes can be applied to one or more columns and can
be either unique or non-unique.
- Unique indexes ensure that no duplicate values are entered
in the indexed columns, while non-unique indexes simply
speed up queries without enforcing uniqueness.
Single-column indexes: For basic queries
Multi-column indexes: For queries using multiple filters
Unique indexes: To ensure data uniqueness
SQL indexes(Single-column )
Syntax:
CREATE INDEX CREATE INDEX
index_name idx_product_id
ON TABLE column; ON Sales (product_id);
This creates an index named idx_product_id on the
product_id column in the Sales table, improving the
speed of queries that filter or join based on this column.
 SQL indexes(Multi-column )

CREATE INDEX index_name CREATE INDEX

ON TABLE (column1, idx_product_quantity
column2,.....); ON Sales (product_id,
quantity);
This index allows the database to quickly filter or join data
based on both product_id and quantity columns.
 SQL indexes(Unique )
A unique index ensures that all values in the indexed
column(s) are unique, preventing duplicates.

CREATE UNIQUE INDEX

CREATE UNIQUE INDEX
idx_unique_employee_id
index_name
ON Employees
ON table_name
(employee_id);
(column_name);
This index ensures that no two rows in the Employees
table have the same employee_id, which maintains data
integrity and prevents duplicate entries.
Altering and Removing an Index
Altering an Index: If an index requires adjustments,
such as reorganizing or rebuilding, it can be altered
without affecting the data.
Syntax:
ALTER INDEX IndexName
ON TableName REBUILD;

Removing an Index:
- If an index is no longer needed, it can be removed to
improve write performance or save storage space
Syntax: DROP INDEX index;
Data Manipulation Commands
Data Manipulation Language (DML) commands in SQL are
used to manage and manipulate the data within
database tables. The primary DML commands are

1) SELECT : Retrieve data from the table.

2) INSERT : Insert data into a table.
3) UPDATE : Update existing data within a table.
4) DELETE : Delete records from a database table.
Data Manipulation Commands
1) SELECT Command:Select command is used to retrieve
data from the table.
Syntax: SELECT * from <table_name>;
Example: SELECT * from Employee;

2) INSERT Command: Insert command is used to insert

data into a table
Syntax: Insert into <table_name> (column list) values
(column values);
Example: Insert into Employee(Emp_id, Emp_name) values
(1, "test_name");
Data Manipulation Commands
3) UPDATE Command:Update command is used to
update existing data within a table.
Syntax: UPDATE <table_name> SET column_number
=value_number WHERE condition;
Example: UPDATE Employee SET Emp_name= “Krishna”
WHERE Emp_id= 1;

4) DELETE Command:Delete command is used to delete

records from a database table.
Syntax: Delete from <table_name> WHERE condition;
Example: DELETE from Employee WHERE Emp_id=1;
COMMIT and ROLLBACK
- In SQL, COMMIT and ROLLBACK are Transaction Control
Language (TCL) commands.
- They are used to control changes made by INSERT,
UPDATE, DELETE, etc., within a transaction.
COMMIT :
- Saves all the changes made in the current transaction
permanently to the database.
- Once committed, the changes cannot be undone
Syntax: COMMIT;
COMMIT and ROLLBACK
Rollback:
- Undoes all the changes made in the current
transaction.
- Brings the database back to the state before the
transaction began.
- once you COMMIT in SQL, you cannot ROLLBACK to
undo those changes.
Syntax: ROLLBACK;
 Select Subquery
what is Subquery?
- A subquery is a query inside another query.
- It’s used when you want the result of one query to be used in
another.
- also known as a nested query or an inner query

Types of SELECT Subqueries:

1) Single-row subquery : returns only 1 value
2) Multiple-row subquery : returns many values
3) Multiple-column subquery : returns multiple columns
Subquery
Syntax:
SELECT column1, column2, ...
FROM <table_name>
WHERE <column_name> operator
(SELECT column_name
FROM another_table
WHERE
condition
);
 Subquery (Single-row Subquery)
Employees
SELECT emp_name
emp_nam
emp_id dept_id salary FROM Employees
e
1 Ravi 101 50000 WHERE dept_id = (
2 Raj 102 60000 SELECT dept_id
3 Ram 101 55000 FROM Departments
4 Sasi 103 70000 WHERE dept_name =
Departments 'IT'
dept_na );
dept_id Inner Query : Result : 102
me
101 HR Outer query: Result: Raj
102 IT
 Subquery(Multiple-row Subquery
(IN))
Employees
emp_nam SELECT emp_name
emp_id dept_id salary
e FROM Employees
1 Ravi 101 50000 WHERE dept_id IN (
2 Raj 102 60000 SELECT dept_id
3 Ram 101 55000 FROM Departments
4 Sasi 103 70000 WHERE dept_name IN
('HR', 'Finance')
Departments );
dept_na
dept_id
me Inner query : Result: 101, 103
101 HR
102 IT
Outer Query: Result : Ravi , Ram, Sasi
 Subquery( Multiple-Column
Subquery)
Employees SELECT emp_name,
emp_nam dept_id, salary
emp_id dept_id salary
e
FROM Employees
1 Ravi 101 50000
WHERE (dept_id, salary) =
2 Raj 102 60000
(
3 Ram 101 55000 SELECT dept_id, salary
4 Sasi 103 70000 FROM Employees
5 Keyur 101 55000 WHERE emp_name =
'Ram'
Inner query : Result: 101, 55000 );
Outer Query: Result : Ram, Keyur
 Selecting rows with conditional
restrictions
- You can select partial table
contents by placing
restrictions on the rows to be
included in the output. Use
the WHERE clause to add
conditional restrictions to the
SELECT statement that limit
the rows returned by the emp_nam
query. emp_id dept_id salary
e
Example: SELECT * FROM 1 Alka 101 50000
Employees WHERE salary > 2 Balram 102 60000
50000; 3 Chris 101 55000
4 Dev 103 70000
 Selecting rows with conditional restrictions
(Character Comparisons)
Employees
emp_nam
emp_id dept_id salary
e
1 Alka 101 50000
2 Balram 102 60000
3 Chris 101 55000
4 Dev 103 70000
1) SELECT emp_name FROM Employees WHERE emp_name = 'Alka'; o/p:
Alka
2) SELECT emp_name FROM Employees WHERE emp_name <> 'Balram';
o/p: Alka,Chris, Dev
3) SELECT emp_name FROM Employees WHERE emp_name > 'Balram'; o/p:
Chris, Dev
4) SELECT emp_name FROM Employees WHERE emp_name < 'Chris'; o/p:
Alias in DBMS
-An alias is a temporary name you give to a table or
column in a query.
Column Alias:
Syntax: SELECT <column_name> AS <alias_name>
FROM <table_name>;
Example: SELECT emp_name AS Employee FROM
Employees;
Table Alias:
Syntax: SELECT <t.column_name> FROM <table_name>
AS t;
Example: SELECT e.emp_name, d.dept_name
Logical Operator

Operator Meaning Example

WHERE salary >
AND All conditions must be TRUE 50000 AND dept_id
= 101
WHERE dept_id =
At least one condition must be
OR 101 OR dept_id =
TRUE
103
Negates a boolean condition in WHERE NOT dept_id
NOT
SQL = 102
 Special Operators
Operator Purpose Example
WHERE salary BETWEEN 40000
BETWEENValue within a range
AND 60000
Matches any value from a
IN WHERE dept_id IN (101, 103)
list
Pattern matching with
wildcards:
LIKE WHERE emp_name LIKE 'A%'
% many chars, _ single
char
IS NULL Checks if value is NULL WHERE manager_id IS NULL
WHERE EXISTS (SELECT *
TRUE if subquery returns
 Example:
SELECT emp_name, salary
FROM Employees
WHERE dept_id IN (101, 103) -- IN (special)
AND salary BETWEEN 50000 AND 70000 -- BETWEEN
(special)
AND
-% NOTany
means emp_name LIKE 'D%';
and all following -- NOT + LIKE
or preceding characters are
(logicalFor
eligible. + example:
special)
- 'J%' includes June, Jia, Jenil, July, and J-231Q.
- 'Jo%' includes John and Jones.
- '%n' includes John and Jernian.
' _ means any one character may be substituted for the
underscore. For example:
'_23-355-6889' includes 123-355-6889, 223-355-6889, and 323-
Changing a Column’s Data Type
- Changing a column’s data type in DBMS can be done using the
ALTER TABLE command.
Example:
To increase or decrease size ( VARCHAR(50) to VARCHAR(100))
To change the format (VARCHAR to DATE).
Syntax:
ALTER TABLE <table_name> MODIFY COLUMN <column_name>
<new_data_type>;
ALTER TABLE Employees MODIFY COLUMN salary DECIMAL(10,2);
- You can only change to a data type that can store the existing data
without loss.
- If the new type is smaller, existing values may be truncated or
Changing a Column’s Data
Characteristics
- Changing a column’s data characteristic in DBMS means
altering properties of the column without changing its
actual data type.
Syntax:
ALTER TABLE table_name
MODIFY COLUMN <column_name>
<data_type>(new_characteristics);
Example:
ALTER TABLE Employees
MODIFY COLUMN emp_name VARCHAR(100);
Renaming a Column
Syntax:
ALTER TABLE Employees
RENAME COLUMN <old_column_name> TO
<new_column_name>;

Example:
ALTER TABLE Employees
RENAME COLUMN emp_name TO employee_name;
 Adding a Column
Syntax:
ALTER TABLE <table_name>
ADD COLUMN <column_name> data_type [constraints] [position];
(where position is optional, tells MySQL where to place the
column
(FIRST or AFTER column_name))
Example: 3)ALTER TABLE Employees
1) ALTER TABLE Employees
ADD COLUMN status ADD COLUMN phone_number
VARCHAR(20) DEFAULT 'Active'; VARCHAR(15) AFTER
emp_name;
2) ALTER TABLE Employees
ADD COLUMN hire_date DATE, 4) ALTER TABLE Employees
ADD COLUMN salary ADD COLUMN emp_code INT
Dropping a Column
Stntax:
ALTER TABLE <table_name> DROP COLUMN
<column_name>;

Example:
1) ALTER TABLE Employees DROP COLUMN department;
2) ALTER TABLE Employees
DROP COLUMN hire_date,
DROP COLUMN salary;
 Advanced Data Update
Syntax: UPDATE <table_name> SET <column> = <value>
WHERE condition;
Example:1 Example:3
UPDATE UPDATE Employees
Employees Example:2
SET salary = salary
SET UPDATE Employees e
+ 7000
salary = salary JOIN Departments d
WHERE emp_id IN (
+ 1000, ON e.dept_id =
SELECT emp_id
status = 'Active' d.dept_id
FROM
WHERE SET
Performance
salary < 50000; e.salary = e.salary +
WHERE
5000
Copying Parts of Tables
- to break up a table structure into several component
parts (or smaller tables).
- use SELECT ... and INSERT INTO ... SELECT statements
for creating new table and copying data from existing to
new one table.
Syntax: Example:
CREATE TABLE Sales_2024 AS
CREATE TABLE <new_table> ASSELECT order_id, customer_id,
SELECT column1, column2, ... amount
FROM <old_table>; FROM Sales;
Copying Parts of Tables
Syntax:
INSERT INTO <target_table> (col1, col2, ...)
SELECT col1, col2, ...
FROM <source_table>
WHERE condition;
Example:
INSERT INTO Sales_2024 (order_id, customer_id, amount)
SELECT order_id, customer_id, amount
FROM Sales
YEAR(order_date) = 2024;
Adding Primary and Foreign Key
Designations
1. Adding a Primary Key :
Syntax:
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY
(column1);

Example:
ALTER TABLE Employees
ADD CONSTRAINT pk_emp_id PRIMARY KEY (emp_id);
Adding Primary and Foreign Key
Designations
Syntax:
ALTER TABLE <child_table>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (child_column)
REFERENCES <parent_table>(<parent_column>);

Example:
ALTER TABLE Orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES Customers(customer_id);