SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Thread: SQL Queries

  1. #1
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation SQL Queries

    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:
    Code:
    book(isbn, title, publisher,year_published,category,price,on_hand,order_point,active)
    
    
    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)
    
    category(category)
    
    publisher(publisher)
    
    state(state_code, state_name)
    
    author(first_name, last_name)
    
    cc_type(cc_type)
    
    administrator(user_name, pin, first_name, last_name, address, city, state, zipcode, date_hired)
    
    admin_phone(user_name, phone_number)
    
    book_author(isbn, first_name,last_name)
    
    contains(user_name, order_date, order_time, isbn, quantity_ordered, price_at_purchase)
    
    restock_order(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!

    Code:
    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 '&#37;?%') 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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry, we won't do your homework for you

    do you have a specific question about any one of these queries?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's actually not homework. I have been trying to set up some SQL queries to use on a database I have been putting together of books and ISBNs. For the last couple I was hoping to create a view to see the results. However, I am not exactly sure how to join the tables together. I gave it a quick shot for Z and got

    Code:
    CREATE VIEW SALES_BY_CATEGORY AS
    		SELECT	category, sum(total)
    		FROM	CATEGORY AS c, BOOK_ORDER AS b
    		WHERE	
    		GROUP BY  CATEGORY;
    But since total and category are on seperate tables I am not sure how to set up the rest. Any suggestions? Also I am not worried about the given for month clause I can deal with that later.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Bubs View Post
    But since total and category are on seperate tables I am not sure how to set up the rest. Any suggestions?
    yup... take an SQL tutorial, there are dozens of good ones on teh interwebs, make sure you cover how to write an INNER JOIN

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What is the connection between the 'CATEGORIE' and the 'BOOK_ORDER' table?

  6. #6
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is no connection between 'category' & 'book_order' tables. Thanks for the hint Rudy, I have some knowledge about INNER JOINS but yet they are still hard to setup.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    so for Z you will have to join book_order to book, and you won't need the categories table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see that book has the category field but am still unaware how to link the two tables since there is no fk referencing back to the book table. And there's no way to tell what book is in the book_order table unless I have to JOIN to the contains table aswell?

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Bubs View Post
    I see that book has the category field but am still unaware how to link the two tables since there is no fk referencing back to the book table. And there's no way to tell what book is in the book_order table unless I have to JOIN to the contains table aswell?
    Exactly

    Btw, you don't have an order number?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    why does there have to be an order number?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As of right now I don't have an order number.. unless I actually need it to do these queries which I don't believe I do? Also I'm trying to set this up so I would do a join between book_order & contains linking by user_name, and then an INNER JOIN between contains isbn & book isbn? If anyone wouldn't mind of kinda setting it up to help me out a little more that would be great as well That way I have a little more to work with.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    why don't you set it up? it's not that hard, and you did say you have some knowledge of INNER JOINs

    as i said earlier, we're not going to do your homework for you, but we'll help you do it

    but the key part is that you have to do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    why does there have to be an order number?
    Quote Originally Posted by Bubs View Post
    As of right now I don't have an order number.. unless I actually need it to do these queries which I don't believe I do?
    There doesn't HAVE to be an order number. It's just that usually these systems DO have them. When I order something, they usually give me an ordernumber that I can refer to if I want to have info about my order. Instead of giving them my username, order date and order time...

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    It's just that usually these systems DO have them.
    this isn't an order system, this is a homework assignment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess a developer can't start off with a template around here?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    here's your template:
    Code:
    SELECT t1.columns
         , t2.columns
         , t3.columns
      FROM table1 AS t1
    INNER
      JOIN table2 AS t2
        ON t2.column = t1.column
    INNER
      JOIN table3 AS t3
        ON t3.column = t2.column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •