DATABASE MANAGEMENT SYSTEM

Structured query language

Divya Rai @ 2025 Techpile Technology Pvt.Ltd. Summer Tranning 2025

 Page 1

INDEX
Introduction to SQL ............................................................................................................... 2
What is SQL? ............................................................................................................................... 2
What is DBMS (Database Management System)? ...................................................................... 2
Key Functions of a DBMS: ........................................................................................................... 2
Types of DBMS: ........................................................................................................................... 2
Difference between DBMS (Database Management System) and RDBMS (Relational Database
Management System) ................................................................................................................. 3
What is SQL Commands? ............................................................................................................ 4
Types of SQL Commands ? .......................................................................................................... 4
1.Data Definition Language (DDL):- ............................................................................................ 4
2.Data Manipulation Language:-................................................................................................. 5
3. Data Control Language:- ......................................................................................................... 6
4. Transaction Control Language:- .............................................................................................. 6
SQL OPERATORS:- .............................................................................................................. 8
Types Of SQL Operators .............................................................................................................. 8
1. Arithmetic Operators .............................................................................................................. 8
2. Comparison Operators ............................................................................................................ 9
3. Logical Operators .................................................................................................................... 9
4. Set Operators .......................................................................................................................... 9
Other Common SQL Operators ................................................................................................. 10
SQL JOINING ........................................................................................................................ 10
1. INNER JOIN ............................................................................................................................ 10
2. LEFT JOIN (or LEFT OUTER JOIN) ........................................................................................... 11
3. RIGHT JOIN (or RIGHT OUTER JOIN)...................................................................................... 11
4. FULL JOIN (or FULL OUTER JOIN) ......................................................................................... 11
5. CROSS JOIN............................................................................................................................ 11

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 2

Introduction to SQL
What is SQL?

SQL stands for Structured Query Language. It is used for storing and managing
data in Relational Database Management System (RDBMS). It is a standard
language for Relational DatabaseSystem. It enables a user to create, read,
updateand delete relational databases and tables. All the RDBMS like MySQL,
Informix, Oracle, MSAccess and SQL Server use SQL as their standarddatabase
language .

What is DBMS (Database Management System)?

DBMS stands for Database Management System. It is software that allows users to
create, manage, and interact with databases efficiently. A DBMS provides an
interface between the database and users or application programs, ensuring that
data is consistently organized and remains easily accessible.

Key Functions of a DBMS:

1. Data Storage, Retrieval, and Update: Stores large volumes of data and
allows quick access and modification.
2. Data Security: Controls user access and enforces security rules.
3. Data Integrity: Maintains accuracy and consistency of data over its
lifecycle.
4. Data Backup and Recovery: Provides tools for backing up data and
restoring it after failures.
5. Multi-User Access Control: Allows multiple users to access the database
simultaneously without conflicts.

Types of DBMS:

1. Relational DBMS (RDBMS): Organizes data into tables (e.g., MySQL,

SQL Server).
2. Hierarchical DBMS: Organizes data in a tree-like structure.
3. Network DBMS: Uses a graph structure for relationships.
4. Object-Oriented DBMS: Stores data in objects, as in object-oriented
programming

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 3

Difference between DBMS (Database Management System) and RDBMS

(Relational Database Management System)

DBMS (Database RDBMS (Relational Database

Feature
Management System) Management System)
Stores data as files or
Stores data in tables (rows and
Data Storage collections of data (e.g.,
columns).
hierarchical, network).
No specific mechanism to
Enforces relationships between
Data
define relationships between
tables using keys
Relationship
data. (primary/foreign).
Enforces data integrity rules
Does not enforce integrity
Data Integrity (like entity and referential
constraints.
integrity).
Normalization Supports normalization to
Usually not supported.
Support reduce redundancy.
No standard query language Uses SQL (Structured Query
Query Language
(may be proprietary). Language).
Example File systems, XML MySQL, SQL Server, Oracle,
Systems databases, IMS. PostgreSQL.
Designed for multi-user
Multi-user
Limited support. environments with concurrency
Access
control.
Supports ACID properties
Basic or no transaction
Transactions (Atomicity, Consistency,
support.
Isolation, Durability).

Summary:

• DBMS is a broader concept that includes various types of data management

systems.
• RDBMS is a specific type of DBMS that organizes data into related tables,
enforces rules, and uses SQL for operations.

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 4

What is SQL Commands?

SQL commands are instructions. It is used tocommunicate with the database. It is

also usedtoperform specific tasks, functions, and queries of data.

Types of SQL Commands ?

There are four types of SQL commands:-

1.DDL 2. DML 3.DCL 4. TCL

1.Data Definition Language (DDL):- DDL changes the structure of the table
like creating atable, deleting a table, altering a table, etc.

Here are some commands that come under DDL:

 CREATE

 ALTER

 DROP

TRUNCATE

A. CREATE:- It is used to create a new table in the database.

Syntax:CREATE TABLE TABLE_NAME (COLUMN_NAME
DATATYPES[,....]);

Example: CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email

VARCHAR2(100), DOB DATE);

B. DROP: It is used to delete both the structure and recordstored in the table.
Syntax:DROP TABLE ;

Example: DROP TABLE EMPLOYEE;

C. ALTER: It is used to alter the structure of the database. This changecould

be either to modify the characteristics of an existing attribute or probably to
add a new attribute.
Syntax:ALTER TABLE table_name ADD column_name
COLUMN-definition;

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 5

ALTER TABLE MODIFY(COLUMN DEFINITION....);

Example: ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

D. TRUNCATE: It is used to delete all the rows from the table and freethe
space containing the table.
Syntax: TRUNCATE TABLE table_name;

Example: TRUNCATE TABLE EMPLOYEE;

2.Data Manipulation Language:- DML commands are used to modify the

database. It isresponsible for all form of CHANGES in the database.

Here are some commands that come under DML:

 INSERT

 UPDATE

 DELETE

A. INSERT: The INSERT statement is a SQL query. It is used to insert data

into the row of a table.

Syntax: INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)VALUES (value1,
value2, value3, .... valueN);

OR

INSERT INTOTABLE_NAME VALUES (value1, value2, value3, .... valueN);

Example: INSERT INTO XYZ (Author, Subject) VALUES ("Sonoo", "DBMS");

INSERT:

B. UPDATE: This command is used to update or modify the value of acolumn

in the table.
Syntax: UPDATE table_name SET [column_name1= value1,...column_nameN =
valueN] [WHERE CONDITION]

Example: UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 6

C. DELETE: This command is used to Delete the value of a column on

condition based in the table.
Syntax: DELETE FROM table_name [WHERE CONDITION]

Example: DELETE FROM student where student_id= '3'

3. Data Control Language:- DCL commands are used to GRANT and TAKE
BACK authority from any database user.

Here are some commands that come under DCL:

Grant

Revoke

A. GRANT: It is used to give user access privileges to a database.

Example: GRANT SELECT, UPDATE ON MY_TABLE TO

SOME_USER, ANOTHER_USER;

B. REVOKE: It is used to take back permissions from the user.

Example: REVOKE SELECT, UPDATE ON MY_TABLE FROM
USER1, USER2;

4. Transaction Control Language:-TCL commands can only use with DML

commands like INSERT, DELETE and UPDATE only. These operations are
automatically committed in the database that's why they cannot be used while
creatingtables or dropping them.

Here are some commands that come under TCL:

 COMMIT

 ROLLBACK

 SAVEPOINT

A. COMMIT:-Commit command is used to save all the transactions tothe

database.
Syntax: COMMIT;

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 7

Example: DELETE FROM CUSTOMERS WHERE AGE = 25; COMMIT;

B. ROLLBACK:-Rollback command is used to undo transactions that have

notalready been saved to the database.

Syntax: ROLLBACK;

Example: DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK;

C. SAVEPOINT: It is used to roll thetransaction back to a certain point without

rolling back the entire transaction.

Syntax: SAVEPOINT SAVEPOINT_NAME;

Category Command Example Description

DDL CREATE CREATE TABLE Creates a new table
(Definition) Members (IDINT,
Name
VARCHAR(50));
ALTER Modifies an existing
table
ALTER TABLE
Members ADD
Phone
VARCHAR(15);
DROP TABLE
DROP Deletes a table
Members;
DML INSERT INTO
(Manipulation) INSERT INTO Members (Name) Adds new data
VALUES ('John');
SELECT * FROM
SELECT Retrieves data
Members;
UPDATE Members
Modifies existing
UPDATE SET Name='Jane'
data
WHERE ID=1;
DELETE FROM
DELETE Removes data
Members
DCL (Control) GRANT SELECT
GRANT ON Members TO Gives privileges
user123;
REVOKE SELECT
Removes
REVOKE ON Members FROM
user123; privileges
TCL SAVEPOINT SAVEPOINT Save1; Sets a savepoint

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 8

(Transaction) within a transaction

Saves changes made
COMMIT COMMIT;
in transaction
Reverts changes if
ROLLBACK ROLLBACK;
an error occurs

SQL OPERATORS:-SQL Operators are special symbols or keywords used in

SQL statements to perform operations on data. They are used in SQL queries to
filter, compare, and manipulate data in the database.

Types Of SQL Operators

1. Arithmetic Operators
2. Comparison Operators
3. Logical Operators
4. Bitwise Operators
5. Other Special Operators (LIKE, BETWEEN, IN, etc.)

1. Arithmetic Operators

Used for mathematical calculations.

Operator Description Example

+ Addition SELECT 10 + 5;
- Subtraction SELECT 10 - 5;
* Multiplication SELECT 10 *5;
/ Division SELECT 10 /5;
% Modulus (remainder) SELECT 10 %3;

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 9

2. Comparison Operators

Used to compare two values.

Operator Description Example

= Equal WHERE salary = 50000
!= or <> Not Equal WHERE name != 'John'
> Greater than WHERE age > 18
< Less than WHERE age < 30
>= Greater than or equal to WHERE marks >= 40
<= Less than or equal to WHERE marks <= 50
3. Logical Operators

Used to combine multiple conditions.

Operator Description Example

Returns true if both WHERE age > 18 AND
AND
conditions are true city = 'Delhi'
Returns true if at least WHERE age < 18 OR
OR
one is true city = 'Delhi'
WHERE NOT city =
NOT Reverses the result
'Delhi'

4. Set Operators

Combine results of queries

Operator Description
Combines result sets (removes
UNION
duplicates)
Combines result sets (includes
UNION ALL
duplicates)
INTERSECT Returns common rows

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 10

Other Common SQL Operators

Operator Description Example

Matches a pattern using WHERE name LIKE
LIKE
wildcards 'A%'
Checks if a value exists WHERE dept IN ('HR',
IN
in a list 'Finance')
Checks if a value is WHERE age
BETWEEN
within a range BETWEEN 18 AND 25
Checks if a value is WHERE address IS
IS NULL
NULL NULL
WHERE EXISTS
Checks if a subquery
EXISTS (SELECT * FROM
returns any rows
dept WHERE ...)

Syntax Of Creating Table:-

CREATE TABLE table_name (

column1 datatype [constraints],

column2 datatype [constraints]

);

SQL JOINING:-SQL,JOIN means" to combine two or more tables" .In

SQL,JOIN clause is used to combine the records from two or more tables in a
database.

1. INNER JOIN

Returns only the rows with matching values in both tables.

Syntax:

SELECT A.column1, B.column2FROM Table AA INNER JOIN Table BB ON

A.common_column = B.common_column;

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 11

Example:

SELECT Employees.Name, Departments.DeptNameFROM EmployeesINNER

JOINDepartmentsON Employees.DeptID = Departments.ID;

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and the matched rows from the right
table. Unmatched rows will have NULL in right table columns.

Syntax:

SELECT A.*, B.*FROM TableA ALEFT JOIN TableB BON A.common_column

=B.common_column;

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and the matched rows from the left
table. Unmatched rows from the left side will be NULL.

SELECT A.*, B.*FROM TableA ARIGHT JOIN TableB BON A.common_column =

B.common_column;

4. FULL JOIN (or FULL OUTER JOIN)

Returns all rows from both tables, whether there’s a match or not. Where
there is no match, NULL is shown.

Syntax:

SELECT A.*, B.* FROM TableA A FULL OUTER JOIN TableB B ON

A.common_column = B.common_column;

5. CROSS JOIN

Returns the Cartesian product of two tables — every row from TableA is
combined with every row from TableB.

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301

 Page 12

Syntax:

SELECT A.*, B.*FROM TableA ACROSS JOIN TableB B;

A SELF JOIN is a regular join where a table is joined with itself.

Example:

SELECT A.Name AS Employee, B.Name AS ManagerFROM Employees A, Employees B

WHERE A.ManagerID = B.EmployeeID;

www.techpile.in | www.techpilelko.in +91- 7007237006 | +91-9140444301