Question

/* (5) Question #2: Fix the following query. It should return

    13 rows, first one is "USB food flash drive - banana"

*/

SELECT

    SI.StockItemID,

    SI.StockItemName AS [ItemForSales],

    SI.ColorID AS [Color]

FROM

    Warehouse.StockItems SI

ORDER BY

    ItemForSales

    

WHERE

    SI.ColorID = NULL

    AND

    SI.StockItemName LIKE '%USB%'   

my error :

Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'WHERE'.

What is causing my error?

Answer 1

Problem is with incorrect placement of WHERE clause in the query.

First SQL query fetches all the rows that are satisfying the condition present in WHERE clause and then result set is sorted based on expression/column(s) specified in ORDER BY clause.

Syntax of an SQL query with ORDER BY clause:

SELECT column1, column2, ....
FROM <table_name>
WHERE <condition>
ORDER BY <column1, column2, ...> <ASC|DESC>;

By default if order(ASC|DESC) is not specified results are sorted in Ascending order.

NULL Values are usually Compared as: IS NULL (or) IS NOT NULL

Updated Query:

SELECT
SI.StockItemID,
SI.StockItemName AS [ItemForSales],
SI.ColorID AS [Color]
  
FROM
Warehouse.StockItems SI

WHERE
SI.ColorID IS NULL
AND
SI.StockItemName LIKE '%USB%'   
  
ORDER BY
ItemForSales