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.

类似问题

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?

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单