--
CREATE DATABASE IF NOT EXISTS `customer_orders` DEFAULT CHARACTER
SET utf8 COLLATE utf8_general_ci;
USE `customer_orders`;
-- --------------------------------------------------------
--
-- Table structure for table `customer`
--
CREATE TABLE `customer` (
`custID` int(11) NOT NULL,
`custName` varchar(100) NOT NULL,
`street` varchar(30) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`zip` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `customer`
--
INSERT INTO `customer` (`custID`, `custName`, `street`, `city`,
`state`, `zip`) VALUES
(1, 'John Doe', '200 Maple', 'SLC', 'UT', '84102'),
(2, 'Bill Doll', '300 West', 'SLC', 'UT', '84107'),
(3, 'Josh Turburn', '250 North', 'SLC', 'UT', '84108'),
(4, 'Mary Lee', '200 South', 'SLC', 'UT', '84102'),
(5, 'Jane Smith', '120 University', 'SLC', 'UT', '84102'),
(6, 'Luis Smith', '200 Maple', 'SLC', 'UT', '84102'),
(7, 'Ben Brown', '300 West', 'SLC', 'UT', '84107'),
(8, 'Carl Smith', '250 North', 'SLC', 'UT', '84108'),
(9, 'John Doll', '200 South', 'SLC', 'UT', '84102'),
(10, 'Jennet Chris', '120 University', 'SLC', 'UT', '84102'),
(11, 'Joyce French', '200 Main', 'Tucson', 'AZ', '45102'),
(12, 'Jennifer English', '250 State', 'Tucson', 'AZ',
'45112'),
(13, 'Tom Borg', '3000 Sunset', 'LA', 'CA', '12112'),
(14, 'Helen Thomas', '1200 Hollywood', 'LA', 'CA', '12117'),
(15, 'Brian Borg', '100 College', 'SF', 'CA', '17118');
-- --------------------------------------------------------
--
-- Table structure for table `orderLine`
--
CREATE TABLE `orderLine` (
`orderID` int(11) NOT NULL,
`productID` int(11) NOT NULL,
`quantity` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `orderLine`
--
INSERT INTO `orderLine` (`orderID`, `productID`, `quantity`)
VALUES
(1001, 101, 2),
(1001, 102, 2),
(1001, 104, 1),
(1002, 103, 5),
(1003, 103, 3),
(1004, 106, 2),
(1004, 109, 2),
(1005, 104, 4),
(1006, 104, 1),
(1006, 105, 2),
(1006, 107, 2),
(1007, 102, 2),
(1008, 103, 3),
(1008, 109, 3),
(1009, 104, 2),
(1009, 107, 3),
(1010, 109, 10);
-- --------------------------------------------------------
--
-- Table structure for table `orders`
--
CREATE TABLE `orders` (
`orderID` int(11) NOT NULL,
`orderDate` date DEFAULT NULL,
`custID` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `orders`
--
INSERT INTO `orders` (`orderID`, `orderDate`, `custID`)
VALUES
(1001, '2008-10-24', 1),
(1002, '2008-10-21', 8),
(1003, '2008-10-22', 15),
(1004, '2008-10-22', 5),
(1005, '2008-10-24', 3),
(1006, '2008-10-24', 2),
(1007, '2008-10-27', 11),
(1008, '2008-10-30', 12),
(1009, '2008-11-01', 4),
(1010, '2008-11-05', 1);
-- --------------------------------------------------------
--
-- Table structure for table `product`
--
CREATE TABLE `product` (
`productID` int(11) NOT NULL,
`productName` varchar(50) DEFAULT NULL,
`productPrice` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`productID`, `productName`,
`productPrice`) VALUES
(101, 'Dell E5300 Laptop', 489.98),
(102, 'Apple Laptop', 988.72),
(103, 'Printer', 59),
(104, 'Desk', 85.98),
(105, 'Office Chair', 55.99),
(106, 'Stapler', 15.88),
(107, 'Index Divider', 5.99),
(109, 'Shredder', 74.99);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
ADD PRIMARY KEY (`custID`);
--
-- Indexes for table `orderLine`
--
ALTER TABLE `orderLine`
ADD PRIMARY KEY (`orderID`,`productID`),
ADD KEY `orderline_fk2` (`productID`);
--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
ADD PRIMARY KEY (`orderID`),
ADD KEY `order_fk` (`custID`);
--
-- Indexes for table `product`
--
ALTER TABLE `product`
ADD PRIMARY KEY (`productID`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `customer`
--
ALTER TABLE `customer`
MODIFY `custID` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=16;
--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
MODIFY `orderID` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=1011;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
MODIFY `productID` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=110;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `orderLine`
--
ALTER TABLE `orderLine`
ADD CONSTRAINT `orderline_fk1` FOREIGN KEY (`orderID`)
REFERENCES `orders` (`orderID`) ON UPDATE CASCADE,
ADD CONSTRAINT `orderline_fk2` FOREIGN KEY (`productID`)
REFERENCES `product` (`productID`) ON UPDATE CASCADE;
--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
ADD CONSTRAINT `order_fk` FOREIGN KEY (`custID`) REFERENCES
`customer` (`custID`) ON UPDATE CASCADE;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT
*/;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS
*/;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION
*/;
Lab: SQL – DML Query in MySQL Combining Queries, Views,
and Stored Procedures
We import the four tables using customer_orders.sql
script from above.
Open a lab.txt text file to hold your working SQL
statements. Once you have ensured that a particular SQL statement
below is working, paste it into the text file.
DML: query
1. List productID from both product and orderLine tables using
UNION.
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 1 (4.17 KiB) Viewed 48 times
UNION ALL.
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 2 (7.35 KiB) Viewed 48 times
something.
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 3 (2.63 KiB) Viewed 48 times
after 10% discount of all products that exist in orderline.
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 4 (32.91 KiB) Viewed 48 times
‘Smith’.
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 5 (16.29 KiB) Viewed 48 times
purchased in order 1001.
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 6 (12.3 KiB) Viewed 48 times
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 7 (9.57 KiB) Viewed 48 times
Apple Laptop. And invoke the procedure.
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 8 (5.14 KiB) Viewed 48 times
bought by customers from x city where x is a user given parameter.
And invoke the procedure with x=SLC.
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 9 (6.86 KiB) Viewed 48 times
of times they appear in ‘orderline’ in more than x orders where x
is a user given parameter. And invoke the procedure with x=2
- Database Customer Orders Create Database If Not Exists Customer Orders Default Character Set Utf8 Collate Utf8 G 10 (10.66 KiB) Viewed 48 times
productID 101 102 103 104 105 106 107 109 101 102 102 103 103 103 104 104 104 104 105 106 107 107 109 109 109
custid 1 2 3 4 5 8
productName Dell E5300 Laptop Apple Laptop Printer Desk Office Chair Stapler Index Divider Shredder productPrice 489.98 988.72 59 85.98 55.99 15.88 5.99 discount Price 440.982 889.8480000000001 53.1 77.382 50.391000000000005 14.292000000000002 5.391 67.491 74.99
custID 5 8 Сл custName street city state zip Jane Smith 120 University SLC UT 84102 Carl Smith 250 North SLC UT 84108 00
productName productPrice Printer 59 Office Chair 55.99 Stapler 15.88 Index Divider 5.99 Shredder 74.99
custID custName Street city state zip Brian Borg 100 College SF CA 17118 15
custName John Doe Joyce French
average_price 162.7269230769231
productName COUNT(product.productName) Desk 4 Printer 3 Shredder 3