Show the number of payments received each month

Assignment Help Database Management System

Reference no: EM132295533

SQL Homework 1- Summarizing Data

Create SQL commands to answer the following questions using the tables shown below from the classicmodelsdatabase. Paste your SQL code below each question along with a screen shot of the first few rows of the results grid.

For all problems, use a single word alias (no quote marks) for any calculated fields. Limit long output to the first 5 rows.

Example: SELECT AVG(amount) AS Avg_Payment

The following questions use aggregate functions and all will require a GROUP BY clause. Some of them will require a HAVING clause if you are making selection criteria based on results from aggregated functions.

1. Show the count of orders by status in the orders table. Use the alias N_Status for the counts.

2. Show the count of orders by customer number in the orders table. Use the alias N_Status for the counts. Order by counts from highest to lowest.

3. Write a query to show the customer number, the count of checks, and the total payments made by customer. Use the aliases N_Checks and Total_Paid. Order by the total paid.

4. Show the minimum, maximum, and average credit limit by city. Use the aliases Min_Limit, Max_Limit, and Average_Limit. Sort by city in alphabetical order.

5. Show the number of customers and average credit limit for customers in zip code (postalCode) 94217. Use appropriate aliases and round the average credit limit to 0 decimal places. (HINT: use a WHERE clause for the zip code.)

6. Show the number of payments received each month in 2003.

7. Show the months in 2003 that had more than 10 payments. (HINT: You need a HAVING clause.)

8. Show the average credit limit by city for all cities that have anaverage limit between$99,000 and $150,000. Use the alias Average_Limit for the averages. Sort by the average credit limits, largest to smallest. (HINT: You need a HAVING clause.)

9. Show the count of customers by state for all states that have more than 2 customers. Use the alias N_State for the counts. Sort by state in alphabetical order. Make sure you do not count customers that have Null values in the state field. (HINT: You need a HAVING clause.)

SQL Homework 2

Create SQL commands to answer the following questions from the tables in the classicmodels database using the tablesbelow. 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.

1. 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.

2. 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().

3. 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.

4. 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).

5. 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.

6. 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.

7. 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.

8. Show the customer name and country for customers with the word ‘Toys' in their name. Sort by customer name.

9. Show customer name, phone and country from the customers table where state has a Null value. Order by customer name.

10. 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.

Attachment:- SQL Assignment.rar

Reference no: EM132295533

Questions Cloud

What is the present value of the payments you will receive : You have just won the Strayer Lottery jackpot of $11,000,000. You will be paid in 26 equal annual installments beginning immediately.

Explaining the use of questioning in sales position : For this assignment assume you are a sales trainer - create a powerpoint presentation explaining the use of questioning in a sales position.

Determine whether stock prices are affected more : Determine whether stock prices are affected more by long-term or short-term performance.

Organization to use capital budgeting effectively : It is important for an organization to use capital budgeting effectively. the steps of the process and the role of financial analysis.

Show the number of payments received each month : 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.

Should bloom take huge risk and confront tread gold : Should Bloom take a huge risk and confront Tread gold with a solid explanation of why he should promote from within rather than rehire Kelleher?

Motivation is the psychological process : Motivation is the psychological process that arouses and directs behavior. There are three theories that will help us understand the motivation of employees

Differences in four tasks of management : What are the differences in the four tasks of management and the four functions of an agribusiness?

How did become a unifying symbol for the early christians : Define the following: Incarnation; Atonement and the Holy Trinity. Why do Jews and Muslims reject the idea of the Holy Trinity?

User Account

All Pages