060010102 – database management systemsutu.ac.in/dcst/download/documents/prmscit060010102.pdf ·...

060010102 – Database Management Systems 2013

Problem statement to perform in laboratory

SECTION - A

NOTE: SECTION- A is compulsory for all students.

Sr.No.

AllocatedHours

Problems Definition Objective

1 1 Introduction of DB2 To understandbasicintroductionabout DB2

2 1 Create the following table in DB2 Client_Master(Client_no,Name,Address,City,

Pincode) Salesman(Saleman_no,Sname,Address1,Address2,City,

Pincode,State) Project(Project_Id,Project_name) Cust(Custid, Fname ,Lname ,Address ,City ,State

,Country) Student_Detail(Studid,Sname,Address,City,State,Conta

ctno,Pincode)

To understandsimple tablecreation


3 2 Create the following table with proper constraints in DB2.

Employee(EmpID,EmpName,City,Salary,DateOfJoining,Desig,DeptID)

Department(DeptID, DeptName)

Make EmpID of Employee &DeptID of Department table asPrimary key.Apply necessary constraints while creating table.

1. EmpID and DeptID as primary key.2. DeptID as foreign key in Employee table.3. Apply constraint salary > 0 in Employee table.4. Desig can store values “Manager” , “Programmer” ,

“Designer” ,”Tester” ,”System Administrator” .

Create the following table with proper constraints in DB2 Student(Studid,Name,DOB,City,Gender) Course(Ccode,Coursename,Ccost) Studies(Studid,Ccode,JDT)1. Apply necessary constraint while creating table.2. Add constraint primary key for studies table as

combination of fields{studid,Ccode}3. Add constraints NOT NULL for courename field in

course table4. Gender can store value ‘M’ or ‘F’.

To learn tablecreation withappliednecessaryconstraints

4 1 Create the following table inDB2. Customer(CustNo,CustName,Address,City,State,

ContactNo) Item(ItemNo, ItemName,ItemPrice,Qty_In_Hand) Invoice(InvNo, InvDate,CustNo) Inv_Item(InvNo, ItemNo,Qty)

Underline attribute is primary key.Create a table along with primary key constraint only.

To learn simpletable creationalong withprimary key

5 1 1. Add foreign key ‘CustNo’ in Invoice table.2. Add foreign key ‘InvNo’ and ‘ItemNo’ in Inv_Item

table.3. Add NOT NULL constraint on CustName attribute.4. Add Qty> 0 constraint in Inv_Item table.5. Add new attribute ‘Pincode’ in Customer table.6. Add new attribute ‘Address2’ in Customer table

To learnMODIFY andALTERstatement

6 1 1. Destroy attribute ‘Address2’ in customer table.2. Destroy attribute‘pincode’ in client_master.

To learn DROPand DELETE


SECTION -1

Sr.No.

AllocatedHours


8 1 1. Change the salary of Employee Table to Rs. 100002. Change the city of EmpID ‘E004’ and DeptName

‘management’ to ‘Baroda’.3. Change the city of Deptname ‘Research’ to ‘mumbai’4. Change the ItemPrice of Item Name ‘gear’ to 500.5. Change the state of InvNo ‘In003’ to ‘Rajasthan’

To understandUPDATEstatement

9 1 1. Delete Project from project table whoseProjectId is‘P_005’.

2. Delete Employee from employee table whose city is‘Ahmadabad’.

3. Delete Employee from employee table whosedesignation is ‘manager’ and city is ‘surat’.

4. Delete Customer from customer table whose city is‘Mumbai’.

5. Delete Employee from employee table whose salarygreater than 5000 and less than 10000.

6. Delete Item from Item Table whose item name is ‘TV’and Item price less than 10000.

To understandDELETEstatement

10 1 1. Retrieve the entries contents of the Employee table. To understand

3. Destroy the table‘Client_master’ along with the data.4. Destroy the table‘Cust’ along with the data.5. Destroy the table ‘Student_Detail’ along with the data

statement

7 2 1. Insert at least 10 records in Employee table.2. Insert at least 10 records in Department table.3. Insert at least 10 records in Customer table.4. Insert at least 10 records in Item table.5. Insert at least 10 records in student table.6. Insert at least 10 records in Course table.7. Insert at least 10 recodes in Studies table.8. Write Insert script with at least 10 records in Invoice

table.9. Write Insert script with at least 10 records in Inv_item

table.10. Insert at least 5 records in Project table.

To learn INSERTStatement


2. Find out the name of all employees.3. Retrieve name, city and the state of all employees.4. Retrieve unique city from employee table.5. Retrieve unique qty_on _hand from item table.

simple SELECTstatement

11 1 1. Find employees who are from city ‘Mumbai’.2. Find name from employee table whose contact no is

9898989890.3. Find employees whose date of joining is ’25-9-2012’.4. Find unique city from customer table.5. Find student detail whose name is ‘smith’.6. Find student name whose date of birth is ’12-11-1989’.

To learn SELECTstatement withWHERE clause

12 1 1. Find student detail whose city is ‘surat’ or ‘mumbai’.2. Find detail of department whose id is ‘D006’ or ‘D007’.3. Find the student detail whose not belong to ‘surat’ city.4. Find department detail whose department id is ‘D008’

and department name is ‘Developing’ or ‘Research’.5. Find student detail whose student id not ‘S007’.6. Find unique city from employee table whose salary is

10000 or 15000.7. Find name,city from student table whose studid is

‘S006’ or ‘S009’ and city is ‘Pune’.

To learn AND,OR ,NOToperator

13 2 1. Find the project detail whose project name is ‘Onlinehotel management, or ‘online booking’.

2. Find the name of employee whose salary is 10000 or15000.

3. Find the employee detail whose department id is ‘D007’or ‘D005’ or ‘D003’.

4. Find the name,DoB whose city is ‘mumbai’ or ‘pune’.5. Find course detail whose course name is ‘Java’ or ‘C

programming’.6. List the students who stay in city whose first letter is

‘M’7. List the project having ‘O’ as the first letter and ‘T’ as

last letter in their name.8. List the name from customer table having ‘A’ as first

letter in their name OR’s’ as second letter in their city.9. List the item detail having ‘G’ letter in their item name.10. List the customer whose gender is ‘M’ and having ‘H’

as last letter in their name.11. List the student whose birth date January to march.12. List the employee detail whose salary less than 800013. List the employees whose joining date march to august

To understandIN,BETWEENand LIKEoperator


14. List the detail from studies whose joining dates June toaugust.

15. List the student whose not stay at ‘Navsari’ or ‘Bardoli’and gender is ‘G’.

14 1 1. List the employee details who are ‘Manager’2. List name,city whose course code is ‘C004’ or ‘C002’.3. List course detail whose studid is ‘S007’ or ‘S004’.4. List the name,address,city from customer table whose

invoice no is ‘I006’ and date ’30-4-2013’.5. List student detail whose city is ‘surat’ and joining date

is ’30-8-2013’.

To understandsub queriesstatement

15 1 1. Find the absolute value of number -11.2. Find the square root of number 1003. Find the largest value of number -10.6.4. Find the sign of 45.4 numbers.

To understandsimple numericfunction

16 1 1. Find the length of ‘Hello world’2. Find the position of ‘I’ character in ‘Introduction’.3. Find the length of customer name whose custid is

‘C005’.4. Find the last five character of city whose EmpID is

‘E003’.5. Find the length of EmpName whose deptname is

‘Finance’.

To understandsimple STRINGfunctions.

17 1 1. List the next day of current month.2. List the day and month of those students whose city is

‘Surat’3. List the students detail whose birth year is ‘1989’.4. Add the 5 month in employee joining date whose city is

‘Surat’.5. List the student detail who’s joining date is ‘30/4/2013’.

To understandDATE Functions

18 1 1. List the student detail in ascending order of city.2. List the employee name,city ,salary from employee

detail in ascending order of salary3. List the detail detail order by course code.4. List invoice item order by qty.

To understandsimple ORDERBY statement

19 1 1. Find the total employee.2. Find item name,price,qty whose qty minimum3. Find the total course from course table.4. Find the total employee who’s come from ‘surat’.

To learnaggregatefunctions


20 1 1. Find the no.of customer in each city2. Find the miminum salary from employee table in each

department.3. Display ename salary and deptno for each employee

who earns a salary greater than the average for theirdepartment order by deptno?

4. Reduce the salary of an employee by 5% who getsmaximum salary and work in deptno ‘D005’.

To understandGROUP BY andHAVING clause

21 1 1. Find the item and invoice detail (natural join).2. Find the item and invoice detail(evai-join)3. List the item and invoice detail (left join)4. List the customer and invoice detail whose customer

name start with ‘M’ character (right join).

To understandsimple joins

22 1 1. Retrieve the name of all the customer andemployeeresiding in the state of ‘Gujarat’ and city namestart with ‘N’ character

2. Retrieve the customer detail whose name start with ‘P’character but not stay on ‘pune’.

3. Retrieve student detail whose city is ‘mumbai’ or cousername is ‘VB.Net’.

To learn SEToperator

SECTION -2

Sr.No.

AllocatedHours


8 1 1. Change the salary of EmpID ‘E005’ to Rs.5000.2. Modify Salary to 10000 for employee whose designation is

“Manager”.3. Change the CustName of ContactNo 9878987879

to’john’.4. Change the Qty_in_hand of ItemID ‘I008’ to 40.5. Change the Invdate of InvNo ‘In009’ to ‘30/9/2012’.


9 1 1. Delete Project from project table whose ProjectId is‘P_007’ Project name is ‘Online Hospital

To understandDELETE


management’2. Delete Employee from employee table whose EmpId is

‘E009’.3. Delete Employee from employee table whose salary

>10000 or designation ‘Tester’.4. Delete Customer from customer table whose state is

‘Maharashtra’.5. Delete customer from customer table who’s Invno is

‘In009’.6. Delete from Customer where the column state holds the

value ‘Tamil Nadu’.

statement

10 1 1. Retrieve the entries contents of the Department table2. Find out the name of all customers.3. Retrieve name and address of all customers.4. Retrieve unique designation from employee table.5. Retrieve unique customer no from invoice table.

To understandsimple SELECTstatement

11 1 1. Find name from department table whose deptid is‘D008’.

2. Find name from customer table whose city is ‘pune’.3. Find customer no customer table whose contactno is

98789657454. Find course detail whose course cost is 10000.5. Find detail of student whose gender is ‘M’.


12 1 1. Find student name,city whose city is ‘surat’ or ‘pune’.2. Find detail of department whose name is ‘Research’ or

‘Management’3. Find employee detail whose salary is 10000 and city is

‘surat’.4. Find employee detail whose department id is ‘D006’ or

‘D008’.5. Find student detail whose city is ‘surat’ but gender not

‘male’6. Find item detail from item table whose item price is

2000 and qty on hand is 60.7. Find invoice id and invoice date from invoice table

whose custno is ‘C002’.


13 2 1. Find the project detail whose project id is ‘P006’ or‘P007’ or ‘P004’.

2. Find the name of department whose ID is ‘P007’ or‘P008’ or ‘P005’.



3. Find the customer detail whose name is ‘C007’ or‘C005’.

4. Find the item no and item name from item table whoseitem price is ‘I007’ or ‘I009’.

5. Find studid whose joining date is ’30-7-2012’ or ’23-6-2013’.

6. List the names of all student having ‘a’ as the secondletter in their name.

7. List the employee having ‘D’ as the first letter I theirdesignation.

8. List name from item table having ‘P’ as second letter intheir name.

9. List the item detail having ‘P’ as last letter and ‘A’ letterin their name

10. List the item whose item price is 800 and having ‘D’ assecond letter in their name.

11. List the employee detail whose range lies betweenRs.8000 to Rs.10000.

12. List the name,city,state whose salary greater than 6000and less than 10000.

13. List the course detail whose range lies betweenRs.10000 to Rs.20000

14. List the employee whose not belong to ‘tester’ or‘system analyst’ designation and those stay at ‘surat’.

15. List the invoice detail whose cust no ‘C006’ or ‘C009’.

14 1 1. List the name,city ,salary from employee table whosedeptname is ‘Research’

2. List student detail whose joining date is ’30-5-2012’.3. List course name whose studid is ‘S008’ and date is ’30-

7-2012’.4. List the item detail from item table whose qty is 605. List cname from customer table whose state is ‘gujarat’

and custno is ‘C007’ or ‘C003


15 1 1. Find the absolute value of number 60.2. Find the square root of 81.3. Find the largest value of number -11.5.4. Find the sign of -1.6 numbers

To understandsimple numbericfunction

16 1 1. Find the length of ‘introduction’2. Find the position of ‘o’ character in ‘Hello world’.3. Find the length of course whose course code is ‘C003’.4. Find the starting two character of Empname whose

EmpID is ‘E005’.5. Find the last three character of empname whose



deptname is ‘Development’

17 1 6. Write a query to add 6 month in current date.7. List the year of that student whose ID is ‘E007’.8. List the students detail whose birth month is ‘March’9. Add the 2 days in student birthday whose id is ‘S003’10. Find different between two student dates whose

employee id is ‘E005’ and ‘E003’


18 1 1. List the project detail in descending order of projectname.

2. List the employee detail order by department id.3. List the item detail order by item price.4. List the student detail order by city and descending

order by state.


19 1 1. Find the average salary of employee.2. Find item detail whose qty maximum3. Find the total customer from customer table.4. Find the total student whose joining a course.


20 1 1. Find the no.of employee in each designation2. Find the employee detail in each department and salary

in descending order.3. Find the course detail in each course code.4. Increase the salary of an employee by 10% who gets

minimum salary and work in deptno ‘D007’.


21 1 1. Find the student and studies detail (natural join)2. Find the customer and invoice detail (evai-join)3. List the employee and department detail (left join)4. List the item and invoice detail whose item price

50(right join)


22 1 1. Retrieve the detail of all customer and employeeresiding in the city of ‘Surat’ and name start with ‘P’character.

2. Retrieve the item detail whose not sold.3. Retrieve employee detail whose gender is ‘male’ but

department name is ‘Management’



SECTION -3

Sr.No.

AllocatedHours


8 1 1. Change the city of EmpID ‘E007’ to ‘puna’.2. Change the Salary of DeptName ‘Management’ to

15000.3. Change the ContactNo of CustNo ‘C005’to

88978987894. Change the Itemname of ItemID ‘I005’ to ‘TV’.5. Change the Qty of ItemNo ‘I007’ to 50.


9 1 1. Delete Project from project table whose ProjectId is‘P_007’ Project name is ‘Online Hospitalmanagement’

2. Delete Delete Employee from employee table whosesalary >10000 or designation ‘Tester’.

3. Delete Customer from customer table whose state is‘Maharashtra’.

4. Delete customer from customer table who’s Invno is‘In009’

5. Employee from employee table whoseEmpId is ‘E009’.6. Delete all Item form item table where the quantity on

hand is equal to 100.


10 1 1. Retrieve the entries contents of the Customer table.2. Find out the name of all items.3. Retrieve item name and item price of all items.4. Retrieve name, unique city from employee table.5. Retrieve unique salary and designation from employee

table.


11 1 1. Find name,city,state from employee table whose ID is‘E009’.

2. Find employees whose salary is 5000.3. Find unique designation from employee table whose

salary is 10000.4. Find Inv_no and Itemno from Inv_item whose qty is 60.5. Find course name and cost whose course code is ‘C006’.6. Find student detail whose city is ‘surat’.


12 1 1. Find name from student table whose state is ‘Gujarat’ or‘Maharashtra’.

To learn AND,OR ,NOT


2. Find detail of project from project table whose projectID is ‘P007’ or ‘P009’.

3. Find employee name whose salary is 10000 or 6000 andcity is ‘mumbai’.

4. Find Employee detail whose salary is 10000 andemployee id is ‘E008’

5. Find item detail from item table whose item price is 700or 900.

6. Find item name from item table whose item price is2000 or qty on hand is 100.

7. Find student detail whose not belong to ‘surat’ or‘mumbai’.

operator

13 2 1. Find the name,city from employee table who stay in‘surat’ or ‘mumbai’.

2. Find the name,city and salary from employee tablewhose designation is ‘developer’ or ‘Tester’

3. Find the name,city from customer table whose state is‘Gujarat’ or ‘Maharashtra’.

4. Find the item detail whose qty on hand is ‘I008’ or‘I004’ or ‘I002’.

5. Find employee details whose salary is 10000 or 6000and who stay in ‘surat’

6. List the students having ‘m’ as the seond letter in theirname.

7. List the course detail whose course name start with ‘I’asthe first letter in their name.

8. List name,city from customer table having ‘P’ letter intheir name.

9. List the customer table having ‘9’ as first letter in theircontact no.

10. List department whose department id is ‘D008’ OR‘D004’ and having ‘Info’ word in their name

11. List the name,city from employee table whose rangesnot lies between Rs.8000 to Rs.10000.

12. List the item detail whose range lies between Rs.500 toRs.1000.

13. List the course detail whose cost is greater than 2000014. List the employee whose salary greater than 10000 and

designation is ‘system analyst’.15. List the student whose city is ‘surat or ‘mumbai’ and

‘M’ as second letter in their name.


14 1 1. List the unique salary from employee table whose To understand


department name is ‘Management’ or ‘Research’.2. List studid,name,city whose joining date is ‘7-6-2013’

or ‘6-9-2013’.3. List the customer detail whose invoice no is ‘I008’.4. List the item detail whose qty is 50 and invoice no is

‘In005’.5. List item name,item price from item table whose item

no is ‘I005’ or ‘I003’ and qty is 10.

sub queriesstatement

15 1 1. Find the absolute value of number -503.2. Find the square root of 4.3. Find the largest value of number 14.34. Find the sign of 54 numbers.


16 1 1. Find the length of ‘database management system’.2. Find the position of ‘m’ character in ‘Database

management system’.3. Find the length of student name whose studid is ‘S005’.4. Find the starting five character of deptname whose

deptid is ‘D005’5. Find substring of empname start position 2 and

endposition 5 whose empid is ‘E005’.


17 1 1. List the last date of current month.2. List the month of that student whose ID is ‘E003’.3. List the students detail whose birth day 29.4. Add the 4 month in student birthday whose id is ‘S005’5. Add the 10 days in employee joining date whose city is

‘Navsari’ or salary 5000


18 1 1. List the employee detail in ascending order ofdesignation.

2. List the cname,city,state descending order by city.3. List the item name,price,qty order by qty on hand.4. List the department detail department name in

descending order


19 1 1. Find the minimum salary of employee.2. Find item detail whose price minimum3. Find the total item in item table4. Find the mimimum course cost


20 1 1. Find the no.of student in each state2. Find out the employees who earn the highest salary in

each job type. Sort in descending sal order?3. Remove the detail of an employee who gets minimun



salary in ‘Account’ department4. List student detail whose course cost maximum

21 1 1. Find the customer and invoice detail(natural join)2. Find the student and studies detail (evai-join)3. List the student and studies detail (left join)4. List the employee and department detail whose salary

is 10000 (right join)


22 1 1. Retrieve the names of all the customer and employeeresiding in the state of ‘Gujarat’.(union)

2. Retrieve the customer detail whose not purchase anyitem.

3. Retrieve employee detail whose designation‘Developer’ but salary grether than 10000.


SECTION -4

Sr.No.

AllocatedHours


8 1 1. Change the Deptname of DeptID ‘D006’to ‘SeniorDeveloper’.

2. Change the Empname of city ‘surat’ and salary 10000 to‘Piter’

3. Change the Address of custno ‘C007’ to ‘parle point’.4. Change the Custname of InvDate ‘30/9/2012’ to ‘smith’.

5. Change the ContactNo of InvNo ‘In006’ to9897898789.


9 1 1. Delete all recodes from project table.2. Delete Employee from employee table whose city is

‘surat’ and salary is 10000.3. Delete customer from customer table who’s CustNo is

‘C007’.4. Delete employee from employee table who have joined

on date ’21-06-2011’5. Delete customer from customer table whoseInvdate is

‘29/5/2012’.6. Delete employee from employee table whose



designation ‘programmer’ or ‘designer’.

10 1 1. Retrieve the entries contents of the Item table.2. Find out the name of all departments.3. Retrieve invoice id and invoice date of all invoice.4. Retrieve unique item price from item table.5. Retrieve unique Item No from Inv_Item table.


11 1 1. Find employee whose designation is ‘Developer’2. Find name, address and city from employee table whose

state is ‘Gujarat’.3. Find item from item table whose item price 600.4. Find Item from item table whose itemno is ‘I008’.5. Find studies detail whose joining date is ‘12/5/2012’.6. Find course code and course code from course table

whose course name is ‘VB.net’.


12 1 1. Find student detail whose date of joining is ’12-5-2012’or ’15-10-2011’.

2. Find project id whose project name is ‘Onlinemanagement system’.

3. Find name,city state from employee table whosedesignation is ‘developer’ or ‘Tester’.

4. Find course name whose course cost is 10000 or 8000.5. Find item detail from item table whose item name is

‘gear’ and item price 900 or 8006. Find customer detail whose not belong to ‘Gujarat’

state.7. Find id,name,city,state whose salary is 1000 or 6000

and not belong to ‘surat’ city.


13 2 1. Find the detail for employees who stay in ‘pune’ or‘baroda’.

2. Find the employee detail whose designation is ‘seniardeveloper’ or ‘developer’

3. Find the student detail from student table whose studentid is ‘S007’ or ‘S009’ or ‘S004’.

4. Find the invoice detail whose custno is ‘C004’or‘C002’.

5. Find customer detail whose city is ‘surat’ or ‘baroda’ or‘Ahmedabad’ and state is ‘gujarat’.

6. List the customer having ‘p’as the last letter in theirname

7. List the name,city from employee table having ‘P’ as thethired letter and ‘G’ as fifth letter in their name



8. List the employee detail having ‘D’ letter in theirdesignation

9. List the employee whose salary is 10000 and having ‘K’letter in their name.

10. List course detail whose course code is ‘C007’ or‘C005’ and having ‘T’ as last letter in their name

11. List the employee detail whose salary greater than10000.

12. List the item name,item price ,qty on hand from itemtable whose qty range lies between 30 to 60.

13. List the course detail whose cost is less than 20000.14. List the item detail whose itemprice less than 1000 and

qty on hand is 50.15. List name,city from customer table whose salary is

10000 or 6000 and ‘p’ as lastletter in their city

14 1 1. List student detail whose course code is ‘C007’ or‘Coo5’.

2. List student detail who’s joining date is ’08-5-2013’ andcourse code is ‘C006’.

3. List employee detail whose designation is ‘Developer’and ‘Tester’ and ‘M’ as firstletter in their departmentname.

4. List itemno,itemname ,itemprice from item table whoseInvoice no is ‘In006’ or ‘In004’.

5. List the customer detail whose invoice no is ‘I006’ or‘I003’.


15 1 1. Find the absolute value of number 40.2. Find the square root of 64.3. Find the largest value of number 78.3.4. Find the sign of -30.6 numbers.


16 1 1. Find the length of ‘mathematics’2. Find the position of ‘p’ character in ‘fundamental of

programing’.3. Find the length of EmpName whose EmpID is ‘E005’4. Find the last three character of Empname whose EmpID

is ‘E006’.5. Find substring of city between 2 to 5 whose EmpID is

‘E004’.


17 1 1. Write query to add 4 month in current date2. List the day of that student whose id is ‘E006’.



3. List the day, month and year of those student whosedepartment name is ‘Management’

4. List the invoice date year whose custno is ‘C005’.5. Add the 2 month in employee joining date whose city is

‘Surat’ and salary 10000.

18 1 1. List the employee detail in ascending order ofdesignation and descending order of city

2. List the course detail order by course cost.3. List the item detail order by itemprice and qty on hand

in descending order.4. List the name,address,city from customer table customer

name in descending order


19 1 1. Find the maximum salary of employee2. Find item detail whose price maximum3. Find item detail total price itemprice and qty on hand4. Find the maximum course cost


20 1 1. Find the maximum salary from employee table in eachdepartment.

2. Find out the most recently hired employees in eachdepartment. Order by joiningdate?

3. Get the details an employee who gets maximum salaryin their department.

4. List the student detail in each state and employ id is‘E003’


21 1 1. Find the employee and department detail(natural join)2. Find the student and department detail (evai-join)3. List the customer and invoice detail (left join)4. List the student and studies detail whose city is

‘surat’(right join)


22 1 1. Retrieve the names of all the customer and employeeresiding in the city of ‘Mumbai’. (union)

2. Retrieve the employees have city is ‘surat’ and salary is10000.

3. Retrieve the customer detail whose city start with ‘Sicharacter but not stay on ‘Gujarat’ state


060010102 – database management systemsutu.ac.in/dcst/download/documents/prmscit060010102.pdf ·...

Documents