SQL Exercise: Information about the department Marketing

SQL SORTING and FILTERING on HR Database: Exercise-5 with Solution

5. From the following table, write a SQL query to find the details of 'Marketing' department. Return all fields.

+---------------+----------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME      | MANAGER_ID | LOCATION_ID |
+---------------+----------------------+------------+-------------+
|            10 | Administration       |        200 |        1700 |
|            20 | Marketing            |        201 |        1800 |
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|            60 | IT                   |        103 |        1400 |
|            70 | Public Relations     |        204 |        2700 |
|            80 | Sales                |        145 |        2500 |
|            90 | Executive            |        100 |        1700 |
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |          0 |        1700 |
|           130 | Corporate Tax        |          0 |        1700 |
|           140 | Control And Credit   |          0 |        1700 |
|           150 | Shareholder Services |          0 |        1700 |
|           160 | Benefits             |          0 |        1700 |
|           170 | Manufacturing        |          0 |        1700 |
|           180 | Construction         |          0 |        1700 |
|           190 | Contracting          |          0 |        1700 |
|           200 | Operations           |          0 |        1700 |
|           210 | IT Support           |          0 |        1700 |
|           220 | NOC                  |          0 |        1700 |
|           230 | IT Helpdesk          |          0 |        1700 |
|           240 | Government Sales     |          0 |        1700 |
|           250 | Retail Sales         |          0 |        1700 |
|           260 | Recruiting           |          0 |        1700 |
|           270 | Payroll              |          0 |        1700 |
+---------------+----------------------+------------+-------------+

Sample Solution:

-- Selecting all columns from the 'departments' table
SELECT *
-- Specifying the table to retrieve data from ('departments')
FROM departments
-- Filtering the results based on the condition that 'department_name' is equal to 'Marketing'
WHERE department_name = 'Marketing';

Sample Output:

 department_id | department_name | manager_id | location_id
---------------+-----------------+------------+-------------
            20 | Marketing       |        201 |        1800
(1 row)

Code Explanation:

The said query in SQL that retrieves all columns from the 'departments' table where the department name is 'Marketing'. As a result of the query, a list of all rows in the 'departments' table that match the conditions specified in the "WHERE" clause is returned. The "SELECT *" clause retrieves all columns from the table.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Details of employees without any department number.
Next SQL Exercise: Employees first name does not contain the letter M .