SQL breezer

Here is a short SQL breezer i made for revising SQL for interviews.

SQL SELECT command syntax :
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 adds rows (formally tuples) to an existing table, e.g.,:

INSERT INTO My_table
       (field1, field2, field3)
   VALUES
       ('test', 'N', NULL);

• UPDATE modifies a set of existing table rows, e.g.,:

UPDATE My_table
   SET field1 = 'updated value'
   WHERE field2 = 'N';

• DELETE removes existing rows from a table, e.g.,:

DELETE FROM My_table
   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.,:

CREATE TABLE My_table(
   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.,:

ALTER TABLE My_table ADD my_field4 NUMBER(3) NOT NULL;

• 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.

TRUNCATE TABLE My_table;

• DROP deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back, e.g.,:

DROP TABLE My_table;

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.

p AND q p True False Unknown q True True False Unknown False False False False Unknown Unknown False 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

CREATE TABLE tbl_1(id int);
 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.

Once the COMMIT statement completes, the transaction's changes cannot be rolled back.

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.