Study Notes for Oracle9i: SQL exercises

1. The book lending system includes the following tables:
Member table (member)
Book table (title)
Inventory book backup table (title_copy)
Borrow)
Reservation)

2. Table Information
1) The member table contains the following fields:
Member ID (serial number), member name, address, city, phone number, join time (default: system time), member ID is the primary key

2) The Book table contains the following fields:
Book No. (serial number), title, description, publication time, author, and book category (only 'java', 'C', 'c ++ ', 'db', and 'web') the book number is the primary key.

3. The inventory backup table contains the following information:
Number of books, inventory quantity (minimum quantity: 0), missing quantity (default value: 0), and number of books as the primary key

4. The book borrowing table contains the following fields (each member can borrow more books ):
Serial number, book number, member ID, borrowing date (the default is the current date of the system), return date, expiration date (the default is one month after the system signing date), serial number is the primary key

5. The reservation table contains the following fields (each member can book multiple books ):
Member ID, book number, and reservation date. Member ID, book number, and reservation date are combined primary keys.

3. Procedure
1) create a user neu and assign the create session, unlimited tablespace, create table, createsequence, and create view permissions to the user.

SQL> create user Neusoft identified by Neu;

SQL> grant create session, unlimited tablespace, create table,
Create sequence, create View
To neu;

2) create tables, select appropriate data types, and add integrity constraints.

Create Table Member
(Member_id number (10) Constraint member_id_pk primary key,
M_name varchar2 (25) not null,
Address varcharacter 2 (100 ),
City varchar2 (30 ),
Phone varchar2 (15 ),
Join_date date default sysdate not null );

Create Table title
(Title_id number (10) Constraint title_id_pk primary key,
Title varchar2 (60) not null,
Description varchar2 (400) not null,
Pub_date date,
Author varchar2 (40 ),
Label varchar2 (20) Constraint label_ck
Check (label in ('java', 'C', 'c ++ ', 'db', 'web ')));

Create Table title_copy
(Title_id number (10) Constraint title_bak_pk primary key,
Bak_num number (10) Constraint bak_num_c check (bak_num> = 0 ),
Lack_num number (10) default 0,
Constraint title_id_bak_fk foreign key (title_id) References title (title_id ));

Create Table reservation
(Member_id number (10) Constraint r_member_id_fk references member (member_id ),
Title_id number (10) Constraint r_title_id_fk references title (title_id ),
Res_date date,
Constraint res_s_pk primary key (member_id, title_id, res_date ));

Create Table borrow
(Borrow_seq number (10) Constraint borrow_seq_pk primary key,
Title_id number (10) Constraint B _title_id_fk references title (title_id ),
Borrow_date date default sysdate,
Return_date date,
Exp_date date default add_months (sysdate, 1 ));

3) Check the data dictionary to verify that the table and constraints have been completely created.

Select * From user_tables;
Select * From user_constraints;

Or:
Select table_name
From user_tables
Where table_name in ('member', 'title', 'title _ copy', 'borrow', 'reservation ');

Select constraint_name, constraint_type, table_name
From user_constraints
Where table_name in ('member', 'title', 'title _ copy', 'borrow', 'reservation ');

4) create a sequence to uniquely identify each row in the member table, book table, and book borrowing table

Create sequence member_seq
Increment by 1
Start with 1
Nocache;

Create sequence title_seq
Increment by 1
Start with 1
Nocache;

Create sequence borrow_seq
Increment by 1
Start with 1
Nocache;
5) Verify the sequence that already exists in the data dictionary

Select * From user_sequences;

Or:
Select sequence_name, increment_by, last_number
From user_sequences
Where sequence_name in ('Member _ seq ', 'title _ id_seq', 'borrow _ seq ');

6) add data to the table. Note: sequence is used to add data.

Insert into Title
Values (title_seq.nextval, 'World is ping', 'introduce why the world is ping', '14-September 07 ', 'xiyou', 'java ');
 
Insert into Member
Values (member_seq.nextval, 'yang', 'shaanxi hancheng', 'hancheng', 83660002, sysdate );

Insert into title_copy
Values (title_seq.currval, 10, 0 );

Insert into borrow (borrow_seq, title_id, member_id, borrow_date, exp_date)
Values (borrow_seq.nextval, title_seq.currval, member_seq.currval, sysdate, add_months (sysdate, 1 ));

Insert into reservation
Values (member_seq.currval, title_seq.currval, sysdate );

7) create a view named title_avail to display the book number, book title, publishing time, and inventory quantity. query all data with a publishing date greater than from the view, sort by Book Number

Create or replace view title_avail
As
Select T. title_id, T. Title, C. bak_num, T. pub_date
From Title T, title_copy C
Where T. title_id = C. title_id;

Select *
From title_avail
Where pub_date> to_date ('1970-01-01 ', 'yyyy-mm-dd ')
Order by title_id;

8) create a non-unique index with the member ID on the Book Borrowing table

Create index bow_mem_idx
On Borrow (member_id );

9) update the inventory book backup table and add 10 inventory items named XXX to the original inventory.

Update title_copy
Set bak_num = bak_num + 10
Where title_id =
(Select title_id
From Title
Where title = 'World is ping ')

10) modify the table structure, add a price column to the title table, and record the purchase price of the book. This column should have eight digits and two decimal places to verify your modification.

Alter table title
Add (price number (8, 2 ));

Desc title

11) add price for each book

Update title set price = 50 where title_id = 1;

12) change the price field in the title table to a non-empty value. Verify this constraint.

Alter table title modify (price not null );

Select * From user_constraints;
Or:
Select constraint_name, constraint_type, search_condition
From user_constraints
Where table_name = 'title ';

13) query the customer's rental information, including the customer ID, customer name, title, book price, borrow date, return date, and expiration date.
Select M. member_id, M. m_name, T. Title, T. Price, B. borrow_date, B. return_date, B. exp_date
From member M, title T, borrow B
Where M. member_id = B. member_id
And T. title_id = B. title_id;
14) delete data from each table

Delete from borrow;
Delete from reservation;
Delete from title_copy;
Delete from title;
Delete from member;

15) Delete the primary key constraint on the member table and delete any constraint on which it depends.

Alter table Member
Drop primary key cascade;