Module 8 Problems Krishna

docx

School

Rutgers University *

*We aren’t endorsed by this school

Course

335

Subject

Computer Science

Date

Jan 9, 2024

Type

docx

Pages

13

Uploaded by DrTarsier3954

Report
Module 8 Problems Krishna Priya Rao Vennamaneni Chapter 7 # 51-70 51. The Binder Prime Company wants to recognize the employee who sold the most of its products during a specified period. Write a query to display the employee number, employee first name, employee last name, email address, and total units sold for the employee who sold the most Binder Prime brand products between November 1, 2015, and December 5, 2015. If there is a tie for most units sold, sort the output by employee last name (Figure P7.51). SELECT lgemployee.EMP_NUM, EMP_FNAME, EMP_LNAME, EMP_EMAIL, SUM(LINE_QTY) AS Total FROM lgemployee JOIN lginvoice ON lgemployee.EMP_NUM = lginvoice.EMPLOYEE_ID JOIN lgline ON lginvoice.INV_NUM = lgline.INV_NUM JOIN lgproduct ON lgline.PROD_SKU = lgproduct.PROD_SKU WHERE BRAND_ID = 33 AND (INV_DATE BETWEEN '2017-11-01' AND '2017-12-05') GROUP BY EMP_NUM HAVING SUM(LINE_QTY) = (SELECT MAX(TOTAL) FROM (SELECT SUM(LINE_QTY) AS TOTAL FROM lginvoice JOIN lgline ON lginvoice.INV_NUM = lgline.INV_NUM JOIN lgproduct ON lgline.PROD_SKU = lgproduct.PROD_SKU WHERE BRAND_ID = 33 AND (INV_DATE BETWEEN '2017-11-01' AND '2017-12-05') GROUP BY EMPLOYEE_ID) A) ORDER BY EMP_LNAME; 52. Write a query to display the customer code, first name, and last name of all customers who have had at least one invoice completed by employee 83649 and at least one invoice completed by employee 83677. Sort the output by customer last name and then first name (Figure P7.52). SELECT lgcustomer.CUST_CODE, CUST_FNAME, CUST_LNAME FROM lginvoice EMP1 JOIN lgcustomer ON EMP1.CUST_CODE = lgcustomer.CUST_CODE JOIN lginvoice EMP2 ON lgcustomer.CUST_CODE = EMP2.CUST_CODE WHERE EMP1.EMPLOYEE_ID = 83649 AND EMP2.EMPLOYEE_ID = 83677 GROUP BY CUST_CODE ORDER BY CUST_LNAME, CUST_FNAME;
53. LargeCo is planning a new promotion in Alabama (AL) and wants to know about the largest purchases made by customers in that state. Write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total of the largest purchase made by each customer in Alabama. Be certain to include any customers in Alabama who have never made a purchase; their invoice dates should be NULL and the invoice totals should display as 0. Sort the results by customer last name and then first name (Figure P7.53). SELECT CUST_CODE, CUST_FNAME, CUST_LNAME, CUST_STREET, CUST_CITY, CUST_STATE, CUST_ZIP FROM LGCUSTOMER WHERE CUST_STATE = 'AL' AND CUST_CODE NOT IN (SELECT CUST_CODE FROM LGINVOICE); (SELECT lgcustomer.CUST_CODE, CUST_FNAME, CUST_LNAME, CUST_STREET, CUST_CITY, CUST_STATE, CUST_ZIP, INV_DATE, MAX(INV_TOTAL) AS 'Largest Invoice' FROM lgcustomer JOIN lginvoice ON lgcustomer.CUST_CODE = lginvoice.CUST_CODE WHERE CUST_STATE = 'AL' GROUP BY CUST_CODE) UNION (SELECT CUST_CODE, CUST_FNAME, CUST_LNAME, CUST_STREET, CUST_CITY, CUST_STATE, CUST_ZIP, NULL, 0 FROM LGCUSTOMER WHERE CUST_STATE = 'AL' AND CUST_CODE NOT IN (SELECT CUST_CODE FROM LGINVOICE)) ORDER BY CUST_LNAME, CUST_FNAME;
54. One of the purchasing managers is interested in the impact of product prices on the sale of products of each brand. Write a query to display the brand name, brand type, average price of products of each brand, and total units sold of products of each brand. Even if a product has been sold more than once, its price should only be included once in the calculation of the average price. However, you must be careful because multiple products of the same brand can have the same price, and each of those products must be included in the calculation of the brand’s average price. Sort the result by brand name (Figure P7.54). SELECT BRAND_NAME, BRAND_TYPE, ROUND(AVG(AVRG),2) AS 'Average Price', SUM(SLQ) AS 'Units Sold' FROM (SELECT lgbrand.BRAND_ID, BRAND_NAME, BRAND_TYPE, AVG(PROD_PRICE) AS AVRG FROM lgbrand JOIN lgproduct ON lgbrand.BRAND_ID = lgproduct.BRAND_ID GROUP BY BRAND_NAME) A JOIN (SELECT BRAND_ID, SUM(LINE_QTY) AS SLQ FROM lgproduct JOIN lgline ON lgproduct.PROD_SKU = lgline.PROD_SKU GROUP BY BRAND_ID) B ON A.BRAND_ID = B.BRAND_ID GROUP BY BRAND_NAME ORDER BY BRAND_NAME;
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
55. The purchasing manager is still concerned about the impact of price on sales. Write a query to display the brand name, brand type, product SKU, product description, and price of any products that are not a premium brand, but that cost more than the most expensive premium brand products (Figure P7.55). SELECT BRAND_NAME, BRAND_TYPE, PROD_SKU, PROD_DESCRIPT, PROD_PRICE FROM lgbrand JOIN lgproduct ON lgbrand.BRAND_ID = lgproduct.BRAND_ID WHERE BRAND_TYPE != 'PREMIUM' AND PROD_PRICE > (SELECT MAX(PROD_PRICE) FROM lgbrand JOIN lgproduct ON lgbrand.BRAND_ID = lgproduct.BRAND_ID WHERE BRAND_TYPE = 'PREMIUM'); 56. Write a query that displays the book title, cost and year of publication for every book in the system. Sort the results by book title. SELECT BOOK_TITLE, BOOK_COST, BOOK_YEAR FROM book ORDER BY BOOK_TITLE;
57. Write a query that displays the first and last name of every patron, sorted by last name and then first name. Ensure the sort is case insensitive (Figure P7.57). (50 rows) SELECT PAT_FNAME, PAT_LNAME FROM patron ORDER BY PAT_LNAME, PAT_FNAME; 58. Write a query to display the checkout number, checkout date, and due date for every book that has been checked out sorted by checkout number (Figure P7.58). (68 rows SELECT CHECK_NUM, CHECK_OUT_DATE, CHECK_DUE_DATE FROM checkout ORDER BY CHECK_NUM;
59. Write a query to display the book number, book title, and subject for every book sorted by book number (Figure P7.59). (20 rows) SELECT BOOK_NUM, BOOK_TITLE AS TITLE, BOOK_SUBJECT AS 'Subject of Book' FROM book ORDER BY BOOK_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
60. Write a query to display the different years in which books have been published. Include each year only once and sort the results by year (Figure P7.60) SELECT DISTINCT BOOK_YEAR FROM book ORDER BY BOOK_YEAR; 61. Write a query to display the different subjects on which FACT has books. Include each subject only once and sort the results by subject (Figure P7.61). SELECT DISTINCT BOOK_SUBJECT FROM book ORDER BY BOOK_SUBJECT;
62. Write a query to display the book number, title, and cost of each book sorted by book number (Figure P7.62). SELECT BOOK_NUM, BOOK_TITLE, BOOK_COST AS 'Replacement Cost' FROM book ORDER BY BOOK_NUM; 63. Write a query to display the checkout number, book number, patron ID, checkout date, and due date for every checkout that has ever occurred in the system. Sort the results by checkout date in descending order and then by checkout number in ascending order (Figure P7.63). (68 rows) SELECT CHECK_NUM, BOOK_NUM, PAT_ID, CHECK_OUT_DATE, CHECK_DUE_DATE FROM checkout ORDER BY CHECK_OUT_DATE DESC, CHECK_NUM ASC;
64. Write a query to display the book title, year, and subject for every book. Sort the results by book subject in ascending order, year in descending order, and then title in ascending order (Figure P7.64). (20 rows) SELECT BOOK_TITLE, BOOK_YEAR, BOOK_SUBJECT FROM book ORDER BY BOOK_SUBJECT ASC, BOOK_YEAR DESC, BOOK_TITLE ASC;
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
65. Write a query to display the book number, title, and cost for all books that cost $59.95 sorted by book number (Figure P7.65). SELECT BOOK_NUM, BOOK_TITLE, BOOK_COST FROM book WHERE BOOK_COST = 59.95 ORDER BY BOOK_NUM; 66. Write a query to display the book number, title, and replacement cost for all books in the “Database” subject sorted by book number (Figure P7.66). SELECT BOOK_NUM, BOOK_TITLE, BOOK_COST FROM book WHERE BOOK_SUBJECT = 'Database' ORDER BY BOOK_NUM;
67. Write a query to display the checkout number, book number, and checkout date of all books checked out before April 5, 2017 sorted by checkout number (Figure P7.67). SELECT CHECK_NUM, BOOK_NUM, CHECK_OUT_DATE FROM checkout WHERE CHECK_OUT_DATE < '2017-04-05' ORDER BY CHECK_NUM; 68. Write a query to display the book number, title, and year of all books published after 2015 and on the “Programming” subject sorted by book number (Figure P7.68). SELECT BOOK_NUM, BOOK_TITLE, BOOK_YEAR FROM book WHERE BOOK_YEAR > 2015 AND BOOK_SUBJECT = 'Programming' ORDER BY BOOK_NUM; 69. Write a query to display the book number, title, subject, and cost for all books that are on the subjects of “Middleware” or “Cloud,” and that cost more than $70 sorted by book number (Figure P7.69).
SELECT BOOK_NUM, BOOK_TITLE, BOOK_SUBJECT, BOOK_COST FROM book WHERE (BOOK_SUBJECT = 'Middleware' OR BOOK_SUBJECT = 'Cloud') AND BOOK_COST > 70 ORDER BY BOOK_NUM; 70. Write a query to display the author ID, first name, last name, and year of birth for all authors born in the decade of the 1980s sorted by author ID (Figure P7.70). SELECT * FROM author WHERE AU_BIRTHYEAR BETWEEN 1980 AND 1989 ORDER BY AU_ID; Leetcode: Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead. Return the result table in any order. SELECT firstName, lastName, city, state from Person Left Join Address on Person.personId = Address.personId;
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

Related Documents

BA 222 Introduction to Pandas (Oct 11) Class.py
hw3-client.py
Variability.docx
Week 8 Assignment - IPsec, SLL_TLS Standards, and VPN Security.docx
CIS502 Theories of Security Management.docx
Roles of Core Technologies in an Effective IT System-3.pdf
CIS502_Performance_Lab_Report_2_Week 6.docx
Assignment 1 Sample Solutions.pdf
Assignment 3 (4).pdf
ELG2136final2020.pdf
COMP6321_ML_Assignment2.pdf
COMP6321_ML_Assignment1.pdf
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Text book image
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
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Text book image
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
SEE MORE TEXTBOOKS