Question
Write a menu driven program to do the following task:
1. Insert
2. Delete
3. Update
4. Display all
5. Search a Records
6. Create table
1. Insert
2. Delete
3. Update
4. Display all
5. Search a Records
6. Create table
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.
Write a program to create the list and ask user whether you want to delete an element if say yes then delete the element and print the new list else print the entered list.
Write the SQL query questions from (i) to (iv) and write the output of SQL command for questions from (v) to (vii) given below:
Table: EMPLOYEES
| EMPID | FIRST NAME | LAST NAME | ADDRESS | CITY |
| 010 | George | Smith |
83 First Street |
Howard |
| 105 | Mary | Jones |
842 Vine Ave. |
Losantiville |
| 152 | Sam | Tones | 33 Elm St. | Paris |
| 215 | Sarah | Ackerman | 440 U.S. 110 | Upton |
| 244 | Manila | Sengupta |
24 Friends Street |
New Delhi |
| 300 | Robert | Samuel | 9 Fifth Cross | Washington |
| 335 | Henry | Williams |
12 Moore Street |
Boston |
| 400 | Rachel | Lee |
121 Harrison St. |
New York |
| 441 | Peter | Thompson | 11 Red Road | Paris |
Table: EMPSALARY
| EMPID | SALARY | BENEFITS | DESIGNATION |
| 010 | 75000 | 15000 | Manager |
| 105 | 65000 | 15000 | Manager |
| 152 | 80000 | 25000 | Director |
| 215 | 75000 | 12500 | Manager |
| 244 | 50000 | 12000 | Clerk |
| 300 | 45000 | 10000 | Clerk |
| 355 | 40000 | 10000 | Clerk |
| 4000 | 32000 | 7500 | Salesman |
| 441 | 28000 | 7500 | Salesman |
Write the SQL commands for the following :
(i) To show firstname, lastname, address and city of all employees living in paris.
(ii) To display the content of Employees table in ascending order of Firstname.
(iii) To display the firstname,lastname and total salary of all managers from the tables employee and empsalary, where total salary is calculated as salary+benefits.
(iv) To display the maximum salary among managers and clerks from the table Empsalary.
Give the Output of following SQL commands:
(v) Select firstname, salary from employees, empsalary where designation = ‘Salesman ’and Employees.
empid=Empsalary.empid;
(vi) Select count(distinct designation) from empsalary;
(vii) Select designation, sum(salary) from empsalary group by designation having count(*) >2;
Answer the following questions:
Students
| Adm No | Name | Class | Sec | R No. | Address | Phone |
| 1271 | Utkarsh Madam | 12 | C | 1 | C-32, Punjabi Bagh | 4356154 |
| 1324 | Naresh Sharma | 10 | A | 1 | 31, Mohan Nagar | 435654 |
| 1325 | Md. Yusuf | 10 | A | 2 | 12/12, Chand Nagar | 145654 |
| 1328 | Sumedha | 10 | B | 23 | 59, Moti Nagar | 4135654 |
| 1364 | Subya Akhtar | 11 | B | 13 | 12, Janak Puri | Null |
| 1434 | Varuna | 12 | B | 21 | 69, Rohini | Null |
| 1461 | David DSouza | 11 | B | 1 | D-34, Model Town | 243554. 98787665 |
| 2324 | Satinder Singh | 12 | C | 1 | 1/2, Gulmohar Park | 143654 |
| 2328 | Peter Jones | 10 | A | 18 | 21/328, Vishal Enclave | 24356154 |
| 2371 | Mohini Mehta | 11 | C | 12 | 37, Raja Garden | 435654, 6765787 |
Sports
| Adm No | Game | CoachName | Grade |
| 1324 | Cricket | Narendra | A |
| 1326 | Volleball | M.P. Singh | A |
| 1271 | Volleball | M.P. Singh | B |
| 1434 | Basket Ball | I. Malhotra | B |
| 1461 | Cricket | Narendra | B |
| 2328 | Basket Ball | I. Malhotra | A |
| 2371 | Basket Ball | I. Malhotra | A |
| 1271 | Basket Ball | I. Malhotra | A |
| 1434 | Cricket | Narendra | A |
| 2328 | Cricket | Narendra | B |
| 1364 | Basket Ball | I. Malhotra | B |
(a) Based on these tables write SQL statements for the following queries:
(i) Display the lowest and the highest classes from the table STUDENTS.
(ii) Display the number of students in each class from the table STUDENTS.
(iii) Display the number of students in class 10.
(iv) Display details of the students of Cricket team.
(v) Display the Admission number, name, class, section, and roll number of the students whose grade in Sports table is 'A'.
(vi) Display the name and phone number of the students of class 12 who are play some game.
(vii) Display the Number of students with each coach.
(viii) Display the names and phone numbers of the students whose grade is 'A' and whose coach is Narendra.
(b) Identify the Foreign Keys (if any) of these tables. Justify your choices.
(c) Predict the output of each of the following SQL statements, and then verify the output by actually entering these statements:
(i) SELECT class, sec, count(*) FROM students GROUP BY class, sec;
(ii) SELECT Game, COUNT(*) FROM Sports GROUP BY Game;]
(iii) SELECT Game FROM students, Sports WHERE students.admno = sports.admno AND Students.AdmNo = 1434;
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 show all the information about the patients of the cardiology department.
(b) To list the names of female patients who are either in the orthopaedic or surgery department.
(c) To list the name of all the patients with their date of admission in ascending order.
(d) To display the patient’s name, charges, the age for female patients only.
(e) To count the number of patients with age > 30.
(f) To display various departments.
(g) To display the number of patients in each department.