Assignment Week 5

The three table summary="SQL"s

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.

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.

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.

3.b

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

Titles that have been sold.

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?