Download OU (Osmania University) B.Com (Bachelor of Commerce) (Important Questions) Bcom (Computers ,CA,& Hons) MIS Practical Question Bank
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
11
49. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
50. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
***
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
11
49. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
50. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
***
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
-------------------------------------------------------------------------------------------------
B.Com II Sem w.e.f.2016-17
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)
Paper No.207
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
1. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce , FIT, WebProgramming)
i) Insert five records.
ii) calculate total by adding a total column.
iii) calculate average by adding a average column.
2. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce, FIT , C, WP)
i) insert five records.
ii) increase 10 marks in dbms for all students who got less than 50 in dbms.
iii) List the details of student in ascending order of their name.
3.Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
ECommerce, FIT, C,WP)
i) insert 5 records.
ii) calculate percentage by adding a percentage column.
iii) list the student?s name and number who have failed.
4. Create Table Student with the following fields (StudentNumber, StudentName, Hindi , English,
English. Economics, computer , total, average)
i) insert 5 records.
ii) increase 7% marks for all whose average is less than 80.
iii) create a remark column with data according to the following:
a) Average>= 60 (first)
b) Average >=50 &<60 (second)
c) Average >=40 &<50 (third)
5. Create Tableemp with the following fields ( EmpNo , EmpName, Job , Salary , Dept , Comm ,
DateOfJoining)EmpNo should be primary key.
i) Insert 5 records.
ii) increase salary of for all the employees by Rs 1000/- whose salary less than Rs6,000.
iii) List the employee details with highest salary.
6. Create tableemployee with the following fields (EmpNo , Name , Basic , DA , HRA , Net Salary )
i) Insert five records.
ii) Calculate DA -25% of basic ,
iii) Calculate HRA - 45% of basic ,
iv) Calculate net salary
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
11
49. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
50. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
***
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
-------------------------------------------------------------------------------------------------
B.Com II Sem w.e.f.2016-17
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)
Paper No.207
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
1. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce , FIT, WebProgramming)
i) Insert five records.
ii) calculate total by adding a total column.
iii) calculate average by adding a average column.
2. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce, FIT , C, WP)
i) insert five records.
ii) increase 10 marks in dbms for all students who got less than 50 in dbms.
iii) List the details of student in ascending order of their name.
3.Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
ECommerce, FIT, C,WP)
i) insert 5 records.
ii) calculate percentage by adding a percentage column.
iii) list the student?s name and number who have failed.
4. Create Table Student with the following fields (StudentNumber, StudentName, Hindi , English,
English. Economics, computer , total, average)
i) insert 5 records.
ii) increase 7% marks for all whose average is less than 80.
iii) create a remark column with data according to the following:
a) Average>= 60 (first)
b) Average >=50 &<60 (second)
c) Average >=40 &<50 (third)
5. Create Tableemp with the following fields ( EmpNo , EmpName, Job , Salary , Dept , Comm ,
DateOfJoining)EmpNo should be primary key.
i) Insert 5 records.
ii) increase salary of for all the employees by Rs 1000/- whose salary less than Rs6,000.
iii) List the employee details with highest salary.
6. Create tableemployee with the following fields (EmpNo , Name , Basic , DA , HRA , Net Salary )
i) Insert five records.
ii) Calculate DA -25% of basic ,
iii) Calculate HRA - 45% of basic ,
iv) Calculate net salary
7. Create table Emp_detailswith the following fields (EmpNo , Name ,Department , Net Salary )
i) Insert Five records
ii) Display all employee details who work in ?sales? department.
iii)Calculate annual_salary by adding a column.
8. Create table Empwith the following fields (EmpNo (PK), Name ,Department ,Job_Desig, Net
Salary )
i) Insert Five records
ii) Display all employee details who Job_desig=Accountant.
iii)Display the names of employee whose annual salary is more than 3 lacs
9. Create table Employee with the following fields (EmpNo (PK), Name ,Department ,Job_Desig,
Net Salary )
i) Insert Five records
ii)Calculate annual_salary by adding a column .
iii) Delete all employee whose annual_salary is more than 500000
10. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Display supplier details for all suppliers with more than 75000 turnover
iii) Get the details of the supplier who operate from Hyderabad with turnover > 50000
11. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier table in descending order of turnover
iii)Get the total number of suppliers
iv) Get the supplier details whose turnover is between 25000 and 35000
12. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier number of supplier who located Hyderabad
iii) Get the names and cities of supplier whose names begin with ?C?
13. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Increase the turnover by 10% for suppliers who operate from Hyderabad
iii) change the city to ?Bangalore? for S_NO 109
14. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii)Display the average turnover by suppliers in the city Hyderabad
iii) Delete all rows from supplier where city is Kolkata
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
11
49. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
50. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
***
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
-------------------------------------------------------------------------------------------------
B.Com II Sem w.e.f.2016-17
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)
Paper No.207
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
1. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce , FIT, WebProgramming)
i) Insert five records.
ii) calculate total by adding a total column.
iii) calculate average by adding a average column.
2. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce, FIT , C, WP)
i) insert five records.
ii) increase 10 marks in dbms for all students who got less than 50 in dbms.
iii) List the details of student in ascending order of their name.
3.Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
ECommerce, FIT, C,WP)
i) insert 5 records.
ii) calculate percentage by adding a percentage column.
iii) list the student?s name and number who have failed.
4. Create Table Student with the following fields (StudentNumber, StudentName, Hindi , English,
English. Economics, computer , total, average)
i) insert 5 records.
ii) increase 7% marks for all whose average is less than 80.
iii) create a remark column with data according to the following:
a) Average>= 60 (first)
b) Average >=50 &<60 (second)
c) Average >=40 &<50 (third)
5. Create Tableemp with the following fields ( EmpNo , EmpName, Job , Salary , Dept , Comm ,
DateOfJoining)EmpNo should be primary key.
i) Insert 5 records.
ii) increase salary of for all the employees by Rs 1000/- whose salary less than Rs6,000.
iii) List the employee details with highest salary.
6. Create tableemployee with the following fields (EmpNo , Name , Basic , DA , HRA , Net Salary )
i) Insert five records.
ii) Calculate DA -25% of basic ,
iii) Calculate HRA - 45% of basic ,
iv) Calculate net salary
7. Create table Emp_detailswith the following fields (EmpNo , Name ,Department , Net Salary )
i) Insert Five records
ii) Display all employee details who work in ?sales? department.
iii)Calculate annual_salary by adding a column.
8. Create table Empwith the following fields (EmpNo (PK), Name ,Department ,Job_Desig, Net
Salary )
i) Insert Five records
ii) Display all employee details who Job_desig=Accountant.
iii)Display the names of employee whose annual salary is more than 3 lacs
9. Create table Employee with the following fields (EmpNo (PK), Name ,Department ,Job_Desig,
Net Salary )
i) Insert Five records
ii)Calculate annual_salary by adding a column .
iii) Delete all employee whose annual_salary is more than 500000
10. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Display supplier details for all suppliers with more than 75000 turnover
iii) Get the details of the supplier who operate from Hyderabad with turnover > 50000
11. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier table in descending order of turnover
iii)Get the total number of suppliers
iv) Get the supplier details whose turnover is between 25000 and 35000
12. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier number of supplier who located Hyderabad
iii) Get the names and cities of supplier whose names begin with ?C?
13. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Increase the turnover by 10% for suppliers who operate from Hyderabad
iii) change the city to ?Bangalore? for S_NO 109
14. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii)Display the average turnover by suppliers in the city Hyderabad
iii) Delete all rows from supplier where city is Kolkata
15. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit by adding a column
iii) Display the details of product which Is sold in loss.
16. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Display the product details whose selling_price is double the cost_price
iii) Display the product details whose product_name starts with G and quantity is more then 200
17. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Increase the cost and selling price for product_no 107 by 10%
iii) Get product table in descending order on Quantity
iv) Display the total quantity of products available.
18. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Change the cost_price of ?Trousers? to 1000
iii) delete all rows where quantity is greater than 100
19. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii)Get the product details whose selling price is between 2000 to 5000
iii) Increase quantity by 25% for all products whose cost price is less than Rs 250
20. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Get the product number that costs 200,300 or 400
iii) Get the product details whose cost_price is maximum
iv) Count all product whose cost_price is less than 100
21. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit_percentage by adding a column
iii) Get the total number of product in the table
iv) Change the product_name by ?Cap? for productNo 10 and increase the selling price by 5%
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
11
49. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
50. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
***
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
-------------------------------------------------------------------------------------------------
B.Com II Sem w.e.f.2016-17
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)
Paper No.207
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
1. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce , FIT, WebProgramming)
i) Insert five records.
ii) calculate total by adding a total column.
iii) calculate average by adding a average column.
2. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce, FIT , C, WP)
i) insert five records.
ii) increase 10 marks in dbms for all students who got less than 50 in dbms.
iii) List the details of student in ascending order of their name.
3.Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
ECommerce, FIT, C,WP)
i) insert 5 records.
ii) calculate percentage by adding a percentage column.
iii) list the student?s name and number who have failed.
4. Create Table Student with the following fields (StudentNumber, StudentName, Hindi , English,
English. Economics, computer , total, average)
i) insert 5 records.
ii) increase 7% marks for all whose average is less than 80.
iii) create a remark column with data according to the following:
a) Average>= 60 (first)
b) Average >=50 &<60 (second)
c) Average >=40 &<50 (third)
5. Create Tableemp with the following fields ( EmpNo , EmpName, Job , Salary , Dept , Comm ,
DateOfJoining)EmpNo should be primary key.
i) Insert 5 records.
ii) increase salary of for all the employees by Rs 1000/- whose salary less than Rs6,000.
iii) List the employee details with highest salary.
6. Create tableemployee with the following fields (EmpNo , Name , Basic , DA , HRA , Net Salary )
i) Insert five records.
ii) Calculate DA -25% of basic ,
iii) Calculate HRA - 45% of basic ,
iv) Calculate net salary
7. Create table Emp_detailswith the following fields (EmpNo , Name ,Department , Net Salary )
i) Insert Five records
ii) Display all employee details who work in ?sales? department.
iii)Calculate annual_salary by adding a column.
8. Create table Empwith the following fields (EmpNo (PK), Name ,Department ,Job_Desig, Net
Salary )
i) Insert Five records
ii) Display all employee details who Job_desig=Accountant.
iii)Display the names of employee whose annual salary is more than 3 lacs
9. Create table Employee with the following fields (EmpNo (PK), Name ,Department ,Job_Desig,
Net Salary )
i) Insert Five records
ii)Calculate annual_salary by adding a column .
iii) Delete all employee whose annual_salary is more than 500000
10. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Display supplier details for all suppliers with more than 75000 turnover
iii) Get the details of the supplier who operate from Hyderabad with turnover > 50000
11. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier table in descending order of turnover
iii)Get the total number of suppliers
iv) Get the supplier details whose turnover is between 25000 and 35000
12. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier number of supplier who located Hyderabad
iii) Get the names and cities of supplier whose names begin with ?C?
13. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Increase the turnover by 10% for suppliers who operate from Hyderabad
iii) change the city to ?Bangalore? for S_NO 109
14. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii)Display the average turnover by suppliers in the city Hyderabad
iii) Delete all rows from supplier where city is Kolkata
15. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit by adding a column
iii) Display the details of product which Is sold in loss.
16. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Display the product details whose selling_price is double the cost_price
iii) Display the product details whose product_name starts with G and quantity is more then 200
17. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Increase the cost and selling price for product_no 107 by 10%
iii) Get product table in descending order on Quantity
iv) Display the total quantity of products available.
18. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Change the cost_price of ?Trousers? to 1000
iii) delete all rows where quantity is greater than 100
19. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii)Get the product details whose selling price is between 2000 to 5000
iii) Increase quantity by 25% for all products whose cost price is less than Rs 250
20. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Get the product number that costs 200,300 or 400
iii) Get the product details whose cost_price is maximum
iv) Count all product whose cost_price is less than 100
21. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit_percentage by adding a column
iii) Get the total number of product in the table
iv) Change the product_name by ?Cap? for productNo 10 and increase the selling price by 5%
22. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) List all client who are located in Timil Nadu state
iii) Change the city of clientNo ?c005? to Mumbai
23. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) List the client details for all clients whose Bal_due is less than 5000
iii) get the client table details in ascending order of Bal_due
iv) Count the number of clients from the city Chennai
24. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Change the bal_due for clientNo 1099
iii) Delete all clients who are from the city New Delhi
25. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Display the client details whose having the minimum bal_due
iii) Display the total bal_due in the table
iv) delete all clients who are from Maharashtra state
26. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Display all client details for all client whose name starts with M
iii) delete the client tuples whose pin code is 713304
iv) Increase the bal_due by 7% for all whosepin_code= 600013
27. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Display all salesman details who fail to achieve targetamt
iii) count the number of employee in each city
28. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Increase the tagetamt for all by 10%
iii) Delete all employee who fail to achieve targetamt
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
11
49. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
50. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
***
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
-------------------------------------------------------------------------------------------------
B.Com II Sem w.e.f.2016-17
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)
Paper No.207
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
1. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce , FIT, WebProgramming)
i) Insert five records.
ii) calculate total by adding a total column.
iii) calculate average by adding a average column.
2. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce, FIT , C, WP)
i) insert five records.
ii) increase 10 marks in dbms for all students who got less than 50 in dbms.
iii) List the details of student in ascending order of their name.
3.Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
ECommerce, FIT, C,WP)
i) insert 5 records.
ii) calculate percentage by adding a percentage column.
iii) list the student?s name and number who have failed.
4. Create Table Student with the following fields (StudentNumber, StudentName, Hindi , English,
English. Economics, computer , total, average)
i) insert 5 records.
ii) increase 7% marks for all whose average is less than 80.
iii) create a remark column with data according to the following:
a) Average>= 60 (first)
b) Average >=50 &<60 (second)
c) Average >=40 &<50 (third)
5. Create Tableemp with the following fields ( EmpNo , EmpName, Job , Salary , Dept , Comm ,
DateOfJoining)EmpNo should be primary key.
i) Insert 5 records.
ii) increase salary of for all the employees by Rs 1000/- whose salary less than Rs6,000.
iii) List the employee details with highest salary.
6. Create tableemployee with the following fields (EmpNo , Name , Basic , DA , HRA , Net Salary )
i) Insert five records.
ii) Calculate DA -25% of basic ,
iii) Calculate HRA - 45% of basic ,
iv) Calculate net salary
7. Create table Emp_detailswith the following fields (EmpNo , Name ,Department , Net Salary )
i) Insert Five records
ii) Display all employee details who work in ?sales? department.
iii)Calculate annual_salary by adding a column.
8. Create table Empwith the following fields (EmpNo (PK), Name ,Department ,Job_Desig, Net
Salary )
i) Insert Five records
ii) Display all employee details who Job_desig=Accountant.
iii)Display the names of employee whose annual salary is more than 3 lacs
9. Create table Employee with the following fields (EmpNo (PK), Name ,Department ,Job_Desig,
Net Salary )
i) Insert Five records
ii)Calculate annual_salary by adding a column .
iii) Delete all employee whose annual_salary is more than 500000
10. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Display supplier details for all suppliers with more than 75000 turnover
iii) Get the details of the supplier who operate from Hyderabad with turnover > 50000
11. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier table in descending order of turnover
iii)Get the total number of suppliers
iv) Get the supplier details whose turnover is between 25000 and 35000
12. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier number of supplier who located Hyderabad
iii) Get the names and cities of supplier whose names begin with ?C?
13. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Increase the turnover by 10% for suppliers who operate from Hyderabad
iii) change the city to ?Bangalore? for S_NO 109
14. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii)Display the average turnover by suppliers in the city Hyderabad
iii) Delete all rows from supplier where city is Kolkata
15. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit by adding a column
iii) Display the details of product which Is sold in loss.
16. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Display the product details whose selling_price is double the cost_price
iii) Display the product details whose product_name starts with G and quantity is more then 200
17. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Increase the cost and selling price for product_no 107 by 10%
iii) Get product table in descending order on Quantity
iv) Display the total quantity of products available.
18. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Change the cost_price of ?Trousers? to 1000
iii) delete all rows where quantity is greater than 100
19. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii)Get the product details whose selling price is between 2000 to 5000
iii) Increase quantity by 25% for all products whose cost price is less than Rs 250
20. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Get the product number that costs 200,300 or 400
iii) Get the product details whose cost_price is maximum
iv) Count all product whose cost_price is less than 100
21. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit_percentage by adding a column
iii) Get the total number of product in the table
iv) Change the product_name by ?Cap? for productNo 10 and increase the selling price by 5%
22. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) List all client who are located in Timil Nadu state
iii) Change the city of clientNo ?c005? to Mumbai
23. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) List the client details for all clients whose Bal_due is less than 5000
iii) get the client table details in ascending order of Bal_due
iv) Count the number of clients from the city Chennai
24. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Change the bal_due for clientNo 1099
iii) Delete all clients who are from the city New Delhi
25. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Display the client details whose having the minimum bal_due
iii) Display the total bal_due in the table
iv) delete all clients who are from Maharashtra state
26. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Display all client details for all client whose name starts with M
iii) delete the client tuples whose pin code is 713304
iv) Increase the bal_due by 7% for all whosepin_code= 600013
27. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Display all salesman details who fail to achieve targetamt
iii) count the number of employee in each city
28. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Increase the tagetamt for all by 10%
iii) Delete all employee who fail to achieve targetamt
29. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Increase the salesAmtby 12% for employee in the city Indore
iii) change the city to Bhopal for salesmanNo 871
30. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Count the employee who fail to achieve targetamt in the city raipur
iii) get the salesman details in descending order of salesAmt.
iv) List the Salesman details with Maximum SalesAmt.
31. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) create a remark column with data according to the following:
a) TargetAmt>= SalesAmt (VeryGood)
b) TargetAmt
32) Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii ) Display the Maximum course fee along with the Course
iii) Display the name of students, their college name, Principal name where the course is M.C.A..
33) Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii) Count the number of courses taught in TechnoHitec College
iii) Delete all records where college name is TechnoHitec
34. Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii) get the course details in descending order of fee
iii) List the college name whose course fee is greater than 20000 for MBA course.
35) Create a software table with the following fields
( softwarename , Cost, date of manufacture, date of expiry ,NO of copies)
i ) Insert five records
ii ) Display cost and date of manufacture for each software.
iii ) Add a column called software type and enter data into it .
iv) Display the name of software in upper case .
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
11
49. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
50. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
***
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
-------------------------------------------------------------------------------------------------
B.Com II Sem w.e.f.2016-17
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)
Paper No.207
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
1. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce , FIT, WebProgramming)
i) Insert five records.
ii) calculate total by adding a total column.
iii) calculate average by adding a average column.
2. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce, FIT , C, WP)
i) insert five records.
ii) increase 10 marks in dbms for all students who got less than 50 in dbms.
iii) List the details of student in ascending order of their name.
3.Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
ECommerce, FIT, C,WP)
i) insert 5 records.
ii) calculate percentage by adding a percentage column.
iii) list the student?s name and number who have failed.
4. Create Table Student with the following fields (StudentNumber, StudentName, Hindi , English,
English. Economics, computer , total, average)
i) insert 5 records.
ii) increase 7% marks for all whose average is less than 80.
iii) create a remark column with data according to the following:
a) Average>= 60 (first)
b) Average >=50 &<60 (second)
c) Average >=40 &<50 (third)
5. Create Tableemp with the following fields ( EmpNo , EmpName, Job , Salary , Dept , Comm ,
DateOfJoining)EmpNo should be primary key.
i) Insert 5 records.
ii) increase salary of for all the employees by Rs 1000/- whose salary less than Rs6,000.
iii) List the employee details with highest salary.
6. Create tableemployee with the following fields (EmpNo , Name , Basic , DA , HRA , Net Salary )
i) Insert five records.
ii) Calculate DA -25% of basic ,
iii) Calculate HRA - 45% of basic ,
iv) Calculate net salary
7. Create table Emp_detailswith the following fields (EmpNo , Name ,Department , Net Salary )
i) Insert Five records
ii) Display all employee details who work in ?sales? department.
iii)Calculate annual_salary by adding a column.
8. Create table Empwith the following fields (EmpNo (PK), Name ,Department ,Job_Desig, Net
Salary )
i) Insert Five records
ii) Display all employee details who Job_desig=Accountant.
iii)Display the names of employee whose annual salary is more than 3 lacs
9. Create table Employee with the following fields (EmpNo (PK), Name ,Department ,Job_Desig,
Net Salary )
i) Insert Five records
ii)Calculate annual_salary by adding a column .
iii) Delete all employee whose annual_salary is more than 500000
10. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Display supplier details for all suppliers with more than 75000 turnover
iii) Get the details of the supplier who operate from Hyderabad with turnover > 50000
11. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier table in descending order of turnover
iii)Get the total number of suppliers
iv) Get the supplier details whose turnover is between 25000 and 35000
12. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier number of supplier who located Hyderabad
iii) Get the names and cities of supplier whose names begin with ?C?
13. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Increase the turnover by 10% for suppliers who operate from Hyderabad
iii) change the city to ?Bangalore? for S_NO 109
14. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii)Display the average turnover by suppliers in the city Hyderabad
iii) Delete all rows from supplier where city is Kolkata
15. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit by adding a column
iii) Display the details of product which Is sold in loss.
16. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Display the product details whose selling_price is double the cost_price
iii) Display the product details whose product_name starts with G and quantity is more then 200
17. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Increase the cost and selling price for product_no 107 by 10%
iii) Get product table in descending order on Quantity
iv) Display the total quantity of products available.
18. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Change the cost_price of ?Trousers? to 1000
iii) delete all rows where quantity is greater than 100
19. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii)Get the product details whose selling price is between 2000 to 5000
iii) Increase quantity by 25% for all products whose cost price is less than Rs 250
20. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Get the product number that costs 200,300 or 400
iii) Get the product details whose cost_price is maximum
iv) Count all product whose cost_price is less than 100
21. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit_percentage by adding a column
iii) Get the total number of product in the table
iv) Change the product_name by ?Cap? for productNo 10 and increase the selling price by 5%
22. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) List all client who are located in Timil Nadu state
iii) Change the city of clientNo ?c005? to Mumbai
23. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) List the client details for all clients whose Bal_due is less than 5000
iii) get the client table details in ascending order of Bal_due
iv) Count the number of clients from the city Chennai
24. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Change the bal_due for clientNo 1099
iii) Delete all clients who are from the city New Delhi
25. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Display the client details whose having the minimum bal_due
iii) Display the total bal_due in the table
iv) delete all clients who are from Maharashtra state
26. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Display all client details for all client whose name starts with M
iii) delete the client tuples whose pin code is 713304
iv) Increase the bal_due by 7% for all whosepin_code= 600013
27. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Display all salesman details who fail to achieve targetamt
iii) count the number of employee in each city
28. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Increase the tagetamt for all by 10%
iii) Delete all employee who fail to achieve targetamt
29. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Increase the salesAmtby 12% for employee in the city Indore
iii) change the city to Bhopal for salesmanNo 871
30. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Count the employee who fail to achieve targetamt in the city raipur
iii) get the salesman details in descending order of salesAmt.
iv) List the Salesman details with Maximum SalesAmt.
31. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) create a remark column with data according to the following:
a) TargetAmt>= SalesAmt (VeryGood)
b) TargetAmt
32) Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii ) Display the Maximum course fee along with the Course
iii) Display the name of students, their college name, Principal name where the course is M.C.A..
33) Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii) Count the number of courses taught in TechnoHitec College
iii) Delete all records where college name is TechnoHitec
34. Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii) get the course details in descending order of fee
iii) List the college name whose course fee is greater than 20000 for MBA course.
35) Create a software table with the following fields
( softwarename , Cost, date of manufacture, date of expiry ,NO of copies)
i ) Insert five records
ii ) Display cost and date of manufacture for each software.
iii ) Add a column called software type and enter data into it .
iv) Display the name of software in upper case .
36) Create a Faculty table with following fields
( Name , Specialization , Experience ,Age )
i ) Insert five records
ii ) Display the name in lower case
iii ) Add a column called Grade
iv ) If the experience is more than five years assign the grade as ?? A ?? else ?B?
37) create table Loan with the following fields (IoadId, Cust_name, Cust_city, Cust_phone,
Loan_amt, Rate_of_int)
i ) Insert five records
ii) calculate the Interest amount by adding a column
iii) List all the customer details whose Loan_amt> 800000
iv) Display the details of loan in descending order of Loan_amt
38) create table Loan with the following fields
(IoadId, Cust_name, Cust_city, Cust_phone, Loan_amt, Rate_of_int)
i ) Insert five records
ii) decreaserate_of_int by 2% for all loan amount> 700000
iii) Delete all tuples where cust_city is hubly
39) create table Loan with the following fields (IoadId, Cust_name, Cust_city, Cust_phone,
Loan_amt, Rate_of_int)
i ) Insert five records
ii) Count number of people whose loan_amt exceeds 50000
iii) List the customer details who is having maximum Loan_amt
40) create table Loan with the following fields (IoadId, Cust_name, Cust_city, Cust_phone,
Loan_amt, Rate_of_int)
i ) Insert five records
ii) List all customer details whose loan amount is between 200000 and 500000
iii) Display the Loan details for all in the city hyderabad
41. create the table book with following details (bookid, author, title, price, quantity)
i ) Insert five records
ii) Display the books of title rdbms
iii) delete all rows where author name is MattWall
[[
42. create the table book with following details (bookid, author, title, price, quantity)
i ) Insert five records
ii) Which title of book is having maximum quantity
iii) Increase the price all books by 10% whose author is Ramakrishna
43.create the table book with following details (bookid, author, title, price, quantity)
i ) Insert five records
ii) Display the book with maximum price
iii) Change the title of bookid 955 to ?C Skills?
FirstRanker.com - FirstRanker's Choice
1
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
------------------------------------------------------------------------------------------
MANAGEMENT INFORMATION SYSTEM PRACTICAL QUSTION BANK
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
MS ACCESS:
1. Create a Student Table in MS-Access with the following features:
a. Roll Number
b. Student Name
c. Class
d. Marks in Subject 1, Subject 2, Subject 3
e. Total
f. Average
g. Result
2. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
3. Create an Inventory table in MS-Access with fields:
a. Poduct ID
b. Product Name
c. Product Quantity
d. Product Price
4. Create a database ?College? and table ??Student?. The following are the details of the
table:
5. Create a database ?Student?. Also create table ?studtable? and execute the following
queries
Number Fname Lname Address Home phone Joining date
101 Ram Prasad 11-1-963/A, Banjara
Hills, Hyderabad
040-23343256 02/06/2016
102 Shyam Singh 12-1-254/B, Sultan
Bazar, Hyderabad
040-23345679 05/06/2016
103 Ankur Agrawal 11-2-667/A, Vidhya
Nagar, Hyderabad
040-23358796 03/06/2016
104 Ramesh Sharma 10-2-340, Road
No.12, Banjara Hills,
Hyderabad
040-23389745 04/06/2016
105 Harish Shekhawat 15-4-633/A, Begum
Bazar, Hyderabad
040-23356486 02/06/2016
2
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 89 77 82 78 82 86
104 Harish 75 72 70 65 60 77
105 Rajesh 86 85 81 80 89 90
a) Sort the data by Sname.
b) Calculate Total Marks of Individual Students.
c) Calculate Percentage Marks of Individual Student.
6. Create a database ?Student1?. Also create table ?stdtable? and execute the following
queries
SRollno Sname English SL FA-I BE BOM IT
101 Rajendra 75 85 90 85 86 90
102 Arvind 88 75 95 80 82 85
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display all Students who got >=80 Marks in Any Subject.
b) Display all Students who got >=80% Marks in Total.
c) Display all Students who got <=60% Marks in Total.
7. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
8. Create a database ?Student3?. Also create table ?stdmarks1? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 90 95 92 90 88 90
102 Arvind 48 52 48 50 53 54
103 Krishna 85 88 89 85 95 90
104 Harish 12 10 15 12 10 15
105 Rajesh 60 66 75 77 65 79
a) Update table by adding Grade as per the following:-
i. Total %age >=90% Grade ?E?
3
ii. Total %age >=75% Grade ?A?
iii. Total %age >=60% Grade ?B?
iv. Total %age >=50% Grade ?C?
v. Total %age >=36% Grade ?D?
vi. Total %age <36% Grade ?F?
9. Create ?Employee? database and table ?Emp?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) Calculate DA as 40% of Basic Salary.
b) Calculate Hra as 15% of Basic Salary.
c) Calculate PF as 12.67% of Basic Salary
d) Calculate Gross Pay = Basic Salary + DA + HRA
e) Calculate Net Pay = Gross Pay ? PF.
10. Create ?Employee1? database and table ?Emp1?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
a) List all the Employees Who are working in Dept no.10
b) List all the Employees who get less than 20000 Salary.
11. Create ?Employee2? database and table ?Emp2?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
4
a) Update Salary by adding the increments as per the following:-
a. 10% Increment in Basic Salary who get < 20000
b. 5% Increment in Basic Salary who get >20000.
12. Create ?Employee3? database and table ?Emp3?
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
Generate the query to find out the following:-
a) Total Salary paid by the Company to Employees
b) Maximum Salary paid by the Company to Employees
c) Minimum Salary paid by the Company to Employees
13. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
14. Create ?Course? database and ?Coursedtl? table having following records:-
SRollno Sname Course Section Medium College Total Fees
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
a) Prepare the report to display lowest course fees with college name.
b) Prepare the Report to display all students who are pursuing B.Com(Computers).
15. In the following table generate the queries for the followings:-
a. Display the Course and College having Lowest Course Fees.
b. Display all the courses pursuing by the PQR College.
SRollno Sname Course Section Medium College Total Fees
5
16. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
17. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
18. Create the ?Order? database and a table ?Orderdtl? having following records:-
Order
No
Order Date Order
Item
Order
Qty
Order
Price
Client
Code
Delivery
Type
Order
Status
1011 12/02/2015 LED
Monitors
100 7500 1025 Road Delivered
1012 25/02/2015 Printers 75 8500 1025 Road Delivered
1013 25/03/2015 CPU 100 15000 1045 Road Delivered
1014 31/03/2015 LED
Monitors
75 6500 1045 Road Not
Delivered
1015 31/03/2015 CPU 75 18000 1025 Road Not
Delivered
Generate the following reports from the above table:-
a) Display all the Order No. which have not been yet Delivered.
b) Display all the Orders of LED Monitor and CPU.
c) Display all the Orders of LED Monitor and CPU which are not have
been delivered yet.
19. Create a a ?Sales? Database and a table ?Salesdtl? having following fields and data:-
SalesNo. Salesman
Code
Salesman
Name
Branch Sale
Amount
1011 101 Arvind Jaipur 75000
1012 102 Ashok Jaipur 80000
1013 103 Harish Hyderabad 75000
1014 104 Manish Hyderabad 65000
101 Rajendra B.Com General English ABC 5500
102 Arvind B.Com General English PQR 15000
103 Krishna B.Com General Telugu ABC 5500
104 Harish B.Com General Telugu PQR 7500
105 Rajesh B.Com Computers English ABC 15000
106 Harish B.Com Computers English PQR 25000
6
1015 105 Krish Secunderabad 78000
20. Generate the queries to find out the followings from the above ?Sales? Table:
a) Total sale of all the Branches.
b) Total sale group by Branches.
c) Maximum sale of individual branch.
d) Maximum sale in all branches.
21. Create an ?Stock? database having an ?Inventory? Table as per the followings:-
ProductCode Product
Name
Purchase
Quantity
Purchase
Price (Per
Unit)
1011 CPU 200 15000
1012 Monitor 100 7500
1013 Printer 75 5500
1014 UPS 200 3500
1015 Speakers 200 250
22. Generate the following queries from the above ?Inventory ?Table:-
a) Item wise total purchase price.
b) Display new purchase price after adding 15% increase in purchase price.
c) Display new Purchase quantity after deducting 5% from the quantity
23. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
24. Create a database ?Student2?. Also create table ?stdmarks? and execute the following
queries
SRollno Sname English SL FA BE BOM FIT
101 Rajendra 22 20 23 20 23 20
102 Arvind 20 21 20 23 20 20
103 Krishna 50 52 58 50 53 54
104 Harish 52 55 51 56 57 59
7
105 Rajesh 86 85 81 80 89 90
a) Display All the students who got <25 in individual subject.
b) Display all the students who are Failed in individual Subject.
c) Increase marks by adding 5 marks whose marks are <25.
25. Create a Personal Information table in MS-Access with the following fields:
a. First Name
b. Middle Name
c. Last Name
d. Nationality
e. Gender
f. Temporary Address
g. Permanent Address. Generate a Form and Report.
26. Create a table (student) in access with (name,fathername,gender,DOB,phno,address)
. insert 10 records.
27. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
28. Create a table (person) in access with name,phno,email-id and address . insert 10
records.
Create a query to display email id,phno and address when user gives name.
29. Create a SALES table in access with
(ID,productname,quantity,salesrate,commission,tax)insert 10 records . add new
column affiliate earnings. Write a query to calculate affiliate earnings.
calculate affiliate earnings=(quantity*salesrate)+commission-tax.
30. Create a database Club and table Member. The following are the details of the table.
Number Fname Lname Address Home phone Joining date
S1465T Jone Johnson 1234 Country Club
Texas
(713)-555-7890 01-02-04
J1050S Bill Smith 1112 Peter Avenue
Texas
(713)-556-6556 30-11-04
S1300T Lisa Stanley 985 Venton Circle
Texas
(713)-558-1227 11-11-04
31. Create a employee database and table ?emp?.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
8
32. Create a database ?Student?. Also Create table ?stdtable?& execute the following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
33. create ?employee database and table ?emp? create a Report.
Emp.No Ename Job DOJ Basic Deptno Sex Due prom
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
34. create a database ?Library? and create a table as ?Books? and execute the queries
given below:
Author Lname Author Fname Book title Book type Year of publication
Gupta Sharma Management
Accounting
Accounting 2002
Nain Patel Financial Accounting 2000
Reddy Ram Corporate
accounting
Accounting 1990
a) Display Book title, Book type, author name where author = Ram and book type =
Accounting.
b) Display Author Fname, Book type year of publication where year above 2000.
35. Create Business table by using Design Wizard with year own data (atleast 6 fields
and 10 records)
36. Create Personal table by using Design Wizard with your own data (atleast 6 fields
and 10 records)
37. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
9
105 Rajesh B.Com-I MP 02-02-83 45 C
38. Create ?Student database table ?and also create a Report .
SRoll no Sname Class State DOB Marks Grade
1 Rajesh B.Com-I AP 21-09-84 81 A
2 Ramesh B.Com-II MP 31-08-85 72 B
3 Rakesh B.Com-III UP 30-06-89 90 A
4 Suresh B.Com-II AP 25-07-82 36 B
5 Vignesh B.Com-I MP 02-02-83 45 C
39. Create a employee database and Create a Report .
Emp.No Ename Job DOJ Basic Deptno Sex
101 Ajya Clerk 17-12-01 6000 20 Male
102 Arvind Salesmen 20-02-02 5000 30 Male
103 Rahul Salesmen 22-03-01 5500 20 Male
104 Rehman Manager 01-04-01 12000 40 Male
105 Sahil Manager 20-09-02 11500 10 Male
40. Create table textbook with the following fields. Insert 10 records.
Book title, author name, publication name, quantity, price.
Create query to display the books of information technology,
Create query to display the books of Himalaya publications.
41. Create a table student with the following fields(rno, name, fa marks, be marks, it
marks, total and average). Insert 10 records, calculate total = fa+be+it. Average =
total/ no of subjects.
Create a query to display all information of student who secured >75 marks in IT
subject.
Create a query to display name, total and average who?s average is > 80.
42. Create a table employ with (idno, name, job, age, salary). Insert 10 records.
Create a query to display the information of all managers.
Create a query to display the names of employs who?s salary is >15000.
43. Create a report for the student table with rno, name, fa, be, it total and average.
44. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
10
45. Create a ?Stock? database having ?Inventory? table:-
Item Code Name of the
Item
Opening
Stock
(In Qty)
Purchase
( In Qty)
Sales
(In Qty)
1011 CPU 200 150 300
1012 Monitor 100 100 150
1013 Printer 75 150 125
1014 UPS 200 300 400
1015 Speakers 200 250 350
Generate the following queries from the above table:-
a) Calculate the closing stock of each item (Closing Stock = Opening Stock +
Purchase ? Sales)
b) Display all the Items which has closing stock < 100
c) If closing stock is less than 100 then set the remark as ?Re-Order Level? otherwise
?Enough Stock?.
46. Create a FEE table in access with (name,category,course/group,year, fees,)insert 10
records . add new column concession. Write a query to calculate concession.
calculate concession=fee*20/100; when category is NCC/NSS/GIRL
47. Create a database ?Student?. Also Create table ?stdtable?& execute the
following.
SRoll no Sname Class State DOB Marks Grade
101 Raj B.Com-I AP 21-09-82 80 A
105 Ram B.Com-II MP 31-08-81 70 B
104 Rakesh B.Com-III UP 30-06-80 90 A
103 Ramesh B.Com-II AP 25-07-82 38 C
105 Rajesh B.Com-I MP 02-02-83 45 C
a) Sort the data by SRoll no.
b) Display SRoll no., Sname, State and Marks where marks > 80.
c) Display SRoll no., Sname, State and Marks where Grade = A.
48. In the following table find out the Followings:
a. All the Department Nos and Employees Names.
b. All the Employees who joined in year 2001.
Empno Ename Designation Deptno DOJ Basic
Salary
101 Ajay Manager 10 12/12/2001 25000
102 Arvind Dy. Manager 40 20/02/2002 20000
103 Rahul Programmer 20 22/03/2001 18000
104 Hari Mktg. Officer 40 01/04/2001 15000
105 Sahil Accountant 10 20-09-2000 12000
11
49. Create a Library Database of your college having following fields and enter atleast 30
records in it:-
Field Name Description
BookCode Primary Key
BookTitle Subject Name
Publisher Book Publisher Name
No.of.Copies Quantity in one Invoice
No.ofPages Total Pages in book
Edition Book Printing Edition
50. From the College Library database find out the following:-
a. Total no. of copies of books subject wise.
b. A report displays all books group by Publisher.
c. A report displays all books group by Book Title.
d. A report displays all books group by Book Edition
***
Computer Lab - Practical Question Bank
FACULTY OF COMMERCE, OSMANIA UNIVERSITY
-------------------------------------------------------------------------------------------------
B.Com II Sem w.e.f.2016-17
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)
Paper No.207
Time: 60 Minutes Record : 10
Skill Test : 15
Total Marks : 25
1. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce , FIT, WebProgramming)
i) Insert five records.
ii) calculate total by adding a total column.
iii) calculate average by adding a average column.
2. Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
Ecommerce, FIT , C, WP)
i) insert five records.
ii) increase 10 marks in dbms for all students who got less than 50 in dbms.
iii) List the details of student in ascending order of their name.
3.Create Table Student with the following fields (StudentNumber, StudentName, DBMS ,
ECommerce, FIT, C,WP)
i) insert 5 records.
ii) calculate percentage by adding a percentage column.
iii) list the student?s name and number who have failed.
4. Create Table Student with the following fields (StudentNumber, StudentName, Hindi , English,
English. Economics, computer , total, average)
i) insert 5 records.
ii) increase 7% marks for all whose average is less than 80.
iii) create a remark column with data according to the following:
a) Average>= 60 (first)
b) Average >=50 &<60 (second)
c) Average >=40 &<50 (third)
5. Create Tableemp with the following fields ( EmpNo , EmpName, Job , Salary , Dept , Comm ,
DateOfJoining)EmpNo should be primary key.
i) Insert 5 records.
ii) increase salary of for all the employees by Rs 1000/- whose salary less than Rs6,000.
iii) List the employee details with highest salary.
6. Create tableemployee with the following fields (EmpNo , Name , Basic , DA , HRA , Net Salary )
i) Insert five records.
ii) Calculate DA -25% of basic ,
iii) Calculate HRA - 45% of basic ,
iv) Calculate net salary
7. Create table Emp_detailswith the following fields (EmpNo , Name ,Department , Net Salary )
i) Insert Five records
ii) Display all employee details who work in ?sales? department.
iii)Calculate annual_salary by adding a column.
8. Create table Empwith the following fields (EmpNo (PK), Name ,Department ,Job_Desig, Net
Salary )
i) Insert Five records
ii) Display all employee details who Job_desig=Accountant.
iii)Display the names of employee whose annual salary is more than 3 lacs
9. Create table Employee with the following fields (EmpNo (PK), Name ,Department ,Job_Desig,
Net Salary )
i) Insert Five records
ii)Calculate annual_salary by adding a column .
iii) Delete all employee whose annual_salary is more than 500000
10. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Display supplier details for all suppliers with more than 75000 turnover
iii) Get the details of the supplier who operate from Hyderabad with turnover > 50000
11. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier table in descending order of turnover
iii)Get the total number of suppliers
iv) Get the supplier details whose turnover is between 25000 and 35000
12. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Get the supplier number of supplier who located Hyderabad
iii) Get the names and cities of supplier whose names begin with ?C?
13. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii) Increase the turnover by 10% for suppliers who operate from Hyderabad
iii) change the city to ?Bangalore? for S_NO 109
14. Create table supplier with the following fields ( S_NO (PK) ,SNAME, SCITY, TURNOVER)
i) Insert Five records
ii)Display the average turnover by suppliers in the city Hyderabad
iii) Delete all rows from supplier where city is Kolkata
15. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit by adding a column
iii) Display the details of product which Is sold in loss.
16. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Display the product details whose selling_price is double the cost_price
iii) Display the product details whose product_name starts with G and quantity is more then 200
17. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Increase the cost and selling price for product_no 107 by 10%
iii) Get product table in descending order on Quantity
iv) Display the total quantity of products available.
18. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Change the cost_price of ?Trousers? to 1000
iii) delete all rows where quantity is greater than 100
19. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii)Get the product details whose selling price is between 2000 to 5000
iii) Increase quantity by 25% for all products whose cost price is less than Rs 250
20. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price, Quantity)
i) Insert Five records
ii) Get the product number that costs 200,300 or 400
iii) Get the product details whose cost_price is maximum
iv) Count all product whose cost_price is less than 100
21. Create table product with the following fields (ProductNo (pk), Product_name, Cost_price,
Selling_price)
i) Insert Five records
ii) Calculate profit_percentage by adding a column
iii) Get the total number of product in the table
iv) Change the product_name by ?Cap? for productNo 10 and increase the selling price by 5%
22. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) List all client who are located in Timil Nadu state
iii) Change the city of clientNo ?c005? to Mumbai
23. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) List the client details for all clients whose Bal_due is less than 5000
iii) get the client table details in ascending order of Bal_due
iv) Count the number of clients from the city Chennai
24. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Change the bal_due for clientNo 1099
iii) Delete all clients who are from the city New Delhi
25. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Display the client details whose having the minimum bal_due
iii) Display the total bal_due in the table
iv) delete all clients who are from Maharashtra state
26. Create table client with the following fields (ClientNo (pk) , name, City, State,
PinCode,Bal_Due)
i) Insert Five records
ii) Display all client details for all client whose name starts with M
iii) delete the client tuples whose pin code is 713304
iv) Increase the bal_due by 7% for all whosepin_code= 600013
27. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Display all salesman details who fail to achieve targetamt
iii) count the number of employee in each city
28. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Increase the tagetamt for all by 10%
iii) Delete all employee who fail to achieve targetamt
29. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Increase the salesAmtby 12% for employee in the city Indore
iii) change the city to Bhopal for salesmanNo 871
30. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) Count the employee who fail to achieve targetamt in the city raipur
iii) get the salesman details in descending order of salesAmt.
iv) List the Salesman details with Maximum SalesAmt.
31. Create table salesman with the following fields (SalesmanNo, name, Address, City, SalesAmt,
targetAmt)
i) Insert Five records
ii) create a remark column with data according to the following:
a) TargetAmt>= SalesAmt (VeryGood)
b) TargetAmt
32) Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii ) Display the Maximum course fee along with the Course
iii) Display the name of students, their college name, Principal name where the course is M.C.A..
33) Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii) Count the number of courses taught in TechnoHitec College
iii) Delete all records where college name is TechnoHitec
34. Create a Course table with the following fields
( Student Name, Course ,College ,College Address,,Principal Name ,Fee )
i ) Insert five records
ii) get the course details in descending order of fee
iii) List the college name whose course fee is greater than 20000 for MBA course.
35) Create a software table with the following fields
( softwarename , Cost, date of manufacture, date of expiry ,NO of copies)
i ) Insert five records
ii ) Display cost and date of manufacture for each software.
iii ) Add a column called software type and enter data into it .
iv) Display the name of software in upper case .
36) Create a Faculty table with following fields
( Name , Specialization , Experience ,Age )
i ) Insert five records
ii ) Display the name in lower case
iii ) Add a column called Grade
iv ) If the experience is more than five years assign the grade as ?? A ?? else ?B?
37) create table Loan with the following fields (IoadId, Cust_name, Cust_city, Cust_phone,
Loan_amt, Rate_of_int)
i ) Insert five records
ii) calculate the Interest amount by adding a column
iii) List all the customer details whose Loan_amt> 800000
iv) Display the details of loan in descending order of Loan_amt
38) create table Loan with the following fields
(IoadId, Cust_name, Cust_city, Cust_phone, Loan_amt, Rate_of_int)
i ) Insert five records
ii) decreaserate_of_int by 2% for all loan amount> 700000
iii) Delete all tuples where cust_city is hubly
39) create table Loan with the following fields (IoadId, Cust_name, Cust_city, Cust_phone,
Loan_amt, Rate_of_int)
i ) Insert five records
ii) Count number of people whose loan_amt exceeds 50000
iii) List the customer details who is having maximum Loan_amt
40) create table Loan with the following fields (IoadId, Cust_name, Cust_city, Cust_phone,
Loan_amt, Rate_of_int)
i ) Insert five records
ii) List all customer details whose loan amount is between 200000 and 500000
iii) Display the Loan details for all in the city hyderabad
41. create the table book with following details (bookid, author, title, price, quantity)
i ) Insert five records
ii) Display the books of title rdbms
iii) delete all rows where author name is MattWall
[[
42. create the table book with following details (bookid, author, title, price, quantity)
i ) Insert five records
ii) Which title of book is having maximum quantity
iii) Increase the price all books by 10% whose author is Ramakrishna
43.create the table book with following details (bookid, author, title, price, quantity)
i ) Insert five records
ii) Display the book with maximum price
iii) Change the title of bookid 955 to ?C Skills?
44. create the table book with following details (bookid, author, title, price, quantity)
i ) Insert five records
ii) display the book details in ascending order for title
iii) calculate total price by adding a column
45. create the table book with following details (bookid, author, title, price, quantity)
i ) Insert five records
ii) Increase the quantity of all books of rdbms by 10%
iii)calculate the total number of books.
46. create table purchase with the following details (orderid, productName, unit_price, quantity)
i ) Insert five records
ii) Calculate total_price by adding a column
iii) Which product is purchased in maximum quantity
47. create table purchase with the following details (orderid, productName, unit_price, quantity)
i ) Insert five records
ii) increaseunit_price by 5% for all products
iii) display the productnameswhose unit_price is more than 440
48. create table purchase with the following details (orderid, productName, unit_price, quantity)
i ) Insert five records
ii) Delete all records whose quantity is less than 5
iii) increase the quantity by 15% for Productname Desks
49. create table purchase with the following details (orderid, productName, unit_price, quantity)
i ) Insert five records
ii) Display the OrderId and Product name whose unit price is least
iii) display the purchase detail in ascending order of product_name
50. create table purchase with the following details (orderid, productName, unit_price, quantity)
i ) Insert five records
ii) Increase the unit price by Rs100/- for productName Chord
iii) Which product is having the minimum quantity+
@@@@
FirstRanker.com - FirstRanker's Choice
This post was last modified on 27 January 2020