1

What is Query Language?

A query language is a specialized programming language used to retrieve,
manipulate, and manage data stored in databases or other information systems. It
allows users to interact with data by writing queries (commands) to perform
operations such as searching, inserting, updating, and deleting data.

Why Query Language is Required:

1. Data Retrieval:

o Helps users extract only the relevant data from massive databases.

o Example: Get the list of employees earning more than ₹50,000.

2. Data Manipulation:

o Allows updating, inserting, and deleting records in the database.

3. User-Friendly Interaction:

o Enables non-programmers to access and manage data using simple

commands.

o SQL, for instance, is close to English (e.g., SELECT, WHERE).

4. Timesaving& Efficient:

o Instead of manually searching data, a query retrieves results instantly.

5. Automated Decision-Making:

o Essential in systems like banking, e-commerce, and hospitals where

real-time data processing is needed.

6. Standardized Communication:

o Provides a unified way for different systems and applications to talk

to the database.
 2

Common Types of Query Languages:

1. SQL (Structured Query Language)
The most widely used standardized programming language for managing and
manipulating relational databases (e.g., MySQL, PostgreSQL, Oracle).

It allows users to:

 Store data in structured tables (with rows and columns).

 Retrieve data efficiently using queries.

 Modify data (insert, update, delete).

 Control access and define database structures.

Why is SQL Used?

SQL is the most widely used database language because of its:

a. Efficiency in Handling Structured Data

 Works seamlessly with tables (rows & columns), making it ideal for
business data, financial records, and customer information.

b. Standardization & Compatibility

 Supported by major relational database management systems

(RDBMS) like:

o MySQL, PostgreSQL (open source)

o Microsoft SQL Server, Oracle Database (enterprise)

o SQLite (lightweight, used in mobile apps).

c. Powerful Data Manipulation

 CRUD Operations (Create, Read, Update, Delete).

 Complex Queries with joins, subqueries, and aggregations.

 3

d. Data Integrity & Security

 Ensures ACID (Atomicity, Consistency, Isolation, Durability) compliance

for reliable transactions.

 Allows user permissions (e.g., restricting access to sensitive data).

e. Scalability & Performance

 Optimized for large datasets with indexing, stored procedures, and query
optimization.

f. Integration with Applications

 Used in backend systems (e.g., Python + SQLAlchemy, Java + JDBC).

 Supports APIs and business intelligence tools (e.g., Tableau, Power BI).

2. NoSQL Query Languages

NoSQL (Not Only SQL) databases are designed for unstructured, semi-
structured, or distributed data, offering flexibility and scalability beyond
traditional SQL databases. Unlike SQL, which uses a standardized language,
NoSQL systems use different query methods depending on their data model.

3. Graph Query Languages

Graph query languages are specialized programming languages designed to

interact with graph databases, which store data as nodes (entities), edges
(relationships), and properties (attributes). Unlike SQL (which works with tables)
or NoSQL (which handles documents or key-value pairs), graph query languages
excel at traversing complex relationships efficiently.

Why Use Graph Query Languages?

Graph databases and their query languages are ideal for:

Highly connected data (social networks, fraud detection).
Real-time relationship queries (recommendation engines, knowledge graphs).
Pathfinding & network analysis (supply chains, cybersecurity).
 4

4. Search Query Languages

Search query languages are specialized syntaxes used to retrieve information

from search engines, databases, or document stores. Unlike SQL (structured) or
NoSQL (flexible schemas), search query languages focus on full-text search,
relevance ranking, and fuzzy matching.

Why Use Search Query Languages?

Fast text-based searches (Google-like queries).

Handle unstructured data (emails, logs, articles).
Support advanced features (fuzzy search, synonyms, geospatial queries).

Structured Query Language (SQL)

SQL's integration with various technologies makes it essential for managing and
querying data in databases. Whether it is in traditional relational databases
(RDBMS) or modern technologies such as machine learning, AI and blockchain,
SQL plays a key role. It works effortlessly with DBMS to help users interact with
data, whether stored in structured RDBMS or other types of databases.

 Data Science & Analytics: Used for querying large datasets, data cleaning
and analysis. Analysts use SQL to generate reports and insights that inform
business decisions.

 Machine Learning & AI: Helps in preparing and managing the data required
for training machine learning models and AI algorithms. It is used for data
cleaning, transformation, and extraction.

 Web Development: Used to manage user data, e-commerce transactions, and

content management in websites and applications built with frameworks like
Django, [Link], and Ruby on Rails.

 Cloud and Big Data: SQL is integrated into cloud-based databases (e.g.,
Amazon RDS, Microsoft Azure SQL) and Big Data platforms (e.g., Apache
Hive) to enable seamless data querying and management.
 5

 Blockchain and Decentralized Systems: In blockchain systems, SQL can be

used to manage off-chain data, providing efficient data storage and retrieval
alongside decentralized ledger technology

How SQL Query getexecuted?

How SQL Manages and Interacts with Data

Data is the core of every application and SQL plays a key role in managing and
interacting with this data. Whether you are working with a small user database or
handling massive datasets like sales records, SQL allows you to
efficiently query, update, and manage relational databases.
 Data Retrieval: SQL constructs a query that tells the Database Management
System (DBMS) what data is required.
 DBMS Processes Query: DBMS processes the SQL query, retrieves the
data and returns it to the user or application.
 6

 Declarative Nature: SQL is declarative, meaning it focuses on what data

should be retrieved, organized or modified, leaving the how to the DBMS.

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.

1. Statement Termination: Every SQL statement ends with a semicolon (;),

signalling the DBMS to execute the command.

2. Case Insensitivity: SQL keywords (e.g., SELECT, INSERT) are case-

insensitive, but database names and column names may be case-sensitive
depending on the DBMS.

3. Whitespace Flexibility: SQL statements can span multiple lines, but

keywords and identifiers must be separated by at least one space.

4. Unique Identifiers: Reserved words (e.g., SELECT, FROM) cannot be

used as table or column names unless enclosed in double quotes (") or
backticks (`), depending on the DBMS.
5. Comments: Comments enhance readability:
 Single-line comments: --
 Multi-line comments: /* ... */

6. Data Integrity: Constraints like NOT NULL, UNIQUE, and PRIMARY

KEY must be defined correctly to maintain data consistency.

7. String Literals: String values must be enclosed in single quotes (').

8. Valid Identifiers: Table and column names must:

 Begin with an alphabetic character.
 Contain up to 30 characters.
 Avoid special characters except underscores (_).
 7

What are SQL Commands?

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

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

 8

Command Description

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.

 9

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 allchanges made during the current transaction on a

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

Reverts changes made during the current transaction, ensuring no

ROLLBACK unwanted changes are saved.

Sets a point within a transaction to which changes can be rolled

SAVEPOINT back, allowing partial rollbacks

SQL Data Type:

SQL data types define the kind of data a column can store, dictating how the
database manages and interacts with the data. Each data type in SQL specifies a set
of allowed values, as well as the operations that can be performed on the values.
Choosing the correct data type:

 Ensuresdata integrity

 Saves storage space

 Helps infaster processing

 10

1. Numeric Data Types

Data Type Description Example Notes

INT / INTEGER Whole numbers 10, 200 Default size: 4 bytes

SMALLINT Smaller whole 0 to 32,767 2 bytes

numbers

BIGINT Very large > 2 billion 8 bytes

integers

DECIMAL(p,s) / Fixed-point 12.45 p = total digits, s =

NUMERIC(p,s) numbers digits after decimal

FLOAT Floating-point 10.5, 20.9 Approximate values

numbers

REAL Single-precision 0.0034 Less accurate than

float DOUBLE

DOUBLE Double- 12345.6789 High precision

precision float

Use Case Tip: Use DECIMAL for money, FLOAT/REAL for scientific data.

2. String (Character/Text) Data Types

Data Type Description Example Notes

CHAR(n) Fixed-length 'YES' Padded with spaces if

string shorter

VARCHAR(n) Variable-length 'Hello' More space-efficient

string than CHAR

TEXT Large text Paragraphs Max size varies by

block system (MySQL:
 11

65,535 bytes)

TINYTEXT / Increasing — For blogs, descriptions,

MEDIUMTEXT / sizes of text etc.
LONGTEXT

Use Case Tip: Use VARCHAR for names, TEXT for descriptions.

3. Date and Time Data Types

Data Type Description Example Notes

DATE Calendar date '2025-07-04' Format: YYYY-

MM-DD

TIME Time of day '14:30:00' Format:

HH:MM:SS

DATETIME Combined date and time '2025-07-04

14:30:00'

TIMESTAMP Stores UTC time + auto- '2025-07-04 Useful for logs

updated 14:30:00'

YEAR Year (2 or 4 digits) 2025 Deprecated in some

DBs

Use Case Tip: Use DATETIME for event logs, DATE for birthdays.

4. Boolean Data Types

Data Type Description Example Notes

BOOLEAN True/False TRUE / Internally stored as 0 (false), 1

values FALSE (true)

BIT Bit value (0 or 0 / 1 MySQL stores BOOLEAN as

1) TINYINT(1)

Use Case Tip: Ideal for flags like "is_active", "has_paid"

 12

5. Binary Data Types

Data Type Description Example Notes

BINARY(n) Fixed-length binary For images,

data files

VARBINARY(n) Variable-length binary For BLOBs

data

BLOB Binary Large Object Image or file Up to 4GB in

MySQL

Use Case Tip: Use BLOB for storing images or files, not plain text.

6. Miscellaneous / Advanced
Data Description Notes
Type

ENUM String with predefined set of Example: 'small', 'medium',

values 'large'

SET Multiple values from predefined MySQL specific

list

JSON Stores JSON formatted strings Ideal for nested or dynamic data
 13

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 create a new database within a
SQL based Database Management System (DBMS) such as MySQL,
PostgreSQL, or SQL Server. A database acts as a container where all your data
tables, views, stored procedures etc are stored.

Without databases, data would be unorganized and difficult to manage. By defining

a database, we establish a dedicated environment where data can be stored,
accessed and manipulated effectively. Once a database is created, we can create
tables, insert data, run queries and perform many other database operations.

Syntax:

CREATE DATABASE database_name;

 14

Query:

CREATE DATABASE Batchname_StudentName;

How to Verify if Your Database Was Created

Now we will verify whether the new database that we have just created has been
successfully added to our system or not.

We use the SHOW DATABASES command, and it will return a list of databases
that exist in our system. Now we will look for the name of the database that we
have just created.

Query:

SHOW DATABASES;

2. SELECT Database
The USE DATABASE statement is a command in certain SQL-based database
management systems that allows users to select and set a specific database as the
default for the current session. By selecting a database, subsequent queries are
executed within the context of that database, making it easier to interact with tables
and other objects contained within it.

Syntax:

USE database_name;
Example:

USE BATCHNAME_STUDENTNAME;
 15

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

Syntax:
RENAME DATABASE
old_database_nameTOnew_database_name;
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 Database_name;

SQL CREATE TABLE

The CREATE TABLE command in SQL is used to define a new table within a
database. A table's structure, including column names, data types, and constraints
like NOT NULL, PRIMARY KEY, and CHECK, are defined when it is created in
SQL.

Syntax:

CREATE table table_name

(
Column1 datatype (size),
column2 datatype (size),
.
.
columnN datatype(size)
);
 16

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 1: books Table

CREATE TABLE books (

title VARCHAR(150),
author VARCHAR(100),
published_year INT,
price DECIMAL(6,2)
);
Example 2: Customer Table
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerNameVARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age INT CHECK (Age >= 0 AND Age <= 99),
Phone int(10)
);
SQL Command to See the Whole Table
To view all the data (every row and column) from a table in SQL, use
the:
SELECT * FROM table_name;
Example:
If you have a table named students, the command would be:
 17

SELECT * FROM students;

This will display all columns (*) and all rows from the student’s table.

SQL INSERT INTO Command

The SQL INSERT INTO statement allows you to add new rows of data
into an existing table in a database. It’s a fundamental command in SQL,
frequently used to populate tables with data. There are two primary
methods for using this command:
1. Inserting Data into All Columns: If you want to insert data into
all columns of the table, you can omit the column names and
simply provide values for every column.
2. Inserting Data with Specific Columns: You can explicitly specify
the columns you want to insert data into, followed by the
corresponding values.
The choice of method depends on whether you're inserting data for all
the columns in a table or only for a subset of them.

1. Inserting Data into All Columns (Simple Method)

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);
 18

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 TIUDB;
USE TIUDB;

CREATE TABLE Student (

ROLL_NO INT,
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);
 19

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

 20

ROLL_NO NAME ADDRESS PHONE Age

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 (Flexible Method)

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.
 21

 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

 22

ROLL_NO NAME ADDRESS PHONE Age

5 PRATIK null null 19

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

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'),
 23

(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

Manav New
8 XXXXXXXXXX 17
Bhatt Delhi

Riya
9 Udaipur XXXXXXXXXX 10
Kapoor
 24

SQL DROP TABLE

The DROP TABLE statement in SQL is used to delete a table and all of its data
from the database permanently. This operation cannot be undone, and once the
table is dropped all data in that table is lost. Once executed, this operation removes
the table definition and all its rows, so the table can no longer be accessed or used.
This action is irreversible which means that once a table is dropped, it cannot be
recovered unless there is a backup.

Syntax:
DROP TABLE table_name;

SQL TRUNCATE TABLE

The TRUNCATE TABLE statement in SQL is a powerful command used to
swiftly remove all rows from a table, leaving the table structure intact.

Syntax
TRUNCATETABLEtable_name;

Difference Between DELETE and TRUNCATE

Feature DELETE TRUNCATE

Purpose Deletes specific rows or all Deletes all rows from a table
rows from a table

Condition Supports WHERE clause Does not support WHERE clause

Support

Table Structure Keeps table structure and Also keeps structure, but resets
constraints identity counters (in some DBs)

Rollback Rollback possible if inside a Rollback may not be supported

Support transaction (depends on DB)
 25

Speed Slower (row-by-row Faster (bulk operation, no row-

deletion) by-row deletion)

Trigger Fires DELETE triggers (if Usually doesn’t fire triggers

Execution defined)

Permissions DELETE permission DROP or ALTER permission

Needed required required

Auto Does not reset Resets AUTO_INCREMENT to

Increment AUTO_INCREMENT 1
(MySQL) counter

SQL DELETE Statement

The SQL DELETE statement is an essential command in SQL used to remove one
or more rows from a database table. Unlike the DROP statement, which removes
the entire table, the DELETE statement removes data (rows) from the table
retaining only the table structure, constraints, and schema. Whether you need to
delete a single row based on a condition or remove an entire dataset, understanding
this command will help you manage your data effectively.

Syntax:
DELETE FROM table_nameWHERE 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.
 26

Example Table: Students

ID Name Age Grade
1 Alice 20 A
2 Bob 21 B
3 Chralie 22 C
4 Diana20 20 B

Example 1: Delete a specific row

DELETEFROM Students
WHERE ID =2;
Deletes the row where ID = 2 (Bob).
Example 2: Delete rows based on a condition
DELETEFROM Students
WHERE Grade ='B';

Deletes all students whose grade is 'B' (Bob and Diana).

Example 3: Delete all rows from a table

DELETEFROMStudents;

Deletes all records from Students, but the table structure remains.

Example 4: Delete rows using subquery

DELETEFROM Students
WHERE ID IN (SELECT ID FROM Students WHERE Age >21);

Deletes students older than 21 (Charlie).

Example 5: Delete with multiple conditions

DELETEFROM Students
WHERE Age =20AND Grade ='B';

Deletes students who are 20 years old and have grade 'B' (Diana).
 27

Example 6: TRUNCATE the Students Table

TRUNCATETABLEStudents;

This will remove all data from the Students table, but the table
structure will remain.

After this operation:

SELECT*FROMStudents;
-- No rows will be returned
Example 7: DROP the Students Table
DROPTABLEStudents;

This will permanently delete the table itself, including:

 All data
 Table structure (columns, keys, constraints)
 Associated indexes

After this operation:

SELECT*FROMStudents;
-- ❌Error: Table 'Students' doesn't exist

What is * in SQL?
In SQL, the asterisk symbol * is a wildcard that means "all columns". It is
commonly used in SELECT statements to retrieve all columns from a table without
listing them individually.

Syntax
SELECT*FROMtable_name;

 SELECT * means: Select all columns from the table.

 28

Example Table: Employees

+----+----------+----------+
| ID | Name | Dept |
+----+----------+----------+
|1| Alice | HR |
|2| Bob | IT |
|3|Charlie | Sales |
+----+----------+----------+
Using *:
SELECT*FROMEmployees;

Output:

ID | Name | Dept
---------------------
1 | Alice | HR
2 | Bob | IT
3 |Charlie | Sales

The SELECT Statement in SQL

The SELECT statement is the fundamental command in SQL
(Structured Query Language) used to retrieve data from a database. It
allows you to query and return data from one or more tables in a
database.

Basic Syntax
SELECT column1, column2,...FROMtable_name;

Key Features

1. Selecting Columns: You can specify which columns to retrieve

SELECTfirst_name,last_nameFROMemployees;
 29

2. Selecting All Columns: Use the asterisk (*) wildcard

SELECT*FROMemployees;