Wine Database System
SQL Queries

Single Table Queries

Our first single table query is intended to find the order id of all sales of a certain day. So for example we chose to run a query for October 13, 2004 and the result was that order id 1006 occurred that day. The query is as follows.

SELECT ORDER_ID
from ORDER_T
where order_date = '13/Oct/04';

Results:
1006

The second single table query is to find the orders that took place during a specified time period. We will be able to run this query for accounting and financial purposes at any time with very little effort. What might have taken hours to calculate can be accomplished in seconds with this query.

SELECT ORDER_ID, ORDER_DATE
FROM ORDER_T
WHERE ORDER_DATE > '01/SEP/2004'
AND ORDER_DATE < '30/SEP/2004';

Results:
1002 16-SEP-04
1003 20-SEP-04

The third single table query we made is to find the inventory that needs to be re-ordered because it is low in quantity. We find this to make sure we re-order certain wines before they are out of stock. Customer service is a top priority and nothing is worse than turning down a customer that is ready to buy something because the product is out of stock.

SELECT Bar_Code, Product_Quantity
FROM PRODUCT_T
WHERE PRODUCT_QUANTITY < 24;

Results:
53125 12
83225 12

The fourth query is done to find customer ratings of wine that were rated below 4. The e-commerce site we hope to implement in the future will allow the customer to rate the wine after they purchase and try it. We can easily find which wines are consistently rated poorly and make managerial decisions based on the results. If a specific wine is being rated low, we can discontinue the product.

SELECT BAR_CODE, RATING
FROM PRODUCT_RATING_T
WHERE RATING < 4;

Results:
12113 3
36748 3

The fifth single table queries are done to find how many customers we have in specific age groups. For example the first query below finds those that are between the ages of 21 and 30, 21, being the legal drinking age limit, is therefore our youngest possible customer. Then the second, third and fourth queries below find the customers between the ages 30-40, 40-50 and 50 and above. We will use this query periodically to find out who our customers are in order to target our marketing towards these customers.

SELECT count (dob)
from customer_t
where dob > '02/dec/1974' and dob < '02/dec/1983';

SELECT count (dob)
from customer_t
where dob > '10/dec/1964' and dob < '10/dec/1973';

SELECT count (dob)
from customer_t
where dob > '10/dec/1954' and dob < '10/dec/1963';

SELECT count (dob)
from customer_t
where dob < '10/dec/1954';

Results:
21-30 = 3
31-40 = 1
41-50 = 7
50+ = 3

Our sixth and final single table query is to find what states our customers are in. We will count the customer states from the customer table in order to find our result. This will help in our marketing campaigns along with the customer age group. We can target our market by age group and regions which will greatly increase our return on investment for marketing dollars.

SELECT customer_state, COUNT(customer_state)
FROM CUSTOMER_zip_t
GROUP BY customer_state
HAVING COUNT(customer_state) > 1;

Multi-Table Queries

The first multiple table query we chose to write is to find all the information needed to create an invoice. We can do this by order number so in the example below it says order_t.order_id = 1003. This long query will be done every time a customer orders wine online. They will be sent an automatic responded email with the information that results from this query. They will be able to check the information in order to make sure everything was filled out correctly when ordering.

select customer_t.customer_id, customer_name, customer_address, customer_email, zip_code, order_t.order_id, order_date, order_line_t.order_quantity , price, product_t.product_name, (order_line_t.order_quantity * product_t.price)
from customer_t, order_t, order_line_t, product_t
where customer_t.customer_id = order_t.customer_id
and order_t.order_id = order_line_t.order_id
and order_line_t.bar_code = product_t.bar_code
and order_t.order_id = 1003;

Results:
CUSTOMER_ID CUSTOMER_NAME CUSTOMER_ADDRESS
----------- ------------------------- ------------------------------
CUSTOMER_EMAIL ZIP_CODE ORDER_ID
---------------------------------------- --------- -------------------------
ORDER_DAT ORDER_QUANTITY PRICE PRODUCT_NAME
--------- -------------- ---------- ------------------------------
(ORDER_LINE_T.ORDER_QUANTITY*PRODUCT_T.PRICE)
---------------------------------------------
15 Robert Aboolian 6687 Second Ave.
raboolian@spamail.com 23465 1003
20-SEP-04 3 15.99 Dynamite Vineyards Merlot
47.97

Our second multi-table query is to find the customers that have purchased at least 6 bottles of one type of wine at one time. For example I order 8 bottles of Buehler Cabernet in one order, then my customer id and email would be in the results. We intend to contact the resulting customers with information on how to purchase in bulk when the managers decide it is time to sell off mass quantities of wine. If we have extra stock of particular wines, these customers will be emailed first with special coupons and deals.

SELECT customer_t.customer_id, customer_t.customer_email
FROM CUSTOMER_T, ORDER_LINE_t, ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID
AND ORDER_T.ORDER_ID = ORDER_LINE_T.ORDER_ID
AND ORDER_LINE_T.ORDER_QUANTITY >= 6;

Results:
5 mjohnson@spamail.com
11 she@spamail.com

The third multi-table query is to find customers that have purchased the most expensive wine. We found that the most expensive wine we currently have in stock is bar code number 36748. Therefore we can search to find who has purchased this wine in the past. When we have expensive wine that needs to be sold we will contact these customers first. The query gives us there email address which then will be used for solicitation.

SELECT customer_t.customer_id, customer_t.customer_email
FROM CUSTOMER_T, ORDER_LINE_T, ORDER_T, PRODUCT_T
WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID
AND ORDER_T.ORDER_ID = ORDER_LINE_T.ORDER_ID
AND ORDER_LINE_T.BAR_CODE = PRODUCT_T.BAR_CODE
AND ORDER_LINE_T.BAR_CODE = 36748;

Results:
8 fcummings@spamail.com
4 moskoorouchi@spamail.com

The fourth multi-table query is to find the most popular wines that we have sold and how many times each has sold. We count how many times each bar code has been used in our order line table. This does not give us the amount of bottles purchased, but gives us the count on how many times it has been ordered in separate transactions. We want this information in order to find out some sort of indication of what our customers prefer, therefore allowing us to make better supply decisions in the future.

SELECT order_line_t.bar_code, Count(order_line_t.bar_code)
from product_t, order_line_t
where product_t.bar_code = order_line_t.bar_code
group by order_line_t.bar_code;

Results:
12113 5
23445 3
36748 2
46247 3
53125 1
71347 2
83225 2

The fifth and final multi-table query is to find the name of the customer that placed order a specific order number. If we have some sort of problem with an order we will be able to quickly identify the information about the transaction and then take the necessary steps to resolve the situation. We take customer service seriously.

SELECT Customer_Name
FROM CUSTOMER_t
Where CUSTOMER_t.Customer_ID =
(SELECT ORDER_t.Customer_ID
From ORDER_t
Where order_ID = 1010);

Results:
Yi Sun

Description of Database Tables

Customer Zip table consists of attributes which are zip code, customer_city, and customer_state. Customer zip is the Primary key for this table. The reason why we made this table was because there are only one state and one city for one zip code, therefore; we do not have duplicate the data over and over again.

Customer table stores and shows important information about the customers. Customer table consists of attributes which are customer_ID, customer_name, customer_email, DOB, customer_address, and zip_code. We set up the customer_ID as number, because we only use number for the customer IDs, and we set the date of birth as DATE. Everything else is varchar. The primary key for this table is the customer_ID, and the foreign key is the Zip_Code. DOB has very important role to determine whether each of our customer can buy the wine legally, because we can not sell the wines to the customers who are under the age of 21.

Order table consists of attributes which are order_ID, order_date, and customer_ID. The primary key for this table is the Order_ID, and foreign key is the customer_ID. This foreign key is related to the primary key from the customer table.

Product table is the table which has important information about the wine. Product table consists of attributes which are bar_code, product_quantity, varietal, product_name, description, price, alcohol, appellation, and vintage. Each attribute shows and gives all important information regarding to the wines we sell. The primary key for this table is the bar_code. The other attribute, alcohol shows the percentage of alcohol content in a bottle. The primary key is very important because the each bar code is very unique, and each kind of wine has different bar code. This primary key is the foreign keys for other tables.

Order Line table is the table which has all information regarding to the wines. Order Line table consists of attributes which are order_line_ID, bar_code, order_Id, and order_quantity. The primary key for this table is the order_line_ID. And there are two foreign keys which are order_ID and bar_code. Order_ID is the foreign key 1 and bar_code is foreign key 2.

Product rating table is the table which shows the rating on the wines customers gave. The product rating table consists of attributes which are bar_code, and rating. Both attributes are set as number, so customers can put numbers to rate the wines. Bar_code is the primary key for this table, and bar_code is the foreign key for this table. The foreign key references to primary key of product table which is bar_code.

Supplier zip table consists of attributes which are zip_code, supplier_city, and supplier_state. Zip_code is the primary key for this table. This table basically shows suppliers information.
Supplier table were created to store important information on company’s suppliers. Supplier table consists of attributes which are supplier_ID, supplier_name, supplier_email, supplier_address, and zip_code. Supplier_ID is the primary key for this table, and zip_code is the foreign key which references to zip_code from the supplier table.

Shipment table is consists of attributes which are shipment_ID, shipment_date, and supplier_ID. Shipment_ID is the primary key for this table, and supplier_ID is the foreign key which references to supplier_ID from the supplier table. This table is the important to keep track on all important information regarding about the shipments. This will help company and consumers to get information. For example, we all will be able to know which product was send, when that product was send, and which supplier send that products.

Includes table consists of attributes which are bar_code and shipment_ID. Both attributes are set as numbers, because bar code is unique combination of numbers and can be identical, and shipment ID is ID so each shipment has different ID number. The primary key for this table is bar_code and shipment_ID. There are two foreign keys. Foreign key 1 is bar_code, which references to primary key of bar_code from the product rating table. Foreign key 2 is shipment_ID, which references to primary key of shipment_ID from the shipment table.

< Previous 2 1

Topics

Tech Business Computer Engineering Electrical Engineering Selected Topics

Wine Database System SQL Queries

Wine Database System
SQL Queries