SQL

RDBMS - Relational Databases

• RDBMS

• RDBMS - Relational database management system

• SQL - Structured Query Language

• DDL - Data definition language

• DML - Data Manipulation Language

• Database schema

• ACID - Atomicity, Consistency, Isolation, Durability

• CRUD - Create, read, update and delete

• Candidate key

• Cartesian product

• Database normalization

Popular Relational Databases

• Oracle
• MySQL (open source)
• Microsoft SQL Server
• PostgreSQL (open source)
• IBM DB2
• IBM Informix
• SAP Sybase
• Teradata
• SQLite (open source)
• Microsoft Access

NoSQL

• NoSQL
• MongoDB
• Redis
• Apache Cassandra

SQL Standards

• 1986
• ...
• 1999
• ...
• 2011

Description

An RDBMS holds data in tables. The columns of each table has names describing the value they hold. Each row in this dable contains the data.

Schemas

Each databse usually can hold several schemas. Each schema is a set of tables and other database elements. Usually each project has its own schema and usually there is no relation between data information in one schema and another schema.

CREATE TABLE

• CREATE

• ENUM

• VARCHAR

• FLOAT

• INTEGER

• DATE

• ENUM

• DDL

• DDL - Data definition language (part of SQL)

• Column types

• Comments

CREATE TABLE person (
    name       VARCHAR(100),
    height     FLOAT,         -- in meter
    weight     INTEGER,       -- in kg
    birthday   DATE,
    occupation VARCHAR(100),
    gender     ENUM('male', 'female')
);

DML - Data Manipulation Language

• DML

• DML

• INSERT

• UPDATE

• DELETE

• SELECT

INSERT

• INSERT

INSERT INTO person (name, height, weight, birthday, occupation, gender)
       VALUES ("Musashimaru Koyo", 1.92, 235, DATE('1971-05-02'), 'sumo wrestler', 'male');

INSERT INTO person (name, height, weight, birthday, occupation, gender)
       VALUES ("Tara Nott Cunningham", 1.54, 48, DATE('1972-05-10'), 'weight lifter', 'female');

INSERT INTO person (name, height, weight, birthday, occupation, gender)
       VALUES ("Elisa Di Francisca", 1.77, 65, DATE('1982-12-13'), 'foil fencer', 'female');

INSERT INTO person (name, birthday, occupation, gender)
       VALUES ("Alfréd Hajos", DATE('1878-02-01'), 'swimmer', 'male');

INSERT INTO person (name, height, weight, birthday, occupation, gender)
       VALUES ("Krisztina Egerszegi", 1.74, 57, DATE('1974-08-16'), 'swimmer', 'female');

INSERT INTO person (name, height, weight, occupation, gender)
       VALUES ("Sharran Alexander", 1.82, 203, 'sumo wrestler', 'female');

SELECT

• SELECT
• FROM

SELECT * FROM person;

select * from person;
+----------------------+--------+--------+------------+---------------+--------+
| name                 | height | weight | birthday   | occupation    | gender |
+----------------------+--------+--------+------------+---------------+--------+
| Musashimaru Koyo     |   1.92 |    235 | 1971-05-02 | sumo wrestler | male   |
| Tara Nott Cunningham |   1.54 |     48 | 1972-05-10 | weight lifter | female |
| Elisa Di Francisca   |   1.77 |     65 | 1982-12-13 | foil fencer   | female |
| Alfrd Hajos          |   NULL |   NULL | 1878-02-01 | swimmer       | male   |
| Krisztina Egerszegi  |   1.74 |     57 | 1974-08-16 | swimmer       | female |
| Sharran Alexander    |   1.82 |    203 | NULL       | sumo wrestler | female |
+----------------------+--------+--------+------------+---------------+--------+
6 rows in set (0.00 sec)

NULL

• NULL

If a field does not have a value, it has a NULL value in it. It is not the empty string. Not the number 0. It is NULL

Reject INSERT

INSERT INTO person (name, height, weight, birthday, occupation, gender)
       VALUES ("Foo Bar", "tall", 100, DATE('1971-05-02'), 'sportsman', 'male');

ERROR 1265 (01000): Data truncated for column 'height' at row 1

SELECT WHERE

• WHERE

SELECT * FROM person WHERE occupation = "sumo wrestler";

+-------------------+--------+--------+------------+---------------+--------+
| name              | height | weight | birthday   | occupation    | gender |
+-------------------+--------+--------+------------+---------------+--------+
| Musashimaru Koyo  |   1.92 |    235 | 1971-05-02 | sumo wrestler | male   |
| Sharran Alexander |   1.82 |    203 | NULL       | sumo wrestler | female |
+-------------------+--------+--------+------------+---------------+--------+
2 rows in set (0.00 sec)

WHERE not

SELECT * FROM person WHERE occupation != "sumo wrestler";

+----------------------+--------+--------+------------+---------------+--------+
| name                 | height | weight | birthday   | occupation    | gender |
+----------------------+--------+--------+------------+---------------+--------+
| Tara Nott Cunningham |   1.54 |     48 | 1972-05-10 | weight lifter | female |
| Elisa Di Francisca   |   1.77 |     65 | 1982-12-13 | foil fencer   | female |
| Alfrd Hajos          |   NULL |   NULL | 1878-02-01 | swimmer       | male   |
| Krisztina Egerszegi  |   1.74 |     57 | 1974-08-16 | swimmer       | female |
+----------------------+--------+--------+------------+---------------+--------+
4 rows in set (0.00 sec)

SELECT WHERE

SELECT name, weight, height FROM person WHERE occupation = "sumo wrestler";

Aggregates

• COUNT

SELECT COUNT(*) FROM person;

constraints

• NOT NULL
• UNIQUE
• PRIMARY KEY

CREATE TABLE car (
    owner_name    VARCHAR(100),
    color         VARCHAR(20) NOT NULL,
    license_plate VARCHAR(20) UNIQUE,
    motor_number  VARCHAR(20) UNIQUE NOT NULL
);

INSERT INTO car (owner_name, color, license_plate, motor_number)
       VALUES ("Foo Bar", "Blue", "12-345-67", "DFAFD3243EGGER");

INSERT INTO car (color, motor_number)
       VALUES ("Yellow", "GFAFD3243EGGER");

INSERT INTO car (color, motor_number)
       VALUES ("White", "GFAFD3243EGGER");

PRIMARY KEY

• natural primary key
• surrogate primary key

ACID

• ACID
• Atomicity
• Consistency
• Isolation
• Durability