UNIT-2

CONTENT OF UNIT- 2
• Relational data Model and Language
• Relational data model concepts
• integrity constraints:
 entity integrity
 referential integrity
 Keys constraints
 Domain constraints
• Relational algebra
• Introduction to SQL
• Characteristics of SQL
• Advantage of SQL
• SQL data types and literals
• Types of SQL commands
• SQL operators and their procedure
• Tables
• views
• Queries
• Aggregate functions
• Insert
• update and delete operations
• Joins
• Unions, Intersection, Minus
Relational Model: The Relational Model
What is the Relational Model?
The relational model represents how data is stored in Relational Databases. A relational
database consists of a collection of tables, each of which is assigned a unique name.
Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and
AGE shown in the table.

Table Student
ROLL_NO NAME ADDRESS PHONE AGE
1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 18
Important Terminologies
Attribute:
Attributes are the properties that define an entity. e.g.; ROLL_NO, NAME, ADDRESS
Relation Schema:
A relation schema defines the structure of the relation and represents the name of the relation with
its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation schema
for STUDENT. If a schema has more than 1 relation, it is called Relational Schema.
Tuple: Each row in the relation is known as a tuple. The above relation contains 4 tuples, one of
which is shown as:
1 RAM DELHI 9455123451 18

Relation Instance: The set of tuples of a relation at a particular instance of

time is called a relation instance. Table 1 shows the relation instance of
ROLL_NO
STUDENT at a particular time. It can change whenever there is an insertion,
deletion, or update
1
In the database.
Degree: The number of attributes in the relation is known as the degree of 2
the relation. The STUDENT relation defined above has degree 5.
Cardinality: The number of tuples in a relation is known as cardinality. 3
The STUDENT relation defined above has cardinality 4.
Column: The column represents the set of values for a particular attribute. 4
The column ROLL_NO is extracted from the relation STUDENT.
NULL Values: The value which is not known or unavailable is
called a NULL value. It is represented by blank space. e.g.; PHONE
of STUDENT having ROLL_NO 4 is NULL.
Relation Key: These are basically the keys that are used to
identify the rows uniquely or also help in identifying tables.

These are of the following types.

Primary Key
Candidate Key
Super Key
Foreign Key
Alternate Key
Composite Key
Relational data Model and Language
Relational Data Model and Language
• Relational Data Model is the most widely used model in the database
management systems. This model organizes data into tables (relations),
where each table is a collection of related data entries, consisting of rows and
columns.

Key Concepts in Relational Data Model

• Relation: A table with columns and rows.
• Tuple: A single row in a table, representing a single record.
• Attribute: A column in a table, representing a data field.
• Domain: The set of permissible values for an attribute.
• Relation Schema: The structure of a relation, defined by a set of attributes.
• Relation Instance: A set of tuples at a particular point in time.
• Primary Key: An attribute or a set of attributes that uniquely identify a tuple
in a relation.
• Foreign Key: An attribute in one table that is a primary key in another table,
used to link two tables.
Relational Language
The most commonly used relational language is SQL (Structured
Query Language). SQL is used to perform various operations on the
data stored in a relational database. The SQL language is divided into
several categories:

1. Data Definition Language (DDL)

• Purpose: Defines the structure of the database.
• Examples:
CREATE TABLE employees
(
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
2. Data Manipulation Language (DML)
• Purpose: Manipulates the data stored in the database.
• Examples:
 SELECT * FROM employees;
 INSERT INTO employees (id, name, email) VALUES (1, 'John Doe',
'john.doe@example.com');
 UPDATE employees SET email = 'john.newemail@example.com' WHERE
id = 1;
 DELETE FROM employees WHERE id = 1;

3. Data Control Language (DCL)

• Purpose: Controls access to data in the database.
• Examples:

 GRANT SELECT ON employees TO user;

 REVOKE SELECT ON employees FROM user;

4. Transaction Control Language (TCL)
• Purpose: Manages transactions in the database.
• Examples:
 BEGIN TRANSACTION;
 COMMIT;
 ROLLBACK;
Advantages of the Relational Model
• Simplicity: Easy to understand and use.
• Flexibility: Easy to modify and extend the
database.
• Data Integrity: Enforces data integrity constraints.
• Data Independence: Provides logical and physical
data independence.
Example of a Relational Database
Consider a simple database for an online store with two tables:
customers and orders.

Customers Table
customer_id name email
1 John Doe john.doe@example.com
2 Jane Smith jane.smith@example.com

order_id
order_id customer_id product quantity
101 1 Laptop 1
102 2 Smartphone 2
Constraints in dbms
What are the Constraints in DBMS?
• Constraints in DBMS (Database Management Systems) are rules
or conditions that are applied to the data within a database to
ensure data integrity, consistency, and adherence to business
rules.
• They define limitations and requirements that the data must
meet, thereby preventing the entry of invalid or inconsistent data.
• Constraints serve as a set of predefined rules that govern the
behavior and relationships of the data in a database, helping to
maintain its accuracy and reliability.
• The purpose of constraints is to enforce data quality and prevent
data inconsistencies, thereby enhancing the overall data integrity
and reliability of the database.
• Constraints define boundaries for data values, relationships
between entities, uniqueness requirements, and more.
• By enforcing constraints, DBMS can ensure that the data conforms
to predefined standards and business rules, making the database
Types of Constraints in DBMS
• In relational databases, there are mainly 5
types of constraints in DBMS called relational
constraints.
They are as follows:
• Domain Constraints in DBMS
• Key Constraints in DBMS
• Entity Integrity Constraints in DBMS
• Referential Integrity Constraints in DBMS
• Tuple Uniqueness Constraints in DBMS
Domain Constraints in DBMS
• The domain means a range of values. In
mathematics, the concept of Domain means the
allowed values for a function.
• Similarly, in DBMS, the Domain Constraint specifies
the domain or set of values.
• This is a constraint applied to attributes, not tuples.
This means that it defines what values are allowed
to be kept inside a particular column (attribute) for
a table.
• The domain constraint specifies that the value of an
attribute must be an atomic value in its own
domain.
• Domain constraint defines the domain or set of values for an attribute.
• It specifies that the value taken by the attribute must be the atomic
value from its domain. Example- Consider the following Student table-
STU_ID Name Age
S001 Akshay 20
S002 Abhishek 21
S003 Shashank 20
S004 Rahul A
• Here, value ‘A’ is not allowed since only integer values can be taken by
the age attribute.
Consider the following example table.
Student ID Student Name Marks (in %)
1 Guneet 90
2 Ahan 92
3 Yash 87
4 Lavish 90
5 Ashish 79

• So, we can say that this is a valid table.

• This is because the student ID attribute can have only
integers as ID and it does have only integers as ID.
• Also, the names can be strings only and the marks
can be integers or floating values only.
• So, every attribute for every tuple in this table has its
values within its domain.
Now, consider the table shown below.

Student ID Student Name Marks (in %)

1 Guneet 90

2 Ahan 92

3 Yash 87

4 Lavish A

5 Ashish 79

• Now, in the table above, the tuple with Student ID = 4 and name =
“Lavish” has marks = A.
• This is not an integer or float value. So, the domain constraint is
violated here.
Tuple Uniqueness Constraint- Tuple
Uniqueness constraint specifies that all the tuples must be
necessarily unique in any relation.
• Consider the following Student table-
STU_ID Name Age
S001 Akshay 20
S002 Abhishek 21
S003 Shashank 20
S004 Rahul 20

• This relation satisfies the tuple uniqueness constraint since here all the
tuples are unique.
STU_ID Name Age
S001 Akshay 20
S001 Akshay 20
S003 Shashank 20
S004 Rahul 20

• This relation does not satisfy the tuple uniqueness constraint since
here all the tuples are not unique.
Key Constraint
• All the values of primary key must be unique.
• The value of primary key must not be null.
• Consider the following Student table

STU_ID Name Age

S001 Akshay 20
S001 Abhishek 21
S003 Shashank 20
S004 Rahul 20

• This relation does not satisfy the key constraint as here all the values of
primary key are not unique.
Entity Integrity Constraints
• Entity integrity constraint specifies that no attribute of primary key must
contain a null value in any relation.
• This is because the presence of null value in the primary key violates the
uniqueness property.
• Example- Consider the following Student table
STU_ID Name Age
S001 Akshay 20
S002 Abhishek 21
S003 Shashank 20
Rahul 20

• This relation does not satisfy the entity integrity constraint

as here the primary key contains a NULL value.
Referential Integrity Constraints
• This constraint is enforced when a foreign key references the primary key
of a relation.
• It specifies that all the values taken by the foreign key must either be
available in the relation of the primary key or be null.
Referential Integrity Constraints
Consider the following two relations- ‘Student’ and ‘Department’.
Here, relation ‘Student’ references the relation ‘Department’.
Student Department
STU_I Dept_no Dept_name
Name Dept_no
D D10 ASET
S001 Akshay D10 D11 ALS
S002 Abhishek D10
D12 ASFL
S003 Shashank D11
D13 ASHS
S004 Rahul D14

The relation ‘Student’ does not satisfy the referential integrity constraint.
This is because in relation ‘Department’, no value of primary key specifies department no. 14.
Relational Algebra
Relational Algebra
• Relational algebra is a procedural query language.
• It gives a step by step process to obtain the result of the query.
• It uses operators to perform queries.
• Relational Operators always work on one or more relational tables.
• Relational Operators always produce another relational table.

Types of Relational operation

• Relational algebra is also known as
Procedural Query Language or Formal
Query Language
• Symbol: ∏ (Pi)
• What to do & How to do would be
mentioned in Procedural Query
Language
• Collection of mathematical Expression
• It removes duplicate tuples (records) from the
result.
Relational Algebra operators
 Projection operation
Projection operation- Works on column.
How to retrieve data(integer as well as Char
type ) from table
How to retrieve data(Char type only) from table
• Example Display RollNo, Name and Branch
of all students. Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 9

Answer ∏ RollNo, Name, Branch (Student)

Output
RollNo Name Branch
101 Raju CE
Mitesh
102 ME
103 Nilesh CL
104 MEET CE
QUE- Write down the relational algebra for the student table.
• Display RollNo, Name and SPI of all students.
• Display Name and SPI of all students. Student
• Display the Name of all students.
• Display the Name of all branches.
RollNo Name Branch SPI
101 Raj CE 6
102 Meet ME 8
103 Harsh EE 7
104 Punit CE 9

QUE- Write down the relational algebra for the employee table.
• Display EmpID with Name of all employee.
• Display Name and Salary of all employee.
• Display the Name of all employee. Employee
• Display the Name of all departments. EmpID Name Dept Salary
101 NILESH SALES 10000

102 MAYUR HR 25000

103 HARDIK HR 15000

104 AJAY ADMIN 20000

 Selection
Operation(sigma)
you can mention multiple fields using
Projection operation at the same time and
can fetch specific information
If you want to try projection first in selecting specific
record values then it will not retrieve you required
info
 Selection Operation(sigma)
Works on row. For choosing specific record(This way it will retrieve
you the complete values of that roll number.()
Symbol:σ(Sigma)
It selects those rows or tuples from the relation that satisfies the
selection condition.
Operation: Selects tuples from a relation that satisfy a given
condition. Operators: =, <>, <, >, <=, >=, Λ (AND), V (OR)

Example Display the detail of students belongs to “CE” Branch.

Student
RollNo Name Branch SPI Answer σBranch=‘CE’ (Student)
101 Raju CE 8 Output
102 Mitesh ME 9 RollNo Name Branch SPI
103 Nilesh CI 9
101 Raju CE 8
104 Meet CE 9
104 Meet CE 9
 Example Display the detail of students belongs to “CE” Branch and having
SPI more than 8.

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 9

Answer σBranch=‘CE’ Λ SPI>8 (Student)

Output
RollNo Name Branch SPI

104 Meet CE 9
 • Example :Display the detail of students belongs to either “CI”
or “ME” Branch.

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 9

(Student)
σ
Answer : Branch=‘CI’ V Branch=‘ME’

Output
RollNo Name Branch SPI
102 Mitesh ME 9
103 Nilesh CI 9
 Example: Display the detail of students whose SPI between
7 and 9.
Example

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 9

Answer σSPI>7 Λ SPI<9 (Student)

Output
RollNo Name Branch SPI
101 Raju CE 8
Que: Write down the relational algebra for the student table.
• Display the detail of students whose RollNo is less than 104.
• Display the detail of students having SPI more than 8.
• Display the detail of students belongs to “CE” Branch having SPI less than 8.
• Display the detail of students belongs to either “CE” or “ME” Branch.
• Display the detail of students whose SPI between 6 and 9.

STudent
RollNo
Employee Name Branch SPI
101 EmpID NameRaj Dept Salary CE 6
102 Meet ME 8
103 Harsh EE 7
104 Punit CE 9
 Combined Projection & Selection Operation

• Example Display RollNo, Name & Branch of “ME” Branch

students Student

RollNo Name Branch SPI

101 Raju CE 8
Step: 1σBranch=‘ME’(Student) 102 Mitesh ME 9
Output1 103 Nilesh CI 9
tep-1
RollNo Name Branch SPI
104 Meet CE 7
102 Mitesh ME 9

Answer- ∏ RollNo, Name, Branch (σBranch=‘ME’ (Student))

Output-2
RollNo Name Branch
102 Mitesh ME
• Example Display Name, Branch and SPI of
students whose SPI is more than 8
Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 7 Answer :∏ Name, Branch, SPI
Step-1 σSPI>8 (Student) (
σSPI>8 (Student))
Output-
Output-1 2
RollNo Name Branch SPI Name Branch SPI
Mitesh ME 9
102 Mitesh ME 9
Nilesh CI 9
103 Nilesh CI 9
 Que- Display Name of students along with their Branch who belong to either
“ME” Branch or “CI” Branch. Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 7

STEP 1-σBranch=‘ME’ V Branch=‘CI’ (Student)

Output- ANSWER=
1 ∏ Name, Branch (σBranch=‘ME’ V Branch=‘CI’
(Student))
RollNo Name Branch SPI
102 Mitesh ME 9 Output-2
103 Nilesh CI 9 Name Branch
Mitesh ME
Nilesh CI
Cross/Cartesian Product in
Relational Algebra
The cross product simply pairs every row from the first table with
every row from the second table
• Symbol: X (Cross)
• In this all rows one by one cross the data with rows of
another table
• In cross product all column on both table need to be equal
• Total number of columns must be m+n.
• Operation: It will multiply each tuples of Relation-1 to each tuples
of Relation-2.
• Total number of rows must be m*n.
• For Cross product we need to apply Join on the table then store the
record in new table.
• When two tables are going to be join then at least one column
name must be same.
Example: Perform Cross Product between Student and Result

Student Result
RNo Name Branch RNo SPI
101 Raju CE 101 8
102 Mitesh ME 102 9

ANSWER: (Student) X (Result)

Output
Student.RNo Name Branch Result.RNo SPI
101 Raju CE 101 8
101 Raju CE 102 9
102 Mitesh ME 101 8
102 Mitesh ME 102 9

If both relations have some attribute with the same name, it can be
distinguished by combing relation-name.attribute-name.
EXAMPLE: Perform Cross Product between Student and Result with colums Rno.,
name, branch and Rno., SPI, BL

Student Result
RNo Name Branch Sem RNo SPI BL Rank
101 Raju CE 3 101 8 1 2
102 Mitesh ME 5 102 9 0 1

ANSWER : ∏ RNo, Name, Branch (Student) X ∏ RNo, SPI, BL (Result)

Output

RNo Name Branch RNo SPI BL

101 Raju CE 101 8 1

101 Raju CE 102 9 0

102 Mitesh ME 101 8 1

102 Mitesh ME 102 9 0

 Student Faculty Student Faculty
RNo Name Dept SPI FId Name Dept RNo Name Dept FId Name Dept
101 Raj CE 8 101 Patel CE 101 Raj CE 101 Patel CE
102 Meet ME 9 102 Shah ME 102 Meet ME 102 Shah ME
103 Jay CE 9 103 Dave ME 103 Jay CE 103 Dave ME

Colums are not equal Colums are equal

tudent Faculty Faculty Studen

Ex t
RNo Name Dept SPI FId Name Dept Sub FId Name Dept p
RNo Name Dept SPI
101 Raj CE 8 101 Patel CE DS 101 Patel CE 5
101 Raj CE 8
102 Meet ME 9 102 Shah ME DBMS 102 Shah ME 3
103 Dave ME 4 102 Meet ME 9
103 Jay CE 9 103 Dave ME DF
103 Jay CE 9

Attributes don’t have same data types

Attributes have same data types
Set Difference/Set Operators
• Set operators combine the results of two or
more queries into a single result.
• Three types of set Operators
1. Union U
2. Intersect/Intersection ∩
3. Minus/ Set Difference −
• Set operators will take two or more queries as
input, which must be union-compatible.
• Both queries should have same (equal) number
of columns
• Corresponding attributes should have the
same data type or domain
 EXERCISE: Check whether following tables are compatible or not:

• A: (First_name(char), Last_name(char), Date_of_Birth(date))

• B: (FName(char), LName(char), PhoneNumber(number))
Χ (Not compatible) Both tables have 3 attributes but third attributes datatype is
different.

• A: (First_name(char), Last_name(char), Date_of_Birth(date))

• B: (FName(char), LName(char), DOB(date))
(Compatible) Both tables have 3 attributes and of same data type

Person (PersonID, Name, Address, Hobby)

Professor (ProfessorID, Name, OfficeAddress, Salary)
(Not compatible) Both tables have 4 attributes but forth attributes datatype is
different.
Union Operation in Relational
Algebra
• Symbol: U
• Notation: Relation-1 (R1) U Relation-2 (R2) OR
Algebra-1 U Algebra-2
• It displays all the tuples/records belonging to
the first relation (left relation) or the second
relation (right relation) or both.
• It also eliminates duplicate tuples (tuples
present in both relations appear once).
 Example Perform Union between Customer ANSWER: (Customer) U (Employee)
and Employee.
Output
Customer Employee
Name
Name Name
Manoj
Raju Meet
Meet
Suresh Suresh
Raju
Meet Manoj
Suresh

EXAMPLE: Display Name of person who are either employee or customer.

Name (Customer) U Name (Employee)

Employee
Customer Output
ID Name Balance ID Name Dept Salary
Name
1 Raju 10000 2 Suresh CE 8000
Manoj
2 Suresh 20000 3 Manoj ME 9000
Raju
Suresh
Intersect/ Intersection
Operator
Symbol: ∩
Notation: Relation-1 (R1) ∩ Relation-2 (R2) OR Algebra-1 ∩ Algebra-2
• It displays all the tuples/records belonging to both relations. OR

• It displays all the tuples/records which are common from both relations

Example Perform Intersection between Customer and Employee.

Answer(Customer) ∩ (Employee
Customer Employee
Name Name Output
Raju Meet Name
Meet
Suresh Suresh
Suresh
Meet Manoj
• Example: Display Name of person who are employee as well as customer.
Customer Employee
ID Name Balance ID Name Dept Salary
1 Raju 10000 2 Suresh CE 8000
2 Suresh 20000 3 Manoj ME 9000

ANSWER: Name(Customer) ∩ Name(Employee)

Output
Name
Suresh
Minus/ Set difference Operators
• Example Display Name of person who are
customer but not employee
Customer Employee
ID Name Balance ID Name Dept Salary Output
1 Raju 10000 2 Suresh CE 8000 Name
2 Suresh 20000 3 Manoj ME 9000 Raju

Name (Customer) − Name (Employee)

Exercise :What is the output of following relational algebra for the below
mentioned tables:
Customer Employee
ID Name Balance ID Name Dept Salary
1 Raju 10000 2 Suresh CE 8000
2 Suresh 20000 3 Manoj ME 9000

Find union, intersection or set difference for table

Introduction to SQL
• SQL (Structured Query Language) is a standard programming
language specifically designed for managing and manipulating
relational databases.
• It allows you to create, read, update, and delete (CRUD) data
stored in a relational database.
• Here's a brief overview of the main concepts and commands in
SQL:
Basic Concepts
Database: A database is an organized collection of structured
data. It is managed by a Database Management System (DBMS).
Table: A table is a collection of related data entries that consists
of rows and columns. Each table in a database has a unique
name.
Row: A row, also known as a record or tuple, represents a single
data item in a table.
Column: A column, also known as a field or attribute, is a vertical
entity in a table that contains all information associated with a
specific attribute.
Primary Key: A primary key is a column (or a set of columns)
that uniquely identifies each row in a table. Each table should
have a primary key to ensure that each record is unique.
Foreign Key: A foreign key is a column (or a set of columns) that
establishes a link between the data in two tables. It is a
reference to the primary key in another table.
SQL Commands:
DDL
DML
DCL
TLC
 Basic SQL Queries:
Creating a Table: CREATE TABLE Employees (
EmployeeID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
Age int,
PRIMARY KEY (EmployeeID)
);

Inserting Data into a Table:

INSERT INTO Employees (EmployeeID,
FirstName, LastName, Age)
VALUES (1, 'John', 'Doe', 30);

Selecting Data from a Table:

SELECT * FROM Employees;

Updating Data in a Table: UPDATE Employees
SET Age = 31
WHERE EmployeeID = 1;

Deleting Data from a Table:

DELETE FROM Employees
WHERE EmployeeID = 1;
Characteristics of SQL
key characteristics of SQL:
1. High-Level Language: SQL is a high-level language that allows users to interact with the database
without needing to understand the underlying database structure. It focuses on what needs to be done
rather than how it should be done.
2. Declarative Nature: SQL is declarative, meaning users specify what they want to retrieve, insert,
update, or delete without describing the procedural steps to accomplish these tasks.
Data Definition: SQL includes commands for defining the database schema. This involves creating,
modifying, and deleting tables and other database objects.
Data Manipulation: SQL provides commands for inserting, updating, deleting, and querying data within
the tables.
Data Control: SQL has commands for defining access controls and permissions. It allows database
administrators to control who can access and manipulate the data.
Transaction Control: SQL supports transaction control commands such as BEGIN TRANSACTION,
COMMIT, and ROLLBACK to ensure data integrity and consistency.
Set-Oriented Processing: SQL processes data in sets rather than individually, making it efficient for
handling large volumes of data.
Built-in Functions: SQL provides a variety of built-in functions for performing operations on data, such as
mathematical calculations, string manipulations, and date/time processing.
Integration with Other Languages: SQL can be embedded within other programming languages (e.g.,
Python, Java, C#) to provide a seamless way to interact with databases from within applications.
Portability: SQL is a standardized language, which means SQL code can be used across different
relational database management systems (RDBMS) with minimal modifications.
Advantage of SQL
Efficient Data Retrieval:
SQL is designed to efficiently query and retrieve large volumes of data from relational databases.

High Performance:
SQL can handle large databases with millions of rows of data and complex queries efficiently.

Flexibility:
SQL can be used to query data from multiple tables using joins, subqueries, and unions, providing flexibility in data retrieval.

Data Integrity:
SQL supports integrity constraints such as primary keys, foreign keys, and unique constraints to maintain data accuracy and consistency.

Portability:
SQL is standardized by ANSI and ISO, making SQL code portable across different relational database management systems (RDBMS) with
minimal changes.

Security:
SQL provides robust security features, including user authentication, access control, and permissions to protect sensitive data.

Scalability:
SQL databases can scale vertically and horizontally to handle increasing data loads and user requests.

Data Manipulation:
SQL allows for efficient data manipulation operations such as inserting, updating, deleting, and merging data.

Transaction Control:
SQL supports transactions, ensuring that a series of operations are executed reliably and maintaining database consistency.

Built-in Functions:
SQL includes a wide range of built-in functions for mathematical calculations, string operations, date and time manipulation, and more.

Data Standardization:
SQL provides a standardized way to interact with databases, ensuring consistency in data management across different systems.

Ease of Use:
SQL is relatively easy to learn and use, even for users with little programming experience, due to its declarative nature and straightforward
syntax.
SQL data types and literals
1. Numeric Data Types:
A. INTEGER: 32 bit
Stores whole numbers without fractions.
Example: INTEGER, INT.

C. DECIMAL(p, s):
Stores fixed-point numbers with precision p and scale s.
Example: DECIMAL(10, 2).

D. NUMERIC(p, s):

Similar to DECIMAL, ensures exact precision.

Example: NUMERIC(10, 2).

E. FLOAT(p):

Stores floating-point numbers with precision p.

Example: FLOAT. like 35.3, -2.34

F. REAL:

Stores single-precision floating-point numbers.

Example: REAL.
G. DOUBLE PRECISION:

Stores double-precision floating-point numbers.

Example: DOUBLE PRECISION.
2. Character String Data Types:

A. CHAR(n):
Fixed-length character string of length n.
Example: CHAR(10).

B. VARCHAR(n):
Variable-length character string with a maximum length
of n.
Example: VARCHAR(255).

C. TEXT:
Variable-length character string with an unspecified
length.
Example: TEXT.
3. Date and Time Data Types:
A. DATE:
Stores date values (year, month, day).
Example: DATE.

B. TIME:
Stores time of day values (hour, minute, second).
Example: TIME.

C. TIMESTAMP:
Stores date and time values.
Example: TIMESTAMP.

D. INTERVAL:
Stores a time span.
Example: INTERVAL.
4. Binary Data Types:
A. BINARY:
Fixed-length binary data.
Example: BINARY(100).

B. VARBINARY:
Variable-length binary data with a maximum length.
Example: VARBINARY(255).

5. Boolean Data Type:

A. BOOLEAN:
Stores TRUE or FALSE values.
Example: BOOLEAN.
SQL Literals- SQL literals are fixed values that are used directly in SQL
statements. These values can be of various data types, such as numeric,
string, date and time, boolean, and more. Literals are often used to
represent constant values in queries, expressions, and conditions.
• String Literals: string literals enclosed with single quotes (‘ ‘)
Enclosed in single quotes (').
Example: 'Hello, World!'.
• Numeric Literals: it Can be integers or floating-point numbers.
Example: 123, 45.67.
• Date and Time Literals: Enclosed in single quotes (‘ ’)and in specific
formats.
Example: '2024-07-22' for DATE,(‘YYYY-MM-DD’)
• '14:30:00' for TIME, ( ‘HH:MI:SS’)
• '2024-07-22 14:30:00' for TIMESTAMP. Combine data and time
• Boolean Literals:
Represented by TRUE or FALSE.
Example: TRUE, FALSE.
• NULL:
Represents missing or unknown data.
Example: NULL.
Types of SQL commands
 DDL
• This command used to define data structures and modify data.
• Data Definition Language (DDL) is a subset of SQL used to define and manage
all database objects, such as tables, indexes, and schemas. Key DDL commands
include:
• CREATE: Used to create a new table, database, index,
or view.
• CREATE TABLE Employees (
• EmployeeID int,
• FirstName varchar(255),
• LastName varchar(255),
• BirthDate date,
• Position varchar(255)
• );
• ALTER: Used to modify an existing database object,
such as a table.
• ALTER TABLE Employees
• ADD COLUMN Salary decimal(10, 2);

• DROP: Used to delete a table, database, or other

database object.
• DROP TABLE Employees;
• TRUNCATE: Used to remove all records from a table,
but the structure of the table remains.
• TRUNCATE TABLE Employees;

Create student table

CREATE TABLE students (

student_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
enrollment_date DATE
);
DML
1.SELECT: Used to query and retrieve data from a database.
SELECT FirstName, LastName, Position
FROM Employees
WHERE Position = 'Manager';

2.INSERT: Used to add new records to a table.

INSERT INTO Employees (EmployeeID, FirstName, LastName,

BirthDate, Position)
VALUES (1, 'John', 'Doe', '1980-01-15', 'Manager');
3.UPDATE: Used to modify existing records in a table.

UPDATE Employees
SET Salary = 75000
WHERE EmployeeID = 1;

4.DELETE: Used to delete records from a table.

DELETE FROM Employees
WHERE EmployeeID = 1;
DCL
TCL
1. COMMIT: Used to save all changes made during the current
transaction.
COMMIT;

2. ROLLBACK: Used to undo changes made during the current

transaction.
ROLLBACK;

3. SAVEPOINT: Used to set a point within a transaction to

which you can later roll back.
SAVEPOINT savepoint_name;
Difference between Alter & Update

• Alter all the time works / applies on structure

of table.
• Update always works/ applies on the data in
table
SQL operators and their procedure-

• SQL Operators perform arithmetic, comparison, and

logical operations to manipulate and retrieve data
from databases.
• Operators in SQL are symbols that help us to perform
specific mathematical and logical computations on
operands. An operator can either be unary or binary.
• The unary operator operates on one operand, and
the binary operator operates on two operands.
Types of Operators in SQL

• Arithmetic operator
• Comparison operator
• Logical operator
• Bitwise Operators
• Compound Operators
SQL Arithmetic operator-
Arithmetic operator in SQL are used to perform mathematical operations on
numeric values in queries. Some common arithmetic operators are
Operator Description
The addition is used to perform an addition operation on the data
+ values.
Code- SELECT 10 + 5;
This operator is used for the subtraction of the data values.
–
Code- SELECT 10 - 5;
This operator works with the ‘ALL’ keyword and it calculates division
/ operations.
Code- SELECT 10 / 5;
This operator is used for multiplying data values.
*
Code- SELECT 10 * 5;

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

% another.
Code -SELECT 10 % 3;
Examples of Arithmetic operator

• SELECT 5 + 3; -- Result: 8
• SELECT 5 - 3; -- Result: 2
• SELECT 5 * 3; -- Result: 15
• SELECT 6 / 3; -- Result: 2
• SELECT 7 % 3; -- Result: 1
• Ex- find out the total salary of employee, like –
salary+TA+DA
SQL 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.
< Less than.
>= Greater than equal to.
<= Less than equal to.
<>, != Not equal to.
Examples of Comparison Operators-

• SELECT * FROM employees WHERE age = 30;

• SELECT * FROM employees WHERE age <>, != 30;

• SELECT * FROM employees WHERE salary > 50000;

• SELECT * FROM employees WHERE salary < 50000;

• SELECT * FROM employees WHERE salary >= 50000;

• SELECT * FROM employees WHERE salary <= 50000;

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.
Examples of Logical Operators-

• SELECT * FROM employees WHERE age > 30 AND

salary > 50000;

• SELECT * FROM employees WHERE age > 30 OR

salary > 50000;

• SELECT * FROM employees WHERE NOT (age > 30);

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 when both are different then
^ result 1 otherwise 0

~ Bitwise NOT (complement) operator

<< Left shift operator

>> Right shift operator

Examples of Bitwise Operators-
• AND :SELECT 5 & 3; -- Result: 1
 Explanation:
 Binary representation of 5: 0101
 Binary representation of 3: 0011
 Bitwise AND result: 0001 (which is 1 in decimal)
• OR : SELECT 5 | 3; -- Result: 7
 Explanation:
 Binary representation of 5: 0101
 Binary representation of 3: 0011
 Bitwise OR result: 0111 (which is 7 in decimal)
• XOR : SELECT 5 ^ 3; -- Result: 6
 Explanation:
 Binary representation of 5: 0101
 Binary representation of 3: 0011
 Bitwise XOR result: 0110 (which is 6 in decimal)
• NOT :SELECT ~5; -- Result: -6
 Explanation:Binary representation of 5: 0101
 Bitwise NOT result: 1010 (which is -6 in decimal when considering signed integers)
• SELECT 5 << 1; -- Result: 10
 Explanation:
 Binary representation of 5: 0101
 Left shift by 1 bit: 1010 (which is 10 in decimal)
• SELECT 5 >> 1; -- Result: 2
 Explanation:
 Binary representation of 5: 0101
 Right shift by 1 bit: 0010 (which is 2 in decimal)
SQL Compound Operators
Compound operator in SQL are used to perform an operation
and assign the result to the original value in a single line. 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-
Special operators are used in SQL queries to perform
specific operations like comparing values, checking for
existence, and filtering data based on certain conditions.
Operators Description
ALL is used to select all records of a SELECT STATEMENT. It compares a value to every
ALL value in a list of results from a query. The ALL must be preceded by the comparison
operators and 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
ANY
if the result of an inner query contains at least one row.
The SQL BETWEEN operator tests an expression against a range. The range consists of a
BETWEEN
beginning, followed by an AND keyword and an end expression.
The IN operator checks a value within a set of values separated by commas and retrieves
IN
the rows from the table that match.

The EXISTS checks the existence of a result of a subquery. The EXISTS subquery tests
EXISTS whether a subquery fetches at least one row. When no data is returned then this operator
returns ‘FALSE’.

SOME operator evaluates the condition between the outer and inner tables and evaluates
SOME
to true if the final result returns any one row. If not, then it evaluates to false.
UNIQUE The UNIQUE operator searches every unique row of a specified table.
Example of Special Operators-
• SELECT * FROM employees WHERE salary BETWEEN
40000 AND 60000;

• SELECT * FROM employees WHERE department_id IN

(1, 2, 3);

• SELECT * FROM employees WHERE name LIKE 'J%';

• SELECT * FROM employees WHERE department_id IS

NULL;
Aggregate functions-

• SQL Aggregate functions are functions where

the values of multiple rows are grouped as
input on certain criteria to form a single value
result of more significant meaning.
• It is used to summarize data, by combining
multiple values to form a single result.
• SQL Aggregate functions are mostly used with
the GROUP BY clause of the SELECT statement.
Various Aggregate Functions

• Count()
• Sum()
• Avg()
• Min()
• Max()
1: Counting Employees by Department
– SELECT department_id, COUNT(*) AS employee_count
– FROM employees
– GROUP BY department_id;
2: Summing Salaries by Department
– SELECT department_id, SUM(salary) AS total_salary
– FROM employees
– GROUP BY department_id;
3: Finding the Average Salary by Job Title
– SELECT job_title, AVG(salary) AS average_salary
– FROM employees
– GROUP BY job_title;
4: Finding the Minimum and Maximum Salaries
by Department
– SELECT department_id, MIN(salary) AS min_salary,
MAX(salary) AS max_salary
– FROM employees
– GROUP BY department_id;
5: Concatenating Employee Names by
Department
– SELECT department_id, GROUP_CONCAT(name) AS
employee_names
– FROM employees
– GROUP BY department_id;
Joins in SQL
An INNER JOIN returns records that have matching values in both tables.
A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left
table (Employees), and the matched records from the right table
(Departments). The result is NULL from the right side if there is no match.
A RIGHT JOIN (or RIGHT OUTER JOIN) returns all records from the right table
(Departments), and the matched records from the left table (Employees). The result
is NULL from the left side when there is no match.
A FULL JOIN (or FULL OUTER JOIN) returns all records when there is a match in
either left (Employees) or right (Departments) table records. The result is NULL from the
side where there is no match.