SQL Introduction-

SQL stands for Structured Query Language and is used to pull information from databases.SQL offers many features making it a powerfully diverse language that also offers a secure way to work with databases.

SQL - Fundamentals

SQL alone can input, modify, and drop data from databases. In this tutorial we use command line examples to show you the basics of what we are able to accomplish. With the use of web languages such as HTML and PHP, SQL becomes an even greater tool for building dynamic web pages. To get started you will need a working database program such as Oracle, DB2, SQL Server, or Mysql.

For more information and installation help on either of the above database programs, we suggest you go straight to the developer homepages at:

Oracle
SQL Server
MySQL

SQL - World Wide Web

SQL has become a popular among web designers due to its flexibility and simplicity. With some basic knowledge of HTML, PHP, and a database program such as MySQL, a web designer is capable of creating some of the most complex of web sites. Most of the concepts demonstrated here in the tutorial are also great for web programming.

SQL - Fundamentals

SQL alone can input, modify, and drop data from databases. In this tutorial we use command line examples to show you the basics of what we are able to accomplish. With the use of web languages such as HTML and PHP, SQL becomes an even greater tool for building dynamic web pages. To get started you will need a working database program such as Oracle, DB2, SQL Server, or Mysql.

For more information and installation help on either of the above database programs, we suggest you go straight to the developer homepages at:

Oracle
SQL Server
MySQL

SQL - World Wide Web

SQL has become a popular among web designers due to its flexibility and simplicity. With some basic knowledge of HTML, PHP, and a database program such as MySQL, a web designer is capable of creating some of the most complex of web sites. Most of the concepts demonstrated here in the tutorial are also great for web programming.

SQL - Four Principles of Database Design

When designing and implementing a database, keep in mind these four guidelines.

Atomicity
Your coded statements flow without the constant need to update or "fix" your data.
Consistency
Your statements are either executed 100% or fail 100%, do not implement code that partially works.
Isolation
Keep data files and logs away from public eyes, and limit the number of users with administration access to your database.
Durability
Maintain reliable servers with plenty of storage space and back-up systems that save transactions immediately.

A well thought out database will continue to serve and meet your needs for ages. It is important to plan ahead and really put some thought into what you intend to record in your databases. Keep in mind that tables and databases should maintain some relationship. In many instances it is far more desirable to have several small related tables and databases than one giant one. "Never place your eggs all in one basket."

The Database Model

Dr. Edgar F. Codd was the founding father of what is known as the relational model of databases. In 1970, he published a groundbreaking article "A Relational Model of Data for Large Shared Data Banks." Included within the article were 12 rules of relational databases. These rules are as follows (paraphrased).

1. Information Rule
   All data in the database should be represented as values in tables.
2. Guaranteed Access
   Every piece of data should be accesible by using the table name, primary key, and a column name.
3. Treatment of NULL Values
   Null values must be treated as incomplete data pieces. Nulls are not to be confused with zeros.
4. Active-Online Relational Catalog
   A database is represented at a logical level of tables.
5. Sublanguage
   Having one supported language with a well-defined syntax.
6. Updating Views
   All views should be updated through the system.
7. Set-level Insertion, Update, and Deletion
   System must support set-time insert, update, and deletion operations.
8. Data Independence (Physical)
   Alterations to the way data is stored must not alter the application itself.
9. Data Independence (Logical)
   Altering tables, columns, and/or rows must not alter the application itself.
10. Integrity Independence
    The language of the database must define integrity rules.
11. Distribution Independence
    Distributing the database to numerous locations should be anonymous and existing applications should be unaffected.
12. Nonsubversion
    If the system uses a low level interface to record data, then there must be a higher level interface used when administrating.

The largest of corporations follow these rules of cataloging information to this very day.

Database Driven Web Sites

Today we experience the power of databases throughout the web. Many sites are completely dynamic, meaning the content that is being display is held inside of a table with columns, and columns of information to display. This is quickly becoming the ideal way to host websites. It allows for dynamic content on the fly, user interaction, and webmasters can store information about returning users making way for the site to recognize returning users. Any chance at building rapport with your audience is a great opportunity indeed.

SQL - Platforms

A SQL platform acts as the stage for building and developing your databases. Several different platforms exist including:

SQL - MySQL and PostgreSQL

MySQL and PostgreSQL are open source database programs rich in functionality and flexibility. They are often the choice of web developers and small businesses simply because they get the job done for a very reasonable price. Also they will go anywhere and can operate on nearly every operating system available.

SQL - SQL Server

Microsoft's SQL Server is steadily on the rise in the commercial world gaining popularity slowly. This platform has a GUI "Windows" type interface and is also rich with functionality. A free trial version can be downloaded at the Microsoft web site, however it is only available to Windows users.

SQL - DB2 and Oracle

By far the selection of choice for large corporations is either Oracle or DB2. Companies that have large ties to IBM stick to their DB2 software whereas others have made the switch to Oracle. These systems run on personal computers as well as large corporate mainframes.

SQL - Queries

Queries are the backbone of SQL. Query is a loose term that refers to a widely available set of SQL commands called clauses. Each clause (command) performs some sort of function against the database. For instance, the create clause creates tables and databases and the select clause selects rows that have been inserted into your tables. We will dive deeper in detail as this tutorial continues but for now let's take a look at some query structure.

Query construction begins with one of the following clauses:

• Add
• Drop
• Create
• Insert
• Select
• Update
• Replace
• Delete

Queries are loosely typed into your SQL prompt. Spacing and line breaks are not very important as we will discuss further in our SQL Syntax lesson. We now know that a query begins with a clause, what comes next depends on the clause we select and we will be covering all the clauses as the tutorial progresses. For now, let's take a look at some syntax.

SQL - Query Syntax

The syntax of a query is loose, meaning you are free to place line breaks where you please without injuring the code. Few instances require parentheses, including the insert statement listed below. Parentheses will also be covered during our Functions lesson. Be sure to end all query statements with a semicolon (;).

SQL Code:

SELECT * FROM table_name;

The above code selects every row and every column from a hypothetical table (table_one) and prints it to our prompt. Here's a look at a few more queries that should become second nature to you as the tutorial coninues.

SQL Code:

INSERT INTO table_name (column_one,column_two)
VALUES(value_one,value_two);

SQL Code:

UPDATE table_name SET column_one = value_one, column_two = value_two;

Queries are how you communicate to your database program. Nearly everything typed at a SQL command prompt is a query.

SQL - What are Subqueries: Advanced Queries

Subqueries can be performed inside of existing queries. This expands the capabilities of SQL in a number of ways providing a 3rd dimension to the language you might say. Again, we will discuss subqueries in more detail in a later lesson. Feel free to familiarize yourself with what a subquery may look like using the example below.

SQL Code:

SELECT * FROM table_one WHERE unique_column =
(SELECT unique_column FROM table_two WHERE id_column = 1) 

Above is a look at where you might cross subqueries. The logic behind the entire query is fairly confusing at this point, try and stay with us and focus on the syntax of the query and subquery.

SQL - Syntax

SQL follows a general syntax, there are not many quotations or other symbols to throw into your statements. Generally we follow a Do what To what syntax, meaning first we decide what we want to do, then we decide what we want to do it to, and finally we end the whole thing with a semicolon (;).

A statement begins with a clause. Clauses are commands in the SQL world and the backbone of any script. The first clause of a statement gives a general idea of what type of action a script is taking. A few basic clauses are SELECT, INSERT, or CREATE. We will look at each of these clauses a little more in depth on the next few pages but it may be obvious to you already what each of those clauses does. SQL statements end with a semicolon as most with most programming languages. A basic statement might look like this:

SQL Code:

SELECT * FROM table_name;

Above we have a SELECT clause asking for all columns and values (*) from our database table. As shown above, a good habit is to capitalize your clauses. Later on when we have larger statements and subqueries it will make life much easier to go back and debug your code.

Formating your statements in a similar fashion will also aid your debugging efforts. The common formatting technique is to begin each line with a clause or to break up and list columns or tables as needed. More on this in a moment.

SQL Code:

SELECT *
FROM table_name;

The advantage of this isn't apparent with this example. Each are fairly easy to read. However the example below shows an example where this format shines.

SQL Code:

SELECT column_one, column_two
FROM table_name
WHERE (
column_one,
column_two,
column_three,
)

SQL - Data Types

SQL recognizes 4 general types of data. As the database designer you will be selecting which type of data that can be placed in each table column. Before we look at each type of table column we will elaborate on specific data types and how they are handled in SQL.

SQL - NULL Values

A null value may be the most foreign to new programmers. Stating that a value has a null value indicates that nothing exists in that table field. When the table is created you may either allow a table to have a null value or may disallow null values for each table column.

SQL Code:

CREATE TABLE weekly_payroll
(employee_id VARCHAR(10) PRIMARY KEY,
total_hours INT NULL,
hourly_rate MONEY NOT NULL,);
= (SELECT column_one,
column_two
FROM table_two
WHERE table_one.id = 'table_two.id');

As you can see, when subqueries are thrown into the mix things become a little more complicated. A one line statement will not fit across your screen. Both statements are neither right nor wrong, each are easier to follow. Parentheses generally depict order of operations but it is not an exact science. Quotations are not found until the predicate of the statement.

SQL - Numeric Data

Dates, time stamps, integers, and money are all numeric data types. The advantage of working with numbers is that SQL has built in functions such as the AVG() or SUM() functions that will return the average or the sum of a numeric column.

Numbers:

rate_of_pay

27

26.66

28.40

SQL - Boolean Data

Boolean values are either yes/no (true/false) types of data. Others use a 1/0 (1 for yes 0 for no) approach. Either something is or something is not.

Boolean Values:

admin

1

1

0

SQL - Character Strings

Character strings are sentences, symbols, or a combination of both. Math functions can not be performed with character strings.

Character Strings:

employee_id

TS_0036

TS_0078

CL_1099

SQL - Commands

SQL commands can be categorized into three distinct groups. Each type of command plays an essential role while working with your database. One analogy might be to think of each SQL Command as a possible tool in your tool shed. Certain duties require specific tools and the more tools you have in your shed, the greater the chances that you will have the exact tool you need for the appropriate job.

SQL - What is a Clause

Clauses were mentioned briefly in the SQL Queries lesson. To briefly recap, they are the commands issued during a query. SELECT, INSERT, ADD, DROP, CREATE, etc are all clauses that begin each SQL Query and execute some sort of action upon your database.

SQL - What is a Function

There are a number of functions built into SQL that can add column values for you, average column values, or even change lowercase to uppercase strings. These functions are used directly inside of your queries and are excellent tools for you to use.

SQL Code:

Count() function


SELECT COUNT(*) FROM table_one;

The query above will return a numeric value representing the number of rows that have been inserted into your database. We will be covering this function as well as many others as this tutorial continues.

SQL - What is an Operator

Operators are a means by which SQL can manipulate numbers and strings or test for equality. They come in four flavors including: Arithmatic, Range, Equality, and Logical. As your skills with SQL grow, you may want the language to start performing some basic arithmatic for you or perhaps you wish to select a range of rows with a numeric column value larger than 5. This becomes possible with operators.

SQL Code:

SELECT * FROM table_one WHERE column_one > 5;
 

SQL - Operators

Operators are used in expressions or conditional statements. they show equality, inequality, or a combination of both. Mathematical operators are found in every computer language and may be familiar to you. SQL operators follow the same rules you may have learned in a math class or know from previous programming experience.

Operators come in three flavors, mathematical, logical, or range operations. Mathematical operators add, subtract, multiply, divide, and compare equality of numbers and strings. There are two logical operators, AND / OR. Range operators include the infamous <> symbols used to compare equality. Take note of the following tables for future reference.

SQL Arithmetic Operators:

Operator	Example	Result	Definition
+	7 + 7	= 14	Addition
-	7 - 7	= 0	Subtraction
*	7 * 7	= 49	Multiplication
/	7 / 7	= 1	Division
%	7 % 7	= 0	Modulus

Modulus may be the only unfamiliar term on the chart. this term describes the result when one number is divided by another number resulting in a remainder. For example 4 % 3 would result with a value of 1 since 1 is left over after 4 is divided by 3.

SQL Range Operators:

Operator	Example	Defined	Result
<	7 <>	7 less than 4?	False
>	7 > 4	greater than 4?	True
<=	7 <= 11	Is 7 less than or equal to 11?	True
>=	7 >= 11	Is 7 greater than or equal to 11?	False

SQL Equality Operators:

Operator	Example	Defined	Result
=	5 = 5	Is 5 equal to 5?	True
<>	7 <> 2	Is 7 not equal to 2?	True

SQL Logical Operators:

Operator	Defined	Example
AND	Associates two values using AND	if (($x AND $y) == 5)...
OR	Associates two values using OR	if (($x OR $y) == 5)...

SQL - Expressions

In the programming world an expression is a special statement that returns a value. SQL is no exception to this standard rule.

SQL - Expression Types

Expressions in SQL generally fall into one of four categories including: Boolean, Numeric, Character, and/or Date Expressions.

SQL Code:

SELECT column_one FROM table_name;

The simplest form of an expression appears as column_one of our table. Select is our clause telling our database what we want to do, and column_one acts as the defined expression returning each row of that particular column.

Expressions after the where clause might appear more familiar to programmers.

SQL Code:

SELECT * FROM table_name WHERE column_one = 'some value';

The latter example returns rows of the specified column containing 'some value'. Using expressions like the one above gives you precise control over what results will be returned. More information on the where clause is available at SQL Where.

SQL - Boolean Expressions

A boolean expression in any programming language returns a true/false result. Returning to the previous example containing the where clause.

SQL Code:

SELECT * FROM table_name WHERE column_one = 'some value';

The logic behind this query is that each row is being tested for 'some value' to appear in our column_one. Each time a match is found (testing true), that row is selected and returned for our viewing pleasure.

SQL - Numeric Expression

A numeric expression simply returns a numeric value. There are some built in functions that we will be examining in greater detail later on. Using one of the following functions is perhaps the easiest way to demonstrate the return of a number:

• AVG() -- Returns the average of a stated column.
• COUNT() -- Returns a count of the number of rows of a given column.
• SUM() -- Returns the sum of a given column.

SQL Code:

SELECT COUNT(*) FROM table_name;

Our expression above returns a numeric value representing the number of rows that have been inserted into your table thus far. Please be aware that the AVG(), COUNT(), and SUM() only return results for integer table columns. Using one of these functions with a varchar column will result in an error message.

SQL - Character Expressions

Character expressions are used to test for values of a string. Often these expressions will appear in a where clause as follows.

SQL Code:

SELECT * FROM table_name WHERE
column_one LIKE '%string';

Here we have used the percent(%) symbol to signify the start of our string. SQL tests our expression against column_one and returns all the rows and columns where column_one contains our string.

This might come across easier if we use a live example. Say we have created a table with employee information. In this table we have set up a column a last_name column. The query above will come in handy if we were wanting to pull all the employees with a last_name that begins with a "T". Now if we plug in our hypothetical situation into our code, we should have something like the following.

SQL Code:

SELECT * FROM employees WHERE
last_name LIKE '%T';

Keep in mind that SQL is case sensitive, using a lowercase t would not yield results for a last_name that has been capitalized.

SQL - Date Expressions

Date expressions come in three flavors. These expressions are very straight forward, simply type in any of those listed below and SQL will return exactly what you have requested.

• Current_Date -- Returns the current date.
• Current_Time -- Returns the current time.
• Current_Timestamp -- Returns the current timestamp.

These expressions can also be placed into your tables as column values for any given row with an insert statement. We will be looking more indepth at the insert clause, however here is a glimpse of what is to come.

SQL Code:

INSERT INTO table_name(column_one,column_two,)
Values(Current_Date,Current_Timestamp);

This statement inserts a new row into our imaginary table with the current date value for column one and the current timestamp value for columne_two.