Database Management System (220401)

Lecture Notes: Chapter−3

Dr. Gaurav Khare

Syllabus:
Introduction of SQL, features of SQL, Data Definition & Data Manipulation commands in SQL, SQL operators, Update State-
ments & Views in SQL, Query & Sub query, Data Retrieval Queries & Data Manipulation Statements examples etc. Overview
of Tuple Oriented Calculus & Domain Oriented Relational Calculus.

1 Introduction of SQL
SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It is a
standard language for managing data in a database, and it provides an easy and efficient way to access, store, and retrieve data.
SQL is used by database administrators, data analysts, and software developers to create, modify, and manage databases.
SQL is a declarative language, which means that users can describe the data they want to retrieve or manipulate, and the
database management system (DBMS) will figure out how to do it. SQL is a domain-specific language, which means that it is
designed for a specific purpose (in this case, working with databases).

1.1 SQL Syntax

SQL is made up of a series of commands and queries that are used to manage data in a database. Here are some of the basic
SQL commands:
• SELECT: used to retrieve data from a database

• INSERT: used to insert data into a database

• UPDATE: used to update existing data in a database
• DELETE: used to delete data from a database
• CREATE: used to create new tables, views, or indexes in a database

• ALTER: used to modify existing tables, views, or indexes in a database

• DROP: used to delete existing tables, views, or indexes in a database
SQL statements are written in a specific format, with each statement separated by a semicolon. Here is an example of a
SELECT statement:
SELECT * FROM customers;
This statement retrieves all data from the “customers” table in a database.

1.2 SQL Data Types

SQL supports several different data types, including:
• Numeric types: used to store numeric data (e.g., INTEGER, FLOAT, DECIMAL)
• Character types: used to store character or string data (e.g., CHAR, VARCHAR)
• Date/time types: used to store date and time data (e.g., DATE, TIME, TIMESTAMP)

• Boolean types: used to store Boolean (true/false) data (e.g., BOOLEAN)

• Binary types: used to store binary data (e.g., BLOB, CLOB)

1
 1.3 SQL Constraints
SQL constraints are rules and conditions applied to columns in a database table to ensure data integrity and maintain consistency.
They help enforce data validation and business rules, preventing the insertion of incorrect or inconsistent data.

1.3.1 Primary Key Constraint

A primary key constraint ensures that each row in a table has a unique identifier. It also enforces the rule that a primary key
column cannot contain NULL values.
1 CREATE TABLE Employees (
2 EmployeeID INT PRIMARY KEY ,
3 FirstName VARCHAR (50) ,
4 LastName VARCHAR (50)
5 );

1.3.2 Unique Constraint

A unique constraint ensures that the values in a column or a set of columns are unique across all rows in the table. Unlike the
primary key, unique constraints allow NULL values.
1 CREATE TABLE Customers (
2 CustomerID INT UNIQUE ,
3 FirstName VARCHAR (50) ,
4 LastName VARCHAR (50)
5 );

1.3.3 Foreign Key Constraint

A foreign key constraint establishes a relationship between two tables, ensuring that the values in a specified column(s) of one
table match the values in a primary key column of another table. This constraint helps maintain referential integrity.
] CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE, FOREIGN KEY (Cus-
tomerID) REFERENCES Customers(CustomerID) );

1.3.4 Check Constraint

A check constraint defines a condition that must be met for data to be inserted or updated in a column. It can be used to
enforce domain-specific rules or data validation.
1 CREATE TABLE Products (
2 ProductID INT PRIMARY KEY ,
3 ProductName VARCHAR (100) ,
4 Price DECIMAL (10 , 2) ,
5 StockQuantity INT ,
6 CHECK ( Price >= 0 AND StockQuantity >= 0)
7 );

1.3.5 Not Null Constraint

A not null constraint ensures that a column cannot contain NULL values.
1 CREATE TABLE Students (
2 StudentID INT PRIMARY KEY ,
3 FirstName VARCHAR (50) NOT NULL ,
4 LastName VARCHAR (50) NOT NULL
5 );

2
 1.3.6 Default Constraint
A default constraint specifies a default value for a column if no value is provided during insertion.
1 CREATE TABLE Tasks (
2 TaskID INT PRIMARY KEY ,
3 TaskName VARCHAR (100) ,
4 Status VARCHAR (20) DEFAULT ’ Pending ’
5 );

1.3.7 Multi-Column Constraints

Constraints can be applied to multiple columns simultaneously to enforce rules that involve multiple columns.
1 CREATE TABLE Reservations (
2 ReservationID INT PRIMARY KEY ,
3 CheckInDate DATE ,
4 CheckOutDate DATE ,
5 CONSTRAINT C h e c k I n B e f o r e C h e c k O u t CHECK ( CheckInDate < CheckOutDate )
6 );

SQL constraints play a vital role in maintaining the quality and integrity of your database, ensuring that the data follows
specific rules and relationships, which is crucial for database consistency and reliability.

2 SQL Features
Here are some of the key features of SQL:
1. Data Retrieval: SQL provides a rich set of commands for retrieving data from a database. The SELECT statement is
used to retrieve data from one or more database tables. SQL also provides various clauses like WHERE, GROUP BY,
HAVING, and ORDER BY to refine the data returned by a query.

2. Data Manipulation: SQL provides commands for modifying data stored in a database. The INSERT statement is used
to add new records to a table, the UPDATE statement is used to modify existing records, and the DELETE statement is
used to remove records from a table.
3. Database Creation and Modification: SQL provides commands for creating and modifying database objects like
tables, views, indexes, and constraints. These commands enable developers to define the structure of a database, enforce
data integrity rules, and optimize performance.
4. Data Integrity: SQL provides various mechanisms for ensuring data integrity, such as constraints, triggers, and transac-
tions. Constraints ensure that data entered into a table meets certain criteria, while triggers allow developers to execute
code automatically in response to specific events. Transactions ensure that multiple database operations are executed as
a single unit of work, and can be rolled back if any errors occur.

5. Security: SQL provides robust security features, such as user authentication, authorization, and encryption. These
features enable developers to control access to data stored in a database and protect sensitive information from unauthorized
access.
6. Portability: SQL is a standard language that is supported by most relational database management systems (RDBMS).
This means that SQL code written for one database system can be easily ported to another database system without major
modifications.
7. Scalability: SQL is designed to handle large amounts of data and to scale horizontally as the amount of data grows. SQL
databases can be distributed across multiple servers and can be scaled up or down as needed to meet the demands of an
application.

8. Performance: SQL is designed to optimize database performance by providing mechanisms for indexing, query optimiza-
tion, and database tuning. These features enable developers to ensure that queries execute quickly and efficiently, even
when working with large data sets.

3
3 Data Definition Commands
Data Definition Language (DDL) commands in SQL are used to define and manage the structure of databases and database
objects such as tables, views, indexes, and constraints. DDL commands are used to create, modify, or drop database objects. In
this answer, we will discuss the syntax and purpose of various DDL commands.
1. CREATE: The CREATE command is used to create new database objects such as tables, views, indexes, and procedures.
The syntax for the CREATE command is as follows:
1 CREATE object_type object_name (
2 column1 datatype1 [ nullable ] ,
3 column2 datatype2 [ nullable ] , ... ,
4 columnN datatypeN [ nullable ]
5 );

• object type is the type of object being created (e.g. TABLE, VIEW, INDEX, PROCEDURE).
• object name is the name of the object being created.
• column1 through columnN are the names of the columns being created in a table or view.
• datatype1 through datatypeN are the data types for each column.
• nullable is an optional keyword that specifies whether a column allows null values or not.

Example:
1 CREATE TABLE Customers (
2 customer_id INT NOT NULL ,
3 first_name VARCHAR (50) ,
4 last_name VARCHAR (50) NOT NULL ,
5 email VARCHAR (100) UNIQUE ,
6 created_date DATE DEFAULT SYSDATE ,
7 PRIMARY KEY ( customer_id )
8 );

2. ALTER: The ALTER command is used to modify the structure of existing database objects such as tables, views, and
indexes. The syntax for the ALTER command is as follows:
1 ALTER object_type object_name action ;

• object type is the type of object being altered (e.g. TABLE, VIEW, INDEX).
• object name is the name of the object being altered.
• action is the modification being made to the object (e.g. ADD COLUMN, DROP COLUMN, MODIFY COLUMN).

Example:
1 ALTER TABLE Customers ADD COLUMN phone_number VARCHAR (20) ;

3. DROP: The DROP command is used to delete existing database objects such as tables, views, indexes, and procedures.
The syntax for the DROP command is as follows:
1 DROP object_type object_name ;

• object type is the type of object being dropped (e.g. TABLE, VIEW, INDEX, PROCEDURE).
• object name is the name of the object being dropped.

Example:
1 DROP TABLE Customers ;

4. TRUNCATE: The TRUNCATE command is used to delete all data from a table. The syntax for the TRUNCATE command
is as follows:

4
 1 TRUNCATE TABLE table_name ;

• table name is the name of the table being truncated.

Example:
1 TRUNCATE TABLE Customers ;

5. COMMENT: The COMMENT command is used to add comments to tables, columns, and other database objects. The
syntax for the COMMENT command is as follows:
1 COMMENT ON object_type object_name IS ’ comment_text ’;

• object type is the type of object being commented on (e.g. TABLE, COLUMN).
• object name is the name of the object being commented on.
• comment text is the text of the comment.

Example:
1 COMMENT ON TABLE Customers IS ’ This table contains information about customers . ’;

6. INDEX: The INDEX command is used to create an index on one or more columns of a table, which helps to improve query
performance. The syntax for the INDEX command is as follows:
1 CREATE [ UNIQUE ] INDEX index_name ON table_name ( column1 [ , column2 , ...]) ;

• UNIQUE is an optional keyword that

4 Data Manipulation Commands

Data manipulation commands in SQL allow users to retrieve, insert, update, and delete data from tables within a database.
Below are some of the most commonly used data manipulation commands in SQL along with their syntax and an explanation
of each segment of their syntax.
• SELECT: The SELECT statement is used to retrieve data from one or more tables in a database. The syntax for SELECT
is as follows:
1 SELECT column1 , column2 , ... FROM table_name WHERE condition ;

The SELECT statement begins with the keyword SELECT, followed by a list of columns to retrieve data from. The column
names are separated by commas. The FROM keyword is used to specify the table or tables to retrieve data from. The
WHERE keyword is used to specify one or more conditions to filter the data.
• INSERT: The INSERT statement is used to insert data into a table in a database. The syntax for INSERT is as follows:
1 INSERT INTO table_name ( column1 , column2 , ...) VALUES ( value1 , value2 , ...) ;

The INSERT statement begins with the keyword INSERT INTO, followed by the name of the table to insert data into.
The column names are enclosed in parentheses and separated by commas. The VALUES keyword is used to specify the
values to insert into the columns. The values are also enclosed in parentheses and separated by commas.
• UPDATE: The UPDATE statement is used to update data in a table in a database. The syntax for UPDATE is as
follows:
1 UPDATE table_name SET column1 = value1 , column2 = value2 , ... WHERE condition ;

The UPDATE statement begins with the keyword UPDATE, followed by the name of the table to update data in. The
SET keyword is used to specify the columns to update and their new values. The column names are followed by an equal
sign and the new value to set. The WHERE keyword is used to specify one or more conditions to filter the data to be
updated.

5
 • DELETE: The DELETE statement is used to delete data from a table in a database. The syntax for DELETE is as
follows:
1 DELETE FROM table_name WHERE condition ;

The DELETE statement begins with the keyword DELETE FROM, followed by the name of the table to delete data from.
The WHERE keyword is used to specify one or more conditions to filter the data to be deleted.
• TRUNCATE: The TRUNCATE statement is used to remove all data from a table in a database. The syntax for
TRUNCATE is as follows:
1 TRUNCATE TABLE table_name ;

The TRUNCATE statement begins with the keyword TRUNCATE TABLE, followed by the name of the table to remove
all data from.
In summary, the syntax for data manipulation commands in SQL can be broken down into several segments, including the
keyword (e.g. SELECT, INSERT, UPDATE), the table name, the column names, the values to insert or update, and the
conditions to filter the data. Understanding these segments is critical to effectively manipulating data in a SQL database.

5 SQL Operators
SQL operators are symbols or keywords used to perform different operations on data stored in a relational database. These
operators are used in SQL queries to retrieve, manipulate, and manage data in tables. SQL operators can be categorized into
several types based on their functionality, including:
1. Arithmetic Operators: Arithmetic operators in SQL are used to perform various mathematical operations on different
numeric values, such as calculating totals, averages, and percentages. They can also be used in conjunction with other
operators and functions to perform more complex calculations. These operators have the Syntax: operand1 operator
operand2
These operators include:
• Addition (+): Adds two values together. Syntax: operand1 + operand2.
For example, to add two values together: SELECT 5 + 7;
This will return the value 12.
• Subtraction (−): Subtracts one value from another. Syntax: operand1 - operand2
• Multiplication (∗): Multiplies two values together. Syntax: operand1 * operand2
• Division (/): Divides one value by another. Syntax: operand1 / operand2
• Modulo (%): Returns the remainder of a division operation.
2. Comparison Operators: Comparison operators in SQL are used to compare two values and return a boolean result of
either TRUE or FALSE.
Syntax: operand1 operator operand2
These operators include:
• Equal to (=): Checks whether two values are equal.
• Not equal to (!= or <>): Checks whether two values are not equal.
• Greater than (> or >=): Checks whether one value is greater than the other.
• Less than (< or <=): Checks whether one value is less than the other.
For example, to compare two values:
1 SELECT * FROM employees WHERE salary > 50000;

This will return all employees whose salary is greater than 50000.
3. Logical Operators: Logical operators are used to combine two or more conditions in SQL. Syntax: condition1 operator
condition2. These operators include:
• AND: Returns true if both conditions are true.
• OR: Returns true if either of the conditions is true.

6
 • NOT: Reverses the logical value of the condition.
For example, to use the AND operator:
1 SELECT * FROM employees WHERE salary > 50000 AND department = ’ IT ’;

This will return all employees whose salary is greater than 50000 and who work in the IT department.
4. Assignment Operators: Assignment operators are used to assign values to variables in SQL. Syntax: variable
operator value. These operators include:
• =: Assigns a value to a variable.
• +=: Adds a value to a variable.
• -=: Subtracts a value from a variable.
• *=: Multiplies a variable by a value.
• /=: Divides a variable by a value.
For example, to assign a value to a variable:
1 SET @myVar = ’ Hello , world ! ’;

This will assign the value ’Hello, world!’ to the variable @myVar.
5. Concatenation Operator: The concatenation operator is used to combine two or more strings in SQL.
Syntax: string1 || string2
• ||: Concatenates two or more strings.
For example, to concatenate two strings:
1 SELECT ’ Hello , ’ || ’ world ! ’;

This will return the string ’Hello, world!’.

In conclusion, SQL operators are an essential component of SQL, as they allow you to perform different operations on data
stored in tables. By understanding the syntax and functionality of these operators, you can use them effectively to manipulate
and manage data in a relational database.

6 Update Statements
In SQL, UPDATE statements are used to modify data in a table. They allow you to change one or more values in one or more
rows of a table based on specified conditions. The syntax for an UPDATE statement is as follows:
1 UPDATE table_name
2 SET column1 = value1 , column2 = value2 , ...
3 WHERE condition ;

Explanation of the UPDATE statement syntax:

• The UPDATE keyword is used to indicate that you want to update data in the table.
• The table name is the name of the table that you want to update.
• The SET keyword is followed by a comma-separated list of column names and their new values. These values will replace
the existing values in the table.
• The WHERE clause is optional and is used to specify the condition(s) that the rows must meet in order to be updated.
For example, let’s say we have a table named employees with columns employee id, first name, last name, department,
and salary. To update the salary of employees who work in the marketing department, we can use the following UPDATE
statement:
1 UPDATE employees
2 SET salary = salary * 1.1
3 WHERE department = ’ Marketing ’;

This will increase the salary of all employees in the marketing department by 10%.

7
 7 Views in SQL
Views in SQL are virtual tables that are based on the result of a SELECT statement. They do not store any data themselves
but provide a way to simplify complex queries or to provide controlled access to certain data. The syntax for creating a view is
as follows:
1 CREATE VIEW view_name AS
2 SELECT column1 , column2 , ...
3 FROM table_name
4 WHERE condition ;

Explanation of the VIEW syntax:

• The CREATE VIEW statement is used to create a new view.
• The view name is the name of the view that you want to create.
• The AS keyword is followed by a SELECT statement, which specifies the columns and rows that should be included in the
view.
• The FROM keyword specifies the name of the table that the view is based on.
• The WHERE clause is optional and is used to specify the conditions that the rows must meet in order to be included in the
view.

For example, let’s say we have a table named orders with columns order id, customer id, order date, and total amount.
We can create a view that shows only the orders that were placed in the last month using the following CREATE VIEW
statement:
1 CREATE VIEW la st_mon th_ord ers AS
2 SELECT *
3 FROM orders
4 WHERE order_date >= DATEADD ( month , -1 , GETDATE () ) ;

This view will only show the orders that were placed in the last month, making it easier to analyze recent data without having
to filter the table every time we run a query.

8 Query & Sub query

In SQL, a query is a request for data from a database. It is used to retrieve, filter, and sort data based on specific criteria. A
subquery, on the other hand, is a query that is nested within another query. It is used to retrieve data that will be used in the
main query.
Here is an example of a simple query:
1 SELECT * FROM customers WHERE city = ’ New York ’;

This query retrieves all the customers from the ”customers” table where the city is ”New York”.
Now let’s see an example of a subquery:
1 SELECT * FROM orders WHERE customer_id IN ( SELECT customer_id FROM customers WHERE city
= ’ New York ’) ;

This query retrieves all the orders from the ”orders” table where the customer’s city is ”New York”. The subquery is used to
retrieve the customer IDs of all the customers in New York, which is then used in the main query to retrieve the orders made
by those customers.
Subqueries can be used in different parts of a SQL statement, such as in the SELECT, FROM, WHERE, and HAVING
clauses. They can also be nested to several levels to retrieve more complex data.

9 Overview of Tuple and Domain Oriented Relational Calculus

In the context of relational databases and SQL, Tuple-Oriented Calculus (TRC) and Domain-Oriented Relational Calculus (DRC)
are not actual query languages but rather theoretical concepts used to describe how queries can be formulated and expressed in
a more abstract and declarative manner.

8
 9.1 Tuple-Oriented Calculus (TRC):
• TRC is a formal notation that focuses on specifying the conditions that desired tuples or rows in a relational database
must satisfy.

• It is part of the broader relational algebra and calculus framework used to query relational databases.
• TRC abstracts away from the specifics of how data is retrieved and instead emphasizes what data should be retrieved.
• TRC queries are expressed using a notation that emphasizes the selection criteria for tuples (rows) in a table.
• TRC is not commonly used in practice as a query language but is more of a theoretical concept used for database query
language research and understanding.

Example of TRC Query:

1 { t | t is a tuple in the " employees " table and t . salary > 50000}

In this TRC query, we are expressing the condition that we want tuples from the ”employees” table where the salary is greater
than $50,000. It emphasizes the ”what” (the selection criteria) rather than the ”how” (the steps to retrieve the data).

9.2 Domain-Oriented Relational Calculus (DRC):

• DRC, like TRC, is a theoretical concept used to describe an approach to formulating queries in a relational database.

• DRC, however, focuses on specifying conditions on individual attributes or domains (columns) rather than on tuples.
• It allows for expressing queries in terms of what should be retrieved based on conditions applied to specific attributes.
• DRC is also not commonly used in practical SQL implementations but is used in database theory and research.

Example of DRC Query:

1 { t . first_name | t is a tuple in the " employees " table and t . salary > 50000}

In this DRC query, we are specifying that we want the first names of employees whose salary exceeds $50,000. DRC lets us
express conditions on specific attributes, providing a more granular level of control over query criteria.
In summary, TRC and DRC are theoretical concepts used to describe different approaches to formulating queries in relational
databases. While they emphasize the declarative nature of queries and what data should be retrieved, they are not practical
query languages used in everyday SQL database operations. SQL itself is the dominant query language for relational databases
and is used to specify both ”what” data should be retrieved and ”how” it should be retrieved.