Can you please implement this in Oracle sql Write a SELECT statement that returns one row...

Can you please implement this in Oracle sql

Write a SELECT statement that returns one row for each customer that has orders with these columns:

The email_address from the Customers table

A count of the number of orders

The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)

Return only those rows where the customer has more than 1 order.

Sort the result set in descending sequence by the sum of the line item amounts.

Solutions

Expert Solution

Customer table:

Order table:

Query:

SELECT c.email_address, SUM((o.price - o.discount)*o.quantity) AS amount,COUNT(o.cust_id) AS num_of_orders FROM `customer` c INNER JOIN `orders` o ON c.oid = o.oid GROUP BY o.cust_id HAVING num_of_orders > 1 ORDER BY amount DESC

Result:

venereology answered 1 year ago

Next > < Previous

Related Solutions

Please implement this in Oracle sql 2.) Write a SELECT statement that answers this question: What...

Please implement this in Oracle sql 2.) Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns: The product name from the Products table The total amount for each product in the Order_Items (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) Use the ROLLUP operator to include a row that gives the grand total.

In SQL we are working with functions 1. Write a SELECT statement that returns these columns...

In SQL we are working with functions 1. Write a SELECT statement that returns these columns from the Instructors table: a. The AnnualSalary column b. A column named MonthlySalary that is the result of dividing the AnnualSalary column by 12 c. A column named MonthlySalaryRounded that calculates the monthly salary and then uses the ROUND function to round the result to 2 decimal places 2. Write a SELECT statement that returns these columns from the Students table: a. The EnrollmentDate...

Can you explain the means of “where, from, select”? Of SQL

Write a function checkvertical(board, word, row, col), which returns True if the word word can be...

Write a function checkvertical(board, word, row, col), which returns True if the word word can be added to the board starting at position board[row][col] and going down. The restrictions are (1) it has to fit entirely on the board, (2) it has to intersect and match with one or more non-blank letters on the board, and (3) it cannot change any letters on the board, other than blanks (that is, overlapping mismatches are not allowed). My program isn't working :(...

Please write the SQL statement for the following. I am using the Adventurework2014 database. 1. Create...

Please write the SQL statement for the following. I am using the Adventurework2014 database. 1. Create a login for AdventureWorks employees. An employee login should be composed of the first letter of a person's first name combined with their last name. In addition, the login should be all lower case characters. All the required information is located in Person.Person table. Employees can be identified by "EM" value in the PersonType field. The output should include BusinessEntityID, first name (FirstName), last...

Write a SELECT statement that returns these columns using a JOIN of three tables: order_date from...

Write a SELECT statement that returns these columns using a JOIN of three tables: order_date from the orders table shipped_date from the orders table order_qty from the order_details table title from the items table artist from the items table unit_price from the items table Result table should be in order of order_date in ascending order

QUESTION 34 seqAID is a valid Oracle sequence: select seqAID.nextval from dual; the above query returns...

QUESTION 34 seqAID is a valid Oracle sequence: select seqAID.nextval from dual; the above query returns 1500, what the following query will return? Select seqAID.nextval From dual; A. 1499 B. 1500 C. 1501 D. 1502 QUESTION 35 after running the query in previous Question, what the following query will return? Select seqAID.nextval-1 From dual; A. 1499 B. 1500 C. 1501 D. 1502 QUESTION 36 after running queries in previous two Questions (34 & 35), what the following query will return?...

PL/SQL Write a PL/SQL block, using a Case Statement that prints a student’s letter grade...

PL/SQL Write a PL/SQL block, using a Case Statement that prints a student’s letter grade based on the value stored in a variable called grade. Use the ACC grading system described in the course syllabus to create the block and set the initial value of grade as 95. Use only one print statement and no logical operators in your code. Assume a grade can exceed 100, but it can’t be negative. Grade Scale: Grade Scale:...

What is the benefit of using “prepared statements” in the prevention of SQL injection? Select one:...

What is the benefit of using “prepared statements” in the prevention of SQL injection? Select one: a. User input is treated as secret data like passwords. b. User input is properly treated as commands, rather than as secret data like passwords c. With them it is easier to construct a SQL query d. They ensure user input is parsed as data, not (potentially) code

Write a SQL statement which joins the parts table with the supplier table and lists the...

Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query. Write a SQL statement which joins the parts table with the suppliers table and lists...

Subjects