2. SQL

2.1. ACID

  • Atomicity
  • Consistency
  • Isolation
  • Durability

2.1.1. Atomicity

Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.

2.1.2. Consistency

Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct.

2.1.3. Isolation

Transactions are often executed concurrently (e.g., reading and writing to multiple tables at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. Isolation is the main goal of concurrency control; depending on the method used, the effects of an incomplete transaction might not even be visible to other transactions.

2.1.4. Durability

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.

2.2. Data Types

Tab. 2.8. SQLite data types
Type Description
NULL The value is a undefined value
INTEGER The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value
REAL The value is a floating point value, stored as an 8-byte IEEE floating point number
TEXT The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)
BLOB The value is a blob of data, stored exactly as it was input

2.3. Constrains

Tab. 2.9. SQL Constraints
Constraint Description
NOT NULL Ensures that a column cannot have a NULL value
UNIQUE Ensures that all values in a column are different
PRIMARY KEY A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY Uniquely identifies a row/record in another table
CHECK Ensures that all values in a column satisfies a specific condition
DEFAULT Sets a default value for a column when no value is specified
INDEX Used to create and retrieve data from the database very quickly

2.4. CREATE

Code Listing 2.24. CREATE
-- SQLite3
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(20),
    last_name VARCHAR(50),
    pesel INTEGER UNIQUE,
    age INTEGER
);

-- SQLite3
CREATE TABLE IF NOT EXISTS sensor_data (
    datetime DATETIME PRIMARY KEY,
    sync_datetime DATETIME DEFAULT NULL,
    device VARCHAR(255),
    parameter VARCHAR(255),
    value REAL,
    unit VARCHAR(255)
);

-- MySQL
CREATE DATABASE astronauts;

-- MySQL
CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

2.5. INSERT

Code Listing 2.25. INSERT
INSERT INTO contacts (first_name, last_name) VALUES ("Max", "Peck");
INSERT INTO contacts VALUES ("José", "Jiménez");
INSERT INTO contacts (first_name, last_name) VALUES (?, ?)
INSERT INTO contacts VALUES (NULL, :first_name, :last_name)

2.6. AUTOINCREMENT

Code Listing 2.26. Auto Increment
CREATE TABLE astronauts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT
);


INSERT INTO astronauts VALUES ("José", "Jiménez");
INSERT INTO astronauts (first_name, last_name) VALUES ("Max", "Peck");


SELECT * FROM astronauts;

-- 1|José|Jiménez
-- 2|Max|Peck

2.7. COMMIT and ROLLBACK

Code Listing 2.27. COMMIT and ROLLBACK
CREATE TABLE customer (id INT, name CHAR (20), INDEX (id));


START TRANSACTION;
INSERT INTO customer VALUES (1, 'Jose Jimenez');
COMMIT;


SET autocommit=0;
INSERT INTO customer VALUES (2, 'Mark Watney');
INSERT INTO customer VALUES (3, 'Ivan Ivanovich');
DELETE FROM customer WHERE name='Jose Jimenez';
ROLLBACK;  -- Now we undo those last 2 inserts and the delete.


SELECT * FROM customer;
-- +------+--------------+
-- | id   | name         |
-- +------+--------------+
-- |    1 | Jose Jimenez |
-- +------+--------------+
-- 1 row in set (0.00 sec)

2.8. SELECT

Code Listing 2.28. SELECT
SELECT * FROM kontakty;
SELECT * FROM stocks ORDER BY price;
SELECT long_name_of_the_colum as col FROM kontakty;

SELECT * FROM Customers WHERE CustomerName LIKE 'a%'; -- % - any character
SELECT * FROM Customers WHERE CustomerName LIKE '%or%'; -- % - any character
SELECT * FROM Customers WHERE CustomerName LIKE 'a_%_%'; -- _ - single character

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);

SELECT DISTINCT Country FROM Customers;
SELECT COUNT(DISTINCT Country) FROM Customers;

SELECT COUNT(column_name) FROM table_name WHERE condition;
SELECT AVG(column_name) FROM table_name WHERE condition;
SELECT SUM(column_name) FROM table_name WHERE condition;

2.9. UPDATE

Code Listing 2.29. UPDATE
UPDATE kontakty SET
    first_name='José'
  WHERE last_name='Jiménez';


UPDATE kontakty SET
    first_name=:firstname,
    last_name=:lastname,
    address=:address
WHERE id=:id

2.10. GROUP BY

Code Listing 2.30. GROUP BY
SELECT COUNT(CustomerID), Country
  FROM Customers
  GROUP BY Country;


SELECT COUNT(CustomerID), Country
  FROM Customers
  GROUP BY Country
  ORDER BY COUNT(CustomerID) DESC;

2.11. HAVING

Code Listing 2.31. HAVING
SELECT COUNT(CustomerID), Country
  FROM Customers
  GROUP BY Country
  HAVING COUNT(CustomerID) > 5;


SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
  FROM (Orders
  INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
    GROUP BY LastName
    HAVING COUNT(Orders.OrderID) > 10;


SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
  FROM Orders
  INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
    WHERE LastName = 'Davolio' OR LastName = 'Fuller'
    GROUP BY LastName
    HAVING COUNT(Orders.OrderID) > 25;

2.12. ALTER

  • write your statement starting with --
  • after you’re sure, remove comments
Code Listing 2.32. ALTER
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;

-- SQL Server / MS Access:
ALTER TABLE table_name ALTER COLUMN column_name datatype;

-- My SQL / Oracle (prior version 10G):
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

-- Oracle 10G and later:
ALTER TABLE table_name MODIFY column_name datatype;

-- MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE table_name ADD PRIMARY KEY (id);

-- MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE table_name ADD CONSTRAINT PK_contacts PRIMARY KEY (id, last_name);

2.13. DROP

Code Listing 2.33. DROP
DROP TABLE table_name;

2.14. DELETE

  • write your statement starting with --
  • after you’re sure, remove comments
Code Listing 2.34. DELETE
DELETE FROM table_name WHERE condition;


DELETE FROM contacts
  WHERE last_name='Jimenez';


DELETE FROM contacts
  WHERE first_name='Jose'
    AND last_name='Jimenez';

2.15. JOIN

2.15.1. INNER JOIN

../_images/sql-innerjoin.gif
Code Listing 2.35. INNER JOIN
SELECT column_name(s)
  FROM table1
  INNER JOIN table2 ON table1.column_name = table2.column_name;


SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
  FROM Orders
  INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;


SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
  FROM ((Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
  INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

2.15.2. LEFT JOIN

../_images/sql-leftjoin.gif
Code Listing 2.36. LEFT JOIN
SELECT column_name(s)
  FROM table1
  LEFT JOIN table2 ON table1.column_name = table2.column_name;


SELECT Customers.CustomerName, Orders.OrderID
  FROM Customers
  LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  ORDER BY Customers.CustomerName;

2.15.3. RIGHT JOIN

../_images/sql-rightjoin.gif
Code Listing 2.37. RIGHT JOIN
SELECT column_name(s)
  FROM table1
  RIGHT JOIN table2 ON table1.column_name = table2.column_name;


SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
  FROM Orders
  RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
  ORDER BY Orders.OrderID;

2.15.4. FULL OUTER JOIN

../_images/sql-fulljoin.gif
Code Listing 2.38. FULL OUTER JOIN
SELECT column_name(s)
  FROM table1
  FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;


SELECT Customers.CustomerName, Orders.OrderID
  FROM Customers
  FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
  ORDER BY Customers.CustomerName;

2.15.5. SELF JOIN

Code Listing 2.39. SELF JOIN
SELECT column_name(s)
  FROM table1 T1, table1 T2
  WHERE condition;


SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
  FROM Customers A, Customers B
  WHERE A.CustomerID <> B.CustomerID
  AND A.City = B.City
  ORDER BY A.City;

2.16. TRUNCATE

Code Listing 2.40. TRUNCATE
TRUNCATE TABLE table_name;

2.17. SQL Injection

Code Listing 2.41. SQL Injection
username = input('Username: ')  # User type: ' OR 1=1; DROP TABLE users --
password = input('Password: ')  # User type: whatever

query = f"""

    SELECT id, username, email
    FROM users
    WHERE username='{username}' AND password='{password}'

"""

print(query)
# SELECT id, username, email
# FROM users
# WHERE username='' OR 1=1; DROP TABLE users -- ' AND password='132'
../_images/sql-injection.jpg

Fig. 2.10. SQL Injection