1. Navigate to the SQL command window and create a SQL query that will give the
ID: 3750863 • Letter: 1
Question
1. Navigate to the SQL command window and create a SQL query that will give the product ID, product description and product list price from the PRODUCT_INFORMATION table. Use the TO_CHAR function with the dollar/currency format to display the list price in American dollars and cents.
In your submission include the SQL code and a screen shot of the result set.
2. Modify the previous SQL query so that it displays the date three months from now. To make this query as flexible as possible use SYSDATE with the LAST_DAY() function to get the last day of this month (’30-SEP-2018’ in this case), along with the ADD_MONTHS() function to display the date three months from now. The purpose of this SELECT statement is to show how long the given price of the product will be in effect, so add a column alias to the date column saying “Price good until.”
In your submission include the SQL code and a screen shot of the result set.
3. Write a SQL query that will give the employee number, employee name and hired date from the EMP table. Use the TO_CHAR function along with the format mask ‘DDMMYYY’ to display the hired date.
In your submission include the SQL code and a screen shot of the result set.
4. Write a query that will select the ORDER_ID, CUSTOMER_ID and ORDER_TIMESTAMP columns from the ORDERS table. Use the to_char() function to display the date data from the ORDER_TIMESTAMP column in a format that has the day as two numbers, the month as the first three characters of the name of the month, the full year as numbers, the hour and the minutes.
In your submission include the SQL code and a screen shot of the result set.
Explanation / Answer
1.
SELECT productID, productDescription, to_char(productListPrice,"$999999.99") from PRODUCT_INFORMATION;
to char can display upto 7 digits and 2 decimals. If you want to reduce or increase, change the number of 9's accordingly.
2.
SELECT productID, productDescription, to_char(productListPrice,"$999999.99"),
from PRODUCT_INFORMATION;
sysdate + 3 will give a month. Get last day of that month. Display it under price good until.
3)
SELECT employee_number, employee_name, to_char(hired_Date,'DDMMYYY') from EMP;
4)
SELECT ORDER_ID, CUSTOMER_ID, to_char(ORDER_TIMESTAMP, 'DD-MON-YYYY:HH-MM') from ORDERS;
In case of any doubts, please comment.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.