CREATE PROCEDURE s_grade @sno char(20) AS BEGIN SELECT student.sno, student.sname, course.cname, sc.grade FROM student INNER JOIN sc ON student.sno = sc.sno INNER JOIN course ON sc.cno = course.cno END

时间: 2024-04-05 11:34:05 浏览: 132

sql语句练习题及答案

根据提供的信息，我们可以总结出以下相关的SQL知识点： ### 一、表的创建 #### 创建学生表 `Student` ```sql CREATE TABLE Student ( Sno CHAR(6) PRIMARY KEY, -- 学号 Sname VARCHAR(8), -- 姓名 Ssex CHAR(2), -- 性别 Sage SMALLINT, -- 年龄 Sdept VARCHAR(15) -- 系科 ); ``` #### 创建课程表 `Course` ```sql CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, -- 课程号 Cname VARCHAR(20), -- 课程名 Cpno CHAR(4), -- 先修课 Ccredit TINYINT -- 学分 ); ``` #### 创建选课表 `SC` ```sql CREATE TABLE SC ( Sno CHAR(6), -- 学号 Cno CHAR(4), -- 课程号 Grade DECIMAL(12, 2), -- 成绩 PRIMARY KEY (Sno, Cno) -- 联合主键 ); ``` ### 二、数据的插入与删除 #### 插入数据 ```sql INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('4001', '赵茵', '男', 20, 'SX'), ('4002', '杨华', '女', 21, 'JSJ'); ``` #### 删除数据 ```sql -- 删除指定的记录 DELETE FROM Student WHERE Sno = '4001'; -- 删除所有JSJ系的男生 DELETE FROM Student WHERE Sdept = 'JSJ' AND Ssex = '男'; ``` ### 三、表的删除与修改 #### 删除整个表 ```sql DROP TABLE Student; ``` #### 添加新字段 ```sql ALTER TABLE Student ADD Sbirthdate DATETIME; ``` ### 四、更新数据 #### 更新单条记录 ```sql UPDATE Student SET Sdept = 'JSJ' WHERE Sno = '0001'; ``` #### 更新多条记录 ```sql -- 把陈小明的年龄加1岁，性别改为女 UPDATE Student SET Sage = Sage + 1, Ssex = '女' WHERE Sname = '陈小明'; -- 修改李文庆的1001课程的成绩为93分 UPDATE SC SET Grade = 93 WHERE Sno = '李文庆' AND Cno = '1001'; -- 把“数据库原理”课的成绩减去1分 UPDATE SC SET Grade = Grade - 1 WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = '数据库原理'); ``` ### 五、查询语句 #### 单表查询 ```sql -- 查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列 SELECT Sno, Sname, Sage FROM Student WHERE Sage BETWEEN 19 AND 21 AND Ssex = '女' ORDER BY Sage DESC; -- 查询姓名中第2个字为“明”字的学生学号、性别 SELECT Sno, Ssex FROM Student WHERE Sname LIKE '_明%'; -- 查询1001课程没有成绩的学生学号、课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NULL AND Cno = '1001'; -- 查询JSJ、SX、WL系的年龄大于25岁的学生学号,姓名，结果按系排列 SELECT Sno, Sname FROM Student WHERE Sdept IN ('JSJ', 'SX', 'WL') AND Sage > 25 ORDER BY Sdept; -- 按10分制查询学生的sno,cno,10分制成绩 SELECT Sno, Cno, CASE WHEN Grade BETWEEN 1 AND 10 THEN 1 WHEN Grade BETWEEN 11 AND 20 THEN 2 WHEN Grade BETWEEN 30 AND 39 THEN 3 -- ... 依此类推 WHEN Grade BETWEEN 90 AND 100 THEN 10 END AS TenPointGrade FROM SC; -- 查询student表中的学生共分布在那几个系中 SELECT DISTINCT Sdept FROM Student; -- 查询0001号学生1001，1002课程的成绩 SELECT Sno, Cno, Grade FROM SC WHERE Sno = '0001' AND Cno IN ('1001', '1002'); ``` #### 统计 ```sql -- 查询姓名中有“明”字的学生人数 SELECT COUNT(*) FROM Student WHERE Sname LIKE '%明%'; -- 计算‘JSJ’系的平均年龄及最大年龄 SELECT AVG(Sage), MAX(Sage) FROM Student WHERE Sdept = 'JSJ'; -- 查询学生中姓名为张明、赵英的人数 SELECT COUNT(*) FROM Student WHERE Sname IN ('张明', '赵英'); -- 计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 SELECT Cno, SUM(Grade) AS Total, AVG(Grade) AS Average, MAX(Grade) AS Max, MIN(Grade) AS Min FROM SC GROUP BY Cno ORDER BY Average DESC; -- 计算1001,1002课程的平均分 SELECT Cno, AVG(Grade) FROM SC WHERE Cno IN ('1001', '1002') GROUP BY Cno; -- 查询平均分大于80分的学生学号及平均分 SELECT Sno, AVG(Grade) AS Average FROM SC GROUP BY Sno HAVING Average > 80; -- 统计选修课程超过2门的学生学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(Cno) > 2; -- 统计有10位成绩大于85分以上的课程号 SELECT Cno FROM SC WHERE Grade > 85 GROUP BY Cno HAVING COUNT(*) >= 10; -- 统计平均分不及格的学生学号 SELECT Sno FROM SC GROUP BY Sno HAVING AVG(Grade) < 60; -- 统计有大于两门课不及格的学生学号 SELECT Sno FROM SC WHERE Grade < 60 GROUP BY Sno HAVING COUNT(Cno) > 2; ``` #### 连接查询 ```sql -- 查询JSJ系的学生选修的课程号 SELECT S.Sno, C.Cno FROM Student S JOIN SC SC ON S.Sno = SC.Sno WHERE S.Sdept = 'JSJ'; -- 查询选修1002课程的学生的学生姓名 SELECT Sname FROM Student S JOIN SC SC ON S.Sno = SC.Sno WHERE SC.Cno = '1002'; -- 查询数据库原理不及格的学生学号及成绩 SELECT SC.Sno, SC.Grade FROM SC JOIN Course C ON SC.Cno = C.Cno WHERE C.Cname = '数据库原理' AND SC.Grade < 60; -- 查询选修“数据库原理”课且成绩80以上的学生姓名 SELECT Sname FROM Student S JOIN SC SC ON S.Sno = SC.Sno JOIN Course C ON SC.Cno = C.Cno WHERE C.Cname = '数据库原理' AND SC.Grade >= 80; -- 查询平均分不及格的学生的学号，姓名,平均分 SELECT S.Sno, S.Sname, AVG(SC.Grade) AS Average FROM Student S JOIN SC SC ON S.Sno = SC.Sno GROUP BY S.Sno, S.Sname HAVING AVG(SC.Grade) < 60; -- 查询女学生平均分高于75分的学生姓名 SELECT S.Sname FROM Student S JOIN SC SC ON S.Sno = SC.Sno WHERE S.Ssex = '女' GROUP BY S.Sname HAVING AVG(SC.Grade) > 75; -- 查询男学生学号、姓名、课程号、成绩 SELECT S.Sno, S.Sname, SC.Cno, SC.Grade FROM Student S LEFT JOIN SC SC ON S.Sno = SC.Sno WHERE S.Ssex = '男'; ``` #### 嵌套、相关及其他 ```sql -- 查询平均分不及格的学生人数 SELECT COUNT(*) FROM (SELECT Sno, AVG(Grade) AS Average FROM SC GROUP BY Sno HAVING AVG(Grade) < 60) AS SubQuery; -- 查询没有选修1002课程的学生的学生姓名 SELECT Sname FROM Student S WHERE S.Sno NOT IN (SELECT Sno FROM SC WHERE Cno = '1002'); -- 查询平均分最高的学生学号及平均分 SELECT Sno, AVG(Grade) AS Average FROM SC GROUP BY Sno HAVING AVG(Grade) = (SELECT MAX(Average) FROM (SELECT AVG(Grade) AS Average FROM SC GROUP BY Sno) AS SubQuery); -- 查询没有选修1001，1002课程的学生姓名 SELECT Sname FROM Student S WHERE S.Sno NOT IN (SELECT Sno FROM SC WHERE Cno IN ('1001', '1002')); -- 查询1002课程第一名的学生学号 SELECT Sno FROM SC WHERE Cno = '1002' ORDER BY Grade DESC LIMIT 1; -- 查询平均分前三名的学生学号 SELECT Sno FROM (SELECT Sno, AVG(Grade) AS Average FROM SC GROUP BY Sno) AS SubQuery ORDER BY Average DESC LIMIT 3; -- 查询JSJ系的学生与年龄不大于19岁的学生的差集 SELECT S.Sno FROM Student S WHERE S.Sdept = 'JSJ' EXCEPT SELECT S.Sno FROM Student S WHERE S.Sage <= 19; -- 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 SELECT S.Sno, S.Sname FROM Student S JOIN SC SC ON S.Sno = SC.Sno WHERE SC.Cno = '1001' AND SC.Grade > 90 UNION SELECT S.Sno, S.Sname FROM Student S JOIN SC SC ON S.Sno = SC.Sno GROUP BY S.Sno, S.Sname HAVING AVG(SC.Grade) > 85; -- 查询每门课程成绩都高于该门课程平均分的学生学号 SELECT S.Sno FROM Student S JOIN SC SC ON S.Sno = SC.Sno WHERE SC.Grade > (SELECT AVG(Grade) FROM SC WHERE Cno = SC.Cno) GROUP BY S.Sno HAVING COUNT(DISTINCT SC.Cno) = (SELECT COUNT(DISTINCT Cno) FROM Course); ``` 通过以上练习题及解答，我们可以看到SQL语言的强大功能，它不仅可以用来创建、修改和删除数据库中的表，还可以用来查询、统计和操作复杂的数据。这些练习题覆盖了SQL的基本语法以及高级查询技巧，对于学习SQL的人来说是非常好的实践机会。

这段代码是一个 SQL 存储过程，名为 s_grade，接收一个名为 @sno 的 char(20) 类型参数。该存储过程查询了学生表（student）、成绩表（sc）和课程表（course），通过内连接将它们关联起来，最终返回指定学号 @sno 的学生的所有课程成绩信息，包括学号（sno）、姓名（sname）、课程名（cname）和成绩（grade）。

阅读全文

CREATE PROCEDURE s_grade @sno char(20) AS BEGIN SELECT student.sno, student.sname, course.cname, sc.grade FROM student INNER JOIN sc ON student.sno = sc.sno INNER JOIN course ON sc.cno = course.cno END

相关推荐

SQLServer2000创建student、course和SC表教程

数据库与课程关联：创建Stu_Union和Course表及SC表的实验教程

CREATE VIEW V_SCG AS SELECT Sno,Sname,Cname,Score,Credit from student,grade,course WHERE grade.Sno = student.Sno AND grade.Cno = course.Cno AND Cno = '0001'; 哪里错了

CREATE VIEW scs_view_8208210915 AS SELECT s.Sno, s.Sname, c.Cno, c.Cname, r.Score FROM Students_8208210915 s, Courses_8208210915 c, Score_8208210915 r WHERE s.Sno = r.Sno AND c.Cno = r.Cno;

CREATE VIEW CS_Student_Grades(Sno,Sname,Cname,Grade) AS SELECT S.Sno,Students.Sname,Cname,Grade FROM Students S,Student_Grades SG WHERE S.Sno=SG.Sno AND Speciality='计算机科学与技术';报错为无法绑定由多个部分组成的标识符 "Students.Sname"，为什么

create view view136 (sno36，sname36，cname36，grade36) as select sc36.sno36，sname36，cname36，grade36 from student36,course36,sc36 where student36.sno36=sc36.sno36 and course36.cno36=sc36.cno36;有错误吗

名。 select sno,sname from student where ____________ (select ______ from sc ,course where _______________ and cname='数据库基础')

SELECT cName,score FROM course,sc WHERE sc.cNo = course.cNo AND sNo = (SELECT sNo FROM student WHERE sName = '王兵');

select sc.sno,sname from sc join students on sc.sno=students.sno where cno in (select cno from course where cname='操作系统' and '数据结构与算法');

USE teaching SELECT A.sno, sname, C.cno, cname, score FROM student A ,sc B , course C where B.cno=C.cno and A.sno=B.sno AND score>=90 该程序的功能是：

关系模式如下： Student(Sno，Sname，Ssex，Sage，Sdept) Course(Cno,Cname,Cpno,Ccredit) SC(Sno,Cno,Grade) 在begin...end之间填入SQL命令： 查询所有课程的名称和选修人数，包括无人选课的课程。

最新推荐

PHP语言基础知识详解及常见功能应用.docx

公司金融课程期末考试题目

适用于 Python 应用程序的 Prometheus 检测库.zip

DFC力控系统维护及使用

火炬连体网络在MNIST的2D嵌入实现示例

管理建模和仿真的文件

L2正则化的终极指南：从入门到精通，揭秘机器学习中的性能优化技巧

如何构建一个符合GB/T19716和ISO/IEC13335标准的信息安全事件管理框架，并确保业务连续性规划的有效性？

Angular插件增强Application Insights JavaScript SDK功能

"互动学习：行动中的多样性与论文攻读经历"

名。 select sno,sname from student where (select from sc ,course where _____ and cname='数据库基础')

关系模式如下： Student(Sno，Sname，Ssex，Sage，Sdept) Course(Cno,Cname,Cpno,Ccredit) SC(Sno,Cno,Grade) 在begin...end之间填入SQL命令：查询所有课程的名称和选修人数，包括无人选课的课程。