Write a sql query that shows the empno

Assignment Help Database Management System

Reference no: EM132111178

Sub-query with an inequality operator, and a weakly correlated sub-query.

1. a)

Write a SQL query that shows the empno, ename, job and total salary for all employees from the emp table, and uses a sub-query to show that earn more than the average salary. You will have to times sal by 52 weeks plus the commission *12 (commission is paid on a monthly basis) in the outer query and in the inner query (sub-query) to get the correct amounts, assuming that the value in sal represents the pay for one week.

Copy and paste your SQL code into this assignment.

Sub-query with an inequality operator, and a strongly correlated sub-query.

Write a SQL query that shows the empno, ename, job and total salary for all employees from the emp table and uses a sub-query to show employees that earn more than the average salary per job, because the first query compared everyone to the overall average, now we want to break it down by job.

Format the salary returned in the result set using the to_char() function to display the numbers in the currency format. You will have to account for a six-figure salary because they are all big earners.

Copy and paste your SQL code into this assignment.

2. Run the following SQL command in the SQL command window:

create table insurance as(select empno, decode(deptno,10,'Providien HS',20,'ORB Health')AS "PROVIDER", sysdate as "CREATEDATE" from emp where deptno IN (10,20));

Sub-query with the EXISTS operator.

3. Write a SQL query that will show the empno, ename, job, deptno, loc from the emp and dept tables. Use a sub-query to check for the existence of a record in the insurance table. You will have to correlate the empno from insurance with the empno from the emp table.

Copy and paste your SQL code into this assignment.

Sub-query with the IN operator.

4. Write a SQL query that will show the empno, ename, job, hiredate for all employees that have ‘Providien HS' as their health insurance provider. Use a sub-query with the IN operator to handle the order list of empno that is returned from the subquery.

Copy and paste your SQL code into this assignment.

Reference no: EM132111178

Questions Cloud

Which block will be the first block to be repeated : The pseudo-random stream of blocks generated by 64-bit OFB must eventually repeat (since at most 2^64 different blocks can be generated).

Provide a database schema in tabular format : Provide a database schema in tabular format and provide the necessary SQL statements to build it for three tuples and three attributes each.

Provide an overview of a defensive coding practice : Provide an overview of a Defensive Coding Practice and/or Control for choosing a language with security in mind.

Write the matlab code lines that add 2 to the value : Provide the matlab syntax that will ask a user at the command window to enter a name and assign the name to a variable called 'name'.

Write a sql query that shows the empno : Write a SQL query that shows the empno, ename, job and total salary for all employees from the emp table and uses a sub-query to show employees.

Create a sql query that lists the id and policy id : Create a SQL query that lists the ID, Policy ID, county, construction and line for residential properties between Clearwater in the south.

Write a sql query that will give the employee number : Write a SQL query that will give the employee number, employee name and hired date from the EMP table.

Create a sql query that will use the count function : Create a SQL query that will use the count function to return the number of employees in each department in the emp table.

A program to simulate signals of a pedestrian traffic light : Specifically, the car lantern will light up for 10 seconds, then turn off and turn on the pedestrian traffic light for 7 seconds.

User Account

All Pages