CSC351_Lab_Manual_Database Systems

DATABASE SYSTEMS
Course Code CSC 352
Laboratory Manual
Department of Computer Science

Lahore Garrison University
Main Campus, Sector-C Phase-VI, DHA Lahore
Guidelines for Laboratory Procedure
The laboratory manual is the record of all work on your experiments. A complete, neat, and organized data
record is as important as the experiment itself. Please follow these guidelines for efficient performance in th
laboratory:
Attend the lab orientation to familiarize yourself with the lab setup.
Follow the designated lab schedule and complete assignments on time.
Write clear and well-documented code, avoiding plagiarism and unauthorized collaboration.
Seek help from lab instructors or peers if you encounter difficulties with programming concepts.
Regularly back up your code and project files to prevent data loss.
Use lab resources responsibly, including computers and software licenses.
If collaboration is allowed, work effectively with peers, ensuring each member contributes meaningfully.
Maintain a clean and organized workspace for better focus and efficiency.
Thoroughly test your code to identify and fix errors before submission.
1. Engage in lab discussions, share insights, and actively participate to enhance the learning experience.
Safety Precautions
1. Handle equipment carefully to prevent damage and avoid placing liquids near electronic devices.
2. Maintain an ergonomic workspace for comfortable and strain-free programming.
3. Save work frequently and use surge protectors to prevent data loss due to power issues.
4. Keep software and antivirus programs up to date and avoid downloading from untrusted sources.
5. Regularly back up code and important files to prevent data loss.
6. Establish clear communication and collaboration guidelines when working with others.
7. Be aware of emergency exits, fire extinguisher locations, and evacuation procedures.

Safety Undertaking
I have read all of the above, and I agree to conform to its

contents.
Name:
Registration No.:
Student Signature:
Date:
Lab Instructor:
Grading Policy
Lab Performance 15%
Lab Report 15%
Lab Project +Viva 20%+10%
Final Lab Exam 40%
Rubrics
Lab Performance (Continuous Assessment) / Performance Test
Sr.No. Performance Exemplary Satisfactory Unsatisfactory

Indicator (4-5) (2-3) (0-1)
1 Fully understand the Has very good Has poor

software including its understanding of understanding of
Ability to Conduct features, working and the software the software
Practical quite able to conduct including its including its
the entire practical features, working features, working
with negligible help and able to and unable to
from lab instructor conduct practical conduct practical
with some help on his own;
from lab
instructor
2 Always analyzes and Analyzes and Analyzes and
Data Analysis & interprets data interprets data interprets data
Interpretation correctly and always correctly most of incorrectly
display correct output; the time; most of most of the
always compares the output are time; many
theory against correct; output are
practical results and compares theory incorrect;
resolve related error. against practical most of the
results and time never
resolve related attempts to
error most of the compare
time. theory against
practical
results.
Lab Reports
Sr. Performance Exemplary (4-5) Satisfactory (2-3) Unsatisfactory

Indicator (0-1)
1 All the code is Most of the Some of or complete
Structure very accurate and code is very code is inaccurate.
precise. accurate and Somewhat or no logical
Completely precise. Quite and systematic
logical and logical and compilation.
systematic systematic
compilation. compilation.
2 Efficiency The code is fairly The code The code is huge and
efficient without is brute appears to be patched
force and
sacrificing unnecess together.
readability and arily long.
understanding.
Viva Voce
Sr Performance Exemplary Satisfactory (2- Unsatisfactory

Indicator (4-5) 3) (0-1)
1 Responds well, Generally Non-responsive.

Responsiveness quick and very Responsive and
to Questions/ accurate all the accurate most of
Accuracy time. the times.
2 Level of Demonstration of At ease with No grasp of information.
understanding of full knowledge of content and able Clearly no knowledge of
the learned skill the subject with to elaborate and subject matter. No
explanations and explain to some questions are answered.
elaboration. degree. No interpretation made.
Lab Project
Sr.No. Performance Exemplary Satisfactory (2-3) Unsatisfactory

(4-5) (0-1)
Project Design
1 Implementation Project is Project is Project is completed but
and Completion completed without completed with not working properly. Or
any external quite less technical Project is not
assistance and is assistance from completed.
working properly. the instructor or
others in order to
complete the
project and is
working properly.
2 Appearance and Circuit wiring and Circuit wiring and Circuit wiring and
Problem components are components are components are
Analysis perfectly organized organized and disorganized but some
and proper some prototyping prototyping is done.
prototyping is is done.
done.
Project Report
1 Structure and Information is
Information is
Literature Review presented in a
presented in a
less logical way, Information is not
logical,
which is little presented in a logical,
interesting way,
difficult to interesting way, which
which is easy to
follow. All is so difficult to follow.
follow. All
sections are in a All sections are
sections are in a
little incorrect incorrect order or not
correct order and
order or submitted on a time.
submitted on a
submitted little Collected a poor
time. Collected a
late time. information--all
great deal of
Collected a fine doesn’t relates to the
information--all
information—all topic.
relates to the
may relates to
topic.
the topic.
2 Result and Clearly discusses Generally clear
Presentation what results discussion of
Limited discussion
mean results and
of results and
and what conclusions, but
conclusions. Little
conclusions may may miss some
or no reference to
be points. Some use
published standards
drawn from them. of references and
or other reports.
Cites published published
standards or standards.
other
related reports.
Project Viva
Responds well, Generally Non-responsive.
Responsiveness to quick and very Responsive and
1
Questions/ accurate all the accurate most of
Accuracy time. the times.
Demonstration of At ease with No grasp of
full knowledge of content and able information. Clearly no
Level of
the subject with to elaborate and knowledge of subject
2 understanding of
explanations and explain to some matter. No questions
the learned skill
elaboration. degree. are answered. No
interpretation made.
Project Presentation
Presentation is
Presentation is
clear Listener can follow
generally clear.
and logical. presentation with
A
1 Organization Listener can effort.
few minor points
easily Organization not
may be
follow line of well thought out.
confusing
reasoning
Is very confident
and explains the
details properly. Is confident to Has low confidence to
Proper eye some extent explain and deliver
contact is with quite less topic properly. Less
2 Confident
maintained all eye contact and eye contact and
the time with presentational presentational
proper gestures. gestures used.
presentational
gestures.
Responds well to
Reluctantly interacts
questions. Generally
Responsiveness to with audience.
3 Restates responsive to
Audience Responds poorly to
and summarizes questions.
questions.
when needed.
Team Work
Relays very little
Share Relays some basic
1 Relays a great deal information--some
Information information--most
of relates to the topic
information--all relates
relates to to the topic.
the topic.
Performs all duties
Performs nearly
Fulfill Team of Performs very little
2 all
duties assigned team duties.
duties
role
Lab’s Course Learning Outcomes
Course Title: Database Systems

Course Code: CSC-352
Instructor: Ms. Farwa Javed
Designation: Lecturer
E-mail:farwajaved@lgu.edu.pk
Students will be able to:
To provide a classroom and laboratory environment that enables students to become proficient C++ programmers.
• Students are not assumed to have a background in computer programming and therefore introductory
material on software engineering and program development will be presented.
• However, most of the course will be on the specifics of the C++ language. Students completing this course
will be able to compile various programs in text-user-interface computer language.
• Students can write the basic structure of C++ programs, perform programming experiments in C++
language, and develop techniques to solve problems.
CLO1: Formulate SQL query to access data within the relational databases.
CLO2: Use structured query language (SQL) to manage databases systems and extracting data efficiently.
Mapping of Course Learning Outcomes (CLO) to Program Learning Outcomes (PLO) / Graduate
Attributes
Course
CLOs/ PLO 1 PLO
Code PLO2 PLO3 PLO4 PLO5 PLO6 PLO7 PLO8 PLO9
10
PLOs
CLO 1
X
CSC-
351 CLO2
X
PLO1: Academic Education
PLO2: Knowledge for Solving Computing Problems PLO3:
Problem Analysis
PLO4: Design/ Development of Solution PLO5:
Modern Tool Usage
PLO6: Individual and Teamwork
PLO7: Communication
PLO8: Computing Professionalism and Society PLO9:
Ethics
PLO10: Lifelong Learning
Sr.No List of practical CLO’s
1 Introduction of Database Systems and its Practical Use 1

Installation of SQL server and SSMS.
2 Create Database, Table in SQL, DDL, DML and DCL commands 1
3 Select, From, Where all related Queries in SQL, Distinct and Order by clause 1
4 Arithmetic Operators in SQL., Logical and Relational Operators In SQL. 1
5 Group functions, Having clause, Column and Table Alias. 1
6 Commands to Alter Table, Add or Drop a Column, update data 1
7 Use queries Delete Table and a Row, Truncate a Table in SQL. 1
8 JOINS in SQL Server 1
9 Subqueries 2
10 Single Row functions in SQL 2
11 Stored Procedure and Views 2
12 Introduction to Triggers and its type 2
13 Views and its types 2
14 CRUD operations using C# language with SQL server 2

PRACTICAL NO.01
Introduction of Database Systems and its Practical Use
Installation of SQL server and SSMS.
PLO CLO LL
5 1 P2
Objectives:
The objectives of this first experiment are:
• Installation of SQL server and SSMS.

• Create a new database named Northwind.
• Restore Schema using scripts Northwind. SQL.
• Restore schema data using northwind_data.sql.
Activity:
1. System Requirements for SQL Server 2019
1.1. Operating system
Windows 10 TH1 1507 or greater and Windows Server 2016 or greater
1.2. .NET Framework
SQL Server 2016 (13.x) RC1 and later require .NET Framework 4.6 for the Database
Engine, Master Data Services, or Replication. SQL Server setup automatically installs
.NET Framework. You can also manually install .NET Framework from Microsoft .NET
Framework 4.6 (Web Installer) for Windows.
1.3. Disc Space
SQL Server requires a minimum of 6 GB of available hard-disk space.
1.4. Memory
At least 4 GB and should be increased as database size increases to ensure optimal

performance.
1.5. Processor
• Minimum: x64 Processor: 1.4 GHz, Recommended: 2.0 GHz or faster
• x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon

with Intel EM64T support, Intel Pentium IV with EM64T support
1.6. Monitor
SQL Server requires Super-VGA (800x600) or higher resolution monitor.
2. Installation guide
Important Note:
2.1. connect your system with internet and download the developer edition
using this given link: https://www.microsoft.com/en-us/sql-server/sql-server-
downloads
2.2. Login on window with as Administrator
1:
2.3. Once downloaded then click on the .exe file and run.
2.4. Then click on Basic version to install.
Figure2: Selection of basic version

2.5. Scroll down and then click on accept.
Figure3: Terms and conditions for installing SQL Server
2.6. Click on Install and eat pop corns until installation is done.
2.7. After installation is done click on install SSMS
Figure5:
2.8. Click on highlighted link. It will download .exe file of SSMS. After
downloading run .exe file.
Figure6: Version of SSMSfor installation
2.9. Click on Install and wait until installation is done successfully.
Figure 7: Installation of SSMS

3. Generate Scripts of a schema
• Step 1
Right click on your database and select Task -> generate script.
Figure 8: Database_Scripts_With_Data_Select_Option
Database system Lab
Step 2
Click next in the introduction screen
Figure 9: Database_Scripts_With_Data_Introduction
DATABASE MANAGEMENT SYSTEMS LAB 21

Database system Lab
• Step 3
Select the database object which you are all you need and then click next.
Figure 10: Selecting database objects

Database system Lab
Step 4
Now you will be shown a window which asks you about how your script should be published.
Figure 11: Database_Scripts_With_Data_Publish_Options
Click on advanced in that window.

Database system Lab
Step 5
Select ‘Schema and data’ from type of data to script option and then click OK.
Figure 12: Database_Scripts_With_Data_Advanced Click on Next.
Step 6
Page 1 of 2
Database system Lab
Click finish, now check the script file, it must be having the insert queries too.
Figure 13: Database_Scripts_With_Data_Finish
Now what else is pending, go ahead and run your script.
Lab Tasks
1. Create a new database named TestDB_2022_CS
2. Create a new table named student using some attributes
3. Generate Scripts of your database
4. Restore your schema to another machine
5. Generate scripts of data
6. Restore data to other system as well
CONCLUSION:
Page 2 of 2
Database system Lab
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:
Performance Lab Report
Description Total Marks Description Total Marks

Marks Obtained Marks Obtained
Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Total Marks obtained Total Marks Obtained
Instructor Signature
Page 3 of 2
Database system Lab
PRACTICAL NO.02
DML, DDL, DCL commands
PLO CLO LL
5 1 P2
Objectives:
This lab provides an
To create database and table using SQL, Briefing various commands and their use
Structured query language is mainly divided into 5 sub language.
SQL Data
types
Datatypes define what type of data a column can hold
Page 4 of 2
Database system Lab
Numeric data types store all numerical values or integer values.
Constraints in SQL
What is Data Integrity

Data integrity ensures that the data which is going to be entered in the database is reliable, accurate and
consistent. Data integrity maintains the correctness and completeness of data.
What is Constraints?
To maintain the data integrity, we use the constraints. Constraints are the rules which we applied on the
table’s column to ensure the data integrity.
We can add constraints on column while creating the table and can also add after creating the table.
Constraints are used to specify rules for data in a table.
The following constraints are commonly used in Sql:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly
Page 5 of 2
Database system Lab
Create Database:
Syntax:
Create database database_ name;
Create Table:
Once a database is made it is easy to add tables in it, a table in SQL is like
Syntax:
CREATE TABLE table_name (
Column_1 data_type Identity (1 1) PRIMARY KEY,
Column_2 data_type NOT NULL,
Column_3 data_type NULL
Column_4 data_type
...,
);
INSERT:
Using this command we can Insert the records into the existing table
We can insert the records into the table in two methods
1. Explicit method
2. Implicit method
Explicit method
In this method user has to enter all the values into all the columns without anything omitting (or) left any
column data
Syntax:
INSERT INTO <TABLE NAME> VALUES <VAL1, VAL2,….VALN>;
Page 6 of 2
Database system Lab
(OR)
INSERT <TABLE NAME> VALUES <VAL1, VAL2, .VALN>;
(Here “INTO” Keyword is optional)
Ex1: INSERT INTO EMP VALUES (101,‟RAJ‟,9500);
Ex2: INSERT EMP VALUES (101,‟RAJ‟,9500);
Implicit Method:
In this method we can enter the values into the required columns in the table, so that user can omit (or) left
some columns data while he enters the records into the table
If the user omit any column data in the table then it automatically takes NULL
Syntax: INSERT INTO <TABLE NAME> (COL1, COL2….COLN) VALUES (VAL1, VAL2… VALN);
Ex: INSERT INTO EMP (EID, SAL) VALUES (106,9999)
Lab Tasks:
1. Create a new database named Lab2
2. Create table with following schema Student (Registration Number: String, Name: String,
Department: String, Session: Number, CGPA: Number, Address: String)
3. Insert 10 records using both insert methods.
Page 7 of 2
Database system Lab
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Page 8 of 2
Database system Lab
PRACTICAL NO.03
Basic queries of SQL (select, where, distinct, order by)
PLO CLO LL
Objectives: 5 1 P2
Understand use of basic queries in SQL Select, From, Where , Distint ,
Order By all related Queries in SQL
The SQL SELECT Statement
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
SELECT Syntax
SELECT column1, column2, ...

FROM table_name;
Here, column1, column2, are the field names of the table you want to select data from. If you want to select all
the fields available in the table, use the following syntax:
SELECT * FROM table_name;
SELECT Column Example
The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:
Example
SELECT CustomerName, City FROM Customers;
SELECT * Example
The following SQL statement selects all the columns from the "Customers" table:
Example
SELECT * FROM Customers;
The SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
Page 9 of 2
Database system Lab
FROM table_name
WHERE condition;
WHERE Clause Example
The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:
Example
SELECT * FROM Customers

WHERE Country='Mexico';
Example
SQL requires single quotes around text values (most database systems will also allow double quotes).
However, numeric fields should not be enclosed in quotes:
Example

WHERE CustomerID=1;
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the
different (distinct) values.
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ...

FROM table_name;
The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending
order, use the DESC keyword.
ORDER BY Syntax
SELECT column1, column2, ... FROM table_name
Page 10 of
2
Database system Lab
ORDER BY column1, column2, ... ASC|DESC;
ORDER BY Example
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country"
column:
Example
SELECT * FROM Customers ORDER BY Country;
Lab Task
Task 01: Perform various queries on the inserted and saved data.
Task 02: Check output and practice by changing data.
Task 03: Perform various queries on the inserted and saved data.
Page 11 of
2
Database system Lab
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Page 12 of
2
Database system Lab
PRACTICAL NO.04
Operators in SQL
PLO CLO LL
3 1 P2
Objectives:
1. Use of Logical and Relational Operators in SQL.
SQL Comparison Operators
Operator Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
Page 13 of
2
Database system Lab
SQL Logical Operators
Operator Description
AND TRUE if all the conditions separated by AND is

TRUE
BETWEEN TRUE if the operand is within the range of

Comparisons
IN TRUE if the operand is equal to one of a list of

Expressions
LIKE TRUE if the operand matches a pattern
NOT Displays a record if the condition(s) is NOT

TRUE
OR TRUE if any of the conditions separated by OR

is TRUE
1. SELECT * FROM Customers
WHERE City = "London" AND Country = "UK";
2. SELECT * FROM Customers

WHERE City NOT LIKE 's%';
Page 14 of
2
Database system Lab
3. SELECT * FROM Products
WHERE Price > 30;
4. SELECT * FROM Products
WHERE Price <= 30;
The SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
BETWEEN Example
The following SQL statement selects all products with a price BETWEEN 10 and 20:
Example
SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;
The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
IN Syntax
FROM table_name
WHERE column_name IN (value1, value2, ...);
or:
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Page 15 of
2
Database system Lab
IN Operator Examples
The following SQL statement selects all customers that are located in "Germany", "France" and "UK":
Example

WHERE Country IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":
Example

WHERE Country NOT IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are from the same countries as the suppliers:
Example

WHERE Country IN (SELECT Country FROM Suppliers);
The SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards used in conjunction with the LIKE operator:
• % - The percent sign represents zero, one, or multiple characters

• _ - The underscore represents a single character
LIKE Syntax
FROM table_name
WHERE columnN LIKE pattern;
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
Page 16 of
2
Database system Lab
LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that starts with "a"
WHERE CustomerName LIKE '%a' Finds any values that ends with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in
any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the
second position
WHERE CustomerName LIKE 'a_%_%' Finds any values that starts with "a"
and are at least 3 characters in
length
WHERE ContactName LIKE 'a%o' Finds any values that starts with "a"
and ends with "o"
The following SQL statement selects all customers with a CustomerName ending with "a":
Example

WHERE CustomerName LIKE '%a';
Page 17 of
2
Database system Lab
LAB TASK:
Task 01: Perform various operations on the fields on the inserted and saved data.
Task 02: Check output and practice by changing data.
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Database system Lab
PRACTICAL NO.05
Group, having
PLO CLO LL
3 1 P2
Objectives: The objective of this exercise is to get you to
• Understanding of Group functions, Having clause, Column and Table Alias.

Helping Material:
1. AGGREGATE FUNCTIONS:
Aggregate functions allow you to perform a calculation on a set of values to return a single scalar value. The
most common aggregate functions are:
▪ AVG
▪ MIN
▪ SUM
▪ COUNT
▪ STDEV
▪ STDEVP
▪ VAR
▪ VARP
▪ MAX
Note: All the Group function except COUNT ignore null values
1. THE GROUP BY CLAUSE
The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into
groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER
BY clause.
For example:
SELECT SupplierID, AVG(UnitPrice) FROM Products
GROUP BY SupplierID
Database system Lab
3. THE HAVING CLAUSE
The WHERE clause is a row filter, the HAVING clause is a group filter. Only groups for which the HAVING
predicate evaluates to TRUE are returned by the HAVING phase to the next logical query processing phase.
Groups for which the predicate evaluates to FALSE or UNKNOWN are discarded. Because the HAVING clause
is processed after the rows have been grouped, you can refer to aggregate functions in the logical expression.
For example, in the query below, the HAVING clause has the logical expression COUNT(*) > 1, meaning that
the HAVING phase filters only groups (Title) with more than one row. The following fragment of query will
help to retrieve required data:
SELECT Title, COUNT(*)
FROM Employees GROUP BY Title HAVING COUNT(*)
>1
Aliases provide database administrators, as well as other database users, with the ability to reduce the
amount ofcode required for a query, and to make queries simpler to understand. In addition, aliasing can be
used as an obfuscation technique to protect the real names of database fields.
1.1.ALIASING COLUMNS
SELECT column_name AS alias_name FROM table_name;
1.2.ALIASING TABLES
SELECT column_name(s) FROM table_name AS alias_name;
2. FOOD FOR BRAIN

Here is some extra food for your brain which can help in further assignments:
• YEAR function is used to extract year from a date
• MONTH function is used to extract year from a date
• DAY function is used to extract day from a date.
Lab Tasks:
 Perform all the group function on Northwind Schema.

 Perform all the group function using HAVING clause on Northwind Schema
 Apply aliasing syntax on arbitrary column on Northwind Schema.
 Bonus: Find an alternate method to alias tables and columns in SQL Server.
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
Database system Lab
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Database system Lab
PRACTICAL NO.06
Command to Alter, add and drop
PLO CLO LL
3 1 P2
Objectives: The objective of this exercise is to get you to know
• Use of Commands to Alter Table, Add or Drop a Column
SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing
table.
ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name

ADD column_name datatype;
ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following syntax (notice that some database systems
don't allow deleting a column):

DROP COLUMN column_name;
ALTER TABLE - ALTER/MODIFY COLUMN
To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access:

ALTER COLUMN column_name datatype;
Database system Lab
SQL ALTER TABLE Example
Look at the "Persons" table:
ID LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to add a column named "DateOfBirth" in the "Persons" table. We use the following SQL
statement:
ALTER TABLE Persons ADD DateOfBirth date;
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type
specifies what type of data the column can hold. For a complete reference of all the data types available
in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.
The "Persons" table will now look like this:
ID LastName FirstNam Address City DateOfBirth

e
1 Hansen Ola Timoteiv Sandnes

n 10
Database system Lab
2 Svendson Tove Borgvn Sandnes

23
3 Pettersen Kari Storgt 20 Stavange

r
Change Data Type Example
Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or four-
digit format.
DROP COLUMN Example
Next, we want to delete the column named "DateOfBirth" in the "Persons" table. We use the following
SQL statement:
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
The "Persons" table will now look like this:
ID LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Database system Lab
LAB TASK:
Task 01: Alter the name of your already added field
Task 02: Drop 2 columns
Task 03: Add 1 column and check the output

CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
______________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
Data Analysis & 5 5
Efficiency
Interpretation
Database system Lab
PRACTICAL NO.07
Delete and truncate table.
PLO CLO LL
5 1 P2
Objectives: The objective of this exercise is to get you to
• Use queries Delete Table and a Row, Truncate a Table in SQL.

The SQL DELETE Statement
The DELETE statement is used to delete existing records in a table. DELETE Syntax
DELETE FROM table_name
WHERE condition;
SQL DELETE Example
The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:
Example
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';
Delete All Records
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and
indexes will be intact:
DELETE FROM table_name; or:

DELETE * FROM table_name;
In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table
for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a
number of integrity enforcing mechanisms.
Task 01: Create a new table, add data in it and then apply these queries.
Database system Lab
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Database system Lab
PRACTICAL NO.08
Joins in SQL server
PLO CLO LL
5 1 P2
Objectives:
• Understanding of JOINS in SQL Server.
1. JOINS In SQL Server:

A JOIN clause is used to combine rows from two or more tables, based on a related column between themSQL server provides
the following joins:
• Cross Join
• Natural Join
• Equi Join
• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
2. JOINS SYNTAX:
SELECT ColumnName(s) FROM Table_1,Table_2 OR
SELECT ColumnName(s)
FROM Table_1 CROSS JOIN Table_2
FROM table1 INNER JOIN/ CROSS JOIN/ NATURAL JOIN/ LEFT JOIN/ RIGHT JOIN/ FULL OUTER
JOIN table2
ON table1.column_name = table2.column_name;
Lab Tasks + Home Tasks:

• Perform all JOIN queries on any table using Northwind Schema.
• Perform self-cross join and see if there is any difference between cross join and self cross join.
• SELECT Customers.custid, Customers.companyname, Orders.orderid, Orders.orderdate FROM
Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON Customers.custid = Orders.custid;
Advanced Tasks:
• Return customers and their orders, including customers who placed no orders (CustomerID, OrderID, OrderDate)
• Report only those customer IDs who never placed any order. (CustomerID, OrderID, OrderDate)
• Report those customers who placed orders on July,1997. (CustomerID, OrderID, OrderDate)
• Report the total orders of each customer. (customerID, totalorders)
• Write a query to generate a five copies of each employee. (EmployeeID, FirstName, LastName)
• Write a query that returns a row for each employee and day in the range 04-07-1996 through 04-08- 1997.
(EmployeeID, Date)
• Return US customers, and for each customer return the total number of orders and total quantities. (CustomerID,
Totalorders, totalquantity)
• Write a query that returns all customers in the output, but matches them with their respective orders only if they were
placed on July 04,1997. (CustomerID, CompanyName, OrderID, Orderdate)
Database system Lab
• Are there any employees who are older than their managers?
• List that names of those employees and their ages. (EmployeeName, Age, Manager Age)
• List the names of products which were ordered on 8th August 1997. (ProductName, OrderDate)
• List the addresses, cities, countries of all orders which were serviced by Anne and were shipped late. (Address, City,
Country)
• List all countries to which beverages have been shipped. (Country)
What to Submit:
You are required to submit the following files.

• 2020-CS-X.sql (You will replace the value of “X” with you roll number to keep name of file)
• Pdf report.
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Database system Lab
PRACTICAL NO.09
Sub Queries
PLO CLO LL
5 2 P4
Objective:
• Understanding of Subqueries.
1.Subquery:
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside
another subquery. A subquery can be used anywhere an expression is allowed.
A subquery nested in the outer SELECT statement has the following components:
• A regular SELECT query including the regular select list components.
• A regular FROM clause including one or more table or view names.
• An optional WHERE clause.
• An optional GROUP BY clause.
• An optional HAVING clause.
The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or
FORBROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE,
or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit
varies based on available memory and the complexity of other expressions in the query. Individual queries may
not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a
singlevalue.
If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included
in the output (the select list of the outer query).
Statements that include a subquery usually take one of these formats:

• WHERE expression [NOT] IN (subquery)
• WHERE expression comparison_operator [ANY | ALL] (subquery)
• WHERE [NOT] EXISTS (subquery)
In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query.
Conceptually,the subquery results are substituted into the outer query (although this is not necessarily how SQL
Server actuallyprocesses Transact-SQL statements with subqueries). There are three basic types of subqueries.
Those that:
• Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
• Are introduced with an unmodified comparison operator and must return a single value.
Database system Lab
• Are existence tests introduced with EXISTS.
2. Subquery Rules
A subquery is subject to the following restrictions:

• The select list of a subquery introduced with a comparison operator can include only one expression
orcolumn name (except that EXISTS and IN operate on SELECT * or a list, respectively).
• If the WHERE clause of an outer query includes a column name, it must be join-compatible with
thecolumn in the subquery select list.
• The ntext, text, and image data types cannot be used in the select list of subqueries.
• Because they must return a single value, subqueries introduced by an unmodified comparison
operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING
clauses.
• The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
• The COMPUTE and INTO clauses cannot be specified.
• ORDER BY can only be specified when TOP is also specified.
• A view created by using a subquery cannot be updated.
• The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single
column name. The rules for a subquery introduced with EXISTS are the same as those for a standard
selectlist, because a subquery introduced with EXISTS creates an existence test and returns TRUE or
FALSE, instead of data
Subquery Syntax
Select column1, column2, column3 from table1 where columnN=(select column from table2 where condition)
Lab Tasks + Home Tasks:
• Return customers and their orders, including customers who placed no orders (CustomerID,
OrderID,OrderDate)
• Report only those customer IDs who never placed any order. (CustomerID, OrderID, OrderDate)
• Report those customers who placed orders on July,1997. (CustomerID, OrderID, OrderDate)
• Report the total orders of each customer. (customerID, totalorders)
• Write a query to generate a five copies of each employee. (EmployeeID, FirstName, LastName)
• List all the products whose price is more than average price.
• Find the second highest price of product.
• Write a query that returns a row for each employee and day in the range 04-07-1996 through 04-
08-1997. (EmployeeID, Date)
• Return US customers, and for each customer return the total number of orders and total quantities.
(CustomerID, Totalorders, totalquantity)
• Write a query that returns all customers in the output, but matches them with their respective orders
only if they were placed on July 04,1997. (CustomerID, CompanyName, OrderID, Orderdate)
Are there any employees who are older than their managers?
• List that names of those employees and their ages. (EmployeeName, Age, Manager Age)
Database system Lab
• List the names of products which were ordered on 8th August 1997. (ProductName, OrderDate)
• List the addresses, cities, countries of all orders which were serviced by Anne and were shipped
late.(Address, City, Country)
• List all countries to which beverages have been shipped. (Country)
What to Submit:

• 2020-CS-X.sql (You will replace the value of “X” with you roll number to keep name of file)
• Pdf report.
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Database system Lab
PRACTICAL NO.10
Single row functions
PLO CLO LL
5 2 P4
Objectives:
The objective of this exercise is to get you familiar with
To apply Single Row functions in SQL.
SQL Functions optionally take arguments from the user and mandatorily return a value. On a broader category, there are two types of
functions:-
Single Row functions - Single row functions are the one who work on single row and return one output per row.
For example, length and case conversion functions are single row functions.
Multiple Row functions - Multiple row functions work upon group of rows and return one result for the complete
set of rows. They are also known as Group Functions.
Single row functions
Single row functions can be character functions, numeric functions, date functions, and conversion functions.
Note that these functions are used to manipulate data items. These functions require one or more input arguments
and operate on each row, thereby returning one output value for each row. Argument can be a column, literal or
an expression. Single row functions can be used in SELECT statement, WHERE and ORDER BY clause. Single
row functions can be -
• General functions - Usually contains NULL handling functions. The functions under the category are
NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.
• Case Conversion functions - Accepts character input and returns a character value. Functions under
the category are UPPER, LOWER and INITCAP.
o UPPER function converts a string to upper case.
o LOWER function converts a string to lower case.
o INITCAP function converts only the initial alphabets of a string to upper case.
• Character functions - Accepts character input and returns number or character value. Functions under
the category are CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM and REPLACE.
o CONCAT function concatenates two string values.
o LENGTH function returns the length of the input string.
o SUBSTR function returns a portion of a string from a given start point to an end point.
o INSTR function returns numeric position of a character or a string in a given string.

Database system Lab
o LPAD and RPAD functions pad the given string upto a specific length with a given character.
o TRIM function trims the string input from the start or end.
o REPLACE function replaces characters from the input string with a given character.
• Date functions - Date arithmetic operations return date or numeric values. Functions under the
category are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND and TRUNC.
o MONTHS_BETWEEN function returns the count of months between the two dates.
o ADD_MONTHS function add 'n' number of months to an input date.
o NEXT_DAY function returns the next day of the date specified.
o LAST_DAY function returns last day of the month of the input date.
o ROUND and TRUNC functions are used to round and truncates the date value.
• Number functions - Accepts numeric input and returns numeric values. Functions under the category
are ROUND, TRUNC, and MOD.
o ROUND and TRUNC functions are used to round and truncate the number value.
o MOD is used to return the remainder of the division operation between two
numbers. General functions

The SELECT query below demonstrates the use of NVL function.
SELECT first_name, last_name, salary, NVL (commission_pct,0)
FROM employees
WHERE rownum < 5;
FIRST_NAME LAST_NAME SALARY NVL(COMMISSION_PCT,0)
Steven King 24000 0
Neena Kochhar 17000 0
Lex De Haan 17000 0
The SELECT query below demonstrates the use of case conversion functions.
SELECT UPPER (first_name), INITCAP (last_name), LOWER (job_id)
Database system Lab
FROM employees Department of Computer Science
WHERE rownum < 5;
UPPER(FIRST_NAME) INITCAP(LAST_NAME) LOWER(JOB_
STEVEN King ad_pres
NEENA Kochhar ad_vp
LEX De Haan ad_vp
ALEXANDER Hunold it_prog
Character functions
SELECT CONCAT (first_name, last_name)

FROM employees
WHERE rownum < 5;
CONCAT(FIRST_NAME,LAST_NAME)
EllenAbel
SundarAnde
MozheAtkinson
DavidAustin
The SELECT query below demonstrates the use of SUBSTR and INSTR functions. SUBSTR function returns the
portion of input string from 1st position to 5th position. INSTR function returns the numeric position of character 'a' in
the first name.
SELECT SUBSTR (first_name,1,5), INSTR (first_name,'a')
FROM employees
WHERE rownum < 5;
SUBST INSTR(FIRST_NAME,'A')
Ellen 0
Sunda 5
Mozhe 0
David 2
Database system Lab
The SELECT query below demonstrates the usage of LPAD and RPAD to pretty print the employee and job
information.
SELECT RPAD(first_name,10,'_')||LPAD (job_id,15,'_')
FROM employees
WHERE rownum < 5;
RPAD(FIRST_NAME,10,'_')||
Steven AD_PRES
Neena AD_VP
Lex AD_VP
Alexander IT_PROG
Number functions
The SELECT query below demonstrates the use of ROUND and TRUNC functions.
SELECT ROUND (1372.472,1)
FROM dual; ROUND(1372.472,1)
______________
1372.5
SELECT TRUNC (72183,-2)
FROM dual;
TRUNC(72183,-2)
________________
72100
Date arithmetic operations

The SELECT query below shows a date arithmetic function where difference of employee hire date and sysdate is
done.
SELECT employee_id, (sysdate - hire_date) Employment_days FROM
employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_DAYS
_______________________
Database system Lab
100 3698.61877
101 2871.61877
102 4583.61877
103 2767.61877
SELECT ADD_MONTHS (sysdate, 5), NEXT_DAY (sysdate), LAST_DAY (sysdate) FROM dual;
ADD_MONTH NEXT_DAY( LAST_DAY(

01-JAN-14 05-AUG-13 31-AUG-13
LAB TASK:
Task 01: Perform single row functions as discussed in class and check output.
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Database system Lab
PRACTICAL NO.11
Stored Procedure
PLO CLO LL
5 2 P4
Objective:
• Understanding of Stored Procedure
Helping Material:
A SQL stored procedure (SP) is a collection of SQL statements and sql command logic, which is compiled and stored on the
database. Stored procedures in SQL allow us to create SQL queries to be stored and executed on the server. Stored
procedures can also be cached and reused. The main purpose of stored procedures is to hide direct SQL queries from the
code and improve performance of database operations such as select, update, and delete data.
• Types of stored procedures
There are two types of stored procedures available in SQL Server:
I. User defined stored procedures
II. System stored procedures
• User defined stored procedures
User defined stored procedures are created by database developers or database administrators. These SPs contain one more
SQL statements to select, update, or delete records from database tables. User defined stored procedure can take input
parameters and return output parameters. User defined stored procedure is mixture of DDL (Data Definition Language) and
DML (Data Manipulation Language) commands.
User defined SPs are further classified into two types:
T-SQL stored procedures: T-SQL (Transact SQL) SPs receive and return parameters. These SPs process the Insert, Update
and Delete queries with or without parameters and return data of rows as output. This is one of the most common ways to
write SPs in SQL Server.
CLR stored procedures: CLR (Common Language Runtime) SPs are written in a CLR based programming language such
as C# or VB.NET and are executed by the .NET Framework.
• System stored procedures

System stored procedures are created and executed by SQL Server for the server administrative activities. Developers
usually don't interfere with system SPs.
Let's login to our SQL Server database, so we can achieve the following:
• How to create a SELECT QUERY based stored procedure which return all records?
• How to create a PARAMETER based SELECT QUERY stored procedure which return records based on
parameters?
• How to create an INSERT query based stored procedure?
• How to create an UPDATE query based stored procedure?
• How to create a DELETE query based stored procedure?
Switch to your database. My database name is MBKTest.

Database system Lab
Empty stored procedure will be created using the following:
The empty template created by SQL Server for a SP looks like the following. The CREATE PROCEDURE SQL command is
used to create a procedure, followed by a SP name and its parameters. The BEGIN and END area is used to define the query
for the operation. This is where you will write a select, update, insert, or delete queries.
2. -- Template generated from Template Explorer using:
Database system Lab
3. -- Create Procedure (New Menu).SQL
4. --
5. -- Use the Specify Values for Template Parameters
6. -- command (Ctrl-Shift-M) to fill in the parameter
7. -- values below.
8. --
9. -- This block of comments will not be included in
10. -- the definition of the procedure.
11. -- ================================================
12. SET ANSI_NULLS ON
13. GO
14. SET QUOTED_IDENTIFIER ON
15. GO
16. -- =============================================
17. -- Author: <Author,,Name>
18. -- Create date: <Create Date,,>
19. -- Description: <Description,,>
20. -- =============================================
21. CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
22. -- Add the parameters for the stored procedure here
23. <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
24. <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
26. BEGIN
27. -- SET NOCOUNT ON added to prevent extra result sets from
28. -- interfering with SELECT statements.
29. SET NOCOUNT ON;
30.
31. -- Insert statements for procedure here
32. SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
33. END
34. GO
What is the naming convention for stored procedures?
We must follow standard naming conventions which may also depend on your project and coding policies.
For user defined stored procedure naming conventions, my suggestions are to add one of the following prefixes
to your SP names.
1. sp
2. stp
3. stp_
4. udstp
5. udstp_
Naming conventions are just to identify objects. By adding these prefixes in the name, we can clearly identify
that this object is a stored procedure.
Create a database table

Database system Lab
Before, we can create and execute any SPs, we need a database table. I create a database table named,
“tblMembers” using the following SQL query and execute it on the server. As you can see, my table has 4
column where the first column is an idenity column. Once the table is created, open table in your SSMS and add
some data by manually entering data to the table.
USE [MBKTest]
/****** Object: Table [dbo].[tblMembers] Script Date: 18-Nov-17,Sat 6:47:55 PM ******/
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[MemberName] [varchar](50) NULL,
[MemberCity] [varchar](25) NULL,
[MemberPhone] [varchar](15) NULL
)
GO
SET ANSI_PADDING OFF
GO
How to create a SELECT stored procedure?

Click on your Database and expand “Programmability” item and right click on “Stored Procedures” or press CTRL + N to
get new query window. In the query area between BEGIN and END, type your SELECT statement to select records from the
table. See the Select statement in the below code.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Manoj Kalla
-- Create date: 18th Nov 2017
-- Description: Return all members
-- =============================================
--Store procedure name is --> stpGetAllMembers
CREATE PROCEDURE stpGetAllMembers
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Database system Lab
SET NOCOUNT ON;
-- Select statements for procedure here

Select * from tblMembers
END
GO
Now, press F5 or click on Execute button to execute the SP.
You should see a message, “Command(s) completed successfully.”

Now go to Programmability -->Stored Procedures, Right Click and select Refresh.
You can see in the following image, the new SP called stpGetAllMembers is created.
Database system Lab
Execute stored procedures in SQL Server

In the UI below, right click on the SP name and select Execute Stored Procedure... to execute a SP. From here,
you can also modify an existing SP.
Alternatively, you can also execute a SP from the Query window.

To run stored procedure in SQL Server Management Studio, switch to Query window or CTRL +N to open an
new query window and type the following command.
• Syntax - EXEC <stored procedure name>

• Example - EXEC stpGetAllMembers
Now, we run our stored procedure called stpGetAllMembers. The output looks like the following:
OUTPUT
Database system Lab
What are parameters in stored procedures?

Parameters in SPs are used to pass input values and return output values. There are two types of parameters:
1. Input parameters - Pass values to a stored procedure.

2. Output parameters - Return values from a stored procedure.
How to create a SELECT query SP with parameters?
In the previous steps, we created a simple SP that returned all rows from a table. Now, let's create a new SP that will
take a city name as an input parameter and will return all rows where city name matches the input parameter value.
Here is the updated SP with a parameter @CityName.
2. GO
4. GO
5. -- =============================================
6. -- Author: Manoj Kalla
7. -- Create date: 20-Nov-2017
8. -- Description: Return specifc city records
9. -- =============================================
10. CREATE PROCEDURE stpGetMembersByCityName
11. -- Add the parameters for the stored procedure here
12. @CityName nvarchar(30)
13.
14. AS
15. BEGIN
Database system Lab
18. SET NOCOUNT ON;
19.
20. Select * From tblMembers
21. where MemberCity like '%'+@CityName+'%'
22.
23. END
24. GO
Execute it.
To run this SP, type the following command in SQL query tool:
EXEC GetMemberByCityName @CityName = 'mal'
OR from the UI, run the SP and provide the following input.
The code to execute looks like the following:

Database system Lab
Output
How to create a INSERT query based stored procedure?

We can use an INSERT INTO SQL query to insert data into a table. The following SQL statement creates an
INSERT SP with three parameters.
2. GO
4. GO
5. -- =============================================
8. -- Description: To create a new member
9. -- =============================================
10. CREATE PROCEDURE stpInsertMember
11. @MemberName varchar(50),
12. @MemberCity varchar(25),
13. @MemberPhone varchar(15)
14.
15. AS
16. BEGIN
19. SET NOCOUNT ON;
20.
21. Insert into tblMembers (MemberName,MemberCity,MemberPhone)
Database system Lab
22. Values (@MemberName,@MemberCity, @MemberPhone)
23.
24. END
25. GO
Right click on stored procedure in Object Explorer and select Refresh.
Pass the value of parameter in Execute dialog box. Something like this:
Database system Lab
The following code can be used to execute this SP in SSMS.
Output
In the query window, you can check if a new record for Member Name 'Mahesh Chand' is added to the
table.
Database system Lab
You can also run the same SP in code.
EXEC stpInsertMember @MemberName = 'Suhana & Ashish Kalla ', @MemberCity = 'Mumbai ',
@MemberPhone = N'9022592774xxx'
OUTPUT
You can check “Suhana & Ashish Kalla” record is added successfully.
Database system Lab
How to create an UPDATE quert based stored procedure?

Let's create a new SP that will update a table records based on the Member ID column. The ID is passed as an input
parameter. Here is the new SP that uses an UPDATE..SET..WHERE command.
2. GO
4. GO
5. -- =============================================
8. -- Description: Update a member detail by ID
9. -- =============================================
10. CREATE PROCEDURE stpUpdateMemberByID
11. @MemberID int,
12. @MemberName varchar(50),
13. @MemberCity varchar(25),
14. @MemberPhone varchar(15)15.
16. AS
17. BEGIN
20. SET NOCOUNT ON;21.
22. UPDATE tblMembers
23. Set MemberName = @MemberName,
24. MemberCity = @MemberCity,
25. MemberPhone = @MemberPhone
26. Where MemberID = @MemberID
27. END
28. GO
Right click on the stored procedure in the Object Explorer and select Refresh. You will see the SP is created.
Now, Right click on SP name and select Execute stored procedure…. Provide the input values and execute.
Database system Lab
We can use the following command in SSMS.
1. USE [MBKTest]
2. GO
3.
4. DECLARE @return_value int
5.
6. EXEC @return_value = [dbo].[stpUpdateMemberByID]
7. @MemberID = 20,
8. @MemberName = N'Nirupama Kalla',
9. @MemberCity = N'Mumbai',
10. @MemberPhone = N'904512541xxxx'
11.
12. SELECT 'Return Value' = @return_value
13.
14. GO
Database system Lab
EXEC stpUpdateMemberByID 17,'Gopal Madhavrai','Bikaner','90454564xxx'
The results should show you the updated values.
How to create a DELETE query based stored procedure?

Let's create an SP that will delete records. The new SP uses a DELETE command and deletes all records that match
provided Member ID.
Execute it.
Right click on Stored Procedures in the Object Explorer and select Refresh.
RUN stored procedure BY UI
Now again right click on stored procedure and select Execute stored procedure…
As you can see in the image, I passed @MemberID parameter value = 4.
Database system Lab
RUN DELETE stored procedure BY MANUALLY (CODING)

EXEC stpDeleteMemberByMemberID 2
OUTPUT
You can see in image MemberID = 4 record has been deleted successfully.
In this article, we saw how to create stored procedures in a SQL Server database for inserting, updating, anddeleting
Database system Lab
records.
Modifying the stored procedure
ALTER PROCEDURE GetProductDescAS
SELECT P.ProductID,P.ProductName,PD.ProductDescription FROMProduct P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductIDEND
Renaming the stored procedure

sp_rename 'GetProductDesc','GetProductDesc_new'
SQL Dropping a stored procedure

DROP PROCEDURE procedure_name;
What to Submit:
• Run all the things in any table of Northwind database and create a pdf file similar like this.
Database system Lab
CONCLUSION:
___________________________________________________________________________
___________________________________________________________________________
__________________________________________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
Database system Lab
-PRACTICAL NO.12
Understanding of Triggers
PLO CLO LL
5 2 P4
Objective:
Understanding of Triggers
Introduction:
A Trigger in Structured Query Language is a set of procedural statements which are executed automatically when
there is any response to certain events on the table in the database. Triggers are used to protect the data integrity
in the database.
In SQL, this concept is the same as the trigger in real life. For example, when we pull the gun trigger, the bullet is
fired.
The trigger is always executed with the specific table in the database. If we remove the table, all the triggers
associated with that table are also deleted automatically.
In Structured Query Language, triggers are called only either before or after the below events:
1. INSERT Event: This event is called when the new row is entered in the table.
2. UPDATE Event: This event is called when the existing record is changed or modified in the table.
3. DELETE Event: This event is called when the existing record is removed from the table.
Types of Triggers in SQL
Following are the six types of triggers in SQL:
1. AFTER INSERT Trigger

This trigger is invoked after the insertion of data in the table.
2. AFTER UPDATE Trigger
This trigger is invoked in SQL after the modification of the data in the table.
3. AFTER DELETE Trigger
This trigger is invoked after deleting the data from the table.
4. BEFORE INSERT Trigger
This trigger is invoked before the inserting the record in the table.
5. BEFORE UPDATE Trigger
This trigger is invoked before the updating the record in the table.
Database system Lab
6. BEFORE DELETE Trigger

This trigger is invoked before deleting the record from the table.
Syntax of Trigger in SQL
CREATE TRIGGER Trigger_Name

[ BEFORE | AFTER ] [ Insert | Update | Delete]
ON [Table_Name]
[ FOR EACH ROW | FOR EACH COLUMN ]
AS
Set of SQL Statement
In the trigger syntax, firstly, we have to define the name of the trigger after the CREATE TRIGGER keyword. After
that, we have to define the BEFORE or AFTER keyword with anyone event.
Then, we define the name of that table on which trigger is to occur.
After the table name, we have to define the row-level or statement-level trigger
Example of Trigger in SQL

To understand the concept of trigger in SQL, first, we have to create the table on which trigger is to be executed.
The following query creates the Student_Trigger table in the SQL database:
CREATE TABLE Student_Trigger
Student_RollNo INT NOT NULL PRIMARY KEY,
Student_FirstName Varchar (100),
Student_EnglishMarks INT,
Student_PhysicsMarks INT,
Student_ChemistryMarks INT,
Student_MathsMarks INT,
Student_TotalMarks INT,
Student_Percentage );
The following query fires a trigger before the insertion of the student record in the table:
CREATE TRIGGER
Student_Table_Marks
BEFORE INSERT
ON
Student_Trigger
FOR EACH ROW
SET new.Student_TotalMarks = new.Student_EnglishMarks + new.Student_PhysicsMarks +
new.Student_Chemist ryMarks + new.Student_MathsMarks,
new.Student_Percentage = (
new.Student_TotalMarks / 400) * 100; The
following query inserts the record into
Student_Trigger table:
INSERT INTO Student_Trigger (Student_RollNo, Student_FirstName,

Student_EnglishMarks, Student_PhysicsMar ks, Student_ChemistryMarks,
Student_MathsMarks, Student_TotalMarks, Student_Percentage)
VALUES ( 201, Sorya, 88, 75, 69, 92, 0, 0);
To check the output of the above INSERT statement, you have to type the following SELECT
statement:
SELECT * FROM Student_Trigger;
Output:
Student Student_ Student_En Student Student_ Student_Maths Student Stu

_RollNo FirstNam glishMarks _Physic chemistry Marks _Total erc
e sMarks Marks Marks
201 Sorya 88 75 69 92 324 81

Database system Lab
Advantages of Triggers in SQL
Following are the three main advantages of triggers in Structured Query Language:
1. SQL provides an alternate way for maintaining the data and referential integrity in the
tables.
2. Triggers helps in executing the scheduled tasks because they are called automatically.
3. They catch the errors in the database layer of various businesses.
4. They allow the database users to validate values before inserting and updating.
Disadvantages of Triggers in SQL
Following are the main disadvantages of triggers in Structured Query Language:
1. They are not compiled.

2. It is not possible to find and debug the errors in triggers.
3. If we use the complex code in the trigger, it makes the application run slower.
4. Trigger increases the high load on the database system
LAB TASK :
Practice Triggers on the data stored in your table.
59
Database system Lab
CONCLUSION:
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
60
Database system Lab
PRACTICAL NO.13
Views and its types in SQL Server
PLO CLO LL
5 2 P4
Learning Objectives:
To understand views and its types in SQL.
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.
CREATE VIEW Syntax

CREATE
VIEW
view_name
AS SELECT
column1,
column2, ...
FROM
table_name
WHERE condition;
SQL CREATE VIEW Examples
If you have the Northwind database you can see that it has several views installed by default.
The view "Current Product List" lists all active products (products that are not
discontinued) from the "Products" table. The view is created with the following SQL:
CREATE VIEW
[Current Product List]
AS SELECT
ProductID,
ProductName
FROM Products
WHERE Discontinued = No;
61
Database system Lab
Then, we can query the view as follows:
SELECT * FROM [Current Product List];
Another view in the Northwind sample database selects every product in the "Products"
table with a unit price higher than the average unit price:
CREATE VIEW [Products
Above Average Price] AS
SELECT ProductName,
UnitPrice
FROM Products
WHERE UnitPrice > (SELECT
AVG(UnitPrice) FROM Products); We can
query the view above as follows:
SELECT * FROM [Products Above Average Price];
Another view in the Northwind database calculates the total sale for each category in
1997. Note that this view selects its data from another view called "Product Sales for
1997":
CREATE VIEW [Category Sales For 1997] AS

SELECT DISTINCT CategoryName,
Sum(ProductSales) AS CategorySales FROM
[Product Sales for 1997]
GROUP BY CategoryName;
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997];

We can also add a condition to the query. Let's see the total sale only for the category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName = 'Beverages'; SQL Updating a View
You can update a view by using the following syntax:
SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ...
FROM table_name WHERE condition;
Now we want to add the "Category" column to the "Current Product List" view. We will update the
view with the following SQL:
62
Database system Lab
CREATE OR REPLACE VIEW [Current Product List] AS SELECT ProductID, ProductName,
Category
FROM Products
WHERE Discontinued = No;
SQL Dropping a View
You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name;
LAB TASK:
Task 01: Practice views on the data stored in your table
CONCLUSION:
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
63
Database system Lab
PRACTICAL NO.14
CRUD operations
PLO CLO LL
5 2 P4
Learning Objectives:
• CRUD operations using C# language with SQL server
SQL CRUD Operations Queries Syntax
• Insert Data into Table:

INSERT INTO table_name (column1, column2,
column3, ...) VALUES (value1, value2,
value3, ...);
• Update Data into Table:

UPDATE table_name
SET column1 = value1,

column2 = value2, ... WHERE
condition;
• Retrieve Data from Table:

FROM table_name;
• Delete Data from a Table:

DELETE FROM table_name WHERE condition;
Connection of SQL using C#
C# code to insert data in a table while clicking on a button is given below.
var con = Configuration.getInstance().getConnection();
SqlCommand cmd = new SqlCommand("Insert into Table_1 values (@ID,

@Name, @Department)", con);
64
Database system Lab
cmd.Parameters.AddWithValue("@ID", int.Parse(textBox1.Text));
cmd.Parameters.AddWithValue("@Name", textBox2.Text);
cmd.Parameters.AddWithValue("@Department",
textBox3.Text); cmd.ExecuteNonQuery();
MessageBox.Show("Successfully saved");
C# code to show data in a grid is given below.
var con =
Configuration.getInstance().getConnection();
SqlCommand cmd = new SqlCommand("Select *
from Table_1", con); SqlDataAdapter da = new
SqlDataAdapter(cmd);
DataTable dt =
new
DataTable();
da.Fill(dt);
dataGridView1.D
ataSource = dt;
Lab Tasks
1. Create a new database named Lab2

2. Create table with following schema
Student(RegistrationNumber:String, Name:String , Department:String ,
Session:Number, CGPA:Number, Address:String)
3. Use the student table to create the following CRUD operations
a. Add a Student
b. Edit Student
c. List of Students
d. Search Student
e. Delete Student
4. UI components for the fields should be appropriate. Change the Edit Use case to
optimize such that all the fields should not be updated in table, you need to
detect, which field has been changed on frontend, then update only those fields
which have been changed. E.g. for editing of student, we change registration
number, then database should only update only registration number.
65
Database system Lab
CONCLUSION:
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________
RUBRICS:

Ability to 5 5
Conduct Structure
practical
5 5
Data Analysis &
Efficiency
Interpretation
66

CSC351_Lab_Manual_Database Systems

Uploaded by

Document Informationclick to expand document information

Document Informationclick to expand document information

Copyright:

Available Formats

CSC351_Lab_Manual_Database Systems

Uploaded by

Document Information

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

CSC351_Lab_Manual_Database Systems

Uploaded by

Copyright:

Available Formats

DATABASE SYSTEMS

Course Code CSC 352

Department of Computer Science

Follow the designated lab schedule and complete assignments on time.

Use lab resources responsibly, including computers and software licenses.

2. Maintain an ergonomic workspace for comfortable and strain-free programming.

5. Regularly back up code and important files to prevent data loss.

7. Be aware of emergency exits, fire extinguisher locations, and evacuation procedures.

I have read all of the above, and I agree to conform to its

Sr.No. Performance Exemplary Satisfactory Unsatisfactory

1 Fully understand the Has very good Has poor

Sr. Performance Exemplary (4-5) Satisfactory (2-3) Unsatisfactory

Sr Performance Exemplary Satisfactory (2- Unsatisfactory

1 Responds well, Generally Non-responsive.

Sr.No. Performance Exemplary Satisfactory (2-3) Unsatisfactory

Course Title: Database Systems

1 Introduction of Database Systems and its Practical Use 1

2 Create Database, Table in SQL, DDL, DML and DCL commands 1

4 Arithmetic Operators in SQL., Logical and Relational Operators In SQL. 1

5 Group functions, Having clause, Column and Table Alias. 1

6 Commands to Alter Table, Add or Drop a Column, update data 1

7 Use queries Delete Table and a Row, Truncate a Table in SQL. 1

8 JOINS in SQL Server 1

10 Single Row functions in SQL 2

11 Stored Procedure and Views 2

12 Introduction to Triggers and its type 2

13 Views and its types 2

14 CRUD operations using C# language with SQL server 2

The objectives of this first experiment are:

• Installation of SQL server and SSMS.

1. System Requirements for SQL Server 2019

1.1. Operating system

Windows 10 TH1 1507 or greater and Windows Server 2016 or greater

1.2. .NET Framework

1.3. Disc Space

SQL Server requires a minimum of 6 GB of available hard-disk space.

At least 4 GB and should be increased as database size increases to ensure optimal

• Minimum: x64 Processor: 1.4 GHz, Recommended: 2.0 GHz or faster

• x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon

SQL Server requires Super-VGA (800x600) or higher resolution monitor.

Figure2: Selection of basic version

Figure3: Terms and conditions for installing SQL Server

Figure 7: Installation of SSMS

Click next in the introduction screen

DATABASE MANAGEMENT SYSTEMS LAB 21

Figure 10: Selecting database objects

DATABASE MANAGEMENT SYSTEMS LAB 22

Figure 11: Database_Scripts_With_Data_Publish_Options

Click on advanced in that window.

DATABASE MANAGEMENT SYSTEMS LAB 23

Figure 12: Database_Scripts_With_Data_Advanced Click on Next.

Figure 13: Database_Scripts_With_Data_Finish

Now what else is pending, go ahead and run your script.

1. Create a new database named TestDB_2022_CS

2. Create a new table named student using some attributes

3. Generate Scripts of your database

4. Restore your schema to another machine

5. Generate scripts of data