SQL - Structured Query Language

SQL - Structured Query Language
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 - What's a Database?
A database is nothing more than an empty shell, like a vacant warehouse. It offers no real
functionality what so ever, other than holding a name. Tables are the next tier of our tree offering a
wide scope of functionality. If you follow our warehouse example, a SQL table would be the physical
shelving inside our vacant warehouse. Each SQL table is capable of housing 1024 columns(shelves).
Depending on the situation, your goods may require reorganization, reshelving, or removal. SQL tables
can be manipulated in this same way or in any fashion the situation calls for.
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:
IBM's DB2
MySQL
PostgreSQL
Oracle
Microsoft's SQL Server
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,
)
= (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 - 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.
Character Strings - ('Words or numbers')
Numbers - (3, 3.423, -17)
Booleans - (True / False)
Nulls - (empty fields)
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,);
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 < and > 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<4
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.
SQL - Create
A database is nothing more than an empty shell, like a vacant warehouse. It offers no real
functionality what so ever, other than holding a name. Tables are the next tier of our tree offering a
wide scope of functionality. If you follow our warehouse example, a SQL table would be the physical
shelving inside our vacant warehouse. Depending on the situation, your goods may require
reorganization, reshelving, or removal. SQL tables can be manipulated in this same way or in any
fashion the situation calls for.
SQL - Create Database
We first need to build our warehouse, or create our database. The create clause is straight
forward. Here we have a one line script using the create clause to create a database we named
business. The exact number of databases a SQL program can handles is entirely up to the
manufacturer, visit your sites manufacturer if you would like to have specifics.
SQL Code:
CREATE DATABASE business;
We will be using this database for the remainder of the tutorial.
SQL - Create Table
Our emtpy shell of a database will do nothing standing alone, next we create our tables to store
and organize our data. We use the same create clause and follow the same syntax above. Tables,
however, are fairly complex. In our script we must also include parameters for each table column as
well as name each one. Naming your table columns can be as simple or complicated as desired. SQL
programs are case sensitive, keep this in mind as you will be calling on our table columns by name a
great deal as you enhance your SQL knowledge. Also, most programs do not support spaces in
column names, you must use the underscore (_).
Column types specify what type of data can be placed inside a table column ranging from
numbers, paragraphs, or brief strings. For example, setting a column type to an int value means your
database will ONLY accept an interger value for this column type.
Column Types:
Column Type
int
varchar
text/blob
Description
Accepts integer values only
Accepts any symbol, character, or number
lots of text including line breaks
Syntax
tinyint, int
varchar(char limit value)
text, blob
Int, varchar, and text are the 3 most common types of columns. Text and int columns have 3
flavors tiny, medium, and large. Every SQL program has its unique sizes, but for this tutorial we will be
using medium sized column fields for each exercise. Later on as your database exapands it becomes
extremely important to not overdue the size of your column fields. Using the correct size field will
dramatically increase performance including query speeds.
Now create the table.
SQL Code:
CREATE TABLE employees
(
id INT(3) NOT NULL AUTO_INCREMENT,
Lastname VARCHAR(50),
Firstname VARCHAR(25),
Title VARCHAR(10) DEFAULT 'crew' NULL
);
Above is our table, the first column simply numbers each row that will be added to the table up to
a maximum of 3 digits wide (999) automatically(auto_increment). Our second line is a varchar meaning
it will hold numbers, digits, or symbols, which is perfect for short names of people. The last column has
a specified default value of "crew" so that if we add a new crew member we can simply place a default
value.
We do not always have to specify a default value or state weather each column may have a NULL
value, By default, most table columns will allow null values to be recorded but certain table columns
will not allow a null value to be inserted. We recommend reading up on each column type available to
you from your database manufacturer.
SQL - Primary Key
A primary key is a property given to a table column that distinguishes that record apart from each.
For each record in the table the primary key acts like a driver's license number, only one number exists
for each person. The same principle applys here. Any table can only be given one auto increment field
and as such it is forced to be the primary key of the table. Therefore in our following examples we use
the 'id' field as our primary key.
The alter clause is used to add or drop primary keys and indexes. To change a primary key you
must drop the first one, then add your desired primary as shown below. More about the alter clause
later.
SQL Code:
ALTER TABLE 'employees' DROP PRIMARY KEY,
ADD PRIMARY KEY ('id');
SQL - Indexes
SQL automatically creates some indexes based on column types and attributes. An index can also
be given to a table column to optimize speeds. When a query is executed searching for a specific
column value, SQL will start at the top of the table and search each and every record until it finds
matches. This becomes a performance issue when a table holds a vast amount of records. By adding
an index to columns, SQL will no longer search the entire table, it will pinpoint your index columns and
search those first. The downside to indexing is that it enlarges the disk space consumed by a table on
your webserver. Use indexes when you notice a drop in your query speeds.
SQL Code:
ALTER TABLE 'employees' ADD INDEX ('id');
or
ALTER TABLE `employees` DROP INDEX `Lastname`;
SQL - Insert
The insert clause has one function; to insert data into a table. Insert populates each table column
with a value. Rows are inserted one right after another into the corresponding column.
SQL Code:
INSERT INTO employees (Lastname,Firstname,Title)
VALUES(Johnson,David,crew);
Above we have a single line example of the insert syntax. Since our 'id' column is set up to auto
increment, we can omit this field from our script. Our SQL program automatically begins counting
starting with one, when the auto increment attribute is added to an integer field.
Display:
id Lastname Firstname Title
1 Johnson David
crew
SQL - Insert defaults and nulls
We mentioned setting up default or null values for table columns. Simply placing the word default
or null, in place of a value is the solution.
SQL Code:
INSERT INTO employees (Lastname,Firstname,Title)
VALUES('Hively','Jessica',DEFAULT);
or
INSERT INTO employees (Lastname,Firstname,Title)
VALUES('Hively','Jessica',NULL);
SQL - Inserting multilpe values
Here's an example of how to insert more than one record at a time. Many web developers will use
the single example above along with HTML forms to continually insert and update their SQL tables.
SQL Code:
INSERT INTO employees VALUES
(DEFAULT,'Hicks','Freddy','crew'),
(DEFAULT,'Harris','Joel','crew'),
(DEFAULT,'Davis','Julie','manager');
We use a default value for the id field so that it will continue to auto increment for each new record.
Using this method you must have some value for each table column.
SQL - Insert into multiple tables
This concept isn't widely supported by open source database programs, however they do offer
alternative methods to achieve the same goal. The idea is to insert similar record values into 2 or more
tables with one statement. Using the example from above, we want to place Julie's information into our
manager table as well as the general employee table.
SQL Code:
INSERT ALL INTO employees (Lastname,Firstname,Title)
VALUES('Davis','Julie','manager')
INTO manager (training,salary)
VALUES ('yadayada','22500');
SQL - Select
In SQL, queries begin with the select clause. A typical query statement needs only two parts, we
select what from where. The what will represent columns of your table you wish to select and the
where represents the table name of your data table.
To avoid typing a list of every single column in your table, you can use the astric (*) to select all
table columns from a table. However, query speeds are very important and always selecting every
table column can dramatically decrease your SQL performance. As good practice, it is best to only
select the table columns that you wish to use or display.
SQL Code:
SELECT * FROM employees;
SQL Table:
ID
1
2
9
10
11
Lastname
Johnson
Hively
Hicks
Harris
Davis
Firstname
David
Jessica
Freddy
Joel
Julie
Title
crew
crew
crew
crew
manager
Our result should print every table column and every bit of data stored in each row thus far.
SQL Code:
SELECT Lastname,Firstname FROM employees;
SQL Table:
Lastname Firstname
Johnson
Hively
Hicks
Harris
Davis
David
Jessica
Freddy
Joel
Julie
SQL - Select Multiple Tables
Multiple columns from different tables can also be selected. The syntax is relatively the same, the
difference being when you choose that table column you wish to select, you must now name the table
of the desired table column.
The period is necessary to differentiate between the table name and the column name, other than
that all the same query rules apply.
SQL Code:
SELECT employees.Fisrtname, employees.Lastname, crew.experience FROM
employees, crew;
SQL - Select Functions
We havn't mentioned anything about SQL Functions yet and we will eventually cover them in
greater detail, for the moment we would like to introduce this concept of using a selection query with a
function. For instance we can query our database program for the current_timestamp or retrieve the
number of rows in our table using the COUNT() function.
SQL Code:
SELECT CURRENT_TIMESTAMP;
This will return a server timestamp representing the date and time of when the query was
executed, more on this toward the end of the tutorial.
SQL Code:
SELECT COUNT(Lastname) FROM employees;
We will dive deeper into detail as the tutorial progresses, but be aware of this concept of selecting
date by means of calling a function as it will prove more useful later on.
SQL - Order By
The order by statement allows for table column assortment. It allows for ascending or descending
lists of your table column values permitting SQL to reorder your table rows for the purpose of viewing.
SQL Code:
SELECT * FROM employees ORDER BY Lastname;
SQL Table:
ID
11
10
9
2
1
Lastname
Davis
Harris
Hicks
Hively
Johnson
Firstname
Julie
Joel
Freddy
Jessica
David
Title
manager
crew
crew
crew
crew
The above example resorts our rows of data alphabetically by lastname. Here is another example
ordering by two different columns First we alphabatize our job titles and again we order by lastname.
SQL Code:
SELECT * FROM employees ORDER BY Title,Lastname;
SQL Table:
ID
10
9
2
1
11
Lastname
Harris
Hicks
Hively
Johnson
Davis
Firstname
Joel
Freddy
Jessica
David
Julie
Title
crew
crew
crew
crew
manager
Hint: It is possible to order by a column you do not wish to display such as an ID field. Also notice
at the bottom of each query display will be the number of rows affected by your query. This number will
also be displayed anytime an insert, select, update, or delete statement is properly executed by your
SQL program.
SQL - Distinct Usage
Distinct is used to retrieve rows of your database that have unique values for a given column. For
example say we have a table of employees and in this table of employees we have several job titles
from janitors to CEOs. We would like to know just how many distinct job titles we have.
SQL Code:
SELECT DISTINCT job_titles FROM employees;
Our statement will return the number of rows with a distinct value for our column (job_titles).
SQL - Distinct Multiple Columns
Using the distinct function with more than one column yields some substantial results. SQL will
return the rows with distinct or unique combinations of those columns. Assume this same employee
table has another column including the salary of each employee. With the use of the distinct function
we can pull a list of unique job titles - salaries.
SQL Code:
SELECT DISTINCT job_titles, salary FROM employees;
SQL has returned all the rows with unique combinations of job titles and salaries. Any duplicate
combinations of job titles and salaries will be ignored. For example if we had two CEOs in the table
making the same salary each year, only one row would be returned but if we had two CEOs with
different salaries, both rows would be returned.
SQL - Where
The where clause sets a conditional statement for your queries. Your server will query the
database searching for rows that meet your specific where condition. A typical query will have the
following syntax.
A conditional statement has 2 parts, the left side is your table column and the right side is the
condition to be met. Example:WHERE table_column(s) = condition.
SQL Code:
SELECT * FROM employees WHERE Title = 'crew' ORDER BY Lastname;
SQL Table:
ID
10
9
2
1
Lastname
Harris
Hicks
Hively
Johnson
Firstname
Joel
Freddy
Jessica
David
Title
crew
crew
crew
crew
All the known typical operators can be used in the conditional statements, for a complete list
backtrack to SQL Operators
SQL - Where multiple conditionals
SQL also supports multiple conditional statements within one script by using the AND or OR
operators. Continuing the example from above we can select only those employees that worked 36 or
more hours for the week, perhaps meaning they are full time employees.
SQL Code:
SELECT employees.Lastname,
employees.Firstname,
employees.Title,
payroll.Hours
FROM employees,payroll
WHERE employees.Lastname = payroll.Lastname
AND payroll.Hours => '36'
ORDER BY employees.Lastname;
As you can see, the where clause is a very powerful tool used to quickly pull rows from one table
or many.
SQL - In
In is a special kind of operator for use in your where clauses. Recall that in a where expression
only one value is allowed to be sent through the query. With the in operator one can send multiple
values in the where clause.
SQL Code:
SELECT * FROM table_name
WHERE some_column IN('value1','value2','value3');
The rows returned will have one of the values listed (value1, value2, or value3) in brackets as the
value for the specified column (column_one).
SQL - In Subqueries
To better understand the power of this function, let's use an example involving a subquery. Say
we have a database with the following tables.
Employees:
user_id
last
first
0045 Davis
Julie
0067 Smith
John
0098 Hodgensen Bruce
Logbook:
user_id timestamp
0045 000000002345
0045 000000045666
0098 000000076444
Now above we have an employees table giving us a user_id along with a first and last name of
employees we have working in the office. The second table might be a log type table that keeps track
of who accessed the database and at what time. This a security check. A standard operating
procedure if many people will be accessing the database.
What we want to know is the last name and first name of those that accessed the database. To do
this we may use a subquery like in the example below.
SQL Code:
SELECT first,last FROM employees WHERE user_id IN
(Select user_id FROM logbook);
The subquery highlighted in red selects all the values of user_id in the logbook and returns those
to the previous in statement. The result is a complete listing of the names of the employees that have
accessed the database and have been recorded inside the logbook table. The good news is that this
feat was accomplished without having to know the user_ids of every employee in the office. This
definatly adds some depth to your SQL knowledge thus far.
SQL - Between
Between is a special operator appearing in a where statement. It allows for the selection of a
range of values between one value and another.
SQL Code:
SELECT * FROM table_name WHERE column_one
BETWEEN value1 AND value2;
Be aware that the values you specify will also return results. For example if we were looking for
values between 5 and 10, all rows would be retrieved where our column value is 5,6,7,8,9, or 10.
SQL - In Between (Subqueries)
This next example requires that you are familiar with the previous lesson, SQL In. You may recall
that the In operator allows the where clause to return more than one single value for a column. Below
are two tables. The employee table only stores personal information about employees while the
logbook records when and who accessed our database.
employees
user_id last
first
0045 Davis
Julie
0048 Thomas
David
0067 Smith
John
0098 Hodgensen Bruce
logbook
user_id timestamp
0045 000000002345
0045 000000045666
0048 000000055767
0098 000000076444
Say we were thinking ahead when issuing user_ids and coordinated each user_id number with a
department. For example our data security department employees will be numbered 004X, where X
represents any number 0-9 (So data security employees are numbered 0040-0049). Since we konw
this information it will prove useful if we want to track down database users by department.
SQL Code:
SELECT * FROM employees WHERE user_id IN
(SELECT user_id FROM logbook WHERE user_id BETWEEN 0040 AND 0049);
SQL will now retrieve exactly what we need. A listing of all the data security employees (user_id
004X) that have accessed the database thus far.
SQL - Alter Table
The alter clause changes a table by adding, removing, or modifying an existing table column.
The following example adds a new column to our table.
SQL Code:
ALTER TABLE employees
ADD dob varchar(10);
SQL Table:
id Lastname Firstname Title dob
1 Johnson David
crew
Now we will remove the same column from the table.
SQL Code:
ALTER TABLE employees
DROP dob;
SQL Table:
id Lastname Firstname Title
1 Johnson David
crew
SQL - Alter Columns
Alter is used again when changing attributes of the table cloumns. For instance if we wanted to
change a table column name or change a column from a varchar to an interger type.
SQL Code:
ALTER TABLE 'employees' CHANGE 'ID' 'id' TINYINT ( 3 )
NOT NULL AUTO_INCREMENT;
Above we changed 'ID' to 'id' because SQL is case sensative and this will remove a potential bug
issue. After we have selected a new name, we list the new attributes to be given to the altered column.
SQL - Update
The update clause updates column values of a table. Update requires a conditional statement to
select the row to be updated.
SQL Code:
UPDATE employees
SET Lastname = 'Fletcher', Firstname = 'Ron'
WHERE id = '11';
SQL Table:
ID Lastname Firstname Title
11 Fletcher Ron
manager
Here we changed record 11, our manager, to a new manager.
SQL - Updating Multiple Rows
With the use of expressions and operators, update allows for the manipulation of several rows at
once.
SQL Code:
UPDATE employees
SET Title = UPPER(Title);
SQL Table:
ID
1
2
9
10
11
Lastname
Johnson
Hively
Hicks
Harris
Davis
Firstname
David
Jessica
Freddy
Joel
Julie
Title
CREW
CREW
CREW
CREW
MANAGER
Using the UPPER expression we changed our Title field to all capital letters. Update also supports
the use of subqueries.
SQL - Delete
Entire rows can be deleted from a table using the delete clause.
SQL Code:
DELETE FROM employees
WHERE id='4';
We could use the above code to delete an employee using their unique employee number that
has been assigned by the auto_increment field.
SQL - Deleting Multiple Rows
Delete is not limited to one row at a time. We may use all of our known SQL operators as well as
subqueries to delete any or all rows that apply to our conditional.
SQL Code:
DELETE FROM employees
WHERE id <= '999';
The above example would delete every row from our table since we had previously limited our "id"
field when we set up the table. Predicates, expressions, subqueries, and operators can be used to
delete any rows you would like to remove from your table.
SQL - Truncate
We can clear an entire table using a truncate statement. Truncate quickly clears all rows of a table
without deleting the table itself. If you are following along, we don't recommend that you run this script
because you will essentially undo all of your inserts and have only empty table field to show for it, but it
is a handy statement to know and use when the time is right.
SQL Code:
TRUNCATE TABLE employees;
SQL - Predicates
Predicates follow the where clause. Predicates allow the searching through database records to
recover specific strings and ranges or characters. Rows will be returned if they match the predicate
condition.
SQL - Like; Not Like
Like is a means of quering data in your database by keyword or keyletter means. After a where
clause, use LIKE to match a character or a string. SQL retrieves all rows containing all or part of the
string placed within the parameters. Generally, a percent sign (%) is used to define the start and
ending or your string. Escape characters can be used for special situations.
SQL Code:
SELECT * FROM employees WHERE Lastname LIKE '%H%';
SQL Table:
ID
10
9
2
Lastname
Harris
Hicks
Hively
Firstname
Joel
Freddy
Jessica
Title
crew
crew
crew
Our example will retrieve any and all rows with a capital letter H in the lastname field. Case
sensitivity is important in this situation.
On a contrary note, use the Not Like predicate to find all rows that do not match the string.
SQL Code:
SELECT * FROM employees WHERE Lastname NOT LIKE '%H%';
SQL Table:
ID Lastname Firstname Title
1 Johnson David
crew
11 Davis
Julie
manager
SQL - Predicates Escaping Characters
Say you want to find a percent character(%) in your database, or all rows associated with one.
SQL Server and Oracle, require an additional statement over MySQL. With Oracle and SQL Server,
you must specify the character used to escape. Here's code example.
SQL Code:
Oracle/SQL Server
SELECT * FROM employees WHERE Lastname LIKE '%\%%' ESCAPE '\';
MySQL
SELECT * FROM employees WHERE Lastname LIKE '%\%%'
MySQL has a built in default escape character the backslash (\). MySQL also supports the escape
predicate allowing you to change your escape character exactly as it is done in the other programs.
SQL - Between
Between is a predicate to call a range of numeric values such as 1-20 or the like. Its syntax
follows the same as above.
SQL Code:
SELECT * FROM employees WHERE id BETWEEN 1 AND 4;
SQL Table:
ID Lastname Firstname Title
1 Johnson David
crew
2 Hively
Jessica
crew
Between is essentially replacing your range operators. Always start your ranges with the lowest
number first.
SQL - Limit
The limit predicate allows you to limit the number of rows selected.
SQL Code:
SELECT * FROM employees LIMIT 2;
SQL Table:
ID Lastname Firstname Title
1 Johnson David
crew
2 Hively
Jessica
crew
SQL - Inner Join
The join clause combines columns of one table to that of another to create a single table. Join
matches up a column with one table to a column in another table. A join query does not alter either
table, but temporarily combines data from each table to be viewed as a single table. There are three
types of join statements, inner, left, and right.
We will be using our employees table from previous examples, and a new table to track sales of
each employee called invoices.
Our invoices table is set up with 3 fields, EmployeeID, Sale, and Price. If we were a business
owner we now have a means to track what was sold, and by whom and we can bring this information
together using an inner join clause.
Inner Join: An inner join returns all rows that result in a match such as the example above.
SQL Code:
SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID
SQL Table:
Lastname
Johnson
Hively
Davis
Davis
Firstname
David
Jessica
Julie
Julie
Sale
HOT DOG
LG SFT DRK
CD SLD
CD SLD
Price
1.99
1.49
3.99
3.99
We haven't changed or updated any information in either of our tables but we were able to fashion
together a new table using a conditional that matches one table column to another.
SQL - Left Join
A Left join returns all rows of the left of the conditional even if there is no right column to match.
SQL Code:
SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
LEFT JOIN invoices
ON employees.id = invoices.EmployeeID
SQL Table:
Lastname
Johnson
Hively
Hicks
Harris
Davis
Davis
Firstname
David
Jessica
Freddy
Joel
Julie
Julie
Sale
Price
HOT DOG
1.99
LG SFT DRK 1.49
CD SLD
CD SLD
3.99
3.99
This would be a great way to track sales per person per day if the invoice table had a date field as
well.
SQL - Right Join
A right join will display rows on the right side of the conditional that may or may not have a match.
SQL Code:
SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
RIGHT JOIN invoices
ON employees.id = invoices.EmployeeID
SQL Table:
Lastname
Davis
Davis
Johnson
Firstname
Julie
Julie
David
Hively
Jessica
Sale
CD SLD
CD SLD
HOT DOG
HOT DOG
LG SFT DRK
LG SFT DRK
Price
3.99
3.99
1.99
1.99
1.49
1.49
This would happen generally if perhaps nobody recieved credit for the sale or the sale was
credited to the store by default.
SQL - Union
The union clause places two serarate queries together forming one table. A union works best
when using two tables with similar columns because each cloumn must have the same data type.
Say we had another table called employees2 with the names and information of employees from
our second store. With 2 queries, we can combine the tables into a list of all employees.
SQL Code:
SELECT * FROM employees
UNION
SELECT * FROM employees2;
SQL Table:
ID
1
2
9
10
11
101
102
103
104
Lastname
Johnson
Hively
Hicks
Harris
Davis
Yazzow
Anderson
Carlson
Maines
Firstname
David
Jessica
Freddy
Joel
Julie
Jim
Craig
Kevin
Brad
Title
crew
crew
crew
crew
manager
crew
crew
crew
crew
The result is a complete listing every employee from store 1 and 2.
The next example shows a more practical means of using a union clause. Here we will select all of
our employees from both tables, and join them with our invoices table to generate a complete list of
sales from both stores on a given day.
SQL Code:
SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID
UNION
SELECT employees2.Lastname, employees2.Firstname, invoices.Sale, invoices.Price
FROM employees2
INNER JOIN invoices
ON employees2.id = invoices.EmployeeID;
SQL Table:
Lastname
Johnson
Hively
Davis
Yazzow
Carlson
Firstname
David
Jessica
Julie
Jim
Kevin
Sale
HOT DOG
LG SFT DRK
CK SLD
HOT DOG
LG SFT DRK
Price
1.99
1.49
3.99
1.99
1.49
Here we combined a join query with the union clause to create one table.
SQL - Union All
Union all selects all rows from each table and combines them into a single table. The difference
between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all
rows from all tables fitting your query specifics and combines them into a table.
SQL Code:
SELECT * FROM employees
UNION ALL
SELECT * FROM employees2;
SQL Table:
ID
1
2
9
10
11
101
102
103
11
104
Lastname
Johnson
Hively
Hicks
Harris
Davis
Yazzow
Anderson
Carlson
Davis
Maines
Firstname
David
Jessica
Freddy
Joel
Julie
Jim
Craig
Kevin
Julie
Brad
Title
crew
crew
crew
crew
manager
crew
crew
crew
manager
crew
SQL Code:
SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID
UNION ALL
SELECT employees2.Lastname, employees2.Firstname, invoices.Sale, invoices.Price
FROM employees2
INNER JOIN invoices
ON employees2.id = invoices.EmployeeID;
SQL Table:
Lastname
Johnson
Hively
Davis
11
Yazzow
Carlson
11
11
Firstname
David
Jessica
Julie
Davis
Jim
Kevin
Davis
Davis
Sale
HOT DOG
LG SFT DRK
CK SLD
Julie
HOT DOG
LG SFT DRK
Julie
Julie
Price
1.99
1.49
3.99
manager
1.99
1.49
manager
manager
SQL - Subqueries
MySQL offers a very limited support for subqueries, however Oracle and DB2 fully support them.
Subqueries are Select queries placed within an existing SQL statement. They may exist in any of the
following types of SQL statements.
Select
Insert
Update
Delete
Set
Do
Subqueries are great for answering very specific questions regarding your data inside your
database. For instance, as the employer you may notice employee number 101 had a great day
yesterday with sales. Just given this information we can use a subquery to pull the employee lastname
and first name from our database.
SQL Code:
SELECT * FROM employees
WHERE id =
(SELECT EmployeeID FROM invoices WHERE EmployeeID='1');
SQL Table:
id Lastname Firstname Title
11 Davis
Julie
MANAGER
Here we have pulled our employee information from the employees table by only knowing the
employee number from the invoices table.
SQL - Subquery Inserts
Subqueries can be used to pull old data from your database and insert it into new tables. For
instance if we opened up a third store and we wanted to place the same manager over 3 stores we
could do this by pulling the manager's information using a subquery and then inserting the records.
Also note that this form of insert will insert all cases where the subquery is true, therefore several rows
may or may not be inserted depending upon how your table is set up.
SQL Code:
INSERT INTO employees3
(id,Lastname,Firstname,Title)
(SELECT id,Lastname,Firstname,Title
FROM employees WHERE Title='manager');
With complete mastery of a subqueries you can now see the power of the SQL language. The
language is capable of nearly all things imaginable.
SQL - Dates
Unfortunately, every SQL platform has its own version of date functions, the few listed work in
DB2, Oracle, and MySQL. Microsoft SQL Server users should skip to our SQL Datepart lesson.
SQL - Timestamp
A timestamp servers as the catch all for dates and times. Retrieving a timestamp is very simple
and the result can be converted or manipulated in nearly every way imaginable.
SQL Code:
SELECT CURRENT_TIMESTAMP;
Return a Timestamp:
2004-06-22 10:33:11.840
Keep in mind that each platform of SQL (DB2, Oracle, SQL Server, etc...) may return dates and
times which are formatted differently.
SQL - Date Functions
As we just mentioned, it is possible to breakdown timestamps into their individual pieces using any
of the following date functions.
SQL Code:
SELECT MONTH(CURRENT_TIMESTAMP);
Return a Month:
6
SQL Code:
SELECT DAY(CURRENT_TIMESTAMP);
Return a Day:
22
There are many more functions available, including functions to extract milliseconds, names of the
months, names of each week day, etc. Each SQL platform varies in the actual naming of date
functions. Here's a few c\The following is a list of other date functions available to most platforms of
SQL with the exception of MS's SQL Server.
SQL Function Code:
SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)
SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)
SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)
SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)
SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)
SELECT MONTHNAME(CURRENT_TIMESTAMP);
- returns the month name (January - December
SELECT DAYNAME(CURRENT_TIMESTAMP);
- returns the name of the day (Sunday - Saturday)
SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)
Timestamps are often the easiest to work with, but we certainly are not limited to using only the
current_timestamp as our parameter. We can send any date, time, or timestamp to the function which
then returns our result.
SQL Code:
SELECT MONTHNAME('2004-11-27');
Return a Month Name:
MONTHNAME('2004-11-27')
November
Date functions can also be performed on table columns similarly to numeric and mathematical
functions such as SUM() or AVG().
SQL Code:
SELECT DAYOFYEAR(column_name) FROM table_name WHERE name = 'Joe';
SQL will return a numeric result from 1 - 365 representing the day of the year that Joe's record
was created/inserted.
We can expand this concept one step further with the use of a subquery. Say we have a table with
a column named timestamp. In this table column are timestamps of when each record was entered
and we would like to know what was the numeric day of the year that a record was entered.
SQL Code:
SELECT DAYOFYEAR(
(SELECT DATE(timestamp) FROM employees WHERE name = 'James Bond')
);
Above you can see how it is possible to combine several date functions as well as a subquery to
return very specific information about Mr. James Bond.
SQL - Inserting Date Data
Date data exists as numbers, strings, and timestamps. Built into most platforms are several date
column types such as DATE or TIMESTAMP. By setting the default value to the current date or
timestamp, the table column will automatically be filled with a current date/timestamp as each record is
inserted.
Here's the code to add a timestamp column to an existing table.
SQL Code:
ALTER TABLE `orders` ADD `order_date`
TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
Now each time an order is placed in our make believe business, a timestamp of that order is also
recorded.
A date or timestamp table column will only allow date data types to be inserted as values so be
sure to convert any strings or numbers to dates and timestamps before trying to insert them.
SQL - Datepart()
Microsoft's SQL Server takes a little different approach to working with dates. It is still possible to
extract individual parts of a timestamp and several other functions also work as outlined in SQL - Date.
The difference is that SQL Server uses one main function as oppose to several different functions
ie the DATEPART() function. Datepart() requires two parameters, a part argument and a date
argument. By part we mean year, day of the year, day of the week, etc. Let's look at an example.
SQL Code:
SELECT DATEPART(week, '2005-12-31');
Return the Week Number:
53
Here we have successfully pulled the "week number" from existing date. SQL's CURRENT_DATE
function could be also be substituted or a string value representing the year ('Dec 31, 2005').
The following table lists the datepart() arguments that can be extracted from any given date, time,
or timestamp.
part
year
quarter
month
dayofyear
day
week
weekday
hour
minute
second
Description
returns a 4 digit year
returns a value 1-4 representing the year's quarter
returns the numeric equivalent month (1-12)
returns the day of the year (1-365)
returns the day of the month (1-31)
returns the number of the week (1-53)
returns the number of the day of the week (1-7)
returns the number of hours for a given time
returns the number of minutes from a given time
returns the number of seconds in a given time
Abbreviation(s)
yy,yyyy
qq,q
mm,m
dy,y
dd,d
wk,ww
dw
hh
mi,n
ss,s
millisecond returns the number of milliseconds in a given time ms
SQL - Case
In SQL case works with either the select or update clauses. It provides when-then-else
functionality (WHEN this_happens THEN do_this) also known as conditional statements.
SQL - CASE (select)
For this concept, visualize the following table.
employee_name
Ted
Terry
Trish
admin
0
0
1
As you can see, we have a list of a few employees and then an admin column with boolean
(true/false) values for their administration rights (admin). A zero means they are not an admin a 1
mean they are.
Using case logic, we can present this information in a better form.
SQL Code:
SELECT employee_name
CASE admin
WHEN 1 THEN 'yes'
ELSE 'no'
END 'admin'
FROM employees;
SQL Case:
employee_name
Ted
Terry
Trish
admin
no
no
yes
In short all we really did is replace 1's and 0's with the words 'yes' and 'no'.
SQL - Case (update)
Case functions additionally allow for the updating of records within your table. For example, we
could update the prices of items in our online store, but more importantly we could update very specific
records because of the conditional logic allowed by case.
Let's use the following table for this next example.
item
goldfish
guppy
blow fish
quantity
12
24
1
price
1.00
0.50
5.00
Let's say we wanted to have a sale to clean out some of our overstock. We'll go ahead and take
25% off of all our items that we currently have 20 or more of (>20). Then we'll take 10% off the items
that we have 10-20 of (between 10 and 20) and everything else we will discount only 5%.
SQL Code:
UPDATE inventory SET price = price *
CASE
WHEN quantity > 20 THEN 0.75
WHEN quantity BETWEEN 10 AND 20 THEN 0.90
ELSE 0.95
END;
SQL Case:
item
goldfish
guppy
blow fish
quantity
12
24
1
price
.9
0.375
4.75
Each price has automatically been reduced by the appropriate percentages.
SQL - Historical Information
In the 60's database software required the use of complex mainframe machines that were difficult
to maintain and run. Information technologists worked around the clock monitoring, updated, and
manually uncorrupting these machines.
Each mainframe ran different software from different manufacturers. IBM pulled ahead in software
development internationally with efforts of software aimed at database management. The problem was
that each mainframe ran a different type of "language".
Enter SQL. The new standard for any database program: Structured Query Language. SQL
bridged the barriers between mainframes and allowed large corporations to network their efforts. SQL
was introduced in the 1970's and quickly gained international popularity. SQL allows a programmer to
tell a program exactly what data to retrieve and how to display it.
SQL - Under Development
Software at the time was still underdeveloped and several continuous problems plagued database
storage. Transactions happening at the same time were often combined, mixed together, or even lost
altogether. For instance, say two individuals place make a deposit at exactly the same time from two
different locations, often times the software was unable to cope with this and their bank transactions
could be completely switched. Suzy deposited $10,000 while Joe withdrew $15. Because of the faulty
software it was not uncommon to see Joe's withdrawal post on Suzy's account while Suzy's deposit is
posted on Joe's account.
Along came Relational Database Management Systems (RDBMS). This software is still being
used and is quite powerful. Relational databases allow the developer to build relationships between
databases and tables. This provides tremendous opportunities for data management and is still the
favorite software used today. MySQL, SQL Server, DB2, and Oracle are all RDBMSs and each have a
substantial hold in the market share to this day.
SQL - Structure
The importance of building your tables correctly could be considered its own artform.
Psychologists have even gone as far to say that those with the ability to organize their thoughts and
concepts well, tend to be absolutely brilliant (ie Mozart, Einstein, DaVinci). A database is only slightly
different than the human brain and clear organization is a must, especially if many people will be using
the same database.
Designing the right database has a lot to do with what you need to accomplish. A database with
thousands and thousands or even millions of records requires a completely different solution than a
database that will only have a few hundred records. Think about the future and plan ahead.
SQL - Table Relationships
The key to a large database is having tables with material that can relate to one another. A great
example is the employee ID. Ever wonder or joke about being just another number to the company. A
lot of that has to do with the way they set up their database. A large company is going to have a
weekly payroll and a personal information table. They might be set up like the following:
personal_info
emp_id last_name first_name
0056
Mynce
George
0097
Jenkins
Fred
weekly_payroll
emp_id hours rateofpay
0056
37.5 27.00
0097
44.5 22.25
This set up proves useful in several ways. The tables are smaller, more compact, and precise
allowing for maximum access speeds with each query. We can also moderate who has access to each
table, meaning that our accountant can have access to the payroll table but not the personal
information table which will eliminate security risks.
Relationship tables are the choice for high-traffic databases. There are countless security
advantages, faster query returns, and complete view customization (more on this later).
SQL - Identifiers, Think SQL
As with any programming language, there are a few terms unique to that language.
An Identifier is essentially a name of a database, table, or table column. As the creator of the
database you are free to identify these objects as you please, we merely suggest you keep these
guidelines in mind when doing so.
Develop your own unique naming scheme. -- Use terms that relate to one another and will
be easy to recognize apart from your code.
Be conscious of how long your names become. -- Especially be aware when the time
comes to name your columns.
Avoid names without meaning. -- Develop a working scheme that also has meaning behind
the names.
Be consistent. -- If you capitalize one table name, capitalize each table name, if you choose
to use abbreviations make sure they do not have double meanings or ambiguous meaning.
Develop a clear, concise schema and stick to it as your database develops.
SQL - Literals
Literal is a term for data types such as strings, numbers, or boolean values in SQL. These values
are not named by you the programmer they just exist. Strings, numbers, and boolean values are
literals.
Literal Breakdown:
string literals
'This is a string value'
'5893 Moon River Dr.'
number literals
823
-4.5
3.387920
boolean literals
TRUE
FALSE
1
0
SQL - Keywords and Reserved Terms
A reserved term is a word used in the actual processing of data. A few lessons down the road you
will be introduced to terms called clauses. These clauses are commands issued to the SQL program
and usually involve some sort of data manipulation. For instance the clause "CREATE" is used to
create tables and databases therefore CREATE is a reserved term. Functions are also reserved terms.
A simple function is the SUM() function. This function will simply sum up all the numbers of one of your
table columns and return the result. Since SUM is used as a function by SQL it is a reserved term.
Reserved terms should not be used as identifiers.