20
有一个关于商品供应及顾客订单的数据库。其中包括四个表,表中信息如下: 供应表apply(id、name、sid、price) 说明:id 供应厂家编号,name 供应厂家名称,sid 商品编号,price 商品价格。 顾客表customers(gid、name、address、balance) 说明:gid 顾客编号,address 地址,balance 余额。 订单表orders(sid、gid、date) 说明:sid 商品编号,gid 顾客编号,date 订单日期。 商品表goods(sid、name、count) 说明:sid 商品编号,name 商品名称,count 商品数量 写出SQL完成如下操作的SQL语句:
1)查询出2008-8-8这一天顾客的订单信息,要求包括顾客姓名、商品名称及订单日期.
2)将商品表中商品编号为'204'的商品名称更改为百事可乐
3)将顾客表上余额不足1000元的,将其订单日期延后10天
4) 删除订单表中商品编号为102的订单记录 5)将商品表中没有顾客订购的商品信息删除
学生答案:
SELECT customers.name,goods.`name`,orders.date
FROM customers,goods,orders
WHERE customers.gid=orders.gid AND goods.`sid`=orders.`sid` AND orders.`date`='2008-8-8';
UPDATE goods
SET `name`='百事可乐'
WHERE sid='204';
UPDATE orders,customers
SET orders.`date`=DATE_ADD(orders.`date`,INTERVAL 10 DAY)
WHERE customers.`gid`=orders.`gid` AND customers.`balance`<1000;
DELETE FROM orders WHERE sid='102';
DELETE FROM goods WHERE sid NOT IN(SELECT sid FROM orders);
已知进销存数据库Jxcxt包含如下数据表: 1)商品表:商品id(自增1), 商品名称, 单位, 单价, 商品状态 2)库存表:库存id(自增1), 商品id, 库存数量 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上两个表的SQL语句;
2、在商品表上创建一个触发器(名字自定),当删除商品时自动删除该商品在库存表中的记录。
学生答案:
CREATE TABLE 商品表(
商品id INT AUTO_INCREMENT PRIMARY KEY,
商品名称 VARCHAR(50) NOT NULL,
单位 VARCHAR(50),
单价 DOUBLE,
商品状态 VARCHAR(10)
);
CREATE TABLE 库存表(
库存id INT AUTO_INCREMENT PRIMARY KEY,
商品id INT,
库存数量 INT,
FOREIGN KEY(商品id)REFERENCES 商品表(商品id)
);
DROP TRIGGER IF EXISTS `delete_spb`;
DELIMITER $$
CREATE TRIGGER delete_spb BEFORE DELETE ON 商品表
FOR EACH ROW
BEGIN
DELETE FROM 库存表 WHERE 商品id=old.商品id;
END
$$
DELIMITER ;
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上三个表的SQL语句;
2、创建视图 V_选课,其中包含这些字段: 选课id, 学号, 姓名, 课程名, 成绩;
3、查询张三的数据开发技术课程的成绩。
学生答案:
CREATE TABLE 学生表(
学生id INT PRIMARY KEY,
学号 VARCHAR(13),
姓名 VARCHAR(5),
专业 VARCHAR(10),
年级 CHAR(2),
姓名拼音缩写 VARCHAR(5)
)CHARSET=utf8;
#课程表:
CREATE TABLE 课程表(
课程id INT PRIMARY KEY,
课程名称 VARCHAR(9),
学分 SMALLINT
)CHARSET=utf8;
#选课表:
CREATE TABLE 选课表(
选课id INT PRIMARY KEY,
学生id INT,
课程id INT,
成绩 INT,
FOREIGN KEY(学生id)REFERENCES 学生表(学生id),
FOREIGN KEY(课程id)REFERENCES 课程表(课程id)
)CHARSET=utf8;
CREATE VIEW V_选课
AS(
SELECT 选课id,学号,姓名,课程名称,成绩
FROM 学生表,选课表,课程表
WHERE 学生表.`学生id`=选课表.`学生id` AND 课程表.`课程id`=选课表.`课程id`
);
SELECT 成绩
FROM 学生表,选课表,课程表
WHERE 学生表.`学生id`=选课表.`学生id` AND 课程表.`课程id`=选课表.`课程id` AND 学生表.`姓名`='张三' AND 课程表.`课程名称`='数据库';
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 系统已建有getstrpy(p_name VARCHAR(100))用户自定义函数。 请完成如下操作的SQL语句,表名和列名必须和上面的一致: 1、创建以上三个表的SQL语句; 2、创建一个存储过程(名字自定),实现学生表的插入,姓名拼音缩写需要调用getstrpy函数实现。
学生答案:
CREATE TABLE 学生表(
学生id INT PRIMARY KEY,
学号 VARCHAR(13),
姓名 VARCHAR(5),
专业 VARCHAR(10),
年级 CHAR(2),
姓名拼音缩写 VARCHAR(5)
)CHARSET=utf8;
#课程表:
CREATE TABLE 课程表(
课程id INT PRIMARY KEY,
课程名称 VARCHAR(9),
学分 SMALLINT
)CHARSET=utf8;
#选课表:
CREATE TABLE 选课表(
选课id INT PRIMARY KEY,
学生id INT,
课程id INT,
成绩 INT,
FOREIGN KEY(学生id)REFERENCES 学生表(学生id),
FOREIGN KEY(课程id)REFERENCES 课程表(课程id)
)CHARSET=utf8;
CREATE VIEW V_选课
AS(
SELECT 选课id,学号,姓名,课程名称,成绩
FROM 学生表,选课表,课程表
WHERE 学生表.`学生id`=选课表.`学生id` AND 课程表.`课程id`=选课表.`课程id`
);
SELECT 成绩
FROM 学生表,选课表,课程表
WHERE 学生表.`学生id`=选课表.`学生id` AND 课程表.`课程id`=选课表.`课程id` AND 学生表.`姓名`='张三' AND 课程表.`课程名称`='数据库';
DELIMITER $$
CREATE PROCEDURE `insert_xmsx`(xsid INT,xh VARCHAR(13),xm VARCHAR(5),zy VARCHAR(10),nj CHAR(2))
BEGIN
INSERT INTO 学生表(学生id,学号,姓名,专业,年级,姓名拼音缩写) VALUES(xsid,xh,xm,zy,nj,getstrpy(xm));
END
$$
DELIMITER ;
已知选课管理系统Xkglxt包含如下数据表和自定义函数(函数已定义好可直接使用): 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)自定义函数拼音缩写查询: pysx('中文字符串') 返回 该中文字符串的拼音缩写 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建选课管理系统数据库以及学生表的SQL语句;
2、在学生表上创建一个触发器(名字自定),当新增学生时自动处理姓名拼音缩写。
学生答案:
1、CREATEDATABASEXkglxtCHARSETutf8;CREATETABLE学生(学生idINTPRIMARYKEY,学号INT,专业VARCHAR(30),年纪INT,姓名VARCHAR(30),姓名拼音缩写VARCHAR(30));
2DELIMITER$$CREATETRIGGERmytriggerAFTERINSERTON学生FOREACHROWBEGININSERTINTO学生(姓名拼音缩写)VALUES(pysx(new.姓名));END$$DELIMITER;
10
已知产品销售数据库cpxs包含如下数据表: 1)产品表:产品编号,产品名称,价格,库存量。 2)销售商表:客户编号,客户名称,地区,负责人,电话。 3)产品销售表:销售日期,产品编号,客户编号,数量,销售额。 写出SQL完成如下操作的SQL语句:
1.写出产品表的建表语句
2.向产品表中插入如下记录: 0001 空调 3000 200 0002 冰箱 2500 300
3.将产品表中每种商品价格打八折后进行显示
4.查找价格在2000到2900之间的产品名称。 5.在产品销售表上创建“冰箱”产品表的视图bxcp
学生答案:
1、CREATETABLE产品表(产品编号char(4)PRIMARYKEY,产品名称VARCHAR(20)NOTNULL,价格float(7,2),库存量int(5));CREATETABLE销售商表(客户编号char(5)PRIMARYKEY,客户名称VARCHAR(20)NOTNULL,地区VARCHAR(10),负责人VARCHAR(10),电话VARCHAR(11));CREATETABLE产品销售表(销售日期date,产品编号char(4),客户编号char(5),数量int,销售额float(8,2));
2、insertinto产品表values(’0001’,’空调’,3000,200);insertinto产品表values(’0002’,’冰箱’,2500,300);
3、update产品表set价格=价格*0.8;
4、select产品名称from产品表where价格between2000and2900;5、createviewbxcpasselect*from产品表where产品名称=冰箱withcheckoption;
10
已知进销存数据库Jxcxt包含如下数据表: 1)商品表:商品id, 商品名称, 单位, 单价, 商品状态 2)库存表:库存id, 商品id, 库存数量 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上两个表的SQL语句;
2、创建一个存储过程(名字自定),实现商品表的的插入,需要判断当前商品名称是否存在。
学生答案:
CREATEDATABASEJxcxt;CREATETABLE商品表(商品idCHAR(4)PRIMARYKEYAUTO_INCREMENT,商品名称CHAR(4),单位CHAR(4),单价INT,商品状态CHAR(4))CHARSET=utf8;CREATETABLE库存表(库存idCHAR(4)PRIMARYKEYAUTO_INCREMENT,商品idCHAR(4),库存数量INT,FOREIGNKEY(商品id)REFERENCES商品表(商品id))CHARSET=utf8;CREATEVIEWV_库存(库存id,商品名称,单位,库存数量,库存金额)ASSELECT*FROM(商品表,库存表)WITHCHECKOPTION;
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上三个表的SQL语句;
2、创建一个存储过程(名字自定),实现选课表的插入,需要判断当前课程id和学生id是否存在。
学生答案:
CREATEDATABASExkglxt;CREATETABLE学生表(学生idCHAR(4)PRIMARYKEY,学号CHAR(4),姓名CHAR(4),专业CHAR(4),年级CHAR(4),姓名拼音缩写CHAR(8))CHARSET=utf8;CREATETABLE课程表(课程idCHAR(4)PRIMARYKEY,课程名称CHAR(10),学分CHAR(4))CHARSET=utf8;CREATETABLE选课表(选课idCHAR(4)PRIMARYKEY,学生idCHAR(4),课程idCHAR(4),成绩INT,FOREIGNKEY(学生id)REFERENCES学生表(学生id),FOREIGNKEY(课程id)REFERENCES课程表(课程id))CHARSET=utf8;
SELECT学号,姓名,专业,年级FROM学生表,课程表,选课表WHERE成绩<60;
10
有一个关于商品供应及顾客订单的数据库。其中包括四个表,表中信息如下: 供应表apply(id、name、sid、price) 说明:id 供应厂家编号,name 供应厂家名称,sid 商品编号,price 商品价格。 顾客表customers(gid、name、address、balance) 说明:gid 顾客编号,address 地址,balance 余额。 订单表orders(sid、gid、date) 说明:sid 商品编号,gid 顾客编号,date 订单日期。 商品表goods(sid、name、count) 说明:sid 商品编号,name 商品名称,count 商品数量
1)从供应表中查询全体供应厂商的基本信息
2)从顾客表中查询地址在"长春"的顾客的顾客编号、顾客姓名及余额.
3)从商品表中查询以"可乐"两个字结尾的商品名称及数量,并按商品数量降序排序
4)从订单表中查询购买商品编号为"101"商品的顾客编号及订单日期.
5)向商品表中追加一条纪录(204,可口可乐,900)
6)从商品表中查询最多商品数量、最少商品数量及商品总数量的记录信息
学生答案:
1、select*fromapply;
2、selectgid,name,balancefromcustomerswhereaddress="长春";
3、selectname,countfromgoodswherenamelike"%可乐"orderbycountdesc;
4、selectgid,datefromorderswheresid=101;
5、insertintogoodsvalues(204,"可口可乐",900);
6、selectmax(count),min(count),sum(count)fromgoods;
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致: 1、创建以上三个表的SQL语句; 2、创建一个存储过程(名字自定),实现课程表的插入,需要判断当前课程名称的课程是否存在。
学生答案:
- CREATETABLE`temp`.`学生表`(`学生id`VARCHAR(20),`学号`VARBINARY(20),`姓名`VARCHAR(20),`专业`VARCHAR(20),`年级`VARCHAR(20),`姓名拼音缩写`VARCHAR(10))CHARSET=utf8;CREATETABLE`temp`.`课程表`(`课程id`VARCHAR(20),`课程名称`VARCHAR(20),`学分`VARCHAR(20))CHARSET=utf8;
- CREATETABLE`temp`.`选课表`(`选课id`VARCHAR(20),`学生id`VARCHAR(20),`课程id`VARCHAR(20),`成绩`VARCHAR(20))CHARSET=utf8;2.SELECT`学生表`.`学号`,`学生表`.`姓名`,`学生表`.`专业`,`学生表`.`年级`FROM`学生表`,`选课表`,`课程表`WHERE`学生表`.`学生id`=`选课表`.`学生id`AND`课程表`.`课程id`=`选课表`.`课程id`AND`课程表`.`课程名称`="数据库原理";
8
已知选课管理系统Xkglxt包含如下数据表和自定义函数(函数已定义好可直接使用): 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)自定义函数拼音缩写查询: pysx('中文字符串') 返回 该中文字符串的拼音缩写 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建选课管理系统数据库以及学生表的SQL语句;
2、在学生表上创建一个触发器(名字自定),当修改学生表的姓名时自动处理姓名拼音缩写
学生答案:
1、CREATEDATABASEXkglxtCHARSETutf8;CREATETABLE学生(学生idINTPRIMARYKEY,学号INT,专业VARCHAR(30),年纪INT,姓名VARCHAR(30),姓名拼音缩写VARCHAR(30));
2`DELIMITER$$CREATETRIGGERmytriggerAFTERINSERTON学生FOREACHROWBEGININSERTINTO学生(姓名拼音缩写)VALUES(pysx(new.姓名));END$$DELIMITER;
8
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致: 1、创建以上三个表的SQL语句; 2、在课程表上创建一个触发器(名字自定)。,当删除课程信息时自动删除该课程的选课信息。
学生答案:
1、1、CREATETABLE学生(学生idINTPRIMARYKEY,学号INT,姓名VARCHAR(30),专业VARCHAR(30),年纪INT,姓名拼音缩写VARCHAR(10))CHARSETutf8;CREATETABLE课程(课程idINTPRIMARYKEY,课程名称VARCHAR(30),学分INT)CHARSETutf8;CREATETABLE选课(选课idINTPRIMARYKEY,学生idINT,课程idINT,成绩INT)CHARSETutf8;
2、DELIMITER$$CREATETRIGGERmytriggerAFTERDELETEON课程FOREACHROWBEGINDELETEFROM选课WHERE选课.课程id=old.课程id;END$$DELIMITER;
8
已知进销存数据库Jxcxt包含如下数据表:
1)商品表:商品id(自增1), 商品名称, 单位, 单价, 商品状态
2)库存表:库存id(自增1), 商品id, 库存数量
请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上两个表的SQL语句;
2、在商品表上创建一个触发器(名字自定),当新增商品时自动在库存表中插入该商品的库存信息(库存数量为0)。
学生答案:
1)
createtable商品表(商品idintnotnullauto_increment,商品名称varchar(255),单位varchar(10),单价int,商品状态varchar(10),primarykey(商品id))charsetutf8;createtable库存表(库存idintnotnullauto_increment,商品idint,库存数量int,foreignkey(商品id)references商品表(商品id),primarykey(库存id))charsetutf8;
2)delimiter$$createtrigger增商品触库存afterinserton商品表foreachrowbegininsertinto库存表values(null,new.商品id,0);end$$
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上三个表的SQL语句;
2、创建一个存储过程(名字自定),实现学生表的插入,需要判断当前学号的学生是否存在。
学生答案:
CREATE TABLE `student` (
`Sid` int(11) NOT NULL,
`Sxh` varchar(20) NOT NULL,
`Sname` varchar(10) NOT NULL,
`Szy` varchar(20) DEFAULT NULL,
`Snj` varchar(10) DEFAULT NULL,
`Sxmpysx` varchar(10) DEFAULT NULL,
PRIMARY KEY (`Sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `course` (
`Cid` int(11) NOT NULL COMMENT '课程id',
`Cname` varchar(20) DEFAULT NULL COMMENT '课程名称',
`Cxf` float DEFAULT NULL COMMENT '学分',
PRIMARY KEY (`Cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `xuanke` (
`Xid` int(11) NOT NULL COMMENT '选课id',
`Sid` int(11) DEFAULT NULL COMMENT '学生id',
`Cid` int(11) DEFAULT NULL COMMENT '课程id',
`Xcj` float DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`Xid`),
KEY `Sid` (`Sid`),
KEY `Cid` (`Cid`),
CONSTRAINT `xuanke_ibfk_1` FOREIGN KEY (`Sid`) REFERENCES `student` (`Sid`),
CONSTRAINT `xuanke_ibfk_2` FOREIGN KEY (`Cid`) REFERENCES `course` (`Cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DELIMITER$$
CREATE PROCEDURE isInsert(id1 INT,id2 INT,id3 INT,grade FLOAT)
BEGIN IF
((SELECT COUNT(*) FROM Student,Course WHERE Student.`Sid`=id2 AND
Course.`Cid`=id3)<>0
)
THEN INSERT INTO xuanke VALUES(id1,id2,id3,grade);
END IF;
END$$