Generating two SQL queries for specified tasks: For my assignment, I need to gen

Question

Generating two SQL queries for specified tasks:

For my assignment, I need to generate and test two SQL queries for each of the following tasks: a) the SQL statement needed to perform the stated task with the traditional approach, and b) the SQL statement needed to perform the stated task with the JOIN keyword. Also, I need to apply table aliases in all queries. I can get the program to output the correct value, however, it outputs duplicates, which I am unsure why it is doing so. I believe that the program should not be outputting duplicates. Can you help me understand how to fix this? Thanks!

Question: What gift will a customer who orders the book Shortest Poems receive? Use the actual book retail value to determine the gift.  

What I have:

Part A) SELECT b. title, p.gift
FROM books b, promotion p
WHERE b.retail BETWEEN p.minretail AND p.maxretail AND b.title = 'SHORTEST POEMS';

Part B) SELECT b.title, p.gift
FROM books b JOIN promotion p
ON b.retail BETWEEN p.minretail AND p.maxretail
WHERE b.title = 'SHORTEST POEMS';

Explanation / Answer

Hi,

1) There should be a relation between 2 tables say in your example it should be between books and promotion.

2) Same column should be refered between both the tables if it has. If not there should be a foreign key relation ship between the tables and that should be a primary key in another table.

So, please join the 2 tables, and if you still get duplicates you can use DISTINCT keyword which will eliminate duplicates in the output.

Below are the sample queries for joining 2 tables and also DISTINCT.

1) SELECT ORD.ORDER_ID, CUSTOM.CUSTOMER_NAME
FROM ORDERS ORD, Customers CUSTOM WHERE ORD.Customer_ID=CUSTOM.Customer_ID;

Here in the above example Customer_ID in orders table refers to the Customer_ID in Customers table.

2) Below is the example for distinct keyword.

SELECT DISTINCT NAME FROM STUDENT;

The above will return only NAME's with distinct values. Say for eg: if 2 students have JOHNY as the name than output will display only 1 row with name JOHNY.

Hope the above clariffication will help you in resolving your problem.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.