Computer Science
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.
Answer
1.
SELECT Title
FROM MOV
WHERE Price > 20
ORDER BY Price ;
Output
+--------------------+
| Title |
+--------------------+
| Splash |
| Tootsie |
| Cocoon |
| Gone with the Wind |
| Risky Business |
| Top Gun |
| 101 Dalmatians |
| Friday the 13th |
| Crocodile Dundee |
+--------------------+
2.
SELECT Title
FROM MOV
ORDER BY Qty DESC ;
Output
+--------------------+
| Title |
+--------------------+
| Top Gun |
| Gone with the Wind |
| Splash |
| Independence Day |
| 101 Dalmatians |
| Friday the 13th |
| Risky Business |
| Cocoon |
| Crocodile Dundee |
| Tootsie |
+--------------------+
3.
SELECT No AS Movie_Number , Price AS Current_Value, (Qty * Price * 1.15) AS Replacement_Value
FROM MOV ;
Output
+--------------+---------------+--------------------+
| Movie_Number | Current_Value | Replacement_Value |
+--------------+---------------+--------------------+
| 1 | 39.95 | 183.77000350952147 |
| 2 | 69.95 | 160.884992980957 |
| 3 | 49.95 | 402.09750614166256 |
| 4 | 29.95 | 103.3275026321411 |
| 5 | 19.95 | 68.8275026321411 |
| 6 | 44.95 | 103.38500175476074 |
| 7 | 31.95 | 73.48500175476073 |
| 8 | 69.95 | 160.884992980957 |
| 9 | 59.95 | 206.8275026321411 |
| 10 | 29.95 | 34.44250087738037 |
+--------------+---------------+--------------------+
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 ;
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 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.