SQL - Queries

SQL uses commands called queries to execute tasks such as creating a databases (which we've seen already), updating data inside a table, or selecting data rows that meet specific criteria. Each query statement begins with a clause such as SELECT, UPDATE, CREATE, or DELETE and the simplest query is only two words long. It is a query that uses a built in SQL function called getdate() that will return the current server date. Execute the following statement to have SQL go and fetch the current SQL server date and time:

SELECT getdate();

SQL Results:

As you can see, this statement begins with the SELECT clause and the built in function getdate() does all the work for you. By executing this query you are essentially telling your database to "go and fetch" the current date.

This particular query is probably as simple and straight forward as it gets in the SQL world. Even though you may not know exactly what is happening here or when this may be useful, you have taken the first step toward understanding the fundamental mechanics of SQL query statements and the very foundation on which SQL architecture resides.

SQL - Query Structure

Queries are loosely typed into your SQL prompt. Spacing and line breaks are not very important as excess line breaks and spacing are ignored by the SQL application. We now know that a query begins with a clause, what comes next depends on the clause.

In order to expand our understanding of queries we will need to create a table and populate this table with some data which we can then manipulate as we introduce more Query commands and SQL functions. The next couple of examples should be overwhelming to newer SQL programmers but we will still take a moment to explain what's going on and focus on the structure of each query.

USE mydatabase;

CREATE TABLE orders
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   customer VARCHAR(50),
   day_of_order DATETIME,
   product VARCHAR(50),
   quantity int
);

The first line of our example "use mydatabase;" is pretty straight forward, this line defines the scope of the query and directs SQL to run the following command against the mydatabase database as appose to some other database that exists inside our SQL application. The blank line after is not required but it makes our query much more legible and the line following the page break starting with the CREATE clause is where we are actually going to tell SQL to create our new table and give this table a name, orders (CREATE TABLE orders).

The lines contained in parenthesis() are telling SQL how to set up the columns. This is a list and each new table column is separated by a comma (,). It isn't important to understand many of the other details they will be explained later on in different lessons. For now just take note that we are creating a new table to store data and this table has only 5 essential table columns .

The next query is going to add data to our table by inserting values into each table column.

use mydatabase;

INSERT INTO orders (customer,day_of_order,product, quantity)
VALUES('Tizag','8/1/08','Sharpie',4);

Listed above is a typical INSERT query used to insert data into the table we previously created. Again, we must specify which database we intend on executing a query against (use mydatabase - Line 1). And then we tell SQL what we want to do (INSERT INTO orders- insert data). Finally, we have to list the columns we intend to insert data into and the final line lists the actual values that will be inserted into each of the table columns.

SQL - Query Structure Review

Structurally, all three queries we have run in this lesson are very similar. All queries start with a clause, telling SQL how to handle the query and then the remaining lines describe in detail how we want SQL to go about performing our task.

• Go Back
• Continue

Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!