Refer the given tables and answer the question that follow:Table : PURCHASE_ORDERORDER_NO AMOUNT ORDER_DATE CUSTOMER_ID SALESMAN_ DELIVERY_DATENAME100130005-03-20172001AJI05-05-20171002200.2010-07-20172002SHALA10-10-2017100360012-06-20172005KABIR12-08-20171004400.2510-05-20172003AKBAR10-07-20171005500.1003-08-20172004BABER03-05-2017100640010-07-20172005HUSAIN10-10-2017a) Write an SQL query to display the ORDER_NO and ORDER_DATE from PURCHASE_ORDER tablewho order the products between 05-03-2017 and 01-06-2017b) Develop an SQL query to display the ORDER_NO, SALESMAN_NAME and extract the first three letters ofORDER NO. Give an alias Name PRO_ORDER. from the table PURCHASE_ORDER.c) Write an SQL query to display the ORDER_NO, DELIVERY_DATE and find the average of amount, alsogroup the records based on ORDER_NO. [Hint: Use PURCHASE_ORDER table].d) Develop an SQL query to display the unique ORDER_NO and CUSTOMER_ID for those SALEAMANNAME ends with letter R. [Hint: PURCHASE_ORDER Table]

Answer: a) SELECT * FROM PURCHASE_ORDER; SELECT ORDER_NO,ORDER_DATE FROM PURCHASE_ORDER WHERE…

Answered: Refer the given tables and answer the…

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter11: Group Functions

Section: Chapter Questions

Problem 16MC

See similar textbooks

Similar questions

Can you write 10 query for this tables? Plase use join and nested queries Customer (CustomerID(Primary Key), phone) Customer_Name (CustomerID(Primary Key),fname,lname) Customer_Location(CustomerID(Primary Key),pincode,district,street_code,street_name,city) Employee(EmployeeID(Primary Key),phone,startdate) Employee_name(EmployeeID(Primary Key),emp_name) Employee_location(EmployeeID(Primary Key),city,country,country_code,pincode,street_code,street_name) RawMaterial(RawMaterialsID(Primary Key),available_quantity) RawMaterial_information(RawMaterialsID(Primary Key),raw_name,raw_details) RawMaterial_price(RawMaterialsID(Primary Key),raw_price) Product(ProductID(Primary Key),type) Product_price(ProductID(Primary Key),product_price) Product_information(ProductID(Primary Key),product_name,product_details) Payment(PaymentID(Primary Key),CustomerID,ProfitID) Product_quantity(ProductID(Primary Key),payment_amount) Payment_data(ProductID(Primary Key),data) Profit(ProfitID(Primary Key),date)…
Which query in this relational database (represented by relational diagram) will provide you with the list of 3 customers from Germany who bought most iPhones 11 phones? Select one: a. SELECT TOP(3) c.CustomerID, c.CompanyName, Count(od.Quantity) FROM Products p LEFT JOIN [Order Details] od ON p.ProductID = od.ProductID LEFT JOIN Orders o ON od.OrderID = o.OrderID LEFT JOIN Customers c ON o.CustomerID = c.CustomerID WHERE c.Country = 'Germany' AND p.ProductName = 'iPhone 11' GROUP BY c.CustomerID, c.CompanyName ORDER BY Count(od.Quantity) DESC b. SELECT TOP(3) c.CompanyName, SUM(od.Quantity) FROM Products p INNER JOIN [Order Details] od ON p.ProductID = od.ProductID INNER JOIN Orders o ON od.OrderID = o.OrderID INNER JOIN Customers c ON o.CustomerID = c.CustomerID WHERE c.Country = 'Germany' AND p.ProductName = 'iPhone 11' GROUP BY c.CustomerID, c.CompanyName ORDER BY SUM(od.Quantity) DESC c. SELECT TOP(3) c.CompanyName, p.ProductID, Sum(od.Quantity) FROM Products p LEFT…
SQL SQL Provide the StayWell Property Management team with a list of all property addresses combined with the name of the owners. here is what i have so far but says query completed but nothing shows SELECT O.FIRST_NAME, O.LAST_NAME,P.ADDRESS FROM OWNER AS O,PROPERTY AS P two tables im grabbing the information from are attached owner table and property expected outcome FIRST_NAME LAST_NAME ADDRESS Ernest Burke 30 West Thomas Rd. Ceyda Aksoy 782 Queen Ln. Nicole Bianchi 9800 Sunbeam Ave. Jakub Kowalczyk 105 North Illinois Rd. Haydon Sims 887 Vine Rd. Elle-May Moore 8 Laurel Dr. Meerab Cole 447 Goldfield St. Jakub Kowalczyk 594 Leatherwood Dr. Makesh Patel 504 Windsor Ave. Janine Lopez 891 Alton Dr. Ammarah Jones 9531 Sherwood Rd. Seth Redman 2 Bow Ridge Ave. Haydon Sims 916 Kent Drive
DATABASE SQL PLEASE DO NOT COPY AND PASTE FROM PREVIOUS ANSWERS AND READ THE QUESTIONS CAREFULLY TABLES: drop table tech_supplier cascade constraints;drop table teacher cascade constraints;drop table student cascade constraints;drop table tablet cascade constraints;drop table tablet_amountstatus cascade constraints;create table tech_supplier(supplier_id number(5) primary key,supplier_name varchar2(15),address varchar2(15));insert into tech_supplier values(201,'ali','Amman');insert into tech_supplier values(202,'omar','Zarqa');insert into tech_supplier values(203,'hend','Irbid');insert into tech_supplier values(204,'bana','Amman');insert into tech_supplier values(205,'zaid',null);-----------------------------------------------------------------------------------------------------------------------create table teacher(id number(5) primary key,name varchar2(15),specialty varchar2(25));insert into teacher values(121,'said','physics');insert into teacher values(122,'reem','math');insert…
Instructions: Write the suitable SQL statement to answer the following questions based on the following tables ( image for better table) Table Name: Employee EMP_ID FNAME LNAME SALARY JOIN_DATE DEP_NO 101 John Abraham 10000 01-JAN-13 11 202 Michael Clarke 80000 01-JAN-13 22 303 Roy Thomas 70000 01-FEB-13 11 404 Tom Jose 60000 01-FEB-13 22 505 Jerry Pinto 65000 01-FEB-13 22 606 Philip Mathew 75000…
Instructions: Write the suitable SQL statement to answer the following questions based on the following tables ( image for better table) Table Name: Employee EMP_ID FNAME LNAME SALARY JOIN_DATE DEP_NO 101 John Abraham 10000 01-JAN-13 11 202 Michael Clarke 80000 01-JAN-13 22 303 Roy Thomas 70000 01-FEB-13 11 404 Tom Jose 60000 01-FEB-13 22 505 Jerry Pinto 65000 01-FEB-13 22 606 Philip Mathew 75000…
Instructions: Write the suitable SQL statement to answer the following questions based on the following tables ( image for better table) Table Name: Employee EMP_ID             FNAME               LNAME               SALARY       JOIN_DATE              DEP_NO                     101                  John                  Abraham            10000                   01-JAN-13          11             202                  Michael                Clarke                80000                  01-JAN-13          22            303                       Roy                   Thomas              70000                  01-FEB-13          11             404                     Tom                       Jose                  60000                   01-FEB-13          22             505                        Jerry                    Pinto                 65000                   01-FEB-13               22             606                  Philip                  Mathew              75000…
Instructions: Write the suitable SQL statement to answer the following questions based on the following tables ( image for better table) Table Name: Employee EMP_ID             FNAME               LNAME               SALARY       JOIN_DATE              DEP_NO                     101                  John                  Abraham            10000                   01-JAN-13          11             202                  Michael                Clarke                80000                  01-JAN-13          22            303                       Roy                   Thomas              70000                  01-FEB-13          11             404                     Tom                       Jose                  60000                   01-FEB-13          22             505                        Jerry                    Pinto                 65000                   01-FEB-13               22             606                  Philip                  Mathew              75000…
Given the following schema, answer the following questions USERS Attribute Data Type USER_ID NUMBER(3) USER_NAME VARCHAR2(20) USER_TYPE VARCHAR2(10) USER_LIMIT NUMBER(1)                         BOOKS Attribute Data Type BOOK_ID NUMBER(5) BOOK_TITLE VARCHAR2(25) BOOK_YEAR VARCHAR2(4) Write the SQL code that will create the tables structures for the tables named Books, and Users. Identify and include any PK and FK for both tables.
SELECT P.BRAND_ID, B.BRAND_NAME, B.BRAND_TYPE,MAX(AVGPRICE) FROM LGPRODUCT P INNER JOIN LGBRAND B ON P.BRAND_ID = B.BRAND_ID (SELECT P.BRAND_ID, AVG (P.PROD_PRICE) AS AVGPRICE FROM LGPRODUCT P GROUP BY P.BRAND_ID)AS AVG_PRICE GROUP BY P.BRAND_ID, B.BRAND_NAME, B.BRAND_TYPE ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT P.BRAND_ID, AVG (P.PROD_PRICE) AS AVGPRICE FROM LGPRODUCT P GROUP BY P.' at line 3
2- selects the order line items (orderNumber, orderlinenumber) fromthe “orderdetails” table. Also, display the subtotal (quantityOrdered * priceEach) for eachline item and sorts the result set based on the subtotal descending .3- Display unique last names from employees table ordered by last name.4- Display customerNumber, customerName and creditLimit for getting the top five customerswho have the highest creditLimit.5- Find customer Name, customer country, sales representative employee number fromcustomers who do not have a sales representative.6- For each order status, display status and count of orders have this status.7- For Each order number in order details table, Display order number, sum of quantityordered (itemsCount) and sum of (priceEach * quantityOrdered) as total. With condition“total” greater than 1000.8- Display product code, product name and text description for product.9- Find customer number, customer name, order status and order number, for customerwith their orders…
With SQL code, find the name of each employee with a manager along with their manager's name (use cartesian product and rename) Employee table: create table employee( ID varchar(6) not null,person_name varchar(10),street varchar(10),city varchar(10),primary key (ID)); Manages table: create table manages(ID varchar(6) not null,manager_id varchar(6),primary key (ID),foreign key (ID) references employee,foreign key (manager_id) references employee);

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:

9781337508841

Author:

Carey

Publisher:

Cengage

CMPTR

Computer Science

ISBN:

9781337681872

Author:

PINARD

Publisher:

Cengage

SEE MORE TEXTBOOKS