WORK SHEET NETWORK

Class 12 - Computer Science

1. What are hubs? How are active hubs different from passive hubs? [3]
2. What do you understand by Traceroute in networking. [3]
3. What is Ping network tool? [3]
4. What are the different types of networks? [3]
5. What are the facilities provided by the SERVER in a Network environment? [3]
6. What is the difference between IMAP and POP? [3]
7. What are the components required for networking? [3]
8. What is the difference between Hub, Switch, and Router? [3]
9. Mention one advantage of networking. [3]
10. What is a network? Why is it needed? [3]
11. What are protocols? What is the significance of protocols in networks? [3]
12. What is a tracert or traceroute command? [3]
13. What are bridges? How do they differ from repeaters? [3]
14. Define the following terms: [3]
i. ipconfig
ii. nslookup
iii. speed test
15. What is SSH? [3]
16. Distinguish between private and public cloud? [3]
17. Differentiate between PAN and LAN type of networks, giving two examples of each. [3]
18. What is CSMA/CA? How does it work? [3]
19. What is switching techniques? Explain any two switching technique. [4]
20. Expertia Professional Global (EPG) in an online corporate training provider company for IT related courses. The [4]
company is setting up their new campus in Mumbai. You as a network expert have to study the physical
locations of various buildings and the number of computers to be installed. In the planning phase, provide the
best possible answers for the queries (i) to (v) raised by them.

1 / 36
 Physical locations of the buildings of EPG

Building to building distance (in metre)

From To Distance

Administrative Building Finance Building 60

Administrative Building Faculty Studio Building 120

Finance Building Faculty Studio Building 70

Expected computers to be installed in each building

Buildings Computers

Administrative Building 20

Finance Building 40

Faculty Studio Building 120

i. Suggest the most appropriate building, where EPG should plan to install the server.
ii. Suggest the most appropriate building to building cable layout to connect all three buildings for efficient
communication.
iii. Which type of network out of the following is formed by connection the computers of these three buildings?
a. LAN
b. MAN
c. WAN
iv. Which wireless channel out of the following should be opted by EPG to connect to students of all over the
world?
a. Infrared
b. Microwave
c. Satellite
v. Expand the following
WAN
MAN
21. What are repeaters and routers? [4]
22. What is DNS and also explain its functions? [4]
23. Learn Together is an educational NGO. It is setting up its new campus at Jabalpur for its web-based activities. [4]
The campus has four compounds as shown in the diagram below:

2 / 36
 Centre to centre distance between various compounds as per architectural drawing (in m) is as follows

Main Compound to Resource Compound 110 m

Main Compound to Training Compound 115 m

Main Compound to Finance Compound 35 m

Resource Compound to Training Compound 25 m

Resource Compound to Finance Compound 135 m

Training Compound to Finance Compound 100 m

Expected number of computers in each compound are as follows

Main Compound 5

Resource Compound 15

Training Compound 150

Finance Compound 20

i. Suggest a cable layout of connections between the compounds.

ii. Suggest the most suitable place (i.e. compound) to house the server for this NGO. Also, provide a suitable
reason for your suggestion.
iii. Suggest the placement of the following devices with justification:
a. Repeater
b. Hub/Switch
iv. The NGO is planning to connect its international office situated in Mumbai, which out of the following wired
communication link, will you suggest for a very high speed connectivity?
a. Telephone analog line
b. Optical fiber
c. Ethernet cable.
v. Expand the following
LAN
PAN
24. Be Happy Corporation has set up its new centre at Noida, Uttar Pradesh for its office and web-based activities. It [4]
has 4 blocks of buildings.

3 / 36
 Distance between the various blocks is as follows

A to B 40 m

B to C 120 m

C to D 100 m

A to D 170 m

B to D 150 m

A to C 70 m

Numbers of computers in each block

Block A - 25
Block B - 50
Block C - 125
Block D - 10
i. Suggest and draw the cable layout to efficiently connect various blocks of buildings within the Noida centre
for connecting the digital devices.
ii. Suggest the placement of the following device with justification
a. Repeater
b. Hub/Switch
iii. Which kind of network (PAN/LAN/WAN) will be formed if the Noida office is connected to its head office
in Mumbai?
iv. Which fast and very effective wireless transmission medium should preferably be used to connect the head
office at Mumbai with the centre at Noida?
25. Workalot consultants are setting up a secured network for their office campus of Gurgaon for their day-to-day [4]
office and web based activities. They are planning to have connectivity between 3 buildings and the head office
situated in Mumbai. Answer the questions (i) to (v) after going through the building positions in the campus and
other details, which are given below:

Distance between various buildings

Building GREEN to Building RED 110 m

4 / 36
 Building GREEN to Building BLUE 45 m

Building BLUE to Building RED 65 m

Gurgaon Campus to Head Office 1760 m

Number of computers

Building GREEN 32

Building RED 150

Building BLUE 45

Head Office 10

i. Suggest the most suitable place (i.e. building) to house the server of this organisation. Also, give a reason to
justify your suggested location.
ii. Suggest a cable layout of connections between the buildings inside the campus.
iii. Suggest the placement of the following devices with justification:
a. Switch
b. Repeater
iv. The organisation is planning to provide a high speed link with its head office situated in the Mumbai using a
wired connection. Which of the following cables will be most suitable for this job?
a. Optical fibre
b. Co-axial cable
c. Ethernet cable
v. What is the use of firewall in network?
26. Tech Up Corporation (TUC) is a professional consultancy company. The company is planning to set up their [4]
new offices in India with its hub at Hyderabad. As a network adviser, you have to understand their requirement
and suggest to them the best available solutions. Their queries are mentioned as (i) to (v) below.
Physical locations of the blocks of TUC

Block to block distances (in metre)

Block (From) Block (To) Distance

Human Resource Conference 60

Human Resource Finance 120

Conference Finance 80

Expected number of computers to be installed in each block

Block Computers

5 / 36
 Human Resource 125

Finance 25

Conference 60

i. What will the most appropriate block, where TUC should plan to install their server?
ii. Draw a block to block cable layout to connect all the buildings in the most appropriate manner for efficient
communication.
iii. What will be the best possible connectivity out of the following, you will suggest to connect the new setup of
offices in Bengalore with its London based office?
Infrared
Satellite Link
Ethernet Cable
iv. Which of the following devices will be suggested by you to connect each computer in each of the buildings?
Gateway
Switch
Modem
v. Company is planning to connect its Block in Hyderabad which is more than 20 km. Which type of network
will be formed?
27. Trine Tech Corporation (TTC) is a professional consultancy company. The company is planning to set up their [4]
new offices in India with its hub at Hyderabad. As a network adviser, you have to understand their requirement
and suggest them the best available solutions. Their queries are mentioned as (i) to (v) below.

Block to block distance (in m)

Block (From) Block (To) Distance

Human Resource Conference 110

Human Resource Finance 40

Conference Finance 80

Expected number of computers to be in each block

Block Computers

Human Resource 25

Finance 120

Conference 90

i. Which will be the most appropriate block, where TTC should plan to install their server?

6 / 36
 ii. Draw a block to block cable layout to connect all the buildings in the most appropriate manner for efficient
communication.
iii. What will be the best possible connectivity out of the following, you will suggest to connect the new set up
of offices in Bengalore with its London based office.
Satellite Link
Infrared
Ethernet
iv. Which of the following device will be suggested by you to connect each computer in each of the buildings?
Switch Modem
Gateway
v. Company is planning to connect its offices in Hyderabad which is less than 1 km. Which type of network
will be formed?
28. China Middleton Fashion is planning to expand their network in India, starting with two cities in India of [4]
provide infrastructure for distribution of their product. The company has planned to set up their main office units
in Chennai at the different locations and has named their offices as Production Unit, Finance Unit and Media
Unit. The company has its Corporate Unit in Delhi. A rough layout of the same is as follows:

Approximate distance between these units are as follows:

From To Distance

Production Unit Finance Unit 70 m

Production Unit Media Unit 15 km

Production Unit Corporate Unit 2112 km

Finance Unit Media Unit 15 km

In continuation of the above, the company experts have planned to install the following number of
computers in each of their office units

Production Unit 150

Finance Unit 35

Media Unit 10

Corporate Unit 30

i. Suggest the kind of network required (out of LAN, MAN, WAN) for connecting each of the following office
units:
Production Unit and Media Unit
Production Unit and Finance Unit.

7 / 36
 ii. Which one of the following device will you suggest for connecting all the computers within each of their
office units?
Switch/Hub
Modem
Telephone
iii. Which of the following communication media, will you suggest to be procured by the company for
connecting their local office units in Chennai for very effective (high speed) communication?
Telephone cable
Optical fiber
Ethernet cable
iv. Suggest a cable/wiring layout for connecting the company's local office units located in Chennai. Also,
suggest an effective method/technology for connecting the company's office unit located in Delhi.
v. Suggest the most suitable place to install the server with reason.
29. G.R.K International Inc. is planning to connect its Bengaluru Office Setup with its Head Office in Delhi. The [4]
Bengaluru Office G.R.K. International Inc. is spread across an area of approx. 1 square kilometres consisting of
3 blocks. Human Resources, Academics and Administration. You as a network expert have to suggest answers to
the four queries (i) to (v) raised by them.
Note Keep the distances between blocks and number of computers in each block in mind, while providing them
the solutions.

Shortest distances between various blocks

Human Resources to Administration 100 m

Human Resources to Academics 65 m

Academics to Administration 110 m

Delhi Head Office to Bengaluru Office Setup 2350 km

Number of computers installed at various blocks

Block Number of Computers

Human Resources 156

Administration 20

Academics 100

Delhi Head Office 20

i. Suggest the most suitable block in the Bengaluru Office Setup to host the server. Give a suitable reason with
your suggestion.
ii. Suggest the cable layout among the various blocks within the Bengaluru Office Setup for connecting the
blocks.

8 / 36
 iii. Suggest a suitable networking device to be installed in each of the blocks essentially required for connecting
computers inside the blocks with fast and efficient connectivity.
iv. Suggest the most suitable media to provide secure, fast and reliable data connectivity between Delhi Head
Office and the Bengaluru Office Setup.
v. Expand the following
WAN
LAN
30. Institute of Distance Learning is located in Pune and is planning to go in for networking of four wings for better [4]
interaction. The details are shown below:

The distance between various wings

Student Wing to Admin Wing 150 m

Student Wing to Admission Wing 100 m

Student Wing of Lib Wing 325 m

Admission Wing to Admin Wing 100 m

Admission Wing to Lib Wing 125 m

Admin Wing to Lib Wing 90 m

Number of computers

Student Wing 225

Admission Wing 50

Admin Wing 10

Lib Wing 25

i. Suggest the type of networking (LAN, MAN, WAN) for connecting Lib Wing to Admin Wing. Justify your
answer.
ii. Suggest the most suitable place (i.e. wing) to house the server, with a suitable reason.
iii. Suggest and placement of the following devices with reasons.
a. Repeater
b. Switch
iv. The Institute is planning to link its study centre situated in Delhi. Suggest an economic way to connect it
with reasonably high speed. Justify your answer.
v. Expand the following
PAN
WAN
31. Explain the common E-mail protocols. [4]

9 / 36
32. A school library is connecting computers in its units in a LAN. The library has 3 units as shown in the diagram [4]
below:

The three units are providing the following services:

1. Teachers Unit : For access of the Library Books by teachers
2. Students Unit : For access of the Library Books by Students
3. Circulation Unit : For issue and return of books for teachers and students
Centre to Centre distances between the 3 units are as follows:
Circulation Unit to Teachers Unit 20 metres
Circulation Unit to Students Unit 30 metres
Teachers Unit to Students Unit 10 metres
Number of computers in each of the units is as follows:
Circulation Unit 15
Teachers Unit 10
Students Unit 10
a. Suggest the most suitable place (i.e. the Unit name) to install the server of this Library with a suitable reason.
b. Suggest an ideal layout for connecting these Units for a wired connectivity.
c. Which device will you suggest to be installed and where should it be placed to provide Internet connectivity
to all the Units?
d. Suggest the type of the most efficient and economical wired medium for connecting all the computers in the
network.
e. The university is planning to connect the Library with the School Principal's computer which is in his office
at a distance of 50 metres. Which type of network out of LAN, MAN or WAN will be used for the network?
Justify your answer.
33. Explain the twisted pair cable in details. [4]
34. Bias Methodologies is planning to expand their network in India, starting with three cities in India to build [4]
infrastructure for research and development of their chemical products. The company has planned to set up their
main office in Pondicherry at three different locations and have named their offices as Back Office, Research
Lab and Development Unit. The company has one more research office namely Corporate Unit in Mumbai. A
rough layout of the same is as follows:

Approximate distance between these offices are as follows

From To Distance

10 / 36
 Research Lab Back Office 110 m

Research Lab Development Unit 16 km

Research Lab Corporate Unit 1800 km

Back Office Development Unit 13 km

In continuation of the above, the company experts have planned to install the following number of
computers in each of their offices

Research Lab 158

Back Office 79

Development Unit 90

Corporate Unit 51

i. Suggest the type of network required (out of LAN, MAN, WAN) for connecting each of the following office
units.
Research Lab and Back Office
Research Lab and Development Unit.
ii. Which one of the following device, will you suggest for connecting all the computers with in each of their
office units?
Switch/Hub
Modem
Telephone.
iii. Which of the following communication medium, will you suggest to be procured by the company for
connecting their local office units in Pondicherry for very effective (high speed) communication?
Telephone cable
Optical fibre
Ethernet cable.
iv. Suggest a cable/wiring layout for connecting the company’s local office units located in Pondicherry. Also,
suggest an effective method/technology for connecting the company's office unit located in Mumbai.
v. Which building is suitable to install the server with suitable reason?
35. Freshminds University of India is starting its first campus in Anna Nagar of South India with its centre [4]
admission office in Kolkata. The university has three major blocks comprising of Office block, Science block
and Commerce block is in 5 km area campus.
As a network expert, you need to suggest the network plan as per (i) to (v) to the authorities keeping in mind the

11 / 36
 distance and other given parameters.

Expected wire distance between various locations

Office Block to Science Block 90 m

Office Block to Commerce Block 80 m

Science Block to Commerce Block 15 m

Kolkata Admission Office to Anna Nagar Campus 450 km

Expected number of computers to be installed at various locations in the university are as follows:

Office Block 10

Science Block 140

Commerce Block 30

Kolkata Admission Office 8

i. Suggest the authorities, the cable layout amongst various blocks inside university campus for connecting the
blocks.
ii. Suggest the most suitable place (i.e. block) to house the server for this university with a suitable reason.
iii. Suggest an efficient device form the following to be installed in each of the block to connect all the
computers.
a. Modem
b. Switch
c. Gateway
iv. Suggest the most suitable (very high speed) service to provide data connectivity between admission office
located in Kolkata and the campus located in Anna Nagar form the following options:
Telephone line
Fixedline dial-up connection
Co-axial cable network
GSM
Leased line
Satellite connection.
v. University is planning to connect its campus in Kolkata which is more than 100 km. Which type of network
will be formed?
36. Eduminds University of India is starting its campus in a small town Parampur of Central India with its centre [4]
admission office in Delhi. The university has three major buildings comprising of Admin building, Academic

12 / 36
 building and Research building in 5 km area campus.
As a network expert, you need to suggest the network plan as per (i) to (v) to the authorities keeping in mind the
distances and other given parameters.

Expected wire distance between various locations

Research Building to Admin Building 90 m

Research Building to Academic Building 80 m

Academic Building to Admin Building 15 m

Delhi Admission Office to Parampur Campus 1450 km

Expected number of computers to be installed at various locations in the university are as follows

Research Building 20

Academic Building 150

Admin Building 35

Delhi Admission Office 5

i. Suggest the authorities, the cable layout amongst various buildings inside the university campus for
connecting the buildings.
ii. Suggest the most suitable place (i.e. building) to house the server of this organisations with a suitable reason.
iii. Suggest an efficient device for the following to be installed in each of the building to connect all the
computers
a. Gateway
b. Modem
c. Switch
iv. Suggest the most suitable (very high speed) service to provide data connectivity between admission building
located in Delhi and the campus located in Parampur form the following options:
Telephone line
Fixedline dial-up connection
Co-axial cable network
GSM
Leased line
Satellite connection.
v. University is planning to connect its campus in Delhi which is less than 100 km. Which type of network will
be formed?
37. What is server side script? Which languages are used for server side scripts? Explain them. [4]
38. Quickdev, an IT based firm, located in Delhi is planning to set up a network for its four branches within a city [4]
with its Marketing department in Kanpur. As a network professional, give solutions to the questions (i) to (v),

13 / 36
 after going through the branches locations and other details which are given below:

Distance between various branches is as follows:

Branch Distance

Branch A to Branch B 40 m

Branch A to Branch C 80 m

Branch A to Branch D 65 m

Branch B to Branch C 30 m

Branch B to Branch D 35 m

Branch C to Branch D 15 m

Delhi Branch to Kanpur 300 km

Number of computers in each of the branches:

Branch Number of Computers

Branch A 15

Branch B 25

Branch C 40

Branch D 115

i. Suggest the most suitable place to install the server for the Delhi branch with a suitable reason.
ii. Suggest an ideal layout for connecting all these branches within Delhi.
iii. Which device will you suggest, that should be placed in each of these branches to efficiently connect all the
computers within these branches?
iv. Delhi firm is planning to connect to its Marketing department in Kanpur which is approximately 300 km
away. Which type of network out of LAN, WAN or MAN will be formed? Justify your answer.
v. Suggest a protocol that shall be needed to provide help for transferring of files between Delhi and Kanpur
branch.
39. Vidya for All is an educational NGO. It is setting up its new campus at Jaipur for its web-based activities. The [4]
campus has four buildings as shown in the diagram below:

Centre to centre distance between various buildings as per architectural drawing (in m) is as follows

Main Building to Resource Building 120 m

14 / 36
 Main Building to Training Building 40 m

Main Building to Accounts Building 135 m

Resource Building to Training Building 125 m

Resource Building to Accounts Building 45 m

Training Building to Accounts Building 110 m

Expected number of computers in each building are as follows

Main Building 15

Resource Building 25

Training Building 250

Accounts Building 10

i. Suggest a cable layout of connection between the buildings.

ii. Suggest the most suitable place (i.e. building) to house the server for this NGO. Also, provide a suitable
reason for your suggestion.
iii. Suggest the placement of the following devices with justification:
a. Repeater
b. Hub/Switch.
iv. The NGO is planning to connect its international office situated in Delhi. Which out of the following wired
communication links, will you suggest for a very high speed connectivity?
a. Telephone analog line
b. Optical fibre
c. Ethernet cable.
v. Expand the MODEM.
40. What is mobile computing? Explain any two mobile computing technologies. [4]
41. Write a brief note on the TCP/IP suite. [4]
42. Granuda consultants are setting up a secured network for their office campus at Faridabad for their day-to-day [4]
office and web based activities. They are planning to have connectivity between 3 buildings and the head office
situated in Kolkata.
Answer the questions (i) to (v) after going through the building positions in the campus and other details, which
are given below.
Distance between various buildings

Building RAVI to Building JAMUNA 120 m

Building RAVI to Building GANGA 50 m

Building GANGA to Building JAMUNA 65 m

Faridabad Campus to Head Office 1460 m

Number of computers

Building RAVI 25

Building JAMUNA 150

15 / 36
 Building GANGA 51

Head Office 10

i. Suggest the most suitable place (i.e. block) to house the server of this organisation. Also, give a reason to
justify your suggested location.
ii. Suggest a cable layout of connections between the building inside the campus.
iii. Suggest the placement of the following devices with justification:
a. Switch
b. Repeater
iv. The organisation is planning to provide a high speed link with its head office situated in the Kolkata using a
wired connection. Which of the following cable will be most suitable for this job?
a. Optical fibre
b. Co-axial cable
c. Ethernet cable
v. Consultancy is planning to connect its office in Faridabad which is more than 10 km from Head office.
Which type of network will be formed?
43. Gargi Education Service Ltd. is an educational organisation. It is planning to set up its India campus at Nepal [4]
with its head office at Mumbai. The Nepal campus has 4 main buildings-ADMIN, ENGINEERING, BUSINEES
and MEDIA. You as a network expert have to suggest the best network related solutions for their problems
raised in (i) to (v), keeping in mind the distance between the buildings and other given parameters.

Shortest distance between various buildings

ADMIN To ENGINEERING 50 m

ADMIN To BUSINESS 80 m

ADMIN To MEDIA 45 m

ENGINEERING To BUSINESS 60 m

ENGINEERING To MEDIA 50 m

BUSINESS To MEDIA 45 m

Mumbai Head Office To Nepal Campus 2175 m

Number of computers installed at various buildings are as follows

ADMIN 110

ENGINEERING 75

BUSINESS 40

MEDIA 10

16 / 36
 Mumbai Head Office 20

i. Suggest the most appropriate location of the server inside the Nepal Campus (out of 4 buildings), to get the
best connectivity for maximum number of computers. Justify your answer.
ii. Suggest and draw the cable layout to efficiently connect various buildings within the Nepal Campus for
connecting the computers.
iii. Which hardware device will you suggest to be procured by the company to be installed to protect and control
the Internet uses within the campus.
iv. Which of the following will you suggest to establish the online face-to-face communication between the
people in the ADMIN office of Nepal Campus and Mumbai Head Office?
a. Cable TV
b. E-mail
c. Video Conferencing
d. Text Chat
v. Expand the following
MAN
PAN
44. Quick Learn University is setting up its academic blocks at Prayag Nagar and planning to set up a network. The [4]
university has three academic blocks and one human resource centre as shown in the diagram below:

Centre to centre distance between various blocks/centre is as follows:

Law Block to Business Block 40 m

Law Block to Technology Block 80 m

Law Block to HR Centre 105 m

Business Block to Technology Block 30 m

Business Block to HR Centre 35 m

Technology Block to HR Centre 15 m

Number of computers in each of the blocks/centre are as follows:

Law Block 15

Technology Block 40

HR Centre 115

Business Block 25

17 / 36
 i. Suggest the most suitable place (i.e. block/centre) to install the server of this university with a suitable
reason.
ii. Suggest an ideal layout for connecting these block/centre for a wired connectivity.
iii. Which device you will suggest to be placed/installed in each of these blocks/centre to efficiently connect all
the computers within these blocks/centre?
iv. The university is planning to connect its admission office in the closest big city, which is more than 250 km
from university, which type of network out of LAN, MAN or WAN will be formed? Justify your answer.
v. Expand the following
LAN
WAN
45. Learn Together is an educational NGO. It is setting up its new campus at Jabalpur for its web-based activities. [5]
The campus has 4 compounds as shown in the diagram below:

Center to center distances between various Compounds as per architectural drawings (in Metre) is as follows:

Main Compound to Resource Compound 110 m

Main Compound to Training Compound 115 m

Main Compound to Finance Compound 35 m

Resource Compound to Training Compound 25 m

Resource Compound to Finance Compound 135 m

Training Compound to Finance Compound 100 m

The Expected Number of Computers in each Compound is as follows:

Main Compound 5

Resource Compound 15

Training Compound 150

Accounts Compound 20

i. Suggest the most suitable place (i.e., compound) to house the server for this NGO. Also, provide a suitable
reason for your suggestion.
ii. Suggest the placement of the following devices with justification:
a. Repeater
b. Hub/Switch
iii. The NGO is planning to connect its International office situated in Mumbai, which out of the following
wired communication link, you will suggest for very high-speed connectivity?
a. Telephone Analog Line
b. Optical Fiber

18 / 36
 c. Ethernet Cable
46. Granuda Consultants are setting up a secured network for their office campus at Faridabad for their day to day [5]
office and web-based activities. They are planning to have connectivity between 3 buildings and the head office
situated in Kolkata. Answer the questions (i) to (iv) after going through the building positions on the campus and
other details, which are given below:

Distances between various buildings:

Building "RAVI" to Building "JAMUNA" 120 m

Building "RAVI" to Building "GANGA" 50 m

Building "GANGA" to Building "JAMUNA" 65 m

Faridabad Campus to Head Office 1460 KM

Number of Computers

Building "RAVI" 25

Building "JAMUNA" 150

Building "GANGA" 51

Head Office 10

i. Suggest the most suitable place (i.e.r block) to house the server of this organization. Also, give a reason to
justify your suggested location.
ii. What type of network will be formed if all buildings are connected?
iii. Suggest the placement of the following devices with justification:
a. Switch
b. Repeater
iv. The organization is planning to provide a high-speed link with its head office situated in the KOLKATA
using a wired connection. Which of the following cables will be most suitable for this job?
a. Optical Fibre
b. Co-axial Cable
c. Ethernet Cable
47. Knowledge Supplement Organisation has set up its new center at Mangalore for its office and web-based [5]
activities. It has 4 blocks of buildings as shown in the diagram below:

Center to center distances between various blocks

Block A to Block B 50 m

Block B to Block C 150 m

19 / 36
 Block C to Block D 25 m

Number of Computers

Block A 25

Block B 50

Block C 125

Block D 10

i. What type of network will be formed if all blocks are connected?

ii. Suggest the most suitable place (i.e., block) to house the server of this organisation with a suitable reason.
iii. Suggest the placement of the following devices with justification
a. Repeater
b. Hub/Switch
iv. The organization is planning to link its front office situated in the city in a hilly region where cable
connection is not feasible, suggest an economic way to connect it with reasonably high speed.
48. Uplifting Skills Hub India is a knowledge and skill community which has an aim to uplift the standard of [5]
knowledge and skills in society. It is planning to set up its training centres in multiple towns and villages pan
India with its head offices in the nearest cities. They have created a model of their network with a city, a town,
and 3 villages as follows.
As a network consultant, you have to suggest the best network related solutions for their issues/ problems raised
in (i) to (iv) keeping in mind the distance between various locations and given parameters.

The shortest distance between various location:

VILLAGE 1 to B_TOWN 2 KM

VILLAGE 2 to B_TOWN 1.0 KM

VILLAGE 3 to B_TOWN 1.5 KM

VILLAGE 1 to VILLAGE 2 3.5 KM

VILLAGE 1 to VILLAGE 3 4.5 KM

VILLAGE 2 to VILLAGE 3 2.5 KM

A_CITY Head Office to B_HUB 25 KM

The number of Computers installed at various locations is as follows:

B_TOWN 120

VILLAGE 1 15

VILLAGE 2 10

VILLAGE 3 15

20 / 36
 A_CITY Head OFFICE 6

Note:
In Villages, there are community centers, in which one room has been given as a training center for this
organization to install computers.
The organization has got financial support from the government and top IT companies.
i. Suggest the most appropriate location of the SERVER in the B_HUB (out of the 4 locations), to get the best
and effective connectivity. Justify your answer.
ii. Suggest the best-wired medium and draw the cable layout (location to location) to efficiently connect various
locations within the B_HUB.
iii. Which hardware device will you suggest to connect all the computers within each location of B_HUB?
iv. Which service/protocol will be most helpful to conduct live interactions of Experts from Head Office and
people at all locations of B_HUB?
49. Trine Tech Corporation (TTC) is a professional consultancy company. The company is planning to set up its new [5]
offices in India with its hub at Hyderabad. As a network adviser, you have to understand their requirement and
suggest to them the best available solutions. Their queries are mentioned as (i) to (iv) below.
Physical Locations of the blocks of TTC

Block to Block distances (in Mtrs.)

Block (From) Block (To) Distance

Human Resource Conference 110

Human Resource Finance 40

Conference Finance 80

Expected Number of Computers to be installed in each block

Block Computers

Human Resource 25

Finance 120

Conference 90

i. What will be the most appropriate block, where TTC should plan to install their server?
ii. Draw a block diagram showing a cable layout to connect all the buildings in the most appropriate manner for
efficient communication.
iii. What will be the best possible connectivity out of the following, you will suggest connecting the new setup
of offices in Bengaluru with its London based office.
Satellite Link
Infrared
Ethernet Cable
iv. Which of the following device will be suggested by you to connect each computer in each of the buildings?

21 / 36
 Switch
Modem
Gateway
50. Workalot Consultants are setting up a secured network for their office campus at Gurgaon for their day-to-day [5]
office and web-based activities. They are planning to have connectivity between 3 buildings and the head office
situated in Mumbai. Answer the questions (i) to (iv) after going through the building positions on the campus
and other details, which are given below:

Distances between various buildings:

Building "GREEN" to Building "RED" 110 m

Building "GREEN" to Building "BLUE" 45 m

Building "BLUE" to Building "RED" 65 m

Gurgaon Campus to Head Office 1760 KM

Number of Computers:

Building "GREEN" 32

Building "RED" 150

Building "BLUE" 45

Head Office 10

i. Suggest the most suitable place (i.e.f building) to house the server of this organization. Also give a reason to
justify your suggested location.
ii. Suggest a connection medium to connect Gurgaon office with Headoffice.
iii. Suggest the placement of the following devices with justification:
a. Switch
b. Repeater
iv. The organization is planning to provide a high-speed link with its head office situated in MUMBAI using a
wired connection. Which of the following cables will be most suitable for this job?
a. Optical Fibre
b. Co-axial Cable
c. Ethernet Cable
51. "Hindustan Connecting World Association" is planning to start their offices in four major cities in India to [5]
provide regional IT infrastructure support in the field of Education & Culture. The company has planned to
setup their head office in New Delhi in three locations and have named their New Delhi offices as "Sales
Office", "Head Office" and "Tech Office". The company's regional offices are located in "Coimbatore",
"Kolkata" and "Ahmedabad".
A rough layout of the same is as follows:

22 / 36
 Approximate distance between these offices as per network survey team is as follows:

Place From Place To Distance

Head Office Sales Office 10 KM

Head Office Tech Office 70 Meter

Head Office Kolkata Office 1291 KM

Head Office Ahmedabad Office 790 KM

Head Office Coimbatore Office 1952 KM

In continuation of the above, the company experts have planned to install the following number of computers in
each of their offices:

Head Office 100

Sales Office 20

Tech Office 50

Kolkata Office 50

Ahmedabad Office 50

Coimbatore Office 50

i. Suggest network type (out of LAN, MAN, WAN) for connecting each of the following set of their offices:
Head Office and Tech Office
Head Office and Coimbatore Office
ii. Which device you will suggest to be produced by the company for connecting all the computers with in each
of their offices out of the following devices?
Switch/Hub
Modem
Telephone
iii. Which of the following communication media, you will suggest to be procured by the company for
connecting their local offices in New Delhi for very effective and fast communication?
Telephone Cable
Optical Fibre
Ethernet Cable
iv. Suggest an effective method/technology for connecting the company's regional offices at "Kolkata",
"Coimbatore" and "Ahmedabad".
52. The Freshminds University of India is starting its first campus in Ana Nagar of South India with its center [5]
admission office in Kolkata. The university has 3 major blocks comprising of Office Block, Science Block and

23 / 36
 Commerce Block in the 5 km area Campus.

As a network expert, you need to suggest the network plan as per (i) to (iv) to the authorities keeping in mind the
distance and other given parameters.
Expected Wire distances between various locations:

Office Block to Science Block 90 m

Office Block to Commerce Block 80 m

Science Block to Commerce Block 15 m

Kolkata Admission office to Ana Nagar Campus 2450 km

Expected number of Computers to be installed at various locations in the University are as follows:

Office Block 10

Science Block 140

Commerce Block 30

Kolkata Admission office 8

i. What type of server should be installed in university?

Dedicated
Non-dedicated
ii. Suggest the most suitable place (i.e., block) to house the server of this university with a suitable reason.
iii. Suggest an efficient device from the following to be installed in each of the blocks to connect all the
computers:
MODEM
SWITCH
GATEWAY
iv. Suggest the most suitable (very high speed) service to provide data connectivity between Admission Office
located in Kolkata and the campus located in Ana Nagar from the following options:
Telephone line
Fixed-Line Dial-up connection
Co-axial Cable Network
GSM
Leased line
Satellite Connection
53. Ravya Industries has set up its new center at Kaka Nagar for its office and web based activities. The company [5]
compound has 4 buildings as shown in the diagram below:

24 / 36
 Center to center distances between various building is as follows:

Harsh Building to Raj Building 50 m

Raj Building to Fazz Building 60 m

Fazz Building to Jazz Building 25 m

Jazz Building to Harsh Building 170 m

Harsh Building to Fazz Building 125 m

Raj Building to Jazz Building 90 m

Number of Computers in each of the buildings is as follows:

Harsh Building 15

Raj Building 150

Fazz Building 15

Jazz Building 25

i. Suggest the most suitable place (i.e., building) to house the server of this organisation with a suitable reason.
ii. Suggest the placement of the following devices with justification:
a. Internet Connecting Device/Modem
b. Switch
iii. The organisation is planning to link its sale counter situated in various parts of the same city, which type of
network out of LAN, MAN or WAN will be formed? Justify your answer.
54. Great Studies University is setting up its Academic schools at Sunder Nagar and planning to set up a network. [5]
The university has 3 academic schools and one administration centre as shown in the diagram below:

Center to center distances between various buildings is as follows:

Law School to Business School 60 m

Law School to Technology School 90 m

Law School to Admin Center 115 m

Business School to Technology School 40 m

25 / 36
 Business School to Admin Center 45 m

Technology School to Admin Center 25 m

Number of Computers in each of the Schools/Center is as follows:

Law School 25

Technology School 50

Admin Center 125

Business School 35

i. Suggest the most suitable place (i.e., Schools/Center) to install the server of this university with a suitable
reason.
ii. Suggest the most efficient connecting medium for connecting these Schools/center for wired connectivity.
iii. Which device you will suggest to be placed/installed in each of these Schools! center to efficiently connect
all the computers within these Schools/center?
iv. The university is planning to connect its admission office in the closest big city, which is more than 350 km
from the university. Which type of network out of LAN, MAN or WAN will be formed? Justify your answer.
55. Quick Learn University is setting up its Academic blocks at Prayag Nagar and planning to set up a network. The [5]
university has 3 academic blocks and one Human Resource Center as shown in the diagram below:

Center to center distances between various blocks is as follows:

Law Block to Business Block 40 m

Law Block to Technology Block 80 m

Law Block to HR Center 105 m

Business Block to Technology Block 30 m

Business Block to HR Center 35 m

Technology Block to HR Center 15 m

Number of Computers in each of the Blocks/Center is as follows:

Law Block 15

Technology Block 40

HR Center 115

Business Block 25

i. Suggest the most suitable place (i.e., Block/Center) to install the server of this university with a suitable
reason.

26 / 36
 ii. What type of network will be formed if all these blocks are connected?
iii. Which device you will suggest to be placed/installed in each of these blocks/center to efficiently connect all
the computers within these blocks/center?
iv. The university is planning to connect its admission office in the closest big city, which is more than 250 km
from university, which type of network out of LAN, MAN or WAN will be formed? Justify your answer.
56. Ayurveda Training Educational Institute is setting up its Centre in Hyderabad with three specialized departments [5]
for Orthopaedics, Neurology and Paediatrics along with an administrative office in separate buildings. The
physical distances between these department buildings and the member of computers to be installed in these
departments and administrative offices are given as follows. You, as a network expert have the shortest distances
between various locations in meters:

Administrative office to Orthopaedics unit 55

Neurology unit to Administrative office 30

Orthopedics unit to Neurology unit 70

Paediatrics unit to Neurology unit 50

Pediatrics unit to Administrative office 40

Paediatrics unit to Orthopaedics unit 110

The number of Computers installed at the various location is as follows:

Paediatrics unit 40

Administrative office 140

Neurology unit 50

Orthopedics unit 80

i. Suggest the most suitable location for the main server of this institution to get efficient connectivity.
ii. Suggest the cable layout for effective network connectivity of the building having a server with all the other
buildings.
iii. Suggest a device to be installed in each of these building for connecting computers installed within the
building out of the following:
Gateway
Modem
Switch
iv. Suggest the topology of the network and network cable for efficiently connecting in each of the building one
of the following: Topologies: Bus Topology, Star Topology, Network Cable: Single Pair Telephone Cable,
Coaxial Cable, Ethernet Cable.
57. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), Which are based on the table. [5]
Table: CUSTOMER

27 / 36
 CNO CNAME ADDRESS

101 Richa Jain Delhi

102 Surbhi Sinha Chennai

103 Lisa Thomas Bangalore

104 Imran Ali Delhi

105 Roshan Singh Chennai

Table: TRANSACTION

TRNO CNO AMOUNT TYPE DOT

T001 101 1500 Credit 2017-11-23

T002 103 2000 Debit 2017-05-12

T003 102 3000 Credit 2017-06-10

T004 103 12000 Credit 2017-09-12

T005 101 1000 Debit 2017-09-05

i. To display details of all transactions of TYPE Credit from Table TRANSACTION.

ii. To display the CNO and AMOUNT of all Transactions done in the month of September 2017 from table
TRANSACTION.
iii. To display the last dale of transaction (DOT) front the table TRANSACTION for the customer having CNO
as 103.
iv. To display all CNO CNAME and DOT (date of transaction) of those CUSTOMERS fron, tables
CUSTOMER and TRANSACTION who have done transactions more than or equal to 2000.
v. SELECT COUNT(*), AVG (AMOUNT) FROM TRANSACTION WHERE DOT > = '2017-06-01'
vi. SELECT CNO, COUNT(*), MAX (AMOUNT) FROM TRANSACTION GROUP BY CNO HAVING
COUNT (*)> 1
vii. SELECT CNO, CNAME FROM CUSTOMER WHERE ADDRESS NOT IN ('DELHI', BANGALORE )
viii. SELECT DISTINCT CNO FROM TRANSACTION
58. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables: [5]
TABLE: BOOK

Code BNAME TYPE

F101 The Priest Fiction

L102 German easy Literature

C101 Tarzan in the lost world Comic

F102 Untold story Fiction

C102 War heroes Comic

TABLE: MEMBER

MNO MNAME CODE ISSUEDATE

M101 RAGHAV SINHA LI 02 2016-10-13

28 / 36
 M103 S ARTHAKJ OHN FI 02 2017-02-23

M102 ANISHA KHAN C101 2016-06-12

i. To display all details from table MEMBER in descending order of ISSUEDATE.

ii. To display the BNO and BNAME of all Fiction Type books from the table Book.
iii. To display the TYPE and number of books in each TYPE from the table BOOK.
iv. To display all MNAME and ISSUEDATE of those members from table MEMBER who have books issued
(i.e., ISSUEDATE) in the year 2017.
v. SELECT MAX (ISSUEDATE) FROM MEMBER
vi. SELECT DISTINCT TYPE FROM BOOK
vii. SELECT [Link], BNAME, MNO. MNAME FROM BOOK A. MEMBER B WHERE [Link]=
[Link]
viii. SELECT BNAME FROM BOOK WHERE TYPE NOT IN ("FICTION", "COMIC")
59. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables [5]
TRANSPORT and TRIP.
TABLE: TRANSPORT

TCODE TTYPE PERKM

103 ORDINARY BUS 90

105 SUV 40

104 CAR 20

103 ORDINARY BUS 90

101 VOLVO BUS 160

102 AC DELUXE BUS 140

Note:
PERKM is Freight Charges per kilometre
TTYPE is Transport Vehicle Type
TABLE: TRIP

NO NAME TDATE KM TCODE NOP

11 Tanish Khan 2015-12-13 200 101 32

13 Danish Sahai 2016-06-21 100 103 45

15 Ram Kumar 2016-02-23 350 102 42

12 Fen Shen 2016-01-13 90 102 40

17 Aan Kumar 2015-02-10 75 104 2

14 Veena 2016-06-28 80 105 4

16 Raj pal Kirti 2016-06-06 200 101 25

Note:
• NO is Driver Number

29 / 36
 • KM is Kilometre travelled
• NOP is number of travellers travelled in a vehicle
• TDATE is Trip Date
i. To display NO, NAME, TDATE from the table TRIP in descending order of NO.
ii. To display the NAME of the drivers from the table TRIP, who are travelling by transport vehicle with code
101 or 103.
iii. To display the NO and NAME of those drivers from the table TRIP who travelled between 2015-02-10 and
2015-04-01.
iv. To display all the details from table TRIP in which the distance travelled is more than 100 KM in ascending
order of NOP
v. SELECT COUNT (*), TCODE From TRIP
GROUP BY TCODE HAVING COUNT (*) > 1;
vi. SELECT DISTINCT TCODE from TRIP;
vii. SELECT [Link], NAME, TTYPE
FROM TRIP A, TRANSPORT B
WHERE [Link] = B. TCODE AND KM < 90;
viii. SELECT NAME, KM * PERKM
FROM TRIP A, TRANSPORT B
WHERE A. TCODE = B. TCODE AND A. TCODE = '105';
60. Write queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables [5]
Table: VEHICLE

VCODE VEHICLETYPE PERKM

V01 VOLVO BUS 150

V02 AC DELUXE BUS 125

V03 ORDINARY BUS 80

V05 SUV 30

V04 CAR 18

Table: TRAVEL

CNO CNAME TRAVELDATE KM VCODE NOP

101 K. Niwal 2015-12-13 200 V01 32

103 Fredrick Sym 2016-03-21 120 V03 45

105 Hitesh Jain 2016-04-23 450 V02 42

102 Ravi Anish 2016-01-13 80 V02 40

107 John Malina 2015-02-10 65 V04 2

104 Sahanubhuti 2016-01-28 90 V05 4

106 Ramesh Jaya 2016-04-06 100 V01 25

30 / 36
 Note:
• PERKM is Freight Charges per kilometre.
• Km is kilometres Travelled
• NOP is number of passengers travelled in vehicle.
i. To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO
ii. To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code
V01 or V02
iii. To display the CNO and CNAME of those customers from the table TRAVEL who travelled between 2015-
12- 31 and 2015-05-01
iv. To display all the details from table TRAVEL for the customers, who have travel distance more than 120 KM
in ascending order of NOP
v. SELECT COUNT (*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT (*) > 1
vi. SELECT DISTINCT VCODE FROM TRAVEL
vii. SELECT [Link], CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B WHERE A. VCODE =
B. VCODE and KM < 90
viii. SELECT CNAME, KM*PERKM FROM TRAVEL A, VEHICLE B WHERE [Link] = [Link] AND
A. VCODE 'V05'
61. Consider the following tables CARDEN and CUSTOMER and answer (b) and (c) parts of this question: [5]
Table: CARDEN

Ccode CarName Make Colour Capacity Charges

501 A-Star Suzuki RED 3 14

503 Indigo Tata SILVER 3 12

502 Innova Toyota WHITE 7 15

509 SX4 Suzuki SILVER 4 14

510 C Class Mercedes RED 4 35

Table: CUSTOMER

Code Cname Ccode

1001 Hemant Sahu 501

1002 Raj Lai 509

1003 Feroza Shah 503

1004 Ketan Dhal 502

a. Give a suitable example of a table with sample data and illustrate Primary and Alternate Keys in it.
b. Write SQL commands for the following statements:
a. To display the names of all the silver-colored cars.
b. To display names of car, make and capacity of cars in descending order of their sitting capacity.
c. To display the highest charges at which a vehicle can be hired from CARDEN.
d. To display the customer name and the corresponding name of the cars hired by them.
c. Give the output of the following SQL queries:

31 / 36
 i. SELECT COUNT(DISTINCT Make)FROM CARDEN;
ii. SELECT MAX(Charges), MIN(Charges) FROM CARDEN;
iii. SELECT COUNT(*), Make FROM CARDEN;
iv. SELECT CarName FROM CARDEN WHERE Capacity=4;
62. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables: [5]
DVD

DCODE DTITLE DTYPE

F101 Henry Martin Folk

Cl 02 Dhrupad Classical

C101 The Planets Classical

F102 Universal Soldier Folk

R102 A day in the life Rock

MEMBER

MID NAME DCODE ISSUEDATE

101 AGAM SINGH R102 2017-11-30

103 ARTH JOSEPH F102 2016-12-13

102 NISHA HANS C101 2017-07-24

i. To display all details from the table MEMBER in descending order of ISSUEDATE.
ii. To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.
iii. To display the Dtype and number of DVDs in each DTYPE from the table DVD.
iv. To display all NAME and ISSUEDATE of those members from the table MEMBER who have DVDs issued
(i.e., ISSUEDATE) in the year 2017.
v. SELECT MIN (ISSUEDATE) FROM MEMBER;
vi. SELECT DISTINCT DTYPE FROM DVD;
vii. SELECT [Link]. NAME, DTITLE: FROM DVD D, MEMBER M WHERE [Link]=[Link];
viii. SELECT DTITLE FROM DVD WHERE DTYPE NOT IN ("Folk", "Classical");
63. Write SQL commands for the queries (i) to (iv) and output for (v) to (viii) based on the tables 'Watches' and Sale [5]
given below.
Watches

Watchid WatchName Price Type Qty_Store

W001 High Time 10000 Unisex 100

W002 Life Time 15000 Ladies 150

W003 Wave 20000 Gents 200

W004 High Fashion 7000 Unisex 250

W005 Golden Time 25000 Gents 100

Sale

32 / 36
 Watchid Qty_Sold Quarter

W001 10 1

W003 5 1

W002 20 2

W003 10 2

W001 15 3

W002 20 3

W005 10 3

W003 15 4

i. TO DISPLAY ALL THE DETAILS OF THOSE WATCHES WHOSE NAME ENDS WITH TIME.
ii. TO DISPLAY WATCH'S NAME AND PRICE OF THOSE WATCHES WHICH HAVE PRICE RANGE IN
BETWEEN 5000-15000.
iii. TO DISPLAY TOTAL QUANTITY IN-STORE OF UNISEX TYPE WATCHES.
iv. TO DISPLAY WATCH NAME AND THEIR QUANTITY SOLD IN the FIRST QUARTER.
v. SELECT MAX (PRICE), MIN(QTY_STORE) FROM WATCHES;
vi. SELECT QUARTER, SUM(QTY_SOLD) FROM SALE GROUP BY QUARTER;
vii. SELECT WATCHNAME, PRICE, TYPE FROM WATCHES W, SALE S WHERE W. WATCHID!=
[Link];
viii. SELECT WATCHNAME, QTYSTORE, SUM (QTYSOLD), QTY_STORE - SUM (QTY_SOLD)
"STOCK" FROM WATCHES W, SALE S WHERE W. WATCHID = [Link] GROUP BY
[Link];
64. Write SQL queries for (i) to (vii) on the basis of table ITEMS and TRADERS: [5]
Table: ITEMS

ICODE INAME QTY PRICE COMPANY TCODE

1001 DIGITAL PAD 12i 120 11000 XENITA T01

1006 LED SCREEN 40 70 38000 SANTORA T02

1004 CAR GPS SYSTEM 50 21500 GEOKNOW T01

1003 DIGITAL CAMERA 12X 160 8000 DIGICLICK T02

1005 PEN DRIVE 32 GB 600 1200 STOREHOME T03

Table: TRADERS

TCode TName City

101 ELECTRONIC SALES MUMBAI

103 BUSY STORE CORP DELHI

102 DISP HOUSE INC CHENNAI

i. To display the details of all the items in ascending order of item names (i.e., INAME).

33 / 36
 ii. To display item name and price of all those items, whose price is in the range of 10000 and 22000 (both
values inclusive).
iii. To display the number of items, which are traded by each trader. The expected output of this query should be:
T01 2 T02 2 T03 1
iv. To display the price, item name and quantity (i.e., qty) of those items which have quantity more than 150.
v. To display the names of those traders, who are either from DELHI or from MUMBAI.
vi. To display the names of the companies and the names of the items in descending order of company names.
vii. Obtain the outputs of the following SQL queries based on the data given in tables ITEMS and TRADERS
above.
a. SELECT MAX (PRICE), MIN (PRICE) FROM ITEMS;
b. SELECT PRICE*QTY FROM ITEMS WHERE CODE=1004;
c. SELECT DISTINCT TCODE FROM ITEMS;
d. SELECT INAME, TNAME FROM ITEMS I, TRADERS T WHERE [Link]=[Link] AND
QTY<100;
65. Consider the following tables CABHUB and CUSTOMER and answer (b) and (c) parts of this question: [5]
Table: CABHUB

Vcode VehicleName Make Colour Capacity Charges

100 Innova Toyota WHITE 7 15

102 SX4 Suzuki BLUE 4 14

104 C Class Mercedes RED 4 35

105 A-Star Suzuki WHITE 3 14

108 Indigo Tata SILVER 3 12

Table: CUSTOMER

Ccode Cname Vcode

1 Hemant Sahu 101

2 Raj Lai 108

3 Feroza Shah 105

4 Ketan Dhal 104

a. Give a suitable example of a table with sample data and illustrate Primary and alternate Keys in it.
b. Write SQL commands for the following statements:
i. To display the names of all the white-colored vehicles.
ii. To display the name of vehicle name and the capacity of vehicles in ascending order of their sitting
capacity.
iii. To display the highest charges at which a vehicle can be hired from CABHUB.
iv. To display the customer name and the corresponding name of the vehicle hired by them.
c. Give the output of the following SQL queries:
i. SELECT COUNT (DISTINCT Make) FROM CABHUB;
ii. SELECT MAX(Charges), MIN(Charges) FROM CABHUB;

34 / 36
 iii. SELECT COUNT (*) Make FROM CABHUB;
iv. SELECT Vehicle FROM CABHUB WHERE Capacity=4;
66. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables. [5]
TRAINER

TID TNAME CITY HIREDATE SALARY

101 SUNAINA MUMBAI 1998-10-15 90000

102 ANAMIKA DELHI 1994-12-24 80000

103 DEEPTI CHANDIGARH 2001-12-21 82000

104 MEENAKSHI DELHI 2002-12-25 78000

105 RICHA MUMBAI 1996-01-12 95000

106 MANIPRABHA CHENNAI 2001-12-12 69000

COURSE

CID CNAME FEES STARTDATE TID

C201 AGDCA 12000 2018-07-02 101

C202 ADCA 15000 2018-07-15 103

C203 DCA 10000 2018-10-01 102

C204 DDTP 9000 2018-09-15 104

C205 DHN 20000 2018-08-01 101

C206 0 LEVEL 18000 2018-07-25 105

i. Display the Trainer Name, City & Salary in descending order of their Hiredate.
ii. To display the TNAME and CITY of Trainer who joined the Institute in the month of December 2001.
iii. To displayTNAME, HIREDATE, CNAME, STARTDATE from tables TRAINER and COURSE of all those
courses whose FEES is less than or equal to 10000. (iv) To display number of Trainers from each Ans. city.
iv. SELECT TID. TNAME, FROM TRAINER WHERE CITY NOT IN ('DELHT', 'MUMBAI');
v. SELECT DISTINCT TID EROM COURSE;
vi. SELECT TID, COUNT(*), MIN (FEES) FROM COURSE. CROUP BY TID HAVING COUNT(*)>1;
vii. SELECT COUNTS), SUM(FEES) FROM COURSE WHERE STARTDATE< '2018-09- 15';
67. Write SQL queries for (i) to (vii) on the basis of tables given below: [5]
Table: PRODUCTS

PID PNAME QTY PRICE COMPANY SUPCODE

101 DIGITAL CAMERA 14X 120 12000 RENBIX SOI

102 DIGITAL PAD l l i 100 22000 DIGI POP S02

104 PEN DRIVE 16 GB 500 1100 STOREKING SOI

106 LED SCREEN 32 70 28000 DISPEXPERTS S02

105 CAR GPS SYSTEM 60 12000 MOVEON S03

35 / 36
 Table: SUPPLIERS

SUPCODE SNAME CITY

S01 GET ALL INC KOLKATA

S03 EASY MARKET CORP DELHI

S02 DIGI BUSY GROUP CHENNAI

i. To display the details of all the products in ascending order of product names (i.e., PNAME).
ii. To display product name and price of all those products, whose price is in the range of 10000 and 15000
(both values inclusive).
iii. To display the number of products, which are supplied by each supplier, i.e., the expected output should be;
S01 2
S02 2
S03 1
iv. To display the price, product name, and quantity (i.e., qty) of those products which have a quantity of more
than 100.
v. To display the names of those suppliers, who are either from DELHI or from CHENNAI.
vi. To display the name of the companies and the name of the products in descending order of company names.
vii. Obtain the outputs of the following SQL queries based on the data given in tables PRODUCTS and
SUPPLIERS above.
a. SELECT DISTINCT SUPCODE FROM PRODUCTS;
b. SELECT MAX (PRICE), MIN (PRICE) FROM PRODUCTS;
c. SELECT PRICE*QTY FROM PRODUCTS WHERE PID = 104;
d. SELECT PNAME, SNAME FROM PRODUCTS P, SUPPLIERS S WHERE P. SUPCODE = S.
SUPCODE AND QTY >100;

36 / 36