# getName
SELECT c_fname,c_lname FROM customer WHERE c_id = ?;

# getBook
SELECT * FROM item,author WHERE item.i_a_id = author.a_id AND i_id = ?;

# getCustomer
SELECT * FROM customer, address, country WHERE customer.c_addr_id = address.addr_id AND address.addr_co_id = country.co_id AND customer.c_uname = ?;

# doSubjectSearch
SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_subject = ? ORDER BY item.i_title limit 50;

# doTitleSearch
-- SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND substring(soundex(item.i_title),0,4)=substring(soundex(?),0,4) ORDER BY item.i_title limit 50;
-- SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND substring(soundex(item.i_title),1,4)=substring(soundex(?),1,4) ORDER BY item.i_title limit 50;
-- SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_title LIKE ? ORDER BY item.i_title limit 50;

# doAuthorSearch
-- SELECT * FROM author, item WHERE substring(soundex(author.a_lname),0,4)=substring(soundex(?),0,4) AND item.i_a_id = author.a_id ORDER BY item.i_title limit 50;
-- SELECT * FROM author, item WHERE substring(soundex(author.a_lname),1,4)=substring(soundex(?),1,4) AND item.i_a_id = author.a_id ORDER BY item.i_title limit 50;
-- SELECT * FROM author, item WHERE author.a_lname LIKE ? AND item.i_a_id = author.a_id ORDER BY item.i_title limit 50;

# getNewProducts
SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE item.i_a_id = author.a_id AND item.i_subject = ? ORDER BY item.i_pub_date DESC,item.i_title limit 50;

# getBestSellers
SELECT i_id, i_title, a_fname, a_lname FROM item, author, order_line WHERE item.i_id = order_line.ol_i_id AND item.i_a_id = author.a_id AND order_line.ol_o_id > (SELECT MAX(o_id)-3333 FROM orders) AND item.i_subject = ? GROUP BY i_id, i_title, a_fname, a_lname ORDER BY SUM(ol_qty) DESC limit 50;

# getRelated
SELECT J.i_id,J.i_thumbnail from item I, item J where (I.i_related1 = J.i_id or I.i_related2 = J.i_id or I.i_related3 = J.i_id or I.i_related4 = J.i_id or I.i_related5 = J.i_id) and I.i_id = ?;

# adminUpdate
UPDATE item SET i_cost = ?, i_image = ?, i_thumbnail = ?, i_pub_date = CURRENT_DATE WHERE i_id = ?;
# adminUpdate.related
SELECT ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND NOT (order_line.ol_i_id = ?) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT MAX(o_id)-10000 FROM orders) AND order_line.ol_i_id = ?) GROUP BY ol_i_id ORDER BY SUM(ol_qty) DESC limit 5;
# adminUpdate.related1
UPDATE item SET i_related1 = ?, i_related2 = ?, i_related3 = ?, i_related4 = ?, i_related5 = ? WHERE i_id = ?;

# getUserName
SELECT c_uname FROM customer WHERE c_id = ?;

# getPassword
SELECT c_passwd FROM customer WHERE c_uname = ?;

# getRelated1
SELECT i_related1 FROM item where i_id = ?;

# getMostRecentOrder.id
SELECT o_id FROM customer, orders WHERE customer.c_id = orders.o_c_id AND c_uname = ? ORDER BY o_date, orders.o_id DESC limit 1;

# getMostRecentOrder.order
SELECT orders.*, customer.*, cc_xacts.cx_type, ship.addr_street1 AS ship_addr_street1, ship.addr_street2 AS ship_addr_street2, ship.addr_state AS ship_addr_state, ship.addr_zip AS ship_addr_zip, ship_co.co_name AS ship_co_name, bill.addr_street1 AS bill_addr_street1, bill.addr_street2 AS bill_addr_street2, bill.addr_state AS bill_addr_state, bill.addr_zip AS bill_addr_zip, bill_co.co_name AS bill_co_name FROM customer, orders, cc_xacts, address AS ship, country AS ship_co, address AS bill, country AS bill_co WHERE orders.o_id = ? AND cx_o_id = orders.o_id AND customer.c_id = orders.o_c_id AND orders.o_bill_addr_id = bill.addr_id AND bill.addr_co_id = bill_co.co_id AND orders.o_ship_addr_id = ship.addr_id AND ship.addr_co_id = ship_co.co_id AND orders.o_c_id = customer.c_id;
# getMostRecentOrder.lines
SELECT * FROM order_line, item WHERE ol_o_id = ? AND ol_i_id = i_id;

# createEmptyCart
SELECT COUNT(*) FROM shopping_cart;
# createEmptyCart.insert
-- INSERT into shopping_cart (sc_id, sc_time) VALUES ((SELECT COUNT(*) FROM shopping_cart), CURRENT_TIMESTAMP);
# createEmptyCart.insert.v2
INSERT into shopping_cart (sc_id, sc_time) VALUES (?, CURRENT_TIMESTAMP);

# addItem
SELECT scl_qty FROM shopping_cart_line WHERE scl_sc_id = ? AND scl_i_id = ?;
# addItem.update
UPDATE shopping_cart_line SET scl_qty = ? WHERE scl_sc_id = ? AND scl_i_id = ?;
# addItem.put
INSERT into shopping_cart_line (scl_sc_id, scl_qty, scl_i_id) VALUES (?,?,?);

# refreshCArt.remove
DELETE FROM shopping_cart_line WHERE scl_sc_id = ? AND scl_i_id = ?;
# refreshCart.update
UPDATE shopping_cart_line SET scl_qty = ? WHERE scl_sc_id = ? AND scl_i_id = ?;

# addRandomItemToCartIfNecessary
SELECT COUNT(*) from shopping_cart_line where scl_sc_id = ?;

# resetCartTime
UPDATE shopping_cart SET sc_time = CURRENT_TIMESTAMP WHERE sc_id = ?;

# getCart
SELECT * FROM shopping_cart_line, item WHERE scl_i_id = item.i_id AND scl_sc_id = ?;

# refreshSession
-- UPDATE customer SET c_login = NOW(), c_expiration = (CURRENT_TIMESTAMP + INTERVAL 2 HOUR) WHERE c_id = ?;

# createNewCustomer
INSERT into customer (c_id, c_uname, c_passwd, c_fname, c_lname, c_addr_id, c_phone, c_email, c_since, c_last_login, c_login, c_expiration, c_discount, c_balance, c_ytd_pmt, c_birthdate, c_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
# createNewCustomer.maxId
SELECT max(c_id) FROM customer;

# getCDiscount
SELECT c_discount FROM customer WHERE customer.c_id = ?;

# getCAddrId
SELECT c_addr_id FROM customer WHERE customer.c_id = ?;

# getCAddr
SELECT c_addr_id FROM customer WHERE customer.c_id = ?;

# enterCCXact
-- INSERT into cc_xacts (cx_o_id, cx_type, cx_num, cx_name, cx_expire, cx_xact_amt, cx_xact_date, cx_co_id) VALUES (?, ?, ?, ?, ?, ?, CURRENT_DATE, (SELECT co_id FROM address, country WHERE addr_id = ? AND addr_co_id = co_id));

# clearCart
DELETE FROM shopping_cart_line WHERE scl_sc_id = ?;

# enterAddress.id
SELECT co_id FROM country WHERE co_name = ?;
# enterAddress.match
SELECT addr_id FROM address WHERE addr_street1 = ? AND addr_street2 = ? AND addr_city = ? AND addr_state = ? AND addr_zip = ? AND addr_co_id = ?;
# enterAddress.insert
INSERT into address (addr_id, addr_street1, addr_street2, addr_city, addr_state, addr_zip, addr_co_id) VALUES (?, ?, ?, ?, ?, ?, ?);
# enterAddress.maxId
SELECT max(addr_id) FROM address;

# enterOrder.insert
-- INSERT into orders (o_id, o_c_id, o_date, o_sub_total, o_tax, o_total, o_ship_type, o_ship_date, o_bill_addr_id, o_ship_addr_id, o_status) VALUES (?, ?, CURRENT_DATE, ?, 8.25, ?, ?, CURRENT_DATE + INTERVAL ? DAY, ?, ?, 'Pending');
# enterOrder.maxId
SELECT count(o_id) FROM orders;

# addOrderLine
INSERT into order_line (ol_id, ol_o_id, ol_i_id, ol_qty, ol_discount, ol_comments) VALUES (?, ?, ?, ?, ?, ?);

# getStock
SELECT i_stock FROM item WHERE i_id = ?;

# setStock
UPDATE item SET i_stock = ? WHERE i_id = ?;

# verifyDBConsistency.custId
SELECT c_id FROM customer;
# verifyDBConsistency.itemId
SELECT i_id FROM item;
# verifyDBConsistency.addrId
SELECT addr_id FROM address;