【sql】训练四

最新推荐文章于 2023-06-03 22:49:40 发布

congjiu2607

最新推荐文章于 2023-06-03 22:49:40 发布

阅读量250

点赞数

1、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SQL> select d.department_name,d.department_id,e.first_name,e.department_id

2 tt from employees e,departments d where d.department_id = e.department_id or e.department_id is null;

2、查询每个员工的领导是谁（自连接）。

SQL> select w.employee_id,w.first_name,m.first_name from employees w join

2 employees m on w.manager_id = m.employee_id;

3、查询每个领导都管理哪些员工（自连接）

SQL> select m.first_name,m.employee_id,w.first_name from employees m join

2 employees w on m.employee_id = w.manager_id;

4、查询所有员工的信息并且列出员工的部门名称

SQL> select e.first_name,e.salary,e.employee_id,d.department_id from

2 employees e left join departments d on e.department_id = d.department_id;

5、要求查询雇员的编号、姓名、部门编号、部门名称及部门位置

SQL> select e.employee_id,e.first_name,d.department_id,d.department_name,

2 d.location_id from employees e full join departments d

3 on e.department_id = d.department_id;

6、创建一个用户：students密码是students，并且授予create session,resource权限。以students用户登录创建如下表：

写一个sql脚本，实现多对多关系（括号里的为约束）：学生表、课程表、学生选课表，学生表包含如下信息：学号（主键）、姓名（非空）、性别（非空）、出生日期、政治面貌，课程表包含如下信息：课程号（主键）、课程名称、学分、学时、学期，学生选课表包含如下信息：学号（外键参照学生表）、课程号（外键参照课程表）、成绩并模拟学生信息插入数据。

统计如下信息：

1、查看每个学生的都选了哪些课，显示学生姓名和课程名

2、统计每个学生选了几门课，显示学生姓名和课程数

3、显示每门课程的成绩，显示课程名称和成绩。

4、查询数据字典信息，查看约束名称，及对哪个字段创建了约束。

SQL> select OWNER,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME from USER_CONS_COLUMNS;

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAM

--------------- --------------- -------------------- ----------

STUDENTS XKB_CNO_FK XKB CNO

STUDENTS XKB_SNO_FK XKB SNO

STUDENTS SYS_C0011403 KC CNO

STUDENTS SYS_C0011402 STUDENT SNO

STUDENTS SYS_C0011401 STUDENT SEX

STUDENTS SYS_C0011400 STUDENT NAME

6 rows selected.

5、说明主键约束，惟一性约束，非空约束几个关系。

SQL> create user students identified by students;

User created.

SQL> grant create session to students;

Grant succeeded.

SQL> grant resource to students;

Grant succeeded.

SQL> conn students/students;

Connected.

SQL> show user

USER is "STUDENTS"

SQL> create table student(sno number primary key,name

2 varchar2(10) not null,sex varchar2(5) not null,

3 born date,politics_status varchar2(10));

Table created.

SQL> create table kc(cno number primary key,course varchar2(10),

2 xuefen number,xueshi number,term number);

Table created.

SQL> create table xkb(sno number,cno number,score number,

2 constraint xkb_sno_fk foreign key (sno) references student(sno),

3 constraint xkb_cno_fk foreign key (cno) references kc(cno));

Table created.

SQL> insert into student values(1,'www','nan','12-SEP-1988','qingbai');

1 row created.

SQL> insert into student values(2,'qq','nan','23-OCT-1991','qingbai');

1 row created.

SQL> insert into student values(3,'whb','nv',sysdate,'qingbai');

1 row created.

SQL> select * from student;

SNO NAME SEX BORN POLITICS_S

---------- ---------- ----- --------- ----------

1 www nan 12-SEP-88 qingbai

2 qq nan 23-OCT-91 qingbai

3 whb nv 07-SEP-16 qingbai

SQL> insert into kc values(1,'yuwen',6,33,1);

1 row created.

SQL> insert into kc values(2,'shuxue',8,66,1);

1 row created.

SQL> insert into kc values(3,'yingyu',9,78,1);

1 row created.

SQL> select * from kc；

CNO COURSE XUEFEN XUESHI TERM

---------- ---------- ---------- ---------- ----------

1 yuwen 6 33 1

2 shuxue 8 66 1

3 yingyu 9 78 1

SQL> insert into xkb values(1,1,88);

1 row created.

SQL> insert into xkb values(2,2,95);

1 row created.

SQL> insert into xkb values(3,3,80);

1 row created.

SQL> insert into xkb values(1,2,50);

1 row created.

SQL> insert into xkb values(1,3,66);

1 row created.

SQL> insert into xkb values(2,1,88);

1 row created.

SQL> insert into xkb values(2,3,77);

1 row created.

SQL> insert into xkb values(3,1,76);

1 row created.

SQL> insert into xkb values(3,2,81);

1 row created.

SQL> select * from xkb order by 1,2;

SNO CNO SCORE

---------- ---------- ----------

1 1 88

1 2 50

1 3 66

2 1 88

2 2 95

2 3 77

3 1 76

3 2 81

3 3 80

9 rows selected.

SQL> commit;

Commit complete.

1）.SQL> select s.name,k.course from student s,kc k,xkb x

2 where s.sno=x.sno and k.cno=x.cno;

NAME COURSE

---------- ----------

www yuwen

qq shuxue

whb yingyu

www shuxue

www yingyu

qq yuwen

qq yingyu

whb yuwen

whb shuxue

9 rows selected.

2).SQL> select s.name,count(k.course) from student s,kc k,

2 xkb x where s.sno=x.sno and k.cno=x.cno

3 group by s.name;

NAME COUNT(K.COURSE)

---------- ---------------

whb 3

www 3

qq 3

3).SQL> select k.course,x.score,s.name from student s,kc k,xkb x where s.sno=x.sno and k.cno=x.cno;

COURSE SCORE NAME

---------- ---------- ----------

yuwen 76 whb

yuwen 88 qq

yuwen 88 www

shuxue 81 whb

shuxue 50 www

shuxue 95 qq

yingyu 77 qq

yingyu 66 www

yingyu 80 whb

9 rows selected.

4).SQL> desc user_constraints;

Name Null? Type

----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)

CONSTRAINT_NAME NOT NULL VARCHAR2(30)

CONSTRAINT_TYPE VARCHAR2(1)

TABLE_NAME NOT NULL VARCHAR2(30)

SEARCH_CONDITION LONG

R_OWNER VARCHAR2(30)

R_CONSTRAINT_NAME VARCHAR2(30)

DELETE_RULE VARCHAR2(9)

STATUS VARCHAR2(8)

DEFERRABLE VARCHAR2(14)

DEFERRED VARCHAR2(9)

VALIDATED VARCHAR2(13)

GENERATED VARCHAR2(14)

BAD VARCHAR2(3)

RELY VARCHAR2(4)

LAST_CHANGE DATE

INDEX_OWNER VARCHAR2(30)

INDEX_NAME VARCHAR2(30)

INVALID VARCHAR2(7)

VIEW_RELATED VARCHAR2(14)

SQL> select CONSTRAINT_NAME,TABLE_NAME,OWNER from user_constraints;

CONSTRAINT_NAME TABLE_NAME OWNER

--------------- ---------- ---------------

XKB_SNO_FK XKB STUDENTS

XKB_CNO_FK XKB STUDENTS

SYS_C0011403 KC STUDENTS

SYS_C0011402 STUDENT STUDENTS

SYS_C0011400 STUDENT STUDENTS

SYS_C0011401 STUDENT STUDENTS

6 rows selected.

SQL> desc user_tab_columns

Name Null? Type

----------------------------------------- -------- ----------------------------

TABLE_NAME NOT NULL VARCHAR2(30)

COLUMN_NAME NOT NULL VARCHAR2(30)

DATA_TYPE VARCHAR2(106)

DATA_TYPE_MOD VARCHAR2(3)

DATA_TYPE_OWNER VARCHAR2(30)

DATA_LENGTH NOT NULL NUMBER

DATA_PRECISION NUMBER

DATA_SCALE NUMBER

NULLABLE VARCHAR2(1)

COLUMN_ID NUMBER

DEFAULT_LENGTH NUMBER

DATA_DEFAULT LONG

NUM_DISTINCT NUMBER

LOW_VALUE RAW(32)

HIGH_VALUE RAW(32)

DENSITY NUMBER

NUM_NULLS NUMBER

NUM_BUCKETS NUMBER

LAST_ANALYZED DATE

SAMPLE_SIZE NUMBER

CHARACTER_SET_NAME VARCHAR2(44)

CHAR_COL_DECL_LENGTH NUMBER

GLOBAL_STATS VARCHAR2(3)

USER_STATS VARCHAR2(3)

AVG_COL_LEN NUMBER

CHAR_LENGTH NUMBER

CHAR_USED VARCHAR2(1)

V80_FMT_IMAGE VARCHAR2(3)

DATA_UPGRADED VARCHAR2(3)

HISTOGRAM VARCHAR2(15)

SQL> select TABLE_NAME,COLUMN_NAME,DATA_TYPE from user_tab_columns;

TABLE_NAME COLUMN_NAME DATA_TYPE

---------- ------------------------------ ----------

KC TERM NUMBER

KC XUESHI NUMBER

KC XUEFEN NUMBER

KC COURSE VARCHAR2

KC CNO NUMBER

STUDENT POLITICS_STATUS VARCHAR2

STUDENT BORN DATE

STUDENT SEX VARCHAR2

STUDENT NAME VARCHAR2

STUDENT SNO NUMBER

XKB SCORE NUMBER

TABLE_NAME COLUMN_NAME DATA_TYPE

---------- ------------------------------ ----------

XKB CNO NUMBER

XKB SNO NUMBER

13 rows selected.

查看约束：

SQL> select OWNER,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME from USER_CONS_COLUMNS;

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAM

--------------- --------------- -------------------- ----------

STUDENTS XKB_CNO_FK XKB CNO

STUDENTS XKB_SNO_FK XKB SNO

STUDENTS SYS_C0011403 KC CNO

STUDENTS SYS_C0011402 STUDENT SNO

STUDENTS SYS_C0011401 STUDENT SEX

STUDENTS SYS_C0011400 STUDENT NAME

6 rows selected.

5). 主键约束，惟一性约束，非空约束关系：

主键即唯一且为非空，主键可以为唯一键，而唯一键不能成为主键。

唯一键，只是唯一，它不满足非空要求，所以不能为主键。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2127036/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2127036/

congjiu2607

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【sql】训练四

1、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 SQL> selectd.department_name,d.department_id,e.first_name,e.depar...
复制链接

扫一扫