数据库第三章习题

5.针对习题3中的四个表试用SQL语言完成以下各项操作：

创建一个数据库进行操作

exp_db.sql

CREATE DATABASE exp_db;
CREATE TABLE IF NOT EXISTS spj_s(
	sno CHAR(20),
	sname CHAR(20),
	sta INT(10),
	city CHAR(20),
	PRIMARY KEY(sno)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO spj_s (sno,sname,sta,city) VALUES
('s1','精益',20,'天津'),
('s2','盛熙',10,'北京'),
('s3','东方红',30,'北京'),
('s4','丰泰盛',20,'天津'),
('s5','为民',30,'上海');
 
CREATE TABLE IF NOT EXISTS spj_p
	(
		pno CHAR(20) PRIMARY KEY,
		pname CHAR(20),
		color CHAR(20),
		weight INT(10)
	)ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO spj_p (pno,pname,color,weight) VALUES
('p1','螺母','红',12),
('p2','螺栓','绿',17),
('p3','螺丝刀','蓝',14),
('p4','螺丝刀','红',14),
('p5','凸轮','蓝',40),
('p6','齿轮','红',30);
 
CREATE TABLE IF NOT EXISTS spj_j
	(
		jno CHAR(20) PRIMARY KEY,
		jname CHAR(20),
		city CHAR(20)
	)ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO spj_j (jno,jname,city) VALUES
('j1','三建','北京'),
('j2','一汽','长春'),
('j3','弹簧厂','天津'),
('j4','造船厂','天津'),
('j5','机车厂','唐山'),
('j6','无线电厂','常州'),
('j7','半导体厂','南京');
 
CREATE TABLE IF NOT EXISTS spj_spj
(
	sno CHAR(20),
	pno CHAR(20),
	jno CHAR(20),
	qty INT(10)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO spj_spj (sno,pno,jno,qty) VALUES
('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');