Candy Database         Candy ( CandyType char(15), Cost dec(9,2), Colour char(10))

                                    People ( PersonName char(20), Age smallint,

Suburb char(20), PhoneNumber char(10))

                                    Likes    ( PersonName char(20), CandyType char(15))

Choose the SQL query that will answer the following question using the Candy Database.

Write a report to show the person's name and the cost of the candy they like to buy. Only include blue, red and green candy in the report.

Question

Candy Database         Candy  ( CandyType char(15), Cost dec(9,2), Colour char(10))

People ( PersonName char(20), Age smallint,

Suburb char(20),   PhoneNumber char(10))

Likes    ( PersonName char(20), CandyType char(15))

Choose the SQL query that will answer the following question using the Candy Database.

Write a report to show the person's name and the cost of the candy they like to buy. Only include blue, red and green candy in the report.

BlackTom AI · Accepted Answer

We start by restating the task: write a report showing each person’s name and the cost of the candy they like to buy, but only for blue, red, and green candy.
Option 1:
Select PersonName, cost
From Candy C, Likes L
Where C.CandyType = L.CandyType
And Colour in ('Blue','Red','Green')
Order by PersonName;
This query correctly joins Candy and Likes on the CandyType column, filters by Colour to only Blue, Red, or Green, and orders the result by PersonName. The projection matches: PersonName and cost. It uses a straightforward implicit join, which is acceptable in many SQL dialects, and the condition Colour in ('Blue','Red','Green') is the right way to restrict colors. Overall, this is a correct and clean solution.
Option 2:
Select PersonName, costs
From Candy C, Likes L
Where C.CandyType = L.CandyType
Order by PersonName
and Colour in ('Blue','Red','Green');
This option tries to filter after ordering, placing the Colour filter in a clause that logically should come before the ordering and in the WHERE clause, not after ORDER BY. Additionally, it selects a non-existent column: costs (plural) instead of cost, which would cause an error. The positioning of the colour filter is incorrect in some SQL dialects, and the typo makes this unusable.
Option 3:
Select PersonName, cost

From Candy C, Likes L

Where C.CandyType = L.CandyType

And (Colour = 'Blue' or 'Red' or 'Green')

Order by Cost;
This option attempts to express multiple values with (Colour = 'Blue' or 'Red' or 'Green'), but the syntax is wrong: ('Red' or 'Green') is treated as a boolean expression that always evaluates to true because non-empty strings are truthy in SQL, and the colour column check ends up not functioning as intended. Additionally, ordering by Cost is semantically odd if the goal is to list by PersonName; there’s also an inconsistency in case or alias usage.
Option 4:
Select PersonName, cost

From Candy C, Likes L

Where C.CandyType = L.CandyType

And (Colour = 'Blue' or 'Red' or 'Green')

Order by PersonName;
This option shares the same incorrect colouring condition as Option 3, leading to incorrect or unpredictable filtering due to the flawed boolean expression. It also uses the same potential alias/ordering mismatch, ordering by PersonName as requested but using an invalid colour filter expression. The incorrect colour predicate makes this option invalid.
In summary, Option 1 is the only choice that correctly joins the tables, filters by the desired colours using a proper IN clause, selects the required columns, and orders by PersonName. The other options contain a combination of syntax errors, invalid column names, or faulty boolean logic that would prevent them from producing the correct results.

Similar Questions

SELECT the SQL statement that will list the first name, last name, city, and country where they work.

Select the SQL statement that will list the first_name, last_name and the name of the department they work for. Be sure to select the syntactically correct statement.

The following code is an example of a(n): SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID;

The most commonly used form of join operation is the:

Which join type could be used to answer the following question? Show all information for products in the Electronics category, whether or not they have ever been ordered, and also show any related order details they may have. Choose all that apply.

要连接两个或多个表，应该使用哪条 SQL 语句？

Below are the Movies and Sales table of a relational database: Dexter has created the following SQL query: SELECT tblMovies.Title, tblMovies.Genre, tblSales.CopiesFROM tblMovies, tblSalesWHERE tblMovies.MovieID = tblSales.MovieID How many records will be returned by the query?

More Practical Tools for Students Powered by AI Study Helper

Homework AI Solver

Stylized AI Paper Writer

Plagiarism Checker Assistant

Citation AI Academic Writing Tool

In-Class Translation Assistant

AI Note Generator

AI Quiz Answers

Past Exam Questions from University Test Bank

Smart Practice Assistant

Adaptive Practice

Making Your Study Simpler