首页资源分类嵌入式系统 > SQL的练习(答案)

SQL的练习(答案)

已有 445023个资源

下载专区

文档信息举报收藏

标    签:SQL练习

分    享:

文档简介

SQL的练习题包括答案

文档预览

SQL语言练习 1. 找出Comp.Sci.系开设的具有3个学分的课程名称。 select title from course where dept_name = 'Comp. Sci.' and credits = 3; 2. 找出名叫Einstein的教师所教的所有学生的标识,保证结果中没有重复。 select distinct ID from student natural join takes where course_id in ( select course_id from instructor natural join teaches where name = 'Einstein' ); 3. 找出教师的最高工资 select max(salary) from instructor; 4. 找出工资最高的所有教师(可能不止一位教师拥有同样的工资) select ID, name from instructor where salary = (select max(salary) from instructor); 5. 找出2009年秋季开设的每个课程段的选课人数 select course_id, count(ID) from section natural join takes where semester = 'Fall' and year = 2009 group by course_id; 或者是 select course_id, ( select count(ID) from takes where takes.year = section.year and takes.semester = section.semester and takes.course_id = section.course_id and takes.sec_id = section.sec_id ) from section where semester = 'Fall' and year = 2009; 6. 从2009年秋季开设的所有课程段中,找出最多的选课人数 select max(enrollment) from ( select count(ID) as enrollment from section natural join takes where semester = 'Fall' and year = 2009 group by course_id ) a; 7. 找出在2009年秋季拥有最多的选修人数的课程的选修人数 select a.course_id, max(a.enrollment) from ( select course_id, sec_id, count(ID) as enrollment from section natural join takes where semester = 'Fall' and year = 2009 group by course_id ) a; 8. 给Comp.Sci.系的每位教师涨10%的工资 update instructor set salary = salary * 1.10 where dept_name = 'Comp. Sci.' 9. 删除所有未开设过(即没有出现在section关系中)的课程 delete from course where course_id not in (select course_id from section) 10. 把每位在tot_cred属性上取值超过100的学生作为同系教师插入,工资为10000元 insert into instructor select ID, name, dept_name, 10000 from student where tot_cred > 100 11. 找出所有至少选修了一门Comp. Sci.课程的学生姓名,保证结果中没有重复的姓名 select name from student where ID in ( select distinct id from takes where course_id in (select course_id from course where dept_name = 'Comp. Sci.')); 注意:子查询中的distinct关键字可以去掉,考虑原因 12. 找出所有没有选修在2010年春季之前开设的任意课程的学生的ID和姓名 select ID, name from student where ID not in ( select ID from takes where year < 2010 ) 13. 找出每个系教师的最高工资,可以假设每个系至少有一位教师 select dept_name, max(salary) from instructor group by dept_name; 14. 找从13所查询的每个系最高工资中的最低值 select * from instructor where salary = ( select min(max_salary) from (select max(salary) as max_salary from instructor group by dept_name) a ); 15. 创建一门课程CS-001,其名称为 Weekly Seminar,学分为0。 insert into course( course_id, title, dept_name, credits) values('CS-001', 'Weekly Seminar', null, 0); 16. 创建该课程在2009年秋季的一个课程段,sec_id为1 insert into section(course_id, sec_id, semester, year) values('CS-001', 1, 'Fall', 2009); 17. 让Comp. Sci.系的每个学生都选上16所创建的课程段 insert into takes select ID, 'CS-001', 1, 'Fall', 2009, null from student where dept_name = 'Comp. Sci.'; 18. 删除名为Chavez的学生选修17课程段的信息 delete from takes where ID = (select ID from student where name = 'Chavez') and course_id = 'CS-001'; 19. 不使用外连接运算也可以在计算中表示外连接,重写下列查询语句 A: select * from student natural left outer join takes; B: select * from student natural full outer join takes; A: select * from student natural join takes union select ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL from student S1 where not exists (select ID from takes T1 where T1.id = S1.id); B: (select * from student natural join takes) union (select ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL from student S1 where not exists (select ID from takes T1 where T1.id = S1.id)) union (select ID, NULL, NULL, NULL, course_id, sec_id, semester, year, grade from takes T1 where not exists (select ID from student S1 where T1.id = S1.id))

Top_arrow
回到顶部
EEWORLD下载中心所有资源均来自网友分享,如有侵权,请发送举报邮件到客服邮箱bbs_service@eeworld.com.cn 或通过站内短信息或QQ:273568022联系管理员 高员外,我们会尽快处理。