DEPARTMENT OF CSE
DATA BASE MANAGEMENT SYSTEMS
24AD2104R
TOPIC
SQL INTRODUCTION
AIM OF THE SESSION
To familiarize students with the basic concept
SQL Introduction
INSTRUCTIONAL
OBJECTIVES
This Session is designed to:
SQL Introduction
LEARNING OUTCOMES
At the end of this session, you should be able to:
Introduction of SQL.
Structural Query Language
14
QUERY & SUB-QUERY
A DB query is a code written in order to get the information back from the database.
Query can be designed in such a way that it matched with our expectation of the result
set. Simply,
a question to the Database.
A sub-query is a query within another query. The outer query is called as main query,
and inner
query is called sub-query.
Sub-query is always executed first, and the result of sub-query is passed on to the main
query.
There are two types of sub-query – Correlated and Non-Correlated.
A correlated sub-query cannot be considered as independent query, but it can
refer the column in a table listed in the FROM the list of the main query.
SQL
Structured Query Language
SQL is a language which is used to create & operate database.
SQL is the basic language used for all the databases.
SQL can be used by both casual users as well as skilled programmer.
There are minor syntax changes amongst different databases, but the
basic SQL syntax remains largely the same.
According to ANSI (American National Standards Institute), SQL is the
standard language to operate a relational database management system.
SQL is used in the accessing, updating, and manipulation of data in a database. Its
design allows for the management of data in an RDBMS, such as MYSQL and
PostgreSQL.
16
PROCESSING CAPABILITIES OF SQL
1. DATA DEFINITION LANGUAGE(DDL)
The SQL DDL provides commands for defining relation schemas, deleting
relations, creating indexes and modifying relation schemas .
2.INTERACTIVE DATA MANIPULATION LANGUAGE(DML)
The SQL DML includes a query language based on both
relational algebra and the tuple relational calculus. It includes
the commands to insert, delete and modify tuples in the data
base.
17
PROCESSING CAPABILITIES OF SQL
3. Embedded SQL
It is a method of combining the computing power of a programming
language and the database manipulation capabilities of SQL.
4.View Definition
The SQL DDL also includes commands for defining views.(Views are the
virtual tables that does not really exist in its own but is derived from one or
more base table(s)).
5.Authorization
The SQL DDL also includes commands for specifying access rights to relation and
18
views.
PROCESSING CAPABILITIES OF SQL
6.Integrity
The SQL provides limited forms of integrity checking. Future
products
and standards of SQL are likely to include enhanced features
for
integrity checking.
7.Transaction Control
SQL includes commands for specifying the beginning
19
and
ending of transactions along with the commands to
DATA DEFINITION LANGUAGE(DDL)
A set of definitions which are expressed by a special
language.
An ideal DDL should perform the following
functions :
1. Identify the types of data
2. Give a unique name
3. Specify the proper data types
4. Specify how to record types are related to make
structures.
5. Define the type of encoding the program uses in the
data items.
6. Define the length of data item.
7. Define the range of the values that a data item can
Data assume
Dictionary
8. Specify means of checking for errors in the data.
Data dictionary is a file that contains metadata i.e. data about
9. Specify privacy locks.
data.
10. Should not specify addressing, indexingor specify or
searching techniques or specify the storage units.
DATA MANIPULATION LANGUAGE (DML)
Data manipulation language (DML) is a language that enables users to
access or manipulate data as organized by the appropriate data
model.
By data manipulation we mean:
1. Retrieval
2. Insertion
3. Deletion
4. Modification
TYPES OF DATA MANIPULATION LANGUAGE (DML)
DML’s are Basically of two types
1)Procedural DML
It requires a user to specify what data is needed and how to get
it.
2) Non-procedural DML
It requires a user to specify what data is needed without
specifying how to get it.
1
1
SQL PROCESSING
1. SQL is a language oriented specifically around relational
databases.
2. The SQL command can operate on entire groups of tables as a
single object and can treat any quantity of information extracted
or derived from them as a single unit as well.
1
2
CONCEPT OF BASIC DATATYPE
Class Data Type
Text CHAR( or, CHARACTER)
Exact Numeric DEC( or, DECIMAL)
NUMERIC
INT(or, INTEGER)
SMALLINT
Approximate Numeric FLOAT
REAL
DOUBLE(or, DOUBLE
PRECISION)
Date & Time
Currency
1
3
DATE &
TIMEDate Time
Standard Full Form Format Example Format Example
ISO Internation yyyy-mm-dd 2020-03-15 hh-mm-ss 23-13-41
al
Organizati
on for
Standard
JIS Japanese yyyy-mm-dd 2020-03-15 hh-mm-ss 23-13-41
TIM
Industria E
l
Standard
s
EUR IBM dd.mm.yyyy 15.03.2020 hh.mm.ss 23.13.41
European
Standard
DATE & TIME can be added subtracted or
USA IBM USA
compared mm/dd/yyyy 03/15/2020 hh.m 11.13 PM
Standard m
AM/P
M
VARIOUS SQL COMMANDS AND
FUNCTIONS
1. Keywords : The words that have a special meaning. SQL
keywords have been printed in capital letters.
e.g. NULL, UNIQUE, DEFAULT, CHECK, …etc.
2. Commands or Statements : Instructions to a SQL database
3. Clauses : Commands consist of one or more logically distinct parts
4. Arguments : Modify the meaning of a clause
5. Objects : Structures in the database that are given names and stored in
memory. They include base tables, views & indexes 1
5
SYMBOLS USED IN SYNTAX OF
STATEMENT
Symbols Meaning
| or
{} treated as a unit
[ ] everything enclosed in it is
optional
… repeated any number of times
…. repeated any number of times
with the individual occurrences
separated by commas
<> SQL and other special terms
1
are in angle brackets 6
RELATIONAL
OPERATORS
Symbols Meaning
= equal to
> greater than
< less than
>= greater equal to
<= less equal to
<> not equal to
1
7
ACTIVITIES/ CASE STUDIES/ IMPORTANT FACTS RELATED
TO THE SESSION
The relational model in DBMS is an abstract model used
to organize and manage the data stored in a database. It
stores data in two-dimensional inter-related tables, also
known as relations in which each row represents an entity
and each column represents the properties of the entity.
SQL stands for Structured Query Language. It is used for
storing and managing data in relational database
management system (RDMS).It is a standard language for
Relational Database System. It enables a user to create,
read, update and delete relational databases and tables.
All the RDBMS like MySQL, Informix, Oracle, MS Access
and SQL Server use SQL as their standard database
language.
SUMMARY
The relation, which is a two-dimensional table, is the primary
unit of storage in a relational database.
A relational database can contain one or more of these tables,
with each table consisting of unique set of rows and columns.
A single record is stored in a table as a row, also known as a
tuple, while attributes of the data are defined in columns or
fields in the table.
Each column has a unique name and the content within it
must be of the same type.
The characteristics of the data, or the column, relates one
record to another.
SELF-ASSESSMENT QUESTIONS
1. What does SQL stand for?
•A. Structured Query Language
B. Simple Query Language
C. Sequential Query Language
D. Server Query Language
Answer: A
2. Which SQL statement is used to retrieve data from a database?
D. A. GET
B. SELECT
C. FETCH
D. EXTRACT
Answer: B. SELECT
TERMINAL QUESTIONS
1. Describe various data types in SQL.
2. List out the DBMS languages and explain the use of the
languages.
REFERENCES FOR FURTHER LEARNING OF THE
SESSION
Reference Books:
1. Database System Concepts, Sixth Edition, Abraham Silberschatz, Yale
University Henry, F. Korth Lehigh University, S. Sudarshan Indian Institute of
Technology, Bombay.
2. Fundamentals of Database Systems, 7th Edition, RamezElmasri, University of
Texas at Arlington, Shamkant B. Navathe, University of Texas at Arlington.
Sites and Web links:
3. https://nptel.ac.in/courses/106105175
4. https://beginnersbook.com/2018/11
