Hey guys, I am been coming here as a guest recently to view some SQL and Database code. It seems like a nice forum that I’d like to come to regularly. I have been having some problems with some problems I have been trying to complete. I believe A-Y are correct but I really need help with the z-dd. I assume I have to CREATE VIEW to beable to pull these queries off?
Database Tables and fields I have to work with:
[B]book[/B](isbn, title, publisher,year_published,category,price,on_hand,order_point,active)
[B]
book_order[/B](user_name, order_date, order_time, ship_to_first_name, ship_to_last_name, ship_to_address, ship_to_city, ship_to_state, ship_to_zipcode, credit_card_type, credit_card_number, credit_card_expr_date, subtotal, shipping_handling, total)
[B]category[/B](category)
[B]publisher[/B](publisher)
[B]state[/B](state_code, state_name)
[B]author[/B](first_name, last_name)
[B]cc_type[/B](cc_type)
[B]administrator[/B](user_name, pin, first_name, last_name, address, city, state, zipcode, date_hired)
[B]admin_phone[/B](user_name, phone_number)
[B]book_author[/B](isbn, first_name,last_name)
[B]contains[/B](user_name, order_date, order_time, isbn, quantity_ordered, price_at_purchase)
[B]restock_order[/B](user_name, isbn, order_number, quantity_ordered)
QUERIES TO COMPLETE:
a. Select all publishers
b. Select all states
c. Find the count of customers by user name
d. Find customer by user name and pin
e. Insert a customer
f. Update a customer
g. Find administrator by user name and pin
h. Find phones by administrator user name
i. Insert administrator
j. Update an administrator
k. Delete phones of an administrator by user name
l. Insert phone
m. Find the count of books by ISBN
n. Find books where on hand is less than order point
o. Find books by keyword in title, category, and active
p. Find books by author, category, and active
q. Find book by ISBN
r. Insert a book
s. Update a book
t. Insert a book order
u. Insert an order item
v. Insert an author
w. Find book authors by isbn
x. Delete book authors by isbn
y. Insert restock order
z. Find the total sales by category for a given month
aa. Find the count of buyers by category for a given month
bb. Find average number of books per order for a given month
cc. Find average number of customers per day for a given month
dd. Find average sales per customer for a given month
So far I believe I got a-y but please correct me if I am wrong. If anyone can let me with z-dd that would be nice. Thanks!
A.) SELECT *
FROM PUBLISHER;
----------------------------------------------------------
B.) SELECT *
FROM STATE;
----------------------------------------------------------
C.) SELECT count(user_name) AS num_of_cust
FROM CUSTOMER;
----------------------------------------------------------
D.) SELECT user_name, pin, first_name, last_name, address, city, state, zipcod, credit_card_type, credit_card_number, credit_card_expr_date
FROM CUSTOMER
WHERE user_name = '?' AND pin = ?;
----------------------------------------------------------
E.) INSERT INTO CUSTOMER (user_name, pin, first_name, last_name, address, city, state, zipcod, credit_card_type, credit_card_number, credit_card_expr_date)
values ('?', ?, '?', '?', '?', '?', '?', ?, '?', '?', '?');
----------------------------------------------------------
F.) UPDATE CUSTOMER
SET user_name = '?', pin = ?, first_name = '?', last_name = '?', address = '?', city = '?', state = '?', zipcode = ?, credit_card_type = '?', credit_card_number = ?,
credit_card_expr_date = '?' ;
----------------------------------------------------------
G.) SELECT user_name, pin, first_name, last_name, address, city, state, zipcode, date_hired
FROM ADMINISTRATOR
WHERE user_name = '?' AND pin = ? ;
----------------------------------------------------------
H.) SELECT phone_number
FROM ADMIN_PHONE
WHERE user_name = '?' ;
----------------------------------------------------------
I.) INSERT INTO ADMINISTRATOR (user_name, pin, first_name, last_name, address, city, state, zipcode, date_hired)
values ('?', ?, '?', '?', '?', '?', '?', ?, '?');
----------------------------------------------------------
J.) UPDATE ADMINISTRATOR
SET user_name = '?', pin = ?, first_name = '?', last_name = '?', address = '?', city = '?', state = '?', zipcode = ?, date_hired = '?';
----------------------------------------------------------
K.) DELETE FROM ADMIN_PHONE
WHERE user_name = '?';
----------------------------------------------------------
L.) INSERT INTO ADMIN_PHONE (user_name, phone_number) values ('?', ?);
----------------------------------------------------------
M.) SELECT count(ISBN) AS num_of_books
FROM BOOK;
----------------------------------------------------------
N.) SELECT ISBN, title, publisher, year_published, category, price, on_hand, order_point, active
FROM BOOK
WHERE on_hand < order_point ;
----------------------------------------------------------
O.) SELECT ISBN, title, publisher, year_published, category, price, on_hand, order_point, active
FROM BOOK
WHERE (title LIKE '%?%') OR (category LIKE '%?%') OR (active LIKE '%?%') ;
----------------------------------------------------------
P.) SELECT ISBN, title, publisher, year_published, category, price, on_hand, order_point, active
FROM BOOK
WHERE author = '?' AND category = '?' AND active = '?' ;
----------------------------------------------------------
Q.) SELECT ISBN, title, publisher, year_published, category, price, on_hand, order_point, active
FROM BOOK
WHERE ISBN = ? ;
----------------------------------------------------------
R.) INSERT INTO BOOK (ISBN, title, publisher, year_published, category, price, on_hand, order_point, active)
values (?, '?', '?', ?, '?', ?, ?, '?', '?') ;
----------------------------------------------------------
S.) UPDATE BOOK
SET ISBN = ?, title = '?', publisher = '?', year_published = ?, category = '?', price = ?, on_hand = ?, order_point = '?', active = '?' ;
----------------------------------------------------------
T.) INSERT INTO BOOK_ORDER (user_name, order_date, order_time, ship_to_first_name, ship_to_last_name, ship_to_address, ship_to_city, ship_to_state, ship_to_zipcode, credit_card_type,
credit_card_number, credit_card_expr_date, subtotal, shipping_handling, total)
values ('?', '?', '?', '?', '?', '?', '?', '?', ?, '?', ?, '?', ?, ?, ?) ;
----------------------------------------------------------
U.) INSERT INTO RESTOCK_ORDER (user_name, ISBN, order_number, quantity_ordered,)
values ('?', ?, ?, ?) ;
----------------------------------------------------------
V.) INSERT INTO AUTHOR (first_name, last_name)
values ('?', '?');
----------------------------------------------------------
W.) SELECT first_name, last_name
FROM BOOK_AUTHOR
WHERE ISBN = ? ;
----------------------------------------------------------
X.) DELETE FROM BOOK_AUTHOR
WHERE ISBN = ? ;
----------------------------------------------------------
Y.) INTER INTO RESTOCK_ORDER (user_name, ISBN, order_number, quantity_ordered)
values ('?', ?, ?, ?) ;
----------------------------------------------------------
Z.)
NOTE: I am using “?” to denote user input.
