Assignment Week 5

The three table summary="SQL"s

Books
Item Titel Writer PublishingDate Sold Printed Price Cost
1 Slaughter House 5 Kurt Vonnegut Jr. 1969-01-01 1000000 10000000 25 5
2 Death is a lonely business Ray Bradbury 1985-05-05 2000000 20000000 40 7
3 Tobacco Road Erskine Caldwell 1932-02-02 500000 5000000 25 5
4 Silmarillion JRR. Tolkien 1979-01-02 3000000 30000000 25 5
5 Figures of Earth James Branch Cabell 1974-01-01 500000 5000000 25 5
6 Larions Law Peter Freuchen 1981-01-01 300000 3000000 25 5
7 The Cross Lars Molin 1905-05-31 100000 100000 25 5
9 Venus on the Half-Shell Kilgore Trout 1905-05-26 100000 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


Sales
Sale Item Date Salesperson Sold
1 3 2002-05-03 501 1
2 4 2002-05-03 502 1
2 9 2002-05-03 502 1
3 1 2002-05-03 503 1
11 16 2002-05-04 504 10
4 7 2002-05-03 501 1
5 2 2002-05-03 502 2
5 13 2002-05-03 502 1
5 9 2002-05-03 502 1
6 12 2002-05-03 505 1
7 11 2002-05-03 504 1
8 4 2002-05-03 505 1
9 14 2002-05-04 502 1
10 3 2002-05-04 503 1


SalesPerson
Salesperson Name Address Zip Town State
501 John Smith Lonestreet 2 1234 Georgetown Minnesota
502 John Bradbury Washingtonstreet 4 2345 Georgetown Minnesota
503 Arthur Logan Mainstreet 3 3456 Georgetown Minnesota
504 Benjamin Morris Backstreet 45 5678 Georgetown Minnesota
505 Lukas Bónniér Old Street 6789 Georgetown Minnesota
506 Jan Nylund Milkway 3 8765 Georgtown Minnesota



2.a

SELECT [Books].[Item], [Books].[Title], [Sales].[Date], [SalesPerson].[Salesperson], [Sales].[Sold] FROM Books, Sales, SalesPerson WHERE [Books].[Item]=[Sales].[Item] And [SalesPerson].[Salesperson]=[Sales].[Salesperson] And [Sales].[Sold]=1;

Only one book of each Title sold at each sale and what salesperson that made the sale.

Fråga32
3 Tobacco Road 2002-05-03 501 1
4 Silmarillion 2002-05-03 502 1
9 Venus on the Half-Shell 2002-05-03 502 1
1 Slaughter House 5 2002-05-03 503 1
7 The Cross 2002-05-03 501 1
13 Panjamon 2002-05-03 502 1
9 Venus on the Half-Shell 2002-05-03 502 1
12 Body language 2002-05-03 505 1
11 Great Expectations 2002-05-03 504 1
4 Silmarillion 2002-05-03 505 1
14 Dictionary of Trout Flies 2002-05-04 502 1
3 Tobacco Road 2002-05-04 503 1

2.b

SELECT [Books].[Item], [Books].[Title], [Sales].[Date], [SalesPerson].[Salesperson], [Sales].[Sold] FROM (Books INNER JOIN Sales ON [Books].[Item]=[Sales].[Item]) INNER JOIN SalesPerson ON [Sales].[Salesperson]=[SalesPerson].[Salesperson];

Books sold and by what salesperson.

Fråga31
3 Tobacco Road 2002-05-03 501 1
4 Silmarillion 2002-05-03 502 1
9 Venus on the Half-Shell 2002-05-03 502 1
1 Slaughter House 5 2002-05-03 503 1
7 The Cross 2002-05-03 501 1
2 Death is a lonely business 2002-05-03 502 2
13 Panjamon 2002-05-03 502 1
9 Venus on the Half-Shell 2002-05-03 502 1
12 Body language 2002-05-03 505 1
11 Great Expectations 2002-05-03 504 1
4 Silmarillion 2002-05-03 505 1
14 Dictionary of Trout Flies 2002-05-04 502 1
3 Tobacco Road 2002-05-04 503 1

3.a

SELECT [Sales].[Date], [Sales].[Item], [SalesPerson].[Name] FROM SalesPerson LEFT JOIN Sales ON [SalesPerson].[Salesperson]=[Sales].[Salesperson];

The Salespersons that have made one sale or more.

Fråga33
2002-05-04 3 Arthur Logan
2002-05-03 1 Arthur Logan
2002-05-03 11 Benjamin Morris
2002-05-03 4 Lukas Bónniér
2002-05-03 12 Lukas Bónniér
Jan Nylund
2002-05-03 7 John Smith
2002-05-03 3 John Smith
2002-05-04 14 John Bradbury
2002-05-03 9 John Bradbury
2002-05-03 13 John Bradbury
2002-05-03 2 John Bradbury
2002-05-03 9 John Bradbury
2002-05-03 4 John Bradbury

3.b

SELECT [Books].[Title], [Sales].[Sold] FROM Sales RIGHT JOIN Books ON [Books].[Item]=[Sales].[Item];

Titles that have been sold.

Fråga34
Slaughter House 5 1
Death is a lonely business 2
Tobacco Road 1
Tobacco Road 1
Silmarillion 1
Silmarillion 1
Figures of Earth
Larions Law
The Cross 1
Venus on the Half-Shell 1
Venus on the Half-Shell 1
Great Expectations 1
Body language 1
Panjamon 1
Dictionary of Trout Flies 1

3.c

SELECT Books.Title, Books.Writer, Sales.Salesperson FROM Books LEFT JOIN Sales ON Books.Item = Sales.Item UNION SELECT Books.Title, Books.Writer, Sales.Salesperson FROM Books RIGHT JOIN Sales ON Sales.Item = Books.Item

What Salesperson has sold what books and is there any books that not have been sold of any salesperson?

Fråga36
Body language Julius Fast 505
Death is a lonely business Ray Bradbury 502
Dictionary of Trout Flies John Roberts 502
Figures of Earth James Branch Cabell
Great Expectations Charles Dickens 504
Larions Law Peter Freuchen
Panjamon Jean-Yves Domalain 502
Silmarillion JRR. Tolkien 502
Silmarillion JRR. Tolkien 505
Slaughter House 5 Kurt Vonnegut Jr. 503
The Cross Lars Molin 501
Tobacco Road Erskine Caldwell 501
Tobacco Road Erskine Caldwell 503
Venus on the Half-Shell Kilgore Trout 502