Computer Science
Consider the table Student1 of Q. 13. Give the output of following SQL statement :
- SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75 ;
- SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B' ;
- SELECT CONCAT(Name, Stream) FROM Student1 WHERE Class = '12A' ;
- SELECT RIGHT(Stream, 2) FROM Student1 ;
Answer
1. It will return error because no argument is passed as decimal places to truncate. Syntax of truncate function is TRUNCATE(number, decimals).
2.
Output
+----------------+
| ROUND(AvgMark) |
+----------------+
| 78 |
| 73 |
| 75 |
+----------------+
3.
Output
+----------------------+
| CONCAT(Name, Stream) |
+----------------------+
| RubinaNonmedical |
| VikasNonmedical |
+----------------------+
4.
Output
+------------------+
| RIGHT(Stream, 2) |
+------------------+
| al |
| ce |
| ce |
| es |
| al |
| al |
| es |
| al |
| al |
| ce |
+------------------+
Related Questions
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.
Given the following table :
Table : STUDENT
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 Give the output of following SQL statements :
- SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75 ;
- SELECT SUM(Stipend) FROM STUDENT WHERE Grade = 'B' ;
- SELECT AVG(Stipend) FROM STUDENT WHERE Class = '12A' ;
- SELECT COUNT(DISTINCT) FROM STUDENT ;
Write SQL commands for the following on the basis of given table MOV :
Table : MOV
No Title Type Rating Stars Qty Price 1 Gone with the Wind Drama G Gable 4 39.95 2 Friday the 13th Horror R Jason 2 69.95 3 Top Gun Drama PG Cruise 7 49.95 4 Splash Comedy PG13 Hanks 3 29.95 5 Independence Day Drama R Turner 3 19.95 6 Risky Business Comedy R Cruise 2 44.95 7 Cocoon Scifi PG Ameche 2 31.95 8 Crocodile Dundee Comedy PG13 Harris 2 69.95 9 101 Dalmatians Comedy G 3 59.95 10 Tootsie Comedy PG Hoffman 1 29.95 - Display a list of all movies with Price over 20 and sorted by Price.
- Display all the movies sorted by QTY in decreasing order.
- Display a report listing a movie number, current value and replacement value for each movie in the above table. Calculate the replacement value for all movies as : QTY * Price * 1.15.