To visit my site

HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#


To contact us

© 2002 - 2015 All Rights Reserved Total Application Works



  • Tell a friend about this site (copy and paste the following HTML page into a file (e.g., using Notepad).)
  • <html>
    <head>
    <title>Example of a link </title>
    </head>
    <body>
    <a href="http://sumtotalz.com/TotalAppsWorks/SQL/MY_SQL_INTRO.html">
    Lists and their Tags </a>
    </table>
    </body>
    </html>



SQL Introduction


Objectives

The objective of this tutorial is to briefly describe the SQL commands and present examples that show how to use them.

Introduction

The purpose of this tutorial is to introduce the reader to the basics of SQL (pronounced like the word sequel: see-quele). SQL stands for Structured Query Language In this tutorial, we will cover the following aspects of the SQL language:

  1. DQL - Data Query Language
    • SELECT
  2. DML - Data Manipulation Language
    • DELETE
    • INSERT
    • UPDATE
  3. DDL - Data Definition Language
    • ALTER INDEX
    • ALTER TABLE
    • CREATE INDEX
    • CREATE TABLE
    • DROP INDEX
    • DROP TABLE
Before we delve into the SQL commands, we need to understand the purpose of SQL. We will start this understanding in the next section.

What is SQL?

SQL commands are used for accessing/manipulating data stored in Relational Database Management Systems (RDBMS). The SQL commands allows the means by which data can be

  • deleted
  • read
  • stored
  • sorted
  • updated
and tables can be created, etc. SQL can be used with any SQL compliant RDBMS (Relational Database Management System), such as MS SQL Server, Microsoft Access, Oracle, DB2, and MySQL.

What is a RDBMS?

A database management system (DBMS) is a way to store data such that a computer program can easily retrieved it. Traditional databases are organized into rows of related data. These rows are also called records. A RDBMS is a type of database management system (DBMS) that stores data in related tables.

What is a Table?

A table consists of two-dimensional sets of related data. In this case, a set is a row of data, where the data in that row is related. For example, a row might consist of the Visa account information for John Doe.

Account
Number
First
Name
Last
Name
Address City State Zip Country Birth
Date
Telephone Email
Address
Balance
1234567889 John Doe 123 Main St. Seattle WA 98101 USA 07/04/1978 206-555-0987 MyName@MyISP 5,987.95
Figure 1: Depiction of a table


Each row consists of columns or fields, e.g., FirstName. A more generic representation of a table of rows and columns/fields might be shown like the following.

Row Column 1 Column 2 Column 3 Column ... Column n
Row 1 Item 1 1 Item 1 2 Item 1 3 Item ... Item 1 n
Row 2 Item 2 1 Item 2 2 Item 2 3 Item ... Item 2 n
Row 3 Item 3 1 Item 3 2 Item 3 3 Item ... Item 3 n
... ... ... ... ... ...
Row n Item n 1 Item n 2 Item n 3 Item ... Item n n
Figure 2: Depiction of table consisting of rows


What is a Primary Key?

A primary key is a field/column or set of fields/columns that uniquely identifies the rest of the data in any given row. An example of a field that can be used that is unique is a social security number or an employee ID number. No two rows can have the same values for their primary key fields/columns.

What is a Foreign Key?

A foreign key is a column in a table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the other table where that column is the primary key. A foreign key field is a field that links one table to another table's primary or foreign key. A table can contain many foreign keys. The number is based on the number of tables that are related to it.

  1. Foreign keys in one table map to primary keys in another table.
  2. If the linked table has a composite primary key, you’ll have one-to-one mapping of foreign key fields to each primary key field in the linked table.
  3. Finally, your foreign key fields and the primary key fields should have the same name. For documentation purposes, this is the best approach (though neither a requirement nor always possible to accomplish).
        Table CUSTOMER                  
column name characteristic
SOCIAL SECURITY No. Primary Key
Last_Name  
First_Name  
Address  
City  
State  
Zip Code  
Telephone  
 



<----+
     |
     |
     +-->
        Table ORDERS            
column name characteristic
Order_ID Primary Key
Order_Date  
Customer_SOC_SECUR_No. Foreign Key
Amount  
Figure 3: Primary Key vs. Foreign Key

What is a One-to-one relationship?

A One-to-one relationship means that you have a primary key field/column in Table_1 that is related to a foreign key field/column in Table_2, and that for every primary key value, there is one foreign key value. One-to-one relationships occur when there is exactly one record in the first table that corresponds to exactly one record in the related table. This type of relationship can occur in a department that has one manager and one employee. Another instance occurs when a social security number is a primary key in Table_1 and a foreign key in Table_2.

 
   Manager                     Employee
   Table                         Table
+------------+               +------------+
| Manager 1  | <-----------> | Employee 1 |  
+------------+               +------------+
 
Figure 4: One-to-one relationship

What is a One-to-many relationship?

One-to-many relationships occur when each record in Table_1 may have many linked records in Table_2 but each record in Table_2 may have only one corresponding record in Table_1. This type of relationship exists when one person has multiple pets, but the pets have only one owner. In business, this relationship occurs when a department manager is responsible for several employees.

 
   Manager                     Employee
   Table                         Table
+------------+               +------------+
| Manager 1  | <------+----> | Employee 1 |  
+------------+        |      +------------+
                      +----> | Employee 2 | 
                      |      +------------+
                      +----> | Employee 3 | 
                      |      +------------+
                      +----> | Employee...|  
                      |      +------------+ 
                      +----> | Employee n | 
                             +------------+
 
Figure 5: One-to-many relationship

What is a many-to-many relationship?

What is a many-to-many relationship? A many-to-many relationship means that a field/column in Table_1 has a one-to-many relationship to fields/columns in Table_2, and a field/column in Table_2 has a one-to-many relationship to fields/columns in Table_1. Consider the relationships depicted in Figure 5 below.

 
   Manager                     Employee
   Table                         Table
                              Department JA8
+------------+               +------------+
| Manager 5  | <------+----> | Employee 1 |  
+------------+        |      +------------+
| Manager 4  | <------+----> | Employee 2 | 
+------------+        |      +------------+
| Manager 3  | <------+----> | Employee 3 | 
+------------+        |      +------------+
| Manager 2  | <------+----> | Employee...|  
+------------+        |      +------------+ 
| Manager 1  | <------+----> | Employee n | 
+------------+               +------------+
 
Figure 5: Many-to-many relationship


In a hierarchical management organization, one employee will have several levels of management in his direct chain, and one manager may have several employees that are in his chain of responsibility. Another example is the military and the chain of command. A general may have several people reporting to him/her, and a Private may have several levels of command that he reports to before reaching the general. In Figure 5 above, employee N has a relation with every manager in his management chain, where Manager 1 is his direct manager, Manager 2 is his second line manager, and Manager 5 is his fifth line manager.

How is Data in a Table Queried?

The SELECT statement is arguably the most used command used to retrieve data from a database. To retrieve all of the columns/fields from a table, use the following format.

  
SELECT * FROM Table_name
 

I will be using Java and Microsoft Access to develop this tutorial.

Summary

The purpose of this introduction was to present a brief overview of:

  1. RDBMS databases;
  2. Tables
  3. Primary Keys
  4. Foreign Keys
  5. One-to-one relationships
  6. One-to-many relationships
  7. Many-to-many relationships
  8. SQL
  9. Table Queries
In the following tutorials, we will explore these topics in more detail.












[
HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#

© 2002 - 2015 All Rights Reserved Total Application Works