BCA(H) D-I Paper 1(b) Practical exercises based on MS Office/Open office

                                                                                 MS Word

1. Prepare a grocery list having four columns (Serial number, The name of the product, quantity and price) for the month of April, 06.

• Font specifications for Title (Grocery List): 14-point Arial font in bold and italics.

• The headings of the columns should be in 12-point and bold.

• The rest of the document should be in 10-point Times New Roman.

• Leave a gap of 12-points after the title.

2. Create a telephone directory.

• The heading should be 16-point Arial Font in bold

• The rest of the document should use 10-point font size

• Other headings should use 10-point Courier New Font.

• The footer should show the page number as well as the date last updated.

3. Design a time-table form for your college.

• The first line should mention the name of the college in 16-point Arial Font and should be bold.

• The second line should give the course name/teacher‘s name and the department in 14-point Arial.

• Leave a gap of 12-points.

• The rest of the document should use 10-point Times New Roman font.

• The footer should contain your specifications as the designer and date of creation.

4. BPB Publications plans to release a new book designed as per your syllabus. Design the first page of the book as per the given specifications.

• The title of the book should appear in bold using 20-point Arial font.

• The name of the author and his qualifications should be in the center of the page in 16-point Arial font.

• At the bottom of the document should be the name of the publisher and address in 16-point Times New Roman.

• The details of the offices of the publisher (only location) should appear in the footer.

5. Create the following one page documents. a. Compose a note inviting friends to a get-together at your house, Including a list of things to bring with them.

b. Design a certificate in landscape orientation with a border around the document.

c. Design a Garage Sale sign.

d. Make a sign outlining your rules for your bedroom at home, using a numbered list.

6. Create the following documents: (a) A newsletter with a headline and 2 columns in portrait orientation, including at least one image surrounded by text.

(b) Use a newsletter format to promote upcoming projects or events in your classroom or college.

7. Convert following text to a table, using comma as delimiter

Type the following as shown (do not bold).

Color, Style, Item

Blue, A980, Van

Red, X023, Car

Green, YL724, Truck

Name, Age, Sex

Bob, 23, M

Linda, 46, F

Tom, 29, M

9. Enter the following data into a table given on the next page.

Salesperson	Dolls	Trucks	Puzzles
Kennedy, Sally	1327	1423	1193
White, Pete	1421	3863	2934
Pillar, James	5214	3247	5467
York, George	2190	1278	1928
Banks, Jennifer	1201	2528	1203
Atwater, Kelly	4098	3079	2067

Add a column Region (values: S, N, N,S,S,S) between the Salesperson and Dolls columns to the given table Sort your table data by Region and within Region by Salesperson in ascending order:

In this exercise, you will add a new row to your table, place the word "Total" at the bottom of the Salesperson column, and sum the Dolls, Trucks, and Puzzles columns.

10. Wrapping of text around the image.

11. Following features of menu option must be covered

FILE Complete menu

EDIT Complete menu

VIEW Complete menu

INSERT Complete menu

FORMAT Complete menu

TABLE Complete menu

WINDOW Complete menu

HELP Complete menu

TOOLS All options except Online collaboration, Tools on Macro, Templates

MS Excel

1. Enter the Following data in Excel Sheet

REGIONAL SALES PROJECTION
State	Qtr1		Qtr2	Qtr3	QTR4	Qtr Total	Rate Amount
Delhi		2020	2400	2100	3000	15
Punjab		1100	1300	1500	1400	20
U.P.		3000	3200	2600	2800	17
Haryana		1800	2000	2200	2700	15
Rajasthan		2100	2000	1800	2200	20

TOTAL

AVERAGE

(a) Apply Formatting as follow:

I.Title in TIMES NEW ROMAN

ii. Font Size - 14

iii. Remaining text - ARIAL, Font Size -10

iv. State names and Qtr. Heading Bold, Italic with Gray Fill Color.

v. Numbers in two decimal places.

vi. Qtr. Heading in center Alignment.

vii. Apply Border to whole data.

(b) Calculate State and Qtr. Total

(c) Calculate Average for each quarter

(d) Calculate Amount = Rate * Total.

2. Given the following worksheet
A	B		C	D
1		Roll No.	Name	Marks	Grade
2	1001		Sachin	99
3	1002		Sehwag	65
4	1003		Rahul	41
5	1004		Sourav	89
6	1005		Har Bhajan	56

Calculate the grade of these students on the basis of following guidelines:

If Marks	Then Grade
>= 80	A+
>= 60 < 80	A
>= 50 < 60	B
< 50	F

3. Given the following worksheet

	A	B			C	D		E	F	G
1	Salesman	Sales in (Rs.)
2	No.	Qtr1	Qtr2				Qtr3	Qtr4	Total		Commission
3	S001	5000		8500			12000	9000
4	S002	7000		4000			7500	11000
5	S003	4000		9000			6500	8200
6	S004	5500		6900			4500	10500
7	S005	7400		8500			9200	8300
8	S006	5300		7600			9800	6100

Calculate the commission earned by the salesmen on the basis of following Candidates:

If Total Sales	Commission
< 20000	0% of sales
> 20000 and < 25000	4% of sales
> 25000 and < 30000	5.5% of sales
> 30000 and < 35000	8% of sales
>= 35000	11% of sales

The total sales is sum of sales of all the four quarters.

4. A company XYZ Ltd. pays a monthly salary to its employees which consists of basic salary, allowances & deductions. The details of allowances and deductions are as follows:

Allowances

• HRA Dependent on Basic

30% of Basic if Basic <=1000

25% of Basic if Basic>1000 & Basic<=3000

20% of Basic if Basic >3000

• DA Fixed for all employees, 30% of Basic

• Conveyance Allowance Rs. 50/- if Basic is <=1000 Rs. 75/- if Basic >1000 & Basic<=2000

Rs. 100 if Basic >2000

• Entertainment Allowance NIL if Basic is <=1000 Rs. 100/- if Basic > 1000

Deductions

• Provident Fund 6% of Basic

• Group Insurance Premium Rs. 40/- if Basic is <=1500

Rs. 60/- if Basic > 1500 & Basic<=3000

Rs. 80/- if Basic >3000

Calculate the following:

Gross Salary = Basic + HRA + DA + Conveyance + Entertainment

Total deduction = Provident Fund + Group Insurance Premium

Net Salary = Gross Salary – Total Deduction

5. Create Payment Table for a fixed Principal amount, variable rate of interests and time in the format below:

No. of Instalments	5%	6%	7%	8%	9%
3	XX	XX	XX	XX	XX
4	XX	XX	XX	XX	XX
5	XX	XX	XX	XX	XX
6	XX	XX	XX	XX	XX

6. Use an array formula to calculate Simple Interest for given principal amounts given the rate of Interest and time

Rate of Interest	8%
Time	5 Years
Principal	Simple Interest
1000	?
18000	?
5200	?

7. The following table gives year wise sale figure of five salesmen in Rs.

Salesman	2000	2001	2002	2003
S1	10000	12000	20000	50000
S2	15000	18000	50000	60000
S3	20000	22000	70000	70000
S4	30000	30000	100000	80000
S5	40000	45000	125000	90000

(a)	Calculate total sale year wise.
(b)	Calculate the net sale made by each salesman
(c)	Calculate the maximum sale made by the salesman
(d)	Calculate the commission for each salesman under the condition.
(i) If total sales >4,00,000 give 5% commission on total sale made by the salesman.
(ii) Otherwise give 2% commission.
(e)	Draw a bar graph representing the sale made by each salesman.
(f)	Draw a pie graph representing the sale made by salesman in 2000.

8. Enter the following data in Excel Sheet

TOTAL REVENUE EARNED FOR SAM’S BOOKSTALL

Publisher name	1997	1998	1999	2000	total
A	Rs. 1,000.00	Rs. 1100.00	Rs. 1,300.00	Rs. 800.00
B	Rs.1,500.00	Rs. 700.00	Rs. 1,000.00	Rs. 2,000.00
C	Rs. 700.00	Rs. 900.00	Rs. 1,500.00	Rs. 600.00
D	Rs.1,200.00	Rs. 500.00	Rs. 200.00	Rs. 1,100.00
E	Rs 800.00	Rs. 1,000.00	Rs. 3,000.00	Rs. 560.00

(a) Compute the total revenue earned.

(b) Plot the line chart to compare the revenue of all publisher for 4 years.

(b) Chart Title should be ‗Total Revenue of sam‘s Bookstall (1997-2000)‘

(c) Give appropriate categories and value axis title.

9. Generate 25 random numbers between 0 & 100 and find their sum, average and count. How many no. are in range 50-60

                                                           Database  Lab Using MS -Access

1) Create a database having two tables with the specified fields, to computerize a library system of a Delhi University College.

LibraryBooks (Accession number, Title, Author, Department, PurchaseDate, Price) IssuedBooks (Accession number, Borrower)

a) Identify primary and foreign keys. Create the tables and insert at least 5 records in each table.

b) Delete the record of book titled ―Database System Concepts‖.

c) Change the Department of the book titled ―Discrete Maths‖ to ―CS‖.

d) List all books that belong to ―CS‖ department.

e) List all books that belong to ―CS‖ department and are written by author ―Navathe‖.

f) List all computer (Department=‖CS‖) that have been issued.

g) List all books which have a price less than 500 or purchased between ―01/01/1999‖ and ―01/01/2004‖.

2) Create a database having three tables to store the details of students of Computer Department

in your college.

Personal information about Student (College roll number, Name of student, Date of birth, Address, Marks(rounded off to whole number) in percentage at 10 + 2, Phone number) Paper Details (Paper code, Name of the Paper)

Student’s Academic and Attendance details (College roll number, Paper code, Attendance, Marks in home examination).

a) Identify primary and foreign keys. Create the tables and insert at least 5 records in each table.

b) Design a query that will return the records (from the second table) along with the name of student from the first table, related to students who have more than 75% attendance and more than 60% marks in paper 2.

c) List all students who live in ―Delhi‖ and have marks greater than 60 in paper 1.

d) Find the total attendance and total marks obtained by each student.

e) List the name of student who has got the highest marks in paper 2.

3) Create the following tables and answer the queries given below:

Customer (CustID, email, Name, Phone, ReferrerID)

Bicycle (BicycleID, DatePurchased, Color, CustID, ModelNo) BicycleModel (ModelNo, Manufacturer, Style)

Service (StartDate, BicycleID, EndDate)

a) Identify primary and foreign keys. Create the tables and insert at least 5 records in each table.

b) List all the customers who have the bicycles manufactured by manufacturer ―Honda‖.

c) List the bicycles purchased by the customers who have been referred by customer ―C1‖.

d) List the manufacturer of red colored bicycles.

e) List the models of the bicycles given for service.

4) Create the following tables, enter at least 5 records in each table and answer the queries given below.

EMPLOYEE ( Person_Name, Street, City )

WORKS ( Person_Name, Company_Name, Salary )

COMPANY ( Company_Name, City )

MANAGES ( Person_Name, Manager_Name )

a) Identify primary and foreign keys.

b) Alter table employee, add a column ―email‖ of type varchar(20). c) Find the name of all managers who work for both Samba Bank and NCB Bank.

d) Find the names, street address and cities of residence and salary of all employees who work for ―Samba Bank‖ and earn more than $10,000.

e) Find the names of all employees who live in the same city as the company for which they work.

f) Find the highest salary, lowest salary and average salary paid by each company.

g) Find the sum of salary and number of employees in each company.

h) Find the name of the company that pays highest salary.

5) Create the following tables, enter at least 5 records in each table and answer the queries given below.

Suppliers (SNo, Sname, Status, SCity) Parts (PNo, Pname, Colour, Weight, City) Project (JNo, Jname, Jcity)

Shipment (Sno, Pno, Jno, Qunatity)

a) Identify primary and foreign keys.

b) Get supplier numbers for suppliers in Paris with status>20.

c) Get suppliers details for suppliers who supply part P2. Display the supplier list in increasing order of supplier numbers.

d) Get suppliers names for suppliers who do not supply part P2.

e) For each shipment get full shipment details, including total shipment weights.

f) Get all the shipments where the quantity is in the range 300 to 750 inclusive.

g) Get part nos. for parts that either weigh more than 16 pounds or are supplied by suppliers S2, or both.

h) Get the names of cities that store more than five red parts.

i) Get full details of parts supplied by a supplier in London.

j) Get part numbers for part supplied by a supplier in London to a project in London.

k) Get the total number of project supplied by a supplier (say, S1).

l) Get the total quantity of a part (say, P1) supplied by a supplier (say, S1).