Starting from:
$25

$19

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.


More products