HW8_SQL_AdvQueries

docx

School

Northern Kentucky University *

*We aren’t endorsed by this school

Course

350

Subject

Computer Science

Date

Apr 3, 2024

Type

docx

Pages

5

Uploaded by Lukem11568

Report
CSC 350 Database Programming Assignment 8 (62 points) Due Date: As posted in Canvas Advanced SQL Queries a) Use your script from HW Assignment 6 to create the LIBRARY relational database schema in the figure below, in your Oracle schema. (Alternatively, a solution script HW6_SQL_DDL_Library.sql is provided on Canvas for that assignment.) b) Use the HW7_HW8_SQL_DML_Library.sql script provided on Canvas to populate the LIBRARY tables. If you use your HW Assignment 6 script and get errors because varchar attributes are limited to less characters than used in the insert statements, please extend the allowed size for these attributes, recreate the LIBRARY tables, and run again the script to populate them. Leave the tables in your schema until your assignment is graded in Canvas. c) (9 points) Find the book_id and title of the most expensive book(s). d) (11 points) For each publishing company, find the number of books in the library published by that publisher. Requirements: - Report the publisher name and the number of library books they published. The LIBRARY database
- The calculated column (= the number of books per publisher) should be named nr_of_books. - Order the report in descending order of the number of books per publisher. - Note: you are to find the number of books = titles, not the number of book copies, by each publisher. e) (15 points) Report the card_no, name, and number of books borrowed by each library member who has borrowed at least 5 different (see below) books. Requirements: - The calculated column (= the number of different books borrowed by each library member) should be named nr_of_books_borrowed. - Order the report in descending order of the number of books per member. - For each library member, you should count how many different books they borrowed; if someone borrowed the same book twice (or more times), that book should only be counted once. f) (10 points) Report the book_id and title of all books that have never been borrowed from the library. In the template below, you must use a correlated subquery in the highlighted portion. Hints: The subquery will find, for each book (= row) in the outer query, data about loans of that book; the subquery can be used in combination with comparison to a scalar, =0, or in combination with the NOT EXISTS operator. Template: SELECT book_id, title FROM book B WHERE ….. (correlated subquery) g) (8 points) Increase by 10% the price of all books under $10 and by 5% the price of all books that cost $10 or more. You must use: - a single UPDATE statement, - with the CASE operator. h) (9 points) Delete all loan records for the library members whose names start with the letter ‘S’. It is required that you use a subquery to find who are these library members. Turn in, for each query c) – h): - The SQL query. Each query has to run correctly on our Oracle database server; test it in your Oracle schema, as you would any other program.
- For SELECT queries c)-f): the output of running the query on the LIBRARY database after creating it in Oracle; output is 1 point of the query value. No output required for queries g) and h). - The queries must be provided as plain text, not as an image. - Please write each select clause (SELECT, UPDATE, DELETE, FROM, WHERE etc.) on a separate line and preferably in uppercase. How and where to turn in answers: - All and only the answers for queries c) - h) must be included in this Word document – in the highlighted sections below. - Re-name the Word document HW8_ YourLastName .docx (substitute your last name), and submit it using the link Assignment 8 in the module for Chapter 3 (Introduction to SQL). Requirements a) and b): Leave the LIBRARY tables in your schema until your assignment is graded in Canvas. Query c): SELECT book_id, title FROM book WHERE price = (SELECT MAX(price) FROM book); Query c) output: Query d): SELECT publisher, COUNT(book_id) AS nr_of_books FROM book GROUP BY publisher ORDER BY nr_of_books DESC; Query d) output: Query e):
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
SELECT br.card_no, br.name, COUNT(DISTINCT bl.book_id) AS nr_of_books_borrowed FROM borrower br JOIN book_loans bl ON br.card_no = bl.card_no GROUP BY br.card_no, br.name HAVING COUNT(DISTINCT bl.book_id) >= 5 ORDER BY nr_of_books_borrowed DESC; Query e) output: Query f): SELECT book_id, title FROM book B WHERE NOT EXISTS ( SELECT 1 FROM book_loans BL WHERE BL.book_id = B.book_id ); Query f) output: Query g): UPDATE book SET price = CASE WHEN price < 10 THEN price * 1.10 WHEN price >= 10 THEN price * 1.05 ELSE price END;
Query h): DELETE FROM book_loans WHERE card_no IN ( SELECT card_no FROM borrower WHERE UPPER(name) LIKE 'S%' );

Related Documents

LucianoRosario_IT640_IP5.pptx
HW3.docx
Laudando_Sara_Hardware_Shopping.docx
Sachin veldi APCS-P ModuleSixLessonTwoActivityGuide.docx
LAB 1 EC2-3_09_03_2023 (1).docx
Lab3_S3-1.docx
Assignment 4.docx
quiz1spring23.pdf
Quiz 1 notes.pdf
Exam_1_spring_2024_solutions.pdf
SWEN90016_S1_2023_T06_Instructions_Sprint.docx
Food Systems CLD Worksheet.docx
Recommended textbooks for you
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:9781305627482
Author:Carlos Coronel, Steven Morris
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
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
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:9781305627482
Author:Carlos Coronel, Steven Morris
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
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
SEE MORE TEXTBOOKS