oracle¿¼ÊԲο¼×ÊÁÏ

INSERT INTO consume VALUES('C001','H001',to_date('2015-05-13','yyyy-mm-dd'),'°¢Ë¾Æ¥ÁÖ',2,5.2);

INSERT INTO insurance VALUES(to_date('2015-05-12','yyyy-mm-dd'),'C001','10','B001');

--4.(2)

CREATE TABLE staff_sql2 ( sno CHAR(5) PRIMARY KEY, sname CHAR(20) NOT NULL,

ssex CHAR(2) CHECK(ssex IN('ÄÐ','Å®')), sbirthday date, sadress CHAR(20),

stel CHAR(15) UNIQUE NOT NULL, cno CHAR(15), bno CHAR(10),

CONSTRAINT staff_sql2_card FOREIGN KEY (cno) REFERENCES card (cno),

CONSTRAINT staff_sql2_business FOREIGN KEY (bno) REFERENCES business (bno) );

INSERT INTO staff_sql2 VALUES('S0001','Àîºê','ÄÐ',to_date('1994-03-08','yyyy-mm-dd'),'37','8208208820','C001','B001');

INSERT INTO staff_sql2 VALUES('S0002','ÍõÃ÷','ÄÐ',to_date('1994-03-09','yyyy-mm-dd'),'38','8208208821','C001','B001');

INSERT INTO staff_sql2 VALUES('S0003','ÕÅÀö','ÄÐ',to_date('1994-03-10','yyyy-mm-dd'),'39','8208208822','C001','B001');

INSERT INTO staff_sql2 VALUES('S0004','Ǯǿ','ÄÐ',to_date('1994-03-11','yyyy-mm-dd'),'40','8208208823','C001','B001');

¶°¶°¶°¶°

INSERT INTO staff_sql2 VALUES('S0005','ËïÎå','ÄÐ',to_date('1994-03-12','yyyy-mm-dd'),'41¶°','8208208824','C001','B001'); SELECT * FROM staff_sql2;

--4.(3)

CREATE TABLE staff_sql3 AS SELECT * FROM staff_sql2; SELECT * FROM staff_sql3; --4.(4)

CREATE TABLE staff_sql4 AS SELECT * FROM staff_sql3 WHERE SNO = 'S0001'; SELECT * FROM staff_sql4; --5.

update staff set bno = (select bno from staff where sname = 'Õų¬') where sname = 'À'; --6.(1)

select * from business; select * from hospital; select * from card; select * from staff; select * from see;

select * from consume;

select * from insurance; --6.(2)

selectsname, sbirthday from staff; --6.(3)

select distinct * from business b where b.bno in (select bno from staff); --6.(4)

select sname, ssex, sbirthday from staff where sadress = '»Æɽ·' and ssex = 'Å®'; --6.(5)

select sno, sname, ssex from staff where sname like 'Àî%'; --6.(6)

select * from staff order by sbirthdaydesc; --6.(7)

select * from staff order by bno ASC, sbirthdaydesc; --6.(8)

select count(*) from staff group by bno; --6.(9)

selectbno, count(*) from staff group by bno having count(*) > 1; --6.(10)

select sno as \Ô±¹¤±àºÅ\ÐÕÃû\--6.(12)

selectb.bno, b.bname, s.sno, s.sname from business b, staff s where b.bno = s.bno; --6.(13)

selectb.bno, b.bname, co.MNAME, co.MNUM from business b, staff s, see se, consume co where s.bno = b.bno and se.sno = s.sno and se.hno = co.hno; --6.(14)

select sno, sname from staff where bno = (select bno from staff where sname = 'À') and sname != 'À'; --7.Ð޸ıíÊý¾Ý

--(1)delete from business where bno = 'b01'; --(2)delete from HOSPITAL where hno = 'h01'; --(3)drop table staff_sql2; --(4)delete from consume;

--(5)delete from see where sno = 's01'; --(6)rollback --8.ɾ³ý±í½á¹¹

--(1)drop table staff_sql3; --(2)drop table staff_sql2; ------------------------------------- delete from BUSINESS; delete from hospital; delete from card; delete from staff; delete from see;

delete from consume;

delete from insurance; ------------------------------------ select * from BUSINESS; select * from hospital; select * from card; select * from staff; select * from see;

select * from consume; select * from insurance;

--oracleʵÑéÈý¹ÜÀíË÷ÒýºÍÊÓͼ --1.´´½¨Ë÷Òý --(1)

create index hospital_name_index on hospital (hnamedesc) tablespace ts_130202021038; select * from user_indexes where index_name = 'HOSPITAL_NAME_INDEX'; --select index_name from user_indexes where table_name='HOSPITAL'; --(2)

create index staff_into_index on staff (snameasc, ssexdesc, sbirthdaydesc)tablespace ts_130202021038;

select * from user_indexes where index_name = 'STAFF_INTO_INDEX'; --select index_name from user_indexes where table_name = 'STAFF'; --2.²é¿´Ë÷Òý

--(2)select * from USER_INDEXES ;

--(3)select * from DBA_INDEXES where index_name = staff_into_index;

--3.ɾ³ýË÷Òý

--(1)drop index hospital_name_index; --(2)drop index staff_into_index; --4.´´½¨ÊÓͼ

--(1)create view ygbx_card_view as

select card.cno, staff.bno

from card, staff, business where card.cno = staff.cno and staff.bno = business.bno;

select * from ygbx_card_view; --(2)

create view consume_view as

selectcard.cno , staff.sno

from card, staff, see, consume where consume.cno = staff.cno and see.sno = staff.sno and consume.cno = card.cno;

select * from consume_view ;

--(3)

create view insurnce_view as

selecti.idate, i.imoney, i.bno, c.ctype, c.cmoney from insurance i, card c where c.cno = i.cno;

select * from insurnce_view; --(4)

create view business_view as

select * from business;

--5.²é¿´ÊÓͼ

--(2)select view_name, text, read_only from user_view where view_name = 'consume_view'; --(3)select view_name, text, read_only from user_view where view_name = 'card_view';

--(4)select view_name, text, read_only from user_view where view_name = 'insurance_view'; --6.ÊÓͼÊý¾ÝµÄ¸üÐÂ

--(1)insert into business_view (bno, bname, btype, badderess, btel) values ('B1997010287', '¸ñÁÖÖÆÒ©', 'ÆóÒµ', 'ѼÂ̽­½Ö98ºÅ', '84692315');

--(2)select view_name, text, read_only from user_view where view_name = 'business_view'; --7.ɾ³ýÊÓͼ

--(1)drop view business_view; --(2)drop view card_view; --(3)drop view consume_view; --(4)drop view insurnce_view;

---oracleʵÑéËÄ´´½¨Í¬Òå´Ê --1.´´½¨Í¬Òå´Ê

--(1)create public synonym qyb for business; --(2)create public synonym ybk for card; --2.²éѯͬÒå´Ê

--(1)select * from business; select * from qyb; --(2)select * from card; select * from ybk; --3.ɾ³ýͬÒå´Ê

--(1)drop public synonym qyb; --(2)drop public synonym ybk; --4.´´½¨ÐòÁÐ --(1)

create sequence ygbx_seql increment by 1 minvalue 60