Library

Course: MY SQL for Data Manipulation and Analysis with Real Life Practical Examples

MY SQL for Data Manipulation and Analysis with Real Life Practical Examples

  • Life Time Access
  • Certificate on Completion
  • Access on Android and iOS App
About this Course

About This Class

Hi! Welcome to MY SQL Course for manipulating and analyzing data.

We are excited to present you a course that stands out.

When you can work with SQL, it means you don’t have to rely on others sending you data and executing queries for you. You can do that on your own. This allows you to be independent and dig deeper into the data to obtain the answers to questions that might improve the way your company does its business.

Basic knowledge
  • No prior experience is required. We will start from the very basics
  • You’ll need to install MySQL
  • We will show you how to do it step by step
What you will learn
  • What you will Learn
  • Use SQL to create, design, and manipulate SQL databases
Curriculum
Number of Lectures: 53
Total Duration: 03:20:07
Relational Database Theory and Introduction to SQL
  • 1 Why Use SQL?  
  • 2 Why MYSQL?  
  • 3 Intro to Databases  
  • 4 Relational Database Fundamentals  
  • 5 Comparing Database and Spreadsheets  
  • 6 Importance of Database Terminology  
  • 7 The Concept of A Relational Schema (Primary Key)  
  • 8 The Concept of A Relational Schema (Foreign Key)  
  • 9 The Concept of Relational Schemas  
  • 10 The Concept of Relational Schemas (Relationships between Tables)  
Install and Get to Know MYSQL
  • 1 Install MYSQL Workbench Server  
  • 2 Link GUI with MY SQL Server  
  • 3 Familiarise Yourself with MYSQL Interface  
Best SQL Practices
  • 1 Coding Tips and Best Practice 1  
  • 2 Coding Tips and Best Practice 2  
  • 3 Loading the Employee Database  

    Click on the link below to download the Employee Database.



    https://1drv.ms/u/s!AlplhCyuvwT8aYZtRThsY5cFHnc

Practical Application of the SELECT Statement
  • 1 Using SELECT from  

    Exercise 1


    Select the information from the “dept_no” column of the “departments” table.

    Select all data from the “departments” table.

  • 2 Using WHERE  

    Exercise 2


    Select all people from the “employees” table whose first name is “Elvis”.

  • 3 Using AND  

    Exercise 3


    Retrieve a list with all female employees whose first name is Kellie. 

  • 4 Using OR  

    Exercise 4


    Retrieve a list with all employees whose first name is either Kellie or Aruna.

  • 5 Operator Precedence and Logical Order  

    Exercise 5


    Retrieve a list with all female employees whose first name is either Kellie or Aruna.

  • 6 Using IN NOT IN  

    Exercise 6a


    Use the IN operator to select all individuals from the “employees” table, whose first name is either “Denis”, or “Elvis”.


    Exercise 6b


    Extract all records from the ‘employees’ table, aside from those with employees named John, Mark, or Jacob.

  • 7 Using LIKE NOT LIKE  

    Exercise 7


    Working with the “employees” table, use the LIKE operator to select the data about all individuals, whose first name starts with “Mark”; specify that the name can be succeeded by any sequence of characters.

    Retrieve a list with all employees who have been hired in the year 2000.

    Retrieve a list with all employees whose employee number is written with 5 characters, and starts with “1000”. 

  • 8 Using Wild Characters  

    Exercise 8


    Extract all individuals from the ‘employees’ table whose first name contains “Jack”.

    Once you have done that, extract another list containing the names of employees that do not contain “Jack”.

  • 9 Using Between AND  

    Exercise 9


    Select all the information from the “salaries” table regarding contracts from 66,000 to 70,000 dollars per year.

    Retrieve a list with all individuals whose employee number is not between ‘10004’ and ‘10012’.

    Select the names of all departments with numbers between ‘d003’ and ‘d006’.

  • 10 Using IS NOT NULL IS NULL  

    Exercise 10


    Select the names of all departments whose department number value is not null.

  • 11 Using Other Comparison Operators  

    Exercise 11:


    Retrieve a list with data about all female employees who were hired in the year 2000 or after.

    Hint: If you solve the task correctly, SQL should return 7 rows.

    Extract a list with all employees’ salaries higher than $150,000 per annum.

  • 12 Using SELECT DISTINCT  

    Exercise 12:


    Obtain a list with all different “hire dates” from the “employees” table.

    Expand this list and click on “Limit to 1000 rows”. This way you will set the limit of output rows displayed back to the default of 1000.

    In the next lectures, we will show you how to manipulate the limit rows count. 

  • 13 Getting to Know Aggregate Functions  

    Exercise 13


    How many annual contracts with a value higher than or equal to $100,000 have been registered in the salaries table?

    How many managers do we have in the “employees” database? Use the star symbol (*) in your code to solve this exercise.

  • 14 Using ORDER BY  

    Exercise 14


    Select all data from the “employees” table, ordering it by “hire date” in descending order.

  • 15 Using GROUP BY  




  • 16 Using Aliases  

    Exercise 16:

    This will be a slightly more sophisticated task.

    Write a query that obtains an output whose first column must contain annual salaries higher than 80,000 dollars. The second column, renamed to “emps_with_same_salary”, must show the number of employee contracts signed with this salary.

  • 17: Using HAVING  

    Exercise 17:


    Select all employees whose average salary is higher than $120,000 per annum.

    Hint: You should obtain 101 records.

    Compare the output you obtained with the output of the following two queries:

    SELECT

       *, AVG(salary)

    FROM

       salaries

    WHERE

       salary > 120000

    GROUP BY emp_no

    ORDER BY emp_no;

     

    SELECT

       *, AVG(salary)

    FROM

       salaries

    GROUP BY emp_no

    HAVING AVG(salary) > 120000;

  • 18 WHERE VS HAVING  

    Exercise 18


    Select the employee numbers of all individuals who have signed more than 1 contract after the 1st of January 2000.

    Hint: To solve this exercise, use the “dept_emp” table.

  • 19: Using LIMIT  

    Exercise 19:

    Select the first 100 rows from the ‘dept_emp’ table. 

Expanding of My Aggregate Functions
  • 1 APPLYING COUNT  

    Exercise 1


    How many departments are there in the “employees” database? Use the ‘dept_emp’ table to answer the question.

  • 2 Applying SUM  

    Exercise 2


    What is the total amount of money spent on salaries for all contracts starting after the 1st of January 1997?

  • 3 MIN AND MAX  

    Exercise 3


    1. Which is the lowest employee number in the database?

    2. Which is the highest employee number in the database?

  • 4 Applying AVG  

    Exercise 4


    What is the average annual salary paid to employees who started after the 1st of January 1997?

  • 5 Rounding Numbers with ROUND  

    Exercise 5

    Round the average amount of money spent on salaries for all contracts that started after the 1st of January 1997 to a precision of cents.

SQL JOINS
  • 1 What are JOINS?  

    Exercise 1


    Create and fill in the ‘departments_dup’ table, using the following code:


    DROP TABLE IF EXISTS departments_dup;

    CREATE TABLE departments_dup

    (

       dept_no CHAR(4) NULL,

       dept_name VARCHAR(40) NULL

    );

     

    INSERT INTO departments_dup

    (

       dept_no,

       dept_name

    )SELECT

                   *

    FROM

                   departments;

     

    INSERT INTO departments_dup (dept_name)

    VALUES              ('Public Relations');

     

    DELETE FROM departments_dup

    WHERE

       dept_no = 'd002'; 

       

    INSERT INTO departmentsdup(deptno) VALUES ('d010'), ('d011');



    Exercise 2


    Create and fill in the ‘dept_manager_dup’ table, using the following code:


    DROP TABLE IF EXISTS dept_manager_dup;

    CREATE TABLE dept_manager_dup (

     emp_no int(11) NOT NULL,

     dept_no char(4) NULL,

     from_date date NOT NULL,

     to_date date NULL

     );

     

    INSERT INTO dept_manager_dup

    select * from dept_manager;

     

    INSERT INTO dept_manager_dup (emp_no, from_date)

    VALUES               (999904, '2017-01-01'),

                                  (999905, '2017-01-01'),

                                 (999906, '2017-01-01'),

                                (999907, '2017-01-01');

     

    DELETE FROM dept_manager_dup

    WHERE

       dept_no = 'd001';

      

  • 2 The Functionalities of INNER JOIN 1  




  • 3 The Functionalities of INNER JOIN 2  

    Exercise 2


    Extract a list containing information about all managers’ employee number, first and last name, department number, and hire date. 

  • 4 Extra Info of Using JOINS  
  • 5 Duplicate Rows  
  • 6 The Functionality of LEFT JOIN PART 1  
  • 7 The Functionality of LEFT JOIN PART 2  

    Exercise


    Join the 'employees' and the 'dept_manager' tables to return a subset of all the employees whose last name is Markovitch. See if the output contains a manager with that name.  

    Hint: Create an output containing information corresponding to the following fields: ‘emp_no’, ‘first_name’, ‘last_name’, ‘dept_no’, ‘from_date’. Order by 'dept_no' descending, and then by 'emp_no'.

  • 8 The Functionality of RIGHT JOIN  
  • 10 Using JOINS and WHERE together  

    Exercise

    Select the first and last name, the hire date, and the job title of all employees whose first name is “Margareta” and have the last name “Markovitch”.

  • 11 The Functionality of CROSS JOIN  

    Exercise 1

    Use a CROSS JOIN to return a list with all possible combinations between managers from the dept_manager table and department number 9.


    Exercise 2


    Return a list with the first 10 employees with all the departments they can be assigned to.

    Hint: Don’t use LIMIT; use a WHERE clause.

  • 12 Combining Aggregate Functions with JOINS  
  • 13 JOIN more than two Tables  

    Exercise


    Select all managers’ first and last name, hire date, job title, start date, and department name.

  • 14 Top Tips for Using JOINS  

    Exercise


    How many male and how many female managers do we have in the ‘employees’ database?

Reviews (0)