SHREE SWAMI ATMANAND SARASWATI INSTITUTE OF TECHNOLOGY

COMPUTER ENGINEERING DEPARTMENT

LAB ASSIGNMENT
B.E. II (SEM. III)
DATABASE MANAGEMENT SYSTEM

PRACTICAL LAB ASSIGNMENT: 1

(Required Lab Hours : 4)
A. Create a relational database that contains the following tables and insert the following data into
these tables.

STUD_MEMBER
Roll_No FName MName SName Dept_ID Semester Contact_No Gender
1 Ankur Samir Kahar 1 1 272121 M
2 Dhaval Dhiren Joshi 1 1 232122 M
3 Ankita Biren Shah 1 1 112121 F
10 Komal Maheshkumar Pandya 2 3 123123 F
13 Amit Jitenkumar Mehta 3 3 453667 M
23 Jinal Ashish Gandhi 2 1 323232 M
22 Ganesh Asha Patel 2 3 124244 M
4 Shweta Mihir Patel 3 1 646342 F
7 Pooja Mayank Desai 3 3 328656 F
8 Komal Krishnaraj Bhatia 2 3 257422 F
43 Kiran Viraj Shah 1 1 754124 F

DEPARTMENT
Dept_ID Dept_Name
1 Information Technology
2 Electrical
3 Civil
4 Mechanical
5 Chemical

B. Now, solve the following SQL Queries.

1. Display the names and contact numbers of all student members.

2. Give the names and roll numbers of all students of Information Technology who are members.
3. Display names of Departments whose students are members.
4. Display names of Departments for which no students are members.
5. Display names of all Departments.
6. Find the number of students of Electrical Department who are members.
7. Display information of student members whose name begins with the letter A.
8. Display all details of Male members only.
9. Display data of student members who are currently in semester 3.
10. Display data of student female members in alphabetical order.
 PRACTICAL LAB ASSIGNMENT (SQL): 2
(Required Lab Hours : 4)
Table: sales

OrderID OrderDate OrderPrice OrderQuantity CustomerName

1 12/22/2005 160 2 Smith

2 08/10/2005 190 2 Johnson

3 07/13/2005 500 5 Baldwin

4 07/15/2005 420 2 Smith

5 12/22/2005 1000 4 Wood

6 10/2/2005 820 4 Smith

7 11/03/2005 2000 2 Baldwin

Solve following queries using Aggregate Function for above table:

1. Count how many orders have made a customer with CustomerName of Smith.
2. Find number of unique customers that have ordered from the store.
3. Find out total no. of items ordered by all the customers.
4. Find out average number of items per order.
5. Find out the average OrderQuantity for all orders with OrderPrice greater than 200
6. Find out what was the minimum price paid for any of the orders.
7. Find out the highest OrderPrice from the given sales table
8. List out unique customers name only from the table.
9. List out name of the customers who have given order in the month of DECEMBER
10. Find out the total amount of money spent for each of the customers.
11. Select all unique customers, who have spent more than 1200 in the store.
12. Select all customers that have ordered more than 5 items in total from all their orders.
13. Select all customers who have spent more than 1000, after 10/01/2005.
14. Select orders in increasing order of order price.
15. Select orders in decreasing order of order price.
 PRACTICAL LAB ASSIGNMENT (SQL): 3
(Required Lab Hours : 6)
Table: sales
OrderID OrderDate OrderPrice OrderQuantity CustomerName

1 12/22/2005 160 2 Smith

2 08/10/2005 190 2 Johnson

3 07/13/2005 500 5 Baldwin

4 07/15/2005 420 2 Smith

5 12/22/2005 1000 4 Wood

6 10/2/2005 820 4 Smith

7 11/03/2005 2000 2 Baldwin

8 12/22/2002 1000 4 Wood

9 12/29/2004 5000 4 Smith

Table: products
Product_id OrderId Manufacture_Date Raw_Material Vender_id
AZ145 2 12/23/2005 Steel 1
CS784 4 11/28/2005 Plastic 2
AZ147 6 08/15/2002 Steel 3
FD344 3 11/03/2005 Milk 1
GR233 3 11/30/2005 Pulses 2
FD123 2 10/03/2005 Milk 2
CS783 1 11/03/2004 Plastic 2
CS435 5 11/04/2001 Steel 1
GR567 6 09/03/2005 Pulses 2
FD267 5 21/03/2002 Bread 4
FD333 9 12/12/2001 Milk 1

Table: vender_info
Vender_id Vender_name
1 Smith
2 Wills
3 Johnson
4 Roger

Table: venders
Raw_Material Venders Vender_id
Steel Smith 1
Plastic Wills 2
Steel Johnson 3
Milk Smith 1
Pulses Wills 2
Bread Roger 4
Bread Wills 2
Milk Wills 2
1. Display product information which are ordered in the same year of its manufacturing year.
2. Display product information which are ordered in the same year of its manufacturing year where vender
is smith.
3. Display total no. of orders placed in each year.
4. Display total no. of orders placed in each year by vender Wills.
5. Display the name of all those persons who are venders and customers both.
6. Display total no. of food items ordered every year.
7. Display total no. of food items ordered every year made from Bread.
8. Display list of product_id whose vender and customer is different.
9. Display all those customers who are ordering products of milk by smith.
10. Display total no. of orders by each vender every year.
11. Display name of those venders whose products are sold more than 2000 Rs. Every year.

PRACTICAL LAB ASSIGNMENT (PL/SQL Exercise): 4

(Required Lab Hours : 6)
1. Write a simple PL/SQL script that displays Hello World.
2. Write a PL/SQL stored procedure to display Hello World.
3. Write a PL/SQL script that performs simple arithmetic like Addition, Subtraction, Multiplication &
Division of input numbers.
4. Create two tables as shown below:
Table 1 : product (product_id, product_name, supplier_name, unit_price)
Table 2: product_price_history(product_id, product_name, supplier_name, unit_price)

Insert appropriate data into Table 1 i.e. the product table.

Now write a PL/SQL trigger that automatically copies a row from product table to
product_price_history table whenever the unit price of a product is changed in the product table.
Note: product table contains new updated value of unit price while product_price_history table
contains the old value.
5. Write a PL-SQL script to compare three given numbers and display them in ascending order.
6. Create the following table:
Emp(E_ID, E_Name, E_Dept, E_Salary)

Insert appropriate data into Emp table.

The attribute E_Dept contains values like ( I.T. , Accounts, Sales)..
Write a PL-SQL cursor that increments the salary of employees of I.T. Dept. by 20%.