sql语句练习50题

合集下载

sql 五十题

sql 五十题以下是一些SQL练习题，共50题：1. 什么是SQL？2. 什么是关系型数据库？3. 什么是主键？4. 什么是外键？5. 什么是索引？6. 什么是查询？7. 什么是插入操作？8. 什么是更新操作？9. 什么是删除操作？10. 什么是聚合函数？11. 什么是分组操作？12. 什么是排序操作？13. 什么是联接操作？14. 什么是子查询？15. 什么是视图？16. 什么是存储过程？17. 什么是触发器？18. 如何使用WHERE子句进行过滤？19. 如何使用ORDER BY子句进行排序？20. 如何使用GROUP BY子句进行分组？21. 如何使用HAVING子句进行分组过滤？22. 如何使用JOIN操作联接表？23. 如何使用聚合函数SUM、AVG、MIN、MAX？24. 如何使用聚合函数COUNT、COUNT()和COUNT(列名)的区别？25. 如何使用子查询嵌套查询？26. 如何创建视图并查询视图数据？27. 如何创建存储过程并调用存储过程？28. 如何创建触发器并触发触发器？29. 如何使用INSERT INTO语句插入数据？30. 如何使用UPDATE语句更新数据？31. 如何使用DELETE语句删除数据？32. 如何使用TRUNCATE TABLE语句清空表数据？33. 如何使用UNION操作符合并多个查询结果？34. 如何使用UNION ALL操作符合并多个查询结果（包括重复行）？35. 如何使用IN操作符筛选符合条件的多个值？36. 如何使用LIKE操作符进行模糊查询？37. 如何使用NOT操作符进行否定筛选？38. 如何使用BETWEEN操作符筛选范围值？39. 如何使用EXISTS操作符检查子查询结果是否存在？40. 如何使用NOT EXISTS操作符检查子查询结果是否存在（否定筛选）？41. 如何使用CASE语句进行条件判断和值转换？42. 如何使用DISTINCT关键字去除查询结果的重复行？43. 如何使用LIMIT关键字限制查询结果的行数？44. 如何使用JOIN操作连接多个表并筛选数据？45. 如何使用INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN的区别和用法？46. 如何使用视图提高查询效率和维护数据安全性？47. 如何使用存储过程存储复杂的业务逻辑和重复使用的查询语句？48. 如何使用触发器自动执行特定事件或数据修改操作？49. 如何优化SQL查询性能，提高数据库的响应速度？50. 如何安全地管理和维护数据库，确保数据的完整性和可靠性？。

SQL基础知识题库100道及答案(完整版)

SQL基础知识题库100道及答案(完整版)1. 在SQL 中，用于创建表的语句是（）A. CREATE TABLEB. INSERT INTOC. UPDATED. DELETE答案：A2. 以下哪个关键字用于在SQL 中添加数据（）A. ADDB. INSERTC. APPENDD. PUT答案：B3. 在SQL 中，用于从表中检索数据的语句是（）A. SELECTB. GETC. FETCHD. REQUEST答案：A4. 以下哪个关键字用于在SQL 中更新数据（）A. MODIFYB. CHANGEC. UPDATED. REPLACE答案：C5. 要从表中删除数据，应使用的SQL 语句是（）A. DROPB. DELETEC. REMOVED. CLEAR答案：B6. SQL 中用于创建索引的关键字是（）A. CREATE INDEXB. MAKE INDEXC. BUILD INDEXD. ESTABLISH INDEX答案：A7. 以下哪种数据类型用于存储整数（）A. INTB. FLOATC. VARCHARD. DATE答案：A8. 用于存储字符串的常见数据类型是（）A. CHARB. INTC. DECIMALD. DOUBLE答案：A9. 在SQL 中，用于对结果集进行排序的关键字是（）A. SORTB. ORDER BYC. ARRANGED. ALIGN答案：B10. 以下哪个运算符用于等于比较（）A. =B. ==C. <>D. >答案：A11. 用于不等于比较的运算符是（）A.!=B. < >C. NOT EQUALSD. DIFFERENT答案：B12. 以下哪个运算符用于大于比较（）A. >B. >=C. <D. <=答案：A13. 用于小于比较的运算符是（）A. <B. <=C. >D. >=答案：A14. 以下哪个关键字用于在SQL 中进行分组操作（）A. GROUP BYB. SORT BYC. CLASSIFY BYD. CATEGORIZE BY答案：A15. 聚合函数SUM 用于（）A. 计算总和B. 计算平均值C. 计算数量D. 计算最小值答案：A16. 聚合函数AVG 用于（）A. 计算总和B. 计算平均值C. 计算数量D. 计算最大值答案：B17. COUNT 函数用于（）A. 计算总和B. 计算平均值C. 计算数量D. 计算最小值答案：C18. MAX 函数用于（）A. 计算总和B. 计算平均值C. 计算最大值D. 计算最小值答案：C19. MIN 函数用于（）A. 计算总和B. 计算平均值C. 计算最大值D. 计算最小值答案：D20. 在SQL 中，用于连接两个表的关键字是（）A. JOINB. CONNECTC. LINKD. BIND答案：A21. 内连接使用的关键字是（）A. INNER JOINB. LEFT JOINC. RIGHT JOIND. FULL JOIN答案：A22. 左连接使用的关键字是（）A. INNER JOINB. LEFT JOINC. RIGHT JOIND. FULL JOIN答案：B23. 右连接使用的关键字是（）A. INNER JOINB. LEFT JOINC. RIGHT JOIND. FULL JOIN答案：C24. 全连接使用的关键字是（）A. INNER JOINB. LEFT JOINC. RIGHT JOIND. FULL JOIN答案：D25. 在SQL 中，用于限制结果集行数的关键字是（）A. LIMITB. RESTRICTC. BOUNDD. CONSTRAINT答案：A26. 以下哪个子句用于在SQL 中进行条件筛选（）A. WHEREB. HAVINGC. FROMD. GROUP BY答案：A27. HAVING 子句通常与（）一起使用A. GROUP BYB. ORDER BYC. WHERED. FROM答案：A28. 在SQL 中，用于创建视图的语句是（）A. CREATE VIEWB. MAKE VIEWC. BUILD VIEWD. ESTABLISH VIEW 答案：A29. 以下哪个关键字用于删除视图（）A. DROP VIEWB. DELETE VIEWC. REMOVE VIEWD. CLEAR VIEW 答案：A30. 要在SQL 中添加注释，可以使用（）A. //B. /* */C. #D. --答案：D31. 在SQL 中，以下哪种数据类型用于存储日期和时间（）A. DATEB. TIMEC. DATETIMED. TIMESTAMP答案：C32. 用于提取日期部分的函数是（）A. DATEPART()B. DAY()C. MONTH()D. YEAR()答案：A33. 以下哪个函数用于计算字符串的长度（）A. LENGTH()B. SIZE()C. COUNT()D. LEN()答案：A34. 在SQL 中，用于将字符串转换为大写的函数是（）A. UPPER()B. TO_UPPER()C. CAPITALIZE()D. BIGCASE()答案：A35. 用于将字符串转换为小写的函数是（）A. LOWER()B. TO_LOWER()C. SMALLCASE()D. DECAPITALIZE()答案：A36. 以下哪个函数用于去除字符串两端的空格（）A. TRIM()B. CLEAN()C. REMOVE_SPACES()D. STRIP()答案：A37. 在SQL 中，用于执行事务的语句是（）A. BEGIN TRANSACTIONB. START TRANSACTIONC. OPEN TRANSACTIOND. INITIATE TRANSACTION答案：A38. 提交事务使用的语句是（）A. COMMITB. SUBMITC. CONFIRMD. VALIDATE答案：A39. 回滚事务使用的语句是（）A. ROLLBACKB. REVERTC. CANCELD. ABORT答案：A40. 以下哪个关键字用于在SQL 中创建存储过程（）A. CREATE PROCEDUREB. MAKE PROCEDUREC. BUILD PROCEDURED. ESTABLISH PROCEDURE答案：A41. 调用存储过程使用的语句是（）A. EXECUTEB. CALLC. INVOKED. RUN答案：B42. 在SQL 中，用于删除存储过程的语句是（）A. DROP PROCEDUREB. DELETE PROCEDUREC. REMOVE PROCEDURED. CLEAR PROCEDURE答案：A43. 以下哪个关键字用于在SQL 中创建触发器（）A. CREATE TRIGGERB. MAKE TRIGGERC. BUILD TRIGGERD. ESTABLISH TRIGGER44. 删除触发器使用的语句是（）A. DROP TRIGGERB. DELETE TRIGGERC. REMOVE TRIGGERD. CLEAR TRIGGER答案：A45. 在SQL 中，用于授予权限的语句是（）A. GRANTB. ALLOWC. PERMITD. AUTHORIZE答案：A46. 收回权限使用的语句是（）A. REVOKEB. DENYC. REFUSED. FORBID答案：A47. 以下哪个关键字用于在SQL 中创建索引的唯一性约束（）A. UNIQUEB. PRIMARY KEYC. FOREIGN KEYD. CHECK答案：A48. 用于定义主键约束的关键字是（）A. PRIMARY KEYB. UNIQUE KEYC. FOREIGN KEYD. INDEX KEY答案：A49. 外键约束使用的关键字是（）A. FOREIGN KEYB. OUTER KEYC. RELATED KEYD. REFERENCED KEY答案：A50. 以下哪个约束用于检查数据的有效性（）A. CHECKB. VALIDATEC. INSPECTD. VERIFY答案：A51. 在SQL 中，以下哪个语句用于创建数据库（）A. CREATE DATABASEB. MAKE DATABASEC. BUILD DATABASED. ESTABLISH DATABASE 答案：A52. 要删除数据库，应使用的语句是（）A. DROP DATABASEB. DELETE DATABASEC. REMOVE DATABASED. CLEAR DATABASE 答案：A53. 以下哪个关键字用于在SQL 中切换数据库（）A. USEB. SELECT DBC. CHANGE DBD. SWITCH DB答案：A54. 在SQL 中，以下哪种操作可以对多个表同时进行（）A. 联合查询B. 子查询C. 交叉连接D. 内连接55. 子查询可以在以下哪个子句中使用（）A. SELECTB. FROMC. WHERED. 以上都可以答案：D56. 以下哪个语句用于在SQL 中创建用户（）A. CREATE USERB. MAKE USERC. BUILD USERD. ESTABLISH USER答案：A57. 删除用户使用的语句是（）A. DROP USERB. DELETE USERC. REMOVE USERD. CLEAR USER答案：A58. 在SQL 中，用于修改表结构的语句是（）A. ALTER TABLEB. MODIFY TABLEC. CHANGE TABLED. UPDATE TABLE答案：A59. 以下哪个操作可以添加列到表中（）A. ADD COLUMNB. INSERT COLUMNC. APPEND COLUMND. PUT COLUMN答案：A60. 要删除表中的列，应使用（）A. DROP COLUMNB. DELETE COLUMNC. REMOVE COLUMND. CLEAR COLUMN答案：A61. 在SQL 中，以下哪个关键字用于对结果集进行分页（）A. PAGEB. PAGINGC. OFFSETD. LIMIT答案：D62. 以下哪个函数用于返回当前日期（）A. CURDATE()B. NOW()C. CURRENT_DATE()D. TODAY()答案：C63. 用于返回当前时间的函数是（）A. CURTIME()B. NOW()C. CURRENT_TIME()D. THIS_TIME()答案：C64. 在SQL 中，以下哪个关键字用于为表中的列设置默认值（）A. DEFAULTB. INITIALC. BASED. START答案：A65. 以下哪个语句用于在SQL 中重命名表（）A. RENAME TABLEB. MODIFY TABLE NAMEC. CHANGE TABLE NAMED. UPDATE TABLE答案：A66. 要获取表的结构信息，可以使用以下哪个语句（）A. DESCRIBE TABLEB. SHOW TABLE STRUCTUREC. EXPLAIN TABLED. GET TABLE DETAILS答案：A67. 在SQL 中，以下哪个关键字用于在查询结果中去除重复行（）A. DISTINCTB. UNIQUEC. SINGLED. ONLY答案：A68. 以下哪个函数用于对字符串进行拼接（）A. CONCAT()B. JOIN()C. MERGE()D. COMBINE()答案：A69. 在SQL 中，用于创建临时表的关键字是（）A. TEMPORARY TABLEB. TEMP TABLEC. TRANSIENT TABLED. SHORT_LIVED TABLE答案：A70. 以下哪个语句用于在SQL 中为列添加注释（）A. COMMENT ON COLUMNB. NOTE ON COLUMNC. REMARK ON COLUMND. EXPLAIN COLUMN答案：A71. 在SQL 中，以下哪个关键字用于在子查询中引用外部查询的结果（）A. CORRELATEDB. RELATEDC. CONNECTEDD. LINKED答案：A72. 以下哪个操作符用于在SQL 中进行范围查询（）A. BETWEENB. INC. LIKED. EXISTS答案：A73. 用于模糊匹配的操作符是（）A. LIKEB. SIMILARC. MATCHD. CLOSE_TO答案：A74. 在SQL 中，以下哪个关键字用于对查询结果进行排序时按照多个列进行（）A. ORDER BY MULTIPLEB. SORT BY SEVERALC. ORDER BY MORE THAN ONED. ORDER BY MULTIPLE COLUMNS答案：D75. 以下哪个函数用于返回字符串的子串（）A. SUBSTRING()B. PART()C. SEGMENT()D. SLICE()答案：A76. 在SQL 中，以下哪个关键字用于在存储过程中定义输入参数（）A. INB. OUTC. INOUTD. PARAMETER答案：A77. 用于定义输出参数的关键字是（）A. INB. OUTC. INOUTD. PARAMETER答案：B78. 以下哪个关键字用于在存储过程中定义既可以输入又可以输出的参数（）A. INB. OUTC. INOUTD. PARAMETER答案：C79. 在SQL 中，以下哪个关键字用于在创建表时指定自增列（）A. AUTO_INCREMENTB. SELF_INCREMENTC. AUTO_GROWD. SELF_GROW答案：A80. 以下哪个语句用于在SQL 中创建序列（）A. CREATE SEQUENCEB. MAKE SEQUENCEC. BUILD SEQUENCED. ESTABLISH SEQUENCE 答案：A81. 要获取序列的下一个值，可以使用以下哪个函数（）A. NEXTVAL()B. GET_NEXT()C. NEXT_VALUE()D. FOLLOWING_VALUE()答案：A82. 在SQL 中，以下哪个关键字用于锁定表（）A. LOCKB. HOLDC. FREEZED. BLOCK答案：A83. 以下哪种锁类型用于防止其他事务读取或修改数据（）A. 共享锁B. 排他锁C. 意向共享锁D. 意向排他锁答案：B84. 共享锁允许其他事务（）A. 读取数据B. 修改数据C. 删除数据D. 以上都不行答案：A85. 在SQL 中，以下哪个关键字用于解锁表（）A. UNLOCKB. RELEASEC. FREED. UNBIND答案：A86. 以下哪个函数用于计算两个日期之间的天数差（）A. DATEDIFF()B. DATE_DIFFERENCE()C. DAY_DIFFERENCE()D. TIME_DIFFERENCE()答案：A87. 在SQL 中，以下哪个关键字用于在查询中使用别名（）A. ASB. LIKEC. SAME ASD. EQUALS答案：A88. 以下哪个语句用于在SQL 中创建索引的唯一约束（）A. UNIQUE INDEXB. PRIMARY INDEXC. FOREIGN INDEXD. CHECK INDEX答案：A89. 要在SQL 中创建全文索引，应使用（）A. FULLTEXT INDEXB. COMPLETE TEXT INDEXC. ALL_TEXT INDEXD. WHOLE_TEXT INDEX 答案：A90. 在SQL 中，以下哪个关键字用于在存储过程中声明变量（）A. DECLAREB. DEFINEC. STATED. ANNOUNCE答案：A91. 以下哪个语句用于在SQL 中为变量赋值（）A. SETB. ASSIGNC. GIVED. PUT答案：A92. 在SQL 中，以下哪个关键字用于在存储过程中进行条件判断（）A. IFB. WHENC. CASED. CHECK答案：A93. 以下哪种语句用于在SQL 中进行循环操作（）A. FORB. WHILEC. LOOPD. 以上都是答案：D94. 在SQL 中，用于退出循环的语句是（）A. BREAKB. EXITC. STOPD. END答案：A95. 以下哪个函数用于将数字转换为字符串（）A. CAST()B. CONVERT()C. TO_STRING()D. NUM_TO_STR()答案：B96. 在SQL 中，用于获取当前会话的用户名称的函数是（）A. CURRENT_USERB. SYSTEM_USERC. LOGGED_IN_USERD. SESSION_USER答案：A97. 以下哪个关键字用于在SQL 中创建存储函数（）A. CREATE FUNCTIONB. MAKE FUNCTIONC. BUILD FUNCTIOND. ESTABLISH FUNCTION 答案：A98. 要删除存储函数，应使用的语句是（）A. DROP FUNCTIONB. DELETE FUNCTIONC. REMOVE FUNCTIOND. CLEAR FUNCTION答案：A99. 在SQL 中，用于获取数据库版本信息的函数是（）A. VERSION()B. DB_VERSION()C. DATABASE_VERSION()D. SYSTEM_VERSION()答案：A100. 以下哪个操作可以在SQL 中对表进行重命名（）A. RENAME TABLEB. MODIFY TABLE NAMEC. CHANGE TABLE NAMED. UPDATE TABLE NAME答案：A。

50条数据库sql语句及答案

(select count(courseid) from tblCourse where teaid = (
select teaid from tblTeacher where teaname = '叶平')))
--练习
select stuid from tblScore where courseid in (
--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
------运用连接查询
SELECT DISTINCT ST.* FROM tblStudent ST, tblScore SC WHERE ST.STUID = SC.STUID
AND SC.COURSEID IN
select stuid from tblScore GROUP BY STUID HAVING max(SCORE)<60)
--10、查询没有学全所有课的同学的学号、姓名；
--练习
SELECT TS.STUID,TS.STUNAME,COUNT(TSC.SCORE) FROM tblStudent TS
TC.COURSEID INNER JOIN tblTeacher TT ON TC.TEAID = TT.TEAID
WHERE TT.TEANAME ='叶平')
--对
SELECT STUID, STUNAME FROM tblStudent WHERE STUID NOT IN(
where A.SCORE > B.SCORE
--老师讲的方法
SELECT T.* FROM tblScore T WHERE T.COURSEID = 1 AND

MySQL-50道经典sql题汇总

MySQL-50道经典sql题汇总题⽬是根据⾃⼰的理解做的，都能跑出来，但是肯定还有不⾜之处，欢迎⼤家提出问题，⼀起研究学习建表DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`CId` varchar(10) default NULL,`Cname` varchar(10) default NULL,`TId` varchar(10) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for sc-- ----------------------------DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` (`SId` varchar(10) default NULL,`CId` varchar(10) default NULL,`score` int(4) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`SId` varchar(10) default NULL,`Sname` varchar(20) default NULL,`Sage` date default NULL,`Ssex` varchar(10) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for teacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`TId` varchar(10) default NULL,`Tname` varchar(20) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;添加数据courseINSERT INTO `course` VALUES ('01', 'CHINESE', '02');INSERT INTO `course` VALUES ('02', 'MATH', '01');INSERT INTO `course` VALUES ('03', 'ENGLISH', '03');INSERT INTO `course` VALUES ('04', 'JAVA', '04');scINSERT INTO `sc` VALUES ('01', '01', 80);INSERT INTO `sc` VALUES ('01', '03', 99);INSERT INTO `sc` VALUES ('02', '04', 50);INSERT INTO `sc` VALUES ('02', '03', 80);INSERT INTO `sc` VALUES ('03', '01', 80);INSERT INTO `sc` VALUES ('03', '03', 80);INSERT INTO `sc` VALUES ('04', '01', 50);INSERT INTO `sc` VALUES ('04', '03', 20);INSERT INTO `sc` VALUES ('05', '01', 100);INSERT INTO `sc` VALUES ('06', '01', 31);INSERT INTO `sc` VALUES ('06', '03', 34);INSERT INTO `sc` VALUES ('07', '03', 98);INSERT INTO `sc` VALUES ('07', '04', 55);INSERT INTO `sc` VALUES ('01', '04', 50);INSERT INTO `sc` VALUES ('01', '02', 10);INSERT INTO `sc` VALUES ('02', '02', 70);INSERT INTO `sc` VALUES ('03', '02', 50);INSERT INTO `sc` VALUES ('04', '02', 80);INSERT INTO `sc` VALUES ('05', '02', 73);INSERT INTO `sc` VALUES ('06', '02', 99);INSERT INTO `sc` VALUES ('07', '02', 100);INSERT INTO `sc` VALUES ('08', '02', 59);INSERT INTO `sc` VALUES ('09', '02', 80);INSERT INTO `sc` VALUES ('08', '01', 40);INSERT INTO `sc` VALUES ('08', '03', 35);studentINSERT INTO `student` VALUES ('01', 'Zhao Lei', '1990-1-1', 'MAN'); INSERT INTO `student` VALUES ('02', 'Qian Dian', '1990-12-21', 'MAN'); INSERT INTO `student` VALUES ('03', 'Sun Feng', '1990-5-20', 'MAN'); INSERT INTO `student` VALUES ('04', 'Li Yun', '1990-8-6', 'MAN'); INSERT INTO `student` VALUES ('05', 'Zhou Mei', '1991-12-1', 'WOMAN'); INSERT INTO `student` VALUES ('06', 'Wu Lan', '1992-3-1', 'WOMAN'); INSERT INTO `student` VALUES ('07', 'Zheng Zhu', '1989-7-1', 'WOMAN'); INSERT INTO `student` VALUES ('08', 'Wang Ju', '1990-1-20', 'WOMAN'); INSERT INTO `student` VALUES ('09', 'Wang Ju', '2020-8-30', 'MAN');teacherINSERT INTO `teacher` VALUES ('01', 'Li Pengfei');INSERT INTO `teacher` VALUES ('02', 'Wang Wen');INSERT INTO `teacher` VALUES ('03', 'Zhang Zhichao');INSERT INTO `teacher` VALUES ('04', 'Ye Ping');题⽬01、查询'01'课程⽐'02'课程成绩⾼的所有学⽣的学号；SELECT a.SId FROM (select SId,score from `sc` where CId = '01')as aINNER JOIN(select SId,score from `sc` where CId = '02')as bON a.SId = b.SIdINNER JOIN student as c ON c.SId = a.SIdwhere a.score>b.score02、查询平均成绩⼤于60分的同学的学号和平均成绩；select SId,AVG(score) FROM sc GROUP BY SId HAVING AVG(score)>6003、查询所有同学的学号、姓名、选课数、总成绩；⼀SELECT t1.SId,t1.Sname,(SELECT COUNT(CId) FROM sc s1 where s1.SId=t1.SId) 数量,(SELECT SUM(score) FROM sc s2 where s2.SId = t1.SId) 总成绩from student t1⼆select stu.SId , stu.Sname,COUNT(s.CId),sum(s.score) from sc as s join student as stuon s.SId = stu.SIdGROUP BY stu.SId04、查询姓“李”的⽼师的个数；SELECT COUNT(Tid) FROM teacher where Tname LIKE 'Li%'05、查询没学过“叶平”⽼师课的同学的学号、姓名；思路：查找所有学过yp⽼师可的学⽣，然后看哪个Sid没在⾥⾯SELECT SId,Sname from student WHERE SId NOT IN(SELECT SId from sc where CId IN(SELECT CId FROM course where TId IN(SELECT TId FROM teacher where Tname ='Ye Ping')))06、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；SELECT SId,Sname from student st where(select COUNT(*) FROM sc s1 where s1.SId=st.SId and CId='01')>0and (select COUNT(*) from sc s2 where s2.SId=st.SId and CId='02')>0SELECT DISTINCT stu.SId , stu.Sname from sc as s join student as stu on s.SId = stu.SId where01 in (SELECT CId from sc where stu.SId = SId) and 02 in (SELECT CId from sc where stu.SId = SId)07、查询学过“Li Pengfei”⽼师所教的所有课的同学的学号、姓名；select DISTINCT stu.SId ,stu.Sname from student as stujoin sc as s on stu.SId = s.SIdwhere (SELECT COUNT(1) from sc as ss join course as c on ss.CId = c.CIdJOIN teacher as t on c.TId = t.TIdwhere s.SId = ss.SId and t.Tname = "Li PengFei" )=(select COUNT(1) from course as cou join teacher as tea on cou.TId = tea.TIdwhere tea.Tname = "Li PengFei")08、查询课程编号“02”的成绩⽐课程编号“01”课程低的所有同学的学号、姓名；⼀ SELECT st.SId,st.Sname from (SELECT SId,score from sc where CId = '02') aINNER JOIN (SELECT SId,score from sc where CId = '01') bON a.SId = b.SIdINNER JOIN student st ON st.SId = b.SId where a.score<b.score⼆ SELECT SId,Sname from student where SId IN(SELECT s1.SId from sc s1 ,sc s2 wheres1.SId = s2.Sid and s2.CId = '02' and s1.CId = '01' and s1.score>s2.score)09、查询所有课程成绩⼩于80分的同学的学号、姓名；SELECT SId ,sname from student where SId NOT IN (select SId from sc where score >=80)SELECT stu.SId,stu.Sname from student as stuwhere (SELECT COUNT(1) from sc where stu.SId = SId) = (SELECT COUNT(1) from sc WHERE stu.SId = SId and score<80) 10、查询没有学全所有课的同学的学号、姓名；SELECT SId,Sname from student where SId IN(select SId from scgroup BY SId HAVING (SELECT COUNT(CId) from course)=COUNT(SId))SELECT DISTINCT stu.SId,stu.Sname from student as stuwhere (SELECT COUNT(1) from sc where stu.SId = SId) = (SELECT COUNT(1) from course )11、查询⾄少有⼀门课与学号为“01”的同学所学相同的同学的学号和姓名；SELECT SId ,Sname from student where SId IN(select SId from sc where CId IN(select CId from sc where SId = '01'))12、查询学过学号为“07”同学所有门课的其他同学学号和姓名；select SId ,Sname from student where SId IN(SELECT SId from sc WHERECId IN(SELECT CId from sc where SId = '07')GROUP BY CId HAVING COUNT(SId)>=(SELECT COUNT(SId) from sc where SId = '07'))13.丢了知道的可以告诉我⼀下14、查询和“07”号的同学学习的课程完全相同的其他同学学号和姓名；select s.SId,stu.Sname from sc as s join student as stu on s.SId = stu.SId wheres.SId not in (select SId from sc where CId not in (select CId from sc where SId = 07)) and(SELECT COUNT(1) from sc where SId = 07) = (SELECT COUNT(1) from sc where SId = s.SId)GROUP BY s.SId,stu.Sname15、删除学习“Li Pengfei”⽼师课的SC表记录；DELETE from sc where CId IN (select CId from course where TId = (SELECT TId from teacher where Tname = 'Li Pengfei'))16、向SC表中插⼊⼀些记录，这些记录要求符合以下条件：没有上过编号“03”课程的同学学号，“02”，以及“02”课的平均成绩；INSERT INTO sc SELECT SId,'02',b.av from sc,(SELECT AVG(score) AS av from sc where CId = '02') bwhere SId NOT IN (SELECT SId from sc s2 where CId ='03')17、按平均成绩从⾼到低显⽰所有学⽣的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显⽰：select st.Sname,a.score as 'MATH',b.score as 'ENGLISH',c.score as 'CHINESE' from(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'MATH')) as aINNER JOIN (SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'ENGLISH')) as b ON a.SId = b.SIdINNER JOIN(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'CHINESE')) as c ON b.SId = c.SIdINNER JOIN student as st ON c.SId = st.SIdGROUP BY st.Sname ORDER BY AVG(a.score+b.score+c.score)DESC18、查询各科成绩最⾼和最低的分：以如下形式显⽰：course_id，max，minSELECT CId as 'coures_id',MAX(score) as 'max',MIN(score) as 'min'from scGROUP BY CId19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序SELECT CId,AVG(score) as '平均成绩',sum(case when score>60 then 1 else 0 end)/count(*)*100 as '及格率'from scGROUP BY CIdORDER BY AVG(score) asc,sum(case when score>=60 then 1 else 0 end)/count(*) descSELECT s.CId,ame,AVG(s.score),(SELECT COUNT(1) from sc where CId = s.CId and score>60)/(SELECT COUNT(1) from sc WHERE CId = s.CId) as 及格率from sc as s JOIN course as cou on s.CId = cou.CIdGROUP BY CIdORDER BY AVG(s.score) ASC,及格率 DESC20、查询如下课程平均成绩和及格率的百分数(⽤"1⾏"显⽰): math（01），chinese（02），english（03）select AVG(a.score) as 'MATH',SUM(case when a.score>=60 then 1 else 0 end)/count(a.SId)*100 as 'MATH%',AVG(b.score) as 'ENGLISH',sum(case when b.score>=60 then 1 else 0 end)/count(b.SId)*100 as 'ENGLISH%',AVG(c.score) as 'CHINESE', sum(case when c.score>=60 then 1 else 0 end)/count(c.SId)*100 as 'CHINESE%'from(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'MATH')) as aINNER JOIN (SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'ENGLISH')) as b INNER JOIN(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'CHINESE')) as cSELECT s.CId,ame,AVG(s.score),(SELECT COUNT(1) from sc where CId = s.CId and score>60)/(SELECT COUNT(1) from sc WHERE CId = s.CId) as 及格率from sc as s JOIN course as cou on s.CId = cou.CId where s.CId in (01,02,03)GROUP BY CId21、查询不同⽼师所教不同课程平均分从⾼到低显⽰SELECT tea.Tname,ame,AVG(s.score) as 平均分from sc as s JOIN course as cou on s.CId = cou.CIdJOIN teacher as tea on tea.TId = cou.TIdGROUP BY s.CIdORDER BY 平均分 desc22、查询如下课程成绩第 3 名到第 6 名的学⽣成绩单：math(01),chinese(02),english(03)——student_id,student_name,math,chinese,english,avg_score;⼀：select stu.SId,stu.Sname,(SELECT score from sc as s1 where CId = "02" and SId = stu.SId) as 数学,(SELECT score from sc as s2 where CId = "01" and SId = stu.SId) as 语⽂,(SELECT score from sc as s3 where CId = "03" and SId = stu.SId) as 英语,avg((SELECT score from sc as s1 where CId = "02" and SId = stu.SId)+(SELECT score from sc as s1 where CId = "01" and SId = stu.SId)+(SELECT score from sc as s1 where CId = "03" and SId = stu.SId))/3 as 平均成绩from student stuGROUP BY stu.SIdORDER BY 平均成绩 desc LIMIT 2,4⼆：select stu.SId,stu.Sname,ms.score as "数学",ys.score as "语⽂",es.score as "英语" ,avg(ms.score+ys.score+es.score)/3 as "平均成绩" from(SELECT SId,score from sc where CId = "02") as msINNER join (SELECT SId,score from sc where CId = "01" )as ys on ms.SId = ys.SIdINNER join (SELECT SId,score from sc where CId = "03")as es on ys.SId = es.SIdINNER join student stu on stu.SId = es.SIdGROUP BY stu.SId,stu.Sname,ms.score,ys.score,es.scoreORDER BY avg(ms.score+ys.score+es.score) DESC23、统计列印各科成绩,各分数段⼈数:课程ID,课程名称,[100-85],[85-70],[70-60],[-60]--分组求和 sum case when then else endSELECT cou.CId ,ame,SUM(CASE when s.score>=85 then 1 ELSE 0 END) as "[100-85]" ,SUM(CASE when s.score<85 and s.score >=70 then 1 ELSE 0 END) as "[85-70]" ,SUM(CASE when s.score<70 and s.score>=60 then 1 ELSE 0 END) as "[70-60]" ,SUM(CASe when s.score<60 then 1 ELSE 0 END) as "60以下"from sc as s join course as cou on cou.CId = s.CIdGROUP BY cou.CId ,ame24、查询学⽣平均成绩及其名次select SId,平均成绩,(SELECT COUNT(1) from(SELECT SId,AVG(score) as 平均成绩 from sc GROUP BY SId)as a where a.平均成绩>b.平均成绩)+1 as 排名 from(select SId,AVG(score) as 平均成绩 from sc GROUP BY SId) as bGROUP BY SIdORDER BY 平均成绩 DESCSELECT(SELECT COUNT(1) from (SELECT AVG(score) as pjcj,SId from sc GROUP BY SId) as b where b.pjcj>a.平均成绩)+1 as 排名,姓名,平均成绩from(SELECT stu.Sname as 姓名, AVG(s.score) as 平均成绩 from sc as s join student as stu on s.SId = stu.SId GROUP BY stu.Sname)as aORDER BY 排名25、查询各科成绩前三名的记录:(不考虑成绩并列情况)//有瑕疵SELECT ame,b.score,b.SId,(SELECT COUNT(1) from sc as c where c.score>b.score and c.CId = b.CId)+1 as 排名fromsc as b join course as cou on b.CId = cou.CId where (select COUNT(1) from sc as a WHERE a.CId = b.CId and a.score>b.score order BY a.CId)<3 GROUP By ame,b.SIdORDER BY ame,b.score descSELECT s.CId ,s.SId ,s.score from(SELECT CId,SId,score,(SELECT COUNT(1) from sc as s2 WHERE s2.score>s1.score and s2.CId = s1.CId)+1 as c1 from sc as s1HAVING c1<4)as sGROUP BY s.CId ,s.SIdORDER BY s.CId, s.score DESC26、查询每门课程被选修的学⽣数select ame,(SELECT count(*) from sc as a WHERE a.CId = b.CId),COUNT(b.SId)from sc as b JOIN course as cou on b.CId = cou.CidGROUP BY ame27、查询出只选修了2门课程的全部学⽣的学号和姓名select stu.SId,stu.Sname from student as stu join (SELECT SId,COUNT(SId) from scGROUP BY SId HAVING COUNT(SId) = 2) as a on stu.SId = a.SId28、查询男⽣、⼥⽣⼈数SELECT Ssex,count(Ssex) from studentGROUP BY Ssex29、查询姓“王”的学⽣名单select * from student WHERE Sname LIKE "Wang%"30、查询同名同性学⽣名单，并统计同名⼈数select a.Sname,(SELECT COUNT(1) from student as b where a.Sname = b.Sname)as c from student as a GROUP BY a.SnameHAVING c>131、1990年出⽣的学⽣名单(注：Student表中Sage列的类型是datetime)select * from student where YEAR(Sage)=199032、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列SELECT s.CId,ame,AVG(s.score) from sc as s JOIN course as cou on s.CId = cou.CIdGROUP BY s.CIdORDER BY AVG(s.score) DESC ,s.CId ASC33、丢了知道的告诉我⼀下34、查询课程名称为“MATH”，且分数低于60的学⽣姓名和分数SELECT stu.Sname,s.score from sc as s join student as stu on s.SId = stu.SIdwhere s.score<60 and s.CId = (SELECT CId from course where Cname = "MATH")35、查询所有学⽣的选课情况；⼀ select stu.SId,stu.Sname,s.CId,ame from sc as s join student as stu on s.SId = stu.SIdjoin course as cou on cou.CId = s.CIdGROUP BY stu.SId,s.CIdORDER BY s.SId asc⼆.合并SELECT stu.SId,stu.Sname,GROUP_CONCAT(ame SEPARATOR ' , ') as 选课 from sc as s join student as stu ON s.SId = stu.SId JOIN course as cou on cou.CId = s.CIdGROUP BY stu.SId36、查询每门课程成绩在70分以上的姓名、课程名称和分数；SELECT stu.Sname,ame,s.score from student as stu join sc as s on stu.SId = s.SId JOIN course as cou on cou.CId = s.CIdwhere s.score>=7037、查询不及格的课程，显⽰学号、姓名、课程号、成绩SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SIdwhere s.score <6038、查询课程编号为03且课程成绩在80分以上的学⽣的学号和姓名；SELECT stu.SId,stu.Sname from student as stu join sc as s on stu.SId = s.SIdwhere s.score >=80 and s.CId = 0339、求选了课程的学⽣⼈数去重SELECT COUNT(DISTINCT SId) as 选课⼈数 from sc40、查询选修“Li Pengfei”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩select stu.Sname,s.score from student as stujoin sc as s on stu.SId = s.SIdwhere s.score = (SELECT MAX(score) from sc as s join course as cou on cou.CId = s.CIdjoin teacher as tea on tea.TId = cou.TId where tea.Tname = "Li Pengfei" )and s.CId = (SELECT DISTINCT s.CId from sc as s join course as cou on cou.CId = s.CIdjoin teacher as tea on tea.TId = cou.TId where tea.Tname = "Li Pengfei" )41、查询各个课程及相应的选修⼈数SELECT ame,count(1) from sc as s join course as cou on s.CId = cou.CIdGROUP BY ame42、查询不同课程成绩相同的学⽣的学号、课程号、学⽣成绩理解俩种意思：⼀：不同课程，不同学⽣，成绩相同SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SIdwhere s.score in (SELECT score from sc where SId!=s.SId)ORDER BY s.score DESC⼆：不同课程，相同学⽣，成绩相同（可以去重不⽤in）SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SIdwhere s.score in (SELECT score from sc where SId=s.SId and CId!=s.CId)ORDER BY s.score DESC43、查询每门课程成绩最好的前两名SELECT s.CId,stu.Sname,s.score from student as stu join sc as s on stu.SId = s.SIdwhere (SELECT COUNT(1) FROM sc where CId = S.CId and score>s.score)<2GROUP BY s.CId,stu.SnameORDER BY s.CId,s.score DESC44、统计每门课程的学⽣选修⼈数（超过5⼈的课程才统计）。

SQL 经典五十道题

insert into Student values(' 02' , ' 钱电' , '199012-21' , '男') insert into Student values(' 03' , ' 孙风' , '199005-20' , '男') insert into Student values(' 04' , ' 李云' , '199008-06' , '男') insert into Student values(' 05' , ' 周梅' , '199112-01' , '女') insert into Student values(' 06' , ' 吴兰' , '199203-01' , '女') insert into Student values(' 07' , ' 郑竹' , '198907-01' , '女')
a.* from (SELECT * from sc WHERE sc.C in('01') )a left JOIN(SEL ECT * from sc where sc.C in('02') )b ON a.s =b.s
where a.score> b.score )a ,student c where a.s =c.s; --2、查询"01"课程比"02" 课程成绩低的学生的信息及课程分数 SELECT a. ,c. FROM (SELECT a.* FROM (SELECT * FROM sc WHERE sc.C in('01') )a LEFT JOIN(SEL ECT * FROM sc WHERE sc.C in('02') )b ON a.s =b.s WHERE a.score< b.score) a,studen tc WHERE a.s =c.s

SQL经典50题练习

创建表及插⼊数据学⽣表Studentcreate table SC(SId varchar(10),CId varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);练习题⽬1. 查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数1.1 查询同时存在" 01 "课程和" 02 "课程的情况1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显⽰为 null )1.3 查询不存在" 01 "课程但存在" 02 "课程的情况2. 查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩3. 查询在 SC 表存在成绩的学⽣信息4. 查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显⽰为 null )4.1 查有成绩的学⽣信息5. 查询「李」姓⽼师的数量6. 查询学过「张三」⽼师授课的同学的信息7. 查询没有学全所有课程的同学的信息8. 查询⾄少有⼀门课与学号为" 01 "的同学所学相同的同学的信息9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息10. 查询没学过"张三"⽼师讲授的任⼀门课程的学⽣姓名11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩12. 检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息13. 按平均成绩从⾼到低显⽰所有学⽣的所有课程的成绩以及平均成绩14. 查询各科成绩最⾼分、最低分和平均分：以如下形式显⽰：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列15. 按各科成绩进⾏排序，并显⽰排名， Score 重复时保留名次空缺15. 按各科成绩进⾏排序，并显⽰排名， Score 重复时保留名次空缺15.1 按各科成绩进⾏排序，并显⽰排名， Score 重复时合并名次16. 查询学⽣的总成绩，并进⾏排名，总分重复时保留名次空缺16.1 查询学⽣的总成绩，并进⾏排名，总分重复时不保留名次空缺17. 统计各科成绩各分数段⼈数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分⽐18. 查询各科成绩前三名的记录19. 查询每门课程被选修的学⽣数20. 查询出只选修两门课程的学⽣学号和姓名21. 查询男⽣、⼥⽣⼈数22. 查询名字中含有「风」字的学⽣信息23. 查询同名同性学⽣名单，并统计同名⼈数24. 查询 1990 年出⽣的学⽣名单25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列26. 查询平均成绩⼤于等于 85 的所有学⽣的学号、姓名和平均成绩27. 查询课程名称为「数学」，且分数低于 60 的学⽣姓名和分数28. 查询所有学⽣的课程及分数情况（存在学⽣没成绩，没选课的情况）29. 查询任何⼀门课程成绩在 70 分以上的姓名、课程名称和分数30. 查询不及格的课程31. 查询课程编号为 01 且课程成绩在 80 分以上的学⽣的学号和姓名32. 求每门课程的学⽣⼈数33. 成绩不重复，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩34. 成绩有重复的情况下，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩35. 查询不同课程成绩相同的学⽣的学⽣编号、课程编号、学⽣成绩36. 查询每门功成绩最好的前两名37. 统计每门课程的学⽣选修⼈数（超过 5 ⼈的课程才统计）。

sql语句练习50题

sql语句练习50题Student(Sid,Sname,Sage,Ssex) 学⽣表Course(Cid,Cname,Tid) 课程表SC(Sid,Cid,score) 成绩表Teacher(Tid,Tname) 教师表练习内容：1.查询“某1”课程⽐“某2”课程成绩⾼的所有学⽣的学号；SELECT a.sid FROM (SELECT sid,score FROM SC WHERE cid=1) a,(SELECT sid,score FROM SC WHERE cid=3) b WHEREa.score>b.score AND a.sid=b.sid;此题知识点，嵌套查询和给查出来的表起别名2.查询平均成绩⼤于60分的同学的学号和平均成绩；SELECT sid,avg(score) FROM sc GROUP BY sid having avg(score) >60;此题知识点，GROUP BY 语句⽤于结合合计函数，根据⼀个或多个列对结果集进⾏分组。

group by后⾯不能接where，having代替了where3.查询所有同学的学号、姓名、选课数、总成绩SELECT Student.sid,Student.Sname,count(SC.cid),sum(score)FROM Student left Outer JOIN SC on Student.sid=SC.cid GROUP BY Student.sid,Sname4.查询姓“李”的⽼师的个数；select count(teacher.tid)from teacher where teacher.tname like'李%'5.查询没学过“叶平”⽼师课的同学的学号、姓名；SELECT Student.sid,Student.Sname FROM Student WHERE sid not in (SELECT distinct( SC.sid) FROM SC,Course,Teacher WHERE SC.cid=Course.cid AND Teacher.id=Course.tid AND Teacher.Tname='叶平');此题知识点，distinct是去重的作⽤6.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名；select a.SID,a.SNAME from (select student.SNAME,student.SID from student,course,sc where cname='c++'and sc.sid=student.sid and sc.cid=course.cid) a,(select student.SNAME,student.SID from student,course,sc where cname='english'and sc.sid=student.sid and sc.cid=course.cid) b where a.sid=b.sid;标准答案（但是好像不好使）SELECT Student.S#,Student.Sname FROM Student,SC WHERE Student.S#=SC.S# ANDSC.C#='001'and exists( SELECT * FROM SC as SC_2 WHERE SC_2.S#=SC.S# AND SC_2.C#='002');此题知识点，exists是在集合⾥找数据，as就是起别名7.查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；select a.sid,a.sname from (select student.sid,student.sname from student,teacher,course,scwhere teacher.TNAME='杨巍巍' and teacher.tid=course.tid and course.cid=sc.cid and student.sid=sc.sid) a标准答案：SELECT sid,Sname FROM Student WHERE sid in (SELECT sid FROM SC ,Course ,Teacher WHERE SC.cid=Course.cid AND Teacher.tid=Course.tid AND Teacher.Tname='杨巍巍' GROUP BY sid having count(SC.cid)=(SELECT count(cid) FROM Course,Teacher WHERE Teacher.tid=Course.tid AND Tname='杨巍巍'))8.查询课程编号“”的成绩⽐课程编号“”课程低的所有同学的学号、姓名；select a.sid,a.sname from(select student.SID,student.sname,sc.SCORE from student,sc where student.sid=sc.sid and sc.cid=1) a, (select student.SID,student.sname,sc.score from student,sc where student.sid=sc.sid and sc.cid=2) b where a.score<b.score anda.sid=b.sid标准答案：SELECT sid,Sname FROM (SELECT Student.sid,Student.Sname,score ,(SELECT score FROM SC SC_2 WHERE SC_2.sid=Student.sid AND SC_2.cid=1) score2 FROM Student,SCWHERE Student.sid=SC.sid AND cid=1) S_2 WHERE score2 <score;9.查询所有课程成绩⼩于分的同学的学号、姓名；SELECT sid,Sname FROM Student WHERE sid not in (SELECT Student.sid FROM Student,SC WHERE Student.sid=SC.sid AND score>60);此题知识点，先查出⼤于60分的，然后not in 就是⼩于60分的了10.查询没有学全所有课的同学的学号、姓名；SELECT Student.sid,Student.Sname FROM Student,SCWHERE Student.sid=SC.sid GROUP BY Student.sid,Student.Sname having count(cid) <(SELECT count(cid) FROM Course);11.查询⾄少有⼀门课与学号为“”的同学所学相同的同学的学号和姓名；12.查询⾄少学过学号为“”同学所有⼀门课的其他同学学号和姓名；SELECT student.sid,student.Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHERE sid=1)此题知识点，SELECT sid,Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHEREsid=1)这样写是错误的，因为from后⾯是两个表，不能明确是哪个表⾥⾯的sid和sname所以错误提⽰是“未明确定义列”13.把“SC”表中“叶平”⽼师教的课的成绩都更改为此课程的平均成绩；update sc set score=(select avg(score) from sc,course,teacher where course.cid=sc.cid and course.tid=teacher.tid andteacher.tname='杨巍巍')14.查询和“”号的同学学习的课程完全相同的其他同学学号和姓名；SELECT sid FROM SC WHERE cid in (SELECT cid FROM SC WHERE sid=6) GROUP BY sid having count(*)=(SELECT count(*) FROM SC WHERE sid=6);此题知识点，⽤数量来判断15.删除学习“叶平”⽼师课的SC表记录；delete from sc s where s.cid in (select c.cid from teacher t,course c where t.tid = c.tid and tname='李⼦')此题知识点，嵌套查询可以分布考虑，先查出李⼦⽼师都交了什么课的id，然后再删除那些id的值16.向SC表中插⼊⼀些记录，这些记录要求符合以下条件：没有上过编号“”课程的同学学号、课程的平均成绩；Insert into SC SELECT sid,2,(SELECT avg(score) FROM SC WHERE cid=2) FROM Student WHERE sid not in (SELECT sid FROM SC WHERE cid=2);17.按平均成绩从⾼到低显⽰所有学⽣的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID,,数据库,企业管理,英语,有效课程数,有效平均分；(没做出来)18.查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分；select cid as 课程号,max(score)as 最⾼分,min(score) as 最低分 from sc group by cid标准答案（但是运⾏不好使）SELECT L.cid As 课程ID,L.score AS 最⾼分,R.score AS 最低分FROM SC L ,SC AS RWHERE L.cid = R.cid ANDL.score = (SELECT MAX(IL.score)FROM SC AS IL,Student AS IMWHERE L.cid = IL.cid AND IM.sid=IL.sidGROUP BY IL.cid)AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.cid = IR.cid GROUP BY IR.cid );19.按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序26.查询每门课程被选修的学⽣数select sc.cid,count(sc.sid) from sc,course where sc.cid=course.cid group by sc.cid27.查询出只选修了⼀门课程的全部学⽣的学号和姓名SELECT SC.sid,Student.Sname,count(cid) AS 选课数 FROM SC ,StudentWHERE SC.sid=Student.sid GROUP BY SC.sid ,Student.Sname having count(cid)=1;32.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列SELECT Cid,Avg(score) FROM SC GROUP BY cid ORDER BY Avg(score),cid DESC ;37.查询不及格的课程，并按课程号从⼤到⼩排列SELECT cid,sid FROM sc WHERE score <60 ORDER BY cid38.查询课程编号为且课程成绩在分以上的学⽣的学号和姓名；select student.sid,student.sname from sc,student where sc.cid=1 and sc.score>60 and sc.sid=student.sid40.查询选修“叶平”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩select student.sname,sc.score from sc,student,teacher,course c where teacher.tname='李⼦'and teacher.tid=c.tid and c.cid=sc.cid and sc.sid=student.sid and sc.score=(select max(score)from sc where sc.cid=c.cid)41.查询各个课程及相应的选修⼈数select sc.cid ,count(sc.sid)from sc,student where sc.sid=student.sid group by sc.cid43.查询每门功成绩最好的前两名44.统计每门课程的学⽣选修⼈数（超过⼈的课程才统计）。

sql基础50题

sql基础50题1. 什么是SQL？它的作用是什么？2. SQL中的数据类型有哪些？3. SQL中如何创建表格？4. SQL中如何插入数据？5. SQL中如何查询数据？6. SQL中如何更新数据？7. SQL中如何删除数据？8. SQL中如何对数据进行排序？9. SQL中如何对数据进行分组？10. SQL中如何对数据进行过滤？11. SQL中如何进行数据的连接操作？12. SQL中如何使用子查询？13. SQL中如何使用聚合函数？14. SQL中如何使用索引提高查询性能？15. SQL中如何使用视图？16. SQL中如何使用存储过程？17. SQL中如何使用触发器？18. SQL中如何进行事务管理？19. SQL中如何进行数据备份与恢复？20. SQL中如何使用JOIN进行表连接？21. SQL中如何使用UNION进行表合并？22. SQL中如何使用DISTINCT去重？23. SQL中如何使用HAVING进行分组过滤？24. SQL中如何使用LIKE进行模糊查询？25. SQL中如何使用IN进行条件筛选？26. SQL中如何使用BETWEEN进行范围查询？27. SQL中如何使用COUNT函数统计数据数量？28. SQL中如何使用SUM函数求和？29. SQL中如何使用MAX函数获取最大值？30. SQL中如何使用MIN函数获取最小值？31. SQL中如何使用AVG函数计算平均值？32. SQL中如何使用GROUP BY进行分组统计？33. SQL中如何使用ORDER BY进行排序？34. SQL中如何使用LIMIT进行分页查询？35. SQL中如何使用UPDATE进行数据更新？36. SQL中如何使用DELETE进行数据删除？37. SQL中如何使用TRUNCATE进行数据清空？38. SQL中如何使用TRANSACTION进行事务处理？39. SQL中如何使用JOIN进行内连接？40. SQL中如何使用LEFT JOIN进行左连接？41. SQL中如何使用RIGHT JOIN进行右连接？42. SQL中如何使用FULL JOIN进行全连接？43. SQL中如何使用UNION进行多表合并？44. SQL中如何使用CREATE INDEX进行索引创建？45. SQL中如何使用DROP INDEX进行索引删除？46. SQL中如何使用CREATE VIEW进行视图创建？47. SQL中如何使用DROP VIEW进行视图删除？48. SQL中如何使用CREATE PROCEDURE进行存储过程创建？49. SQL中如何使用DROP PROCEDURE进行存储过程删除？50. SQL中如何使用CREATE TRIGGER进行触发器创建？。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

sql语句练习50题Student(Sid,Sname,Sage,Ssex) 学生表Course(Cid,Cname,Tid) 课程表SC(Sid,Cid,score) 成绩表Teacher(Tid,Tname) 教师表练习内容：1.查询“某1”课程比“某2”课程成绩高的所有学生的学号；SELECT a.sid FROM (SELECT sid,score FROM SC WHERE cid=1) a,(SELECT sid,score FROM SC WHERE cid=3) b WHERE a.score>b.score AND a.sid=b.sid;此题知识点，嵌套查询和给查出来的表起别名2.查询平均成绩大于60分的同学的学号和平均成绩；SELECT sid,avg(score) FROM sc GROUP BY sid having avg(score) >60;此题知识点，GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

group by后面不能接where，having代替了where3.查询所有同学的学号、姓名、选课数、总成绩SELECT Student.sid,Student.Sname,count(SC.cid),sum(score)FROM Student left Outer JOIN SC on Student.sid=SC.cid GROUP BY Student.sid,Sname4.查询姓“李”的老师的个数；select count(teacher.tid)from teacher where teacher.tname like'李%'5.查询没学过“叶平”老师课的同学的学号、姓名；SELECT Student.sid,Student.Sname FROM Student WHERE sid not in (SELECT distinct( SC.sid) FROM SC,Course,Teacher WHERE SC.cid=Course.cid AND Teacher.id=Course.tid AND Teacher.Tname='叶平');此题知识点，distinct是去重的作用6.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名；select a.SID,a.SNAME from (select student.SNAME,student.SID from student,course,sc where cname='c++'and sc.sid=student.sid and sc.cid=course.cid) a,(select student.SNAME,student.SID from student,course,sc where cname='english'andsc.sid=student.sid and sc.cid=course.cid) b where a.sid=b.sid;标准答案（但是好像不好使）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 WHERESC_2.S#=SC.S# AND SC_2.C#='002');此题知识点，exists是在集合里找数据，as就是起别名7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；select a.sid,a.sname from (select student.sid,student.sname from student,teacher,course,scwhere teacher.TNAME='杨巍巍' and teacher.tid=course.tid and course.cid=sc.cid andstudent.sid=sc.sid) a标准答案：SELECT sid,Sname FROM Student WHERE sid in (SELECT sid FROMSC ,Course ,Teacher WHERE SC.cid=Course.cid AND Teacher.tid=Course.tid ANDTeacher.Tname='杨巍巍' GROUP BY sid having count(SC.cid)=(SELECT count(cid) FROM Course,Teacher WHERE Teacher.tid=Course.tid AND Tname='杨巍巍'))8.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；select a.sid,a.sname from(select student.SID,student.sname,sc.SCORE from student,sc where student.sid=sc.sid and sc.cid=1) a,(select student.SID,student.sname,sc.score from student,sc where student.sid=sc.sid and sc.cid=2) b where a.score<b.score and a.sid=b.sid标准答案：SELECT sid,Sname FROM (SELECT Student.sid,Student.Sname,score , (SELECT score FROM SC SC_2 WHERE SC_2.sid=Student.sid AND SC_2.cid=1) score2 FROM Student,SCWHERE Student.sid=SC.sid AND cid=1) S_2 WHERE score2 <score;9.查询所有课程成绩小于分的同学的学号、姓名；SELECT sid,Sname FROM Student WHERE sid not in (SELECT Student.sid FROM Student,SC WHERE Student.sid=SC.sid AND score>60);此题知识点，先查出大于60分的，然后not in 就是小于60分的了10.查询没有学全所有课的同学的学号、姓名；SELECT Student.sid,Student.Sname FROM Student,SCWHERE Student.sid=SC.sid GROUP BY Student.sid,Student.Sname having count(cid)<(SELECT count(cid) FROM Course);11.查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名；12.查询至少学过学号为“”同学所有一门课的其他同学学号和姓名；SELECT student.sid,student.Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHERE sid=1)此题知识点，SELECT sid,Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHERE sid=1)这样写是错误的，因为from后面是两个表，不能明确是哪个表里面的sid和sname所以错误提示是“未明确定义列”13.把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；update sc set score=(select avg(score) from sc,course,teacher where course.cid=sc.cid and course.tid=teacher.tid and teacher.tname='杨巍巍')14.查询和“”号的同学学习的课程完全相同的其他同学学号和姓名；SELECT sid FROM SC WHERE cid in (SELECT cid FROM SC WHERE sid=6) GROUP BY sid having count(*)=(SELECT count(*) FROM SC WHERE sid=6);此题知识点，用数量来判断15.删除学习“叶平”老师课的SC表记录；delete from sc s where s.cid in (select c.cid from teacher t,course c where t.tid = c.tid and tname='李子')此题知识点，嵌套查询可以分布考虑，先查出李子老师都交了什么课的id，然后再删除那些id的值16.向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“”课程的同学学号、课程的平均成绩；Insert into SC SELECT sid,2,(SELECT avg(score) FROM SC WHERE cid=2) FROM Student WHERE sid not in (SELECT sid FROM SC WHERE cid=2);17.按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分；(没做出来)18.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；select cid as 课程号,max(score)as 最高分,min(score) as 最低分from sc group by cid标准答案（但是运行不好使）SELECT L.cid As 课程ID,L.score AS 最高分,R.score AS 最低分FROM SC L ,SC AS RWHERE L.cid = R.cid ANDL.score = (SELECT MAX(IL.score)FROM SC AS IL,Student AS IMWHERE L.cid = IL.cid AND IM.sid=IL.sidGROUP BY IL.cid)AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.cid = IR.cid GROUP BY IR.cid );19.按各科平均成绩从低到高和及格率的百分数从高到低顺序26.查询每门课程被选修的学生数select sc.cid,count(sc.sid) from sc,course where sc.cid=course.cid group by sc.cid27.查询出只选修了一门课程的全部学生的学号和姓名SELECT SC.sid,Student.Sname,count(cid) AS 选课数FROM SC ,StudentWHERE SC.sid=Student.sid GROUP BY SC.sid ,Student.Sname having count(cid)=1;32.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列SELECT Cid,Avg(score) FROM SC GROUP BY cid ORDER BY Avg(score),cid DESC ;37.查询不及格的课程，并按课程号从大到小排列SELECT cid,sid FROM sc WHERE score <60 ORDER BY cid38.查询课程编号为且课程成绩在分以上的学生的学号和姓名；select student.sid,student.sname from sc,student where sc.cid=1 and sc.score>60 andsc.sid=student.sid40.查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select student.sname,sc.score from sc,student,teacher,course c where teacher.tname='李子'and teacher.tid=c.tid and c.cid=sc.cid and sc.sid=student.sid and sc.score=(select max(score)from sc where sc.cid=c.cid)41.查询各个课程及相应的选修人数select sc.cid ,count(sc.sid)from sc,student where sc.sid=student.sid group by sc.cid43.查询每门功成绩最好的前两名44.统计每门课程的学生选修人数（超过人的课程才统计）。