SQL Tutorials

In this course, we will study the domain types in SQL, constraints and the SQL query structure. So let us start.

What are the domain types in SQL?

char(n): It represents a character string of fixed length, with user-specified length n.

varchar(n): It represents a variable-length character string with maximum length n.

int.: It is a finite subset of the integers that is dependent on the machine.

Smallint: It represents a small integer which is a machine-dependent subset of the integer domain type.

numeric(p,d): It represents a fixed-point number in which the user specifies the precision of p digits, with n digits to the right of the decimal point.

real, double precision: It represents floating-point and double-precision floating-point numbers.

float(n).: It represents a floating-point number in which the user specifies precision.

What is the basic SQL query structure?

A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
Ai represents an attribute
Ri represents a relation
P is a predicate.

Let us study about each clause in detail:

Select clause

The select clause list all the attributes desired in the result of a query that is corresponding to the projection operation of the algebra.
Example: find the names of all employees in the employee relation:

Select emloyee_name
From employee
In the relational algebra, the query is:
IIemployee_name(employee)

Where clause

The where clause specifies conditions that need to be satisfied by the result. It Corresponds to the selection predicate of the algebra.

To find all student_id from student table who have marks greater than 200
Select student_id
From student
Where marks>200

From clause

The from clause lists all the relations which are involved in the query. It Corresponds to the Cartesian product operation of the algebra.

Find the Cartesian product student X marks.
select *
From student,marks

What are the constraints in SQL?

SQL constraints are used to specifying the rules for the data inside the table. In case of any violation between the constraint and the action, the action is aborted immediately by the constraint. Constraints can be specified at any point that is at the time when the table is created or after the table is created.

Constraints in SQL

NOT NULL

This constraint stops us from storing a null value in a column. That is, in case if a column is specified as NOT NULL then it won’t be able to store null in the particular column anymore.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(15) NOT NULL,
ADDRESS varchar(25)
);

UNIQUE

When this constraint is specified with a column then all the values in the column must be unique. This means that the values in any row of a column must not be repeated.

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(15) NOT NULL,
ADDRESS varchar(25)
);

PRIMARY KEY

A primary key is a field that is used to uniquely identify each row in a table. And by using this constraint we can specify a field in a table as the primary key.

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(15) NOT NULL,
ADDRESS varchar(25),
PRIMARY KEY(
ID)
);

FOREIGN KEY

A Foreign key is a field that is used to uniquely identify each row in another table. And by using this constraint we can specify a field as Foreign key.

CREATE TABLE Student
(
O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,
PRIMARY KEY (O_ID),
FOREIGN KEY(C_ID) REFERENCES Customers(C_ID)
);

CHECK

This constraint helps us to validate the values of a column. That is, it ensures that the value stored in a column meets the given conditions.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE>=25) varchar(25)
);

DEFAULT

When no value is specified by the user, this constraint specifies a default value for the column.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(15) NOT NULL,
AGE int DEFAULT 17
);