Job Ads Exercise using MySQL

1) Write a query to display job_id, job_title, salary_estimate_min, salary_estimate_max where salary_estimate_max is less than 105,000

SELECT job_id, job_title, salary_estimate_min, salary_estimate_max

FROM jobs

WHERE REPLACE(salary_estimate_max,'K','') < 105;

2) Write a query to display the company_name, company_rank, company_size_min, and company_size_max for companies with more than 60 employees and less than 120 employees

SELECT DISTINCT company_name, company_rank, company_size_min, company_size_max

FROM jobs

WHERE REPLACE(company_size_min, ' Employees', '') > 60 AND REPLACE(company_size_max, ' Employees', '') < 120;

3) Write a query to display the job_id, job_title in uppercase, company_name in lowercase

SELECT job_id, UPPER(job_title) AS 'upper_jobtitle', LOWER(company_name) AS 'lower_companyname'

FROM jobs;

4) Write a query to display the job_id, company_name, headquarters_of_company, first letter of company_name, first letter of headquarters_of_company

SELECT job_id, company_name, headquarters_of_company,

   SUBSTRING(company_name,1,1)             AS 'company_name_first_letter', 

   SUBSTRING(headquarters_of_company, 1,1) AS 'state_of_company_first_letter'  

FROM jobs;

5) Write a query to display the job_id, company_name, headquarters_of_company, company_code (a new column containing a concatenation of the first letter of company_name and the first letter of headquarters_of_company)

SELECT job_id, company_name, headquarters_of_company,

   CONCAT(SUBSTRING(company_name,1,1), SUBSTRING(headquarters_of_company, 1,1)) AS 'company_code'   

FROM jobs;

6) Repeat query no.5 but this time display the company_code in lowercase

SELECT job_id, company_name, headquarters_of_company,

   LOWER(CONCAT(SUBSTRING(company_name,1,1), SUBSTRING(headquarters_of_company, 1,1))) AS 'company_code'

FROM jobs;

7) Write a query to display the job titles with length greater than 29

SELECT DISTINCT job_title

FROM jobs

WHERE LENGTH(job_title) > 29;

8) Write a query to display the company_name, company_rank, company_market_value, company_market_value rounded using ROUND function with precision of 2 digits, company_market_value rounded down using FLOOR, company_market_value rounded up using CEIL

SELECT company_name, company_rank, company_market_value,

   ROUND(company_market_value, 2) AS 'round_mv',

   FLOOR(company_market_value) AS 'floor_mv',

   CEIL(company_market_value) AS 'ceil_mv'

FROM jobs;

9) Write a query to display the job_id, job_title, published_date, and removed_date for all jobs that were published on 2016

SELECT job_id, job_title, published_date, removed_date

FROM jobs

WHERE YEAR(published_date) = 2016;

10) Which job adverts were posted during January 2017?

SELECT job_id, job_title, published_date, removed_date

FROM jobs

WHERE YEAR(published_date) = 2017 AND MONTH(published_date) = 1;

11) Which job adverts were removed after a single day?

SELECT job_id, job_title, published_date, removed_date

FROM jobs

WHERE DATEDIFF(removed_date, published_date) = 1;

12) Which job adverts were posted on the same day and month as the current date? For example, if today is February 11th 2021, which jobs were published on February 11th (regardless the year)?

SELECT job_id, job_title, published_date, removed_date

FROM jobs

WHERE DAY(published_date) = DAY(CURDATE())

AND MONTH(published_date) = MONTH(CURDATE())；

13）In a few job adverts, the value of published_date is greater than the removed_date, those rows represent invalid data. Find those rows.

SELECT job_id, job_title, published_date, removed_date

FROM jobs

WHERE published_date > removed_date;

14) List the job adverts where at least one of the following conditions is met: (1) the row does not contain a value in removed_date, (2) the row does not contain a value in company_name or (3) |the row does not contain a value in headquarters_of_company

SELECT job_id, job_title, removed_date, company_name, headquarters_of_company

FROM jobs

WHERE removed_date IS NULL OR

  company_name IS NULL OR 

  headquarters_of_company IS NULL;

15) Use the previous queries and instead of the NULL values, display: (1) the current date instead of NULL values in removed_date, (2) the company_state instead of NULL values in headquarters_of_company, (3) 'Not Available' instead of NULL values in company_name

SELECT job_id, job_title,

   IFNULL(removed_date, CURDATE()) AS 'removed_date',

   IFNULL(headquarters_of_company, state_of_company) AS 'headquarters_of_company',

   IFNULL(company_name, 'Not Available') AS 'company_name'

FROM jobs

WHERE removed_date IS NULL OR

  company_name IS NULL OR 

  headquarters_of_company IS NULL;

16) Write a query to display the company_name, company_market_value, and a new column named as 'company_market_value_rank' based on the following logic:

a. For companies with market_value in the range of 0-300 provide the rank : 'low range'

b. For companies with market_value in the range of 301-600 provide the rank : 'mid range'

c. For companies with market_value in the range of 601-900 provide the rank : 'high range'

d. For any other range provide the rank : 'other range'

SELECT DISTINCT company_name, company_market_value,

    CASE WHEN company_market_value BETWEEN 0 AND 300 THEN   'low range'

         WHEN company_market_value BETWEEN 301 AND 600 THEN 'mid range'

         WHEN company_market_value BETWEEN 601 AND 900 THEN 'high range'

         ELSE 'other range'

    END AS 'company_market_value_rank'

FROM jobs;

17) Write a query to display the job_title, company_name, company_size_min, company_size_max, and a new column 'company_size' based on the following logic:

a. For companies with up to 60 employees, provide the value: ‘Small Company’

b. For companies with up to 120 employees, provide the value: ‘Medium Company’

c. For companies with up to 180 employees, provide the value: ‘ Large Company’

d. For any other range 'Unknown'

SELECT job_title, company_name, company_size_min, company_size_max,

   CASE WHEN REPLACE(company_size_max, ' Employees', '') <= 60 THEN 'Small Company'

        WHEN REPLACE(company_size_max, ' Employees', '') <=  120 THEN 'Medium Company'

        WHEN REPLACE(company_size_max, ' Employees', '') <=  180 THEN 'Large Company'

        ELSE 'Unknown'

    END AS 'company_size'

FROM jobs;