Introduction to SQL

The Structured Query Language (SQL) is the language of databases. All modern relational databases, including Access, FileMaker Pro, Microsoft SQL Server and Oracle use SQL as their basic building block. In fact, it’s often the only way that you can truly interact with the database itself. All of the fancy graphical user interfaces that provide data entry and manipulation functionality are nothing more than SQL translators. They take the actions you perform graphically and convert them to SQL commands understood by the database.

What is SQL?

·         SQL stands for Structured Query Language

·         SQL lets you access and manipulate databases

·         SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

·         SQL can execute queries against a database

·         SQL can retrieve data from a database

·         SQL can insert records in a database

·         SQL can update records in a database

·         SQL can delete records from a database

·         SQL can create new databases

·         SQL can create new tables in a database

·         SQL can create stored procedures in a database

·         SQL can create views in a database

·         SQL can set permissions on tables, procedures, and views

Introducing Databases

Before we get into the SQL statement required to retrieve this information, let’s try phrasing our question in plain English. We want to “select all stock numbers from the prices table where the price is over $5.” That’s a pretty simple request when expressed in plain English, and it’s almost as simple in SQL! Here’s the corresponding SQL statement:

SELECT StockNumber
FROM Prices
WHERE Price > 5

It’s as simple as that! If you read the statement above out loud, you’ll find that it’s extremely similar to the English question we posed in the last paragraph.

Interpreting SQL Statements

Now let’s try another example. This time, however, we’ll do it backwards. First, I’ll provide you with the SQL statement and let’s see if you can explain it in plain English: 

SELECT Price
FROM Prices
WHERE StockNumber = 3006

So, what do you think this statement does? That’s right, it retrieves the price from the database for item 3006. 

There’s one simple lesson you should take away from our discussion at this point: SQL is like English. Don’t worry about how you construct SQL statements; we’ll get to that in the rest of our series. Just realize that SQL isn’t as intimidating as it may first appear. 

How SQL Works

The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle Database, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:

·         It processes sets of data as groups rather than as individual units.

·         It provides automatic navigation to the data.

·         It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and the PL/SQL extension to Oracle SQL is similar to PSM.

SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle Database that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.

SQL provides statements for a variety of tasks, including:

·         Querying data

·         Inserting, updating, and deleting rows in a table

·         Creating, replacing, altering, and dropping objects

·         Controlling access to the database and its objects

·         Guaranteeing database consistency and integrity

SQL unifies all of the preceding tasks in one consistent language.

            For SQL online training classes please contact : training@virtualnuggets.com

                                                 http://www.virtualnuggets.com/