数据库基础-基础、设计与实现 Marcia干洗店项目练习

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;