BA 497/597 – SQL Homework 2
Copy this document to your local drive and add your name to the footer.
Create SQL commands to answer the following questions from the tables in the classicmodels database using the tables below. Paste your SQL code below each question along with a screen shot of the first few rows of the results grid.
Submit the completed document in Moodle.
- Show the order number, order date, status and comments for orders that were not shipped (use the status field) from the orders table. Limit the comments to 30 characters and use an appropriate alias for the column name. Sort by order date.
- Create a new field called Order_CustID that combines the order number and customer number separated by a dash (-). List the Order_CustID, order date and shipped date for all orders shipped in 2005. Sort by shipped date. HINT: Use the character function CONCAT().
- Create a list that shows the order number, order date and shipped date for all cancelled orders. Use the status field to find cancelled orders. Sort by order date.
- Show the customer number, payment date, and payment amount from the payments table for payments made in 2005. Round the payment amount to whole dollars and use an alias. Sort by payment date in reverse order (newest first).
- Show all columns from the payments table where the payment date is in 2005 and the payment amount is more than $75,000. Order by payment amount from highest to lowest.
- Show the customer name, city and state for customer names starting with the letters M or S from the states of New York (NY), Massachusetts (MA), or Nevada (NV). Order by state then city.
- Show the customer name, city, state and credit limit for customers from the states of New York (NY) or California (CA) who have a credit limit below $100,000. Sort by credit limit.
- Show the customer name and country for customers with the word ‘Toys’ in their name. Sort by customer name.
- Show customer name, phone and country from the customers table where state has a Null value. Order by customer name.
- Show the order number, order date, shipped date, and number of days between the order date and the shipped date for orders that shipped in December 2004. Use an alias for the number of days to ship.