Database Systems Lab Manual - Updated Sep 2023

COMPUTER LABORATORY MANUAL

Database System
(CS – 220)
Fall Semester 2023
Version 2.4

DEPARTMENT OF COMPUTER SOFTWARE ENGINEERING

Military College of Signals
National University of Sciences and Technology
www.mcs.nust.edu.pk
PREFACE
This lab manual has been prepared to facilitate the students of software engineering in studying and analysing
various components of a database system. The lab focuses on creating and manipulating databases. The
programming language for database system Structured Query Language (SQL) is taught. The manual also
addresses advanced concepts including transaction, indexing, concurrency and optimization.
PREPARED BY
Lab manual is prepared by Lec Ayesha Naseer under the supervision of Head of Department Dr. Naveed Iqbal
Rao in year 2013. This course will provide a thorough introduction to the theory and practices of database
systems. The emphasis will be on the theoretical considerations involved in modeling data and in designing
efficient database systems.
GENERAL INSTRUCTIONS
a. Students are required to maintain the lab manual with them till the end of the semester.
b. All readings, answers to questions and illustrations must be solved on the place provided. If more space is
required, then additional sheets may be attached.
c. It is the responsibility of the student to have the manual graded before deadlines as given by the instructor
d. Loss of manual will result in re submission of the complete manual.
e. Students are required to go through the experiment before coming to the lab session. Lab session details will
be given in training schedule.
f. Students must bring the manual in each lab.
g. Keep the manual neat clean and presentable.
h. Plagiarism is strictly forbidden. No credit will be given if a lab session is plagiarised and no re submission
will be entertained.
i. Marks will be deducted for late submission.
VERSION HISTORY
Date Update By Details
July 2013 Lec Ayesha Naseer First Version Created
Sep 2014 Lec Ayesha Naseer Second version created. Labs improved
Sep 2015 Kabeer Ahmed Labs improved &Updated exercises
Sep 2016 Kabeer Ahmed Labs improved &Updated exercises
Sep 2017 Kabeer Ahmed Labs improved &Updated exercises included the Views
Sep 2018 Lec Ayesha Naseer and Kabeer Ahmed Updated exercises included CLOs and lab rubrics
Sep 2019 Lec Ayesha Naseer and Kabeer Ahmed Updation exercises and CLOs
Sep 2020 Lec Ayesha Naseer and Kabeer Ahmed Updation of CLOs, Labs and exercises
Sep 2021 Lec Ayesha Naseer and Kabeer Ahmed Update Lab Rubrics
Sep 2022 Lec Ayesha Naseer and Kabeer Ahmed Updated CLOs and Exercises
Sep 2023 Dr Ayesha Naseer Revised CLO mappings with experiments
Department of Computer Software Engineering
Lab Rubrics
Query Formulation
Criteria Unacceptable Substandard Adequate Proficient

(Marks=0) Marks=1 Marks=2 Marks=3
The SQL Query execution let
The SQL Query was The SQL Query was
R1 The SQL Query failed to to inaccurate or incomplete
correctly functional and correctly functional, and all
Completeness produce the right accurate results. It was not correctly
most of the features the features were
And Accuracy result functional or not all the
were implemented implemented
features were implemented
Student successfully
The student fails to figure Student successfully figures Student successfully figures
R2 figures out most of
out the syntax and semantic out few of syntax and out all syntax and semantic
Syntax and syntax and semantic
errors of the incorrect SQL semantic errors of the SQL errors of the SQL Query
Semantics errors of the SQL Query
Query Query with extensive guidance without any guidance
with minimum guidance
Student has demonstrated
Student has basic
Student failed to on accurate understanding
Student has basic knowledge of
R3 demonstrate a clear of the lab objective and
understanding, but asked understanding. Provides
Demonstration understanding of the concepts. All the questions
questions were not answered. fundamental answers to
assigned task are answered completely
asked questions
and correctly
The SQL Query is
R4 The SQL Query is poorly The SQL Query is readable
The SQL Query is fairly exceptionally well
Complexity and organized and very difficult only by someone who knows
easy to read organized and very easy to
Readability to read what it is supposed be doing
follow
Complete working SQL
Most of working SQL
R5 Query is copied indicating Most of working SQL Query is Complete working SQL
Query is contributed by
Perseverance and no effort on student’s part copied. Minor contribution by Query is contributed by the
the student. Minor
plagiarism resulting in a total score of the student student
copied components
zero for all rubrics
Lab Rubrics
Design ERDs

The ERD is exceptionally clear
The ERD is clear and
The ERD is difficult to read due The ERD is somewhat clear, but and easy to read. Labels,
R1 readable, but there may be
to unclear labels, symbols, or there are notable issues with symbols, and notations are
Clarity and occasional inconsistencies
notations, making it labels, symbols, or notations consistently applied and well-
Readability or choices that could be
challenging to understand. that hinder readability. chosen for conveying
improved for better clarity.
information.
The ERD is mostly complete The ERD is complete and
The ERD is incomplete and The ERD is somewhat complete and accurate but may have accurately represents the
R2
contains significant errors or but lacks essential components minor omissions or errors required entities,
Completeness and
omissions, making it unusable or contains noticeable errors in representing entities, relationships, attributes, and
Correctness for its intended purpose. that affect its accuracy. relationships, attributes, or cardinalities. There are no
cardinalities. errors or omissions.
Complete working ERD is
copied indicating no effort on Most of working ERD is copied. Most of working ERD is
R3 Complete working ERD is
student’s part resulting in a Minor contribution by the contributed by the student.
Plagiarism total score of zero for all student Minor copied components
contributed by the student
rubrics
Entities and attributes are
Entities and attributes have Entities and attributes have
Entities and attributes are well-designed, with clear
R4 significant design flaws, noticeable design issues, such as
mostly well-designed but names, appropriate data
Entity and making it challenging to unclear names, inappropriate
may have some naming or types, and suitable
Attribute Design understand their purpose or data types, or missing
data type issues. constraints. Attributes are
usage. constraints.
properly defined.
Relationships are mostly Relationships are clearly
Relationships are poorly Relationships have noticeable
R5 well-represented but may represented with appropriate
represented, making it difficult issues with cardinalities,
Relationship have minor issues with cardinalities, participation
to understand their meaning constraints, or names, affecting
Representation cardinalities, constraints, or constraints, and descriptive
or usage. their clarity.
names. names.
Lab Rubrics
Group Task

The system execution let to
The system was correctly The system was correctly
R1 The system failed to inaccurate or incomplete
functional and most of functional, and all the
Completeness and produce the right accurate results. It was not correctly
the features were features were
Accuracy result functional or not all the
implemented implemented
features were implemented
The student has
demonstrated on accurate
The student failed to The student has basic The student has
understanding of the lab
R2 demonstrate a clear knowledge of understanding, moderate knowledge of
objective and concepts. All
Demonstration understanding of the but asked questions were not understanding. Answer
the questions are
assigned task answered. to the question is basic
answered completely and
correctly
Complete working program
Most of working
is copied indicating no effort Most of working program is Complete working
R3 program is contributed
on student’s part resulting copied. Minor contribution by program is contributed by
Plagiarism by the student. Minor
in a total score of zero for the student the student
copied components
all rubrics
R4 Demonstrates commitment Demonstrates Actively helps to identify
Shows little commitment to
Contribution/ to group goals, but has commitment to group group goals and works
group goals and fails to
Group difficulty performing assigned goals and carries out effectively to meet them in
perform assigned roles
participation roles assigned roles effectively all roles assumed
Poor presentation; cannot Presentation lacks clarity and Presentation acceptable; Well-organized, clear
explain topic; scientific organization; little use of adequate use of presentation; good use of
R5
terminology lacking or scientific terms and scientific terms; scientific vocabulary and
Presentation skills
confused; lacks vocabulary; poor acceptable terminology; good
understanding of topic understanding of topic understanding of topic understanding of topic
Lab Rubrics
Open Ended Lab

The SQL Query execution let to The SQL Query was
R1 (CLO-5) The SQL Query was correctly
The SQL Query failed to produce inaccurate or incomplete results. It correctly functional and
Completeness functional, and all the features
the right accurate result was not correctly functional or not most of the features
And Accuracy were implemented
all the features were implemented were implemented
ERD has significant design flaws, ERD has noticeable design issues, ERD is mostly well- ERD is well-designed, with clear
R2 (CLO-2) making it challenging to such as unclear names, designed but may have names, appropriate data types,
ERD Design understand their purpose or inappropriate data types, or missing some naming or data and suitable constraints.
usage. constraints. type issues. Attributes are properly defined.
Student has basic Student has demonstrated on
Student failed to demonstrate a Student has basic understanding, knowledge of accurate understanding of the lab
R3 (CLO-5)
clear understanding of the but asked questions were not understanding. Provides objective and concepts. All the
Demonstration
assigned task answered. fundamental answers to questions are answered
asked questions completely and correctly
The SQL Query is poorly The SQL Query is readable only by The SQL Query is exceptionally
R4 (CLO-5) The SQL Query is fairly
organized and very difficult to someone who knows what it is well organized and very easy to
Complexity and easy to read
read supposed be doing follow
Readability
Complete working SQL Query is Most of working SQL
Most of working SQL Query is
R5 (CLO-5) copied indicating no effort on Query is contributed by Complete working SQL Query is
copied. Minor contribution by the
Perseverance and student’s part resulting in a total the student. Minor contributed by the student
student
plagiarism score of zero for all rubrics copied components
Organized to some
R6 (CLO-4) Lab Report is not organized Poorly organized and all aspects of The document is well-organized
extent and written with
Organization and project are not discussed and complete in all aspects
minor errors
Coherence
No formatting is done
R7 (CLO-4) Formatting guidelines barely Formatting guidelines Written work is very well
Formatting and followed adequately followed formatted and well-written
Technical Writing
CS-220 Database System
Mapping of CLOs to Program Learning Outcomes

PLOs/CLOs CLO1 CLO2 CLO3 CLO4 CLO5
PLO 1 (Engineering Knowledge) √
PLO 2 (Problem Analysis)
PLO 3 (Design/Development of Solutions) √
PLO 4 (Investigation) √
PLO 5 (Modern tool usage) √ √
PLO 6 (The Engineer and Society)
PLO 7 (Environment and Sustainability)
PLO 8 (Ethics)
PLO 9 (Individual and Team Work)
PLO 10 (Communication)
PLO 11 (Project Management)
PLO 12 (Lifelong Learning)
7 CS 220 Database System Lab Manual

Lab Experiments CLO Mapping
S No List of Experiments CLO

1 EXPERIMENT 1 – ENTITY RELATIONSHIP DIAGRAMS (ERD) 2 (50%)
2 EXPERIMENT 2 – DEVELOPING DATA MODELS FOR BUSINESS DATABASES 2(5%)
3 EXPERIMENT 3 – DATA DEFINITION LANGUAGE (DDL) ALTER, DROP, INSERT INTO 5(5%)
EXPERIMENT 4 – DATA MANIPULATION LANGUAGE (DML) SELECT, FROM, WHERE,
4 5(5%)
DISTINCT, LIKE, ORDER BY
5 EXPERIMENT 5 – DATA FILTERING USING GROUP BY /HAVING CLAUSE 5(5%)
6 EXPERIMENT 6 – SQL INBUILT FUNCTIONS 5(5%)
7 EXPERIMENT 7 – NESTED QUERIES 5(5%)
8 EXPERIMENT 8 – SQL JOINS (CROSS, INNER, SELF) 5(5%)
9 EXPERIMENT 9 – SQL JOINS (LEFT OUTER, RIGHT OUTER, FULL OUTER) 5(5%)
10 EXPERIMENT 10 – NESTED QUERIES WITH JOINS 5(5%)
11 EXPERIMENT 11 – PROGRAMMABLE OBJECTS IN SQL 5(5%)
12 EXPERIMENT 12 – STORED PROCEDURES 5(5%)
13 EXPERIMENT 13 – TRIGGERS 5(5%)
14 EXPERIMENT 14– APPLICATION DEVELOPMENT WITH SQL + FRONT END TOOL (JAVA) 5 (5%)
15 EXPERIMENT 15– OPEN ENDED LAB -
16 LAB FINAL PROJECT -

LIST OF EXPERIMENTS
EXPERIMENT 1 – ENTITY RELATIONSHIP DIAGRAMS (ERD)................................................................................17

EXPERIMENT 2 – DEVELOPING DATA MODELS FOR BUSINESS DATABASES..................................................23
EXPERIMENT 3 – DATA DEFINITION LANGUAGE (DDL)........................................................................................27
EXPERIMENT 4 – DATA MANIPULATION LANGUAGE (DML)...............................................................................35
EXPERIMENT 5 – GROUP BY AND HAVING...............................................................................................................39
EXPERIMENT 6 – SQL INBUILT FUNCTIONS.............................................................................................................44
EXPERIMENT 7 – NESTED QUERIES............................................................................................................................50
EXPERIMENT 8 – SQL JOINS (CORSS, INNER, SELF)................................................................................................58
EXPERIMENT 9 – SQL JOINS (LEFT OUTER, RIGHT OUTER, FULL OUTER)........................................................64
EXPERIMENT 10 – NESTED QUERIES WITH JOINS...................................................................................................68
EXPERIMENT 11 – PROGRAMMABLE OBJECTS IN SQL..........................................................................................73
EXPERIMENT 12 – STORED PROCEDURES.................................................................................................................78
EXPERIMENT 13 – TRIGGERS.......................................................................................................................................83
EXPERIMENT 14 – APPLICATION DEVELOPMENT WITH SQL + FRONT END TOOL (JAVA)............................87
EXPERIMENT 15– OPEN ENDED LAB (DATABASE SYSTEM).................................................................................98

SAMPLE DATABASE SPECIFICATIONS
Student Management System: Requirements Specifications

The Student Management System is used for practice/Problems.
ABC College is a reputable educational institution that offers a wide range of courses to its students. As the
college has grown in size and popularity, the management has realized the need for a comprehensive Student
Management System to streamline administrative processes related to student enrolment, course management,
and academic records.
The detailed requirements specifications are given below:
1. Student Management:
 Maintain student profiles with details such as name, contact information, date of birth, and
address.
 Assign a unique student ID to each student.
 Store historical data of student enrolments and academic performance.
2. Course Management:
 Manage course details including course code, title, description, credits, and prerequisites.
 Store information about faculty members assigned to each course.
3. Enrolment:
 Allow students to browse available courses and enrol in them.
 Ensure that students meet prerequisites before enrolling in advanced courses.
 Limit the number of students per course if necessary.
 Handle waitlists for courses that have reached maximum enrolment.
4. Academic Records:
 Record grades for each student in their enrolled courses.
 Calculate GPA and maintain a transcript for each student.
 Generate reports for students, faculty, and administration.
5. Faculty Management:
 Maintain faculty profiles including name, contact information, and areas of expertise.
 Assign faculty members to specific courses.
Main Entities and Relationships:
Entities:
1. Student
2. Course
3. Faculty
4. Enrollment
5. Academic Record
Relationships:
1. Student-Course (Many-to-Many):
• A student can enroll in multiple courses, and a course can have multiple students enrolled.
• This relationship will have attributes like enrollment date. Each enrollment is associated with one
course, but a course can have multiple enrollments (students).
2. Student-Academic Record (One-to-Many):
• Each student has one academic record, but an academic record contains multiple course grades.

• This relationship will have attributes like GPA and transcript.
3. Course-Faculty (Many-to-One):
• Multiple courses can be taught by the same faculty member, but each course is taught by one
faculty member.
4. Course-Prerequisites (Many-to-Many):
• A course can have multiple prerequisites, and multiple courses can have the same prerequisite.
Entity Relationship Diagram:

Tables with Data Entries:

SQL Statements
CREATE TABLE Student

(StudentID INT PRIMARY KEY,
Name VARCHAR(255),
ContactInfo VARCHAR(255),
DateOfBirth DATE,
Address VARCHAR(255) );
CREATE TABLE Course

(CourseCode VARCHAR(10) PRIMARY KEY,
Title VARCHAR(255),
Description TEXT,
Credits INT, FacultyID INT,
FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID) );
CREATE TABLE Faculty

(FacultyID INT PRIMARY KEY,
Name VARCHAR(255),
ContactInfo VARCHAR(255),
Expertise VARCHAR(255) );
CREATE TABLE Enrollment

(EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseCode VARCHAR(10),
EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode) );
CREATE TABLE AcademicRecord

( RecordID INT PRIMARY KEY,
StudentID INT,
CourseCode VARCHAR(10),
Grade CHAR(2),
GPA DECIMAL(3, 2),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode) );
CREATE TABLE CoursePrerequisites

( CourseCode VARCHAR(10),
PrerequisiteCode VARCHAR(10),
PRIMARY KEY (CourseCode, PrerequisiteCode),
FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode),
FOREIGN KEY (PrerequisiteCode) REFERENCES Course(CourseCode) );

EXPERIMENT 1 – ENTITY RELATIONSHIP DIAGRAMS (ERD)
OBJECTIVE
Design the Entity Relationship Diagrams (ERDs) by leveraging Crow's Foot notations within the user-friendly
interface of ER Assistant.
THEORY
The entity relationship diagram (ERD) is a graphical representation that depicts things of interest (entities) and
relationship among entities. ERD’s have three basic elements: entity types, relationships and attributes.
Components of ER Diagram
Symbols Used in ER Model
16 CS 220 Database System

Entity: Entities are collection of things of interest in an application. Each entity type represents collections of
physical things such as books, people and places as well as events such as payments.
Attribute: Attribute are the properties that define the entity type. For example, Roll No, Name, DOB, Age,
Address, and Mobile No are the attributes that define entity type Student. In ER diagram, the attribute is
represented by an oval.
Relationships: are named associations among entity types.
Relationship Cardinality: The number of times an entity of an entity set participates in a relationship set is
known as cardinality. Cardinality can be of different types:
cardinalities constrain the number of entities that participate in a relationship.
Cardinality can be classified into various types, namely:
1. One-to-One (1:1): This cardinality indicates that each entity instance in one entity is related to exactly one
entity instance in another entity, and vice versa. It is often denoted as 1:1.
2. One-to-Many (1:N): This cardinality signifies that each entity instance in one entity can be associated with
multiple entity instances in another entity, while each instance in the other entity is related to at most one
instance in the first entity. It is represented as 1:N.
3. Many-to-One (N:1): This cardinality implies that multiple instances in one entity can be related to a single
instance in another entity. It is often depicted as N:1.
4. Many-to-Many (N:N): This cardinality represents a complex relationship where multiple instances in one
entity can be associated with multiple instances in another entity. To implement a many-to-many relationship in
a database, an intermediary table is typically used. It is denoted as N:N.

Mandatory Relationship: A mandatory relationship signifies that an entity instance must participate in a
relationship. In other words, the existence of an entity instance in a mandatory relationship is necessary. This
constraint implies that an entity cannot exist without being associated with another entity through a particular
relationship. In an ERD, it is represented by a solid line connecting the entities.
Optional Relationship: An optional relationship indicates that an entity instance may or may not participate in
a relationship. It implies that the existence of an entity instance is not dependent on its participation in the
relationship. In an ERD, it is represented by a dashed line connecting the entities.
Classification of Cardinalities:
Weak entities and Identifying relationships: Entity types that borrow part or their entire primary key are
known as weak entities. The relationship that provides components of the primary key is known as an
identifying relationship.
Self-Referencing Relationship: A self-referencing relationship involves connections among members of the

same entity type.

Generalization Hierarchy: A collection of entity types arranged in a hierarchical structure to show similarity
in attributes. Each subtype contains a subset of entities of its super type.

Crow’s Foot Notation Symbols for ERD’s
Practice Problems for Experiment 1:-

The practice problem using the Crow’s Foot notation. You are encouraged to use the ER Assistant or other
drawing tool to complete the practice problems in Experiment.
1. Draw an ERD containing Student and Paper entity types connected by a 1-M relationship. The Student
entity type should have attributes for StdNo (primary key), StdFirstName, StdLastName, StdAdmitSemester,
StdAdmitYear, and StdEnrollStatus (full or part-time). The Paper entity type should have attributes for
PaperNo (primary key), PaperTitle, PaperSubmitDate, PaperAccepted (yes or no), and PaperType (first,
second, proposal, or dissertation). Add a 1-M relationship from Student to Paper.
2. Extend the ERD with an Evaluator entity type and an M-N relationship between Paper and Evaluator. The
Evaluator entity type should have attributes for EvalNo (primary key), EvalFirstName, EvalLastName,
EvalEmail, and EvalOffice. The M-N relationship should have attributes for EvalDate, EvalLitReview (1 to
5 rating), EvalProbId (1 to 5 rating), EvalTechWriting (1 to 5 rating), EvalModelDev (1 to 5 rating),
EvalOverall (1 to 5 rating), and EvalComments.
3. Transform the M-N relationship from problem 9 into an associative entity type and identifying relationships.

Solution:
Part 1
Part 2
Part 3

Summary
Student will understand attributes and relationship types used during ER Modeling.
Web Resources for Additional Studies

https://www.youtube.com/watch?v=a6UCXWJZ2GE
https://www.youtube.com/watch?v=bivWAcqVGrs
https://datapandas.com/index.php/2017/02/18/how-to-use-er-assistant-free-entity-relationship-diagram-erd-
creation-tool/

Lab# 1 EXCERCISE ( 15 marks)
Problem [15]
1. Draw an ERD containing the LabVisit and Patient entity types connected bya 1-M relationship from Patient
to LabVisit. Choose an appropriate relationship name using your common knowledge of interactions
between patients and lab visits. Define minimum cardinalities so that a patient is required for a lab visit. For
the Patiententity type, add attributes PatNo (primary key), PatLastName, PatFirstName, PatDOB (date of
birth). For the LabVisit entity type, add attributes for the LVNo (primary key), LVDate, LVProvNo, and
optional LVOrdNo (for orders from physicians). If you are using a data modeling tool that supports data
type specification, choose appropriate data types for the attributes based on your common knowledge.
2. Extend problem 1 with the Lab entity type connected by a 1-M relationship from Lab to LabVisit. Choose
an appropriate relationship name using your common knowledge of interactions between labs and lab visits.
Define minimum cardinalities so that a lab is required for a lab visit. For the Labentity type, add attributes
LabNo (primary key), LabName, LabStreet, LabCity, LabState, and LabZip. If you are using a data
modeling tool that supports data type specification, choose appropriate data types for the attributes based on
your common knowledge.
3. Augment your ERD from problem 2 with the Specimen entity type. For each specimen collected, the
database should record a unique SpecNo, SpecArea (vaginal, cervical, or endocervical), and
SpecCollMethod (thin prep or sure path). You should also add a 1-M relationship from LabVisit to
Specimen. A lab visit must produce at least one specimen. A specimen is associated with exactly one lab
visit.
Important Note: - Copied assignment will get zero Marks.

EXPERIMENT 2 – DEVELOPING DATA MODELS FOR BUSINESS DATABASES
OBJECTIVE
Create Entity Relationship Diagrams (ERDs) that align seamlessly with the context of narrative scenarios.
THEORY
Data modeling involves the collection and analysis of business requirements resulting in an ERD to represent
the requirements. Business requirements are rarely well structured. Rather, as an analyst you will often face an
ill-defined business situation in which you need to add structure. You will need to interact with a variety of
stakeholders who sometimes provide competing statements about the database requirements.
In collecting the requirements, you will conduct interviews, review documents and system documentation, and
examine existing data. The main goal when analyzing narrative problem statements is to create an ERD that is
consistent with the narrative.
The ERD should not contradict the implied ERD elements in the problem narrative.
Identifying Entity Types
In a narrative, you should look for nouns involving people, things, places, and events as potential entity types.
The nouns may appear as subjects or objects in sentences. For example, the sentence, "Students take courses
at the university" indicates that student and course may be entity types.
1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute. Strong Entity does not depend on other Entity in the
Schema. It has a primary key, that helps in identifying it uniquely, and it is represented by a rectangle. These are
called Strong Entity Types.
2. Weak Entity
An Entity type has a key attribute that uniquely identifies each entity in the entity set. But some entity type
exists for which key attributes can’t be defined. These are called Weak Entity types.
Determining Attributes
The properties often indicate attributes of entity types. For example, the sentence, "Students choose their
major and minor in their first year" indicates the major and minor may be attributes of student. The sentence,
"Courses have a course number, semester, year, and room listed in the catalogue" indicates that course number,
semester, year, and room are attributes of course.

1. Key Attribute
The attribute which uniquely identifies each entity in the entity set is called the key attribute. For example,
Roll_No will be unique for each student. In ER diagram, the key attribute is represented by an oval with
underlying lines.
2. Composite Attribute
An attribute composed of many other attributes is called a composite attribute. For example, the Address
attribute of the student Entity type consists of Street, City, State, and Country. In ER diagram, the composite
attribute is represented by an oval comprising of ovals.
3. Multivalued Attribute
An attribute consisting of more than one value for a given entity. For example, Phone_No (can be more than
one for a given student). In ER diagram, a multivalued attribute is represented by a double oval.
4. Derived Attribute
An attribute that can be derived from other attributes of the entity type is known as a derived attribute. e.g.; Age
(can be derived from DOB). In ER diagram, the derived attribute is represented by a dashed oval.

Determining Primary Keys
Identification of primary keys is an important part of entity type identification. Ideally, primary keys should be
stable and single purpose. "Stable" means that a primary key should never change after it has been assigned to
an entity. "Single purpose" means that a primary key attribute should have no purpose other than entity
identification. Typically, good choices for primary keys are integer values automatically generated by a DBMS.
Adding Relationships
Relationships often appear as verbs connecting nouns previously identified as entity types. For example, the
sentence, "Students enroll in courses each semester" indicates a relationship between students and courses.
For relationship cardinality, you should look at the number (singular or plural) of nouns along with other words
that indicate cardinality. For example, the sentence, "A course offering is taught by an instructor" indicates
that there is one instructor per course offering. You should also look for words such as "collection" and "set"
that indicate a maximum cardinality of more than one.
For example, the sentence, "An order contains a collection of items" indicates that an order is related to
multiple items.
Minimum cardinality can be indicated by words such as "optional" and "required." In the absence of indication
of minimum cardinality, the default should be mandatory.
Practice Problem

Information Requirements - The Water Utility Database
The water utility database should support the recording of water usage and billing for water usage. To support
these functions, the database should contain data about customers, rates, water usage, and bills. Other functions
such as payment processing and customer service are omitted from this description for brevity. The following
list describes the data requirements in more detail.
 Customer data include a unique customer number, a name, a billing address, a type (commercial or residential), an
applicable rate, and a collection (one or more) of meters.
 Meter data include a unique meter number, an address, a size, and a model. The meter number is engraved on the
meter before it is placed in service. A meter is associated with one customer at a time.
 An employee periodically reads each meter on a scheduled date. When a meter is read, a meter-reading document
is created containing a unique meter reading number, an employee number, a meter number, a timestamp (includes
date and time), and a consumption level. When a meter is first placed in service, there are no associated readings for
it.
 A rate includes a unique rate number, a description, a fixed dollar amount, a consumption threshold, and a variable
amount (dollars per cubic foot). Consumption up to the threshold is billed at the fixed amount. Consumption greater
than the threshold is billed at the variable amount. Customers are assigned rates using a number of factors such as
customer type, address, and adjustment factors. Many customers can be assigned the same rate. Rates are typically
proposed months before approved and associated with customers.
 The water utility bills are based on customers' most recent meter readings and applicable rates. A bill consists of a
heading part and a list of detail lines. The heading part contains a unique bill number, a customer number, a
preparation date, a payment due date, and a date range for the consumption period. Each detail line contains a
meter number, a water consumption level, and an amount. The water consumption level is computed by subtracting
the consumption levels in the two most recent meter readings. The amount is computed by multiplying the
consumption level by the customer's rate.
Solution

Summary
Student will go through development phase of ERDs.

https://www.youtube.com/watch?v=a6UCXWJZ2GE
https://www.youtube.com/watch?v=bivWAcqVGrs
https://datapandas.com/index.php/2017/02/18/how-to-use-er-assistant-free-entity-relationship-diagram-erd-
creation-tool/

Problem 2.1: [5]
Define an ERD for the following narrative. The database should track homes and owners. A home has a unique
homidentifier, a street address, a city, a state, a zip, a number of bedrooms, a number of bathrooms, and square
feet. A home is either owner occupied or rented. An owner has a Social Security number, a name, an optional
spouse name, a profession, and an optional spouse profession. An owner can possess one or more homes. Each
home has only one owner.
Problem 2.2: [10]

Construct an ERD to represent accounts in a database for personal financial software. The software supports
checking accounts, credit cards, and two kinds of investments (mutual funds and stocks). No other kinds of
accounts are supported, and every account must fall into one of these account types.
For each kind of account, the software provides a separate data entry screen. The following list describes the
fields on the data entry screens for each kind of account:
• For all accounts, the software requires the unique account identifier, the account name, date established, and
the balance.
• For checking accounts, the software supports attributes for the bank name, the bank address, the checking
account number, and the routing number.
• For credit cards, the software supports attributes for the credit card number, the expiration date, and the
credit card limit.
• For stocks, the software supports attributes for the stock symbol, the stock type (common or preferred), the
last dividend amount, the last dividend date, the exchange, the last closing price, and the number of shares (a
whole number).
• For mutual funds, the software supports attributes for the mutual fund symbol, the share balance (a real
number), the fund type (stock, bond, or mixed), the last closing price, the region (domestic, international, or
global), and the tax-exempt status (yes or no).

EXPERIMENT 3 – DATA DEFINITION LANGUAGE (DDL)
OBJECTIVE
Exécute SQL DDL (Data Definition Language) statements to define and manage the structure of a database,
including creating, modifying, and deleting database objects such as tables, indexes, views, and schemas.
THEORY
Structured Query Language

SQL (Structured Query Language) is a database computer language designed for the retrieval and management
of data in relational database management systems (RDBMS), database schema creation and modification, and
database object access control management. SQL is a standard supported by all the popular relational database
management systems in the marketplace. The basis data structure in RDBMS is a table. SQL provides you the
features to define tables, define constraints on tables, query for data in table, and change the data in table by
adding, modifying, and removing data. SQL also supports grouping of data in multiple rows, combining tables
and other features. All these put together, SQL is a high-level query language standard to access and alter data
in RDBMS.
History of SQL
The first version of SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early
1970s. This version, initially called SEQUEL, was designed to manipulate and retrieve data stored in IBM's
original relational database product; System R. IBM patented their version of SQL in 1985, while the SQL
language was not formally standardized until 1986, by the American National Standards Institute (ANSI) as
SQL-86. Subsequent versions of the SQL standard have been released by ANSI and as International
Organization for Standardization (ISO) standards.
Originally designed as a declarative query and data manipulation language, variations of SQL have been
created by SQL database management system (DBMS) vendors that add procedural constructs, control-of-flow
statements, user-defined data types, and various other language extensions. With the release of the SQL: 1999
standard, many such extensions were formally adopted as part of the SQL language via the SQL Persistent
Stored Modules (SQL/PSM) portion of the standard.
Various Data Types:
1. Character Datatypes:
 Char – fixed length character string that can varies between 1-2000 bytes
 Varchar / Varchar2 – variable length character string, size ranges from 1-4000 bytes.it saves the disk
space(only length of the entered value will be assigned as the size of column)
 Long - variable length character string, maximum size is 2 GB
2. Number Datatypes : Can store +ve,-ve,zero,fixed point,floating point with 38 precission.
 Number – {p=38,s=0}
 Number(p) - fixed point
 Number(p,s) –floating point (p=1 to 38,s= -84 to 127)
3. Date Datatype: used to store date and time in the table.

 DB uses its own format of storing in fixed length of 7 bytes for
century,date,month,year,hour,minutes,seconds.
 Default data type is “dd-mon-yy”

Database vendors: The leading database vendors are:
Microsoft: MS SQL server, transact- SQL
Teradata: Teradata
Oracle: oracle, PL/SQL
IBM: DB2
Several aspects of SQL:

DDL: data-definition language provides commands for create, alter, truncate and drop tables
DML: data-manipulation language provides commands for insert, delete and update rows.
DCL: Data-control language provides commands for grant and revokes access to users.
Data Definition Language (DDL) commands in RDBMS

It is used to communicate with database. DDL is used to:
 Create an object
 Alter the structure of an object
 To drop the object created.
The commands used are:

 Create
 Alter
 Drop
 Truncate
CREATE TABLE
This command is used to create a table in a database. Tables are organized into rows and columns. The general
syntax is as bellow:-
Syntax: Create table tablename (column_name1 data_ type constraints,

column_name2 data_ type constraints …)
Note:- The column_name parameters specify the names of the columns of the table and data_type parameter
specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).
Example:
CREATE TABLE Customer
( CustNo CHAR(8),
CustFirstName VARCHAR(20) CONSTRAINT CustFirstNameRequired NOT NULL,
CustLastName VARCHAR(30) CONSTRAINT CustLastNameRequired NOT NULL,
CustStreet VARCHAR(50),
CustCity VARCHAR(30),
CustState CHAR(2),
CustZip CHAR(10),
CustBal DECIMAL(12,2) DEFAULT 0,
CONSTRAINT PKCustomer PRIMARY KEY (CustNo) )
CREATE TABLE Employee

( EmpNo CHAR(8),
EmpFirstName VARCHAR(20) CONSTRAINT EmpFirstNameRequired NOT NULL,
EmpLastName VARCHAR(30) CONSTRAINT EmpLastNameRequired NOT NULL,

EmpPhone CHAR(15),
EmpEMail VARCHAR(50) CONSTRAINT EmpEMailRequired NOT NULL,
SupEmpNo CHAR(8),
EmpCommRate DECIMAL(3,3),
CONSTRAINT PKEmployee PRIMARY KEY (EmpNo),
CONSTRAINT UNIQUEEMail UNIQUE(EmpEMail),
CONSTRAINT FKSupEmpNo FOREIGN KEY (SupEmpNo) REFERENCES Employee )
ALTER TABLE
Alter command is used to:
 Add a new column.
 Modify the existing column definition.
 To include or drop integrity constraint.
Syntax: alter table tablename add/modify (attribute datatype(size));
Example:
To add the Column in a table
ALTER TABLE Customer
ADD Martial_Status char(1);
To delete the Column in a table

DROP COLUMN Martial_Status;
To modify the Column in a table

ALTER COLUMN CustCity char(60);
To add a constraint in a table

ADD CONSTRAINT pk_cust PRIMARY KEY (Customer_id);
DROP TABLE
Drop statement is used to delete a table including records inside the table.
Syntax: DROP TABLE <TABLE NAME>;
Example:
DROP TABLE Customer;
TRUNCATE TABLE
Truncate statement is used to delete data from a table. If there is no further use of records stored in a table and
the structure has to be retained then the records alone can be deleted.
Syntax: TRUNCATE TABLE <TABLE NAME>;
Example:
TRUNCATE TABLE Customer;

INTEGRITY CONSTRAINT
An integrity constraint is a mechanism used by oracle to prevent invalid data entry into the table. It has
enforcing the rules for the columns in a table. The types of the integrity are:
a) Domain Integrity
b) Entity Integrity
c) Referential Integrity
a) Domain Integrity
This constraint sets a range and any violations that take place will prevent the user from performing the
manipulation that caused the breach. It includes:
Not Null constraint:
While creating tables, by default the rows can have null value. The enforcement of not null constraint in a table
ensures that the table contains values.
Example:
(CustNo CHAR(8),
CustState CHAR(2),
CustZip CHAR(10),
CONSTRAINT PKCustomer PRIMARY KEY (CustNo))
You can add not null constraint, If you forget to add not null constraint on table while creating table.
Example:
ALTER COLUMN CustCity char(30) NOT NULL;
This command will ensure that the user enters a value for the CustCity columns on the Customer table, failing
which it returns an error message.
Check Constraint:
Check constraint can be defined to allow only a particular range of values.when the manipulation violates this
constraint; the record will be rejected.Check condition cannot contain sub queries.
Example:
CREATE TABLE Customer1
(CustNo CHAR(8) check (len(CustNo)>=8),
CustFirstName VARCHAR(20) CONSTRAINT CustFirstNameRequired1 NOT NULL,
CustLastName VARCHAR(30) CONSTRAINT CustLastNameRequired1 NOT NULL,
CustState CHAR(2),
CustZip CHAR(10),
CONSTRAINT PKCustomer1 PRIMARY KEY (CustNo))

You can add more constraints, If you forget to add it while creating table.
ALTER TABLE Employee

ADD CONSTRAINT chk_EmpCommRate CHECK (EmpCommRate>=0.0);
ALTER TABLE Employee

ADD CONSTRAINT chk_Emp_fname CHECK (len(EmpFirstName)>=3);
b) Entity Integrity
Maintains uniqueness in a record. An entity represents a table and each row of a table represents an instance of
that entity. To identify each row in a table uniquely we need to use this constraint. There are 2 entity
constraints:
a) Unique key constraint
It is used to ensure that information in the column for each record is unique, as with telephone or drivers license
numbers. It prevents the duplication of value with rows of a specified column in a set of column. A column
defined with the constraint can allow null value.
If unique key constraint is defined in more than one column i.e., combination of column cannot be specified.
Maximum combination of columns that a composite unique key can contain is 16.
Example:
(CustNo CHAR(8),
CustStreet VARCHAR(50) CONSTRAINT cust_uniq_street UNIQUE,
CustState CHAR(2),
CustZip CHAR(10),
You can add unique constraint, If you forget to add it while creating table.

ADD CONSTRAINT uniq_cust_street UNIQUE(CustStreet);
PRIMARY KEY CONSTRAINT

A primary key avoids duplication of rows and does not allow null values. Can be defined on one or more
columns in a table and is used to uniquely identify each row in a table. These values should never be changed
and should never be null.
A table should have only one primary key. If a primary key constraint is assigned to more than one column or
combination of column is said to be composite primary key, which can contain 16 columns.
Example;
(CustNo CHAR(8),

CustState CHAR(2),
CustZip CHAR(10),
CONSTRAINT PKCustomer PRIMARY KEY (CustNo))
Note: Can’t be defined using alter command when there is records in the table having null values.
c) Referential Integrity
It enforces relationship between tables. To establish parent-child relationship between 2 tables having a
common column definition, we make use of this constraint. To implement this, we should define the column in
the parent table as primary key and same column in the child table as foreign key referring to the corresponding
parent entry.
Foreign key
A column or combination of column included in the definition of referential integrity, which would refer to a
referenced key.
Referenced key
It is a unique or primary key upon which is defined on a column belonging to the parent table.
Example:

(CustNo CHAR(8),
CustState CHAR(2),
CustZip CHAR(10),

CREATE TABLE OrderTbl3
( OrdNo CHAR(8),
OrdDate DATE CONSTRAINT OrdDateRequired3 NOT NULL,
CustNo CHAR(8) CONSTRAINT CustNoRequired3 NOT NULL,
EmpNo CHAR(8),
OrdName VARCHAR(50),
OrdStreet VARCHAR(50),
OrdCity VARCHAR(30),
OrdState CHAR(2),
OrdZip CHAR(10),
CONSTRAINT PKOrderTbl3 PRIMARY KEY (OrdNo),
CONSTRAINT FKCustNo3 FOREIGN KEY (CustNo) REFERENCES Customer3)
Summary
An integrity constraint is a mechanism used by oracle to prevent invalid data entry into the table. It has
enforcing the rules for the columns in a table.

http://www.1keydata.com/
http://www.w3schools.com/

Problem 3.1: [5]
Create Employee table to store given data as bellow:-
empno empname department email phone

E100 Chuck Coordinator Administration [email protected] 3-1111
E101 Mary Manager Football [email protected] 5-1111
E102 Sally Supervisor Planning [email protected] 3-2222
E103 Alan Administrator Administration [email protected] 3-3333
Note :- Set data type according to data requirements.
Problem 3.2: [5]

Write a query to add constraints to the EMPLOYEE table that EMPNO as the primary key.
Problem 3.3: [5]

The Company wants to set minimum event cost is 1000. You are required to write a query to add suitable
constraint to the EventRequest table.

EXPERIMENT 4 – DATA MANIPULATION LANGUAGE (DML)
OBJECTIVE
Exécute SQL DML (Data Manipulation Language) statements to manipulate data stored in a relational database
management system (RDBMS).
THEORY
Data Manipulation Language (DML)

DML commands are the most frequently used SQL commands and is used to query and manipulate the existing
database objects. Some of the commands are
 Insert
 Select
 Update
 Delete
Insert Command
This is used to add one or more rows to a table. The values are separated by commas and the data types char and
date are enclosed in apostrophes. The values must br entered in the same order as they are defined.
Inserting a single row into a table:

Syntax: insert into <table name> values (value list)
Example:
insert into Customer values ('C0954327','Sheri','Gordon','336 Hill
St.','Littleton','CO','80129-5543',$230.00)
Check: select * from Customer
Inserting more than one record using a single insert commands:

Syntax: INSERT INTO table_name
VALUES (value1, value2, value3,...), (value1, value2, value3,...), (value1, value2, value3,...);
Example:
insert into Customer values
('C1010398','Jim','Glussman','1432 E. Ravenna','Denver','CO','80111-0033',$200.00),
('C2388597','Beth','Taylor','2396 Rafter Rd','Seattle','WA','98103-1121',$500.00),
('C3340959','Betty','Wise','4334 153rd NW','Seattle','WA','98178-3311',$200.00)
Check: select * from Customer
Select Commands
It is used to retrieve information from the table. It is generally referred to as querying the table. We can either
display all columns in a table or only specify column from the table.
Syntax: Select * from tablename; //

This query selects all rowsfrom the
table.
Example
select * from Employee

The retrieval of specific columns from a table:
It retrieves the specified columns from the table
Syntax: Select column_name1, …..,column_namen from table name;
Example:
Select empno, empfirstname from employee;
Elimination of duplicates from the select clause:

It prevents retrieving the duplicated values .Distinct keyword is to be used.
Syntax: Select DISTINCT col1, col2 from table name;
Example: select distinct custcity from customer
Select command with where clause:

To select specific rows from a table we include ‘where’ clause in
the select command. It can appear only after the ‘from’ clause.
Syntax: Select column_name1, …..,column_namen from table

name where condition;
Example: Select empno, empfirstname, EmpCommRate from

employee where EmpCommRate>=0.040;
Wildcard Operators
Use of Like operator (% and _ are used for
multiple & Single Char matching)
Example: Select * from employee where empfirstname like 'T%';
Select command with order by clause:

Syntax: Select column_name1, …..,column_namen from table name where condition order by colmnname;

Example: Select * from employee order by empfirstname;
Update Command
It is used to alter the column values in a table. A single column may be updated or more than one column could
be updated.
Syntax: update tablename set field=values where condition;
Example: Update Employee set EmpCommRate=0.070 where EmpNo='E9973110'
Delete command
After inserting row in a table we can also delete them if required. The delete command consists of a from clause
followed by an optional where clause.
Syntax: Delete from table where conditions;
Example: Delete from Employee where EmpNo='E9973110'
Summary
A data manipulation language (DML) is a family of syntax elements similar to a computer programming
language used for selecting, inserting, deleting and updating data in a database. Performing read-only queries of
data is sometimes also considered a component of DML. Data manipulation language comprises the SQL data
change statements, which modify stored data but not the schema or database objects. Manipulation of persistent
database objects, e.g., tables or stored procedures, via the SQL schema statements, rather than the data stored
within them, is considered to be part of a separate data definition language. In SQL these two categories are
similar in their detailed syntax, data types, expressions etc., but distinct in their overall function.

Lab# 4 EXCERCISE (15 marks)
Problem 4.1: [2]

Insert 10 record into Customer table.
Problem 4.2: [2]

Insert 12 record into Employee table.
Problem 4.3: [2]

Update the resourcetbl table to set the rate of event is $15, which has resno is R100.
Problem 4.4: [3]

Delete all customer, who are belonging to "Boulder" city.
Problem 4.5: [3]

Delete the customer, whose custno is C100.
Problem 4.6: [3]

List all cutomer, order by name
In ascending order.
In descending order

EXPERIMENT 5 – DATA FILTERING USING GROUP BY AND HAVING CLAUSE
OBJECTIVE
Execute SQL Group by and having clauses to group and filter data in the result set of a SQL query based on
certain criteria.
THEORY
Group Functions
A group function returns a result based on group of rows.
aggr
1. The AVG() function returns the average value of a numeric column

Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Example: To calculate the average custBal of customers.

select avg (custbal) as Bal from customer;
2. Max:
Example: select Max(EmpCommRate) from Employee;
3. Min
Example: select Min(EmpCommRate) from Employee;
4. Sum
Example: select sum (custbal) from customer;
Count Function
In order to count the number of rows, count function is used.
1. count(*) – It counts all, inclusive of duplicates and nulls.

Example: select count(*) from Employee;
2. count(col_name)– It avoids null value.

Example: select count(SupEmpNo) from Employee;
3. count(distinct col_name) – It avoids the repeated and null values.

Example: select count(distinct CustState) from Customer;
Group by clause
This is function is used in conjunction with the aggregate functions to group the result-set by one or more
columns. This allows us to use simultaneous column name and group functions.
Example:
Select CustState, max(CustBal)
from Customer
42 CS 220 Database Systems – DML

group by CustState;
Having clause
This is used to specify conditions on rows retrieved by using group by clause.
Example:
Select CustState, max(CustBal)
from Customer
group by CustState
Having max(CustBal)>300;
Special Operators
In / not in – used to select a equi from a specific set of values
Any - used to compare with a specific set of values
Between / not between – used to find between the ranges
Like / not like – used to do the pattern matching
Examples of Group by and Having
Summary
The group by clause is used to group the data. The group by and having clauses are very important for data
analysis.


Problem 5.1: [2]

Display activity wise number of Event planed.
Problem 5.2: [2]

Display month wise number of Events Requested in year 2018.
Problem 5.3: [2]

Count and display the total number of employees in each department and make sure department has greater than
5 employees.
Problem 5.4: [3]

Display status wise number of Events Requested and estimated cost is greater than equal to $5000.
Problem 5.5: [3]

List event requested, which has highest number of audience in each month of 2018
Problem 5.6: [3]

Display all the details of employee, whose name does start with ‘A’ using LIKE operator.

Schema Diagram to be used for Lab Exercises
List of Tables used for Lab Exercises

Employee

EventRequest
eventno dateheld datereq facno custno dateauth status estcost estaudience budno
E100 25-Oct-2018 06-Jun-2018 F100 C100 08-Jun-2018 Approved $5,000.00 80000 B1000
E101 26-Oct-2018 28-Jul-2018 F100 C100 Pending $5,000.00 80000 B1000
E102 14-Sep-2018 28-Jul-2018 F100 C100 31-Jul-2018 Approved $5,000.00 80000 B1000
E103 21-Sep-2018 28-Jul-2018 F100 C100 01-Aug-2018 Approved $5,000.00 80000 B1000
E104 03-Dec-2018 28-Jul-2018 F101 C101 31-Jul-2018 Approved $2,000.00 12000 B1000
E105 05-Dec-2018 28-Jul-2018 F101 C101 01-Aug-2018 Approved $2,000.00 10000 B1000
E107 23-Nov-2018 28-Jul-2018 F100 C105 31-Jul-2018 Denied $10,000.00 5000
EventPlan
planno eventno workdate notes activity empno
P100 E100 25-Oct-2018 Standard operation Operation E102
P101 E104 03-Dec-2018 Watch for gate crashers Operation E100
P102 E105 05-Dec-2018 Standard operation Operation E102
P103 E106 12-Dec-2018 Watch for seat switching Operation
P104 E101 26-Oct-2018 Standard cleanup Cleanup E101
P105 E100 25-Oct-2018 Light cleanup Cleanup E101
P299 E101 26-Oct-2018 Operation E101
P349 E106 12-Dec-2018 Cleanup E101
P85 E100 25-Oct-2018 Standard operation Setup E102
P95 E101 26-Oct-2018 Extra security Setup E102

EXPERIMENT 6 – SQL INBUILT FUNCTIONS
OBJECTIVE
Exécute SQL aggregate functions to perform calculations on sets of values in a database table.
THEORY
Functions
Function accepts zero or more arguments and both return one or more results. Both are used to manipulate
individual data items. Operators differ from functional in that they follow the format of function_name(arg..).
An argument is a user defined variables or constants. Most operators accept at most 2 arguments while the
structure of functions permit to accept 3 or more arguments. Function can be classifying into single row
function and group functions.
Single Row functions

A single row function or scalar function returns only one value for every row queries in table. Single row
function can appear in a select command and can also be included in a where clause. The single row function
can be broadly classified as:
 Date Function
 Numeric Function
 Character Function
 Conversion Function
 Group Function
Example: This is function is used to get the current system date.

SELECT GETDATE();
Date Function
They operate on date values and produce outputs, which also belong to date data type except for months,
between, date function returns a number.
1. DATEADD(): This function returns a date after adding a specified date with specified number of days,
months or years.
Example: To find date after adding one month.

SELECT ordDate, DATEADD(month,1,ordDate) FROM orderTbl;
SELECT GETDATE(), DATEADD(year,2,GETDATE());
2. Months_between
It gives the difference in number of months between d1 & d2.
Syntax: month_between (d1,d2); where d1 & d2 –dates
Example:
SELECT DATEDIFF(YYYY, '15-Jan-2017', GETDATE())
5. Round
This function returns value, which is rounded to the unit specified by the format model.
47 CS 220 Database System – SQL Functions

ROUND(number, decimals, operation)
Parameter Description
number Required. The number to be rounded
decimals Required. The number of decimal places to round number to
operation Optional. If 0, it rounds the result to the number of decimal. If another value than 0, it
truncates the result to the number of decimals. Default value is 0
Example:
SELECT ROUND(235.415, 2) AS RoundValue;
Numerical Functions
Command Query Output

ABS(number) SELECT Abs(-15) AS AbsNum; 15
CEILING(number) SELECT CEILING(55.67) AS CeilValue; 56
EXP(number) SELECT EXP(4) as EXPValue; 54.59
FLOOR(number) SELECT FLOOR(100.2) AS FloorValue; 100
POWER(base,exponent) SELECT POWER(4, 2) AS PowerOfNum; 16
SQRT(number) SELECT SQRT(16) AS SqrtOfNum; 4
6. Replace
The REPLACE() function replaces all occurrences of a substring within a str, with a new substring.
REPLACE(str, old_str, new_str)
str Required. The original string
old_str Required. The string to be replaced
new_str Required. The new replacement string
Example:
SELECT REPLACE('Jack and jue', 'j', 'bl');
7. Substring
This function extracts some characters from a string .
SUBSTRING(str, start, length)
str Required. The string to extract from
start Required. The start position. The first position in string is 1
length Required. The number of characters to extract. Must be a positive
number
Example:
SELECT SUBSTRING('information', 3, 4) AS 'Part of String';
Character Functions

Command Query Output
LOWER(text) SELECT LOWER('HELLO') LowerText; hello
UPPER(text) SELECT UPPER('hello') upperText;
Hello
LTRIM(string) SELECT LTRIM(' Hello') AS Hello
LeftTrimmedString;
RTRIM(string) SELECT RTRIM('Hello ') AS Hello
RightTrimmedString;
REPLACE(str, old_str, new_str SELECT REPLACE('Jack and jue', 'j', 'bl'); Black and blue
)
SUBSTRING(str,start,lengt SELECT SUBSTRING('information', 3, 4) AS From
h) 'Part of String';
Conversion Function
1. Date Converstion
This function converts date to a value of varchar type in a form specified by date format. If format is negelected
then it converts date to varchar2 in the default date format.
Example:
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM) SELECT convert(varchar,
getdate(), 101) -- mm/dd/yyyy - 10/02/2015
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2015.10.02
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar(12),getdate(),106) --dd-mon-yyyy 15 Nov 2015
Summary
A built-in function is an expression in which an SQL keyword or special operator executes some operation.
Built-in functions use keywords or special built-in operators. Function accepts zero or more arguments and both
return one or more results. Both are used to manipulate individual data items. Operators differ from functional
in that they follow the format of function_name (arg..).


Problem 6.1: [2]

Count and diplay the total numer of employees in Administration Department.
Problem 6.2: [2]

Calaulate the max and min rate of resources and name the column as max_rate and min_rate.
Problem 6.3: [2]

List all resources and 30% rise in rate.
Problem 6.4: [3]

Show how many days are left for each event to be held as compare with current system date.
Problem 6.5: [3]

Show all customer city in captical letters.
Problem 6.6: [3]

Write a query to update name of all employees with capital letters. Verify you result with select statement.


Customer
custno custname address Internal contact phone city state zip
C100 Faisal Naseer Box 352200 Yes Mary Manager 6857100 Boulder CO 80309
C101 Mubashir Nazir Box 352400 Yes Sally Supervisor 5431700 Boulder CO 80309
C103 Babar Nawaz Box 352020 Yes Bill Baseball 5431234 Boulder CO 80309
C104 Waheed Iqbal Box 351200 Yes Sue Softball 5434321 Boulder CO 80309
C105 Hashir Shahid 123 AnyStreet No Coach Bob 4441234 Louisville CO 80027

Employee
ResourceTbl
resno resname rate
R100 attendant $10.00
R101 police $15.00
R102 usher $10.00
R103 nurse $20.00
R104 janitor $15.00
R105 food service $10.00
EventRequest

EXPERIMENT 7 – NESTED QUERIES
OBJECTIVE
Perform complex filtering and retreive data from multiple tables uisng SQL nested queries.
THEORY
Nested Queries
Nesting of queries one within another is known as a nested query.
Subqueries
The query within another is known as a subquery. A statement containing subquery is called parent statement.
The rows returned by subquery are used by the parent statement.
Syntax
SELECT "col_name1” FROM "table_name1"

WHERE "col_name2" [Comparison Operator]
(SELECT "col_name3” FROM "table_name2" WHERE [Condition])
Example: To get employee, whose commission is greater then the commission of Amy.
select EmpNo, EmpFirstName, EmpPhone, EmpCommRate
from Employee
where EmpCommRate >(select EmpCommRate
from employee
where EmpFirstName ='Amy');
Note:- The subquery (inner query) executes once before the main query (outer query) and The result of the
subquery is used by the main query.
53 CS 220 Database System –Sub Queries

Guidelines for Using Subqueries
• Enclose subqueries in parentheses.
• Place subqueries on the right side of the comparison condition.
• The ORDER BY clause in the subquery is not needed.
• Use single-row operators with single-row (>,=) subqueries, and use multiple-row operators (IN) with
multiple-row subqueries.
Types
1. Subqueries that return several values
Subqueries can also return more than one value. Such results should be made use along with the operators IN,
ANY and ALL
Operator Meaning
IN Equal to any member in the list
ANY Compare value to each value returned by the subquery
ALL Compare value to every value returned by the subquery
Example: To get customer, whose balance is greater then balance of all customer belongs to ‘CO’ state.
select custno, custBal
from customer
where custbal >all (select custbal
from customer
where custState ='CO');
2. Multiple queries
Here more than one subquery is used. These multiple subqueries are combined by
means of ‘and’ & ‘or’ keywords.
SetOperators
The Set operator combines the result of 2 queries into a single result.The following are the operators:
 Union
 Union all
 Intersect
 Minus
The rules to which the set operators are strictly adhere to:
 The queries which are related by the set operators should have a same number of column and column
definition.
 Such query should not contain a type of long.
 Labels under which the result is displayed are those from the first select statement.
Union:
Returns all distinct rows selected by both the queries
Rules:
• SELECT statement within the UNION must have the same number of colns.
• The columns must also have similar data types.
• The columns in each SELECT statement must be in the same order.
Syntax:
select * from Customer where Custcity='Denver' or CustCity='Englewood'
union
select * from Customer where Custcity='Denver' or Custcity='Littleton'
Intersect:
Returns rows selected that are common to both queries.
Syntax:
select * from Customer where Custcity='Denver' or CustCity='Englewood'
intersect
select * from Customer where Custcity='Denver' or Custcity='Littleton'
SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real
tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were
coming from one single table.

Syntax:
CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example: How to create view

CREATE VIEW myEmployee AS
select EmpNo, EmpFirstName, EmpPhone, EmpCommRate
from Employee
How to access view
select * from myEmployee
Examples of Sub queries

Summary
The query within another is known as a subquery. A statement containing subquery is called parent statement.
The rows returned by subquery are used by the parent statement. A Subquery or Inner query or Nested query is
a query within another SQL query and embedded within the WHERE clause.A subquery is used to return data
that will be used in the main query as a condition to further restrict the data to be retrieved.


Problem 7.1: [2]

Display all eventno, datereq and estcost, whose estcost is lesser than maximum estcost of current requested
events.
SELECT eventno, datereq, estcost
FROM your_table_name
WHERE estcost < (
SELECT MAX(estcost)
FROM your_table_name
);
Problem 7.2: [2]

Write a query to find all the customers who’s city is same as ‘Babar Nawaz’.
SELECT *
FROM customers
WHERE city = (
SELECT city
FROM customers
WHERE name = 'Babar Nawaz'
);
Problem 7.3: [2]

Write a query to display information of Eventrequest which has estcost more than any Approved event.
SELECT *
FROM Eventrequest
WHERE estcost > (
SELECT MAX(estcost)
FROM ApprovedEvents
);

Problem 7.4: [3]
Write a query to list all the eventrequest, which has estcost < then average estcost of events.
SELECT *
FROM Eventrequest
WHERE estcost < (
SELECT AVG(estcost)
FROM Eventrequest
);
Problem 7.5: [3]

a. List all event planed, which are in operation or setup using IN operator.
SELECT *
FROM EventPlans
WHERE status IN ('operation', 'setup');
b. List all event requested, which has estimated cost is greater than all pending events using ALL operator.
SELECT *
FROM EventRequests
WHERE estcost > ALL (
SELECT estcost
FROM PendingEvents
);
c. List all event requested, which has estimated cost is less than any pending events using ANY operator.
SELECT *
FROM EventRequests
WHERE estcost < ANY (
SELECT estcost
FROM PendingEvents
);
Problem 7.6: [3]

Write a query to create a view, which list the customer number, name and phone no.
CREATE VIEW CustomerDetails AS
SELECT customer_number, name, phone_no
FROM Customers;


Customer

EventPlan
P349 E106 12-Dec-2018 Cleanup E101
EventRequest

EXPERIMENT 8 – SQL JOINS (CROSS, INNER, SELF)
OBJECTIVE
Exécute SQL joins to retrieve and present data from multiple tables in a single result set based on combine rows
from two or more tables based on related columns between them.
THEORY
SQL Joins:
The purpose of a join concept is to combine data spread across tables. A join is actually performed by the
‘where’ clause which combines specified rows of tables.
PROGRAMS
SQL Join Algorithms
Syntax:
• Select
• From
• Join
• On
• Where
Cross Join
• A cross join (also called a Cartesian join) is a join of tables without specifying the join condition.
• In this scenario, the query would return all possible combination of the tables in the SQL query.
• CROSS JOIN returns the Cartesian product of rows from tables in the join.
• In other words, it will produce rows which combine each row from the first table with each row from the
second table.
Cartesian product
• In mathematics, a Cartesian product (or product set) is the direct product of two sets.
• Specifically, the Cartesian product of two sets X (for example the points on an x-axis) and Y (for
example the points on a y-axis), denoted X × Y, is the set of all possible ordered pairs whose first com-
ponent is a member of X and whose second component is a member of Y (e.g., the whole of the x–y
plane):
Example:
• A = {1,2}; B = {3,4}
• A × B = {1,2} × {3,4} = {(1,3), (1,4), (2,3), (2,4)}
Example : Show all combination of customer and orderTbl (Cross Join)

SELECT * FROM Customer CROSS JOIN OrderTbl;
Inner join
• Inner join creates a new result table by taking
• Cartesian product
• filter (join predicate)
62 CS 220 Database Systems – Sub Queries

• An inner join in SQL returns rows where there is at least one match on both tables.
Example: Show all coustomer with order listing (Inner Join)

SELECT * FROM Customer
INNER JOIN OrderTbl ON Customer.CustNo = OrderTbl.CustNo;
OR
SELECT * FROM Customer, OrderTbl
where Customer.CustNo = OrderTbl.CustNo;
Types of Join
Composite join: This join has more than one condition on single ON clause.
Example:
INNER JOIN OrderTbl ON Customer.CustNo = OrderTbl.CustNo and CustState='CO'
Equi join: This join has an only equality comparison on join predicate.
Example:
INNER JOIN OrderTbl ON Customer.CustNo = OrderTbl.CustNo;
Non-equijoin: This join has other operator than equality on join predicate.
Example:
INNER JOIN OrderTbl ON Customer.CustNo != OrderTbl.CustNo;
Multitable join: This join is used to join more than two tables. Processing starts from left to right.
Example:
SELECT Customer.CustNo, CustFirstName, OrderTbl.OrdNo, OrdDate, Product.ProdNo, ProdName FROM
Customer
INNER JOIN OrderTbl ON Customer.CustNo = OrderTbl.CustNo
INNER JOIN OrdLine ON OrderTbl.OrdNo = OrdLine.OrdNo
INNER JOIN Product ON OrdLine.ProdNo = Product.ProdNo
Summary
A SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as
a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to
each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL
OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-
join.



Problem 8.1: [2]

Display the all customer’s number, name and event status and its estimated cost.
Problem 8.2: [3]

Display the list of events and assigned to which employee.
Problem 8.3: [5]

Display events requested which are planed in year 2018 and has Football stadium facility.
Problem 8.4: [5]

Display the list Eventplan which has rate greater or equal to 15.


Customer

Employee
Facility
facno facname
F100 Football stadium
F101 Basketball arena
F102 Baseball field
F103 Recreation room
Location
locno facno locname
L100 F100 Locker room
L101 F100 Plaza
L102 F100 Vehicle gate
L103 F101 Locker room
L104 F100 Ticket Booth
L105 F101 Gate
L106 F100 Pedestrian gate
ResourceTbl
resno resname rate
R100 attendant $10.00
R101 police $15.00
R102 usher $10.00
R103 nurse $20.00
R104 janitor $15.00
R105 food service $10.00
EventRequest

EventPlanLine
PlanNo LineNo TimeStart TimeEnd NumberFld LocNo ResNo
P100 1 8:00 17:00 2 L100 R100
P100 2 12:00 17:00 2 L101 R101
P100 3 7:00 16:30 1 L102 R102
P100 4 18:00 22:00 2 L100 R102
P101 1 18:00 20:00 2 L103 R100
P101 2 18:30 19:00 4 L105 R100
P101 3 19:00 20:00 2 L103 R103
P102 1 18:00 19:00 2 L103 R100
P102 2 18:00 21:00 4 L105 R100
P102 3 19:00 22:00 2 L103 R103
P103 1 18:00 21:00 2 L103 R100
P103 2 18:00 21:00 4 L105 R100
P103 3 19:00 22:00 2 L103 R103
P104 1 18:00 22:00 4 L101 R104
P104 2 18:00 22:00 4 L100 R104
P105 1 18:00 22:00 4 L101 R104
P105 2 18:00 22:00 4 L100 R104
P199 1 8:00 12:00 1 L100 R100
P349 1 12:00 15:30 1 L103 R100
P85 1 9:00 17:00 5 L100 R100
P85 2 8:00 17:00 2 L102 R101
P85 3 10:00 15:00 3 L104 R100
P95 1 8:00 17:00 4 L100 R100
P95 2 9:00 17:00 4 L102 R101
P95 3 10:00 15:00 4 L106 R100
P95 4 13:00 17:00 2 L100 R103
P95 5 13:00 17:00 2 L101 R104
EventPlan
P349 E106 12-Dec-2018 Cleanup E101
EXPERIMENT 9 – SQL JOINS (LEFT OUTER, RIGHT OUTER, FULL OUTER)

OBJECTIVE
Perform SQL outer join to retrieve data from multiple tables while including unmatched rows from one or both
of the joined tables.
THEORY
SQL Joins:
The purpose of a join concept is to combine data spread across tables. A join is actually performed by the
‘where’ clause which combines specified rows of tables.
Self Join: A self-join is joining a table to itself.
select EmpNo, Empfirstname + ' ' + Emplastname as 'Emp Name',

Supempno from Employee
Example: Write a query to display the employee and their Supvisor Name.
Output should like:

Emp Name Supervisor Name
select e1.Empfirstname + ' ' + e1.Emplastname as 'Emp Name',

e2.Empfirstname + ' ' + e2.Emplastname as 'Supervisor Name'
from Employee e1 inner join Employee e2 on e1.SupempNo=e2.EmpNo
Outer Join (Left, Right, Full): Outer join gives results by taking:
• Cartesian product
• filter (join predicate)
• get outer rows
Left Outer join

In a left outer join, all rows from the first table mentioned in the SQL query is selected, regardless whether there
is a matching row on the second table mentioned in the SQL query.
Example Query Left Outer Join

LEFT OUTER JOIN OrderTbl ON Customer.CustNo = orderTbl.CustNo;
Right outer join

In an left outer join, all rows from the second table mentioned in the SQL query is selected, regardless whether
there is a matching row on the first table mentioned in the SQL query.

Example Query Right Outer Join
RIGHT OUTER JOIN OrderTbl ON Customer.CustNo = orderTbl.CustNo;
Full Outer Join

This join combines left outer join and right outer join. It returns row from either table when the conditions are
met and returns null value when there is no match.
Example Query Full Outer Join

Full OUTER JOIN OrderTbl ON Customer.CustNo = orderTbl.CustNo;
Summary
A SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as
a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to
each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL
OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-
join.


Lab# 9 EXCERCISE(15 marks)
Problem 9.1: [7]

Write a query to output the name, city for each customer, even if there is no event requested.
Problem 9.2: [8]

Display the details of those customer who has same estimated cost.

EXPERIMENT 10 – NESTED QUERIES WITH JOINS
OBJECTIVE
Perform Nested queries in SQL combined with joins to achieve more complex and sophisticated data retrieval
and manipulation.
THEORY
An SQL Join statement is used to combine data or rows from two or more tables based on a common field
between them. A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE
statement, or inside another subquery. Joins and subqueries are both used to combine data from different tables
into a single result.
JOIN a table with a subquery
A subquery can be used with JOIN operation.
In the example below, the subquery actually returns a temporary table which is handled by database server in
memory. The temporary table from the subquery is given an alias so that we can refer to it in the outer select
statement.
Note that the left and right table of the join keyword must both return a common key that can be used for the
join. Also note that, using subquery in JOIN operation should generally be avoided if you can rewrite your
query in a different way, the reason being that no indexes can be used on a temporary table in memory.
Example :-
select y.CategoryID,
y.CategoryName,
round(x.actual_unit_price, 2) as "Actual Avg Unit Price",
round(y.planned_unit_price, 2) as "Would-Like Avg Unit Price"
from
(
select avg(a.UnitPrice) as actual_unit_price, c.CategoryID
from order_details as a
inner join products as b on b.ProductID = a.ProductID
inner join categories as c on b.CategoryID = c.CategoryID
group by c.CategoryID
) as x
inner join
(
select a.CategoryID, b.CategoryName, avg(a.UnitPrice) as planned_unit_price
from products as a
inner join categories as b on b.CategoryID = a.CategoryID
group by a.CategoryID
) as y on x.CategoryID = y.CategoryID

Subquery in FROM With a GROUP BY
First of these is a subquery in a FROM clause using a GROUP BY to calculate aggregate values.
Example :-
SELECT city, sum_price
FROM
(
SELECT city, SUM(price) AS sum_price FROM sale
GROUP BY city
) AS s
WHERE sum_price < 2100;
The subquery selects the cities and calculates the sum of the sale prices by city. The sum of all sale prices in
each city from the sale table is calculated by the aggregate function SUM(). Using the results of the subquery,
the outer query selects only the cities whose total sale price is less than 2,100 (WHERE sum_price < 2100).
You should remember from previous lessons how to use aliases for subqueries and how to select an aggregate
value in an outer query.
Summary
In this students learn subqueries with joins.


Lab# 10 EXCERCISE(15 marks)
Problem 10.1: [8]

write a SQL query to find those employees who receive a higher salary than the employee with ID E103 and
Activity is "operation".
Problem 10.2: [7]

write a SQL query to find out which customer have the same city as the customer whose ID is
C104 and Event state is approved.
Customer
EventRequest
EventPlan
P349 E106 12-Dec-2018 Cleanup E101
EXPERIMENT 11 – PROGRAMMABLE OBJECTS IN SQL
OBJECTIVE
Perform T-SQL that includes a set of programming constructs and extensions to the standard SQL language,
making it a powerful tool for working with these database systems.
THOERY
Variables
• Sql server provides two types of variables:

• Local and Global
• Local variable is instantiated with single @ sign while global variable is instantiated with double @@
sign.
• Variables are declared with DECLARE clause where you specify name and data type of variable.
• Either SET or SELECT is used to assign a value to a variable.
Begin
PRINT 'Welcome to '+CHAR(10)+'MCS, NUST';
PRINT CHAR(10);
PRINT 'Current date and time is ' +CONVERT(VARCHAR(20), GETDATE());
End
Example:
• Declare a variable:
DECLARE @per_marks double
DECLARE @min_range , @hi_range int
• Assign a value into a variable:
SET @min_range = 0, @hi_range = 100
SET @ per_marks = 70.53
• Assign a value into a variable in SQL statement:
SELECT @sal = salary FROM Employee WHERE EmpId = ’23091‘
Example:
Begin
DECLARE @sal int
SELECT @sal=salary FROM Employee WHERE EmpId=2312
print 'Your Salary is '+convert(varchar, @sal)
End
Control flow constructs (IF…ELSE)
Defines conditional and, optionally, alternate execution when a condition is false following is general syntax.
IF Boolean_expression
T-SQL_statement | block_of_statements
[ELSE
T-SQL_statement | block_of_statements ]
Example:
Begin
DECLARE @sal int
SELECT @sal=salary FROM Employee WHERE EmpId=2313
if (@sal>5000)
print 'Your Salary is greater then 5000'
else
print 'Your Salary is less then 5000'
End
Example:
Declare @var int
Set @var = 1
If @var = 1
Print ‘this is the code executed when true’
else
Print ’this is the code executed when false’
Repeat constructs (While loop)
Repeats a statement (or block) while a specific condition is true.
Example:
DECLARE @Counter INT
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter)
SET @Counter = @Counter + 1
END
Example:
DECLARE @intFlag INT
SET @intFlag=1
WHILE ( @intFlag <= 10)
BEGIN
PRINT 'The flag value is = ' + CONVERT(VARCHAR,@intflag)
SET @intFlag = @intFlag + 1
END
Example:
DECLARE @Counter INT , @MaxId INT, @EmpName NVARCHAR(100)
SELECT @Counter = min(Id) , @MaxId = max(Id) FROM EmpTable
WHILE(@Counter IS NOT NULL
AND @Counter <= @MaxId)
BEGIN
SELECT @CountryName = EmpName
FROM EmpTable WHERE Id = @Counter
PRINT CONVERT(VARCHAR,@Counter) + '. employee name is ' + @EmpName
SET @Counter = @Counter + 1
END
Summary
Modularized program development using conditions and loop structures.

http://dba.fyicenter.com/
1000912_Conditional_Statements_and_Loops_in_SQL_Server_in_SQL_Server_Transact-SQL.html
Problem 11.1 [5]

Write a block of T-SQL code to show all events in following format.
The event of C101 is Approved and to be held on ’03-Dec-2018’
Problem 11.2 [5]

Write a block of T-SQL code to show all events, which are registered in July as shown format.
E100 of C100 is register on 06-Jun-2018 and to be held in ’Oct-2018’
Problem 11.3 [5]

Write a block of T-SQL code to check the event is pending or not
EventRequest
EXPERIMENT 12 – STORED PROCEDURES
OBJECTIVE
Construct SQL procedures to write SQL code once and reuse it multiple times in various parts of an application
or across different applications.
THOERY
Stored Procedures
A procedure is a block that can take parameters (sometimes referred to as arguments) and be invoked.
Procedures promote reusability and maintainability. Once validated, they can be used in number of applications.
If the definition changes, only the procedure are affected, this greatly simplifies maintenance.
Modularized program development:
• Group logically related statements within blocks.

• Nest sub-blocks inside larger blocks to build powerful programs.
• Break down a complex problem into a set of manageable well defined logical modules and implement
the modules with blocks.
Pre-compiled execution plan:
Sql server compiles each stored procedure once and then store compile and execution plans in query cache and
reuse for subsequent executions. so you get tremendous performance boost when stored procedures are called
repeatedly.
Abstraction:
Stored procedures provide an abstraction layer that shields applications from the underlying database structure.
Syntax for procedure:

Create [or Replace] PROCEDURE procedur_name
(parameter1 [model1] datatype1,
(parameter2 [model2] datatype2,
…)
AS
PL/SQL Block;
Example:
Create PROCEDURE leave_emp

(@v_id int)
AS
BEGIN
DELETE FROm emp
WHERE empno=@v_id;
END
Parameters
• Parameters are local variables that are used to pass values into a stored procedure when it is executed.
• U can create two types of parameters:
• Input and output
• Output parameters are used when u need to return a single value to an application.
Input parameters example
Output parameter example
create procedure getmanagerID

@employeeID int, @managerID int output
AS
begin
select Manager
from Employee
where Empno = @employeeID
end
How to call procedure with output parameters
declare @var int
execute getmanagerID 12 , @managerID= @var output
Practice
1: Write and execute a procedure (Print_Emp) to print all employee of computer with their salary.
2: Write and execute a procedure (Print_Dept) to print department wise no of employee and sum of their salary.
3: Write and execute a procedure (Print_Employee_Info) to print the complete details of a single employee
whose empno pass as parameter.
4: Write and execute a procedure (Increase_Sal) to 25% increase in the salary of all employees.
5: Write and execute a procedure (Add_Emp) to insert newly hired employee all info passed as parameters. This
procedure observe the following rules.
 Checks the empno pass as parameter is not exist
 Checks the deptno exits in department table.
 Checks the salary of employee must be >= 5000
Summary
A procedure is a block that can take parameters (sometimes referred to as arguments) and be invoked.
Procedures promote reusability and maintainability. Once validated, they can be used in number of applications.
If the definition changes, only the procedure are affected, this greatly simplifies maintenance.
Modularized program development:
· Group logically related statements within blocks.
· Nest sub-blocks inside larger blocks to build powerful programs.
· Break down a complex problem into a set of manageable well defined logical modules and implement the
modules with blocks.

Problem 12.1 [5]

Write a procedure to increase 15% salary of employees whose salaries is greater than 5000 and who belongs to
the deptname passed as an argument.
Problem 12.2 [5]

Write a procedure to find the phone number, name, address of the customer whose custno passed as an
argument and display as proper message does not exits.
Problem 12.3 [5]

Write a procedure to list all events planed by customer belongs to perticular state.
Customer
Employee
empno empname department email Salary phone
E100 Chuck Coordinator Administration [email protected] 5000 3-1111
E101 Mary Manager IT [email protected] 6000 5-1111
E102 Sally Supervisor Planning [email protected] 7000 3-2222
E103 Alan Administrator Administration [email protected] 5500 3-3333
EXPERIMENT 13 – TRIGGERS
OBJECTIVE
Construct SQL Triggers to enforce Data integrity constraints in relational database systems.
THEORY
A trigger is a PL/SQL block or a PL/SQL procedure that executes implicitly whenever a particular event takes
place. It can either be:
1. Application trigger: Fires whenever an event occurs with a particular application.
2. Database Trigger: Fires whenever a data event (such as DML) occurs on a schema or database.
• Triggers are special type of stored procedures.

• Automatically executes when DDL and DML statements associated with triggers executes.
DDL Triggers
• Create
• Alter
• Drop
• For/After (create/alter/drop)
• AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL
statement have executed successfully. All referential cascade actions and constraint checks also must
succeed before this trigger fires.
• AFTER is the default when FOR is the only keyword specified.
DML Trigger
• Insert
• Update
• Delete
• For/After ( insert, update, delete)
• Instead of ( insert, update, delete)
• Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding
the actions of the triggering statements. INSTEAD OF cannot be specified for DDL or logon triggers
and DML.
• When a trigger is executed, two special tables inserted and deleted are available.
• These tables help to maintain the record of updates.
PROGRAMS
Example of DDL Trigger
Example of DML trigger

• Create a DML trigger on student table to maintain the record of updates. Maintain a separate table to re-
cord the inserted and deleted information.
Summary
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events
are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired,
regardless of whether or not any table rows are affected. For more information, see DML Triggers.
DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily
correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform
DDL-like operations. Logon triggers fire in response to the LOGON event that is raised when a user sessions is being
established. Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are
created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server.
SQL Server allows for creating multiple triggers for any specific statement.

Problem 13.1 [7]

Write a TRIGGER to ensure that Customer Table does not contain duplicate or null values in phone column.
Problem 13.2 [8]

Write a Trigger to carry out the following action: on deleting any records from the employee table, the same
values must be inserted into the employee_log table.
Customer
Employee
empno empname department email Salary phone
E100 Chuck Coordinator Administration [email protected] 5000 3-1111
E101 Mary Manager IT [email protected] 6000 5-1111
E102 Sally Supervisor Planning [email protected] 7000 3-2222
E103 Alan Administrator Administration [email protected] 5500 3-3333
EXPERIMENT 14 – APPLICATION DEVELOPMENT WITH SQL + FRONT END TOOL (JAVA)
OBJECTIVE
Develop the secure databse connection with Java to enable Java applications to interact with databases
effectively.
Tools
 Netbeans (Front End)
 Sql server (Back End)
THEORY
Steps
1. In sql server management studio login using
Server Name: DESKTOP-F2DHUN6
User name : sa
Password: 123
2. Now select database (Practice25A30Nov) and create new query for table as shown figure
3. Find out jre version from Netbeans>tools>java platforms

4. Download jdbc driver from https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-
sql-server-support-matrix?view=sql-server-ver15 according to your platform. I am downloading and
using JDBC Driver Version 6.2.
5. Open Netbeans>Services>Databases>New Connection.
6. Select “New Driver …” from Driver list.

7. Add downloaded JDBC jar file and click and click on open. Then click OK.
8. The main Wizard screen shown and click on Next.

9. Now in NetBeans, Provide the connection information shown in figure like (username, password,
database, server name and port)
Server Name: DESKTOP-F2DHUN6
Database:
User name : sa
Password: 123
Note:-Copy jdbc url (jdbc:sqlserver://DESKTOP-F2DHUN6:1433;databaseName=Practice25A30Nov),

which is used in java code to connect database.
10. Click Next

11. Finish
12. From explorer right click on connection and select properties > click on view connection properties
13. Create new project in Netbeans.
14. Next Write project name “DBConn” and finish wizard.
15. Now add Jar files to it.

16. Select jar files
17. Now add this code to your project. add username (sa) and password (123) to jdbcurl and pass it in
getconnection method
package dbconn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
*
* @author kabeer
*/
public class DBConn {
public static void main(String[] args) {
try {
Connection connection = DriverManager.getConnection("jdbc:sqlserver://DESKTOP-
F2DHUN6:1433;databaseName=Practice25A30Nov;userName=sa;password=123");
PreparedStatement statement = connection.prepareStatement("select * from user_info");
ResultSet result = statement.executeQuery();
while (result.next()) {
System.out.print(result.getString("id")+" ");
System.out.print(result.getString("name")+" ");
System.out.println(result.getString("city")+" ");
}
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
18. Final Run the Program to display the data from table.
1. Run the program

Lab# 14 EXCERCISE ( 15 MARKS)
Problem 14.1 [8]

Create a java application to display the records of customer and employee tables.
Problem 14.2 [7]

Create a java application to insert a record into customer tables.
Customer
Employee
EXPERIMENT 15– OPEN ENDED LAB (DATABASE SYSTEM)
Back Ground
Diversity in age has emerged as a strategic advantage that can provide organizations with a competitive edge. The
workforce of many organizations exhibits a rich spectrum of ages, making it one of the most diverse demographic
factors. It is widely acknowledged that organizational culture, human resource practices, the nature of work, and
the unique business strategies of individual firms set them apart from one another. These distinctions are likely to
influence the way various variables impact employee performance, with variations between firms.
In this laboratory experiment, students are tasked with exploring the correlation between employees' ages and
organizational performance, shedding light on the multifaceted relationship between these factors.
TASKS
Task 1: Data Retrieval Write an SQL query to retrieve the names and ages of all employees from the
"Employee" table in a database. Organize the result in ascending order of age.
Task 2: Age Group Analysis Extend the previous query to categorize employees into age groups (e.g., "Under
30," "30-40," "Over 40"). Calculate and display the count of employees in each age group.
Task 3: Organizational Performance Analysis Create an SQL query to retrieve data from an
"OrganizationalPerformance" table that contains performance metrics for different companies. Join this table with
the "Employee" table using a relevant key and filter the results for a specific company. Calculate the average
performance score for employees in different age groups within that company.
Task 4: Cross-Company Comparison Compare the average performance scores of employees in two different
companies from the "OrganizationalPerformance" table. Calculate and display the difference in average
performance scores between these two companies for each age group of employees.
Deliverables
1. Create a lab report detailing the SQL queries used to perform the four tasks.
2. Provide the result sets showing the outputs of each task.
3. Present your findings and analysis on how performance scores vary across companies and age groups.
Note: Each deliverable should be accompanied by clear explanations, SQL code, result sets, and any relevant
observations or insights. Students should ensure that their reports are well-organized and effectively communicate
their findings from the SQL tasks.
“I hear and I forget,
I see and I remember,
I do and I understand”
Confuciu
s

Database Systems Lab Manual - Updated Sep 2023

Uploaded by

Copyright:

Available Formats

Database Systems Lab Manual - Updated Sep 2023

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Systems Lab Manual - Updated Sep 2023

Uploaded by

Copyright:

Available Formats

COMPUTER LABORATORY MANUAL

DEPARTMENT OF COMPUTER SOFTWARE ENGINEERING

Criteria Unacceptable Substandard Adequate Proficient

Criteria Unacceptable Substandard Adequate Proficient

Criteria Unacceptable Substandard Adequate Proficient

Criteria Unacceptable Substandard Adequate Proficient

Mapping of CLOs to Program Learning Outcomes

7 CS 220 Database System Lab Manual

S No List of Experiments CLO

8 CS 220 Database System Lab Manual

EXPERIMENT 1 – ENTITY RELATIONSHIP DIAGRAMS (ERD)................................................................................17

9 CS 220 Database System Lab Manual

Student Management System: Requirements Specifications

The detailed requirements specifications are given below:

Main Entities and Relationships:

10 CS 220 Database System Lab Manual

Entity Relationship Diagram:

11 CS 220 Database System Lab Manual

12 CS 220 Database System Lab Manual

CREATE TABLE Student

CREATE TABLE Course

CREATE TABLE Faculty

CREATE TABLE Enrollment

CREATE TABLE AcademicRecord

CREATE TABLE CoursePrerequisites

15 CS 220 Database System Lab Manual

Symbols Used in ER Model

16 CS 220 Database System

Relationships: are named associations among entity types.

Cardinality can be classified into various types, namely:

17 CS 220 Database System

Self-Referencing Relationship: A self-referencing relationship involves connections among members of the

18 CS 220 Database System

19 CS 220 Database System

Practice Problems for Experiment 1:-

20 CS 220 Database System

21 CS 220 Database System

Web Resources for Additional Studies

22 CS 220 Database System

Important Note: - Copied assignment will get zero Marks.

23 CS 220 Database System

24 CS 220 Database System

25 CS 220 Database System

26 CS 220 Database System

27 CS 220 Database System

Web Resources for Additional Studies

28 CS 220 Database System

Problem 2.2: [10]

29 CS 220 Database System

Structured Query Language

3. Date Datatype: used to store date and time in the table.

30 CS 220 Database System

Several aspects of SQL:

Data Definition Language (DDL) commands in RDBMS

The commands used are:

Syntax: Create table tablename (column_name1 data_ type constraints,

CREATE TABLE Employee

31 CS 220 Database System

Syntax: alter table tablename add/modify (attribute datatype(size));