数据分析师SQL操作练习_数据分析师

本帖是关于一些数据分析师SQL操作练习，让大家多多了解一下数据分析师的SQL数据库操作。从而更好的运用在数据分析师日常工作中去。

工程项目-供应商-零件数据库

创建供应商表

CREATE TABLE S  

(  

  snoVARCHAR(2)  PRIMARY KEY,  

sname VARCHAR(6),  

status INT,  

city VARCHAR(8)  

  

);  

插入供应商数据

INSERT INTO S VALUES('S1','精益',20,'天津');

INSERT INTO S VALUES('S2','盛锡',10,'北京');

INSERT INTO S VALUES('S3','东方红',30,'北京');

INSERT INTO S VALUES('S4','丰泰盛',20,'天津');

INSERT INTO S VALUES('S5','为民',30,'上海');

SELECT * FROM S;  

创建零件表

CREATE TABLE P  

(  

  pnoVARCHAR(2) PRIMARY KEY,  

pname VARCHAR(6),  

color VARCHAR(2),  

weight INT  

);  

插入零件数据

INSERT INTO P VALUES('P1','螺母','红',12);

INSERT INTO P VALUES('P2','螺栓','绿',17);

INSERT INTO P VALUES('P3','螺丝刀','蓝',14);

INSERT INTO P VALUES('P4','螺丝刀','红',14);

INSERT INTO P VALUES('P5','凸轮','蓝',40);

INSERT INTO P VALUES('P6','齿轮','红',30);

SELECT * FROM P;  

创建工程项目表

CREATE TABLE J  

(  

  jnoVARCHAR(2) PRIMARY KEY,  

jname VARCHAR(8),  

city VARCHAR(8)  

);  

插入工程项目数据

INSERT INTO J VALUES('J1','三建','北京');

INSERT INTO J VALUES('J2','一汽','长春');

INSERT INTO J VALUES('J3','弹簧厂','天津');

INSERT INTO J VALUES('J4','造船厂','天津');

INSERT INTO J VALUES('J5','机车厂','唐山');

INSERT INTO J VALUES('J6','无线电厂','常州');

INSERT INTO J VALUES('J7','半导体厂','南京');

SELECT * FROM J;  

创建供应情况表

CREATE TABLE SPJ  

(  

  snoVARCHAR(2),  

  pnoVARCHAR(2),  

  jnoVARCHAR(2),  

  qtyINT,  

PRIMARY KEY(sno,pno,jno),  

  #建立表级完整性约束条件  

FOREIGN KEY(sno) REFERENCES S(sno),

FOREIGN KEY(jno) REFERENCES J(jno),

FOREIGN KEY(pno) REFERENCES P(pno)

);  

插入供应情况数据

INSERT INTO SPJVALUES('S1','P1','J1',200);  

INSERT INTO SPJVALUES('S1','P1','J3',100);  

INSERT INTO SPJVALUES('S1','P1','J4',700);  

INSERT INTO SPJVALUES('S1','P2','J2',100);  

INSERT INTO SPJVALUES('S2','P3','J1',400);  

INSERT INTO SPJVALUES('S2','P3','J2',200);  

INSERT INTO SPJ VALUES('S2','P3','J4',500);  

INSERT INTO SPJVALUES('S2','P3','J5',400);  

INSERT INTO SPJVALUES('S2','P5','J1',400);  

INSERT INTO SPJVALUES('S2','P5','J2',100);  

INSERT INTO SPJVALUES('S3','P1','J1',200);  

INSERT INTO SPJVALUES('S3','P3','J1',200);  

INSERT INTO SPJVALUES('S4','P5','J1',100);  

INSERT INTO SPJVALUES('S4','P6','J3',300);  

INSERT INTO SPJVALUES('S4','P6','J4',200);  

INSERT INTO SPJVALUES('S5','P2','J4',100);  

INSERT INTO SPJVALUES('S5','P3','J1',200);  

INSERT INTO SPJ VALUES('S5','P6','J2',200);  

INSERT INTO SPJVALUES('S5','P6','J4',500);  

SELECT * FROM SPJ;  

SELECT FOUND_ROWS();  

查询练习

供应工程J1零件的供应商号码SNO

SELECT DISTINCT sno FROM SPJ WHEREjno='J1';  

供应工程J1零件P1的供应商号码

SELECT DISTINCT sno FROM SPJ WHERE jno='J1'AND pno='P1';  

供应工程J1零件为红色的供应商号码

SELECT sno FROM SPJ,P WHERE jno='J1' ANDcolor='红' AND SPJ.pno=P.pno;  

没有使用天津供应商生产的红色零件的工程号

SELECT jno FROM SPJ WHERE sno IN  (SELECT sno FROM S WHERE city<>'天津')  

AND pno IN (SELECT pno FROM P WHEREcolor<>'红');

至少使用了供应商S1所供应的全部零件的工程

Unkonw