SDF II(15B11CI211)

EVEN Semester 2021

2nd Semester , First Year

Jaypee Institute Of Information Technology
(JIIT), Noida
•1
Topics Covered
• An Overview of SQL
• Data Types
• SQL constraints
• Create Table

•2
An Overview of SQL
SQL stands for Structured Query Language.
• It is the most commonly used relational database
language today.
• SQL is a standard language for storing, manipulating
and retrieving data in databases.

•3
SQL is used for:

• Data Manipulation
• Data Definition
• Data Administration
• All operations are expressed as an SQL statement or
command.

•4
 SQL Requirements
• SQL Must be embedded in a programming language or a
web page.
• SQL is a free form language so there is no limit to the
number of words per line or fixed line break.
• Syntax statements, words or phrases are always in
lower case; keywords are in uppercase.

Not all versions are case sensitive!

•5
SQL is a Relational Database
• Represent all info in database as tables
• Keep logical representation of data independent from its
physical storage characteristics
• Use one high-level language for structuring, querying, and
changing info in the database
• Support the main relational operations
• Support alternate ways of looking at data in tables
• Provide a method for differentiating between unknown
values and nulls (zero or blank)
• Support Mechanisms for integrity, authorization,
transactions, and recovery

•6
What Can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views
•7
Data Types
• Data types are used to represent the nature of the data that can be stored
in the database table. For example, in a particular column of a table, if we
want to store a string type of data then we will have to declare a string
data type of this column.
Data types mainly classified into three categories for every database.
• String Data types
• Numeric Data types
• Date and time Data types

•8
Data Types (continued)

•9
SQL Constraints
• NOT NULL constraint
– Ensures that column does not accept nulls
• UNIQUE constraint
– Ensures that all values in column are unique
• DEFAULT constraint
– Assigns value to attribute when a new row is added to
table
• CHECK constraint
– Validates data when attribute value is entered

•10
SQL DML and DDL
• SQL can be divided into two parts: The Data
Manipulation Language (DML) and the Data Definition
Language (DDL).
• The query and update commands form the DML part
of SQL:
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database

•11
The most important DDL statements in
SQL are:
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

•12
SQL CREATE TABLE Syntax

CREATE TABLE table_name

(
column_name1 data_type constraint if any,
column_name2 data_type constraint if any,
column_name3 data_type constraint if any,
....
);

CREATE TABLE Persons

(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
•13
Specifying SQL Constraints

Constraints are used to limit the type of data

that can go into table.
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

•14
NOT NULL Constraints in SQL
NOT NULL : By default, a column can hold
NULL. If you don’t want to allow NULL value in
a column, you will have to place a constraint
on this column.

CREATE TABLE Customer

(
SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30)
);

•15
 Not Null Constraint
CREATE TABLE Employee (
Employee_Id Integer,
Employee_Name VARCHAR2(20) NOT NULL,
Employee_Address VARCHAR2(20) NOT NULL,
);

•16
UNIQUE
• The UNIQUE constraint ensures that all values
in a column are distinct.
CREATE TABLE Customer
(
SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30)
);

•17
SQL UNIQUE Constraint
• The UNIQUE and PRIMARY KEY constraints
both provide a guarantee for uniqueness for a
column or set of columns.
• A PRIMARY KEY constraint automatically has a
UNIQUE constraint defined on it.
• You can have many UNIQUE constraints per
table, but only one PRIMARY KEY constraint
per table.

•18
CHECK

• The CHECK constraint ensures that all values

in a column satisfy certain conditions.
CREATE TABLE Customer
(
SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30)
);

•19
 Check Constraint
 We can define a CHECK constraint in a single column as well as in a
table.
 If we define a CHECK constraint on a single column it allows only
certain range of values for this column.
 If you define a CHECK constraint on a table it can limit the values in
certain columns based on values in other columns in the row.

•20
 Check Constraint

CREATE TABLE Employee (

Employee_Id Integer NOT NULL CHECK(Employee_Id >0),
Employee_Name VARCHAR(20),
Employee_City VARCHAR(20),
);

•21
 Check Constraint

CREATE TABLE Employee (

Employee_Id Integer NOT NULL,
Employee_Name VARCHAR(20),
Employee_City VARCHAR(20),
Constraints emp_check CHECK(Employee_Id >0)
);

•22
 Check Constraint(Multiple
columns)
CREATE TABLE Employee (
Employee_Id Integer NOT NULL,
Employee_Name VARCHAR(20) NOT NULL,
Employee_City VARCHAR(20),
Employee_Age Integer,
Constraints emp_check CHECK(Employee_Age >20 AND
Employee_City=‘Mumbai’)
);

•23
Creating Primary key constraint

CREATE TABLE Persons (

P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk PRIMARY KEY (P_Id,LastName)
)

•24
Primary Key Constraint
CREATE TABLE Employee (
Employee_Id Integer PRIMARY KEY,
Employee_Name VARCHAR(20) NOT NULL,
Employee_Address VARCHAR(20) NOT NULL,
);

•Primary Key implies: * NOT NULL * UNIQUE.

•There can only be one primary key.

•25
 Primary Key Constraint (Syntax 2)
CREATE TABLE Employee (
Employee_Id Integer,
Employee_Name VARCHAR(20) NOT NULL,
Employee_ Address VARCHAR(20) NOT NULL,
PRIMARY KEY(Employee_Id)
);

•Needed when the primary key is made up of two or more fields

•26
Creating Foreign Key
• A foreign key is a field (or fields) that points to
the primary key of another table.
• The purpose of the foreign key is to ensure
referential integrity of the data.
CREATE TABLE ORDERS
(
Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID)
);
•27
Naming the constraints
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

•28
 Default Constraints

 This constraint is used to insert a default value into a column.

 The default value will be added to all new records, if no other value is
specified.

CREATE TABLE Employee (

Employee_Id Integer,
Employee_Name VARCHAR(20) NOT NULL,
Employee_ City VARCHAR(20) DEFAULT ‘Mumbai’);

•29
Example: Create a CUSTOMERS table having some
attributes like ID, NAME,AGE,ADDRESS,SALARY with
ID as a primary key and NOT NULL as the constraints
in ID, NAME and AGE that will show these fields
cannot be NULL.
Solution:
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

•30
 References
• Elamsari and Navathe, Fundamentals of
Database Management systems
• Silberschatz−Korth−Sudarshan, Database System
Concepts, Fourth Edition
• https://www.javatpoint.com/
• https://www.sqlshack.com/
• https://www.tutorialspoint.com/

•31