INFO224 Principles of Information Technology

SQL Product 1A

In this assignment, you will learn to code in Structured Query Language (SQL). The purpose of each problem is to translate each instruction in English into an instruction in SQL. SQL is a data manipulation language that a computer executes and produces the desired results. Be sure to pay attention to details including parentheses and quotes.

Questions 1-5 are samples with solutions. Questions 6-10 are questions to be answered in this assignment. Study the sample questions and solutions, and complete the solutions to 6-10.

1. Display ItemID, ItemName, and ItemPrice for all items in the Products table with item price less than 23 dollars. Sort in ascending order of item price.

SQL:

SELECT ItemID, ItemName, ItemPrice

FROM Products

WHERE (ItemPrice< 23)

ORDER BY ItemPrice

2. Display ItemID, ItemName, and StoreLocation for all items in the Products table located in Dumfries.

SQL:

SELECT ItemID, ItemName, StoreLocation

FROM Products

WHERE (StoreLocation = 'Dumfries')

3. Display ItemName, ItemColor, and ItemPrice for all items in the Products table that are Green in ItemColor.

SQL:

SELECT ItemName, ItemColor, ItemPrice

FROM Products

WHERE (ItemColor = 'Green')

4. Display ItemID, ItemName, and ItemPrice of all items in the Products table with ItemName Pants. Sort in descending order of item price.

SQL:

SELECT ItemID, ItemName, ItemPrice

FROM Products

WHERE (ItemName = 'Pants')

ORDER BY ItemPrice DESC

5. Display ItemName, ItemColor and ItemPrice for items in the Products table between 12 and 30 dollars in item price.

SQL:

SELECT ItemName, ItemColor, ItemPrice

FROM Products

WHERE ItemPrice Between 12 And 30

6. Display ItemID, ItemName, and ItemPrice for all items in the Products table with item price greater than 23 dollars. Sort in ascending order of item price.

SQL:

7. Display ItemID, ItemName, and StoreLocation for all items in the Products table located in Charlotte.

SQL:

8. Display ItemName, ItemColor, and ItemPrice for all items in the Products table that are Brown in item color.

SQL:

9. Display ItemID, ItemName, ItemPrice of all items in the Products table with item name Shoes. Sort in descending order of Price.

SQL:

10. Display ItemName, ItemColor and ItemPrice for items in the Products table between 15 and 35 dollars in item price.

SQL: