数据库SQL查询练习

--重点推荐使用natural join,inner join!!!,尽量避免使用广义的笛卡尔积
--重点推荐使用natural join,inner join!!!,尽量避免使用广义的笛卡尔积
--重点推荐使用natural join,inner join!!!,尽量避免使用广义的笛卡尔积

--设有一个SPJ数据库，
--包括S,P,J,SPJ四个关系模式：

--供应商表S(英文全称为:supplier)
--S(SNO,SNAME,STATUS,CITY)
--供应商代码Sno,供应商名称Sname,供应商状态Status,供应商所在城市City

--零件表P(英文名称为:Part)
--P(PNO,PNAME,COLOR,WEIGHT)
--Pno零件代码,Pname零件名称,Color零件的颜色,Weight零件的重量

--工程项目表J(英文全称为Project,因为之前已经使用了P作为零件的简称,所以这里选择了J作为项目工程表的名字)
--J(JNO,JNAME,CITY)
--工程项目代码Jno,工程项目名称Jname,工程项目所在城市City

--供应情况表SPJ(英文全称supplier-part-project)
--SPJ(SNO,PNO,JNO,QTY)
--供应商代码Sno,零件代码Pno,工程项目代码Jno,供应数量Qty

  /*
  --供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成；
  --S(SNO,SNAME,STATUS,CITY)
             S表
            SNO  SNAME  STATUS  CITY
            S1  精益  20  天津
            S2  盛锡  10  北京
            S3  东方红  30  北京
            S4  丰泰盛  20  天津
            S5  为民  30  上海

--零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；
--P(PNO,PNAME,COLOR,WEIGHT)
            P表
            PNO  PNAME  COLOR  WEIGHT
            P1  螺母  红  12
            P2  螺栓  绿  17
            P3  螺丝刀  蓝  14
            P4  螺丝刀  红  14
            P5  凸轮  蓝  40
            P6  齿轮  红  30

--工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成；
--J(JNO,JNAME,CITY)
            J表
            JNO  JNAME  CITY
            J1  三建  北京
            J2  一汽  长春
            J3  弹簧厂  天津
            J4  造船厂  天津
            J5  机车厂  唐山
            J6  无线电厂  常州
            J7  半导体厂  南京

--供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，
--表示某供应商 供应某种零件 给某工程项目的数量为QTY。
--SPJ(SNO,PNO,JNO,QTY)
            SPJ表
            SNO  PNO  JNO  QTY
            S1  P1  J1  200
            S1  P1  J3  100
            S1  P1  J4  700
            S1  P2  J2  100
            S2  P3  J1  400
            S2  P3  J2  200
            S2  P3  J4  500
            S2  P3  J5  400
            S2  P5  J1  400
            S2  P5  J2  100
            S3  P1  J1  200
            S3  P3  J1  200
            S4  P5  J1  100
            S4  P6  J3  300
            S4  P6  J4  200
            S5  P2  J4  100
            S5  P3  J1  200
            S5  P6  J2  200
            S5  P6  J4  500
  */
请根据以上内容完成下面题目，写出相应的SQL语句.
2、创建表
--创建供应商表S
--供应商代码Sno,供应商名称Sname,供应商状态Status,供应商所在城市City
create table S
(
       Sno char(2) primary key,           --假设供应商代码的长度为定长2
       Sname varchar2(9) ,--假设供应商的名称最多为三个汉字
       Status varchar2(3)  not null,--假设供应商供应状态为最多有三个数字
       City varchar2(15)    not null        --假设城市最长为5个字符的汉字
);            

drop table S;
--查询S表的所有信息
select * from S;
--查询表的结构


--创建零件P表
--Pno零件代码,Pname零件名称,Color零件的颜色,Weight零件的重量
create table P
(
       Pno char(2), --假设Pno为2位定长的字符
       Pname    varchar2(9) not null,    --假设Pname最多有三个汉字
       Color char(3) check (Color in('红','绿','蓝')), --颜色只能是一个汉字,并且这个字只能为红绿蓝三种中的一种
       Weight smallint not null,--重量是个整数,并且不能为空
       constraint PK_P primary key (Pno)
);


--删除P表定义及其数据
drop table P;
--查询P表的所有信息
select * from P;

--创建工程项目表J表
--工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成；
create table J
(
       Jno char(2) , --假设工程项目代码是由定长的两位字符组成
       Jname varchar2(12), --假设工程名称最多为长度为4的汉字组成
       City varchar2(15), --假设工程项目所在的城市的名称最多含有5个汉字
       constraint PK_J primary key (Jno)
);


--删除J表定义及其数据
drop table J;
--查询J表的所有信息
select * from J;

--创建供应情况表SPJ表
--供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成
create table SPJ
(
       Sno char(2),
       Pno char(2),
       Jno char(2),
       Qty int constraint C1 check(Qty between 0 and 1000),
       constraint SPJKey primary key(Sno,Pno,Jno),
       constraint FK_Sno foreign key(Sno) references S(Sno) on delete cascade,
       constraint FK_Pno foreign key(Pno) references P(Pno) on delete cascade,
       constraint FK_Jno foreign key(Jno) references J(Jno) on delete cascade
);

--删除SPJ表定义及其数据
drop table SPJ;
--查询SPJ表的所有信息
select * from SPJ;

--使用该语句可以查询当前数据库的字符编码:我这里是
--SIMPLIFIED CHINESE_CHINA.ZHS16GBK
--如果是UTF-8:则一个汉字占三个字符的大小
--如果是GBK:则一个汉字占两个字符的大小
--为了避免出错:这里我统一按照一个汉字占三个字符的标准(UTF-8)
select userenv('language') from dual;

3、插入数据
--向供应商表中插入数据
--向S表中插入数据
  --S表
          --   SNO  SNAME  STATUS  CITY
          --   S1  精益  20  天津
          --   S2  盛锡  10  北京
          --   S3  东方红  30  北京
          --   S4  丰泰盛  20  天津
          --   S5  为民  30  上海
insert 
into S(Sno,Sname,Status,City)
values('S1','精益','20','天津');

insert 
into S(Sno,Sname,Status,City)
values('S2','盛锡','10','北京');

insert 
into S(Sno,Sname,Status,City)
values('S3','东方红','30','北京');

insert 
into S(Sno,Sname,Status,City)
values('S4','丰泰盛','20','天津');

insert 
into S(Sno,Sname,Status,City)
values('S5','为民','30','上海');

select * from S;

--向P表中插入有效数据
--P(PNO,PNAME,COLOR,WEIGHT)
            --P表
            --PNO  PNAME  COLOR  WEIGHT
            --P1  螺母  红  12
            --P2  螺栓  绿  17
            --P3  螺丝刀  蓝  14
            --P4  螺丝刀  红  14
            --P5  凸轮  蓝  40
            --P6  齿轮  红  30
insert 
into P(Pno,Pname,Color,Weight)
values('P1','螺母','红',12);

insert 
into P(Pno,Pname,Color,Weight)
values('P2','螺栓','绿',17);

insert 
into P(Pno,Pname,Color,Weight)
values('P3','螺丝刀','蓝',14);

insert 
into P(Pno,Pname,Color,Weight)
values('P4','螺丝刀','红',14);

insert 
into P(Pno,Pname,Color,Weight)
values('P5','凸轮','蓝',40);

insert