数据库sql语句练习1.2

-- 41.查询有哪些同学考上的学校开设有“计算机”这门学科，列出学生姓名、学校名称、学科名称、他的录取批次。

-- 1、先查出有计算机专业的学校id

select m.collageid,m.major

from major m

where m.major = '计算机';

-- 2、再查出学校的信息

select c.collageid,c.collagename,m.major

from collage c, (

    select m.collageid,m.major

    from major m

    where m.major = '计算机'

)m

where c.collageid = m.collageid;

-- 3、查出学生的录取批次号

select s.stuname,s.collageid,b.batchnum

from student s,

(

    select *from batchinfo b

    where b.batchnum in (

    select batchnum from batchinfo

    )

)b

where s.score>= b.lowscore && s.score <= b.highscore;

-- 4、实现

select b.stuname,a.collagename,a.major,b.batchnum

from (

    select c.collageid,c.collagename,m.major

    from collage c, (

        select m.collageid,m.major

        from major m

        where m.major = '计算机'

    )m

    where c.collageid = m.collageid

) a,

(

    select s.stuname,s.collageid,b.batchnum

    from student s,

    (

        select *from batchinfo b

        where b.batchnum in (

        select batchnum from batchinfo

        )

    )b

    where s.score>= b.lowscore && s.score <= b.highscore

)b

where a.collageid = b.collageid;

-- 42.查询学科信息表，列出各学科类别下学科的数量（majorcount）、总计划人数（totalplanned）、重点学科的数量（totalkeymajor）。

-- 1、先查出来各学科类下的学科数量

select m.majorcategory,count(m.major) majorcount,sum(m.plannedpeople) totalplanned

from major m

group by majorcategory;

-- 2、实现

select m.majorcategory,b.majorcount,b.totalplanned,count(m.keymajor) totalkeymajor

from major m,(

    select m.majorcategory,count(m.major) majorcount,sum(m.plannedpeople) totalplanned

    from major m

    group by majorcategory

)b

where m.keymajor = '是' and m.majorcategory = b.majorcategory

group by majorcategory,majorcount,totalplanned;

-- 43.创建一个视图“view_majorinfo”，该视图能够展示所有工学相关的学科名称信息，按照计划人数逆序排列。

create or replace view view_majorinfo

as

select m.major,sum(m.plannedpeople) plannedpeoplecount

from major m

where m.majorcategory = '工学'

group by major

order by plannedpeoplecount desc;

-- 44.在学生信息表中创建一个基于“姓名”的索引“stuname_index”。

alter table student add index stuname_index(stuname);

-- 45.创建一个新的表“student_copy”，将户籍为江苏省的学生信息复制到该表中。

create table student_copy

as

    select *from student

    where location = '江苏';

-- 46.写出上题的oracle语句。

create table student_copy

as

    select *from student

    where location = '江苏';

-- 47.删除student_copy表中所有姓名中包含“明”的学生数据。

delete from student_copy

where stuname like '%明%';

-- 48.查询student_copy表，列出学生姓名、考试成绩，并将查询结果中姓名的“东”字替换为“冬”。

select replace(s.stuname,'东','冬'),s.score

from student_copy s;

-- 49.修改student_copy表，将所有邮箱改成@msn.com。

-- 定位@的位置，要用到locate(str,substr):返回子串substr在字符串str中的第一个出现的位置。

update student_copy set email = replace(email,substr(email,locate('@',email),length(email)),'@msn.com');

-- 50.删除student_copy表。

drop table student_copy;