Java面试题-----数据库部分_java 数据库面试题

1、有 3 个表（15 分钟）：【基础】

Student 学生表 (学号，姓名，性别，年龄，组织部门)

Course 课程表 (编号，课程名称)

Sc 选课表 (学号，课程编号，成绩)

建表语句

-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `sno` bigint NOT NULL COMMENT '学号',
  `sname` varchar(50) DEFAULT NULL COMMENT '姓名',
  `ssex` char(10) DEFAULT NULL COMMENT '性别',
  `sage` bigint DEFAULT NULL COMMENT '年龄',
  `sdept` varchar(50) DEFAULT NULL COMMENT '组织部门',
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES ('2010211112', '貂蝉', '男', '22', '研发部');
INSERT INTO `t_student` VALUES ('2010211113', '西施', '女', '23', '财务部');
INSERT INTO `t_student` VALUES ('2010211114', '周星驰', '男', '22', '研发部');
INSERT INTO `t_student` VALUES ('2010211115', '王昭君', '男', '22', '研发部');
 
-- ----------------------------
-- Table structure for t_course
-- ----------------------------
DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
  `cno` bigint NOT NULL COMMENT '课程号',
  `cname` varchar(50) DEFAULT NULL COMMENT '课程名称',
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
-- ----------------------------
-- Records of t_course
-- ----------------------------
INSERT INTO `t_course` VALUES ('978001123', '计算机组成原理');
INSERT INTO `t_course` VALUES ('978001124', 'Java基础');
INSERT INTO `t_course` VALUES ('978001125', 'JavaWeb设计');
INSERT INTO `t_course` VALUES ('978001126', '计算机网络');
 
-- ----------------------------
-- Table structure for t_sc
-- ----------------------------
DROP TABLE IF EXISTS `t_sc`;
CREATE TABLE `t_sc` (
  `grade` decimal(20,2) DEFAULT NULL COMMENT '分数',
  `sno` bigint DEFAULT NULL COMMENT '学号',
  `cno` bigint DEFAULT NULL COMMENT '课程号',
  KEY `fk_sno` (`sno`),
  KEY `fk_cno` (`cno`),
  CONSTRAINT `fk_cno` FOREIGN KEY (`cno`) REFERENCES `t_course` (`cno`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `t_student` (`sno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
-- ----------------------------
-- Records of t_sc
-- ----------------------------
INSERT INTO `t_sc` VALUES ('80.00', '2010211112', '978001123');
INSERT INTO `t_sc` VALUES ('85.00', '2010211113', '978001123');
INSERT INTO `t_sc` VALUES ('95.00', '2010211114', '978001123');
INSERT INTO `t_sc` VALUES ('91.00', '2010211114', '978001124');
INSERT INTO `t_sc` VALUES ('92.00', '2010211114', '978001125');
INSERT INTO `t_sc` VALUES ('88.00', '2010211114', '978001126');

-- 1.查询选修了'计算机原理'的学生学号和姓名
select stu.sno, stu.sname
from t_student stu
where (select count(*)
       from t_sc
       where sno = stu.sno
         and cno =
             (select cno from t_course where cname = '计算机原理')) != 0;
 
-- 2.查询’周星驰’同学选修了的课程名字
select cname
from t_course
where cno in (select cno
              from t_sc
              where sno = (select sno
                           from t_student
                           where sname = '周星驰'));
 
-- 3.查询选修了 5 门课程的学生学号和姓名
select stu.sno, stu.sname
from t_student stu
where
      (select count(*) from t_sc where stu.sno=stu.sno)=5;

-- 员工表
create table t_employee
(
    id         bigint      not null comment '主键',
    name       varchar(50) null comment '姓名',
    age        bigint      null comment '年龄',
    manager_id bigint      null comment '所属主管人',
    constraint t_employee_id_uindex
        unique (id)
)
    comment '员工表';
 
alter table t_employee
    add primary key (id);

-- 4. 列出所有年龄比所属主管年龄大的人的 ID 和名字
select emp.name
from t_employee emp
where emp.age > (select manager.age
                 from t_employee manager
                 where manager.id = emp.manager_id);

1）写出建表语句；（参考上题）略

2）写出 SQL 语句,查询选修了所有选修课程的学生；

3）写出 SQL 语句,查询选修了至少 5 门以上的课程的学生。

-- 5. 查询选修了所有选修课程的学生；
select stu.sno 学号, stu.sname 姓名, stu.sage 性别, stu.sdept 部门
from t_student stu
where (select count(*)
       from t_sc
       where sno = stu.sno) = (select count(*)
                               from t_course);
 
-- 6. 查询选修了至少 5 门以上的课程的学生;
select stu.sno, stu.sname
from t_student stu
where (select count(*) from t_sc where stu.sno = stu.sno) >= 5;

SQL语句：有如下两张表：【中等难度】

表 city： 表 state：

 

-- 7. 查询城市和省份信息；
select city.city_no     城市编号,
       city.city_name   城市名称,
       state.state_no   省份编号,
       state.state_name 省份名称
from t_city city,
     t_state state
where city.state_no = state.state_no
order by (city.city_no);

查询结果：

-- 8. 根据部门号从高到低，工资从低到高列出每个员工的信息；
select emp.name, emp.salary, emp.dept_id
from t_employee emp
order by dept_id desc, salary asc;

SQL执行顺序

select distinct
a.sex,
b.city,
count(1) as cnt,
sum(salary) as sum1
from table1 a
join table2 b on a.id=b.id
where a.name=b.name
group by a.sex,b.city
having cnt>=2
order by a.sex,b.city
limit 10

建表模型

-- 1.查询平均成绩大于60分的学生学号、姓名和平均成绩

select sc.s_id, s.s_name, avg(sc.score) avg_score
from tb_score sc
left join tb_student s on sc.s_id = s.s_id
group by sc.s_id
having avg(sc.score)>60;

-- 2.查询所有学生的学号、姓名、选课数、总成绩

select
s.s_id, s.s_name, count(sc.c_id) course_count, sum(sc.score) total_score
from tb_score sc
right join tb_student s on sc.s_id=s.s_id
group by s.s_id
order by s.s_id desc;

-- 3.查询姓"李"的老师个数

SELECT COUNT(t.t_id) teacher_count
FROM tb_teacher t
WHERE t.t_name LIKE '李%';

-- 4.查询选修 001 课程和002课程的学生的学号、姓名

select s.s_id, s.s_name
from tb_score sc
right join tb_student s on sc.s_id=s.s_id
right join tb_course c on sc.c_id=c.c_id
where c.c_id in ('KC001','KC002')
group by s.s_id, s.s_name;

-- 5.查询所有课程成绩小于60分的学生学号、姓名

select s.s_id, s.s_name
from tb_student s
where s.s_id in (select sc.s_id
                 from tb_score sc
                 where sc.score < 60
                 order by sc.s_id desc);

-- 6.删除"叶平"老师 的课 

delete
from tb_course c
where c.t_id in (select t.t_id from tb_teacher t where t.t_name like '叶平%');

-- 7.查询每门课被选到的学生 

select s.*, c.*
from tb_course c
inner join tb_score sc on c.c_id=sc.c_id
inner join tb_student s on sc.s_id=s.s_id
limit 0, 5;

-- 8.查询课程数据库、分数低于60分的学生姓名和分数

select s.s_name,sc.score,c.c_name
from tb_score sc
left join tb_course c on sc.c_id=c.c_id
left join tb_student s on sc.s_id = s.s_id
where sc.score < 60 and c.c_name like '数据库%';

-- 9.查询选修小于两门课程的学生学号

select s.s_id, s.*
from tb_score sc
right join tb_student s on sc.s_id=s.s_id
group by sc.s_id
having count(sc.c_id) <= 2;

-- 10.查询选修两门以上不及格的学生和平均成绩

select s.*, avg(sc.score) avg_score
from tb_score sc
right join tb_student s on sc.s_id=s.s_id
where sc.score < 60
group by sc.s_id
having count(sc.c_id) >=2;

在oracle中建表模型

-- 创建表空间
create tablespace Sys_SchoolDB
datafile 'c:\Sys_SchoolDB.dbf'
size 100m
autoextend on
next 10m
 
-- 创建用户
create user hfnu_112
identified by hfnu_112
default tablespace Sys_SchoolDB
 
-- 给用户授权给用户授权 dba
grant dba to hfnu_112
 
-- 查询所有用户
select * from dba_users;
 
-- 查看表空间
select tablespace_name from dba_tablespaces;