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.

IDBF001 Tutorial Quiz 7 |LA007 - Requires Respondus LockDown Browser

查看解析

登录即可查看完整答案

类似问题

Two tables may be joined:

Question at position 51 We can calculate the cumulative sum for each date using the following SQL statement: SELECT t1.date, t1.precip AS 'daily precip', SUM(t2.precip) AS 'cum. precip' FROM rainfall AS t1 INNER JOIN rainfall AS t2 ON t2.date >= t1.date GROUP BY t1.date ORDER BY t1.date;TrueFalse

Question at position 57 We can calculate the cumulative sum for each date using the following SQL statement: SELECT t1.date, t1.precip AS 'daily precip', SUM(t2.precip) AS 'cum. precip' FROM rainfall AS t1 INNER JOIN rainfall AS t2 ON t2.date >= t1.date GROUP BY t1.date ORDER BY t1.date;TrueFalse

Question at position 57 We can calculate the cumulative sum for each date using the following SQL statement: SELECT t1.date, t1.precip AS 'daily precip', SUM(t2.precip) AS 'cum. precip' FROM rainfall AS t1 INNER JOIN rainfall AS t2 ON t2.date >= t1.date GROUP BY t1.date ORDER BY t1.date;TrueFalse

Question at position 46 Joining conditions for an INNER JOIN always need to use '='.TrueFalse

Question at position 40 One major disadvantage of the outer join is that information is easily lost.TrueFalse

Question at position 17 A join operation:is used to combine indexing operations.causes two tables with a common domain to be combined into a single table or view.causes two disparate tables to be combined into a single table or view.brings together data from two different fields.

更多留学生实用工具

考试浏览器助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单