Exercise 7

1. Consider the following tables, with the following data:

Book

Item	Title	Author	Pages
1	Nature	Bob	167
2	Karate	Tim	95

Inventory

Item	Qty	Location
1	5	Downtown
1	9	Roseburg

There is a foreign key from Inventory.Item to Book.Item.

At the time of the above database instance, the following statements are issued:

SELECT Qty, Location, Title INTO Temp1

FROM Book LEFT JOIN Inventory USING (Item)

CREATE VIEW View1 AS

SELECT Qty, Location, Title

FROM Book LEFT JOIN Inventory USING (Item)

At some point later (after Temp1 and View1 have been defined), the following statements are issued:

INSERT INTO Book VALUES (3, Quizzes are lame, James, 3)

INSERT INTO Inventory VALUES (3, 6, Downtown);

Show what the contents of Temp1 and View1 are AFTER the insert statements have been issued.

Temp1

Qty	Location	Title
5	Downtown	Nature
9	Roseburg	Nature
NULL	NULL	Karate

View1

Qty	Location	Title
5	Downtown	Nature
9	Roseburg	Nature
NULL	NULL	Karate
6	Downtown	Quizzes are lame

2. Suppose I want to delete the row (NULL, NULL, Karate) from View1. What would you need to do to the base tables to make this change happen?

Remove the row (2, Karate, Tim, 95) from the book table.

3. Your company uses a database including the following table:

emp( empid, name, address)

Then your company is bought by another company with an Employee table like this:

employee( empid, fname, lname, address)

Write a view so that existing SQL queries from your company’s software repository will work.

CREATE VIEW emp AS

SELECT empid, fname || lname AS name, address

FROM employee;

Note that the double vertical bar “||” is the string concatenation operator in SQL.