sql

Introduction to SQL Basics

In this article, we will make an introduction to SQL Basics.

1. Introduction

Structured Query Language (SQL) is a standardized database language that is used to create, maintain, and retrieve data from a relational database. SQL was initially developed at IBM San Jose Research Laboratory in early 1970. IBM released the first Relational Database Management System (RDBMS) – Multics_Relational_Data_Store in June 1976. By 1986, American National Standards Institute (ANSI) and International Organization for Standardization (ISO) standard groups officially adopted the standard “Database Language SQL” language definition.

In this example, I will show the following basics SQL commands based on the ISO standard:

  • Data Definition Language – CREATE TABLE
  • Data Manipulation Language – INSERT
  • Data Query Language – SELECT

2. Technologies Used

The example code in this article was built and run using:

  • MySQL
  • SQL

3. RDBMS

A relational database management system (RDBMS) stores data in a row-based table structure that connects related data elements. MySQL, Oracle, Sybase, SQL Server, and Postgre are common RDBMS. In this step, I will create an entity-relationship diagram that outlines two tables and their relationship.

sql basics - er diagram
Figure 1 ER Diagram
  • User_tbl has 1-to-Many relationship to Phone_tbl. A single record in User_tbl can have zero or many phone records.
  • The user_id column is a foreign key which references user_tbl.

4. Data Definition Language

Data Definition Language(DDL) is used to define the structure of the database. In this step, I will create two tables and define the relationship between them.

ddl-create table.sql

create table user_tbl(
   user_id INT NOT NULL AUTO_INCREMENT,
   user_fname VARCHAR(100) NOT NULL,
   user_lname VARCHAR(40) NOT NULL,
   PRIMARY KEY ( user_id )
);

create table phone_tbl(
   phone_id INT NOT NULL AUTO_INCREMENT,
   user_id  INT NOT NULL,
   phone_number VARCHAR(40) NOT NULL,
   PRIMARY KEY ( phone_id ),
   FOREIGN KEY (user_id  ) REFERENCES  user_tbl(user_id)
);
  • Line 5 – user_id is the primary key of user_tbl.
  • line 13 – user_id is the foreign key of phone_tbl.

5. Data Manipulation Language

Data Manipulation Language(DML) is used to manipulate data in the relations. In this example, I will use the INSERT command to save records.

dml-insert.sql

insert into user_tbl(user_fname,user_lname) values('Mary','Zheng');
insert into user_tbl(user_fname, user_lname) values('Tom2','Zheng');
insert into user_tbl(user_fname, user_lname) values('Jerry2','Cheng');
insert into user_tbl(user_fname, user_lname) values('MARY','ZHENG');
insert into user_tbl(user_fname, user_lname) values('TOM2','ZHENG');
insert into user_tbl(user_fname, user_lname) values('JERRY2','CHENG');
insert into phone_tbl(user_id, phone_number) values(1, '314-123-4567');
insert into phone_tbl(user_id, phone_number) values(2, '636-213-5673');
insert into phone_tbl(user_id, phone_number) values(3, '404-132-5734');

6. Data Query Language

Data Query Language (DQL) is used to extract the data from the relations. In this example, I will use the SELECT command to retrieve data.

This returns all data from user_tbl.

select *

mysql> select * from user_tbl;
+---------+------------+------------+
| user_id | user_fname | user_lname |
+---------+------------+------------+
|       1 | Mary       | Zheng      |
|       2 | Tom2       | Zheng      |
|       3 | Jerry2     | Cheng      |
|       4 | MARY       | ZHENG      |
|       5 | TOM2       | ZHENG      |
|       6 | JERRY2     | CHENG      |
+---------+------------+------------+
6 rows in set (0.00 sec)

This returns unique user_lname from user_tbl.

select distinct

select distinct user_lname from user_tbl;
+------------+
| user_lname |
+------------+
| Zheng      |
| Cheng      |
+------------+
2 rows in set (0.01 sec)

This returns all data from both user_tbl and phone_tbl with equal join at the user_id column.

select * from two tables

mysql> select * from user_tbl u, phone_tbl p
    -> where u.user_id = p.user_id;
+---------+------------+------------+----------+---------+--------------+
| user_id | user_fname | user_lname | phone_id | user_id | phone_number |
+---------+------------+------------+----------+---------+--------------+
|       1 | Mary       | Zheng      |        1 |       1 | 314-123-4567 |
|       2 | Tom2       | Zheng      |        2 |       2 | 636-213-5673 |
|       3 | Jerry2     | Cheng      |        3 |       3 | 404-132-5734 |
+---------+------------+------------+----------+---------+--------------+
3 rows in set (0.00 sec)

This returns all data from both user_tbl and phone_tbl with left join at the user_id column.

select * with left join

mysql>  select * from user_tbl u left join phone_tbl p on  u.user_id  = p.user_id;
+---------+------------+------------+----------+---------+--------------+
| user_id | user_fname | user_lname | phone_id | user_id | phone_number |
+---------+------------+------------+----------+---------+--------------+
|       1 | Mary       | Zheng      |        1 |       1 | 314-123-4567 |
|       2 | Tom2       | Zheng      |        2 |       2 | 636-213-5673 |
|       3 | Jerry2     | Cheng      |        3 |       3 | 404-132-5734 |
|       4 | MARY       | ZHENG      |     NULL |    NULL | NULL         |
|       5 | TOM2       | ZHENG      |     NULL |    NULL | NULL         |
|       6 | JERRY2     | CHENG      |     NULL |    NULL | NULL         |
+---------+------------+------------+----------+---------+--------------+
6 rows in set (0.00 sec)

7. Summary

In this example, I showed basic SQL commands which manage data in a relational database. DDL commands are executed by a database administrator (DBA) usually. DML and DQL commands are executed by an application developer.

8. Download the Source Code

Download
You can download the full source code of this example here: Introduction to SQL Basics

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button