Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Combining Table Results | Nested Subqueries
course content

Зміст курсу

Intermediate SQL

1. Grouping

GROUP BYOrder of StatementsTotal Ride Time for Each Subway Line ChallengeAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVINGSeveral Aggregate Functions in the QueryMore Complex Filtering

2. Nested Subqueries

Nested QueriesNon-tech Department Employees ChallengeUsing Inner Subqueries in the FROM SectionEmployees With More Than Average Salary ChallengeUNION ClauseCombining Table Results

3. Joining Tables

JOIN 2 TablesGet Category By Product PriceGet Minimum Price By CategoryGet Product Amount By CategoryLEFT, RIGHT and INNER JOINsTable Join PracticeFULL JOIN Usage

4. DDL and DML in SQL

CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

Intermediate SQL

Combining Table ResultsCombining Table Results

In this chapter, we'll practice with the UNION clause. We'll be working with these two tables:

employees:

employee_id first_name last_name department salary hire_date
1 John Doe Engineering 80000.00 2015-03-01T00:00:00Z
2 Jane Smith Engineering 90000.00 2017-08-15T00:00:00Z
3 Alice Johnson Marketing 75000.00 2016-11-10T00:00:00Z
4 Bob Brown Marketing 72000.00 2018-06-25T00:00:00Z
... ... ... ... ... ...
100 William Solis Sales 55000.00 2020-05-01T00:00:00Z

contractors:

contractor_id first_name last_name email
1 Alice Johnson alice.johnson@contractor.com
2 Bob Brown bob.brown@contractor.com
3 Charlie Davis charlie.davis@contractor.com
4 David Evans david.evans@contractor.com
... ... ... ...
50 Yvonne Zimmer yvonne.zimmer@contractor.com

Завдання

For practice, your task will be to combine the employees and contractors tables using the UNION clause.

After that, from the resulting table, you should retrieve only those IDs that are multiples of 2. Also, you need to retrieve the first_name and last_name columns. Use the syntax WHERE id % 2 = 0. For this task, you need to use subqueries in the FROM section just as shown in the example.

Good luck!

Note

As a result, you should have 3 columns: id (an alias for the ID from both tables), first_name, last_name.

Все було зрозуміло?

Секція 2. Розділ 6
toggle bottom row
course content

Зміст курсу

Intermediate SQL

1. Grouping

GROUP BYOrder of StatementsTotal Ride Time for Each Subway Line ChallengeAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVINGSeveral Aggregate Functions in the QueryMore Complex Filtering

2. Nested Subqueries

Nested QueriesNon-tech Department Employees ChallengeUsing Inner Subqueries in the FROM SectionEmployees With More Than Average Salary ChallengeUNION ClauseCombining Table Results

3. Joining Tables

JOIN 2 TablesGet Category By Product PriceGet Minimum Price By CategoryGet Product Amount By CategoryLEFT, RIGHT and INNER JOINsTable Join PracticeFULL JOIN Usage

4. DDL and DML in SQL

CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

Intermediate SQL

Combining Table ResultsCombining Table Results

In this chapter, we'll practice with the UNION clause. We'll be working with these two tables:

employees:

employee_id first_name last_name department salary hire_date
1 John Doe Engineering 80000.00 2015-03-01T00:00:00Z
2 Jane Smith Engineering 90000.00 2017-08-15T00:00:00Z
3 Alice Johnson Marketing 75000.00 2016-11-10T00:00:00Z
4 Bob Brown Marketing 72000.00 2018-06-25T00:00:00Z
... ... ... ... ... ...
100 William Solis Sales 55000.00 2020-05-01T00:00:00Z

contractors:

contractor_id first_name last_name email
1 Alice Johnson alice.johnson@contractor.com
2 Bob Brown bob.brown@contractor.com
3 Charlie Davis charlie.davis@contractor.com
4 David Evans david.evans@contractor.com
... ... ... ...
50 Yvonne Zimmer yvonne.zimmer@contractor.com

Завдання

For practice, your task will be to combine the employees and contractors tables using the UNION clause.

After that, from the resulting table, you should retrieve only those IDs that are multiples of 2. Also, you need to retrieve the first_name and last_name columns. Use the syntax WHERE id % 2 = 0. For this task, you need to use subqueries in the FROM section just as shown in the example.

Good luck!

Note

As a result, you should have 3 columns: id (an alias for the ID from both tables), first_name, last_name.

Все було зрозуміло?

Секція 2. Розділ 6
toggle bottom row
some-alt