2019-04-05 09:56:17
customer表
invoice表
invoice_item表
一、建表和插入数据
/********************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (14th Edition) ch 2 */
/* */
/* Marcia's Dry Cleaning [MDC] Project Create Tables */
/* */
/* These are the MySQL 5.6 SQL code solutions */
/* Note: MySQL does not support auto_increment with a step besides 1. */
/* Thus, customerIDs are inserted manually 100, 105, 110, etc. */
/********************************************************************************/
CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL auto_increment,
FirstName Char(25) NOT NULL,
LastName Char(25) NOT NULL,
Phone Char(12) NOT NULL,
Email varchar(100) NULL,
CONSTRAINT CustomerPK PRIMARY KEY(CustomerID)
);
CREATE TABLE INVOICE(
InvoiceNumber Int NOT NULL auto_increment,
CustomerNumber Int NOT NULL,
DateIn Date NOT NULL,
DateOut Date NULL,
TotalAmount Numeric(8,2) NULL,
CONSTRAINT InvoicePK PRIMARY KEY (InvoiceNumber),
CONSTRAINT Invoice_Cust_FK FOREIGN KEY(CustomerNumber)
REFERENCES CUSTOMER(CustomerID)
);
ALTER TABLE invoice AUTO_INCREMENT = 20150001;
CREATE TABLE INVOICE_ITEM(
InvoiceNumber Int NOT NULL,
ItemNumber Int NOT NULL,
Item Char(50) NOT NULL,
Quantity Int NOT NULL DEFAULT 1,
UnitPrice Numeric(8,2) NULL,
CONSTRAINT InvoiceItemPK PRIMARY KEY(InvoiceNumber, ItemNumber),
CONSTRAINT Invoice_Item_FK FOREIGN KEY(InvoiceNumber)
REFERENCES INVOICE(InvoiceNumber)
ON UPDATE CASCADE
ON DELETE CASCADE
);
/********************************************************************************/
插入数据
/******************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (14th Edition) Chapter 2 */
/* */
/* Marcia's Dry Cleaing (MDC) Database - Insert Data */
/* These are the MySQL 5.6 SQL code solutions */
/******************************************************************************/
INSERT INTO CUSTOMER VALUES(
1, 'Nikki', 'Kaccaton', '723-543-1233',
'Nikki.Kaccaton@somewhere.com');
INSERT INTO CUSTOMER VALUES(
2, 'Brenda', 'Catnazaro', '723-543-2344',
'Brenda.Catnazaro@somewhere.com');
INSERT INTO CUSTOMER VALUES(
3, 'Bruce', 'LeCat', '723-543-3455',
'Bruce.LeCat@somewhere.com');
INSERT INTO CUSTOMER VALUES(
4, 'Betsy', 'Miller', '725-654-3211',
'Betsy.Miller@somewhere.com');
INSERT INTO CUSTOMER VALUES(
5, 'George', 'Miller', '725-654-4322',
'George.Miller@somewhere.com');
INSERT INTO CUSTOMER VALUES(
6, 'Kathy', 'Miller', '723-514-9877',
'Kathy.Miller@somewhere.com');
INSERT INTO CUSTOMER VALUES(
7, 'Betsy', 'Miller', '723-514-8766',
'Betsy.Miller@elsewhere.com');
/* */
INSERT INTO INVOICE VALUES(
2015001,1,'2015-10-04','2015-10-06',158.50);
INSERT INTO INVOICE VALUES(
2015002,2,'2015-10-04','2015-10-06',25.00);
INSERT INTO INVOICE VALUES(
2015003,1,'2015-10-06','2015-10-08',49.00);
INSERT INTO INVOICE VALUES(
2015004,4,'2015-10-06','2015-10-08',17.50);
INSERT INTO INVOICE VALUES(
2015005,6,'2015-10-07','2015-10-11',12.00);
INSERT INTO INVOICE VALUES(
2015006,3,'2015-10-11','2015-10-13',152.50);
INSERT INTO INVOICE VALUES(
2015007,3,'2015-10-11','2015-10-13',7.00);
INSERT INTO INVOICE VALUES(
2015008,7,'2015-10-12','2015-10-14',140.50);
INSERT INTO INVOICE VALUES(
2015009,5,'2015-10-12','2015-10-14',27.00);
/* */
INSERT INTO INVOICE_ITEM VALUES(2015001, 1, 'Blouse', 2, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2015001, 2, 'Dress Shirt', 5, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2015001, 3, 'Formal Gown', 2, 10.00);
INSERT INTO INVOICE_ITEM VALUES(2015001, 4, 'Slacks-Mens', 10, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2015001, 5, 'Slacks-Womens', 10, 6.00);
INSERT INTO INVOICE_ITEM VALUES(2015001, 6, 'Suit-Mens', 1, 9.00);
INSERT INTO INVOICE_ITEM VALUES(2015002, 1, 'Dress Shirt', 10, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2015003, 1, 'Slacks-Mens', 5, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2015003, 2, 'Slacks-Womens', 4, 6.00);
INSERT INTO INVOICE_ITEM VALUES(2015004, 1, 'Dress Shirt', 7, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2015005, 1, 'Blouse', 2, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2015005, 2, 'Dress Shirt', 2, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2015006, 1, 'Blouse', 5, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2015006, 2, 'Dress Shirt', 10, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2015006, 3, 'Slacks-Mens', 10, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2015006, 4, 'Slacks-Womens', 10, 6.00);
INSERT INTO INVOICE_ITEM VALUES(2015007, 1, 'Blouse', 2, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2015008, 1, 'Blouse', 3, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2015008, 2, 'Dress Shirt', 12, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2015008, 3, 'Slacks-Mens', 8, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2015008, 4, 'Slacks-Womens', 10, 6.00);
INSERT INTO INVOICE_ITEM VALUES(2015009, 1, 'Suit-Mens', 3, 9.00);
/* */
题目:
-- A.显示张表中的所有数据
select * from CUSTOMER;
select * from INVOICE;
select * from INVOICE_ITEM;
-- B.列出每个客户的LastName,FirstName和Phone
select LastName, FirstName, Phone from CUSTOMER;
-- C.列出所有名为'Nikki'客户的LastName, FirstName和Phone
select LastName, FirstName, Phone from CUSTOMER where FirstName = 'Nikki';
-- D.列出所有超出100元的订单LastName, FirstName, Phone, DataIn和DateOut
select CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone, INVOICE.DateIn, INVOICE.DateOut
from CUSTOMER, INVOICE
where CUSTOMER.CustomerID = INVOICE.CustomerNumber
and INVOICE.TotalAmount > 100;
-- E.列出所有名字以'B'开始的客户的LastName,FirstName和Phone
select LastName, FirstName, Phone from CUSTOMER where FirstName like 'B%';
-- F.列出所有姓氏包含字符'cat'的客户的LastName, FirstName和Phone
select LastName, FirstName, Phone from CUSTOMER where LastName like '%cat%';
-- G.列出所有电话号码第二位和第三位分别是2和3的客户的LastName, FirstName和Phone
select LastName, FirstName, Phone from CUSTOMER
where Phone like '_23%';
-- H.确定最大和最小的TotalAmount
select max(TotalAmount) as maxamount, min(TotalAmount) as minamount
from INVOICE;
-- I确定平均的TotalAmount
select avg(TotalAmount) as avgTotalAmount from INVOICE;
-- J.计算客户数
select count(*) from CUSTOMER;
-- M.使用子查询, 给出拥有单一订单总价超过100元的客户LastName, FirstName, Phone。结果按照LastName升序排列, 再按照FirstName降序。
select CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone from CUSTOMER
where CUSTOMER.CustomerID in (select CustomerNumber from INVOICE where TotalAmount > 100 group by
CustomerNumber having count(*) = 1)
order by CUSTOMER.LastName, CUSTOMER.FirstName asc;
-- N.使用联接, 但不使用JOIN ON语法, 给出拥有单一订单总价超过100元的客户LastName, FirstName, Phone。结果按照LastName升序排列, 再按照FirstName降序。
select CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone from CUSTOMER, INVOICE
where CUSTOMER.CustomerID = INVOICE.CustomerNumber and INVOICE.TotalAmount > 100
group by CUSTOMER.CustomerID
having count(*) = 1
order by CUSTOMER.LastName, CUSTOMER.FirstName asc;
-- O.使用JOIN ON语法, 给出拥有单一订单总价超过100元的客户LastName, FirstName, Phone。结果按照LastName升序排列, 再按照FirstName降序。
select CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone from CUSTOMER inner join INVOICE
on ( CUSTOMER.CustomerID = INVOICE.CustomerNumber and INVOICE.TotalAmount > 100 )
group by CUSTOMER.CustomerID
having count(*) = 1
order by CUSTOMER.LastName asc, CUSTOMER.FirstName desc;
-- P.使用子查询, 给出拥有包含物品'Dress Shirt'的订单的客户LastName, FirstName, Phone。
-- 结果按照LastName升序排列, 再按照FirstName降序
select CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone from CUSTOMER
where CUSTOMER.CustomerID in (
select INVOICE.CustomerNumber from INVOICE
where INVOICE.InvoiceNumber
in (select INVOICE_ITEM.InvoiceNumber from INVOICE_ITEM where Item in ('Dress Shirt'))
)
order by CUSTOMER.LastName asc, CUSTOMER.FirstName desc;
-- Q.使用联接,但不使用JOIN ON语法, 给出拥有包含物品'Dress Shirt'的订单的客户LastName, FirstName, Phone。
-- 结果按照LastName升序排列, 再按照FirstName降序
select CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone from CUSTOMER, INVOICE, INVOICE_ITEM
where
CUSTOMER.CustomerID = INVOICE.CustomerNumber
and
INVOICE.InvoiceNumber = INVOICE_ITEM.InvoiceNumber
and
INVOICE_ITEM.Item = 'Dress Shirt'
order by CUSTOMER.LastName asc, CUSTOMER.FirstName desc;
-- T.给出拥有包含物品'Dress Shirt'的订单的客户LastName, FirstName, Phone和TotalAmount。同时也列出其他客户的LastName, FirstName, Phone
-- 结果按照LastName升序排列, 再按照FirstName降序
select CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone, a.TotalAmount
from CUSTOMER
left join
(select INVOICE.CustomerNumber, INVOICE.TotalAmount
from INVOICE
where INVOICE.InvoiceNumber
in
(select INVOICE_ITEM.InvoiceNumber from INVOICE_ITEM where Item in ('Dress Shirt'))
) as a
on CUSTOMER.CustomerID = a.CustomerNumber
order by CUSTOMER.LastName asc, CUSTOMER.FirstName desc;
问题:计算不同名同姓的客户数?
select count(*) from (
select count(*) as n from CUSTOMER
group by CUSTOMER.FirstName, CUSTOMER.LastName
having count(*) = 1
) t;