- The original title summary:
- English:
- Delete_rows.sql
- Alter_tables.sql:
- (1) List all library member IDs, first names, and Join_date that meet the following requirements;
- (2) List the names of all books that meet the following requirements, ISBN, CallNumber.
- (3) List all library member IDs and first names that meet the following requirements.
- (4) List the IDs and first names of all library members that meet the following requirements.
- (5) List all published names, IDs, and total amount of books that meet the following requirements.
- (6) List the first name, ID of all library members who meet the following requirements.
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.
English:
For this exercise, we will use the library schema. 
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.
Delete_rows.sql
|
1 2 3 4 |
USE libsupply; DELETE FROM book4; DELETE FROM title4; DELETE FROM member4; |
3. Write SQL statements as required and run them to complete the modification and improvement of the database.
Alter_tables.sql:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
Alter table libsupply.title4 Add constraint ak_isbn alternate KEYS title4(isbn); Alter table libsupply.title4 Add constraint ak_isbn alternate KEYS title4(name); Alter table libsupply.MEMBER4 Add Join_date DATE; Alter table libsupply.MEMBER4 Add Gender CHAR(1); ALTER TABLE libsupply.book4 Add CONSTRAINT fk_callnumber FOREIGN KEY(callnumber) REFERENCES libsupply.title4(callnumber); ALTER TABLE libsupply.book4 Add CONSTRAINT fk_borrowerid FOREIGN KEY(borrowerid) REFERENCES libsupply.member4(libid); CREATE TABLE PURCHASE_ORDER4( PoNum char(8), Qty INT, OrderDate DATE, DueDate DATE, ReceivedDate DATE, PRIMARY KEY (PoNum) ); CREATE TABLE SUPPLIER4( Supplier_Id char(8), Name varchar(30), Address VARCHAR(100), PRIMARY KEY(Supplier_Id) ); CREATE TABLE READ_BY4 ( Callnumber char(8) NOT NULL, LibId char(6) NOT NULL, TimesRead INT, Primary KEY(CallNumber, LibId), FOREIGN KEY(CallNumber) REFERENCES libsupply.title4(CallNumber), FOREIGN KEY(LibId) REFERENCES libsupply.MEMBER4(LibId) ); CREATE TABLE ORDERED4( CallNumber char(8) NOT NULL, PoNum char(8), Supplier_Id char(8), PRIMARY KEY(CallNumber, PoNum, Supplier_Id), FOREIGN KEY(CallNumber) REFERENCES libsupply.TITLE4(CallNumber), FOREIGN KEY(PoNum) REFERENCES libsupply.PURCHASE_ORDER4(PoNum), FOREIGN KEY(Supplier_Id) REFERENCES libsupply.SUPPLIER4(Supplier_Id) ); ALTER TABLE READ_BY4 Add CONSTRAINT CK_TR CHECK(TimesRead>=0); |
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
|
1 2 3 |
SELECT libid,fname,Join_date FROM libsupply.member4 WHERE (libid LIKE 'A/%B%' ESCAPE '/' or libid LIKE 'A&B%') AND JOIN_date<'1997-11-30'; |
(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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE libsupply; SELECT T4.name,t4.isbn,t4.callnumber FROM Title4 t4,ordered4 o4,purchase_order4 po4,supplier4 s4 WHERE T4.callnumber = o4.callnumber AND o4.ponum = po4.ponum AND o4.supplier_id = s4.supplier_id GROUP BY t4.callnumber HAVING COUNT(O4.SUPPLIER_ID)>=2 and SUM(po4.Qty)>=10 ORDER BY o4.supplier_id; |
/*
(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. */
|
1 2 3 4 5 6 7 |
SELECT m4.libid,m4.fname From member4 m4,read_by4 rb4 WHERE m4.libid=rb4.libid GROUP BY rb4.libId HAVING SUM(rb4.TimesRead)<(SELECT SUM(TimesRead) From read_by4)*0.05 ORDER BY SUM(rb4.TimesRead); |
(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.
|
1 2 3 4 5 6 7 |
SELECT libid,fname From member4 WHERE libid in (( SELECT libid From read_by4 )and Gender='F' ORDER BY fname; |
(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 ). */
|
1 2 3 4 5 6 7 8 |
Select s4.Name, s4.Supplier_Id, SUM(po4.Qty)/COUNT(o4.PoNum), SUM(po4.Qty), COUNT(o4.PoNum) From ordered4 o4, supplier4 s4,purchase_order4 po4 Where o4.PoNum=po4.PoNum and o4.Supplier_Id=s4.Supplier_Id GROUP BY o4.Supplier_Id HAVING SUM(po4.Qty)/COUNT(o4.PoNum)<( SELECT AVG(Qty) FROM purchase_order4 ); |
/*
(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 */
|
1 2 3 4 5 6 7 8 9 |
SELECT m4.libid,m4.fname From member4 m4,read_by4 rb4,title4 t4 Where m4.gender='M' And m4.JOIN_date<='1995-10-10' And t4.callnumber=rb4.callnumber And m4.libid=rb4.libid AND rb4.TimesRead>=2 GROUP BY (m4.LibId) HAVING COUNT(DISTINCT t4.name)<=5; |
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
No Comment