Bright Tutorials Bright Tutorials
CBSE Class 12 Computer Science Question 23 of 78

Simple Queries in SQL — Question 16

Back to all questions
16
Question

Question 16

Write SQL commands for the following on the basis of given table MOV :

Table : MOV

NoTitleTypeRatingStarsQtyPrice
1Gone with the WindDramaGGable439.95
2Friday the 13thHorrorRJason269.95
3Top GunDramaPGCruise749.95
4SplashComedyPG13Hanks329.95
5Independence DayDramaRTurner319.95
6Risky BusinessComedyRCruise244.95
7CocoonScifiPGAmeche231.95
8Crocodile DundeeComedyPG13Harris269.95
9101 DalmatiansComedyG359.95
10TootsieComedyPGHoffman129.95
  1. Display a list of all movies with Price over 20 and sorted by Price.
  2. Display all the movies sorted by QTY in decreasing order.
  3. 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 |
+--------------+---------------+--------------------+