Storing and Retrieving Data
Lecture 3
Structured Query Language (SQL) – Part 1
Lecturer: Mijail Naranjo-Zolotov
Email:
[email protected] Previous class
Architecture of a DBMS
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa 3
Relational Database Management System (RDBMS)
Information is stored in tables, and relations between data materialize in
relations between tables
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa 4
Crow’s foot Notation for the ERD
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
5
Today
- Is SQL dead?
- Create a database
- Create tables
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
6
Is SQL dead?
Image source: https://www.rd.com/wp-content/uploads/2018/09/Dinosaur.jpg
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
7
Let’s compare the two years (2020 - 2024)
2020
This chart shows the
popularity of each
category. More on how
the scores are
calculated: https://db-
engines.com/en/ranking
_definition
2024
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
Key Characteristics of SQL
• First version, SQL-86 in 1986, most recent version in 2011
(SQL:2011)
• Accepted by the American National Standards Institute (ANSI) in
1986 and by the International Organization for Standardization (ISO)
in 1987
• Each vendor provides its own implementation (also called SQL
dialect) of SQL
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
Key Characteristics of SQL
• Set-oriented and declarative
• Free-form language
• Case insensitive
• Can be used both interactively from a command prompt or executed
by a program
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
Key Characteristics of SQL
Example: Executed from MySQL workbench
11
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
Key Characteristics of SQL
Example: Embedded in java code.
12
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
Recommended tutorials!!
Many of the examples are from:
https://www.mysqltutorial.org/
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
13
Philosophical moment
Will AI replace the SQL Analyst anytime
soon?
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
14
Suggested readings
Will AI replace the SQL analyst anytime soon?
Scientific articles for home reading (shared in Moodle):
• Li, G., Zhou, X., & Cao, L. (2021, June). AI meets database: AI4DB and DB4AI.
In Proceedings of the 2021 International Conference on Management of
Data (pp. 2859-2866).
• Perry, N., Srivastava, M., Kumar, D., & Boneh, D. (2022). Do users write more
insecure code with AI assistants?. arXiv preprint arXiv:2211.03622.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
15
Create a Database
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
16
Check existing SHOW DATABASES;
databases
Create Database CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]
A MySQL character set is a set A MySQL collation is a set of rules used
of characters that are legal in a to compare characters in a
string particular character set.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
17
use Database USE database_name;
Drop Database DROP DATABASE [IF EXISTS]
database_name;
See tables present SHOW TABLES;
in the database
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
18
Create Tables
Image source: https://database.guide/database-tutorial-part-1-about-databases-creating-databases-tables/
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
19
Table creation
Create a Table
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
col2 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
col3 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE=storage_engine;
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
20
Table creation
Create a Table
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE=storage_engine;
The column_name specifies the name of the column.
Each column has a specific data type and maximum length e.g.,VARCHAR(255)
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
21
Table creation – data types
The Data Type specifies what type of data the column can hold.
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in
parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in
parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TEXT Holds a string with a maximum length of 65,535 characters
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified
in parenthesis
FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter.
The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter.
The maximum number of digits to the right of the decimal point is specified in the d parameter
DATE() A date. Format: YYYY-MM-DD Note: The supported range is from '1000-01-01' to '9999-12-31'
YEAR() A year in two-digit or four-digit format. Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit
format: 70 to 69, representing years from 1970 to 2069
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
22
Table creation
Create a Table
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE=storage_engine;
The NOT NULL indicates that the column does not allow NULL
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
23
Table creation
Create a Table
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE=storage_engine;
The DEFAULT value is used to specify the default value of the column
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
24
Table creation
Create a Table
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE=storage_engine;
The AUTO_INCREMENT indicates that the value of the column is
incremented by one automatically whenever a new row is inserted into the
table. Each table has one and only one AUTO_INCREMENT column
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
25
Table creation
Create a Table
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE=storage_engine; dont use different engines on the same database
Third, you can optionally specify the storage engine for the table in the ENGINE clause. You
can use any storage engine such as InnoDB and MyISAM. If you don’t explicitly declare the
storage engine, MySQL will use InnoDB by default.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
26
Table creation – primary key
Primary Keys
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length) [PRIMARY KEY]
col2 data_type(length) [PRIMARY KEY]
col3 data_type(length) [PRIMARY KEY]
);
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
col2 data_type(length)
col3 data_type(length)
PRIMARY KEY(col1, col2, …)
);
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
27
Table creation - example
Person CREATE TABLE Person (
pid int AUTO_INCREMENT NOT NULL,
pid int
firstname varchar(25)
firstname varchar(25) NOT NULL,
surname varchar(25) surname varchar(25) NOT NULL,
);
What are we missing?
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
Person CREATE TABLE Person (
pid int AUTO_INCREMENT NOT NULL,
pid int firstname varchar(25) NOT NULL,
firstname varchar(25)
surname varchar(25) surname varchar(25) NOT NULL,
PRIMARY KEY (pid)
);
What are we missing? The Primary Key
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
29
Table creation - Foreign keys
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table (columns)
ON DELETE action
ON UPDATE action
);
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
30
Table creation - Foreign keys
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
);
The CONSTRAINT clause allows you to define constraint name for the foreign key
constraint. If you omit it, MySQL will generate a name automatically.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
31
Table creation - Foreign keys
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
);
The FOREIGN KEY clause specifies the columns in the child table that refers to primary key columns
in the parent table.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
32
Table creation - Foreign keys
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
);
The REFERENCES clause specifies the parent table and its columns to which the columns in the child
table refer. The number of columns in the child table and parent table specified in the FOREIGN KEY
and REFERENCES must be the same.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
33
Table creation - Foreign keys
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action possible actions :
CASCADE, SET NULL, NO ACTION, RESTRICT
);
The ON DELETE clause allows you to define what happens to the records in the child table when the
records in the parent table are deleted. If you omit the ON DELETE clause and delete a record in the
parent table that has records in the child table refer to, MySQL will reject the deletion.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
34
Table creation - Foreign keys
CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action possible actions :
CASCADE, SET NULL, NO ACTION, RESTRICT
);
The ON UPDATE clause enables you to specify what happens to the rows in the child table when
rows in the parent table are updated. You can omit the ON UPDATE clause to let MySQL reject any
updates to the rows in the child table when the rows in the parent table are updated.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
35
Table creation - Foreign keys - Example
CREATE TABLE category(
categoryId INT AUTO_INCREMENT PRIMARY KEY,
categoryName VARCHAR(100) NOT NULL);
CREATE TABLE products(
productId INT AUTO_INCREMENT PRIMARY KEY,
productName varchar(100) not null,
categoryId INT NOT NULL,
CONSTRAINT fk_category
FOREIGN KEY (categoryId)
REFERENCES category(categoryId)
ON UPDATE SET NULL
Source: https://www.mysqltutorial.org/mysql-foreign-key/
ON DELETE SET NULL );
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
36
Drop a table
DROP TABLE [IF EXISTS] table_name [, table_name] …
The DROP TABLE statement removes a table and its data permanently from the database. In MySQL,
you can also remove multiple tables using a single DROP TABLE statement, each table is separated
by a comma (,).
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
37
Drop a table
DROP TABLE [IF EXISTS] table_name [, table_name] …
The IF EXISTS addition helps you prevent from attempt of removing non-existent tables. When you
use IF EXISTS addition, MySQL generates a NOTE, which can be retrieved by using the SHOW
WARNING statement. It is important to note that the DROP TABLE statement removes all existing
tables and issues an error message or a NOTE when you have a non-existent table in the list.
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
38
Quick quiz
https://b.socrative.com/login/student/
Room: SRD24
Instituto Superior de Estatística e Gestão de Informação
Universidade Nova de Lisboa
END OF LECTURE 3
