Get the step-by-step solution for this question inside the Vidyadip app.
Get the answer in the appGenerate a complete, print-ready paper with questions like this in minutes — across 16+ boards, with answer keys.
Answer the (a) and (b) on the basis of the following tables STORE and ITEM:
Table: STORE
| SNo | SName | AREA |
| S01 | ABC Computronics | GK II |
| S02 | All Infotech Media | CP |
| S03 | Tech Shoppe | Nehru Place |
| S05 | Hitech Tech Store | CP |
Table: ITEM
| INo | IName | Price | SNo |
| T01 | Mother Board | 12000 | S01 |
| T02 | Hard Disk | 5000 | S01 |
| T03 | Keyboard | 500 | S02 |
| T04 | Mouse | 300 | S01 |
| T05 | Mother Board | 13000 | S02 |
| T06 | Key Board | 400 | S03 |
| T07 | LCD | 6000 | S04 |
| T08 | LCD | 5500 | S05 |
| T09 | Mouse | 350 | S05 |
| T10 | Hard disk | 4500 | S03 |
(a) Write the SQL queries (1 to 4):
(i) To display IName and Price of all the items in the ascending order of their Price.
(ii) To display the SNo and SName or all stores located in CP.
(iii) To display the minimum and maximum price of each IName from the table Item.
(iv) To display the IName, price of all items and their respective SName where they are available.
(b) Write the output of the following SQL commands (i) to (iv):
(i) Select distinct iname from item where price >= 5000;
(ii) Select area, count(*) from store group by area;
(iii) Select count(distinct area) from store;
(iv) Select iname, price*0.05 discount from item where sno in (‘s02 ’, ‘s03 ’);
As a network consultant, you have to suggest the best network related solutions for their issues/
problems raised in (a) to (d) 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 centers, in which one room has been given as training center to this organization to install computers.
• The organization has got financial support from the government and top IT companies.
(a) 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.
(b) Suggest the best wired medium and draw the cable layout (location to location) to efficiently connect various locations within the B_HUB.
(c) Which hardware device will you suggest to connect all the computers within each location of B_HUB?
(d) Which service/protocol will be most helpful to conduct live interactions of Experts from Head Office and people at all locations of B_HUB?
Table: HOSPITAL
| PNo | Name | Age | Department | DateofAdm | Charges | Sex |
| 1 | Mayank | 65 | Surgery | 23/02/2018 | 600 | M |
| 2 | Babita | 24 | ENT | 01/01/2019 | 400 | F |
| 3 | Kashish | 45 | Orthopaedic | 19/12/2018 | 400 | M |
| 4 | Tarun | 12 | Surgery | 01/10/2018 | 600 | M |
| 5 | Manisha | 36 | ENT | 12/01/2018 | 400 | F |
| 6 | Imran | 16 | ENT | 24/02/2018 | 400 | M |
| 7 | Ankita | NULL | Cardiology | 20/08/2018 | 800 | F |
| 8 | Zoya | 45 | Gynecology | 22/02/2018 | 500 | F |
| 9 | Kush | 19 | Cardiology | 13/01/2019 | 800 | M |
| 10 | Shalini | 31 | Medicine | 19/02/2018 | 300 | F |
Note: PNo is the primary key in the above table.
Write SQL commands for the statements (a) to (g) on the table HOSPITAL.
(a) To display the details of all the patients whose name starts with the alphabet ‘Z’.
(b) To change the age of the patient Kush to 20.
(c) To increase the charges of all the patients by 5%.
(d) To remove the record of the patient whose Name is Tarun.
(e) To add another column DocName(Doctor Name) of the type varchar in the above table.
(f) To display patient detail whose age is missing(null).
(g) To decrease the charges by 5% of all the patients admitted to the ENT department.