欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

关系数据库的查询建表

程序员文章站 2022-06-02 18:40:48
...
1:create table test2_01 as select sid, name
                        from pub.student
                         where sid not in (select distinct sid
                         from pub.student_course);
2:create table test2_02 as select pub.student.sid ,pub.student.name 
from pub.student,pub.student_course
where pub.student.sid=pub.student_course.sid and pub.student_course.cid in(select cid
    from pub.student_course
     where sid='200900130417');
3:select pub.student.sid,pub.student.name
from pub.student,pub.student_course
where pub.student.sid=pub.student_course.sid and
      pub.student_course.cid in(select cid
                                 from pub.course
                               where fcid='300002');
4:create table test2_04 as select pub.student.sid,pub.student.name
from pub.student,pub.student_course,pub.course
where pub.student.sid=pub.student_course.sid and pub.student_course.cid=pub.course.cid and pub.course.name=('数据结构')intersect(select pub.student.sid,pub.student.name
from pub.student,pub.student_course,pub.course
where pub.student.sid=pub.student_course.sid and pub.student_course.cid=pub.course.cid and pub.course.name=('操作系统'));
5:create table test2_05 as select s.sid,name,avg_score,sum_score from
(select sid,round(avg(score),0) avg_score,sum(score) sum_score from pub.student_course group by sid) sc,(select sid,name from pub.student where age=20) s where sc.sid=s.sid
6:create table test2_06 as select  cid,max(score) as max_score
from pub.student_course
group by cid
7:create table test2_07 as select sid, name
from pub.student
where name not like '王%'and name not like '李%'and name not like '张%';
8:create table test2_08 as select second_name,count(second_name)as p_count from (select substr(name,1,1) as second_name from pub.student) group by second_name
9:create table test2_09 as select pub.student.sid,pub.student.name,pub.student_course.score
from pub.student ,pub.student_course
where pub.student.sid=pub.student_course.sid and pub.student_course.cid=
'300003';
10:create table test2_10 as select sid ,cid
from pub.student_course
where score is not null;

转载于:https://my.oschina.net/yanjianhai/blog/124559