SQLite Exercise: Find the employee ID, job title number of days between ending date and starting date for all jobs in department 90 from job history

Write a query to find the employee ID, job title number of days between ending date and starting date for all jobs in department 90 from job history.

Sample table : job_history

employee_id  start_date  end_date    job_id      department_id
-----------  ----------  ----------  ----------  -------------
employee_id  start_date  end_date`   job_id      department_id
102          1993-01-13  1998-07-24  IT_PROG     60
101          1989-09-21  1993-10-27  AC_ACCOUNT  110
101          1993-10-28  1997-03-15  AC_MGR      110
201          1996-02-17  1999-12-19  MK_REP      20
114          1998-03-24  1999-12-31  ST_CLERK    50
122          1999-01-01  1999-12-31  ST_CLERK    50
200          1987-09-17  1993-06-17  AD_ASST     90
176          1998-03-24  1998-12-31  SA_REP      80
176          1999-01-01  1999-12-31  SA_MAN      80
200          1994-07-01  1998-12-31  AC_ACCOUNT  90

Sample table : jobs

+------------+---------------------------------+------------+------------+
| JOB_ID     | JOB_TITLE                       | MIN_SALARY | MAX_SALARY |
+------------+---------------------------------+------------+------------+
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
+------------+---------------------------------+------------+------------+

SQLite Code:

-- Selecting employee_id, job_title, and the number of days worked
SELECT 
    employee_id,    -- Selecting the employee ID from the job_history table
    job_title,      -- Selecting the job title from the jobs table
    -- Calculating the number of days worked by using the JULIANDAY function to get the difference in days between end_date and start_date
    JULIANDAY(end_date) - JULIANDAY(start_date) AS 'Number of days worked'
FROM 
    job_history     -- From the job_history table
    NATURAL JOIN jobs -- Joining with the jobs table using NATURAL JOIN which joins tables based on columns with the same names
WHERE 
    department_id = 90;  -- Filtering results to include only records where department_id is 90

Explanation:

• The SQL query selects data from two tables, job_history and jobs, to find the number of days worked by employees in department 90.
• It calculates the number of days worked by subtracting the Julian day number of the start date from the Julian day number of the end date.
• The NATURAL JOIN clause automatically joins the tables based on columns with the same name, allowing the query to retrieve job titles from the jobs table.
• The WHERE clause filters the results to include only employees in department 90.
• Finally, the results are displayed with the employee ID, job title, and the calculated number of days worked.

Output:

employee_id    |job_title                      |Number of days worked|
---------------+-------------------------------+---------------------+
200            |Administration Assistant       |                 2100| 
200            |Public Accountant              |                15000|

Practice SQLite Online

Model Database

Structure of 'hr' database :

Improve this sample solution and post your code through Disqus.

Previous: Write a query to get the department name and number of employees in the department.
Next: Write a query to display the department ID, department name, and manager first name.