- 論壇徽章:
- 0
|
不錯(cuò)的sql面試題
Student(S#,Sname,Sage,Ssex) 學(xué)生表
Course(C#,Cname,T#) 課程表
SC(S#,C#,score) 成績(jī)表
Teacher(T#,Tname) 教師表
問(wèn)題：
1、查詢“001”課程比“002”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;
2、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)；
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4、查詢姓“李”的老師的個(gè)數(shù)；
select count(distinct(Tname))
from Teacher
where Tname like '李%';
5、查詢沒(méi)學(xué)過(guò)“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名；
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平');
6、查詢學(xué)過(guò)“001”并且也學(xué)過(guò)編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名；
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7、查詢學(xué)過(guò)“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名；
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='葉平'));
8、查詢課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名；
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 60);
10、查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) =60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分?jǐn)?shù)
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查詢?nèi)缦抡n程平均成績(jī)和及格率的百分?jǐn)?shù)(用"1行"顯示): 企業(yè)管理（001），馬克思（002），OO&UML （003），數(shù)據(jù)庫(kù)（004）
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企業(yè)管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企業(yè)管理及格百分?jǐn)?shù)
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 馬克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 馬克思及格百分?jǐn)?shù)
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分?jǐn)?shù)
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 數(shù)據(jù)庫(kù)平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 數(shù)據(jù)庫(kù)及格百分?jǐn)?shù)
FROM SC
21、查詢不同老師所教不同課程平均分從高到低顯示
SELECT max(Z.T#) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程ＩＤ,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績(jī)
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
22、查詢?nèi)缦抡n程成績(jī)第 3 名到第 6 名的學(xué)生成績(jī)單：企業(yè)管理（001），馬克思（002），UML （003），數(shù)據(jù)庫(kù)（004）
[學(xué)生ID],[學(xué)生姓名],企業(yè)管理,馬克思,UML,數(shù)據(jù)庫(kù),平均成績(jī)
SELECT DISTINCT top 3
SC.S# As 學(xué)生學(xué)號(hào),
Student.Sname AS 學(xué)生姓名 ,
T1.score AS 企業(yè)管理,
T2.score AS 馬克思,
T3.score AS UML,
T4.score AS 數(shù)據(jù)庫(kù),
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 總分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
23、統(tǒng)計(jì)列印各科成績(jī),各分?jǐn)?shù)段人數(shù):課程ID,課程名稱,[100-85],[85-70],[70-60],[
24、查詢學(xué)生平均成績(jī)及其名次
SELECT 1+(SELECT COUNT( distinct 平均成績(jī))
FROM (SELECT S#,AVG(score) AS 平均成績(jī)
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成績(jī) > T2.平均成績(jī)) as 名次,
S# as 學(xué)生學(xué)號(hào),平均成績(jī)
FROM (SELECT S#,AVG(score) 平均成績(jī)
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成績(jī) desc;
25、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)
SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù)
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
26、查詢每門(mén)課程被選修的學(xué)生數(shù)
select c#,count(S#) from sc group by C#;
27、查詢出只選修了一門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名
select SC.S#,Student.Sname,count(C#) AS 選課數(shù)
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
28、查詢男生、女生人數(shù)
Select count(Ssex) as 男生人數(shù) from Student group by Ssex having Ssex='男';
Select count(Ssex) as 女生人數(shù) from Student group by Ssex having Ssex='女'；
29、查詢姓“張”的學(xué)生名單
SELECT Sname FROM Student WHERE Sname like '張%';
30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
select Sname,count(*) from Student group by Sname having count(*)>1;;
31、1981年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)
select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))='1981';
32、查詢每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列
Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
33、查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
select Sname,SC.S# ,avg(score)
from Student,SC
where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;
34、查詢課程名稱為“數(shù)據(jù)庫(kù)”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
Select Sname,isnull(score,0)
from Student,SC,Course
where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='數(shù)據(jù)庫(kù)'and score =70 AND SC.S#=student.S#;
37、查詢不及格的課程，并按課程號(hào)從大到小排列
select c# from sc where scor e 80 and C#='003';
39、求選了課程的學(xué)生人數(shù)
select count(*) from sc;
40、查詢選修“葉平”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)
select Student.Sname,score
from Student,SC,Course C,Teacher
where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='葉平' and SC.score=(select max(score)from SC where C#=C.C# );
41、查詢各個(gè)課程及相應(yīng)的選修人數(shù)
select count(*) from sc group by C#;
42、查詢不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)
select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# B.C# ;
43、查詢每門(mén)功成績(jī)最好的前兩名
SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù)
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
44、統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò)10人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列
select C# as 課程號(hào),count(*) as 人數(shù)
from sc
group by C#
order by count(*) desc,c#
45、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)
select S#
from sc
group by s#
having count(*) > = 2
46、查詢?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名
select C#,Cname
from Course
where C# in (select c# from sc group by c#)
47、查詢沒(méi)學(xué)過(guò)“葉平”老師講授的任一門(mén)課程的學(xué)生姓名
select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='葉平');
48、查詢兩門(mén)以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score 2)group by S#;
49、檢索“004”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)
select S# from SC where C#='004'and score
問(wèn)題描述：
本題用到下面三個(gè)關(guān)系表：
CARD 借書(shū)卡。 CNO 卡號(hào)，NAME 姓名，CLASS 班級(jí)
BOOKS 圖書(shū)。 BNO 書(shū)號(hào)，BNAME 書(shū)名,AUTHOR 作者，PRICE 單價(jià)，QUANTITY 庫(kù)存冊(cè)數(shù)
BORROW 借書(shū)記錄。 CNO 借書(shū)卡號(hào)，BNO 書(shū)號(hào)，RDATE 還書(shū)日期
備注：限定每人每種書(shū)只能借一本；庫(kù)存冊(cè)數(shù)隨借書(shū)、還書(shū)而改變。
要求實(shí)現(xiàn)如下15個(gè)處理：
1． 寫(xiě)出建立BORROW表的SQL語(yǔ)句，要求定義主碼完整性約束和引用完整性約束。
2． 找出借書(shū)超過(guò)5本的讀者,輸出借書(shū)卡號(hào)及所借圖書(shū)冊(cè)數(shù)。
3． 查詢借閱了"水滸"一書(shū)的讀者，輸出姓名及班級(jí)。
4． 查詢過(guò)期未還圖書(shū)，輸出借閱者（卡號(hào)）、書(shū)號(hào)及還書(shū)日期。
5． 查詢書(shū)名包括"網(wǎng)絡(luò)"關(guān)鍵詞的圖書(shū)，輸出書(shū)號(hào)、書(shū)名、作者。
6． 查詢現(xiàn)有圖書(shū)中價(jià)格最高的圖書(shū)，輸出書(shū)名及作者。
7． 查詢當(dāng)前借了"計(jì)算方法"但沒(méi)有借"計(jì)算方法習(xí)題集"的讀者，輸出其借書(shū)卡號(hào)，并按卡號(hào)降序排序輸出。
8． 將"C01"班同學(xué)所借圖書(shū)的還期都延長(zhǎng)一周。
9． 從BOOKS表中刪除當(dāng)前無(wú)人借閱的圖書(shū)記錄。
10．如果經(jīng)常按書(shū)名查詢圖書(shū)信息，請(qǐng)建立合適的索引。
11．在BORROW表上建立一個(gè)觸發(fā)器，完成如下功能：如果讀者借閱的書(shū)名是"數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用"，就將該讀者的借閱記錄保存在BORROW_SAVE表中（注ORROW_SAVE表結(jié)構(gòu)同BORROW表）。
12．建立一個(gè)視圖，顯示"力01"班學(xué)生的借書(shū)信息（只要求顯示姓名和書(shū)名）。
13．查詢當(dāng)前同時(shí)借有"計(jì)算方法"和"組合數(shù)學(xué)"兩本書(shū)的讀者，輸出其借書(shū)卡號(hào)，并按卡號(hào)升序排序輸出。
14．假定在建BOOKS表時(shí)沒(méi)有定義主碼，寫(xiě)出為BOOKS表追加定義主碼的語(yǔ)句。
15．對(duì)CARD表做如下修改：
a. 將NAME最大列寬增加到10個(gè)字符（假定原為6個(gè)字符）。
b. 為該表增加1列NAME（系名），可變長(zhǎng)，最大20個(gè)字符。
1. 寫(xiě)出建立BORROW表的SQL語(yǔ)句，要求定義主碼完整性約束和引用完整性約束
--實(shí)現(xiàn)代碼：
CREATE TABLE BORROW(
CNO int FOREIGN KEY REFERENCES CARD(CNO),
BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
RDATE datetime,
PRIMARY KEY(CNO,BNO))
2. 找出借書(shū)超過(guò)5本的讀者,輸出借書(shū)卡號(hào)及所借圖書(shū)冊(cè)數(shù)
--實(shí)現(xiàn)代碼：
SELECT CNO,借圖書(shū)冊(cè)數(shù)=COUNT(*)
FROM BORROW
GROUP BY CNO
HAVING COUNT(*)>5
3. 查詢借閱了"水滸"一書(shū)的讀者，輸出姓名及班級(jí)
--實(shí)現(xiàn)代碼：
SELECT * FROM CARD c
WHERE EXISTS(
SELECT * FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
AND b.BNAME=N'水滸'
AND a.CNO=c.CNO)
4. 查詢過(guò)期未還圖書(shū)，輸出借閱者（卡號(hào)）、書(shū)號(hào)及還書(shū)日期
--實(shí)現(xiàn)代碼：
SELECT * FROM BORROW
WHERE RDATE
5. 查詢書(shū)名包括"網(wǎng)絡(luò)"關(guān)鍵詞的圖書(shū)，輸出書(shū)號(hào)、書(shū)名、作者
--實(shí)現(xiàn)代碼：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAME LIKE N'%網(wǎng)絡(luò)%'
6. 查詢現(xiàn)有圖書(shū)中價(jià)格最高的圖書(shū)，輸出書(shū)名及作者
--實(shí)現(xiàn)代碼：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE PRICE=(
SELECT MAX(PRICE) FROM BOOKS)
7. 查詢當(dāng)前借了"計(jì)算方法"但沒(méi)有借"計(jì)算方法習(xí)題集"的讀者，輸出其借書(shū)卡號(hào)，并按卡號(hào)降序排序輸出
--實(shí)現(xiàn)代碼：
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N'計(jì)算方法'
AND NOT EXISTS(
SELECT * FROM BORROW aa,BOOKS bb
WHERE aa.BNO=bb.BNO
AND bb.BNAME=N'計(jì)算方法習(xí)題集'
AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC
8. 將"C01"班同學(xué)所借圖書(shū)的還期都延長(zhǎng)一周
--實(shí)現(xiàn)代碼：
UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
FROM CARD a,BORROW b
WHERE a.CNO=b.CNO
AND a.CLASS=N'C01'
9. 從BOOKS表中刪除當(dāng)前無(wú)人借閱的圖書(shū)記錄
--實(shí)現(xiàn)代碼：
DELETE A FROM BOOKS a
WHERE NOT EXISTS(
SELECT * FROM BORROW
WHERE BNO=a.BNO)
10. 如果經(jīng)常按書(shū)名查詢圖書(shū)信息，請(qǐng)建立合適的索引
--實(shí)現(xiàn)代碼：
CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)
11. 在BORROW表上建立一個(gè)觸發(fā)器，完成如下功能：如果讀者借閱的書(shū)名是"數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用"，就將該讀者的借閱記錄保存在BORROW_SAVE表中（注ORROW_SAVE表結(jié)構(gòu)同BORROW表）
--實(shí)現(xiàn)代碼：
CREATE TRIGGER TR_SAVE ON BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
INSERT BORROW_SAVE SELECT i.*
FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO
AND b.BNAME=N'數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用'
12. 建立一個(gè)視圖，顯示"力01"班學(xué)生的借書(shū)信息（只要求顯示姓名和書(shū)名）
--實(shí)現(xiàn)代碼：
CREATE VIEW V_VIEW
AS
SELECT a.NAME,b.BNAME
FROM BORROW ab,CARD a,BOOKS b
WHERE ab.CNO=a.CNO
AND ab.BNO=b.BNO
AND a.CLASS=N'力01'
13. 查詢當(dāng)前同時(shí)借有"計(jì)算方法"和"組合數(shù)學(xué)"兩本書(shū)的讀者，輸出其借書(shū)卡號(hào)，并按卡號(hào)升序排序輸出
--實(shí)現(xiàn)代碼：
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
AND b.BNAME IN(N'計(jì)算方法',N'組合數(shù)學(xué)')
GROUP BY a.CNO
HAVING COUNT(*)=2
ORDER BY a.CNO DESC
14. 假定在建BOOKS表時(shí)沒(méi)有定義主碼，寫(xiě)出為BOOKS表追加定義主碼的語(yǔ)句
--實(shí)現(xiàn)代碼：
ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)
15.1 將NAME最大列寬增加到10個(gè)字符（假定原為6個(gè)字符）
--實(shí)現(xiàn)代碼：
ALTER TABLE CARD ALTER COLUMN NAME varchar(10)
15.2 為該表增加1列NAME（系名），可變長(zhǎng)，最大20個(gè)字符
--實(shí)現(xiàn)代碼：
ALTER TABLE CARD ADD 系名 varchar(20)
問(wèn)題描述：
為管理崗位業(yè)務(wù)培訓(xùn)信息，建立3個(gè)表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分別代表學(xué)號(hào)、學(xué)員姓名、所屬單位、學(xué)員年齡
C (C#,CN ) C#,CN 分別代表課程編號(hào)、課程名稱
SC ( S#,C#,G ) S#,C#,G 分別代表學(xué)號(hào)、所選修的課程編號(hào)、學(xué)習(xí)成績(jī)
要求實(shí)現(xiàn)如下5個(gè)處理：
1. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修課程名稱為’稅收基礎(chǔ)’的學(xué)員學(xué)號(hào)和姓名
2. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修課程編號(hào)為’C2’的學(xué)員姓名和所屬單位
3. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢不選修課程編號(hào)為’C5’的學(xué)員姓名和所屬單位
4. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修全部課程的學(xué)員姓名和所屬單位
5. 查詢選修了課程的學(xué)員人數(shù)
6. 查詢選修課程超過(guò)5門(mén)的學(xué)員學(xué)號(hào)和所屬單位
1. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修課程名稱為’稅收基礎(chǔ)’的學(xué)員學(xué)號(hào)和姓名
--實(shí)現(xiàn)代碼：
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM C,SC
WHERE C.[C#]=SC.[C#]
AND CN=N'稅收基礎(chǔ)')
2. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修課程編號(hào)為’C2’的學(xué)員姓名和所屬單位
--實(shí)現(xiàn)代碼：
SELECT S.SN,S.SD FROM S,SC
WHERE S.[S#]=SC.[S#]
AND SC.[C#]='C2'
3. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢不選修課程編號(hào)為’C5’的學(xué)員姓名和所屬單位
--實(shí)現(xiàn)代碼：
SELECT SN,SD FROM S
WHERE [S#] NOT IN(
SELECT [S#] FROM SC
WHERE [C#]='C5')
4. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修全部課程的學(xué)員姓名和所屬單位
--實(shí)現(xiàn)代碼：
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
RIGHT JOIN C ON SC.[C#]=C.[C#]
GROUP BY [S#]
HAVING COUNT(*)=COUNT(DISTINCT [S#]))
5. 查詢選修了課程的學(xué)員人數(shù)
--實(shí)現(xiàn)代碼：
SELECT 學(xué)員人數(shù)=COUNT(DISTINCT [S#]) FROM SC
6. 查詢選修課程超過(guò)5門(mén)的學(xué)員學(xué)號(hào)和所屬單位
--實(shí)現(xiàn)代碼：
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(DISTINCT [C#])>5)
if not object_id('cj')is null
drop table cj
go
create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
insert into cj select '張三','語(yǔ)文',98
union select '李四','語(yǔ)文',89
union select '王五','語(yǔ)文',67
union select '周攻','語(yǔ)文',56
union select '張三','數(shù)學(xué)',89
union select '李四','數(shù)學(xué)',78
union select '王五','數(shù)學(xué)',90
union select '周攻','數(shù)學(xué)',87
方法一：
select stuname from
(select stuName,kcm,(select count(*) from cj where stuname!=a.stuname and kcm=a.kcm and cj>a.cj) cnt from cj a) x
group by stuname having max(cnt)1)
方法三：
select distinct stuname from cj a
where not exists(select kcm from cj b where a.stuname=stuname
and (select count(*) from cj where kcm=b.kcm and stuname!=a.stuname and cj>b.cj)>1)
SQLSEREVER測(cè)試題(上)
一 SQLSERVER管理部分
1 請(qǐng)講出身份驗(yàn)證模式與登錄帳號(hào)的關(guān)系及如何用各種帳號(hào)進(jìn)行登錄，并畫(huà)出示意圖
2 請(qǐng)講出登錄帳號(hào)、數(shù)據(jù)庫(kù)用戶及數(shù)據(jù)庫(kù)角色之間的關(guān)系，并畫(huà)出示意圖
3 請(qǐng)講出數(shù)據(jù)庫(kù)用戶、數(shù)據(jù)庫(kù)角色與數(shù)據(jù)庫(kù)對(duì)象之間的關(guān)系，并畫(huà)出直接對(duì)用戶授權(quán)與間接對(duì)用戶授權(quán)（系統(tǒng)權(quán)限與對(duì)象權(quán)限）的方法
4 請(qǐng)講出服務(wù)器角色、數(shù)據(jù)庫(kù)角色、標(biāo)準(zhǔn)角色與應(yīng)用程序角色的區(qū)別與驗(yàn)證其權(quán)限的方法
5 請(qǐng)講出數(shù)據(jù)庫(kù)還原模型對(duì)數(shù)據(jù)庫(kù)的影響
6 有一個(gè)執(zhí)行關(guān)鍵任務(wù)的數(shù)據(jù)庫(kù)，請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)備份策略
7 請(qǐng)使用文件與文件組恢復(fù)的方式恢復(fù)數(shù)據(jù)庫(kù)
8 請(qǐng)使用事務(wù)日志恢復(fù)數(shù)據(jù)庫(kù)到一個(gè)時(shí)間點(diǎn)
9 請(qǐng)?jiān)O(shè)計(jì)作業(yè)進(jìn)行周期性的備份數(shù)據(jù)庫(kù)
10 如何監(jiān)控?cái)?shù)據(jù)庫(kù)的阻塞，并實(shí)現(xiàn)數(shù)據(jù)庫(kù)的死鎖測(cè)試
11 如何監(jiān)控?cái)?shù)據(jù)庫(kù)的活動(dòng)，并能使用索引優(yōu)化向?qū)伤饕?br />
12 理解數(shù)據(jù)庫(kù)框圖的作用并可以設(shè)計(jì)表與表之間的關(guān)系
SQLSEREVER測(cè)試題(中)
二 SQLSERVER的實(shí)現(xiàn)部分
1 有訂單表，需要實(shí)現(xiàn)它的編號(hào)，格式如下：200211030001……200222039999等
2 有表T1,T2，現(xiàn)有一事務(wù)，在向表T1添加數(shù)據(jù)時(shí)，同時(shí)也必須向T2也添加數(shù)據(jù)，如何實(shí)現(xiàn)該事務(wù)
3 如何向T1中的編號(hào)字段（code varchar(20)）添加一萬(wàn)條記錄，不充許重復(fù)，規(guī)則如下：編號(hào)的數(shù)據(jù)必須從小寫(xiě)的a-z之間取值
4 如何刪除表中的重復(fù)數(shù)據(jù),請(qǐng)使用游標(biāo)與分組的辦法
5 如何求表中相鄰的兩條記錄的某字段的值之差
6 如何統(tǒng)計(jì)數(shù)據(jù)庫(kù)中所有用戶表的數(shù)據(jù)，顯示格式如下：
表名 記錄數(shù)
sales 23
7 如何刪除數(shù)據(jù)庫(kù)中的所有用戶表（表與表之間有外鍵關(guān)系）
8 表A editor_id lb2_id
123 000
123 003
123 003
456 007
456 006
表B lb2_id lb2_name
000 a
003 b
006 c
007 d
顯示 a 共1條 （表A內(nèi)lb2_id為000的條數(shù)）
b 共2條（表A內(nèi)lb2_id為003的條數(shù)）
9 人員情況表（employee）:里面有一字段文化程度（wh）：包括四種情況（本科以上，大專，高中，初中以下）,現(xiàn)在我要根據(jù)年齡字段查詢統(tǒng)計(jì)出：表中文化程度為本科以上，大專，高中，初中以下，各有多少人，占總?cè)藬?shù)多少。
SELECT wh AS 學(xué)歷,age as 年齡, Count(*) AS 人數(shù),
Count(*) * 100 /(SELECT Count(*) FROM employee) AS 百分比
FROM employee GROUP BY wh,age
學(xué)歷 年齡 人數(shù) 百分比
本科以上 20 34 14
大專 20 33 13
高中 20 33 13
初中以下 20 100 40
本科以上 21 50 20
10 現(xiàn)在有三個(gè)表student:（FID 學(xué)生號(hào)，F(xiàn)Name 姓名），
subject:（FSubID 課程號(hào)，F(xiàn)SubName 課程名）,
Score(FScoreId 成績(jī)記錄號(hào),FSubID 課程號(hào),FStdID 學(xué)生號(hào),FScore 成績(jī))
怎么能實(shí)現(xiàn)這個(gè)表：
姓名 英語(yǔ) 數(shù)學(xué) 語(yǔ)文 歷史
張薩 78 67 89 76
王強(qiáng) 89 67 84 96
SELECT a.FName AS 姓名,
英語(yǔ) = SUM(CASE b.FSubName WHEN '英語(yǔ)' THEN c.FScore END),
數(shù)學(xué) = SUM(CASE b.FSubName WHEN '數(shù)學(xué)' THEN c.FScore END),
語(yǔ)文 = SUM(CASE b.FSubName WHEN '語(yǔ)文' THEN c.FScore END),
歷史 = SUM(CASE b.FSubName WHEN '歷史' THEN c.FScore END)
FROM Student a, Subject b, Score c
WHERE a.FID = c.FStdId AND b.FSubID = c.FsubID GROUP BY a.FName
11 原始表的數(shù)據(jù)如下：
PID PTime PNo
111111 2003-01-28 04:30:09
111111 2003-01-28 18:30:00
222222 2003-01-28 04:31:09
333333 2003-01-28 04:32:09
111111 2003-02-09 03:35:25
222222 2003-02-09 03:36:25
333333 2003-02-09 03:37:25
查詢生成表
PDate 111111 222222 333333 ......
2003-01-28 04:30:09 04:31:09 04:32:09 ......
2003-01-28 18:30:00
2003-02-09 03:35:25 03:36:25 03:37:25 ......
12 表一(AAA)
商品名稱mc 商品總量sl
A 100
B 120
表二(BBB)
商品名稱mc 出庫(kù)數(shù)量sl
A 10
A 20
B 10
B 20
B 30
用一條SQL語(yǔ)句算出商品A,B目前還剩多少？
一
declare @AAA table (商品名稱 varchar(10), 商品總量 int)
insert into @AAA values('A',100)
insert into @AAA values('B',120)
declare @BBB table (商品名稱 varchar(10), 出庫(kù)數(shù)量 int)
insert into @BBB values('A', 10)
insert into @BBB values('A', 20)
insert into @BBB values('B', 10)
insert into @BBB values('B', 20)
insert into @BBB values('B', 30)
select TA.商品名稱,A-B AS 剩余數(shù)量 FROM
(select 商品名稱,sum(商品總量) AS A
from @AAA
group by 商品名稱)TA,
(select 商品名稱,sum(出庫(kù)數(shù)量) AS B
from @BBB
group by 商品名稱)TB
where TA.商品名稱=TB.商品名稱
二
select 商品名稱,sum(商品總量) 剩余數(shù)量 from (select * from @aaa union all select 商品名稱,-出庫(kù)數(shù)量 from @bbb) a group by 商品名稱
13 優(yōu)化這句SQL語(yǔ)句
UPDATE tblExlTempYear
SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC
FROM tblExlTempYear,tblExlTempMonth
where tblExlTempMonth.GDXM=tblExlTempYear.GDXM and tblExlTempMonth.TXDZ=tblExlTempYear.TXDZ
（1）、加索引：
tblExlTempYear（GDXM，TXDZ）
tblExlTempMonth （GDXM，TXDZ）
（2）、刪除無(wú)用數(shù)據(jù)
（3）、轉(zhuǎn)移過(guò)時(shí)數(shù)據(jù)
（4）、加服務(wù)器內(nèi)存，升級(jí)服務(wù)器
（5）、升級(jí)網(wǎng)絡(luò)系統(tǒng)
UPDATE tblExlTempYear
SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC
FROM tblExlTempYear (index indexY),tblExlTempMonth (index indexM)
where tblExlTempMonth.GDXM=tblExlTempYear.GDXM and tblExlTempMonth.TXDZ=tblExlTempYear.TXDZ
14 品種 日期 數(shù)量
P0001 2002-1-10 10
P0001 2002-1-10 11
P0001 2002-1-10 50
P0001 2002-1-12 9
P0001 2002-1-12 8
P0001 2002-1-12 7
P0002 2002-10-10 5
P0002 2002-10-10 7
P0002 2002-10-12 0.5
P0003 2002-10-10 5
P0003 2002-10-12 7
P0003 2002-10-12 9
結(jié)果要先按照品種匯總,再按照日期匯總,結(jié)果如下:
P0001 2002-1-10 71
P0001 2002-1-12 24
P0002 2002-10-10 12
P0002 2002-10-12 0.5
P0003 2002-10-10 5
P0003 2002-10-12 16
SQL SERVER能做出這樣的匯總嗎…
15 在分組查循中with{cube|rollup}的區(qū)別是什么?
如:
use pangu
select firm_id,p_id,sum(o_price_quantity)as sum_values
from orders
group by firm_id,p_id
with cube
與
use pangu
select firm_id,p_id,sum(o_price_quantity)as sum_values
from orders
group by firm_id,p_id
with rollup
的區(qū)別是什么?
CUBE 和 ROLLUP 之間的區(qū)別在于：
CUBE 生成的結(jié)果集顯示了所選列中值的所有組合的聚合。
ROLLUP 生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。
例如，簡(jiǎn)單表 Inventory 中包含：
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查詢將生成小計(jì)報(bào)表：
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00
(7 row(s) affected)
如果查詢中的 ROLLUP 關(guān)鍵字更改為 CUBE，那么 CUBE 結(jié)果集與上述結(jié)果相同，只是在結(jié)果集的末尾還會(huì)返回下列兩行：
ALL Blue 225.00
ALL Red 433.00
CUBE 操作為 Item 和 Color 中值的可能組合生成行。例如，CUBE 不僅報(bào)告與 Item 值 Chair 相組合的 Color 值的所有可能組合（Red、Blue 和 Red + Blue），而且報(bào)告與 Color 值 Red 相組合的 Item 值的所有可能組合（Chair、Table 和 Chair + Table）。對(duì)于 GROUP BY 子句中右邊的列中的每個(gè)值，ROLLUP 操作并不報(bào)告左邊一列（或左邊各列）中值的所有可能組合。例如，ROLLUP 并不對(duì)每個(gè) Color 值報(bào)告 Item 值的所有可能組合。ROLLUP 操作的結(jié)果集具有類似于 COMPUTE BY 所返回結(jié)果集的功能；然而，
ROLLUP 具有下列優(yōu)點(diǎn)： ROLLUP 返回單個(gè)結(jié)果集；COMPUTE BY 返回多個(gè)結(jié)果集，而多個(gè)結(jié)果集會(huì)增加應(yīng)用程序代碼的復(fù)雜性。ROLLUP 可以在服務(wù)器游標(biāo)中使用；COMPUTE BY 不可以。有時(shí)，查詢優(yōu)化器為 ROLLUP 生成的執(zhí)行計(jì)劃比為 COMPUTE BY 生成的更為高效。
16 假如我有兩個(gè)表
表1（電話號(hào)碼，是否存在）
表2（電話號(hào)碼，是否撥打）
想查找表1中的電話號(hào)碼是否在表2中存在，如果存在就更新表1中的是否存在字段為1。
UPDATE 表1 SET 是否存在=1
WHERE EXISTS(SELECT * FROM 表2 WHERE 表2.電話號(hào)碼 = 表1.電話號(hào)碼)
17 用存儲(chǔ)過(guò)程調(diào)用外部程序.
不過(guò)要做成com控件
用sp_OACreate存儲(chǔ)過(guò)程)
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr),
[email=Source=@src]Source=@src[/email]
,
[email=Description=@desc]Description=@desc[/email]
RETURN
END
1、在MS SQL Server中，用來(lái)顯示數(shù)據(jù)庫(kù)信息的系統(tǒng)存儲(chǔ)過(guò)程是（ ）
A sp_ dbhelp
B sp_ db
C sp_ help
D sp_ helpdb
2、SQL語(yǔ)言中，刪除一個(gè)表的命令是（ ）
A DELETE
B DROP
C CLEAR
D REMORE
3、關(guān)系數(shù)據(jù)庫(kù)中，主鍵是（__）
A、為標(biāo)識(shí)表中唯一的實(shí)體
B、創(chuàng)建唯一的索引，允許空值
C、只允許以表中第一字段建立
D、允許有多個(gè)主鍵的
4、在Transact-SQL語(yǔ)法中，SELECT語(yǔ)句的完整語(yǔ)法較復(fù)雜，但至少包括的部分（1___），使用關(guān)鍵字（2___）可以把重復(fù)行屏蔽，將多個(gè)查詢結(jié)果返回一個(gè)結(jié)果集合的運(yùn)算符是（3___），如果在SELECT語(yǔ)句中使用聚合函數(shù)時(shí)，一定在后面使用（4___）。
⑴ A、SELECT，INTO B、SELECT，F(xiàn)ROM
C、SELECT，GROUP D、僅SELECT
⑵ A、DISTINCT B、UNION
C、ALL C、TOP
⑶ A、JOIN B、UNION
C、INTO C、LIKE
⑷ A、GROUP BY B、COMPUTE BY
C、HAVING D、COMPUTE
5、語(yǔ)句DBCC SHRINKDATABASE (Sample, 25)中的25表示的意思是
A、25M
B、剩余占整個(gè)空間的25%
C、已用空間占整個(gè)空間的25%
D、以上都不對(duì)
6、你是一個(gè)保險(xiǎn)公司的數(shù)據(jù)庫(kù)開(kāi)發(fā)人員，公司的保單信息存儲(chǔ)在SQL Server 2000數(shù)據(jù)庫(kù)中，你使用以下腳本建立了一個(gè)名為Policy的表：
CREATE TABLE Policy
(
PolicyNumber int NOT NULL DEFAULT (0),
InsuredLastName char (30) NOT NULL,
InsuredFirstName char (20) NOT NULL,
InsuredBirthDate datetime NOT NULL,
PolicyDate datetime NOT NULL,
FaceAmount money NOT NULL,
CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber)
)
每次公司銷售出一份保單，Policy表中就增加一條記錄，并賦予其一個(gè)新的保單號(hào)，你將怎么做？
a.建立一個(gè)INSTEAD OF INSERT觸發(fā)器來(lái)產(chǎn)生一個(gè)新的保單號(hào)，并將這個(gè)保單號(hào)插入數(shù)據(jù)表中。
b.建立一個(gè)INSTEAD OF UPDATE觸發(fā)器來(lái)產(chǎn)生一個(gè)新的保單號(hào)，并將這個(gè)保單號(hào)插入數(shù)據(jù)表中。
c.建立一個(gè)AFTER UPDATE觸發(fā)器來(lái)產(chǎn)生一個(gè)新的保單號(hào)，并將這個(gè)保單號(hào)插入數(shù)據(jù)表中。
d.用AFTER UPDATE觸發(fā)器替代DEFAULT約束條件產(chǎn)生一個(gè)新的保單號(hào)，并將這個(gè)保單號(hào)插入數(shù)據(jù)表中。
７、在SQL語(yǔ)言中，如果要建立一個(gè)工資表包含職工號(hào)，姓名，職稱。工資等字段。若要保證工資字段的取值不低于800元，最合適的實(shí)現(xiàn)方法是：
A。在創(chuàng)建工資表時(shí)為”工資“字段建立缺省
B。在創(chuàng)建工資表時(shí)為”工資“字段建立檢查約束
C。在工資表建立一個(gè)觸發(fā)器
D。為工資表數(shù)據(jù)輸入編寫(xiě)一個(gè)程序進(jìn)行控制
８、Select 語(yǔ)句中用來(lái)連接字符串的符號(hào)是______.
A. “+” B. “&” C.“||” D.“|”
９、你是一個(gè)出版公司的數(shù)據(jù)庫(kù)開(kāi)發(fā)人員，對(duì)特定的書(shū)名的每天的銷售情況建立了如下的存儲(chǔ)過(guò)程：
CREATE PROCEDURE get_sales_for_title
title varchar(80), @ytd_sales int OUTPUT
AS
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
IF @@ROWCOUNT = 0
RETURN(-1)
ELSE
RETURN(0)
另外建立了一個(gè)腳本執(zhí)行這個(gè)存儲(chǔ)過(guò)程，如果執(zhí)行成功，將返回對(duì)應(yīng)于書(shū)名的每天的銷售情況的報(bào)表，如果執(zhí)行失敗，將返回“No Sales Found”，怎樣建立這個(gè)腳本？
Ａ. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’, @ytd
IF @retval
Ｂ. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
IF @retval
Ｃ. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’,@retval OUTPUT
IF @retval
Ｄ. DECLARE @retval int
DECLARE @ytd int
EXEC @retval = get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
IF @retval
１０、You are a database developer for a container manufacturing company. The containers produced by your company are a number of different sizes and shapes. The tables that store the container information are shown in the Size, Container, and Shape Tables exhibit:
Size
SizeID
SizeName
Height
Container
ContainerID
ShapeID
SizeID
Shape
ShapeID
ShapeName
Measurements
A sample of the data stored in the tables is shown below:
Size Table
SizeID SizeName Height
1 Small 40
2 Medium 60
3 Large 80
4 Jumbo 100
Shape Table
ShapeID ShapeName Measurement
1 Triangle 10
2 Triangle 20
3 Triangle 30
4 Square 20
5 Square 30
6 Square 40
7 Circle 15
8 Circle 25
9 Circle 35
Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables.
You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information. What should you do?
A. Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.
B. Create a stored procedure that requires ContainerID as an argument and returns the volume of the container.
C. Add a column named volume to the container table. Create a trigger that calculates and stores volume in this column when a new container is inserted into the table.
D. Add a computed column to the container table that calculates the volume of the container.
填空題（１空１分共２０分）
1、 如果設(shè)計(jì)的表不符合第二范式，可能會(huì)導(dǎo)致_______，________，_______。
2、 ＳＱＬ是由_______語(yǔ)言，________語(yǔ)言，_______語(yǔ)言組成。
3、 SQL Server在兩個(gè)安全級(jí)上驗(yàn)證用戶，分別是______________,_____________________。
4、 自定義函數(shù)由___________函數(shù)，_______________函數(shù)，___________________函數(shù)組成。
5、 備份策略的三種類型是__________備份，_______________備份，___________________備份組成。
6、 啟動(dòng)一個(gè)顯式事務(wù)的語(yǔ)句為_(kāi)_________，提交事務(wù)的語(yǔ)句為_(kāi)_________，回滾事務(wù)的語(yǔ)句為_(kāi)_________
7、 表的每一行在表中是惟一的實(shí)體屬于__________完整性，使列的輸入有效屬于__________完整性，兩個(gè)表的主關(guān)鍵字和外關(guān)鍵字的數(shù)據(jù)應(yīng)該對(duì)應(yīng)一致屬于__________完整性。
簡(jiǎn)答題（共２０分）
1、 在幫助中[ ,...n ] 意思是什么？（４分）
2、 請(qǐng)簡(jiǎn)述一下第二范式（４分）
3、 現(xiàn)有1銷售表，它們結(jié)構(gòu)如下：（４分）
id int (標(biāo)識(shí)號(hào))
codno char(7) (商品編碼)
codname varchar(30) (商品名稱)
spec varchar(20) （商品規(guī)格）
price numeric(10,2) （價(jià)格）
sellnum int （銷售數(shù)量）
deptno char(3) （售出分店編碼）
selldate datetime （銷售時(shí)間）
要求：寫(xiě)出查詢銷售時(shí)間段在2002-2-15日到2002-4-29之間，分店編碼是01的所有記錄。
４、寫(xiě)一個(gè)存儲(chǔ)過(guò)程，要求傳入一個(gè)表名，返回該表的記錄數(shù)（假設(shè)傳入的表在數(shù)據(jù)庫(kù)中都存在）（４分）
５、請(qǐng)簡(jiǎn)述UPDATE 觸發(fā)器如何工作原理。（４分）
簡(jiǎn)答題：（共４０分）
1、（５分）使用一條SQL語(yǔ)句找到重復(fù)的值及重復(fù)的次數(shù)：有一數(shù)據(jù)表ZD_ks，其中有字段BM，MC，。。。，請(qǐng)查詢出在ZD_ks中BM有重復(fù)的值及重復(fù)的次數(shù)，沒(méi)有的不要列出。如下表：
BM DUPCOUNT
001 3
002 2
2、描述(5分)
表1 student 學(xué)生信息表
ID int 學(xué)生編號(hào)
Name varchar 學(xué)生姓名
Sex bit 性別(男0女1)
Class int 班級(jí)編號(hào)
表2 schedule 課程信息表
ID int 課程編號(hào)
Name varchar 課程名稱
表3 Grade 成績(jī)信息表
ID int 自動(dòng)編號(hào)
UID int 學(xué)生編號(hào)
SID int 課程編號(hào)
Num int 考試成績(jī)
(a)求各班的總?cè)藬?shù)（１分）
(b)求1班女生和男生的平均成績(jī)（２分）
(c)各班"數(shù)據(jù)結(jié)構(gòu)"(課程名稱)不及格的人數(shù)（２分）
３、問(wèn)題描述：(30分)
本題用到下面三個(gè)關(guān)系表：
CARD 借書(shū)卡。 CNO 卡號(hào)，NAME 姓名，CLASS 班級(jí)
BOOKS 圖書(shū)。 BNO 書(shū)號(hào)，BNAME 書(shū)名,AUTHOR 作者，PRICE 單價(jià)，QUANTITY 庫(kù)存冊(cè)數(shù)
BORROW 借書(shū)記錄。 CNO 借書(shū)卡號(hào)，BNO 書(shū)號(hào)，RDATE 還書(shū)日期
備注：限定每人每種書(shū)只能借一本；庫(kù)存冊(cè)數(shù)隨借書(shū)、還書(shū)而改變。
要求實(shí)現(xiàn)如下15個(gè)處理：
1．找出借書(shū)超過(guò)5本的讀者,輸出借書(shū)卡號(hào)及所借圖書(shū)冊(cè)數(shù)。(2分)
2．查詢借閱了"水滸"一書(shū)的讀者，輸出姓名及班級(jí)。(3分)
3．查詢過(guò)期未還圖書(shū)，輸出借閱者（卡號(hào)）、書(shū)號(hào)及還書(shū)日期。(3分)
4．查詢書(shū)名包括"網(wǎng)絡(luò)"關(guān)鍵詞的圖書(shū)，輸出書(shū)號(hào)、書(shū)名、作者。(2分)
5．查詢現(xiàn)有圖書(shū)中價(jià)格最高的圖書(shū)，輸出書(shū)名及作者。(2分)
6．查詢當(dāng)前借了"計(jì)算方法"但沒(méi)有借"計(jì)算方法習(xí)題集"的讀者，輸出其借書(shū)卡號(hào)，并按卡號(hào)降序排序輸出。(4分)
7．將"C01"班同學(xué)所借圖書(shū)的還期都延長(zhǎng)一周。(2分)
8．從BOOKS表中刪除當(dāng)前無(wú)人借閱的圖書(shū)記錄。(2分)
9．在BORROW表上建立一個(gè)觸發(fā)器，完成如下功能：如果讀者借閱的書(shū)名是"數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用"，就將該讀者的借閱記錄保存在BORROW_SAVE表中（注ORROW_SAVE表結(jié)構(gòu)同BORROW表）。(4分)
10．建立一個(gè)視圖，顯示"力01"班學(xué)生的借書(shū)信息（只要求顯示姓名和書(shū)名）。(3分)
11．查詢當(dāng)前同時(shí)借有"計(jì)算方法"和"組合數(shù)學(xué)"兩本書(shū)的讀者，輸出其借書(shū)卡號(hào)，并按卡號(hào)升序排序輸出。(3分)
第二套
1、問(wèn)題描述：
已知關(guān)系模式：
S (SNO,SNAME） 學(xué)生關(guān)系。SNO 為學(xué)號(hào)，SNAME 為姓名
C (CNO,CNAME,CTEACHER) 課程關(guān)系。CNO 為課程號(hào)，CNAME 為課程名，CTEACHER 為任課教師
SC(SNO,CNO,SCGRADE) 選課關(guān)系。SCGRADE 為成績(jī)
要求實(shí)現(xiàn)如下5個(gè)處理：
1． 找出沒(méi)有選修過(guò)“李明”老師講授課程的所有學(xué)生姓名
2． 列出有二門(mén)以上（含兩門(mén)）不及格課程的學(xué)生姓名及其平均成績(jī)
3． 列出既學(xué)過(guò)“1”號(hào)課程，又學(xué)過(guò)“2”號(hào)課程的所有學(xué)生姓名
4． 列出“1”號(hào)課成績(jī)比“2”號(hào)同學(xué)該門(mén)課成績(jī)高的所有學(xué)生的學(xué)號(hào)
5． 列出“1”號(hào)課成績(jī)比“2”號(hào)課成績(jī)高的所有學(xué)生的學(xué)號(hào)及其“1”號(hào)課和“2”號(hào)課的成績(jī)
2、問(wèn)題描述：
為管理崗位業(yè)務(wù)培訓(xùn)信息，建立3個(gè)表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分別代表學(xué)號(hào)、學(xué)員姓名、所屬單位、學(xué)員年齡
C (C#,CN ) C#,CN 分別代表課程編號(hào)、課程名稱
SC ( S#,C#,G ) S#,C#,G 分別代表學(xué)號(hào)、所選修的課程編號(hào)、學(xué)習(xí)成績(jī)
要求實(shí)現(xiàn)如下5個(gè)處理：
1. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修課程名稱為’稅收基礎(chǔ)’的學(xué)員學(xué)號(hào)和姓名
2. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修課程編號(hào)為’C2’的學(xué)員姓名和所屬單位
3. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢不選修課程編號(hào)為’C5’的學(xué)員姓名和所屬單位
4. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修全部課程的學(xué)員姓名和所屬單位
5. 查詢選修了課程的學(xué)員人數(shù)
6. 查詢選修課程超過(guò)5門(mén)的學(xué)員學(xué)號(hào)和所屬單位
有重復(fù)的再發(fā)
Answer 1：找出當(dāng)月的天數(shù)
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))
Question2：Can you use a SQL statement to calculating it!
How can I print "10 to 20" for books that sell for between $10 and $20，"unknown" for books whose price is null, and "other" for all other prices?
Answer 2：
select bookid,bookname,price=case when price is null then 'unknown'
when price between 10 and 20 then '10 to 20' else price end
from books
Question3：Can you use a SQL statement to finding duplicate values!
How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname number_dups
---------------------------------------- -----------
Ringer 2
(1 row(s) affected)
Answer 3
select au_lname,number_dups=count(1) from authors group by au_lname
Question4：Can you create a cross-tab report in my SQL Server!
How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?
You can use the table sales and stores in datatabase pubs.
Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale qulity. Table stores record all store information.
I want to get the result look like as below:
Output:
stor_name Total Qtr1 Qtr2 Qtr3 Qtr4
---------------------------------------- ----------- ----------- ----------- ----------- -----------
Barnum's 50 0 50 0 0
Bookbeat 55 25 30 0 0
Doc-U-Mat: Quality Laundry and Books 85 0 85 0 0
Fricative Bookshop 60 35 0 0 25
Total 250 60 165 0 25
Answer 4：用動(dòng)態(tài)SQL實(shí)現(xiàn)
Question5: The Fastest Way to Recompile All Stored Procedures
I have a problem with a database running in SQL Server 6.5 (Service Pack 4). We moved the database (object transfer) from one machine to another last night, and an error (specific to a stored procedure) is cropping up. However, I can't tell which procedure is causing it. Permissions are granted in all of our stored procedures; is there a way from the isql utility to force all stored procedures to recompile?
Tips: sp_recompile can recomplie a store procedure each time
Answer 5：在執(zhí)行存儲(chǔ)過(guò)程時(shí),使用 with recompile 選項(xiàng)強(qiáng)制編譯新的計(jì)劃；使用sp_recompile系統(tǒng)存儲(chǔ)過(guò)程強(qiáng)制在下次運(yùn)行時(shí)進(jìn)行重新編譯
Question6: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
Result:
line-no title_id
----------- --------
1 BU1032
2 BU1111
3 BU2075
4 BU7832
5 MC2222
6 MC3021
7 MC3026
8 PC1035
9 PC8888
10 PC9999
11 PS1372
12 PS2091
13 PS2106
14 PS3333
15 PS7777
16 TC3218
17 TC4203
18 TC7777
Answer 6：
--SQL 2005的寫(xiě)法
select row_number() as line_no ,title_id from titles
--SQL 2000的寫(xiě)法
select line_no identity(int,1,1),title_id into #t from titles
select * from #t
drop table #t
Question 7: Can you tell me what the difference of two SQL statements at performance of execution?
Statement 1:
if NOT EXISTS ( select * from publishers where state = 'NY')
begin
SELECT 'Sales force needs to penetrate New York market'end
else
begin
SELECT 'We have publishers in New York'
end
Statement 2:
if EXISTS ( select * from publishers where state = 'NY')
begin
SELECT 'We have publishers in New York'
end
else
begin
SELECT 'Sales force needs to penetrate New York market'
end
Answer 7：不同點(diǎn):執(zhí)行時(shí)的事務(wù)數(shù),處理時(shí)間,從客戶端到服務(wù)器端傳送的數(shù)據(jù)量大小
Question8: How can I list all California authors regardless of whether they have written a book?
In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each author written.
CA behalf of california in table authors.
Answer 8：
select * from authors where state='CA'
Question9: How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?
In database pubs, use three table stores,sales and titles to implement this requestment.
Now I want to get the result as below:
stor_id stor_name
------- ----------------------------------------
...
7896 Fricative Bookshop
...
...
...
Answer 9：
select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook')
Question10: How can I list non-contignous data?
In database pubs, I create a table test using statement as below, and I insert several row as below
create table test
( id int primary key )
go
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go
Now I want to list the result of the non-contignous row as below,how can I do it?
Missing after Missing before
------------- --------------
6 8
9 11
...
Answer 10：
select id from test t where not exists(select 1 from test where id=t.id+1)
or not exists(select 1 from test where id=t.id-1)
Question11: How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:
type title price
------------ -------------------------------------------------------------------------------- ---------------------
business The Busy Executive's Database Guide 19.9900
...
...
...
...
Answer 11：
select a.type,a.title,a.price from titles a,
(select type,price=avg(price) from titles group by type)b
where a.type=b.type and a.price>b.price
向T1中的編號(hào)字段（code varchar(20)）添加一萬(wàn)條記錄，不充許重復(fù):
網(wǎng)上有給出的答案:
create table #tmp(id char(1),name char(1))
create table #tmp1(id varchar(10))
go
insert #tmp(id,name) values(0,'a')
insert #tmp(id,name) values(1,'b')
insert #tmp(id,name) values(2,'c')
insert #tmp(id,name) values(3,'d')
insert #tmp(id,name) values(4,'e')
insert #tmp(id,name) values(5,'f')
insert #tmp(id,name) values(6,'g')
insert #tmp(id,name) values(7,'h')
insert #tmp(id,name) values(8,'i')
insert #tmp(id,name) values(9,'j')
go
declare @t varchar(10)
set @t=10000
while @t
這樣做,確實(shí)沒(méi)想到,測(cè)試后本機(jī)用了5秒(不往T1里面插入數(shù)據(jù),用#T1做臨時(shí)表,只有一個(gè)Code字段),后改成我這樣的寫(xiě)法,用時(shí)3秒.
declare @i int,@j int,@r int,@acount int
create table #tmp(id int null, code varchar(3) null)
declare @ichar char(1)
declare @jchar char(1)
declare @rchar char(1)
select @i=0
select @j=0
select @r=0
select @acount=0
select @ichar=''
while @i
[email=97+@i]97+@i[/email]
)
select @j=0
while @j
[email=97+@j]97+@j[/email]
)
select @r=0
while @r
[email=97+@r]97+@r[/email]
)
select @acount=@acount+1
if @acount>10000
break
insert into #tmp(id,code)
select @acount,@ichar+@jchar+@rchar
select @r=@r+1
end
if @acount>10000
break
select @j=@j+1
end
if @acount>10000
break
select @i=@i+1
end
insert t1(code)
select code from #tmp order by id
go
drop table #tmp
本文來(lái)自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：
http://blog.csdn.net/gdjlc/archive/2008/10/07/3027562.aspx
一 單詞解釋(2分/個(gè)) 34
Data
Database
RDBMS
GRANT
REVOKE
DENY
DECLARE
PROCEDURE
Transaction
TRIGGER
繼續(xù)
唯一
主鍵
標(biāo)識(shí)列
外鍵
檢查
約束
二 編寫(xiě)SQL語(yǔ)句(5分/題)50
1) 創(chuàng)建一張學(xué)生表，包含以下信息，學(xué)號(hào)，姓名，年齡，性別，家庭住址，聯(lián)系電話
Create table student (id(varchar),name(varchar),age(int),sex(char),addr(varchar),phone(varchar))
2) 修改學(xué)生表的結(jié)構(gòu)，添加一列信息，學(xué)歷
3) 修改學(xué)生表的結(jié)構(gòu)，刪除一列信息，家庭住址
4) 向?qū)W生表添加如下信息：
學(xué)號(hào)
姓名
年齡
性別
聯(lián)系電話
學(xué)歷
1
A
22
男
123456
小學(xué)
2
B
21
男
119
中學(xué)
3
C
23
男
110
高中
4
D
18
女
114
大學(xué)
5) 修改學(xué)生表的數(shù)據(jù)，將電話號(hào)碼以11開(kāi)頭的學(xué)員的學(xué)歷改為“大�！�
6) 刪除學(xué)生表的數(shù)據(jù)，姓名以C開(kāi)頭，性別為‘男’的記錄刪除
7) 查詢學(xué)生表的數(shù)據(jù)，將所有年齡小于22歲的，學(xué)歷為“大�！钡�，學(xué)生的姓名和學(xué)號(hào)示出來(lái)
8) 查詢學(xué)生表的數(shù)據(jù)，查詢所有信息，列出前25%的記錄
9) 查詢出所有學(xué)生的姓名，性別，年齡降序排列
10) 按照性別分組查詢所有的平均年齡
本文來(lái)自ChinaUnix博客，如果查看原文請(qǐng)點(diǎn)：http://blog.chinaunix.net/u3/111677/showart_2177828.html |
|
免費(fèi)注冊(cè)
發(fā)表于 2010-02-04 15:40