• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 8/29/24

Get Some Practice Executing Simple SQL Queries

It's Your Turn!

The goal of this exercise is to write queries in a database to find the answers to specific questions. This database models a company in which people are employed in different departments.

The database you'll use contains the following six tables:

  • employees: company employees

  • titles: the job title under which the employee was hired

  • salaries: the salaries of the employees

  • departments: the company’s departments

  • dept_manager: the managers (heads) of the departments

  • dept_emp: the employees associated with a given department 

These tables are summarized in this diagram:

Employees database scheme
Employees database scheme 

Data and Tools

You have two options, using MySQL or SQLite. Neither option is better than the other, but I would recommend using SQLite.

If you want to use SQLite, you will have to:

  • Install SQLiteStudio or SQLite-Browser which are user interface clients to interact with the SQLite database system.

  • Download SQLIite file.

  • Launch SQLiteStudio or SQLIte-Browser from your computer.

  • Click on "Open Database" or "Load Database" and then select the dowloaded file.

If you want to useMySQL, you will have to:

Both databases have the same structure but the SQLite database is just an extract of the MySQL database. The results will therefore be different from one system to another.

Further Instructions

For each question, provide the SQL query you used to obtain the result, along with a screen capture of the table that was returned by your query. If the result of a query contains many rows, a screen capture of only the first few rows is sufficient.

  1. Display all of the rows of the employees table, showing only the employees’ first and last names.

  2. Display all employees who were hired after August 1, 1999 (non-inclusive).

  3. In this database, two tables are of the same schema. Combine them using a Union operator.

  4. Display the identifier and different salaries of the employee who was born on this date:  '1964-01-31', using a join.

  5. Using the join operator, produce a table specifying the name(s) of the department(s) in which the employee associated with identifier 499902 has worked (the table must also contain the start and end dates of employment in each department, as well as the employee’s last name and first name).

  6. Using aggregation, find out how many people have the last name "Gewali" (the last name is provided in the last_name column of the "employees" table).

Deliverables

  • The SQL queries

  • A screen capture of the table

Check Your Work!

Ever considered an OpenClassrooms diploma?
  • Up to 100% of your training program funded
  • Flexible start date
  • Career-focused projects
  • Individual mentoring
Find the training program and funding option that suits you best
Example of certificate of achievement
Example of certificate of achievement