SQL assignment Week 3

1.

SELECT Title FROM Books ORDER BY Cost

To find out: the cheapest and the most expencive book to produce with the lowest cost on the top

Fråga17
Great Expectations
Mind and Nature
The Cross
Silmarillion
Figures of Earth
Tobacco Road
Venus on the Half-Shell
Larions Law
Slaughter House 5
Death is a lonely business
SELECT Title FROM Books ORDER BY Cost DESC

To get the highest price on top and the lowest on bottom

Fråga18
Death is a lonely business
Larions Law
Slaughter House 5
Venus on the Half-Shell
Figures of Earth
Tobacco Road
The Cross
Silmarillion
Great Expectations
Mind and Nature
SELECT Title,Cost FROM Books ORDER BY Title,Cost

To sort by Title and Cost. Title first and if there is two Titles with the same name, the Title with the lowest cost is first.

Fråga19
Title Cost
Death is a lonely business 7
Figures of Earth 3
Great Expectations 0
Larions Law 5
Mind and Nature 0
Silmarillion 2
Slaughter House 5 5
The Cross 2
Tobacco Road 3
Venus on the Half-Shell 4

2

SELECT Sold, Count(*) FROM Books GROUP BY Sold

Number of books sold and the number of books that have this salesfigures

Fråga21
Sold Expr1001
0 2
100000 1
300000 1
500000 2
1000000 2
2000000 1
3000000 1
SELECT Sum(Sold) AS 'Number of books sold', Price FROM Books GROUP BY Price

At what price is the majority of the books sold

Fråga22
'Number of books sold' Price
0.00 0
3500000.00 15
1800000.00 20
1000000.00 25
100000.00 30
2000000.00 40

3

SELECT [Title], Round(Price-Cost, 0) AS SalesMargin FROM Books ORDER BY [Title];

What is the sales margin on books sold?

Fråga24
Title SalesMargin
Death is a lonely business 33.00
Figures of Earth 12.00
Great Expectations 0.00
Larions Law 15.00
Mind and Nature 0.00
Silmarillion 13.00
Slaughter House 5 15.00
The Cross 28.00
Tobacco Road 17.00
Venus on the Half-Shell 21.00

4

DELETE FROM Books WHERE Item = 8

Deleting Item nr8 because it have no sales and have never been printed by this company.

Books-8
1 Slaughter House 5 Kurt Vonnegut Jr. 1969-01-01 1000000 1000000 20 5
2 Death is a lonely business Ray Bradbury 1985-05-05 2000000 2000000 40 7
3 Tobacco Road Erskine Caldwell 1932-02-02 500000 500000 20 3
4 Silmarillion JRR. Tolkien 1979-01-02 3000000 3000000 15 2
5 Figures of Earth James Branch Cabell 1974-01-01 500000 500000 15 3
6 Larions Law Peter Freuchen 1981-01-01 300000 300000 20 5
7 The Cross Lars Molin 1905-05-31 100000 98000 30 2
9 Venus on the Half-Shell Kilgore Trout 1905-05-26 1000000 1000000 25 4
11 Great Expectations Charles Dickens 1860-01-01 0 0 0 0