學生表教室表課程表sql
❶ 三張表查詢出選了兩門課以上的學生,學生表課程表選課表sql怎麼寫
學生表：student（學號：sno、姓名：sname、性別：sgender、年齡：sage）
課程表：couse （課程編號：cno、課程名稱：cname）
選課表：sc（學號：sno、課程編號：cno、成績：marks）
SELECT s.sno ,
s.sname ,
c.cname
FROM student s
INNER JOIN sc t1 ON t1.sno = s.sno
INNER JOIN couse c ON sc.cno = c.cno
WHERE s.sno IN ( SELECT t.sno
FROM sc t
GROUP BY t.sno
HAVING COUNT(t.cno) = 2 )
❷ SQL查詢，學生表，課程表，成績表的一個查詢要求如下怎麼寫呢
selects.sid,s.sname,sc.scorefromstudent,course,scwherestudent.sid=sc.sidandcourse.cid=sc.cidandcourse.cname='影視設計';
注意一下，你自己寫的sid和sname中間沒有逗號，到時候一定執行報錯
❸ 1．在學生資料庫中有三張數據表，即學生表、課程表和選課表，三張表的表數據如下，寫出創建學生表、課程
create table student(S_no char(9), S_name char(20), S_sex char(2), S_age int, S_department char(20))
create table course (C_no char(10), C_name char(20), C_teacher char(20) )
create table choice(S_no char(9), C_no char(10), S_score int)
insert into student(S_no, S_name, S_sex, S_age, S_department)
values('001', '李志強', '男', 20, '計算機系' )
insert into student(S_no, S_name, S_sex, S_age, S_department)
values('002', '張 亮', '男', 21 '建築工程系' )
insert into student(S_no, S_name, S_sex, S_age, S_department)
values('003', '李 平', '女', 19 '計算機系' )
insert into course(C_no, C_name, C_teacher )
values('C001', 'C語言', '王雅新' )
insert into course(C_no, C_name, C_teacher )
values('C002', '數據結構', '和海蓮' )
insert into course(C_no, C_name, C_teacher )
values('C003', 'SQL Server2000', '陳 紅' )
insert into choice(S_no, C_no, S_score)
values('001', 'C001', 83)
insert into choice(S_no, C_no, S_score)
values('001', 'C002', 80 )
insert into choice(S_no, C_no, S_score)
values('002', 'C001', 75)
insert into choice(S_no, C_no, S_score)
values('002', 'C004', 85)
insert into choice(S_no, C_no, S_score)
values('003', 'C002', 88)
insert into choice(S_no, C_no, S_score)
values('003', 'C003', 86)
❹ Student 學生表 ，Course 課程表 ，SC成績表 ，Teacher 教師表，sql操作運用
建表語句
CREATE TABLE student
(
s# INT,
sname nvarchar(32),
sage INT,
ssex nvarchar(8)
)
CREATE TABLE course
(
c# INT,
cname nvarchar(32),
t# INT
)
CREATE TABLE sc
(
s# INT,
c# INT,
score INT
)
CREATE TABLE teacher
(
t# INT,
tname nvarchar(16)
)
插入測試數據語句
insert into Student select 1,N'劉一',18,N'男' union all
select 2,N'錢二',19,N'女' union all
select 3,N'張三',17,N'男' union all
select 4,N'李四',18,N'女' union all
select 5,N'王五',17,N'男' union all
select 6,N'趙六',19,N'女'
insert into Teacher select 1,N'葉平' union all
select 2,N'賀高' union all
select 3,N'楊艷' union all
select 4,N'周磊'
insert into Course select 1,N'語文',1 union all
select 2,N'數學',2 union all
select 3,N'英語',3 union all
select 4,N'物理',4
insert into SC
select 1,1,56 union all
select 1,2,78 union all
select 1,3,67 union all
select 1,4,58 union all
select 2,1,79 union all
select 2,2,81 union all
select 2,3,92 union all
select 2,4,68 union all
select 3,1,91 union all
select 3,2,47 union all
select 3,3,88 union all
select 3,4,56 union all
select 4,2,88 union all
select 4,3,90 union all
select 4,4,93 union all
select 5,1,46 union all
select 5,3,78 union all
select 5,4,53 union all
select 6,1,35 union all
select 6,2,68 union all
select 6,4,71
問題
問題： 1、查詢「001」課程比「002」課程成績高的所有學生的學號； 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、查詢平均成績大於60分的同學的學號和平均成績； select S#,avg(score) from sc group by S# having avg(score) >60; 3、查詢所有同學的學號、姓名、選課數、總成績； 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、查詢姓「李」的老師的個數； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查詢沒學過「葉平」老師課的同學的學號、姓名； 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、查詢學過「001」並且也學過編號「002」課程的同學的學號、姓名； 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、查詢學過「葉平」老師所教的所有課的同學的學號、姓名； 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、查詢課程編號「002」的成績比課程編號「001」課程低的所有同學的學號、姓名； 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 <score; 9、查詢所有課程成績小於60分的同學的學號、姓名； select S#,Sname from Student where S# not in (select S.S# from Student AS S,SC where S.S#=SC.S# and score>60); 10、查詢沒有學全所有課的同學的學號、姓名； select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 11、查詢至少有一門課與學號為「1001」的同學所學相同的同學的學號和姓名； select distinct S#,Sname from Student,SC where Student.S#=SC.S# and SC.C# in (select C# from SC where S#='1001'); 12、查詢至少學過學號為「001」同學所有一門課的其他同學學號和姓名； select distinct SC.S#,Sname from Student,SC where Student.S#=SC.S# and C# in (select C# from SC where S#='001'); 13、把「SC」表中「葉平」老師教的課的成績都更改為此課程的平均成績； update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平'); 14、查詢和「1002」號的同學學習的課程完全相同的其他同學學號和姓名； select S# from SC where C# in (select C# from SC where S#='1002') group by S# having count(*)=(select count(*) from SC where S#='1002'); 15、刪除學習「葉平」老師課的SC表記錄； Delect SC from course ,Teacher where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平'; 16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號「003」課程的同學學號、2、 號課的平均成績； Insert SC select S#,'002',(Select avg(score) from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 17、按平均成績從高到低顯示所有學生的「資料庫」、「企業管理」、「英語」三門的課程成績，按如下形式顯示： 學生ID,,資料庫,企業管理,英語,有效課程數,有效平均分 SELECT S# as 學生ID ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 資料庫 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業管理 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英語 ,COUNT(*) AS 有效課程數, AVG(t.score) AS 平均成績 FROM SC AS t GROUP BY S# ORDER BY avg(t.score) 18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分 SELECT L.C# As 課程ID,L.score AS 最高分,R.score AS 最低分 FROM SC L ,SC AS R WHERE L.C# = R.C# and L.score = (SELECT MAX(IL.score) FROM SC AS IL,Student AS IM WHERE L.C# = IL.C# and IM.S#=IL.S# GROUP BY IL.C#) AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.C# = IR.C# GROUP BY IR.C# );
自己寫的:select c# ,max(score)as 最高分 ,min(score) as 最低分 from dbo.sc group by c# 19、按各科平均成績從低到高和及格率的百分數從高到低順序 SELECT t.C# AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績 ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分數 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、查詢如下課程平均成績和及格率的百分數(用"1行"顯示): 企業管理（001），馬克思（002），OO&UML （003），資料庫（004） SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企業管理平均分 ,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 企業管理及格百分數 ,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 馬克思及格百分數 ,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及格百分數 ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 資料庫平均分 ,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 資料庫及格百分數 FROM SC
21、查詢不同老師所教不同課程平均分從高到低顯示
SELECT max(Z.T#) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程ID,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績
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、查詢如下課程成績第 3 名到第 6 名的學生成績單：企業管理（001），馬克思（002），UML （003），資料庫（004）
[學生ID],[學生姓名],企業管理,馬克思,UML,資料庫,平均成績
SELECT DISTINCT top 3
SC.S# As 學生學號,
Student.Sname AS 學生姓名 ,
T1.score AS 企業管理,
T2.score AS 馬克思,
T3.score AS UML,
T4.score AS 資料庫,
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、統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]
SELECT SC.C# as 課程ID, Cname as 課程名稱
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
24、查詢學生平均成績及其名次
SELECT 1+(SELECT COUNT( distinct 平均成績)
FROM (SELECT S#,AVG(score) AS 平均成績
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成績 > T2.平均成績) as 名次,
S# as 學生學號,平均成績
FROM (SELECT S#,AVG(score) 平均成績
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成績 desc;
原文地址：http://www.cnblogs.com/qixuejia/p/3637735.html
❺ 從學生表、選課表、課程表，試用SQL語句表示下列查詢語句： （1）列出全部學生的信息。
1.
SELECT*
FROM學生表
2.
SELECT學號,姓名
FROM學生表
WHERE專業內='軟體測試專業'
3.
SELECT 課程號
FROM 課程表
WHERE 類容型='必修課'
❻ 用SQL創建一個學生表，課程表，學生成績表
第一個表是學生表Student，包含三個欄位，學生id（_id） 和 學生姓名（stu_name）和課程id（les_id）
Create Table [dbo].Student(
stu_id [uniqueidentifier] NOT NULL,
stu_name [nvarchar](425) NOT NULL,
les_id [uniqueidentifier] NOT NULL
)
第二個表課程表Lesson，兩個欄位，課程id（les_id）和課程名稱（les_name）
Create Table [dbo].Lesson(
les_id [uniqueidentifier] NOT NULL,
les_name [nvarchar](425) NOT NULL
)
第三個表學生成績表Score,三個欄位課程id（les_id），學生id（stu_id）和課程得分（les_score）
Create Table [dbo].Score(
les_id [uniqueidentifier] NOT NULL,
stu_id [uniqueidentifier] NOT NULL,
les_score [int] NOT NULL
)
❼ 已知三個關系模式(學生表,課程表,成績表)用SQL語句實現以下內容
CREATE TABLE 選課表
(學號 CHAR(8) REFERENCES 學生表(學號),
課程編號 CHAR(6) REFERENCES 課程表(課程編號),
成績 smallint)
GO
INSERT INTO 學生表 VALUES('20100101','李丹','女','1993-6-6','管理系')
GO
SELECT 學號,姓名,所在系
FROM 學生表
GO
SELECT 學生表.學號,姓名
FROM 學生表 JOIN 選課表 ON 學生表.學號=選課表.學號
WHERE 課程編號='A01-02'
GO
SELECT *
FROM 學生表
WHERE 姓名 LIKE '[趙李張]%'
GO
SELECT 課程名稱,COUNT(*) AS 選修總人數
INTO 修課統計
FROM 課程表 JOIN 選課表 ON 課程表.課程編號=選課表.課程編號
GROUP BY 課程名稱
❽ 有四張表：Student 學生表 ，Course 課程表 ，SC成績表 ，Teacher 教師表，求SQL語句
把表結構發來，我給你寫
❾ 有四張表：Student 學生表 ，Course 課程表 ，SC成績表 ，Teacher 教師表，求SQL語句
select sc.* from sc,course where sc.課程號=course.課程號 and course.課程名='3-105' and sc.成績 between 60 and 80;
select sc.* from sc where sc.成績 in(85,56,88);
3. 這個有疑問
我的理解是，學生參加了多門考試，所有成回績都在70-90之間的。答取這部分人的學號。
select 學號 from (select 學號，max(成績) 最高分，min(成績) 最低分 from sc group by 學號) a where a.最高分 < 90 and a.最低分 >70
❿ 用SQL語句實現：學生表、課程表、選課表三張表中的問題：
用子查詢
select 姓名
from 學生,選課,課程
where 學生.學號=選課.學號 and 選課.課程號=課程.課程號 and 課程.課程名='資料庫'
and 學生.學號 in
(select 學生.學號
from 學生,選課,課程
where 學生.學號=選課.學號 and 選課.課程號=課程.課程號 and 課程.課程名='操作系統';
}