database tables question

[king03]

hi guys I really need some help I don't quite understand the instructions in our homework, I uploaded it below for your reference.

Please explain to me how to do the things required on the instructions, it's not clear to me at all. Thank you for those who will help.

[rch1231]

If I understand correctly you are supposed to write the sql statements that will SELECT the records from the tables shown that meet the requirements listed.

For example the second instruction is to list products rented by both customers 1003 and 1101. I normally break this down into a couple of tasks. First I would find the transactions for the date range from TRXN where the customer IDs match. From there find the Products purchased by both where the TRXN id is in the list above. Then use those product IDs to list the products.

These are complex queries that use sub-queries to generate a subset that you select from later. I suggest that you talk with others in the class or go over your notes closly if the abve does not make sense.

[seslie]

Hello king03,

In addition to rch1231's response,
The table contains some data that you arent showing here. You are to majorly perform a 'select' query, whose syntax you should be familiar with. for e.g

SELECT [column_name]
 FROM [table_name]
 WHERE (column_name = condition);

For the first question,
List 'Sold' transactions for customer 1003 for September with the Rental transactions for customer 1011 for October. Display Customer Name and product description/movie title with transaction type and month.

You'd be getting this information from different tables; CUSTOMERS, PRODUCT, MOVIE and TRXN.

This tells you that you'd need a form of connection between theses, which has already been established. Something like this should give you a good idea on how to go about it.

Customer  Trxn_Type  Trxn Date        Product Desc   Movie Title
Rancid     Sold      sept something    Very good      Avengers
Sola       Rental    Oct something     Also good      Dark Knight


Select ...
from  customerTable c, trxn t, movie m, product p
where (c.cust_code = 1003
      and c.cust_code = t.cust_code
      and t.trxn_type = 'SOLD' 
      and p.movie_id = m.movie_id)

or    (c.cust_code = 1011
      and c.cust_code = t.cust_code
      and t.trxn_type = 'RENTAL'
      and p.movie_id = m.movie_id);

I'm not sure this is very efficient though, Just to give you an idea.