Databases - SQL
Module backend
Data is the 21st century's oil
Databases
save and query
The web app
DBs are an important
technology to save data.
Databases Also important to query
large amounts of data.
This can be done in a
structured and efficient way.
Agenda
⭔ Types of databases
⭔ MySQL
⭔ Tables and how tables are created
⭔ Primary key & Foreign key
⭔ Relation Types
⭔ Database schema
⭔ Alter Tables
⭔ Inserting, Updating, Deleting and Selecting Data
⭔ Selecting data groups
⭔ Joins
⭔ Subqueries
⭔ Docker
Types of databases
Relational databases NoSQL databases
⭔ MySQL ⭔ MongoDB
⭔ SQLite ⭔ Cassandra
⭔ PostgreSQL ⭔ MarkLogic
⭔ MS-SQL Server ⭔ Oracle NoSQL Database
⭔ Oracle DB
SQL stands for Structured Query
Language.
It consists of the DDL for defining
SQL the structure.
And the DML for inserting,
updating, deleting and selecting
data.
MySQL
relational database
Tables,Tables, Tables and more Tables
⭔ Tables consist of rows and columns.
⭔ The columns describe properties of a table and the rows are data entries.
This is very similar to the concepts of classes and objects in Object Oriented Programming.
Tables,Tables, Tables and more Tables
How tables are created
CREATE TABLE office ( CREATE TABLE employee (
officeCode varchar(10), employeeNumber int,
city varchar(50), lastName varchar(50) NOT NULL,
phone varchar(50) , firstName varchar(50) NOT NULL,
addressLine1 varchar(50), extension varchar(10) NOT NULL,
addressLine2 varchar(50) DEFAULT NULL, email varchar(100) NOT NULL,
state varchar(50) DEFAULT NULL, officeCode varchar(10) NOT NULL,
country varchar(50), reportsTo int DEFAULT NULL,
postalCode varchar(15), jobTitle varchar(50) NOT NULL,
territory varchar(10), PRIMARY KEY (employeeNumber),
PRIMARY KEY (officeCode) CONSTRAINT employee_ibfk_1 FOREIGN KEY (reportsTo)
); REFERENCES employee (employeeNumber),
CONSTRAINT employee_ibfk_2 FOREIGN KEY (officeCode)
REFERENCES office (officeCode)
);
Foreign key syntax
CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <referencing table> (<column>)
So we had these Foreign Keys created in the table employees.
CONSTRAINT employee_ibfk_1 FOREIGN KEY (reportsTo) REFERENCES employee (employeeNumber),
CONSTRAINT employee_ibfk_2 FOREIGN KEY (officeCode) REFERENCES office (officeCode)
The first foreign key in the example is a special case. It is a reference to the same table. An employee reports to another employee.
CONSTRAINT employee_ibfk_1 FOREIGN KEY (reportsTo) REFERENCES employee (employeeNumber)
Relation Types
⭔ 1 : 1 (one to one)
Order : OrderDetails
⭔ 1 : N (one to many)
Office : Employee
⭔ M : N (many to many)
Student : Course
- a third table is needed to model this correctly
Alter Tables
It is also possible to modify the structure of a table after it was created. This is very common due to
changes in different software versions using the database.
⭔ Adding a new column: ALTER TABLE table_name ADD column_name datatype;
⭔ Removing a column: ALTER TABLE table_name DROP COLUMN column_name;
⭔ Removing a table: DROP TABLE table_name;
Inserting Data
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
INSERT INTO office
(officeCode,city,phone,addressLine1,addressLine2,state,country,
postalCode,territory)
VALUES
(42,'Graz','0316245435','Eichenstrasse','32',null, 'Austria','8010','EMEA');
Updating Data
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
update office set phone='06803424234' where officeCode='42';
Deleting Data
DELETE FROM table_name WHERE condition;
delete from office where officeCode='42';
Selecting Data
SELECT [ORDER BY {col_name | expr | position}
[ALL | DISTINCT | DISTINCTROW ] [ASC | DESC], ... [WITH ROLLUP]]
[HIGH_PRIORITY] [LIMIT {[offset,] row_count | row_count OFFSET
[STRAIGHT_JOIN] offset}]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [into_option]
[SQL_BUFFER_RESULT] [FOR {UPDATE | SHARE}
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [OF tbl_name [, tbl_name] ...]
select_expr [, select_expr] ... [NOWAIT | SKIP LOCKED]
[into_option] | LOCK IN SHARE MODE]
[FROM table_references [into_option]
[PARTITION partition_list]]
[WHERE where_condition] into_option: {
[GROUP BY {col_name | expr | position}, ... [WITH INTO OUTFILE 'file_name'
ROLLUP]] [CHARACTER SET charset_name]
[HAVING where_condition] export_options
[WINDOW window_name AS (window_spec) | INTO DUMPFILE 'file_name'
[, window_name AS (window_spec)] ...] | INTO var_name [, var_name] ...
}
Examples of Selecting Data
- - show all the offices with all the properties
select * from offices;
- - show the officeCode and the city of all the offices from USA
select officeCode,city from offices where country='USA';
- - show all the customers that have the name "Peter Ferguson"
select * from customers where contactFirstName = 'Peter' and contactLastName = 'Ferguson';
- - show all the ordered from z to a that have the phone starting with 0316
select * from customers where phone like '0316%' order by contactLastName desc;
- - how many customers are in the database
select count(*) from customers;
- - show the minimum, average and the maximum of the payments that are in the database
select min(amount), avg(amount), max(amount) from payments;
Selecting data groups
-- find out how many offices are in each city
select count(*),city from offices group by city;
-- total amount paid by customer
select customerNumber, sum(amount) from payments group by customerNumber;
-- number of payments from each customer
select customerNumber, count(*) from payments group by customerNumber;
-- customers with more than 4 payments (note the having keyword instead of where)
select customerNumber, count(*) from payments group by customerNumber having count(*) > 4;
Joins
Sometimes you want to select data from multiple related tables joined in one select statement.
-- find out the office code and the city for that office for each employeeNumber
select [Link], [Link], [Link] from offices o, employees e where [Link]=[Link];
-- the above statement can also be written with the inner join syntax
select [Link], [Link], [Link] from offices o inner join employees e where [Link]=[Link];
The inner join is the most common one. There are also other join types like the left and right join. The terminology is inspired by the
set theory from mathematics.
Joins
Subqueries
Sometimes you can not write joins but you need to create statements consisting of nested select queries.
-- select customers with no payments yet
select * from customers where
not exists (select 1 from payments where [Link]=[Link]);
-- the same result as above but with in instead of exists
select * from customers where
[Link] not in (select [Link] from payments where
[Link]=[Link]);
Docker
Summary
⭔ Types of databases
⭔ MySQL
⭔ Tables and how tables are created
⭔ Primary key & Foreign key
⭔ Relation Types
⭔ Database schema
⭔ Alter Tables
⭔ Inserting, Updating, Deleting and Selecting Data
⭔ Selecting data groups
⭔ Joins
⭔ Subqueries
⭔ Docker
SQL stands for Structured
Query Language.
SQL is a relational database.
Remember
SQL consists of the DDL for
defining the structure and the
DML for inserting, updating,
deleting and selecting data.
Databases are an important
technology to save and query
large amounts of data in a
structured and efficient way.
That’s all
Databases - SQL