[Assignments] Simple Queries in SQL — Chapter 12 Assignments (Q/A)
Table of Contents
17. Write SQL commands for the following on the basis of given 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.
Solution:
SELECT * FROM MOV
WHERE Price > 20
ORDER BY Price ;SELECT * FROM MOV
ORDER BY Qty DESC ;SELECT No as 'Movie Number', Price as 'Current Value', Qty * Price * 1.15 as 'Replacement Value'
FROM MOV ;