PRACTICAL ASSIGNMENT EXCEL

Assignment 1
Make a table as given below:
10 nct

autumn term

assignments
Surname

Forename

Li

Danny

Holt

Bethany

Paphitis

adam

Patel

Mandeep

Bamburst

Rose

Coppell

Sean

Edwards

Michael

1
5
2
1
5
8
5
8
5
8
5
5
6
5
6

51

25

15

75

16

85

85

88

68

61

64

85

95

92

98

85

95

85

25

21

89

95

59

65

45

84

56

Add a column in the end and calculate the total for each individual.
Format the table to improve the presentation of the information present in the table
Ans:

Assignment 2
Enter the following labels and values starting in cell A3:
B. Alban

64

87

55

73

Y. Salto

80

90

78

82

S. Cruz

93

96

92

90

R. Kim

71

87

52

70

P. Quinn

87

73

49

82

H. Toro

81

77

73

80

Format the table to improve the presentation of the information present in the table
Ans:

Assignment 3
Type the following data in excel worksheet and save it as second.xls.
X: People per physician
Y: Life Expectancy
X
Y

370
70.5

6166
53.5

684
65

449
76.5

643
70

Do the following
(a) Complete column C for finding product x * y
(b) Find sum of x column at the end of data
(c) Find sum of y column at the end of data
(d) Find sum of x * y column at the end of data
(e) Find sum of x^2
(f) Find sum of y^2
(g) Find the minimum from the values of x and y both
(h) Count the values of x which are less than 1000.
Ans:

1551
71

616
60.5

403
51.5

Assignment 4
The table below outlines rainfall levels, in millimeters, for seven cities during a
six- month period:
RAINFALL(M
M)
CITY
BRININGHAM
BRISTOL
GLASGOW
LIVERPOOL
LONDON
SHEFFIELD
SOUTHAMPTO
N

JAN

FEB

MAR

APR

MAY

JUN

56
64
76
59
49
53

51
58
68
50
52
54

47
53
65
51
42
49

42
47
56
43
33
44

30
34
42
30
26
35

22
24
26
23
17
24

42

45

36

29

20

11

Enter these details onto a spreadsheet and complete the required tasks:
1. Widen the first column to 15.
2. Add rows beneath to show the monthly rainfall.
3. Delete blank rows or columns.
4. Add a new column after the June rainfall statistics to show the total rainfall in
each city over the period.
5. Name the spreadsheet as RainFall.
6. The rainfall in Birmingham during March should be 58.
7. Insert a new row between the rows holding the London and Sheffield rainfall
statistics. Enter the following details: Newcastle 65 63 57 50 39 21
Ans:

Assignment 5
Enter the following data and save it in grade .xls
Name
Marks1
Marks2
Marks3 Total Percentage Grade
Amit
80
70
80
Renu
70
60
90
Rajeev
60
50
80
Manish
50
30
90
Sanjeev
40
40
80
Anita
70
70
90
Do the following
(a) Compute the total marks and percentage of each student by entering appropriate
formula.
(b) Compute the grades based on following criteria
If percentage >= 90 then grade = A
If percentage >= 80 and <90 then grade = B
If percentage >= 70 and <80 then grade = C

If percentage >= 60 and <70 then grade = D

If percentage < 60 then grade = E
Ans:

Assignment 6
Langton Breweries Ltd uses barley as the chief raw material in producing a variety
of beers for the home and export markets. The barley, which is purchased from
cereal growers, is graded according to its quality into A, B and C categories.
The following table gives the quantity (in tonnes) and respective quality of barley
sold to the company by 12 different cereal growers:
LANGTON BREWERIES LTD
Quant Quali
Supplier ity
ty
J.Andre
ws
87
A
L.Benso
n
136
A
W.Brun
nel
163
C
H.Colw
ell
48
B
B.
Cullen
107
A
K.Davie
s
64
B
T.Dukes 82
B
S.Freem
an
58
A
O.Harris 183
A
M.Lioyd 119
A
R.Meen
gan
76
C
J.Ward
142
B
The basic purchase price of the barley is $85 per tonne. In order to encourage the
production of top quality barley, however, the company uses the following
bonus/penalty scheme based on barley quality: Category A - 10% bonus is paid

 Category B - No bonus paid or penalty charged Category C - 25% penalty is

charged
The barley is collected from each supplier by one of the company's trucks. The
company charges a $2.50 per tonne collection fee.
You are required to carry out the following tasks:
1. Enter the details given in the table above onto a spreadsheet titled 'Langton
Breweries Ltd - Barley Purchases'.
2. Add 5 new columns to show:
a) The basic price paid to each supplier
b) The bonus paid (if any) to each supplier (Hint: Use IF)
c) The penalty charged (if any) to each supplier (Hint: Use IF)
d) The overall collection fee deducted by the company for each supplier
e) The actual amount received by each supplier after bonuses were awarded,
penalties charged and collection fees deducted.
Ans:

Assignment 7
JD Seafoods Ltd. supply cod, plaice, sole and whiting to restaurants and shops
throughout the UK. The company operates a fleet of 8 trawlers out of Grimsby
port. When a trawler docks in port, the crew sorts their catch into 25kg boxes.
Part A
The following table shows the number of boxes of each fish type caught on each
day over a three-day period by one of the company's trawlers, the Morning Star:
Morning Star - Fish
Catches
Fish
type
Cod
Plaice
Sole
Whiting

Day
1
47
11
28
54

Day
2
38
9
36
40

Day 3
48
15
30
43

You are required to enter these details onto a spreadsheet and carry out the
following tasks:
1. Add a column, headed Total, and show the total number of boxes of each
fish type caught over the period.

2. Add a row, labelled Total, and show the total number of boxes of fish caught
on each day.

3. Alter the cod catches to 51, 37 and 54 for day 1, day 2 and day 3
respectively.

4. Save the sheet as Fish1.

Part B
The following table shows the number of boxes of each fish type landed by the
other 7 trawlers in the company's fleet over the same three-day period:
JD Seafood Ltd.

trawler
Blue Oyster
Edelweiss
Freswick
Lass
Kingfisher
Netmaster
Red Rose
Velvet Band

Co
d

Plaic
e

Sole

Whitin
g

151 44
79 17

114
63

109
124

110
129
118
96
102

71
83
57
79
48

117
120
135
89
134

26
42
38
23
12

Enter these details onto a spreadsheet and carry out the following:
1. Add a column, headed Total, and show the total number of boxes of fish
caught by each trawler over the period.

2. Add a row, labelled Total, and show the total number of boxes of each fish
type caught by all 7 trawlers.

3. Insert a row, labelled Morning Star, before Netmaster's details.

4. Enter formulae to link the appropriate cells in this new row with the cells in
the Fish1 spreadsheet file which contain the total number of boxes of each
fish type caught by the Morning Star over the period (Part A above).

Assignment 8
The Lakeview Hotel and Country Club accommodates many tourists each year.
The following table shows the numbers of foreign bookings for 1998, 1999 and
2000:
LAKEVIEW HOTEL AND COUNTRY CLUB
Nationalit
y
American
Australia
n
French
German
Japanese
Others

1998

1999

2000

937

1108

1405

650
1238
994
395
786

793
968
854
483
794

934
627
699
597
756

You must now carry out the following:

1. Enter the details onto a spreadsheet.
2. Save the spreadsheet as Hotel1.
3. Sort the data according to alphabetic order of nationality.
4. Produce a bar graph (with suitable titles, legends and axes titles) showing the
numbers of bookings for each nationality in 1998, 1999 and 2000.
5. Can you establish any visible trends?
6. Produce a pie graph, with a suitable title, showing a percentage breakdown of
the bookings for 2000.

Ans:

Assignment 9
A university maintains a year wise result for four courses and then generates an
average report as given below
S no. Year
Average
1
2002
2
2003
3
2004
4
2005
5
2006
6
Total

Course1
356
200
256
400
456

Course2
300
400
500
600
450

Course3

Course4

300
200
400
500
550

400
450
600
550
450

(a) Complete the report to calculate the course wise average in row 6
(b) Provide formula to calculate year wise average in column G
(c) Generate a column chart to compare data
Ans:

Assignment 10
A company records the details of total sales (in Rs. ) sector wise and month wise in
the following format

Sector 30
Sector 22
Sector 23
Sector 15

Jan
12000
14000
15000
16000

Feb
17000
18000
19000
12000

March
14000
15000
16000
17000

April
15000
16000
17000
18000

(a) Enter the data in a worksheet and save it as sector.xls

(b) Using appropriate formula, calculate total sale for each sector
(c) Create a 3-D column chart to show sector wise data for all four
Ans:

Assignment 11
Suppliers

Zone

Quantity

Trimnet suppliers

South

10

Zebra Suppliers

East

24

Ramnack Suppliers

West

35

Seashadow Suppliers

North

64

Trimnet suppliers

West

23

Zebra Suppliers

West

12

Ramnack Suppliers

North

14

Seashadow Suppliers

North

34

Seashadow Suppliers

North

65

Zebra Suppliers

North

45

Trimnet suppliers

North

34

Zebra Suppliers

South

23

Trimnet suppliers

South

43

Zebra Suppliers

South

24

Ramnack Suppliers

South

35

Apply CountIf to count the occurance of suppliers

Trimnet
suppliers

Zebra Suppliers
Ramnack
Suppliers
Seashadow
Suppliers

Apply CountIfs to count the occurance of suppliers in each zone

South
Trimnet
suppliers
Zebra Suppliers
Ramnack
Suppliers
Seashadow
Suppliers

Ans:

North

East

West

PRACTICAL ASSIGNMENT SQL

ASSIGNMENT 1
Q1. Write an SQL command that will create a table named Friend with the
following fields and types: - idno NUMERIC (10), fname VARCHAR (24),
address VARCHAR (30), age NUMERIC (10), giftvalue NUMERIC (10, 2).
Ans:
createtable friend (idno numeric(10), fname varchar(24), address varchar(30), age
numeric(10), giftvalue numeric(10,2));
select*from friend

Q2. Insert the following items in the table you have created
Idno
01

FName
Ram

02

Sita

Address
Dwarka sector
10
Janakpuri
block c

Age
41

Giftvalue
200

26

250

03

Rajesh

04

Ajit

05

Rita

Dwarka sector
15
Noida sector
11
Noida sector
11

23

200

35

150

40

200

Ans:
INSERTINTO friend values('01','Ram','Dwarka Sector 10','41','200')
INSERTINTO friend values('02','Sita','Janakpuri Block C','26','250')
INSERTINTO friend values('03','Rajesh','Dwarka Sector 15','23','200')
INSERTINTO friend values('04','Ajit','Noida Sector 11','35','150')
INSERTINTO friend values('05','Rita','Noida Sector 11','40','200')
select*from friend

Q3. Write an SQL query to display all the records.

Ans:

select*from friend

Q4. Write an SQL query to display all the records where age is >40.
Ans:select*from friend where age>'40';

Q5. Write an SQL query to display Fname, Age from the table.
Ans:select Fname, Age from friend

Q6. Write an SQL query to display Fname, Age, Gift value where Age > 35 from
the table.
Ans:select Fname, Age, GiftValue from friend where Age>'35'

Q7. Write an SQL query to display all record where Gift vale is > 200 and Age
>20.
Ans:select*from friend where Giftvalue>'200'and Age>'20'

Q8. Write an SQL query to display all record where Gift vale is > 200 or Age >20.
Ans:select*from friend where Giftvalue>'200'or Age>'20'

ASSIGNMENT 2
Q1. Create a table with the following specifications
Field name
EMPID
DEPT
EMPNAME
ADDRESS
SALARY

Data type
Numeric(10)
CHAR(5)
VARCHAR(15)
VARCHAR(30)
NUMERIC(7)

Ans:create table Employee (Empid numeric(10), Dept char(5), Empname varchar(15),

Address varchar(30), Salary numeric(7));
select * from Employee

Q2. Make the following entries in the table

EMPID
101
102
103
104
105

DEPT
RD01
RD01
RD02
RD02
ED01

EMPNAME
Prince
Harry
Tom
Susan
Mark

106

AD01

Francis

107

GR01

Robert

108
109
110

RD03
RD03
AD01

Philip
Henry
Frank

ADDRESS
Park Way
Pebble Street
Park Avenue
Model Town
Victor
Crescent
Chelmsford
Park
Downtown
Cross
Park Avenue
Malibu Beach
St. Peters Lane

SALARY
15000
12000
11000
10000
10000
13000
14000
15000
14000
7000

Ans:INSERT INTO Employee values ('101', 'RD01', 'Prince', 'Park Way','15000')

INSERT INTO Employee values('102','RD01', 'Harry', 'Pebble Street','12000')
INSERT INTO Employee values('103','RD02', 'Tom', 'Park Avenue','11000')
INSERT INTO Employee values('104','RD02', 'Susan', 'Model Town','10000')
INSERT INTO Employee values('105','ED01', 'Mark', 'Victor Crescent','10000')
INSERT INTO Employee values ('106','AD01', 'Francis', 'Chelmsford
Park','13000')
INSERT INTO Employee values('107','GR01', 'Robert', 'Downtown Cross','14000')
INSERT INTO Employee values('108','RD03', 'Philip', 'Park Avenue','15000')
INSERT INTO Employee values('109','RD03','Henry', 'Malibu Beach','14000')
INSERT INTO Employee values('110','AD01','Frank', 'St. Peters Lane','7000')
select * from Employee

1 Write an SQL query to display all the records

Ans:Select * from Employee

2 Write an SQL query to display all the records where RD01is the department.

Ans:Select * from Employee where Dept='RD01'

3 Write an SQL query to display EMPNAME, DEPT, Salary from the table.
Ans:Select Empname, Dept, Salary from Employee

4 Write an SQL query to display EMPNAME, DEPT, Salary from the table
where salary is greater than 13000.
Ans:Select Empname, Dept from Employee where Salary>'13000'

5 Write an SQL query to display the record of those employees who lives in
Park Avenue.
Ans:Select * from Employee where Address= 'Park Avenue'

1. Display name, id of those employees who salary is 15000 and lives in Park
Avenue
ANS:
Select Empid, Empname from Employee where Salary='15000' and
Address='Park Avenue'

2. Find names for all employees who work for the department RD01.
Ans:
Select Empname from Employee where Dept='RD01

8. How many employees work in department starting from RD.

Ans:
Select * from Employee where Dept like 'RD%'

9. What is the maximum and minimum of the salaries.

Ans:
Select max (Salary) from Employee

Select min (Salary) from Employee

10. Name the employees and their department whose salaries are greater than
12000.
Ans:
Select Empname, Dept from Employee where Salary>'12000'

11. List the employees in increasing order of their salaries.

Ans:
select * from Employee order by Salary

12. Modify the table so that Susan is assigned department AD01.

Ans:
update Employee set Dept='AD01' where Empname='Susan';

13. Name the employee in department RD03 who lives in Park Avenue
Ans:
select Empname from Employee where Dept='RD03' and Address='Park Avenue';

14. Find the Average salary.

Ans:
select avg (salary) from Employee

15. Count the number of employees.

Ans: select count (salary) from Employee

16. Find details of those employees whose salary is > the average salary for all
employees
Ans:
select * from Employee where salary>'12000'

Assignment 3

1. Write an SQL command that will create a table named FriendNew with the
following fields and types: idno NUMERIC(10) PRIMARY KEY, fname
VARCHAR(24), address VARCHAR(30), age NUMERIC(10) , giftvalue
NUMERIC(10,2).
Ans:
createtable FriendNew (Idno numeric(10)PrimaryKey, Fname varchar(24),
Address varchar(30), Age numeric(10), Giftvalue Numeric(10,2));
select*from FriendNew

2. Insert the following items in the table you have created

Idno
01

FName
Ram

02

Sita

03

Rajesh

04

Ajit

05

Rita

Ans:

Address
Dwarka
sector 10
Janakpuri
block c
Dwarka
sector 15
Noida sector
11
Noida sector
11

Age
41

Giftvalue
200

26

250.80

23

200

35

150.50

40

200

INSERT INTO FriendNew values('01','Ram','Dwarka Sector 10','41','200')

INSERT INTO FriendNew values('02','Sita','Janakpuri Block c','26', '250.80')
INSERT INTO FriendNew values('03','Rajesh','Dwarka Sector 15','23','200')
INSERT INTO FriendNew values('04','Ajit','Noida Sector 11','35','150.50')
INSERT INTO FriendNew values('05','Rita','Noida Sector 11','40','200')
select*from FriendNew

3. Write a SQL query to display all the records whose name starts with R.
Ans:
Select*from FriendNew where Fname like'R%';

4. Write an SQL query that will insert a complete record into the Friend table with
these values for the respective fields: '123', 'Anil', 'Dwarka Sector 11', 23, 29.99.
Ans:
Insertinto FriendNew values(123,'Anil','Dwarka Sector 11', 23, 29.99);

5. Write an SQL query to change the age of Sita to 28.

Ans:
Update FriendNew Set Age=28 where Fname='Sita';

6. Write an SQL query to delete the record with idno 123.

Ans:
Deletefrom FriendNew where Idno=123;

7. Write an SQL query that will update the giftvalue to 49.99 for all people in the
Friend table whose age is equal to or greater than 31 years.
Ans:
Update FriendNew Set Giftvalue=49.99 where Age=31 OR Age>31;

8. Write an SQL query that will add a field named City to the Friend table with
datatype as varchar and size equal to 15.
Ans:
AlterTable FriendNew Add City Varchar(15);

9. Add the name of the city for all the records in the table.
Ans:
Update FriendNew Set City='Delhi';

10. Write a SQL query to display fname and age of all the records in ascending
order.
Ans:
Select Fname, Age from FriendNew Orderby Fname;

11. Write SQL query to get the cumulative giftvalue for all records.
Ans:
SelectSum(Giftvalue)from FriendNew;

12. Write a query to average of the age of all the friends under the heading
Average Age.
Ans:
SelectAvg(Age)'Average Age'from FriendNew;

13. Write a query to display the name and age of the youngest member.
Ans:
Select Fname, Age from FriendNew where Age=(SelectMin(Age)from
FriendNew);

14. Write a query to count the number of candidates whose age in more than 30
years.
Ans:
SelectCount(*)from FriendNew where Age>30;

15. Write a query to display the name and giftvalue of the record with highest gift
value.
Ans:
Select Fname, Giftvalue from Friendnew where
Giftvalue=(SelectMax(Giftvalue)from FriendNew);

16. Write an SQL query that will delete all records from the Friend table whose
idno is 123.
Ans: Deletefrom FriendNew where Idno=123;

17. Write a query to delete all the records.

Ans:
DropTable FriendNew;