w3resource

SQL SORTING and FILTERING on HR Database: Display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80

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

20. From the following table, write a SQL query to find those employees who are working in the departments, which are not included in the department number 50 or 30 or 80. Return employee_id, first_name, job_id, department_id.

Sample table : employees


Sample Solution:

SELECT employee_id, first_name, job_id, department_id
 FROM employees
  WHERE department_id NOT IN (50, 30, 80);

Sample Output:

 employee_id | first_name  |   job_id   | department_id
-------------+-------------+------------+---------------
         100 | Steven      | AD_PRES    |            90
         101 | Neena       | AD_VP      |            90
         102 | Lex         | AD_VP      |            90
         103 | Alexander   | IT_PROG    |            60
         104 | Bruce       | IT_PROG    |            60
         105 | David       | IT_PROG    |            60
         106 | Valli       | IT_PROG    |            60
         107 | Diana       | IT_PROG    |            60
         108 | Nancy       | FI_MGR     |           100
         109 | Daniel      | FI_ACCOUNT |           100
         110 | John        | FI_ACCOUNT |           100
         111 | Ismael      | FI_ACCOUNT |           100
         112 | Jose Manuel | FI_ACCOUNT |           100
         113 | Luis        | FI_ACCOUNT |           100
         178 | Kimberely   | SA_REP     |             0
         200 | Jennifer    | AD_ASST    |            10
         201 | Michael     | MK_MAN     |            20
         202 | Pat         | MK_REP     |            20
         203 | Susan       | HR_REP     |            40
         204 | Hermann     | PR_REP     |            70
         205 | Shelley     | AC_MGR     |           110
         206 | William     | AC_ACCOUNT |           110
(22 rows)

Relational Algebra Expression:

Relational Algebra Expression: Display  the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80.

Relational Algebra Tree:

Relational Algebra Tree: Display  the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80 - Duration

Rows:

Query visualization of Display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80 - Rows

Cost:

Query visualization of Display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80 - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees whose first name contains a character 's' in 3rd position. Return first name, last name and department id.
Next: From the following table, write a SQL query to find those employees whose department numbers are included in 30 or 40 or 90. Return employee id, first name, job id, department id.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

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