tdog.blog

SQL, or Structured Query Language, is a widely used and powerful language for
accessing database information. Although it is powerful and flexible, it can
be complex. The goal of this article is to provide an overview of the language’s
basic capabilities.

NOTE: Not all dialects of SQL are the same. Every attempt has been made
to use a common subset of the various SQL dialects. If an example in this article
doesn’t work on your system, refer to your database documentation.

Before discussing SQL, you need to understand databases.

Database Terminology

A database is a collection of information organized as a two-dimensional grid
of rows (sometimes called records) and columns. Each column
has a unique name and can contain a certain type of data, such as numbers or
words. Each row contains the values for each column. A particular intersection
of a row and column is called a field. Each field contains data, such
as a name, a phone number, or address.

A relational database system is simply a database whose data is split into
multiple tables. Each table in the database has a unique name, and together
they are related to each other with keys.

A key provides a unique identifier for a record. In the first example, the
EMPLOYEES table contains information on employees. You wouldn’t choose
the first or last name column as a key because there is a high likelihood that
more than one employee has the same first or last name. You also wouldn’t choose
the phone number as the key as there might be more than one employee living
at the same house. Instead, you would use the employee’s Social Security number
as the key.

EMPLOYEES
SSN (key)
first_name
last_name
home_phone
job_title

Using SQL to Retrieve Database Information

To retrieve information from the EMPLOYEES table, you would use the
SQL select command. There are many ways to use this command, for example:

To retrieve all records from the table:

	select * from EMPLOYEES;

This query returns:

SSN	first_name	last_name	home_phone	job_title
111-22-3333	James	Miller	555-1234	Webmaster
222-44-3331	Kathy	Jones	555-1393	CFO
444-33-1993	William	Smith	555-1990	CEO
888-99-9999	Tom	Smith	555-3991	Senior Developer

To retrieve only the first and last names:

	select first_name,last_name from EMPLOYEES;

This query returns:

first_name	last_name
James	Miller
Kathy	Jones
William	Smith
Tom	Smith

To retrieve only those records for employees with a last name of Smith:

	select *
	from EMPLOYEES
	where last_name = Smith;

This query returns:

SSN	first_name	last_name	home_phone	job_title
444-33-1993	William	Smith	555-1990	CEO
888-99-9999	Tom	Smith	555-3991	Senior Developer

To execute the same query again, except this time sort the results by the
employee’s first name:

	select *
	from EMPLOYEES
	where last_name = Smith
	order by first_name;

This query returns:

SSN	first_name	last_name	home_phone	job_title
888-99-9999	Tom	Smith	555-3991	Senior Developer
444-33-1993	William	Smith	555-1990	CEO

To retrieve only those records for employees with certain digits in their
phone number:

	select *
	from EMPLOYEES
	where home_phone like '555-%9%';

This query returns:

SSN	first_name	last_name	home_phone	job_title
222-44-3331	Kathy	Jones	555-1393	CFO
444-33-1993	William	Smith	555-1990	CEO
888-99-9999	Tom	Smith	555-3991	Senior Developer

Complicating Matters

To complicate the situation a little (and to show off the power of SQL), let’s
add another table, called TRAINING, to the existing database.

The TRAINING table contains a record of each employee’s performance
on training classes. Because the EMPLOYEES table already contains personal
information on each employee, there is no need to recreate that information
in this new table.

How do you take advantage of the fact that this information already exists
in another table? All you have to do is create a column called SSN
in the TRAINING table and make it the primary key. The values in this
column link the TRAINING table to the EMPLOYEES table. We’ll
show some examples of running queries on multiple tables (called joins)
later.

TRAINING
SSN (key)
word_proc
db
sprdsht
time_mgmt
html
internet

There are different types of queries you can run on numbers, for example:

To find out which employees received a score higher than 50 on the database
training course:

	select SSN,db
	from TRAINING
	where db > 50;

This query returns:

SSN	db
111-22-3333	66
222-44-3331	86
888-99-9999	96

To find the lowest grade for the spreadsheets course:

	select min(sprdsht)
	from TRAINING;

This query returns:

min(sprdsht)

67

NOTE: Use the max function to find the highest score.

To find the average grade for the time management course:

	select avg(time_mgmt)
	from TRAINING;

This query returns:

avg(time_mgmt)

50.5

Retrieving Data from Multiple Tables

From the examples above, you can see that the query results are useful but
not very user-friendly. For example, it would be better to see an employee’s
first and last name instead of a Social Security number.

To achieve this, you use a join. Joins allow you to join information
from one table with information from another table. The most likely place to
execute the join is through the keys. In our example, you would use the SSN
keys in each table to create the join.

To list the names of each employee and their grade for the HTML training
course:

	select first_name,last_name,html
	from EMPLOYEES,TRAINING
	where EMPLOYEES.SSN = TRAINING.SSN;

This query returns:

first_name	last_name	html
James	Miller	95
Kathy	Jones	14
William	Smith	55
Tom	Smith	74

To list the names of each employee who received a score higher than 80 on
the Internet basics course:

	select first_name,last_name,internet
	from EMPLOYEES,TRAINING
	where
		internet > 80 and
		EMPLOYEES.SSN = TRAINING.SSN;

This query returns:

first_name	last_name	internet
William	Smith	99

Comments(0)