数据库查询练习152含参考答案.docx

1、数据库查询练习152含参考答案查询练习题第一部分：#创建fruits表 CREATE TABLE fruits ( f_id CHAR(10) NOT NULL, s_id INT NOT NULL, f_name CHAR(255) NOT NULL, f_price DECIMAL (8, 2) NOT NULL, PRIMARY KEY (f_id) ;#插入数据 INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES (a1, 101, apple, 5.2), (b1, 101, blackberry, 10.2),(bs1,

2、102, orange, 11.2), (bs2, 105, melon, 8.2),(t1, 102, banana, 10.3),(t2, 102, grape, 5.3), (o2, 103, coconut, 9.2),(c0, 101, cherry, 3.2), (a2, 103, apricot, 2.2), (l2, 104, lemon, 6.4),(b2, 104, berry, 7.6),(m1, 106, mango, 15.6), (m2, 105, xbabay, 2.6),(t4, 107, xbababa, 3.6),(m3, 105, xxtt, 11.6),

3、 (b5, 107, xxxx, 3.6) ;【1】从fruits表中检索所有字段的数据Select * from fruits;【2】查询fruits表中f_name列所有水果名称Select f_name from fruits;【3】从fruits表中获取f_name和f_price两列Select f_name,f_price from fruits;【4】查询价格为10.2元的水果的名称Select f_name from fruits where f_price=10.2;【5】查找名称为“apple”的水果的价格Select f_price from fruits where f

4、_name=apple;【6】查询价格小于10的水果的名称Select f_name from fruits where f_peice2.00 and f_price10.20;【10】查询价格在2.00元到10.20元之外的水果名称和价格Select f_name,f_price from fruits where f_price10.20;【11】查找所有以b字母开头的水果Select f_name from fruits where f_name like b%;【12】在fruits表中，查询f_name中包含字母g的记录Select * from fruits where f_na

5、me like “%g%”;【13】查询以b开头，并以y结尾的水果的名称Select * from fruits where f_name like “b%y”;【14】在fruits表中，查询以字母y结尾，且y前面只有4个字母的记录Select * from fruits where f_name like “_y”;第二部分：#创建数据表customersCREATE TABLE customers( c_id INT NOT NULL AUTO_INCREMENT, c_name CHAR(50) NOT NULL, c_address CHAR(50) NULL, c_city CHA

6、R(50) NULL, c_zip CHAR(10) NULL, c_contact CHAR(50) NULL, c_email CHAR(255) NULL, PRIMARY KEY (c_id);#插入数据，请执行以下语句。INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email) VALUES(10001, RedHook, 200 Street , Tianjin, 300000, LiMing, ),(10002, Stars, 333 Fromage Lane,Dalian,

7、116000, Zhangbo,),(10003, Netbhood, 1 Sunny Place, Qingdao, 266000,LuoCong, NULL),(10004, JOTO, 829 Riverside Drive, Haikou, 570000, YangShan, ); 查询表中的记录数目： SELECT COUNT(*) AS cust_num FROM customers;【15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值Select c_id,c_name,c_email from customers where

8、c_email is null;【16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值Select c_id,c_name,c_email from customers where c_email is not null;【17】在fruits表中查询s_id = 101，并且f_price大于等于5的水果价格和名称Select f_name,f_price from fruits where s_id=101 and f_price=5;【18】在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=

9、apple的水果价格和名称Select f_name,f_price from fruits where s_id in (101,102) and f_price5 and f_name=apple;【19】查询s_id=101或者s_id=102的水果供应商的f_price和f_nameSelect f_price,f_name from fruits where s_id=101 or s_id=102;【20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name（用不同的方法）Select f_price,f_name from fruits where

10、s_id in (101,102);【21】查询fruits表中s_id字段的值，返回s_id字段值且不得重复（使用distinct去重复）SELECT DISTINCT s_id FROM fruits;【22】查询fruits表的f_name字段值，并对其进行排序Select f_name from fruits order by f_name;【23】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序Select f_name,f_price from fruits order by f_name,f_price;【24】查询fruits

11、表中的f_name和f_price字段，对结果按f_price降序方式排序Select f_name,f_price from fruits order by f_price desc;【25】查询fruits表，先按f_price降序排序，再按f_name字段升序排序Select * from fruits order by f_price desc,f_name;【26】根据s_id对fruits表中的数据进行分组，并显示每个分组的总记录数。Select s_id,count(*) from fruits group by s_id;【27】根据s_id对fruits表中的数据进行分组，将

12、每个供应商的水果名称显示出来（使用GROUP_CONCAT拼接字段内容）SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id;【28】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息 Select s_id as nemes from fruits group by s_id having count(f_name)1;【29】根据s_id对fruits表中的数据进行分组，并显示记录数量SELECT s_id, COUNT(*) AS Total FROM fruits GROUP

13、BY s_id WITH ROLLUP;WITH ROLLUP对所有记录进行统计【30】根据s_id和f_name字段对fruits表中的数据进行分组Select * from fruits group by s_id,f_name;第三部分：创建数据表，SQL语句如下：CREATE TABLE orderitems( o_num INT NOT NULL, o_item INT NOT NULL, f_id CHAR(10) NOT NULL, quantity INT NOT NULL, item_price DECIMAL(8,2) NOT NULL, PRIMARY KEY (o_nu

14、m,o_item) ;插入数据，SQL语句如下：INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)VALUES(30001, 1, a1, 10, 5.2),(30001, 2, b2, 3, 6),(30001, 3, bs1, 5, 11.2),(30001, 4, bs2, 15, 9.2),(30002, 1, b3, 2, 20.0),(30003, 1, c0, 100, 10),(30004, 1, o2, 50, 2.50),(30005, 1, c0, 5, 10),(30005, 2, b1,

15、10, 8.99),(30005, 3, a2, 10, 2.2),(30005, 4, m1, 5, 14.99);【31】查询总订单价格大于100的订单号和总订单价格【32】显示fruits表查询结果的前4行【33】在fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录【34】查询customers表中总的行数【35】查询customers表中有电子邮箱的顾客的总数【36】在orderitems表中，使用COUNT()函数统计不同订单号中订购的水果种类【37】在orderitems表中查询30005号订单一共购买的水果总量【38】在orderitems表中，使用

16、SUM()函数统计不同订单号中订购的水果总量【39】在fruits表中，查询s_id=103的供应商的水果价格的平均值【40】在fruits表中，查询每一个供应商的水果价格的平均值【41】在fruits表中查找市场上价格最高的水果【42】在fruits表中查找不同供应商提供的价格最高的水果【43】在fruits表中查找f_name的最大值【44】在fruits表中查找市场上价格最低的水果【45】在fruits表中查找不同供应商提供的价格最低的水果第四部分：#创建数据表suppliers，SQL语句如下：CREATE TABLE suppliers( s_id INT NOT NULL AUTO

17、_INCREMENT, s_name CHAR(50) NOT NULL, s_city CHAR(50) NULL, s_zip CHAR(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id) ;插入数据，SQL语句如下：INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)VALUES(101,FastFruit Inc.,Tianjin,300000,48075),(102,LT Supplies,Chongqing,400000,44333),(103,ACME,Shan

18、ghai,200000,90046),(104,FNK Inc.,Zhongshan,528437,11111),(105,Good Set,Taiyuang,030000, 22222),(106,Just Eat Ours,Beijing,010, 45678),(107,DK Inc.,Zhengzhou,450000, 33332);【46】在fruits表和suppliers表之间使用内连接查询。查询之前，查看两个表的结构： DESC fruits; DESC suppliers;由结果可以看到，fruits表和suppliers表中都有相同数据类型的字段s_id，两个表通过s_id

19、字段建立联系。接下来从fruits表中查询f_name、f_price字段，从suppliers表中查询s_id、s_name【47】在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询【48】查询供应f_id= a1的水果供应商提供的其他水果种类【较难】1LEFT JOIN左连接左连接的结果包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。创建表orders，SQL语句如下：CREATE TABLE orders( o_num INT NOT N

20、ULL AUTO_INCREMENT, o_date DATETIME NOT NULL, c_id INT NOT NULL, PRIMARY KEY (o_num) ;插入数据，SQL语句如下：INSERT INTO orders(o_num, o_date, c_id)VALUES(30001, 2008-09-01, 10001),(30002, 2008-09-12, 10003),(30003, 2008-09-30, 10004),(30004, 2008-10-03, 10005),(30005, 2008-10-08, 10001);【49】在customers表和order

21、s表中，查询所有客户，包括没有订单的客户2RIGHT JOIN右连接右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。【50】在customers表和orders表中，查询所有订单，包括没有客户的订单【51】在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息【52】在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果排序参考答案【1】从fruits表中检索所有字段的数据，SQL语句如下： SELECT * FROM frui

22、ts;【2】查询fruits表中f_name列所有水果名称，SQL语句如下：SELECT f_name FROM fruits;【3】从fruits表中获取f_name和f_price两列，SQL语句如下：SELECT f_name, f_price FROM fruits;【4】查询价格为10.2元的水果的名称，SQL语句如下：SELECT f_name, f_priceFROM fruitsWHERE f_price = 10.2;【5】查找名称为“apple”的水果的价格，SQL语句如下：SELECT f_name, f_priceFROM fruitsWHERE f_name = ap

23、ple;【6】查询价格小于10的水果的名称，SQL语句如下：SELECT f_name, f_priceFROM fruitsWHERE f_price =5;【18】在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=apple的水果价格和名称，SQL语句如下：SELECT f_id, f_price, f_name FROM fruits WHERE s_id IN(101, 102) AND f_price = 5 AND f_name = apple;【19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，

24、SQL语句如下：SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102;【20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,102);【21】查询fruits表中s_id字段的值，返回s_id字段值且不得重复，SQL语句如下：SELECT DISTINCT s_id FROM fruits;【22】查询fruits表的f_name字段值，并

25、对其进行排序，SQL语句如下： SELECT f_name FROM fruits ORDER BY f_name;【23】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;【24】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;【25】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;【26】根据s_id对fruits表中的数据进行分组，并显示每个分组的总记录数，SQL语句如下：SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;【27】根据s_id对f