1. Show the structure of the DEPARTMENTS table

desc hr.departments

1. Select all data from the table.

select *
from hr.departments

Show the structure of the EMPLOYEES table.

1. desc hr.employees Create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Save your SQL statement named:

select EMPLOYEE_ID, LAST_NAME, JOB_ID, HIRE_DATE from hr.EMPLOYEES order by EMPLOYEE_ID;

1. Copy the statement from lab1_nr_indeksu.sql into the Edit window. Name the column headings Emp #, Employee, Job, and Hire Date, respectively. Run your query again and then delete the query from “Saved SQL”

select EMPLOYEE_ID as Emp#, LAST_NAME, JOB_ID as JOB, HIRE_DATE as "Hire Date" from hr.EMPLOYEES order by EMPLOYEE_ID;

1. Show last name and salary for each employee concatenated with a string

“: 1 Month salary =” to give the returned rows more meaning.

select LAST_NAME || ': 1 Month salary=' || SALARY as "Month salary" from hr.EMPLOYEES ;

1. Create a query to display the employee last name and department number for employee number 176.

select LAST_NAME , DEPARTMENT_ID from hr.EMPLOYEES where EMPLOYEE_ID = 176 ;

1. Display the employee last name, job ID, and start date of employees hired between February 20 1998, and May 1, 1998. Order the query in ascending order by start date.

select LAST_NAME ,JOB_ID from hr.EMPLOYEES where HIRE_DATE BETWEEN '1998/02/20' and '1998/05/01' order by HIRE_DATE;

1. list 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.

select LAST_NAME as Employee , SALARY as "Monthly Salary" from hr.EMPLOYEES where SALARY BETWEEN '5000' and '12000' AND DEPARTMENT_ID IN (20,50) ;

1. select LAST_NAME as Employee , SALARY as "Monthly Salary" from hr.EMPLOYEES where SALARY BETWEEN '5000' and '12000' AND DEPARTMENT_ID IN (20,50) ;

select LAST_NAME , JOB_ID from hr.EMPLOYEES where MANAGER_ID IS NULL ;

1. Display the last names of all employees where the third letter of the name is an a.

select LAST_NAME from hr.EMPLOYEES where INSTR(LAST_NAME,'a')=3;

1. Display the last name, job, and salary for all employees whose job is sales representative or stock clerk and whose salary is not equal to $2,500, $3,500, or $7,000.

select LAST_NAME , JOB_ID , SALARY from hr.EMPLOYEES where JOB_ID='SH_CLERK' or JOB_ID='SA_REP' AND SALARY <>'2500' AND SALARY <>'3500' AND SALARY <>'7000' ;

1. For each employee, display the employee ID number, last_name, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary.

select EMPLOYEE_ID , LAST_NAME, SALARY , SALARY*1.15 as "New Salary" from hr.EMPLOYEES;

1. Write a query that displays the employee’s last names with the first letter capitalized and all other letters lowercase and the length of the names, for all employees whose name starts with J, A, or M.

2. Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.

3. Write a query to display the last name, department number, and department name for all employees.

SELECT last_name, departments.DEPARTMENT_ID, departments.department_name
FROM hr.employees, hr.departments
WHERE departments.DEPARTMENT_ID=employees.DEPARTMENT_ID ;

1. Create a unique listing of all jobs that are in department 80. Include the location of department 80 in the output.

SELECT employees.job_id , departments.location_id
FROM hr.employees, hr.departments
WHERE departments.department_id = 80 AND employees.department_id = departments.department_id;

1. Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission.

SELECT employees.last_name, departments.department_name, locations.location_id, locations.city
FROM hr.employees, hr.departments, hr.locations
WHERE employees.department_id = departments.department_id AND departments.location_id = locations.location_id AND employees.commission_pct IS NOT NULL;

1. Display the employee last name and department name for all employees who have an a (lowercase) in their last names.

SELECT employees.last_name, departments.department_name
FROM hr.employees, hr.departments
WHERE last_name LIKE '%a%';

1. Write a query to display the last name, job, department number, and department name for all employees who work in Toronto. Use JOIN .. ON.

SELECT employees.last_name, employees.job_id, employees.department_id, departments.department_name

FROM hr.employees JOIN hr.departments ON (employees.department_id = departments.department_id)

JOIN hr.locations ON (departments.location_id = locations.location_id)
WHERE locations.city = 'Toronto';

1. Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. Use JOIN … ON.

SELECT employees.department_id AS DEPARTMENT, coulleauge.last_name AS EMPLOYEE, employees.last_name AS COLLEAGUE

FROM hr.employees JOIN hr.employees coulleauge ON (employees.department_id =

coulleauge.department_id)

WHERE employees.employee_id != coulleauge.employee_id;

1. Create a query to display the name and hire date of any employee hired after employee Davies.

Join a table to itself (self join). You can not use WHERE HIRE_DATE>’ 97/01/29’.

SELECT E.last_name, E.hire_date
FROM hr.employees E, hr.employees emp_davies
WHERE E.hire_date > emp_davies.hire_date AND emp_davies.last_name = 'Davies';

1. Display the names and hire dates for all employees who were hired before their managers, along with their manager’s names and hire dates. Label the columns Employee, Emp Hired, Manager, and Mgr Hired, respectively. Join a table to itself (self join).

SELECT E.last_name AS Employee, E.hire_date AS "Emp Hired", M.last_name AS

Manager, M.hire_date AS "Mgr Hired"

FROM hr.employees E, hr.employees M

WHERE E.hire_date < M.hire_date AND E.manager_id = M.employee_id;

1. Group functions work across many rows to produce one result per group. True/False

True

1. Group functions include nulls in calculations. True/False

False

1. The WHERE clause restricts rows prior to inclusion in a group calculation. True/False

True

1. Display 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.

select Max (salary) "Maximum", Min (salary) "Minimum", round(Avg (salary)) "Średnia", Sum (salary) "Suma" from employees ;

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

select job_id as "nazwa stanowiska", count(employee_id) as "liczba pracownikow" from employees group by job_id ;

1. Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.

select Max (salary)- Min (salary)as "Differnece" from employees ;

1. 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 less than $6,000. Sort the output in descending order of salary.

select manager_id, MIN(salary) from employees group by manager_id having manager_id is not null and min(salary)<=6000 order by min(salary) desc;

1. Displays the number of employees in department 50.

select count(employee_id ) from employees where department_id=50;

1. Display the number of distinct department values in the EMPLOYEES table.

select count(distinct department_id ) from employees ;

1. Display of average salaries of those departments that have an average salary greater than $8,000

select department_id, round(avg(salary)) from employees group by department_id having avg(salary)>8000 ;

1. Write a query to display the last name and hire date of any employee in the same department as Zlotkey. Exclude Zlotkey.

SELECT last_name, hire_date
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE last_name like 'Zlotkey')
AND last_name <> 'Zlotkey'

1. Create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending order of salary.

SELECT employee_id, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary

1. Write a query that displays the employee numbers and last names of all employees who work in a department with any employee whose last name contains a “u”.

SELECT employee_id, last_name
FROM employees
WHERE department_id
IN (SELECT department_id FROM employees WHERE last_name like '%u%');

1. Display the last name, department number, and job ID of all employees whose department location ID is 1700.

SELECT last_name, department_id, job_id
FROM employees
WHERE department_id = (SELECT department_id FROM locations WHERE location_id = 1700);

1. Display the last name and salary of every employee whose manager is King.

SELECT last_name, salary
FROM employees
WHERE manager_id IN (SELECT employee_id FROM employees WHERE last_name = 'King');

1. Display the department number, last name, and job ID for every employee in the department named “Executive”.

SELECT department_id, last_name, job_id
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Executive');

1. Display all the employees who earn more than “Abel”

SELECT employee_id, last_name
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

1. Display the employees whose job ID is the same as that of employee 141.

SELECT employee_id, last_name
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

1. Display employee last name, job ID, and salary of all employees whose salary is equal to the minimum salary

SELECT last_name, job_id, salary FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

1. displays all the departments that have a minimum salary greater than that of department 50.

SELECT department_id, MIN(salary)
FROM employees
group by department_id
having MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

1. Find the employees who earn the same salary as the minimum salary for each department.

Hint ! The main query would look like the following:

SELECT last_name, salary, department_id

FROM employees

WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 1 7000);

SELECT last_name, salary, department_id
FROM employees
WHERE salary in (SELECT MIN(salary) FROM employees GROUP BY department_id);

1. . Run the statement in the sql script to build the EMPLOYEE_NRINDEKSU table that will be used for the lab.

CREATE TABLE my_employee
(id NUMBER(4) constraint my_employee_id_nn Not null,
last_name Varchar2(25),
first_name varchar2(25),
userid varchar2(8),
salary Number(9,2))

1. Describe the structure of EMPLOYEE_NRINDEKSU the table to identify the column names.

describe my_employee_nn;

1. Add the first row of data to the EMPLOYEE_NRINDEKSU table from the following sample data. Do not list the columns in the INSERT clause.

insert into my_employee_nn values (1, 'Patel' , 'ralph' , 'rpatel' , 895);

1. Populate the EMPLOYEE_ NRINDEKSU table with the rows (of sample data) from the preceding list. This time, list the columns explicitly in the INSERT clause.

insert all
into my_employee_nn values (2, 'Dancs' , 'Betty' , 'bdancs' , 860)
into my_employee_nn values (3, 'Biri' , 'Ben' , 'bbiri' , 1100)
into my_employee_nn values (4, 'Newman' , 'Chad' , 'cnewman' , 750)
into my_employee_nn values (5, 'Roppburn' , 'Audrey' , 'aropebur' , 895)
select* from dual;

1. Confirm your addition to the table.

Update and delete data in the EMPLOYEE_NRINDEKSU table.

select* from my_employee_nn;

1. Change the last name of employee 3 to Drexler.

update my_employee_nn
set last_name= 'Dexter'
where id=3;

1. Change the salary to 1000 for all employees with a salary less than 900.

update my_employee_nn
set salary= 1000
where salary<900;

1. Verify your changes to the table.

select* from my_employee_nn ;

1. Delete Betty Dancs from the EMPLOYEE_NRINDEKSU table.

delete from my_employee_nn
where first_name='Betty' and last_name='Dancs';

1. Confirm your changes to the table.

select* from my_employee_nn ;

1. Commit all pending changes.

COMMIT;

1. Create the DEPT_ NRINDEKSU table based on the following table instance chart.

CREATE TABLE DEPT_NRINDEKSU
(id NUMBER(7),
NAME Varchar2(25));

1. Populate the DEPT_ NRINDEKSU table with data from the DEPARTMENTS table. Include only columns that you need.

insert into dept_nrindeksu (id , name) select department_id , department_name from departments;