MS SQL Schema
/********************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (13th Edition) Chapter 02 */
/* */
/* Marcia's Dry Cleaning [MDC-CH02] Project Create Tables */
/* */
/* Microsoft SQL Server 2008 R2/2012 code solutions */
/* */
/********************************************************************************/
CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL IDENTITY (1, 1),
FirstName Char(25) NOT NULL,
LastName Char(25) NOT NULL,
Phone Char(12) NOT NULL,
Email Char(100) NULL,
CONSTRAINT CustomerPK PRIMARY KEY(CustomerID)
);
CREATE TABLE INVOICE(
InvoiceNumber Int NOT NULL,
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)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
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 Invoice_ItemPK PRIMARY KEY(InvoiceNumber, ItemNumber),
CONSTRAINT Item_Invoice_FK FOREIGN KEY(InvoiceNumber)
REFERENCES INVOICE(InvoiceNumber)
ON UPDATE CASCADE
ON DELETE CASCADE,
);
/********************************************************************************/
/********************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (12th Edition) Chapter 07 */
/* */
/* Morgan Importing [MI-CH07] Project Insert Data */
/* */
/* Microsoft SQL Server 2008 / 2008 R2 code solutions */
/* */
/********************************************************************************/
/***** ITEM Data ************************************************************/
INSERT INTO ITEM VALUES
('QE Dining Set', '07-Apr-13', 'Eastern Treasures',
'Manila', 2, 403405, 0.01774);
INSERT INTO ITEM
VALUES('Willow Serving Dishes', '15-Jul-13', 'Jade Antiques',
'Singapore', 75, 102, 0.5903);
INSERT INTO ITEM
VALUES('Large Bureau', '17-Jul-13', 'Eastern Sales',
'Singapore', 8, 2000, 0.5903);
INSERT INTO ITEM
VALUES('Brass Lamps', '20-Jul-13', 'Jade Antiques',
'Singapore', 40, 50, 0.5903);
/***** SHIPMENT Data ********************************************************/
INSERT INTO SHIPMENT VALUES
('ABC Trans-Oceanic', 2008651, '10-Dec-12', '15-Mar-13', 15000.00);
INSERT INTO SHIPMENT VALUES
('ABC Trans-Oceanic', 2009012, '10-Jan-13', '20-Mar-13', 12000.00);
INSERT INTO SHIPMENT VALUES
('Worldwide', 49100300, '05-May-13', '17-Jun-13', 20000.00);
INSERT INTO SHIPMENT VALUES
('International', 399400, '02-Jun-13', '17-Jul-13', 17500.00 );
INSERT INTO SHIPMENT VALUES
('Worldwide', 84899440, '10-Jul-13', '28-Jul-13', 25000.00);
INSERT INTO SHIPMENT VALUES
('International', 488955, '05-Aug-13', '11-Sep-13', 18000.00);
/***** SHIPMENT Item ********************************************************/
INSERT INTO SHIPMENT_ITEM VALUES(3, 1, 1, 15000);
INSERT INTO SHIPMENT_ITEM VALUES(4, 1, 4, 1200);
INSERT INTO SHIPMENT_ITEM VALUES(4, 2, 3, 9500);
INSERT INTO SHIPMENT_ITEM VALUES(4, 3, 2, 4500);
/********************************************************************************/
MySQL Schema
/********************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (13th Edition) Chapter 02 */
/* */
/* Marcia's Dry Cleaning [MDC-CH2] Project Create Tables */
/* */
/* Theses are the MySQL 5.6 SQL code solutions */
/* */
/********************************************************************************/
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 Char(100) NULL,
CONSTRAINT CustomerPK PRIMARY KEY(CustomerID)
);
CREATE TABLE INVOICE(
InvoiceNumber Int NOT NULL,
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)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
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 (13th Edition) Chapter 02 */
/* */
/* Marcia's Dry Cleaning [MDC-CH02] Project Create Tables */
/* */
/* Theses are the MySQL 5.6 SQL code solutions */
/* */
/********************************************************************************/
/***** CUSTOMER Data *********************************************************/
INSERT INTO CUSTOMER (LastName, FirstName, Phone, Email) VALUES(
'Nikki', 'Kaccaton', '723-543-1233', 'Nikki.Kaccaton@somewhere.com');
INSERT INTO CUSTOMER (LastName, FirstName, Phone, Email) VALUES(
'Brenda', 'Catnazaro', '723-543-2344', 'Brenda.Catnazaro@somewhere.com');
INSERT INTO CUSTOMER (LastName, FirstName, Phone, Email) VALUES(
'Bruce', 'LeCat', '723-543-3455', 'Bruce.LeCat@somewhere.com');
INSERT INTO CUSTOMER (LastName, FirstName, Phone, Email) VALUES(
'Betsy', 'Miller', '725-654-3211', 'Betsy.Miller@somewhere.com');
INSERT INTO CUSTOMER (LastName, FirstName, Phone, Email) VALUES(
'George', 'Miller', '725-654-4322', 'George.Miller@somewhere.com');
INSERT INTO CUSTOMER (LastName, FirstName, Phone, Email) VALUES(
'Kathy', 'Miller', '723-514-9877', 'Kathy.Miller@somewhere.com');
INSERT INTO CUSTOMER (LastName, FirstName, Phone, Email) VALUES(
'Betsy', 'Miller', '723-514-8766', 'Betsy.Miller@elsewhere.com');
/***** INVOICE Data **********************************************************/
INSERT INTO INVOICE VALUES(
2013001, 1, '2013-10-04', '2013-10-06', 158.50);
INSERT INTO INVOICE VALUES(
2013002, 2, '2013-10-04', '2013-10-06', 25.00);
INSERT INTO INVOICE VALUES(
2013003, 1, '2013-10-06', '2013-10-08', 49.00);
INSERT INTO INVOICE VALUES(
2013004, 4, '2013-10-06', '2013-10-08', 17.50);
INSERT INTO INVOICE VALUES(
2013005, 6, '2013-10-07', '2013-10-11', 12.00);
INSERT INTO INVOICE VALUES(
2013006, 3, '2013-10-11', '2013-10-13', 152.50);
INSERT INTO INVOICE VALUES(
2013007, 3, '2013-10-11', '20131-10-13', 7.00);
INSERT INTO INVOICE VALUES(
2013008, 7, '2013-10-12', '2013-10-14', 140.50);
INSERT INTO INVOICE VALUES(
2013009, 5, '2013-10-12', '2013-10-14', 27.00);
/***** INVOICE_ITEM Data *****************************************************/
INSERT INTO INVOICE_ITEM VALUES(2013001, 1, 'Blouse', 2, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2013001, 2, 'Dress Shirt', 5, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2013001, 3, 'Formal Gown', 2, 10.00);
INSERT INTO INVOICE_ITEM VALUES(2013001, 4, 'Slacks-Mens', 10, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2013001, 5, 'Slacks-Womens', 10, 6.00);
INSERT INTO INVOICE_ITEM VALUES(2013001, 6, 'Suit-Mens', 1, 9.00);
INSERT INTO INVOICE_ITEM VALUES(2013002, 1, 'Dress Shirt', 10, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2013003, 1, 'Slacks-Mens', 5, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2013003, 2, 'Slacks-Womens', 4, 6.00);
INSERT INTO INVOICE_ITEM VALUES(2013004, 1, 'Dress Shirt', 7, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2013005, 1, 'Blouse', 2, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2013005, 2, 'Dress Shirt', 2, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2013006, 1, 'Blouse', 5, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2013006, 2, 'Dress Shirt', 10, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2013006, 3, 'Slacks-Mens', 10, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2013006, 4, 'Slacks-Womens', 10, 6.00);
INSERT INTO INVOICE_ITEM VALUES(2013007, 1, 'Blouse', 2, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2013008, 1, 'Blouse', 3, 3.50);
INSERT INTO INVOICE_ITEM VALUES(2013008, 2, 'Dress Shirt', 12, 2.50);
INSERT INTO INVOICE_ITEM VALUES(2013008, 3, 'Slacks-Mens', 8, 5.00);
INSERT INTO INVOICE_ITEM VALUES(2013008, 4, 'Slacks-Womens', 10, 6.00);
INSERT INTO INVOICE_ITEM VALUES(2013009, 1, 'Suit-Mens', 3, 9.00);
/********************************************************************************/