Asha's Blog

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. SQL uses Null to handle missing information.

DML is the subset of SQL used to add, update and delete data:

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’;
MERGE is used to combine the data of multiple tables. It combines the INSERT and UPDATE elements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called “upsert”.

The most common operation in SQL is the query, which is performed with the declarative SELECT statement. SELECT retrieves data from one or more tables, or expressions.

A query includes a list of columns to be included in the final result immediately following the SELECT keyword. An asterisk (“*”) can also be used to specify that the query should return all columns of the queried tables. SELECT is the most complex statement in SQL, with optional keywords and clauses that include:

• The FROM clause which indicates the table(s) from which data is to be retrieved. The FROM clause can include optional JOIN subclauses to specify the rules for joining tables.
• The WHERE clause includes a comparison predicate, which restricts the rows returned by the query. The WHERE clause eliminates all rows from the result set for which the comparison predicate does not evaluate to True.
• The GROUP BY clause is used to project rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause.
• The HAVING clause includes a predicate used to filter rows resulting from 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.
• The ORDER BY clause identifies which columns are used to sort the resulting data, and in which direction they should be sorted (options are ascending or descending). Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.

RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collections of related data entries and it consists of columns and rows.