Unit 2

SQL BASICS
3.1 Introduction:

SQL stands for Structured Query Language. SQL is the language used to create, edit

and manipulate a database. In other words, SQL is used to manage data held within

a relational database management system (RDBMS).

The topics covered in this blog are mainly divided into 4 categories:

● Data Definition Language (DDL) – Consists of commands which are used to

define the database.

● Data Manipulation Language (DML) – Consists of commands which are

used to manipulate the data present in the database.

 ● Data Control Language (DCL) – Consists of commands which deal with

the user permissions and controls of the database system.

● Transaction Control Language (TCL) – Consist of commands which deal

with the transaction of the database.

3.2 Comments in SQL

There are two ways in which you can comment in SQL, i.e. either the Single

-Line Comments or the Multi-Line Comments.

Single-Line Comments

The single line comment starts with two hyphens (–). So, any text mentioned after

(–), till the end of a single line will be ignored by the compiler.

Example:

--Select all:

SELECT * FROM Employee Info;

Multi-Line Comments

The Multi-line comments start with /* and end with */. So, any text mentioned

between /* and */ will be ignored by the compiler.

Example:

/*Select all the

columns of all the

records

from the Employee Info table:*/

SELECT * FROM Students;

3.3 Data Definition Language (DDL)

Data definition language is one of the subcategories of SQL. It is used to define and

work with the database schema (structure). This includes the attributes (columns)

within each table, the name of each table, the name of the database, and the

connection of keys between tables. Here are general explanations of the types of

commands in DDL:
▪
CREATE – used to create the database, the tables, and the columns within each
table. Within the create statement we also define the data type of each column.
A data type is literally the type of data we are supposed to store within each
column, whether it be an

integer, a date, or a string.

▪ ALTER – used to alter existing database structures. This includes adding

columns and more.

▪ RENAME – This is used to…rename.

▪ DROP – This is used to destroy your database or table.

▪ CREATE
▪ DROP
▪ TRUNCATE
▪ ALTER
▪ BACKUP DATABASE

CREATE

This statement is used to create a table or a database.

The ‘CREATE DATABASE’ Statement

As the name suggests, this statement is used to create a database.

Syntax

CREATE DATABASE DatabaseName;

Example

CREATE DATABASE Employee;

The ‘CREATE TABLE’ Statement

This statement is used to create a

table.

Syntax

CREATE TABLE TableName

( Column1 datatype,
Column2

datatype,

Column3

datatype,

....

ColumnN datatype

);

Example

CREATE TABLE Employee_Info

EmployeeIDint,

EmployeeNamevarchar(255),

Emergency

ContactNamevarchar(255),

PhoneNumberint,

Address

varchar(255), City

varchar(255),

Country

varchar(255)

);

You can also create a table using another table. Refer the below syntax and example:

The ‘CREATE TABLE AS’ Statement

Syntax

CREATE TABLE NewTableName AS

SELECT Column1, column2,...,ColumnN

FROM ExistingTableName

WHERE ;

Example
CREATE TABLE ExampleTable AS

SELECT EmployeeName, PhoneNumber

FROM Employee_Info;

DROP

This statement is used to drop an existing table or a database.

The ‘DROP DATABASE’ Statement

This statement is used to drop an existing database. When you use this statement,

complete information present in the database will be lost.

Syntax

DROP DATABASE DatabaseName;

Example

DROP DATABASE Employee;

The ‘DROP TABLE’ Statement

This statement is used to drop an existing table. When you use this statement, complete

information present in the table will be lost.

Syntax

DROP TABLE TableName;

Example

DROP Table Employee_Info;

TRUNCATE

This command is used to delete the information present in the table but does not

delete the table. So, once you use this command, your information will be lost, but

not the table.

Syntax

TRUNCATE TABLE TableName;

Example

TRUNCATE Table Employee_Info;

ALTER

This command is used to delete, modify or add constraints or columns in an existing table.
The ‘ALTER TABLE’ Statement

This statement is used to add, delete, modify columns in an existing table.

The ‘ALTER TABLE’ Statement with ADD/DROP COLUMN

You can use the ALTER TABLE statement with ADD/DROP Column command

according to your need. If you wish to add a column, then you will use the ADD

command, and if you wish to delete a column, then you will use the DROP COLUMN

command.

Syntax

ALTER TABLE TableName

ADD ColumnName Datatype;

ALTER TABLE TableName

DROP COLUMN ColumnName;

Example

--ADD Column BloodGroup:

ALTER TABLE Employee_Info

ADD BloodGroupvarchar(255);

--DROP Column BloodGroup:

ALTER TABLE Employee_Info

DROP COLUMN BloodGroup ;

The ‘ALTER TABLE’ Statement with ALTER/MODIFY COLUMN

This statement is used to change the datatype of an existing column in a table.

Syntax

ALTER TABLE TableName

ALTER COLUMN ColumnName Datatype;

Example

--Add a column DOB and change the data type to Date.

ALTER TABLE Employee_Info

ADD DOB year;

ALTER TABLE Employee_Info

ALTER DOB date;

BACKUP DATABASE

This statement is used to create a full backup of an existing database.

Syntax

BACKUP DATABASE

DatabaseName TO DISK = 'filepath';

Example

BACKUP DATABASE Employee

TO DISK = 'C:UsersSahitiDesktop';

You can also use a differential back up. This type of back up only backs up the

parts of the database, which have changed since the last complete backup of the

database.

Syntax

BACKUP DATABASE

DatabaseName TO DISK = 'filepath'

WITH DIFFERENTIAL;

Example

BACKUP DATABASE Employee

TO DISK = 'C:UsersSahitiDesktop'

WITH DIFFERENTIAL;
Now that you know the data definition commands, let me take you through the

various types of Keys and Constraints that you need to understand before learning

how to manipulate the databases.

3.4 Data Manipulation Language (DML)

Data manipulation language is used to work with the actual data within the database.

if we looked at an example with a users table, the table is created with DDL while the

value “Caleb Curry” is entered using DML.

The main statement in DML is:

▪ SELECT – this is used to select data from our database. We first say SELECT and

then we say what columns to select. After we say what columns, we specify what

tables using FROM. After we select what columns and what tables we can limit

our results using a WHERE clause. Example: SELECT user_id FROM users

WHERE user_id = 5;.

▪ INSERT INTO – This is used to insert new values.

▪ UPDATE – This is used to change values.
▪ DELETE – this is used to delete values (the database structure stays the
same, only inserted values are removed).

The SQL data manipulation language (DML) is used to query and modify

database data. In this chapter, we will describe how to use the SELECT, INSERT,

UPDATE, and DELETE SQL DML command statements, defined below.

● SELECT – to query data in the database

● INSERT – to insert data into a table

● UPDATE – to update data in a table

● DELETE – to delete data from a

table In the SQL DML statement:

● Each clause in a statement should begin on a new line.

● The beginning of each clause should line up with the beginning of other clauses.
● If a clause has several parts, they should appear on separate lines and be

indented under the start of the clause to show the relationship.

● Upper case letters are used to represent reserved words.

● Lower case letters are used to represent user-defined words.

SELECT Statement

The SELECT statement, or command, allows the user to extract data from tables,

based on specific criteria. It is processed according to the following sequence:

SELECT DISTINCT item(s)

FROM table(s)

WHERE predicate

GROUP BY field(s)

ORDER BY fields

We can use the SELECT statement to generate an employee phone list

from the Employees table as follows:

SELECT FirstName, LastName,

phone FROM Employees

ORDER BY LastName

This action will display employee’s last name, first name, and phone number

from the Employees table, seen in Table 1.1.

Last Name First Name Phone Number

Hagans Jim 604-232-3232

Wong Bruce 604-244-2322

Table 1.1.Employees table.

In this next example, we will use a Publishers table (Table 1.2). (You will notice

that Canada is mispelled in the Publisher Country field for Example Publishing

and ABC Publishing. To correct mispelling, use the UPDATE statement to

standardize the country field to Canada – see UPDATE statement later in this

chapter.)

Publisher Publisher
Publisher Name Publisher
City Province Country

Acme Publishing Vancouver BC Canada

Example
Edmonton AB Cnada
Publishing

ABC Publishing Toronto ON Canda

Table 1.2.Publishers table.

If you add the publisher’s name and city, you would use the SELECT statement

followed by the fields name separated by a comma:

SELECT PubName, city

FROM Publishers

This action will display the publisher’s name and city from the Publishers table.

If you just want the publisher’s name under the display name city, you would use

the SELECT statement with no comma separating pub_name and city:

SELECT PubName city

FROM Publishers

Performing this action will display only the pub_name from the Publishers table

with a “city” heading. If you do not include the comma, SQL Server assumes you

want a new column name for pub_name.

SELECT statement with WHERE criteria

Sometimes you might want to focus on a portion of the Publishers table, such as

only publishers that are in Vancouver. In this situation, you would use the SELECT

statement with the WHERE criterion, i.e., WHERE city = ‘Vancouver’.

These first two examples illustrate how to limit record selection with the WHERE

criterion using BETWEEN. Each of these examples give the same results for store

items with between 20 and 50 items in stock.

Example #1 uses the quantity, qty BETWEEN 20 and 50.

SELECT StorID, qty, TitleID

FROM Sales

WHERE qty BETWEEN 20 and 50 (includes the 20 and 50)

Example #2, on the other hand, uses qty>=20 and qty<=50 .

SELECT StorID, qty, TitleID

FROM Sales

WHERE qty>= 20 and qty <= 50

Example #3 illustrates how to limit record selection with the WHERE criterion

using NOT BETWEEN.

SELECT StorID, qty, TitleID

FROM Sales

WHERE qty NOT BETWEEN 20 and 50

The next two examples show two different ways to limit record selection

with the WHERE criterion using IN, with each yielding the same results.

Example #4 shows how to select records using province= as part of the

WHERE statement.
SELECT *

FROM Publishers

WHERE province = ‘BC’ OR province = ‘AB’ OR province = ‘ON’

Example #5 select records using province IN as part of the WHERE statement.

SELECT *

FROM Publishers

WHERE province IN (‘BC’, ‘AB’, ‘ON’)

The final two examples illustrate how NULL and NOT NULL can be used to select

records. For these examples, a Books table (not shown) would be used that

contains fields called Title, Quantity, and Price (of book). Each publisher has a

Books table that lists all of its books.

Example #6 uses NULL.

SELECT price, title

FROM Books

WHERE price IS NULL

Example #7 uses NOT NULL.

SELECT price, title

FROM Books

WHERE price IS NOT NULL

Using wildcards in the LIKE clause

The LIKE keyword selects rows containing fields that match specified portions of

character strings. LIKE is used with char, varchar, text, datetime and

smalldatetime data. A wildcard allows the user to match fields that contain

certain letters. For example, the wildcard province = ‘N%’ would give all

provinces that start with the letter ‘N’. Table

16.3 shows four ways to specify wildcards in the SELECT statement in regular

express format.

% Any string of zero or more characters

_ Any single character

Any single character within the specified range (e.g., [a-

[]
f]) or set (e.g., [abcdef])

Any single character not within the specified range

[^]
(e.g., [^a – f]) or set (e.g., [^abcdef])

Table 1.3. How to specify wildcards in the SELECT statement.

In example #1, LIKE ‘Mc%’ searches for all last names that begin with the letters

“Mc” (e.g., McBadden).

SELECT

LastName FROM

Employees

WHERE LastName LIKE ‘Mc%’

For example #2: LIKE ‘%inger’ searches for all last names that end with the

letters “inger” (e.g., Ringer, Stringer).

SELECT

LastName FROM

Employees

WHERE LastName LIKE ‘%inger’

In, example #3: LIKE ‘%en%’ searches for all last names that have the letters “en”

(e.g., Bennett, Green, McBadden).

SELECT

LastName FROM

Employees

WHERE LastName LIKE ‘%en%’ SELECT

statement with ORDER BY clause

You use the ORDER BY clause to sort the records in the resulting list. Use ASC

to sort the results in ascending order and DESC to sort the results in descending

order.

For example, with ASC:

SELECT *

FROM Employees

ORDER BY HireDate ASC

And with DESC:

SELECT *

FROM Books

ORDER BY type, price DESC

SELECT statement with GROUP BY clause

The GROUP BY clause is used to create one output row per each group and

produces summary values for the selected columns, as shown below.

SELECT type

FROM Books

GROUP BY type

Here is an example using the above statement.

SELECT type AS ‘Type’, MIN(price) AS ‘Minimum Price’

FROM Books
WHERE royalty > 10

GROUP BY type

If the SELECT statement includes a WHERE criterion where price is not null,

SELECT type, price

FROM Books

WHERE price is not null

then a statement with the GROUP BY clause would look like this:

SELECT type AS ‘Type’, MIN(price) AS ‘Minimum Price’

FROM Books

WHERE price is not null

GROUP BY type

Using COUNT with GROUP BY

We can use COUNT to tally how many items are in a container. However, if we

want to count different items into separate groups, such as marbles of varying

colours, then we would use the COUNT function with the GROUP BY command.

The below SELECT statement illustrates how to count groups of data using the

COUNT function with the GROUP BY clause.

SELECT COUNT(*)

FROM Books

GROUP BY type

Using AVG and SUM with GROUP BY

We can use the AVG function to give us the average of any group, and SUM to

give the total.

Example #1 uses the AVG FUNCTION with the GROUP BY type.

SELECT AVG(qty)

FROM Books

GROUP BY type

Example #2 uses the SUM function with the GROUP BY type.

SELECT SUM(qty)

FROM Books

GROUP BY type

Example #3 uses both the AVG and SUM functions with the GROUP BY type

in the SELECT statement.

SELECT ‘Total Sales’ = SUM(qty), ‘Average Sales’ = AVG(qty), stor_id

FROM Sales

GROUP BY StorID ORDER BY ‘Total Sales’

Restricting rows with HAVING

The HAVING clause can be used to restrict rows. It is similar to the WHERE

condition except HAVING can include the aggregate function; the WHERE cannot

do this.

The HAVING clause behaves like the WHERE clause, but is applicable to groups.

In this example, we use the HAVING clause to exclude the groups with the

province ‘BC’.

SELECT au_fname AS ‘Author”s First Name’, province as ‘Province’

FROM Authors

GROUP BY au_fname, province

HAVING province <> ‘BC’

INSERT statement

The INSERT statement adds rows to a table. In addition,

● INSERT specifies the table or view that data will be inserted into.

● Column_list lists columns that will be affected by the INSERT.

● If a column is omitted, each value must be provided.

● If you are including columns, they can be listed in any order.

● VALUES specifies the data that you want to insert into the table.

VALUES is required.

● Columns with the IDENTITY property should not be explicitly listed in

the column_list or values_clause.

The syntax for the INSERT statement is:

INSERT [INTO] Table_name | view name [column_list]

DEFAULT VALUES | values_list | select statement

When inserting rows with the INSERT statement, these rules apply:

● Inserting an empty string (‘ ‘) into a varchar or text column inserts a single space.

● All char columns are right-padded to the defined length.

● All trailing spaces are removed from data inserted into varchar columns,

except in strings that contain only spaces. These strings are truncated to a

single space.

● If an INSERT statement violates a constraint, default or rule, or if it is the

wrong data type, the statement fails and SQL Server displays an error

message.

When you specify values for only some of the columns in the column_list, one

of three things can happen to the columns that have no values:

1. A default value is entered if the column has a DEFAULT constraint, if a

default is bound to the column, or if a default is bound to the underlying user-

defined data type.

2. NULL is entered if the column allows NULLs and no default value exists

for the column.

3. An error message is displayed and the row is rejected if the column is defined

as NOT NULL and no default exists.

This example uses INSERT to add a record to the publisher’s Authors table.
INSERT INTO Authors

VALUES(‘555-093-467’, ‘Martin’, ‘April’, ‘281 555-5673’, ‘816 Market St.,’ ,

‘Vancouver’, ‘BC’, ‘V7G3P4’, 0)

This following example illustrates how to insert a partial row into the Publishers

table with a column list. The country column had a default value of Canada so it

does not require that you include it in your values.

INSERT INTO Publishers (PubID, PubName, city, province)

VALUES (‘9900’, ‘Acme Publishing’, ‘Vancouver’, ‘BC’)

To insert rows into a table with an IDENTITY column, follow the below example.

Do not supply the value for the IDENTITY nor the name of the column in the

column list.

INSERT INTO jobs

VALUES (‘DBA’, 100, 175)

Inserting specific values into an IDENTITY column

By default, data cannot be inserted directly into an IDENTITY column; however, if

a row is accidentally deleted, or there are gaps in the IDENTITY column values,

you can insert a row and specify the IDENTITY column value.

IDENTITY_INSERT option

To allow an insert with a specific identity value, the IDENTITY_INSERT option can

be used as follows.

SET IDENTITY_INSERT jobs ON

INSERT INTO jobs (job_id, job_desc, min_lvl, max_lvl)

VALUES (19, ’DBA2’, 100, 175)

SET IDENTITY_INSERT jobs OFF

Inserting rows with a SELECT statement

We can sometimes create a small temporary table from a large table. For this, we

can insert rows with a SELECT statement. When using this command, there is no

validation for uniqueness. Consequently, there may be many rows with the same

pub_id in the example below.

This example creates a smaller temporary Publishers table using the CREATE

TABLE statement. Then the INSERT with a SELECT statement is used to add

records to this temporary Publishers table from the publis table.

CREATE TABLE dbo.tmpPublishers

( PubID char (4) NOT NULL ,

PubName varchar (40) NULL ,

city varchar (20) NULL ,

province char (2) NULL

country varchar (30) NULL DEFAULT (‘Canada’)

INSERT tmpPublishers

SELECT * FROM Publishers

In this example, we’re copying a subset of data.

INSERT tmpPublishers (pub_id, pub_name)

SELECT PubID, PubName

FROM Publishers

In this example, the publishers’ data are copied to the tmpPublishers table and

the country column is set to Canada.

INSERT tmpPublishers (PubID, PubName, city, province, country)

SELECT PubID, PubName, city, province, ‘Canada’

FROM Publishers

UPDATE statement
The UPDATE statement changes data in existing rows either by adding new data

or modifying existing data.

This example uses the UPDATE statement to standardize the country field to be

Canada for all records in the Publishers table.

UPDATE Publishers

SET country = ‘Canada’

This example increases the royalty amount by 10% for those royalty amounts

between 10 and 20.

UPDATE roysched

SET royalty = royalty + (royalty * .10)

WHERE royalty BETWEEN 10 and

20

Including subqueries in an UPDATE statement

The employees from the Employees table who were hired by the publisher in

2010 are given a promotion to the highest job level for their job type. This is what

the UPDATE statement would look like.

UPDATE

Employees SET

job_lvl=

(SELECT max_lvl FROM jobs

WHERE employee.job_id = jobs.job_id)

WHERE DATEPART(year, employee.hire_date) = 2010

DELETE statement

The DELETE statement removes rows from a record set. DELETE names the

table or view that holds the rows that will be deleted and only one table or row

may be listed at a time. WHERE is a standard WHERE clause that limits the

deletion to select records.

The DELETE syntax looks like this.

DELETE [FROM] {table_name| view_name }

[WHERE clause]

The rules for the DELETE statement are:

1. If you omit a WHERE clause, all rows in the table are removed (except for

indexes, the table, constraints).

2. DELETE cannot be used with a view that has a FROM clause naming more

than one table. (Delete can affect only one base table at a time.)

What follows are three different DELETE statements that can be used.

1. Deleting all rows from a table.

DELETE

FROM Discounts

2. Deleting selected rows:

DELETE

FROM Sales

WHERE stor_id = ‘6380’

3. Deleting rows based on a value in a subquery:

DELETE FROM Sales

WHERE title_idIN

(SELECT title_id FROM Books WHERE type = ‘mod_cook’)

3.5 Built-in Functions

There are many built-in functions in SQL Server such as:

1. Aggregate: returns summary values

2. Conversion: transforms one data type to another

3. Date: displays information about dates and times

4. Mathematical: performs operations on numeric data

5. String: performs operations on character strings, binary data or expressions

6. System: returns a special piece of information from the database

7. Text and image: performs operations on text and image data.

3.6Aggregate functions

Aggregate functions perform a calculation on a set of values and return a

single, or summary, value. Table 1.4 lists these functions.

FUNCTION DESCRIPTION

Returns the average of all the values, or only the DISTINCT values, in
AVG
the expression.

Returns the number of non-null values in the expression. When

COUNT
DISTINCT is specified, COUNT finds the number of unique non-null
values.

Returns the number of rows. COUNT (*) takes no parameters and

COUNT (*)
cannot be used with DISTINCT.

Returns the maximum value in the expression. MAX can be used with
numeric, character and datetime columns, but not with bit columns.
MAX
With character columns, MAX finds the highest value in the collating
sequence. MAX ignores any null values.

Returns the minimum value in the expression. MIN can be used with
numeric, character and datetime columns, but not with bit columns.
MIN
With character columns, MIN finds the value that is lowest in the sort
sequence. MIN ignores any null values.

Returns the sum of all the values, or only the DISTINCT values, in
SUM
the expression. SUM can be used with numeric columns only.

Table 1.4 A list of aggregate functions and descriptions.

Below are examples of each of the aggregate functions listed in Table 16.4.

Example #1: AVG

SELECT AVG (price) AS ‘Average Title Price’

FROM Books

Example #2: COUNT

SELECT COUNT(PubID) AS ‘Number of Publishers’

-FROM Publishers

Example #3: COUNT

SELECT COUNT(province) AS ‘Number of Publishers’

FROM Publishers

Example #3: COUNT (*)

SELECT COUNT(*)

FROM Employees
WHERE job_lvl = 35

Example #4: MAX

SELECT MAX (HireDate)

FROM Employees

Example #5: MIN

SELECT MIN (price)

FROM Books

Example #6: SUM

SELECT SUM(discount) AS ‘Total Discounts’

FROM Discounts

3.7 Conversion function

The conversion function transforms one data type to another.

In the example below, a price that contains two 9s is converted into five characters. The
syntax for this statement is SELECT ‘The date is ‘ + CONVERT(varchar(12), getdate()).

SELECT CONVERT(int, 10.6496)

SELECT title_id, price
FROM Books
WHERE CONVERT(char(5), price) LIKE ‘%99%’

In this second example, the conversion function changes data to a data type with a different size.

SELECT title_id, CONVERT(char(4), ytd_sales) as ‘Sales’

FROM Books
WHERE type LIKE ‘%cook’

3.8 Date function

The date function produces a date by adding an interval to a specified date. The result is
a datetime value equal to the date plus the number of date parts. If the date parameter is
a smalldatetime value, the result is also a smalldatetime value.

The DATEADD function is used to add and increment date values. The syntax for this
function is DATEADD(datepart, number, date).

SELECT DATEADD(day, 3, hire_date)

FROM Employees

In this example, the function DATEDIFF (datepart, date1, date2) is used.

DATE PART ABBREVIATION VALUES

Year Yy 1753-9999

Quarter Qq 1-4

Month mm 1-12
 Day of year dy 1-366

Day dd 1-31

Week wk 1-53

Weekday dw 1-7 (Sun.-Sat.)

Hour hh 0-23

Minute mi 0-59

Second ss 0-59

Millisecond ms 0-999

This command returns the number of datepart “boundaries” crossed between two
specified dates. The method of counting crossed boundaries makes the result given by
DATEDIFF consistent across all data types such as minutes, seconds, and milliseconds.

SELECT DATEDIFF (day, HireDate, ‘Nov 30 1995’)

FROM Employees

For any particular date, we can examine any part of that date from the year to the millisecond.

The date parts (DATEPART) and abbreviations recognized by SQL Server, and the
acceptable values are listed in Table 16.5.

Table 1.5. Date part abbreviations and values.

3.9 Mathematical functions

Mathematical functions perform operations on numeric data. The following example

lists the current price for each book sold by the publisher and what they would be if all
prices increased by 10%.
 SELECT Price, (price * 1.1) AS ‘New Price’, title
FROM Books
SELECT ‘Square Root’ = SQRT(81)
SELECT ‘Rounded‘ = ROUND(4567.9876,2)
SELECT FLOOR (123.45)

4. Integrity Constraints over Relations

● Database integrity refers to the validity and consistency of stored data.

Integrity is usually expressed in terms of constraints, which are consistency

rules that the database is not permitted to violate. Constraints may apply to

each attribute or they may apply to relationships between tables.

● Integrity constraints ensure that changes (update deletion, insertion) made to

the database by authorized users do not result in a loss of data consistency.

Thus, integrity constraints guard against accidental damage to the database.

EXAMPLE- A brood group must be ‘A’ or ‘B’ or ‘AB’ or ‘O’ only (cannot any other

values else).

TYPES OF INTEGRITY CONSTRAINTS

Various types of integrity constraints are-

1. Domain Integrity

2. Entity Integrity Constraint

3. Referential Integrity Constraint

4. Key Constraints

1. Domain Integrity-

Domain integrity means the definition of a valid set of values for an attribute. You

define data type, length or size, is null value allowed , is the value unique or not for an

attribute ,the default value, the range (values in between) and/or specific values for

the attribute.

2. Entity Integrity Constraint-

This rule states that in any database relation value of attribute of a primary key can't

be null. EXAMPLE- Consider a relation "STUDENT" Where "Stu_id" is a primary key

and it must not contain any null value whereas other attributes may contain null value

e.g "Branch" in the following relation contains one null value.

 Stu_id Name Branch

11255234 Aman CSE

11255369 Kapil EcE

11255324 Ajay ME

11255237 Raman CSE

11255678 Aastha ECE

3. Referential Integrity Constraint-

It states that if a foreign key exists in a relation then either the foreign key value must

match a primary key value of some tuple in its home relation or the foreign key value

must be null.

The rules are:

1. You can't delete a record from a primary table if matching records exist in a

related table.

2. You can't change a primary key value in the primary table if that record has

related records.

3. You can't enter a value in the foreign key field of the related table that doesn't

exist in the primary key of the primary table.

4. However, you can enter a Null value in the foreign key, specifying that the

records are unrelated.

EXAMPLE-

Consider 2 relations "stu" and "stu_1" Where "Stu_id " is the primary key in the "stu"

relation and foreign key in the "stu_1" relation.

Relation "stu"

Stu_id Name Branch

11255234 Aman CSE

11255369 Kapil EcE

 11255324 Ajay ME

11255237 Raman CSE

11255678 Aastha ECE

Relation "stu_1"

Stu_id Course Duration

11255234 B TECH 4 years

11255369 B TECH 4 years

11255324 B TECH 4 years

11255237 B TECH 4 years

11255678 B TECH 4 years

Examples

Rule 1. You can't delete any of the rows in the ”stu” relation that are visible since all

the ”stu” are in use in the “stu_1” relation.

Rule 2. You can't change any of the ”Stu_id” in the “stu” relation since all the “Stu_id”

are in use in the ”stu_1” relation. * Rule 3.* The values that you can enter in the”

Stu_id” field in the “stu_1” relation must be in the” Stu_id” field in the “stu” relation.

Rule 4 You can enter a null value in the "stu_1" relation if the records are unrelated.

13.Key Constraints-

A Key Constraint is a statement that a certain minimal subset of the fields of a

relation is a unique identifier for a tuple. The types of key constraints-

1. Primary key constraints

2. Unique key constraints

3. Foreign Key constraints

4. NOT NULL constraints

5. Check constraints

1. Primary key constraints

Primary key is the term used to identify one or more columns in a table that make a

row of data unique. Although the primary key typically consists of one column in a

table, more than one column can comprise the primary key.

For example, either the employee's Social Security number or an assigned employee

identification number is the logical primary key for an employee table. The objective

is for every record to have a unique primary key or value for the employee's

identification number. Because there is probably no need to have more than one

record for each employee in an employee table, the employee identification number

makes a logical primary key. The primary key is assigned at table creation.

The following example identifies the EMP_ID column as the PRIMARY KEY for the
EMPLOYEES table:

CREATE TABLE EMPLOYEE_TBL

(EMP_ID CHAR(9) NOT NULL PRIMARY KEY,

EMP_NAME VARCHAR (40) NOT NULL,

EMP_ST_ADDR VARCHAR (20) NOT NULL,

EMP_CITY VARCHAR (15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP INTEGER(5) NOT NULL,

EMP_PHONE INTEGER(10) NULL,

EMP_PAGER INTEGER(10) NULL);

A unique column constraint in a table is similar to a primary key in that the value in

that column for every row of data in the table must have a unique value. Although a

primary key constraint is placed on one column, you can place a unique constraint

on another column even though it is not actually for use as the primary key.

CREATE TABLE EMPLOYEE_TBL

(EMP_ID CHAR(9) NOT NULL PRIMARY KEY,

EMP_NAME VARCHAR (40) NOT NULL,

EMP_ST_ADDR VARCHAR (20) NOT NULL,

 EMP_CITY VARCHAR (15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP INTEGER(5) NOT NULL,

EMP_PHONE INTEGER(10) NULL UNIQUE,

EMP_PAGER INTEGER(10) NULL)

3. Foreign Key Constraints

A foreign key is a column in a child table that references a primary key in the parent

table. A foreign key constraint is the main mechanism used to enforce referential

integrity between tables in a relational database. A column defined as a foreign key

is used to reference a column defined as a primary key in another table.

CREATE TABLE EMPLOYEE_PAY_TBL (

EMP_ID CHAR(9) NOT NULL,
POSITION VARCHAR2(15) NOT NULL,
DATE_HIRE DATE,
PAY_RATE NUMBER(4,2) NOT NULL,
DATE_LAST_RAISE DATE,
CONSTRAINT FK_EMP FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL(EMP_ID)
);

4. NOT NULL Constraints

Previous examples use the keywords NULL and NOT NULL listed on the same line as

each column and after the data type. NOT NULL is a constraint that you can place on

a table's column. This constraint disallows the entrance of NULL values into a

column; in other words, data is required in a NOT NULL column for each row of data

in the table. NULL is generally the default for a column if NOT NULL is not specified,

allowing NULL values in a column.

5. Check Constraints

Check (CHK) constraints can be utilized to check the validity of data entered into

particular table columns. Check constraints are used to provide back-end database

edits, although edits are commonly found in the front-end application as well.

General edits restrict values that can be entered into columns or objects, whether

within the database itself or on a front-end

application. The check constraint is a way of providing another protective layer for the

data.CREATE TABLE EMPLOYEE_TBL

(EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR2(40) NOT NULL,

EMP_ST_ADDR VARCHAR2(20) NOT NULL,

EMP_CITY VARCHAR2(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMBER(5) NOT NULL,

EMP_PHONE NUMBER(10) NULL,

EMP_PAGER NUMBER(10) NULL),

PRIMARY KEY (EMP_ID),

CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP = '46234');

Key Constraints- Foreign Key Constraints

A Foreign Key is a database key that is used to link two tables together. The FOREIGN

KEY constraint identifies the relationships between the database tables by

referencing a column, or set of columns, in the Child table that contains the foreign

key, to the PRIMARY KEY column or set of columns, in the Parent table.

The relationship between the child and the parent tables is maintained by checking

the existence of the child table FOREIGN KEY values in the referenced parent table’s

PRIMARY KEY before inserting these values into the child table. In this way, the

FOREIGN KEY constraint, in the child table that references the PRIMARY KEY in the

parent table, will enforce database referential integrity. Referential integrity ensures

that the relationship between the database tables is preserved during the data

insertion process. Recall that the PRIMARY KEY constraint guarantees that no NULL

or duplicate values for the selected column or columns will be inserted into that table,

enforcing the entity integrity for that table. The entity integrity enforced by the

PRIMARY KEY and the referential integrity enforced by the FOREIGN KEY together

form the key integrity.

The FOREIGN KEY constraint differs from the PRIMARY KEY constraint in that, you

can create only one PRIMARY KEY per each table, with the ability to create multiple

FOREIGN
KEY constraints in each table by referencing multiple parent table.

Another difference is that the FOREIGN KEY allows inserting NULL

values if there is no NOT NULL constraint defined on this key, but the

PRIMARY KEY does not accept NULLs.

The FOREIGN KEY constraint provides you also with the ability to control

what action will be taken when the referenced value in the parent table

is updated or deleted, using the ON UPDATE and ON DELETE clauses.

The supported actions that can be taken when deleting or updating the

parent table’s values include:

● NO ACTION: When the ON UPDATE or ON DELETE clauses are

set to NO ACTION, the performed update or delete operation in

the parent table will fail with an error.

● CASCADE: Setting the ON UPDATE or ON DELETE clauses to

CASCADE, the same action performed on the referenced values

of the parent table will be reflected to the related values in the

child table. For example, if the referenced value is deleted in the

parent table, all related rows in the child table are also deleted.

● SET NULL: With this ON UPDATE and ON DELETE clauses option,

if the referenced values in the parent table are deleted or

modified, all related values in the child table are set to NULL value.

● SET DEFAULT: Using the SET DEFAULT option of the ON

UPDATE and ON DELETE clauses specifies that, if the referenced

values in the parent table are updated or deleted, the related

values in the child table with FOREIGN KEY columns will be set to

its default value.