程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 數據庫表的查詢操作實踐演練(實驗三)

數據庫表的查詢操作實踐演練(實驗三)

編輯:關於SqlServer

繼前兩次的實驗,本次實驗以熟練掌握利用select語句進行各種查詢操作:單表查詢、多表連接及查詢、嵌套查詢、集合查詢等,鞏固數據庫查詢操作。
下面就跟著小編一起練習吧!
在實驗一創建並插入數據的表(Student, Course,SC,Teacher,TC)的基礎上,完成以下操作。
(1)將教師‘羅莉'的名字改為‘羅莉莉'。
復制代碼 代碼如下:update Teacher set tname='羅莉莉' where tname='羅莉'
(2)將兩個同學(數據自己臨時設置,用後即刪除)的兩門課程的成績以運行sql程序文件的形式插入score表中。該題用以驗證、理解和掌握關系模型的完整性規則;
 插入:
復制代碼 代碼如下:insert into Score(sno,cno,grade) values ('04261006','C003','64')
insert into Score(sno,cno,grade) values('04261007','C004','79')
查詢:
復制代碼 代碼如下:select sno 學號,cno 課程號,grade 分數from Score where sno=04261006 or sno=04261007;
刪除:
復制代碼 代碼如下:delete from Score where sno=04261006 or sno=04261007;
(3)求每門課的平均成績,並把結果存入average表(自行設計並創建);
復制代碼 代碼如下:CREATE TABLE average
(
cno CHAR(8),
avscore numeric(5,2),
constraint a1 primary key (cno),
constraint a2 foreign key (cno) references Course(cno),
)
insert into average(cno,avscore)
select distinct cno ,avg(grade) from Score group by cno
(4)將學生“馬麗”的年齡改為24;
復制代碼 代碼如下:Update Student set 2014-year(Sbirth) 年齡 where Sname=' 馬麗'
(5)將所有學生的szipcode屬性列值填補上;
復制代碼 代碼如下:update Student set szipcode='221000'
(6)將average表中的所有課程的平均成績置零;
復制代碼 代碼如下: update average set avscore='0'
(7)刪除average表中的課程號為‘C007'的平均成績記錄;
復制代碼 代碼如下:delete from average where cno='C007'
(8)刪除所有average表中平均成績記錄;
復制代碼 代碼如下:delete from average;
(9)建立一個臨時學生信息表(tstudent),刪除該表中的學號含‘101'的所有學生記錄。
復制代碼 代碼如下:create  table  tstudent   ( Sno  char(8)  primary  key,     Sname  varchar(8)  unique ); 
Delete  from  tstudent  where  Sno  like '001011%';
(10)查詢全體學生的學號與姓名;
復制代碼 代碼如下:select sno 學號,sname 姓名from Student
(11)查詢全體學生的學號、姓名、所屬系;
復制代碼 代碼如下:select sno 學號,sname 姓名,sdept 系from Student
(12)查詢全體學生的詳細記錄;
復制代碼 代碼如下:select * from Student
(13)查詢全體學生的姓名及其年齡;
復制代碼 代碼如下:select sname 姓名,2014-year(sbirth) 年齡from Student
(14)查詢全體學生的姓名、出生年份;
復制代碼 代碼如下:select sname 姓名,year(sbirth) 出生年份from Student
(15)查詢所有修過課的學生的學號;
復制代碼 代碼如下:select distinct sno from Score
select distinct student.sno from Student,Score where Student.sno=Score.sno and Score.grade>0 ;
(16)查詢“計算機系”班全體學生名單;
復制代碼 代碼如下:select sno,sname from Student where sdept='計算機系'
(17)查詢查詢所有年齡在23歲以下的學生姓名及其年齡;
復制代碼 代碼如下:select sname 姓名,2014-year(sbirth) 年齡from Student where 2014-year(sbirth)<23;
(18)查詢考試成績有不及格的學生的學號;
復制代碼 代碼如下:select distinct sno from Score where grade<60;
(19)查詢年齡在20至22歲之間的學生姓名、系和年齡;
復制代碼 代碼如下:select sname 姓名,sdept 系,2014-year(sbirth) 年齡from student where 2014-year(sbirth) between 20 and 22;
(20)查詢年齡不在20至22歲之間的學生姓名、系和年齡;
 復制代碼 代碼如下:select sname 姓名,sdept 系,2014-year(sbirth) 年齡from student where 2014-year(sbirth) not between 20 and 22;
(21)查詢“計算機系”和“電商系”的學生的姓名;
復制代碼 代碼如下:select sname from Student where sdept='計算機系' or sclass='電商系'
(22)查詢既不是“計11”也不是“計61”班的學生的姓名和班級信息;
復制代碼 代碼如下:select sname,sclass from Student where sclass not in('計','計');
(23)查詢學號為“04262002”的學生的詳細情況;
[code]select student.sno,sname,ssex,2014-year(sbirth),sclass,grade from Student,Score where Student.sno=Score.sno and Student.sno='04262002';
(24)查詢學號以“04262”打頭的學生信息;
復制代碼 代碼如下:select * from Student where sno like '04262%'
(25)查詢所有姓“張”學生的學號、姓名、性別、年齡;
復制代碼 代碼如下:select sno 學號,sname 姓名,ssex 性別,2011-year(sbirth) 年齡from Student where sname like'王%'
(26)查詢名字中第二個字有“海”字的學生的學號、姓名、性別、年齡;
復制代碼 代碼如下: select sno 學號,sname 姓名,ssex 性別,2011-year(sbirth) 年齡from Student where sname like '_田%'
(27)查詢所有不姓“劉”學生的姓名;
復制代碼 代碼如下:select sname 姓名from Student where sname not like '劉%'
(28)查詢課程號以“C”開頭的最後兩個字母為“05”的課程號和課程名;
復制代碼 代碼如下:select cno,cname from Course where cno like 'C%05'
(29)某些學生選修某門課程後沒有參加考試,所以有選修課記錄,但沒有考試成績,試查找缺少考試成績的學生和相應的課程號;
復制代碼 代碼如下:select Student.sno,sname,cno from Student,Score where Student.sno=Score.sno and grade is NULL;
(30)查找全部有成績記錄的學生學號、課程號;
復制代碼 代碼如下:select sno, cno from Score where grade is not NULL;
(31)查找“計算機系”年齡在22歲以下的學生學號、姓名;
復制代碼 代碼如下:select sno ,sname from Student where sdept='計算機系' and 2014-year(sbirth)<22
(32)查找選修了“C001”號課程的學生學號及其成績,查詢結果按分數降序排序;
復制代碼 代碼如下:select student.sno,grade from student,Score where Student.sno=Score.sno and cno='C001' order by grade desc;
(33)查詢全體學生情況,查詢結果按所在系升序排列,對同一系中的學生按年齡降序排列;
復制代碼 代碼如下:select * from student order by sdept asc,2014-year(sbirth) desc;
(34)查詢學生總人數;
復制代碼 代碼如下:select count(*) 人數from Student;
(35)查詢選修了課程的學生人數;
復制代碼 代碼如下:select count(distinct sno)人數from Score;
(36)在所有課程中查詢最高分的學生學號和成績;
復制代碼 代碼如下:select sno,grade from Score where grade =(select max(grade)from Score )

復制代碼 代碼如下:select distinct a.* from Score a where a.sno IN (select top 1 Score.sno from Score where Score.cno = a.cno order by grade desc)
(37)查詢學習“C001”課程的學生最高分數;
 復制代碼 代碼如下:select max(grade)最高分數from Score where cno='C001'
(38)計算各個課程號與相應的選課人數;
復制代碼 代碼如下:select count(sno) 選課人數from Score group by cno;
(39)查詢“計算機系”選修了兩門課程以上的學生學號、姓名;
復制代碼 代碼如下:select Student.sno,sname from Student where Student.sno in
(select Student.sno from Student,Score where
sdept='計算機系'and Student.sno=Score.sno group by Student.sno having count(cno)>=2);
(40)自然連接student和score表;
復制代碼 代碼如下:select student.*,Score.grade from student ,Score where student.sno=Score.sno;
(41)使用自身連接查詢每一門課程的間接先行課(即先行課的先行課)
復制代碼 代碼如下: select a.cno,b.cpno from Course a,Course b where a.cpno=b.cno;
(42)使用復合條件連接查詢選修“c001”號課程且成績在90分以上的所有同學;
復制代碼 代碼如下: select sname,grade from student,Score where Student.sno=Score.sno and cno='C001' and grade>=90;
(43)使用復合條件連接查詢每個學生選修的課程名及其成績;
 復制代碼 代碼如下: select Student.sno,sname,cname,grade from Course,Score,Student where Course.cno=Score.cno and student.sno=Score.sno;
(44)查詢選修了全部課程的學生;
復制代碼 代碼如下:select Sname from Student where not exists (select *  from Course where not exists(select *  from Score where Sno=Student.Sno and Cno=Course.Cno))
(45)查詢所有選修了C001號課程的學生學號、姓名;
復制代碼 代碼如下:select student.sno,sname from student,Score where student.sno=Score.sno and cno='C001';
(46)查詢選修了課程C001或C007的學生學號、姓名;
[code]select student.sno,sname,cno from student,Score where student.sno=Score.sno and cno in ('C001','C007');[/code]
(47)查詢“計算機系”的學生及年齡不大於23歲的學生;
復制代碼 代碼如下:select sno ,sname,2014-year(sbirth) age ,sclass from student where sdept='計算機系' or 2014-year(sbirth)<=23;
(48)查詢既選修了課程C001又選修了課程C007的所有學生學號、姓名;
復制代碼 代碼如下:select student.sno,sname from student,Score where student.sno=Score.sno and cno='C001' and student.sno in (select student.sno from student,Score where student.sno=Score.sno and cno='C007')
(49)查詢選修了課程名為“數據庫原理”的學生的學號、姓名、性別、年齡;
復制代碼 代碼如下:select student.sno ,sname,ssex,cname,2011-year(sbirth) age from student,Score,Course where student.sno=Score.sno and Score.cno=Course.cno and cname='數據庫原理';
(50)查詢其他班中比“計算機系”所有學生年齡都小的學生名單;
復制代碼 代碼如下:select sno,sname ,2014-year(sbirth) age from student where 2014-year(sbirth)<(select min(2014-year(sbirth)) from student where sclass='計61')and sclass !='計61';
(51)查詢與“夏天”在同一個系學習的學生學號、姓名、性別、年齡;
復制代碼 代碼如下:select sno,sname,ssex,2014-year(sbirth) age from student where sdept=(select sdept from student where sname='夏天') and sname!='夏天'
(52)建立“計算機系”學生的視圖1;
復制代碼 代碼如下:create view view_student
as select sno,sname,ssex,sbirth,sclass from student where sclass='13z網絡'
(53)建立“計算機系”學生的視圖2,並要求進行修改與插入時,仍須保證該視圖只有“計算機系”班學生;
復制代碼 代碼如下:create view view_student2
as select sno,sname,ssex,sbirth,sclass from student where sclass='13z網絡' with check option;
(54)建立“計算機系”選修了“C001”課程的學生的視圖,定義視圖名為“v_cs_C001_student1”;
復制代碼 代碼如下:create view v_cs_C001_student1
as select student.sno,sname,ssex,sbirth,sclass from Student ,Score where
student.sno=Score.sno and sclass='13z網絡' and cno='C001';
(55)建立“計算機系”班選修了“C001”課程且成績在90分以上的學生的視圖,定義視圖名為“cs_c001_student2”;
復制代碼 代碼如下:create view cs_c001_student2
as
select student.sno,sname ,ssex,sbirth,sclass,cno from student,Score where
student.sno=Score.sno and cno='C001' and sclass='13z網絡'and student.sno in (select student.sno from student,Score where student.sno=Score.sno and grade>90)
(56)定義一個反映學生年齡的視圖,定義視圖名為“v_birth_student”;
復制代碼 代碼如下:create view v_birth_student
as
select sno,sname,2014-year(sbirth) age from student
(57)將學生表中所有女生記錄定義為一個視圖,視圖名為“v_female_student”;
復制代碼 代碼如下:create view v_female_student
as
select * from student where ssex='女';
(58)將學生的學號及其平均成績定義為一個視圖,視圖名為“v_average_student”;
復制代碼 代碼如下:create view v_average_student
as
select sno,avg(grade) avscore from Score group by sno;
(59)在“計算機系”學生視圖中找出年齡小於22歲的學生;
復制代碼 代碼如下:select * from view_student where 2014-year(sbirth)<=22;
(60)利用視圖查詢“計算機系”選修了“C001”課程的學生;
復制代碼 代碼如下:select * from v_cs_C001_student1;
(61)通過(52)中的“計算機系”視圖修改某個學生的名字;
復制代碼 代碼如下:update view_student set sname='王某某'where sno=04261001;
(62)通過(53)中的“計算機系”視圖,插入一個新學生記錄。
復制代碼 代碼如下:insert into view_student2(sno,sname,ssex,sbirth,sclass) values ('04262004','張某某','男','1987/11/09','計');
(63)通過(53)中的“計算機系”視圖,刪除一個學生記錄。
復制代碼 代碼如下:delete from view_student2 where sno='04262004'and sname='張某某';

實驗課結束了,相信通過本節課的實踐操作,小伙伴們都對數據庫表的操作有了更進一步的了解。
以上就是查詢數據庫表的基本操作,幾乎涵蓋了各種查詢操作所遇到的情況,值得大家親自操作一下,相信對大家的學習有所幫助。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved