數(shù)據(jù)庫(kù)面試題(4)

1、 數(shù)據(jù)庫(kù)面試題四數(shù)據(jù)庫(kù)寫(xiě) SQL題（ 30）1.按要求寫(xiě) SQL語(yǔ)句：根據(jù)集團(tuán)成員培訓(xùn)業(yè)務(wù)，建立以下三張表：S (S#,SN,SD,SA) S#,SN,SD,SA 分別代表學(xué)號(hào)、學(xué)員姓名、所屬單位、學(xué)員年齡C (C#,CN ) C#,CN 分別代表課程編號(hào)、課程名稱(chēng)SC ( S#,C#,G ) S#,C#,G分別代表學(xué)號(hào)、所選修的課程編號(hào)、學(xué)習(xí)成績(jī)要求如下：1)使用標(biāo)準(zhǔn) SQL語(yǔ)句查詢(xún)成員名單中所屬單位叫“技術(shù)一部”的人員總數(shù)及平均年齡；2)使用標(biāo)準(zhǔn)的 SQL語(yǔ)句更新學(xué)號(hào)為S#1的姓名為“Mike”；3)使用嵌套語(yǔ)句查詢(xún)選修課程編號(hào)為C2的學(xué)員姓名和所屬單位；4)使用嵌套語(yǔ)句查詢(xún)不選修課程編號(hào)

2、為C5的學(xué)員姓名和所屬單位；5)查詢(xún)選修課程超過(guò) 5門(mén)的學(xué)員學(xué)號(hào)和所屬單位；解答： 1) select count(SN),avg(SA) from S where SD=技術(shù)一部; 2) update S set SN=Mike where S#=S#1; 3) select SN,SD from S where S#=(select S# from SC where C#=C2); 4) select SN,SD from S where S# not in(select S# from SC where C#=C5); 5) select S#,SD from S where S#= (

3、select S# from SC group by S# having count(S#)=5);2.請(qǐng)根據(jù)以下四張表（其中course_t表的 teacher_id字段是teacher_t表的 id字段的外鍵引用），拼寫(xiě)出相應(yīng)的sql語(yǔ)句（oracle語(yǔ)法）。（15分）學(xué)生表：students_t id name sex 001趙學(xué)生 Male 002錢(qián)學(xué)生 Male 003孫學(xué)生 Male 004李學(xué)生 Female 005周學(xué)生 Female 教師表：teacher_t id name sex 001吳老師 Male 002鄭老師 Male 003王老師 Male 004劉老師 Fem

4、ale 005張老師 Female課程表：course_t id name credit teacher_id 001語(yǔ)文 3 001 002數(shù)學(xué) 3 002 003英語(yǔ) 4 003 004物理 3 004 005化學(xué) 2 005 006政治 1 001 007生物 1 005 008計(jì)算機(jī) 2 005選課表：student_course_t id student_id course_id 001 001 001 002 001 002 003 001 003 004 002 001 005 002 007 1)統(tǒng)計(jì)每個(gè)學(xué)生選修的學(xué)分，并按學(xué)分降序排序2)統(tǒng)計(jì)每個(gè)學(xué)生選修的所有課程和對(duì)應(yīng)的任課

5、老師；并按學(xué)生 Id和課程 Id排序3)統(tǒng)計(jì)所有學(xué)生、所有課程和所有任課老師的對(duì)應(yīng)關(guān)系；并按學(xué)生 Id和課程 Id排序解答：1）select sc.student_id,count(c.credit) from students_t s, course_t c, student_course_t sc where s.id=sc.student_id and c.id=sc.course_id group by sc.student_id order by count(c.credit); 2) select as s_name, as c_name ,

6、as t_name from students_t s, course_t c, student_course_t sc,teacher_t t where s.id=sc.student_id and c.id=sc.course_id and t.id=c.teacher_id order by s.id,c.id;3)與 2）相同3.假設(shè)有以下的兩個(gè)表： Cus_A ID* Name Address Cus_B ID* Name Address *主鍵表 Cus_A 和表 Cus_B的結(jié)構(gòu)完全相同，表 Cus_A 和表 Cus_B中既存在 ID相同的記錄，也存在 ID不同的記錄。現(xiàn)要求將

7、ID只存在于表表Cus_A 中而不存在于表Cus_B中的記錄全部插入到Cus_B表中，并用表 Cus_A中的記錄更新 Cus_B中相同的 ID的記錄，請(qǐng)寫(xiě)出完成這一功能的存儲(chǔ)過(guò)程。解答： create or replace procedure test is cust_record cus_a%rowtype ; cursor cust_cursor is select id,name,address from cus_a; Begin Open cust_cursor; LOOP Fetch cust_cursor into cust_record; EXIT WHEN cust_curso

8、r %NOTFOUND;-先刪除在插入 delete from cus_b where id=cust_record.id; insert into cus_b values(cust_record.id, cust_, cust_record.address); END LOOP; end;4、已有“成績(jī)”如下表所示：學(xué)號(hào)課程號(hào)分?jǐn)?shù) S1 C1 80 S1 C2 75 S2 C1 null S2 C2 55 S3 C3 901）執(zhí)行 SQL語(yǔ)句： Select Count（學(xué)號(hào)）From成績(jī) Where 分?jǐn)?shù)60后的結(jié)果是什么？2）請(qǐng)寫(xiě)出SQL語(yǔ)句來(lái)進(jìn)行查詢(xún)“成績(jī)”表中

9、學(xué)號(hào)為S1、課程號(hào)為 C2的學(xué)號(hào)和分?jǐn)?shù)解答：1）統(tǒng)計(jì)分?jǐn)?shù)超過(guò) 60的學(xué)生總數(shù)。2）select 學(xué)號(hào)，分?jǐn)?shù) from 成績(jī) where 學(xué)號(hào)=S1 and課程號(hào)=C2;5.SAL是 Product表中的索引列，請(qǐng)優(yōu)化如下 SQL語(yǔ)句，并簡(jiǎn)述原因。原語(yǔ)句： SELECT* FROM ProductWHERE SAL * 1225000；解答：Select * from product where sal(25000/12);理由：WHERE子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不使用索引而使用全表掃描6.有一張表，字段有用戶(hù)名、口令及備注，請(qǐng)用 SQL選擇出用戶(hù)名和口令完全相同的記錄（應(yīng)包括用

10、戶(hù)名和數(shù)量的出現(xiàn)次數(shù)） T_USER(USER_NAME,PASSWORD)顯示 USER_NAME COUNT(*) QWE 4 WER 5解答：select user_name,count(*) from t_user group by user_name,password;7.有一張表，T_MONEY，字段有 ID，F(xiàn)EE，請(qǐng)用SQL語(yǔ)言選擇出 FEE值為前三條記錄。T_MONEY(ID，F(xiàn)EE)顯示 ID FEE 2 100 1 90 2 80 Select Id,fee from (Select id,fee from t_money order by fee desc) where

11、 rownum2;3) delete from applydetail where name=李%;15. 在 system 方案中建立表 table1,表中包含如下字段字段名稱(chēng)數(shù)據(jù)類(lèi)型要求name Varchar2 非空id Number 非空 age Number sex Varchar2 salary Number 解答： Create table system.tablel1 ( Id number not null, Name varchar(8) not null, Age number, Sex varchar(2), Salary number );16、某公司的機(jī)構(gòu)結(jié)構(gòu)為樹(shù)型

12、結(jié)構(gòu)，對(duì)應(yīng)的表結(jié)構(gòu)為 TableCompany(ComCode機(jī)構(gòu)代碼，UpperComCode上級(jí)機(jī)構(gòu)代碼)，如何查詢(xún)出總公司的所有下級(jí)機(jī)構(gòu)？(java或者 SQL均可)。你覺(jué)得這種思維和設(shè)計(jì)是否合理？有什么好建議的？答：select t1.* from TableCompany t1, TableCompany t2 Where t1.ComCode = t2.UpperComCode這種設(shè)計(jì)比較容易讓人理解，但是表中的數(shù)據(jù)聯(lián)系過(guò)于緊密，數(shù)據(jù)量很大，會(huì)給后期維護(hù)造成不便，如果根據(jù)第三范式要求，將每一子公司獨(dú)立成一張表，對(duì)于關(guān)系的維護(hù)和數(shù)據(jù)的管理都會(huì)變得比較方便。17、一個(gè)簡(jiǎn)單的論壇系統(tǒng)，以

13、數(shù)據(jù)庫(kù)存儲(chǔ)如下數(shù)據(jù)：用戶(hù)名，發(fā)帖標(biāo)題，發(fā)帖內(nèi)容，回復(fù)標(biāo)題，回復(fù)內(nèi)容。每天論壇訪問(wèn)量 200萬(wàn)左右，更新帖子 10萬(wàn)左右。請(qǐng)給出數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)，并結(jié)合范式簡(jiǎn)要說(shuō)明設(shè)計(jì)思路。答：用戶(hù)表：存儲(chǔ)用戶(hù)信息；用戶(hù)所發(fā)的帖子表：存儲(chǔ)用戶(hù)所發(fā)的帖子；回復(fù)表：存儲(chǔ)對(duì)帖子所做的回復(fù)。設(shè)計(jì)：User： Create table tb_user( id number(10) primary key, Uname varchar2(20) not null unique ); Comments: Create table tb_comments( id number(10), comments_id number(2

14、0) not null unique, title varchar2(20) not null, comments varchar2(255) not null, foreign key(id) references tb_user(id) ); Replay: Create table tb_replay( id number(10), comments varchar2(255) not null, foreign key(id) references tb_comments(comments_id) );思路：因?yàn)榇藨?yīng)用所要存儲(chǔ)的數(shù)據(jù)量比較大，所以為了避免數(shù)據(jù)的冗余，表的設(shè)計(jì)依托于第三范

15、式。18、有一個(gè)數(shù)據(jù)表userinfo，包含userid，username 字段，其中userid是唯一的，username可能重復(fù)，請(qǐng)寫(xiě)一句 sql查詢(xún)語(yǔ)句，把重復(fù)的記錄全部取出來(lái)。 userid username 1老王 2老王 3老李4老李 5小張要求返回記錄集 userid username 1老王 2老王 3老李 4老李答： select * from userinfo where username in (select username from userinfo group by username having count(username)1);19、建表 Department

16、部門(mén)字段名中文名稱(chēng)類(lèi)型長(zhǎng)度備注 depid部門(mén)號(hào)變長(zhǎng)字符 10主鍵 depname部門(mén)名稱(chēng)變長(zhǎng)字符 depcj部門(mén)平均成績(jī)浮點(diǎn)型保留 2位小數(shù)表 Employee人員表字段名中文名稱(chēng)類(lèi)型長(zhǎng)度備注 empid員工號(hào)變長(zhǎng)字符 10主鍵 name姓名變長(zhǎng)字符 10 depid部門(mén)號(hào)變長(zhǎng)字符 10 Cj成績(jī)浮點(diǎn)型保留 2位小數(shù) xorder名次整型實(shí)現(xiàn)表中的記錄備下面相關(guān)題目使用Department表中嵌入記錄部門(mén)號(hào)部門(mén)名稱(chēng) A001人力資源部 A002財(cái)務(wù)部 Employee表中嵌入記錄員工號(hào)姓名部門(mén)號(hào)成績(jī) 001張三 A001 90 002李四 A001 90 003王五 A001 80 004張

17、飛 A002 70 005劉備 A002 60 006關(guān)羽 A002 501）寫(xiě)出建表以及嵌入記錄語(yǔ)句2）顯示 A001部門(mén)員工的姓名、成績(jī)3）顯示所有員工的員工號(hào)、姓名、部門(mén)名稱(chēng)、成績(jī)4）將關(guān)羽的成績(jī)修改成52分5）按要求寫(xiě)視圖 VdepEmpMax求各部門(mén)的最高分，顯示部門(mén)號(hào)、最高分成績(jī)6）按要求寫(xiě)存儲(chǔ)過(guò)程 SP_Calc求各部門(mén)的平均成績(jī)，并更新到 Department表 depcj字段中 7）按要求寫(xiě)存儲(chǔ)過(guò)程 SP_Order求員工的名次，并更新到 Employee表 xorder字段中8）按要求寫(xiě)視圖 VdepEmp2，求各部門(mén)的前 2名，顯示部門(mén)號(hào)、員工號(hào)、成績(jī)排序規(guī)則如下：?jiǎn)T工部

18、門(mén)分?jǐn)?shù)名次張三 A001 90 1李四 A001 90 1張飛 A002 70 1劉備 A002 60 2答：1） create table Department(depid varchar2(20) primary key, depname varchar2(20), depcj number(10,2); create table Employee(empid varchar2(20) primary key, name varchar2(20), depid varchar2(20), cj number(10,2), xorder number(10);insert into Depa

19、rtment(depid,depname) values(A001,人力資源部);insert into Department(depid,depname) values(A002,財(cái)務(wù)部);insert into Employee(empid, name, depid, cj) values(001,張三,A001,90);insert into Employee(empid, name, depid, cj) values(002,李四,A001,90);insert into Employee(empid, name, depid, cj) values(003,王五,A001,80);

20、insert into Employee(empid, name, depid, cj) values(004,張飛,A002,70);insert into Employee(empid, name, depid, cj) values(005,劉備,A002,60);insert into Employee(empid, name, depid, cj) values(006,關(guān)羽,A002,50);2）select name,cj from employee where depid=A001;3）select e.empid,,d.depname,e.cj from empl

21、oyee e, departmentd where e.depid=d.depid;4）update employee set cj=52 where name=關(guān)羽;5） create view VdepEmpMax as (select deptid,max(cj) from employee e group by deptid)6） create or replace procedure SP_Calc as begin update department d set depcj=( select nvl(avg(cj),0) from employee e where e.depid(

22、+)=d.depid); end;7） create or replace procedure SP_Order as begin update employee w set xorder =(select b.rn from (select empid,rank() over (partition by depid order by cj desc ) rn from employee) b where w.empid=b.empid); end;8） create or replace view VdepEmp2 as select depid,name,cj,rn from (selec

23、t e.*,rank() over (partition by depid order by cj desc) rn from employee e) where rn20;3）delete from students where class=0201;4）select count(s_name) from students where s_name like 李% and class=0302;5）update students set assistant=李四 where class like 02%;21、表名：高考信息表 students_info準(zhǔn)考證號(hào)科目成績(jī) no subject

24、 score 2006001 語(yǔ)文 119 2006001 數(shù)學(xué) 108 2006002 物理 142 2006001 化學(xué) 136 2006001 物理 127 2006002 數(shù)學(xué) 149 2006002 英語(yǔ) 110 2006002 語(yǔ)文 105 2006001 英語(yǔ) 98 2006002 化學(xué) 129寫(xiě)出高考總分在 600以上的學(xué)生準(zhǔn)考證號(hào)的 SQL答： select no from students_info group by no having sum(score)600；22、有一個(gè)表 LEANR，表里有三個(gè)字段分別是學(xué)號(hào)（student_id）,課程（kc）,成績(jī)（grade）

25、。1).查詢(xún)每一門(mén)課程的前兩名2).查詢(xún)以Grade降序排列的第 31至40條記錄(不需要區(qū)分課程)3).查詢(xún)表中存在課程重復(fù) 4次以上的記錄,顯示課程和重復(fù)的次數(shù),并且按照重復(fù)次數(shù)的降序排列答： 1).select student_id,kc,grade from (select student_id,kc,grade, row_number() over(partition by kc order by grade desc)rn from LEANR) where rn=2; 2)select student_id,grade from ( select lea.*,rownum rm

26、from ( select * from LEANR order by grade desc ) lea where rownum =2 order by count(kc) desc;23、a 部門(mén)表 b員工表 a表字段( id -部門(mén)編號(hào) departmentName-部門(mén)名稱(chēng) ) b表字段( id-部門(mén)編號(hào) employee-員工名稱(chēng) ) 問(wèn)題:如何一條sql語(yǔ)句查詢(xún)出每個(gè)部門(mén)共有多少人答：建表語(yǔ)句： create table a( id number primary key, departmentName varchar(20) ); create table b( id number

27、, employee varchar(20) );insert into a values(1,部門(mén)1);insert into a values(2,部門(mén)2);insert into a values(3,部門(mén)3); insert into b values(1,emp1); insert into b values(1,emp2); insert into b values(1,emp3); insert into b values(2,emp4); insert into b values(2,emp5); insert into b values(3,emp6); select dep

28、artmentName,count(employee) from a,b where a.id=b.id group by departmentName;24、為管理崗位業(yè)務(wù)培訓(xùn)信息，建立 3個(gè)表: S (SID,SN,SD,SA) SID,SN,SD,SA 分別代表學(xué)號(hào)、學(xué)員姓名、所屬單位、學(xué)員年齡C (CID,CN ) CID,CN 分別代表課程編號(hào)、課程名稱(chēng)SC ( SID,CID,G ) SID,CID,G 分別代表學(xué)號(hào)、所選修的課程編號(hào)、學(xué)習(xí)成績(jī)1. 使用標(biāo)準(zhǔn) SQL嵌套語(yǔ)句查詢(xún)選修課程名稱(chēng)為稅收基礎(chǔ)的學(xué)員學(xué)號(hào)和姓名2. 使用標(biāo)準(zhǔn) SQL嵌套語(yǔ)句查詢(xún)選修課程編號(hào)為02的學(xué)員姓名和所屬

29、單位3. 使用標(biāo)準(zhǔn) SQL嵌套語(yǔ)句查詢(xún)不選修課程編號(hào)為03的學(xué)員姓名和所屬單位4. 使用標(biāo)準(zhǔn) SQL嵌套語(yǔ)句查詢(xún)選修全部課程的學(xué)員姓名和所屬單位5. 查詢(xún)選修課程超過(guò)5門(mén)的學(xué)員學(xué)號(hào)和所屬單位答：建表sql語(yǔ)句: create table s( sid int(10) primary key, sn varchar(20) not null, sd varchar(20) not null, sa int(3) not null ); create table c( cid int(10) primary key, cn varchar(20) not null ); create table

30、sc( sid int(10) references s(sid), cid int(10) references c(cid), g int(10), primary key(sid,cid) ); insert into s values(1,zhangsan,project,25); insert into s values(2,lisi,mis,26); insert into s values(3,wangwu,manager,27); insert into s values(4,zhaoliu,mis,26);insert into c values(01,稅收基礎(chǔ)); inse

31、rt into c values(02,Core Java); insert into c values(03,NetWork); insert into sc values(1,01,70); insert into sc values(1,02,75); insert into sc values(1,03,80); insert into sc values(2,01,80); insert into sc values(2,03,69); insert into sc values(3,02,73);1） select s.sid,s.sn from s,c,sc where s.si

32、d=sc.sid and c.cid=sc.cidand =稅收基礎(chǔ); 2) select a.sn,a.sd from s a, c b where b.cid in(select c.cid from sc c where a.sid=c.sid and b.cid=c.cid) and b.cid=02; 3) select a.sn,a.sd from s a, c b where b.cid not in(select c.cid from sc c where a.sid=c.sid and b.cid=c.cid) and b.cid=03; 4) select sn

33、,sd from s where sid in (select sid from sc group by sid having count(cid)=(select count(cid) from c); 5) select sn,sd from s where sid in(select sid from sc group by sid having count(distinct cid)5);25、請(qǐng)根據(jù)以下要求來(lái)完成題目：會(huì)議室預(yù)定模塊:某公司有多個(gè)會(huì)議室，以房間號(hào)區(qū)分。如果某部門(mén)需要預(yù)定會(huì)議室，則會(huì)提交預(yù)定請(qǐng)求（包含預(yù)定開(kāi)始使用時(shí)間、預(yù)定結(jié)束使用，所預(yù)定會(huì)議室房間號(hào)）。設(shè)計(jì)一個(gè)表，保存

34、會(huì)議室預(yù)定信息。要求采用SQL語(yǔ)句及JAVA代碼段判斷在2003-3-10下午3：004:00 3號(hào)會(huì)議室是否空閑。請(qǐng)寫(xiě)出有關(guān) SQL語(yǔ)句以及相關(guān) JAVA的代碼段。答：1)Sql語(yǔ)句： create table meeting( id number primary key , room_id varchar(10), isUsed char, begin timestamp, end timestamp ); insert into meeting values(1,201,1,to_date(2003-03-10 15:00:00,yyyy-mm-dd hh24:mi:ss) ,to_da

35、te(2003-03-10 16:00:00,yyyy-mm-dd hh24:mi:ss); insert into meeting values(2,201,1,to_date(2003-03-10 17:00:00,yyyy-mm-dd hh24:mi:ss) ,to_date(2003-03-10 22:00:00,yyyy-mm-dd hh24:mi:ss); 2) package com.tarena; import java.sql.*; public class Test public static void main(String args) String driverName

36、 = oracle.jdbc.OracleDriver; String url = jdbc:oracle:thin::1521:orcl; String username = scott; String pwd = tiger; Connection con = null; Statement stmt = null; ResultSet rs = null; try Class.forName(driverName); con = DriverManager.getConnection(url, username, pwd); stmt = con.createState

37、ment(); String sql = select isUsed from + meeting + where (begin between to_date(2003-03-10 15:00:00,yyyy-mm-dd hh24:mi:ss) and to_date(2003-03-10 16:00:00,yyyy-mm-dd hh24:mi:ss) + or(end between to_date(2003-03-10 15:00:00,yyyy-mm-dd hh24:mi:ss) and to_date(2003-03-10 16:00:00,yyyy-mm-dd hh24:mi:ss

38、) + and room_id=201; if (stmt.execute(sql) rs = stmt.getResultSet(); StringBuffer sb = new StringBuffer(); while (rs.next() sb.append(isFree: + rs.getInt(1) + ); System.out.print(sb.toString(); catch (Exception e) e.printStackTrace(); finally try con.close(); catch (Exception e1) e1.printStackTrace(

39、); 26、下面是兩個(gè)數(shù)據(jù)庫(kù)表，分別記錄員工姓名和工資 T_EMPLOYEE ID NAME 2張三 3李四 5王五 T_SALARY ID SALARY 2 3400 3 4300 5 2500 1.查詢(xún)表 T_EMPLOYEE 中 id = 3的員工記錄2.查詢(xún)表 T_EMPLOYEE 中所有員工記錄3.聯(lián)合查詢(xún)表 T_EMPLOYEE和 T_SALARY 中所有員工的姓名和工資記錄，并按照薪水從高到低排列答： 1).select * from t_employee where id = 3; 2).select * from t_employee; 3).select ,s.

40、salary from t_employee e,t_salary s where e.id=s.id order by s.salary;27、有三張表，學(xué)生表 S，課程表 C，學(xué)生課程表 SC，學(xué)生可以選修多門(mén)課程，一門(mén)課程可能被多個(gè)學(xué)生選修，通過(guò) SC表關(guān)聯(lián)。1)寫(xiě)出建表以及插入語(yǔ)句；2)寫(xiě)出 SQL語(yǔ)句，查詢(xún)選修了所有選修課程的學(xué)生；3)寫(xiě)出 SQL語(yǔ)句，查詢(xún)選修了至少 2門(mén)以上的課程的學(xué)生。答：1） create table student (id number(10) primary key,name varchar2(20); create table course (id nu

41、mber(10) primary key,name varchar2(20); create table sc(sid number(10) references student(id),cid number(10) references course(id),grade number(4,2); insert into student values(1,feifei); insert into student values(2,jingjing); insert into student values(3,nannan); insert into student values(4,yuany

42、uan); insert into student values(5,jiejie); insert into course values(1,corejava); insert into course values(2,c+); insert into course values(3,jdbc); insert into course values(4,hibernate); insert into sc values(1,1,98); insert into sc values(2,1,97); insert into sc values(3,1,94); insert into sc v

43、alues(4,1,92); insert into sc values(5,1,93); insert into sc values(1,2,94); insert into sc values(2,2,92); insert into sc values(3,2,95); insert into sc values(5,2,97); insert into sc values(1,3,92); insert into sc values(2,3,92); insert into sc values(4,3,91); insert into sc values(1,4,99); insert into sc values(3,4,89);2）select sid,count(*) from sc group by sid having count(*)=(select count(*) from course);3）select sid,count(*) from s