Structural Query Language(SQL)

Lesson 2
1

WLDU, Department of Information Technology

 Lesson Topics

 SQL Comments
 SQL Data Types
 SQL Constraints
 SQL SELECT Statement
 SQL WHERE clause 2

WLDU, Department of Information Technology

 Lesson objectives
To know the how to give more clarifications for SQL coding
To know the different SQL Data types.
To have a good understanding on SQL Constraints.
To know how those constraints are implemented on the
database.

To understand how data is retrieved from the database.

3

WLDU, Department of Information Technology

 SQL Comments

SQL can be used comments for detail explanations and

clarifications that express what will you do with in SQL
commands
It can be represent by
1. Single comments (--comments )
2. Double line comments/* comments */

WLDU, Department of Information Technology

 SQL Data Types
SQL data type is a characteristics that specifies type of data of
any object.
Each column, variable and expression has related data type in
SQL.
You would use these data types while creating your tables.
You would choose a particular data type for a table column
based on your requirement.
SQL Server offers the following categories of data types for
your use:
5

WLDU, Department of Information Technology

 Data types…
Character String Data Types:
Data Type Description
CHARACTER (n), Fixed-length character string, where n is the number
CHAR(n) of characters.
CHARACTER VARYING(n), Varying-length character string, where n is the
CHAR VARYING(n), maximum number of characters.
VARCHAR (n)

Variable-length character string with a maximum

TEXT length of 2,147,483,647 characters.
6

WLDU, Department of Information Technology

 Data types…
Numeric Data Types:
Data Type Description
INTEGER,
INT, integer numbers of various sizes
SMALLINT
FLOAT, Real numbers of various precision.
REAL, Formatted numbers can be declared by using DECIMAL(i,j )—or DEC( i,j )
DOUBLE PRECISION, or NUMERIC( i,j )
NUMERIC, —where i, the precision, is the total number of decimal digits and j, the
DECIMAL scale, is the number of digits after the decimal point.
Example: 34.33 inserted into a DECIMAL(3,1) is typically rounded to 34.3.

WLDU, Department of Information Technology

 Cont’d....
Date and Time Data Types:
Data Type Description
DATE The DATE data type has ten positions, and its
components are YEAR, MONTH, and DAY typically in the form YYYY-
MM-DD.

The TIME data type has at least eight positions, with the components
HOUR, MINUTE, and SECOND, typically in the form HH:MM:SS.
TIME
includes both the DATE and TIME fields, plus a minimum of six positions
for fractions of seconds and an optional WITH TIME ZONE qualifier.
TIMESTAMP
8

WLDU, Department of Information Technology

 Data types…
Binary Data Types:
Data Type Description
BIT(n), Fixed-length binary data,

BINARY(n) where n is the maximum number of bits.

BIT VARYING(n) , varying length binary data,

VARBINARY(n) where n is the maximum number of bits.

WLDU, Department of Information Technology

 SQL Constraints
Constraints are the rules enforced on data columns on table.
These are used to limit the type of data that can go into a table.
 This ensures the accuracy and reliability of the data in the
database.
Constraints could be column level or table level.
Column level constraints are applied only to one column where
as table level constraints are applied to the whole table.

10
 Constraints…
• Following are commonly used constraints available in SQL:
1. NOT NULL Constraint
2. DEFAULT Constraint
3. UNIQUE Constraint
4. PRIMARY KEY
5. FOREIGN KEY
6. CHECK Constraint
11

WLDU, Department of Information Technology

 Constraints…
1. NOT NULL Constraint:
• By default, a column can hold NULL values.
• If you do not want a column to have a NULL value then you
need to define such constraint on this column specifying that
NULL is now not allowed for that column.
• i.e. it ensures that a column cannot have NULL value.
• A NULL is not the same as no data, rather, it represents unknown
data.

12

WLDU, Department of Information Technology

 Constraints…
• Example: the following SQL creates a new table called
STUDENT and adds six columns, three of which, ID and NAME
and AGE, specify not to accept NULLs:

CREATE TABLE STUDENT(

STUD_ID INT NOT NULL, STUD_NAME VARCHAR (20) NOT
NULL,
STUD_AGE INT NOT NULL, STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5), STUD_GPA DECIMAL (3, 2) );
13

WLDU, Department of Information Technology

 Constraints…
2. DEFAULT Constraint :
• This constraint provides a default value to a column when the INSERT INTO
statement does not provide a specific value.
• Example:
CREATE TABLE STUDENT( STUD_ID INT NOT NULL,
STUD_NAME VARCHAR (20) NOT NULL, STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) , STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2) DEFAULT 3.50, );
• Here STUD_GPA column is set to 3.50 by default, so in case INSERT INTO
statement does not provide a value for this column then by default this column
would be set to 3.50 .

14

WLDU, Department of Information Technology

 Constraints…
3. UNIQUE Constraint :
• i.e. Ensures that all values in a column are different
• Example:
CREATE TABLE STUDENT (
STUD_ID INT NOT NULL UNIQUE, STUD_NAME VARCHAR
(20) NOT NULL,
STUD_AGE INT NOT NULL, STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5), STUD_GPA DECIMAL (3, 2) DEFAULT 3.50,);

• Here STUD_ID column is set to UNIQUE, so that you can not have two
records with same STUD_ID.

15

WLDU, Department of Information Technology

 Constraints…
4. PRIMARY KEY (PK):
• A PK is a field in a table which uniquely identifies each rows/records in
a database table.
• Primary keys must contain unique values.
• A primary key column cannot have NULL values.
• A table can have only one PK which may consist of single or multiple
fields.
• When multiple fields are used as a PK, they are called a composite key.
• If a table has a PK defined on any field(s) then you can not have two
records having the same value of that field(s).
16

WLDU, Department of Information Technology

 Constraints…
• To create a PK constraint on the “STUD_ID" column when
STUDENT table already exists:
• ALTER TABLE STUDENT ADD PRIMARY KEY (STUD_ID);
• For defining a PK constraint on multiple columns:
CREATE TABLE STUDENT(
STUD_ID INT NOT NULL,
STUD_NAME VARCHAR (20) NOT NULL,
STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2) DEFAULT 3.50,
PRIMARY KEY (STUD_ID, STUD_NAME)
);
17

WLDU, Department of Information Technology

 Constraints…
5. FOREIGN KEY (FK):
• A FK is a key used to link two tables together. This is sometimes called a
Referencing key.
• A FK is a field in a table which uniquely identifies each rows/records in
any another database table.
• FK is a column or a combination of columns whose values match a PK
in a different table.
• The relationship between two tables matches the PK in one of the table
with a FK in the second table
18

WLDU, Department of Information Technology

 Example:
Consider the following two tables: STUDENT and
DEPARTMENT

CREATE TABLE DEPARTMENT

CREATE TABLE STUDENT
( (
STUD_ID INT, DEPT_ID INT,
STUD_NAME VARCHAR (20), DEPT_NAME VARCHAR (20),
STUD_AGE INT,
DEPT_LOCATION VARCHAR
STUD_ADDRESS CHAR (25) , (20),
STUD_SEX CHAR (5),
PRIMARY KEY (DEPT_ID)
STUD_GPA DECIMAL (3, 2),
DEPT_ID INT REFERENCES );
DEPARTMENT(DEPT_ID),
PRIMARY KEY (STUD_ID) );
19
;

WLDU, Department of Information Technology

 Constraints…
• If DEPARTMENT table has already been
created, and the foreign key has not yet been, use
the syntax for specifying a foreign key by altering
a table.
ALTER TABLE DEPARTMENT
ADD FOREIGN KEY (STUD_ID) REFERENCES
STUDENT(STUD_ID);
20

WLDU, Department of Information Technology

 Constraints…
6. CHECK Constraint:
• It enables a condition to check the value being entered
into a record.
 i.e. It ensures that all values in a column satisfy certain
conditions.
• If the condition evaluates to false, the record violates
the constraint and isn’t entered into the table.
21

WLDU, Department of Information Technology

 Constraints…
• Example:
CREATE TABLE STUDENT
(
STUD_ID INT,
STUD_NAME VARCHAR (20),
STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2) CHECK (STUD_GPA >= 2.00),
PRIMARY KEY (STUD_ID)
);

• Here we add a CHECK with STUD_AGE column, so that you cannot have any student below 18 years.
22

WLDU, Department of Information Technology

 Exercise
Create two tables called DEPARTMENT and EMPLOYEE
DEPARTMENT(Dept_ID, Dept_Name, Dept_Location)
EMPLOYEE(Emp_ID, Emp_Fname, Emp_Lname, Emp_Sex, Emp_Age, Emp_Salary, Dept_ID)
Note:
- Bold and underline attribute (s)refers that it is a primary key.
- bold and italics attribute(s) refers that it is a foreign key.
Make sure that:
 The Employee salary, first name and Department name should not allow a null value.
 The employee age should not be null and less than 18 years.
 Set a default value for the Department name to “computer science”.
And finally insert at least 4 records of data to both tables.

23

WLDU, Department of Information Technology

 SQL SELECT STATEMENT

• used to retrieve data or information from table (database).

• used to fetch the data from a database table which returns data in the form
of result table.

• Syntax:
SELECT <attribute list> FROM <table list>

• <attribute list> is a list of attribute names whose values are to be

retrieved by the query.

• <table list> is a list of the relation names required to process the query.
24

WLDU, Department of Information Technology

 SELECT…
• Example: to fetch only ID, First name and Sex of Employee from employee table:
• SELECT Emp_ID,Emp_Fname,Emp_Sex FROM EMPLOYEE

 If you want to fetch all the fields available in the table then you can use following syntax:

• SELECT * FROM table_name;

• Example: to fetch all fields available in the table DEPARTMENT

• SELECT * FROM DEPARTMENT;

• The Astrix (*) symbol stands for all the attributes.

25

WLDU, Department of Information Technology

 SQL DISTINCT keyword

• To eliminate duplicate tuples in a query result, the keyword

DISTINCT is used.
• For example, the result of Q1 may have duplicate SALARY
values where as Q2 does not have any duplicate values.
Q1: SELECT Emp_Salary
FROM EMPLOYEE
Q2: SELECT DISTINCT Emp_Salary
FROM EMPLOYEE

26

WLDU, Department of Information Technology

 END OF THE LESSON!!

27

WLDU, Department of Information Technology