Introduction to MySQL

Lets create our first database

CREATE DATABASE IF NOT EXISTS manu_training; 

We have successfully created database/schema using SQL command.

Show databases

SHOW DATABASES;

Displaying records 1 - 10

Database
company_db
day3
demo_db
denaco_training
employee
first_database
information_schema
intro
manu_training
mysql

Choose which database to use when evaluating commands

USE manu_training;

After selecting the database now we can query what tables are in the db

Show Tables

SHOW TABLES;

2 records

Tables_in_manu_training
notified_births_census
persons

Create a Table

The CREATE TABLE statement allows you to create a new table in a database.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

First, you specify the name of the table that you want to create after the CREATE TABLE keywords. The table name must be unique within a database.

Second, you specify a list of columns of the table in the column_list section, columns are separated by commas.

MySQL CREATE TABLE Example

The following example creates a table called “Persons” that contains five columns: PersonID, LastName, FirstName, Address, and City:

CREATE TABLE IF NOT EXISTS Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

The empty “Persons” table will now look like this:

Persons Table

See if table was created

SHOW TABLES;

2 records

Tables_in_manu_training
notified_births_census
persons

Describe a Table

We will use the DESCRIBE command to show the structure of our table, such as column names, constraints on column names, etc. The DESC command is a short form of the DESCRIBE command.

{DESCRIBE | DESC} table_name;  

DESCRIBE persons;

5 records

Field	Type	Null	Key	Default	Extra
PersonID	int	YES		NA
LastName	varchar(255)	YES		NA
FirstName	varchar(255)	YES		NA
Address	varchar(255)	YES		NA
City	varchar(255)	YES		NA

The empty “Persons” table can now be filled with data with the SQL INSERT INTO statement.

The MySQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

Syntax

It is good practice to specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

INSERT INTO Example

INSERT INTO persons (LastName, FirstName, Address, City) 
VALUES("Muller","Thomas","00100","Munich")

MySQL INSERT multiple rows statement

Use a single MySQL INSERT statement to insert multiple rows into a table.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1, value2, value3, ...),
    (value1, value2, value3, ...),
    ...
    (value1, value2, value3, ...);

In the above syntax:

• First, specify the name of table that you want to insert after the INSERT INTO keywords.

• Second, specify a comma-separated column list inside parentheses after the table name.

• Third, specify a comma-separated list of row data in the VALUES clause. Each element of the list represents a row. The number of values in each element must be the same as the number of columns specified.

Insert Multiple rows example

Lets insert 5 records into our persons table

INSERT INTO persons (PersonID, LastName, FirstName, Address, City)
VALUES(1,"Doe","Jane","0234","Nairobi"),
(2,"Einstein","Albert","1329", "Munich"),
(3,"Man","Bat","00001","New York"),
(4,"Margaret","Mitchelle","23344","Atlanta"),
(5,"Teresa","Mother","001324","Calcutta")

Lets see the number of rows our table contains

SELECT COUNT(*) FROM persons;

1 records

COUNT(*)
6

MySQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, … are the field names of the table you want to select data from.

The SELECT and FROM are the keywords. By convention, you write the SQL keywords in uppercase. However, it’s not mandatory. Because SQL is case-insensitive, you can write the SQL statement in lowercase, uppercase, etc

If you want to select all the fields available in the table, use the “*” wildcard syntax:

SELECT * FROM persons;

SELECT Columns Example

The following SQL statement selects the “LastName”, “FirstName” and “City” from “persons” table

SELECT 
    LastName, 
    FirstName, 
    City
FROM
    persons;

6 records

LastName	FirstName	City
Doe	Jane	Kisumu
Einstein	Albert	Munich
Man	Bat	New York
Margaret	Mitchelle	Atlanta
Teresa	Mother	Calcutta
Muller	Thomas	Munich

Using the MySQL SELECT statement to retrieve data from all columns example

Use the __*__ wildcard

SELECT * FROM persons;

The MySQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT Example Without DISTINCT

SELECT City FROM persons;

6 records

City
Kisumu
Munich
New York
Atlanta
Calcutta
Munich

SELECT DISTINCT Example

SELECT DISTINCT City from persons;

5 records

City
Kisumu
Munich
New York
Atlanta
Calcutta

CREATE a Sample Table

Let’s create a new table notified_births_census that records birth data for counties in Kenya.

CREATE TABLE notified_births_census(
    id INT NOT NULL AUTO_INCREMENT,
    county_name VARCHAR(155),
    total_births INT,
    notified_births INT,
    not_notified_births INT,
    dont_know INT,
    not_stated INT,
    percent_notified DECIMAL(5,2),
    PRIMARY KEY(id)
);

DESCRIBE notified_births_census;

8 records

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NA	auto_increment
county_name	varchar(155)	YES		NA
total_births	int	YES		NA
notified_births	int	YES		NA
not_notified_births	int	YES		NA
dont_know	int	YES		NA
not_stated	int	YES		NA
percent_notified	decimal(5,2)	YES		NA

Let’s insert some records.

INSERT INTO notified_births_census (county_name, total_births, notified_births, not_notified_births, dont_know, not_stated, percent_notified)
 VALUES
('KENYA', 1340468, 1212142, 125714, 2609, 3, 90.4),
('RURAL', 888039, 777343, 108563, 2131, 2, 87.5),
('URBAN', 452429, 434799, 17151, 478, 1, 96.1),
('MOMBASA', 37249, 35201, 2026, 22, NULL, 94.5),
('KWALE', 29226, 26455, 2719, 52, NULL, 90.5),
('KILIFI', 44519, 41950, 2509, 60, NULL, 94.2),
('TANA RIVER', 11683, 8541, 3106, 36, NULL, 73.1),
('LAMU', 4235, 3909, 324, 2, NULL, 92.3),
('TAITA/TAVETA', 9110, 8674, 435, 1, NULL, 95.2),
('GARISSA', 16414, 12198, 3986, 230, NULL, 74.3),
('WAJIR', 16767, 10777, 5921, 69, NULL, 64.3),
('MANDERA', 26639, 17395, 9027, 217, NULL, 65.3),
('MARSABIT', 13679, 9971, 3679, 29, NULL, 72.9),
('ISIOLO', 8037, 6518, 1496, 23, NULL, 81.1),
('MERU', 38222, 36649, 1532, 41, NULL, 95.9),
('THARAKA-NITHI', 9109, 8681, 417, 11, NULL, 95.3),
('EMBU', 14556, 14206, 345, 5, NULL, 97.6),
('KITUI', 27650, 24459, 3115, 75, 1, 88.5),
('MACHAKOS', 33548, 31726, 1783, 39, NULL, 94.6),
('MAKUENI', 20805, 19462, 1294, 49, NULL, 93.5),
('NYANDARUA', 16247, 15825, 417, 4, 1, 97.4),
('NYERI', 16831, 16614, 204, 13, NULL, 98.7),
('KIRINYAGA', 13638, 13459, 175, 4, NULL, 98.7),
('MURANGA', 24866, 24332, 529, 5, NULL, 97.9),
('KIAMBU', 69596, 67736, 1818, 42, NULL, 97.3),
('TURKANA', 24758, 17782, 6726, 250, NULL, 71.8),
('WEST POKOT', 24511, 16956, 7441, 114, NULL, 69.2),
('SAMBURU', 10665, 7561, 3080, 24, NULL, 70.9),
('TRANS NZOIA', 29005, 24817, 4125, 63, NULL, 85.6),
('UASIN GISHU', 32983, 30932, 1995, 56, NULL, 93.8),
('ELGEYO/MARAKWET', 13212, 12459, 742, 11, NULL, 94.3),
('NANDI', 23603, 21137, 2414, 52, NULL, 89.6),
('BARINGO', 19697, 16061, 3567, 69, NULL, 81.5),
('LAIKIPIA', 15383, 13400, 1969, 14, NULL, 87.1),
('NAKURU', 64797, 59771, 4923, 102, 1, 92.2),
('NAROK', 40643, 32520, 7980, 143, NULL, 80.0),
('KAJIADO', 36244, 32319, 3833, 92, NULL, 89.2),
('KERICHO', 24383, 22344, 2007, 32, NULL, 91.6),
('BOMET', 24647, 22848, 1752, 47, NULL, 92.7),
('KAKAMEGA', 49974, 46136, 3774, 64, NULL, 92.3),
('VIHIGA', 14329, 13581, 733, 15, NULL, 94.8),
('BUNGOMA', 47722, 43706, 3936, 80, NULL, 91.6),
('BUSIA', 25597, 23344, 2222, 31, NULL, 91.2),
('SIAYA', 28260, 26784, 1433, 43, NULL, 94.8),
('KISUMU', 34078, 32296, 1752, 30, NULL, 94.8),
('HOMABAY', 34833, 31723, 3069, 41, NULL, 91.1),
('MIGORI', 37118, 33827, 3228, 63, NULL, 91.1),
('KISII', 32057, 30419, 1609, 29, NULL, 94.9),
('NYAMIRA', 14114, 13406, 696, 12, NULL, 95.0),
('NAIROBI CITY', 135229, 131275, 3851, 103, NULL, 97.1);

Sorting Data

Now that we have some data in our table, let’s go ahead and sort it.

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

When you use the SELECT statement to query data from a table, the order of rows in the result set is unspecified. To sort the rows in the result set, you add the ORDER BY clause to the SELECT statement.

Using MySQL ORDER BY clause to sort the result set by one column example

Sort the counties by total number of births from county with lowest number of births

SELECT county_name, total_births
FROM notified_births_census 
ORDER BY total_births;

Displaying records 1 - 10

county_name	total_births
LAMU	4235
ISIOLO	8037
THARAKA-NITHI	9109
TAITA/TAVETA	9110
SAMBURU	10665
TANA RIVER	11683
ELGEYO/MARAKWET	13212
KIRINYAGA	13638
MARSABIT	13679
NYAMIRA	14114

Sort the counties by total number of births starting with county with highest number of births

SELECT county_name, total_births
FROM notified_births_census 
ORDER BY total_births DESC;

Displaying records 1 - 10

county_name	total_births
KENYA	1340468
RURAL	888039
URBAN	452429
NAIROBI CITY	135229
KIAMBU	69596
NAKURU	64797
KAKAMEGA	49974
BUNGOMA	47722
KILIFI	44519
NAROK	40643

We can also sort alphabetically

SELECT county_name, total_births
FROM notified_births_census
ORDER BY county_name

Displaying records 1 - 10

county_name	total_births
BARINGO	19697
BOMET	24647
BUNGOMA	47722
BUSIA	25597
ELGEYO/MARAKWET	13212
EMBU	14556
GARISSA	16414
HOMABAY	34833
ISIOLO	8037
KAJIADO	36244

The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

Sort the counties by total number of births starting with county with highest number of births

The following code will display the top 5 counties leading with number of births

SELECT county_name, total_births
FROM notified_births_census 
ORDER BY total_births 
LIMIT 5;

5 records

county_name	total_births
LAMU	4235
ISIOLO	8037
THARAKA-NITHI	9109
TAITA/TAVETA	9110
SAMBURU	10665

Filtering Data

The SQL WHERE Clause

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

WHERE Clause Example

SHOW total_births for county_name NAIROBI

SELECT county_name, total_births FROM notified_births_census WHERE county_name = "NAIROBI CITY";

1 records

county_name	total_births
NAIROBI CITY	135229

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:

Example

SELECT county_name, total_births FROM notified_births_census WHERE not_stated = 1;

4 records

county_name	total_births
URBAN	452429
KITUI	27650
NYANDARUA	16247
NAKURU	64797

Population density by subcounty in kenya Data

Load in the data provided in the population_density_county_subcounty.sql Github repo.

Make sure the records are inserted correctly into your table.

We will proceed to use this table to learn more about the WHERE clause

Below is the description of the table:

DESCRIBE subcounty_population_density;

8 records

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NA	auto_increment
county_name	varchar(250)	NO		NA
subcounty_name	varchar(500)	NO		NA
total	int	YES		NA
male	int	YES		NA
female	int	YES		NA
square_kms	int	YES		NA
pop_density	int	YES		NA

Operators in The WHERE Clause

The following operators can be used in the WHERE clause

Equal =

Select the sub counties that have 12 square kilometers

SELECT county_name, subcounty_name,square_kms
FROM subcounty_population_density
WHERE square_kms = 12;

2 records

county_name	subcounty_name	square_kms
Nairobi	KIBRA	12
Nairobi	MAKADARA	12

Greater Than >

Select all the sub counties which have more than 500,000 total population

SELECT county_name, subcounty_name, total
FROM subcounty_population_density
WHERE total > 500000;

3 records

county_name	subcounty_name	total
Nairobi	EMBAKASI	988808
Nairobi	KASARANI	780656
Nairobi	NJIRU	626482

less Than <

Select all counties which have less than 10,000 total population

SELECT county_name, subcounty_name, total
FROM subcounty_population_density
WHERE total < 10000;

Displaying records 1 - 10

county_name	subcounty_name	total
Meru	MERU NATIONAL PARK	385
Meru	MT. KENYA FOREST	463
Tharaka Nithi	MOUNT KENYA FOREST	398
Embu	MOUNT KENYA FOREST	22
Nyandarua	ABERDARE NATIONAL PARK	15
Nyeri	MT. KENYA FOREST	188
Nyeri	ABERDARE FOREST	106
Kirinyaga	MOUNT KENYA FOREST	70
Murang’a	ABERDARE FOREST	43
Narok	MAU FOREST	32

<= less than or equal to

SELECT county_name, subcounty_name, total
FROM subcounty_population_density
WHERE square_kms < 12;

2 records

county_name	subcounty_name	total
Nairobi	KAMUKUNJI	268276
Nairobi	MATHARE	206564

Inclusive

SELECT county_name, subcounty_name, total
FROM subcounty_population_density
WHERE square_kms <= 12;

4 records

county_name	subcounty_name	total
Nairobi	KAMUKUNJI	268276
Nairobi	KIBRA	185777
Nairobi	MAKADARA	189536
Nairobi	MATHARE	206564

<> Not equal

Note: In some versions of SQL this operator may be written as !=

SELECT county_name, subcounty_name 
FROM subcounty_population_density
WHERE county_name <> 'Nairobi';

Displaying records 1 - 10

county_name	subcounty_name
Mombasa	CHANGAMWE
Mombasa	JOMVU
Mombasa	KISAUNI
Mombasa	LIKONI
Mombasa	MVITA
Mombasa	NYALI
Kwale	KINANGO
Kwale	LUNGA LUNGA
Kwale	MATUGA
Kwale	MSAMBWENI

The SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition

• The AND operator displays a record if all the conditions separated by AND are TRUE.

• The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Example

Select sub counties in nairobi county which have less than 200000 total population

SELECT county_name, subcounty_name, total 
FROM subcounty_population_density
WHERE county_name = 'Nairobi' AND total < 200000;

3 records

county_name	subcounty_name	total
Nairobi	KIBRA	185777
Nairobi	LANG’ATA	197489
Nairobi	MAKADARA	189536

OR

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Example

The following SQL statement selects all fields from “subcounty_population_density” where county_name is “Nairobi” or “Mombasa”

SELECT county_name, subcounty_name
FROM subcounty_population_density
WHERE county_name = 'Nairobi' OR county_name = 'Mombasa';

Displaying records 1 - 10

county_name	subcounty_name
Mombasa	CHANGAMWE
Mombasa	JOMVU
Mombasa	KISAUNI
Mombasa	LIKONI
Mombasa	MVITA
Mombasa	NYALI
Nairobi	DAGORETTI
Nairobi	EMBAKASI
Nairobi	KAMUKUNJI
Nairobi	KASARANI

NOT

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example

The following SQL statement selects all fields from “subcounty_population_density” where the county_name is not Nairobi

SELECT county_name, subcounty_name, total
FROM subcounty_population_density
WHERE NOT county_name='Nairobi';

Displaying records 1 - 10

county_name	subcounty_name	total
Mombasa	CHANGAMWE	131882
Mombasa	JOMVU	163415
Mombasa	KISAUNI	291930
Mombasa	LIKONI	250358
Mombasa	MVITA	154171
Mombasa	NYALI	216577
Kwale	KINANGO	94220
Kwale	LUNGA LUNGA	198423
Kwale	MATUGA	194252
Kwale	MSAMBWENI	177690

Combining AND, OR and NOT

You can also combine the AND, OR and NOT operators.

The following statement selects all fields from subcounty_population_density that are in Nairobi or Mombasa county which have a population density of more than 10000

SELECT county_name, subcounty_name, total, pop_density
FROM subcounty_population_density
WHERE (county_name = 'Nairobi' OR county_name = 'Mombasa') AND pop_density > 10000;

8 records

county_name	subcounty_name	total	pop_density
Mombasa	MVITA	154171	10543
Nairobi	DAGORETTI	434208	14908
Nairobi	EMBAKASI	988808	11460
Nairobi	KAMUKUNJI	268276	25455
Nairobi	KIBRA	185777	15311
Nairobi	MAKADARA	189536	16150
Nairobi	MATHARE	206564	68940
Nairobi	STAREHE	210423	10205

BETWEEN

MySQL BETWEEN operator to determine whether a value is in a range of values.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE value BETWEEN low AND high;

Select all the fields where subcounty square_kms are within the range 1000 and 10000

SELECT county_name, subcounty_name, square_kms
FROM subcounty_population_density
WHERE square_kms BETWEEN 1000 AND 10000;

Displaying records 1 - 10

county_name	subcounty_name	square_kms
Kwale	KINANGO	1612
Kwale	LUNGA LUNGA	2765
Kwale	MATUGA	1034
Kwale	SAMBURU	2430
Kilifi	GANZE	3218
Kilifi	MAGARINI	5229
Kilifi	MALINDI	2263
Tana River	TANA RIVER	9167
Lamu	LAMU EAST	2338
Lamu	LAMU WEST	3945

IN

The IN operator allows you to determine if a value matches any value in a list of values.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE value IN (value1, value2, value3,...)

Example

SELECT 1 IN (1,2,3);

1 records

1 IN (1,2,3)
1

Select all fields where the counties are in the list of Kenyan cities

SELECT county_name, subcounty_name, total
FROM subcounty_population_density
WHERE county_name IN ('Nairobi','Mombasa','Kisumu','Nakuru');

Displaying records 1 - 10

county_name	subcounty_name	total
Mombasa	CHANGAMWE	131882
Mombasa	JOMVU	163415
Mombasa	KISAUNI	291930
Mombasa	LIKONI	250358
Mombasa	MVITA	154171
Mombasa	NYALI	216577
Nakuru	GILGIL	185209
Nakuru	KURESOI NORTH	175074
Nakuru	KURESOI SOUTH	155324
Nakuru	MOLO	156732

IS NULL

Show the number rows that are missing/NULL values for square_kms column

SELECT county_name, subcounty_name, square_kms 
FROM subcounty_population_density
WHERE square_kms IS NULL;

1 records

county_name	subcounty_name	square_kms
Kericho	TINDERET FOREST	NA

Note: IS NULL is different from = 0

SELECT county_name, subcounty_name, square_kms 
FROM subcounty_population_density
WHERE square_kms = 0;

0 records

county_name	subcounty_name	square_kms

Note: comparing any value to NULL will always return False

NULL = NULL Returns False

Show all fields where pop_density are missing values

SELECT county_name, subcounty_name, total, pop_density 
FROM subcounty_population_density
WHERE pop_density IS NULL;

Displaying records 1 - 10

county_name	subcounty_name	total	pop_density
Meru	MERU NATIONAL PARK	385	NA
Meru	MT. KENYA FOREST	463	NA
Embu	MOUNT KENYA FOREST	22	NA
Nyandarua	ABERDARE NATIONAL PARK	15	NA
Nyeri	MT. KENYA FOREST	188	NA
Nyeri	ABERDARE FOREST	106	NA
Kirinyaga	MOUNT KENYA FOREST	70	NA
Murang’a	ABERDARE FOREST	43	NA
Baringo	LAKE BARINGO	NA	NA
Narok	MAU FOREST	32	NA

LIKE and Wildcards

The LIKE operator is a logical operator that tests whether a string contains a specified pattern or not.

MySQL provides two wildcard characters for constructing patterns:

• The percentage % wildcard matches any string of zero or more characters.

• The underscore _ wildcard matches any string of one character lengths

For example, s% matches any string starts with the character s such as sun and six. The se_ matches any string starts with se and is followed by any character such as see and sea

-- Entries where the county name begins with letter N
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE county_name LIKE "N%";

Displaying records 1 - 10

county_name	subcounty_name
Nyandarua	KINANGOP
Nyandarua	NYANDARUASOUTH
Nyandarua	MIRANGINE
Nyandarua	KIPIPIRI
Nyandarua	NYANDARUACENTRAL
Nyandarua	NYANDARUAWEST
Nyandarua	NYANDARUANORTH
Nyandarua	ABERDARE NATIONAL PARK
Nyeri	TETU
Nyeri	KIENI EAST

In this example, MySQL scans the whole subcounty_population_table to find subcounties whose county_name start with the letter N and are followed by any number of characters.

%y matches any county_name that ends with letter Y.

Note : The wildcard is case insensitive.

-- Entries where the county name ends with letter y
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE county_name LIKE "%y";

8 records

county_name	subcounty_name
Homa Bay	HOMA BAY
Homa Bay	NDHIWA
Homa Bay	RACHUONYONORTH
Homa Bay	RACHUONYOEAST
Homa Bay	RACHUONYOSOUTH
Homa Bay	RANGWE
Homa Bay	SUBA NORTH
Homa Bay	SUBA SOUTH

Roses are , violets are .

Fun Exercise

-- Entries where the county name ends with letter y
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE subcounty_name LIKE "%east%" 
OR subcounty_name LIKE "%west%";

Displaying records 1 - 10

county_name	subcounty_name
Lamu	LAMU EAST
Lamu	LAMU WEST
Wajir	WAJIR EAST
Wajir	WAJIR WEST
Mandera	MANDERA WEST
Mandera	MANDERA EAST
Meru	BUURI EAST
Meru	BUURI WEST
Meru	TIGANIA EAST
Meru	TIGANIA WEST

Try figuring out what subcounties will be returned by the above query.

Example using the underscore _ wildcard

-- Entries where the county name ends with letter y
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE county_name LIKE "k_____";

Displaying records 1 - 10

county_name	subcounty_name
Kilifi	CHONYI
Kilifi	GANZE
Kilifi	KALOLENI
Kilifi	KAUMA
Kilifi	KILIFI NORTH
Kilifi	KILIFI SOUTH
Kilifi	MAGARINI
Kilifi	MALINDI
Kilifi	RABAI
Kiambu	GATUNDU NORTH

This query is used to select rows where the county_name starts with letter k and followed by five letters

Typically, you’ll use the LIKE operator in the WHERE clause of the SELECT , DELETE, and UPDATE statement.

MySQL NOT LIKE

The MySQL allows you to combine the NOT operator with the LIKE operator to find a string that does not match a specific pattern.

Suppose you want to search for all subcounties in Kilifi that do not have the word Kilifi in the subcounty name:

-- Entries where the county name ends with letter y
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE county_name = "kilifi"
AND subcounty_name NOT LIKE "%kilifi%";

7 records

county_name	subcounty_name
Kilifi	CHONYI
Kilifi	GANZE
Kilifi	KALOLENI
Kilifi	KAUMA
Kilifi	MAGARINI
Kilifi	MALINDI
Kilifi	RABAI

MySQL REGEXP

MySQL REGEXP performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument. Regular Expressions provide a powerful and flexible pattern match that can help us implement power search utilities for our database systems.

Suppose you want to show the sub counties that have the word east

-- Entries where the subcounty name has word east
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE subcounty_name REGEXP "east"

Displaying records 1 - 10

county_name	subcounty_name
Lamu	LAMU EAST
Wajir	WAJIR EAST
Mandera	MANDERA EAST
Meru	BUURI EAST
Meru	TIGANIA EAST
Embu	EMBU EAST
Kitui	MWINGI EAST
Makueni	MBOONI EAST
Nyeri	KIENI EAST
Nyeri	MATHIRA EAST

-- Entries where the subcounty name has word east
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE subcounty_name REGEXP "east"

Displaying records 1 - 10

county_name	subcounty_name
Lamu	LAMU EAST
Wajir	WAJIR EAST
Mandera	MANDERA EAST
Meru	BUURI EAST
Meru	TIGANIA EAST
Embu	EMBU EAST
Kitui	MWINGI EAST
Makueni	MBOONI EAST
Nyeri	KIENI EAST
Nyeri	MATHIRA EAST

-- Entries where the subcounty name has word east
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE subcounty_name REGEXP "east|west"

Displaying records 1 - 10

county_name	subcounty_name
Lamu	LAMU EAST
Lamu	LAMU WEST
Wajir	WAJIR EAST
Wajir	WAJIR WEST
Mandera	MANDERA WEST
Mandera	MANDERA EAST
Meru	BUURI EAST
Meru	BUURI WEST
Meru	TIGANIA EAST
Meru	TIGANIA WEST

-- Entries where the subcounty name has word east
SELECT county_name, subcounty_name  
FROM subcounty_population_density
WHERE subcounty_name REGEXP "east|west|north|south"

Displaying records 1 - 10

county_name	subcounty_name
Kilifi	KILIFI NORTH
Kilifi	KILIFI SOUTH
Tana River	TANA NORTH
Lamu	LAMU EAST
Lamu	LAMU WEST
Wajir	WAJIR EAST
Wajir	WAJIR NORTH
Wajir	WAJIR SOUTH
Wajir	WAJIR WEST
Mandera	MANDERA WEST

Updating Values

Updating data is one of the most important tasks when you work with the database.

The UPDATE statement is used to modify the existing records in a table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE subcounty_population_density
SET subcounty_name = "RACHUONYO NORTH"
WHERE subcounty_name = "RACHUONYONORTH"

The example above will update the records for subcounty with spelling mistake. Try and correct the spelling mistakes of sub counties in Homa Bay and Nyandarua

Below is the code for updating

-- Update records for Rachuonyo East
UPDATE subcounty_population_density
SET subcounty_name = "RACHUONYO EAST"
WHERE subcounty_name = "RACHUONYOEAST";

-- Update records for Rachuonyo South
UPDATE subcounty_population_density
SET subcounty_name = "RACHUONYO SOUTH"
WHERE subcounty_name = "RACHUONYOSOUTH";

-- Update records for NYANDARUASOUTH
UPDATE subcounty_population_density
SET subcounty_name = "NYANDARUA SOUTH"
WHERE subcounty_name = "NYANDARUASOUTH";


-- Update records for NYANDARUA central
UPDATE subcounty_population_density
SET subcounty_name = "NYANDARUA CENTRAL"
WHERE subcounty_name = "NYANDARUACENTRAL";

-- Update records for NYANDARUA WEST
UPDATE subcounty_population_density
SET subcounty_name = "NYANDARUA WEST"
WHERE subcounty_name = "NYANDARUAWEST";

-- Update records for NYANDARUA WEST
UPDATE subcounty_population_density
SET subcounty_name = "NYANDARUA WEST"
WHERE subcounty_name = "NYANDARUAWEST";

-- Update records for NYANDARUA WEST
UPDATE subcounty_population_density
SET subcounty_name = "NYANDARUA NORTH"
WHERE subcounty_name = "NYANDARUANORTH";

Warning: Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Deleting Records

To delete data from a table, you use the MySQL DELETE statement.

Syntax

DELETE FROM table_name
WHERE condition;

• First, specify the table from which you delete data.

• Second, use a condition to specify which rows to delete in the WHERE clause. The DELETE statement will delete rows that match the condition

Warning: Notice that the WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all rows in the table.

Example

Since the sub county LAKE BARINGO has no population, we can DELETE the row

DELETE FROM subcounty_population_density
WHERE subcounty_name = "LAKE BARINGO";

Deleting multiple rows is equally easy. Let’s delete all rows where the population is NULL

DELETE FROM subcounty_population_density
WHERE total IS NULL;

Note that once you delete data, it is gone.

To delete all rows from a table, you use the DELETE statement without the WHERE clause

DELETE table_name;

MySQL Transactions

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

COMMIT and ROLLBACK

• When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect.

• If a failure occurs, a ROLLBACK command should be issued to return every table referenced in the transaction to its previous state.

Example of a transaction

Undoing a mistake using ROLLBACK

-- 1. Start a new transaction  
  
START TRANSACTION;  

-- Update a record in the table
UPDATE subcounty_population_density SET subcounty_name = "THARAKA NITHI";

-- Show updated table
SELECT county_name, subcounty_name, total FROM subcounty_population LIMIT 10;

-- Undo the mistake
ROLLBACK;

Updating a table record and commit after confirming the change

-- 1. Start a new transaction  
  
START TRANSACTION;  

-- Update a record in the table
UPDATE subcounty_population_density SET subcounty_name = "THARAKA NITHI"
WHERE subcounty_name = "THARAKANITHI";

-- Show updated table
SELECT county_name, subcounty_name, total FROM subcounty_population 
WHERE county_name = "Tharaka Nithi";

-- Confirm the update was correct
COMMIT;

SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of sub counties in each county”

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example

Group the sub-counties according to the county

Also shows the number of sub counties per county in the table

SELECT county_name, count(subcounty_name)
FROM subcounty_population_density
GROUP BY county_name;

Displaying records 1 - 10

county_name	count(subcounty_name)
Mombasa	6
Kwale	5
Kilifi	9
Tana River	3
Lamu	2
Taita Taveta	4
Garissa	7
Wajir	8
Mandera	7
Marsabit	7

The following SQL statement lists the number of sub counties in each county, sorted high to low:

SELECT county_name, count(subcounty_name) AS num_counties
FROM subcounty_population_density
GROUP BY county_name
ORDER BY num_counties DESC;

Displaying records 1 - 10

county_name	num_counties
Kitui	18
Meru	13
Kiambu	13
Kakamega	13
Bungoma	12
Nakuru	11
Nairobi	11
Kisii	11
Nyeri	10
Murang’a	9

The SQL COUNT(), AVG() and SUM() Functions

COUNT()

The COUNT() function returns the number of rows that matches a specified criterion.

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example

To get the number of rows in a table

-- Return total number of rows in the table
SELECT COUNT(*)
FROM subcounty_population_density;

1 records

COUNT(*)
349

AVG()

The AVG() function returns the average value of a numeric column.

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example

SELECT county_name, AVG(square_kms)
FROM subcounty_population_density
GROUP BY county_name;

Displaying records 1 - 10

county_name	AVG(square_kms)
Mombasa	36.8333
Kwale	1650.6000
Kilifi	1394.6667
Tana River	12634.6667
Lamu	3141.5000
Taita Taveta	4288.2500
Garissa	6393.2857
Wajir	7096.6250
Mandera	3706.0000
Marsabit	10134.8571

SUM()

Example

The following SQL statement finds the sum of total population per county

SELECT county_name, SUM(TOTAL) AS total_pop
FROM subcounty_population_density
GROUP BY county_name
ORDER BY total_pop;

Displaying records 1 - 10

county_name	total_pop
Lamu	143920
Isiolo	268002
Samburu	310327
Tana River	315943
Taita Taveta	340671
Elgeyo Marakwet	454480
Marsabit	459785
Laikipia	518560
Vihiga	590013
Nyamira	605576

From the results above, it can be seen that Lamu county is the least populated.

SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

MIN() Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MIN() Example

Show the row with the lowest value in the total column.

-- Show least populated subcounty

SELECT MIN(total) AS least_populated_subcounty
FROM subcounty_population_density;

1 records

least_populated_subcounty
15

The MAX() function returns the biggest value of the selected column

MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

MAX() Example

Show the row with the lowest value in the total column.

-- Show least populated subcounty

SELECT MAX(pop_density) AS highest_pop_density
FROM subcounty_population_density;

1 records

highest_pop_density
68940

-- Show least populated subcounty

SELECT MAX(pop_density) AS highest_pop_density
FROM subcounty_population_density;

1 records

highest_pop_density
68940

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Use a GROUP BY and a MAX

SELECT county_name, AVG(pop_density) AS avg_pop_density
FROM subcounty_population_density
GROUP BY county_name
ORDER BY avg_pop_density DESC;

Displaying records 1 - 10

county_name	avg_pop_density
Nairobi	16399.1818
Mombasa	6926.1667
Kiambu	1311.7692
Kisumu	1233.4286
Kisii	969.9091
Vihiga	934.8333
Nyamira	716.2000
Kakamega	673.0000
Bungoma	625.8333
Kirinyaga	562.4000

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER TABLE - ADD Column

To add a new column on a table in SQL

Syntax

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE - DROP COLUMN

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE MODIFY COLUMN

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Thank you.

Next is designing relationships between tables: follow link