chapter 1

• Your first task is to determine the structure of the DEPARTMENTS table and its contentS
  

DESC DEPARTMENTS

SELECT * FROM DEPARTMENTS

1. You need to determine the structure of the EMPLOYEES table
   

DESC EMPLOYEES

1. The HR department wants a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias STARTDATE for the HIRE_DATE column.
   

​SELECT EMPLOYEE_ID , LAST_NAME , JOB_ID , HIRE_DATE "START DATE" FROM EMPLOYEES

1. The HR department needs a query to display all unique job codes from the EMPLOYEES table.
   
   SELECT DISTINCT JOB_ID FROM EMPLOYEES
   
   
   1. 
      The HR department wants more descriptive column headings for its report on employees. Copy the statement from lab_01_07.sql to the iSQL*Plus text box. Name the column headings Emp #, Employee, Job, and Hire Date, respectively. Then
      run your query again.
      
      

SELECT EMPLOYEE_ID "EMP #" ,LAST_NAME EMPLOYEE , JOB_ID JOB , HIRE_DATE "Hire Date " from employees

1. 
   The HR department has requested a report of all employees and their job IDs. Display the last name concatenated with the job ID (separated by a comma and space) and name the column Employee and Title
   
   

•select last_name || ', '|| job_id from employees

1. 
   
   To familiarize yourself with the data in the EMPLOYEES table, create a query to display all the data from that table. Separate each column output by a comma. Name the column title THE_OUTPUT.
   

   select employee_id ||',' || last_name ||',' || job_id ||','|| salary from employees

   
   
   

cheaper 2

1. Due to budget issues, the HR department needs a report that displays the last name and salary of employees who earn more than $12,000. Place your SQL statement in a text file named lab_02_01.sql. Run your query.

select last_name , salary from employees where salary > 12000

Create a report that displays the last name and department number for employee number 176

select last_name , department_id from employees where employee_id = 176

3. The HR departments needs to find high-salary and low-salary employees. Modify lab_02_01.sql to display the last name and salary for any employee

whose salary is not in the range of $5,000 to $12,000. Place your SQL statement in a text file named lab_02_03.sql.

select last_name , salary from employees where salary not between 5000 and 12000

Create a report to display the last name, job ID, and start date for the employees with the last names of Matos and Taylor. Order the query in ascending order by start date.

('select last_name , job_id , hire_date from employees where last_name in ('Matos','Taylor

order by hire_date

Display the last name and department number of all employees in departments 20 and 50 in ascending alphabetical order by name.

select last_name , department_id from employees where department_id in (20,50) order by last_name

Modify

lab_02_03.sql

to display the last name and salary of employees who earn between $5,000 and $12,000 and are in department 20 or 50. Label the columns

Employee

and

Monthly Salary

, respectively. Resave

lab_02_03.sql

as

lab_02_06.sql

. Run the statement

in

lab_02_06.sql

(select last_name , salary from employees where salary between 5000 and 12000 and department_id in (20,50

The HR department needs a report that displays the last name and hire date for all employees who were hired in 1994.


SELECT last_name, hire_date FROM employees WHERE hire_date LIKE '%94';
or select last_name , hire_date from employees
where to_char(hire_date ,'yyyy' )=1994;


Create a report to display the last name and job title of all employees who do not have a manager.

select last_name , job_id from employees
where manager_id is null

Create a report to display the last name, salary, and commission of all employees who earn commissions. Sort data in descending order of salary and commissions.

select last_name , salary , commission_pct from employees

where commission_pct is not null

order by salary , commission_pct

نكمل بكرة بقى سلام

تم تعديل 30 يناير 201313 سنة بواسطة helal_10g

10. Members of the HR department want to have more flexibility with the queries that you are writing. They would like a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt. (You can use the query that you created in practice exercise 1 and modify it.) Save this query to a file named lab_02_10.sql. If you enter 12000 when prompted, the report displays the following results:

select last_name , salary from employees where salary > &sal;

The HR department wants to run reports based on a manager. Create a query that prompts the user for a manager ID and generates the employee ID, last name, salary, and department for that manager’s employees. The HR department wants the ability to sort the report on a selected column. You can

test the data with the following values:

select employee_id , last_name , salary , departmnet_id from employees where manager_id= &m_id
order by &col_nmae ;

12. Display all employee last names in which the third letter of the name is a

'%select last_name from employees where last_name like '%a%' and last_name like '%e

(select last_name, job_id , salary from employees where job_id in ('SA_REP','ST_CLERK') AND SALARY NOT IN (2500 , 3500 ,7000

15. Modify lab_02_06.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. Resave lab_02_06.sql as lab_02_15.sql. Rerun the statement in lab_02_15.sql

SELECT last_name , salary, commission_pct

FROM employees WHERE commission_pct = .20;

'%select last_name from employees where last_name like '__a

13. Display the last name of all employees who have both an a and an e in their last name

chapter 3

1. Write a query to display the current date. Label the column Date

select sysdate "date" from dual

. The HR department needs a report to display the employee number, last name, salary, and salary increased by 15.5% (expressed as a whole number) for each employee. Label the column New Salary. Place your SQL statement in a text file named lab_03_02.sql.

select employee_id , last_name , salary ,round (salary * 1.155 ,0)"new salary " from employees

c

4. Modify your query lab_03_02.sql to add a column that subtracts the old salary fromthe new salary. Label the column Increase. Save the contents of the file as lab_03_04.sql. Run the revised query.

select employee_id , last_name , salary ,round (salary * 1.155 ,0)"new salary " , round (salary * 1.155 ,0)- salary "Increase" from employees

Write a query that displays the last name (with the first letter uppercase and all other letters lowercase) and the length of the last name for all employees whose name starts with the letters J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.

select initcap( last_name) name ,length(last_name ) length

'%from employees where last_name like 'J%' OR last_name like 'A%' OR LAST_NAME like 'M

order by last_name

6. The HR department wants to find the length of employment for each employee. For each employee, display the last name and calculate the number of months between today and the date on which the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.

select last_name ,round( months_between(sysdate,hire_date ),0) months_worked from employees order by 2

7. Create a report that produces the following for each employee:

<employee last name> earns <salary> monthly but wants <3 times salary>. Label the column Dream Salaries.

"select last_name || ' earns' || salary ||' monthly but wants '|| salary *3 "Dream Salaries

from employees

8. Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with the $ symbol. Label the column SALARY.

select last_name , lpad(salary , 15,'$') salary

from employees

تم تعديل 31 يناير 201313 سنة بواسطة helal_10g

9. Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”

select last_name , hire_Date ,to_char(next_day( add_months(hire_date,6),'monday'),'day , "the " ddspth "of" month yyyy')REVIEW
from employees

SELECT last_name, hire_date,
   TO_CHAR(hire_date, 'DAY') DAY
FROM	 employees
ORDER BY TO_CHAR(hire_date - 1, 'd');

11. Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, show “No Commission.” Label the column COMM.

SELECT LAST_NAME ,NVL( TO_CHAR(COMMISSION_PCT),'NO COMMISSION') COMM
FROM EMPLOYEES

13. Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, using the following data:
Job Grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
None of the above 0

SELECT JOB_ID , DECODE(JOB_ID,'AD_PRES' , 'A' , 'ST_MAN', 'B' , 'IT_PROG','C','SA_REP' , 'E' ,0)GRADE
FROM EMPLOYEES

على فكرة لو عايز تعدى من أى إمتحان فى أى شركة إهتم بالموضوع ده جدا

4 chapter

Find the highest, lowest, sum, and average salary of all employees. Label the columns
Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Place your SQL statement in a text file named lab_04_04.sql.

select max(salary) "Maximum" , min(salary)"Minimum"  , sum(salary)""Sum" , round(avg(salary),0)  "Average
from employees

5. Modify the query in lab_04_04.sql to display the minimum, maximum, sum, and average salary for each job type. Resave lab_04_04.sql as lab_04_05.sql. Run the statement in lab_04_05.sql.

select job_id , max(salary) "Maximum" , min(salary)"Minimum"  , sum(salary)"Sum" , round(avg(salary),0)  "Average"
from employees
group by job_id

Write a query to display the number of people with the same job

select job_id , count(*) from employees group by job_id

تم تعديل 31 يناير 201313 سنة بواسطة helal_10g

7. Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers.
"SELECT COUNT(DISTINCT manager_id) "Number of Managers
FROM employees;

8. Find the difference between the highest and lowest salaries. Label the column DIFFERENCE.

SELECT max(Salary) - min(salary) DIFFERENCE
FROM   employees;

9. Create a report to display the manager number and the salary of the lowest-paid employee for that manager. Exclude anyone whose manager is not known. Exclude

any groups where the minimum salary is $6,000 or less. Sort the output in descending order of salary.

(select manager_id , min(salary
from employees
where manager_id is not null
group by manager_id
order by 2 desc

عدد المشاهدات للموضوع 76 و احد ولا يوجد تفاعل مع الموضوع لو الموضوع غير مهم بالنسبة للناس نركز فى حاجة ثانية

ده سؤال كان فى إنترفيوا : عايز أكبر sum للمرتبات على جميع الاقسام

select max(sum(salary)) from employees group by department_id

chapter 5

1.Write a query for the HR department to produce the addresses of all the departments. Use the LOCATIONS and COUNTRIES tables. Show the location ID, street
address, city, state or province, and country in the output. Use a NATURAL JOIN to produce the results.
2.

select LOCATION_ID ,STREET_ADDRESS , CITY ,
STATE_PROVINCE  , COUNTRY_NAME
from locations natural join COUNTRIES

1.The HR department needs a report of all employees. Write a query to display the last name, department number, and department name for all employees.

select last_name, department_id , department_name
from employees  join departments
(using(department_id)

orselect e.last_name, d.department_id , d.department_name
 from employees e  , departments d
where e.department_id = d.department_id

3. The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department name for all employees who work in Toronto.

select last_name , job_id , department_id , department_name
from employees join departments
using (department_id)
join locations
using(location_id)
where city = 'Toronto';

4. Create a report to display employees’ last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file named lab_05_04.sql

select e.last_name employee , e.employee_id  emp# , m.last_name  manager , m.employee_id "Mgr#"
from employees e join employees m
(on ( e.manager_id = m.employee_id

5. Modify lab_05_04.sql to display all employees including King, who has no manager. Order the results by the employee number. Place your SQL statement in a text file named lab_05_05.sql. Run the query in lab_05_05.sql.

select e.last_name employee , e.employee_id  emp# , m.last_name  manager , m.employee_id "Mgr#"
from employees e left outer  join employees m
(on ( e.manager_id = m.employee_id