#sql exercise

One way you can work on your SQL skills without local data access is through SQL Exercise website. Once you register, you will be provided with the name of the tables and columns and the query you need to write. After you write your SQL you can check for accuracy by clicking on the “Run” button.

Here is a couple of snapshots of how SQL exercise work.

I find the above website to be a good tool to practice SQL without installing any database engine on your local machine.

The next section will have exercise on all the elements we learned in the previous articles using the database schema below. Answers will be provided at the end of this article to review your work.

Let’s assume the above database schema belongs to a company you are consulting with and you need to answer the following questions. (I would suggest that you create the table and populate them as a part of the exercise. However, if you want the script for the tables in the above diagram, let me know. I will upload it in Github.)

1. The total number of departments, the total number of employee and the total number of managers.

2. List the employees in “Marketing” Department.

3. Total number of managers with title “Vice President” and name of the “Vice President” who has held that title the longest

4. List the Employees who have been in more than one department.

5. Find the newest employee and the employee who has been with the company the longest.

6.  Find the % of Employees who are managers

7. List the employees and their titles who joined the company in the first year of operation.

8. List the current lowest and the highest salary.

9. Total number of Male and Female Employees

10. The total number of employees hired each year in the past three years.

With this, I will wrap up the first chapter of “Database for Digital Marketers”. In the next chapter, I will cover the basics of data analysis and various methods of understanding, analyzing and interpreting marketing data.

----------------------------------------------------------------------------------------------------------

Answers:

--1. The total number of departments, the total number of employees and the total number of managers.

Select count(*) from departments

Select count(*) from dept_manager

Select count(*) from employees

--2. List the employees in “Marketing” Department.

Select e.First_name,e.Last_name

from employees e

Join dept_emp b

On e.emp_no=b.emp_no

Join departments d

On b.dept_no=d.dept_no

Where d.dept_name='Marketing'

--3. Total number of managers with title “Vice president” and name of the “Vice president” who has hold that title the longest

Select count(*) from

Titles where title = 'Vice President'

Select top 1 e.First_name, e.last_name, datediff(DD,from_date,to_date)/365 as number_years_as_vp

From

employees e

Join Titles t

on e.emp_no=t.emp_no

where title = 'Vice President'

Order by datediff(DD,from_date,to_date) desc

--4. List the employees who have been in more than one department.

Select  Emp_no, count(dept_no)

From dept_emp

Group by Emp_no

Having count(dept_no) > 1

--5. Find the newest employees.

Select first_name,last_name

From employees where hire_date = (Select min(hire_date) from employees)

--6. Find the % of Employees who are managers

Select Count(Manager)*100/Count(AllEmployee)

From

(Select

Case when m.emp_no is not null then m.emp_no else null end as Manager,

e.emp_no  as AllEmployee

From employees e

Left join dept_manager m

On e.emp_no= m.emp_no) a

--7. List the employees and their titles who joined the company in the first year of operation.

Select first_name,last_name,titles.title

From employees

join titles

on employees.emp_no=titles.emp_no

where hire_date <= (Select dateadd(dd,365,min(hire_date)) from employees)

--8. List the current lowest and the highest salary.

Select min(salary), max(salary)

From salaries

Where to_date> GETDATE()

--9. Total number of Male and Female Employees

Select count(case when gender='M' Then emp_no else null end) as MaleemployeesCount,

count(case when gender='M' Then emp_no else null end) as FemaleemployeesCount

From employees

--10. The total number of employees hired each year.

Select Year(hire_date) as hire_year,count(*) from employees