Computer Science
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 ;
SQL Queries
18 Likes
Answer
1.
Output
+--------------+
| MIN(AvgMark) |
+--------------+
| 64.4 |
+--------------+
2.
Output
+--------------+
| SUM(Stipend) |
+--------------+
| 1150 |
+--------------+
3.
Output
+--------------+
| AVG(Stipend) |
+--------------+
| 475 |
+--------------+
4. It will give an error because the COUNT function requires an argument specifying what to count. Additionally, the DISTINCT keyword is followed by a column name to count the distinct values of that column.
Answered By
12 Likes
Related Questions
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.
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 ;
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.
Write SQL commands for the following on the basis of given table Teacher :
Table : Teacher
No Name Age Department Salary Sex Dateofjoin 1 Jugal 34 Computer 12000 M 1997-01-10 2 Sharmila 31 History 20000 F 1998-03-24 3 Sandeep 32 Maths 30000 M 1996-12-12 4 Sangeeta 35 History 40000 F 1999-07-01 5 Rakesh 42 Maths 25000 M 1997-09-05 6 Shyam 50 History 30000 M 1998-06-27 7 Shiv Om 44 Computer 21000 M 1997-02-25 8 Shalakha 33 Maths 20000 F 1997-07-31 - To show all information about the teacher of history department.
- To list the names of female teachers who are in Hindi department.
- To list names of all teachers with their date of joining in ascending order.