Description
Objective: Create SELECT statements involving multiple tables by using joins
Problem 1:
Create a query displaying the employee_id, start_date, end_date and department_name using the old SQL join syntax
(Where clause). Alias the departments table with d and the job_history table with jh. Order it by employee_id and
start_date.
Problem 2:
Rewrite the previous query using the new SQL join syntax (From clause).
Problem 3:
Rewrite the previous query using the following syntax variations:
A.) Using the Using keyword
B.) Using the Natural Join keywords
Problem 4:
Show the count of records of table employees and table job_history (2 queries). How many records do you receive when
you join the two tables using a cartesian join (either show query or briefly explain)?
1. Now join the two tables using an inner join on column employee_id. Show the employee_id, last_name, start_date
and end_date. Alias the table employees with e and job_history with jh.
2. Finally, join the two tables using a natural inner join using the columns in the SELECT clause.
3. Explain the differences in output between the queries in 1) and 2).
Problem 5:
Create a query showing employee_id, start_date, end_date, department_name and job_title. Display the dates columns
in the format of mm/dd/yyyy. Order it by department_name and job_title. (join Departments, Job_History, Jobs)
Problem 6:
Create a query showing job_title, min_salary, max_salary from the jobs table and employee_id, start_date from the
job_history table. Make sure to display all records from the jobs table. Order it by job title.
Modify the previous query to find out how many job_titles are not used in the job_history table.
Problem 7:
Create a query showing employee_id, last_name, and salary from table employees and min_salary and max_salary from
table jobs. Filter the data where the salary matches the midpoint of min_salary and max_salary. Sort the resulting data
set by job_id and last_name.
Problem 8:
Create a query showing the last_name from table employees and the following derived expression:
Concatenate the area code (first 3 numbers of phone_number), the city, and the country_name using a hyphen in
between: area_code-city-country_name
Sort the resulting dataset by country_name, city, and last_name.