1017课后作业

1、找出所有从不订购任何东西的客户（提示： not in 外关联）

--customers表 
create table customers (
                        id   number       --用户id
                       ,name varchar2(10) --用户姓名
                       );
--插入数据                       
insert into customers values(1, 'joe');
insert into customers values(2, 'henry');
insert into customers values(3, 'sam');
insert into customers values(4, 'max');
--提交
commit;
--查询
select * from customers;

--orders表
create table orders (
                     order_id    number --订单id
                    ,customer_id number --顾客id
                    ,order_amt   number --订单金额
                    );
--插入                    
insert into orders values(111,3,300);
insert into orders values(222,1,50);
--提交
commit;
--查询
select * from orders;

select t1.id
      ,t1.name
from customers t1 
where t1.id not in(
                   select t.customer_id
                   from orders t
                  );
                  
select t1.id,t1.name
      ,t2.order_id
      ,t2.customer_id
      ,t2.order_amt
from customers t1 --zhubiao
left join orders t2 
       on t1.id = t2.customer_id
where t2.order_id is null;

2、查询只出现一次的最大数字

--my_numbers表
create table my_numbers (
                         num number
                        );
--插入数据                        
insert into my_numbers values (8);
insert into my_numbers values (8);
insert into my_numbers values (3);
insert into my_numbers values (3);
insert into my_numbers values (1);
insert into my_numbers values (4);
insert into my_numbers values (5);
insert into my_numbers values (6);
--提交
commit;
--查询
select * from my_numbers;

select max(t1.num)
from (
      select t.num
             --,count(1)
      from my_numbers t
      group by t.num
      having count(1) = 1
    ) t1;

3、

--cust_info表
create table cust_info (
                        cust_id   varchar2(20) --客户id
                       ,cust_name varchar2(10) --客户姓名
                       );
--插入数据                       
insert into cust_info values('00100010','张三');
insert into cust_info values('00100110','李四');
insert into cust_info values('00100210','王五');
insert into cust_info values('00100211','lee');
insert into cust_info values('00100201','sql');
--提交
commit;
--查询
select * from c;

—账户存款余额表
acct_id --账户号
cust_id --客户号
bal —存款余额

--acct_bal表
create table acct_bal (
                       acct_id varchar2(20) --账户id
                      ,cust_id varchar2(20) --客户id
                      ,bal     number       --存款余额 
                      );
--插入数据                      
insert into b values ('100010001001','00100010',100);
insert into b values ('100100100101','00100010',200);
insert into b values ('100011021001','00100110'100);
insert into b values ('102102100101','00100210',150);
insert into b values ('123014001001','00100211',200);
insert into b values ('150160108101','00100211',300);
--提交
commit;  
--查询
select * from b;

—答案
(1)请用一个sql写出当前总客户数及存款总金额
–客户总数是以 cust_info 这张表的为准
—方法:先过滤,其子查询结果集作为一个表,然后对该表进行聚合运算

select * from cust_info;
select * from acct_bal;

select count(distinct t1.cust_id)
      ,sum(t2.bal)
from cust_info t1 
left join acct_bal t2 
       on t1.cust_id = t2.cust_id;
     
select (
        select count(t.cust_id) 
        from cust_info t
       ) as count_cust
      ,(select sum(t1.bal) as sum_bal 
        from acct_bal t1
       )
      ,100
from dual;

(2)请用一个sql写出当前有存款余额的客户总数

select count(distinct t.cust_id) as count_cust
from acct_bal t 
where t.bal > 0;

4、

—学生成绩表-课程-教师表课后练习
select * from student;–学生表
select * from teacher;–教师表
select * from course;–课程表
select * from sc;–学生成绩表
查询每一门课程的课程编号、课程名称，该课程最高分，该课程最低分，该课程选修的人数

select t1.cno
      ,t1.cname
      ,max(t2.score) as max_score 
      ,min(t2.score) as min_score 
      ,count(1) as count_sno
from course t1 
inner join sc t2
        on t1.cno = t2.cno
group by t1.cno,t1.cname;

根据成绩表 sc 计算如下数据—子查询
学号 课程编号 成绩 这门课程的最高成绩 这门课程的最低成绩 这门课程的总成绩

select t.sno
      ,t.cno
      ,t.score
      ,t2.max_s
      ,t2.min_s
      ,t2.sum_s
from sc t
inner join (
            select t1.cno
                  ,max(t1.score) as max_s
                  ,min(t1.score) as min_s
                  ,sum(t1.score) as sum_s
            from sc t1 
            group by t1.cno
           ) t2 
        on t.cno = t2.cno;

5、思考题：
1 内关联和外关联的区别是什么：写法上的区别，数据上的区别

2 表关联过滤数据的条件写在 where 的后面和写在 on 的后面的区别是什么

3 谈谈自己多 group by 分组的理解

4 什么时候要用单列子查询，什么时候要用多行多列子查询