Assignment Week 6

1

DELETE * FROM SalesPerson WHERE Salesperson = 501;

Mr John Smith have left the company and I want to delete him in the SalesPerson table summary="SQL"

Before

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

After

SalesPerson_week6
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

UPDATE Books SET Price = 20 WHERE Cost <= 5;

Sales are low and the company is forced to sell the paperbacks at a lower price to get higher sales

Before

Books_week5
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

After

Books_week6
1 Slaughter House 5 Kurt Vonnegut Jr. 1969-01-01 1000000 10000000 20 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 20 5
4 Silmarillion JRR. Tolkien 1979-01-02 3000000 30000000 20 5
5 Figures of Earth James Branch Cabell 1974-01-01 500000 5000000 20 5
6 Larions Law Peter Freuchen 1981-01-01 300000 3000000 20 5
7 The Cross Lars Molin 1905-05-31 100000 100000 20 5
9 Venus on the Half-Shell Kilgore Trout 1905-05-26 100000 1000000 20 5
11 Great Expectations Charles Dickens 1860-01-01 230000 250000 20 5
12 Body language Julius Fast 2002-01-01 10000 25000 20 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

3

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

I want to know the salesmargin on the books I am selling

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

4

SELECT Sum([Sold]) AS ['Number of books sold'], [Cost] FROM Books GROUP BY [Cost];

I want to know the cost of books sold, and the number of books sold at that cost

Fråga37_week6
5740000.00 5
2000000.00 7
50000.00 10

5

INSERT INTO SalesPerson VALUES (507, 'Timothy Lanson', 'Pineroad 2', 1234, 'Georgetown',' Minesota');

I am adding a record into SalesPerson, Timothy Lanson is joining the company

Before

SalesPerson_week6
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

After

SalesPerson_week6_2
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
507 Timothy Lanson Pineroad 2 1234 Georgetown Minesota