There are a lot of questions in DB and SQL topic. I put here some simple and tricky ones.
GROUP BY is not used, the
HAVING clauses are essentially equivalent.
GROUP BY is used:
WHERE clause is used to filter records from a result. The filtering occurs before any
groupings are made.
HAVING clause is used to filter values from a group.
TRUNCATE is DDL (Data Definition Language) command and so cannot be rolled back. It deletes all
rows in a table.
DELETE is Data Manipulation Language (DML) command and can be rolled back. It deletes rows in a
table that satisfy
Atomicity or "all or nothing". If one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.
Consistency. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
Isolation. The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially.
Durability. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.
Execution plan is strategy of fetching data and preparing answer for a query.
It can be shown by query with
EXPLAIN as prefix. Result of such query may differ from actual
execution plan and some DB stores history of queries with plans.
Most operations with
NULL <> 1 and
NULL = 1 and
NULL = NULL are
UNDEFINED. To check for
IS NOT NULL.
FALSE argument return
FALSE despite any other arguments values including
TRUE argument return
TRUE despite any other arguments values including
This query won't return rows with
SELECT name FROM customers WHERE patent <> 2;
and should be rewritten into:
SELECT name FROM customers WHERE patent <> 2 OR patent in NULL;
NOT IN always
FALSE if sub-table has
NULL. This query may be resolved in empty
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders)
NULL for some
orders. Correct query is:
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)
INNER JOIN (default type of join if no specific JOIN type is specified),
LEFT OUTER JOIN),
RIGHT JOIN (or
RIGHT OUTER JOIN),
FULL JOIN (or
OUTER JOIN), and
UNION omit duplicate records while
UNION ALL preserve all of them.