创建表student,并插入数据 字段 类型 说明 stuno number(5) 学生编号,主键 stuname varchar2(30) 学生名称 gender number 学生性别 age number(2) 学生年龄 birthday date 学生生日 学生信息 stuno stuname gender age birthday 101 张x三x 1990-8-25 201 x李四x 男 1991-7-1 301 王五六 1993-12-28 1.查询生日是当月月末的学生信息。 2.查询生日是当月月初的学生信息。 3.查询生日是当月20日的学生信息。 4.计算根据当前日期及学生生日计算每名学生年龄并更新学生表age列。 5.查询每名同学的下一个生日还有多少天。 6.查询学生信息,年龄为空显示0,性别为1显示‘男’,2显示’女’,其他显示’未知’。 7.去掉所有学生姓名中的’x’。 8.将学生姓名统一按照最长(字节长度)学生姓名从左补齐,补齐字符用’#’。 9.按照出生年份分组统计学生人数,输出信息包括出生年和人数。 10.按照出生年份和月份分组统计学生人数,输出信息包括出生
时间: 2024-02-03 14:13:02 浏览: 102
1. 查询生日是当月月末的学生信息:
```sql
SELECT * FROM student
WHERE EXTRACT(MONTH FROM birthday) = EXTRACT(MONTH FROM SYSDATE)
AND EXTRACT(DAY FROM LAST_DAY(birthday)) = EXTRACT(DAY FROM SYSDATE);
```
2. 查询生日是当月月初的学生信息:
```sql
SELECT * FROM student
WHERE EXTRACT(MONTH FROM birthday) = EXTRACT(MONTH FROM SYSDATE)
AND EXTRACT(DAY FROM birthday) = 1;
```
3. 查询生日是当月20日的学生信息:
```sql
SELECT * FROM student
WHERE EXTRACT(MONTH FROM birthday) = EXTRACT(MONTH FROM SYSDATE)
AND EXTRACT(DAY FROM birthday) = 20;
```
4. 计算根据当前日期及学生生日计算每名学生年龄并更新学生表age列:
```sql
UPDATE student SET age = TRUNC(MONTHS_BETWEEN(SYSDATE, birthday) / 12);
```
5. 查询每名同学的下一个生日还有多少天:
```sql
SELECT stuno, stuname,
CASE
WHEN ADD_MONTHS(birthday, 12*(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birthday))) >= SYSDATE
THEN TRUNC(ADD_MONTHS(birthday, 12*(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birthday))) - SYSDATE)
ELSE TRUNC(ADD_MONTHS(birthday, 12*(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birthday))+12) - SYSDATE)
END AS days_to_birthday
FROM student;
```
6. 查询学生信息,年龄为空显示0,性别为1显示‘男’,2显示’女’,其他显示’未知’:
```sql
SELECT stuno, stuname,
CASE gender
WHEN 1 THEN '男'
WHEN 2 THEN '女'
ELSE '未知'
END AS gender,
COALESCE(age, 0) AS age,
birthday
FROM student;
```
7. 去掉所有学生姓名中的’x’:
```sql
UPDATE student SET stuname = REPLACE(stuname, 'x', '');
```
8. 将学生姓名统一按照最长(字节长度)学生姓名从左补齐,补齐字符用’#’:
```sql
SELECT stuno, RPAD(stuname, LENGTH(MAX(stuname)) OVER (), '#') AS stuname, gender, age, birthday
FROM student;
```
9. 按照出生年份分组统计学生人数,输出信息包括出生年和人数:
```sql
SELECT EXTRACT(YEAR FROM birthday) AS birth_year, COUNT(*) AS num_students
FROM student
GROUP BY EXTRACT(YEAR FROM birthday);
```
10. 按照出生年份和月份分组统计学生人数,输出信息包括出生年、月和人数:
```sql
SELECT EXTRACT(YEAR FROM birthday) AS birth_year, EXTRACT(MONTH FROM birthday) AS birth_month, COUNT(*) AS num_students
FROM student
GROUP BY EXTRACT(YEAR FROM birthday), EXTRACT(MONTH FROM birthday);
```
阅读全文