|
||
|
SQL Programming : A short introThis section is a short intro on SQL programming, mostly set up for newbies, and with a few useful links if you are new to SQL.While my site contains mostly themes related to database design, without SQL programming, the database itself would be of little or no use. If you visit my ref cursor page, you will find a lesson on how to include your code in the database itself; by far the most efficient and safe way of approaching the matter. Before I continue, I would like to give you a little background: There have been disputes for many years about the pronounciation of the letters SQL: Should it be pronounced S-Q-L or sequel? The word sequel stands for Structured English QUEry Language, after an IBM project way back in time. Later, during the definition process of relational databases and a standard language for them, the term came to be Structured Query Language, SQL. However, a product like Microsoft SQL Server is pronounced 'sequel Server'. Choose what you like. I prefer SQL; Or, perhaps a more precise acronym that you will find further down.. Here are a few basics on SQL programming: We can divide the SQL language into two sections: DDL: Data Definition Language. Used to create and maintain database objects such as tables, indexes, constraints and any other elements that together form the database structure for a given system. DML: Data Manipulation Language. Used to create and maintain the data we want in our database tables. Luckily, in the DML section, we face only four statements to learn: INSERT: To add new row(s) in a table. Most likely, the SELECT statement is the most used of these. Notice one thing, though; According to the above, SQL is much more than a query language: It's actually a Structured Query, Insert, Update and Delete Language - so perhaps we should rename it to SQIUDL ? Just joking, but it's a fact that it has (amongst other features) all those characteristics. The most important feature of SQL programming, though, is that it is set oriented. What does that mean? It means that one statement can work on many rows in one operation. A very big difference from traditional, procedural languages. Consider this example: UPDATE EMPLOYEE Every employee in the EMPLOYEE table get a 10% pay rise. Very nice. Another example: DELETE FROM EMPLOYEE; All employees are removed... Not so nice. You can reduce the damage by using restriction, or conditions: DELETE FROM EMPLOYEE Now, only the employees of department no. 10 are removed. The management department, perhaps? As you can tell, SQL programming is very powerful, and as you can see, you can create very impressive things, and some not-so impressive, in just a small sentence. However, it is difficult to use conditional programming in SQL (IF/THEN/ELSE). Because of this, the definition and use of ref cursors have emerged. T his is a mechanism that allows you to step through a result set one row at a time. For a broader description on how to define it, you can read my ref cursor page. As for how you can use a ref cursor from a procedural language, read more on my using an Oracle ref cursor in Java page. If you are completely new to SQL programming, here is a starter at w3schools. You can learn more about the CIA factbook by doing some SQL programming here :-). Much more resources are found at SQL.org |
Exclusive interviews with:
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.
Read more
Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together." Mike, USA |
Theory & Practice DB Normalization Analysis Phase Database Keys DB Glossary Appl.Architecture Oracle DBA MySQL DBA SQL Server DBA Install Oracle Install SQL Server Proj.Management Oracle Constraint Programming Tips Database Normalization eBook: |
||
Copyright © www.databasedesign-resource.com /
All rights reserved. All information contained on this website is for informational purposes only. Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein. Return to top
The name Oracle is a trademark of Oracle Corporation. |