Please help me understand SQL vs C like programming?

Question

Specifically I am trying to grasp how SQL statements differ from normal C style programming, I can't seem to understand how to do stuff like "for every userid in this table that has another column of data equal to such and such, run this stored procedure" which in programming would be a for loop, how the heck do you do stuff like that?

Or like, for each row in tableA that has a contentID of 11, add a new row to tableB containing this data and the userID from the row of tableA found containing contentID of 11...

Anyone mind possibly writing a bit on how I should understand SQL statements compared to programming? I feel like I could wield it better if I understood how I was suppose to think about it...

Answer 1

The trick is that SQL is best used for set operations, not loops. You would want to make the stored procedure be a function, then use an Apply operator to apply the function to the appropriate set of rows, producing a set of outputs of the function.

The APPLY operator is documented here: http://technet.microsoft.com/en-us/library/ms175156.aspx. Unfortunately, the example is too complex, but here's a sample of the use:

SELECT D.deptid, D.deptname, D.deptmgrid
    ,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

This produces a rowset of the columns from Departments, then calls the function for each row, passing the deptmgrid column. For each function call that returns a result, that result is added to the final result set.

Note that it's all sets - no loops.

Answer 2

SQL won't do all that for you. You'll have to use an something like T-SQL or PL/SQL or whatever your flavor of database has.

I found this to be an excellent way to get started in Oracle PL/SQL. Use a trigger to fire off your proc every time an INSERT or UPDATE occurs.

Answer 3

SQL can't do what you want, it is a language for getting data based on certain criteria such as records after a certain time or containing a certain text string. Databases have extentions to the SQL language that allow you to operate on results once there returned, for Oracle it's PL/SQL and for SQL server it's T-SQL.

Answer 4

SQL is a language to query a database for sets of data. Although you can use it for others operations, you should only use it for heavy operations that works of large chunks of related data. Despite its S (of standard) in it's name, it is not standard in all of it's features for distinct DBMSs. The implementation used for MS SQL Server is Transact SQL or T-SQL.

C is an all-purpose language. You can write in it programs for whatever you want, but you should not do with it what you can with SQL (although you can).

In fact, they are complimentary.

You can find a lot of information with a simple search in Google. You can begin reading the Wikipedia article about it or some tutorial.

Answer 5

They are approaching the world from different points of view. C is about performing actions. SQL is about storing data, and manipulating data. The only "actions" it is good at are pulling and changing data.

Think of all your data like a Venn diagram- SQL lets you "look" at any part of that diagram you want.

If you want to actually do something to that data, then in C, you might say "Go to every user and perform this action on them", as in

//if a customer is late, send them a reminder
for(int i=0;i<USER_COUNT-1;++i){
  if(LATE_ON_PAYMENTS=CustomerType(Customers[i])){
    SendReminder(Customers[i]);
  }  //if cust is late on their payments
}  //for ea customer

In SQL, you would be able to ASK for the list of users, as in:

SELECT *
FROM CUSTOMERS
WHERE LATE_FLAG = 'Y';

Or you could change data regarding those customers, as in:

UPDATE CUSTOMERS
SET TRUST_LEVEL = TRUST_LEVEL - 1  --trust a little less when they are late
WHERE LATE_FLAG = 'Y';

Note that this UPDATE could affect any number of rows, but there is no loop... you are simply saying "look up these records, and change them in this way".

But if you wanted to send them a reminder, well that's just too bad... you've got to use C or a stored procedure to do that.

You really get the best of both worlds when you combine a traditional language with SQL. If you can replace the earlier example in C with this (disclaimer: I know this is bogus code, it's just an example):

//if a customer is late, send them a reminder

//get all the late customers
sqlCommand = 'SELECT CUSTOMER_ID FROM CUSTOMERS WHERE LATE_FLAG = ''Y''';
dataSet = GetDataSet(sqlCommand);

//now loop through the late customers i just retrieved    
for(int i=0;i<dataSet.RecordCount - 1;++i){
  SendReminder(dataSet[i].Field('CUSTOMER_ID'));
}  //for ea customer

Now the code is more readable, and everyone is pointed at the same data source at runtime. You also avoid the potentially messy code in C that would have been involved in building your list of customers - now it is just a dataset.

Just as SQL sucks at doing imperative actions, C sucks at manipulating data sets. Used together, they can easily get data, manipulate it, and perform actions on it.

Answer 6

The main difference between SQL and languages like C is this:

In SQL, you specify what your results sets should look like -- this makes it very good for performing queries and set operations. Example:

"Give me all the bills with an amount > 10" => SQL figures out how to create the results set and gives it back to you,

In C, you specify what your program should do to retrieve the results set. Example: "

1. List item
2. Create a results list,
3. Get all the bills,
4. Take the topmost bill,
5. If amount > 10, put it in the results list, otherwise remove it,
6. If there are more bills, goto step 4,
7. Return the results set."

The question is then: "How do I formulate my question in SQL so that it does exactly what I want?", which is a bit more difficult.

Answer 7

First, in using SQL you want to avoid looping at all costs. Looping is bad in database terms.

You want to work with sets of data and affect all in one action.

"for every userid in this table that has another column of data equal to such and such, run this stored procedure" This would be a bad thing to do in SQl. In general what you want is to write a new procedure that is set-based. For instance, suppose your sp does a simple insert where @test and @test2 are input variables.

insert table1 (field1, field2)
values (@test, @test2)

To apply to a group of values it is better to put the group of values in a temp table or table variable (Or they may aand often are actually be values you can pull from an existing data table)

Your new insert now becomes something like

insert table1 (field1, field2)
Select field1, field2 from @temp

The reason why you want to do this is that set-based operations are way faster than row by row operations.

The first things you need to feel comfortable with in learning to use SQL are Joins, set-based operations, the insert statement, the update statment, the delate stament and the select statement. Make sure you understand how to effectively use joins in all the action statements as well as selects. Here is a link to start helping you understand joins http://www.tek-tips.com/faqs.cfm?fid=4785

You can go for years writing SQl without ever using a cursor or a loop.

To undestand the difference between a language like C and SQL is that basically SQL is a specialized type of language that is concerned with database operations. C is less concerned with accessing the data than with how the whole application will work. Therefore, since they have different purposes they approach things very differently. Very little of what you know from C applies to SQL. They are truly apples and oranges.

One thing application programmers like to moan about is how SQL is not object-oriented. Do not try to make it object-oriented or think of it it object-oriented terms. That's like putting lipstick on a pig. It doesn't accomplish anything and it annoys the pig (i.e. it makes the database perform less than optimally).

Answer 8

Let me take a crack at this. I'm taking the long road here, so bear with me.

Ultimately all programs, data, etc. on a computer are composed of the same stuff: ones and zeros. Nothing more, nothing less. So how does a computer know to treat one set of ones and zeros as an image and another set as an executable?

The answer is context. It's something that humans are terribly good at so it's no surprise that it's the underpinning of much of what a computer does. The mechanisms are complex but the end effect amounts to a computer that constantly switches perspective in order to do incredibly flexible things with an incredibly limited data set.

I bring this up because computer languages are similar. In the end, ALL computer languages end up as a series of op-codes ran through the processor. In other words, it's assembly language all the way down. All computer languages are assembly language, including any implementation of SQL.

The reason we bother is this: programming languages allow us to create a useful illusion of approaching problems from a new perspective. They give us a way to take a problem and re-frame the solution.

At the risk of being cliche, when we don't like the answer to a problem, a different programming language allows us to ask a different question.

So, when you approach a language, be it a query language or an object-oriented language or a procedural language, your first question needs to be, "What is this language's perspective? What's its outlook on the task of problem solving?" I'd go so far as to suggest that a language without a clear vision of itself is more trouble than it's worth.

With C, I would suggest that the perspective is this: "Even the lowest level operations of vastly different processors can be described in a simple, common language." C is designed to get in the driver's seat of any processor out there while still having the same old steering wheel, pedals, and dash.

So with C, you're doing everything. That's why it's referred to as a "high-level assembly language". Or, to quote a friend of mine, "C is the Latin of computer languages. Assembly language is the grunts of the apes in the trees."

SQL is an entirely different beast with an entirely different perspective... or is it? SQL's perspective is this: "Even the most complex commands of vastly different databases can be described in a simple, common language."

Sounds familiar, eh? SQL is designed to allow you to get into the driver's seat of any database software and have the same steering wheel, pedals, etc.

So in summary, C is a language used to give commonly-understood commands to any arbitrary CPU while SQL is a language used to give commonly-understood commands to any arbitrary database back-end.

Now, where do they cross paths? It's actually fairly simple.

What does a CPU do? It receives, transforms, and sends information. So if your goal is to interpret and present data or accept commands from an end-user, you're working in C. C is for the procedures that you need to automate through the computer.

What does a database do? It stores, groups and retrieves large sets of information. So if at any point your C program needs to store, group or retrieve a large data-set or subsets of a large data set, then chances are you'll be interacting with a database.

How? By having your C program send SQL commands to the database, of course. ;)

I hope this illuminates things somewhat because otherwise I'll just look like a pompous so-and-whatever for this long, rambling reply. :-P

Answer 9

In essence, SQL is a set based language. It operates on sets of data, and differs greatly from how an instruction based language works. In something like C, you specify the exact steps of how something should work. In a set based query language like SQL, you have to change you view a bit. You're not trying to accomplish an operation that can easily be described in simple steps, you're trying to build a result based on how sets of data are related.

Answer 10

SQL is a query language designed to work on sets of data. I've noticed some programmers making the mistake of retrieving a large result set from SQL and then looping over the set in C to filter the data. The optimal design would do as much set filtering in SQL letting the DB trim your set down to the smallest set of data you need to do your loops or other business logic. So basically the answer to your question is use SQL to get you the smallest data set then use C to manipulate the data set according to the business logic.

Answer 11

SQL operations are on SETS of data. So you can do things like this to operate on all the matching records.

UPDATE table1 SET x = x+1 WHERE y = 1

In order to perform a FOR EACH operation you need to use a looping mechanism. In SQL Server (Transact-SQL) The most common such loop is called a CURSOR and allows you to operate on one result row at a time. Here's an example. Note that cursors are horribly inefficient compared to set operations so use them with care.

Answer 12

Both are programming languages (both Turing complete depending on your exact SQL dialect), however...

C (C++, C#, Java, Visual Basic) are Procedural programming languages. You specify a sequence of steps for the computer to take. The computer doesn't understand the goal, it just does what you tell it. Think of this as a bottom up approach to programming.

SQL (Haskell, LISP when it feels like it) are Functional programming languages. You specify a goal, and the computer figures out the best sequence of events to reach it. Think of this as a top down approach to programming.

Both approaches have their pros and cons.

Functional languages suffer from the fact that it's hard to create a language that understands all types of a problems - if this was possible, all programming languages would be functional and we'd just describe what we need done. Instead most functional languages focus on a small problem set, like math, or in the case of SQL, reading and writing a relational dataset.

Procedural languages suffer from the fact that the programmer has to micromanage everything. While the compiler might take care of register assignment and other small optimizations, it can't do bigger things, like reorganizing your entire program to better suit the dataset, or automatically restructure it to run on multiple parallel CPUs.