31.

Uplifting Skills Hub India is a knowledge and skill community which has an aim to uplift the standard of knowledge and skills in the society. It is planning to set up its training centers 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 (1) to (4), keeping in mind the distances between various locations and other given parameters.

Shortest distances between various locations:

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

Number of Computers installed at various locations are as follows:

B_TOWN	120
VILLAGE 1	15
VILLAGE 2	10
VILLAGE 3	15
A_CITY OFFICE	6

Note:

• In Villages, there are community centres, in which one room has been given as training centre to this organization to install computers.
• The organization has got financial support from the government and top IT companies.

1. 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.
2. Suggest the best wired medium and draw the cable layout (location to location) to efficiently connect various locations within the B_HUB.
3. Which hardware device will you suggest to connect all the computers within each location of B_HUB?
4. Which service/protocol will be most helpful to conduct live interactions of Experts from Head Office and people at all locations of B_HUB?

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables

Table: VEHICLE

Note:

• PERKM is Freight Charges per kilometre
• VTYPE is Vehicle Type

Table: TRAVEL

Note:
• NO is Traveller Number
• KM is Kilometer travelled
• NOP is number of travellers travelled in vehicle
• TDATE is Travel Date

1. To display NO, NAME, TDATE from the table TRAVEL in descending order of NO.
2. To display the NAME of all the travellers from the table TRAVEL who are travelling by vehicle with code 101 or 102.
3. To display the NO and NAME of those travellers from the table TRAVEL who travelled between ‘2015‐12‐31’ and ‘2015‐04‐01’.
4. To display all the details from table TRAVEL for the travellers, who have travelled distance more than 100 KM in ascending order of NOP.
5. SELECT COUNT(*), CODE FROM TRAVEL
   GROUP BY CODE HAVING COUNT(*)>1;
6. SELECT DISTINCT CODE FROM TRAVEL;
7. SELECT A.CODE,NAME,VTYPE
   FROM TRAVEL A,VEHICLE B
   WHERE A.CODE=B.CODE AND KM<90;
8. SELECT NAME,KM*PERKM
   FROM TRAVEL A,VEHICLE B
   WHERE A.CODE=B.CODE AND A.CODE=’105’;