db + oracle scott / student / course / teacher / sc(chengjibiao)

s

- oracle 练习题 ， https://www.cnblogs.com/liyuelian/p/16782510.html

1. 连接类型：

   • 左连接（Left Join）： 返回左表中的所有行，以及与右表中匹配的行。如果右表中没有匹配的行，右侧结果列将包含 NULL。
   • 右连接（Right Join）： 返回右表中的所有行，以及与左表中匹配的行。如果左表中没有匹配的行，左侧结果列将包含 NULL。
   • 内连接（Inner Join）： 返回两个表中匹配的行，不包括任何不匹配的行。

2. 结果集：

   • 左连接和右连接： 结果集中包含左表或右表的所有行，以及与之匹配的另一表的行。如果没有匹配的行，则对应的结果列将包含 NULL。
   • 内连接： 结果集中只包含两个表中匹配的行，不包括任何不匹配的行。

3. 使用场景：

   • 左连接和右连接： 适用于需要保留一个表中所有行并添加与之匹配的另一表的数据的情况。
   • 内连接： 适用于只需要保留两个表中匹配行的情况。

4. 语法：

   • 左连接和右连接：
     sqlCopy code

     SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
     sqlCopy code

     SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
   • 内连接：
     sqlCopy code

     SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

5. 性能：

   • 内连接通常比左连接和右连接更快，因为它只返回匹配的行，而不包括不匹配的行。

6. 子查询：

   • 子查询是嵌套在其他查询中的查询，可以返回单一值、单一行或多行结果。
   • 子查询可以用于过滤、排序、聚合等各种场景，通常嵌套在 WHERE、FROM 或 SELECT 子句中。

总体而言，连接和子查询都是用于在数据库中检索和处理数据的强大工具，具体的选择取决于需求和查询的复杂性。

 

- 20230317 构造4张表数据，学生表student、课程表sourse、教师表teacher、成绩表sc

 

-

-- 学生表
drop table student;
create table student(
       sno number primary key,
       sname varchar2(20) unique,
       age int not null,
       sex varchar2(2) check(sex in ('男','女'))
);
alter table student modify age default 20;

-- 成绩表
drop table course;
create table course(
       cno number,
       cname varchar2(20),
       tno varchar2(20),
       primary key(cno,tno) -- 双主键
);
-- 选课表
drop table sc;
create table sc(
       sno number,
       cno number,
       score int,
       primary key(sno,cno) -- 双主键
);
-- 教师表
drop table teacher;
create table teacher(
       tno number primary key,
       tname varchar2(20)
);

-- 造数学生表student
insert into student values(100,'张三',20,'女');
insert into student values(101,'李四',25,'男');
insert into student values(102,'王五',30,'男');
insert into student values(103,'陈六',22,'女');
insert into student values(104,'王琪',22,'女');
-- 造数课程表course
insert into course values(200,'数据结构',300);
insert into course values(201,'数据库',301);
insert into course values(202,'C语言',302);
insert into course values(203,'JAVA',303);
insert into course values(204,'python',304);
insert into course values(205,'PHP',305);
insert into course values(206,'自动化',306);
-- 造数成绩表
insert into sc values(100,200,50);
insert into sc values(100,201,51);  
insert into sc values(100,202,52);
insert into sc values(100,203,53);
insert into sc values(100,204,54);
insert into sc values(100,205,55);
insert into sc values(101,204,56);
insert into sc values(102,200,57);
insert into sc values(102,201,58);
insert into sc values(102,202,59);
insert into sc values(103,202,60);
insert into sc values(103,203,61);
insert into sc values(103,204,62);
insert into sc values(103,205,63);
-- 造数教师表teacher
insert into teacher values(300,'张平');
insert into teacher values(301,'李平');
insert into teacher values(302,'陈平');
insert into teacher values(303,'王平');
insert into teacher values(304,'叶平');
insert into teacher values(305,'刘平');
insert into teacher values(306,'赵平');
insert into teacher values(307,'钱平');

- SQL练习，左连接、右连接、子查询

1、查询张三的总成绩
select sum(c.score) from student s, sc c
where s.sno = c.sno
and s.sname = '张三';

2、查询平均成绩大于60分的同学的学号和平均成绩
select sno,avg(score) from sc
group by sno
having avg(score) > 60

3、查询学过“叶平”老师课的同学的学号，姓名
select s.sno,s.sname from student s, sc c, course e,teacher t
where s.sno = c.sno and e.cno = c.cno and t.tno = e.tno
and t.tname = '叶平'

4、查询没学过“叶平”老师课的同学的学号，姓名
select sno,sname  from student
where sname not in(select s.sname  from student s,course c,sc s1,teacher t
where  (t.tno=c.tno and c.cno=s1.cno and s1.sno=s.sno) and t.tname='叶平')

select sname from student where sname not in (select s.sname from student s, sc c, course e,teacher t
where s.sno = c.sno and e.cno = c.cno and t.tno = e.tno
and t.tname = '叶平')

--左外连接:以主表为主，从表符合条件的就跟主表关联成新的一条数据，没有符合的就用空行跟主表拼接成一条新的数据
第一种写法：
select * from sc s, course c
where s.cno = c.cno(+)
第二种写法：
select * from sc s left join course c on s.cno = c.cno;

--右外连接:以从表为主，主表表符合条件的就跟从表关联成新的一条数据，没有符合的就用空行跟从表拼接成一条新的数据
第一种写法：
select * from sc s, course c
where s.cno(+) = c.cno

第二种写法：
select * from sc s right join course c on s.cno = c.cno;
--全外连接：展示所有
select * from sc s full join course c on s.cno = c.cno;

1、查询没有员工的部门
select * from dept;
select distinct(deptno) from emp;

select d.deptno,d.dname from emp e, dept d
where e.deptno(+) = d.deptno
and empno is null


select deptno 没有员工的部门 from dept where deptno not in
(select distinct(d.deptno) from emp e,dept d where e.deptno = d.deptno)

2、查询没有选修课程的学生姓名
select s.sname from student s, sc c
where s.sno = c.sno(+)
and c.cno is null;

select sname from student where sno not in (select distinct(sno) from sc);


3、查询哪些课程没有被选修
右连接：
select c.cname from sc s,course c
where s.cno(+) = c.cno
and s.cno is null;

左连接：
select c.cname from course c,sc s
where c.cno = s.cno(+)
and s.cno is null;

子查询：
select cname from course where cno not in (select distinct(cno) from sc) 

4、查询哪些老师没有授课
--右外连接
select t.tname from course c,teacher t
where c.tno(+) = t.tno
and c.tno is null

--左外连接
select t.tname from teacher t,course c
where t.tno = c.tno(+)
and c.cno is null;

---子查询
select tname from teacher where tno not in
(select distinct(tno) from course);

 - 新增必修课字段

alter table course add cpno varchar2(20); --新增必修课cpno
insert into course(cpno) values('201');
insert into course(cpno) values('202');
insert into course(cpno) values('205');
insert into course(cpno) values('206');
insert into course(cpno) values('');
insert into course(cpno) values('203');
insert into course(cpno) values('204');

-

 

 - 写个sql语句，查询全部学生都选修的课程的课程号和课程名

-- 写个sql语句，查询全部学生都选修的课程的课程号和课程名

假设我们有以下三个表：

学生表 students，包含学生的信息：
id   | name
-----|-----
1    | Tom
2    | Jerry
3    | Alice
4    | Bob
选课表 course_selections，记录学生选课的信息：
id   | student_id | course_id
-----|------------|----------
1    | 1          | 101
2    | 2          | 102
3    | 3          | 101
4    | 4          | 102
5    | 1          | 102
6    | 2          | 101
7    | 3          | 102
8    | 4          | 101
课程表 courses，记录课程的信息：
id   | name
-----|-----
101  | Math
102  | English
103  | History
104  | Chemistry
以下是查询全部学生都选修的课程的课程号和课程名的 SQL 语句：

SELECT c.id, c.name
FROM courses c
WHERE NOT EXISTS (
   SELECT id FROM students WHERE NOT EXISTS (
      SELECT id FROM course_selections WHERE course_id = c.id AND student_id = students.id
   )
 );
上面的 SQL 语句中， NOT EXISTS 子查询用于查找全部学生都选修的课程。首先在 course_selections 表中找到选择了某个课程的所有学生，然后用 NOT EXISTS 判断是否存在某个学生没有选修该课程。如果不存在这样的学生，则该课程是全部学生都选修的课程。最后从 courses 表中获取课程名和课程号。

 

end