Question: This problem set will use the emp and dept tables from the ApEx default schema. 1. Create a SELECT statement that will use the CASE

This problem set will use the emp and dept tables from the ApEx default schema.

1. Create a SELECT statement that will use the CASE switch. Give the empno, ename , job along with the location information from the dept table. For instance if deptno is 10 then use the CASE statement to display the string Accounting. Do the same for the three other locations in the dept table. Use dept as the column alias so you dont get:

CASEDEPTNOWHEN10THEN'ACCOUNTING'WHEN20THEN'RESEARCH'WHEN30THEN'SALES'WHEN40THEN'OPERATIONS'END as a column header.

2. Modify the previous query so we get the month and the year someone was hired. Dont show the day that they were hired. Use the to_char() function along with a format mask to return the month and year that they were hired.

3. Modify the SQL query to show the number of years someone has worked for this company by subtracting hiredate from sysdate. Because hiredate is of the date data type you will have to use single row functions to extract the year value.

P.S. The tables are already created.

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Problem Set 15 In this assignment you will be creating views. Views are a way to display information from various tables or views in a way that is easily comprehended by end-users. 1. Log on to your...

In this assignment you will be creating views. Views are a way to display information from various tables or views in a way that is easily comprehended by end-users. 1. Log on to your Oracle ApEx...

VIEWS In this assignment you will be creating views. Views are a way to display information from various tables or views in a way that is easily comprehended by end-users. 1. Log on to your Oracle...

1. Create a SQL query in the SQL command window showing the empno, ename, job, sal, and hiredate from emp. Use the TO_CHAR function on the salary and column aliases where appropriate. Copy and paste...

1. Create a SELECT statement that will use the CASE switch. Give the empno, ename , job along with the location information from the dept table. For instance if deptno is 10 then use the CASE...

Database Administration CHAPTER OBJECTIVES I Understand the need for and importance of database I ttno'illI the meaning of ACID transaction administralin - Learn the tour 1992 ANSI standard isolation...

SQL Assignment. TIA SQL Table How to code subqueries Exercises 1. Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in...

Write an equijoin query and inequality join by using the WHERE clause. Learn the basic JOIN operation rules. Write complex JOIN queries with more than two tables, and more than two columns. Write...

haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 2 CONFIRMING PAGES P L U G - I N T6 Basic Skills and Tools Using Access LEARNING OUTCOMES 1. Describe the primary functions using Microsoft Access. 2....

PLEASE READ CAREFULLY THE CASE STUDY PROVIDED AND FEEL FREE TO ADD HERE YOUR COMMENTS FOR EXAMPLE LIKES DISLIKES WORDS OR PHRASES YOU DO NOT UNDERSTAND ANY COMMENTS THAT WILL IMPROVE THE DIALOGUE...

1. A consumer group is testing camp stoves. To test the heating capacity of a stove, they measure the time required to bring 2 liters of water from 30 degrees to boiling. Two competing models are...

What is the major concern when considering the joining of fiber-reinforced composites?

1. Suppose a company had an initial investment of $40,000. The cash flow for the next five years are $13,000, $16,000, $14,000, $14,000, and $16,000, respectively. The interest rate is 6%. answers...

Seved Help 14 Wisconsin Snowmobile Corp. is considering a switch to level production Cost efficiencies would occur under level production, and aftertax costs would decline by $31,500, but inventory...

Assume that the banking system has total reserves of $100 billion. Assume also that required reserves are 10 percent of checking deposits and that banks hold no excess reserves and households hold no...

As shown in Figure 3, the overall labor-force participation rate of men declined between 1970 and 2000. At the same time, the labor-force participation rate of women increased sharply. This overall...

The Bureau of Labor Statistics announced that in February 2008, of all adult Americans, 145,993,000 were employed, 7,381,000 were unemployed, and 79,436,000 were not in the labor force. Use this...