SQL Constraints

SQL constraints are used to specify rules for the data in a table.

If there is any violation between the constraint and the data action, the action is aborted by the
constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or
after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name

(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

In SQL, we have the following constraints:

 NOT NULL - Indicates that a column cannot store NULL value

 UNIQUE - Ensures that each row for a column must have a unique value
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or
combination of two or more columns) have a unique identity which helps to find a particular
record in a table more easily and quickly
 FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in
another table
 CHECK - Ensures that the value in a column meets a specific condition
 DEFAULT - Specifies a default value for a column

Introduction to SQL

Structure Query Language (SQL) is a programming language used for storing and managing data
in RDBMS. SQL was the first commercial language introduced for E.F Codd's Relational
model. Today almost all RDBMS (MySql, Oracle, Infomix, Sybase, MS Access) uses SQL as
the standard database language. SQL is used to perform all type of data operations in RDBMS.

SQL (Structured Query Language) is a special-purpose programming language designed for

managing data held in a relational database management system (RDBMS), or for stream
processing in a relational data stream management system (RDSMS).

Originally based upon relational algebra and tuple relational calculus, SQL consists of a data
definition language, data manipulation language, and Data Control Language. The scope of SQL
includes data insert, query, update and delete, schema creation and modification, and data access
control. Although SQL is often described as, and to a great extent is, a declarative language
(4GL), it also includes procedural elements.
SQL was one of the first commercial languages for Edgar F. Codd's relational model, as
described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data
Banks."[10] Despite not entirely adhering to the relational model as described by Codd, it became
the most widely used database language.[11][12]

SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the
International Organization for Standardization (ISO) in 1987.[13] Since then, the standard has
been revised to include a larger set of features. Despite the existence of such standards, most
SQL code is not completely portable among different database systems without adjustments.

SQL Command

Database languages are used for read, update and store data in a database. There are several such
languages that can be used for this purpose; one of them is SQL (Structured Query Language).

SQL defines following data languages to manipulate data of RDBMS.

A DBMS must provide appropriate languages and interfaces for each category of users to
express database queries and updates. Database Languages are used to create and maintain
database on computer. There are large numbers of database languages like Oracle, MySQL, MS
Access, dBase, FoxPro etc. SQL statements commonly used in Oracle and MS Access can be
categorized as data definition language (DDL), data control language (DCL) and data
manipulation language (DML).

DDL : Data Definition Language

All DDL commands are auto-committed. That means it saves all the changes permanently in the
database.

Command Description
create to create new table or database
alter for alteration
truncate delete data from table
drop to drop a table
rename to rename a table

DML : Data Manipulation Language

DML is used for accessing and manipulating data in a database.

DML commands are not auto-committed. It means changes are not permanent to database, they
can be rolled back.
Command Description
SELECT To read records from table(s)
insert to insert a new row
update to update existing row
delete to delete a row
merge merging two rows or two tables

TCL : Transaction Control Language

These commands are to keep a check on other commands and their affect on the database. These
commands can annul(undo or invalid) changes made by other commands by rolling back to
original state. It can also make changes permanent.

Command Description
commit to permanently save
rollback to undo change
savepoint to save temporarily

DCL : Data Control Language

DCL is used for granting and revoking user access on a database –

Data control language provides command to grant and take back authority.

Command Description
grant grant permission of right
revoke take back permission.

DQL : Data Query Language

Command Description
select retrieve records from one or more table

SQL Operators

Operator Description Example

= Equal to Author = 'Alcott'
Not equal to (many DBMSs accept != in
<> Dept <> 'Sales'
addition to <>)
> Greater than Hire_Date > '2012-01-31'
< Less than Bonus < 50000.00
>= Greater than or equal Dependents >= 2
<= Less than or equal Rate <= 0.05
BETWEEN Between an inclusive range Cost BETWEEN 100.00 AND 500.00
LIKE Match a character pattern First_Name LIKE 'Will%'
IN Equal to one of multiple possible values DeptCode IN (101, 103, 209)
IS or IS NOT Compare to null (missing data) Address IS NOT NULL
IS NOT DISTINCT Is equal to value or both are nulls Debt IS NOT DISTINCT FROM -
FROM (missing data) Receivables
AS Used to change a field name when SELECT employee AS
 viewing results 'department1'

Other operators have at times been suggested and/or implemented, such as the skyline operator
(for finding only those records that are not 'worse' than any others).

SQL has the case/when/then/else/end expression, which was introduced in SQL-92. In its
most general form, which is called a "searched case" in the SQL standard, it works like else if in
other programming languages:

CASE WHEN n > 0

THEN 'positive'
WHEN n < 0
THEN 'negative'
ELSE 'zero'
END

SQL tests WHEN conditions in the order they appear in the source. If the source does not specify
an ELSE expression, SQL defaults to ELSE NULL. An abbreviated syntax—called "simple case" in
the SQL standard—mirrors switch statements:

CASE n WHEN 1
THEN 'one'
WHEN 2
THEN 'two'
ELSE 'I cannot count that high'
END

This syntax uses implicit equality comparisons, with the usual caveats for comparing with
NULL.

For the Oracle-SQL dialect, the latter can be shortened to an equivalent DECODE construct:

SELECT DECODE(n, 1, 'one',

2, 'two',
'i cannot count that high')
FROM some_table;

The last value is the default; if none is specified, it also defaults to NULL. However, unlike the
standard's "simple case", Oracle's DECODE considers two NULLs equal with each other.