New to SQL 1

[EPNICO]

I'm relative new to the world of SQL. I have experience in 3GL languages. What I need help is in understanding how should a programmer think when using SQL. In the 3GL languages the programmer is in control of the sequential reads, Indexing, etc.. In SQL its a bit different. Some people that I talk to tell me to think of it as a logical set. Can anybody give me some examples, ideas, etc...


Thanks for your help.

EPNICO

 

[tlbroadbent]

I hope you aren't put off by the length of this post. I'm never sure when to stop. X-)

Joe Celko, who has authored several SQL books, had this to say about SQL Programming.

"The single biggest challenge to learning SQL programming is unlearning procedural programming."

I also came from a background of many years of procedural programming, primarily COBOL. However, having majored in mathematics in college, I was able to grasp basic relational concepts relatively quickly. (Believe me, there are many concepts that still evade my grasp!) ;-)

I mention the mathematics because the relational model is mathematically based. The data manipulation languages are relational algebra and relational calculus. In addition, understanding sets and set theory is very helpful.

All this doesn't mean programmers with mostly procedural language experience can't learn SQL nor does it mean only math majors can succeed. However, the greater your understanding of these concepts, the easier the learning will be.

You asked for some examples. Here are a couple of simple examples of the differences between procedural languages and SQL.

Example 1:

In a 3GL language, when I wanted to decrease the prices in my product catalog by 10%, I would write a procedure that would loop through code. In each loop, the code would read a record, update the price and write the record. The developer must know all of the steps to perform this task.

In SQL, I write one statement.

UPDATE ProductCatalog SET ProdPrice = Price * .9

The database engine or program handles all the details. I simply update a relation or table. I can add criteria to select a subset of the relation to update such as WHERE Price > 159. I don’t have to know much except the relation and a handful of commands.

Example 2:

In a 3GL language, suppose we need to compare prices from a master catalog with the prices on the product catalog. We would write a similar but more complex looping program. We open the files, make sure they are sorted properly and read one record at a time from each. We must compare the records and report when no matches occur. Again, the programmer codes each step.

In SQL, I write one statement.

SELECT MstrID, MstrPrice, ProdPrice
FROM MstrCatalog LEFT JOIN ProdCatalog On MstrID=ProdID
WHERE ProdPrice Is Null
OR MstrPrice<>ProdPrice

With this statement SQL produces a list of no matches whether the price differs or the ProdCatalog table doesn’t contain a matching row.

Please realize that I am over simplifying. These are trivial examples, even in a 3GL environment. The “real world” is filled with incredible complexity. That’s part of the excitement.

My current favorite book for SQL is “The Guru’s Guide to Transact-SQL” by Ken Henderson. He covers the basics very well and provides some excellent advanced topic coverage.

If you want some more good info on SQL, check out the FAQs in this forum. There are some links that direct you to other resources for learning SQL.

You might want to do a brief study of the relational model. Check the following links. They may or may not be the best available but they do provide basic info. If you search the web for “relational database” you’ll find hundreds, perhaps thousands, of sites dealing with the topic.

Relational model overview by Dublin City University.I

http://www.compapp.dcu.ie/databases/f037.html

Fundamentals of Relational Database Design by Paul Litwin

http://www.microsoft.com/TechNet/Access/technote/ac101.asp

Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[EPNICO]

Thank you for your help. Is there much difference in Transact-sql then just plain sql?

 

[tlbroadbent]

According to Microsoft, &quot;SQL Server uses the superset of ANSI SQL-92 known as T-SQL. T-SQL conforms to the SQL-92 standard defined by ANSI.&quot;

In other words, T-SQL should contain all of the SQL-92 standard syntax plus extensions. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost