5.10. Database SQL¶
5.10.1. ACID¶
Atomicity
Consistency
Isolation
Durability
5.10.2. 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.
5.10.3. 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.
5.10.4. 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.
5.10.5. 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.
5.10.6. CREATE¶
-- SQLite3
CREATE TABLE contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
firstname VARCHAR(20),
lastname 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 ;
5.10.7. INSERT¶
INSERT INTO contacts (firstname, lastname) VALUES ("Max", "Peck");
INSERT INTO contacts VALUES ("José", "Jiménez");
INSERT INTO contacts (firstname, lastname) VALUES (?, ?)
INSERT INTO contacts VALUES (NULL, :firstname, :lastname)
5.10.8. AUTOINCREMENT¶
CREATE TABLE astronauts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
firstname TEXT,
lastname TEXT
);
INSERT INTO astronauts VALUES ("José", "Jiménez");
INSERT INTO astronauts (firstname, lastname) VALUES ("Max", "Peck");
SELECT * FROM astronauts;
-- 1|José|Jiménez
-- 2|Max|Peck
5.10.9. 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)
5.10.10. 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;
5.10.11. UPDATE¶
UPDATE kontakty SET
firstname='José'
WHERE lastname='Jiménez';
UPDATE kontakty SET
firstname=:firstname,
lastname=:lastname,
address=:address
WHERE id=:id
5.10.12. 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;
5.10.13. 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 = 'Watney' OR LastName = 'Lewis'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;
5.10.14. ALTER¶
write your statement starting with
--after you're sure, remove comments
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, lastname);
5.10.15. DROP¶
write your statement starting with
--after you're sure, remove comments
DROP TABLE table_name;
5.10.16. DELETE¶
write your statement starting with
--after you're sure, remove comments
DELETE FROM table_name WHERE condition;
DELETE FROM contacts
WHERE lastname='Jimenez';
DELETE FROM contacts
WHERE firstname='Jose'
AND lastname='Jimenez';
5.10.18. 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);
5.10.19. 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;
5.10.20. 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;
5.10.21. 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;
5.10.22. 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;
5.10.24. Assignments¶
Todo
Convert assignments to literalinclude
5.10.24.1. Database SQL Relations¶
Assignment: Database SQL Relations
Complexity: medium
Lines of code: 0 lines
Time: 13 min
- English:
Use data from "Given" section (see below)
How to write input data in one table without using relations?
There are at least four methods
Discuss pros and cons of each method
Which methods is ACID compliant?
Run doctests - all must succeed
- Polish:
Użyj danych z sekcji "Given" (patrz poniżej)
Jak zapisać w jednej tabeli bez wykorzystania relacji?
Są przynajmniej cztery metody
Przeprowadź dyskusję na temat zalet i wad każdej metody
Która metody jest zgodna z ACID?
Uruchom doctesty - wszystkie muszą się powieść
- Given:
José, Jiménez 2101 E NASA Pkwy, 77058, Houston, Texas, USA , Kennedy Space Center, 32899, Florida, USA Mark, Watney 4800 Oak Grove Dr, 91109, Pasadena, California, USA 2825 E Ave P, 93550, Palmdale, California, USA Иван, Иванович Kosmodrom Bajkonur, Bajkonur, Kazachstan Melissa Lewis, <NO ADDRESS> Alex Vogel Linder Hoehe, 51147, Köln, Germany