---创建学生表---
create table student(
STU_NO number(10) NOT NULL,
STU_NAME varchar2(20) NOT NULL,
STU_AGE number(10) NOT NULL,
constraint PK_STUDENT PRIMARY KEY(STU_NO)--设为主健
);
---插入数据---
insert into student
select 1001,'AE','12' from dual
union
select 1002,'BT','14' from dual
union
select 1003,'AS','20' from dual
union
select 1004,'SE','18' from dual;
commit;
--查询年纪为12的学生姓名--
SELECT STU_NAME FROM STUDENT WHERE STU_AGE='12'
--查询年纪在12至16岁之间的学生姓名--
SELECT STU_NAME FROM STUDENT WHERE STU_AGE BETWEEN '12' and '16'
--查询年纪不在12至16岁之间的学生姓名--
SELECT STU_NAME FROM STUDENT WHERE STU_AGE NOT BETWEEN '12' and '16'
---查询所有姓名以A开头的学生的姓名;---
SELECT STU_NAME FROM STUDENT WHERE STU_NAME LIKE 'A%'
---列出所有学生年纪的和---
SELECT SUM(STU_AGE) FROM STUDENT
---年纪的平均值---
SELECT avg(STU_AGE) FROM STUDENT
---最大值,最小值,最大值与最小值之间的差值;---
SELECT MAX(STU_AGE) FROM STUDENT
SELECT MIN(STU_AGE) FROM STUDENT
SELECT MAX(STU_AGE)-MIN(STU_AGE) FROM STUDENT
--- 将所有学生按学号顺序升序排列---
SELECT * FROM STUDENT order by STU_NO
--- 将所有学生按学号顺序降序排列---
SELECT * FROM STUDENT order by STU_NO desc
---创建COURSE表---
create table course(
COU_NO varchar2(10) NOT NULL,
COU_NAME varchar2(20) NOT NULL,
constraint PK_course PRIMARY KEY(COU_NO)--设为主健
);
select * from course
---插入数据---
insert into course
select 'A001','COURSE_NAME' from dual
union
select 'A002','SQLSERVER' from dual
union
select 'A003','JavaEE' from dual
union
select 'A004','.NET' from dual
---创建STUDENT_COURSE表---
create table STUDENT_COURSE
( stu_no number(10) not null,
course_no varchar2(10) not null);
select * from STUDENT_COURSE
---插入数据---
insert into STUDENT_COURSE
select 1001,'A001' from dual
union
select 1001,'A003' from dual
union
select 1004,'A002' from dual
union
select 1004,'A004' from dual
union
select 1004,'A001' from dual
union
select 1003,'A003' from dual
----创建自增长-----
CREATE SEQUENCE STU_NO_SEQ INCREMENT BY 1 START WITH 1001
MAXVALUE 99999 CYCLE NOCACHE;
INSERT into STUDENT_COURSE(STU_NO, COURSE_NO) values (STU_NO_SEQ .NEXTVAL, 'A001');
INSERT into STUDENT_COURSE(STU_NO, COURSE_NO) values (STU_NO_SEQ .NEXTVAL, 'A001');
INSERT into STUDENT_COURSE(STU_NO, COURSE_NO) values (STU_NO_SEQ .NEXTVAL, 'A001');
INSERT into STUDENT_COURSE(STU_NO, COURSE_NO) values (STU_NO_SEQ .NEXTVAL, 'A001');
DROP SEQUENCE STU_NO_SEQ
DROP table STUDENT_COURSE
---列出每个学生姓名以及他的学习的所有课程。---
select STU_NAME,COU_NAME from STUDENT s, course c,STUDENT_COURSE sc where s.stu_no=sc.stu_no and c.cou_no=sc.course_no
---查询出没有选修任何课程的学生的学号和姓名---
select STU_NO,STU_NAME from STUDENT s where s.stu_no not in (select stu_no from STUDENT_COURSE)
---统计每个学生学习的课程数量并按课程数从大到小排序。(包括没有学习任何课程的学生记录)---
SELECT STU_NAME, COUNT(COU_NO) FROM STUDENT s, course c
select STU_NAME, COUNT(course_no)
from student s left outer join student_course sc
on(s.stu_no=sc.stu_no)
group by s.stu_name
order by count(course_no) desc;
---查询所有学习JavaEE的学生的学号和姓名---
select STU_NO,STU_NAME from STUDENT s where STU_NO IN (
SELECT STU_NO FROM STUDENT_COURSE SC WHERE SC.COURSE_NO IN(
select COU_NO from course c where c.cou_name='JavaEE'))