Loading....
Coupon Accepted Successfully!

 

What's SQL ?

In 1971, IBM researchers created a simple non-procedural language called Structured English Query Language. or SEQUEL. This was based on Dr. Edgar F. (Ted) Codd's design of a relational model for data storage where he described a universal programming language for accessing databases.

In the late 80's ANSI and ISO (these are two organizations dealing with standards for a wide variety of things) came out with a standardized version called Structured Query Language or SQL. SQL is prounced as 'Sequel'. There have been several versions of SQL and the latest one is SQL-99. Though SQL-92 is the current universally adopted standard.

SQL is the language used to query all databases. It's simple to learn and appears to do very little but is the heart of a successful database application. Understanding SQL and using it efficiently is highly imperative in designing an efficient database application. The better your understanding of SQL the more versatile you'll be in getting information out of databases.

Onto SQL

There are four basic commands which are the workhorses for SQL and figure in almost all queries to a database.

INSERT - Insert Data
DELETE - Delete Data
SELECT - Pull Data
UPDATE - Change existing Data

 

As you can see SQL is like English.

Let's build a real world example database using MySQL and perform some SQL operations on it.

A database that practically anyone could use would be a Contacts database.

In our example we are going to create a database with the following fields:

  • FirstName
  • LastName
  • BirthDate
  • StreetAddress
  • City
  • State
  • Zip
  • Country
  • TelephoneHome
  • TelephoneWork
  • Email
  • CompanyName
  • Designation

First, lets decide how we are going to store this data in the database. For illustration purposes, we are going to keep this data in multiple tables.

This will let us exercise all of the SQL commands pertaining to retrieving data from multiple tables. Also we can separate different kinds of entities into different tables. So let's say you have thousands of friends and need to send a mass email to all of them, a SELECT statement (covered later) will look at only one table.

Well, we can keep the FirstName, LastName and BirthDate in one table.

Address related data in another.

Company Details in another.

Emails can be separated into another.

Telephones can be separated into another.

Let's build the database in MySQL.

Let's build the database in MySQL.

While building a database - you need to understand the concept of data types. Data types allow the user to define how data is stored in fields or cells within a database. It's a way to define how your data will actually exist. Whether it's a Date or a string consisting of 20 characters, an integer etc. When we build tables within a database we also define the contents of each field in each row in the table using a data type. It's imperative that you use only the data type that fits your needs and don't use a data type that reserves more memory than the data in the field actually requires.

Let's look at various Data Types under MySQL.

Let's look at various Data Types under MySQL.

Type

Size in bytes

Description

TINYINT (length)

1

Integer with unsigned range of 0-255 and a signed range from -128-127

SMALLINT (length)

2

Integer with unsigned range of 0-65535 and a signed range from -32768-32767

MEDIUMINT(length)

3

Integer with unsigned range of 0-16777215 and a signed range from -8388608-8388607

INT(length)

4

Integer with unsigned range of 0-429467295 and a signed range from -2147483648-2147483647

BIGINT(length)

8

Integer with unsigned range of 0-18446744 and a signed range from
-9223372036854775808-9223372036854775807

FLOAT(length, decimal)

4

Floating point number with max. value +/-3.402823466E38 and min.(non-zero) value +/11.175494351E-38

DOUBLEPRECISION(length, decimal)

8

Floating point number with max. value +/- -1.7976931348623157E308 and min. (non-zero) value +/-2.2250738585072014E-308

DECIMAL(length, decimal)

length

Floating point number with the range of the DOUBLE type that is stored as a CHAR field type.

TIMESTAMP(length)

4

YYYYMMDDHHMMSS or YYMMDDHHMMSS or YYYYMMDD, YYMMDD. A Timestamp value is updated each time the row changes value. A NULL value sets the field to the current time.

DATE

3

YYYY-MM-DD

TIME

3

HH:MM:DD

DATETIME

8

YYYY-MM-DD HH:MM:SS

YEAR

1

YYYY or YY

CHAR(length)

length

A fixed length text string where fields shorter than the assigned length are filled with trailing spaces.

VARCHAR(length)

length

A fixed length text string (255 Character Max) where unused trailing spaces are removed before storing.

TINYTEXT

length+1

A text field with max. length of 255 characters.

TINYBLOB

length+1

A binary field with max. length of 255 characters.

TEXT

length+1

64Kb of text

BLOB

length+1

64Kb of data

MEDIUMTEXT

length+3

16Mb of text

MEDIUMBLOB

length+3

16 Mb of data

LONGTEXT

length+4

4GB of text

LONGBLOB

length+4

4GB of data

ENUM

1,2

This field can contain one of a possible 65535 number of options. Ex: ENUM('abc','def','ghi')

SET

1-8

This type of field can contain any number of a set of predefined possible values.


The following examples will make things quite clear on declaring Data Types within SQL statements.

Steps in Creating the Database using MySQL

From the shell prompt (either in DOS or UNIX):

mysqladmin create contacts;

This will create an empty database called "contacts".

Now run the command line tool "mysql" and from the mysql prompt do the following:

mysql> use contacts;

(You'll get the response "Database changed")

The following commands entered into the MySQL prompt will create the tables in the database.

 

mysql> CREATE TABLE names (contact_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName CHAR(20), LastName CHAR(20), BirthDate DATE);

mysql> CREATE TABLE address(contact_id SMALLINT NOT NULL PRIMARY KEY, StreetAddress CHAR(50), City CHAR(20), State CHAR(20), Zip CHAR(15), Country CHAR(20));
mysql> CREATE TABLE telephones (contact_id SMALLINT NOT NULL PRIMARY KEY, TelephoneHome CHAR(20), TelephoneWork(20));
mysql> CREATE TABLE email (contact_id SMALLINT NOT NULL PRIMARY KEY, Email CHAR(20));
mysql> CREATE TABLE company_details (contact_id SMALLINT NOT NULL PRIMARY KEY, CompanyName CHAR(25), Designation CHAR(15));

 

 

 

Note: Here we assume that one person will have only one email address. Now if there were a situation where one person has multiple addresses, this design would be a problem. We'd need another field which would keep values that indicated to whom the email address belonged to. In this particular case email data ownership is indicated by the primary key. The same is true for telephones. We are assuming that one person has only one home telephone and one work telephone number. This need not be true. Similarly one person could work for multiple companies at the same time holding two different designation. In all these cases an extra field will solve the issue. For now however let's work with this small design.

 

 

KEYS

Keys

The relationships between columns located in different tables are usually described through the use of keys.

 

As you can see we have a PRIMARY KEY in each table. The Primary key serves as a mechanism to refer to other fields within the same row. In this case, the Primary key is used to identify a relationship between a row under consideration and the person whose name is located inside the 'names' table. We use the AUTO_INCREMENT statement only for the 'names' table as we need to use the generated contact_id number in all the other tables for identification of the rows.

This type of table design where one table establishes a relationship with several other tables is known as a 'one to many'relationship.

In a 'many to many' relationship we could have several Auto Incremented Primary Keys in various tables with several inter-relationships.

Foreign Key

Foreign Key: A foreign key is a field in a table which is also the Primary Key in another table. This is known commonly as 'referential integrity'.

Execute the following commands to see the newly created tables and their contents.

To see the tables inside the database:

 

mysql> SHOW TABLES;
+-----------------------+
| Tables in contacts |
+-----------------------+
| address |
| company_details |
| email |
| names |
| telephones |
+----------------------+
5 rows in set (0.00 sec)


To see the columns within a particular table:

 

mysql>SHOW COLUMNS FROM address;
+---------------+-------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges
|
+---------------+-------------+------+-----+---------+-------+---------------------------------+
| contact_id | smallint(6) | | PRI | 0 | | select,insert,update,references |
| StreetAddress | char(50) | YES | | NULL | | select,insert,update,references |
| City | char(20) | YES | | NULL | | select,insert,update,references |
| State | char(20) | YES | | NULL | | select,insert,update,references |
| Zip | char(10) | YES | | NULL | | select,insert,update,references |
| Country | char(20) | YES | | NULL | | select,insert,update,references |
+---------------+-------------+------+-----+---------+-------+------------------ ---------------+
6 rows in set (0.00 sec)

 

So we have the tables created and ready. Now we put in some data.
Let's start with the 'names' table as it uses a unique AUTO_INCREMENT field which in turn is used in the other tables.

Inserting data, one row at a time:

mysql> INSERT INTO names (FirstName, LastName, BirthDate) VALUES ('Yamila','Diaz ','1974-10-13');
Query OK, 1 row affected (0.00 sec)

 

Inserting multiple rows at a time:

mysql> INSERT INTO names (FirstName, LastName, BirthDate) VALUES ('Nikki','Taylor','1972-03-04'),('Tia','Carrera','1975-09-18');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

 

Let's see what the data looks like inside the table. We use the SELECT command for this.

mysql> SELECT * from NAMES;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 3 | Tia | Carrera | 1975-09-18 |
| 2 | Nikki | Taylor | 1972-03-04 |
| 1 | Yamila | Diaz | 1974-10-13 |
+------------+-----------+----------+------------+
3 rows in set (0.06 sec)

 

Try another handy command called 'DESCRIBE'.

 

mysql> DESCRIBE names;
+------------+-------------+------+-----+---------+----------------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges
|
+------------+-------------+------+-----+---------+----------------+---------------------------------+
| contact_id | smallint(6) | | PRI | NULL | auto_increment | select,insert,update,references |
| FirstName | char(20) | YES | | NULL | | select,insert,update,references |
| LastName | char(20) | YES | | NULL | | select,insert,update,references |
| BirthDate | date | YES | | NULL | | select,insert,update,references |
+------------+-------------+------+-----+---------+----------------+---------------------------------+
4 rows in set (0.00 sec)

 

Now lets populate the other tables. Observer the syntax used.

 

mysql> INSERT INTO address(contact_id, StreetAddress, City, State, Zip, Country) VALUES ('1', '300 Yamila Ave.', 'Los Angeles', 'CA', '300012', 'USA'),('2','4000 Nikki St.','Boca Raton','FL','500034','USA'),('3','404 Tia Blvd.','New York','NY','10011','USA');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM address;
+------------+-----------------+-------------+-------+--------+---------+
| contact_id | StreetAddress | City | State | Zip | Country |
+------------+-----------------+-------------+-------+--------+---------+
| 1 | 300 Yamila Ave. | Los Angeles | CA | 300012 | USA |
| 2 | 4000 Nikki St. | Boca Raton | FL | 500034 | USA |
| 3 | 404 Tia Blvd. | New York | NY | 10011 | USA |
+------------+-----------------+-------------+-------+--------+---------+
3 rows in set (0.00 sec)
mysql> INSERT INTO company_details (contact_id, CompanyName, Designation) VALUES ('1','Xerox','New Business Manager'), ('2','Cabletron','Customer Support Eng'),('3','Apple','Sales Manager');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM company_details;
+------------+-------------+----------------------+
| contact_id | CompanyName | Designation |
+------------+-------------+----------------------+
| 1 | Xerox | New Business Manager |
| 2 | Cabletron | Customer Support Eng |
| 3 | Apple | Sales Manager |
+------------+-------------+----------------------+
3 rows in set (0.06 sec)
mysql> INSERT INTO email (contact_id, Email) VALUES ('1', [email protected]'),( '2', [email protected]'),('3',[email protected]');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM email;
+------------+-------------------+
| contact_id | Email |
+------------+-------------------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+------------+-------------------+
3 rows in set (0.06 sec)
mysql> INSERT INTO telephones (contact_id, TelephoneHome, TelephoneWork) VALUES ('1','333-50000','333-60000'),('2','444-70000','444-80000'),('3','555-30000','55 5-40000');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM telephones;
+------------+---------------+---------------+
| contact_id | TelephoneHome | TelephoneWork |
+------------+---------------+---------------+
| 1 | 333-50000 | 333-60000 |
| 2 | 444-70000 | 444-80000 |
| 3 | 555-30000 | 555-40000 |
+------------+---------------+---------------+
3 rows in set (0.00 sec)
Okay, so we now have all our data ready for experimentation.

 

 

Before we start experimenting with manipulating the data let's look at how MySQL stores the Data.

To do this execute the following command from the shell prompt.

mysqldump contacts > contacts.sql
 

Note: The reverse operation for this command is:
mysql contacts < contacts.sql
The file generated is a text file that contains all the data and SQL instruction needed to recreate the same database. As you can see, the SQL here is slightly different than what was typed in. Don't worry about this. It's all good ! It would also be obvious that this is a good way to backup your stuff.

 

Example-1

# MySQL dump 8.2
#
# Host: localhost Database: contacts
#--------------------------------------------------------
# Server version 3.22.34-shareware-debug

#
# Table structure for table 'address'
#
CREATE TABLE address (
contact_id smallint(6) DEFAULT '0' NOT NULL,
StreetAddress char(50),
City char(20),
State char(20),
Zip char(10),
Country char(20),
PRIMARY KEY (contact_id)
);
#
# Dumping data for table 'address'
#
INSERT INTO address VALUES (1,'300 Yamila Ave.','Los Angeles','CA','300012','USA');
INSERT INTO address VALUES (2,'4000 Nikki St.','Boca Raton','FL','500034','USA');
INSERT INTO address VALUES (3,'404 Tia Blvd.','New York','NY','10011','USA');

 

 

Example-2
#
# Table structure for table 'company_details'
#

CREATE TABLE company_details (
contact_id smallint(6) DEFAULT '0' NOT NULL,
CompanyName char(25),
Designation char(20),
PRIMARY KEY (contact_id)
);
#
# Dumping data for table 'company_details'
#
INSERT INTO company_details VALUES (1,'Xerox','New Business Manager');
INSERT INTO company_details VALUES (2,'Cabletron','Customer Support Eng');
INSERT INTO company_details VALUES (3,'Apple','Sales Manager')
;

 

 

Example -3
#
# Table structure for table 'email'
#

CREATE TABLE email (
contact_id smallint(6) DEFAULT '0' NOT NULL,
Email char(20),
PRIMARY KEY (contact_id)
);
#
# Dumping data for table 'email'
#
INSERT INTO email VALUES (1,[email protected]');
INSERT INTO email VALUES (2,[email protected]');
INSERT INTO email VALUES (3,[email protected]');

 

 

#
# Table structure for table 'names'
#

CREATE TABLE names (
contact_id smallint(6) DEFAULT '0' NOT NULL auto_increment,
FirstName char(20),
LastName char(20),
BirthDate date,
PRIMARY KEY (contact_id)
);
#
# Dumping data for table 'names'
#
INSERT INTO names VALUES (3,'Tia','Carrera','1975-09-18');
INSERT INTO names VALUES (2,'Nikki','Taylor','1972-03-04');
INSERT INTO names VALUES (1,'Yamila','Diaz','1974-10-13');

 

 

#
# Table structure for table 'telephones'
#

CREATE TABLE telephones (
contact_id smallint(6) DEFAULT '0' NOT NULL,
TelephoneHome char(20),
TelephoneWork char(20),
PRIMARY KEY (contact_id)
);
#
# Dumping data for table 'telephones'
#
INSERT INTO telephones VALUES (1,'333-50000','333-60000');
INSERT INTO telephones VALUES (2,'444-70000','444-80000');
INSERT INTO telephones VALUES (3,'555-30000','555-40000');

 

SELECT Query

SELECT Query

Let's try some SELECT statement variations:

 

Example-
To select all names whose corresponding contact_id is greater than 1.

mysql> SELECT * FROM names WHERE contact_id > 1;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 3 | Tia | Carrera | 1975-09-18 |
| 2 | Nikki | Taylor | 1972-03-04 |
+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

 

As a condition we can also use NOT NULL. This statement will return all names where there exists a contact_id.

mysql> SELECT * FROM names WHERE contact_id IS NOT NULL;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 3 | Tia | Carrera | 1975-09-18 |
| 2 | Nikki | Taylor | 1972-03-04 |
| 1 | Yamila | Diaz | 1974-10-13 |
+------------+-----------+----------+------------+
3 rows in set (0.06 sec)

 

 

ORDER BY

Result's can be arranged in a particular way using the statement ORDER BY.

mysql> SELECT * FROM names WHERE contact_id IS NOT NULL ORDER BY LastName;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 3 | Tia | Carrera | 1975-09-18 |
| 1 | Yamila | Diaz | 1974-10-13 |
| 2 | Nikki | Taylor | 1972-03-04 |
+------------+-----------+----------+------------+
3 rows in set (0.06 sec)

'asc' and 'desc' stand for ascending and descending respectively and can be used to arrange the results.

 

 

mysql> SELECT * FROM names WHERE contact_id IS NOT NULL ORDER BY LastName desc;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 2 | Nikki | Taylor | 1972-03-04 |
| 1 | Yamila | Diaz | 1974-10-13 |
| 3 | Tia | Carrera | 1975-09-18 |
+------------+-----------+----------+------------+
3 rows in set (0.04 sec)

 

 

Conditional statements

You can also place date types into conditional statements.

mysql> SELECT * FROM names WHERE BirthDate > '1973-03-06';
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 3 | Tia | Carrera | 1975-09-18 |
| 1 | Yamila | Diaz | 1974-10-13 |
+------------+-----------+----------+------------+

2 rows in set (0.00 sec)

 

LIKE

LIKE is a statement to match field values using wildcards. The % sign is used for denoting wildcards and can represent multiple characters.

mysql> SELECT FirstName, LastName FROM names WHERE LastName LIKE 'C%';
+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Tia | Carrera |
+-----------+----------+
1 row in set (0.06 sec)

'_' is used to represent a single wildcard.
mysql> SELECT FirstName, LastName FROM names WHERE LastName LIKE '_iaz';
+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Yamila | Diaz |
+-----------+----------+
1 row in set (0.00 sec)

 

SQL Logical Operations

SQL Logical Operations (operates from Left to Right)

 

1.NOT or !

2. AND or &&
3. OR or ||
4. = : Equal
5. <> or != : Not Equal
6. <=
7. >=
8 <,>

 

Here are some more variations with Logical Operators and using the 'IN' statement.

 

mysql> SELECT FirstName FROM names WHERE contact_id < 3 AND LastName LIKE 'D%';
+-----------+
| FirstName |
+-----------+
| Yamila |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT contact_id FROM names WHERE LastName IN ('Diaz','Carrera');
+------------+
| contact_id |
+------------+
| 3 |
| 1 |
+------------+
2 rows in set (0.02 sec)
To return the number of rows in a table
mysql> SELECT count(*) FROM names;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.02 sec)
mysql> SELECT count(FirstName) FROM names;
+------------------+
| count(FirstName) |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
To do some basic arithmetic aggregate functions.
mysql> SELECT SUM(contact_id) FROM names;
+-----------------+
| SUM(contact_id) |
+-----------------+
| 6 |
+-----------------+
1 row in set (0.00 sec)
To select a largest value from a row. Substitute 'MIN' and see what happens next.
mysql> SELECT MAX(contact_id) FROM names;
+-----------------+
| MAX(contact_id) |
+-----------------+
| 3 |
+-----------------+
1 row in set (0.00 sec)

 

HAVING

HAVING

Take a look at the first query using the statement WHERE and the second statement using the statement HAVING.

 

mysql> SELECT * FROM names WHERE contact_id >=1;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 1 | Yamila | Diaz | 1974-10-13 |
| 2 | Nikki | Taylor | 1972-03-04 |
| 3 | Tia | Carrera | 1975-09-18 |
+------------+-----------+----------+------------+
3 rows in set (0.03 sec)

mysql> SELECT * FROM names HAVING contact_id >=1;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 3 | Tia | Carrera | 1975-09-18 |
| 2 | Nikki | Taylor | 1972-03-04 |
| 1 | Yamila | Diaz | 1974-10-13 |
+------------+-----------+----------+------------+
3 rows in set (0.00 sec)

 





Test Your Skills Now!
Take a Quiz now
Reviewer Name