NOT SURE WHERE TO FIND TABLE

INFO224 Principles of Information Technology

SQL Product 2A

Questions 1-7 are samples with solutions. Questions 8-14 are questions to be answered in this assignment. Study the sample questions and solutions, and complete the solutions to 8-14. Pay particular attention to the use of quotes, parentheses, and the keywords AND, OR, and NOT.

1. Display ItemID, ItemName, and ItemPrice for all items priced less than or equal to 23. Sort in descending order of item price.

SQL:

SELECT ItemID, ItemName, ItemPrice

FROM Products

WHERE (ItemPrice <= 23)

ORDER BY ItemPrice DESC

Note: <= for less than or equal to, >= for greater than or equal to

2. Display ItemID, ItemName, and StoreLocation for all items located at either Springfield or Dumfries.

SQL:

SELECT ItemID, ItemName, StoreLocation

FROM Products

WHERE (StoreLocation = "Springfield") OR (StoreLocation = "Dumfries")

Note: must repeat StoreLocation = connected by the keyword OR

3. Display ItemName and ItemColor for all items that are not Blue.

SQL:

SELECT ItemName, ItemColor

FROM Products

WHERE (ItemColor <> "Blue")

Note: <> means not equal to, equivalently, may use NOT (ItemColor = "Blue")

4. Display ItemName, ItemColor and ItemPrice for all items that are either Blue or Red.

SQL:

SELECT ItemName, ItemColor, ItemPrice

FROM Products

WHERE (ItemColor = "Blue") OR (ItemColor = "Red")

Note: As in query 2, must repeat ItemColor = when connected by an OR

5. Display ItemID, ItemName, ItemPrice, and StoreLocation of all with item name Pants or Shoes. Order by StoreLocation ascending.

SQL:

SELECT ItemID, ItemName, ItemPrice, StoreLocation

FROM Products

WHERE (ItemName = "Pants") OR (ItemName = "Shoes")

ORDER BY StoreLocation

Note: As in 2 and 4, must repeat the field name ItemName = in comparisons connected by an OR

6. Display ItemName, ItemColor and ItemPrice for Shirt or Jacket greater than or equal to 23 dollars in item price.

SQL:

SELECT ItemName, ItemColor, ItemPrice

FROM Products

WHERE (ItemName = "Shirt" OR ItemName = "Jacket") AND (ItemPrice >= 23)

Note: The first condition is item name being either Shirt or Jacket, and the second condition is item price greater than or equal to 23; must enclose each condition in its own pair of parentheses first, then connected by a keyword AND.

7. Display ItemName, ItemColor, ItemPrice, and StoreLocation for all items with item price between 10 and 25 dollars at store locations of either Springfield or Shirlington.

SQL:

SELECT ItemName, ItemColor, ItemPrice, StoreLocation

FROM Products

WHERE (ItemPrice Between 10 And 25) AND (StoreLocation = "Springfield" OR StoreLocation = "Shirlington")

Note: Like query 6, each condition is enclosed in its own pair of parentheses, then connected by a keyword AND.

8. Display ItemID, ItemName, and ItemPrice for all items priced greater than or equal to 23. Sort in descending order of Price.

SQL:

9. Display ItemID, ItemName, and StoreLocation for all items located at either Springfield or Charlotte.

SQL:

10. Display ItemName and ItemColor for all items that are not Red.

SQL:

11. Display ItemName, ItemColor and ItemPrice for all items that are either Green or Blue.

SQL:

12. Display ItemID, ItemName, ItemPrice, and StoreLocation of all items with item name Jacket or Shirt. Order by ItemPrice ascending.

SQL:

13. Display ItemName, ItemColor and ItemPrice for Pants or Shoes less than or equal to 30 dollars in item price.

SQL:

14. Display ItemName, ItemColor, ItemPrice, and StoreLocation for all items between 5 and 30 dollars at Dumfries or Charlotte.

SQL: