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.
Consider the following table Student & Stream.
Table: Student
| Admno | Sname | Class | Sec | Fee | Mobile | Area | S_ID |
| 1001 | RAMESH | XII | A | 2500 | 987654321 | Madipur | 10 |
| 1078 | KRISHNA | XII | B | 2400 | 999911111 | Jawala Heri | 30 |
| 1006 | FARDEEN | XII | C | 2600 | 987654321 | Paschim Puri | 40 |
| 1004 | SUBHAM | XII | A | 2500 | 963025874 | Madipur | 20 |
| 1029 | KRITIKA | XI | C | 2700 | 987456210 | Madipur | 30 |
| 1008 | SAMEEKSHA | XII | A | 2450 | 987123456 | Mangol Puri | 20 |
| 1025 | SALMA | XII | B | 2580 | 998877445 | Madipur | 30 |
| 1036 | AMANDEEP | XII | B | 2600 | 999333555 | Khyala | 40 |
| 1037 | TEJAS | XI | C | 2650 | 987951357 | Paschim Puri | 40 |
| 1029 | HIMANSHU | XII | A | 2750 | 951369874 | Jawala Heri | 10 |
Table : Stream
| S_ID | Stream_name |
| 10 | MEDILCAL |
| 20 | NON MEDICAL |
| 30 | COMMERCE WITH MATH |
| 40 | COMMERCE WITH IP |
| 50 | HUMANITIES |
Write SQL commands for the statements (a) to (h) on the table: Student and Stream
(a) Display class and total fee collected from each class.
(b) Display admission no, students name and stream name.
(c) Display all the student details who have taken Commerce Stream.
(d) Count number of students who have opted for HUMANITIES stream.
(e) Display information of commerce with ip students whose name start with ‘S’. Arrange the record by admission number.
(f) Display details of all students who are in the MEDICAL stream.
(g) Display total fee of ‘Non-Medical’ Student.
(h) Change the name of the Column Sname to Student_Name.
Table: Student
| Admno | Sname | Class | Sec | Fee | Mobile | Area | S_ID |
| 1001 | RAMESH | XII | A | 2500 | 987654321 | Madipur | 10 |
| 1078 | KRISHNA | XII | B | 2400 | 999911111 | Jawala Heri | 30 |
| 1006 | FARDEEN | XII | C | 2600 | 987654321 | Paschim Puri | 40 |
| 1004 | SUBHAM | XII | A | 2500 | 963025874 | Madipur | 20 |
| 1029 | KRITIKA | XI | C | 2700 | 987456210 | Madipur | 30 |
| 1008 | SAMEEKSHA | XII | A | 2450 | 987123456 | Mangol Puri | 20 |
| 1025 | SALMA | XII | B | 2580 | 998877445 | Madipur | 30 |
| 1036 | AMANDEEP | XII | B | 2600 | 999333555 | Khyala | 40 |
| 1037 | TEJAS | XI | C | 2650 | 987951357 | Paschim Puri | 40 |
| 1029 | HIMANSHU | XII | A | 2750 | 951369874 | Jawala Heri | 10 |
Table : Stream
| S_ID | Stream_name |
| 10 | MEDILCAL |
| 20 | NON MEDICAL |
| 30 | COMMERCE WITH MATH |
| 40 | COMMERCE WITH IP |
| 50 | HUMANITIES |
Write SQL commands for the statements (a) to (h) on the above table: Student and Stream
(a) Identify Primary Keys and Foreign Key in the table student and primary key in Stream table given above.
(b) Display stream id and stream-wise total fee collected.
(c) Count no of students from each area.
(d) Display all the student details those who belongs to Madipur Area.
(e) Increase the fees of all students by10%.
(f) Display unique area from the student table.
(g) Display details of those students whose area contains ‘Puri’.
(h) Display the information of those students who are in class XII and section is either B or C.
Consider the following table Bank.
Table: Bank
| AccNo | Cust_name | FD_Amount | Months | Int_Rate | FD_Date |
| 1001 | Arti Gupta | 30000 | 36 | 6.00 | 2018-07-01 |
| 1002 | Dilip Lal | 50000 | 48 | 6.75 | 2018-03-22 |
| 1003 | Navin Gupta | 30000 | 36 | NULL | 2018-03-01 |
| 1004 | D.P. Yadav | 80000 | 60 | 8.25 | 2017-06-12 |
| 1005 | Jyoti Sharma | 20000 | 36 | 6.50 | 2017-01-31 |
| 1006 | Rakesh Kumar | 70000 | 60 | 8.25 | 2018-06-15 |
| 1007 | K.D. Singh | 50000 | 48 | NULL | 2018-07-05 |
| 1008 | Anjali Sharma | 60000 | 48 | 6.75 | 2017-04-02 |
| 1009 | Swati Garg | 40000 | 42 | 6.50 | 2018-06-15 |
| 1010 | Rupinder Kaur | 25000 | 36 | 6.50 | 2018-09-27 |
Write SQL commands for the statements (a) to (h) on the table Bank
(a) Display the details of all FD whose rate of interest is in the range 6% to 7%.
(b) Display the Customer Name and FD Amount for all the loans for which the number of Months is 24, 36, or 48(using IN operator).
(c) Display the Account Number, Customer Name and FD Amount for all the FD for which the Customer Name
ends with “Sharma”.
(d) Delete the records of “Rupinder Kaur”.
(e) Add another column Maturity_Amt of type Integer in the Bank table.
(f) To find the average FD amount. Label the column as “Average FD Amount”.
(g) To find the total FD amount which started in the year 2018?
(h) Update Maturity Amount of all bank customers.
a. Maturity Amount = (FD_Amount*Months* Int_rate)/(12*100)