Question

Identify two related tables in the JustLee Books database. Identify the common field between the two tables. Decide which columns you would like to display in the output. Write a SQL code to join identified tables using the WHERE statement. Make sure to include qualifiers for columns that appear in both tables. Since only two tables are joined, make sure to include one join condition in the WHERE statement. Explain what the query is intended to do.
Repeat problem 1 but remove the WHERE statement. What happened? Why?

Repeat problem 1 using the JOIN … USING keywords.

Repeat problem 1 using the JOIN … ON keywords.

Repeat problem 1 but add a condition in the WHERE statement. Use logical operators to combine multiple conditions. Explain what the query is intended to do.

Repeat problem 1 but add two more conditions in the WHERE statement. Use logical operators to combine multiple conditions. Explain what the query is intended to do.

Identify three related tables in the JustLee Books database. Identify common fields between the tables. Decide which columns you would like to display in the output. Write a SQL code to join identified tables using the WHERE statement. Make sure to include qualifiers for columns that appear in multiple tables. Since three tables are joined, make sure to include two join conditions in the WHERE statement. Explain what the query is intended to do.

Repeat problem 7 using the JOIN … USING keywords and add two conditions. Explain what the query is intended to do.

Repeat problem 7 using the JOIN … ON keywords and add two conditions. Explain what the query is intended to do.

Use set operators UNION, INION ALL, INTERSECT, and MINUS to combine the results of two queries. Make sure the column list in both queries is consistent. Explain the result.

Identify a table in the JustLee Books database where a self-join can be used and write a corresponding SQL query. Explain what the query is intended to do.

Answer 1

Answer:

Answers explained below

Explanation:

The two related table are:

i) Books Table

ii) BOOKAUTHOR Table

The common field between the two tables are:

i) ISBN attribute

The columns that i would like to display are:

Title, ISBN, AuthorID, PubID, PubDate, Cost, Retail, Discount, Category

Sql Code to join tables using where clause

select t1.Title, t1.ISBN, t2.AuthorID, t1.PubID, t1.PubDate, t1.Cost, t1.Retail, t1.Discount, t1.Category from Books t1 INNER JOIN BOOKAUTHOR t2 ON t1.ISBN = t2.ISBN where t1.ISBN = 0401140733

The above query will dispaly the attributes of table "Books" and of table "BOOKAUTHOR" for book ISBN 0401140733

Repeat problem 1 but remove the WHERE statement

After removing the where condition we will have following join query

select t1.Title, t1.ISBN, t2.AuthorID, t1.PubID, t1.PubDate, t1.Cost, t1.Retail, t1.Discount, t1.Category from Books t1 INNER JOIN BOOKAUTHOR t2 ON t1.ISBN = t2.ISBN

The above query will display all the mapping data of table "Books" and of Table "BOOKAUTHOR"