w3resource

SQL Exercise: Details of employees without any department number

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

4. From the following table, write a SQL query to identify employees who do not have a department number. Return employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary,commission_pct, manager_id and department_id.

Sample table: employees


Sample Solution:

SELECT *
 FROM employees
  WHERE department_id IS NULL;

Sample Output:

employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+-------+--------------+-----------+--------+--------+----------------+------------+-------------
(0 rows)

Code Explanation:

The said query in SQL that retrieves all columns from the 'employees' table where the department ID is NULL. The query returns all the rows from the 'employees' table that meet the condition specified in the "WHERE" clause. The "IS NULL" clause is used to check if the value in the "department_id" column is NULL or not.

Relational Algebra Expression:

Relational Algebra Expression: Display all the information for all employees without any department number.

Relational Algebra Tree:

Relational Algebra Tree: Display all the information for all employees without any department number.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display all the information for all employees without any department number - Duration

Rows:

Query visualization of Display all the information for all employees without any department number - Rows

Cost:

Query visualization of Display all the information for all employees without any department number - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Display Employees whose last name is McEwen.
Next SQL Exercise: Information about the department Marketing.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook