Day 7(11.14):(9)练习-1
来源:互联网 发布:虾囧笑话源码 编辑:程序博客网 时间:2024/06/05 00:39
-- 准备工作:
create table stu_info
(
t_number char(8),
t_name varchar(10),
t_gender char(2),
t_birthday datetime
)
create table course
(
c_number char(6),
c_name varchar(20),
c_credit int,
c_hour int,
c_teacher varchar(10)
)
create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)
drop table exam
drop table course
drop table stu_info
--向stu_info表插入数据
insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')
--向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')
insert into course values('100105','大学英语',NULL,80,'刘杰')
--向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)
-- 练习:
-- 1. 编写用户定义函数,要求根据学生学号、课程号查找出考试成绩。并调用函数查看学号为20040301、课程号为100101的成绩。
-- 2. 编写用户定义函数,要求根据姓名得到该学生的学生姓名和各科考试成绩。并调用函数查看学号为20040301的考试成绩
-- 3. 编写用户定义函数。要求得到每个学生的学生编号、学生姓名以及该学生的平均分。并调用函数查看查找每个
create table stu_info
(
t_number char(8),
t_name varchar(10),
t_gender char(2),
t_birthday datetime
)
create table course
(
c_number char(6),
c_name varchar(20),
c_credit int,
c_hour int,
c_teacher varchar(10)
)
create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)
drop table exam
drop table course
drop table stu_info
--向stu_info表插入数据
insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')
--向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')
insert into course values('100105','大学英语',NULL,80,'刘杰')
--向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)
-- 练习:
-- 1. 编写用户定义函数,要求根据学生学号、课程号查找出考试成绩。并调用函数查看学号为20040301、课程号为100101的成绩。
-- 2. 编写用户定义函数,要求根据姓名得到该学生的学生姓名和各科考试成绩。并调用函数查看学号为20040301的考试成绩
-- 3. 编写用户定义函数。要求得到每个学生的学生编号、学生姓名以及该学生的平均分。并调用函数查看查找每个
0 0
- Day 7(11.14):(9)练习-1
- Day 7(11.14):(9)练习-2
- Day 7(11.14):(10)练习答案
- Day 9:(5)简单查询练习参考答案
- Day 7 linux基础命令练习四
- [打字练习]Day One
- [打字练习]Day Two
- [打字练习]Day Five
- Day 7(11.14):(1)介绍视图
- Day 9:(0)数据完整性练习_参考答案
- Day 5(11.8):分组查询练习1答案
- Java 算法练习 ——— CodeWar Day 1
- Day 13 入门简单练习
- DAY 9
- Day 9
- Day 7
- day 7
- Day 1
- HDU_5563Clarke and five-pointed star
- 关于EDITTEXT的监听,以及限制特定字节数的实现
- Archlinux初步系统安装
- 《Android开发》——1.Activity之间的参数传递
- Apache Http Server与Tomcat实现负载均衡和集群
- Day 7(11.14):(9)练习-1
- Python学习路线及资源
- css3基础知识第三章div盒子
- 抽象类和最终类(被final修饰的类)、接口的区别
- Hibernate中常用HQL语句2015-11-14
- 沉思一为何他能成功
- Day 7(11.14):(9)练习-2
- springmvc3.2+mybatis2.3.7整合
- Switch中的参数