Create SELECT statements involving multiple tables by using joins

Description

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.