Database Management Systems-CO222
R V R & J C College of Engineering
Department of CSE-IoT
Prof. N.Nagamalleswara Rao
Department of CSE-IoT
R V R & J C College of Engg.
3 Credit Lecture Note 02
Unit-I Syllabus
2
UNIT – II CO2 15
Periods
Formal Relational Query Languages - The
Relational Algebra and Relational Calculus
SQL: Overview of the SQL Query Language,
SQL Data Definition, Basic Structure of SQL
Queries, Additional Basic Operations, Set
Operations, Null Values, Aggregate Functions,
Nested Subqueries, Modification of the
Database, Join Expressions, Views, Transaction,
Integrity Constraints, SQL Data Types and
Schemas, Authorization
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
3
What is SQL?
SQL is Structured Query Language, which is a
computer language for storing, manipulating
and retrieving data stored in relational
database.
SQL is the standard language for Relation
Database System.
All relational database management systems
like MySQL, MS Access, Oracle, Sybase,
Informix, postgres and SQL Server use SQL as
standard database language.
Also, they are using different dialects, such as:
MS SQL Server using T-SQL,
Oracle using PL/SQL,
Department Computer Science and Engineering-IoT
MS Access version of SQL is called JET SQL (native
Overview of the SQL Query Language
4
Why SQL?
• Allows users to access data in relational
database management systems.
• Allows users to describe the data.
• Allows users to define the data in database and
manipulate that data.
• Allows to embed within other languages using
SQL modules, libraries & pre-compilers.
• Allows users to create and drop databases and
tables.
• Allows users to create view, stored procedure,
functions in a database.
• Allows users to set permissions on tables,
procedures, andComputer
Department viewsScience and Engineering-IoT
Overview of the SQL Query Language
5
History:
• 1970 -- Dr. Edgar F. "Ted" Codd of IBM is known as
the father of relational databases. He described a
relational model for databases.
• 1974 -- Structured Query Language appeared.
• 1978 -- IBM worked to develop Codd's ideas and
released a product named System/R.
• 1986 -- IBM developed the first prototype of
relational database and standardized by ANSI. The
first relational database was released by Relational
Software and its later becoming Oracle.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
6
SQL Process:
When you are executing an SQL command for
any RDBMS, the system determines the best
way to carry out your request and SQL engine
figures out how to interpret the task.
There are various components included in the
process.
These components are Query Dispatcher,
Optimization Engines, Classic Query Engine
and SQL Query Engine, etc.
Classic query engine handles all non-SQL
queries but SQL query engine won't handle
logical files.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
7
SQL Architecture:
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
8
SQL Commands:
The standard SQL commands to interact with
relational databases are CREATE, SELECT,
INSERT, UPDATE, DELETE and DROP.
These commands can be classified into groups
based on their nature:
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
9
DDL - Data Definition Language:
Comma Description
nd
CREATE Creates a new table, a view of a
table, or other object in database
ALTER Modifies an existing database object,
such as a table.
DROP Deletes an entire table, a view of a
table or other object in the database.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
10
DML - Data Manipulation Language:
Comma Description
nd
SELECT Retrieves certain records from one
or more tables
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
11
DCL - Data Control Language:
Comma Description
nd
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from
user
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
12
What is RDBMS?
RDBMS stands for Relational Database
Management System.
RDBMS is the basis for SQL and for all modern
database systems like MS SQL Server, IBM
DB2, Oracle, MySQL, and Microsoft Access.
A Relational database management system
(RDBMS) is a database management system
(DBMS) that is based on the relational model as
introduced by E. F. Codd.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
13
What is table ?
The data in RDBMS is stored in database
objects called tables.
The table is a collection of related data entries
and it consists of columns and rows.
Remember, a table is the most common and
simplest form of data storage in a relational
database.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
14
What is field?
Every table is broken up into smaller entities
called fields. The fields in the CUSTOMERS
table consist of ID, NAME, AGE, ADDRESS and
SALARY.
A field is a column in a table that is designed to
maintain specific information about every
record in the table.
What is record or row?
A record, also called a row of data, is each
individual entry that exists in a table.
For example, there are 7 records in the above
CUSTOMERS table.
Department Computer Science and Engineering-IoT
A record is a horizontal entity in a table.
Overview of the SQL Query Language
15
What is column?
A column is a vertical entity in a table that
contains all information associated with a
specific field in a table.
What is NULL value?
A NULL value in a table is a value in a field that
appears to be blank which means A field with a
NULL value is a field with no value.
It is very important to understand that a NULL
value is different than a zero value or a field
that contains spaces.
A field with a NULL value is one that has been
left blank duringComputer
Department record creation.
Science and Engineering-IoT
Overview of the SQL Query Language
16
SQL Constraints:
Constraints are the rules enforced on data
columns on table.
These 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 database.
Constraints could be column level or table
level.
Column level constraints are applied only to
one column where as table level constraints
are applied to the whole table.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
17
SQL Syntax:
SQL is followed by unique set of rules and
guidelines called Syntax.
All the SQL statements start with any of the
keywords like SELECT, INSERT, UPDATE,
DELETE, ALTER, DROP, CREATE, USE, SHOW
and all the statements end with a semicolon
(;).
Important point to be noted is that SQL is case
insensitive which means SELECT and select
have same meaning in SQL statements but
MySQL make difference in table names.
So if you are working with MySQL then you
need toDepartment
give table names
Computer as Engineering-IoT
Science and they exist in the
database.
Overview of the SQL Query Language
18
SQL SELECT Statement:
SELECT column1,
column2....columnN
FROM table_name;
SQL DISTINCT Clause:
SELECT DISTINCT column1,
column2....columnN
FROM table_name;
SQL WHERE Clause:
SELECT column1,
column2....columnN
FROM table_name
WHERE CONDITION;
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
19
SQL AND/OR Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR}
CONDITION-2;
SQL IN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-
N);
SQL BETWEEN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND
val-2;
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
20
SQL Like Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQL ORDER BY Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SQL GROUP BY Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
21
SQL COUNT Clause:
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;
SQL HAVING Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
22
SQL CREATE TABLE Statement:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
23
SQL DROP TABLE Statement:
DROP TABLE table_name;
SQL CREATE INDEX Statement:
CREATE UNIQUE INDEX index_name
ON table_name ( column1,
column2,...columnN);
SQL DROP INDEX Statement:
ALTER TABLE table_name
DROP INDEX index_name;
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
24
SQL DESC Statement:
DESC table_name;
SQL TRUNCATE TABLE Statement:
TRUNCATE TABLE table_name;
SQL ALTER TABLE Statement:
ALTER TABLE table_name {ADD|DROP|MODIFY}
column_name {data_ype};
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
25
SQL ALTER TABLE Statement (Rename):
ALTER TABLE table_name RENAME TO
new_table_name;
SQL INSERT INTO Statement:
INSERT INTO table_name( column1,
column2....columnN)
VALUES ( value1, value2....valueN);
SQL UPDATE Statement:
UPDATE table_name
SET column1 = value1, column2 =
value2....columnN=valueN
[ WHERE CONDITION ];
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
26
SQL DELETE Statement:
DELETE FROM table_name
WHERE {CONDITION};
SQL CREATE DATABASE Statement:
CREATE DATABASE database_name;
SQL DROP DATABASE Statement:
DROP DATABASE database_name;
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
27
SQL USE Statement:
USE DATABASE database_name;
SQL COMMIT Statement:
COMMIT;
SQL ROLLBACK Statement:
ROLLBACK;
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
28
SQL - Operators: SQL Arithmetic Operators:
Assume variable a holds 10 and variable b holds
20, then:
Opera Description Exampl
tor e
+ Addition-Adds values on either side of a + b will
the operator give 30
- Subtraction - Subtracts right hand a - b will
operand from left hand operand give -10
* Multiplication - Multiplies values on a * b will
either side of the operator give 200
/ Division - Divides left hand operand by b / a will
right hand operand give 2
% Modulus - Divides left hand operand by b%a
right hand operand and returns will give
Department Computer Science and Engineering-IoT 0
remainder
Overview of the SQL Query Language
29
SQL Comparison Operators
Assume variable a holds 10 and variable b holds
20, then:
Opera Description Exampl
tor e
= Checks if the values of two operands are (a = b) is
equal or not, if yes then condition not true.
becomes true.
!= Checks if the values of two operands are (a != b)
equal or not, if values are not equal then is true
condition becomes true.
<> Checks if the values of two operands are (a <> b)
equal or not, if values are not equal then is true.
condition becomes true.
> Checks if the value of left operand is (a > b) is
greater than the value of right operand, not true.
Department
if yes Computer Science
then condition becomes and Engineering-IoT
true.
Overview of the SQL Query Language
30
SQL Comparison Operators
Assume variable a holds 10 and variable b holds
20, then:
Opera Description Exampl
tor e
< Checks if the value of left operand is less (a < b) is
than the value of right operand, if yes true.
then condition becomes true.
>= Checks if the value of left operand is (a >= b)
greater than or equal to the value of is not
right operand, if yes then condition true.
becomes true.
<= Checks if the value of left operand is less (a <= b)
than or equal to the value of right is true.
operand, if yes then condition becomes
true.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
31
SQL Comparison Operators
Assume variable a holds 10 and variable b holds
20, then:
Opera Description Exampl
tor e
!< Checks if the value of left operand is not (a !< b)
less than the value of right operand, if is false.
yes then condition becomes true.
!> Checks if the value of left operand is not (a !> b)
greater than the value of right operand, is true.
if yes then condition becomes true.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
32
SQL Logical Operators
Operat Description
or
ALL The ALL operator is used to compare a value to all
values in another value set.
AND The AND operator allows the existence of multiple
conditions in an SQL statement's WHERE clause.
ANY The ANY operator is used to compare a value to
any applicable value in the list according to the
condition.
BETWE The BETWEEN operator is used to search for values
EN that are within a set of values, given the minimum
value and the maximum value.
EXISTS The EXISTS operator is used to search for the
presence of a
Department row inScience
Computer a specified table that meets
and Engineering-IoT
certain criteria.
Overview of the SQL Query Language
33
SQL Logical Operators
Operat Description
or
IN The IN operator is used to compare a value to a list
of literal values that have been specified.
LIKE The LIKE operator is used to compare a value to
similar values using wildcard operators.
NOT The NOT operator reverses the meaning of the
logical operator with which it is used. Eg: NOT
EXISTS, NOT BETWEEN, NOT IN, etc. This is a
negate operator.
Department Computer Science and Engineering-IoT
Overview of the SQL Query Language
34
SQL Logical Operators
Operat Description
or
OR The OR operator is used to combine multiple
conditions in an SQL statement's WHERE clause.
IS NULL The NULL operator is used to compare a value
with a NULL value.
UNIQUE The UNIQUE operator searches every row of a
specified table for uniqueness (no duplicates).
Department Computer Science and Engineering-IoT
35
