Oracle试卷02

1. 以系统管理员身份登录，创建账号tom，设置tom的默认表空间为neuspace。为tom分配connect和resource系统角色，获取基本的系统权限。然后为tom分配对用户scott的表emp的select权限。（5分）

2.  按如下要求创建表class和student。（10分）

属性	类型(长度)	默认值	约束	含义
CLASSNO	数值 (2)	无	主键	班级编号
CNAME	变长字符 (10)	无	非空	班级名称

 

属性	类型(长度)	默认值	约束	含义
STUNO	数值 (8)	无	主键	学号
SNAME	变长字符 (12)	无	非空	姓名
SEX	字符 (2)	男	无	性别
BIRTHDAY	日期	无	无	生日
EMAIL	变长字符 (20)	无	唯一	电子邮件
SCORE	数值 (5, 2)	无	检查	成绩
CLASSNO	数值 (2)	无	外键，关联到表CLASS的CLASSNO主键	班级编号

3. 在表student的SNAME属性上创建索引student_sname_idx（5分）

4. 创建序列stuseq，要求初值为20050001，增量为1，最大值为20059999。（5分）

5. 向表student中插入如下2行。（5分）

STUNO	SNAME	SEX	BIRTHDAY	EMAIL	SCORE	CLASSNO
从stuseq取值	tom	男	1979-2-3 14:30:25	tom@163.net	89.50	1
从stuseq取值	jerry	默认值	空	空	空	2

6. 修改表student的数据，将所有一班的学生成绩加10分。（5分）

7. 删除表student的数据，将所有3班出生日期小于1981年5月12日的记录删除。（5分）

8. 完成以下SQL语句。(40分)

(1) 按班级升序排序，成绩降序排序，查询student表的所有记录。

(2) 查询student表中所有二班的成绩大于85.50分且出生日期大于1982-10-31日的男生的记录。

(3) 查询student表中所有三班成绩为空的学生记录。

(4) 表student与class联合查询，要求查询所有学生的学号，姓名，成绩，班级名称

(5) 按班级编号分组统计每个班的人数，最高分，最低分，平均分，并按平均分降序排序。

(6) 查询一班学生记录中所有成绩高于本班学生平均分的记录。

(7) 统计二班学生中所有成绩大于所有班级平均分的人数。

(8) 查询平均分最高的班级编号与分数。

(9) 查询所有学生记录中成绩前十名的学生的学号、姓名、成绩、班级编号。 

(10) 创建视图stuvu，要求视图中包含student表中所有一班学生的stuno, sname, score, classno四个属性。

9.jdbc（20分）

(1)编写addStudent()方法实现向student表中插入数据

(2)编写getStudentList()方法查询student表中所有数据，并放入list中

答案：

 --1.以系统管理员身份登录，创建账号tom，设置tom的默认表空间为neuspace。
--为tom分配connect和resource系统角色，获取基本的系统权限。然后为tom分配对用户scott的表emp的select权限。
create user tom identified by 123;
grant connect,resource to tom;
grant select on scott.emp to tom;

--2.  按如下要求创建表class和student.
create table class(
classno number(2) primary key,
cname varchar2(10) not NULL
);
create table student(
stuno number(8) primary key,
sname varchar2(12) not null,
sex char(2) default '男',
birthday date,
email varchar2(20) UNIQUE,
score number(5,2),
classno number(2),
constraint fk_classno foreign key(classno) references class(classno)
);

--3. 在表student的SNAME属性上创建索引student_sname_idx
create index student_sname_idx on student(sname);

--4. 创建序列stuseq，要求初值为20050001，增量为1，最大值为20059999。
create sequence stuseq
INCREMENT by 1
start with 20050001
maxvalue 20059999
nocycle
cache 10;

--5. 向表student中插入如下2行。
--首次得向class表中插入数据，否则违反外键约束
insert into class values(1,'清华班');
insert into class values(2,'北大班');
insert into class values(3,'复旦班');
insert into student values(stuseq.nextval,'tom','男',
to_date('1979-2-3 14:30:25','yyyy-mm-dd hh24:mi:ss'),'tom@l63.net',89.50,1);
insert into student(stuno,sname,classno) values(stuseq.nextval,'jerry',2);
--insert into student values(stuseq.nextval,'jerry',default,null,null,null,2)

--6. 修改表student的数据，将所有一班的学生成绩加10分。
update student set score=score+10 where classno=1;

--7. 删除表student的数据，将所有3班出生日期小于1981年5月12日的记录删除。
insert into student values(stuseq.nextval,'tom','男',
to_date('1979-2-3 14:30:25','yyyy-mm-dd hh24:mi:ss'),'tom@l63.om',89.50,3);
delete student where classno=3 and birthday<to_date('1981-5-12','yyyy-mm-dd');

--8. 完成以下SQL语句。
--(1) 按班级升序排序，成绩降序排序，查询student表的所有记录。
select * from student order by classno asc,score desc;
--(2) 查询student表中所有二班的成绩大于85.50分且出生日期大于1982-10-31日的男生的记录。
select * from student where classno=2 and score>85.5 and birthday>to_date('1982-10-31','yyyy-mm-dd');
--(3) 查询student表中所有三班成绩为空的学生记录。
select * from student where classno=3 and score is null;
--(4) 表student与class联合查询，要求查询所有学生的学号，姓名，成绩，班级名称
select stuno,sname,score,cname from class,student where class.classno=student.classno;
--(5) 按班级编号分组统计每个班的人数，最高分，最低分，平均分，并按平均分降序排序。
select count(1),max(score),min(score),avg(score) from student group by classno order by avg(score);
--(6) 查询一班学生记录中所有成绩高于本班学生平均分的记录。
select * from student where stuno in(select stuno from student where score>(select avg(score) from student where classno=1 group by classno));
--(7) 统计二班学生中所有成绩大于所有班级平均分的人数。
select count(1) from student where stuno in(select stuno from student where classno=2 and score>(select avg(score) from student));
--(8) 查询平均分最高的班级编号与分数。
select classno,score from student where classno in(select classno from student group by classno
having avg(score)>=all(select avg(score) from student GROUP BY classno));
--(9) 查询所有学生记录中成绩前十名的学生的学号、姓名、成绩、班级编号。
select stuno,sname,score,classno from (select * from student s order by score desc) where rownum<=10;
--(10) 创建视图stuvu，要求视图中包含student表中所有一班学生的stuno, sname, score, classno四个属性。
create view stuvu as select stuno,sname,score,classno from student;
--9.jdbc
-- (1)编写addStudent()方法实现向student表中插入数据
-- (2)编写getStudentList()方法查询student表中所有数据，并放入list中

jdbc.properties文件配置：

username=system
userpwd=123
url=jdbc\:oracle\:thin\:@localhost\:1521\:orcl

package demo;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/*
 *	9.jdbc
 *	(1)编写addStudent()方法实现向student表中插入数据
 *	(2)编写getStudentList()方法查询student表中所有数据，并放入list中
 */
public class JDBC_Student {
	static Connection conn=null;
	static PreparedStatement pre=null;
	static ResultSet rs=null;
	static List<Student> stu_list=new ArrayList<Student>();
	public static void main(String[] args) {
		Properties pro=new Properties();
		try {
			//1.加载数据库驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.获取数据库连接
			pro.load(new FileInputStream("jdbc.properties"));
			String url=pro.getProperty("url");
			String user=pro.getProperty("username");
			String password=pro.getProperty("userpwd");
			conn=DriverManager.getConnection(url, user, password);
			//3.编写sql语句
			String sql="insert into student values(?,?,?,?,?,?,?)";
			String sql1="select * from student";
			addStudent(sql,45,"link","男",null,"1456",50,1);
			stu_list=getStudentList(sql1);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(pre!=null){
					pre.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static List<Student> getStudentList(String sql) {
		List<Student> list=new ArrayList<Student>();
		try {
			pre=conn.prepareStatement(sql);
			rs=pre.executeQuery();
			while(rs.next()){
				Student s=new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setBirthday(rs.getDate("birthday"));
				s.setEmail(rs.getString("email"));
				s.setScore(rs.getInt("score"));
				s.setClassno(rs.getInt("classno"));
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	private static void addStudent(String sql,Object ...objects) {
		try {
			pre=conn.prepareStatement(sql);
			for(int i=0;i<objects.length;i++){
				pre.setObject(i+1,objects[i]);
			}
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package demo;
import java.sql.Date;
public class Student {
	private int stuno;
	private String sname;
	private String sex;
	private Date birthday;
	private String email;
	private double score;
	private int classno;
	public int getStuno() {
		return stuno;
	}
	public void setStuno(int stuno) {
		this.stuno = stuno;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public double getScore() {
		return score;
	}
	public void setScore(double score) {
		this.score = score;
	}
	public int getClassno() {
		return classno;
	}
	public void setClassno(int classno) {
		this.classno = classno;
	}	
}