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

2、為C5的學員姓名和所屬單位；5)查詢選修課程超過 5門的學員學號和所屬單位；解答： 1) select count(SN),avg(SA) from S where SD=技術一部; 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字段的外鍵引用），拼寫出相應的sql語句（oracle語法）。（15分）學生表：students_t id name sex 001趙學生 Male 002錢學生 Male 003孫學生 Male 004李學生 Female 005周學生 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ù)學 3 002 003英語 4 003 004物理 3 004 005化學 2 005 006政治 1 001 007生物 1 005 008計算機 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)計每個學生選修的學分，并按學分降序排序2)統(tǒng)計每個學生選修的所有課程和對應的任課

5、老師；并按學生 Id和課程 Id排序3)統(tǒng)計所有學生、所有課程和所有任課老師的對應關系；并按學生 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.假設有以下的兩個表： Cus_A ID* Name Address Cus_B ID* Name Address *主鍵表 Cus_A 和表 Cus_B的結構完全相同，表 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、已有“成績”如下表所示：學號課程號分數(shù) S1 C1 80 S1 C2 75 S2 C1 null S2 C2 55 S3 C3 901）執(zhí)行 SQL語句： Select Count（學號）From成績 Where 分數(shù)60后的結果是什么？2）請寫出SQL語句來進行查詢“成績”表中

9、學號為S1、課程號為 C2的學號和分數(shù)解答：1）統(tǒng)計分數(shù)超過 60的學生總數(shù)。2）select 學號，分數(shù) from 成績 where 學號=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選擇出用戶名和口令完全相同的記錄（應包括用

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、某公司的機構結構為樹型

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

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

15、式。18、有一個數(shù)據(jù)表userinfo，包含userid，username 字段，其中userid是唯一的，username可能重復，請寫一句 sql查詢語句，把重復的記錄全部取出來。 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)表中的記錄備下面相關題目使用Department表中嵌入記錄部門號部門名稱 A001人力資源部 A002財務部 Employee表中嵌入記錄員工號姓名部門號成績 001張三 A001 90 002李四 A001 90 003王五 A001 80 004張

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

18、門分數(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,財務部);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,關羽,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=關羽;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準考證號科目成績 no subject

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

25、。1).查詢每一門課程的前兩名2).查詢以Grade降序排列的第 31至40條記錄(不需要區(qū)分課程)3).查詢表中存在課程重復 4次以上的記錄,顯示課程和重復的次數(shù),并且按照重復次數(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è)務培訓信息，建立 3個表: S (SID,SN,SD,SA) SID,SN,SD,SA 分別代表學號、學員姓名、所屬單位、學員年齡C (CID,CN ) CID,CN 分別代表課程編號、課程名稱SC ( SID,CID,G ) SID,CID,G 分別代表學號、所選修的課程編號、學習成績1. 使用標準 SQL嵌套語句查詢選修課程名稱為稅收基礎的學員學號和姓名2. 使用標準 SQL嵌套語句查詢選修課程編號為02的學員姓名和所屬

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

34、會議室預定信息。要求采用SQL語句及JAVA代碼段判斷在2003-3-10下午3：004:00 3號會議室是否空閑。請寫出有關 SQL語句以及相關 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、有三張表，學生表 S，課程表 C，學生課程表 SC，學生可以選修多門課程，一門課程可能被多個學生選修，通過 SC表關聯(lián)。1)寫出建表以及插入語句；2)寫出 SQL語句，查詢選修了所有選修課程的學生；3)寫出 SQL語句，查詢選修了至少 2門以上的課程的學生。答：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