実験3 sql severデータクエリー操作

実験目的:
1.基本的なクエリー、ネストされたサブクエリー、および接続クエリーを把握する2.データベースの一部の予約文字の使用を学習する3.部分統計関数の使用を学習する.
実験要求:
1.基本的なクエリー、ネストされたサブクエリーと接続クエリーを熟知する2.各種クエリーの異同と相互間の変換を体得し、各種クエリーの実行過程を体得し、総合応用のために良好な基礎を築く.
実験内容:
1.投影クエリー:StudentテーブルのすべてのレコードのSName、Sex、Class列をクエリーし、クエリー結果に表示される列名(SName->名前、Sex->性別、Class->クラス)を変更します.2.簡単な照会:SCテーブルの成績が60～80のすべてのレコードを照会します.Studentテーブルのすべての王という学生の記録を調べます.クエリSCテーブルの成績が85、86、88または90のレコード.Studentテーブルの「95031」クラスまたは性別が「女性」の学生記録を検索します.照会SC表では、課程を選択したが試験を受けていない学生番号を選択する.3.クエリー結果のソート:SCテーブルのすべてのレコードをクエリーし、Cno昇順、Score降順で表示します.4.セット関数の使用:統計Studio表の「95031」クラスの学生数を照会します.クエリ統計SCテーブルでは、「3-105」号課程の平均点.5.データのグループ化:SC表の選択人数が4人より多く、課程番号が3で始まる課程の平均成績を調べる.少なくとも2つの課程の成績が80点以上の学生の学号を調べる.6.表の接続クエリー:少なくとも2つ以上のコースを選択した学生の学号、名前、クラスをクエリーします.選択されていない学生を含むすべての学生の学号、名前(SName)、その学生が選択した授業の平均点を検索します.ヒント:外部接続7.ネストされたクエリー:(次のセクションではネストされたクエリーで完了する必要があります)Studentテーブルの「105」番の学生と同じクラスの学生のSNo、SName、BirthDateをクエリーします.選択科目名が「高等数学」の学生番号、名前を照会します.選択した学生数が4人より多いある課程の担任教師の名前を調べる.成績がこの科目の平均点を下回る学生の学号を調べる.すべての担任教師のTnameとDepIDを検索します.すべての課程を選択した学生の学号、名前、年齢の照会李誠先生が授与した課程を選択していない学生の学号、名前、年齢8.UNION操作:すべての教師と学生の番号、名前、性別を照会する.
関連コード

use JiaoXue
go
--    
-- Student       SName、Sex Class ,              
select SName   ,sex   ,Class    from student


--    :
--  SC     60 80       
select * from sc  where Score between 60 and 80

-- Student           
select * from student where SName like' %'


-- SC     85、86、88 90   
select * from sc where Score=85 or Score=86 or Score=88 or Score =90
-- 
select sno,CNo  ,score from SC where score in(85,86,88,90)

--  Student  “95031”     “ ”     
select * from student  where Class=95031 or Sex =' '
-- 
select * from student where class=95031
union
select * from student where sex=' '

--  SC  ，                 
select * from sc;
select sno from sc where score is null


--    
--  SC       ，   Cno  、Score    
select sno     ,CNo      ,score      from SC order by CNo asc ,score desc


--      
--    Student  “95031”     
select count( sno) as     from student where class=95031
select count(distinct sno) as     from student where class=95031

--    SC  ，“3-105”       
select AVG(score) as      from SC where CNo='3-105'


--    
--  SC        4 ，      3          
select CNo, AVG(score)      from SC where CNo like '3%' 
group by CNo having count(sno)>4

--             80        
select SNo from sc where Score>80 group by SNo having COUNT (*)>=2

--    
--l                  、       

select distinct s.sno,sname,class 
    from (select sc1.sno from sc sc1,sc sc2 where sc1.sno=sc2.sno and sc1.cno<>sc2.cno) as scc,student s
    where s.sno=scc.sno
-- 
select SNo ,SName,class from student 
    where SNo in(select Sno from sc group by Sno  having COUNT (*)>=2)

select * from sc
select * from student

--      （        ）   、  （SName）、           
select st.SNo   ,SName   ,avgScore    
 from student st left  outer join 
 (select SNo,avg(Score)avgScore from sc group by SNo )scc 
 on scc.SNo=st. SNo


--    ：
--  Student   “105”             SNo，SName BirthDate
select SNo ,SName  ,BirthDate 
from student 
where Class in (select Class  from student  where SNo ='105')and SNo ! ='105'

--        “    ”     、  。

insert into sc (SNo ,CNo)
values (103,'9-888')

select sno,sname from student s
where exists(select * from sc ,Course c where sc.SNo=s.SNo and sc.CNo=c.CNo and CName ='    ');
-- 
select sno,sname from student
where SNo in( select SNo from sc ,Course  where sc.CNo=Course.CNo and CName='    ');

-- 
select SNo ,SName from student  where SNo in(select SNo from sc where CNo in (select CNo from Course where CName ='    ') )

select s.SNo ,SName ,CName from student s ,sc ,Course c where s.SNo=sc.SNo and sc.CNo=c.CNo


delete from sc where SNo =103 and CNo ='9-888'


--          4            
select TName from teacher where Tno in 
(select TNo from course where CNo in
(select CNo from sc group by CNo having count(*)>4))

--      
select tname from teacher t ,course c,
(select sc.CNo from SC sc group by sc.CNo having count(sc.sno)>=4) sc2 where t.tno=c.tno and sc2.CNo=c.CNo



select TName     ,count(*)     from Teacher t 
    inner join Course c on c.Tno=t.TNo
    inner join sc on sc.CNo=c.CNo
    inner join student s on s.SNo=sc.SNo 
    group by TName



--         TName DepID
select distinct tname,DepID from teacher t where t.tno in(select c.tno from course c where c.cno in(select sc.cno from sc))



--                  

select sc1.sno ,t.sname,sc1.score,sc3.average 
    from SC sc1,student t,
        (select sc2.CNo,avg(score) as average from SC sc2 group by sc2.CNo) sc3
            where sc1.CNo=sc3.CNo and sc1.sno=t.sno and sc1.scoreselect CName,average,SNo,SCore
    from Course c,sc,
    (select CNo,AVG(score) average from sc group by CNo) sc2
    where c.CNo=sc2.CNo and sc.CNo =sc2.CNo order by CName 


--               ，  ，  
select sno,sname,datediff(yy,birthdate,getdate()) as age 
from student
where not exists
    (select * from course
    where not exists
        (select * from sc
            where student.sno=sc.sno and course.cno=sc.cno))


select SNo ,CNo from sc order by SNo


--                    、  、  
select sno,sname,datediff(yy,birthdate,getdate()) as age 
from student s

where not exists
    (select * from course c,teacher t,sc where sc.sno=s.sno and c.cno=sc.cno
                and c.tno=t.tno and tname='  ')

--      、 、 
--            、  、  
select sno,sname,sex  from student
union
select tno,tname,sex from teacher

select SNo from sc where sc.CNo='3-105'
union all
select SNo from sc where sc.CNo='3-245'


select SNo from sc where sc.CNo='3-105'
except
select SNo from sc where sc.CNo='3-245'

select SNo from sc where sc.CNo='3-105'
intersect
select SNo from sc where sc.CNo='3-245'