Inline Tables And CASE Command

Running head: INLINE TABLES AND CASE COMMAND 1

Inline tables and CASE Command

Name

Institution

INLINE TABLES AND CASE COMMAND 2

Question 1

Using CASE expression to return project names for the corresponding project id’s that follow

(1 –Org Chart, 2 –ERP Implementation, 3 –Change Control, 4 –Payroll Application Rollout, 5 –

Server Refresh),

also return the original project id and project name columns.

SELECT project_id,project_name

(

CASE project_id

WHEN 1 THEN 'Org Chart'

WHEN 2 THEN 'ERP Implementation'

WHEN 3 THEN 'Change Control'

WHEN 4 THEN 'Payroll Application Rollou'

WHEN 5 THEN 'Server Refresh'

ELSE 'Undefined' END

) project_names

FROM PROJECT

INLINE TABLES AND CASE COMMAND 3

Question 2

Use a search CASE expression to return project names from the project table that correspond

with the project id for project id’s 1 through 5. This may require a separate query to

determine the project names and project id’s that corresponded with them before writing the

CASE expression.

SELECT (CASE WHEN p.project_id in (select project_id from project)

THEN p.project_name

ELSE 'ghc' END) "Project Name"

FROM project p;

INLINE TABLES AND CASE COMMAND 4

Question 3

Use a CASE expression combined with an operator to test for the gender of the employees.

If it is a female employee return femminile, otherwise return maschio.

SELECT (

CASE WHEN e.gender = 'female' THEN 'femminile'

ELSE 'maschio' END

) "Gender"

FROM employee e;

INLINE TABLES AND CASE COMMAND 5

Question 4

Create an inline view (derived table) that retrieves the branch name where the branch id is at

least 2 but no more than 7.

SELECT branch_name

FROM (SELECT * FROM branch where branch_id between 2 and 7) b

INLINE TABLES AND CASE COMMAND 6

Question 5

Retrieve the division id and division name from the division table and use an inline

view (derived table) to evaluate the division table and return a count of each division

for the branch in the branch table.

SELECT d.division_id, d.division_name, d2.No

FROM

(SELECT d2. division_id, count(division_id) No FROM branch d2 GROUP BY d2. division_id)

d2, division d

WHERE d.division_id = d2.division_id

INLINE TABLES AND CASE COMMAND 7

Question 6

Use an Inline View (common table expression) to Return the employee first name, last name,

blog url and hitcount from the blog table if the employee has created a blog or if the blog id is at

least 3

with cte as (

SELECT e.first_name, e.last_name, b.blog_url , b.hit_count

FROM employee e inner join blog b on (initcap(SUBSTR(blog_url, 34))=

initcap(e.last_name))

WHERE b.blog_id>=3

)

select c.* from cte c;

INLINE TABLES AND CASE COMMAND 8

References

Harper, S. (2009). Oracle SQL Developer 2.1. Birmingham, U.K.: Packt Pub.

Morton, K. (2010). Pro Oracle SQL. [Berkeley, Calif.]: Apress.

Place new order. It's free, fast and safe

Paper type

Deadline

Page count

550 words

Total price: $ 15.00

Our customers say

Jeff Curtis

USA, Student

"I'm fully satisfied with the essay I've just received. When I read it, I felt like it was exactly what I wanted to say, but couldn’t find the necessary words. Thank you!"

Ian McGregor

UK, Student

"I don’t know what I would do without your assistance! With your help, I met my deadline just in time and the work was very professional. I will be back in several days with another assignment!"

Shannon Williams

Canada, Student

"It was the perfect experience! I enjoyed working with my writer, he delivered my work on time and followed all the guidelines about the referencing and contents."