# SDUDatabaseSystemDesignLab

**Repository Path**: michaelod/SDUDatabaseSystemDesignLab

## Basic Information

- **Project Name**: SDUDatabaseSystemDesignLab
- **Description**: SDUDatabaseSystemDesignLab
- **Primary Language**: SQL
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2019-04-02
- **Last Updated**: 2020-12-18

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

*1-1*

```sql
CREATE TABLE test1_student

(

sid char(12) NOT NULL,

name varchar(10) not null,

sex char(2),

age int,

birthday date,

dname varchar(30),

class varchar(10)

)

```

*1-2*

```sql
CREATE TABLE test1_course
(
cid char(6) NOT NULL,
name varchar(40) not null,
fcid char(6),
credit numeric(4,1)
)
```

*1-3*

```sql
CREATE TABLE test1_student_course
(
sid char(12) NOT NULL,
cid char(6) not null,
score numeric(5,1),
tid char(6)
)
```

*1-4*

```sql
insert into test1_student values('200800020101','王欣','女',19,date '1994-02-02','计算机学院','2010')

insert into test1_student values('200800020102','李华','女',19,date '1995-03-03','软件学院','2009')
```

*1-5*

```sql
insert into test1_course values('300001','数据结构',NULL,'2')

insert into test1_course values('300002','数据库',300001,'2.5')
```

*1-6*

```sql
insert into test1_student_course values('200800020101','300001',91.5,'100101')

insert into test1_student_course values('200800020101','300002',92.6,'100102')
```

*2-1*

```sql
Create or replace view test2_01 as SELECT pub.student.sid,pub.student.name FROM pub.student WHERE pub.student.sid NOT IN (SELECT DISTINCT pub.student_course.sid FROM pub.student_course)
```

*2-2*

```sql
Create or replace view test2_02 as select sid,name from pub.student where sid in (select sid from pub.student_course where cid in(select cid from pub.student_course where sid=200900130417))
```

*2-3*

```sql
Create or replace view test2_03 as select name,sid from pub.student where sid in(select sid from pub.student_course where cid in (select cid from pub.course where fcid=300002))
```

*2-4*

```sql
Create or replace view test2_04 as select a.sid,a.name from pub.student a,pub.course b,pub.student_course c where a.sid=c.sid and b.cid=c.cid and b.name='操作系统' intersect (select a.sid,a.name from pub.student a,pub.course b,pub.student_course c where a.sid=c.sid and b.cid=c.cid and b.name='数据结构')
```

*2-5*

```sql
Create or replace view test2_05 as select student.sid,name,cast(avg(score)  as  numeric(5,0)) avg_score,sum(score) sum_score from pub.student,pub.student_course where student.sid = student_course.sid and age ='20'  group by student.sid,name
```

*2-6*

```sql
create or replace view test2_06 as select cid,name,max_score,max_score2,max_score_count from 
(select cid,max(score) max_score from pub.student_course group by cid) 
natural left outer join 
(select cid,max(score) max_score2 from (select cid,max(score) max_score from pub.student_course group by cid) natural join pub.student_course where max_score>score group by cid) 
natural join 
(select cid,count(distinct sid) max_score_count from (select cid,max(score) max_score from pub.student_course group by cid) 
natural join 
pub.student_course where max_score=score group by cid) natural join pub.course
```

*2-7*

```sql
create or replace view test2_07 as select sid,name from pub.student where name not in(select name from pub.student where name like '张%' or name like '李%' or name like '王%')
```

*2-8*

```sql
create or replace view test2_08 as select substr(name,1,1) second_name,count(*) p_count from pub.student group by substr(name,1,1)
```

*2-9*

```sql
create or replace view test2_09 as select student.sid,student.name,score from pub.student,pub.student_course where student.sid = student_course.sid and cid ='300003'
```

*2-10*

```sql
create or replace view test2_10 as select sid,name from (select distinct sid from (select sid,cid,count(*) c from pub.student_course where score<60 group by sid,cid) a where a.c>=2) natural join pub.student
```

*3-1*

```sql
create table test3_01 as select * from pub.student_31 where regexp_like( sid,'^[[:digit:]]{12}$')
```

*3-2*

```sql
create table test3_02 as(select * from pub.student_31)
delete from test3_02 where sid in ( select sid from test3_02,( select sid a_sid,decode(age,2012-extract(year from birthday),1,0) compare from test3_02) where sid = a_sid and compare = 0)
```

*3-3*

```sql
create table test3_03 as (select * from pub.student_31)
delete from  test3_03 where sex!='女' and sex!='男' and sex is not null
```

*3-4*

```sql
create table test3_04 as(select * from pub.student_31)
delete  from test3_04 where dname is null or length(dname)<3 or length(dname) > length(trim(dname)) or length(dname) > length(replace(dname,' ','') ) 
```

*3-5*

```sql
create table test3_05 as (select * from pub.student_31)
delete from test3_05 where length(class) > 4
```

*3-6*

```sql
create table test3_06 as( select * from pub.student_31 )
delete from test3_06 where sid in (select sid from pub.student_31 where sid not in (select sid from pub.student_31 where regexp_like(sid,'^[[:digit:]]{12}$')))
delete from test3_06 where sid in (select sid from test3_06,(select sid a_sid,decode(age,2012-extract(year from birthday),1,0) compare from test3_06) where sid = a_sid and compare = 0)
delete from test3_06 where name in(select name from test3_06 where length(name)<2 or length(name)>length(trim(name)) or length(name) > length(replace(name,' ',''))）
delete from test3_06 where sex <>'男' and sex<>'女' and sex is not null
delete from test3_06 where sid in(select sid from test3_06 where dname is null or length(dname)<3 or length(dname)>length(trim(dname)) or length(dname) > length(replace(dname,' ',''))  )
delete from test3_06 where length(class)>4
```

*3-7*

```sql
create table test3_07 as( select * from pub.student_course_32)
delete from test3_07 where sid not in ( select sid from pub.student)
```

*3-8*

```sql
create table test3_08 as( select * from pub.student_course_32)
delete from test3_08 where (tid,cid) not in ( select tid,cid from pub.teacher_course )
```

*3-9*

```sql
create table test3_09 as( select * from pub.student_course_32 )
delete from test3_09 where score not in (select score from test3_09 where score between 0 and 100)
```

*3-10*

```sql
create table test3_10 as( select * from pub.student_course_32 )
delete from test3_10 where sid not in ( select sid from pub.student )
delete from test3_10 where cid not in ( select cid from pub.COURSE )
delete from test3_10 where tid not in ( select tid from pub.TEACHER )
delete from test3_10 where (tid,cid) not in ( select tid,cid from pub.teacher_course )
delete from test3_10 where score not in (select score from test3_10 where score between 0 and 100)
```

*4-1*

```sql
create table test4_01 as select * from pub.student_41
alter table test4_01 add (sum_score numeric(6, 1))
update test4_01 set sum_score = (select sum(score)  from pub.STUDENT_COURSE  where pub.STUDENT_COURSE.SID = test4_01.SID)

```

*4-2*

```sql
create table test4_02 as select * from pub.student_41
alter table test4_02 add (avg_score numeric(4, 1))
update test4_02 set avg_score = (select round(avg(score),1)  from pub.STUDENT_COURSE where pub.STUDENT_COURSE.SID = test4_02.SID)
```

*4-3*

```sql

create table test4_03 as select * from pub.student_41;
alter table test4_03 add sum_credit int;
update test4_03 a set (sum_credit) = (select sum(credit) sum_credit from (select * from ((select sid,cid,max(score) score from pub.student_course group by sid,cid) x join pub.course y on x.cid = y.cid) where score>=60) b where b.sid = a.sid
```

*4-4*

```sql
create table test4_04 as (select * from pub.STUDENT_41) update test4_04 a set dname = (
    select did
	from pub.DEPARTMENT b
	where a.DNAME = b.DNAME
)
where dname in (
	select dname
	from pub.DEPARTMENT
)
```

*4-5*

```sql
create table test4_05 as select * from pub.student_41;
alter table test4_05 add sum_score decimal(6,1);
alter table test4_05 add avg_score decimal(5,1);
alter table test4_05 add sum_credit decimal(5,1);
alter table test4_05 add did varchar2(2);
update test4_05 a set (sum_score,avg_score) = (select sum(b.score) sum_score,avg(b.score) avg_score from pub.student_course b where b.sid = a.sid);
update test4_05  a set (sum_credit) = (select sum(credit) sum_credit from (select * from ((select sid,cid,max(score) score from pub.student_course group by sid,cid) x join pub.course y on x.cid = y.cid) where score>=60) b where b.sid = a.sid);
update test4_05 set (did) = ('00');
update test4_05 a set (did) = (select did from (select * from pub.department union select * from pub.department_41) b where a.dname = b.dname);s
update test4_05 a set (did)=(select did from (select * from pub.department union select * from pub.department_41) b where a.dname = b.dname) where dname in (select c.dname from (select * from pub.department union select * from pub.department_41) c)
```

*4-6*

```sql
create table test4_06 as select * from pub.student_42;
update test4_06 a set (name) = (select translate(name,'/ ','/') from test4_06 b where a.sid = b.sid);
```

*4-7*

```sql
create table test4_07 as select * from pub.student_42;
update test4_07 a set (sex) = (select translate(sex,'/性','/') from test4_07 b where a.sid = b.sid);
update test4_07 a set (sex) = (select translate(sex,'/ ','/') from test4_07 b where a.sid = b.sid);
```

*4-8*

```sql
create table test4_08 as select * from pub.student_42;
update test4_08 a set (class) = (select translate(class,'/级','/') from test4_08 b where a.sid = b.sid);
```

*4-9*

```sql
create table test4_09 as select * from pub.student_42;
update test4_09 a set (age) =  2012 - extract(year from birthday) where age is null;
update test4_09 set age=2012-extract(year from birthday) where age is null
```

*4-10*

```sql
create table test4_10 as select * from pub.student_42;
update test4_10 a set (name,dname,sex,class) = (select translate(name,'/ ','/'), translate(dname,'/ ','/'),translate( translate(sex,'/性','/'),'/ ','/'),translate(class,'/级','/') from test4_10 b where a.sid = b.sid);
update test4_10 a set (age) =2012-extract(year from birthday) where age is null
```

*5-1*

```sql
create table test5_10(test varchar(20),age numeric (3));
insert into  test5_10 values('结果1',88)
insert into  test5_10 values('结果2',90)
insert into  test5_10 values('结果3',90)
insert into  test5_10 values('结果4',86)
insert into  test5_10 values('结果5',90)
insert into  test5_10 values('结果6',90)
insert into  test5_10 values('结果7',86)
insert into  test5_10 values('结果8',86)
insert into  test5_10 values('结果9',76)
insert into  test5_10 values('结果10',86)
```

*6-1*

```sql

```

*6-2*

```sql

```

*6-3*

```sql

```

*6-4*

```sql

```

*6-5*

```sql

```

*6-6*

```sql

```

*6-7*

```sql

```

*6-8*

```sql

```

*6-9*

```sql

```

*6-10*

```sql

```

*7-1*

```sql

```

*7-2*

```sql

```

*7-3*

```sql

```

*7-4*

```sql

```

*8-1*

```sql

```

*8-2*

```sql

```

*8-3*

```sql

```

*8-4*

```sql

```

*9-1*

```sql

```

*9-2*

```sql

```

*10-1*

```sql

```

*10-2*

```sql

```

*10-3*

```sql

```

*10-4*

```sql

```

*10-5*

```sql

```