数据库表格

最新推荐文章于 2023-05-27 14:38:26 发布

原创最新推荐文章于 2023-05-27 14:38:26 发布 · 259 阅读

0 ·

CC 4.0 BY-SA版权

本文档通过创建学生、课程、教师及成绩等数据库表格，并进行数据插入操作，展示了基本的数据查询方法，包括筛选、排序及聚合等功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表格：

create table Student(S# varchar2(10),Sname varchar2(10),Sage date,Ssex varchar2(10));
insert into Student values('01' , '赵雷' , to_date('1990-01-01','yyyy-mm-dd') , '男');
insert into Student values('02' , '钱电' , to_date('1990-12-21','yyyy-mm-dd') , '男');
insert into Student values('03' , '孙风' , to_date('1990-05-20','yyyy-mm-dd') , '男');
insert into Student values('04' , '李云' , to_date('1990-08-06','yyyy-mm-dd') , '男');
insert into Student values('05' , '周梅' , to_date('1991-12-01','yyyy-mm-dd') , '女');
insert into Student values('06' , '吴兰' , to_date('1992-03-01','yyyy-mm-dd') , '女');
insert into Student values('07' , '郑竹' , to_date('1989-07-01','yyyy-mm-dd') , '女');
insert into Student values('08' , '王菊' , to_date('1990-01-20','yyyy-mm-dd') , '女');

select * from Student;

create table Course(C# varchar2(10),Cname varchar2(10),T# varchar2(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

select * from Course;

create table Teacher(T# varchar2(10),Tname varchar2(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

select * from Teacher;

create table SC(S# varchar2(10),C# varchar2(10),score number(4,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

select * from SC;

--第一题

select job from emp group by job----查询不重复的工作岗位
--第二题
select ename,sal*12 as 年薪 from emp--查询年薪，起别名,别名不要用单引号括起来
--第三题
select empno as 雇员编号,ename as 姓名,sal as 工资,job as 职位 from emp--:,ename as 姓名是：,sal as 工资为：,job as 职位是：
--第四题
select * from emp
where job not in 'CLERK'

select * from emp
where job <> 'CLERK'--查询不是职位不是“CLERK”的员工信息（至少用2种方式查询）
--第五题
select * from emp
where ename like '%O%'and ename like '%T%'--查询下员工姓名中有O和T的
--第六题
select sal from emp order by sal desc--将员工工资按照由高到低的顺序排列
--第7题
select * from emp where sal>(select sal from emp where ename='SMITH')--要求查询出工资比SMITH工资要高的全部雇员信息
--第8题
select * from emp where sal>(select avg(sal)from emp)--要求查询出高于公司平均工资的全部雇员信息
--第9题
select deptno, count(deptno) from emp group by deptno
--查询出每个部门的编号、名称、位置、部门人数、平均工资
--第10题
select deptno, count(deptno) from emp group by deptno--统计各个部门的人数