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

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

2、為C5的學(xué)員姓名和所屬單位；5)查詢選修課程超過 5門的學(xué)員學(xué)號和所屬單位；解答： 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.請根據(jù)以下四張表（其中course_t表的 teacher_id字段是teacher_t表的 id字段的外鍵引用），拼寫出相應(yīng)的sql語句（oracle語法）。（15分）學(xué)生表：students_t id name sex 001趙學(xué)生 Male 002錢學(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語文 3 001 002數(shù)學(xué) 3 002 003英語 4 003 004物理 3 004 005化學(xué) 2 005 006政治 1 001 007生物 1 005 008計算機(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)計每個學(xué)生選修的學(xué)分，并按學(xué)分降序排序2)統(tǒng)計每個學(xué)生選修的所有課程和對應(yīng)的任課

5、老師；并按學(xué)生 Id和課程 Id排序3)統(tǒng)計所有學(xué)生、所有課程和所有任課老師的對應(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è)有以下的兩個表： 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的記錄，請寫出完成這一功能的存儲過程。解答： 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、已有“成績”如下表所示：學(xué)號課程號分?jǐn)?shù) S1 C1 80 S1 C2 75 S2 C1 null S2 C2 55 S3 C3 901）執(zhí)行 SQL語句： Select Count（學(xué)號）From成績 Where 分?jǐn)?shù)60后的結(jié)果是什么？2）請寫出SQL語句來進(jìn)行查詢“成績”表中

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

10、戶名和數(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，請用SQL語言選擇出 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,表中包含如下字段字段名稱數(shù)據(jù)類型要求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)為樹型

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

13、數(shù)據(jù)庫存儲如下數(shù)據(jù)：用戶名，發(fā)帖標(biāo)題，發(fā)帖內(nèi)容，回復(fù)標(biāo)題，回復(fù)內(nèi)容。每天論壇訪問量 200萬左右，更新帖子 10萬左右。請給出數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計，并結(jié)合范式簡要說明設(shè)計思路。答：用戶表：存儲用戶信息；用戶所發(fā)的帖子表：存儲用戶所發(fā)的帖子；回復(fù)表：存儲對帖子所做的回復(fù)。設(shè)計：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īng)用所要存儲的數(shù)據(jù)量比較大，所以為了避免數(shù)據(jù)的冗余，表的設(shè)計依托于第三范

15、式。18、有一個數(shù)據(jù)表userinfo，包含userid，username 字段，其中userid是唯一的，username可能重復(fù)，請寫一句 sql查詢語句，把重復(fù)的記錄全部取出來。 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、部門字段名中文名稱類型長度備注 depid部門號變長字符 10主鍵 depname部門名稱變長字符 depcj部門平均成績浮點型保留 2位小數(shù)表 Employee人員表字段名中文名稱類型長度備注 empid員工號變長字符 10主鍵 name姓名變長字符 10 depid部門號變長字符 10 Cj成績浮點型保留 2位小數(shù) xorder名次整型實現(xiàn)表中的記錄備下面相關(guān)題目使用Department表中嵌入記錄部門號部門名稱 A001人力資源部 A002財務(wù)部 Employee表中嵌入記錄員工號姓名部門號成績 001張三 A001 90 002李四 A001 90 003王五 A001 80 004張

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

18、門分?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,財務(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)考證號科目成績 no subject

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

25、。1).查詢每一門課程的前兩名2).查詢以Grade降序排列的第 31至40條記錄(不需要區(qū)分課程)3).查詢表中存在課程重復(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 部門表 b員工表 a表字段( id -部門編號 departmentName-部門名稱 ) b表字段( id-部門編號 employee-員工名稱 ) 問題:如何一條sql語句查詢出每個部門共有多少人答：建表語句： create table a( id number primary key, departmentName varchar(20) ); create table b( id number

27、, employee varchar(20) );insert into a values(1,部門1);insert into a values(2,部門2);insert into a values(3,部門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個表: S (SID,SN,SD,SA) SID,SN,SD,SA 分別代表學(xué)號、學(xué)員姓名、所屬單位、學(xué)員年齡C (CID,CN ) CID,CN 分別代表課程編號、課程名稱SC ( SID,CID,G ) SID,CID,G 分別代表學(xué)號、所選修的課程編號、學(xué)習(xí)成績1. 使用標(biāo)準(zhǔn) SQL嵌套語句查詢選修課程名稱為稅收基礎(chǔ)的學(xué)員學(xué)號和姓名2. 使用標(biāo)準(zhǔn) SQL嵌套語句查詢選修課程編號為02的學(xué)員姓名和所屬

29、單位3. 使用標(biāo)準(zhǔn) SQL嵌套語句查詢不選修課程編號為03的學(xué)員姓名和所屬單位4. 使用標(biāo)準(zhǔn) SQL嵌套語句查詢選修全部課程的學(xué)員姓名和所屬單位5. 查詢選修課程超過5門的學(xué)員學(xué)號和所屬單位答：建表sql語句: 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、請根據(jù)以下要求來完成題目：會議室預(yù)定模塊:某公司有多個會議室，以房間號區(qū)分。如果某部門需要預(yù)定會議室，則會提交預(yù)定請求（包含預(yù)定開始使用時間、預(yù)定結(jié)束使用，所預(yù)定會議室房間號）。設(shè)計一個表，保存

34、會議室預(yù)定信息。要求采用SQL語句及JAVA代碼段判斷在2003-3-10下午3：004:00 3號會議室是否空閑。請寫出有關(guān) SQL語句以及相關(guān) JAVA的代碼段。答：1)Sql語句： 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、下面是兩個數(shù)據(jù)庫表，分別記錄員工姓名和工資 T_EMPLOYEE ID NAME 2張三 3李四 5王五 T_SALARY ID SALARY 2 3400 3 4300 5 2500 1.查詢表 T_EMPLOYEE 中 id = 3的員工記錄2.查詢表 T_EMPLOYEE 中所有員工記錄3.聯(liá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é)生可以選修多門課程，一門課程可能被多個學(xué)生選修，通過 SC表關(guān)聯(lián)。1)寫出建表以及插入語句；2)寫出 SQL語句，查詢選修了所有選修課程的學(xué)生；3)寫出 SQL語句，查詢選修了至少 2門以上的課程的學(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