Oracle資料庫練習題

題目要求：創建一個用戶，用戶名是自己姓名全拼，密碼是自己的學號，授予相應權限，能夠進行表的創建、資料添加等操作，在新創建用戶下建立以下兩個表，并完成后邊練習：

創建用戶：

create user tset identified by test;

授予權限：

grant create session to test with admin option;
grant create table to test;

連接用戶

conn test/test;

資料庫中有兩個表格的構造如下：

授予權限：

grant select,insert,update,delete on test.CLASS_TBL to test;
grant select,insert,update,delete on test.GOODS_TBL to test;

假定CLASS_TBL和GOODS_TBL資料如下：

插入資料：

insert into CLASS_TBL(C_NO,C_NAME)VALUES('1','白酒');
insert into CLASS_TBL(C_NO,C_NAME)VALUES('2','紅酒');
insert into CLASS_TBL(C_NO,C_NAME)VALUES('3','黃酒');
INSERT INTO GOODS_TBL(GS_NO,GS_NAME,C_NO,GS_PRICE,GS_AMOUNT)VALUES('101','五糧液','1',300,50);
INSERT INTO GOODS_TBL(GS_NO,GS_NAME,C_NO,GS_PRICE,GS_AMOUNT)VALUES('102','貴州茅臺','1',500,40);
INSERT INTO GOODS_TBL(GS_NO,GS_NAME,C_NO,GS_PRICE,GS_AMOUNT)VALUES('103','山西汾酒','1',100,80);
INSERT INTO GOODS_TBL(GS_NO,GS_NAME,C_NO,GS_PRICE,GS_AMOUNT)VALUES('201','王朝干白','2',30,200);
INSERT INTO GOODS_TBL(GS_NO,GS_NAME,C_NO,GS_PRICE,GS_AMOUNT)VALUES('202','法國紅','2',50,100);
INSERT INTO GOODS_TBL(GS_NO,GS_NAME,C_NO,GS_PRICE,GS_AMOUNT)VALUES('203','通化紅喜','2',20,200);
INSERT INTO GOODS_TBL(GS_NO,GS_NAME,C_NO,GS_PRICE,GS_AMOUNT)VALUES('204','北京天壇','2',15,500);

若顯示用戶空間不足：

ALTER USER "test" QUOTA UNLIMITED ON "USERS";

• 寫出建立CLASS_TBL表格的SQL陳述句(不包含PK)，

create table CLASS_TBL(
C_NO CHAR(1) NOT NULL,
C_NAME VARCHAR2(20) NOT NULL
);

為已生成的CLASS_TBL表格追加建立PK，

alter table class_tbl add constraint pk_class_tbl primary key(c_no);

寫出建立GOODS_TBL表格的SQL陳述句（包含PK（主鍵）和FK（參照鍵）），

CREATE TABLE GOODS_TBL(
GS_NO CHAR(3) NOT NULL PRIMARY KEY,
GS_NAME VARCHAR2(20) NOT NULL,
C_NO CHAR(1) NOT NULL REFERENCES CLASS_TBL(C_NO),
GS_PRICE INT,
GS_AMOUNT INT
);

• 請判斷以下SQL陳述句的對錯,錯的給出理由，
  ①SELECT C_NAME FROM CLASS_TBL WHERE C_NO = (SELECT C_NO FROM GOODS_TBL WHERE GS_AMOUNT = 200);

②SELECT GS_NAME FROM GOODS_TBL ORDER BY GS_AMOUNT;

③INSERT INTO GOODS_TBL (GS_NO, C_NO) VALUES (104, 2);

④SELECT GS_NAME FROM GOODS_TBL WHERE GS_PRICE <> NULL;

⑤SELECT GS_NO + GS_NAME FROM GOODS_TBL;

• 寫出一條SELECT陳述句列出單價小于25元的類名為”紅酒”的所有庫存的名稱、單價數量和在庫價值，并按單價降序，

select g.gs_name,g.gs_price,g.gs_amount
from class_tbl c,goods_tbl g
where c.c_no=g.c_no and g.gs_price<25 and c.c_name='紅酒'
order by g.gs_price desc;

• 按C_NO統計各大類的總金額，寫出一條SELECT陳述句，使輸出結果如下：

select c.c_name 種類,a.suma 總金額
from class_tbl c,(select nvl(sum(g.gs_price*g.gs_amount),0) suma ,c.c_no
from goods_tbl g,class_tbl c 
where c.c_no=g.c_no(+)
group by c.c_no) a
where a.c_no=c.c_no;

• 請把庫存表中所有資訊表示出來，同時用以下形式表示
  400以上：庫存積壓,
  100以下：庫存不足
  其他：庫存適中

select gs_no,gs_amount,case
when gs_amount>400 then '庫存積壓'
when gs_amount<100 then '庫存不足'
else '庫存適中'
end gs_cmount
from goods_tbl;

• 查詢GOODS_TBL表中庫存超過100的記錄，按庫存量降序排序,若庫存相同，則按價格排序？

select *
from goods_tbl
where gs_amount>100
order by gs_amount desc,gs_price;

• 寫一PL/SQL程式，查詢GOODS_TBL表中庫存最多的商品的名稱、價格和分類號，

開啟螢屏列印：

set serveroutput on

declare
v_name goods_tbl.gs_name%type;
v_price goods_tbl.gs_price%type;
v_cno goods_tbl.c_no%type;
begin
select g.gs_name,g.gs_price,g.c_no into v_name,v_price,v_cno from goods_tbl g,(select gs_price,c_no,max(GS_AMOUNT) maxamount from GOODS_TBL) b
where g.gs_amount=b.maxamount;
dbms_output.put_line('商品名：'||v_name||'價格：'||v_price||'分類號:'||v_cno);
end;

• 寫一游標，查詢GOODS_TBL表的所有資料，

declare 
 cursor cur1 is select * from goods_tbl;
 rec_cur1 cur1%rowtype;
begin
 open cur1;
 fetch cur1 into rec_cur1;
while cur1%found
 loop
 dbms_output.put_line('商品號：'||rec_cur1.gs_no||'名稱：'||rec_cur1.gs_name||'類別號：'||rec_cur1.c_no||'價格：'||rec_cur1.gs_price||'數量：'||rec_cur1.gs_amount);
 fetch cur1 into rec_cur1;
 end loop;
close cur1;
end;

• 寫一帶有輸入引數的存盤程序，查詢特定GS_NO的資訊，注意包含例外處理，

create or replace procedure pro1(v_gsno in goods_tbl.gs_no%type,v_name out goods_tbl.gs_name%type)
is
begin
select gs_name into v_name from goods_tbl where gs_no=v_gsno;
exception
when no_data_found then
dbms_output.put_line(v_gsno||'不存在');
end;

declare 
v_name1 goods_tbl.gs_name%type;
begin
pro1('101',v_name1);
dbms_output.put_line('商品名是'||v_name1);

• 寫一個DML觸發器，實作class_tbl表的同步備份，

create table classtblcopy as select * from class_tbl;

create or replace trigger tri1
after 
update or insert or delete
on class_tbl
for each row
begin
if updating then
update classtblcopy set c_no=:new.c_no,c_name=:new.c_name where c_no=:old.c_no;
elsif deleting then
delete from classtblcopy where c_no=:old.c_no;
else
insert into classtblcopy values(:new.c_no,:new.c_name);
end if;
end;