DATABASE MANAGEMENT SYSTEMS SQL
DATABASE MANAGEMENT SYSTEMS
MODULE III
CHAPTER 2
SQL
Data Definition and Data types
Schema change statements in SQL
Specifying constraints in SQL
INSERT, DELETE and UPDATE in SQL
Retrieval Queries in SQL
Additional Features of SQL
Introduction to SQL:
SQL is a standard language for storing, manipulating and retrieving data in databases.
What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL became a standard of the American National Standards Institute (ANSI) in
1986, and of the International Organization for Standardization (ISO) in 1987
What Can SQL do?
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
Dr. Nagashree N. Dept of CSE(Data Science) 1
DATABASE MANAGEMENT SYSTEMS SQL
Why SQL?
Nowadays, SQL is widely used in data science and analytics. Following are the
reasons which explain why it is widely used:
o The basic use of SQL for data professionals and SQL users is to insert, update,
and delete the data from the relational database.
o SQL allows the data professionals and users to retrieve the data from the
relational database management systems.
o It also helps them to describe the structured data.
o It allows SQL users to create, drop, and manipulate the database and its
tables.
o It also helps in creating the view, stored procedure, and functions in the
relational database.
o It allows you to define the data and modify that stored data in the
relational database.
o It also allows SQL users to set the permissions or constraints on table
columns, views, and stored procedures.
History of SQL
"A Relational Model of Data for Large Shared Data Banks" was a paper which was
published by the great computer scientist "E.F. Codd" in 1970.
The IBM researchers Raymond Boyce and Donald Chamberlin originally developed
the SEQUEL (Structured English Query Language) after learning from the paper given
by E.F. Codd. They both developed the SQL at the San Jose Research laboratory of
IBM Corporation in 1970.
Dr. Nagashree N. Dept of CSE(Data Science) 2
DATABASE MANAGEMENT SYSTEMS SQL
SQL Commands
o SQL commands are instructions. It is used to communicate with the database.
It is also used to perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the
table, modify the table, set permission for users.
Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
1. Data Definition Language (DDL)
o DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc.
o All the command of DDL are auto-committed that means it permanently save
all the changes in the database.
Here are some commands that come under DDL:
o CREATE
Dr. Nagashree N. Dept of CSE(Data Science) 3
DATABASE MANAGEMENT SYSTEMS SQL
o ALTER
o DROP
o TRUNCATE
2. Data Manipulation Language
o DML commands are used to modify the database. It is responsible for all form
of changes in the database.
o The command of DML is not auto-committed that means it can't permanently
save all the changes in the database. They can be rollback.
Here are some commands that come under DML:
o INSERT
o UPDATE
o DELETE
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:
o Grant
o Revoke
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 creating tables or dropping them.
Here are some commands that come under TCL:
o COMMIT
o ROLLBACK
o SAVEPOINT
5. Data Query Language
Dr. Nagashree N. Dept of CSE(Data Science) 4
DATABASE MANAGEMENT SYSTEMS SQL
DQL is used to fetch the data from the database.
It uses only one command:
o SELECT
SQL Data Types
Data types are used to represent the nature of the data that can be stored in the
database table. For example, in a particular column of a table, if we want to store a
string type of data then we will have to declare a string data type of this column.
Data types mainly classified into three categories for every database.
o String Data types
o Numeric Data types
o Date and time Data types
Managing Schema and Database in SQL:
Dr. Nagashree N. Dept of CSE(Data Science) 5
DATABASE MANAGEMENT SYSTEMS SQL
Specifying constraints in SQL
Dr. Nagashree N. Dept of CSE(Data Science) 6
DATABASE MANAGEMENT SYSTEMS SQL
SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. This ensures
the accuracy and reliability of the data in the table. If there is any violation between
the constraint and the data action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to a
column, and table level constraints apply to the whole table.
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each
row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly
Examples on Constraints:
Dr. Nagashree N. Dept of CSE(Data Science) 7
DATABASE MANAGEMENT SYSTEMS SQL
Data Manipulation Statements:
Dr. Nagashree N. Dept of CSE(Data Science) 8
DATABASE MANAGEMENT SYSTEMS SQL
INSERT, DELETE and UPDATE in SQL
In the world of relational databases, data manipulation is a crucial aspect of
managing and maintaining the integrity of the data stored in tables. SQL (Structured
Query Language) provides powerful statements for inserting, updating, and deleting
data from database tables.
we will explore the INSERT, UPDATE, and DELETE statements in SQL, accompanied by
practical examples to illustrate their usage. Let’s dive into the world of data
manipulation with SQL!
Understanding the Data Manipulation Operations
SQL supports three primary data manipulation operations:
1. INSERT: This operation allows you to add new records or rows to a
table.
2. UPDATE: The UPDATE operation enables you to modify existing records
in a table.
3. DELETE: The DELETE operation allows you to remove records from a
table.
The INSERT Statement
The INSERT statement is used to add new data into a table. It allows you to specify
the columns to which you want to insert data, as well as the values for each column.
The basic syntax for the INSERT statement is as follows:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Here’s a brief explanation of each part of the INSERT statement:
INSERT INTO: This clause indicates that you want to insert data into
a table.
table_name: This is the name of the table to which you want to add
data.
Dr. Nagashree N. Dept of CSE(Data Science) 9
DATABASE MANAGEMENT SYSTEMS SQL
(column1, column2, column3, ...): These are the names of the
columns in which you want to insert data. If you're inserting data into
all columns, you can omit this part.
VALUES: This keyword is used to specify the values you want to
insert into the columns.
(value1, value2, value3, ...): These are the values that
correspond to the columns specified. The values must match the data
types of the columns.
Example 1: Inserting a Single Row into a Table
Suppose we have a table called employees, which stores information about
employees in a company. To insert a new employee record, we can use the following
SQL query:
INSERT INTO employees (employee_id, first_name, last_name,
hire_date, salary)
VALUES (101, 'John', 'Doe', '2023-07-15', 60000);
In this example, we provided specific values for
the employee_id, first_name, last_name, hire_date, and salary columns to insert a
new employee record.
Example 2: Inserting Multiple Rows into a Table
You can also use the INSERT statement to insert multiple rows into a table with a
single query. For example:
Dr. Nagashree N. Dept of CSE(Data Science) 10
DATABASE MANAGEMENT SYSTEMS SQL
INSERT INTO employees (employee_id, first_name, last_name,
hire_date, salary)
VALUES (102, 'Jane', 'Smith', '2023-07-20', 55000),
(103, 'Michael', 'Johnson', '2023-07-25', 65000),
(104, 'Emily', 'Brown', '2023-08-01', 58000);
In this example, we inserted three new employee records into the employees table
with one INSERT statement.
The UPDATE Statement
The UPDATE statement is used to modify existing records in a table. It allows you to
change the values of specific columns based on certain conditions. The basic syntax
for the UPDATE statement is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Here’s a brief explanation of each part of the UPDATE statement:
UPDATE: This clause indicates that you want to update data in a
table.
table_name: This is the name of the table from which you want to
update data.
Dr. Nagashree N. Dept of CSE(Data Science) 11
DATABASE MANAGEMENT SYSTEMS SQL
SET: This keyword is used to specify the columns and their new
values.
column1 = value1, column2 = value2, ...: These are the
columns you want to update along with their new values.
WHERE: This keyword is used to specify the condition that
determines which rows to update. If you omit the WHERE clause, all
rows in the table will be updated.
Example: Suppose we want to update the salary of employee
with employee_id 101 to $65000. We can use the following SQL query:
UPDATE employees
SET salary = 65000
WHERE employee_id = 101;
In this example, we used the WHERE clause to specify the condition that
only the row with employee_id 101 should be updated.
The DELETE Statement
The DELETE statement is used to remove records from a table based on certain
conditions. It allows you to specify which rows you want to delete. The basic syntax
for the DELETE statement is as follows:
Dr. Nagashree N. Dept of CSE(Data Science) 12
DATABASE MANAGEMENT SYSTEMS SQL
DELETE FROM table_name
WHERE condition;
Here’s a brief explanation of each part of the DELETE statement:
DELETE FROM: This clause indicates that you want to delete data
from a table.
table_name: This is the name of the table from which you want to
delete data.
WHERE: This keyword is used to specify the condition that
determines which rows to delete. If you omit the WHERE clause, all
rows in the table will be deleted.
Example1:Suppose we want to delete the record of the employee
with employee_id 104 from the employees table:
DELETE FROM employees
WHERE employee_id = 104;
Example2: You can also delete multiple rows that match a specific
condition. For example, let’s delete all employees with a salary lower than
$55000:
Dr. Nagashree N. Dept of CSE(Data Science) 13
DATABASE MANAGEMENT SYSTEMS SQL
DELETE FROM employees
WHERE salary < 55000;
Basic Retrieval Queries in SQL
To create a basic SQL query for data retrieval, use the SELECT statement, specifying
the columns you want to retrieve and the table from which you want to retrieve the
data. Optionally, you can include a WHERE clause to filter the data based on specific
conditions.
SELECT Statement in SQL
The SELECT statement in SQL is used to fetch or retrieve data from a database. It
allows users to access the data and retrieve specific data based on specific
conditions.
We can fetch either the entire table or according to some specified rules. The data
returned is stored in a result table. This result table is also called the result set. With
the SELECT clause of a SELECT command statement, we specify the columns that we
want to be displayed in the query result and, optionally, which column headings we
prefer to see above the result table.
The SELECT clause is the first clause and is one of the last clauses of the select
statement that the database server evaluates. The reason for this is that before we
can determine what to include in the final result set, we need to know all of the
possible columns that could be included in the final result set.
Syntax
The syntax for the SELECT statement is:
SELECT column1,column2…. FROM table_name ;
Dr. Nagashree N. Dept of CSE(Data Science) 14
DATABASE MANAGEMENT SYSTEMS SQL
SELECT Statement Example
Let’s look at some examples of the SQL SELECT statement,
to understand it better.
Let’s create a table which will be used in examples:
CREATE TABLE:
Retrieve Data using SELECT Query
Fetch All Table using SELECT Statement
In this example, we will fetch all the fields from the table
Customer:
Query:
Dr. Nagashree N. Dept of CSE(Data Science) 15
DATABASE MANAGEMENT SYSTEMS SQL
SELECT * FROM Customer;
we will fetch CustomerName, LastName from the table
Customer:
Query:
SELECT CustomerName, LastName FROM Customer;
SELECT Statement with WHERE Clause
Suppose we want to see table values with specific conditions then WHERE Clause is
used with select statement.
Query:
SELECT CustomerName FROM Customer where Age = '21';
Dr. Nagashree N. Dept of CSE(Data Science) 16
DATABASE MANAGEMENT SYSTEMS SQL
Features of SQL
Data modification with ease.
Data integrity and constraints.
Joining tables for comprehensive insights.
Aggregation and grouping.
Sorting data for analysis.
Subqueries for complex queries.
Data security and access control.
Transaction management.
Dr. Nagashree N. Dept of CSE(Data Science) 17
