HANOI UIVERSITY OF SCIENCE AND TECHNOLOGY
School of Applied Mathematics and Informatics

000

BAI HOC

PROJECT

STRUCTURED QUERY LANGUAGE

Major: Applied Mathematics

Supervisor: Dr. Nguyen Thi Thanh Huyen
Author: Nguyen Thuy Quynh
Email: quynh.nt206159Qsis.hust.edu.vn

Class: MI2 - 01 K65

HANOI, 7/2022

Introduction

Structured Query Language (SQL) is the typical language for relational
systems and it is supported by just about every database product on the
market today. In this project, we present an overview of the major features

of the SQL language (more detailed aspects).

Contents

Introduction

Overview

The Catalog

Views

4 Transactions

5 Embedded SQL 10

Conclusion 11

References 12

Chapter 1

Overview

SQL includes both data definition and data manipulation operations.
We consider definitional operations first. Fig.1.1 shows the SQL definition
for the suppliers-and-parts data-base.

As we can see, the definition includes one CREATE TYPE statement

for each of the six user-defined types (UDTs) and one CREATE TABLE
statement for each of the three base tables, the keyword TABLE in CRE-
ATE TABLE means a base table specifically).


CREATE
CREATE
CREATE TYPE WEIGHT ..- }
CREATE TYPE QTY «.. 3

CREATE TABLE S
( sứ sf,
SNAME NAME,
STATUS INTEGER,
CITY CHAR(15),
PRIMARY KEY ( S# ) ) 3

CREATE TABLE P

( Pt Pt,

PNAME ,
COLOR COLOR,
WEIGHT WEIGHT,
CITY CHAR(15),
KEY ( PÝ )
PRIMARY

CREATE TABLE SP
(s

Qty QTY,
PRIMARY KEY ( S#, P# ),
FOREIGN KEY ( S# ) REFERENCES S,
FOREIGN KEY ( P# ) REFERENCES P ;


Figure 1.1: The suppliers-and-parts database (SQL definition)

Project K65 MI2 HUST

CREATE TABLE statement specifies the name of the base table to
be created, the names and types of the columns of that table, and the
primary key and any foreign keys in that table (possibly some additional
information also, not illustrated in Fig.4.1). Also, there are some notes in
the following:

e The “#" character is not often used in (e.g.) type names and column
names, but in fact that character is not legal in the standard.

e The semicolon";"3 is used as a statement terminator. Whether SQL ac-
tually uses suchterminators depends on the context.

e The built-in type CHAR in SQL requires an associated length-15 in the
figure to be specified.

Having defined the database, we can now start operating on it by means
of the SQLmanipulative operations SELECT, INSERT, DELETE, and
UPDATE. In particular, we can perform relational restrict, roject and join
operations on the data, in each case byusing the SQL data manipulation
statement SELECT. Some examples are given in Fig.1.2.

Restrict: Result: | S# | Pt | QTY|
SELECT S#, P#, QTY S1 | P5 | 100

FROM SP S1 | P6 | 100


WHERE QTY < QTY ( 150 ) ; Result: | S$‡ | CITY
$1 | London
Project: S2 | Paris
SELECT S#, CITY
FROM $ ; $3 | Paris

§4 | London
S5 | Athens

Join:

SELECT S.S#, SNAME, STATUS, CITY, P#, QTY
FROM r5
WHERE S.S# = SP.S# ;

Result: | s¢ | SNAME | STATUS | CITY Pt | QTY

S1 | smith 20 | Lendon | P1 | 300

§1 | Saith 20 | London | P2 | 200

$1 | Smith 20 | London | P3 | 400

s4 | Clark 20 | London | P3 | 400

Figure 1.2: Restrict, project, and join examples in SQL

Nguyen Thuy Quynh 2 K59CLC Toén DHSPHN


Project K65 MI2 HUST

It is remarked that SQL also support a shorthand form of the SELECT
clause as illustrated by the following example:

SELECT * /* ox "SELECT S,*" (i.@., the */

FROM § ¡ /* "*®" can be dot-qgualified).- +*/

Figure 1.3: Example of SELECT

The result is a copy of the entire S table; the asterisk is shorthand
for a:"comma-list" names of all columns inthe first table referenced in the
FROM clause, in the left-to-right order in which those columns are de-
fined within that table, followed by names of all columns in the second
tablereferenced in the FROM clause, in the left-to-right order in which
those columns aredefined within that table (and so on for all of the other
tables referenced in the FROM clause).

Examples of the SQL INSERT, DELETE and UPDATE statements
deliberately involved single-row operations only. Like SELECT, however,
INSERT, DELETE, and UPDATE are all ser-level operations, in gen-
eral. Here are some set-level update examples for the suppliers-and-parts
database:

INSERT
INTO TEMP ( Pf, WEIGRT )
SELECT P#, WEIGHT
FROM P
WHERE COLOR = COLOR (‘Red‘) ;


Figure 1.4: Example of INSERT

This example assumes that we have already created another table TEMP
with two columns P# and WEIGHT. The INSERT statement inserts into
that table part numbers and corresponding weights for all red parts.

DELETE
FROM SP
WHERE Pé = P# (‘P2‘) ;

Figure 1.5: Example of DELETE

This DELETE statement deletes all shipments for part P2.

This UPDATE statement doubles the status for all suppliers to Rome.

Nguyen Thuy Quynh 3 K59CLC Toén DHSPHN

Project K65 MI2 HUST

UPDATE $
SET STATUS = 2 * STATUS ,
CITY = ‘Rome’
WHERE CITY = ‘Paris’ ;

Figure 1.6: Example of UPDATE

Nguyen Thuy Quynh 4 K59CLC Toén DHSPHN


Chapter 2

The Catalog

The SQL standard does include specifications for a standard catalog
called the Information Schema. In fact, the conventional terms catalog
and schema are both used in SQL but with highly SQL specific meaning;
loosely speaking, an SQL catalog consists of the descriptors for an indi-
vidual database and an SQL schema consists of the descriptorsfor that
portion of that database that belongs to some individual user. In other
wordsthere can be any number of catalogs (one per database), each consists
of any number ofschemas. However, each catalog is required to include ex-
actly one schema called INFORMATION SCHEMA, and from the user’s
perspective it is that schema that, as already indicated, performs the nor-
mal catalog function.

The Information Schema thus consists of a set of SQL tables whose
contents effectively echo, in a precisely defined way, all of the definitions
from all of the other schemasin the catalog in question. More precisely, the
Information Schema is defined to contain a set of views of a hypothetical
“Definition Schema.” The impiementation is not required to support the
Definition Schema as such but it is required to support some kind of "Def-
inition Schema" and to support views of that "Definition Schema" that do
look like those of the Information Schema.

It is not worth going into great detail on the content of the Information
Schema here; instead, we simply list some of the more important Informa-
tion Schema views, in the hope that their names alone will be sufficient to
give some idea of what those views contain. One point that is worth call-
ing out explicitly, however, is that the TABLES viewcontains information


o t

Project K65 MI2 HUST

for all named tables, views as well as base tables, while the VIEWS view
contains information for views only.

ASSERTIONS TABLES
CHECK CONSTRAINTS TABLE CONSTRAINTS
COLUMNS TABLE PRIVILEGES
COLUMN PRIVILEGES USAGE PRIVILEGES
COLUMN” UDT_USAGE USER_DEFINED TYPES
CONSTRAINT COLUMN USAGE upt_ PRIVILEGES
CONSTRAINT TABLE USAGE VIEWS
KEY COLUMN USAGE VIEW_COLUMN USAGE
REFERENTIAL CONSTRAINTS VIEW_TABLE_USAGE
SCHEMATA

Figure 2.1: Some important Information Schema views

Nguyen Thuy Quynh 6 K59CLC Toén DHSPHN

Chapter 3

Views

Here is an example of an SQL view definition:

CREATE VIEW GOOD SUPPLIER

AS SELECT S#, STATUS, CITY
FROM S ;
WHERE STATUS > 15

And here is an example of an SQL qnery against this view:

SELECT S#, STATUS
PRON GOOD SUPPLIER
WHERE CITY = ‘London’ ;

Substiuting the view definition for the reference to thr view name, an
expression is obtained that looks something like this:

SELECT GOOD SUPPLIER.S#, GOOD SUPPLIER.STATUS
PROM ( SELECT st, STATUS, CITY

StaTUS > 15 ) AS GOOD “en
WHERE Good. SUPPLIER.CITY = ‘London™

Project K65 MI2 HUST

This expression can then be simplified to something like this:

SELECT S#, STATUS
FROM 6S
WHERE STATUS > 15
AND CITY = ‘London’

This latter query is what is actually executed.
By way of another example, consider the following DELETE operation:


DELETE
FROM GOOD SUPPLIER
WHERE CITY = ‘London’ ;

The DELETE actually executed looks something like this:

DELETE S > 15
FROM STATUS ‘London’
WHERE CITY =
AND

Nguyen Thuy Quynh 8 K59CLC Toén DHSPHN

Chapter 4

Transactions

SQL includes direct analogs of the BEGIN TRANSACTION, COM-
MIT, and ROLLBACK statements, called START TRANSACTION, COM-
MIT WORK, and ROLLBACK WORK, respectively (the keyword WORK

is optional).

Chapter 5

Embedded SQL

Most SQL products allow SQL statements to be executed both directly
(i.e., interactively from an online terminal) and as part of an application

program (i.e., the SQL statements can be embedded, meaning they can be
intermixed with the programming language statements of such a program).
In the embedded case, moreover, the application program can typically be
written in a variety of host languages; the SQL standard includes support
for Ada, C, COBOL, Fortran, Java, M, Pascal, and PL/I.

A fundamental principle underlying embedded SQL, which is called the
dual-mode principle, is that any SQL statement that can be used interac-
tively can also be embedded in application program. Of course, there are
various difference of detail between a given interactive SQL statement and
its embedded counterpart, and retrieval operations in particular require
significantly extended treatment in the embedded case, but the principle
is nevertheless broadly true.

10

Conclusion

This concludes the introduction to some of the major features of the
SQL standard. Although the project is still incomplete, it is hoped to give
more particular perspective about the Structured Query Language.

11

Bibliography

[1] C.J. DATE, An introduction to Database System, Pearson Education.
Inc., 8th edition.

[2] Raymond A. Lorie and Jean-Jacques Daudenarde: SQL and Its Ap-

plicarions, Englewood Cliffs, 1991.

12