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.

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

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

Programming language= In Java and Oracle Sql You are required to develop a simple HR application...

Programming language= In Java and Oracle Sql You are required to develop a simple HR application for a small accounting firm that wishes to keep track of all the employees at the firm; storing details about their salary, phone numbers and Date of Birth. The firm has many departments and there are 5 to 20 employees in each department. The department information includes department name, description and total number of employees in that department. The company also provides vehicles for...

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

lab requires you to use Oracle VIEW to implement a virtual database on DBSEC schema, for...

lab requires you to use Oracle VIEW to implement a virtual database on DBSEC schema, for example, on CUSTOMER table. Your task is to develop a single SQL script that will perform all the following tasks: Table created for this assignment is listed below: create table CUSTOMER_VPD( SALES_REP_ID NUMBER(4), CUSTOMER_ID NUMBER(8) NOT NULL, CUSTOMER_SSN VARCHAR(9), FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), ADDR_LINE VARCHAR(40), CITY VARCHAR(30), STATE VARCHAR(30), ZIP_CODE VARCHAR(9), PHONE VARCHAR(15), EMAIL VARCHAR(80), CC_NUMBER VARCHAR(20), CREDIT_LIMIT NUMBER, GENDER CHAR(1), STATUS CHAR(1), COMMENTS...

Question