UNIT III STRUCTURED QUERY LANGUAGES

Design Concepts: Normalization of Databases Tables – The need of

Normalization – The Normalization Process – Normalization and
Database Design. – Advanced Design and Implementation: Introduction
to Structured Query language – Data Manipulation Commands –
Advanced SQL – SQL Join Operators – Subqueries and correlated Queries
– SQL Functions – Relational Set Operators – Oracle Sequences –
Procedural SQL – Embedded SQL – Database Design – The Database Life
Cycle – Conceptual Design – Database Design Strategies.

Design Concepts

1
2
3
4
5
6
7
INTRODUCTION TO STRUCTURED QUERY LANGUAGE (SQL)
SQL is a database computer language designed for the retrieval and management of data in a
relational database. SQL stands for Structured Query [Link] is a language that enables
you to create and operate on relational databases, which are sets of related information stored in
tables. It is a standard language for Relational Database System.
It enables a user to create, read, update and delete relational databases and tables. All the
RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard database
language. SQL allows users to query the database in a number of ways, using English-like statements.
Why SQL?
 Allows users to create and drop databases and tables.
 Allows users to describe the data.
 Allows users to define the data in database and manipulate that data.
 Allows users to access data in relational database management systems.
 Allows embedding within other languages using SQL modules, libraries & pre-compilers.
 Allows users to set permissions on tables, procedures, and views

SQL commands and syntax

SQL is, fundamentally, a programming language designed for accessing, modifying and
extracting information from relational databases. As a programming language, SQL has commands
and a syntax for issuing those commands.
SQL commands are divided into several different types, including the following:
 Data Definition Language (DDL) commands are also called data definition commands
because they are used to define data tables.
 Data Manipulation Language (DML) commands are used to manipulate data in existing
tables by adding, changing or removing data. Unlike DDL commands that define how data is
stored, DML commands operate in the tables defined with DDL commands.
 Data Query Language consists of just one command, SELECT, used to get specific data
from tables. This command is sometimes grouped with the DML commands.
 Data Control Language commands are used to grant or revoke user access privileges.
 Transaction Control Language commands are used to change the state of some data --
for example, to COMMIT transaction changes or to ROLLBACK transaction changes.

SQL syntax, the set of rules for how SQL statements are written and formatted, is similar to other
programming languages. Some components of SQL syntax include the following:
o Structure query language is not case sensitive. Generally, keywords of SQL are written in
uppercase.
o Statements of SQL are dependent on text lines. We can use a single SQL statement on one or
multiple text line.
o SQL statements start with a SQL command and end with a semicolon (;)

8
SQL Data Types
Every language has variables to operate on, these variables are called attributes. Every
attribute has a data type (domain) associated with it that specifies the set of values that attribute
can hold. Now, in relational DBMS, each relation is a collection of attributes that defines the nature of
that relation. SQL DDL specifies the domain for each attribute in a relation.

SQL DDL has a variety of built-in data types which we will discuss in detail below:
1. Numeric: The numeric data type specifies a number. Now, a numeric data type can express
an exact value or an approximate value.
a. Exact Numeric value
 int: The int type contains the whole number that can either be positive or negative. It ranges
from -2,147,483,648 to 2,147,483,647. This data type doesn’t hold decimal or fractional
digits. This data type has a storage size of 4 bytes.
 smallint: It is a subset of the integer. It ranges from -32,768 to 32,767. It requires only
2 bytes of storage size.
 bigint: It is just the opposite of smallint. It ranges from -9,223,372,036,854,775,808
to 9,223,372,036,854,755,807. It requires 8 bytes of storage size.
 numeric(p, d): This data type holds a fixed-point number which also has a user-defined
precision i.e. the fractional component. This data type holds the number which has p digits
in total and d number of digits are to the right of the decimal point.
 decimal: Like numeric data type, this type also has a fractional component. The difference is
that this data type has a larger precision as compared to the numeric data type. The storage
size for this datatype is based on the given precision.
b. Approximate Numeric Value
1. real: This data type holds a floating-point number with single precision. Here, the precision
is between 1 to 21. It has 4 bytes of storage size.
2. double precision: It can hold a floating-point number that has precision twice the real data
type. Here, the precision is between 22 to 53. Its storage size is 8 bytes.
3. float: This data type allows the user to specify the precision and then the computer
decides whether to go for the single precision or double precision.

2. String
This kind of data type stores alphanumeric data.
1. Char(n): This data type holds the character string of fixed length, specified by the user. If
the string length is less than the specified length, then the remaining spaces are filled with
blanks which leads to wastage of storage. Well, if you don’t specify the length SQLs DDL
assumes the length of one character. The character data type has a storage space of 254
characters.
2. varchar(n): This data type holds the character string of variable length. It will just store the
exact number of characters that the user specifies thereby saving the storage space. The
varchar has a maximum length of 32,672 characters.
3. Date and Time
This data type holds the data in the context of date and time.
1. date: This data type holds the date in terms of the year, month & day in a particular order
YYYY- MM-DD. Here, the year is expressed in four digits ranging from 0001 to 9999. Whereas,
the month and day are expressed in two digits.
2. time: This data type holds time and expresses it in the format (HH:MM:SS).
9
3. timestamp: This data type expresses date and time in the format YYYY-MM-DD HH:MM:SS.
It ranges from 1970-01-01 00:00:01 to 9999-12-31 23:59:59.

SQL Commands:
 The standard SQL commands to interact with relational databases are CREATE, SELECT,INSERT,
UPDATE, DELETE and DROP.
 These commands can be classified into groups based on their nature:
DDL - DATA DEFINITION LANGUAGE:
o DDL defines the conceptual schema providing a link between the logical and the
physicalstructure of the database.
o The functions of the Data Definition Language (DDL) are:
1. DDL defines the physical characteristics of each record, filed in the record, field‟s data type,
field‟s length, field‟s logical name and also specify relationship among those records.
2. DDL describes the schema and subschema.
3. DDL indicate the keys of records.
4. DDL provides data security measures.
5. DDL provides for the logical and physical data independence.
o Few of the basic commands for DDL are:
Command Description
CREATE Creates a new table, a view of a table, or other object in database
ALTER Modifies an existing database object, such as a table.
DROP Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language:

o DML provides the data manipulation techniques like selection, insertion, deletion,
updation,modification, replacement, retrieval, sorting and display of data or
records.
o DML facilitates use of relationship between the records.
o DML provides for independence of programming languages by supporting several high-
levelprogramming languages like COBOL, PL/1 and C++.
o Few of the basic commands for DML are:
Command Description
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records
DQL - Data Query Language:
o DQL is used to fetch the data from the database. It uses only one command.

Command Description
SELECT Retrieves certain records from one or more tables
DCL - Data Control Language:
o These SQL commands are used for providing security to database objects.
o The different DCL commands are:

10
 Command Description
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from user

TCL – Transaction Control Language:

o It includes commands to control the transactions in a database system.
o The commonly used commands are:
Command Description
COMMIT Make all the changes made by the statements issued permanent.
ROLLBACK Undoes all changes since the beginning of transaction or since a save point.
Data Types in SQL:
o The following are the most common data types of SQL:
SL No DATA TYPE DESCRIPTION
1 NUMBER A variable-length column. Allowed values are zero,
positive and negative numbers
2 CHAR A variable length field up to 255 character in length
3 VARCHAR/VARCHAR2 A variable length field up to 2000 character in length
4 DATE/TIME A fixed length field. The time is stored as a part of the date. The
default format is DD/MON/YY
5 LONG A variable length filed up to 2 GB in length
6 RAW A variable length filed used for binary data up to 2000 in length
7 LONG RAW A variable length filed used for binary data up to 2GB in length
1. NUMBER:
o Used to store a numeric value in a field column.
o It may be decimal, integer or real value.
o General syntax: NUMBER(n, d)
• Where n specifies the number of digits and d specifies the number of digits
to right of thedecimal point.
• Example: marks NUMBER(3), average NUMBER(2, 3)
2. CHAR:
o Used to store a character type data in a column.
o General syntax: CHAR(size)
• Where size represents the maximum (255 Characters) number of characters in
a column.
• Example: name CHAR(15)
3. VARCHAR/VARCHAR2:
o It is used to store variable length alphanumeric data.
o General syntax: VARCHAR(size) / VARCHAR2(size)
• Where size represents the maximum (2000 Characters) number of characters in
a column.
• Example: address VARCHAR2(50)

11
 •
4. DATE:
o It is used to store date in columns.
o SQL supports the various date formats other than the standard DD-MON-YY.
• Example: dob DATE
5. TIME:
o It is used to store time in columns.
o SQL supports the various time formats other than the standard hh-mm-ss.
o Every DATE and TIME can be added, subtracted or compared as it can be done with other
data types.

6. LONG:
o It is used to store variable length strings of up to 2GB size.
o Example: description LONG
 Structure of SQL command:
o Any SQL command is a combination of keywords, identifiers and clauses.
o Every SQL command begins with a keyword (CREATE, SELECT, DELETE and so on) which
asa specific meaning to the language.

 SELECT, FROM and WHERE are keywords.

 The clauses are “FROM student” and “WHERE RegNo=109”.
 Here SELECT and FROM are mandatory, but WHERE is optional.
 Name, Student, RegNo, are identifier that refers to objects in the database.
 Name and RegNo are column names, while Student is a table name.
 The equal sign is an operator and 109 is a numeric constant.

 What is an Operator in SQL?

 An operator is a reserved word or a character used primarily in an SQL statement's WHERE
clause to perform operation(s), such as comparisons and arithmetic operations.
 Operators are used to specify conditions in an SQL statement and to serve as conjunctions for
multiple conditions in a statement.
• Arithmetic operators (+, -, *, / %)
• Comparison operators (>, <, >=, <=, =, !=, <>, !<, !>)
• Logical operators (AND, OR, NOT, IN, BETWEEN, EXISTS, ALL, ANY, LIKE, UNIQUE)

 SQL Logical Operators:

 Here is a list of all the logical operators available in SQL.
Operator Description
ALL The ALL operator is used to compare a value to all values in another value set.
AND The AND operator allows the existence of multiple conditions in an SQL
statement's WHERE clause.

12
 ANY The ANY operator is used to compare a value to any applicable value in the list
according to the condition.
BETWEEN The BETWEEN operator is used to search for values that are within a set of values,
given the minimum value and the maximum value.
EXISTS The EXISTS operator is used to search for the presence of a row in a specified
table that meets certain criteria.
IN The IN operator is used to compare a value to a list of literal values that have
been specified.
LIKE The LIKE operator is used to compare a value to similar values using wildcard
operators.
NOT The NOT operator reverses the meaning of the logical operator with which it is
used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR The OR operator is used to combine multiple conditions in an SQL statement's
WHERE clause.

13
 IS NULL The NULL operator is used to compare a value with a NULL value.
UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness
(noduplicates).

 IMPLEMENTATION OF SQL COMMANDS

 CREATE TABLE
o The SQL CREATE TABLE statement is used to create a new table.
o Creating a basic table involves naming the table and defining its columns and each column's
datatype.
 Syntax: Basic syntax of CREATE TABLE statement is as follows:
CREATE TABLE Table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

o Here CREATE TABLE is the keyword followed by the Table_name, followed by an open
parenthesis, followed by the column names and data types for that column, and followed by aclosed
parenthesis.
o For each column, a name and a data type must be specified and the column name must be aunique
within the table definition.
o Column definitions are separated by commas (,).
o Uppercase and lowercase letters makes no difference in column names.
o Each table must have at least one column.
o SQL commands should end with a semicolon (;).
 Example: Create a table “STUDENT” that contains five columns: RegNo, Name, Combination,DOB
and Fees.
CREATE TABLE
STUDENT( RegNo NUMBER (6),
Name VARCHAR2 (15),
Combination CHAR (4),DOB DATE,
Fees NUMBER (9, 2),PRIMARY KEY ( RegNo )
);
 It creates an empty STUDENT table which looks like this:
RegNo Name Combination DOB Fees
 Viewing the table information:
oThe DESCRIBE or DESC command displays name of the columns, their data type and sizealong with
the constraints.

14
 ALTER Statement:
 The table can be modified or changed by using the ALTER command.
 Syntax: Basic syntax of ALTER TABLE statement is as follows:
1. ALTER TABLE Table_name
ADD (column_name1 DataType, Cloumn_name2 DataType……);
2. ALTER TABLE Table_name
MODIFY (column_name1 DataType, Cloumn_name2 DataType……);
3. ALTER TABLE Table_name
DROP (column_name1 DataType, Cloumn_name2 DataType……);
Example:

Using the ALTER TABLE command the following tasks cannot be performed
o Changing a table name.
o Changing the column name.
o Decreasing the size of a column if table data exists.
o Changing a column‟s data type.

 DROP TABLE:
 The SQL DROP TABLE statement is used to remove a table definition and all data, indexes,
triggers, constraints, and permission specifications for that table.
 Syntax: Basic syntax of DROP TABLE statement is as follows:
DROP TABLE Table_name;
 Example:

 INSERT:
 The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

15
 Syntax:
 There are two basic syntaxes of INSERT INTO statement as follows:

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]

VALUES (value1, value2, value3,...valueN);

 Here, column1, column2,...columnN are the names of the columns in the table into which you
want to insert data.
 You may not need to specify the column(s) name in the SQL query if you are adding values for all the
columns of the table. But make sure the order of the values is in the same order as the columns in the
table.
 METHOD 1: The SQL INSERT INTO syntax would be as follows:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

 Example: Following statements would create six records in STUDENT table:

SQL> INSERT INTO STUDENT VALUES(1401,'RAMESH','PCMC','07-AUG-99',14000);
1 row created.
SQL> INSERT INTO STUDENT VALUES(1402,'JOHN','PCMB','15-SEP-99',13500);
1 row created.
SQL> INSERT INTO STUDENT VALUES(1403,'GANESH','PCME','19-AUG-99',16000);
1 row created.
SQL> INSERT INTO STUDENT VALUES(1404,'MAHESH','PCMC','14-JAN-98',17650);
1 row created.
SQL> INSERT INTO STUDENT VALUES(1405,'SURESH','PCMB','03-MAR-98',11500);
1 row created.
SQL> INSERT INTO STUDENT VALUES(1410,'ARUN','PCMC','01-APR-04',13000);

 METHOD 2: The SQL INSERT INTO syntax would be as follows:

SQL> INSERT INTO STUDENT (REGNO, NAME, FEES) VALUES (1411, 'SHREYA',24000);
1 row created.

SQL> INSERT INTO STUDENT (REGNO, COMBINATION,FEES) VALUES(1412,

'PCMB',21000);
1 row created.
 All the above statements would produce the following records in STUDENT table:

16
 UPDATE:
 SQL provides the ability to change data through UPDATE command.
 The UPDATE command used to modify or update an already existing row or rows of a table.
 The basic syntax of UPDATE command is given below.

UPDATE Table_name
SET column_name =
value
[WHERE [, column_name =value..............]

Example:
SQL> UPDATE STUDENT SET COMBINATION='CEBA' WHERE REGNO=1411;
1 row updated.
SQL> UPDATE STUDENT SET NAME='AKASH' WHERE REGNO=1412;
1 row updated.

 DELETE command:
 In SQL, an already existing row or rows are removed from tables through the use of DELETE
command.
 The basic syntax of DELETE command is given below.

DELETE Table_name
[WHERE condition];

Example:
SQL> DELETE STUDENT WHERE REGNO=1412;
1 row deleted.

 SELECT:
 SQL SELECT statement is used to fetch the data from a database table which returns data in theform
of result table. These result tables are called result-sets.

17
 Syntax: The basic syntax of SELECT statement is as follows:

SELECT column1, column2, columnN Compulsory

FROM Table_name; Part

[WHERE condition(s)]
[GROUPBY column-list] Optional
[HAVING condition(s)] Part
[ORDER BY column-name(s)];

 Here, column1, column2...are the fields of a table whose values you want to fetch. If you want to
fetch all the fields available in the field, then you can use the following syntax:
SELECT * FROM table_name;

 Example: Consider the STUDENT table having the following records:

 Following is an example, which would fetch REGNO, NAME and COMBINATION fields of the
customers available in STUDENT table:

 DISTINCT:
 The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the
duplicate records and fetching only unique records.
 There may be a situation when you have multiple duplicate records in a table. While fetching such
records, it makes more sense to fetch only unique records instead of fetching duplicate records.

SELECT DISTINCT column1, column2,.....columnN

FROM Table_name
WHERE [condition]

 Syntax: The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows:

18
 Example: Consider the STUDENT table having the following records:

 First, let us see how the following SELECT query returns duplicate combination records:

 Now, let us use DISTINCT keyword with the above SELECT query and see the result:

SQL> SELECT DISTINCT COMBINATION FROM STUDENT

ORDER BY COMBINATION;

 This would produce the following result where we do not have any duplicate entry:

 WHERE clause – (Extracting specific rows)

 The SQL WHERE clause is used to specify a condition while fetching the data from single tableor
joining with multiple tables.
 If the given condition is satisfied then only it returns specific value from the table. You would use
WHERE clause to filter the records and fetching only necessary records.
 The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE,
DELETE statement, etc., which we would examine in subsequent chapters.

SELECT column1, column2, columnN

FROM Table_name
WHERE [condition]
 Syntax: The basic syntax of SELECT statement with WHERE clause is as follows:

19
 You can specify a condition using comparison or logical operators like >, <, =, LIKE, NOT, etc.
 Following is an example which would fetch REGNO, NAME and FEES fields from theSTUDENT table
where FEES is greater than 15000:

 Following is an example, which would fetch REGNO, NAME and COMBINATION fields fromthe
STUDENT table for a COMBINATION is „PCMC‟.
 Here, it is important to note that all the strings should be given inside single quotes ('') where as
numeric values should be given without any quote as in above example:

 The SQL AND and OR operators are used to combine multiple conditions to narrow data in anSQL
statement. These two operators are called conjunctive operators.
 These operators provide a means to make multiple comparisons with different operators in the
same SQL statement.

 The AND Operator:

 The AND operator allows the existence of multiple conditions in an SQL statement's WHERE
clause.
 Syntax: The basic syntax of AND operator with WHERE clause is as follows:

SELECT column1, column2, columnN

FROM Table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

 You can combine N number of conditions using AND operator. For an action to be taken by the SQL
statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE.
 Example: Consider the STUDENT table having the following records:

20
 Following is an example, which would fetch REGNO, NAME and DOB fields from the STUDENT table
where fees is less than 1500 AND combination is „PCMC:

 The OR Operator:
 The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
 Syntax: The basic syntax of OR operator with WHERE clause is as follows:

SELECT column1, column2, columnN

FROM Table_name
WHERE [condition1] OR [condition2]...OR [conditionN];

 You can combine N number of conditions using OR operator. For an action to be taken by the
SQL statement, whether it be a transaction or query, only any ONE of the conditions separated bythe
OR must be TRUE.
 Following is an example, which would fetch REGNO, NAME and DOB fields from theSTUDENT table
where fees is less than 1500 OR combination is „PCMC:

 ORDER BY – (Sorting the data)

 The SQL ORDER BY clause is used to sort the data in ascending or descending order, based onone
or more columns. Some database sorts query results in ascending order by default.
 Syntax: The basic syntax of ORDER BY clause is as follows:

SELECT column-list
FROM Table_name
[WHERE condition]
[ORDER column1, column2, .. columnN] [ASC | DESC];
BY

 You can use more than one column in the ORDER BY clause. Make sure whatever column you
are using to sort, that column should be in column-list.
 Example: Consider the STUDENT table having the following records:

21
 Following is an example, which would sort the result in ascending order by NAME:

 Following is an example, which would sort the result in descending order by NAME:

 Working out simple calculations.

 Whenever we want to perform simple calculations such as 10 / 5, we can perform using SELECT
statement which causes an output on monitor.
 But SELECT requires table name to operate.
 One can make use of the dummy table provided by SQL called DUAL which is a single row andsingle
column table.
 It is used when data from table is not required.
 For example, when a calculation is to be performed such as 10*3, 10/2 etc. and to display the
current system date, we could use the following queries.

 SQL Functions:

22
 The SQL functions serve the purpose of manipulating data items and returning a result.
 There are many built in functions included in SQL and can be classified as Group Functions and
Scalar Functions.
 Group Functions:
o Functions that act on set of values are called group functions.
o A group functions can takes entire column of data as its arguments and produces a singledata item
that summarizes the column.
o Following are the SQL group functions.

Function Description
AVG Returns average value of „N‟, ignoring NULL values
COUNT(expr) Returns the number of rows where „expr‟ is not NULL
COUNT(*) Returns the number of rows in the table including duplicates and those
with NULL values

MIN Returns minimum value of „expr‟

MAX Returns maximum value of „expr‟
SUM Returns sum of values „N‟
 Scalar Functions:
o Functions that act on only one value at a time are called scalar functions.
o We can further classify the functions using the type of data with they are designed to work.

Function Description
Numeric Work with numbers.
Functions Examples: ABS, POWER, ROUND, SQRT

String Work with character based data.

Functions Examples: LOWER, INITCAP, UPPER, SUBSTR, LENGTH, LTRIM, RTRIM

Date Work with Date data types.

Functions Example: ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEXT_DAY

Conversion These functions are used to convert one type of data to another. Example:
Functions TO_NUMBER, TO_CHAR, TO_DATE

Consider the EXAMINATION table:

RegNo Name CC Phy Che Mat Cs TotalCity

101 Ajay C1 98 100 97 99 394 Hassan
102 Banu C2 38 50 37 49 174 Belur
103 Chandan C2 100 100 97 99 396 Mysuru
104 John C3 78 80 67 79 304 Alur

23
 105 Kaleem C1 88 80 91 79 338 Hassan
106 Raheem C2 100 98 97 79 374 Hassan
107 Sanjay C3 47 60 56 78 241 Alur
108 Tarun C3 33 34 77 28 172 Arasikere
109 Uday C2 100 98 97 79 374 Hassan
110 Venki C3 47 60 56 78 241 Belur

 COUNT ( ) Function:
 This function is used to count the number of values in a column.
 COUNT (*) is used to count the number of rows in the table including duplicates and those with
NULL values.
 Example 1:
o SELECT COUNT (*) FROM EXAMINATION;
o The above query returns 10.
 Example 2:
o SELECT COUNT (RegNo) FROM EXAMINATION WHERE CC = „C3‟ ;
o The above query returns 4.

 AVG ( ) Function:
 This function is used to find the average of the values in a numeric column.
 Example 1:
o SELECT AVG (Cs) FROM EXAMINATION;
o The above query returns 74.7

 SUM ( ) Function:
 This function is used to find the sum of the values in a numeric column.
 Example:
o SELECT SUM (Phy) FROM EXAMINATION;
o The above query returns 729

 MAX ( ) Function:
 This function is used to find the maximum values in a column.
 Example:
o SELECT MAX (Phy) FROM EXAMINATION;
o The above query returns 100

 MIN ( ) Function:
 This function is used to find the minimum values in a column.
 Example:
o SELECT MIN (Phy) FROM EXAMINATION;
o The above query returns 33
 GROUP BY (Grouping Result)
 The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange
identical data into groups.
 The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes theORDER
24
 BY clause.
 Syntax: The basic syntax of GROUP BY clause is given below.

SELECT column1, column2

FROM Table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

 Example 1: To find the number of students in each [Link] CC, COUNT

(CC) FROM EXAMINATION
GROUP BY CC;
 Example 2: To find the number of students, sum, average, maximum, minimum marks incomputer
science from each city.
SELECT City, COUNT (City), SUM (Cs), AVG (Cs), MAX (Cs), MIN
(Cs)FROM EXAMINATION
GROUP BY City;

 SQL CONSTRAINTS:
 Constraints are the rules enforced on data columns on table.
 These are limiting the type of data that can go into a table.
 This ensures the accuracy and reliability of the data into the database.
 SQL allows two types of constraints.
o Column level constraints: These constraints are defined along with the column definition when
creating or altering a table structure. These constraints apply only to individual columns.
o Table level constraints: These constraints are defined after all the table columns when creating or
altering a table structure. These constraints apply to groups of one or more columns.
 Following are the commonly used constraints available in SQL.

Constraints Description
NOT NULL Ensures that a column cannot have NULL value
UNIQUE Ensures that all values in column are different
PRIMARY KEY Uniquely identified eac row in a database table.
FOREIGN KEY Uniquely identified each rown in any other database table
DEFAULT Provides a default value for a column when none is specified
CHECK Ensures that all values in a column satisfy certain condition.

25
 NOT NULL Constraint:
 By default column can hold NULL values.
 When a column is defined as NOT NULL then the column becomes a mandatory column.
 It implies that a value must be entered into the column if the row is to be inserted for storage in the
table.
 Example: Consider the following CREATE TABLE command creates a new table calledPRODUCT
and add six columns, two which PID and Description specify not to accept NULLs.
CREATE TABLE PRODUCT(
PID CHAR (4) NOT NULL,
Description VARCHAR2 (25), NOT NULL
CompanyId CHAR (10),
DOM DATE,
Type CHAR (10),
Price NUMBER (10,2)
);
 UNIQUE Constraints:
 This constraint ensures that no rows have the same value in the specified column(s). A table must
have many unique keys.
 Example: UNIQUE constraint applied on PID of PRODUCT table ensures that no rows have thesame
PID value, as shown below
CREATE TABLE PRODUCT(
PID CHAR (4) NOT NULL UNIQUE,
Description VARCHAR2 (25), NOT NULLCompanyId CHAR
(10),
DOM DATE,
Type CHAR (10),
Price NUMBER (10,2)
);
 PRIMARY KEY Constraints:
 A primary key is a field which uniquely identifies each row in a database table. A primary key in a
table has special attributes:
 By default this column is NOT NULL. It defines the column as a mandatory column i.e. the
column cannot be left blank.
 The data held in this column must be unique.
 Example:
CREATE TABLE PRODUCT(
PID CHAR (4) PRIMARY KEY,
Description VARCHAR2 (25), NOT NULLCompanyId CHAR
(10),
DOM DATE,
Type CHAR (10),
Price NUMBER (10,2)
);

26
 FOREIGN KEY Constraint:
 A FOREIGN KEY is used to link two tables together.
 A foreign key is a column whose values are derived from the PRIMARY KEY of some other table.
 Example:
CREATE TABLE PRODUCT(
PID CHAR (4) PRIMARY KEY,
Description VARCHAR2 (25), NOT NULL
CompanyId CHAR (10) REFERENCES COMPANY
(CID)DOM DATE,
Type CHAR (10),
Price NUMBER (10,2)
);
CREATE TABLE COMPANY
(
CID CHAR (10) PRIMARY KEY,
CProfile VARCHAR2 (200),
Noofproducts NUMBER (20),DOE DATE
);
 DEFAULT Constraints:
 A default value can be specified for a column using the DEFAULT clause.
 The DEFAULT constraint provides a default value to a column when the INSERT INTOcommand
does not provide a specific value.
 Example:
CREATE TABLE PRODUCT(
PID CHAR (4) PRIMARY KEY,
Description VARCHAR2 (25), NOT NULLCompanyId CHAR
(10),
DOM DATE,
Type CHAR (10),
Price NUMBER (10, 2) DEFALUT 1000.00
);
 CHECK Constraints:
 The CHECK Constraint is used to establish a TRUE/FALSE condition that is applied to the data
placed in a column.
 If a value does not meet the condition, it cannot be placed in the column.
 Example:
CREATE TABLE PRODUCT
(
PID CHAR (4) CHECK (PID LIKE ‘P%’),
Description VARCHAR2 (25),
CompanyId CHAR (10),
DOM DATE,
Type CHAR (10),
Price NUMBER (10, 2) CHECK (Price>0)
);

27
 TABLE Constraints:
 When a constraint is applied to a group of columns of the table, it is called a table constraint.
 Column constraint is defined along with the end of the column.
 Table constraints are defined at the end of the table.
 Example:
CREATE TABLE PRODUCT(
PID CHAR (4) NOT NULL,
Description VARCHAR2 (25) NOT NULL,CompanyId CHAR (10),
DOM DATE,
Type CHAR (10),
Price NUMBER (10, 2),
PRIMARY KEY (PID, Description)
);

28
ORACLE SEQUENCES:
Oracle uses a sequence to assign values to a column on a table.
• Sequences are an independent object in the database. (Sequences are not a data type.)
• Sequences have a name.
• Sequences can be used anywhere a value is expected.
• Sequences are not tied to a table or a column.
• Sequences generate a numeric value that can be assigned to any column in any table.
• The table attribute to which you assigned a value based on a sequence can be edited
and modified.
The basic syntax to create a sequence is as follows:
CREATE SEQUENCE name [START WITH n] [INCREMENT BY n]
[CACHE | NOCACHE]
where
• name is the name of the sequence.
• n is an integer value that can be positive or negative.
• START WITH specifies the initial sequence value. (The default value is 1.)
• INCREMENT BY determines the value by which the sequence is incremented.
(The default increment value is 1. The sequence increment can be positive or negative
to enable you to create ascending or descending sequences.)
• The CACHE or NOCACHE/NO CACHE clause indicates whether the DBMS will preallocate sequence numbers in m
Oracle uses NOCACHE as one word and preallocates 20 values by default.
EXAMPLE:

CREATE SEQUENCE CUS_CODE_SEQ START WITH 20010 NOCACHE;

CREATE SEQUENCE INV_NUMBER_SEQ START WITH 4010 NOCACHE;

PROCEDURAL SQL
Procedural Language SQL (PL/SQL) is a language that makes it possible to use and store procedural code and SQL
statements within the database and to merge SQL and traditional programming constructs, such as variables, conditional
processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops), and error trapping. The procedural code is executed
29
as a unit by the DBMS when it is invoked (directly or indirectly) by the end user.
End users can use PL/SQL to create:
• Anonymous PL/SQL blocks
• Triggers (covered in Section 8-7a)
 Stored procedures (covered in Section 8-7b and Section 8-7c)
 PL/SQL functions
•

1. Anonymous PL/SQL Block

 A temporary PL/SQL block.

 Not stored in the database.
 Mainly used for quick testing or one-time operations.
 Cannot be reused directly.

Example:
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from anonymous block!');
END;

30
The PL/SQL block is known as an anonymous PL/SQL block because it has not been given a specific name. The
block’s last line uses a forward slash ( / ) to indicate the end of the command-line entry.
2. Triggers
A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data
manipulation event. It is useful to remember that:

• A trigger is invoked before or after a data row is inserted, updated, or deleted.

• A trigger is associated with a database table.
• Each database table may have one or more triggers.
• A trigger is executed as part of the transaction that triggered it. Triggers are critical to proper database
operation and management. For example:
• Triggers can be used to enforce constraints that cannot be enforced at the DBMS design and
implementation levels.
• Triggers add functionality by automating critical actions and providing appropriate warnings and
suggestions for remedial action. In fact, one of the most common uses for triggers is to facilitate the
enforcement of referential integrity.
• Triggers can be used to update table values, insert records in tables, and call other

• A stored PL/SQL block that is automatically executed when a specific event occurs on a table (INSERT,
UPDATE, DELETE).
• Often used to enforce business rules, auditing, or automatic data modifications.

31
The syntax to create a trigger in Oracle is as follows:

CREATE OR REPLACE TRIGGER trigger_name

[BEFORE / AFTER] [DELETE / INSERT / UPDATE OF column_name] ON table_name

[FOR EACH ROW]

[DECLARE]

[variable_namedata type[:=initial_value] ]

BEGIN

PL/SQL instructions;

END;

o The triggering timing: BEFORE or AFTER. This timing indicates when the trigger’s PL/SQL code
executes—in this case, before or after the triggering statement is completed.
o The triggering event: The statement that causes the trigger to execute (INSERT, UPDATE, or DELETE).
o – The triggering level: The two types of triggers are statement-level triggers and rowlevel triggers.A
statement-level trigger is assumed if you omit the FOR EACH ROW keywords. This type of trigger is
executed once, before or after the triggering statement is completed. This is the default case.
o A row-level trigger requires use of the FOR EACH ROW keywords. This type of trigger is executed
once for each row affected by the triggering statement. (In other words, if you update 10 rows, the trigger
executes 10 times.)
o The triggering action: The PL/SQL code enclosed between the BEGIN and END keywords. Each
statement inside the PL/SQL code must end with a semicolon ( ; ).

🔍 Example:

Stored Procedures

32
  A named PL/SQL block stored in the database.
 Used to perform a specific task or set of operations.
 Can accept IN, OUT, or IN OUT parameters.
 Can be called multiple times from different programs

CREATE OR REPLACE PROCEDURE procedure_name [(argument [IN/OUT]

data-type, … )]

[IS/AS]

[variable_namedata type[:=initial_value] ]

BEGIN

PL/SQL or SQL statements;

END;

4. PL/SQL Functions

 Similar to procedures, but must return a value.

 Can be used in SQL queries, SELECT statements, or PL/SQL code.

🔍 Example:
CREATE OR REPLACE FUNCTION get_area(p_radius IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN 3.14 * p_radius * p_radius;
END;

DECLARE
area NUMBER;
BEGIN
area := get_area(5);
DBMS_OUTPUT.PUT_LINE('Area is: ' || area);
33
 END;

Embedded SQL
Embedded SQL is a term used to refer to SQL statements contained within an application programming language
such as Visual Basic .NET, C#, COBOL, or Java. The program being developed might be a standard binary executable in
Windows or Linux, or it might be a web application designed to run over the Internet. No matter what language you use,
if it contains embedded SQL statements, it is called the host language.
Embedded SQL is still the most common approach to maintaining procedural capabilities in DBMS-based applications.
However, mixing SQL with procedural languages requires that you understand some key differences between the two
• A standard syntax to identify embedded SQL code within the host language (EXEC SQL/END-EXEC).
• A standard syntax to identify host variables, which are variables in the host language that receive data from the
database (through the embedded SQL code) and process the data in the host language. All host variables are
preceded by a colon ( : ).
• A communication area used to exchange status and error information between SQL and the host language. This
communication area contains two variables—SQLCODE and SQLSTATE.
EXEC SQL
SQL statement;
END-EXEC.

Database Systems Development Life Cycle

The database life cycle (DBLC) consists of six phases. These phases include database
primary study planning, analysis, detailed System design, (prototyping), implementation
and loading, testing and evaluation, operation, maintenance and evolution. In the database
primary study, the researcher examines the current systems operations in the company to
determine how and why the current system isn’t sustainable. The objective of this study is
to analyze the company status, define problems and constraints, define purpose, and define
the scope and boundaries. Each section can be broken down in order to further understand
the usefulness behind creating this study.

34
35
 Pertains to defining the general conditions within a company, including its
Analyzing the organization structure and its mission. In order to correctly do this, the
company situation designer must discover what the company’s operation components are, the
way they function, and how they interact
Pertains to the discovery of issues within the company, formally and
Defining Problems informally. These problems may appear unstructured; however, problems
and Constraints are usually connected, allowing the designer to overcome them by the end
of the process.
Defining Is a part of the new proposed database system showing that it is designed
Objectives to solve the major problems identified previously?
Pertains to the engineer recognizing the existence of their limits: scope
and boundaries. The system’s scope shows the extent of the design
according to the requirements. The system also is connected to limits
Defining the known as boundaries which are external. These boundaries are set by the
Scope and accompanying hardware and software.
Boundaries
Database design is the second phase focusing on the design of the
database that supports company operations and objectives in the future.
This can be viewed as the most critical DBLC phase.
Pertain to a series of instructions when dealing with the creation of tables,
Implementation attributes, etc. in the domain. In this phase, the design specifications are
and Loading installed, creating the exact database required by the parent company. This
can be done in 3 phases.
Install the DBMS Installing a new instance of a DBMS in the system on a server.
Creating the
Creates the table spaces and file groups accompanied by the database.
DBMS
After the database is created, the data must enter the new tables. This
requires them to be merged and imported from other databases or the ones
previously used in order to ensure the same data is relayed into the newer,
better system.
Loading and
Converting Data Testing and evaluation pertain to the decision made to ensure integrity,
security, performance, and recoverability of the database. Following the
plans laid out previously, this fine-tunes the database to ensure that it
performs as expected. This phase is also divided into three phases, making
it easy to follow and accurately test the functionality of the database.
During this step, the database is tested to ensure it has the integrity and
Test the Database security required by the company. This is enforced through the proper use
of primary and foreign key rules.
Fine-Tune the This is the editing of the database with the results of the previous step in
Database mind. If no fine-tuning is required, this step can be skipped.
Evaluating the The database must be reviewed thoroughly to ensure that the data
Database contained is protected against loss, promoting the use of a backup.
The second to last step identifying that the database is fully functional. At
Operation this point, the database is complete, and the new system has space to
evolve as needed by the developers.
Maintenance and The final step. This step is directed by the database administrator allowing
36
 them to perform routine maintenance activities regarding the database.
Some of these activities include Backup, Corrected Maintenance,
Evolution
Adaptive Maintenance and the Assignment of access permissions to
welcome new users and edit old users

Conceptual design
Conceptual design steps
The conceptual design phase is the first stage of database design, where you create a high-level, database-independent
model of the system's data requirements. It typically involves these steps:
1. Gather requirements: Talk to stakeholders to understand what data needs to be stored, how it will be used, and any
governing business rules.

2. Identify entities: Pinpoint the major objects or concepts about which data will be stored.

3. Define attributes: For each entity, identify its properties.

4. Define relationships: Determine the associations and connections that exist between different entities.

5. Set cardinality: Specify the number of instances of one entity that can be related to instances of another (e.g., one-to-
one, one-to-many, many-to-many).

6. Create an Entity-Relationship (ER) diagram: Visualize the entities, attributes, and relationships in a graphical format.

7. Refine the model: Review the ER diagram with users to ensure accuracy and apply normalization rules to reduce data
redundancy

Database Design Strategies

Top – down design method

The top-down design method starts from the general and moves to the specific. In other words, you start with a general
idea of what is needed for the system and then work your way down to the more specific details of how the system will
interact. This process involves the identification of different entity types and the definition of each entity’s attributes.,

37
Bottom – up design method

The bottom-up approach begins with the specific details and moves up to the general. This is done by first identifying the
data elements (items) and then grouping them together in data sets. In other words, this method first identifies the
attributes, and then groups them to form entities.

1. Two general approaches (top – down and bottom – up) to the design of the databases can be heavily influenced
by factors like scope, size of the system, the organizations management style, and the organizations structure.
38
 Depending on such factors, the design of the database might use two very different approaches, centralized
design and decentralized design.
2. Centralized design
 Centralized design is most productive when the data component is composed of a moderately small number of
objects and procedures. The design can be carried out and represented in a somewhat simple database. Centralized
design is typical of a simple or small database and can be successfully done by a single database administrator or
by a small design team. This person or team will define the problems, create the conceptual design, verify the
conceptual design with the user views, and define system processes and data constraints to ensure that the design
complies with the organizations goals. That being said, the centralized design is not limited to small companies.
Even large companies can operate within the simple database environment.

Decentralized design

Decentralized design might best be used when the data component of the system has a large number of entities and
complex relations upon which complex operations are performed. This is also likely to be used when the problem itself is
spread across many operational sites and the elements are a subset of the entire data set. In large and complex projects a
team of carefully selected designers are employed to get the job done. This is commonly accomplished by several teams
that work on different subsets or modules of the system. Conceptual models are created by these teams and compared to
the user views, processes, and constraints for each module. Once all the teams have completed their modules they are all
put aggregated into one large conceptual model.

39
40
RELATIONAL SET OPERATORS

41
42
43
44
45
46
47