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). 主鍵約束，惟一性約束，非空約束關係：

主鍵即唯一且為非空，主鍵可以為唯一鍵，而唯一鍵不能成為主鍵。

唯一鍵，只是唯一，它不滿足非空要求，所以不能為主鍵。

【sql】訓練四

相關文章