ALTER USER "yyf" ACCOUNT UNLOCK

1.创建表空间

CREATE TABLESPACE YYFTABLESPACE 
    DATAFILE 
        'D:\DevRepository\oracle\YYFTABLESPACE' SIZE 5242880 REUSE AUTOEXTEND ON NEXT 134217728 MAXSIZE UNLIMITED 
    NOLOGGING 
    DEFAULT NOCOMPRESS 
    ONLINE 
    EXTENT MANAGEMENT LOCAL

-- USER sql
CREATE USER yyf IDENTIFIED BY yyf 
DEFAULT TABLESPACE "YYFTABLESPACE"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT LOCK

-- QUOTAS

-- ROLES
GRANT "RESOURCE" TO yyf ;
GRANT "CONNECT" TO yyf ;
ALTER USER yyf DEFAULT ROLE "RESOURCE","CONNECT";

-- SYSTEM PRIVILEGES
GRANT UNLIMITED TABLESPACE TO yyf ;

ALTER USER "yyf" ACCOUNT LOCK

ALTER USER "yyf" ACCOUNT UNLOCK

ALTER USER "yyf" IDENTIFIED BY 123 

--01.查找学号为"20130101001"的学生信息
select * from user_info where user_id='20130101001';

--02.查找学号为"20130101001"的学生信息包含(专业名称,班级名称,学号,姓名,手机号码,家庭住址)

select p.professional_name as 专业名称,c.class_name as 班级名称,u.user_id as 学号,u.user_name as 姓名,u.user_tel as 手机号码,u.user_address as 家庭住址 
from user_info u  
inner join class_info c
on u.user_id='20130101001' and u.class_id= c.class_id
inner join professional_info p
on c.professional_id = p. professional_id;

--select class_id,class_name,professional_name from class_info c join professional_info p on c.professional_id = p.professional_id; 
--03.查找全校在读学生在1990年以后出生,并且家庭住址在湖南省的学生信息
SELECT u.user_id,u.user_name,u.class_id,u.user_address,c.CLASS_START_YEAR,c.CLASS_END_YEAR 
from user_info u inner join CLASS_INFO c on u.class_id =  c.CLASS_ID
and sysdate >= to_date(c.CLASS_START_YEAR,'yyyy') and sysdate <= to_date(c.CLASS_END_YEAR,'yyyy')
where user_birthday >= to_date('1990-01-01','yyyy-MM-dd') and user_address like '湖南%';

--04.查找本学期所有成绩都在80分以上的优先学生的信息
select * from(SELECT DISTINCT(r.user_id) FROM RESULT_INFO r inner join TERM_INFO t on r.TERM_ID = t.TERM_ID  and t.TERM_YEAR = 2015 and t.TERM_FIRST = 1
where r.user_id not in(
  SELECT r.USER_ID FROM RESULT_INFO r inner join TERM_INFO t on  r.RESULT_VALUE < 80 
  and r.TERM_ID = t.TERM_ID  and t.TERM_YEAR = 2015 and t.TERM_FIRST = 1
    ) 
) q inner join USER_INFO u on u.USER_ID = q.USER_ID order by u.user_id;

SELECT * from user_info where user_id in(
  SELECT user_id from result_info  where 
    term_id in(
      select term_id from term_info where term_year = 2015 and term_first = 1
    ) and
    user_id not in (
      SELECT user_id from result_info where term_id in(
        SELECT term_id from term_info where term_year = 2015 and term_first = 1
      )and result_value < 80
    ) 
) order by user_id;


--05.查找本学期所有成绩都在60分以下的需要补考的学生信息
select * from(SELECT DISTINCT(r.user_id) FROM RESULT_INFO r inner join TERM_INFO t on r.TERM_ID = t.TERM_ID  and t.TERM_YEAR = to_number(to_char(sysdate,'yyyy')) and t.TERM_FIRST = 1
where r.user_id not in(
  SELECT r.USER_ID FROM RESULT_INFO r inner join TERM_INFO t on  r.RESULT_VALUE >=70 
  and r.TERM_ID = t.TERM_ID  and t.TERM_YEAR = to_number(to_char(sysdate,'yyyy')) and t.TERM_FIRST = 1
    ) 
) q inner join USER_INFO u on u.USER_ID = q.USER_ID;

--06.查找"20130101"班在当前学期需要学习的课程信息
select * from course_info
where course_id in(
  select course_id from result_info where user_id in(
    select user_id from user_info where class_id = '20130101'
  )and term_id in (select term_id from term_info t where t.TERM_YEAR = '2015' and t.TERM_FIRST = 1 )
);
--07.统计2013级的各班级的人数(班级编号,专业名称,班级人数)
select t.class_id as 班级编号,p.PROFESSIONAL_NAME as 专业名称,t.班级人数 from class_info c 
inner join (select count(u.CLASS_ID) as 班级人数,u.CLASS_ID from USER_INFO u group by u.CLASS_ID) t 
on c.CLASS_START_YEAR = '2013' and c.CLASS_ID = t.CLASS_ID 
inner join PROFESSIONAL_INFO p on p.PROFESSIONAL_ID = c.PROFESSIONAL_ID
order by t.CLASS_ID;

--08.编写"20130101"班"大学英语"的成绩表的sql语句
--select max(成绩) from(
SELECT u.user_id as 学号,u.USER_NAME as 姓名,r.RESULT_VALUE as 成绩 FROM RESULT_INFO r
inner join USER_INFO u on u.CLASS_ID = '20130101' and r.USER_ID = u.USER_ID
where r.COURSE_ID = -1 and r.TERM_ID = 8 order by r.RESULT_VALUE desc ;
--);

create or replace procedure yyf is
begin
  insert into role_info(role_id,role_info) values(3,'老师1');
  dbms_output.putline('执行成功！');
  commit;
end yyf;