What is SQL?

Structured Query Language (SQL) is the standard language used to interact with relational
databases.

• Allows users to store, retrieve, update, and manage data efficiently through simple
commands.

• Known for its user-friendly syntax and powerful capabilities, SQL is widely used across
industries.

How Does SQL Work?

• We interact with databases using SQL queries.

• SQL Engine breaks down, optimizes and executes these queries efficiently

• DBMS tools like MySQL and SQL Server have their own SQL engine and an interface
where users can write and execute SQL queries.

Below are the detailed steps involved in the SQL query execution.

1. Input: The user submits a query (e.g., SELECT, INSERT, UPDATE, DELETE) via an
application or interface.

2. Parsing: The query processor breaks the query into parts (tokens) and checks for syntax
and schema correctness.
3. Optimization: The optimizer finds the most efficient way to run the query using indexes,
statistics, and available resources.

4. Execution: The execution engine runs the query using the chosen plan, accessing or
modifying the database as needed.

5. Output: Results are returned to the user — either data (for SELECT) or a success
message (for other operations).

Key Components of a SQL System

• Databases : A database is a structured collection of data. It organizes data into tables,

which are like spreadsheets with rows (records) and columns (fields) .

• Tables: Each table enforces rules and relationships among its columns for data
integrity.

• Indexes: Indexes speed up queries by allowing the database to quickly locate data
without scanning the entire table.

• Views: A view is a virtual table—basically a saved SELECT statement you can query like
a table.

• Stored Procedures: These are pre-written SQL scripts stored inside the database. They
can receive inputs, run complex logic, and return results boosting performance,
reusability, and security.

• Transactions: A transaction groups multiple SQL operations into a single unit. It

ensures all changes are applied successfully or none are, preserving data integrity (ACID
properties)

• Security & Permissions: SQL includes tools to restrict access, letting DBAs assign
who can do what whether it's accessing tables, executing procedures, or changing
structures.

• Joins: Joins combine data from multiple tables based on relationships essential for
querying across related datasets.

Rules for Writing SQL Queries

There are certain rules for SQL which would ensure consistency and functionality across
databases. By following these rules, queries will be well formed and well executed in any
database.

• End with Semicolon (;): Each SQL statement must end with a semicolon to execute
properly.

• Case Insensitivity: SQL keywords (e.g., SELECT, INSERT) are not case-sensitive.
However, table/column names may be case-sensitive depending on the DBMS.

• Whitespace Allowed: Queries can span multiple lines, but use spaces between
keywords and names.

• Reserved Words: Avoid using SQL keywords as names. If needed, wrap them in quotes
(" ") or backticks (`).
 Comments

• Single-line: -- comment

• Multi-line: /* comment */

Data Constraints: Use NOT NULL, UNIQUE, PRIMARY KEY, etc., to ensure data
accuracy.

String Values: Enclose strings in single quotes ('text').

Naming Rules:

• Start with a letter

• Max 30 characters

• Only use letters, numbers, and underscores (_)

What are Different SQL Commands or Queries?

Structured Query Language (SQL) commands are standardized instructions used by

developers to interact with data stored in relational databases. These commands allow
for the creation, manipulation, retrieval and control of data, as well as database
structures. SQL commands are categorized based on their specific functionalities:

1. Data Definition Language

These commands are used to define the structure of database objects

by creating, altering, and dropping the database objects. Based on the needs of the
business, database engineers create and modify database objects using DDL.
The CREATE command, for instance, is used by the database engineer to create
database objects like tables, views, and indexes.

Command Description

Creates a new table, a view on a table, or some other object in

CREATE the database.

ALTER Modifies an existing database object, such as a table

Deletes an entire table, a view of a table, or other objects in the

DROP database

2. Data Manipulation Language

A relational database can be updated with new data using data manipulation language
(DML) statements. The INSERT command, for instance, is used by an application to add
a new record to the database.

Command Description

INSERT Creates a record.

UPDATE Modifies records.

DELETE Deletes records.

3. Data Query Language

Data retrieval instructions are written in the data query language (DQL), which is used to
access relational databases. The SELECT command is used by software programs to
filter and return particular results from a SQL table.

4. Data Control language

DCL commands manage user access to the database by granting or revoking

permissions. Database administrators use DCL to enforce security and control access
to database objects.
 Command Description

GRANT Gives a privilege to the user.

REVOKE Takes back privileges granted by the user.

5. Transaction Control Language

TCL commands manage transactions in relational databases, ensuring data integrity

and consistency. These commands are used to commit changes or roll back operations
in case of errors.

Command Description

Saves all changes made during the current

transaction on a permanent basis. Some
databases provide an auto-commit feature, which
COMMIT can be configured using settings.

Reverts changes made during the current

transaction, ensuring no unwanted changes are
ROLLBACK saved.

Sets a point within a transaction to which changes

SAVEPOINT can be rolled back, allowing partial rollbacks

Benefits of SQL

• Efficiency: SQL is designed to handle complex queries and large datasets with optimal
performance, making data retrieval and manipulation seamless.

• Standardization: As an ANSI and ISO standard language, SQL provides a universal

method to interact with relational databases across platforms.

• Scalability: SQL supports databases ranging from small-scale applications to

enterprise-level systems, ensuring smooth operations regardless of size.

• Flexibility: SQL can be extended with procedural programming (e.g., PL/SQL, T-SQL) to
build complex business logic and custom functions.

Limitations of SQL

• Complexity in Advanced Operations: Advanced functionalities such as indexing, query

optimization and performance tuning require in-depth technical knowledge.
• Scalability Concerns: SQL performs best with structured data; handling unstructured
data or massive distributed systems can pose challenges.

• Platform-Specific Variations: While SQL is standardized, many databases implement

unique extensions, leading to portability and compatibility issues.

• Lack of Real Time Analytics: Traditional SQL databases are not optimized for real-time
data ingestion and analysis.

Real-World Applications of SQL

• E-Commerce: Manage customer orders, product catalogs, and inventory.

• Healthcare: Maintain patient records and appointment schedules.

• Banking: Analyze transaction histories and generate financial reports.

• Web Development: Power dynamic websites with user-specific content.

• Machine Learning and Data Science: Combine SQL databases with tools like Python,
R, and TensorFlow to streamline machine learning workflows.

SQL Data Types

Last Updated : 08 Aug, 2025

•
 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. Choosing the correct data type is
crucial for data integrity, query performance and efficient indexing.

Benefits of using the right data type:

• Memory-efficient storage

• Accurate operations (e.g., calculations, sorting)

• Consistency in stored values

• Validation of input data

SQL data types are broadly categorized into several groups:

1. Numeric Data Types

Numeric data types are fundamental to database design and are used to store numbers,
whether they are integers, decimals or floating-point numbers. These data types allow
for mathematical operations like addition, subtraction, multiplication and division,
which makes them essential for managing financial, scientific and analytical data.

Exact Numeric Datatype

Exact numeric types are used when precise numeric values are needed, such as for
financial data, quantities, and counts. Some common exact numeric types include:
Data Type Description Range

Large integer -9,223,372,036,854,775,808 to

BIGINT
numbers 9,223,372,036,854,775,807

Standard integer -2,147,483,648 to

INT
values 2,147,483,647

SMALLINT Small integers -32,768 to 32,767

Very small
TINYINT 0 to 255
integers

Exact fixed-point
numbers (e.g.,
DECIMAL -10^38 + 1 to 10^38 - 1
for financial
values)

Similar to
DECIMAL, used
NUMERIC -10^38 + 1 to 10^38 - 1
for precision
data

For storing -922,337,203,685,477.5808 to

MONEY
monetary values 922,337,203,685,477.5807

Smaller -214,748.3648 to
SMALLMONEY
monetary values 214,748.3647

Approximate Numeric Datatype

These types are used to store approximate values, such as scientific measurements or
large ranges of data that don't need exact precision.
Data
Type Description Range

-1.79E+308 to
FLOAT Approximate numeric values
1.79E+308

Similar to FLOAT, but with less -3.40E+38 to

REAL
precision 3.40E+38

2. Character and String Data Types

Character data types are used to store text or character-based data. The choice
between fixed-length and variable-length data types depends on the nature of your data.

Data Type Description

The maximum length of 8000 characters. (Fixed-Length

Char
non-Unicode Characters)

The maximum length of 8000 characters. (Variable-Length

Varchar
non-Unicode Characters)

The maximum length of 2^31 - 1 characters(SQL Server

Varchar(max)
2005 only). (Variable Length non-Unicode data)

The maximum length of 2,127,483,647 characters(Variable

Text
Length non-Unicode data)

Unicode Character String Data Types

Unicode data types are used to store characters from any language, supporting a wider
variety of characters. These are given in below table.

Data Type Description

The maximum length of 4000 characters(Fixed-Length

Nchar
Unicode Characters)
Data Type Description

The maximum length of 4000 characters.(Variable-Length

Nvarchar
Unicode Characters)

The maximum length of 2^31 - 1 characters(SQL Server

Nvarchar(max)
2005 only). (Variable Length Unicode data)

3. Date and Time Data Type

SQL provides several data types for storing date and time information. They are essential
for managing timestamps, events and time-based queries. These are given in the below
table.

Storage
Data Type Description Size

DATE stores the data of date (year, month, day) 3 Bytes

stores the data of time (hour,

TIME 3 Bytes
minute,second)

store both the data and time (year, month,

DATETIME 8 Bytes
day, hour, minute, second)

4. Binary Data Types in SQL

Binary data types are used to store binary data such as images, videos or other file
types. These include:

Data Type Description Max Length

Binary Fixed-length binary data. 8000 bytes

VarBinary Variable-length binary data. 8000 bytes

Image Stores binary data as images. 2,147,483,647 bytes

5. Boolean Data Type in SQL

 The BOOLEAN data types are used to store logical values, typically TRUE or FALSE. It is
commonly used for flag fields or binary conditions.

6. Special Data Types

SQL also supports some specialized data types for advanced use cases:

• XML Data Type: Used to store XML data and manipulate XML structures in the database

• Spatial Data Type (Geometry): stores planar spatial data, such as points, lines, and
polygons, in a database table.

SQL Operators

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

• Perform operations like calculations, comparisons, and logical checks.

• Enable filtering, calculating, and updating data in databases.

• Essential for query optimization and accurate data management.

Types of SQL Operators

SQL operators can be categorized based on the type of operation they perform. Here are
the primary types of SQL operators:

• Arithmetic Operator

• Comparison Operator

• Logical Operator

• Bitwise Operators

• Compound Operators

• Special Operators

Each of these operators is essential for performing different types of operations on data
in SQL databases.

SQL Arithmetic Operators

Arithmetic operators in SQL are used to perform mathematical operations on numeric

data types in SQL queries. Some common arithmetic operators:

Operator Description

The addition is used to perform an addition operation on the data

+
values.
Operator Description

- This operator is used for the subtraction of the data values.

This operator works with the 'ALL' keyword and it calculates

/
division operations.

* This operator is used for multiplying data values.

Modulus is used to get the remainder when data is divided by

%
another.

Example: Arithmetic Operations

In this example, we calculates a 5% increment on employee salaries and returns both

the original and updated salary values.

Query:

SELECT emp_salary, emp_salary * 1.05 AS "Revised Salary" FROM employee;

Output:

Arithmetic Operation Example

SQL Comparison Operators

Comparison Operators in SQL are used to compare one expression's value to other
expressions. SQL supports different types of comparison operator, which are described
below:

Operator Description

= Equal to.

> Greater than.

Operator Description

< Less than.

>= Greater than equal to.

<= Less than equal to.

<> Not equal to.

Example: Comparison Operation

In this example, we will retrieve all records from the "MATHS" table where the value in
the "MARKS" column is equal to 50.

Query:

SELECT * FROM MATHS WHERE MARKS=50;

Output:

Comparison Operation

SQL Logical Operators

Logical Operators in SQL are used to combine or manipulate conditions in SQL queries
to retrieve or manipulate data based on specified criteria..
Operator Description

Logical AND compares two Booleans as expressions and returns

AND
true when both expressions are true.

Logical OR compares two Booleans as expressions and returns

OR
true when one of the expressions is true.

Not takes a single Boolean as an argument and change its value

NOT
from false to true or from true to false.

Example: Logical Operation

In this example, retrieve all records from the "employee" table where the "emp_city"
column is equal to 'Allahabad' and the "emp_country" column is equal to 'India'.

Query:

SELECT * FROM employee

WHERE emp_city = 'Allahabad' AND emp_country = 'India';

Output:

Logical Operation

SQL Bitwise Operators

Bitwise operators in SQL are used to perform bitwise operations on binary values in
SQL queries, manipulating individual bits to perform logical operations at the bit level.
Some SQL Bitwise Operators are:

Operator Description

& Bitwise AND operator

| Bitwise OR operator

^ Bitwise XOR (exclusive OR) operator

Operator Description

~ Bitwise NOT (complement) operator

<< Left shift operator

>> Right shift operator

SQL Compound Operators

Compound operators combine an operation with assignment. These operators modify

the value of a column and store the result in the same column in a single step. Some
Compound operators are:

Operator Description

+= Add and assign

-= Subtract and assign

*= Multiply and assign

/= Divide and assign

%= Modulo and assign

&= Bitwise AND and assign

^= Bitwise XOR and assign

|= Bitwise OR and assign

SQL Special Operators

SQL also provides several special operators that serve specific functions such as
filtering data based on a range, checking for existence, and comparing sets of values.
Operators Description

ALL is used to select all records of a SELECT STATEMENT. It

compares a value to every value in a list of results from a query.
The ALL must be preceded by the comparison operators and
ALL evaluated to TRUE if the query returns no rows.

ANY compares a value to each value in a list of results from a

query and evaluates to true if the result of an inner query
ANY contains at least one row.

The SQL BETWEEN operator tests an expression against a range.

The range consists of a beginning, followed by an AND keyword
BETWEEN and an end expression.

The IN operator checks a value within a set of values separated

IN by commas and retrieves the rows from the table that match.

The EXISTS checks the existence of a result of a subquery. The

EXISTS subquery tests whether a subquery fetches at least one
EXISTS row. When no data is returned then this operator returns 'FALSE'.

SOME operator evaluates the condition between the outer and

inner tables and evaluates to true if the final result returns any
SOME one row. If not, then it evaluates to false.

The UNIQUE operator searches every unique row of a specified

UNIQUE table.

Example: Special Operator (BETWEEN)

In this example, we will retrieve all records from the "employee" table where the
"emp_id" column has a value that falls within the range of 101 to 104 (inclusive).

Query:

SELECT * FROM employee WHERE emp_id BETWEEN 101 AND 104;

Output:
 Special Operator

SQL Commands | DDL, DQL, DML, DCL and TCL Commands

SQL commands are the fundamental building blocks for communicating with
a database management system (DBMS). It is used to interact with the database with
some operations. It is also used to perform specific tasks, functions, and queries of
data. SQL can perform various tasks like creating a table, adding data to tables,
dropping the table, modifying the table, set permission for users.

SQL Commands are mainly categorized into five categories:

• DDL – Data Definition Language

• DQL – Data Query Language

• DML – Data Manipulation Language

• DCL – Data Control Language

• TCL - Transaction Control Language

1. DDL - Data Definition Language

DDL or Data Definition Language actually consists of the SQL commands that can be
used for defining, altering and deleting database structures such as tables, indexes and
schemas. It simply deals with descriptions of the database schema and is used to
create and modify the structure of database objects in the database

Common DDL Commands

Command Description Syntax

Create database or its CREATE TABLE

objects (table, index, table_name (column1
CREATE
function, views, store data_type, column2
procedure, and triggers) data_type, ...);

Delete objects from the

DROP DROP TABLE table_name;
database
Command Description Syntax

ALTER TABLE table_name

Alter the structure of the
ALTER ADD COLUMN
database
column_name data_type;

Remove all records from

a table, including all TRUNCATE TABLE
TRUNCATE
spaces allocated for the table_name;
records are removed

COMMENT
Add comments to the
COMMENT 'comment_text' ON TABLE
data dictionary
table_name;

RENAME TABLE
Rename an object
RENAME old_table_name TO
existing in the database
new_table_name;

Example:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);

In this example, a new table called employees is created with columns for employee ID,
first name, last name and hire date.

2. DQL - Data Query Language

DQL statements are used for performing queries on the data within schema objects. The
purpose of the DQL Command is to get some schema relation based on the query
passed to it. This command allows getting the data out of the database to perform
operations with it. When a SELECT is fired against a table or tables the result is compiled
into a further temporary table, which is displayed or perhaps received by the program.

DQL Command
Command Description Syntax

It is used to retrieve SELECT column1, column2,

SELECT data from the ...FROM table_name WHERE
database condition;

Indicates
SELECT column1
FROM the table(s) from
FROM table_name;
which to retrieve data.

Filters rows before any SELECT column1

WHERE grouping or FROM table_name
aggregation WHERE condition;

SELECT column1,
Groups rows that have
GROUP AGG_FUNCTION(column2)
the same values in
BY FROM table_name
specified columns.
GROUP BY column1;

SELECT column1,
AGG_FUNCTION(column2)
Filters the results
HAVING FROM table_name
of GROUP BY
GROUP BY column1
HAVING condition;

Removes duplicate SELECT DISTINCT column1,

DISTINCT rows from the result column2, ...
set FROM table_name;

SELECT column1
ORDER Sorts the result set by FROM table_name
BY one or more columns ORDER BY column1 [ASC |
DESC];

By default, it sorts
in ascending SELECT * FROM table_name
LIMIT
order unless specified LIMIT number;
as DESC
Example:

SELECT first_name, last_name, hire_date

FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;

This query retrieves employees' first and last names, along with their hire dates, from the
employees table, specifically for those in the 'Sales' department, sorted by hire date.

3. DML - Data Manipulation Language

The SQL commands that deal with the manipulation of data present in the database
belong to DML or Data Manipulation Language and this includes most of the SQL
statements. It is the component of the SQL statement that controls access to data and
to the database. Basically, DCL statements are grouped with DML statements.

Common DML Commands

Command Description Syntax

INSERT INTO table_name (column1,

Insert data into
INSERT column2, ...) VALUES (value1,
a table
value2, ...);

Update existing UPDATE table_name SET column1 =

UPDATE data within a value1, column2 = value2 WHERE
table condition;

Delete records
DELETE FROM table_name WHERE
DELETE from a
condition;
database table

Table control LOCK TABLE table_name IN

LOCK
concurrency lock_mode;

Call a PL/SQL
CALL or JAVA CALL procedure_name(arguments);
subprogram

Describe the
EXPLAIN EXPLAIN PLAN FOR SELECT * FROM
access path to
PLAN table_name;
data
Example:

INSERT INTO employees (first_name, last_name, department)

VALUES ('Jane', 'Smith', 'HR');

This query inserts a new record into the employees table with the first name 'Jane', last
name 'Smith' and department 'HR'.

4. DCL - Data Control Language

DCL (Data Control Language) includes commands such as GRANT and REVOKE which
mainly deal with the rights, permissions and other controls of the database
system. These commands are used to control access to data in the database by granting
or revoking permissions.

Common DCL Commands

Command Description Syntax

Assigns new privileges to GRANT privilege_type

a user account, allowing [(column_list)] ON
GRANT access to specific [object_type]
database objects, object_name TO user
actions, or functions. [WITH GRANT OPTION];

Removes previously REVOKE [GRANT OPTION

granted privileges from a FOR] privilege_type
user account, taking [(column_list)] ON
REVOKE
away their access to [object_type]
certain database objects object_name FROM user
or actions. [CASCADE];

Example of DCL

GRANT SELECT, UPDATE ON employees TO user_name;

This command grants the user user_name the permissions to select and update records
in the employees table.

5. TCL - Transaction Control Language

Transactions group a set of tasks into a single execution unit. Each transaction begins
with a specific task and ends when all the tasks in the group are successfully
completed. If any of the tasks fail, the transaction fails. Therefore, a transaction has only
two results: success or failure.

Common TCL Commands

Command Description Syntax

BEGIN Starts a new BEGIN TRANSACTION

TRANSACTION transaction [transaction_name];

Saves all changes

COMMIT made during the COMMIT;
transaction

Undoes all changes

ROLLBACK made during the ROLLBACK;
transaction

Creates a savepoint
SAVEPOINT
SAVEPOINT within the current
savepoint_name;
transaction

Example:

BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE department = 'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;

In this example, a transaction is started, changes are made, and a savepoint is set. If
needed, the transaction can be rolled back to the savepoint before being committed.

Most Important SQL Commands

There are also a few other SQL Commands we often rely on when writing powerful
queries. While they don’t fit neatly into the five main categories, they’re absolutely
essential for working with data effectively.

Command Description

SELECT Retrieves data from one or more tables.

INSERT Adds new rows (records) to a table.

Command Description

UPDATE Modifies existing data in a table.

DELETE Removes specific rows from a table.

CREATE TABLE Creates a new table in the database.

Modifies the structure of an existing table (e.g., add or

ALTER TABLE
remove columns).

DROP TABLE Permanently deletes a table and its data.

Removes all rows from a table but keeps its structure

TRUNCATE TABLE
intact.

WHERE Filters records based on a condition.

ORDER BY Sorts the result set in ascending or descending order.

Groups rows that have the same values in specified

GROUP BY
columns.

HAVING Filters grouped data (used with GROUP BY).

Combines rows from two or more tables based on a

JOIN
related column.

DISTINCT Removes duplicate values from the result set.

IN / BETWEEN /
Used for advanced filtering conditions.
LIKE
 Command Description

UNION Combines the result of two or more SELECT queries.

GRANT Gives user privileges or permissions.

REVOKE Removes user privileges.

COMMIT Saves all changes made in the current transaction.

Undoes changes if something goes wrong in a

ROLLBACK
transaction.

SAVEPOINT Sets a point in a transaction to roll back to if needed.

SQL Database Operations

SQL databases or relational databases are widely used for storing, managing and
organizing structured data in a tabular format. These databases store data in tables
consisting of rows and columns. SQL is the standard programming language used to
interact with these databases. It enables users to create, update, retrieve and manage
data effectively.

Why Do SQL Databases Exist?

• Efficient Data Handling: SQL databases are designed to store, retrieve and manipulate
structured data effectively.

• Reliability: They ensure data consistency and integrity, making them ideal for high
priority applications.

• Complex Relationships: SQL databases can model complex data relationships,

essential for transactional and enterprise systems.

• Transaction Support: They provide reliable transaction processing, ensuring

consistency even under high-demand scenarios.

• Scalability: SQL databases efficiently scale to handle growing data volumes while
maintaining performance.

• ACID Compliance: SQL databases guarantee data accuracy and reliability through
ACID properties (Atomicity, Consistency, Isolation, Durability).
• Universal Adoption: As the standard for relational data, SQL databases are widely used
across industries, from business to government.

How SQL Databases Work

SQL databases work using a client-server system. The database server stores all the
data, while the client interacts with the server by sending queries to retrieve or update
the data.

• Data Storage: Data is stored in tables, each containing rows and columns. Each column
defines a specific attribute, and each row represents a unique entity or record.

• Query Processing: When a user submits a query, the database server processes it by
parsing the SQL command, optimizing the query for performance, and executing it to
retrieve or manipulate the data.

• Data Retrieval: SQL queries can retrieve specific data using SELECT statements and
filter results using WHERE clauses. The server returns the queried data in a structured
format for use by applications.

SQL Database Management

It explains the key processes involved in creating, selecting, renaming and dropping SQL
databases. Understanding these commands is essential for managing your databases
effectively.
1. CREATE Database

The CREATE DATABASE command is used to initialize a new database. It creates an

empty database that acts as a container for your tables and other database objects. It
allows you to organize and manage data easily.

Example:

CREATE DATABASE test_db;

2. SELECT Database

The USE or SELECT DATABASE command allows you to select a specific database for the
current session. Once selected, the database becomes the active database, and all
subsequent queries will be executed on it. This command is essential when working
with multiple databases.

USE test_db;

3. RENAME Database

The RENAME DATABASE command can be used where you need to rename an existing
database. All databases does not support this command, it can be useful when we need
to update the name of a database to show its content.

RENAME DATABASE test_db TO new_test_db;

4. DROP Database
The DROP DATABASE command is used to permanently delete a database, along with all
its tables, data and objects. This action is irreversible, so it should be used carefully. It is
helpful when you no longer need a database and want to free up system resources.

DROP DATABASE test_db;

Difference Between SQL and NoSQL Databases

Below you will find the key differences between SQL and NoSQL databases:

Feature SQL Databases NoSQL Databases

Key-value pairs,
Data Tables with rows and
documents, columns,
Structure columns.
graphs.

Schema Fixed schema. Flexible schema.

ACID compliance for

Data Integrity Eventual consistency.
consistency.

Scalability Vertically scalable. Horizontally scalable.

Various query languages

Query
Uses SQL for queries. (e.g., MongoDB,
Language
Cassandra).

Supports ACID Limited transaction

Transactions
transactions. support.

Best for structured

Ideal for unstructured
Use Cases data and complex
data and scalability.
queries.

MySQL, PostgreSQL, MongoDB, Cassandra,

Examples
SQL Server. Redis.
 Feature SQL Databases NoSQL Databases

Supports complex
Limited relationship
Relationships joins and
support.
relationships.

Flexibility Less flexible. Highly flexible.

Conclusion

SQL databases are used for managing structured data in many applications. They
provide various benefits such as data consistency, optimized performance, scalibility
and minimum redundancy. SQL Databases are ideal for handling complex queries and
transactional systems.

SQL CREATE TABLE

Creating a table is one of the first and most important steps in building a database. The CREATE
TABLE command in SQL defines how your data will be stored, including the table name, column
names, data types, and rules (constraints) such as NOT NULL, PRIMARY KEY, and CHECK.

• Defines a new table in the database.

• Specifies columns, their data types, and sizes.

• Applies constraints such as NOT NULL, PRIMARY KEY, and CHECK.

• Ensures accuracy, consistency, and organized data storage.

Syntax:

CREATE table table_name

Column1 datatype (size),

column2 datatype (size),

columnN datatype(size)

);

Key Terms

• table_name: The name you assign to the new table.

• column1, column2, ... : The names of the columns in the table.

 • datatype(size): Defines the data type and size of each column.

Example: Create a Customer Table

Let’s walk through a practical example where we create a Customer table that stores customer
data. We will define various columns such as CustomerID, CustomerName, Country, Age, and
Phone with appropriate data types and constraints.

Query:

CREATE TABLE Customer(

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(50),

LastName VARCHAR(50),

Country VARCHAR(50),

Age INT CHECK (Age >= 0 AND Age <= 99),

Phone int(10)

);

Output:

Explanation:

• CustomerID is an integer and serves as the PRIMARY KEY, ensuring each record is
unique.

• CustomerName, LastName, and Country are VARCHAR fields to store variable-length

text.

• Age has a CHECK constraint, ensuring it’s within the range of 0 to 99.

• Phone is an integer field, although in real scenarios, a VARCHAR would often be used for
storing phone numbers to allow for leading zeros and formatting.

Inserting Data into the Table

After creating the table, you can use INSERT INTO command to add data into it. Here is how to
add some sample records into the Customer table:

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)

VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),

(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),

 (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),

(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),

(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

Note: If you are working with a large amount of data, consider using bulk inserts or importing
data from external files to optimize performance.

Create Table from Existing Table

We can also create a new table based on the structure (and optionally the data) of an existing
table. The CREATE TABLE AS SELECT command allows us to duplicate an entire table or select
specific columns to form a new one. The following query creates a new table called SubTable
that contains CustomerID and CustomerName from the existing Customer table.

Syntax:

CREATE TABLE new_table_name AS

SELECT column1, column2, ...

FROM existing_table_name

WHERE ...;

In this example, we create a new table SubTable that contains just the CustomerID and
CustomerName columns from the Customer table. This method is useful for creating backups
or performing quick data migrations.

Query:

CREATE TABLE SubTable AS

SELECT CustomerID, CustomerName

FROM customer;

Output:
Note: We can use * instead of column name to copy whole table to another table.

Key Tips for Using CREATE TABLE in SQL

To ensure the smooth creation and management of your tables, keep these points in mind:

1. The CREATE TABLE statement can also define constraints like NOT NULL, UNIQUE, and
DEFAULT.

2. If you attempt to create a table that already exists, SQL will throw an error. To avoid this, you
can use the IF NOT EXISTS clause.

CREATE TABLE IF NOT EXISTS Customer (...);

3. Always define appropriate data types for each column (e.g., VARCHAR(50) for names
and INT for IDs) to optimize performance and storage.

4. After creating a table, use the following command to view the structure of your table:

DESC table_name;

5. If you need to change the table’s structure after creation (e.g., renaming a column, adding a
new column), use the ALTER TABLE statement.

SQL SELECT Query

SQL SELECT is used to retrieve data from one or more tables, either all records or specific
results based on conditions. It returns the output in a tabular format of rows and columns.

• Extracts data from tables.

• Targets specific or all columns (*).

• Supports filtering, sorting, grouping, and joins.

• Results are stored in a result set.

Syntax:

SELECT column1,column2.... FROM table_name ;

Parameters

• column1, column2: The columns you want to retrieve.

• table_name: The name of the table you're querying.

Examples of SELECT Statement

Let us start by creating a sample table that we will use for our examples. We will also insert
some sample data to make the demonstration more practical.

CREATE TABLE Customer(

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(50),

LastName VARCHAR(50),

Country VARCHAR(50),

Age int(2),

Phone int(10)

);

-- Insert some sample data into the Customers table

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)

VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),

(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),

(3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),

(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),

(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

Example 1: Select Specific Columns

In this example, we will demonstrate how to retrieve specific columns from the Customer table.
Here we will fetch only CustomerName and LastName for each record.

Query:

SELECT CustomerName, LastName

FROM Customer;

Output

Example 2: Select All Columns

In this example, we will fetch all the fields from the table Customer:

Query:

SELECT * FROM Customer;

Output

Example 3: SELECT Statement with WHERE Clause

Suppose we want to see table values with specific conditions then WHERE Clause is used with
select statement. In this example, filter customers who are 21 years old.

Query:

SELECT CustomerName

FROM Customer

where Age = '21';

Output:

Example 4: SELECT with GROUP BY Clause

In this example, we will use SELECT statement with GROUP BY Clause to Group rows and
perform aggregation. Here, Count orders per customer.

Query:

SELECT Customer_id, COUNT(*) AS order_count

FROM Orders

GROUP BY Customer_id;

Output:

Example 5: SELECT Statement with HAVING Clause

Use HAVING to filter results after grouping. Consider the following database for fetching
departments with total salary above 50,000. Use WHERE for row-level filtering, HAVING for
group-level filtering.
Query:

SELECT Department, sum(Salary) as Salary

FROM employee

GROUP BY department

HAVING SUM(Salary) >= 50000;

Output:
Example 6: SELECT Statement with ORDER BY clause in SQL

In this example, we will use SELECT Statement with ORDER BY clause. Here, Sort results
by Age in descending order.

Query:

SELECT * FROM Customer ORDER BY Age DESC;

Output:

Common SELECT Use Cases

Goal Technique

Fetch unique values SELECT DISTINCT column FROM table;

Limit result rows SELECT * FROM table LIMIT 10; (MySQL/PostgreSQL)

Aliases for clarity SELECT CustomerName AS Name FROM Customer;

Join tables SELECT a.*, b.* FROM TableA a JOIN TableB b ON a.id = b.id;

SQL INSERT INTO Statement

The INSERT INTO statement in SQL is used to add new rows to an existing table, whether for all
columns, specific columns or by copying from another table. It is an essential command for
populating databases with relevant records like customers, employees, or students.

• Insert data into all or selected columns

• Add one or multiple rows at once

• Copy data from another table

• Populate tables with meaningful records

1. Inserting Data into All Columns

This method is used when you want to insert data into all columns of a table without specifying
column names. We simply provide the values for each column, in the same order that the
columns are defined in the table.

Syntax:

INSERT INTO table_name

VALUES (value1, value2, value);

Parameters:

• table_name: The name of the table where the data will be inserted

• value1, value2: The values you want to insert into the respective columns of the table

Example: For better understanding, let's look at the SQL INSERT INTO statement with
examples. Let us first create a table named 'Student'.

CREATE DATABASE StudentDB;

USE StudentDB;
CREATE TABLE Student (

ROLL_NO INT PRIMARY KEY,

NAME VARCHAR(50),

ADDRESS VARCHAR(100),

PHONE VARCHAR(15),

AGE INT

);

INSERT INTO Student (ROLL_NO, NAME, ADDRESS, PHONE, AGE) VALUES

(1, 'Ram', 'Delhi', 'XXXXXXXXXX', 18),

(2, 'Ramesh', 'Gurgaon', 'XXXXXXXXXX', 18),

(3, 'Sujit', 'Rohtak', 'XXXXXXXXXX', 20),

(4, 'Suresh', 'Rohtak', 'XXXXXXXXXX', 18);

Output

ROLL_NO NAME ADDRESS PHONE AGE

1 Ram Delhi xxxxxxxxxxxxxx 18

2 RAMESH GURGAON xxxxxxxxxxxxxx 18

3 SUJIT ROHTAK xxxxxxxxxxxxxx 20

4 SURESH ROHTAK xxxxxxxxxxxxxx 18

3 SUJIT ROHTAK xxxxxxxxxxxxxx 20

2 RAMESH GURGAON xxxxxxxxxxxxxx 18

If we don’t want to specify the column names (and you’re inserting data into all columns), we
can directly insert values in the order they appear in the table structure. Here's an example:

Query:
INSERT INTO Student

VALUES ('5','HARSH','WEST BENGAL', 'XXXXXXXXXX','19');

Output

ROLL_NO NAME ADDRESS PHONE Age

1 Ram Delhi XXXXXXXXXX 18

2 RAMESH GURGAON XXXXXXXXXX 18

3 SUJIT ROHTAK XXXXXXXXXX 20

4 SURESH Delhi XXXXXXXXXX 18

3 SUJIT ROHTAK XXXXXXXXXX 20

2 RAMESH GURGAON XXXXXXXXXX 18

5 HARSH WEST BENGAL XXXXXXXXXX 19

2. Inserting Data into Specific Columns

In some cases, you might want to insert data into only certain columns, leaving the others
empty or with default values. In such cases, we can specify the column names explicitly.

Syntax

INSERT INTO table_name (column1, column2, column3

VALUES ( value1, value2, value);

Parameters:

• table_name: name of the table.

• column1, column2..: name of first column, second column.

• value1, value2, value..: the values for each specified column of the new record.

Example: Let’s say we only want to insert the student's ID, name, and age into
the Students table, and leave the address and phone number as NULL (the default value).

INSERT INTO Student (ROLL_NO, NAME, Age)

VALUES ('5', "PRATIK", 19');

Output:

ROLL_NO NAME ADDRESS PHONE Age

1 Ram Delhi XXXXXXXXXX 18

2 RAMESH GURGAON XXXXXXXXXX 18

3 SUJIT ROHTAK XXXXXXXXXX 20

4 SURESH Delhi XXXXXXXXXX 18

3 SUJIT ROHTAK XXXXXXXXXX 20

2 RAMESH GURGAON XXXXXXXXXX 18

5 PRATIK null null 19

Note: Columns not included in the INSERT statement are filled with default values (typically
NULL).

3. Inserting Multiple Rows at Once

Instead of running multiple INSERT INTO commands, you can insert multiple rows into a table in
a single query. This is more efficient and reduces the number of database operations.

Syntax

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

VALUES

(value1, value2, ...),

(value1, value2, ...),

(value1, value2, ...);

Example: If we want to add multiple students to the Students table in one go, the query would
look like this:

INSERT INTO Student (ROLL_NO, NAME, AGE, ADDRESS, PHONE)

VALUES
(6, 'Amit Kumar', 15, 'Delhi', 'XXXXXXXXXX'),

(7, 'Gauri Rao', 18, 'Bangalore', 'XXXXXXXXXX'),

(8, 'Manav Bhatt', 17, 'New Delhi', 'XXXXXXXXXX'),

(9, 'Riya Kapoor', 10, 'Udaipur', 'XXXXXXXXXX');

Output:

ROLL_NO NAME ADDRESS PHONE AGE

1 Ram Delhi XXXXXXXXXX 18

2 Ramesh Gurgaon XXXXXXXXXX 18

3 Sujit Rohtak XXXXXXXXXX 20

4 Suresh Rohtak XXXXXXXXXX 18

5 Pratik NULL NULL 19

6 Amit Kumar Delhi XXXXXXXXXX 15

7 Gauri Rao Bangalore XXXXXXXXXX 18

8 Manav Bhatt New Delhi XXXXXXXXXX 17

9 Riya Kapoor Udaipur XXXXXXXXXX 10

Explanation:

• This method is faster than running multiple individual INSERT INTO commands.

• If you're inserting more than 1000 rows, consider using bulk insert or multiple insert
statements for efficiency.

4. Inserting Data from One Table into Another Table

We can also copy data from one table into another table using the INSERT INTO
SELECT statement. This is very useful when we want to move or replicate data from one table to
another without manually typing all the data.
Method 1: Insert All Columns from Another Table

Inserts every column from source table into destination table

INSERT INTO target_table

SELECT * FROM source_table;

Example: If you want to copy all data from the OldStudents table into the Students table, use
this query:

INSERT INTO Students

SELECT * FROM OldStudents;

Method 2: Insert Specific Columns from Another Table

Allows inserting only selected columns from the source table.

INSERT INTO target_table (col1, col2, ...)

SELECT col1, col2, ...

FROM source_table;

Example: Let’s say we want to copy only the Name and Age columns
from OldStudents into Students:

INSERT INTO Students (Name, Age)

SELECT Name, Age

FROM OldStudents;

Method 3: Insert Specific Rows Based on Condition

You can also insert specific rows based on a condition by using the WHERE clause with
the SELECT statement.

INSERT INTO target_table

SELECT * FROM source_table
WHERE condition;

Example: If we want to copy only students older than 20 years from OldStudents to Students,
we would write:

INSERT INTO Students

SELECT * FROM OldStudents

WHERE Age > 20;

Best Practices
Point Description

Multiple Rows Use a single INSERT for multiple entries—more efficient

When not specifying columns, ensure value order matches column

Column Order
order

NULL Values Missing columns will be set to NULL or their default values

Insert from
Easily copy records between tables with SELECT
SELECT

Efficiency Multiple rows inserted at once reduce query execution time

SQL UPDATE Statement

The UPDATE statement in SQL is used to modify existing records in a table without deleting
them. It allows updating one or multiple columns, with or without conditions, to keep data
accurate and consistent.

• Change specific column values in selected rows

• Apply targeted updates using WHERE

• Update single or multiple columns at once

• More efficient than deleting and re-inserting rows

• Maintains data integrity by modifying in place

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2,...
WHERE condition;

Parameters

• table_name: Name of the table you want to update.

• SET: The column(s) you want to update and their new values.

• WHERE: Filters the specific rows you want to update.

Note: The SET keyword assigns new values to columns, while the WHERE clause selects which
rows to update. Without WHERE, all rows will be updated.
Examples of SQL UPDATE Statement

Let’s begin by creating a Customer table with some sample data. This table contains each
customer's unique ID, name, last name, phone number and country. We will use it to
demonstrate how the UPDATE statement works in SQL.

Query:

CREATE TABLE Customer(

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(50),

LastName VARCHAR(50),

Country VARCHAR(50),

Age int(2),

Phone int(10)

);

-- Insert some sample data into the Customers table

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)

VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),

(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),

(3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),

(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),

(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output

Example 1: Update Single Column Using UPDATE Statement

We have a Customer table and we want to Update the CustomerName where the Age is 22.

Query:

UPDATE Customer

SET CustomerName = 'Nitin'

WHERE Age = 22;

Output:

Explanation: Only the rows where Age is 22 will be updated, and the CustomerName will be set
to 'Nitin'.

Example 2: Updating Multiple Columns using UPDATE Statement

We need to update both the CustomerName and Country for a specific CustomerID.

Query:

UPDATE Customer

SET CustomerName = 'Satyam',

Country = 'USA'

WHERE CustomerID = 1;

Output:
Explanation: For the row where CustomerID is 1, both CustomerName and Country will be
updated simultaneously.

Note: For updating multiple columns we have used comma(,) to separate the names and values
of two columns.

Example 3: Omitting WHERE Clause in UPDATE Statement

If we accidentally omit the WHERE clause, all the rows in the table will be updated, which is a
common mistake. Let’s update the CustomerName for every record in the table:

Query:

UPDATE Customer

SET CustomerName = 'Shubham';

Output

Explanation: This will set the CustomerName for every row in the Customer table to
'Shubham'. Be careful while omitting the WHERE clause, as this action is irreversible unless you
have a backup.

Optimizing SQL UPDATE Queries

• Avoid frequent updates: Constantly updating rows can slow down performance. Batch
updates or consider using a database trigger to handle automatic updates.
 • Index relevant columns: Ensure that columns in the WHERE clause (such
as CustomerID) are indexed. This will improve the speed of the update operation.

Best Practices for Using SQL UPDATE Statement

1. Always use the WHERE clause:

The most important point when using the UPDATE statement is to always include
a WHERE clause unless you genuinely intend to update all rows.

2. Check your data before updating:

Run a SELECT query to view the data you want to update before executing
the UPDATE statement. This helps avoid accidental data modifications.

SELECT * FROM Customer WHERE Age = 22;

3. Use transactions for critical updates:

When performing updates in a production environment, consider using transactions.

Transactions allow you to commit or roll back changes as needed.

BEGIN TRANSACTION;

UPDATE Customer SET CustomerName = 'John' WHERE CustomerID = 3;

COMMIT; -- Use ROLLBACK to undo if necessary

4. Test on a small dataset first: If you are uncertain about the impact of your UPDATE, test it on
a small subset of data to ensure the changes are as expected.

SQL DELETE Statement

The SQL DELETE statement is used to remove specific rows from a table while keeping the table
structure intact. It is different from DROP, which deletes the entire table.

• Removes rows based on conditions.

• Retains table schema, constraints, and indexes.

• Can delete a single row or all rows.

• Useful for cleaning or managing datasets.

Syntax:

DELETE FROM table_name

WHERE some_condition;

Parameter Explanation

• Some_condition: A condition used to filter the rows you want to delete.

• table_name: The name of the table from which you want to delete the rows

Note: We can delete single as well as multiple records depending on the condition we provide in
the WHERE clause. If we omit the WHERE clause then all of the records will be deleted and the
table will be empty.
Examples of SQL DELETE Statement

Assume we have created a table named GFG_Employee in SQL, which contains the personal
details of the Employee including their id, name, email and department etc. as shown below.

CREATE TABLE GFG_Employees (

id INT PRIMARY KEY,

name VARCHAR (20) ,

email VARCHAR (25),

department VARCHAR(20)

);

INSERT INTO GFG_Employees (id, name, email, department) VALUES

(1, 'Jessie', 'jessie23@gmail.com', 'Development'),

(2, 'Praveen', 'praveen_dagger@yahoo.com', 'HR'),

(3, 'Bisa', 'dragonBall@gmail.com', 'Sales'),

(4, 'Rithvik', 'msvv@hotmail.com', 'IT'),

(5, 'Suraj', 'srjsunny@gmail.com', 'Quality Assurance'),

(6, 'Om', 'OmShukla@yahoo.com', 'IT'),

(7, 'Naruto', 'uzumaki@konoha.com', 'Development');

Select * From GFG_Employees

Output

Example 1: Deleting Single Record

We can use the DELETE statement with a condition to delete a specific row from a table.
The WHERE clause ensures only the intended record is removed. We can delete the records
named Rithvik by using the below query:

Query:

DELETE FROM GFG_Employees

WHERE NAME = 'Rithvik';

Output:

Example 2: Deleting Multiple Records

To delete multiple records, you can specify a condition that matches several rows. Let's delete
the rows from the table GFG_Employees where the department is "Development". This will
delete 2 rows (the first row and the seventh row).
Query

DELETE FROM GFG_Employees

WHERE department = 'Development';

Output

output

Example 3: Delete All Records from a Table

If we need to delete all records from the table, we can omit the WHERE clause, or alternatively
use the DELETE statement with an asterisk (*) to denote all rows.

Query:

DELETE FROM GFG_Employees;

Or

DELETE * FROM GFG_Employees;

Output:

output

All of the records in the table will be deleted, there are no records left to display. The table
GFG_Employees will become empty.

Rolling Back DELETE Operations

Since the DELETE statement is a DML operation, it can be rolled back when executed in a
statement. If you accidentally delete records or need to repeat the process, you can use
the ROLLBACK command.

Query:

START TRANSACTION;

DELETE FROM GFG_Employees WHERE department = 'Development';

-- If needed, you can rollback the deletion

ROLLBACK;
Explanation: The ROLLBACK command will undo the changes made by the DELETE statement,
effectively restoring the records that were deleted during the transaction.

SQL | WHERE Clause

In SQL, the WHERE clause is used to filter rows based on specific conditions. Whether you are
retrieving, updating, or deleting data, WHERE ensures that only relevant records are affected.
Without it, your query applies to every row in the table! The WHERE clause helps you:

• Filter rows that meet certain conditions

• Target specific data using logical, comparison and pattern-based operators

• Control SELECT, UPDATE, DELETE or even INSERT statements

Syntax:

SELECT column1, column2

FROM table_name

WHERE column_name operator value;

Parameters:

• column1, column2: Columns you want to retrieve

• table_name: Table you are querying from

• operator: Comparison logic (e.g., =, <, >, LIKE)

• value: The value or pattern to filter against

Importance of WHERE Clause

The WHERE clause is critical for several reasons:

• Data Accuracy: Filters data to return only relevant rows

• Performance: Reduces the amount of scanned data

• Flexibility: Works with many operators and conditions

Examples of WHERE Clause in SQL

We will create a basic employee table structure in SQL for performing all the where clause
operation.

Query:

CREATE TABLE Emp1 (

EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Country VARCHAR(50),
Age INT,
Mob VARCHAR(15)
);

INSERT INTO Emp1 VALUES

(1, 'Shubham', 'India', 23, '738479734'),
(2, 'Aman', 'Australia', 21, '436789555'),
(3, 'Naveen', 'Sri Lanka', 24, '34873847'),
(4, 'Aditya', 'Austria', 21, '328440934'),
(5, 'Nishant', 'Spain', 22, '73248679');

SELECT * FROM Emp1;

Output:

EmpID Name Country Age mob

1 Shubham India 23 738479734

2 Aman Australia 21 436789555

3 Naveen Sri lanka 24 34873847

4 Aditya Austria 21 328440934

5 Nishant Spain 22 73248679

Example 1: Where Clause with Logical Operators

To fetch records of Employee with age equal to 24.

Query:

SELECT * FROM Emp1 WHERE Age=24;

Output:

Example 2: WHERE with Comparison Operators

To fetch the EmpID, Name and Country of Employees with Age greater than 21.

Query:

SELECT EmpID, Name, Country FROM Emp1 WHERE Age > 21;
Output:

Example 3: Where Clause with BETWEEN Operator

The BETWEEN operator is used to filter records within a specified range, and it includes both
the start and end values. In this example, we want to find employees whose age is between 22
and 24, including both 22 and 24.

Query:

SELECT * FROM Emp1

WHERE Age BETWEEN 22 AND 24;

Output:

Example 4: Where Clause with LIKE Operator

It is used to fetch filtered data by searching for a particular pattern in the where clause. In this
example we want to find records of Employees where Name starts with the letter. The
'%'(wildcard) signifies the later characters here which can be of any length and value.

Query:

SELECT * FROM Emp1 WHERE Name LIKE 'S%';

Output:

To fetch records of Employees where Name contains the pattern 'M'.

Query:

SELECT * FROM Emp1 WHERE Name LIKE '%M%';

Output:
Example 5: Where Clause with IN Operator

It is used to fetch the filtered data same as fetched by '=' operator just the difference is that here
we can specify multiple values for which we can get the result set. Here we want to find the
Names of Employees where Age is 21 or 23.

Query:

SELECT Name FROM Emp1 WHERE Age IN (21,23);

Output:

List of Operators that Can be Used with WHERE Clause

Operator Description

> Greater Than

>= Greater than or Equal to

< Less Than

<= Less than or Equal to

= Equal to

<> Not Equal to

 Operator Description

BETWEEN In an inclusive Range

LIKE Search for a pattern

IN To specify multiple possible values for a column

SQL | Aliases

ead and write. They don’t change the actual names in the database and exist only for the
duration of that query.

• Make long or complex names readable

• Simplify joins and subqueries

• Improve clarity in result sets

• Avoid naming conflicts in multi-table queries

There are two types of aliases in SQL:

• Column Aliases: Temporary names for columns in the result set.

• Table Aliases: Temporary names for tables used within a query.

Example of SQL Aliases

We will use the following Customer table to demonstrate all SQL alias concepts. This table
contains customer information such as ID, name, country, age, and phone number.

CREATE TABLE Customer (

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(50),

LastName VARCHAR(50),

Country VARCHAR(50),

Age INT,

Phone VARCHAR(15)

);

-- Inserting sample data into the Customer table

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)

VALUES

(1, 'Shubham', 'Thakur', 'India', 23, '9876543210'),

(2, 'Aman', 'Chopra', 'Australia', 21, '9876543211'),

(3, 'Naveen', 'Tulasi', 'Sri Lanka', 24, '9876543212'),

(4, 'Aditya', 'Arpan', 'Austria', 21, '9876543213'),

(5, 'Nishant', 'Jain', 'Spain', 22, '9876543214');

Output:

CustomerID CustomerName LastName Country Age Phone

1 Shubham Thakur India 23 9876543210

2 Aman Chopra Australia 21 9876543211

3 Naveen Tulasi Sri Lanka 24 9876543212

4 Aditya Arpan Austria 21 9876543213

5 Nishant Jain Spain 22 9876543214

1. Column Aliases

A column alias is used to rename a column just for the output of a query. They are useful when:

• Displaying aggregate data

• Making results more readable

• Performing calculations

Syntax:

SELECT column_name AS alias_name

FROM table_name;

The following table explains the arguments in detail:

• column_name: column on which we are going to create an alias name.

• alias_name: temporary name that we are going to assign for the column or table.

• AS: It is optional. If you have not specified it, there is no effect on the query execution.
Example 1: Column Alias for Renaming a Column

To fetch the CustomerID and rename it as id in the result set

SELECT CustomerID AS id

FROM Customer;

Output:

id

2. Table Aliases

A table alias is used when you want to give a table a temporary name for the duration of a query.
Table aliases are especially helpful in JOIN operations to simplify queries, particularly when the
same table is referenced multiple times (like in self-joins).

Example 2: Table Alias for Joining Tables

We want to join the Customer table with itself to find customers who have the same country
and are aged 21. We will use table aliases for each instance of the Customer table.

Query:

SELECT c1.CustomerName, c1.Country

FROM Customer AS c1, Customer AS c2

WHERE c1.Age = c2.Age AND c1.Country = c2.Country;

Output:

CustomerName Country

Shubham India
CustomerName Country

Aman Australia

Naveen Sri Lanka

Aditya Austria

Nishant Spain

Here, c1 and c2 are aliases for two instances of the Customer table.

Combining Column and Table Aliases

We want to fetch customers who are aged 21 or older and rename the columns for better clarity.
We will use both table and column aliases.

Query:

SELECT c.CustomerName AS Name, c.Country AS Location

FROM Customer AS c

WHERE c.Age >= 21;

Output:

Name Location

Shubham India

Aman Australia

Naveen Sri Lanka

Aditya Austria

Nishant Spain

Practical Uses of SQL Aliases

 • Better Readability: Makes complex names shorter and easier to understand.

• Simplifies Queries: Reduces repetition, especially in joins.

• Clear Output: Renames columns for more meaningful results.

• Avoids Conflicts: Prevents naming clashes in multi-table queries.

SQL Joins (Inner, Left, Right and Full Join)

SQL joins are fundamental tools for combining data from multiple tables in relational
databases.

• For example, consider two tables where one table (say Student) has student information
with id as a key and other table (say Marks) has information about marks of every
student id. Now to display the marks of every student with name, we need to join the two
tables.

• Please remember, we store data into multiple tables as part of database

normalization to avoid anomalies and redundancies.

Types of SQL Joins

Let us visualize how each join type operates:

1. SQL INNER JOIN

The INNER JOIN keyword selects all rows from both the tables as long as the condition is
satisfied. This keyword will create the result set by combining all rows from both the tables
where the condition satisfies i.e value of the common field will be the same.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2

ON table1.matching_column = table2.matching_column;

Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.
 Inner Join

Example of INNER JOIN

Consider the two tables, Student and StudentCourse, which share a common
column ROLL_NO. Using SQL JOINS, we can combine data from these tables based on their
relationship, allowing us to retrieve meaningful information like student details along with their
enrolled courses.

1. Student Table:

Student

2. StudentCourse Table:
 StudentCourse

Let's look at the example of INNER JOIN clause, and understand it's working. This query will
show the names and age of students enrolled in different courses.

Query:

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student

INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

Output

Output

2. SQL LEFT JOIN

A LEFT JOIN returns all rows from the left table, along with matching rows from the right table. If
there is no match, NULL values are returned for columns from the right table. LEFT JOIN is also
known as LEFT OUTER JOIN.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.

Left JOIN

LEFT JOIN Example

In this example, the LEFT JOIN retrieves all rows from the Student table and the matching rows
from the StudentCourse table based on the ROLL_NO column.

Query:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output
 Output

3. SQL RIGHT JOIN

RIGHT JOIN returns all the rows of the table on the right side of the join and matching rows for
the table on the left side of the join. It is very similar to LEFT JOIN for the rows for which there is
no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as
RIGHT OUTER JOIN.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

Key Terms

• table1: First table.

• table2: Second table

• matching_column: Column common to both the tables.

Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are the same
Right JOIN

RIGHT JOIN Example

In this example, the RIGHT JOIN retrieves all rows from the StudentCourse table and the
matching rows from the Student table based on the ROLL_NO column.

Query:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output
 Output

4. SQL FULL JOIN

FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The
result-set will contain all the rows from both tables. For the rows for which there is no matching,
the result-set will contain NULL values.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

Key Terms
 • table1: First table.

• table2: Second table

• matching_column: Column common to both the tables.

FULL JOIN Example

This example demonstrates the use of a FULL JOIN, which combines the results of both LEFT
JOIN and RIGHT JOIN. The query retrieves all rows from the Student and StudentCourse tables.
If a record in one table does not have a matching record in the other table, the result set will
include that record with NULL values for the missing fields

Query:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output

NAME COURSE_ID

HARSH 1

PRATIK 2

RIYANKA 2

DEEP 3

SAPTARHI 1

DHANRAJ NULL

ROHIT NULL

NIRAJ NULL
NAME COURSE_ID

NULL 4

NULL 5

NULL 4

5. SQL Natural Join

A Natural Join is a type of INNER JOIN that automatically joins two tables based on columns
with the same name and data type. It returns only the rows where the values in the common
columns match.

• It returns rows where the values in these common columns are the same in both tables.

• Common columns appear only once in the result, even if they exist in both tables.

• Unlike a CROSS JOIN, which creates all possible combinations of rows, a Natural Join
only includes rows with matching values

Example:

Look at the two tables below: Employee and Department

Employee

Emp_id Emp_name Dept_id

1 Ram 10

2 Jon 30

3 Bob 50

Department

Dept_id Dept_name
Department

10 IT

30 HR

40 TIS

Find all Employees and their respective departments.

(Employee) ? (Department)

Output:

Emp_id Emp_name Dept_id Dept_id Dept_name

1 Ram 10 10 IT

2 Jon 30 30 HR

SQL UPDATE Statement

The UPDATE statement in SQL is used to modify existing records in a table without deleting
them. It allows updating one or multiple columns, with or without conditions, to keep data
accurate and consistent.

• Change specific column values in selected rows

• Apply targeted updates using WHERE

• Update single or multiple columns at once

• More efficient than deleting and re-inserting rows

• Maintains data integrity by modifying in place

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2,...
WHERE condition;

Parameters

• table_name: Name of the table you want to update.

 • SET: The column(s) you want to update and their new values.

• WHERE: Filters the specific rows you want to update.

Note: The SET keyword assigns new values to columns, while the WHERE clause selects which
rows to update. Without WHERE, all rows will be updated.

Examples of SQL UPDATE Statement

Let’s begin by creating a Customer table with some sample data. This table contains each
customer's unique ID, name, last name, phone number and country. We will use it to
demonstrate how the UPDATE statement works in SQL.

Query:

CREATE TABLE Customer(

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(50),

LastName VARCHAR(50),

Country VARCHAR(50),

Age int(2),

Phone int(10)

);

-- Insert some sample data into the Customers table

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)

VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),

(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),

(3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),

(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),

(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output
Example 1: Update Single Column Using UPDATE Statement

We have a Customer table and we want to Update the CustomerName where the Age is 22.

Query:

UPDATE Customer

SET CustomerName = 'Nitin'

WHERE Age = 22;

Output:

Explanation: Only the rows where Age is 22 will be updated, and the CustomerName will be set
to 'Nitin'.

Example 2: Updating Multiple Columns using UPDATE Statement

We need to update both the CustomerName and Country for a specific CustomerID.

Query:

UPDATE Customer

SET CustomerName = 'Satyam',

Country = 'USA'
WHERE CustomerID = 1;

Output:

Explanation: For the row where CustomerID is 1, both CustomerName and Country will be
updated simultaneously.

Note: For updating multiple columns we have used comma(,) to separate the names and values
of two columns.

Example 3: Omitting WHERE Clause in UPDATE Statement

If we accidentally omit the WHERE clause, all the rows in the table will be updated, which is a
common mistake. Let’s update the CustomerName for every record in the table:

Query:

UPDATE Customer

SET CustomerName = 'Shubham';

Output

Explanation: This will set the CustomerName for every row in the Customer table to
'Shubham'. Be careful while omitting the WHERE clause, as this action is irreversible unless you
have a backup.

Optimizing SQL UPDATE Queries

 • Avoid frequent updates: Constantly updating rows can slow down performance. Batch
updates or consider using a database trigger to handle automatic updates.

• Index relevant columns: Ensure that columns in the WHERE clause (such
as CustomerID) are indexed. This will improve the speed of the update operation.

Best Practices for Using SQL UPDATE Statement

1. Always use the WHERE clause:

The most important point when using the UPDATE statement is to always include
a WHERE clause unless you genuinely intend to update all rows.

2. Check your data before updating:

Run a SELECT query to view the data you want to update before executing
the UPDATE statement. This helps avoid accidental data modifications.

SELECT * FROM Customer WHERE Age = 22;

3. Use transactions for critical updates:

When performing updates in a production environment, consider using transactions.

Transactions allow you to commit or roll back changes as needed.

BEGIN TRANSACTION;

UPDATE Customer SET CustomerName = 'John' WHERE CustomerID = 3;

COMMIT; -- Use ROLLBACK to undo if necessary

4. Test on a small dataset first: If you are uncertain about the impact of your UPDATE, test it on
a small subset of data to ensure the changes are as expected.

SQL DELETE Statement

The SQL DELETE statement is used to remove specific rows from a table while keeping the table
structure intact. It is different from DROP, which deletes the entire table.

• Removes rows based on conditions.

• Retains table schema, constraints, and indexes.

• Can delete a single row or all rows.

• Useful for cleaning or managing datasets.

Syntax:

DELETE FROM table_name

WHERE some_condition;

Parameter Explanation

• Some_condition: A condition used to filter the rows you want to delete.

• table_name: The name of the table from which you want to delete the rows
Note: We can delete single as well as multiple records depending on the condition we provide in
the WHERE clause. If we omit the WHERE clause then all of the records will be deleted and the
table will be empty.

Examples of SQL DELETE Statement

Assume we have created a table named GFG_Employee in SQL, which contains the personal
details of the Employee including their id, name, email and department etc. as shown below.

CREATE TABLE GFG_Employees (

id INT PRIMARY KEY,

name VARCHAR (20) ,

email VARCHAR (25),

department VARCHAR(20)

);

INSERT INTO GFG_Employees (id, name, email, department) VALUES

(1, 'Jessie', 'jessie23@gmail.com', 'Development'),

(2, 'Praveen', 'praveen_dagger@yahoo.com', 'HR'),

(3, 'Bisa', 'dragonBall@gmail.com', 'Sales'),

(4, 'Rithvik', 'msvv@hotmail.com', 'IT'),

(5, 'Suraj', 'srjsunny@gmail.com', 'Quality Assurance'),

(6, 'Om', 'OmShukla@yahoo.com', 'IT'),

(7, 'Naruto', 'uzumaki@konoha.com', 'Development');

Select * From GFG_Employees

Output
Example 1: Deleting Single Record

We can use the DELETE statement with a condition to delete a specific row from a table.
The WHERE clause ensures only the intended record is removed. We can delete the records
named Rithvik by using the below query:

Query:

DELETE FROM GFG_Employees

WHERE NAME = 'Rithvik';

Output:

Example 2: Deleting Multiple Records

To delete multiple records, you can specify a condition that matches several rows. Let's delete
the rows from the table GFG_Employees where the department is "Development". This will
delete 2 rows (the first row and the seventh row).

Query

DELETE FROM GFG_Employees

WHERE department = 'Development';

Output

output

Example 3: Delete All Records from a Table

If we need to delete all records from the table, we can omit the WHERE clause, or alternatively
use the DELETE statement with an asterisk (*) to denote all rows.

Query:

DELETE FROM GFG_Employees;

Or

DELETE * FROM GFG_Employees;

Output:

output

All of the records in the table will be deleted, there are no records left to display. The table
GFG_Employees will become empty.

Rolling Back DELETE Operations

Since the DELETE statement is a DML operation, it can be rolled back when executed in a
statement. If you accidentally delete records or need to repeat the process, you can use
the ROLLBACK command.

Query:

START TRANSACTION;

DELETE FROM GFG_Employees WHERE department = 'Development';

-- If needed, you can rollback the deletion

ROLLBACK;

Explanation: The ROLLBACK command will undo the changes made by the DELETE statement,
effectively restoring the records that were deleted during the transaction.

SQL DELETE Statement

The SQL DELETE statement is used to remove specific rows from a table while keeping the table
structure intact. It is different from DROP, which deletes the entire table.

• Removes rows based on conditions.

• Retains table schema, constraints, and indexes.

• Can delete a single row or all rows.

• Useful for cleaning or managing datasets.

Syntax:

DELETE FROM table_name

WHERE some_condition;

Parameter Explanation

• Some_condition: A condition used to filter the rows you want to delete.

• table_name: The name of the table from which you want to delete the rows

Note: We can delete single as well as multiple records depending on the condition we provide in
the WHERE clause. If we omit the WHERE clause then all of the records will be deleted and the
table will be empty.

Examples of SQL DELETE Statement

Assume we have created a table named GFG_Employee in SQL, which contains the personal
details of the Employee including their id, name, email and department etc. as shown below.

CREATE TABLE GFG_Employees (

id INT PRIMARY KEY,

name VARCHAR (20) ,

email VARCHAR (25),

department VARCHAR(20)

);

INSERT INTO GFG_Employees (id, name, email, department) VALUES

(1, 'Jessie', 'jessie23@gmail.com', 'Development'),

(2, 'Praveen', 'praveen_dagger@yahoo.com', 'HR'),

(3, 'Bisa', 'dragonBall@gmail.com', 'Sales'),

(4, 'Rithvik', 'msvv@hotmail.com', 'IT'),

(5, 'Suraj', 'srjsunny@gmail.com', 'Quality Assurance'),

(6, 'Om', 'OmShukla@yahoo.com', 'IT'),

(7, 'Naruto', 'uzumaki@konoha.com', 'Development');

Select * From GFG_Employees

Output

Example 1: Deleting Single Record

We can use the DELETE statement with a condition to delete a specific row from a table.
The WHERE clause ensures only the intended record is removed. We can delete the records
named Rithvik by using the below query:

Query:

DELETE FROM GFG_Employees

WHERE NAME = 'Rithvik';

Output:
Example 2: Deleting Multiple Records

To delete multiple records, you can specify a condition that matches several rows. Let's delete
the rows from the table GFG_Employees where the department is "Development". This will
delete 2 rows (the first row and the seventh row).

Query

DELETE FROM GFG_Employees

WHERE department = 'Development';

Output
output

Example 3: Delete All Records from a Table

If we need to delete all records from the table, we can omit the WHERE clause, or alternatively
use the DELETE statement with an asterisk (*) to denote all rows.

Query:

DELETE FROM GFG_Employees;

Or

DELETE * FROM GFG_Employees;

Output:

output

All of the records in the table will be deleted, there are no records left to display. The table
GFG_Employees will become empty.

Rolling Back DELETE Operations

Since the DELETE statement is a DML operation, it can be rolled back when executed in a
statement. If you accidentally delete records or need to repeat the process, you can use
the ROLLBACK command.

Query:

START TRANSACTION;

DELETE FROM GFG_Employees WHERE department = 'Development';

-- If needed, you can rollback the deletion

ROLLBACK;

Explanation: The ROLLBACK command will undo the changes made by the DELETE statement,
effectively restoring the records that were deleted during the transaction.

SQL | WHERE Clause

In SQL, the WHERE clause is used to filter rows based on specific conditions. Whether you are
retrieving, updating, or deleting data, WHERE ensures that only relevant records are affected.
Without it, your query applies to every row in the table! The WHERE clause helps you:
 • Filter rows that meet certain conditions

• Target specific data using logical, comparison and pattern-based operators

• Control SELECT, UPDATE, DELETE or even INSERT statements

Syntax:

SELECT column1, column2

FROM table_name

WHERE column_name operator value;

Parameters:

• column1, column2: Columns you want to retrieve

• table_name: Table you are querying from

• operator: Comparison logic (e.g., =, <, >, LIKE)

• value: The value or pattern to filter against

Importance of WHERE Clause

The WHERE clause is critical for several reasons:

• Data Accuracy: Filters data to return only relevant rows

• Performance: Reduces the amount of scanned data

• Flexibility: Works with many operators and conditions

Examples of WHERE Clause in SQL

We will create a basic employee table structure in SQL for performing all the where clause
operation.

Query:

CREATE TABLE Emp1 (

EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Country VARCHAR(50),
Age INT,
Mob VARCHAR(15)
);

INSERT INTO Emp1 VALUES

(1, 'Shubham', 'India', 23, '738479734'),
(2, 'Aman', 'Australia', 21, '436789555'),
(3, 'Naveen', 'Sri Lanka', 24, '34873847'),
(4, 'Aditya', 'Austria', 21, '328440934'),
(5, 'Nishant', 'Spain', 22, '73248679');

SELECT * FROM Emp1;

Output:

EmpID Name Country Age mob

1 Shubham India 23 738479734

2 Aman Australia 21 436789555

3 Naveen Sri lanka 24 34873847

4 Aditya Austria 21 328440934

5 Nishant Spain 22 73248679

Example 1: Where Clause with Logical Operators

To fetch records of Employee with age equal to 24.

Query:

SELECT * FROM Emp1 WHERE Age=24;

Output:

Example 2: WHERE with Comparison Operators

To fetch the EmpID, Name and Country of Employees with Age greater than 21.

Query:

SELECT EmpID, Name, Country FROM Emp1 WHERE Age > 21;

Output:

Example 3: Where Clause with BETWEEN Operator

The BETWEEN operator is used to filter records within a specified range, and it includes both
the start and end values. In this example, we want to find employees whose age is between 22
and 24, including both 22 and 24.

Query:

SELECT * FROM Emp1

WHERE Age BETWEEN 22 AND 24;

Output:

Example 4: Where Clause with LIKE Operator

It is used to fetch filtered data by searching for a particular pattern in the where clause. In this
example we want to find records of Employees where Name starts with the letter. The
'%'(wildcard) signifies the later characters here which can be of any length and value.

Query:

SELECT * FROM Emp1 WHERE Name LIKE 'S%';

Output:

To fetch records of Employees where Name contains the pattern 'M'.

Query:

SELECT * FROM Emp1 WHERE Name LIKE '%M%';

Output:

Example 5: Where Clause with IN Operator

It is used to fetch the filtered data same as fetched by '=' operator just the difference is that here
we can specify multiple values for which we can get the result set. Here we want to find the
Names of Employees where Age is 21 or 23.

Query:

SELECT Name FROM Emp1 WHERE Age IN (21,23);

Output:

List of Operators that Can be Used with WHERE Clause

Operator Description

> Greater Than

>= Greater than or Equal to

< Less Than

<= Less than or Equal to

= Equal to

<> Not Equal to

BETWEEN In an inclusive Range

LIKE Search for a pattern

IN To specify multiple possible values for a column

SQL | Aliases

In SQL, aliases are temporary names given to columns or tables to make queries easier to read
and write. They don’t change the actual names in the database and exist only for the duration of
that query.
 • Make long or complex names readable

• Simplify joins and subqueries

• Improve clarity in result sets

• Avoid naming conflicts in multi-table queries

There are two types of aliases in SQL:

• Column Aliases: Temporary names for columns in the result set.

• Table Aliases: Temporary names for tables used within a query.

Example of SQL Aliases

We will use the following Customer table to demonstrate all SQL alias concepts. This table
contains customer information such as ID, name, country, age, and phone number.

CREATE TABLE Customer (

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(50),

LastName VARCHAR(50),

Country VARCHAR(50),

Age INT,

Phone VARCHAR(15)

);

-- Inserting sample data into the Customer table

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)

VALUES

(1, 'Shubham', 'Thakur', 'India', 23, '9876543210'),

(2, 'Aman', 'Chopra', 'Australia', 21, '9876543211'),

(3, 'Naveen', 'Tulasi', 'Sri Lanka', 24, '9876543212'),

(4, 'Aditya', 'Arpan', 'Austria', 21, '9876543213'),

(5, 'Nishant', 'Jain', 'Spain', 22, '9876543214');

Output:
CustomerID CustomerName LastName Country Age Phone

1 Shubham Thakur India 23 9876543210

2 Aman Chopra Australia 21 9876543211

3 Naveen Tulasi Sri Lanka 24 9876543212

4 Aditya Arpan Austria 21 9876543213

5 Nishant Jain Spain 22 9876543214

1. Column Aliases

A column alias is used to rename a column just for the output of a query. They are useful when:

• Displaying aggregate data

• Making results more readable

• Performing calculations

Syntax:

SELECT column_name AS alias_name

FROM table_name;

The following table explains the arguments in detail:

• column_name: column on which we are going to create an alias name.

• alias_name: temporary name that we are going to assign for the column or table.

• AS: It is optional. If you have not specified it, there is no effect on the query execution.

Example 1: Column Alias for Renaming a Column

To fetch the CustomerID and rename it as id in the result set

SELECT CustomerID AS id

FROM Customer;

Output:
id

2. Table Aliases

A table alias is used when you want to give a table a temporary name for the duration of a query.
Table aliases are especially helpful in JOIN operations to simplify queries, particularly when the
same table is referenced multiple times (like in self-joins).

Example 2: Table Alias for Joining Tables

We want to join the Customer table with itself to find customers who have the same country
and are aged 21. We will use table aliases for each instance of the Customer table.

Query:

SELECT c1.CustomerName, c1.Country

FROM Customer AS c1, Customer AS c2

WHERE c1.Age = c2.Age AND c1.Country = c2.Country;

Output:

CustomerName Country

Shubham India

Aman Australia

Naveen Sri Lanka

CustomerName Country

Aditya Austria

Nishant Spain

Here, c1 and c2 are aliases for two instances of the Customer table.

Combining Column and Table Aliases

We want to fetch customers who are aged 21 or older and rename the columns for better clarity.
We will use both table and column aliases.

Query:

SELECT c.CustomerName AS Name, c.Country AS Location

FROM Customer AS c

WHERE c.Age >= 21;

Output:

Name Location

Shubham India

Aman Australia

Naveen Sri Lanka

Aditya Austria

Nishant Spain

Practical Uses of SQL Aliases

• Better Readability: Makes complex names shorter and easier to understand.

• Simplifies Queries: Reduces repetition, especially in joins.

• Clear Output: Renames columns for more meaningful results.

• Avoids Conflicts: Prevents naming clashes in multi-table queries.

SQL Joins (Inner, Left, Right and Full Join)

SQL joins are fundamental tools for combining data from multiple tables in relational
databases.

• For example, consider two tables where one table (say Student) has student information
with id as a key and other table (say Marks) has information about marks of every
student id. Now to display the marks of every student with name, we need to join the two
tables.

• Please remember, we store data into multiple tables as part of database

normalization to avoid anomalies and redundancies.

Types of SQL Joins

Let us visualize how each join type operates:

1. SQL INNER JOIN

The INNER JOIN keyword selects all rows from both the tables as long as the condition is
satisfied. This keyword will create the result set by combining all rows from both the tables
where the condition satisfies i.e value of the common field will be the same.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2

ON table1.matching_column = table2.matching_column;

Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.

Inner Join

Example of INNER JOIN

Consider the two tables, Student and StudentCourse, which share a common
column ROLL_NO. Using SQL JOINS, we can combine data from these tables based on their
relationship, allowing us to retrieve meaningful information like student details along with their
enrolled courses.

1. Student Table:

Student

2. StudentCourse Table:

StudentCourse

Let's look at the example of INNER JOIN clause, and understand it's working. This query will
show the names and age of students enrolled in different courses.

Query:
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

Output

Output

2. SQL LEFT JOIN

A LEFT JOIN returns all rows from the left table, along with matching rows from the right table. If
there is no match, NULL values are returned for columns from the right table. LEFT JOIN is also
known as LEFT OUTER JOIN.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.

Left JOIN
LEFT JOIN Example

In this example, the LEFT JOIN retrieves all rows from the Student table and the matching rows
from the StudentCourse table based on the ROLL_NO column.

Query:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output

Output

3. SQL RIGHT JOIN

RIGHT JOIN returns all the rows of the table on the right side of the join and matching rows for
the table on the left side of the join. It is very similar to LEFT JOIN for the rows for which there is
no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as
RIGHT OUTER JOIN.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

Key Terms

• table1: First table.

• table2: Second table

• matching_column: Column common to both the tables.

Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are the same
Right JOIN

RIGHT JOIN Example

In this example, the RIGHT JOIN retrieves all rows from the StudentCourse table and the
matching rows from the Student table based on the ROLL_NO column.

Query:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output
 Output

4. SQL FULL JOIN

FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The
result-set will contain all the rows from both tables. For the rows for which there is no matching,
the result-set will contain NULL values.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

Key Terms
 • table1: First table.

• table2: Second table

• matching_column: Column common to both the tables.

FULL JOIN Example

This example demonstrates the use of a FULL JOIN, which combines the results of both LEFT
JOIN and RIGHT JOIN. The query retrieves all rows from the Student and StudentCourse tables.
If a record in one table does not have a matching record in the other table, the result set will
include that record with NULL values for the missing fields

Query:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output

NAME COURSE_ID

HARSH 1

PRATIK 2

RIYANKA 2

DEEP 3

SAPTARHI 1

DHANRAJ NULL

ROHIT NULL

NIRAJ NULL
NAME COURSE_ID

NULL 4

NULL 5

NULL 4

5. SQL Natural Join

A Natural Join is a type of INNER JOIN that automatically joins two tables based on columns
with the same name and data type. It returns only the rows where the values in the common
columns match.

• It returns rows where the values in these common columns are the same in both tables.

• Common columns appear only once in the result, even if they exist in both tables.

• Unlike a CROSS JOIN, which creates all possible combinations of rows, a Natural Join
only includes rows with matching values

Example:

Look at the two tables below: Employee and Department

Employee

Emp_id Emp_name Dept_id

1 Ram 10

2 Jon 30

3 Bob 50

Department

Dept_id Dept_name
Department

10 IT

30 HR

40 TIS

Find all Employees and their respective departments.

(Employee) ? (Department)

Output:

Emp_id Emp_name Dept_id Dept_id Dept_name

1 Ram 10 10 IT

2 Jon 30 30 HR

SQL CROSS JOIN

In SQL, the CROSS JOIN is a unique join operation that returns the Cartesian product of two or
more tables. This means it matches each row from the left table with every row from the right
table, resulting in a combination of all possible pairs of records.

In this article, we will learn the CROSS JOIN operation, its syntax, and practical examples to
clarify how and when to use it effectively.

What is SQL CROSS JOIN?

Cross Join in SQL produces a result set that contains the cartesian product of two or more
tables. Cross join is also called a Cartesian Join. When CROSS JOIN is used with a WHERE
clause, it behaves like INNER JOIN, filtering the results based on specific conditions. CROSS
JOIN is the best choice when we need to match each row of one table to every other row of
another table. It is helpful in many applications where we need to obtain paired combinations of
records.
 Cross Join B/W Two Sets

Syntax:

SELECT * FROM table1

CROSS JOIN table2;

Examples of SQL CROSS JOIN

Let's look at some examples of CROSS JOIN statement in SQL to understand it's working.

In this CROSS JOIN tutorial, we will use the following two tables in examples:

Table 1- Customer

ID NAME AGE PHONE

1 AMIT JAIN 21 98474

2 JATIN VERMA 47 63996

Table 2- Orders

ORDER_ID AMOUNT PLACED_ON

101 999 2023-04-19

102 4999 2023-04-20

To create both these tables on your system, you can write the following code:

Example 1: CROSS JOIN

In this example, we will use the CROSS JOIN command to match the data of the Customer and
Orders table.

Query:

SELECT *
FROM CUSTOMER
CROSS JOIN ORDERS;

Output:

Cross Join

As we can see, whether the other table matches or not, the CROSS JOIN keyword returns all
similar records from both tables. Therefore, if there are rows in "Customers" or "Orders" that do
not match any entries in either table, those rows will also be listed.

Important Points About CROSS JOIN

• CROSS JOIN performs the cross-product of records from two or more joined tables.

• It is used when we want every possible combination of rows to be present in a

database's tables.

• SQL CROSS JOIN with condition of WHERE Clause operates as an INNER JOIN; when
used without one, it produces the cartesian product of all the rows from all the tables
provided in the SQL query.

• CROSS JOIN is different from other join types like INNER JOIN, LEFT JOIN, and RIGHT
JOIN, as it does not require a matching condition between the tables.

Conclusion

The SQL CROSS JOIN is a powerful tool for generating all possible combinations of rows from
two or more tables. It is particularly useful in scenarios where every record needs to be paired
with every other record, such as in creating combinations or generating test data. However, it
should be used with caution, especially with large datasets, as it can quickly result in huge
result sets that could impact performance. By understanding when and how to use CROSS
JOIN, you can harness its power to handle complex queries and generate diverse datasets for a
wide range of applications.
SQL | Date Functions (Set-1)

SQL Date Functions are essential for managing and manipulating date and time values in SQL
databases. They provide tools to perform operations such as calculating date differences,
retrieving current dates and times and formatting dates. From tracking sales trends to
calculating project deadlines, working with temporal data is central to decision-making and
reporting.

SQL Date Functions

SQL Date Functions are built-in utilities provided by SQL databases to perform operations on
date and time values. These functions simplify handling temporal data, making them
indispensable for tasks like calculating intervals, extracting year or month values, and
formatting dates for display.

Let's use the salestable as the base for demonstrating each of the SQL Date
Functions mentioned. Here’s the structure of the sales table:

Sales Table

1. NOW()

The NOW() function returns the current date and time based on the server’s time zone. It's
commonly used when we need to capture the exact moment an event occurs, such as
a transaction timestamp. This function is useful for logging timestamps or comparing current
time with database records.

Query:

SELECT NOW() AS current_datetime;

Output

current_datetime

2024-08-12 14:35:27
2. CURDATE()

The CURDATE() function returns the current date in the YYYY-MM-DD format. It is useful when
we need to retrieve only the current date without the time. This function is often used in
reporting or filtering records by date.

Query:

SELECT CURDATE() AS current_date;

Output

current_date

2024-08-12

3. CURTIME()

The CURTIME() function returns the current time in the HH:MM:SS format. It is useful for time-
specific operations, such as scheduling tasks. By isolating the time, this function helps in
scenarios requiring precise time comparisons.

Query:

SELECT CURTIME() AS current_time;

Output

current_time

14:35:27

4. DATE()

The DATE() function extracts the date portion from a date or datetime expression. This function
is useful when we want to ignore the time component and focus only on the date. This function
is commonly employed in date-only comparisons or aggregations.

Query:

SELECT sale_id, product_name,

DATE(sale_date) AS sale_date_only
FROM sales;

Output
Date Function

5. EXTRACT()

The EXTRACT() function allows us to retrieve a specific part (like year, month, or day) from a
date. It's particularly useful when we need to group or filter data based on specific time
components. This function is especially useful in reports that require year-over-year analysis.

Query:

SELECT sale_id, product_name,

EXTRACT(YEAR FROM sale_date)
AS sale_year FROM sales;

Output

Extract Function

6. DATE_ADD()

The DATE_ADD() function adds a specified time interval (like days, months, or years) to a date.
It's often used in scenarios where we need to calculate a future date. This function simplifies
future date calculations for planning purposes.

Query:

SELECT sale_id, product_name, DATE_ADD(sale_date, INTERVAL 7 DAY) AS

sale_date_plus_7_days FROM sales;

Output
sale_id product_name sale_date_plus_7_days

1 Widget A 2024-08-08

2 Widget B 2024-08-12

3 Widget C 2024-08-14

4 Widget A 2024-08-17

5 Widget B 2024-08-22

6 Widget C 2024-08-27

7. DATE_SUB()

The DATE_SUB()function subtracts a specified time interval from a date. It's handy when we
need to determine a past date by subtracting days, months, or years. This is often used for
retrospective data analysis.

Query:

SELECT sale_id, product_name,

DATE_SUB(sale_date, INTERVAL 3 DAY)
AS sale_date_minus_3_days
FROM sales;

Output

sale_id product_name sale_date_minus_3_days

1 Widget A 2024-07-29

2 Widget B 2024-08-02

3 Widget C 2024-08-04
sale_id product_name sale_date_minus_3_days

4 Widget A 2024-08-07

5 Widget B 2024-08-12

6 Widget C 2024-08-17

8. DATEDIFF()

The DATEDIFF() function returns the difference in days between two dates. It's commonly used
to calculate the duration between two events or dates. This function is ideal for deadline
tracking or overdue calculations. Here we will find out how many days are left from each sale
till August 15, 2024.

Query:

SELECT sale_id, product_name, sale_date,

DATEDIFF('2024-08-15', sale_date) AS days_until_aug15
FROM sales;

Output

sale_id product_name sale_date days_until_aug15

1 Widget A 2024-08-01 14

2 Widget B 2024-08-05 10

3 Widget C 2024-08-07 8

4 Widget A 2024-08-10 5

5 Widget B 2024-08-15 0

6 Widget C 2024-08-20 -5

9. DATE_FORMAT()
The DATE_FORMAT() function formats a date according to a specified format, allowing
for customized date output (e.g. displaying the full day name, month name etc). This function
is excellent for improving report readability.

Query:

SELECT sale_id, product_name,

DATE_FORMAT(sale_date, '%W, %M %d, %Y')
AS formatted_sale_date FROM sales;

Output

sale_id product_name formatted_sale_date

1 Widget A Thursday, August 01, 2024

2 Widget B Monday, August 05, 2024

3 Widget C Wednesday, August 07, 2024

4 Widget A Saturday, August 10, 2024

5 Widget B Thursday, August 15, 2024

6 Widget C Tuesday, August 20, 2024

10. ADDDATE()

The ADDDATE()function adds a specified time interval to a date. It is useful for

calculating future or past dates based on a given date.

Query:

SELECT sale_id, product_name,

ADDDATE(sale_date, INTERVAL 10 DAY)
AS sale_date_plus_10_days
FROM sales;

Output
sale_id product_name sale_date_plus_10_days

1 Widget A 2024-08-11

2 Widget B 2024-08-15

3 Widget C 2024-08-17

4 Widget A 2024-08-20

5 Widget B 2024-08-25

6 Widget C 2024-08-30

11. ADDTIME()

The ADDTIME()function adds a specified time interval to a time or datetime value. It is useful for
adjusting times by adding hours, minutes or seconds.

Query:

SELECT sale_id, product_name, ADDTIME('10:30:00', '02:30:00') AS sale_time_plus_2hrs_30min

FROM sales;

Output

sale_id product_name sale_time_plus_2hrs_30min

1 Widget A 13:00:00

2 Widget B 13:00:00

3 Widget C 13:00:00

4 Widget A 13:00:00
sale_id product_name sale_time_plus_2hrs_30min

5 Widget B 13:00:00

6 Widget C 13:00:00

Conclusion

SQL Date Functions are powerful tools for managing and analysing temporal data. They
streamline operations ranging from simple date extractions to complex interval calculations.
Mastering these functions empowers users to derive meaningful insights and create accurate,
dynamic reports. By integrating these functions into our queries, we can enhance both
efficiency and clarity in handling date and time data.

SQL | String functions

SQL String Functions are powerful tools that allow us to manipulate, format, and extract
specific parts of text data in our database. These functions are essential for tasks like cleaning
up data, comparing strings, and combining text fields. Whether we're working
with names, addresses, or any form of textual data, mastering SQL string functions is crucial
for efficient data handling and analysis.

Common SQL String Functions

String functions are used to perform an operation on input string and return an output string.
Below are some of the most commonly used SQL string functions:

1. CONCAT(): Concatenate Strings

The CONCAT() function is used to concatenate (combine) two or more strings into one string. It
is useful when we want to merge fields like first and last names into a full name.

Query:

SELECT CONCAT('John', ' ', 'Doe') AS FullName;

Output:

John Doe

2. CHAR_LENGTH() / CHARACTER_LENGTH(): Find String Length

The CHAR_LENGTH() or LENGTH() function returns the length of a string in characters. It’s
essential for validating or manipulating text data, especially when you need to know how many
characters a string contains.

Query:

SELECT CHAR_LENGTH('Hello') AS StringLength;

Output:
5

3. UPPER() and LOWER(): Convert Text Case

These functions convert the text to uppercase or lowercase, respectively. They are useful for
normalizing the case of text in a database.

Query:

SELECT UPPER('hello') AS UpperCase;

SELECT LOWER('HELLO') AS LowerCase;

Output:

HELLO
hello

4. LENGTH(): Length of String in Bytes

LENGTH() returns the length of a string in bytes. This can be useful for working with multi-byte
character sets.

Query:

SELECT LENGTH('Hello') AS LengthInBytes;

Output:

5. REPLACE(): Replace Substring in String

The REPLACE() function replaces occurrences of a substring within a string with another
substring. This is useful for cleaning up data, such as replacing invalid characters or formatting
errors.

Query:

SELECT REPLACE('Hello World', 'World', 'SQL') AS UpdatedString;

Output:

Hello SQL

6. SUBSTRING() / SUBSTR(): Extract Part of a String

The SUBSTRING() (or SUBSTR()) function is used to extract a substring from a string, starting
from a specified position. It is especially useful when we need to extract a specific part of a
string, like extracting the domain from an email address.

Query:

SELECT SUBSTRING('Hello World', 1, 5) AS SubStringExample;

Output:

Hello

7. LEFT() and RIGHT(): Extract Substring from Left or Right

The LEFT() and RIGHT() functions allow you to extract a specified number of characters from the
left or right side of a string, respectively. It is used for truncating strings for display.

Query:

SELECT LEFT('Hello World', 5) AS LeftString;

SELECT RIGHT('Hello World', 5) AS RightString;

Output:

Hello
World

8. INSTR(): Find Position of Substring

The INSTR() function is used to find the position of the first occurrence of a substring within a
string. It returns the position (1-based index) of the substring. If the substring is not found, it
returns 0. This function is particularly useful for locating specific characters or substrings in text
data.

Query:

SELECT INSTR('Hello World', 'World') AS SubstringPosition;

Output:

9. TRIM(): Remove Leading and Trailing Spaces

The TRIM() function removes leading and trailing spaces (or other specified characters) from a
string. By default, it trims spaces but can also remove specific characters using TRIM(character
FROM string). This is helpful for cleaning text data, such as user inputs or database records.

Query:

SELECT TRIM(' ' FROM ' Hello World ') AS TrimmedString;

Output:

Hello World

10. REVERSE(): Reverse the String

The REVERSE() function reverses the characters in a string. It’s useful in situations where we
need to process data backward, such as for password validation or certain pattern matching.

Query:

SELECT REVERSE('Hello') AS ReversedString;

Output:

olleH

Other String Functions

In SQL, beyond the basic string functions, there are several advanced string functions that can
help you manipulate and process string data more effectively. These functions provide powerful
tools for working with text, whether you're cleaning data, formatting outputs, or comparing
strings. These are the some additional SQL Functions.

11. ASCII():Get the ASCII Value of a Character

The ASCII() function returns the ASCII value of a single character. This is helpful when we need
to find the numeric code corresponding to a character, often used in encoding and decoding
text.

Syntax:

SELECT ascii('t');

Output:

116

12. CONCAT_WS(): Concatenate Strings with a Separator

CONCAT_WS() stands for "Concatenate With Separator." It allows us to join multiple strings
with a specific separator between them. This is ideal when we need to merge columns like first
name and last name with a custom separator.

Syntax:

SELECT CONCAT_WS('_', 'geeks', 'for', 'geeks');

Output:

geeks_for_geeks

13. FIND_IN_SET(): Find Position of a Value in a Comma-Separated List

The FIND_IN_SET() function returns the position of a value within a comma-separated list. This
is especially useful for finding out where an element exists in a string of values (e.g., tags,
categories).

Syntax:

SELECT FIND_IN_SET('b', 'a, b, c, d, e, f');

Output:

14. FORMAT(): Format Numbers for Readable Output

The FORMAT() function is used to format a number as a string in a specific way, often with
commas for thousands or with a specific number of decimal places. It's handy when you need
to display numbers in a user-friendly format.

Syntax:

SELECT FORMAT(0.981 * 100, 'N2') + '%' AS PercentageOutput;

Output:
‘98.10%’

15. INSTR(): Find the Position of a Substring

The INSTR() function returns the position of the first occurrence of a substring within a string. If
the substring is not found, it returns 0. It's useful for finding where specific text appears in a
larger string.

Syntax:

SELECT INSTR('geeks for geeks', 'e');

Output:

16. LCASE(): Convert String to Lowercase

The LCASE() function converts all characters in a string to lowercase. It helps standardize text
data, especially when comparing strings in a case-insensitive way.

Syntax:

SELECT LCASE ("GeeksFor Geeks To Learn");

Output:

geeksforgeeks to learn

17. LOCATE(): Find the nth Position of a Substring

LOCATE() allows you to find the nth occurrence of a substring in a string. This is especially
useful when you need to locate a specific substring based on its position.

Syntax:

SELECT LOCATE('for', 'geeksforgeeks', 1);

Output:

18. LPAD(): Pad the Left Side of a String

LPAD() is used to pad a string to a certain length by adding characters to the left side of the
original string. It's useful when you need to format data to a fixed length.

Syntax:

SELECT LPAD('geeks', 8, '0');

Output:

000geeks

19. MID(): Extract a Substring from the Middle

MID() extracts a substring starting from a given position in a string and for a specified length. It's
useful when you want to extract a specific portion of a string.
Syntax:

SELECT Mid ("geeksforgeeks", 6, 2);

Output:

for

20. POSITION(): Find the Position of a Character in a String

The POSITION() function finds the position of the first occurrence of a specified character in a
string.

Syntax:

SELECT POSITION('e' IN 'geeksforgeeks');

Output:

21. REPEAT(): Repeat a String Multiple Times

The REPEAT() function repeats a string a specified number of times. It's useful when you need to
duplicate a string or pattern for certain operations.

Syntax:

SELECT REPEAT('geeks', 2);

Output:

geeksgeeks

22. REPLACE(): Replace a Substring in a String

REPLACE() is used to replace all occurrences of a substring with another substring. It's useful
for replacing or cleaning up certain text in your data.

Syntax:

REPLACE('123geeks123', '123');

Output:

geeks

23. RPAD(): Pad the Right Side of a String

RPAD() pads the right side of a string with specified characters to a fixed length. This is often
used to format text or numbers to a desired size.

Syntax:

RPAD('geeks', 8, '0');

Output:

‘geeks000’
24. RTRIM(): Remove Trailing Characters

RTRIM() removes trailing characters from the right side of a string. By default, it removes spaces,
but you can specify other characters as well.

Syntax:

RTRIM('geeksxyxzyyy', 'xyz');

Output:

‘geeks’

25. SPACE(): Generate a String of Spaces

The SPACE() function generates a string consisting of a specified number of spaces. This is
useful when you need to format output or create padding in your queries.

Syntax:

SELECT SPACE(7);

Output:

‘ ‘

26. STRCMP(): Compare Two Strings

STRCMP() compares two strings and returns an integer value based on their lexicographical
comparison. This is useful for sorting or checking equality between two strings.
STRCMP(string1, string2) returns:

• 0 if both strings are equal.

• A negative value if string1 is less than string2.

• A positive value if string1 is greater than string2.

Syntax

SELECT STRCMP('google.com', 'geeksforgeeks.com');

Output:

Summary of String Functions

Below is a table summarizing these functions, their purposes, and examples.

Function Description Example Query Output

Find ASCII value of a

ASCII() SELECT ASCII('A'); 65
character.
Function Description Example Query Output

Concatenate with a SELECT CONCAT_WS('_', 'A',

CONCAT_WS() A_B
delimiter. 'B');

SELECT FIND_IN_SET('b',
FIND_IN_SET() Find position in a set. 2
'a,b,c');

SELECT LOCATE('e',
LOCATE() Find nth occurrence. 2
'geeksforgeeks', 1);

LPAD() Pad string from the left. SELECT LPAD('geeks', 8, '0'); 000geeks

SELECT POSITION('e' IN
POSITION() Find character position. 2
'geeks');

REPEAT() Repeat a string. SELECT REPEAT('SQL', 3); SQLSQLSQL

Remove trailing SELECT RTRIM('SQLXYZ',

RTRIM() SQL
characters. 'XYZ');

Conclusion

SQL String Functions are powerful tools for manipulating and analyzing string data in
databases. Whether we need to concatenate, extract, compare, or modify strings, these
functions provide the flexibility to handle a wide variety of string-related tasks. Understanding
and applying these functions can make our SQL queries more efficient and help us manipulate
data exactly as we need.

SQL NOT NULL Constraint

In SQL, constraints are used to enforce rules on data, ensuring the accuracy, consistency, and
integrity of the data stored in a database. One of the most commonly used constraints is
the NOT NULL constraint, which ensures that a column cannot have NULL values.

This is important for maintaining data integrity, especially when specific data entries are
mandatory. In this article, we will explore the SQL NOT NULL constraint in detail, its syntax, and
how it can be applied to your tables.

What is the SQL NOT NULL Constraint?

The NOT NULL constraint is used to enforce that a column in a table must always contain a
value; it cannot contain a NULL value. By default, columns in SQL can hold NULL values,
meaning they can have no data. However, for certain columns—such as IDs, names, or any
required fields—you may want to enforce the rule that no NULL values can be inserted.

This constraint is similar to a primary key constraint in that both prevent NULL values. However,
they are different in their purpose and application. A primary key uniquely identifies each record
in a table, while the NOT NULL constraint simply ensures that a column cannot have empty or
undefined values.

Key Points:

• NOT NULL is used to enforce mandatory fields.

• It prevents NULL values from being inserted or updated.

• It is applicable at the column level.

• It can be used during table creation or modification (with the ALTER command).

Syntax:

CREATE TABLE table_Name

column1 data_type(size) NOT NULL,

column2 data_type(size) NOT NULL,

....

);

SQL NOT NULL on CREATE a Table

In SQL, we can add NOT NULL constraints while creating a table.

For example, the "EMPID" will not accept NULL values when the EMPLOYEES table is created
because NOT NULL constraints are used with these columns.

Query:

CREATE TABLE Emp(

EmpID INT NOT NULL PRIMARY KEY,

Name VARCHAR (50),

Country VARCHAR(50),

Age int(2),

Salary int(10));

Output:
SQL NOT NULL on ALTER Table

We can also add a NOT NULL constraint in the existing table using the ALTER statement. For
example, if the EMPLOYEES table has already been created then add NOT NULL constraints to
the "Name" column using ALTER statements in SQL as follows:

Query:

ALTER TABLE Emp modify Name Varchar(50) NOT NULL;

This command ensures that the Name column will no longer accept NULL values, enforcing a
requirement for all employee records to have a valid name.

Advantages of Using the NOT NULL Constraint

• Prevents Data Gaps: With NOT NULL, you avoid incomplete or missing data, which is
crucial for accurate data analysis.

• Enforces Business Logic: Often, business rules require certain fields to be mandatory.
For example, an employee record must have an employee ID, name, and department.

• Improves Data Integrity: By preventing null entries, you ensure that critical data is
always present, improving overall database reliability.

Conclusion

The SQL NOT NULL constraint is a powerful tool that ensures the integrity and completeness of
your database by preventing NULL values in critical columns. Whether you’re creating a new
table or modifying an existing one, applying the NOT NULL constraint guarantees that important
data is always present, which is essential for accurate reporting, analysis, and consistency
across your database. Always consider which fields are required before inserting records into
your tables to enforce data integrity with this constraint.

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint in SQL is one of the most important constraints used to
ensure data integrity in a database table. A primary key uniquely identifies each record in a
table, preventing duplicate or NULL values in the specified column(s). Understanding how to
properly implement and use the primary key constraint is crucial for managing relational data
effectively.

PRIMARY KEY in SQL

PRIMARY KEY in SQL is a column (or group of columns) that uniquely identifies the records in
that table. A primary key must contain unique values and can not have any NULL value.

There can only be one primary key in a table, but that primary key can consist of one or more
columns. When there are two or more columns in the primary key it is called a composite key.

A primary key automatically has a UNIQUE constraint defined on it, and it ensures that there
are no duplicate or NULL values in that column.

SQL PRIMARY KEY Properties

1. No duplicate values are allowed, i.e. The column assigned as the primary key should
have UNIQUE values only.

2. NO NULL values are present in the Primary key column. Hence there is a Mandatory
value in the column having the Primary key.

3. Only one primary key per table exists although the Primary key may have multiple
columns.

4. No new row can be inserted with the already existing primary key.

5. Primary keys can be classified into two categories Simple primary key that consists of
one column and composite primary key that consists of Multiple column.

6. Defined in CREATE TABLE or ALTER TABLE statement.

Syntax

There are two syntaxes to create/add primary key to a table:

• Using CREATE TABLE Statement

• Using ALTER TABLE Statement

SQL PRIMARY KEY with CREATE TABLE

SQL primary key syntax with CREATE TABLE statement is:

CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
...,
CONSTRAINT pk_constraint_name PRIMARY KEY (column1, column2, ...)
);

SQL PRIMARY KEY with ALTER TABLE

SQL primary key syntax with ALTER TABLE statement is

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

SQL PRIMARY KEY Examples

Let's look at some examples of the PRIMARY KEY Constraint in SQL, and understand it's
working.
Create PRIMARY KEY in SQL Example

In this example, we will create primary key in a new table using CREATE TABLE statement.

Query

CREATE TABLE Persons (

PersonID int NOT NULL PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int

);

Verify SQL Primary key creation

To verify if the primary key has been successfully created, we will try adding duplicate values in
primary key column, and SQL should return an error.

Query

INSERT INTO Persons VALUES

(1,"Thakur", "Aditya", 22),

(1, "Kumar", "Shubham", 21);

Output

Error: UNIQUE constraint failed: Persons.PersonID

Add PRIMARY KEY to a Table Example

In this example, we will add primary key to a already existing table using ALTER TABLE
command.

Let's consider previous table, and create it without primary key this time.

CREATE TABLE Persons (

PersonID int,

LastName varchar(255) NOT NULL,

FirstName varchar(255), Age int);

This query will add primary key to 'Persons' table

ALTER TABLE Persons

ADD CONSTRAINT PK_Person PRIMARY KEY (PersonID);

Important Points About SQL PRIMARY KEY

• A primary key is a column or a set of columns in a table that uniquely identifies each row.

• It ensures data integrity by preventing duplicate records and null values.

 • A primary key can be defined on a single column (simple primary key) or multiple
columns (composite primary key).

• Creating a primary key automatically creates a unique index on the key column(s),
improving query performance.

• Establishing relationships between tables using SQL primary key and foreign key
improve database design, reduce data redundancy, and improve data consistency.

Benefits of Using Primary Keys

• Data Integrity: The primary key enforces data integrity by ensuring each record is
unique.

• Efficient Querying: Since a primary key automatically creates an index, querying for
records by the primary key is faster.

• Referential Integrity: Primary keys are used to establish relationships between tables
(via foreign keys), ensuring consistency across related data.

Common Issues and Best Practices

• Avoid NULL values: Always ensure that the columns involved in the primary key do not
accept NULL values.

• Choose meaningful primary keys: If possible, choose a primary key that naturally fits
the data and serves as a meaningful identifier, like an ID field.

• Composite Keys: Be cautious when using composite keys. While they are useful in
some scenarios, they can make queries more complex. If possible, use a simple key or
generate an artificial primary key (like an ID).

• Changing Primary Keys: Once a primary key is established, changing it can be difficult
because of the interdependencies with other tables (foreign key constraints). Always
plan ahead when designing your database schema.

Conclusion

The PRIMARY KEY constraint is a fundamental concept in relational databases that ensures
each record in a table is unique and identifiable. By using the primary key effectively, you can
maintain data integrity, improve query performance, and establish meaningful relationships
between tables. Whether you are working with simple or composite keys, it is important to
understand the properties and best practices to ensure a well-designed and efficient database
structure.

SQL SUM() Function

The SUM() function in SQL is one of the most commonly used aggregate functions. It allows us
to calculate the total sum of a numeric column, making it essential for reporting and data
analysis tasks. Whether we're working with sales data, financial figures, or any other numeric
information, the SUM() function can help us quickly compute the sum of values based on
specific conditions.
In this article, we will explain the SUM() function in detail, provide multiple examples, and
highlight its use in various SQL queries to enhance our understanding.

What is the SQL SUM() Function?

The SUM() function in SQL is used to calculate the total of a numeric column or expression.
This aggregate function sums the values in the specified column and returns a single result. It is
commonly used in combination with other SQL clauses like WHERE, GROUP BY,
and HAVING to refine the data set and calculate sums based on specific conditions.

Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Key Terms

• column_name: The numeric column whose values you want to sum.

• table_name: The name of the table from which to retrieve the data.

• condition: (Optional) A condition to filter the rows before performing the aggregation.

Examples of SQL SUM() Function

In this section, we will demonstrate the usage of the SUM() function with examples using a
sample table called Sales, which stores sales data such as the Product, Quantity, and Price.
This simple dataset will help us understand how the SUM() function works in SQL to calculate
totals, sums of distinct values, and more.

Sales Table

Example 1: Using SUM() with One Column

In this example, we will use the SUM() function to calculate the total value of a specific column,
such as total sales or total salary.

Query:

SELECT SUM(Salary) AS TotalSalary

FROM Employees;

Output
TotalSalary

450,000

Explanation:

This query calculates the sum of the Salary column in the Employees table. This output shows
the total salary paid to employees in the database.

Example 2: Using SUM() with an Expression

We can also use the SUM() function with an expression to calculate sums based on
some logic or mathematical operations.

Query:

SELECT SUM(Price * Quantity) AS TotalRevenue

FROM Sales;

Output

TotalRevenue

1,200,000

Explanation:

This query multiplies Price and Quantity for each record in the Sales table and then calculates
the sum of those values. This is useful for calculating the total revenue generated from sales.

Example 3: Using SUM() with GROUP BY

When we want to calculate the sum of values within groups, we can use the GROUP BY clause
along with SUM(). This is particularly useful for grouping data by categories such
as departments, products, or cities.

Query:

SELECT Department, SUM(Salary) AS DepartmentSalary

FROM Employees
GROUP BY Department;

Output

Department DepartmentSalary

HR 200,000
Department DepartmentSalary

Sales 300,000

IT 250,000

Explanation:

This query groups employees by their Department and then calculates the total salary for each
department.

Example 4: Using SUM() with DISTINCT

If we want to sum only the distinct values in a column, we can use the DISTINCT keyword with
the SUM() function.

Query:

SELECT SUM(DISTINCT Price) AS TotalDistinctPrice

FROM Products;

Output:

TotalDistinctPrice

500,000

Explanation:

This query sums only the unique values in the Price column of the Products table. Duplicate
values are excluded from the sum.

Example 5: Using SUM() with HAVING

The HAVING clause can be used in combination with GROUP BY to filter groups based on the
result of the SUM() function. This allows you to apply conditions to the grouped data after the
aggregation.

Query:

SELECT Department, SUM(Salary) AS DepartmentSalary

FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 200,000;

Output
Department DepartmentSalary

Sales 300,000

IT 250,000

Explanation:

This query calculates the total salary per department and then filters the result to include only
those departments where the total salary is greater than 200,000.

Best Practices for Using the SQL SUM() Function

• Use with Indexes: When summing a large dataset, it’s important to have indexes on the
columns you’re filtering by, such as dates or categories. This will improve the
performance of your query.

• Use GROUP BY to Categorize Data: The SUM() function works perfectly with GROUP
BY. It helps you summarize data efficiently by different categories like departments or
regions.

• Avoid Summing Non-Numeric Values: Ensure that the column you are summing
contains only numeric values. Summing non-numeric values can result in errors.

• Consider Using Aliases: Always use aliases for SUM() results for better readability and
clarity in your output.

Conclusion

The SQL SUM() function is a powerful tool for aggregating numeric data. Whether we need to
calculate the total salary, revenue, or count items, the SUM() function simplifies these tasks
and helps us derive valuable insights from our database. By using it with different clauses
like DISTINCT, GROUP BY, and HAVING, we can tailor our queries to specific conditions,
making our analysis more efficient. The SUM() function is especially useful for generating
summary reports and analyzing financial, sales, or inventory data.

SQL MAX() Function

The MAX() function in SQL is a powerful aggregate function used to retrieve

the maximum (highest) value from a specified column in a table. It is commonly employed for
analyzing data to identify the largest numeric value, the latest date, or other maximum values
in various datasets. The MAX() function automatically excludes NULL values from its
computation, ensuring accuracy.

In this article, we will cover the syntax, use cases, sample tables, and practical examples with
detailed explanations of how to use the MAX() function effectively. Whether we are a beginner
or an advanced SQL user, this guide will help us understand and apply
the MAX() function confidently.
Syntax:

SELECT MAX(column_name)
FROM table_name
[WHERE condition];

Key Terms:

• column_name: The column for which you want to find the maximum value.

• table_name: The table containing the data.

• condition (optional): Filters rows before calculating the maximum value.

Examples of SQL MAX() Function

In this section, we will demonstrate the usage of the MAX() function with examples using a two
sample tables. Product Prices table contains product IDs and their corresponding prices.
The Sales table contains sales data with sale IDs, product IDs, sale dates, and amounts.

Product Prices Table

Product Prices Table

Sales Table

Sales Table

Example 1: Find the Maximum Price in a Table

To find the highest price in the product_prices table. This query is simple yet effective for
identifying the most expensive product in the dataset. It excludes NULL values, ensuring only
valid prices are considered.

Query:

SELECT MAX(price) AS [Highest Price]

FROM product_prices;
Output

Highest Price

199.99

Explanation: The MAX(price) function evaluates all the values in the price column and returns
the largest value, $199.99.

Example 2: Find the Maximum Sale Amount

To determine the highest sale amount from the Sales table. This query is commonly used
in sales data analysis to find the most significant transaction in terms of value. It helps
businesses track their peak sales performances.

Query:

SELECT MAX(amount) AS [Highest Sale]

FROM Sales;

Output:

Highest Sale

1500.00

Explanation: The MAX(amount) function scans the amount column in the Sales table and
identifies the largest sale, which is $1500.00

Example 3: Use MAX() with a Condition

Find the maximum price for products under a specific category, e.g., Electronics: This query is
valuable for targeted analysis such as identifying the product with highest price within a
specific category.

Query:

SELECT MAX(price) AS [Highest Price in Electronics]

FROM product_prices
WHERE category = 'Electronics';

Output:

Highest Price in Electronics

149.95

Explanation: The WHERE clause filters rows to include only products in the 'Electronics'
category. The MAX(price) function then calculates the maximum price within this subset.
Example 4: Find the Latest Sale Date

To find the most recent sale date in the Sales table. This query is particularly useful for tracking
the last activity in a dataset, such as identifying the latest transaction or update.

Query:

SELECT MAX(sale_date) AS [Latest Sale Date]

FROM Sales;

Output:

Latest Sale Date

2023-02-01

Explanation: The MAX(sale_date) function evaluates all dates in the sale_date column and
returns the most recent one, 2023-02-01

Example 5: Using MAX() with GROUP BY

Find the highest sale amount for each product. This query is helpful for analyzing performance
metrics at the product level, allowing businesses to track the best-performing products.

Query:

SELECT product_id, MAX(amount) AS [Highest Sale]

FROM Sales
GROUP BY product_id;

product_id Highest Sale

1 500.00

2 1500.00

3 300.00

Explanation: The GROUP BY clause groups rows by product_id, and the MAX(amount) function
calculates the highest sale amount for each group.

Example 6: Using MAX() in Subqueries

Retrieve details of the product with the highest price. The main query retrieves the full record
associated with this maximum price, providing detailed information about the most expensive
product.

Query:
SELECT *
FROM product_prices
WHERE price = (SELECT MAX(price) FROM product_prices);

Output:

product_id price

4 199.99

Explanation: The subquery (SELECT MAX(price) FROM product_prices) determines the highest
price ($199.99). The main query fetches all records matching this price.

Conclusion

The MAX() function in SQL is a fundamental tool for data analysis, enabling users to find the
highest value in a column. It can be used independently or in combination with other SQL
clauses like WHERE, GROUP BY, and subqueries to handle complex data requirements. By
mastering the MAX() function, WE can efficiently analyze and summarize data in various
scenarios, such as identifying maximum prices, latest dates, or highest scores.

AVG() Function in SQL

SQL is an RDBMS system in which SQL functions become very essential to provide us with
primary data insights. One of the most important functions is called AVG() and is particularly
useful for the calculation of averages within datasets.

In this, we will learn about the AVG() function, and its syntax by understanding various examples
and their output with explanation and so on.

AVG() Function in SQL

The SQL function AVG() is designed for calculating the average value of any numeric column
within a certain data set. It does this by adding up all the values of the column and then dividing
the resulting number by the number of non-null values of the column.

Thus, the function is best suited to propose a typical value of a given data set which helps to
analyze the data-set features.

Syntax:

The syntax of the AVG() function is straightforward:

SELECT AVG(column_name)

FROM table_name;

Here, column_name represents the column from which you want to compute the average,
and table_name is the name of the table containing the data. Optionally, you can use
the WHERE clause to specify conditions for filtering the data before calculating the average.

Set Up an Environment
CREATE TABLE student_scores (
student_id INT,
subject VARCHAR(50),
score INT
);

INSERT INTO student_scores (student_id, subject, score) VALUES

(1, 'Math', 85),
(2, 'Science', 78),
(3, 'English', 92),
(4, 'Math', 90),
(5, 'Science', 82),
(6, 'English', 88),
(7, 'Math', 75),
(8, 'Science', 80),
(9, 'English', 85);

Output:

| student_id | subject | score |

|------------|------------|-------|
|1 | Math | 85 |
|2 | Science | 78 |
|3 | English | 92 |
|4 | Math | 90 |
|5 | Science | 82 |
|6 | English | 88 |
|7 | Math | 75 |
|8 | Science | 80 |
|9 | English | 85 |

Example 1: Calculating Average Score per Subject

SELECT subject, AVG(score) AS average_score

FROM student_scores
GROUP BY subject;

Output:

| subject | average_score |
|------------|---------------|
| Math | 83.3333 |
| Science | 80 |
| English | 88.3333 |

Explanation: In this example, we're using the AVG() function to compute the average score for
each subject. The GROUP BY clause is used to group the results by the subject column. The
output will display two columns: subject and average_score.

Example 2: Calculating Overall Average Score

SELECT AVG(score) AS overall_average_score
FROM student_scores;

Output:

| overall_average_score |
|-----------------------|
| 83.88888888888889 |

Explanation: Here, we're computing the average score across all subjects using the AVG()
function without any grouping. This will give us a single value representing the overall average
score of all students

Example 3: Calculating Average Score for a Specific Subject

SELECT AVG(score) AS average_science_score

FROM student_scores
WHERE subject = 'Science';

Output:

| average_science_score |
|-----------------------|
| 80 |

Explanation: In this example, we're filtering the data using the WHERE clause to focus only on
the "Science" subject. Then, we use the AVG() function to calculate the average score for that
specific subject

Considerations

While the AVG() function is a powerful tool for data analysis, there are some considerations to
keep in mind:

• Handling NULL Values: AVG () function by default ignore NULL values when calculating
the total. However, they have to be careful in how they interpret the results of the study,
especially when missing values are relevant to your study.

• Data Type Compatibility: Make sure the function of AVG() is applied to the column that
contains numbers as the values. The usage of it beyond numerical columns will result in
erroneous results hence.

• Precision and Rounding: Since rounding is used in different cases, you can either keep
the result of the AVG() function as it is, or you may round it to a specific number of
decimal places so that the result is clear and consistent

Conclusion

In SQL, the AVG() function is a good function for studying numerical data. It enables analysts as
well as data experts to carry out easy calculations and thereby give a clear understanding
regarding various vital factors in their datasets. Whether you are deciding on sales figures,
evaluating student performance or monitoring website statistics AVG() gives you the statistics
with facts on which you can depend when making important decisions.
SQL Subquery

A subquery in SQL is a query nested within another SQL query. It allows you to perform complex
filtering, aggregation, and data manipulation by using the result of one query inside another.
Subqueries are often found in the WHERE, HAVING, or FROM clauses and are supported in
SELECT, INSERT, UPDATE, and DELETE statements.

They are an essential tool when we need to perform operations like:

• Filtering: Getting specific records based on conditions derived from another query.

• Aggregating: Performing aggregate functions like SUM, COUNT, or AVG based on

subquery results.

• Updating: Dynamically updating records based on values from other tables.

• Deleting: Deleting records from one table using criteria based on another.

While there is no universal syntax for subqueries, they are commonly used
in SELECT statements as follows. This general syntax allows the outer query to use the results of
the inner subquery for filtering or other operations.

Syntax

SELECT column_name
FROM table_name
WHERE column_name expression operator
(SELECT column_name FROM table_name WHERE ...);

Key Characteristics of Subqueries

1. Nested Structure: A subquery is executed within the context of an outer query.

2. Parentheses: Subqueries must always be enclosed in parentheses ().

3. Comparison Operators: Subqueries can be used with operators like =, >, <, IN, NOT
IN, LIKE, etc.

4. Single-Row vs. Multi-Row Subqueries: Subqueries may return a single value (e.g., a
single row) or multiple values. Depending on the result, different SQL constructs may be
required.

Common SQL Clauses for Subqueries

Subqueries are frequently used in specific SQL clauses to achieve more complex results. Here
are the common clauses where subqueries are used:

• WHERE Clause: Filter rows based on subquery results. For example, you can filter
records based on values returned by a subquery.

• FROM Clause: Subqueries can be used in the FROM clause to treat the result of the
subquery as a derived table or temporary table that can be joined with other tables.

• HAVING Clause: Apply conditions on aggregated results.

Types of Subqueries
 1. Single-Row Subquery: Returns a single value (row). Useful with comparison operators
like =, >, <.

2. Multi-Row Subquery: Returns multiple values (rows). Useful with operators

like IN, ANY, ALL.

3. Correlated Subquery: Refers to columns from the outer query in the subquery. Unlike
regular subqueries, the subquery depends on the outer query for its values.

4. Non-Correlated Subquery: Does not refer to the outer query and can be executed
independently.

Examples of Using SQL Subqueries

These examples showcase how subqueries can be used for various operations
like selecting, updating, deleting, or inserting data, providing insights into
their syntax and functionality. Through these examples, we will understand the flexibility and
importance of subqueries in simplifying complex database tasks. Consider the following two
tables:

1. DATABASE TABLE

Database Table

2. STUDENT TABLE

Student Table

Example 1: Fetching Data Using Subquery in WHERE Clause

This example demonstrates how to use a subquery to retrieves roll numbers of students
in section 'A', and the outer query uses those roll numbers to fetch corresponding details
(name, location, and phone number) from the DATABASE table. This enables filtering based on
results from another table.
Query:

SELECT NAME, LOCATION, PHONE_NUMBER

FROM DATABASE
WHERE ROLL_NO IN (
SELECT ROLL_NO FROM STUDENT WHERE SECTION='A'
);

Output

NAME LOCATION PHONE_NUMBER

Ravi Salem 8989898989

Raj Coimbatore 8877665544

Explanation: The inner query fetches the roll numbers of students in section 'A'. The outer query
uses those roll numbers to filter records from the DATABASE table.

Example 2: Using Subquery with INSERT

In this example, a subquery is used to insert all records from the Student2 table into
the Student1 table. The SELECT statement inside the INSERT INTO statement fetches all the
data from Student2 and inserts it into Student1.

Student1 Table

NAME ROLL_NO LOCATION PHONE_NUMBER

Ram 101 chennai 9988773344

Raju 102 coimbatore 9090909090

Ravi 103 salem 8989898989

Student2 Table

NAME ROLL_NO LOCATION PHONE_NUMBER

Raj 111 chennai 8787878787

NAME ROLL_NO LOCATION PHONE_NUMBER

Sai 112 mumbai 6565656565

Sri 113 coimbatore 7878787878

Query:

INSERT INTO Student1

SELECT * FROM Student2;

Output

NAME ROLL_NO LOCATION PHONE_NUMBER

Ram 101 chennai 9988773344

Raju 102 coimbatore 9090909090

Ravi 103 salem 8989898989

Raj 111 chennai 8787878787

Sai 112 mumbai 6565656565

Sri 113 coimbatore 7878787878

Explanation: The SELECT statement inside the INSERT INTO query fetches all records
from Student2 and inserts them into Student1.

Example 3: Using Subquery with DELETE

Subqueries can be used in DELETE statements to remove rows based on data from another
table. In this example, the subquery gets roll numbers from Student1 where the location is
'Chennai'. The outer query then deletes matching rows from Student2.

Query:

DELETE FROM Student2

WHERE ROLL_NO IN (SELECT ROLL_NO
FROM Student1
WHERE LOCATION = 'chennai');

Output

NAME ROLL_NO LOCATION PHONE_NUMBER

Sai 112 mumbai 6565656565

Sri 113 coimbatore 7878787878

Explanation: The subquery retrieves roll numbers of students from Student1 who are located in
'Chennai'. The outer query deletes those records from Student2.

Example 4: Using Subquery with UPDATE

The subquery retrieves the locations of 'Raju' and 'Ravi' from Student1. The outer query then
updates the NAME in Student2 to 'Geeks' for all students whose LOCATION matches any of the
retrieved locations. This allows for updating data in Student2 based on conditions from
Student1.

Query:

UPDATE Student2
SET NAME='geeks'
WHERE LOCATION IN (SELECT LOCATION
FROM Student1
WHERE NAME IN ('Raju', 'Ravi'));

Output

NAME ROLL_NO LOCATION PHONE_NUMBER

Sai 112 mumbai 6565656565

geeks 113 coimbatore 7878787878

Explanation: The inner query fetches the locations of 'Raju' and 'Ravi' from Student1. The outer
query updates the name to 'Geeks' in Student2 where the location matches those of 'Raju' or
'Ravi'

Tips for Writing Efficient Subqueries

While subqueries are powerful, here are some best practices to ensure your queries are
efficient and maintainable:
 1. Avoid Nested Subqueries When Possible: Too many nested subqueries can slow down
performance. Consider using JOIN operations if applicable.

2. Use EXISTS Instead of IN for Better Performance: When dealing with large
datasets, EXISTS can sometimes be more efficient than IN, especially when the
subquery returns a large result set.

3. Use Aliases for Clarity: When using subqueries in the FROM clause, always use aliases
to make your query more readable and maintainable.

4. Test with Different Scenarios: Always test your subqueries in different environments
(e.g., development vs. production) to ensure they perform well

Window Functions in SQL

SQL window functions are essential for advanced data analysis and database management. It is
a type of function that allows us to perform calculations across a specific set of rows related to
the current row. These calculations happen within a defined window of data and they are
particularly useful for aggregates, rankings and cumulative totals without modifying the dataset.

The OVER clause is a key for defining this window. It partitions the data into different sets (using
the PARTITION BY clause) and orders them (using the ORDER BY clause). These windows enable
functions like SUM(), AVG(), ROW_NUMBER(), RANK() and DENSE_RANK() to be applied in an
organized manner.

Syntax

SELECT column_name1,
window_function(column_name2)
OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;

Key Terms

• window_function= any aggregate or ranking function

• column_name1= column to be selected

• column_name2= column on which window function is to be applied

• column_name3= column on whose basis partition of rows is to be done

• new_column= Name of new column

• table_name= Name of table

Types of Window Functions in SQL

SQL window functions can be categorized into two primary types: aggregate window
functions and ranking window functions. These two types serve different purposes but share a
common ability to perform calculations over a defined set of rows while retaining the original
data. The employee table contains details about employees, such as their name, age,
department and salary.
Employees Table

Employees Table

1. Aggregate Window Function

Aggregate window functions calculate aggregates over a window of rows while retaining
individual rows. Common aggregate functions include:

• SUM(): Sums values within a window.

• AVG(): Calculates the average value within a window.

• COUNT(): Counts the rows within a window.

• MAX(): Returns the maximum value in the window.

• MIN(): Returns the minimum value in the window.

Example: Using AVG() to Calculate the Average Salary within each department

SELECT Name, Age, Department, Salary,

AVG(Salary) OVER( PARTITION BY Department) AS Avg_Salary
FROM employee

Output

AVG() Function

Explanation:

• The AVG() function calculates the average salary for each department using
the PARTITION BY Department clause.
 • The average salary is repeated for all rows in the respective department.

2. Ranking Window Functions

These functions provide rankings of rows within a partition based on specific criteria. Common
ranking functions include:

• RANK(): Assigns ranks to rows, skipping ranks for duplicates.

• DENSE_RANK(): Assigns ranks to rows without skipping rank numbers for duplicates.

• ROW_NUMBER(): Assigns a unique number to each row in the result set.

RANK() Function

The RANK() function assigns ranks to rows within a partition, with the same rank given to rows
with identical values. If two rows share the same rank, the next rank is skipped.

Example: Using RANK() to Rank Employees by Salary

SELECT Name, Department, Salary,

RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_rank
FROM employee;

Output

RANK() Function

Explanation:

Rows with the same salary (e.g., Ramesh and Suresh) are assigned the same rank. The next rank
is skipped (e.g., rank 2) due to duplicate ranks.

DENSE_RANK() Function

It assigns rank to each row within partition. Just like rank function first row is assigned rank 1
and rows having same value have same rank. The difference between RANK() and
DENSE_RANK() is that in DENSE_RANK(), for the next rank after two same rank, consecutive
integer is used, no rank is skipped.

Example:

SELECT Name, Department, Salary,

DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS
emp_dense_rank
FROM employee;

Output

Name Department Salary emp_dense_rank

Ramesh Finance 50,000 1

Suresh Finance 50,000 1

Ram Finance 20,000 2

Deep Sales 30,000 1

Pradeep Sales 20,000 2

Explanation: The DENSE_RANK() function works similarly to RANK(), but it doesn't skip rank
numbers when there are ties. For example, if two employees have the same salary, both will
receive rank 1, and the next employee will receive rank 2.

ROW_NUMBER() Function

ROW_NUMBER() gives each row a unique number. It numbers rows from one to the total rows.
The rows are put into groups based on their values. Each group is called a partition. In each
partition, rows get numbers one after another. No two rows have the same number in a
partition.

Example: Using ROW_NUMBER() for Unique Row Numbers

SELECT Name, Department, Salary,

ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_row_no
FROM employee;

Output

Name Department Salary emp_row_no

Ramesh Finance 50,000 1

Suresh Finance 50,000 2

Name Department Salary emp_row_no

Ram Finance 20,000 3

Deep Sales 30,000 1

Pradeep Sales 20,000 2

Explanation: ROW_NUMBER() assigns a unique number to each employee based on their

salary within the department. No two rows will have the same row number.

Practical Use Cases for Window Functions

Window functions are extremely versatile and can be used in a variety of practical scenarios.
Below are some examples of how these functions can be applied in real-world data analysis.

Example 1: Calculating Running Totals

We want to calculate a running total of sales for each day without resetting the total, every time
a new day starts.

SELECT Date, Sales,

SUM(Sales) OVER(ORDER BY Date) AS Running_Total
FROM sales_data;

Explanation: This query calculates the cumulative total of sales for each day, ordered by date.

Example 2: Finding Top N Values in Each Category

We need to retrieve the top 3 employees in each department based on their salary.

WITH RankedEmployees AS (
SELECT Name, Department, Salary,
RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_rank
FROM employee
)
SELECT Name, Department, Salary
FROM RankedEmployees
WHERE emp_rank <= 3;

Explanation: This query retrieves the top 3 employees per department based on salary. It
uses RANK() to rank employees within each department and filters for the top 3

Troubleshooting Common Issues with Window Functions

While SQL window functions are incredibly powerful, there are some common pitfalls and
challenges that users may encounter:

• Partitioning Error: Ensure that the PARTITION BY clause is used correctly. If no partition
is defined, the entire result set is treated as a single window.
 • ORDER BY Within the Window: The ORDER BY clause within the window function
determines the order of calculations. Always verify that it aligns with the logic of your
calculation.

• Performance Considerations: Window functions can be computationally expensive,

especially on large datasets. Always ensure that your window functions are optimized
and, if necessary, combined with appropriate indexes.

Conclusion

SQL window functions are a crucial feature for advanced data analysis and provide flexibility
when working with partitioned data. By mastering the OVER, PARTITION BY and ORDER
BY clause, we can perform complex calculations like aggregate calculations, ranking and
cumulative totals while preserving the row-level data. The combination of window functions
with ORDER BY and PARTITION BY provides a flexible approach for data manipulation across
different types of datasets.

SQL Stored Procedures

Stored procedures are precompiled SQL statements that are stored in the database and can be
executed as a single unit. SQL Stored Procedures are a powerful feature in database
management systems (DBMS) that allow developers to encapsulate SQL code and business
logic. When executed, they can accept input parameters and return output, acting as a reusable
unit of work that can be invoked multiple times by users, applications, or other procedures.

What is a SQL Stored Procedure?

A SQL Stored Procedure is a collection of SQL statements bundled together to perform a

specific task. These procedures are stored in the database and can be called upon by users,
applications, or other procedures. Stored procedures are essential for automating database
tasks, improving efficiency, and reducing redundancy. By encapsulating logic within stored
procedures, developers can streamline their workflow and enforce consistent business
rules across multiple applications and systems.

Syntax:

CREATE PROCEDURE procedure_name

(parameter1 data_type, parameter2 data_type, ...)
AS
BEGIN
-- SQL statements to be executed
END

Key Terms

• CREATE PROCEDURE: This keyword creates the stored procedure with the given name.

• @parameter1, @parameter2: These are input parameters that allow you to pass values
into the stored procedure.

• BEGIN...END: These keywords define the block of SQL statements that make up the
procedure body.

Types of SQL Stored Procedures

SQL stored procedures are categorized into different types based on their use case and
functionality. Understanding these categories can help developers choose the right type of
procedure for specific scenario

1. System Stored Procedures

These are predefined stored procedures provided by the SQL Server for performing
administrative tasks such as database management, troubleshooting, or system configuration.
Examples include:

• sp_help for viewing database object information

• sp_rename for renaming database objects.

2. User-Defined Stored Procedures (UDPs)

These are custom stored procedures created by the user to perform specific operations. User-
defined stored procedures can be tailored to a business's needs, such as calculating totals,
processing orders, or generating reports. For example, creating a procedure that calculates the
total sales for a particular product category.

3. Extended Stored Procedures

These allow for the execution of external functions, which might be implemented in other
languages such as C or C++. Extended procedures provide a bridge between SQL Server and
external applications or tools, such as integrating third-party tools into SQL Server.

4. CLR Stored Procedures

These are stored procedures written in .NET languages (like C#) and executed within SQL Server.
CLR stored procedures are useful when advanced functionality is needed that isn't easily
achievable with T-SQL alone, such as complex string manipulation or working with external
APIs.

Why Use SQL Stored Procedures?

There are several key reasons why SQL Stored Procedures are widely used in database
management:

1. Performance Optimization: Since stored procedures are precompiled, they execute

faster than running ad-hoc SQL queries. The database engine can reuse the execution
plan, eliminating the need for repeated query parsing and optimization.

2. Security and Data Access Control: By using stored procedures, developers can restrict
direct access to sensitive data. Users can execute procedures without accessing the
underlying tables, helping to protect critical information.

3. Code Reusability and Maintainability: SQL stored procedures can be reused in

multiple applications or different parts of an application. This reduces the need to
rewrite complex queries repeatedly.

4. Reduced Network Traffic: Instead of sending multiple individual queries to the

database server, stored procedures allow you to execute multiple operations in one go,
reducing network load.
 5. Maintainability: Stored procedures simplify code maintenance. Changes made to the
procedure are automatically reflected wherever the procedure is used, making it easier
to manage complex logic.

Example of Creating a Stored Procedure

In this example, we create a stored procedure called GetCustomersByCountry, which accepts

a Country parameter and returns the CustomerName and ContactName for all customers
from that country. The procedure is designed to query the Customers table, which contains
customer information, including their names, contact details, and country.

Customers Table

By passing a country as a parameter, the stored procedure dynamically fetches the relevant
customer details from the table

Query:

-- Create a stored procedure named "GetCustomersByCountry"

CREATE PROCEDURE GetCustomersByCountry
@Country VARCHAR(50)
AS
BEGIN
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = @Country;
END;

-- Execute the stored procedure with parameter "Sri lanka"

EXEC GetCustomersByCountry @Country = 'Sri lanka';

Output

CustomerName Contact Name

Naveen Tulasi

Note: We will need to make sure that the user account has the necessary privileges to create
a database. We can try logging in as a different user with administrative privileges or contact
the database administrator to grant the necessary privileges to our user account. If we are using
a cloud-based database service, make sure that we have correctly configured the user account
and its permissions.

Advantages of Using SQL Stored Procedures

1. Improved Performance: Stored procedures are precompiled, meaning they execute

faster than running multiple individual queries.

2. Enhanced Security: Users can be granted permission to execute stored procedures

without directly accessing the underlying tables.

3. Code Reusability: Stored procedures allow for reusability, making it easier to maintain
and update code.

4. Reduced Network Traffic: By bundling multiple SQL statements into one call, stored
procedures reduce network load and improve application performance.

5. Better Error Handling: SQL stored procedures provide a structured way to manage
errors using TRY...CATCH blocks.

Real-World Use Cases for SQL Stored Procedures

1. Order Processing System In an e-commerce application, a stored procedure can

automate the process of inserting new orders, updating stock levels, and generating
invoices.

2. Employee Management System A stored procedure can be used to calculate salaries

for employees, deduct taxes, and generate monthly salary slips.

3. Data Validation Use stored procedures to validate data before it’s inserted into the
database. For example, checking if an email address already exists before adding a new
user.

4. Audit Logs Create a stored procedure that automatically logs changes to sensitive data,
such as changes to user roles or permissions, for security and auditing purposes.

Best Practices for Writing SQL Stored Procedures

1. Keep Procedures Simple and Modular

Avoid making stored procedures too complex. Break up larger tasks into smaller, more
manageable procedures that can be combined as needed. This improves readability and
maintainability.

2. Use Proper Error Handling

Always use TRY...CATCH blocks to handle exceptions gracefully. This ensures that errors are
caught and logged, and the procedure can handle unexpected scenarios without crashing.

3. Limit the Use of Cursors

While cursors can be useful, they are often less efficient than set-based operations. Use
cursors only when necessary, and consider alternatives like WHILE loops or CTEs (Common
Table Expressions).

4. Avoid Hardcoding Values

Instead of hardcoding values directly in stored procedures, use parameters to make procedures
more flexible and reusable across different contexts.

5. Optimize for Performance

Consider indexing, query optimization, and avoiding unnecessary joins within stored
procedures. Well-optimized queries in stored procedures ensure that performance does not
degrade as the database grows.

Conclusion

SQL stored procedures are an essential part of SQL development, offering benefits such as
improved performance, security, and maintainability. By encapsulating SQL queries into
reusable units, stored procedures simplify database management, enhance efficiency, and
ensure consistent business logic execution. By using stored procedures, we can automate
tasks, minimize the risk of SQL injection, and ensure consistent execution of complex SQL
logic. Stored procedures are integral to modern database management and an important
component in building scalable, efficient, and secure database systems.

SQL Triggers

A trigger is a stored procedure in adatabase that automatically invokes whenever a special

event in the database occurs. By using SQL triggers, developers can automate tasks, ensure
data consistency, and keep accurate records of database activities. For example, a trigger can
be invoked when a row is inserted into a specified table or when specific table columns are
updated.

In simple words, a trigger is a collection of SQL statements with particular names that are
stored in system memory. It belongs to a specific class of stored procedures that are
automatically invoked in response to database server events. Every trigger has a table attached
to it.

Syntax

create trigger [trigger_name]

[before | after]
{insert | update | delete}
on [table_name]
FOR EACH ROW
BEGIN
END;

Key Terms

• trigger_name: The name of the trigger to be created

• BEFORE | AFTER: Specifies whether the trigger is fired before or after the triggering
event (INSERT, UPDATE, DELETE).

• {INSERT | UPDATE | DELETE}: Specifies the operation that will activate the trigger.

• table_name: The name of the table the trigger is associated with.

 • FOR EACH ROW: Indicates that the trigger is row-level, meaning it executes once for
each affected row.

• trigger_body: The SQL statements to be executed when the trigger is fired.

Why Should You Use SQL Triggers?

Using SQL triggers brings several benefits to the table:

• Automation: Triggers handle repetitive tasks, saving our time and effort.

• Consistency & Data Integrity: Automatically enforcing rules ensures that our data
remains clean and accurate.

• Business Rules Enforcement: Triggers can help ensure that changes to our database
follow your business logic.

• Audit Trails: Track changes automatically, making it easier to monitor and record data
updates.

Now that you know why triggers are important, let’s look at how to create and use them
effectively.

Types of SQL Triggers

Triggers can be categorized into different types based on the action they are associated with:

1. DDL Triggers

The Data Definition Language (DDL) command events such as Create_table, Create_view,
drop_table, Drop_view, and Alter_table cause the DDL triggers to be activated. They allow us to
track changes in the structure of the database. The trigger will prevent any table creation,
alteration, or deletion in the database.

Example: Prevent Table Deletions

CREATE TRIGGER prevent_table_creation

ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'you can not create, drop and alter table in this database';
ROLLBACK;
END;

Output:

2. DML Triggers
DML triggers fire when we manipulate data with commands like INSERT, UPDATE, or DELETE.
These triggers are perfect for scenarios where we need to validate data before it is inserted, log
changes to a table, or cascade updates across related tables.

Example: Prevent Unauthorized Updates Let’s say you want to prevent users from updating the
data in a sensitive students table. We can set up a trigger to handle that:

CREATE TRIGGER prevent_update

ON students
FOR UPDATE
AS
BEGIN
PRINT 'You can not insert, update and delete this table i';
ROLLBACK;
END;

Output

DML Trigger

3. Logon Triggers

These triggers are fired in response to logon events. Logon triggers are useful for monitoring user
sessions or restricting user access to the database. As a result, the PRINT statement messages
and any errors generated by the trigger will all be visible in the SQL Server error log.

Authentication errors prevent logon triggers from being used. These triggers can be used to track
login activity or set a limit on the number of sessions that a given login can have in order to audit
and manage server sessions.

Example: Track User Logins

CREATE TRIGGER track_logon

ON LOGON
AS
BEGIN
PRINT 'A new user has logged in.';
END;

Real-World Use Cases of SQL Triggers

1. Automatically Updating Related Tables (DML Trigger Example)

Triggers can automatically perform tasks, like updating related tables when data changes.
Imagine we have a database for students, where the student_gradestable holds individual
subject grades. If the grade of a student is updated, we may also need to update
the total_scorestable.
CREATE TRIGGER update_student_score
AFTER UPDATE ON student_grades
FOR EACH ROW
BEGIN
UPDATE total_scores
SET score = score + :new.grade
WHERE student_id = :new.student_id;
END;

This ensures that every time a student's grade is updated, the total score in the total_scores
table is automatically recalculated.

2. Data Validation (Before Insert Trigger Example)

Triggers can be used to validate data before it is inserted into a table, ensuring that the data
follows specific business rules. For instance, we may want to ensure that the grades being
inserted are within a valid range (say 0 to 100).

CREATE TRIGGER validate_grade

BEFORE INSERT ON student_grades
FOR EACH ROW
BEGIN
IF :new.grade < 0 OR :new.grade > 100 THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid grade value.');
END IF;
END;

The trigger checks if the inserted grade is valid. If not, it throws an error and prevents the
insertion.

Viewing and Managing Triggers in SQL

If we are working with many tables across multiple databases, we can use a simple query to list
all available triggers in our SQL Server instance. This is helpful for tracking and managing
triggers, especially when dealing with tables that have similar names across databases.

SELECT name, is_instead_of_trigger

FROM sys.triggers
WHERE type = 'TR';

Key Terms

• name: The name of the trigger.

• is_instead_of_trigger: Whether the trigger is an INSTEAD OF trigger.

• type = 'TR': This filters the results to show only triggers.

The SQL Server Management Studio makes it very simple to display or list all triggers that are
available for any given table. The following steps will help us accomplish this: Go to
the Databases menu, select the desired database, and then expand it.

• Select the Tables menu and expand it.

 • Select any specific table and expand it.

• We will get various options here. When we choose the Triggers option, it displays all the
triggers available in this table.

BEFORE and AFTER Triggers

SQL triggers can be specified to run BEFORE or AFTER the triggering event.

• BEFORE Triggers: These run before the action (INSERT, UPDATE, DELETE) is executed.
They’re great for data validation or modifying values before they are committed to the
database.

• AFTER Triggers: Execute after the SQL statement completes. Useful for logging or
cascading updates to other tables.

Example: Using BEFORE Trigger for Calculations

Given Student Report Database, in which student marks assessment is recorded. In such a
schema, create a trigger so that the total and percentage of specified marks are automatically
inserted whenever a record is inserted. Here, a trigger will invoke before the record is inserted
so BEFORE Tag can be used.

Query

mysql>>desc Student;

Student Datatype

Below SQL statement will create a trigger in the student database in which whenever subjects
marks are entered, before inserting this data into the database, the trigger will compute
those two values and insert them with the entered values. In this way, triggers can
be created and executed in the databases.
Stud_marks

Output:

Output

Advantages of Triggers

• Data Integrity: Triggers help enforce consistency and business rules, ensuring that data
follows the correct format.

• Automation: Triggers eliminate the need for manual intervention by automatically

performing tasks such as updating, inserting, or deleting records when certain
conditions are met.

• Audit Trail: Triggers can track changes in a database, providing an audit trail
of INSERT, UPDATE, and DELETE operations.

• Performance: By automating repetitive tasks, triggers improve SQL query

performance and reduce manual workload.

SQL Performance Tuning

SQL Performance Tuning

Last Updated : 23 Jul, 2025

SQL performance tuning is an essential aspect of database management that helps improve
the efficiency of SQL queries and ensures that database systems run smoothly. Properly tuned
queries execute faster, reducing response times and minimizing the load on the server

In this article, we'll discuss various techniques, tools, and strategies used for SQL
performance tuning. By mastering these concepts, we can optimize our SQL queries and
achieve superior performance in our database.
What is SQL Performance Tuning?

SQL performance tuning is the process of optimizing SQL queries to improve

the speed and efficiency of database operations. It involves various techniques to optimize
the execution of queries, manage system resources more effectively, and ensure that the
database responds quickly to user requests.

Optimizing SQL performance is crucial because poorly optimized queries can severely affect
the speed of the database, increase CPU usage, and lead to system downtime. By improving
query execution times and resource utilization, performance tuning enhances the overall
performance of the SQL database.

Factors Affecting SQL Speed

Some of the major factors that influence the computation and execution time in SQL are:

• Table Size: Larger tables with millions of rows can slow down query performance if the
query hits a large number of rows.

• Joins: The use of complex joins, especially when joining multiple tables, can
significantly affect query execution time.

• Aggregations: Queries that aggregate large datasets require more processing time and
resources.

• Concurrency: Simultaneous queries from multiple users can overwhelm the database,
leading to slow performance.

• Indexes: Proper indexing speeds up data retrieval but, when misused, can lead to
inefficiencies.

Ways to Find Slow SQL Queries in SQL Server

1. Creating an Execution Plan

SQL Server Management Studio allows users to view the execution plan, which details how SQL
Server processes a query. This plan helps identify inefficiencies like missing indexes or
unnecessary table scans. To create an execution plan:

• Start by selecting "Database Engine Query" from the toolbar of SQL Server
Management Studio.

• Enter the query after that, and then select "Include Actual Execution Plan" from the
Query option.

• It's time to run your query at this point. You can do that by pressing F5 or the "Execute"
toolbar button.

• The execution plan will then be shown in the results pane, under the "Execution Pane"
tab, in SQL Server Management Studio.

2. Monitor Resource Usage

SQL Server's performance is closely tied to resource usage (CPU, memory, and disk).
Monitoring tools like Windows Performance Monitor can track these metrics and
highlight performance bottlenecks. We may view SQL Server objects, performance counters,
and other object activity with it. Simultaneously watch Windows and SQL Server counters with
System Monitor to see if there is any correlation between the two services' performance.

3. Use SQL DMVs to Find Slow Queries

The abundance of dynamic management views (DMVs) that SQL Server includes is one of its
best features, helping identify slow-running queries, execution plans, and resource
consumption. DMVs such as sys.dm_exec_query_stats can be used to track query
performance.

SQL Query Optimization Techniques

Inefficient queries or those containing errors can consume excessive resources in the
production database, leading to slower performance or even disconnecting other users. It's
important to optimize queries to minimize their impact on overall database performance.

In this section, we’ll discuss several effective SQL performance tuning techniques, along with
practical examples, that can help optimize queries and improve database efficiency. These
methods focus on reducing resource consumption and improving execution speed, ensuring
a smoother and faster user experience.

1. SELECT fields instead of using SELECT *

Using SELECT * retrieves all columns from a table, but if you only need specific columns, this
can unnecessarily increase processing time. Instead, specify the columns needed. By using
the SELECT statement, one may direct the database to only query the data we actually need to
suit your business needs. For example:

Inefficient:

Select * from GeeksTable;

Efficient:

SELECT FirstName, LastName,

Address, City, State, Zip FROM GeeksTable;

2. Avoid SELECT DISTINCT

It is practical to get rid of duplicates from a query by using SELECT DISTINCT. To get separate
results, SELECT DISTINCT GROUPs for every field in the query. However, a lot of computing
power is needed to achieve this goal. Instead of using DISTINCT, refine your query to return
unique results naturally by adjusting the selection criteria.

Inefficient:

SELECT DISTINCT FirstName, LastName,

State FROM GeeksTable;

Efficient:

SELECT FirstName, LastName,

State FROM GeeksTable WHERE State IS NOT NULL;

3. Use INNER JOIN Instead of WHERE for Joins

Joining tables using the WHERE clause can lead to inefficiencies and unnecessary
computations. It's more efficient to use INNER JOIN or LEFT JOIN for combining tables.

Inefficient:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate

FROM GFG1, GFG2
WHERE GFG1.CustomerID = GFG2.CustomerID

Efficient:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate

FROM GFG1
INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID

4. Use WHERE Instead of HAVING

The HAVING clause is used after aggregation and can be less efficient. When possible,
use WHERE to filter results before aggregation to speed up the query. A WHERE statement is
more effective if the goal is to filter a query based on conditions. Assuming 500 sales were made
in 2019, for instance, query to find how many sales were made per client that year.

Inefficient:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate

FROM GFG1 INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID
GROUP BY GFG1.CustomerID, GFG1.Name
HAVING GFG2.LastSaleDate BETWEEN "1/1/2019" AND "12/31/2019"

Efficient:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate

FROM GFG1 INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID
WHERE GFG2.LastSaleDate BETWEEN "1/1/2019" AND "12/31/2019"
GROUP BY GFG1.CustomerID, GFG1.Name

5. Limit Wildcards to the End of a Search Term

Wildcards enable the broadest search when searching unencrypted material, such as names
or cities. However, the most extensive search is also the least effective. Using wildcards
like % at the beginning of a string makes it difficult for SQL to efficiently use indexes. It's better
to place them at the end of the search term.

Inefficient:

SELECT City FROM GeekTable WHERE City LIKE ‘%No%’

Efficient:

SELECT City FROM GeekTable WHERE City LIKE ‘No%’

6. Use LIMIT for Sampling Query Results

Limiting the results using LIMIT can help avoid querying the entire table when first testing or
analyzing a query. Only the given number of records are returned by the LIMIT statement. By
using a LIMIT statement, we can avoid stressing the production database with a big query only
to discover that it needs to be edited or improved.

Query:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate

FROM GFG1
INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID
WHERE GFG2.LastSaleDate BETWEEN "1/1/2019" AND "12/31/2019"
GROUP BY GFG1.CustomerID, GFG1.Name
LIMIT 10

7. Run Queries During Off-Peak Hours

Running heavy queries during off-peak hours reduces the load on the database, minimizing the
impact on other users. About planning any query to run at a time when it won't be as busy in
order to reduce the impact of our analytical queries on the database. When the number of
concurrent users is at its lowest, which is often overnight, the query should be executed.

Index Tuning

When choosing and building indexes, database tuning includes index tuning. The index tuning
objective is to speed up query processing. It can be challenging to employ indexes in dynamic
contexts with numerous ad-hoc searches scheduled in advance. The queries that are based on
indexes are subject to index tweaking, and the indexes are generated automatically as needed.
Users of the database do not need to take any specific activities to tune the index.

Advantages of Index Tuning

The performance of queries and databases can be improved by using the Index tuning wizard. It
accomplishes this using the following methods:

• Recommendations for optimal index usage based on query optimizer analysis and
workload

• Examination of changes in query distribution, index utilization, and performance to

determine impact

• Suggestion of fine-tuning strategies for problematic queries

• Use of SQL Profiler to record activity traces and improve performance

Points to consider while creating indexes:

• Short indexes for reduced disk space and faster comparisons

• Distinct indexes with minimal duplicates for better selectivity

• Clustered indexes covering all row data for optimal performance

• Static data columns for clustered indexes to minimize shifting

SQL Performance Tuning Tools

Utilizing index tuning tools and following best practices is essential for maintaining high-
performing SQL Server environments. Regular monitoring, proactive maintenance, and
continuous improvement are key to optimizing database performance and supporting critical
business applications.

Several SQL performance tuning tools can help identify and optimize database performance.
Some of the popular tools include:

• SQL Sentry (SolarWinds)

• SQL Profiler (Microsoft)

• SQL Index Manager (Red Gate)

• SQL Diagnostic Manager (IDERA)

These tools assist with monitoring, identifying slow queries, and recommending optimization
strategies for improving database performance.

Conclusion

SQL performance tuning is important for ensuring efficient database operations, especially as
the size and complexity of databases grow. By utilizing best practices for query
optimization, indexing, and performance monitoring, we can significantly improve
our database’s response time and reduce system resource usage. Regular maintenance and
performance tuning should be an integral part of database management for businesses relying
on SQL databases for critical operations.

SQL TRANSACTIONS

SQL transactions are essential for ensuring data integrity and consistency in relational
databases. Transactions allow for a group of SQL operations to be executed as a single unit,
ensuring that either all the operations succeed or none of them do. Transactions allow us to
group SQL operations into a single, unified unit. But what exactly is a transaction, and why is it
so important in maintaining data integrity?

What is an SQL Transaction?

A transaction in SQL is a sequence of one or more SQL statements executed as a single unit of
work. These statements could be performing operations like INSERT, UPDATE, or DELETE. The
main goal of a transaction is to ensure that all operations within it should be either completed
successful or rolled back entirely if an error occurs.

The essence of SQL transactions lies in the concept of atomicity either all the operations within
the transaction succeed, or none of them do. This helps maintain the integrity and consistency
of the data in the database.

Key Properties of SQL Transactions: ACID

The integrity of SQL transactions is governed by the ACID properties, which guarantee reliable
database transactions. These four properties work together to guarantee that the database
remains consistent and reliable.
 • Atomicity: The outcome of a transaction can either be completely successful or
completely unsuccessful. The whole transaction must be rolled back if one part of it
fails.

• Consistency: Transactions maintain integrity restrictions by moving the database from

one valid state to another.

• Isolation: Concurrent transactions are isolated from one another, assuring the accuracy
of the data.

• Durability: Once a transaction is committed, its modifications remain in effect even in

the event of a system failure.

SQL Transaction Control Commands

In SQL, transaction control commands manage the execution of SQL operations, ensuring the
integrity and reliability of database transactions. These commands help manage the start,
commit, and rollback of changes made to the database. Below are the key transaction control
commands in SQL, explained with syntax and examples for each.

1. BEGIN TRANSACTION Command

The BEGIN TRANSACTION command marks the beginning of a new transaction. All SQL
statements that follow this command will be part of the same transaction until
a COMMIT or ROLLBACK is encountered. This command doesn't make any changes to the
database, it just starts the transaction.

Syntax:

BEGIN TRANSACTION transaction_name ;

Example of SQL Transaction with a Bank Transfer Scenario

Let’s look at an example of a bank transfer between two accounts. This example demonstrates
the usage of multiple queries in a single transaction.

BEGIN TRANSACTION;

-- Deduct $150 from Account A

UPDATE Accounts
SET Balance = Balance - 150
WHERE AccountID = 'A';

-- Add $150 to Account B

UPDATE Accounts
SET Balance = Balance + 150
WHERE AccountID = 'B';

-- Commit the transaction if both operations succeed

COMMIT;

If any error occurs, such as an issue with the UPDATE query, you can use ROLLBACK to undo all
changes made during the transaction:
ROLLBACK;

This ensures that the system doesn't end up in an inconsistent state, such as deducting money
from one account without adding it to another.

BEGIN TRANSACTION TransferFunds;

2. COMMIT Command

The COMMIT command is used to save all changes made during the current transaction to the
database. Once a transaction is committed, the changes are permanent.

Syntax:

COMMIT;

Example

Here is the sample Student table that will be used to perform the operations in this example.
This table contains basic student details such as ID, name, age, and other relevant information
that will be manipulated using various transaction control commands.

Student Table

Following is an example which would delete those records from the table which have age = 20
and then COMMIT the changes in the database.

DELETE FROM Student WHERE AGE = 20;

COMMIT;

Output

output

3. ROLLBACK Command
The ROLLBACK command is used to undo all changes made in the current transaction. It is
used when an error occurs or when the desired changes cannot be completed. The database
will revert to the state it was in before the BEGIN TRANSACTION was executed.

Syntax:

ROLLBACK;

Example

Delete those records from the table which have age = 20 and then ROLLBACK the changes in the
database. In this case, the DELETE operation is undone, and the changes to the database are
not saved.

DELETE FROM Student WHERE AGE = 20;

ROLLBACK;

Output

output

4. SAVEPOINT Command

A SAVEPOINT is used to create a checkpoint within a transaction. We can roll back to a

specific SAVEPOINT instead of rolling back the entire transaction. This allows us to undo part of
the transaction rather than the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example

SAVEPOINT SP1;
//Savepoint created.
DELETE FROM Student WHERE AGE = 20;
//deleted
SAVEPOINT SP2;
//Savepoint created.

Output
output

Explanation:

From the above example Sample table1, Delete those records from the table which have age =
20 and then ROLLBACK the changes in the database by keeping Savepoints. Here SP1 is first
SAVEPOINT created before deletion. In this example one deletion have taken place. After
deletion again SAVEPOINT SP2 is created.

5. ROLLBACK TO SAVEPOINT

The ROLLBACK TO SAVEPOINT command allows us to roll back the transaction to a specific
savepoint, effectively undoing changes made after that point.

Syntax:

ROLLBACK TO SAVEPOINT SAVEPOINT_NAME;

Example

Deletion have been taken place, let us assume that we have changed our mind and decided to
ROLLBACK to the SAVEPOINT that we identified as SP1 which is before deletion. So, In this case
the DELETE operation is undone, and the transaction is returned to the state it was in at
the SP1 savepoint.

ROLLBACK TO SP1;
//Rollback completed

Output

output

6. RELEASE SAVEPOINT Command

This command is used to remove a SAVEPOINT that we have created. Once a SAVEPOINT has
been released, we can no longer use the ROLLBACK command to undo transactions performed
since the last SAVEPOINT. It is used to initiate a database transaction and used to specify
characteristics of the transaction that follows.

Syntax:

RELEASE SAVEPOINT SAVEPOINT_NAME

Example

Once the savepoint SP2 is released, we can no longer roll back to it.

RELEASE SAVEPOINT SP2; -- Release the second savepoint.

Why Use Transactions in Banking?

In this case, without a transaction, you risk scenarios where money is deducted from one
account but not added to the other, leaving the system in an inconsistent state. Transactions
ensure that such issues are avoided by guaranteeing that both operations succeed or fail
together.

Types of SQL Transactions

There are different types of transactions based on their nature and the specific operations they
perform:

• Read Transactions: Used to only read the data, typically with SELECT queries.

• Write Transactions: These involve modifying the data in the database

with INSERT, UPDATE, or DELETE operations.

• Distributed Transactions: These transactions span multiple databases and ensure

consistency across them.

• Implicit Transactions: Automatically started by SQL Server for certain operations.

• Explicit Transactions: Manually controlled transactions where the user begins and
ends the transaction using BEGIN TRANSACTION, COMMIT, and ROLLBACK.

Monitoring and Optimizing SQL Transactions

To maintain performance and prevent issues, consider the following techniques:

1. Monitor Locks: Track locking behavior and adjust queries to minimize locking conflicts.

2. Limit Transaction Scope: Limit the number of rows or records affected by a transaction to
speed up processing.

3. Use Batch Processing: If you're handling large amounts of data, break the operations into
smaller transactions or batches to avoid overwhelming the system.

Best Practices for Managing SQL Transactions

While SQL transactions are powerful, they require careful management to ensure optimal
performance and avoid problems like transaction blocking and unnecessary locks. Here are
some best practices:

1. Keep Transactions Short: Avoid long-running transactions as they can lock tables and
reduce concurrency.
2. Use Proper Indexing: Proper indexing on tables involved in the transaction can greatly
improve performance.

3. Avoid Transaction Blocking: Ensure transactions don’t block others unnecessarily, causing
delays.

4. Commit Early: Try to commit as soon as the transaction is complete to release resources.

Conclusion

SQL transactions are a fundamental part of database management, providing a way to

ensure data consistency, integrity, and isolation. Understanding how to use BEGIN
TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT is essential for writing reliable and
efficient SQL queries. With proper transaction control, we can ensure that our database
operations are robust, consistent, and optimized for performance. By following best practices
like keeping transactions short and committing early, we can also improve
the scalability and concurrency of our database.