query no 2 is select avg(CustBal) from Customer where CusState='WA' GROUP BY city. Here aggregate function avg is used to calculate average of customer balance.next where statement is used to filter the customer who live in Washington state and at last group by clause is used to show the balance city wise

query no 2 is select avg(CustBal) from Customer where CusState='WA' GROUP BY city. Here aggregate function avg is used to calculate average of customer balance.next where statement is used to filter the customer who live in Washington state and at last group by clause is used to show the balance city wise

Np Ms Office 365/Excel 2016 I Ntermed

1st Edition

ISBN:9781337508841

Author:Carey

Publisher:Carey

Chapter8: Working With Advanced Functions

Section: Chapter Questions

Problem 11RA

See similar textbooks

Similar questions

Problems 2224 are based on the following query: SELECT P_CODE, P_DESCRIPT, P_PRICE, P.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = 'NY' AND V_AREACODE = '212' ORDER BY P_PRICE; Write the command(s) used to generate the statistics for the PRODUCT and VENDOR tables.
Problems 2932 are based on the following query: SELECT CUS_CODE, MAX(LINE_UNITSLINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = '615' GROUP BY CUS_CODE; Assuming that you follow the recommendations you gave in Problem 29, how would you rewrite the query?
Repeat Exercise 4, but this time use the EXISTS operator in your query.
Problems 1114 are based on the following query: SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM(LINE_UNITS) (SELECT MAX(LINE_UNITS) FROM LINE); Write the command to create statistics for this table.
PLZ HELP WITH THE FOLLOWING: make the following work select MOVIE_NAME, MOVIE_YEAR from MOVIE where MOVIE_COST > (select a.MOVIE_COST from MOVIE a, PRICE b where a.PRICE_CODE = b.PRICE_CODE and b.PRICE_DESC ="weekly Special"); CREATE TABLE MEMBERSHIP ( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEMNUM_PK PRIMARY KEY, MEM_FNAME VARCHAR(30) NOT NULL, MEM_LNAME VARCHAR(30) NOT NULL, MEM_STREET VARCHAR(30), MEM_CITY VARCHAR(10), MEM_STATE CHAR(2), MEM_ZIP CHAR(5), MEM_BALANCE NUMBER(2) ); CREATE TABLE RENTAL ( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENTNUM_PK PRIMARY KEY, RENT_DATE DATE, MEM_NUM CHAR(3), CONSTRAINT RENTAL_MEMNUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP ); CREATE TABLE PRICE ( PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICECODE_PK PRIMARY KEY, PRICE_DESC VARCHAR(20), PRICE_RENTFEE NUMBER (3, 1), PRICE_DAILYATFEE NUMBER(3, 1) ); CREATE TABLE MOVIE ( MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIENUM_PK PRIMARY KEY, MOVIE_NAME VARCHAR(30) NOT NULL,…
Modify the query in Problem 13 to include the number of individual product purchases made by each customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you count three product purchases. Note that in the original invoice data, customer 10011 generated three invoices, which contained a total of six lines, each representing a product purchase.) Your output values must match those shown in Figure P7.14, sorted by customer code.
Write an appropriate SQL query to determine if there are any students that are not registered for any modules.Sample results:STUDENT_ID STUDENTS654321 Joe, ThaboRequirementsCorrect SELECT statement used. Correct JOIN used. Correct WHERE clause. Question 7 Write an appropriate SQL query to delete the following record from the STUDENTS table: STUDENT_ID: S654321STUDENT_SURNAME: JoeSTUDENT_NAME: ThaboRequirementsCorrect DELETE statement used. Correct WHERE clause.
Use single-row subquery in a HAVING clause. List customer numbers of customers who paid total amount (sum of quantity * paideach) of their orders less than the average of total amount of orders that paid by all customers. Include the following columns in the result. a. Customer Numberb. Total AmountHint: The subquery returns the average total amount that paid by all customers. We need to join two tables in this subquery and use group function to find average of total amount based on the number of customers.
Write a query to produce the total number of hours round to 1 digit and charges round to 2 digits for each of the projects represented in the ASSIGNMENT table, sorted by project number. In the SQL Query do the following Select clause: Proj_Num, sum of Assign_hours rounded to 1 place, sum of Assign_charge rounded to 2 places (You will be using SUM and ROUND to achieve the intended result) From: ASSIGNMENT Where clause: not needed because we are not restricting anything here Group by clause: project number column. Since we are using sum to calculate total number of hours and total charges we have to use group by clause for the columns that are not involved in the calculation which is project number column.
Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: A) FUNCTION: Use the region table to create a function called ‘Return_region_name’ which accepts an input parameter ‘region_id’, and print the name of the region with that id. Then execute this function with region_id = 1. [LAB-7] (3) B) PROCEDURE: Use the cust_node table to create a procedure called ‘Get_node_id’ that takes the cust_id as an input parameter to show its corresponding node id to the banker. Then execute this procedure with cust_id=5. [LAB-7] (2) C) Trigger: Create a row-level trigger called ‘Compute_Amount_trigger’ on the cust_txn table to automatically update the amount of withdraw transaction made after inserting or updating amount on the cust_txn table. Additionally, this trigger must show this statement ‘TRIGGER MESSAGE: The amount of cust_id and txn_type is modified successfully'. Then test your trigger by updating the cust_txn table. For example, update the amount of the…
How to learn creating user-defined functions in sql - IsNewHire: Take a hire date as an argument, and return 1 if their tenure at the company is less than two years, otherwise return 0. After your user-defined functions add SELECT statements calling those functions and illustrating their use: if possible use Aventurework2019 dayabase for function
True or False below: Given two relations named A and B, if we q-JOIN A and B, the maximum number of attributes in the result relation could be (the number of attributes of A) times(*) the number of attributes of B). * in an SQL SELECT statement means “all the tuples”. In relational algebra, Cartesian Product (X) can be implemented using the q-JOIN operator. Cartesian Product ISA q-JOIN where q-condition always evaluates to true. SQL is based on Predicate Calculus. A query in SQL always specifies what should be included in the query result.

SEE MORE QUESTIONS

Recommended textbooks for you

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:

9781337508841

Author:

Carey

Publisher:

Cengage

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

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:

9781337508841

Author:

Carey

Publisher:

Cengage

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

A Guide to SQL

Computer Science