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

I am pretty new to a lot of this so someone please correct me if I’m wrong as I don’t want to give misinformation. My understanding is that SQL queries are instructions to already written C code. When writing code, there are almost always multiple good methods to accomplish the same task. Which one you choose depends on context. Data access from disk space is such an in-demand/specific enough set of tasks that developers wrote algorithms to calculate nearly every possible method to achieve those tasks so future developers don’t have to. Instead, future devs can “declare” what the tasks are. The algorithm then picks the best methods for achieving the task based on what the target (data) of the task is and runs them. The tasks vary so much in complexity that even declaring them eventually turns into its own language: SQL

Answer 2

As mentioned above, SQL is a declarative language, C is procedural. An SQL statement declares what has to be done, the compiler decides how. In a 'normal' language like C a function or method specifies what and how manipulations with data have to be executed. There are some tasks types that can't be solved using only SQL. There are tasks that can be much more easily implemented using 'normal' programming languages, for example recursion. Modern DBMS (Oracle, MS SQL, PostgreSQL and others support recursive queries, but such solutions are less intuitive and often hard to understand compared with implementations in 'normal' languages.

Answer 3

At the definition level, C is a procedural language and SQL is largely a declaritive language. You know about procedural languages—the programmer tells the computer what to do. Or to put it another way, the programmer defines a procedure that the computer follows to (hopefully) perform some sort of task.

A declarative language, in contrast, tells the computer what to produce. Rather than define a procedure, the programmer defines the result they are trying to achieve. The language itself has been pre-programmed to find the proper procedure to produce the defined result.

There are many declarative computer languages, of which SQL is probably the best known. In general, they are based on mathematics, which is a generalized declarative language. As others have pointed out, SQL is based on set theory. More specifically, it's based on the very powerful relational model defined by Edgar Codd. While there is plenty to be said about the theory behind SQL (and RDBMS in general), it comes down to the simple idea of using tables to define the relationships between data.

---

The one twist in the idea that SQL is a declarative language is that each statement has a procedural element as well. To illustrate:

SELECT x FROM table WHERE y = 0;

SELECT x is a procedure for picking out (or selecting) a subset. FROM table defines which relationships (or tables) to operate on. WHERE y = 0 is the declarative portion of the statement. It defines a subset of the data that will be operated upon.

Meanwhile C has some declarative features. For instance:

A = x*y;

In this case, you don't tell the computer how to multiply two numbers. Rather you ask the computer for a result and the C compiler defines the procedure to accomplish the task.

Answer 4

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.

Answer 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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.