RSS

SQL questions

22 Aug

SQL is a way to turn raw data into knowledge. SQL is a language in which you can ask a database a question.  If everything goes well, the engine responds with useful information.  Consider this question more technically known as a query:

select * from PendingOrders

Piece of cake.  That query translates to the question, “What are all my currently pending orders?”  PendingOrders is the table name and “*” is the columns in that table you want to see, * means give me everything. So you ask a simple question and the database spews back 700 records in less than a second.  You then realize this isn’t quite knowledge yet its just a subset of raw data.

select OrderID, CustomerID, SubmitDate from PendingOrders where SubmitDate > 20100101

OrderID CustomerID SubmitDate
000212 000201 20100120
000112 000201 20100211
000092 000219 20100320

You now get a results grid like this so you quickly flip over to the Customer table to id 201 and see Stewbeef is CustomerID 201.  You flip back to your other query data then flip over to the Orders table and find Stewbeef did actually order the silverware he is complaining about it just never showed up on the website because of some bug.  To this I say there are no stupid questions, only stupid people. You could have done a join and seen all these results in one grid. http://www.w3schools.com/sql/sql_join.asp

It appears your data is somewhat normalized, basically meaning you have more than 1 table that holds everything.  Normalization is required for modern systems to function properly and for the data to maintain some kind of integrity.  Unfortunately is makes things harder for everyone developers, dbas and ad-hoc users.

select customer.lastname, orders.orderinfo, PendingOrders.submitdate
from customers
inner join orders on customer.customerID=orders.customerID
inner join PendingOrders.submitdate on PendingOrders.OrderID=orders.OrderID
where PendingOrders.submitdate > 20100101 and
where customer.lastname like '%stewbeef%'

SQL is really easy until you get to joins.  In addition to joins there are many mathematical and date functions that will help you get your answers in a better form than raw data.

Advertisements
 
Leave a comment

Posted by on August 22, 2010 in SQL Dev

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: