SELECT  s.sno，sname，gender，age，dno，cno，score
    FROM    s, sc
    WHERE  s.sno = sc.sno(*)；
1.在表名后面加外连接操作符(*)或(+)指定非主体表(oracle数据库中使用(+))
2.非主体表有一“万能”的虚行，该行全部由空值组成

CREATE  VIEW BT_S(sno，sname，sbirth)
               AS 
              SELECT sno，sname，2022 - age
                FROM  S;

select to_date('2005-01-01 ','yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;

select to_char(88877) from dual;
select to_char(1234567890,'099999999999999')  from dual;
select to_char(12345678,'999,999,999,999')  from dual;
select to_char(1234567890,'999,999,999,999.9999')  from dual;

create or replace view test5_10 as
select
   cid,
  (select name from pub.course where cid=a.cid) cname,
  to_char(trunc(score,-1),'fm00')||'-'||to_char(trunc(score,-1)+9,'fm00') Score,
  count(*) count1,
  (select count(*) from pub.student_course t where a.cid=t.cid and score>=60 and score<100) count0,
  round(count(*)/(select count(*) from pub.student_course where cid=a.cid and score>=60 and score<100)*100,2) percentage
from
   pub.student_course a
where
   score>=60 and score<=99
group by
   cid, to_char(trunc(score,-1),'fm00')||'-'||to_char(trunc(score,-1)+9,'fm00')

1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18
2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.
3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天
4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日
5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天
6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41  
8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确

insert into test9_02
select * from pub.student 
where sex = '女' and sid in
  (select distinct sid from pub.student_course where score < 60 )

1.查询女生选修的全部课程名称
select cname from sc, s, c where sc.cno = s.sno and s.sex = '女' and sc.cno = c.cno;
	 注意在cname前加上 distinct
2.选修了数据库课程的学生的姓名和学号
select s.sno,sname from sc, s, c where sc.cno = c.cno and c.cname = '数据库' and sc.sno = s.sno;
3.选修了数据库课程或数据结构的学生的姓名和学号
select sno, sname from sc, s, c where sc.cno = c.cno and c.cname in ('数据库','数据结构') and sc.sno = s.sno;
select sno, sname from sc, s, c where sc.cno = c.cno and (c.cname = 'DS' or cname = 'DB' ) and sc.sno = s.sno;
4.数据库课程最高成绩的学生的学号
select sno,max(score) from sc where cno = (select cno from c where cname = '数据库') group by sno;

select sno 
from sc 
where score = (select max(score) from sc, c where sc.cno = c.cno and cname = 'DB') 
and cno in (select cno from c where cname = 'DB';)
5.查询软件学院女生的人数
select count(*) from s where s.dno = (select dno from d where dname = '软件学院') and s.sex = '女';
6.平均成绩在90分以上的学生学号
select sno from (select sno,avg(score) as avgsc from sc group by sno) t where t.avgsc > 90;
标答：
select sno from sc group by sno having avg(score) > 90;
7.选修的所有课程都不及格的学生学号
select sno from sc 
except
select sno from sc group by sno having score >= 60;
标答：
select sno from sc group by sno having max(score) < 60;
8.没有学生的学院名称
select dname from d where d.dno not in (select distinct dno from s);
标答：
select dname from d where d.dno not in (select distinct dno from s where dno is not null);
9.重名的学生姓名和此姓名的总人数
select sname,count(*) from s group by sname having count(*) > 2;
10.查询各个学院男女学生的平均年龄和人数（按照学院编号升序排序，按照性别降序排序）
select avg(age), count(*) from s group by dno order by dno asc,age desc;
select dno,gender, avg(age), count(*) from s where gender is not null group by dno, gender order by dno,gender desc;
11.工资最高的老师姓名及其工资数额
select tname,max(sal) from t;错了×

select tname.sal from t where sal = (select max(sal) from t);
12.选修了t1老师讲授全部课程的学生学号
这题不会
select sno from sc where  
select cno from tc where tno = (select tno from t where tname = 't1')
全部的处理方式：B属于A，B - A = 空集
select sno from sc a where not exists (
    (select cno from tc where tno = 't1') 
    	minus 
    (select cno from sc b where a.sno = b.sno));
13.没有选修t1老师教授的课程的学生学号
select distinct sno from sc
except
select sno from sc where sc.cno in (select cno from tc where tno = (select tno from t where tname = 't1') );

select sno from s where sno not in (select sno from sc,tc where sc.cno = tc.cno and tno = 't1');

14.每个学生得到的学分数总和（不及格的不能算）
select sum(c.credit),sc.sno from sc,c where score >= 60 and sc.cno = c.cno group by sno
15.选修四门及以上课程的学生的学号和平均成绩（不及格的课程不进行统计）
select sno,avg(score) from sc where score >= 60 group by sno having count(*) >= 4;
16.查询t1老师所开课程的课程号，最高成绩，最低成绩和平均成绩。如果某门课程的成绩不全（即SCORE属性中有null）则该课程不统计
select cno,max(score), min(score), avg(score) from sc where cno in 
(select cno from tc where tno = (select tno from t where tname = 't1') group by cno);

select sc.cno,max(score),min(score), avg(score) from sc,tc where sc.cno = tc.cno and tno = 't1' and 
cno not in(select distinct cno from sc where score is null)
group by sc.cno;
17.查询优秀学生人数超过10人的课程编号和优秀学生人数(优秀学生：一共有三门以上课程在90分以上)
select cno from sc where sno in 
(select sno from sc where score >= 90 group by sno having count(*) > 3)
group by cno having count(*) > 10;

select cno,count(*) from sc where sno in 
(select sno from sc where score >= 90 group by sno having count(*) > 3) 
group by cno having count(*) > 10;
18.查询学号最大的两个男生，按学号降序排列
select sno from s order by sno desc limit 2;
select 
	sno 
from
	s as a
where
	gender='M' and 
	1 >= (select sno from s as b where gender='M' and a.sno < b.sno)
order by sno desc;

19.查询只参加一次考试就通过考试的学生
S(sno,name,gender,age,dno)
E(sno,year,month,level,score)

20.查询六级考试中合格人数最少的考试年份和月份
select 
	year,month,min(sum)
from
	(select
    	year,month,count(*) as sum
    from
    	E
    where
    	score >= 60 and level=6
    group by 
    	year,month) t
group by 
	year,month
	
	
select 
	year,month 
from
	E 
where 
	level=6 and score >= 60 
group by 
	year,month
having count(*) <= all(select 
                       	count(*)
                      from 
                      	E
                      where
                      	score >= 60 and level=6
                      group by
                      	year,month)
	
	

3.9 a
select 
	w.ID,person_name,city 
from
	employee e, works w
where 
	e.ID = w.ID and w.company_name = 'First Bank Corporation';

b
select 
	w.ID,person_name,city 
from
	employee e, works w
where 
	e.ID = w.ID and w.company_name = 'First Bank Corporation' and w.salary > 10000;
	
c
select
	e.ID
from 
	employee e
left join
	works w
on
	e.ID = w.ID
where
	w.company_name <> 'Fist Bank Corporation ';
	
d
select
	ID
from
	works
where
	salary > (select max(salary) from works where company_name = 'Small Bank Corporation';);
	
e
select
	company_name 
from
	company a
where not exists
	(select city from  company where company_name = 'Small Bank Corporatin')
	minus
	(select city from company b where a.compoany_name = b.company_name and a.company_name <> 'Small Bank Corporatin');
	
//
select cname from company where city = (select city from company where cname = 'Small');


	
f
select
	company_name,max(sump) 
from (select company_name,count(*) as sump from works group by company_name);

g
select 
	company_name 
from
	(select company_name,avg(salary) as avgs from works group by company_name) t
where
	t.avgs > (select company_name,avg(salary) as avgs from works group by company_name having company_name = 'First Bank Corporation');

3.10
a update employee set city = 'Newtown' where ID = '12345';
b
注意顺序问题：先给大于10000的加工资，后给小于10000的加工资
update works set salary * 1.1 where ID in (select m.manager_ID from works,manages where works.ID = manages.manager_id and works.salary < 100000); 
  update works set salary * 1.03                      where ID in (select works.ID from works,manages where works.ID = manages.manager_id and works.salary > 100000); 


3.16
a
select w.ID,e.person_name 
from works w,employee e,company c 
where w.ID = e.ID and w.comapny_name = c.company_name and c.city = e.city;
b
select w.ID,e.person_name 
from (select w.ID,m.manager_id from works w,manages m where w.ID = m.ID) t 
join employee e on t.ID = e.ID 
join employee c on t.manager_id = c.ID 
where e.city = c.city and e.street = c.street;
c
这题不会
select avg(salary) from works group by company_name 
select w1.ID,pname
from works w1,employee e
where w1.Id = E.ID and sal > (select avg(sal) from works w2 where w1.cname = w2.cname)
d
select min(sums),company_name from (select company_name,sum(salary) as sums from works group by company_name) t;
3.17
a update works set sal * 1.1 where company_name = 'First Bank Corporation';
b update works set sal * 1.1 where company_name = 'First Bank Corporation' and ID in (select distinct manager_id from manages);
c delete from works where company_name = 'Small Bank Corporation';

3.18
employee: 主键ID，ID为int, person_name、street\city为varchar
works: 主键是ID，外键是ID和company_name,引用自employee和company表，company_name为varchar，salary为int
company: 主键company_name,类型varchar,city为varchar
manages: 主键是ID,外键是ID和manager_id,均引用自employee表
3.21
a
select memb_no,name from member m,borrowed b where m.memb_no = b.memb_no and isbn in (select isbn from books where publisher = 'McGraw-Hill');
b
select memb_no,name from member m where not exists
	(select isbn from books where publisher = 'McGraw-Hill')
	minus
	(select isbn from borrowed b where m.memb_no = b.memb_no);
c
这题不会
select t.memb_no,t.name from (select memb_no,name from member m,borrowed b where m.mmeb_no = b.memb_no) t
where 
select memb_no,name
from member
where membno in (select membno
                from borrowed,books
                where borrowed.isbn = books.isbn
                group by publisher,membno
                having count(*) >= 5);

d
select b.sum/m.sum as avgb from (select count(*) as sum from borrowed) b,(select count(*) as sum from member) m; 

SELECT b.publisher, m.no, m.name
FROM book b
JOIN borrowed br ON b.isbn = br.isbn
JOIN member m ON br.no = m.no
WHERE b.publisher IN (
    SELECT publisher
    FROM book
    GROUP BY publisher
    HAVING COUNT(*) > 5
)