FUNDAMENTALS OF

DATABASES
Introduction to SQL – Data Definition Language

NGUYEN Hoang Ha
Email: [Link]@[Link]
 Objectives

 Get into the SQL

 Understand the SQL Environment
 Understand the how to write simple queries
 Understand about the string data type, date-time data type
 Understand about NULL values
 Know how to change the data of tables
 Know how to order the output

2
SQL OVERVIEW
Mathematics to Computer: RA to SQL

π σ ρ ⋈c
Math
RA is the conceptual basis for RDB

Computer
4
 What is SQL?
 SEQUEL (Structured English QUEry Language) was
developed by IBM in 1974,
later became Structural Query Language (SQL)
 Standard language to work with RDBMS
 Easy to learn
 Close to English
 Less than 100 words

 Pronounced as “S-Q-L” or “Sequel.”

5
6
 From RA to SQL
 Based on relational algebra, but not entirely identical.
 Relations  Tables
 Tuples  Rows
 Attributes  Columns
 Like a relation, a table is a bag of rows. Duplicates are not
automatically removed.
 This is for practical reasons. Duplicate eliminations are inefficient in
implementation.
 Unlike a relation, the order of rows in a table is relevant.

7 Slide 7
 SQL Revisions

Year Name Alias Comments

1986 SQL-86 SQL-87 First published by ANSI. Ratified by ISO in 1987.

1989 SQL-89 FIPS 127-1 Minor revision, adopted as FIPS 127-1.

1992 SQL-92 SQL2, FIPS 127-2 Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.

1999 SQL:1999 SQL3 Added regular expression matching, recursive queries, triggers, support for procedural
and control-of-flow statements, non-scalar types, and some object-oriented features.

2003 SQL:2003 Introduced XML-related features, window functions, standardized sequences, and
columns with auto-generated values (including identity-columns).

2006 SQL:2006 ISO/IEC 9075-14:2006 defines ways in which SQL can be used in conjunction with
XML. It defines ways of importing and storing XML data in an SQL database,
manipulating it within the database and publishing both XML and conventional SQL-
data in XML form. In addition, it provides facilities that permit applications to
integrate into their SQL code the use of XQuery, the XML Query Language published
by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data
and XML documents.

2008 SQL:2008 Defines more flexible windowing functions, clarifies SQL 2003 items that were still
unclear [1]
8
 Sub-languages of SQL

CREATE

DDL ALTER

DROP

SQL INSERT

UPDATE
DML
DELETE
DCL
SELECT
TCL
PSM

9
 SQL Enviroment

Terminal tool

Network:
MySQL Protocol

IDE: MySQL Workbench

Other Applications

Client side Server side

10
 SQL Commands Are Sequential
 Commands are executed in the order they are encountered.
 DDL commands are not like C/Java declarations.
 DDL and DML commands can be mixed
 For example, you can define a table, fill it up with contents, and delete a
columns.
 That is, table definitions (relation schema) can be changed during the
lifespan of a database.
 The ability of doing so does imply it is a good practice.
 It is best the schema/design of a database is well thought through before its
use.

11 Slide 11
 Element of SQL Code
 Statement: A complete instruction to the database
SELECT * FROM Employees
WHERE LastName = 'Smith'
ORDER BY FirstName;

 Clause: a part of an SQL statement

 Eg: SELECT * FROM Employees
 A statement may consist one or many clauses

 Keyword: reserved words in SQL having a predefined

meaning
 Identifier: objects in a databases
 Eg: tables, columns, indexes, views, schemas, and other elements

12
 Coding in SQL
 SQL is case insensitive
 Convention:
 Keywords are all in UPPER CASE
 Identifier name: vary on each project and team, but must be consistent
 Tables and Columns:
 PascalCase, e.g. :UsthStudent, InvoiceDetails, TeachingLog)
 or lowercase with underscore to separate words. e.g.: usth_student, invoice_details,
teaching_log
 Tables: Singular nouns (customer, order)
 Columns: Descriptive names (first_name, order_date).

 Variables and function: use camelCase, e.g.: computeStudentNumber

13
 Coding in SQL (cont’)

 Indentation and Alignment

 Align SQL statements and clauses for better readability. Each
clause normally is written in a line.
 Use indentation to indicate nested or queries
SELECT column_name
FROM (
SELECT column_name
FROM table_name
WHERE condition
) AS subquery;

14
 Coding in SQL (cont’)
 Comments
 Use inline comments for complex logic or calculations.
SELECT column_name -- This is a comment
FROM table_name;

 Use block comments for detailed explanations.

/*
This is a block comment.
It can span multiple lines.
*/
SELECT column_name
FROM table_name;

15
DDL
 DDL Commands
CREATE DATABASE
CREATE TABLE
ALTER TABLE
RENAME TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
Also – CREATE VIEW

17
18
 Referential Integrity Constraints
 A referential integrity constraint is used to link (or reference)
relations. This means that a foreign key in a relation must also
exist in the relation in which it serves as the primary key
 Super_ssn must be found in Ssn of Employee
 Mgr_ssn of Department must exist in Ssn of Employee
 Dno of Employee must exist in Dnumber of Department
 Dnum of Project must exist in Dnumber of Department
 Dnumber of Dept_Location must exist in Dnumber of Department

19
 Create Database Example
 To create
CREATE DATABASE Company;
 To use (or switch to) the database
USE Company;
 Subsequent commands will operate on the Company
database by default.

20 Slide 20
 CREATE TABLE
CREATE TABLE base-table-name (colname
datatype [column constraints – NULL/NOT
NULL, DEFAULT…, UNIQUE, CHECK…, PRIMARY
KEY],
[,colname datatype [column constraints
…]]
...
[table constraints – PRIMARY KEY…, FOREIGN
KEY…, UNIQUE…, CHECK…]
[storage specifications]);

21
 Datatypes
 Each column must have a datatype specified
 Standards include various numeric types, fixed-length and
varying-length character strings, bit strings, and user-defined
types
 Available datatypes vary from DBMS to DBMS

22
 Datatypes
 char(n). Fixed length character string, with user-specified length n.
 varchar(n). Variable length character strings, with user-specified maximum length n.
 int. Integer (a finite subset of the integers that is machine-dependent).
 smallint. Small integer (a machine-dependent subset of the integer domain type).
 numeric(p,d). Fixed point number, with user-specified precision of p digits, with d
digits to the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores
exactly, but not 444.5 or 0.32)
 real, double precision. Floating point and double-precision floating point numbers,
with machine-dependent precision.
 float(n). Floating point number, with user-specified precision of at least n digits.
 Date: Made up of year-month-day in the format yyyy-mm-dd
 Time: Made up of hour:minute:second in the format hh:mm:ss
 Timestamp: Has both DATE and TIME components
 Others: Boolean, Float, Double Precision
 See user’s manual for more data types.

23
CREATE TABLE Example
CREATE TABLE Department (
Dname VARCHAR(10) NOT NULL,
Dnumber INTEGER DEFAULT 0,
Mgr_ssn CHAR(9),
Mgr_Start_date CHAR(9),
PRIMARY KEY (Dnumber),
UNIQUE (Dname),
FOREIGN KEY (Mgr_ssn) REFERENCES Employee(Ssn)
);

 The “UNIQUE” clause specifies secondary keys.

 Employee has to be created first for the FK Mgr_ssn to refer to it.
 How could we have defined the Dno FK in Employee?
24
 Adding the Dno FK to Employee
 If “CREATE TABLE Employee” is issued first, we cannot
specify Dno as a FK in that CREATE command.
 Statement order:
 CREATE TABLE Employee
 CREATE TABLE Department
 Use an ALTER TABLE to add a Foreign key to Employee table

ALTER TABLE Employee

ADD CONSTRAINT
FOREIGN KEY (Dno)
REFERENCES Department (Dnumber);

25
 The Check Clause
 Used to specify user-defined constraints
 Assume that dept. numbers are from 0 to 99.
CREATE TABLE Department (
…
Dnumber INTEGER Default 0
CHECK (Dnumber>=0 AND Dnumber<=99),
…);
 “Check” can also be a clause of the entire table.
CRATE TABLE Department (
…
Dept_create_date date,
Mgr_start_date date,
CHECK (Dept_create_date <= Mgr_start_date)
);
26
 Review: Multi attribute Key

 The bar and beer together are the key for Sells:
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL,
PRIMARY KEY (bar, beer)
);

27
 Exercise
 Create the table WORKS_ON, assuming tables EMPLOYEE
and PROJECT have been created and Hours ranges from 1
to 56.

28
 Add Columns to Existing Tables
 To add spouse SSN (S_ssn) to Employee
ALTER TABLE Employee
ADD COLUMN S_ssn char(9);
 The new attribute will have NULLs in all the tuples of the
relation right after the command is executed

 Alternatively, we can set a default value.

ALTER TABLE EMPLOYEE ADD COLUMN S_ssn
CHAR(9) DEFAULT “000000000”;

29
 Delete Columns from Existing Tables
 To delete column S_ssn
ALTER TABLE Employee DROP COLUMN S_ssn;

 Reminder: changing relation schemas typically indicates ill-

executed design phase of the database.

30
 Referential Integrity Options
 Causes of referential integrity violation for a foreign key FK
(consider the Mgr_ssn of Department).
 On Delete: when deleting the foreign tuple
 What to do when deleting the manager tuple in Employee ?
 On Update: when updating the foreign tuple
 What to do when updating/changing the SSN of the manager tuple in
Employee is changed ?

 Actions when the above two causes occur.

 Set Null: the Mgr_ssn is set to null.
 Set Default: the Mgr_ssn is set to the default value.
 Cascade: the Mgr_ssn is updated accordingly
 If the manager is deleted, the department is also deleted.

31
The Mgr_ssn Example

CREATE TABLE DEPARTMENT (

…
Mgr_ssn CHAR(9),
…
FOREIGN KEY (Mgr_ssn)
REFERENCES EMPLOYEE (Ssn)
ON DELETE ???
ON UPDATE ???
);

32
 Another Example
CREATE TABLE EMP(
…
SSN CHAR(9),
DNO INTEGER DEFAULT 1,
SUPERSSN CHAR(9),
PRIMARY KEY (ESSN),
FOREIGN KEY (DNO) REFERENCES DEPT
ON DELETE SET DEFAULT
ON UPDATE CASCADE,
FOREIGN KEY (SUPERSSN) REFERENCES EMP
ON DELETE SET NULL
ON UPDATE CASCADE
);

33
 Miscellaneous Commands
 SHOW DATABASES;
 Show all the databases on the server

 SHOW TABLES;
 Show all the tables of the present database

 DROP TALBE t_name;

 Delete the entire table t_name

 DROP DATABASE db_name;

 Delete the entire database db_name

 DESCRIBE table_name
 Show structure of table, only for MySQL

34