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: Is it accurate that associative entities are also regarded as weak entities? What justification or…

A: Associated thing: One or more instances of an entity type are linked together by an associative…

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 number of customers living at each state that is included in the Customer_T table.

A: Query:-

Q: Find nth number highest salary. Table:- EMPLOYEE Column Name:- Name,Dept_id,salary,On_Year_Exp.

A: Find nth number highest salary.Table:- EMPLOYEEColumn Name:-Name,Dept_id,salary,On_Year_Exp

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: ....... Convert the following ER model to relational model colour Vehicle No name model car vehicle…

A: Converting ER Model to Relational Model Model Mapping Steps: Create a table for Strong Entities.…

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: Query list of all our customers full name and email. Show those whose customer id is either…

A: The above question is solved in step 2:-

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: Display the last_name of students table who have algebra_grades from 80 to 90,

A: i have answered this question in step-2.

Q: EMPLOYEE TABLE EMPLOYEE_ID FIRST_NAME SURNAME CONTACT NUMBER emp101 0877277521 emp102 emp103 emp104…

A: The queries for creating and inserting data to all tables are given below along with final OUTPUT…

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: Display the last name, salary, and commission for all employees who earn commissions. Sort data in…

A: In the above question, needs to find out only those employee's last name, salary, and commission who…

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: SELECT firstNAME,City FROM students WHERE AGE 20;

A: convert to relational algebra

Q: SUPPLIERS SID SNAME STYPE LOCATION 1 Lulu DS Salalah 2 C4 DS Saadah 3 Smile WS…

A: Here first we need to find SID of product 802. Let's name this as X. Then find productID and…

Q: According to the “Orders" table below, which of the following will output the customers who made…

A: Question :-

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: Table : PURCHASE_ORDER ORDER_NO AMOUNT ORDER DATE CUSTOMER_ID SALESMAN ID DELIVERY_ DATE 1001 300…

A: b) SELECT * FROM PURCHASE_ORDER INNER JOIN PURCHASE_DETAILS ON PURCHASE_ORDER.ORDER_NO =…

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…

Q: Find the names and surnames of the customers who received the "Kalem" in 2018 but did not receive it…

A: Note: we have provided answer according to table and attributes mention in picture part. we need to…

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.
Display the product ID and the number of orders placed for each product. Show the results in decreasing order by the number of times the product has been ordered and label this result column NumOrders.
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
For each product, show the product ID and the number of orders placed.Show the results in decreasing order based on the number of times the product has been ordered, with NumOrders as the result column.
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.
SELECT FIRST_NAME, LAST_NAME, SCHOOL_FEE FROM STUDENT, FEE_PAYMENT WHERE STUDENT. STUDENT_ID=FEE_PAYMENT. STUDENT_ID ORDER BY FIRST_NAME
Display details of jobs where the minimum salary is greaterthan 10000. using sql plus also show output
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.
Find the compound amount on deposit at the end of 1 year if Php 120, 000 is deposited at 3.5% compounded monthly. Note: Convert your answers in WHOLE NUMBER format only.