A6-Ting Wei Chien(Willy)

docx

School

Santa Clara University *

*We aren’t endorsed by this school

Course

443

Subject

Computer Science

Date

Jun 11, 2024

Type

docx

Pages

Uploaded by CaptainMorningDonkey44

CS443 -- Assignment 6 Ting Wei Chien Write the queries necessary to obtain the required information. Make sure all columns you return have descriptive column headings. You must show the result of the query. For example, if the query is:  Show the office id, the city, and the region  Your query should be: select office, city, region from offices;  and then you need to show the following on the screen: (snapshot of your result) 1) Return the Minimum and Maximum sales for all offices. select min(sales), max(sales) from offices; 2) Determine how many orders were made in 1989. Return the number of rows that meet this condition. select count(order_date) from orders where order_date like '%89'; 3) How many different titles in the sales reps table.

select count(distinct title) from salesreps; 4) What is the average quota for salesreps in office 21. select avg(quota) from salesreps where rep_office = 21; 5) What is the average sale amount for each sale reps in each office. Null should be ignored select office, avg(salesreps.sales) from offices, salesreps where salesreps.rep_office = offices.office group by office; 6) For each salesrep that has made an order, list the minimum, maximum and average order amount for all their orders. Include only those orders made anytime from 1990- 1999. Omit from the list any salesrep that has only made 1 order in this time frame. Sort the results by Empl_Num. select salesreps.empl_num, min(amount), max(amount), avg(amount) from salesreps, orders where salesreps.empl_num = orders.rep and extract(year from order_date) between 1990 and 1999 group by empl_num having count(order_num) > 1 order by empl_num;

7) Use a sub-query to list the Customer number; Name and Credit Limit of any customers who have exceeded their credit limit (amount > credit limit) on any order. select cust_num,company, credit_limit from customers where credit_limit < any ( select amount from orders where cust = cust_num); 8) Use a subquery and using the “all” keyword to find the customer number, Salesrep id, and CreditLimit of every customer whose CreditLimit is larger than the CreditLimit of all of the customers of sales rep number 109. select cust_num,empl_num, credit_limit from salesreps, customers where cust_rep = empl_num and credit_limit > all ( select credit_limit from customers where cust_rep = 109); 9) Do question 8, still using the subquery but do not use the “all” keyword. select cust_num,empl_num, credit_limit from salesreps, customers where cust_rep = empl_num

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

and credit_limit > (select max(credit_limit) from customers where cust_rep = 109); 10) Use sub query and “in” keyword to print the salesreps (ids) who have taken order for the companies ‘Zetacorp’ or ‘JCP Inc.’ . Duplicate rows are not allowed select distinct rep from orders where cust in ( select cust_num from customers where company = 'Zetacorp' or company = 'JCP Inc.'); 11) Use sub query to find the id and the name of every sales rep that represents at least one customer with a credit limit of greater than $5000. select empl_num, name from salesreps where empl_num in ( select cust_rep from customers where (credit_limit > 5000 )); 12) Use sub query and keyword “exists” to list the id and the name of the salesreps in which some customers have orders some products in their hiredate.

select empl_num, name from salesreps where exists ( select rep from orders where orders.order_date = salesreps.hire_date); 13) List all the products (Mfr_ID and Product_ID) that have never been sold. Use the ‘Exists’ clause. select mfr_id, product_id from products where not exists ( select qty from orders where product = product_id and mfr = mfr_id); 14) Insert the following information into the OFFICES table: Office: 39 City: Miami Region: Southern Manager: 106 Target: 1000000 Sales: 0 insert into offices values(39,'Miami','Southern',106,10000,0); 15) Write an insert statement to add Your Name as Empl_Num 772. Use the date the insert is run as the Hire date (sysdate). Sales are zero. Other column remain NULL; insert into salesreps (empl_num,name,hire_date,sales) values (772,'Willy Chien',sysdate,0);

16) Write an insert statement to add 'Tom Sawyer' Empl_Num 814. Use the date the insert is run as the Hire date (sysdate). Sales are zero. Use implicit null values for columns that are not mentioned. insert into salesreps (empl_num,name,hire_date, age, rep_office, title, manager, quota, sales) values (814,'Tom Sawyer',sysdate,null,null,null,null,null,0); 17) Delete all sales reps that have no orders and were hired before Jan 1 1987. delete from salesreps where hire_date < '01-JAN-87' and not exists( select rep from orders where rep = empl_num); 18) Update your employee record with the following: Age: 37 Rep_Office:39 Title: Senior VP Manager: NULL Quota: 100000 update salesreps set age = 37, rep_office = 39, title = 'Senior VP', manager = null, quota = 100000 where salesreps.name = 'Willy Chien';

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

19) Increase customers credit limit by 25% for all customers that have 2 or more orders in which each order is more than 25,000. update customers set credit_limit = (credit_limit * 1.25) where (select count(orders.cust) from orders where cust = cust_num and amount > 25000) >= 2; 20) Increase the credit limit of any customer who has any order that exceeds their credit limit. The new credit limit should be set to their maximum order amount plus $1,000. This must be done in 1 SQL statement. update customers set credit_limit = ((select(amount) from orders where cust = cust_num) + 1000) where credit_limit < any (select amount from orders where cust = cust_num);

Related Documents

7-2 Project Three Submission Evaluation of Network Protection Technologies Brandon Meadows.docx

Untitled document (14).docx

5-3-project-one-cyb220.pdf

A3-Ting Wei Chien(Willy).docx

Exploring Onion Routing: Anonymity, Security, and Tor Design

A7-Ting Wei Chien(Willy).docx

LabActivity8.docx

Understanding Cybersecurity Response & ECPA Compliance

Chapter 5 Notes.docx

A8-Ting Wei Chien(Willy).docx

MCDB 108C Spring 2024 Midterm Exam #1 Study Guide

Implementing C Programs for TimeServer and TimeClient in Week 12

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:9781305251038

Author:Joan Casteel

Publisher:Cengage Learning

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

Programming with Microsoft Visual Basic 2017

Computer Science

ISBN:9781337102124

Author:Diane Zak

Publisher:Cengage Learning

A Guide to SQL

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

CMPTR

Computer Science

ISBN:9781337681872

Author:PINARD

Publisher:Cengage

SEE MORE TEXTBOOKS

Recommended textbooks for you

Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
CMPTR
Computer Science
ISBN:9781337681872
Author:PINARD
Publisher:Cengage