#5 -> More basic queries in MySQL

#5 -> More basic queries in MySQL

Akshat Sharma's photo
Akshat Sharma
·

7 min read

Before we start we need to set up a database to run the queries, the below article will help you with the same.

Let us run a few queries on the database that we just created.

  • Just to make sure that all the tables have been flooded properly.
SELECT * FROM employee;
SELECT * FROM branch;
SELECT * FROM branch_supplier;
SELECT * FROM client;
SELECT * FROM works_with;

image.png

image.png

image.png

image.png

image.png

  • If we need to select the DISTINCT branch_id that the employee works for.
SELECT DISTINCT branch_id FROM employee;

image.png

  • Count the number of employees.
SELECT COUNT(emp_id)
FROM employee;

image.png

  • What if we need to find all the male employees that were born after 1999-01-01.
SELECT COUNT(emp_id) 
FROM employee 
WHERE sex = 'M' AND birth_date > '1999-01-01';

image.png

  • How much is the company paying the male employees, we need to find out the total salary.
SELECT SUM(salary)
FROM employee
WHERE sex = 'M';

image.png

  • If we want to determine how many male and how many female employees are working in the company.
SELECT COUNT(sex),sex
FROM employee
GROUP BY sex;

image.png

  • How much total sales are done by each employee, we need to find this information using the works_with table.
SELECT SUM(total_sales),emp_id
FROM works_with
GROUP BY emp_id;

image.png

  • These are just a few examples, you can poke around the database as you feel like.

Wild Card in MySQL

MySQL provides two wildcard characters for constructing patterns: percentage % and underscores _.

  • The percentage ( % ) wildcard matches any string of zero or more characters.
  • The underscore ( _ ) wildcard matches any single character.

Let's run a few queries using the wildcards.

  • Select all information where the client name has 'Bank' in it. 
SELECT * 
FROM client
WHERE client_name LIKE '%Bank%';

image.png

  • Select branch_supplier information who sells Pens.
SELECT *
FROM branch_supplier
WHERE supply_type LIKE '%Pen';

image.png

  • Select the first and last name of the employees that were born in November.
SELECT first_name,last_name
FROM employee
WHERE birth_date LIKE '____-11%';

image.png

Using the UNION operator in MySQL

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns. The columns must also have similar data types. The columns in each SELECT statement must also be in the same order.

  • What if we want to find the list of all the clients and the branch suppliers and combine it into one single list.
SELECT client_name , branch_id
FROM client 
UNION
SELECT supplier_name , branch_id
FROM branch_supplier;

image.png

  • List of all the money spent or earned by the company.
SELECT salary AS total_money 
FROM employee
UNION
SELECT total_sales
FROM works_with;

image.png

Joins in MySQL

Joins help retrieving data from two or more database tables. These tables are mutually related using primary and foreign keys.

  • Let,s assume that we need to find all the branches and the names of their managers. Here we make use of JOIN so we can connect the two tables using the emp_id and the mgr_id. The below is referred to as the INNER JOIN.
SELECT employee.emp_id , employee.first_name , branch.branch_name 
FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id;

image.png

  • There is something called as LEFT JOIN, what is does is that it will include all the employees from the employee's table. In this type of join, we include all of the information from the left TABLE (table mentioned in the FROM statement).
SELECT employee.emp_id , employee.first_name , branch.branch_name 
FROM employee
LEFT JOIN branch
ON employee.emp_id = branch.mgr_id;

image.png

  • Similar to the above LEFT JOIN we have a RIGHT JOIN which will include all the information from the RIGHT table. It is just the opposite of the LEFT JOIN.
SELECT employee.emp_id , employee.first_name , branch.branch_name 
FROM employee
RIGHT JOIN branch
ON employee.emp_id = branch.mgr_id;

image.png

Nested Queries

Sometimes in order to get the information we might need to right multiple queries and multiple select statements. In nested queries, a query is written inside a query. The result of an inner query is used in the execution of an outer query.

  • Let's assume we need to find the names of the employees who have sold over 60,000 to a single client.
SELECT employee.first_name , employee.last_name
FROM employee
WHERE employee.emp_id IN (
    SELECT works_with.emp_id
    FROM works_with
    WHERE works_with.total_sales > 60000
);

image.png

  • Find all the clients who are handled by the branch that Jaylee Pascal manages, we know that his ID is 1000.
SELECT client.client_name
FROM client 
WHERE client.branch_id = (
    SELECT branch.branch_id
    FROM branch 
    WHERE branch.mgr_id = 1000
    LIMIT 1
);

image.png

  • All the nested queries are just one particular query trying to get information from different queries.

Thank-you!

I am glad you made it to the end of this article. I hope you got to learn something, if so please leave a Like which will encourage me for my upcoming write-ups.

SQLMySQLDatabasesdatabaseBeginner Developers