6 years ago (2015-05-11)  Algorithm language |   First to comment  6 
post score 0 times, average 0.0

This is EX3 of SSD7 practice. It is expected that the extreme guests will write a series. The update frequency will be synchronized with the 2013 level progress of NWPU database system experiment class.Click the tab or search "SSD7" on this site to get the updated SSD7 answer.Previous exercise: SSD7_EX2 [SQL]

The original title summary:

This exercise uses the library schema of the library as a case. The case includes library books information, library member information, library loan information (a book is borrowed several times by a certain reader), publisher information provided by the book, publishing house order information.To distinguish from other exercises, the last character of each relationship name in this exercise is the Arabic numeral ‘4’. Create all your relationships using the SQL statements provided by CREATE TABLE and INSERT statements. You need to run the provided SQL statements in an empty database to create the database.


For this exercise, we will use the library schema. SSD7_EX3 (Advanced SQL) It includes information about book titles in the library, information about library members, about the number of times the books were read by members, about the suppliers who supply books to the library, and about orders placed with the suppliers. To distinguish the table names used in this exercise from the table names in other exercise, each table name in this exercise ends with the character '4'. Point of clarification: The ReceivedDate column refers to the date when the Order was received by the system (i.e., recorded in the database). It does not refer to the date a consumer received the items ordered by him.  

Homework is still a good thing for yourself. The following is the result of my work. It is used for communication and learning. Do not blindly plagiarize. It is your own thing to learn!The answer may be flawed, welcome to correct, limited level, for reference only:

  1. Run the provided SQL statement to complete the creation of the relationship and partial data insertion. 2. Write the SQL statement, delete the data from all relationships, and write your answer in a file named delete_rows.sql.


    3. Write SQL statements as required and run them to complete the modification and improvement of the database.  


2) Run the INSERT SQL statements provided by the SQL statements to insert data 3) Write the SQL statement according to the following query requirements and write your answer in a file called queries.sql.

(1) List all library member IDs, first names, and Join_date that meet the following requirements;

The ID starts with the characters "A%B" or "A&B".● Join the library no later than Nov. 30, 1997


(2) List the names of all books that meet the following requirements, ISBN, CallNumber.

● Provided by two or more publishers. ● The total amount of all orders is 10 or more. ● Arrange the results in order of decreasing order quantity.


(3) List all library member IDs and first names that meet the following requirements.

● The number of borrowed books was less than 5% of the total number of books borrowed by the library. ● Sort the results in ascending order of loan times. */


(4) List the IDs and first names of all library members that meet the following requirements.

● Gender is female. ● There is a loan record in the library (read a book once). ● Sort the results alphabetically by name.

(5) List all published names, IDs, and total amount of books that meet the following requirements.

● Average book load (the total amount of books received by a publisher divided by the number of orders received by the publisher) is lower than the system's average book load (the total number of orders received by the system divided by the total number of orders ). */


(6) List the first name, ID of all library members who meet the following requirements.

● Gender is male. ● Oct. 10, joined the library before 1995. ● Read titles with different titles 5 or less times. ● Reading the same book (the same title) twice or more times */



This article has been printed on copyright and is protected by copyright laws. It must not be reproduced without permission.If you need to reprint, please contact the author or visit the copyright to obtain the authorization. If you feel that this article is useful to you, you can click the "Sponsoring Author" below to call the author!

Reprinted Note Source: Baiyuan's Blog>>https://wangbaiyuan.cn/en/ssd7-ex3-advanced-sql-2.html

Post comment


No Comment


Forget password?