Here is a short SQL breezer i made for revising SQL for interviews.
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
The WHERE clause is applied before the GROUP BY clause.
Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate.
Example Queries:
SELECT Book.title,
count(*) AS Authors
FROM Book JOIN Book_author
ON Book.isbn = Book_author.isbn
GROUP BY Book.title;
INSERT INTO My_table
(field1, field2, field3)
VALUES
('test', 'N', NULL);
- UPDATE modifies a set of existing table rows, e.g.,:
SET field1 = 'updated value'
WHERE field2 = 'N';
- DELETE removes existing rows from a table, e.g.,:
WHERE field2 = 'N';
Data definition
The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements:- CREATE creates an object (a table, for example) in the database, e.g.,:
my_field1 INT,
my_field2 VARCHAR(50),
my_field3 DATE NOT NULL,
PRIMARY KEY (my_field1, my_field2)
);
- ALTER modifies the structure of an existing object in various ways, for example, adding a column to an existing table or a constraint, e.g.,:
- TRUNCATE deletes all data from a table in a very fast way, deleting the data inside the table and not the table itself. It usually implies a subsequent COMMIT operation, i.e., it cannot be rolled back.
- DROP deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back, e.g.,:
Null and three-valued logic (3VL)
The idea of Null was introduced into SQL to handle missing information in the relational model. The introduction of Null (or Unknown) along with True and False is the foundation of three-valued logic. Null does not have a value (and is not a member of any data domain) but is rather a placeholder or “mark” for missing information. Therefore comparisons with Null can never result in either True or False but always in the third logical result, Unknown.
|
Transaction controls
Transactions, if available, wrap DML operations:- START TRANSACTION (or BEGIN WORK, or BEGIN TRANSACTION, depending on SQL dialect) mark the start of a database transaction, which either completes entirely or not at all.
- SAVE TRANSACTION (or SAVEPOINT ) save the state of the database at the current point in transaction
INSERT INTO tbl_1(id) value(1);
INSERT INTO tbl_1(id) value(2);
COMMIT;
UPDATE tbl_1 SET id=200 WHERE id=1;
SAVEPOINT id-1upd;
UPDATE tbl_1 SET id=1000 WHERE id=2;
ROLLBACK TO id-1upd;
SELECT id FROM tbl_1;
- COMMIT causes all data changes in a transaction to be made permanent.
- ROLLBACK causes all data changes since the last COMMIT or ROLLBACK to be discarded, leaving the state of the data as it was prior to those changes.
ACID properties
1. Atomicity - all operations in a txn fail or succeed as a whole
2. Consistency
3. Isolation - all txns run in isolation.
4. Durability - changes in the DB are permanent.
Data control
The Data Control Language (DCL) authorizes users and groups of users to access and manipulate data. Its two main statements are:- GRANT authorizes one or more users to perform an operation or a set of operations on an object.
- REVOKE eliminates a grant, which may be the default grant.
