MySQL必知必会课后题

已于 2023-03-15 12:02:42 修改
阅读量195 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库 java
于 2023-02-19 19:35:43 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mmqgirlfriend/article/details/129113633
版权

MySQL必知必会课后题

看了很多文章，都是有课后题答案但是没有表，找了很久才找到提供了建表的文档；怕以后再找不到，在自己的博客里记录一下

建库&&表&&定义外键

DROP DATABASE IF EXISTS crashcourse;

CREATE DATABASE crashcourse;

USE crashcourse;

########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;



#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

表中插入数据

USE crashcourse;

##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

第2课 检索数据

  1. 编写SQL语句，从Customers表中检索所有的ID（cust_id）
SELECT cust_id FROM customers;
  1. OrderItems表包含了所有已订购的产品（有些已被订购多次）。编写SQL语句，检索并列出已订购产品（prod_id）的清单（不用列每个订单，只列出不同产品的清单）。提示：最终应该显示7行
SELECT DISTINCT prod_id FROM orderitems;
  1. 编写SQL语句，检索Customers表中所有的列，再编写另外的SELECT语句，仅检索顾客的ID。使用注释，注释掉一条SELECT语句，以便运行另一条SELECT语句。（当然，要测试这两个语句。）
-- SELECT * FROM customers;
SELECT cust_id FROM customers;

第3课 排序检索数据

  1. 编写SQL语句，从Customers中检索所有的顾客名称（cust_names），并按从Z到A的顺序显示结果
SELECT cust_name FROM customers
ORDER BY cust_name DESC;
  1. 编写SQL语句，从Orders表中检索顾客ID（cust_id）和订单号（order_num），并先按顾客ID对结果进行排序，再按订单日期倒序排列
SELECT cust_id, order_num FROM orders
ORDER BY cust_id, order_date DESC;
  1. 编写SQL语句，显示OrderItems表中的数量和价格（item_price），并按数量由多到少、价格由高到低排序
SELECT quantity, item_price FROM orderitems
ORDER BY quantity DESC, item_price DESC;
  1. 下面的SQL语句有问题吗？（尝试在不运行的情况下指出。）
SELECT vend_name,  # 错误1：查询的最后一个字段后面不需要加逗号
FROM Vendors
ORDER vend_name DESC;  # 错误2：order by

第4课 过滤数据

  1. 编写SQL语句，从Products表中检索产品ID（prod_id）和产品名称（prod_name），只返回价格为9.49美元的产品
SELECT prod_id, prod_name
FROM products
WHERE prod_price = 9.49;
  1. 编写SQL语句，从Products表中检索产品ID（prod_id）和产品名称（prod_name），只返回价格为9美元或更高的产品
SELECT prod_id, prod_name
FROM products
WHERE prod_price >= 9;
  1. 结合第3课和第4课编写SQL语句，从OrderItems表中检索出所有不同订单号（order_num），其中包含100个或更多的产品
SELECT DISTINCT order_num 
FROM orderitems
WHERE quantity >= 100;
  1. 编写SQL语句，返回Products表中所有价格在3美元到6美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序。（本题有多种解决方案，我们在下一课再讨论，不过你可以使用目前已学的知识来解决它。）

方法I：

SELECT prod_name, prod_price
FROM products
WHERE 3 <= prod_price AND prod_price <= 6
ORDER BY prod_price;

方法II：

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price;

第5课 高级过滤数据

  1. 编写SQL语句，从Vendors表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个加利福尼亚州）。提示：过滤器需要匹配字符串
SELECT vend_name FROM vendors
WHERE vend_country = 'USA' AND vend_city = 'CA';
  1. 编写SQL语句，查找所有至少订购了总量100个的BR01、BR02或BR03的订单。你需要返回OrderItems表的订单号（order_num）、产品ID（prod_id）和数量，并按产品ID和数量进行过滤。提示：根据编写过滤器的方式，可能需要特别注意求值顺序
SELECT order_num, prod_id, quantity
FROM orderitems
WHERE prod_id IN ('BR01', 'BR02', 'BR03')
AND quantity >= 100;
  1. 现在，我们回顾上一课的挑战题。编写SQL语句，返回所有价格在3美元到6美元之间的产品的名称（prod_name）和价格（prod_price）。使用AND，然后按价格对结果进行排序
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 3 AND 6;
  1. 下面的SQL语句有问题吗？（尝试在不运行的情况下指出。）
SELECT vend_name
FROM Vendors
ORDER BY vend_name # order by应该在语句的最后面
WHERE vend_country = 'USA' AND vend_state = 'CA';

第6课 用通配符进行过滤

  1. 编写SQL语句，从Products表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含toy一词的产品
SELECT prod_name, prod_desc
FROM products
WHERE prod_desc LIKE '%toy%';
  1. 反过来再来一次。编写SQL语句，从Products表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现toy一词的产品。这次，按产品名称对结果进行排序
SELECT prod_name, prod_desc
FROM products
WHERE prod_desc NOT LIKE '%toy%'
ORDER BY prod_name;
  1. 编写SQL语句，从Products表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现toy和carrots的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用AND和两个LIKE比较
SELECT prod_name, prod_desc
FROM products
WHERE prod_desc LIKE '%toy%' 
AND prod_desc LIKE'%carrots%';
  1. 来个比较棘手的。我没有特别向你展示这个语法，而是想看看你根据目前已学的知识是否可以找到答案。编写SQL语句，从Products表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现toy和carrots的产品。提示：只需要用带有三个%符号的LIKE即可
SELECT prod_name, prod_desc
FROM products
WHERE prod_desc LIKE '%toy%carrots%';

第7课 创建计算字段

  1. 别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）。编写SQL语句，从Vendors表中检索vend_id、vend_name、vend_address和vend_city，将vend_name重命名为vname，将vend_city重命名为vcity，将vend_address重命名为vaddress。按供应商名称对结果进行排序（可以使用原始名称或新的名称）
SELECT vend_id,
       vend_name AS vname,
       vend_city AS vcity,
       vend_address AS vaddress
FROM vendors
ORDER BY vend_name;
  1. 我们的示例商店正在进行打折促销，所有产品均降价10%。编写SQL语句，从Products表中返回prod_id、prod_price和sale_price。sale_price是一个包含促销价格的计算字段。提示：可以乘以0.9，得到原价的90%（即10%的折扣）
SELECT prod_id, 
       prod_price, 
       prod_price * 0.9 AS sale_price
FROM products;

第8课 使用函数处理数据

  1. 我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。编写SQL语句，返回顾客ID（cust_id）、顾客名称（customer_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。例如，我的登录名是BEOAK（Ben Forta，居住在Oak Park）。提示：需要使用函数、拼接和别名。
SELECT cust_id,
       cust_name,
       UPPER(CONCAT( LEFT(cust_contact, 3), LEFT(cust_city, 2))) AS user_login
FROM customers;
  1. 编写SQL语句，返回2020年1月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期排序。你应该能够根据目前已学的知识来解决此问题，但也可以开卷查阅DBMS文档
SELECT order_num, order_date
FROM orders
WHERE YEAR(order_date) = 2020
AND MONTH(order_date) = 1;

第9课 汇总数据

  1. 编写SQL语句，确定已售出产品的总数（使用OrderItems中的quantity列）
SELECT SUM(quantity) FROM orderitems;
  1. 修改刚刚创建的语句，确定已售出产品项（prod_item）BR01的总数
SELECT SUM(quantity) FROM orderitems
WHERE prod_id = 'BR01';
  1. 编写SQL语句，确定Products表中价格不超过10美元的最贵产品的价格（prod_price）。将计算所得的字段命名为max_price
SELECT MAX(prod_price) AS max_price
FROM products
WHERE prod_price <= 10;

第10课 分组数据

  1. OrderItems表包含每个订单的每个产品。编写SQL语句，返回每个订单号（order_num）各有多少行数（order_lines），并按order_lines对结果进行排序
SELECT order_num, COUNT(*) AS order_lines
FROM orderitems
GROUP BY order_num
ORDER BY order_lines;
  1. 编写SQL语句，返回名为cheapest_item的字段，该字段包含每个供应商成本最低的产品（使用Products表中的prod_price），然后从最低成本到最高成本对结果进行排序
SELECT vend_id, min(prod_price) AS cheapest_item
FROM products
GROUP BY vend_id, prod_price
ORDER BY prod_price DESC, vend_id;
  1. 确定最佳顾客非常重要，请编写SQL语句，返回至少含100项的所有订单的订单号（OrderItems表中的order_num）
SELECT order_num, SUM(quantity) AS total_ordered
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity) >= 100;
  1. 确定最佳顾客的另一种方式是看他们花了多少钱。编写SQL语句，返回总价至少为1000的所有订单的订单号（OrderItems表中的order_num）。提示：需要计算总和（item_price乘以quantity）。按订单号对结果进行排序
SELECT order_num, SUM(quantity * item_price) AS total_price
FROM orderitems
GROUP BY order_num
HAVING total_price >= 1000
ORDER BY order_num;
  1. 下面的SQL语句有问题吗？（尝试在不运行的情况下指出。）
SELECT order_num, COUNT(＊) AS items
FROM OrderItems
GROUP BY items             -- -----------------group by order_num
HAVING COUNT(＊) >= 3
ORDER BY items, order_num;

第11课 使用子查询

  1. 使用子查询，返回购买价格为10美元或以上产品的顾客列表。你需要使用OrderItems表查找匹配的订单号（order_num），然后使用Order表检索这些匹配订单的顾客ID（cust_id）
SELECT *
FROM customers
WHERE cust_id IN (
	SELECT cust_id FROM orderitems
    WHERE item_price >= 10
)
  1. 你想知道订购BR01产品的日期。编写SQL语句，使用子查询来确定哪些订单（在OrderItems中）购买了prod_id为BR01的产品，然后从Orders表中返回每个产品对应的顾客ID（cust_id）和订单日期（order_date）。按订购日期对结果进行排序
SELECT cust_id, order_date
FROM orders
WHERE order_num IN (
	SELECT order_num FROM orderitems
    WHERE prod_id = 'BR01'
)
ORDER BY order_date;
  1. 现在我们让它更具挑战性。在上一个挑战题，返回购买prod_id为BR01的产品的所有顾客的电子邮件（Customers表中的cust_email）。提示：这涉及SELECT语句，最内层的从OrderItems表返回order_num，中间的从Customers表返回cust_id
SELECT cust_email
FROM customers
WHERE cust_id IN (
	SELECT cust_id FROM orders
    WHERE order_num IN (
		SELECT order_num FROM orderitems
        WHERE prod_id = 'BR01'
    )
);
  1. 我们需要一个顾客ID列表，其中包含他们已订购的总金额。编写SQL语句，返回顾客ID（Orders表中的cust_id），并使用子查询返回total_ordered以便返回每个顾客的订单金额。将结果按金额从大到小排序。提示：你之前已经使用SUM()计算订单金额
SELECT cust_id,
(
	SELECT SUM(quantity * item_price)
    FROM orderitems
    WHERE order_num IN (
		SELECT order_num FROM orders
        WHERE orders.cust_id = customers.cust_id
    )
) AS total_ordered
FROM customers
ORDER BY total_ordered DESC;
  1. 编写SQL语句，从Products表中检索所有的产品名称（prod_name），以及名为quant_sold的计算列，其中包含所售产品的总数（在OrderItems表上使用子查询和SUM(quantity)检索）
SELECT prod_name,
(
	SELECT SUM(quantity) FROM orderitems
    WHERE products.prod_id = orderitems.prod_id
) AS quant_sold
FROM products;

第12课 联结表

  1. 编写SQL语句，返回Customers表中的顾客名称（cust_name）和Orders表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行排序。实际上是尝试两次，一次使用简单的等联结语法，一次使用INNER JOIN
SELECT c.cust_name, o.order_num
FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id
ORDER BY c.cust_id, o.order_num;
  1. 我们来让上一题变得更有用些。除了返回顾客名称和订单号，添加第三列OrderTotal，其中包含每个订单的总价。有两种方法可以执行此操作：使用OrderItems表的子查询来创建OrderTotal列，或者将OrderItems表与现有表联结并使用聚合函数。提示：请注意需要使用完全限定列名的地方
# 方法# 方法I: 子查询
SELECT c.cust_name, 
       o.order_num,
       (
			SELECT SUM(quantity * item_price)
            FROM orderitems oi
            WHERE oi.order_num = o.order_num
       ) AS OrderTotal
FROM orders o
INNER JOIN customers c ON c.cust_id = o.cust_id
ORDER BY c.cust_id, o.order_num

# 方法II: 联表查询
SELECT c.cust_name,
       o.order_num,
       SUM(oi.quantity * oi.item_price) AS OrderTotal
FROM orders o
INNER JOIN customers c ON c.cust_id = o.cust_id
INNER JOIN orderitems oi ON o.order_num = oi.order_num 
GROUP BY o.order_num, c.cust_name;
  1. 我们重新看一下第11课的挑战题2。编写SQL语句，检索订购产品BR01的日期，这一次使用联结和简单的等联结语法。输出应该与第11课的输出相同
SELECT order_date
FROM orderitems oi
INNER JOIN orders o ON o.order_num = oi.order_num
WHERE prod_id = 'BR01';
  1. 很有趣，我们再试一次。重新创建为第11课挑战题3编写的SQL语句，这次使用ANSI的INNER JOIN语法。在之前编写的代码中使用了两个嵌套的子查询。要重新创建它，需要两个INNER JOIN语句，每个语句的格式类似于本课讲到的INNER JOIN示例，而且不要忘记WHERE子句可以通过prod_id进行过滤
SELECT cust_email
FROM orders o
INNER JOIN customers c ON c.cust_id = o.cust_id
INNER JOIN orderitems oi ON o.order_num = oi.order_num
WHERE prod_id = 'BR01';
  1. 再让事情变得更加有趣些，我们将混合使用联结、聚合函数和分组。准备好了吗？回到第10课，当时的挑战是要求查找值等于或大于1000的所有订单号。这些结果很有用，但更有用的是订单数量至少达到这个数的顾客名称。因此，编写SQL语句，使用联结从Customers表返回顾客名称（cust_name），并从OrderItems表返回所有订单的总价。提示：要联结这些表，还需要包括Orders表（因为Customers表与OrderItems表不直接相关，Customers表与Orders表相关，而Orders表与OrderItems表相关）。不要忘记GROUP BY和HAVING，并按顾客名称对结果进行排序。你可以使用简单的等联结或ANSI的INNER JOIN语法。或者，如果你很勇敢，请尝试使用两种方式编写
SELECT c.cust_name,
       o.order_num,
       SUM(oi.quantity * oi.item_price) AS OrderedTotal
FROM orders o
INNER JOIN customers c ON c.cust_id = o.cust_id
INNER JOIN orderitems oi ON o.order_num = oi.order_num
GROUP BY o.order_num, c.cust_name
HAVING OrderedTotal >= 100;

第13课 创建高级联结

  1. 使用INNER JOIN编写SQL语句，以检索每个顾客的名称（Customers表中的cust_name）和所有的订单号（Orders表中的order_num）
SELECT c.cust_name,
       o.order_num
FROM orders o
INNER JOIN customers c ON c.cust_id = o.cust_id;
  1. 修改刚刚创建的SQL语句，仅列出所有顾客，即使他们没有下过订单
SELECT c.cust_name,
       o.order_num
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id;
  1. 使用left OUTER JOIN联结Products表和OrderItems表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按商品名称排序
SELECT p.prod_name,
       oi.order_num
FROM products p
LEFT JOIN orderitems oi ON p.prod_id = oi.prod_id
ORDER BY p.prod_name;
  1. 修改上一题中创建的SQL语句，使其返回每一项产品的总订单数（不是订单号）
SELECT p.prod_name,
       COUNT(oi.order_num) AS total_ordered
FROM products p
LEFT JOIN orderitems oi ON p.prod_id = oi.prod_id
GROUP BY p.prod_name
ORDER BY p.prod_name;
  1. 编写SQL语句，列出供应商（Vendors表中的vend_id）及其可供产品的数量，包括没有产品的供应商。你需要使用OUTER JOIN和COUNT()聚合函数来计算Products表中每种产品的数量。注意：vend_id列会显示在多个表中，因此在每次引用它时都需要完全限定它
SELECT v.vend_id,
       COUNT(p.prod_id) AS total_products
FROM vendors v
LEFT JOIN products p ON v.vend_id = p.vend_id
GROUP BY v.vend_id
ORDER BY total_products DESC;

第14课 组合查询

  1. 编写SQL语句，将两个SELECT语句结合起来，以便从OrderItems表中检索产品ID（prod_id）和quantity。其中，一个SELECT语句过滤数量为100的行，另一个SELECT语句过滤ID以BNBG开头的产品。按产品ID对结果进行排序
SELECT prod_id, quantity
FROM orderitems
WHERE quantity = 100
UNION
SELECT prod_id, quantity
FROM orderitems
WHERE prod_id LIKE 'BNBG%'
ORDER BY prod_id;
  1. 重写刚刚创建的SQL语句，仅使用单个SELECT语句
SELECT prod_id, quantity 
FROM orderitems
WHERE quantity = 100 
OR prod_id LIKE 'BNBG%';
  1. 编写SQL语句，组合Products表中的产品名称（prod_name）和Customers表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行排序
SELECT prod_name
FROM products
UNION
SELECT cust_name
FROM customers
ORDER BY prod_name;
  1. 下面的SQL语句有问题吗？（尝试在不运行的情况下指出。）
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state  = 'MI'
ORDER BY cust_name;  # 只应该有一个order by在最末尾 且 只应该有一个;在结尾
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'
ORDER BY cust_name;

第15课 插入数据

  1. 使用INSERT和指定的列，将你自己添加到Customers表中。明确列出要添加哪几列，且仅需列出你需要的列
INSERT INTO customers (
	cust_name,
    cust_city,
    cust_contact,
    cust_email
) VALUES (
  'Joy Zhang',
  'BJ',
  'Joy Zhang',
  'joy@cityu.edu'
)
  1. 备份Orders表和OrderItems表
CREATE TABLE copy_orderitems AS
SELECT * FROM orderitems;

CREATE TABLE copy_orders AS
SELECT * FROM orders

第16课 更新和删除数据

  1. 美国各州的缩写应始终用大写。编写SQL语句来更新所有美国地址，包括供应商状态（Vendors表中的vend_state）和顾客状态（Customers表中的cust_state），使它们均为大写
-- SET SQL_SAFE_UPDATES = 0
UPDATE vendors SET vend_state = UPPER(vend_state)
WHERE vend_country = 'USA';
  1. 第15课的挑战题1要求你将自己添加到Customers表中。现在请删除自己。确保使用WHERE子句（在DELETE中使用它之前，先用SELECT对其进行测试），否则你会删除所有顾客！
DELETE FROM customers
WHERE cust_id = 10006;

第17课 创建和操纵表

  1. 在Vendors表中添加一个网站列（vend_web）。你需要一个足以容纳URL的大文本字段
ALTER TABLE vendors ADD vend_web char(100);
  1. 使用UPDATE语句更新Vendor记录，以便加入网站（你可以编造任何地址）
UPDATE vendors
SET vend_web = 'https://www.coursera.com'
WHERE vend_id = 1006;

第18课 使用视图

  1. 创建一个名为CustomersWithOrders的视图，其中包含Customers表中的所有列，但仅仅是那些已下订单的列。提示：可以在Orders表上使用JOIN来仅仅过滤所需的顾客，然后使用SELECT来确保拥有正确的数据
CREATE VIEW CustomersWithOrders AS
SELECT c.* FROM customers c
JOIN orders o ON c.cust_id = o.cust_id;

SELECT * FROM CustomersWithOrders;
  1. 下面的SQL语句有问题吗？（尝试在不运行的情况下指出。）
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity＊item_price AS expanded_price
FROM OrderItems
ORDER BY order_num;

这个语句不能说一定有问题，在我的workbench里运行可以通过，在有些DBMS里运行可能会报错，报错的原因是建立视图的时候不能用也没必要用order by语句，注意一下就行

smygirlfriend
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL必知必会（第5版）学习笔记（9-17）章
weixin_45661573的博客
08-19 950
因为疫情，有两周居家学习的时间。根据导师布置的任务，将第二周的作业提交到这里。 SQL必知必会（第5版）学习笔记学习目标：学习内容：学习时间：学习产出：1. 思维导图制作：2. 9-17章挑战题第九章挑战题第十章挑战题第十一章挑战题第十二章挑战题第十三章挑战题第十四章挑战题第十五章挑战题第十六章挑战题第十七章挑战题 学习目标： 9-17章语法学习，熟悉SQL的基本操作，并提交课后题答案。 学习内容： 《SQL必知必会（第5版）》 9-17章语法学习 学习时间： 8月16日---8月20日 学习
精选 60 道 MySQL 面试题，附答案！
Java_LingFeng的博客
01-29 1706
今天给大家整理一些数据库必知必会的面试题，一起来看吧。
MySQL必知必会 第二章答案
weixin_52643599的博客
08-18 460
1．编写SQL语句，从Customers表中检索所有的ID（cust_id）。 select cust_id from customers; 2．OrderItems表包含了所有已订购的产品（有些已被订购多次）。编写SQL语句，检索并列出已订购产品（prod_id）的清单（不用列每个订单，只列出不同产品的清单）。提示：最终应该显示7行。 select distinct prod_id from orderitems; 3．编写SQL语句，检索Customers表中所有的列，再编写另外的SELECT语句，
MySQL笔记精简版(含数据源)
仁义
04-26 7398
文章目录数据库结构图1. 检索数据1.1 选择数据库1.2 选择单列1.3 选择多列1.4 选择所有列1.5 检索不同的值1.6 限制结果1.7 注释第一章练习题2. 排序检索数据2.1 排序数据2.2 按多个列排序2.3 按排列位置排序2.4 指定排序方向第二章练习题3. 过滤数据3.1 where子句3.1.1 检查单个值3.1.2 不匹配检查3.1.3 范围值检查3.1.4 空值检查第三章练习题4. 高级数据过滤4.1 组合where子句4.1.1 and操作符4.1.2 OR操作符4.1.3 求值顺
MySQL必知必会——实践习题
weixin_53364209的博客
10-08 1669
MySQL必知必会习题
mysql必知必会题目_MYSQL必知必会挑战题答案
weixin_31681589的博客
02-28 829
SQLsql语句执行顺序(8) SELECT(9)DISTINCT(1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH {CUBE|ROLLUP}(7) HAVING (10) ORDER BY (11) LIMIT (1) FROM:对FROM子句中的左表和右表执行笛卡儿积，产生虚拟表VT1;(2) ON: 对虚拟表V...
SQL必知必会 课后题答案
weixin_53273040的博客
03-13 4577
sql必知必会第五版 课后挑战题 自制答案
数据库原理及应用课件及课后习题答案
06-18
通常，这样的课件会包含以下几个方面的内容： 1. **数据库概述**：介绍数据库的定义、类型（如关系型数据库、非关系型数据库），以及数据库管理系统（DBMS）的基本功能。 2. **数据模型**：包括层次模型、网状模型...
计算机英语课后第四题翻译
01-07
【标题】：“计算机英语课后第四题翻译” 在学习计算机科学的过程中，掌握一定的专业英语是必不可少的，因为许多原始的技术文档、编程语言和软件开发工具都是英文原版。本题目聚焦于“计算机英语课后第四题”，这...
课后练习题
11-05
数据库应用课后练习题是学习数据库管理系统的常见环节，它旨在巩固课堂所学知识，提升实践操作能力。这里我们主要探讨的是与数据库相关的知识点，包括但不限于数据库设计、SQL语言、数据模型、数据库管理系统（DBMS...
PHP程序设计-3期（KC016） 4.3.3添加记录课后习题.doc
07-13
本节我们将深入探讨如何在PHP环境中创建数据表以及使用SQL语句添加记录，结合课后习题4-3-2来进一步巩固这些概念。 首先，我们需要创建一个数据表。在描述中提到的习题1，我们被要求使用第三方工具，如Navicat，来...
java web开发实战经典jsp+mysql版
08-24
通过阅读本书和完成课后习题，读者将能全面了解Java Web开发流程，掌握JSP与MySQL的集成应用，为实际的项目开发打下坚实基础。同时，对于想要进一步提升的开发者，还可以探索Spring MVC、Hibernate等更高级的框架，...
浅谈SQL Server、Oracle和MySQL
a876106354的博客
08-15 431
SQL Server 是Microsoft公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点，可跨越从运行MicrosoftWindows 98的膝上型电脑到运行2012 的大型多处理器的服务器等多种平台使用。Microsoft SQL Server 是一个全面的数据库平台，使用集成的商业智能(BI)工具提供了企业级的数据管理。数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使您可以构建和管理用于业务的高可用和高性能的数据应用程序。MySQL是一个。
Java实现MySQL表结构导出
promise524的博客
08-12 336
Java实现MySQL表结构导出
【面试宝典】mysql常见面试题总结(上)
Tsing
08-11 628
MySQL中的事务是一个作为单个逻辑工作单元执行的一系列操作。事务具有ACID属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这些属性确保了事务的完整性和可靠性。原子性：事务中的所有操作要么全部完成，要么全部不执行，不会结束在中间某个环节。一致性：事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。隔离性。
中医体验馆预约系统小程序的设计与实现---附源码14660
weixin_BYSJ1987的博客
08-12 722
本论文将详细介绍 PHP 中医体验馆预约系统小程序的设计与实现。首先，我们将对系统进行需求分析，确定系统的功能和性能要求。然后，根据需求分析的结果，进行系统设计，包括数据库设计、界面设计和功能模块设计等。在系统实现部分，我们将详细介绍系统的各个功能模块的实现过程，并给出相应的代码示例。最后，我们将对系统进行测试和总结，评估系统的性能和功能是否符合预期。
mysql5.7安装
kevin_cat的博客
08-12 231
改成命令 rpm -ivh mysql-community-release-el7-5.noarch.rpm --nodeps --force。1.创建一个software文件。4、此时查看mysqld服务是否开启。2.先下载mysql的repo源。
mysql篇-mysql的锁
最新发布
qq_38360229的博客
08-15 34
此文为mysql 45第21课学习笔记。
MySQL入门经典：MySQL必知必会详解
"《MySQL必知必会》是Ben Forta所著的一本关于MySQL数据库管理系统的实用指南，由刘晓霞和钟鸣翻译成中文，属于图灵程序设计丛书中的一部。这本书旨在帮助读者从基础到进阶地了解和掌握MySQL，适合软件开发人员和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值