Display the state of customers, the total and the average purchase amounts of each state in 2017 ordered by state. Rounding the amounts to 2 decimal places. The image depicts a database schema diagram, illustrating the relationships between various tables within a database. The schema consists of the following tables:1. **Department Table** - Columns: - DEPT_NUM: Decimal(5,0) - DEPT_NAME: Varchar(50) - DEPT_MAIL_BOX: Varchar(3) - DEPT_PHONE: Varchar(9) - EMP_NUM: Decimal(6,0)2. **Employee Table** - Columns: - EMP_NUM: Decimal(6,0) - EMP_FNAME: Varchar(20) - EMP_LNAME: Varchar(25) - EMP_EMAIL: Varchar(25) - EMP_PHONE: Varchar(20) - EMP_HIREDATE: Date - EMP_TITLE: Varchar(45) - EMP_COMM: Decimal(2,2) - DEPT_NUM: Decimal(5,0) - Relations: Linked to the Department table via DEPT_NUM.3. **Salary History Table** - Columns: - EMP_NUM: Decimal(6,0) - SAL_FROM_DATE: Date - SAL_END_DATE: Date - SAL_AMOUNT: Decimal(10,2)4. **Line Table** - Columns: - INV_NUM: Decimal(38,0) - LINE_NUM: Decimal(10,0) - PROD_SKU: Varchar(15) - LINE_QTY: Decimal(9,0) - LINE_PRICE: Decimal(6,2) - Relations: Linked to the Invoice and Product tables.5. **Invoice Table** - Columns: - INV_NUM: Decimal(38,0) - INV_DATE: Date - CUST_CODE: Decimal(38,0) - INV_TOTAL: Decimal(11,2) - EMPLOYEE_ID: Decimal(6,0) - Relations: Linked to Employee and Customer tables.6. **Customer Table** - Columns: - CUST_CODE: Decimal(38,0) - CUST_FNAME: Varchar(20) - CUST_LNAME: Varchar(20) - CUST_STREET: Varchar(70) - CUST_CITY: Varchar(50) - CUST_STATE: Char(2) - CUST_ZIP:

Answered: Display the state of customers, the…

Display the state of customers, the total and the average purchase amounts of each state in 2017 ordered by state. Rounding the amounts to 2 decimal places.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: LIST THE FIRST AND LAST NAMES OF ALL OWNERS WHO OWN A TWO-BEDROOM PROPERTY. USE THE IN OPERATOR IN…

A: Considering there are 2 tables:- OWNER: which holds the OWNER_NUM, FIRST_NAME, and LAST_NAME…

Q: List the years and their total gross earnings (sum of earnings per year), but do not include gross…

A: ANSWER:-

Q: Insert into the LARGE_PROPERTY table the office number, address, bedrooms, floors, monthly rent, and…

A: We need to insert some columns from property table to largeproperty table whose square footage is…

Q: List every column in the Condo_UNIT table and its associated typ

A: The, database is a used to stores the data.

Q: Display employees where the first name or last name starts with S. using sql plus hr user

A: To select data from a database, SELECT statement are used. The data is saved in a result table…

Q: Based on the ERD below, display Employee first name, middle initial and last name so it displays in…

A: In this case, the whole name is displayed in a single column of employees where the job code is…

Q: List the order number and order date for every order that was placed by Mary Nelson and that…

A: SQL, or Structured Query Language, is a programming language designed for managing and manipulating…

Q: - Copy the name, salary, and commission of each employee hired before 1980 into TEMP table - Copy…

A: INSERT INTO query is used to copy the sql data from one table to another table.WHERE is SQL clause…

Q: SQL List the item number in lower case, description in upper case, order date 30 days later (add 30…

A: ORDER Table: Creating ORDER table: create table ORDERS(ORDER_NUM integer,ORDER_DATE…

Q: 1. Which customers live in New Jersey? List each customer’s last name, first name, and state.

A: The solution is given in next step: Let's say that the table's name is Customer for the moment.

Q: Write the following as the MySQL query: List the employee ID, first name, last name, hire date,…

A: In database administration, it is frequently essential to extract particular data from a dataset…

Q: department name information for those departments whose location_id is 1500

A: Attributes of Department Table are- department_id department_name employee_id location_id

Q: Orders Table: OrderID,CustomerID, Order Date, TotalAmount Customers Table: CustomerID, CustomerName.…

A: In this question we have to create a query to retrieve the customer name and order date for each…

Q: Show students’ first name and last name, the course numbers the student took (don’t include…

A: Data is coming from 3 tables . So apply inner join on 3 tables with joining condition. student…

Q: CARD_NO PLAYER_NO TEAM_CODE PRICE 44 BR 4.45 27 BR 3.5 54 BR vers cards

A: query SELECT team_code,COUNT(card_no) AS TOTAL_CARD_NO, to_char(SUM(price),'$99.99') as total_price…

Q: For each order id, calculate the total of the Order. The total of an order is the sum of the prices…

A: For each order id, calculate the total of the Order. The total of an order is the sum of the prices…

Q: Display CustomerName and Status as 'Q2 for every customer who was born during the 2nd quarter of…

A: The given problem is related to database SQL query. The problem has provided the msCustomer table…

Q: Find out the total sales, minimum sales, maximum sales and the number of invoices during the 2nd…

A: Overview : The COUNT() function returns the number of rows that matches a specified criterion. The…

Q: The code: "SELECT SupplierlID FROM Products Order By SupplierName DESC;" extracts data from Products…

A: Sol1: Current procedure terminology (CPT) is a code such as reflex testing and is an integrated part…

Q: For each sale made on June 1, 2015, show the following information: CustomerID, Customer name…

A: Hey, since there are multiple questions posted, we will answer first question. If you want any…

Q: Display Customer name (concatenate First Name and Last Name with a space between) and total number…

A: In order to concatenate the 'FNAME' with 'LNAME' use the '+' sign to combine them. Use the SELECT…

Concept explainers

Basic SQL Queries

Structured Query Language is the full form of SQL. It is a high-level language used to communicate with a relational database. It is used for creating, storing and accessing contents of a relational database. SQL is used to write the queries which are executed using database engines like Oracle, SQL Server and so forth.

Question

Display the state of customers, the total and the average purchase amounts of each state in 2017 ordered by state. Rounding the amounts to 2 decimal places.

The image depicts a database schema diagram, illustrating the relationships between various tables within a database. The schema consists of the following tables:

1. **Department Table**
- Columns:
- DEPT_NUM: Decimal(5,0)
- DEPT_NAME: Varchar(50)
- DEPT_MAIL_BOX: Varchar(3)
- DEPT_PHONE: Varchar(9)
- EMP_NUM: Decimal(6,0)

2. **Employee Table**
- Columns:
- EMP_NUM: Decimal(6,0)
- EMP_FNAME: Varchar(20)
- EMP_LNAME: Varchar(25)
- EMP_EMAIL: Varchar(25)
- EMP_PHONE: Varchar(20)
- EMP_HIREDATE: Date
- EMP_TITLE: Varchar(45)
- EMP_COMM: Decimal(2,2)
- DEPT_NUM: Decimal(5,0)
- Relations: Linked to the Department table via DEPT_NUM.

3. **Salary History Table**
- Columns:
- EMP_NUM: Decimal(6,0)
- SAL_FROM_DATE: Date
- SAL_END_DATE: Date
- SAL_AMOUNT: Decimal(10,2)

4. **Line Table**
- Columns:
- INV_NUM: Decimal(38,0)
- LINE_NUM: Decimal(10,0)
- PROD_SKU: Varchar(15)
- LINE_QTY: Decimal(9,0)
- LINE_PRICE: Decimal(6,2)
- Relations: Linked to the Invoice and Product tables.

5. **Invoice Table**
- Columns:
- INV_NUM: Decimal(38,0)
- INV_DATE: Date
- CUST_CODE: Decimal(38,0)
- INV_TOTAL: Decimal(11,2)
- EMPLOYEE_ID: Decimal(6,0)
- Relations: Linked to Employee and Customer tables.

6. **Customer Table**
- Columns:
- CUST_CODE: Decimal(38,0)
- CUST_FNAME: Varchar(20)
- CUST_LNAME: Varchar(20)
- CUST_STREET: Varchar(70)
- CUST_CITY: Varchar(50)
- CUST_STATE: Char(2)
- CUST_ZIP:

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

For each customer, list the CustomerID and the total number of orders placed in 2015.
C) Which employees earn less salary than the average salary for the consultants who perform the same job? Show the employees first and last names, job id, salary, department name, and manager's last name. Sort the result by the employees’ employee id. D) For each department, find the manager who pays out the most in salaries. Show the department number, the manager's last name, and the total she/he pays each month. Sort by the department number. Here are the tables and the columns within the table Here are the tables and the columns within the table CONSULTANTS - CONSULTANT_ID- FIRST_NAME- LAST_NAME- EMAIL- PHONE_NUMBER- HIRE_DATE- JOB_ID- SALARY- COMMISSION_PCT- MANAGER_ID- DEPARTMENT_ID . COUNTRIES - COUNTRY_ID- COUNTRY_NAME-REGION_ID . CUSTOMERS - CUST_IDCUST_EMAILCUST_FNAMECUST_LNAMECUST_ADDRESSCUST_CITYCUST_STATE_PROVINCECUST_POSTAL_CODECUST_COUNTRYCUST_PHONECUST_CREDIT_LIMIT . DEPARTMENTS - DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_ID . EMPLOYEES -…
Change the MONTHLY_RENT column in the LARGE_PROPERTY table to reject null values.
Find the total revenue generated by each product in the past quarter (last 3 months), you would need the following tables and their respective columns: Orders table: order_id (Primary key) order_date Order_details table: order_id (Foreign key) product_id (Foreign key) quantity price_per_unit Products table: product_id (Primary key) product_name
The Colombia City office of StayWell indicated that there is a new tenant by the name of Yigit Yilmaz staying at the property with an ID of 13. You need to include this new resident in the RESIDENTS table.
List the names of all Staff members who hold a faculty position and have a salary of $45,000 or less. Show their names as their first name, followed by a space, and then their last name but sort the list alphabetically by last name and then first name. Hint: Check out the Position column in the Staff table. All staff members who do not hold a faculty position will get a bonus this year equal to 5% of their salary. List the names, current salary, and bonus amount for each Staff member (non-faculty) who is due a bonus. Show your list in order with the person receiving the highest bonus first.
1. List the book title and retail price for all books with a retail price lower than the averageretail price of all books sold by JustLee Books.
Formula to calculare the total sales amount of the customer name HORSHOE INCORPORATED. Note: This excel worksheet goes up to row 1699! Thanks.
Find the total revenue generated by each product in the past quarter (last 3 months), you would need the following tables and their respective columns: Orders table: order_id (Primary key) order_date Order_details table: order_id (Foreign key) product_id (Foreign key) quantity price per unit Products table: product_id (Primary key) product_name