[go: up one dir, main page]
Scribd
Upload
47 views3 pages

Employees: First - Name First - Name First - Name

The document contains SQL queries that perform various operations on database tables like employees and departments. These include queries with functions to manipulate and extract data like upper, lower, concat, substring. Other queries join multiple tables, group and aggregate data, filter on null values, and perform date operations.

Uploaded by

Vishal Panchal
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd
47 views3 pages

Employees: First - Name First - Name First - Name

The document contains SQL queries that perform various operations on database tables like employees and departments. These include queries with functions to manipulate and extract data like upper, lower, concat, substring. Other queries join multiple tables, group and aggregate data, filter on null values, and perform date operations.

Uploaded by

Vishal Panchal
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd
You are on page 1/ 3

select * from employees select Initcap(first_name),upper(first_name),lower(first_name) from employees select concat(first_name,last_name) from employees select substr(first_name,1,2) from employees

select length(last_name) from employees select lpad(first_name,10,'v') from employees select rpad (last_name,10,0) from employees select trim('a' from last_name) from employees select round(salary,2) from employees select trunc(salary,-2) from employees

select mod(salary,100) from employees

select sysdate from dual select last_name,hire_date from employees select hire_date-sysdate from employees select hire_date-30 from employees

select months_between(sysdate,hire_date) from employees select add_months(hire_date,10) from employees select next_day(sysdate,'Friday') from employees select last_day(sysdate) from employees

select round(sysdate),trunc(sysdate) from dual select round(sysdate,'Month'),trunc(sysdate,'Year') from dual select to_char(hire_date,' fmdd month yyyy MM:ss:HH AM') from employees

select to_char(salary,'999,99999$') from employees

select nvl(COMMISSION_PCT,0) from employees

select COMMISSION_PCT from employees where COMMISSION_PCT is not null select nvl(hire_date,'01-Jan-2011') from employees select hire_date from employees where hire_date is null select job_id from employees where job_id is null

select min(Distinct salary),max(Distinct salary),avg(Distinct salary),sum(Distinct salary),count(Distinct salary) from employees

select avg(COMMISSION_PCT) from employees select avg(nvl(COMMISSION_PCT,0)) from employees select nvl(COMMISSION_PCT,0) from employees where COMMISSION_PCT <> 0

select department_id,avg(salary) from employees group by department_id having department_id is not null order by 2--avg(salary) Select Job_id, round (Max(salary),0) "Maximum",Min(salary) "Average",Sum(salary) "Sum" from employees group by job_id select count(job_id),job_id from employees group by job_id order by job_id select count( distinct MANAGER_ID) "Number of Manager"from employees select min(salary),max(salary),(max(salary))-(min(salary)) "diff" from employees select MANAGER_ID,min(salary) from employees where MANAGER_ID is not null group by manager_id having min(salary) > 6000 order by min(salary) desc select job_id,department_id,salary,sum(salary) from employees where department_id in (20,50,80,90) group by job_id,department_id,salary

"Minimum",Avg(salary)

select l.city,d.department_name

from locations l join departments d using(location_id) select Distinct department_id from departments natural join locations where department_id in (10,20,30) select employees.employee_id,employees.first_name,departments.department_name, department_id from employees join departments using (department_id) select employees.employee_id,employees.first_name,departments.department_name from employees join departments on employees.department_id = departments.department_id select e.employee_id,e.first_name,d.department_name from employees e join departments d using (department_id) Select e.last_name,m.last_name from employees e join employees m on (E.MANAGER_ID = m.employee_id) and e.manager_id = 149 Select e.last_name,m.last_name from employees e join employees m on (E.MANAGER_ID = m.employee_id) where e.manager_id = 149 select employee_id,city,d.department_name from employees e Join departments d on E.DEPARTMENT_ID = D.DEPARTMENT_ID join locations l on l.location_id = D.LOCATION_ID select e.last_name,e.salary,j.JOB_ID from employees e join JOBs j on e.salary between J.MAX_SALARY and J.MIN_SALARY left outer, right outer, full join, cross join select * from employees cross join departments --on EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

You might also like