Querying multiple tables 1

[peeryowen]

I have a basic grasp of SQL and now need to apply it to more complex examples with multiple tables.

Problem 1 is I am not sure I fully understand the client request, I think I do but I am not sure. I know you can't really help me here but I figure once you have done this a while, many client requests resemble each other.

Problem 2, is my query correct, I am at home and cannot test it right now.

Plumbing company wants to manage its contracts for heating and water heaters.

Tables
Clients(CliNum, CliLastName, CliFirstName, CliAddress)
Contracts(ContNum, CliNum*, AppCat)
Intervention_Contract(IntContNum, ContNum*, IntContDate, IntContduration)

I need to write a query to fetch a list of interventions, their duration, concerning a specific appliance category X.

SELECT Intervention_Contract.IntComNum, Intervention_Contract.IntContDuration, Contracts.AppCat
WHERE Intervention_Contract.ContNum=Contracts.ContNum

For a specific appliance catergory, we'll say X, do I then put it in parentheses?

Like
Contracts.AppCat(X)

Thanks for any help.

PS, I know I can create a few databases locally to practice my queries but I find it helpful to use data that I don't know well. I tend to make simple tables and simple data for my examples, but when querying more complex databases I find it is more helpful to use data you did not create and are not as familiar with. Does anyone know of a website with a couple of databases already generated where i can practice more complex queries across multiple tables?

 

[r937]

your basic grasp of SQL seems to have overlooked the FROM clause ;-)

SELECT Intervention_Contract.IntComNum
     , Intervention_Contract.IntContDuration
  FROM Contracts
INNER
  JOIN Intervention_Contract
    ON Intervention_Contract.ContNum = Contracts.ContNum
 WHERE Contracts.AppCat = X

notice that the FROM clause includes an INNER JOIN, with the join conditions specified in the ON clause

there is no need to put Contracts.AppCat into the SELECT clause because you aleady know what it's going to be

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[peeryowen]

I was so careful to get the unimportant details right that I overlooked that one. Select, From, Where, ingrained in my head.

Before I came back to look here, I looked again, something looked wrong, but it didn't pop off the page then. Thanks again.

I understand your query, but wouldn't this one work too? I translated the original one from French, but the example below is the one in French sorry, I think it will be easy to follow, instead of the JOIN I used . . . .

SELECT

INTERVENTION_CONTRAT.NoIntCont,

INTERVENTION_CONTRAT.Duree

FROM

INTERVENTION_CONTRAT, CONTRATS

WHERE

CONTRATS.NoCont = INTERVENTION_CONTRAT.NoContrat AND

CONTRATS.CatApp = 'X';

My employer is letting me train to do DB programming with the eventual end of getting more into decisional infrmation systems and maybe data mining etc. These are little exercises to train, but I guess I have a long way to go. Seems simpler than other programming I have done, but I have to stop trying to complicate it.

Thanks again.

 

[r937]

you should tyry not to write the old-style implicit joins, with a comma-delimited list of tables in the FROM clause and the actual join conditions in the WHERE clause

instead, always write using explicit JOIN syntax

the query will be much easier to understand, and may even run faster

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[towerbase]

You could try the Northwind sample database which has 14 tables. Go to

http://www.sqltutorial.org/sqlsampledatabase.aspx

Another tip is to persuade your employer to buy you Simply SQL by Rudy Limeback. It's the best and easiest to read introduction to SQL that I've come across. The time saved following the advice in Simply SQL will easily justify the cost of the book.

Andrew
Hampshire, UK

 

[r937]

thanks for the very kind words about my book, andrew

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[peeryowen]

Well since I work in a French company they might buy a book in French but certainly not in English, even though SQL is well in English. Once I get a little deeper into SQL and can figure out if it is what i want to do, I will look into the book. At this point I am just trying to learn more advanced queries etc. Once I am more comfortable with that I will look at the book, it sounds good though.

Thanks for the database link, it is exactly what I was looking for, although I did find others to practice with.

I did find lots of resources saying the using the . connectors or the JOIN clause is about the same thing. I am curious why Rudy suggested JOIN is better. From what I found online it looks like JOIN is better if you are joining many many tables but for me to practice joining 2-3 tables, it might no make a big difference. I guess if JOIN is what is expected then I should learn it, but I also have the experience of learning programming in a university and then finding much of what we were told was in fact incorrect and not a best practice. I want to avoid that in the future, I felt like I had to relearn everything.

Thanks again for the help.

 

[r937]

there is no difference in execution between implicit joins and JOIN syntax, provided you are only ever doing inner joins

there is a huge difference if you try to do outer joins

and of course JOIN syntax is always easier to read and understand

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon