Description

This week’s lab continues using the SELECT command and learning the interfaces for both SQL Developer and introduces the use of single-line functions.
Submission
Your submission will be a single text-based SQL file with appropriate header and commenting. Please ensure your file runs when the entire file is executed in SQL Developer. Create a new Worksheet in SQL Developer. Save the file as L01_ID#_LASTNAME.sql
Your submission needs to be commented and include the question, the solutions.
Do not comment the solutions (SQL Statements).
Example Submission
— ***********************
— Name: Your Name
— ID: #########
— ***********************

— Q1 SOLUTION —
SELECT * FROM TABLE;

— Q2 Solution – SELECT * FROM TABLE;

Style Guide
Your SQL should be written using the standard coding style:
• all keywords are to be upper case,
• all user-defined names are to be lower case, (example: table and field names)
• there should be a carriage return before each major part of the SQL statements (i.e. before SELECT, FROM, WHERE and ORDER BY)
See the following sample:

SELECT columns
FROM tables
WHERE conditions
ORDER BY column1, column2;
To save time, you can write all SQL statement in your SQL developer. To make sure that your SQL statements style follows the standard SQL style guide, copy and paste your SQL statement onto the following website and click on “FORMAT SQL” or “FORMAT SQL IN NEW WINDOW”.
https://www.freeformatter.com/sql-formatter.html#ad-output
You can also upload your SQL file. See the setting in the following image. Have SQL keywords (SELECT, INSERT, UPDATE, etc.) uppercase and user defined objects and identifiers (tables, columns, etc.) lowercase.

Tasks
the result will depend on the day when you RUN/EXECUTE this query. Label the column “Tomorrow”.

See the following example:
select * from employees where employee_id = 107;
You can also have the following code:
define emp_id number = 107; select * from employees where employee_id = &emp_id;
After you use the variable, you can undefined the variable:
undefine emp_id;
Define a variable of type datetime:
define tomorrow = sysdate + 1;

— Q2: For each product in category 2, 3, and 5, show product ID, product name, list price, and the new list price increased by 2%. Display a new list price as a whole number.
In your result, add a calculated column to show the difference of old and new list prices.
Sort the result according to category ID first and then based on product ID.
You output has to match the following result. This result is partially displayed as it has 158 rows.
See the result for the first 10 rows.

— Q3: For employees whose manager ID is 2, write a query that displays the employee’s Full Name and Job Title in the following format:
Summer, Payne is Public Accountant.
Sort the result based on employee ID.

• Label the column Years worked.
• Order your results by the number of years employed. Round the number of years employed up to the closest whole number.
The output result includes 89 rows. See the partial result (The first 10 rows).

• Label the column REVIEW DAY.
• Format the dates to appear in the format like:
You can use ddspth to have the above format for the day.
The Query returns 107 rows. See the first 10 rows of the output result for comparision.

— Q6: For all warehouses, display warehouse id, warehouse name, city, and state. For warehouses with the null value for the state column, display “unknown”. Sort the result based on the warehouse ID.