Informatics Practices
Write SQL commands for the following on the basis of given table STUDENT1.
Table : STUDENT1
| No. | Name | Stipend | Stream | AvgMark | Grade | Class |
|---|---|---|---|---|---|---|
| 1 | Karan | 400.00 | Medical | 78.5 | B | 12B |
| 2 | Divakar | 450.00 | Commerce | 89.2 | A | 11C |
| 3 | Divya | 300.00 | Commerce | 68.6 | C | 12C |
| 4 | Arun | 350.00 | Humanities | 73.1 | B | 12C |
| 5 | Sabina | 500.00 | Nonmedical | 90.6 | A | 11A |
| 6 | John | 400.00 | Medical | 75.4 | B | 12B |
| 7 | Robert | 250.00 | Humanities | 64.4 | C | 11A |
| 8 | Rubina | 450.00 | Nonmedical | 88.5 | A | 12A |
| 9 | Vikas | 500.00 | Nonmedical | 92.0 | A | 12A |
| 10 | Mohan | 300.00 | Commerce | 67.5 | C | 12C |
(a) Select all the Nonmedical stream students from STUDENT1.
(b) List the names of those students who are in class 12 sorted by Stipend.
(c) List all students sorted by AvgMark in descending order.
SQL Queries
2 Likes
Answer
(a)
SELECT *
FROM STUDENT1
WHERE Stream = 'Nonmedical' ;
+-----+--------+---------+------------+---------+-------+-------+
| No. | Name | Stipend | Stream | AvgMark | Grade | Class |
+-----+--------+---------+------------+---------+-------+-------+
| 5 | Sabina | 500 | Nonmedical | 90.6 | A | 11A |
| 8 | Rubina | 450 | Nonmedical | 88.5 | A | 12A |
| 9 | Vikas | 500 | Nonmedical | 92.0 | A | 12A |
+-----+--------+---------+------------+---------+-------+-------+
(b)
SELECT Name
FROM STUDENT1
WHERE Class LIKE '12%'
ORDER BY Stipend ;
+--------+
| Name |
+--------+
| Divya |
| Mohan |
| Arun |
| Karan |
| John |
| Rubina |
| Vikas |
+--------+
(c)
SELECT *
FROM STUDENT1
ORDER BY AvgMark DESC ;
+-----+---------+---------+------------+---------+-------+-------+
| No. | Name | Stipend | Stream | AvgMark | Grade | Class |
+-----+---------+---------+------------+---------+-------+-------+
| 9 | Vikas | 500 | Nonmedical | 92.0 | A | 12A |
| 5 | Sabina | 500 | Nonmedical | 90.6 | A | 11A |
| 2 | Divakar | 450 | Commerce | 89.2 | A | 11C |
| 8 | Rubina | 450 | Nonmedical | 88.5 | A | 12A |
| 1 | Karan | 400 | Medical | 78.5 | B | 12B |
| 6 | John | 400 | Medical | 75.4 | B | 12B |
| 4 | Arun | 350 | Humanities | 73.1 | B | 12C |
| 3 | Divya | 300 | Commerce | 68.6 | C | 12C |
| 10 | Mohan | 300 | Commerce | 67.5 | C | 12C |
| 7 | Robert | 250 | Humanities | 64.4 | C | 11A |
+-----+---------+---------+------------+---------+-------+-------+
Answered By
2 Likes
Related Questions
Consider the following table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv).
Table : FITNESS
PCODE PNAME PRICE Manufacturer P1 Treadmill 21000 Coscore P2 Bike 20000 Aone P3 Cross Trainer 14000 Reliable P4 Multi Gym 34000 Coscore P5 Massage Chair 5500 Regrosene P6 Belly Vibrator Belt 6500 Ambawya (i) To display the names of all the products with price more than 20000.
(ii) To display the names of all products by the manufacturer "Aone".
(iii) To change the price data of all the products by applying 25% discount reduction.
(iv) To add a new row for product with the details :
"P7", "Vibro Exerciser", 28000, "Aone".Write SQL commands for the following on the basis of given table CLUB :
Table : CLUB
COACH_ID COACHNAME AGE SPORTS PAY SEX DATOFAPP 1 KUKREJA 35 KARATE 1000 M 1996-03-27 2 RAVINA 34 KARATE 1200 F 1998-01-20 3 KARAN 34 SQUASH 2000 M 1998-02-19 4 TARUN 33 BASKETBALL 1500 M 1998-01-01 5 ZUBIN 36 SWIMMING 750 M 1998-01-12 6 KETAKI 36 SWIMMING 800 F 1998-02-24 7 ANKITA 39 SQUASH 2200 F 1998-02-20 8 ZAREEN 37 KARATE 1100 F 1998-02-22 9 KUSH 41 SWIMMING 900 M 1998-01-13 10 SHAILYA 37 BASKETBALL 1700 M 1998-02-19 - To show all information about the swimming coaches in the club.
- To list names of all coaches with their date of appointment (DATOFAPP) in descending order.
- To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.
What is foreign key ? How do you define a foreign key in your table ?
How is FOREIGN KEY commands different from PRIMARY KEY command ?