Em Andamento

Oracle SQL Statements

Oracle Homework #2

1. Display the name and salary for all employees whose salary is not in the range of $1000 - $3000. Sort in descending order by salary. Label the columns “Employee” and “Salary”, and format the salary for dollar sign, comma, and no decimal places.

2. Display the last name and date of birth for all students born between May 1st and September 30th 1985 inclusive. Label the columns “Student” and “Date of Birth”, respectively. Order by the last name.

3. List the building code, room number and capacity for all locations that have capacity over 30 and are on the first floor. Order by capacity.

4. Display the name and job title of all managers who do not have a commission. Column headings “Name” and “Job Title”.

5. List the call id and course name for all courses that have C++ in the title. Label the columns “Call ID” and “Course Name”. Order by Call_id;

1. Display the name (concatenate the first name, middle initial, and last name), date of birth, and age for all students born in 1986. Label the columns with “Full Name”, “Date of Birth”, and “Age” respectively. Show the age with two decimal places. Order by date of birth, as shown below:

Full Name Date of Birth Age

-------------------- --------------- -----

Amanda J. Mobley 24-SEP-86 20.02

Ruben R. Sanchez 20-NOV-86 19.86

Michael S. Connoly 04-DEC-86 19.82

1. Type in the following into the workspace:

SET PAGESIZE 5

SELECT ROWNUM, s_ID

FROM enrollment;

Run and observe the results.

Now enter the following into the workspace:

SET PAGESIZE 20

SELECT ROWNUM, s_ID

FROM enrollment;

Again, run and observe the results.

From what you observe, explain what the “SET PAGESIZE” command does.

1. Update Daniel Black’s record so that his middle initial is S. Write a TOP-N query to select the 3 oldest students. Display the full name (as done for #6 above), the date of birth, and age. Use the same labels as before. The results of the query will be:

ROWNUM

Full Name

Date of Birth

Age

1

Daniel S. Black

10-OCT-82

23.98

2

Sarah M. Miller

14-JUL-85

21.22

3

Brian D. Umato

19-AUG-85

21.12

Oracle Homework #3

1. Display the employee number, name, salary and salary increase by 15% rounded off to a whole number. Label the column New Salary. Now add a column that will subtract the old salary from the new salary. Label the column Increase.

2. Display the faculty’s information as shown below:

Instructor Information

------------------------------------------------------

Instructor: J. Blanchard...........Phone: 715-555-9087

Instructor: P. Brown...............Phone: 715-555-6082

Instructor: K. Cox.................Phone: 715-555-1234

Instructor: L. Sheng...............Phone: 715-555-6409

Instructor: J. Williams............Phone: 715-555-5412

1. Display the employee’s name, hire date, and salary review date, which is the first Wednesday after six months of employment. Label the column REVIEW. Display only those employees who were hired before June 1, 1981. Put in order by hire date.

1. Write a query that produces the following for each employee who earns $3,000 or more:

<employee name> earns <salary> but wants <1.5 times salary>. Label the column Dream Salaries. Change the case of the name to capitalize the first initial in the name. For example:

Ford earns $3,000 monthly but wants $4,500.

Format your numbers for currency, zero decimal places. Order by name.

1. Display the name, hire date and day of the week on which the employee started for all employees whose manager is 7839. Label the columns as shown and order by employee name. Format the day hired as shown below:

Employee Day Hired

---------- -----------------------------

BLAKE Friday, May 1, 1981

CLARK Tuesday, June 9, 1981

JONES Thursday, April 2, 1981

1. Write a query to display ‘Small’, ‘Medium’, ‘Large’ for a classroom, based on the capacity of the room. If the capacity is >= 100, then it is ‘Large’, between 30 and 99 is ‘Medium’, and less than 30 is ‘Small’. Use the Location table and a Case statement to complete this query. Here are the results (note column headings):

Building

Room

Capacity

CR

101

Large

CR

202

Medium

CR

103

Medium

CR

105

Medium

BUS

105

Medium

BUS

404

Medium

BUS

421

Medium

BUS

211

Medium

BUS

424

Small

BUS

402

Small

BUS

433

Small

LIB

217

Small

LIB

222

Small

13 rows selected.

1. Write a query that displays the call id, course name, and level of the course, based on the course number as shown below:

If the number starts with a “1” -- Freshman

“2” -- Sophomore

“3” -- Junior

“4” -- Senior.

Anything else should display “Invalid Level”.

Label the grade column Level. Order by Level. Here is the result of the query:

CALL_ID COURSE_NAME LEVEL

---------- ------------------------- -------------

MIS 101 Intro. to Info. Systems Freshman

CS 155 Programming in C++ Freshman

MIS 301 Systems Analysis Junior

MIS 441 Database Management Senior

MIS 451 Web-Based Systems Senior

Habilidades: Processamento de dados

Ver mais: list building code room number locations capacity 103, count column oracle sql, order date oracle, count age dates oracle sql, oracle sql age, display names hire dates employees hired, oracle sql decimal places, oracle sql order numbers last, use location table case statement complete query, salary salary oracle sql, oracle sql format label name, write sql query label column employee title, write query display student result oracle, sql add column will subtract salary salary, subtract salary increase salary oracle sql, list building code room number location oracle, list full name born sql, display manager salary employee number oracle, label employee oracle, display name born sql, sql statement list date birth age, write hired date sql, oracle sql label column query, sql decimal oracle, oracle display employee hire

Acerca do Empregador:
( 2 comentários ) Leesville, United States

ID do Projeto: #180148

Premiar a:

expertMan

hi, forward me.

$30 USD em 0 dias
(3 Avaliações)
4.0