A few days back, I have decided to learn MySQL. I have decided to keep you updated with whatever I am learning. I will try my best to explain each stuff that I learn. From very basic to advanced,  I will try to cover everything! No string attached!

Connection To MySQL [Windows PC]

I have you have installed MySQL from the official website or using any software like XAMPP. I will also use MySQL Workbench as well later.

1. Open the command prompt.
2. Use cd.. until you reach C:\>
3. Enter MySQL Server Bin File as cd “Program Files\MySQL\MySQL Server 8.0\bin”
4. Check what’s in there using dir after \bin
5. Use cls to clear the window
6. Connect to the database as: mysql –u root –p
7. Enter the root password and you are connected to the database.

Alternatively, you can use MySQL Workbench to connect to the database. If you don’t know how to, watch the video:

Working With MySQL Tables

In this chapter, we will create a database and a table to start our journey. A particular database can have several tables. Each table will only a particular set of data. Follow these steps along with me!

Create a database as:

create database project1;

Use the database as:

use project1;

If you forget which database you are using use:

select database();

Create a table as:

CREATE TABLE people ( PersonID INT
,first_name VARCHAR(100)
,last_name VARCHAR(100)
);

Check the table as:

show tables;

Create another table with auto increment serial number as:

CREATE TABLE people2 ( PersonID INT NOT NULL AUTO_INCREMENT
,first_name VARCHAR(100)
,last_name VARCHAR(100)
,PRIMARY KEY(PersonID));

To delete the first table: DROP TABLE people;

To see what’s inside a column:

show columns in people2;

Check the image:

To add a new column after the last_name, write:

ALTER TABLE people2 ADD column DOB date NULL;

Delete the new column as:

ALTER table people2 DROP column DOB;

Our preliminary experiment is done. Let’s enter into the real world of SQL! Now create 2 new tables for actors and movie as:

create table actors ( ActorID INT NOT NULL auto_increment
,FIRST_NAME VARCHAR(100) NULL
,LAST_NAME VARCHAR(100) NULL
,primary key(ActorID)
);

create table movies ( MovieID INT NOT NULL auto_increment
,TITLE VARCHAR(100) NULL
,RELEASE_YEAR INT NULL
,RATING VARCHAR(5)
,primary key(MovieID)
);

MySQL Insert Statement: Inserting Rows Of Data Into The Tables

Let’s add actors name to the actors columns as:

insert into actors ( FIRST_NAME, LAST_NAME ) VALUES ('Ben', 'Stiller');

To check the data, write:

select * from actors;

Now let’s add multiple values in the same table as:

insert into actors ( FIRST_NAME, LAST_NAME ) VALUES
('Owen', 'Wilson')
,('Will', 'Ferrel')
,('Mill', 'Jovovich')
,('Jerry', 'Stiller')
,('David', 'Duchovny')
,('Jon', 'Voight')
,('Nathan', 'Graham');

Add some more data into the actors table:

insert into actors ( FIRST_NAME, LAST_NAME ) VALUES
('Kristen', 'Wiig')
,('Penelope', 'Cruz')
,('Lenny', 'Kravitz')
,('Macaulay', 'Culkin')
,('Justin', 'Biever')
,('Cyrus', 'Arnold')
,('S.M.', 'Arman');

Add a signgle record into the movies table as:

INSERT INTO movies ( TITLE, RELEASE_YEAR, RATING ) VALUES ('Zoolander', 2001, 'PG-13');

Add multiple values to table movies as:

INSERT INTO movies ( TITLE, RELEASE_YEAR, RATING ) VALUES
('Zoolander 2', 2016, 'PG-13')
,('Night at the Museum: Secret fo the Tomb', 2014, 'PG')
,('Night at the Museum: Battle of the Smithsonian', 2009, 'PG')
,('Night at the Museum', 2005, 'PG')
,('National Treasure', 2004, 'PG')
,('Tropic Thunder', 2008, 'R');

SELECT Statement of MySQL: Basic

To select everything:

SELECT * FROM movies;

To select only the movie name and release year:

SELECT Statement of MySQL: CONCAT and SUBSTRING

Add a comma (,) using CONCAT; in between the last_name and first_name and display as:

Giving the header a new name using AS. Notice the code:

Select last_name & only the first 3 characters from the last_name using SUBSTRING as:

Display first_name, last_name, first character of first_name, add that first character with the last_name and display it as User_Name:

SELECT FIRST_NAME, LAST_NAME, SUBSTRING(FIRST_NAME, 1, 1) AS INITIAL,
CONCAT(SUBSTRING(FIRST_NAME, 1, 1), LAST_NAME) As USER_NAME
FROM actors;

Display last_name+first_name as Actor’s Name, last_name as LName, first_name as First Name, first character of first_name+last_name as USER_NAME, any text and some math as:

SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME) AS "Actor's Name"
, LAST_NAME AS LNAME
,FIRST_NAME AS "First Name"
,CONCAT(SUBSTRING(FIRST_NAME, 1, 1), LAST_NAME) AS USER_NAME
,'Any Text' AS anything
,1+2 AS Math FROM actors;

It’s getting complex, right? So much information to consume! Let’s create another table named ‘bowlers’ where there will be 4 players where each player will have scores of 4 games. This will help us to master the technique of creating table and using the SELECT statement. First, create the table as:

CREATE TABLE bowlers ( BowlerID INT NOT NULL auto_increment
,FNAME VARCHAR(100) DEFAULT NULL
,LNAME VARCHAR(100) DEFAULT NULL
,Game1 INT DEFAULT NULL
,Game2 INT DEFAULT NULL
,Game3 INT DEFAULT NULL
,Game4 INT DEFAULT NULL
,primary key(BowlerID));

Now, insert data into the table as:

INSERT INTO bowlers (FNAME, LNAME, Game1, Game2, Game3, Game4)
VALUES('Mashrur', 'Hossain', 121, 87, 115, 124)
,('Mark', 'Futre', 111, 99, 135, 105)
,('John', 'Smith', 80, 90, 101, 120)
,('Evgeny', 'Ramen', 120, 115, 119, 120);

Now, run the SELECT statement:

SELECT CONCAT(LNAME, ', ', FNAME) AS Player
, CONCAT(SUBSTRING(FNAME, 1, 1), SUBSTRING(LNAME, 1, 1)) AS Initial
, Game1 AS G1
, Game2 AS G2
, Game3 AS G3
, Game4 AS G4
, Game1 + Game2+ Game3 + Game4 AS "Tournament Total"
, (Game1 + Game2+ Game3 + Game4) / 4 AS "Tourname Average" FROM bowlers;

It will display something like this:

SELECT Statement: LIMIT, ORDER BY, DISTINCT

Let’s say we want to select only the first 7 from actors table. So, the query will be:

SELECT * FROM actors LIMIT 7;

Let’s select everything from actors order by last_name ascending orders [or DSC] as:

SELECT * FROM actors ORDER BY last_name ASC;

Let’s order the last_name as ASC, and first_name DESC:

SELECT * FROM actors ORDER BY last_name ASC, first_name DESC;

Using the ORDER BY & LIMIT at a time:

SELECT * FROM actors ORDER BY last_name ASC, first_name DESC LIMIT 7;

To avoid duplicate data, we use DISTINCT as:

But if we want to display Movie ID and rating using DISTINCT, it will return everything because IDs are unique as well. Watch this.

MySQL SELECT Statement: COUNT, LIKE, & GROUP BY

To display how many movies are there:

SELECT COUNT(*) AS cnt FROM movies;

How many movies are there for each rating?

Let’s say we partially remember a movie name. To get similar to that partial thing, we can use LIKE as:

‘zoo%’ means every name starts with zoo. ‘%zoo%’ then it will look for zoo in every movie name:

Now, to see only how many of them?

SELECT COUNT(*) AS "Museum Movies" FROM movies WHERE TITLE LIKE "%Museum%";

MAX & MIN: Finding The Maximum And Minimum Value

Selecting the max and min release_year from movies:

MIN, LIKE, & GROUP BY together! Display min release year and TITLE but group by them as title:

SELECT MIN(RELEASE_YEAR), TITLE FROM movies WHERE TITLE LIKE "%Museum%" GROUP BY TITLE, RATING;

Getting the minimum release year of the movies where the title has “al” and group them as rating:

Recap: Code Challenge Create a New Report

This is a bowling game code challenge. This challenge will help to recap everything we have learned so far. First create a new table:

CREATE TABLE bowlResults (BowlResultID INT NOT NULL AUTO_INCREMENT
,FNAME VARCHAR(50) DEFAULT NULL,LNAME VARCHAR(50) DEFAULT NULL
,Game_Num INT DEFAULT NULL,Game_Score INT DEFAULT NULL,PRIMARY KEY (BowlResultID));

Insert data into the table:

INSERT INTO bowlResults (FNAME, LNAME, Game_Num, Game_Score) VALUES
('S.M.', 'Arman', 1, 121),('S.M.', 'Arman', 2, 87),('S.M.', 'Arman', 3, 115)
,('S.M.', 'Arman', 4, 124),('Matt', 'Damon', 1, 111),('Matt', 'Damon', 2, 99)
,('Matt', 'Damon', 3, 135),('Matt', 'Damon', 4, 105),('Anna', 'Torv', 1, 75)
,('Anna', 'Torv', 2, 99),('Anna', 'Torv', 3, 125),('Anna', 'Torv', 4, 141)
,('Rick', 'Jarvis', 1, 115),('Rick', 'Jarvis', 2, 128),('Rick', 'Jarvis', 3, 101)
,('Rick', 'Jarvis', 4, 84);

Perform the SELECT query to display all results:

SELECT * FROM bowlResults;

So, it will show data as:

Display Top 3 players with their tournament total, tournament average in descending order (From best player to worst). Perform the following SELECT query;

SELECT CONCAT(FNAME, ' ', LNAME) AS Player
,SUM(Game_Score) AS "Tournament Total"
,SUM(Game_Score)/COUNT(*) AS "Tournament Average"
FROM bowlResults GROUP BY FNAME, LNAME
ORDER BY SUM(Game_Score)/COUNT(*) DESC LIMIT 3;

The result:

Get the list of all the players with the best score ordering from best to worst. Perform the following query:

SELECT CONCAT(FNAME, ' ', LNAME) AS Player
, MAX(Game_Score) AS "Best Game"
FROM bowlResults
GROUP BY FNAME, LNAME
ORDER BY MAX(Game_Score) DESC;

The result:

Display all players name in one row per player. Here is the query:

SELECT DISTINCT FNAME AS "First Name", LNAME AS "Last Name" FROM bowlResults;

The result:

MySQL Logical Operators

Data Types

Each column of a table has a certain data type. It could be varchar, int, Boolean, datetime, decimal, etc. All of them are self-explanatory. For more information, visiting the official MySQL website is a good idea!

Logical Operators: Where and Not Equal

WHERE clause only affects the column that will be returned. It has no effect on other columns. From our movies table, let’s display all the Night at the museum movie that has rating of ‘PG’. Notice the query:

SELECT * FROM movies WHERE RATING = 'PG' AND TITLE LIKE "Night%";

And it will return:

Display all the movies with ‘PG’ rating BUT that do not start with ‘Night’. Sounds complex? Easy! Watch the query:

SELECT * FROM movies WHERE RATING = 'PG' AND TITLE NOT LIKE "Night%";

Result:

Logical Operators: Comparison

From our movies table, display all movies that have release year greater than 2005. Here is the query:

SELECT TITLE, RELEASE_YEAR FROM movies WHERE RELEASE_YEAR > 2005;

Results:

Perform the same query with ‘>=’ instead of ‘>’ only as:

SELECT TITLE, RELEASE_YEAR FROM movies WHERE RELEASE_YEAR >= 2005;

And you will get movies that released in 2005 too:

Use ‘<’ and ‘>’ comparison operator in a single statement where the release year should be above 2005 but less than 2016 as:

SELECT TITLE, RELEASE_YEAR FROM movies WHERE RELEASE_YEAR > 2005 AND RELEASE_YEAR < 2016;

It will return:

Let’s use ‘<=’ and ‘>=’ as well with the above query as:

SELECT TITLE, RELEASE_YEAR FROM movies WHERE RELEASE_YEAR >= 2005 AND RELEASE_YEAR <= 2016;

It will return:

We can get the same result displayed above using BETWEEN as well  performing the query as:

SELECT TITLE, RELEASE_YEAR FROM movies WHERE RELEASE_YEAR BETWEEN 2005 AND 2016;

Another great comparison operator is ‘<>’ which means ‘Not Equal’. Let’s display all the movies except Tropic Thunder using the <> operator.

SELECT TITLE, RELEASE_YEAR FROM movies WHERE TITLE <> 'Tropic Thunder';

Displays all movies except Tropic Thunder:

Using the IN keyword in the WHERE clause, we can add the more complex query. Let’s say we want to display all the movies that were released in 2005, 2009, and 2016. Here is the query with IN:

SELECT TITLE, RELEASE_YEAR FROM movies WHERE RELEASE_YEAR IN (2005, 2009, 2016);

The Result:

You can perform the above query using NOT IN as well like the following:

SELECT TITLE, RELEASE_YEAR FROM movies WHERE RELEASE_YEAR NOT IN (2005, 2009, 2016);

This will display:

We can use the ‘IN’ keyword in string query as well like:

SELECT TITLE, RELEASE_YEAR FROM movies WHERE TITLE IN ('Tropic Thunder', 'National Treasure');

And it will display this:

Using ‘OR’ logical keywords. Let’s display movies that have ‘R’ rating OR that starts with Zoo as:

SELECT * FROM movies WHERE (RATING = "R" OR TITLE LIKE "Zoo%");

The Result:

Logical Operators: Null and Blanks

To understand how to deal with NULL and Blank data in MySQL, let’s add 2 additional INSERT queries into our movies table. The first INSERT query will miss the release year and the second query will miss the rating data.

INSERT INTO movies (TITLE, RATING)
VALUES ('American Sniper', 'PG-13')
,('The Irish Man', 'PG-18')
,('Star Wars', 'PG-13');

And another query:

INSERT INTO movies (TITLE, RELEASE_YEAR)
VALUES ('Intersteller', 2016)
,('Narcos: Mexico', 2013)
,('Breaking Bad', 2014);

Another INSERT query with the empty string (but not null!):

INSERT INTO movies (TITLE, RATING) VALUES ('Fight Club', '');

So our movies table will have the following movies with missing data;

Now, let’s perform a query where there will be no RATING as:

SELECT * FROM movies WHERE NOT RATING = 'PG-13' AND NOT RATING = 'PG-18' AND NOT RATING = 'PG' AND NOT RATING = 'R';

And it will show only:

But, Intersteller, Narcos, and Breaking Bad are not on the list. Why? Because their values are set to NULL and NULL is non-existent which can’t be compared with an existent value. To display movies with rating set to null, we have to perform the following query;

SELECT * FROM movies WHERE RATING IS NULL;

Result:

MySQL Case Statement

If we want to display a specific message or anything based on release_year or rating, we can use the CASE statement. Let’s display a distinct message based on rating. Write the select statement as:

SELECT title, rate, CASE
    -> WHEN rate='PG' THEN 'Bring Kids'
    -> WHEN rate='PG-13' THEN 'Older Kids'
    -> WHEN rate='R' THEN 'Not for Kids!'
    -> ELSE 'No Information'
    -> END as audience
    -> FROM movies;

Result:

MySQL Dates and Times Tutorial

There are various built-in functions for date and time in MySQL. Let’s learn what they do first:

• NOW(): It returns the system date and time of the server. It is a useful function for adding a timestamp when a row is being added to the database.
• CURDATE() returns the system date without the time.
• CURTIME() returns the system time without the date.
• DAY returns the day of the month.
• DAYNAME gives the name of the day of the week.
• DAYOFMONTH is same as DAY.
• DAYOFWEEK gives the number corresponding to the day. Sunday is 1 and Saturday is 7.
• DAYOFYEAR gives the day if we were counting from January 1^st as 1.

Now, let’s run some real-time SQL SELECT query.

SELECT NOW(), CURDATE(), CURTIME(), YEAR(NOW()), YEAR(CURDATE());

The above select statement will return:

To get month related information, run the query:

SELECT NOW(), MONTH(NOW()), MONTHNAME(NOW());

And it will return:

To get day related information, run the query:

SELECT NOW()

    ,DAY(NOW())

    ,DAYNAME(NOW())

    ,DAYOFMONTH(NOW())

    ,DAYOFWEEK(NOW())

    ,DAYOFYEAR(NOW());

The Result:

Date Time Math

We will calculate age and other date time related math here. For that, let’s create another table to our database as ‘person’. This is the query:

CREATE TABLE person (personID INT NOT NULL AUTO_INCREMENT

    ,fname VARCHAR(50) DEFAULT NULL

    ,lname VARCHAR(50) DEFAULT NULL

    ,dob DATE DEFAULT NULL

    ,PRIMARY KEY(personID));

Now, let’s insert 2 person’s data as:

INSERT INTO person(fname,lname,dob) VAlUES('S.M.', 'Arman','1986-12-31')

   ,('Mark', 'Ronson', '1977-07-16');

If we perform the SELECT query as:

SELECT * FROM person;

We will get this:

Now, let’s find out how old are they using the MySQL TIMESTAMPDIFF function. First, let’s display their date of birth and today’s date as:

SELECT dob AS Birthdays, CURDATE() AS Today FROM person;

You will get something like this:

Now, let’s find their age. Perform the select query as:

SELECT dob AS Birthday

    ,CURDATE() AS Today

    ,TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS Age

    FROM person;

It will return something like this:

Now, let’s find out what’s the birthday this year. Write the query:

SELECT fname, dob AS Birthday, CURDATE() AS Today,

    STR_TO_DATE(CONCAT(MONTH(dob), '/', DAY(dob), '/', YEAR(CURDATE())), '%m/%d/%Y') AS "Birthday This Year"

    FROM person;

You will get something like this:

Let’s find what will be the birthday next year as:

SELECT fname, dob AS Birthday, CURDATE() AS Today

    ,STR_TO_DATE(CONCAT(MONTH(dob), '/',DAY(dob),'/',YEAR(CURDATE())), '%m/%d/%Y') AS "Birthday This Year"

    ,DATE_ADD(STR_TO_DATE(CONCAT(MONTH(dob), '/', DAY(dob), '/', YEAR(CURDATE())), '%m/%d/%Y')

    ,INTERVAL 1 YEAR) AS "Birthday Next Year" FROM person;

You will get something like this:

Instead of finding birthday next year, we can find next birthday from this year. Now, for some, it may already happened. If that is the case, using the MySQL CASE statement, we can get his next birthday as:

SELECT fname, dob AS Birthday, CURDATE() AS Today,

STR_TO_DATE(CONCAT(MONTH(dob), '/', DAY(dob), '/', YEAR(CURDATE())), '%m/%d/%Y') AS "Birthday This Year"

,DATE_ADD(STR_TO_DATE(CONCAT(MONTH(dob), '/', DAY(dob), '/', YEAR(CURDATE())), '%m/%d/%Y'), INTERVAL 1 YEAR) AS "Birthday Next Year"

,CASE WHEN STR_TO_DATE(CONCAT(MONTH(dob), '/', DAY(dob), '/', YEAR(CURDATE())), '%m/%d/%Y') >= CURDATE() THEN STR_TO_DATE(CONCAT(MONTH(dob), '/', DAY(dob), '/', YEAR(CURDATE())), '%m/%d/%Y') ELSE DATE_ADD(STR_TO_DATE(CONCAT(MONTH(dob), '/', DAY(dob), '/', YEAR(CURDATE())), '%m/%d/%Y'), INTERVAL 1 YEAR) END AS "Next Birthday" FROM person;

You will get something like this:

MySQL Date Time Math Code Challenge!

Create a table with your four best friends’ first names, last names, and their birthdays.
Create a report which will return the following:

1. First and last name together in one column.
2. Current Age.
3. Create a column – if the birthday is today, return ‘Call Today’, else if the birthday is less than 14 days, display ‘Send Card”, else display Birthday is in (name of month).
4. Only return friends that are within the next 6 months.
5. Test your report by replacing the current date with various hardcoded dates.

First create the table :

CREATE TABLE Friends (FriendID INT NOT NULL AUTO_INCREMENT
                     ,FNAME varchar(50) DEFAULT NULL
                     ,LNAME varchar(50) DEFAULT NULL
                     ,dob date DEFAULT NULL 
                     ,PRIMARY KEY (FriendID));

Fill in data for 4 friends:

INSERT INTO Friends (FNAME, LNAME,dob)
VALUES ('Mashrur', 'Hossain','1982-12-01')
      ,('Matt', 'Berstein','1980-08-05')
      ,('Anastasia', 'Ivanov','1989-04-01')
      ,('Mark', 'Futre','1989-07-04');

Let’s see our table. Run the query:

SELECT * FROM Friends;

It will show you something like this:

Let’s display First and last name together in one column. Run the query:

SELECT CONCAT(FNAME, ' ', LNAME) AS Friends FROM Friends;

It will return this:

Let’s check their Current Age. Run the query as:

SELECT CONCAT(FNAME,' ',LNAME)  AS Friend,TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS AGE FROM Friends;

You will get something like this:

Now, we will go to part 3 where we will create a column – if the birthday is today, return ‘Call Today’, else if the birthday is less than 14 days, display ‘Send Card”, else display Birthday is in (name of month). BUT, we have to do it step by step. First, let’s just display the current date and birthday this year.

Write the query as:

SELECT CONCAT(FNAME,' ',LNAME)  AS Friend

,TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS AGE

,dob

,CURDATE()

,STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y') AS BDAY_THIS_YR

FROM Friends;

It will return something like this:

Along with the above data, let’s find the birthday next year too. Write the query:

SELECT CONCAT(FNAME,' ',LNAME)  AS Friend

,TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS AGE

,dob

,CURDATE()

,STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y') AS BDAY_THIS_YR

,DATE_ADD( STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y'), INTERVAL 1 YEAR ) AS BDAY_NEXT_YR

FROM Friends;

It will return something like this:

Let’s display what will be 6 months from current date and the name of month they born. Write the query:

SELECT CONCAT(FNAME,' ',LNAME)  AS Friend

,TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS AGE

,dob

,CURDATE()

,STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y') AS BDAY_THIS_YR

,DATE_ADD( STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y'), INTERVAL 1 YEAR ) AS BDAY_NEXT_YR

,DATE_ADD(CURDATE(), INTERVAL 6 MONTH) AS 6Months

,MONTHNAME(dob) AS "Birth Month"

FROM Friends;

The Result:

Now, we will see, within next 6 months, who have birthdays out of the 4. It’s a very complex query. Write the code and run it:

SELECT CONCAT(FNAME,' ',LNAME)  AS Friend

,TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS AGE

,dob

,CURDATE()

,STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y') AS BDAY_THIS_YR

,DATE_ADD( STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y'), INTERVAL 1 YEAR ) AS BDAY_NEXT_YR

,DATE_ADD(CURDATE(), INTERVAL 6 MONTH) AS 6Months

,MONTHNAME(dob) AS "Birth Month"

FROM Friends

WHERE CASE

  WHEN STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y') >= CURDATE()

  THEN STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y')

  ELSE DATE_ADD( STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y'), INTERVAL 1 YEAR )

  END BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 6 MONTH);

You will get something like this. Of course, you may find more than 1 result!

Let’s find out who we need to call today for birthday and who will have a birthday within next 1 year. Write the code:

SELECT CONCAT(FNAME,' ',LNAME)  AS Friend

,TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS AGE

,CASE

     WHEN STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y') = CURDATE()

           THEN 'Call Today'

         WHEN CASE

          WHEN STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y') >= CURDATE()

            THEN STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y')

           ELSE DATE_ADD( STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y'), INTERVAL 1 YEAR )

            END <= DATE_ADD(CURDATE(), INTERVAL 14 DAY) 

           THEN 'Send a card'

         ELSE concat('Birthday is in ',MONTHNAME(dob))

       END AS ToDo

FROM Friends

WHERE CASE

         WHEN STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y') >= CURDATE()

           THEN STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y')

         ELSE DATE_ADD( STR_TO_DATE(CONCAT(MONTH(dob),'/',DAY(dob),'/',YEAR(CURDATE())),'%m/%d/%Y'), INTERVAL 1 YEAR )

       END BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 6 MONTH);

You will get something like this:

MySQL: Advanced Update & Delete Statement Tutorial

We always should perform SELECT query first with WHERE statement to see what we are about to update or delete. This is very much important because a single mistake can ruin the entire database!

To understand how MySQL UPDATE works, let’s select all the movies where release year is set to null as:

SELECT title, release_year FROM movies WHERE release_year IS NULL;

You will get something like this:

Now, let’s set the release year of American Sniper to 2014.  We can write the query as:

UPDATE movies SET release_year = 2014 WHERE title = 'American Sniper';

Run the SELECT query to see the change as:

SELECT * FROM movies where title = 'American Sniper';

The Result:

N.B: we could run the UPDATE statement based on MovieID too!

Now, we can UPDATE multiple rows and columns with a single statement. Let’s select some of the entries first as:

SELECT * FROM movies WHERE title IN ('The Irish Man','Star Wars','Fight Club');

You will get something like this:

Let’s set release year to 2019 for all of them as:

UPDATE movies SET release_year = 2019 WHERE title IN ('The Irish Man','Star Wars','Fight Club');

Now, if you run the above SELECT statement again, you will see something like this:

Update based on Comparision

We will create a new table as CustBal to understand how UPDATE statement works in a complex situation. Let’s create the table first as:

CREATE TABLE CustBal (CustID int NOT NULL AUTO_INCREMENT
            ,CheckingBalance DECIMAL(8,2)
            ,SavingsBalance DECIMAL(8,2)
            ,CreditCardBalance DECIMAL(8,2)
            ,CreditCardTotalCredit DECIMAL(8,2)
            ,CreditCardAvailableCredit DECIMAL(8,2)
            ,TotalBalance DECIMAL(8,2)
            ,ActiveStatus boolean
            ,primary key (CustID));

Let’s insert data to the table as:

INSERT INTO CustBal ( CheckingBalance
                     ,SavingsBalance
                     ,CreditCardBalance
                     ,CreditCardTotalCredit
                     ,CreditCardAvailableCredit
                     ,TotalBalance
                     ,ActiveStatus)
VALUES (835.27 , 2223.01 ,   123.09 ,  1000.00 , NULL , NULL , 1)
      ,(165.27 , 1223.01 ,     0.00 ,  1000.00 , NULL , NULL , 1)
      ,(899.27 , 3223.01 ,  9125.09 , 20000.00 , NULL , NULL , 1)
      ,( 0.00 ,    0.00 ,    00.00 ,     0.00 , NULL , NULL , 0)      
      ,(123.27 , 4223.01 ,   123.00 ,   800.00 , NULL , NULL , 1)
      ,( 65.00 , 6223.01 , 21123.09 , 20000.00 , NULL , NULL , 1)
      ,(315.90 , 7223.01 ,     0.00 , 21000.00 , NULL , NULL , 1)
      ,(992.22 , 1923.01 , 20000.00 , 20000.00 , NULL , NULL , 1)
      ,( 75.21 , 4223.01 ,  1123.51 , 15000.00 , NULL , NULL , 1)
      ,(165.22 ,    0.01 , 15123.08 , 16000.00 , NULL , NULL , 0)
      ,(455.23 ,    0.00 ,  4123.00 ,  5000.00 , NULL , NULL , 1);

Run the SELECT statement as:

SELECT * FROM CustBal;

The Result:

Let’s say we wanted a process to update the customer accounts. Use Select Statements to plan out what you want to do. To understand things easily, we will use 2 SELECT statements. Run the first one as:

SELECT CustID
      ,CreditCardTotalCredit
      ,CreditCardBalance 
      ,CreditCardAvailableCredit AS OldCreditCardAvailableCredit
      ,CreditCardTotalCredit - CreditCardBalance AS NewCreditCardAvailableCredit
FROM CustBal 
WHERE ActiveStatus = 1;

Result:

SELECT CustID
      ,CheckingBalance
      ,SavingsBalance
      ,CreditCardBalance
      ,TotalBalance AS OldTotalBalance 
      ,CheckingBalance + SavingsBalance - CreditCardBalance  AS NewTotalBalance 
FROM CustBal 
WHERE ActiveStatus = 1;

Result:

Now, run the UPDATE statement as:

UPDATE CustBal
SET CreditCardAvailableCredit = CreditCardTotalCredit - CreditCardBalance
,TotalBalance = CheckingBalance + SavingsBalance - CreditCardBalance
WHERE ActiveStatus = 1;

To see what changed/updated, run the SELECT statement as:

SELECT * FROM CustBal;

You should see this:

MySQL DELETE Statement

Like the UPDATE function, we should always run the SELECT statement first to check what we are about to delete. From our CustBal table, let’s SELECT all the inactive customers as:

SELECT * FROM CustBal WHERE ActiveStatus = 0;

The Result:

Now, run the DELETE statement as:

DELETE FROM CustBal WHERE ActiveStatus = 0;

Multiple tables and JOINS

So far we have run SELECT, UPDATE, and DELETE statements on a single table. In this section, we will deal with multiple tables at a time.

We will learn how to join them and how to work with joined tables too!

• Primary Key and Foreign Key

We have already seen the primary key but let me rephrase that again. This is the unique identification number of any table data which can be auto incremental or manual.

Now, when you join one table to another table in MySQL, we must reference the second table using its primary key. Meaning, when any table uses the primary key of another table, that table becomes a foreign key!

Let’s explain it in a simple example.

Notice, The table Persons used FavMovie to reference the primary key of table Movies. So, FavMovie is the foreign key of Persons!

• Data Preparation For Joins

This is a complex part of MySQL. So, we must do some hard world and need to have some data preparations.

Right now, we have an actors table.

For the sake of this section, let’s rename the table as people and add more actors.

Rename table name as:

RENAME TABLE actors TO people;

Add more people as:

INSERT INTO people (FIRST_NAME , LAST_NAME)
VALUES ('Steven','Spielberg')
    ('Shawn','Levy') 
    ('Jon','Turteltaub') 
    ('Rawson','Thurber') 
    ('John','Hamburg') 
    ('Adam','McKay') 
    ('Tom','McGrath')
    ('David','Fincher');

Now, if you run the SELECT statement as:

SELECT * FROM people;

It should display something like this:

If you remember correctly, we don’t have any Director column in our movies table. Let’s add that because we will use it as the foreign key for our people table! You can add such column to existing table as:

ALTER TABLE movies ADD COLUMN Director INT NULL;

If you are wondering why I set Director as an integer – please keep in mind that we will use it to reference the ID personID from people.

Now, if you run the SELECT statement for movies as:

SELECT * FROM movies;

You will get something like this:

Now, let’s fill the Director column. I want the actorID of Ben Stiller as the Director number. So far I know, he was in all Zoolander movies including Tropic Thunder. That’s how we should do it:

UPDATE movies SET Director = 1 WHERE MovieID IN (1,6,10);

Now, run the SELECT statement again and you will see this:

Assign more Director number as:

UPDATE movies
SET Director = 17
WHERE MovieId IN (3,4,5);

UPDATE movies
SET Director = 18
WHERE MovieId = 6;

UPDATE movies
SET Director = 19
WHERE MovieId = 8;

UPDATE movies
SET Director = 20
WHERE MovieId = 9;

UPDATE movies
SET Director = 21
WHERE MovieId IN (10,11);

UPDATE movies
SET Director = 22
WHERE MovieId = 12;

UPDATE movies
SET Director = 23
WHERE MovieId = 13;

Now run the SELECT statement again, and you will see this:

Now, we will create a new table for character id for the movie using the MovieID and Actor ID as:

CREATE TABLE Characters (CharacterID INT NOT NULL AUTO_INCREMENT

 MovieID INT NULL

 ActorID INT NULL

 CharacterName VARCHAR(100) NULL

 PRIMARY KEY (CharacterID));

Show the tables as:

show columns from characters;

Now, we will fill the MovieID , ActorID, and CharacterName as below:

INSERT INTO Characters (MovieID, ActorID, CharacterName) VALUES (1,1,'Derek Zoolander');

Similarly, Enter more Character name as:

INSERT INTO characters (MovieID , ActorID, CharacterName) VALUES
(1 , 2 , 'Hansel')
,(1 , 3 , 'Matilda Jeffries')

,(1 , 4 , 'Mugatu')

,(1 , 5 , 'Katinka')

,(1 , 6 , 'Maury Ballstein')

,(1 , 7 , 'J.P. Prewitt')

,(1 , 8 , 'Larry Zoolander')

,(1 , 9 , 'Todd')

,(2 , 1 , 'Derek Zoolander')

,(2 , 2 , 'Hansel')

,(2 , 3 , 'Matilda Jeffries')

,(2 , 4 , 'Mugatu')

,(2 , 5 , 'Katinka')

,(2 , 9 , 'Todd')

,(2 , 10, 'Alexanya Atoz')

,(2 , 11, 'Valentina Valencia')

,(2 , 12, 'Lenny Kravitz')

,(2 , 14, 'Justin Bieber')

,(2 , 15, 'Derek Jr.')

,(5 , 1 , 'Larry Daley')

,(5 , 2 , 'Jedediah')

,(4 , 1 , 'Larry Daley')

,(4 , 2 , 'Jedediah')

,(3 , 1 , 'Larry Daley')

,(3 , 2 , 'Jedediah')

,(3 , 1 , 'Laaa')

,(6 , 8 , 'Patrick Gates')

,(7 , 1 , 'Tugg Speedman')

,(7 , 3 , 'Rebecca')

,(7 , 8 , 'Jon Voight')

,(8 , 3 , 'Kate Veatch')

,(8 , 1 , 'White Goodman')

,(9 , 1 , 'Reuben Feffer');

Run the SELECT query and you will see something like this:

If you notice the above table, both the ActorID and the MovieID will never be the same for a single Character name. With that being said, our data preparation is ready for the next phase!

• MySQL Joins and Aliases

Let’s check our movies table again.

Right now, we have just the id of the director but I need to show their name. So, we need to join the two tables because the name of the director is now on the people table, right?

This is how you can make a MySQL join statement:

SELECT * FROM movies, people
WHERE movies.Director = people.ActorID;

Notice how I referenced the table name for the Director and ActorID. This is how we should do it when we work with multiple tables.

You should get something like this:

Still, this returned data does not look good. We need the director name directly under the Director heading. This is how we can do it:

SELECT movies.title
   ,movies.release_year
   ,movies.rate
   ,CONCAT(people.first_name, ' ', people.last_name) AS Director
   FROM movies, people
   WHERE movies.Director = people.ActorID;

It shall return:

Instead of referencing the table names all the time, we can use alias to make the job a little easier. We can get the same result using alias. This is how we can do it:

SELECT A.title
    ,A.release_year
    ,A.rate
    ,CONCAT(B.first_name, ' ', B.last_name) AS Director
    FROM movies A
    ,people B
    WHERE A.Director = B.ActorID;

And the result will be this:

• Advanced MySQL Joins

Let’s join the movies and characters table. If you remember our characters table, it has a MovieID too. Let’s join them as:

SELECT A.title
    ,A.release_year
    ,A.rate
    ,B.CharacterName
    ,B.ActorID
    FROM movies A, characters B
    WHERE A.MovieID = B.MovieID;

You will see a large result like this:

Now, why zoolander and Avater have multiple entries? Simply because they have multiple CharacterName.

From the above table, we only have the CharacterName and ActorID, NOT the actor name. So, we will now join the people table too to get the actor name! Just run the query:

SELECT A.title
    ,A.release_year
    ,A.rate
    ,B.CharacterName
    ,CONCAT(C.first_name, ' ', C.last_name) AS Actor
    FROM movies A, characters B, people C
    WHERE A.MovieID = B.MovieID
    AND B.ActorID = C.ActorID;

We just joined 3 tables! Now, instead of the ActorID, we should be able to see the actual actor name like this!

We can reference the same table more than once! Let’s add Director name to our above table referencing the people table 2 times. This time we will return data for the Zoolander movie to keep the data table short. Write the query as:

SELECT A.title
    ,CONCAT(D.first_name, ' ', D.last_name) AS Direcotr
    ,A.release_year
    ,A.rate
    ,B.CharacterName
    ,CONCAT(C.first_name, ' ', C.last_name) AS Actor
    FROM movies A, characters B, people C, people D
    WHERE A.MovieID = B.MovieID
    AND B.ActorID = C.ActorID
    AND A.Director = D.ActorID
    AND A.title LIKE "Zoo%";

You should see something like this:

We can get the same result using MySQL INNER JOIN condition too without setting condition in the WHERE clause like this:

SELECT A.title
    ,CONCAT(D.first_name, ' ', D.last_name) AS Direcotr
    ,A.release_year
    ,A.rate
    ,B.CharacterName
    ,CONCAT(C.first_name, ' ', C.last_name) AS Actor
    FROM movies A
    INNER JOIN characters B
    ON A.MovieID = B.MovieID
    INNER JOIN people C
    ON B.ActorID = C.ActorID
    INNER JOIN people D
    ON A.Director = D.ActorID
    WHERE A.title LIKE "Zoo%";

Try it, you will get the same result. Of course, you can use any way you like. Both of them are fine to use.

• Inner Outer Left Right Joins

The main types of MySQL joins are LEFT OUTER JOIN, RIGHT OUTER JOIN, INNER JOIN, and FULL OUTER JOIN.

Imagine we have two tables: