Homework 1 solution

Coding and Database Managment

SQL - Homework 1 - solution

The following queries use the classicmodels schema. All queries use the products table.

1. Prepare a list of products sorted by product name in descending order.

   SELECT * FROM products 
    ORDER BY productName DESC;

2. List the product names and their quantity available from high to low?

   SELECT productName, quantityInStock FROM products
    ORDER BY quantityInStock DESC;

3. Report the MSRP for the products in “Vintage Cars” category?

   SELECT MSRP FROM products
    WHERE productLine = 'Vintage Cars';    

4. Report the product(s) with the buy price of 48.81.

   SELECT * FROM products
    WHERE buyPrice = 48.81;   

5. Report those products with the price greater than 70.

   SELECT * FROM products
    WHERE buyPrice > 70;    

6. Which products have product scale ratio 1:10 and 1:72?

   SELECT * FROM products
    WHERE productScale IN ('1:10', '1:72');    

7. List the product names except the ones in the Planes and Ships category.

   SELECT productName FROM products
    WHERE productLine NOT IN ('Planes', 'Ships');   

8. Report the names of products excluding Motorcycles and Classic Cars.

   SELECT productName FROM products
    WHERE productLine NOT IN ('Motorcycles', 'Classic Cars');    

9. Report the products having MSRP greater than 110.

   SELECT * FROM products
    WHERE MSRP > 110;

10. List the product vendors in ascending alphabetical order.

    SELECT DISTINCT productVendor FROM products
     ORDER BY productVendor ASC; 

Tip

ASC is not obligatory. By default ORDER BY is asceding.

© 2025. All Rights Reserved.

Enabled by Dr. Marcin Bartosiak