Fog Creek Software
Discussion Board




best way to learn SQL?

I graduate in May and a friend of mine is going to try to help me get a job w/ his company (which is nice since 6 out of 33 Dec graduates in cs at my school got job offers). The thing is, I need to learn SQL and preferably .Net, javascript and/or vbscript. Anyways, what is the best way to go about learning SQL? The computers I have at my disposal are a pc (1 GHz athlon w/ 512 ram, running WinXP Pro) and a laptop (1.2GHz w/ 256 mb ram running WinXP Home). Thanks to school connections (MS license for the cs department = burn as much MS onto cd's as you have cd's for) I have access to all MS software. What's the best way to go about learning sql? I'm lookin at the w3school's website, but are there any books you would recommend? Any software? Thanks for your time.

Tim
Thursday, January 02, 2003

Do you mean the actual SQL language, or MS SQL Server?

Matthew Lock
Thursday, January 02, 2003

If you're on a budget and need to learn a variety of topics over the next few months, I heartily recommend O'Reilly's Safari service at safari.oreilly.com. For $10 a month you get 5 points that can be applied to get the full contents of technical books online. For the most part 1 book = 1 point, and the selection is not limited to O'Reilly titles. You can change titles out once a month.

I can't recommend any specific books on the topics you're interested in, but one general piece of advice I'd offer is this: Pick a project that you find personally interesting that would take advantage of all these technologies, and then go build it. You'll learn more from doing real-world problem-solving, IMHO, than simply walking through prefab exercises in books. A natural project given the specific areas you want to learn would be to build a database-driven Web site.

John C.
Thursday, January 02, 2003

SQL for Dummies is actually not bad as an introduction.  SQL for Mere Mortals (I think that's it, in any case the SQL companion to Databases for Mere Mortals) is highly regarded as an introduction, though I've never read it.

Using the visual Query Design View in MS Access can be a good way to see how things work, especially if your mind works visually.  You can toggle back and forth between the visual representatiion and the SQL that the design generates.  Or you can write the SQL yourself and let Access generate the visual representation.  Access/Jet SQL has a few peculiarities, but you won't likely run across those until you get to more advanced queries. 

Herbert Sitz
Thursday, January 02, 2003

One way to go at it is to use MS access where you can build queries with a graphical interface or with SQL, and switch back and forth between to two views.
Access SQL isnt the easiest to decipher with lots of redundant [] and stuff. But it can interpret regular sql well enough so its a good interface for practising.

But imho, there is no point in learning SQL (beyond the basics anyway) before you are confident with relational data modelling.

Eric DeBois
Thursday, January 02, 2003

To Matt: I am wanting to learn the SQL language, although my friend's emloyere is a 95% ms based shop.
To Herb: Thanks for the book recommendations.
To Eric: Any book (or website) recommendations for relational databases?

Thanks guys.

Tim
Thursday, January 02, 2003

Take a look at SQL for Web Nerds by Philip Greenspun at http://philip.greenspun.com/sql/

It uses Oracle as its database, but the SQL part is generic.

Also, there is A Gentle Introduction to SQL at http://sqlzoo.net/

jeff
Thursday, January 02, 2003

Leanring basic SQL will take you as long as leanring HTML. About a day. If you know what a database is and how it is structured you shouldn't have a problem. I am surprised you have a CS and don't know SQL; it is a simple tool...do you know what grep it?

For stored procedures PLSQL or TransactSQL is in order but you can use google for that.

  
Thursday, January 02, 2003

SQL is very easy, as it was designed to be a language for business people. like someone else said, it should take about a day. that said, it is worth your time to actually take a day to learn it well enough so that you can use it properly. i have been repeatedly horrified by the "recent CS grad" who thinks it is appropriate to only learn "SELECT * FROM FOO" and then use loops in the calling language to pull the desired information out of the result set. the greenspun reference mentioned is ok, and joe celko's "SQL for Smarties" is pretty good for the concepts and code, but not written very well.

soda
Thursday, January 02, 2003

Sorry, but saying "SQL is easy you can learn it in a day" reminds me of the thread currently going where web programmers are complaining that people think they're just web page designers and their 12 year olds can give some tips. 

Well, I'm a database programmer and I get tired of people saying, "Oh, yeah, I know SQL."  (No offense, Soda, I have no idea whether you're one of these people or not.  But I do know there are a lot of them out there.)

Yes, it is simple to learn the basics of the SELECT query.  But there are way too many little things you've got to pick up and pay attention to for SQL to be a quick learn.  Difference between inner and outer joins is a biggie that you likely won't get the feel for until you've worked with things a bit.  Likewise dealing with NULLs and how they can foul things up.  Issues with queries that are updateable vs. non-updateable.  Optimizing queries.  How to deal with parameters in your queries.  Then start doing grouping and problems get multiplied.  I could go on, and we haven't even gotten to stored procedures and related stuff.

One big problem with people who jump into SQL and think it's easy is that their queries often are wrong.  That is, they write a query that seems to return the data they want and they think they've got it.  But in fact their result set includes some records that weren't really wanted or some records are left out that the programmer actuall wanted to include.    And the "SQL is easy" programmer never notices.  It's often easy to write something that gets you close.  But to be confident you're getting exactly the right result set in a more complicated query is not something you can pick up that quickly.

Also, Celko's SQL for Smarties book would be a miserable introduction to SQL.  (A clue: the subtitle of the book is:  Advanced SQL Programming.)  It's not a systematic treatment of SQL at all.  Per Celko himself, "[SQL for Smarties] is for the working SQL programmer who wants to pick up some advanced programming tips and techniques.  IT ASSUMES THAT THE READER IS AN SQL PROGRAMMER WITH A YEAR OF ACTUAL EXPERIENCE." (from the Introduction) 

What's needed for the original poster in this thread are some introductory books and some playing around with data and queries.  SQL for Dummies really isn't for dummies at all.  Someone remarked that some reading on database design is also necessary.  I agree.  SQL for Dummies has a decent introduction to normalization issues, what normalization is and why it's necessary.    There's also a pretty good whitepaper on normalization on MSDN someone.  I'll try to post the link if I can find it.

Herbert Sitz
Friday, January 03, 2003

The best way to learn SQL, or any computer language, is the same as the way to learn a human language - immerse yourself in it. Do nothing for a week but practice SQL queries.

Install MySQL on your machine and build a database directly through the command line using just SQL. Populate the database and practice doing queries on it.

Once you have mastered the subset of SQL that MySQL supports install PostgreSQL and learn about views and subselects etc.

Matthew Lock
Friday, January 03, 2003

<quote>
SQL for Mere Mortals [..] is highly regarded as an introduction, though I've never read it.
</quote>

I did read SQL for Mere Mortals, but would not recommend it.

The problem is that it only covers SELECT queries (and doing a fine job at that). There's not one single example of inserting or updating data. My guess is that it is targeted at managers not programmers.

Jan Derk
Friday, January 03, 2003

Herbert talks a lot of sense.

I would also advise that before trying to do too much with the language you make sure that you understand the relational algebra underlying it.

I've seen several people with excellent SQL language knowledge beating their brains out over "wrong" results because they are failing to consider the basic facts about sets and set operations, or are imposing baseless presuppositions about the order in which things will happen.

Don't think it will be too hard or too mathematical - if I can understand it, it can't be.

An excellent distance-learning course on this is run by the UK's Open University, but you don't have that sort of time.

As far as books go, surely C.J.Date "An Introduction to Database Systems" is a good grounding in all the essentials.

Mathematical Dunce
Friday, January 03, 2003

Herbert Sitz,

Nice post!!!!!!!!!!!!

Sometimes it seems as if all the online forums are filled with newbies trying to advise other newbies.

Just about everything related to software development seems easy until you try it yourself and then your work gets scrutinized by other developers.


Friday, January 03, 2003

SQL is perhaps the best investment of ANY ONE skill you can learn. I started using SQL more than 10 years ago. I still use it today. Virtually EVERY database system in the world uses SQL. I used SQL on TONS of systems. In fact, in the last 10 years, it is the ONLY language I have learned that I still use. (everthing else I have had learn over and over)

Learn SQL today, and you will still use it 20 years from now.  Every single business application today will use sql. It is that simple.

Now, as for those who state SQL is easy?

Ok, lets try two my interview questions on you:

I have a table of car salesman. I also have a child table that lists all the cars that each salesman sold. Hence, we have a classic and standard one to many relation between the salesman, and the table of cars that they sold. Ok, with this classic parent to child relation, write me a SQL query that shows each sales man that sold a blue and a red car? Thus, I want you to list the salesman name, and the color,make, model of the cars they sold.
Thus:

tblSalesMan
ID, SalesRepName

tblCars
Sales_id, Color, Make, Model, SalesDate


I will be nice, and REPEAT THE question (since 9 out of 10 people get this wrong):

I want a sales man that sold red * A N D *  a blue car. (they can have sold lots of cars of each color, THIS IS NOT A TRICK question).

Note of course the above question is classic, since it is same type of question like: what customers had a invoice in the last two months? Or what customer bought a product in each of the last two years? This is extremely common question.

Anyway…anyone want to post a easy solution?

Problem #2
Give me a list of salesman and the last car they sold (SalesRep, color, make, model, salesdate). Thus, I only want a nice listing of the salesman, and the make, model, color etc of the LAST CAR that they sold.

Again, a VERY COMMON question (ie: when was the last trip, last purchase, last doctor visit…again VERY VERY common).

Ok, folks, the two simplest and everyday questions

Lets have all of those who said SQL is real easy post me an answer to the above incredible EVERY DAY questions that virtually every busines I know asks?

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Friday, January 03, 2003

I believe the MSDN whitepaper on normalization that Herbert mentioned can be found at http://msdn.microsoft.com/library/en-us/dnacc2k2/html/odc_FMSNormalization.asp

It says it "applies to Microsoft Access", but the advice is relevant to most any database.

ODN
Friday, January 03, 2003

Thanks a lot for the posts guys. Feel free to keep posting. I'm currently reading the "SQL for web nerds" site to get a basic understanding. I'll probably also figure out what class at ISU teaches SQL (there is one but I took a precursor to it before they modified the program) and look at any lecture notes that are online.

Anyways, thanks again.

Tim
Friday, January 03, 2003

Albert, there is a small problem with your data model.

You need to have a salesman's ID in the tblCars as a foreign key.

#1.

Select  t1.ID, t3.SalesRepName
  from  tblcars as t1., tblcars as t2
  where t1.ID = t2.ID
  and t1.ID = t3.ID
  and t1.Color = "red"
  and t2.Color = "blue"
  group by t1.ID;

tapiwa
Friday, January 03, 2003

try "The Practical SQL Handbook"

apw
Friday, January 03, 2003

Tapiwa -- You need to specify the t3 table, of course.  But I like that solution.  Clever.  I think I usually do that sort of thing with subselects; I like your solution better.

Herbert Sitz
Friday, January 03, 2003

I would probably do I like this, but I bet there is something wrong with this approach...

SELECT tblSalesman.Sales_rep_Name
FROM tblCars AS t1, tblCars AS t2
INNER JOIN tblSalesman ON t1.ID = tblSalesman.ID
WHERE t1.color = "RED" AND t2.color = "BLUE"
GROUP BY tblSalesman.Sales_rep_Name;

Eric DeBois
Friday, January 03, 2003

Eric - Yes, you only have one join for three tables.  You could do it like that, but you'd need to have two INNER JOINS in the FROM clause (and I think there'll be a syntax error in what you've got now because the join of t2 references t1 and tblSalesman, not t2 at all).  Once you do that it's going to be not quite as concise as tapiwa's version, though maybe more explicit about what's going on.

Herbert Sitz
Friday, January 03, 2003

i would do it this way:
select repname from tblsalesman
join tblcars on tblsalesman.id = tblcars.sales_id
where repname in
(select repname from tblsalesman
join tblcars on tblsalesman.ID = tblcars.sales_id
where tblcars.color = 'Red') and
tblcars.color = 'Blue'

i guess it's not elegant, but it works.

nathan
Friday, January 03, 2003

Herbert, I stand by my claim that SQL is easy. I no longer do much database programming, but at one point in my life I was the database guy, and regretfully have about 5 years of Oracle experience.  I was responsible for training new recruits at a defunct startup, and we took people from zero through outer/inner joins, subselects, grouping, aggregates, transactions, trees, and intro PL/SQL in about five days. The fact is you can learn inserts, deletes, transactions, selects, subselects, inner/outer joins in about 1 day, and that is enough to do 80% of what most people ever need to do.

I recommended Celko's book because every intro book I have seen is 10% useful and 90% garbage.  I don't like the Celko book because it is poorly written, but there isn't any other book with the same content. You can learn the easy stuff with no book and the Celko book is really the only other thing out there. If you are using Oracle (which didn't seem to be the case in the original posting), most of the Oracle press books are reasonably good.

soda
Friday, January 03, 2003

Soda -- That's fair.  I still stand by what I said, though.  Go ahead and lead some guys through SQL in a quick crash course.  They'll even understand it and see how it works. 

Then set them loose on their own with a real-world database.  They'll be able to do simple queries, sure.  But I wouldn't trust them to get the correct results on anything very complicated.  They're going to have to suffer through a learning curve.  Not a terribly steep one or long one, as far as programming-related topics go.  But steep and long enough that I think it's very misleading to say "SQL is easy you can learn it in a day."

And if they're trying to integrate SQL into programs they're going to have to deal with lots more issues.  How to embed SQL in their programs.  Assembly of SQL strings, proper quoting or nonquoting of variables, etc.  Yes, you can show them this stuff in a day, but it's going to take longer and they're going to have to suffer through frustration and mistakes before they become proficient, and more importantly, before they can reliably create complex queries.

On a more general level, I think the number of programming-related things you can learn in a day is vastly overestimated.  You can pick just about any topic and find someone giving a 1 to 5 day training seminar on it.  Can you say someone who attends that seminar "knows" the subject?  In almost every case I'd say, "No."  You only learn by doing it on your own and suffering through your own frustration and mistakes.  Sure an introduction and overview are helpful, but that's all they are.  (And don't get me going on how much money I think is wasted on training seminars, in the computer field and elsewhere.)

Herbert Sitz
Friday, January 03, 2003

"You can pick just about any topic and find someone giving a 1 to 5 day training seminar on it.  Can you say someone who attends that seminar "knows" the subject?"

Unfortunately, in my experience, the person who attended the seminar "knows" the subject much better than most people who claim they "know" the subject on their resume...

soda
Friday, January 03, 2003

Here's a variation on tapiwa's solution:

Prob #1

SELECT
t1.Sales_Id,
t3.SalesRepName,
t1.Color,
t1.Make,
t1.Model,
t1.SalesDate
FROM
tblCars as t1, tblCars as t2, tblSalesMan as t3
WHERE t1.Sales_Id = t2.Sales_Id
AND t1.Sales_Id = t3.ID
AND t1.Color = "Red"
AND t2.Color = "Blue"
GROUP BY
t1.Sales_Id,
t3.SalesRepName,
t1.Color,
t1.Make,
t1.Model,
t1.SalesDate

UNION SELECT
t2.Sales_Id,
t3.SalesRepName,
t2.Color,
t2.Make,
t2.Model,
t2.SalesDate
FROM
tblCars as t1, tblCars as t2, tblSalesMan as t3
WHERE t1.Sales_Id = t2.Sales_Id
AND t1.Sales_Id = t3.ID
AND t1.Color = "Red"
AND t2.Color = "Blue"
GROUP BY
t2.Sales_Id,
t3.SalesRepName,
t2.Color,
t2.Make,
t2.Model,
t2.SalesDate
ORDER BY SalesRepName


Prob #2

SELECT
tblSalesMan.ID,
tblSalesMan.SalesRepName,
tblCars.Color,
tblCars.Make,
tblCars.Model,
MAX(tblCars.SalesDate) AS LastSale
FROM tblSalesMan INNER JOIN tblCars ON tblSalesMan.ID=tblCars.Sales_Id
GROUP BY
tblSalesMan.ID,
tblSalesMan.SalesRepName,
tblCars.Color,
tblCars.Make,
tblCars.Model
HAVING
MAX(tblCars.SalesDate) = (SELECT MAX(tblCars.SalesDate) AS LastSale FROM tblCars WHERE tblCars.Sales_Id = tblSalesMan.ID)
ORDER BY Max(tblCars.SalesDate) DESC

Tim Lara
Friday, January 03, 2003

>
Sorry, but saying "SQL is easy you can learn it in a day" reminds me of the thread currently going where web programmers are complaining that people think they're just web page designers and their 12 year olds can give some tips. 
<

I said "Leanring basic SQL" is simple. Stored Procedures in PLSQL, ProC, ProCobol, TransactSQL, or any database API is difficult and I know that. Learning a database IMO is like learning an operating system.

   
Friday, January 03, 2003

Problem 2:
Surely it is last "cars" not "car" because the
last two cars could have the same sale date?

Curious
Saturday, January 04, 2003

>>Problem 2:
Surely it is last "cars" not "car" because the
last two cars could have the same sale date?

Excellent obseraton. In fact any SQL ace will also notice that I don't have a pk for the child table. That means that I cannot really use a top 1 conditon of "date" to return only one record (which most sub-query conditions need).

The basics of SQL are not too hard. However, because SQL is generally a non procedural language, thus getting data out of tables into the way your want can present many challenges.

The fact that SQL is non procedural is both its strength, and its weakness.

Many people coming from a clipper or older type database systems do find that quite a large mind set has to change to become effective with SQL.

In fact, it is OFTEN easier to simply sit down and write code to solve a data problem (that is what most clipper folks always did anyway). I also find the same applies to people working with the Pick database system. Writing some code to find the last car sold can take LONGER via code as compared to SQL, but is actually EASIER from a brain cramp point of view! Programmers tend to think in procedures.

Anyway, it was good see some people take a crack at those two simple problems. There are MANY solutions.

For the 2nd problem (when last car was sold) the following works well. The reason why I prefer the following solution is that additional fields can be added, or removed from the query with ease (this means that I can include, or add additional columns from either table with out worry).  I also don’t use any aggregate functions to return field values. This is important for future maintenance, and simple re-usability. I don’t want to have to use some max function on a set of fields, since all kinds of problems occur when trying to return lots of child fields (I purposely said return the color AND date etc because a simple max function will get the last date of sale. That is too easy, and in the real world that is not useful (what is useful is a nice table that can be sent to word to print mailing labels, or to excel). Thus I do believe that conditions in SQL that use aggregate functions like max is ok, but returning the values for use via aggregate functions is not useful at all!

Thus, we really want something that returns a nice “table list” that can easily be sent to Excel, or to some report. After all, in the real world, we likely to be looking at a existing application, and almost for sure looking at a existing query. Chances are that the query and report already exist, but we want to change the criteria. So, I am REALLY looking for a solution that easily applies to EXISTING queries.

Thus, like code…SQL is very much a “incremental” process. You work and massage the SQL towards your final goal. You work and massage code towards your final goal.

That means that the solution should work on existing joins. In other words, you are likely to be starting out with a basic SQL datasheet that is a join already. 

I mean, you do copy, and paste and re-use existing queries right?

Ok, I think point is clear hear.

So, ok. With the above in mind, the first thing I want is a nice table listing of the sales man and the cars they sold. So lets write our basic every day join and ignore the “last car sold” problem for later (I have very very small brain, and thus really can only work with a  small task at a time!!).
:

Lets write our join:

  SELECT SalesMan, Color, Make, Model, SalesDate
  FROM tblSalesMan
  INNER JOIN tblCarsSold ON tblSalesMan.ID =  tblCarsSold.Sales_id;

The above is real simple, and real basic. The result looks like:

  SalesMan      Color  Make  Model    SalesDate
  Albert Kallal  Red    Ford  Mustang  01/03/03
  Albert Kallal  Red    Ford  Winstar  01/03/02
  David          Red    GM    Corvette 01/03/03
  David          Blue  GM    LaSaber  01/03/02
  Bob            Blue  Dodge  MiniVar  01/03/03
  Bob            Blue  Ford  Mustang  01/03/02

Now, that we have a our nice list to work with, lets restrict this list to only the LAST car sold. We do this by simply adding a where clause:

That where clause is:
  where SalesDate =
  (select max(salesDate) from tblCarsSold
  where sales_id = tblSalesMan.id )

Gee, this whole things seems easier when we break it into steps..don’t it!!

Thus, our whole SQL is now is the first part + 2nd part (this also means that the this solution works very well for IN-LINE SQL code. (because again we want to be able to ADD or modify the solution down the road..or later on in the code!!).

Ok, the two together:

  SELECT SalesMan, Color, Make, Model, SalesDate
  FROM tblSalesMan
  INNER JOIN tblCarsSold ON tblSalesMan.ID = tblCarsSold.Sales_id
  where SalesDate =
  (select top 1 salesDate from tblCarsSold
  where sales_id = tblSalesMan.id
  order by SalesDate desc);

The resulting table is:

  SalesMan      Color  Make  Model    SalesDate
  Albert Kallal  Red    Ford  Mustang  01/03/03
  David          Red    GM    Corvette 01/03/03
  Bob            Blue  Dodge  MiniVar    01/03/03

The other nice thing about the above approach is that we can change the inner join to a left join. We will often want to list all salesman, and even those who have NOT sold a car would now appear in the list if we used a left join (and a is null for date). As also mentioned, the above solution allows us to apply the “where” clause to any given EXISTING SQL join we have. This is most likely to be most of the queries you work with.


The above solution does of course assume that sub-queries are allowed. Most database engines out there do allow this. (JET and ms-sql for example do). MySql does not yet..but it is coming soon (gee, hope real darn soon!).

As for the first solution. Boy, that one is wide open, and I get really weird answers all the time!!

Anyway, again lets start with the original basic join statement. (hey, I can cut an paste the original!)

If you ever have watched a real talented developer, you instantly notice how they cut and paste more code. These top notch developers do this, because they ALWAYS THINK in terms of re-usable solutions. The first few lines of code they type takes longer, but by the end of the day they have cut and pasted twice as much code as the guy sitting down the hall.

Ok, so, I am going to whack the page/up key a few times and CUT AND PASTE my original SQL again. (I am saving time, or your are saving me money since I don’t really want to pay you again to write a query from scratch).

Ok, here is query:

  SELECT SalesMan, Color, Make, Model, SalesDate
  FROM tblSalesMan
  INNER JOIN tblCarsSold ON tblSalesMan.ID =  tblCarsSold.Sales_id;

Ok, so now we need to add a where condition (remember, again..same issues as before, we want to be able to add, or remove conditions over time, and most important add to exiting queries.

  WHERE
  tblSalesMan.ID In
  (select sales_id from tblCarsSold where Color = "Red" and sales_id = tblSalesMan.id)

That gets us the red cars. Now add the blue:

And

  (tblSalesMan.ID) In
  (select sales_id from tblCarsSold where Color = "Blue"  and sales_id = tblSalesMan.id)


The above conditions are a bit long. You can also consider changing the “in” command to “=” and use “top 1”. Either way, most of the time the above select does get optimized by the query engine. However, while are a bit long, they can be added to our existing queries.

Thus, if the boss now asks for red, blue and green car we can simply cut and paste the last condition, and change “Blue” to green. In other words, we can easily extend and add to the above conditions, BUT NOT modify the original query. Again, this is a obvious and important concept. May be we even want to throw in the last car condition into this solution here! In other words, I can combine my two examples into a 3rd with all the conditions.  And I can do this whole 3rd condition via cut and paste (see I how I am now REDUCING time for additional solutions!!).


Even more important is that we need a solution that works well via a GUI that is attached to code. I can take the above where condition and place it into a loop that concatenates that condition over and over several times grabbing a value from a multi-select listbox Take look at the following report screens:

  http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
.
You can see in the above that examples that I believe very strongly in supplying a GUI prompt screen for reports. I do not in any way believe that my end users should have to know, or even ever see SQL. It is a huge myth that end users need to build SQL.

Users of that application have not asked for a new reports in about 2 years now. That means I have nailed the requirements right on.  It also means that a few prompt screens for reports with the right options keeps a customer VERY happy!

Other different solutions for the blue/red car given to me for this are:

SELECT SalesMan, sum(Color = "Red") as RedCount , sum(Color = "Blue") as BlueCount
FROM tblSalesMan INNER JOIN tblCarsSold ON tblSalesMan.ID = tblCarsSold.Sales_id
group by salesman
where RedCount < 0 and BlueCount < 0

Of course many times, the were clause can NOT used on fields that are a condition, so in the above the RedCount < 0 would have to be replaced with the "whole" actual sum condition all over again. Of course the other problem with the above solution is that again aggregate functions are used, and you don’t wind up with a nice table listing that will no doubt get sent to a report writer or Excel. (in other words..it is of little use in a real application envornment).

So, at end of the day, I actually agree that SQL is not really that hard.

However, for a team of developers you do want to adopt some approach and standards as to how solutions are to be attacked. The end result is a lot more SQL and code that is extensible will be written. If the developers start writing more code..they also feel a lot better too.


Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Saturday, January 04, 2003


The solution is to have an expert DBA in house, let the developers crank whatever SQL they want, but the DBA do the final reviews and approvals.

I agree that anybody can do queries and stuff with SQL, but it takes a long road to become a DBA.

Leonardo Herrera
Saturday, January 04, 2003

No offense Albert, but your table definitions are terrible.

If I ever had an interview question like that (and it was not specified that I could/was expected to add new tables or columns; ie that this is not the normal way you define tables internally) - I would assume that the company in question had little clue about db design and it would be unlikely I'd accept a position there.

Also, we give out similar problems with a complete structure, and I can tell you that someone who solved problem #2 using a sub-select would be red-flagged, because so often a sub-select is going to be so much slower than (for example) an inner join.

Not sure if you were trying to impress me but...
Sunday, January 05, 2003

Most of the DB vendors have limited time demos for NT or Windows 2000.  They can be downloaded on a T1 or DSL speed link in a few hours at worst and some are available in the certification study guides (MS was).  Down load the database install it and you have an environment to test in.

John McQuilling
Sunday, January 05, 2003

In case anyone cares, I ended up running MS SQL Server 2k on my laptop and a copy of IIS on my pc. I'm experimenting w/ asp and sql separately to get my feet wet and hope to hook the two together soon. Thanks for the recommendations/advice.

Tim
Sunday, January 05, 2003

If you want to learn MYSQL and PHP you might also want to check out this walk through tutorial:
http://www.webmasterbase.com/article.php?aid=228&pid=0

Jan Derk
Sunday, January 05, 2003

Tim -- That sounds like a good way to go.  I'd still suggest hooking up Access as a front end to your SQL Server database, so you can have the advantage of visual query design along with pure SQL text.  Just set up an ODBC DSN for your SQL Server database, and create links in an Access database to its tables through the DSN.

I'm sure you'll find or have already found that SQL is easy to pick up and nothing to be afraid of, even if you won't become a guru in a day.

In addition to SQL, I assume, you're going to want to learn how to program a database application.  That'll take a little longer, but combining SQL and your previous programming experience will give you a good start. 

Herbert Sitz
Monday, January 06, 2003

Not sure if Anyone else mentioned this, but in my opinion, you don't really "Learn" SQL, you just kinda do it, and get better at it and start thinking in an SQL mindset.  The concepts and basic functions are very straightforward, its using them in the right combination, knowing a few tricks here and there etc. 

Vincent Marquez
Monday, January 06, 2003

I really like the book by Ben Forta, "SQL in 10 minutes" in the SAMS Teach Yourself series (ISBN 0-672-31664-1).  I don't really see where the 10 minutes comes in, myself -- if you read each of the 20 chapters carefully and work through the examples, each chapter takes longer than 10 minutes for sure.

But, it is defintely concise and well-written, only 159 pages total not counting the appendices.  I refer to it over and over (and I've been working with, and designing, 40+ table schemas for 2 years now).  Covers almost everything, including basic selects, filtering and grouping, calculated fields, inner and outer joins, views, even (briefly) transactions, triggers, stored procedures, etc.

I loan it to all new employees around here who never worked with SQL before, they all like it too.

Biotech coder
Monday, January 06, 2003

>>Not sure if you were trying to impress me but...
wrote:
>>No offense Albert, but your table definitions are terrible.

None taken!! (but I wish you would have posted your answers!). After all, this is a everyday simple sql question..right?

The table defs are kept to a absolute min and as simple as possible for the example. However, I am always open to suggestions. What would you use? (I purposely left out a PK in the child table for several reasons, hence, the child table only has a key value relating to the parent salesman).

There is no question I want the person to point out that the child table has no PK..but it in no way prevents a solution to the problem.

Several of the answers get me an ID, but do not result in a nice table that can be sent to excel, or word. We really are looking for a nice useable result here.

My comments about additional columns do not only mean that additional columns may be added over time, but in fact you might even be dealing with a view. (my point here is some try to use aggregate functions to return several values. If all of a sudden we need more columns then  aggregate functions can become a huge mess). Anyway, I am not sure why it should dawn on anyone that over time new fields might get added?  duh?

But, as always, I am open to suggestions. Thus, post a nice elegant solution. As mentioned, this is a VERY common problem. There are many solutions.

So,
What kind of join would you use to solve the problem?

And how would you re-name the fields in the example? (or re-structure the data?)

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Tuesday, January 07, 2003

<albert>(I purposely left out a PK in the child table for several reasons, hence, the child table only has a key value relating to the parent salesman). </albert>

I find this statement troubling. While you may have reasons to leave out a PK, I always teach my students to have a PK as a rule and not as an exception.

Two main reasons..
1. Optimise. Most rdbms will auto-index the PK. Newbies especially will not normally think of indexing/optimising.

2. rdbms is by definition just that. Not a glorified spreadsheet. When you normalise your data sufficiently, you will find PKs coming naturally. You talk of code reuse, yet produce tables with no PKs. They really are a mission to work with, especially with complex queries.

You do point out that the table could actually be a view. Even here, I maintain the same policy. Where possible, I try and have a unique identifier (aka primary key) when I create views.

<albert>Several of the answers get me an ID, but do not result in a nice table that can be sent to excel, or word. We really are looking for a nice useable result here.</albert>

If we return to my original solution, you begin with the second table/view
tblCars:
Sales_id(PK), ID (FK), Color, Make, Model, SalesDate

The query gives you a table with
ID, SalesRepName

Very simple query. Very simple result. Should you decide to use this view/result to join with
tblSalesRepAddress or tblSalesRepBonus or .... you can do it, even when you have two reps called "John Smith"

tapiwa
Tuesday, January 07, 2003

>>If we return to my original solution, you begin with the second table/view
tblCars:
Sales_id(PK), ID (FK), Color, Make, Model, SalesDate

In my example Sales_id is the FK!  (is is not called id, but sales_id for that reason!!).

As writen I have:

Sales_id(FK), Color, Make, Model, SalesDate

In none of my examples here is the PK of the child table used is it? (and that is why I left it out). However, you do get a bonus for pointing that out. I do accept that that child table MOST CERTAINLY SHOULD HAVE a PK. I would be NUTS to argue we don't every want PK here!! But for this example…it don’t need it.  I as mentioned (more than once!!) that I made a effort to leave it out on purpose!!

However, again, I think it quite easy to agree that our final lists should show things like car, color, make etc.

As for the results…yes, if we send them into a cursor or another table, you can then do another join and produce my list with your solution. But it does requite another step.  that requires another sql statement here!!

It also assumes that we have a cursors, or we are willing manage a temp table for the solution (that means we now have to involve a temp table naming scheme). You also have to decide what to do with the temp table after you are done. Lots of issues here. There is certainly nothing wrong with your approach, and managing a temp table or a cursor is a fine solution. (and one of many).

Albert D. Kallal
Tuesday, January 07, 2003

If you're asking: I find the naming the most worrisome.

The biggest problem being the Sales_ID field. You obviously meant SalesManID but from the name several will conclude that it is the primary key of the tblCars table. Some posters in this forum topic were confused.

You should also make up your mind if you want to give your tables singular or plural names. You name one tblSalesMan (singular) and the other tblCars (plural).

It is generally a good idea to give fields in a database a clear descriptive and unique name. You do it once: SalesRepName but in the same table you have a field named ID. That's as descriptive as a variable named x in source code. And why do you say SalesRepName instead of SalesManName? After all you named the table SalesMan and not SalesRep.

Then you sometimes connect some words with an underscore (Sales_id) and others without it (SalesDate).

Create a standard (preferably a company wide one) and stick to it. There's more, but you got the idea. It's seem to me that you just have not given the subject sufficient thought.

The importance of consistent, descriptive naming of variables, method names, database tables and fields is way too often underestimated. McConnell has written some interesting stuff about this in Code Complete.

Jan Derk
Tuesday, January 07, 2003

The real rule for naming conventions since the dawn of time is to use something that works. There is nor real cut and dry rule.

However, the one rule is to make some rules and stick to them.

So, lets take a few points here:

>>Then you sometimes connect some words with an underscore (Sales_id)

Yes, the only time I allow under scores is when using a foreign key. No other fields allow this. The advantage is that you can ALWAYS know that the field you are dealing with is a foreign key. What the heck do you use to solve this problem Hum? Tell me your great solution so that  a developer looking at the fields can instantly know they are looking at a FK? Hum..? I rather think my approach here is brilliant.

>>It is generally a good idea to give fields in a database a clear descriptive and unique name. You do it once: SalesRepName but in the same table you have a field named ID.

Using ID in every single table for the primary key is a time honored practice. If I have 200 tables, I WILL NEVER HAVE TO GUESS,  OR LOOKUP WHAT THE PRIMARY KEY is. In my case it is always ID. You can use whatever naming convention you want, but to say that ID is a poor field name when it is used in EVERY SINGLE table in a consistent manor?. Sorry, that is a load of BS to me. That is fine practice for me. If you think of something better, then post it. If a person just using ID for some arbitrary thing like a Car license number..then yes..it is REALLY poor. However, to consistently use the same field name for every table PK is great a practice. I use “ID" for this. Gee, what a sad day if you really do think that is poor practice.

Heck, to each to his own! However, to say that using ID for a PK in all tables is bad? Hum, ok..I guess some of you people like to grasp at straws here looking for flaw!. Gee, you mean the developers should be forced come up with a different PK name for every table? You mean they now have to guess what the PK is, or look it up all the time? My approach you always know the PK.

>>>Create a standard (preferably a company wide one) and stick to it. There's more, but you got the idea. It's seem to me that you just have not given the subject sufficient thought.

Yes..I can certainly agree with that!!

>>You should also make up your mind if you want to give your tables singular or plural names. You name one tblSalesMan (singular) and the other tblCars (plural).

Ah, gee, plural or not? This one will not keep me awake!!. Should I laugh,or cry? Ok, that is about your only fair point, but we sure are really getting small again!

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Tuesday, January 07, 2003

Gee, reading my post today, I don’t like the tone of my last post above very much.

Jan Derk:
Please accept my apologies. You made a good suggestion, and I barked back at you like a dog who had his tail stepped on.

I don’t like barking at people. I simply should have explained my naming conventions (for better, or worse) and moved on. So, while I did jump to explain my conventions, I also barked at you.

Again, there was no reason for that.

I am sorry. That is not my style....


Sincerely,

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Wednesday, January 08, 2003

Wow this is some funny stuff!

I think people like Albert are funny because you can't tell if there really is a method to their madness or if they are just arbitrarily coming up with expectations of others and excuses for themselves.

I bet that if Albert were reading what he said but written by someone else, he would have a whole slew of criticisms to heap onto the writer.

E.g., I love how Albert's weird SQL can be explained away with a few "simple rules".

Brian
Saturday, August 28, 2004

*  Recent Topics

*  Fog Creek Home