Description
Objective: Create SELECT queries using basic data types and associated functions
Problem 1:
Create a query displaying the employee_id, the last name based on table employees. Create a calculated column with an
alias of Weekly Salary (case and space required). Assume that the salary column contains monthly salary and that a year
has 52 weeks. Round the weekly salary result to 2 decimals. Display only those records where the last_name column is 6
characters long. Sort it by the Weekly Salary and the last_name.
Problem 2:
Show the maximum min_salary value, the minimum max_salary, and the difference between the minimum of
max_salary and maximum of min_salary aliased as Diff based the table jobs;
Problem 3:
Display the last name and the commission percentage for employees working in department 20 or 80. Order by last
name. Add the following two columns:
➢ Replace null commission values with the string ‘No Commission’ aliased Commission.
➢ Replace non-null commission_pct values with the string ‘Commission’ and null values with the string ‘No
Commission’ aliased No Commission.
Problem 4:
Display the last name and the months between today and the hire date of employees hired after 1/1/2000.
Truncate the months between result to a whole number. Use Months since 2000 as an alias. Order it by Months since
2000 in descending order.
Problem 5:
Concatenate the last name, comma and blank space, and first name together for employees’ last name starting with a B.
Right pad this expression with periods (.) s to fill a 50-character length.
Problem 6:
Part 1: Display the department_id and department_name from table departments only for those records where the
department_name column contains at least one blank space sorted by department_name.
Part 2: Add the following derived column to the query in part 1:
– Display the partial department_name following the blank space and alias it with partial.
– Make sure to remove any leading or trailing blank spaces.
Part 3: You notice that there is one department_name with 3 words (= two blank spaces).
– Modify the WHERE clause so that only department_names having two blank spaces are retrieved.
– Modify the derived column partial to display only the middle word. Alias this column with middle.
– Make sure to remove any leading or trailing blank spaces.
