delete from sc from sc join student on sc.sno=student.sno from sc join course on sc.cno=course.cno where student.sdept ='计算机系'and grade <60 and cnamne='java'对不对

时间: 2023-11-27 21:50:21 浏览: 109

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 语句有语法错误，正确的语句应该是： ```sql DELETE sc FROM sc JOIN student ON sc.sno = student.sno JOIN course ON sc.cno = course.cno WHERE student.sdept = '计算机系' AND sc.grade < 60 AND course.cname = 'java'; ``` 这条 SQL 语句的作用是删除选修了“java”课程且所在学生所在院系为“计算机系”且该门课程的成绩低于60分的记录。

阅读全文

delete from sc from sc join student on sc.sno=student.sno from sc join course on sc.cno=course.cno where student.sdept ='计算机系'and grade <60 and cnamne='java'对不对

相关推荐

SQLServer2000创建student、course和SC表教程

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

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

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

解释一下SELECT s.Sno AS 学号, s.Sname AS 姓名 FROM Student s INNER JOIN SC ON s.Sno = SC.Sno WHERE SC.Cno NOT IN ( SELECT SC.Cno AS 课程号 FROM SC, Student s WHERE SC.Sno = s.Sno AND s.Sname = '刘晨' );

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'; 哪里错了

select s.* from student s inner join sc ,course c ,teacher t on sc.SNO =s.SNO and c.CNO =sc.CNO and t.TNO =c.TNO where t.TNAME = '张三' order by sc.SCORE desc limit 1,1 这串sql有什么问题，怎么解决

解释一下select Sname from student where not exists( select * from course where course.Cno in (1,2) and not exists( SELECT * from sc where Sno=student.Sno AND Cno=course.Cno ) )

CREATE PROCEDURE Proc_Selesno ( VARIABLE student_id INT ) BEGIN SELECT s.*, c.Cname, sc.grade FROM S s INNER JOIN SC sc ON s.sno = sc.sno INNER JOIN C c ON sc.cno = c.cno WHERE s.sno = student_id; END

select Sno,Sname from Student where Sno in(select distinct Sno from SC s1 where not exists(select * from SC s2 where s2.Sno='200215121' and not exists(select * from SC s3 where s1.Sno=s3.Sno and s2.Cno=s3.Cno))

select sname from student where not exists (select * from course where not exists (select * from sc where sno=student.sno and Cno=course.cno));

SELECT Cno FROM course SQLEXCEPTION SELECT Cno from student , sc where student.Sno = sc.Sno AND student.Sname='李丽' 我的这个代码有什么问题吗

sqlserver中，将下列语句转换为使用exist语句完成的语句： SELECT Sno FROM SC,Course where SC.Cno=Course.Cno Group by Sno having count(*)=(select count(*) from Course)

SELECT DISTINCT sno FROM SC WHERE NOT EXISTS ( SELECT cno FROM SC AS T WHERE T.sno = '20201234567' AND NOT EXISTS( SELECT * FROM SC AS TT WHERE sno = TT.sno AND T.cno = TT.cno) );

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 该程序的功能是：

最新推荐

整体风格与设计理念 整体设计风格简约而不失优雅，采用了简洁的线条元素作为主要装饰，营造出一种现代、专业的视觉感受 配色上以柔和的色调为主，搭配少量鲜明的强调色，既保证了视觉上的舒适感，又能突出重点内容

PureMVC AS3在Flash中的实践与演示：HelloFlash案例分析

管理建模和仿真的文件

YRC1000 EtherNet_IP通信协议：掌握连接与数据交换的6个关键策略

如何设置 OpenFileDialog 用户只能在固定文件夹及其子文件夹里选择文件

掌握Makefile多目标编译与清理操作

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

模拟IC设计在无线通信中的五大机遇与四大挑战深度解读

STM32-407芯片定时器控制与系统时钟管理

sqlserver中，将下列语句转换为使用exist语句完成的语句： SELECT Sno FROM SC,Course where SC.Cno=Course.Cno Group by Sno having count()=(select count() from Course)

整体风格与设计理念整体设计风格简约而不失优雅，采用了简洁的线条元素作为主要装饰，营造出一种现代、专业的视觉感受配色上以柔和的色调为主，搭配少量鲜明的强调色，既保证了视觉上的舒适感，又能突出重点内容