数据库，advanced SQL,根据数据库表格数据和问题编写SQL代码

Question

数据库，Advanced SQL,已经根据数据库表格数据创建数据库并插入了数据，编写剩余的SQL代码
3 编写查询，生成不包含重复客户记录的组合表
4 编写查询，生成CUSTOMER_2特有的记录
5 编写查询显示客户余额为1000或以上的所有客户的发票编号，客户编号，客户名字，发票日期，发票金额
6 编写查询，显示发票编号，发票金额，平均发票金额和平均发票金额和实际发票金额的差
7 修改customer表以包含两个新属性CUST_DOB, CUST_AGE，客户1000在1979年3月15日出生，客户1001在1988年12月22日出生
8.完成了第7个问题，编写查询，列出客户的名字和年龄
9.编写查询，列出客户平均年龄（假设customer表已修改为包含CUST_DOB和CUST_AGE属性）

#Task1
#1
CREATE SCHEMA Task1;
use Task1;
CREATE TABLE CUSTOMER(CUST_NUM int, CUST_LNAME CHAR(20), CUST_FNAME CHAR(20), CUST_BALANCE CHAR(20));
CREATE TABLE CUSTOMER_2(CUST_NUM int, CUST_LNAME CHAR(20), CUST_FNAME CHAR(20));
CREATE TABLE INVOICE(INV_NUM int, CUST_NUM int, INV_DATE CHAR(20), INV_AMOUNT CHAR(20));
#2
INSERT INTO CUSTOMER(CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE)
VALUES(1000, 'Smith', 'Jeanne', 1050.11),
(1001, 'Ortega', 'Juan', 840.92);
INSERT INTO CUSTOMER_2(CUST_NUM, CUST_LNAME, CUST_FNAME)
VALUES(2000, 'McPherson', 'Anne'),
(2001, 'Ortega', 'Juan'),
(2002, 'Kowalski', 'Jan'),
(2003, 'Chen', 'George');
INSERT INTO INVOICE(INV_NUM, CUST_NUM, INV_DATE, INV_AMOUNT)
VALUES(8000, 1000, '23-Mar-14', 235.89),
(8001, 1001, '23-Mar-14', 312.82),
(8002, 1001, '30-Mar-14', 528.10),
(8003, 1000, '12-Apr-14', 194.78),
(8004, 1000, '23-Apr-14', 619.44);
COMMIT;

Answer 1

1. 3题

   SELECT 
    *
   FROM
    INVOICE
   WHERE
    INV_NUM IN (SELECT 
            MIN(INV_NUM)
        FROM
            INVOICE
        GROUP BY CUST_NUM)
   

2. 4题

   SELECT 
    INVOICE.*
   FROM
    INVOICE
        INNER JOIN
    CUSTOMER
        JOIN
    CUSTOMER_2
   WHERE
    INVOICE.CUST_NUM = CUSTOMER.CUST_NUM
        AND CUSTOMER.CUST_LNAME = CUSTOMER_2.CUST_LNAME
        AND CUSTOMER.CUST_FNAME = CUSTOMER_2.CUST_FNAME
   

3. 5题

   SELECT 
    INVOICE.*, CUSTOMER.CUST_LNAME, CUSTOMER.CUST_FNAME
   FROM
    INVOICE
        INNER JOIN
    CUSTOMER
   WHERE
    INVOICE.CUST_NUM = CUSTOMER.CUST_NUM
        AND CUSTOMER.CUST_BALANCE >= 1000
   

4. 6题

   SELECT 
    *,
    (SELECT 
            AVG(INV_AMOUNT)
        FROM
            INVOICE) AS avg,
    (SELECT 
            AVG(INV_AMOUNT)
        FROM
            INVOICE) - INV_AMOUNT AS cha
   FROM
    INVOICE;
   

5. 7题

   ALTER TABLE CUSTOMER
   ADD COLUMN CUST_DOB DATE NULL,
   ADD COLUMN CUST_AGE INT NULL;
   ALTER TABLE `CUSTOMER` ADD PRIMARY KEY (`CUST_NUM`);
   UPDATE CUSTOMER SET CUST_DOB = '1979-3-15', CUST_AGE = '43' WHERE CUST_NUM = '1000';
   UPDATE CUSTOMER SET CUST_DOB = '1988-12-22', CUST_AGE = '34' WHERE CUST_NUM = '1001';
   

6. 8题

   SELECT 
    CUST_LNAME, CUST_FNAME, CUST_AGE
   FROM
    CUSTOMER
   

7. 8题

   SELECT 
    AVG(CUST_AGE)
   FROM
    CUSTOMER