Learning SQL – Part 1 – Tugrul's Adventures with Data & R

Sql stands for Structured Query Language and is being used to query and manipulate relational databases. Most of the Relational Database Management Systems use SQL as standard database language. I will be using MS SQL in these examples and learning process.

Sql History

Dr Edgar F. Codd is known as the “Codd Father” of the relational databases. He described a relational model for databases in 1970. First SQL appeared in 1974 and IBM has worked to develop the ideas of Codd and released a product System/R. In 1986, IBM developed first prototype of relational database and it was standardized by ANSI.

SELECT statements

Capabilities of SELECT statements

SELECT statements can give us a projection, we can get a subset of a column. Secondly, you can filter the number of rows with SELECT and also you can join different tables by primary and foreign keys. It allows to get data from different tables and show as a table.

Basis SELECT statement identifies the columns o be displayed and you also need to add FROM to tell which tables you will get the data from.

If you want to select all columns you just need to put in “*” after SELECT, i.e. SELECT * FROM result.

CREATE TABLE Trainee
(
TraineeID int  NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
DOB date NOT NULL,
CONSTRAINT pkTrainee PRIMARY KEY (TraineeID)
);


CREATE TABLE ModuleLength
(
LevelNo int NOT NULL,
ModuleDuration int NOT NULL,
CONSTRAINT pkModuleLength PRIMARY KEY (LevelNo)
);


CREATE TABLE Module
(
ModuleID int NOT NULL,
ModuleName varchar(50) NOT NULL,
LevelNo int NOT NULL,
CONSTRAINT pkModule PRIMARY KEY (ModuleID),
CONSTRAINT fkModule_ModuleLength FOREIGN KEY(LevelNo) REFERENCES ModuleLength(LevelNo)
);


CREATE TABLE Result
(
ModuleID int NOT NULL,
TraineeID int NOT NULL,
CompletionDate date NULL,
Result varchar(50) NOT NULL,
CONSTRAINT fkResult_Module FOREIGN KEY(ModuleID) REFERENCES Module(ModuleID),
CONSTRAINT fkResult_Trainee FOREIGN KEY(TraineeID) REFERENCES Trainee(TraineeID)
);


INSERT INTO dbo.ModuleLength
(LevelNo, ModuleDuration)
VALUES
(1, 30),
(2, 60),
(3, 100);


INSERT INTO Module
(ModuleID, ModuleName, LevelNo)
VALUES
(100, 'Learning to Juggle', 1),
(102, 'Fire eating for beginners', 1),
(200, 'Acrobatic dancing', 2),
(210, 'Flying Trapeze', 2),
(250, 'Static Trapeze', 2),
(330, 'Advanced Juggling', 3),
(345, 'Static Trapeze artist', 3);


INSERT INTO Trainee
(TraineeID, FirstName, LastName, DOB)
VALUES
(800110, 'James', 'Allen', '1986/04/17'),
(800123, 'Benjamin', 'Boateng', '1986/06/25'),
(800140, 'Tatiana', 'Jones', '1987/03/02'),
(800145, 'Selima', 'Khan', '1987/10/09');


INSERT INTO Result
(ModuleID, TraineeID, CompletionDate, Result)
VALUES
(100, 800123, '10/10/04', 'Pass'),
(102, 800145, '04/07/02', 'Merit'),
(200, 800123, '04/06/23', 'Pass'),
(200, 800110, '04/03/05', 'Pass'),
(250, 800145, '04/03/12', 'Merit'),
(250, 800123, '04/03/05', 'Pass'),
(330, 800140, '04/10/10', 'Merit'),
(345, 800140, '04/03/05', 'Distinction');

CREATE TABLE Trainee

(

TraineeID int NOT NULL,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NOT NULL,

DOB date NOT NULL,

CONSTRAINT pkTrainee PRIMARY KEY (TraineeID)

);

CREATE TABLE ModuleLength

(

LevelNo int NOT NULL,

ModuleDuration int NOT NULL,

CONSTRAINT pkModuleLength PRIMARY KEY (LevelNo)

);

CREATE TABLE Module

(

ModuleID int NOT NULL,

ModuleName varchar(50) NOT NULL,

LevelNo int NOT NULL,

CONSTRAINT pkModule PRIMARY KEY (ModuleID),

CONSTRAINT fkModule_ModuleLength FOREIGN KEY(LevelNo) REFERENCES ModuleLength(LevelNo)

);

CREATE TABLE Result

(

ModuleID int NOT NULL,

TraineeID int NOT NULL,

CompletionDate date NULL,

Result varchar(50) NOT NULL,

CONSTRAINT fkResult_Module FOREIGN KEY(ModuleID) REFERENCES Module(ModuleID),

CONSTRAINT fkResult_Trainee FOREIGN KEY(TraineeID) REFERENCES Trainee(TraineeID)

);

INSERT INTO dbo.ModuleLength

(LevelNo, ModuleDuration)

VALUES

(1, 30),

(2, 60),

(3, 100);

INSERT INTO Module

(ModuleID, ModuleName, LevelNo)

VALUES

(100, 'Learning to Juggle', 1),

(102, 'Fire eating for beginners', 1),

(200, 'Acrobatic dancing', 2),

(210, 'Flying Trapeze', 2),

(250, 'Static Trapeze', 2),

(330, 'Advanced Juggling', 3),

(345, 'Static Trapeze artist', 3);

INSERT INTO Trainee

(TraineeID, FirstName, LastName, DOB)

VALUES

(800110, 'James', 'Allen', '1986/04/17'),

(800123, 'Benjamin', 'Boateng', '1986/06/25'),

(800140, 'Tatiana', 'Jones', '1987/03/02'),

(800145, 'Selima', 'Khan', '1987/10/09');

INSERT INTO Result

(ModuleID, TraineeID, CompletionDate, Result)

VALUES

(100, 800123, '10/10/04', 'Pass'),

(102, 800145, '04/07/02', 'Merit'),

(200, 800123, '04/06/23', 'Pass'),

(200, 800110, '04/03/05', 'Pass'),

(250, 800145, '04/03/12', 'Merit'),

(250, 800123, '04/03/05', 'Pass'),

(330, 800140, '04/10/10', 'Merit'),

(345, 800140, '04/03/05', 'Distinction');

You can use IDENTITY to create an auto increment, it is very useful for Primary Key. If you type in IDENTITY(1,1) your column will start with “1” and it will increase by 1 each time you enter a new value.

You can see all tables created with this code;

select * from result;
select * from modulelength;
select * from trainee;
select * from module;

select * from result;

select * from modulelength;

select * from trainee;

select * from module;

You can also get subsets from your tables. That is the projection part of SELECT statement.

SELECT TraineeID, CompletionDate, Result
FROM Result;

1 2	SELECT TraineeID, CompletionDate, Result FROM Result;

This will show you below table in SQL Server Management Studio. You can change the order of columns by changing the order in SELECT statement

SQL Statements – Things To Remember

SQL statements are not case sensitive.
SQL statements can be entered on one or more
Keywords cannot be abbreviated or split across
Clauses are usually placed on separate
Indents are used to enhance
In SQL Developer, SQL statements can be optionally terminated by a semicolon (;). Semicolons are required when you execute multiple SQL
In SQL*Plus, you are required to end each SQL statement with a semicolon (;).

Arithmetic Operators in SQL

You can do any arithmetic operations in SQL such as +,-,*,/.

Also you can add values to a column as a new column. You can give this column a name by adding AS to the SELECT statement.

SELECT modulename, levelno, 
levelno +3 as Plus3,
levelno -3 as Minus3,
levelno /3 as Divide3,
levelno *3 as Multiple3
FROM module;

SELECT modulename, levelno,

levelno +3 as Plus3,

levelno -3 as Minus3,

levelno /3 as Divide3,

levelno *3 as Multiple3

FROM module;

It is better to check your outputs before getting your reports/tables. In below case although we want to get 48 we will get a result of 15. If we add () after 12 the problem will be solved. Be careful with the calculations you do.

NULL Values

NULL values are different than having “0” in the field. Zero means there is a value but it is zero, null means there is no value in that row/column. Below visualization explains the difference between zero and NULL perfectly.

toilet paper visualization of the difference between zero and null pic.twitter.com/ku3BBRjjUY

— Jonathan Nelson (@iamelgringo) March 23, 2017

While writing queries, you need to be careful about NULL values. If there is a NULL value while querying a database you will get a different answer than expected answer. As NULL means undefined value, SQL will perpetrate that differently and you will get below answers in a Boolean way.

DISTINCT

DISTINCT gives you the first occurrence of a record in the table and will remove the duplicates in the result. You can use DISTINCT in SELECT statement after SELECT keyword.

As in below example; if you just SELECT moduleid FROM result, you will get 8 rows including duplicates. Using DISTINCT will give you the 6 distinct values by removing duplicates.

You can also use DISTINCT in different functions such as COUNT and can count the results without duplicates.

select distinct moduleid from result;

select count( distinct moduleid) as countmodule from result;

select distinct moduleid from result;

select count( distinct moduleid) as countmodule from result;

CONCATENATE

You can use CONCAT statement to concatenate different values from different columns and add strings to the result. Let’s create table called employees with below data in it;

-- create employee table and insert 3 records
create table employee(
emp_id int identity(1,1) not null,
emp_name varchar(20) not null,
man_id int,
);

insert into employee
values
('Darren', NULL),
('Keith', 1),
('Paola', 2);

-- create employee table and insert 3 records

create table employee(

emp_id int identity(1,1) not null,

emp_name varchar(20) not null,

man_id int,

);

insert into employee

values

('Darren', NULL),

('Keith', 1),

('Paola', 2);

After creating this table we can use CONCAT to create a sentence which gives us the employee ID of each employee.

-- selec emp_id is the id of 
select CONCAT(emp_id, ' is the id of ', emp_name)
AS "ID and Name"
from employee

-- selec emp_id is the id of

select CONCAT(emp_id, ' is the id of ', emp_name)

AS "ID and Name"

from employee

So, that wraps up the first part of Learning SQL, where we created tables, did arithmetic operations, concatenate, get unique/distinct values, and also learnt what are the NULL values and what you need to be careful about them.

Tugrul's Adventures with Data & R

Blog for Data Management & Analytics

Learning SQL – Part 1

Sql History

SELECT statements

Capabilities of SELECT statements

SQL Statements – Things To Remember

Arithmetic Operators in SQL

Leave a Reply Cancel reply