Outline Preliminaries Data Definition Problems
Fundamentals of Database Systems
[SQL – I]
Malay Bhattacharyya
Assistant Professor
Machine Intelligence Unit
Indian Statistical Institute, Kolkata
December, 2020
Outline Preliminaries Data Definition Problems
1 Preliminaries
2 Data Definition
Principle Structure
Database Creation
Database Modification
3 Problems
Outline Preliminaries Data Definition Problems
Basics of SQL
SQL or structured query language is a special-purpose
programming language designed for managing data held in a
relational database management system (RDBMS). SQL uses a
combination of relational algebra and relational calculus constructs.
Note that, SQL is a declarative (non-procedural) language.
SQL is not only for querying, rather it also helps in defining the
structure of the data, modifying the data and specifying the
security constraints.
Outline Preliminaries Data Definition Problems
Basics of SQL
SQL or structured query language is a special-purpose
programming language designed for managing data held in a
relational database management system (RDBMS). SQL uses a
combination of relational algebra and relational calculus constructs.
Note that, SQL is a declarative (non-procedural) language.
SQL is not only for querying, rather it also helps in defining the
structure of the data, modifying the data and specifying the
security constraints.
Note: The SQL keywords are case-insensitive, however, they are
often written in uppercase. In some setups, table and column
names are case-sensitive.
Outline Preliminaries Data Definition Problems
SQL functionalities
Data-definition language (DDL) – provides commands for
defining relation schemas, deleting relations, and modifying
relation schemas.
Data-manipulation language (DML) – includes commands
to work on attributes, insert tuples into, delete tuples from,
and modify tuples in the database.
View definition – includes commands for defining views.
Transaction control – includes commands for specifying the
beginning and ending of transactions.
Embedded SQL and dynamic SQL – embeds SQL
statements into general-purpose programming languages.
Integrity – includes commands for specifying integrity
constraints that the data stored in the database must satisfy.
Authorization – includes commands for specifying access
rights to relations and views.
Outline Preliminaries Data Definition Problems
History
“An SQL query goes into a bar, walks up to two tables and asks,
‘May I join you?’.” – Anonymous.
1970s: Original version called Sequel, developed as a part of the
System R project, was first implemented by IBM.
Outline Preliminaries Data Definition Problems
History
“An SQL query goes into a bar, walks up to two tables and asks,
‘May I join you?’.” – Anonymous.
1970s: Original version called Sequel, developed as a part of the
System R project, was first implemented by IBM.
1986: American national Standards Institute (ANSI) and
International Organization for Standardization (ISO) published an
SQL standard SQL-86.
Outline Preliminaries Data Definition Problems
History
“An SQL query goes into a bar, walks up to two tables and asks,
‘May I join you?’.” – Anonymous.
1970s: Original version called Sequel, developed as a part of the
System R project, was first implemented by IBM.
1986: American national Standards Institute (ANSI) and
International Organization for Standardization (ISO) published an
SQL standard SQL-86.
1987: IBM published its own corporate SQL standard Systems
Application Architecture Database Interface (SAA-SQL).
Outline Preliminaries Data Definition Problems
History
“An SQL query goes into a bar, walks up to two tables and asks,
‘May I join you?’.” – Anonymous.
1970s: Original version called Sequel, developed as a part of the
System R project, was first implemented by IBM.
1986: American national Standards Institute (ANSI) and
International Organization for Standardization (ISO) published an
SQL standard SQL-86.
1987: IBM published its own corporate SQL standard Systems
Application Architecture Database Interface (SAA-SQL).
1989: ANSI published an extended version SQL-89.
Outline Preliminaries Data Definition Problems
History
“An SQL query goes into a bar, walks up to two tables and asks,
‘May I join you?’.” – Anonymous.
1970s: Original version called Sequel, developed as a part of the
System R project, was first implemented by IBM.
1986: American national Standards Institute (ANSI) and
International Organization for Standardization (ISO) published an
SQL standard SQL-86.
1987: IBM published its own corporate SQL standard Systems
Application Architecture Database Interface (SAA-SQL).
1989: ANSI published an extended version SQL-89.
1992: A major extended version SQL-92 was published.
Outline Preliminaries Data Definition Problems
History
“An SQL query goes into a bar, walks up to two tables and asks,
‘May I join you?’.” – Anonymous.
1970s: Original version called Sequel, developed as a part of the
System R project, was first implemented by IBM.
1986: American national Standards Institute (ANSI) and
International Organization for Standardization (ISO) published an
SQL standard SQL-86.
1987: IBM published its own corporate SQL standard Systems
Application Architecture Database Interface (SAA-SQL).
1989: ANSI published an extended version SQL-89.
1992: A major extended version SQL-92 was published.
1999-2016: The versions SQL:1999, SQL:2003, SQL:2006,
SQL:2008, SQL:2011 and SQL:2016 were published.
Outline Preliminaries Data Definition Problems
Standard conformance of SQL
Significant Features SQL:2008 SQL:2011 SQL:2016
Truncation of table Yes Yes Yes
INSTEAD OF trigger Yes Yes Yes
XQuery regular expression Yes Yes Yes
Partitioned JOIN Yes Yes Yes
System-versioned tables No Yes Yes
Time-sliced & sequenced queries No Yes Yes
Temporal referential integrity No Yes Yes
Temporal primary keys No Yes Yes
Polymorphic table functions No No Yes
Row pattern recognition No No Yes
DECFLOAT data type No No Yes
JSON data type No No Yes
Outline Preliminaries Data Definition Problems
Data view through SQL
In practice, the databases (as a whole) comprises several separate
database and each database consists of several tables.
databases
/ / \ \
database 1 ... database n
/ | \
table 1 ... table m
Note: The MySQL Community Server can be downloaded from
https://dev.mysql.com/downloads/mysql. Online MySQL
compilers are also available through platforms like Rextester
(https://rextester.com/l/mysql online compiler).
Outline Preliminaries Data Definition Problems
Connecting with MySQL
$ mysql -u <user name> -p
Enter password:
mysql>
mysql> show databases;
mysql> connect <database name>;
mysql> show tables;
mysql> desc <table name>;
mysql> exit
$
We will be using the accounts with username/password
root/mysql (DBA) and student/student123 (general user) on
MySQL during the course.
Note: In Oracle SQL, scott and tiger are the general username
and password, respectively. The default password for the DBA is
system.
Outline Preliminaries Data Definition Problems
Principle structure of defining a table
A typical SQL query for data definition appears as follows:
create table R (
A1 D1 , A2 D2 , . . . , Ak Dk ,
(IC1 ), . . . , (ICn )
);
Here, each Ai represents an attribute in the schema of relation R,
each Di denotes the data type of values in the domain of the
corresponding attribute Ai , and ICi symbolizes an integrity
constraint. Some integrity constraints may also appear along with
the data types.
Note: SQL is a freeform language.
Outline Preliminaries Data Definition Problems
The data types in SQL
Outline Preliminaries Data Definition Problems
The data types in MySQL – Numerics
Type Size (bits) Minimum Maximum
tinyint(dig) 8 −27 27 − 1
unsigned tinyint(dig) 8 0 28 − 1
smallint(dig) 16 −215 215 − 1
unsigned smallint(dig) 16 0 216 − 1
mediumint(dig) 24 −223 223 − 1
unsigned mediumint(dig) 24 0 224 − 1
int(dig) 32 −231 231 − 1
unsigned int(dig) 32 0 232 − 1
bigint(dig) 64 −263 263 − 1
unsigned bigint(dig) 64 0 264 − 1
real(dig, dec) 32 −3.40E + 38 3.40E + 38
float(dig, dec) 64 −1.79E + 308 1.79E + 308
decimal(dig, dec) 136 −1038 + 1 1038 − 1
numeric(dig, dec) 136 −1038 + 1 1038 − 1
Note: Total number of digits (before and after the decimal point) and number
of digits only after the decimal point are specified by dig and dec, respectively.
Outline Preliminaries Data Definition Problems
The data types in MySQL – Text
Type Storage Maximum
char(size) Fixed length string 255 characters
varchar(size) Variable length string 255 characters
tinytext(size) Character large object/CLOB 255 characters
text(size) Character large object/CLOB 65,535 characters
mediumtext(size) Character large object/CLOB 16,777,215 characters
longtext(size) Character large object/CLOB 4,294,967,295 characters
blob(size) Binary large object/BLOB 65,535 bytes
mediumblob(size) Binary large object/BLOB 16,777,215 bytes
longblob(size) Binary large object/BLOB 4,294,967,295 bytes
enum(x,y,z,etc.) A list of possible values 65535 values
Note: A string can contain letters, numbers, and special characters. The
maximum number of characters in a string can be specified with size.
Outline Preliminaries Data Definition Problems
Consider a table
Table: IPL
YEAR VENUE WINNER PoS
8 India Rajasthan Royals Shane Watson
9 South Africa Deccan Chargers Adam Gilchrist
10 India Chennai Super Kings Sachin Tendulkar
11 India Chennai Super Kings Chris Gayle
12 India Kolkata Knight Riders Sunil Narine
13 India Mumbai Indians Shane Watson
14 India, UAE Kolkata Knight Riders Glenn Maxwell
15 India Mumbai Indians Andre Russell
16 India Sunrisers Hyderabad Virat Kohli
17 India Mumbai Indians Ben Stokes
18 India Chennai Super Kings Sunil Narine
19 India Mumbai Indians Andre Russell
20 UAE Mumbai Indians Jofra Archer
Outline Preliminaries Data Definition Problems
Creating a table
The IPL table, on which we will be working on further, can be
created with the following SQL query.
create table IPL(
YEAR tinyint(4) not null,
VENUE char(50),
WINNER char(30),
PoS char(30),
primary key (YEAR)
);
Note: The attribute YEAR, which cannot be null, is defined as
the primary key of IPL table.
Outline Preliminaries Data Definition Problems
Table creation with ease
Try this out!!!
SQLizer – Easily convert files into SQL databases
https://sqlizer.io
Outline Preliminaries Data Definition Problems
Deleting a table
The IPL table can be deleted from database using the following
SQL query.
drop table IPL;
Outline Preliminaries Data Definition Problems
Altering a table
The IPL table can be altered by adding a new attribute Ap and
mentioning its domain Dp (data type) as follows. All the existing
tuples will be assigned a null value for the new attribute Ap .
alter table IPL add Ap Dp ;
Outline Preliminaries Data Definition Problems
Altering a table
The IPL table can be altered by adding a new attribute Ap and
mentioning its domain Dp (data type) as follows. All the existing
tuples will be assigned a null value for the new attribute Ap .
alter table IPL add Ap Dp ;
The IPL table can be altered by dropping an existing attribute Ap
as follows.
alter table IPL drop Aq ;
Outline Preliminaries Data Definition Problems
Altering a table
The IPL table can be altered by adding a new attribute Ap and
mentioning its domain Dp (data type) as follows. All the existing
tuples will be assigned a null value for the new attribute Ap .
alter table IPL add Ap Dp ;
The IPL table can be altered by dropping an existing attribute Ap
as follows.
alter table IPL drop Aq ;
Note: New primary keys can also be added/dropped in a similar
way.
Outline Preliminaries Data Definition Problems
Renaming a table and its attributes
The IPL table and its attributes can be renamed and reused, as
and when required, within an SQL query as follows:
select IPL1.PoS from IPL as IPL1, IPL as IPL2 where
IPL1.PoS = IPL2.PoS and IPL1.YEAR > IPL2.YEAR and
IPL2.WINNER = ‘Mumbai Indians’;
Outline Preliminaries Data Definition Problems
Renaming a table and its attributes
The IPL table and its attributes can be renamed and reused, as
and when required, within an SQL query as follows:
select IPL1.PoS from IPL as IPL1, IPL as IPL2 where
IPL1.PoS = IPL2.PoS and IPL1.YEAR > IPL2.YEAR and
IPL2.WINNER = ‘Mumbai Indians’;
This will yield the names of Player of Series (PoS) winners who
once won earlier and for ‘Mumbai Indians’ at a later time.
Outline Preliminaries Data Definition Problems
Delete operation on the tuples
delete from IPL
where YEAR < 10;
Note: It works on the entire tuple and can not delete values on
arbitrary attributes.
Outline Preliminaries Data Definition Problems
Delete operation on the tuples
Table: IPL
YEAR VENUE WINNER PoS
10 India Chennai Super Kings Sachin Tendulkar
11 India Chennai Super Kings Chris Gayle
12 India Kolkata Knight Riders Sunil Narine
13 India Mumbai Indians Shane Watson
14 India, UAE Kolkata Knight Riders Glenn Maxwell
15 India Mumbai Indians Andre Russell
16 India Sunrisers Hyderabad Virat Kohli
17 India Mumbai Indians Ben Stokes
18 India Chennai Super Kings Sunil Narine
19 India Mumbai Indians Andre Russell
20 UAE Mumbai Indians Jofra Archer
Outline Preliminaries Data Definition Problems
Insert operation
insert into IPL
values (8, ‘India’, ‘Rajasthan Royals’, ‘Shane
Waton’);
insert into IPL values (9, ‘South Africa’, ‘Deccan
Chargers’, ‘Adam Gilchrist’);
Note: You can optionally mention the attributes as well as follows
“insert into IPL (YEAR, VENUE, WINNER, PoS) values
(9, ‘South Africa’, ‘Deccan Chargers’,‘Adam
Gilchrist’);”.
Outline Preliminaries Data Definition Problems
Insert operation
Table: IPL
YEAR VENUE WINNER PoS
8 India Rajasthan Royals Shane Watson
9 South Africa Deccan Chargers Adam Gilchrist
10 India Chennai Super Kings Sachin Tendulkar
11 India Chennai Super Kings Chris Gayle
12 India Kolkata Knight Riders Sunil Narine
13 India Mumbai Indians Shane Watson
14 India, UAE Kolkata Knight Riders Glenn Maxwell
15 India Mumbai Indians Andre Russell
16 India Sunrisers Hyderabad Virat Kohli
17 India Mumbai Indians Ben Stokes
18 India Chennai Super Kings Sunil Narine
19 India Mumbai Indians Andre Russell
20 UAE Mumbai Indians Jofra Archer
Note: Tuples are inserted following the order of primary key,
otherwise (no primary key) at the bottom.
Outline Preliminaries Data Definition Problems
Update operation
update IPL
set YEAR = YEAR + 2000
where YEAR < 2000;
Outline Preliminaries Data Definition Problems
Update operation
Table: IPL
YEAR VENUE WINNER PoS
2008 India Rajasthan Royals Shane Watson
2009 South Africa Deccan Chargers Adam Gilchrist
2010 India Chennai Super Kings Sachin Tendulkar
2011 India Chennai Super Kings Chris Gayle
2012 India Kolkata Knight Riders Sunil Narine
2013 India Mumbai Indians Shane Watson
2014 India, UAE Kolkata Knight Riders Glenn Maxwell
2015 India Mumbai Indians Andre Russell
2016 India Sunrisers Hyderabad Virat Kohli
2017 India Mumbai Indians Ben Stokes
2018 India Chennai Super Kings Sunil Narine
2019 India Mumbai Indians Andre Russell
2020 UAE Mumbai Indians Jofra Archer
Outline Preliminaries Data Definition Problems
Understanding the concepts in a better way
Try this out!!!
RAT – Relational Algebra Translator
http://www.slinfo.una.ac.cr/rat/rat.html
Outline Preliminaries Data Definition Problems
Problems
1 Consider the following E-R diagram representing the details
about products, customers, and orders that were placed by
the customers of Amazon Freedom Sale during August 8-11,
2020. Create the database and insert data.
