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.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics