Sheet 5 - Problem 2

--Creating tables:
CREATE TABLE jobs (JOB_ID VARCHAR(30) PRIMARY KEY, JOB_TITLE VARCHAR(50) NOT NULL, MIN_SALARY MONEY NOT NULL, MAX_SALARY MONEY NOT NULL);
CREATE TABLE job_history (EMPLOYEE_ID INT NOT NULL, START_DATE DATE NOT NULL, END_DATE DATE NOT NULL, job_id VARCHAR(30) FOREIGN KEY REFERENCES jobs(JOB_ID), department_id INT NOT NULL);

--Populating jobs table:
INSERT INTO jobs VALUES ('AD_PRES', 'President', 20080, 40000);
INSERT INTO jobs VALUES ('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO jobs VALUES ('AD_ASST', 'Administration Assistant', 3000, 6000);
INSERT INTO jobs VALUES ('FI_MGR', 'Finance Manager', 8200, 16000);
INSERT INTO jobs VALUES ('FI_ACCOUNT', 'Accountant', 4200, 9000);
INSERT INTO jobs VALUES ('AC_MGR', 'Accounting Manager', 8200, 16000);
INSERT INTO jobs VALUES ('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
INSERT INTO jobs VALUES ('SA_MAN', 'Sales Manager', 10000, 20080);
INSERT INTO jobs VALUES ('SA_REP', 'Sales Representative', 6000, 12008);
INSERT INTO jobs VALUES ('PU_MAN', 'Purchasing Manager', 8000, 15000);
INSERT INTO jobs VALUES ('PU_CLERK', 'Purchasing Clerk', 2500, 5500);
INSERT INTO jobs VALUES ('ST_MAN', 'Stock Manager', 5500, 8500);
INSERT INTO jobs VALUES ('ST_CLERK', 'Stock Clerk', 2008, 5000);
INSERT INTO jobs VALUES ('SH_CLERK', 'Shipping Clerk', 2500, 5500);
INSERT INTO jobs VALUES ('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO jobs VALUES ('MK_MAN', 'Marketing Manager', 9000, 15000);
INSERT INTO jobs VALUES ('MK_REP', 'Marketing Representative', 4000, 9000);
INSERT INTO jobs VALUES ('HR_REP', 'Human Resources Representative', 4000, 9000);
INSERT INTO jobs VALUES ('PR_REP', 'Public Relations Representative', 4500, 10500);

--Populating job_history table:
INSERT INTO job_history VALUES (102, '2001-01-13', '2006-07-24', 'IT_PROG', 60);
INSERT INTO job_history VALUES (101, '1997-09-21', '2001-10-27', 'AC_ACCOUNT', 110);
INSERT INTO job_history VALUES (101, '2001-10-28', '2005-03-15', 'AC_MGR', 110);
INSERT INTO job_history VALUES (201, '2004-02-17', '2007-12-19', 'MK_REP', 20);
INSERT INTO job_history VALUES (114, '2006-03-24', '2007-12-31', 'ST_CLERK', 50);
INSERT INTO job_history VALUES (122, '2007-01-01', '2007-12-31', 'ST_CLERK', 50);
INSERT INTO job_history VALUES (200, '1995-09-17', '2001-06-17', 'AD_ASST', 90);
INSERT INTO job_history VALUES (176, '2006-03-24', '2006-12-31', 'SA_REP', 80);
INSERT INTO job_history VALUES (176, '2007-01-01', '2007-12-31', 'SA_MAN', 80);
INSERT INTO job_history VALUES (200, '2002-07-01', '2006-12-31', 'AC_ACCOUNT', 90);

--Query a) Write a query in SQL to display job ID for those jobs that were done by two or more than 300 days:
SELECT job_id FROM job_history WHERE DATEDIFF(DAY, START_DATE, END_DATE) > 300 GROUP BY job_id HAVING COUNT(*) >= 2;

--Query b) Write a query in SQL to display the details of jobs in descending sequence on job title:
SELECT * FROM jobs ORDER BY JOB_TITLE DESC;

--Query c) Write a query in SQL to display job Title, the difference between minimum and maximum salaries for those jobs which max salary within the range 12000 to 18000:
SELECT JOB_TITLE, (MAX_SALARY - MIN_SALARY) 'Salary Difference' FROM jobs WHERE MAX_SALARY BETWEEN 12000 AND 18000;