6 replies to this topic

[Lokantis]

Hello, I am a total noobie at SQL and I still don't understand all the concepts. I want to see how to solve this first query and hopefully I'll be able to go from there.
I have this database:
Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal)

Employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEmail, SupEmpNo, EmpCommRate)

Product (ProdNo, ProdName, ProdMfg, ProdQOH, ProdPrice, ProdNextShipDate)

OrderTbl (OrdNo, OrdDate, CustNo, EmpNo, ShpName, ShpStreet, ShpCity, ShpState, ShpZip)

OrdProd (OrdNo, ProdNo, Qty)

2 answers: first one using 3 simple subqueries nested, second one with JOIN (using no subqueries).
List the product number, name, and price of those products with a price greater than $150 and were ordered in Jan 2008 by customers with balances greater than $400.

Thank you in advance.

[gaylo565]

You need to tell us what the structure of the db is (relationships between tables; im assuming that what you have listed is the table structure with the columns you would like returned.) If the relationships aren't set up as expected (every programmer sets them up a bit differently) than as soon as I used a join I would end up with erroneous results. If these are indeed your tables all you need to list is the primary and other keys. Unfortunately I have a heavy work load and can't guarantee that I will be able to stop back by and answer the rest of this but if I find some time I will try:) Hopefully someone else will help you out with this if you post your keys (also maybe label your tables, db's, columns, and rows as such so that we know what it is you are dealing with. Im also assuming that what you want is a simple SELECT query but if you need more than that you should specify.

[WingedPanther]

Lokantis said:

Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal)

Employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEmail, SupEmpNo, EmpCommRate)

Product (ProdNo, ProdName, ProdMfg, ProdQOH, ProdPrice, ProdNextShipDate)

OrderTbl (OrdNo, OrdDate, CustNo, EmpNo, ShpName, ShpStreet, ShpCity, ShpState, ShpZip)

OrdProd (OrdNo, ProdNo, Qty)

2 answers: first one using 3 simple subqueries nested, second one with JOIN (using no subqueries).
List the product number, name, and price of those products with a price greater than $150 and were ordered in Jan 2008 by customers with balances greater than $400.

Thank you in advance.

Based on the tables, I'm guessing OrderTbl is the parent table, with child tables OrdProd (on OrdNo), Customer (on CustNo) and Employee (on EmpNo). I'm further guessing that OrdProd has child Product (on ProdNo). If that's correct, creating the join should be fairly simple. I'm not certain why you would want to do subqueries for this unless you were dealing with a lousy database like Access.

Do you understand how to do a subquery on a select? Do you understand how to do a join?

[Lokantis]

I kind of understand how to do joins but I don't understand why or how it works exactly. I got this so far
SELECT DISTINCT ProdNo, ProdName, ProdPrice
FROM Product
JOIN Product ON OrdProd.ProdNo = Product.ProdNo
JOIN OrdProd ON OrderTbl.OrdNo = OrdProd.OrdNo
WHERE ProdPrice > '150' AND OrdDate = 'Jan 2008';
I know the syntax is probably wrong.

Also I forgot to specify the keys, they are the bolded words:
Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal)

Employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEmail, SupEmpNo, EmpCommRate)

Product (ProdNo, ProdName, ProdMfg, ProdQOH, ProdPrice, ProdNextShipDate)

OrderTbl (OrdNo, OrdDate, CustNo, EmpNo, ShpName, ShpStreet, ShpCity, ShpState, ShpZip)

OrdProd (OrdNo, ProdNo, Qty)

[WingedPanther]

You've got a good start. The idea behind a join is that you are creating a NEW temporary table.

If you do:
SELECT DISTINCT *
FROM Product
JOIN Product ON OrdProd.ProdNo = Product.ProdNo
JOIN OrdProd ON OrderTbl.OrdNo = OrdProd.OrdNo

You will get a column for EACH column in the original tables. Each record will consist of every possible match between the three original tables. Inner joins require matching data from both tables. Left outer only requires that a record exist for the first (left) table... the right table may not have a match resulting in null records for its result. Right outer is the reverse. Full outer means every record from either table will appear, with a match if possible.

[kresh7]

Ok i would suggest you to read everything on this page

http://www.w3schools.com/sql/default.asp

im not making any marketing for anything but this is the right page :D its not a forum or anything it only teachs you MySQL and a lot of cool stuff i've learned here the most i know about mysql

[WingedPanther]

+Rep to kresh for a great idea. I strongly recommend w3schools' SQL tutorial as well.