Computer Science
Write SQL commands for the following on the basis of given table SPORTS :
Table : SPORTS
| StudentNo. | Class | Name | Game1 | Grade1 | Game2 | Grade2 |
|---|---|---|---|---|---|---|
| 10 | 7 | Sameer | Cricket | B | Swimming | A |
| 11 | 8 | Sujit | Tennis | A | Skating | C |
| 12 | 7 | Kamal | Swimming | B | Football | B |
| 13 | 7 | Venna | Tennis | C | Tennis | A |
| 14 | 9 | Archana | Basketball | A | Cricket | A |
| 15 | 10 | Arpit | Cricket | A | Athletics | C |
- Display the names of the students who have grade 'C' in either Game1 or Game2 or both.
- Display the names of the students who have same game for both Game1 and Game2.
- Display the games taken up by the students, whose name starts with 'A'.
Answer
1.
SELECT Name
FROM SPORTS
WHERE Grade1 = 'C' OR Grade2 = 'C' ;
Output
+-------+
| Name |
+-------+
| Sujit |
| Venna |
| Arpit |
+-------+
2.
SELECT Name
FROM SPORTS
WHERE Game1 = Game2 ;
Output
+-------+
| Name |
+-------+
| Venna |
+-------+
3.
SELECT Game1, Game2
FROM SPORTS
WHERE Name LIKE 'A%' ;
Output
+------------+-----------+
| Game1 | Game2 |
+------------+-----------+
| Basketball | Cricket |
| Cricket | Athletics |
+------------+-----------+
Related Questions
Write a query that selects all orders (Order table) except those with zeros or NULLs in the amt field.
Write SQL commands for the following on the basis of given table STUDENT :
Table : STUDENT
StudentNo. Class Name GAME Grade1 SUPW Grade2 10 7 Sameer Cricket B Photography A 11 8 Sujit Tennis A Gardening C 12 7 Kamal Swimming B Photography B 13 7 Veena Tennis C Cooking A 14 9 Archana Basket Ball A Literature A 15 10 Arpit Cricket A Gardening C - Display the names of the students who are getting a grade 'C' in either GAME or SUPW.
- Display the different games offered in the school.
- Display the SUPW taken up by the students, whose name starts with 'A'.
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.
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 - Select all the Nonmedical stream students from STUDENT1.
- List the names of those students who are in class 12 sorted by Stipend.
- List all students sorted by AvgMark in descending order.
- Display a report, listing Name, Stipend, Stream and amount of stipend received in a year assuming that the Stipend is paid every month.