SQL Tutorial

8 min readMay 10, 2022

SQL tutorial

Introduction

SQL is a standard language for storing, manipulating, and retrieving data in database.SQL stands for the structured query language.

Download and Install MySQL workbench to get started. You can search for other resources for installation.

Uses

Execute queries against the database.
Retrieve data from the database.
Insert, update and delete records in a database.
Create new databases.
Create new tables in a database.
Create stored procedures in a database.
Create views in a database.
Set permissions on tables, procedures, and views.

To build a website that shows data from a database, you need:

RDBMS program(MS Access, SQL Server, and MYSQL)
Use SQL to get the data you want

RDBMS

Relational Database Management System which provides an interface between users and applications and database. It is the basis of SQL and all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. It stores its data in database objects called tables.

A table is a collection of data entries and consists of rows and columns. A table is broken down into smaller entities called fields(columns).

A field is a column in a table designed to maintain specific information about every record in the table.

A record(row) is each entry in a table.

A database contains one or more tables.

Advantage of SQL

No programming needed
High-Speed Query Processing
Standardized Language
Portability

Get Started with examples

Alias

It’s used to give a table or column a temporary name making them more readable. It only exist during the duration of the query. An alias is created with AS keyword.

syntax

SELECT column_name AS alias_name
  FROM table_name;

Step 1: SetUp

Toggle over the bottom part and drag up to see the terminal.

If you see this you are ready to go. N.B you can use any other software you comfortable with for this.

Step 2: Create Database

Use the thunder/lightening button to execute the query

create database sql_learning;

The command above is used to create a database in MySQL workbench. You can view the database on the left navbar after schema.

N.B

Use the refresh button if you can’t see the database or right click and select refresh option on theleft nav bar.
Use # to comment out a command

To move into the newly created DB we use the command

//use DBnameuse sql_learning;

After this command you can run any command in your DB including creating tables and other SQL operations.

When carrying out numeric operation a new window will open called query result pane while the one shown in the image above is the history output window.

Numeric Operations

create database sql_learning;use sql_learning;
#Explore numeric functions
select abs(20); #20
select abs(-20); #20
select mod(10,4) as remainder;#get the remainder
select power(4,2); #16
select  sqrt(144); #12
select greatest(3,6,8,34); #34
select least(3,6,8,34); #3
select truncate(22.786876, 2);# 22.78 trancate to two decimal places
select round(22.897); #23 round to whole number
select round(22.897,2); #22.90 round to 2 decimal place

Create a table

#Create table
create table students
(std_id int primary key,
std_name varchar (25),
age int , gender char(1), dob date, city varchar(20));

Primary key is used for student id to uniquely identify each record in the student table. You can view the table and its column on the left side of the nav bar under schemas.

Insert Data into a table

insert into students values
(01, "Mercy",23,"F","2019-02-02","Nairobi"),
(02, "Jemosop",27,"F","2016-02-02","Kampala"),
(03, "Dominic",29,"F","2018-02-02","Kigali")

The above command inserts the records to a table. N.B The primary key should be unique if you repeat the same value you will get an error.

Select records from a Table

select * from students;

The command selects all records from the student table and displays it in the query result pane.

Select specific columns

select std_name, age, gender from students;

Select with WHERE clasue to filter records

You can use different conditions to filter records. N.B it’s good to use single quotes for strings.

select * from students where age < 25;
select * from students where std_name ='Mercy';

From the history pane you can see 1 row is returned for the last two commands indicating that the condition was met. If the condition was not met 0 row will output in history.

Select with WHERE and AND clasue to filter records

Select records which meets more than two condition. We will combine the two conditions above into one condition. N.B this records will be displayed when a record matches both conditions.

select * from students where std_name ='Mercy' and age < 25;

Records matching the condition will be displayed, but according to our records only one record meets the condition.

Select with WHERE and OR clasue to filter records

This filters records which may meet one of the different conditions. N.B this records will be displayed when a record matches either condition.

select * from students where std_name ='Mercy' or city = 'Kampala';

Select with WHERE and NOT clasue to filter records

This displays records where the field name is not same as the condition

select * from students where not std_name ='Mercy';

Select with Group By with Count()

Group by groups rows that have same values into summary rows. This is useful mostly when it comes to counting based on group in our case it can be city, name, age etc.

Add more records to the table to make this easier.insert into students values
(04, "Mercy",23,"F","2019-02-02","Nairobi"),
(05, "Jemosop",27,"F","2016-02-02","Kampala"),
(06, "Dominic",29,"F","2018-02-02","Kigali"),
(07, "Mesrcy",23,"F","2019-02-04","Nairobi"),
(08, "Jemososp",27,"F","2016-02-05","Kampala"),
(09, "Domsinic",29,"F","2018-02-06","Kampala");

Group items by city

select  city , count(std_id)  as total_students from students group by city;

Select with Group By with Sum()

select  std_name , sum(std_id)  as total_students from students group by std_name;

This command add(sums) the std_id for records with the same name. But in a real world scenario you will need to add values/know the total number of related records.

Select with Group By with Max(),Min(),Avg()

//Find the count of same cities
select  city , count(std_id)  as total_students from students group by city;//find the sum of student id with same name
select  std_name , sum(std_id)  as total_students from students group by std_name;//find the average of age of students with same name
select  std_name , avg(age)  as total_students from students group by std_name;//find max value of student id with same name
select  std_name , max(std_id)  as total_students from students group by std_name;//find the minimum value of student id with same name
select  std_name , min(std_id)  as total_students from students group by std_name;

Select with the HAVING Clause

Having clause is used in sql is used because WHERE keyword cannot be used with aggregate functions. Example of aggregate function count(),avg(),sum(),min(),max().

The example below will result in invalid group of group function

select  city , count(std_id)  as total_students from students where count(std_id) < 4 group by city;

using having with aggregate functions

select  city , count(std_id)  as total_students from students group by city having count(std_id) < 4;

Select with the Order By Clause

Order By clause is used to sort records/results in either ascending and descending order. Order By sorts records in either ascending or descending order. By default it sorts in an ascending order.

select * from students order by std_name DESC;

The command above will order records in Ascending order based on their name.

As you can see the results are in descending order from the largest to the smallest.

Convert case during selection

#strings
select upper('Mercy') as upper_case; # MERCY
select lower('MERCY') as lower_case; # mercy
select lcase('MERCY') as lower_case; # mercy

length of a string

#String length
select character_length('Mercy') as total_length; # 5

To get name alongside the length of a string

select std_name, character_length(std_name) as total_len  from students;

Add two or more expression using concat() function

#concatinate strings
select concat('Nairobi',' is in', ' Kenya') as merged;
#output
'Nairobi is in Kenya'

Print string in reverse order

# Reverse
select reverse(std_name) from students;

Replace function with strings

#Replace
select replace("Africa is a country", "country",'continent');

Remove space from a string

# manage space in a string
#remove space from beginning of a string
select length(ltrim("     Mercy     ")); # 10
#remove space from end of a string
select length(rtrim("      Mercy  ")); # 11
#remove space from the beginning and end of a string
select length(trim("   Mercy   ")); # 5

find the position of a string

#find the position of a string 
select position("city" in "Nairobi is the city of clean water");

Get ascii value

select ascii('b'); #98
select ascii('4'); #52

Here is a summary of the sql above