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
Related
Calculate
Lifetime Value of a Customer
Ecommerce Shopping Cart Software
Smart
Home Technology
Leapfrog
and Powertouch
How
to set up an Internet Radio Station
Pressure
Sensitive Label Waste
Topics
Tech
Business Computer
Engineering Electrical
Engineering Selected
Topics |