SQL Assignment Week 4

1.

SELECT Books.Title, Books.Sold, Books.Printed, InStock.InventoryLocation FROM InStock, Books WHERE (((Books.Item)=[InStock].[Item]));

I want to know the number of books sold, the Titles and the inventory location

Fråga26
Title Sold Printed InventoryLocation
Body language 10000 25000 11120
Panjamon 20000 30000 11121
Dictionary of Trout Flies 30000 40000 11122
Slaughter House 5 1000000 1000000 11111
Death is a lonely business 2000000 2000000 11112
Tobacco Road 500000 500000 11113
Silmarillion 3000000 3000000 11114
Figures of Earth 500000 500000 11115
Larions Law 300000 300000 11116
The Cross 100000 98000 11117
Venus on the Half-Shell 1000000 1000000 11118
Great Expectations 230000 250000 11119


InStock
InventoryLocation InStock Item
11111 0 1
11112 0 2
11113 0 3
11114 0 4
11115 0 5
11116 0 6
11117 1 7
11118 0 9
11119 0 11
11120 1 12
11121 1 13
11122 1 14

2.

SELECT Title,Cost,Price,Printed,InventoryLocation FROM InStock, Books WHERE InStock.Item = Books.Item AND Price > 15;

I want to know what books have a price lower than 15

Fråga27
Title Cost Price Printed InventoryLocation
Panjamon 10 20 30000 11121
Dictionary of Trout Flies 10 20 40000 11122
Slaughter House 5 5 20 1000000 11111
Death is a lonely business 7 40 2000000 11112
Tobacco Road 3 20 500000 11113
Larions Law 5 20 300000 11116
The Cross 2 30 98000 11117
Venus on the Half-Shell 4 25 1000000 11118

3.

SELECT Books.Title, Books.Writer, Books.PublishingDate, Books.Price, InStock.InventoryLocation FROM InStock INNER JOIN Books ON InStock.Item = Books.Item WHERE Cost < 5

What books have a printing cost lower than 5

Fråga28
Title Writer PublishingDate Price InventoryLocation
Body language Julius Fast 2002-01-01 15 11120
Tobacco Road Erskine Caldwell 1932-02-02 20 11113
Silmarillion JRR. Tolkien 1979-01-02 15 11114
Figures of Earth James Branch Cabell 1974-01-01 15 11115
The Cross Lars Molin 1905-05-31 30 11117
Venus on the Half-Shell Kilgore Trout 1905-05-26 25 11118

4.

UPDATE Books SET Price = 20 WHERE Price < 20
table summary="SQL" before

I update the price for books with a lower price then 20 to 20, because I want a higher profit

Books3
Item Title Writer PublishingDate Printed Sold Price Cost
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 230000 250000 15 5
12 Body language Julius Fast 2002-01-01 10000 25000 15 4
13 Panjamon Jean-Yves Domalain 1971-03-03 20000 30000 20 10
14 Dictionary of Trout Flies John Roberts 1988-05-03 30000 40000 20 10


table summary="SQL" after
Books4
Item Title Writer PublishingDate Printed Sold Price Cost
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 20 2
5 Figures of Earth James Branch Cabell 1974-01-01 500000 500000 20 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 230000 250000 20 5
12 Body language Julius Fast 2002-01-01 10000 25000 20 4
13 Panjamon Jean-Yves Domalain 1971-03-03 20000 30000 20 10
14 Dictionary of Trout Flies John Roberts 1988-05-03 30000 40000 20 10

5.

UPDATE Books SET Price = 25, Cost = 5 WHERE Cost<6

I am uppdating price and cost. If the cost goes up and I have to update cost and price to adjust to the change

table summary="SQL" before
Books4
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 20 2
5 Figures of Earth James Branch Cabell 1974-01-01 500000 500000 20 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 230000 250000 20 5
12 Body language Julius Fast 2002-01-01 10000 25000 20 4
13 Panjamon Jean-Yves Domalain 1971-03-03 20000 30000 20 10
14 Dictionary of Trout Flies John Roberts 1988-05-03 30000 40000 20 10
table summary="SQL" after
Books5
1 Slaughter House 5 Kurt Vonnegut Jr. 1969-01-01 1000000 1000000 25 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 25 5
4 Silmarillion JRR. Tolkien 1979-01-02 3000000 3000000 25 5
5 Figures of Earth James Branch Cabell 1974-01-01 500000 500000 25 5
6 Larions Law Peter Freuchen 1981-01-01 300000 300000 25 5
7 The Cross Lars Molin 1905-05-31 100000 98000 25 5
9 Venus on the Half-Shell Kilgore Trout 1905-05-26 1000000 1000000 25 5
11 Great Expectations Charles Dickens 1860-01-01 230000 250000 25 5
12 Body language Julius Fast 2002-01-01 10000 25000 25 5
13 Panjamon Jean-Yves Domalain 1971-03-03 20000 30000 20 10
14 Dictionary of Trout Flies John Roberts 1988-05-03 30000 40000 20 10