oracle第一章上机,oracle 查询上机实验

查询是否有hr用户

conn sys/oracle as sysdba;

desc dba_users；

select username,account_status from dba_users;

第一章 基本的sql语句

4.

select distinct job_id from employees;

5.

select last_name || ', ' || job_id as "JOB"

from employees;

6.

select

employee_id || ',' ||

first_name || ',' ||

last_name || ',' ||

email || ',' ||

phone_number || ',' ||

hire_date || ',' ||

job_id || ',' ||

salary || ',' ||

commission_pct || ',' ||

manager_id || ',' ||

department_id as "the_output"

from employees;

第二章 限制和排序数据

1

select last_name,salary

from employees where

salary>12000;

2.

select last_name,department_id

from employees where

employee_id = 176;

3.

select last_name,salary

from employees

where salary not between 5000 and 12000;

4.**

select last_name,job_id,hire_date from employees where

hire_date between to_date('2/20/1998','MM/DD/YYYY')

and to_date('5/1/1998','MM/DD/YYYY')

order by hire_date;

5.*

select last_name,department_id

from employees

where department_id in(20,50)

order by 1;

6.

select last_name,hire_date

from employees

where to_char(hire_date,'YYYY')=1994;

7.*

select last_name,job_id

from employees

where manager_id is null;

8.*

select last_name,salary,commission_pct

from employees

where commission_pct is not null

order by salary desc,commission_pct desc;

9.*

select e.last_name,j.job_title,e.salary

from jobs j,employees e

where e.job_id=j.job_id and

j.job_title in('Sales Representative','Stock Clerk')

and e.salary not in(2500,3500,7000);

10

select last_name,salary,commission_pct

from employees

where commission_pct=0.2;

第三章 单行函数

1.

select sysdate as "Date" from dual;

2.

select employee_id,last_name,salary,salary*1.15 "New Salary"

from employees;

3.

select initcap(last_name) last_name,length(last_name) name_length

from employees

where upper(substr(last_name,1,1)) in ('J','A','M')

order by 1;

4.

select

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

from employees;

5.

select last_name,

lpad(substr(to_char(salary),1,15),15,'$')

as "SALARY" from employees;

6.

select last_name,

nvl(to_char(commission_pct),'NO Commission')

as "COMM" from employees;

7.

select distinct job_id,

decode(job_id, 'AD_PRES','A',

'ST_MAN','B',

'IT_PROG','C',

'SA_REP','D',

'ST_CLERK','E',

'0') Grade

from employees;

8.

select distinct job_id,

case job_id when 'AD_PRES' then 'A'

when 'ST_MAN' then 'B'

when 'IT_PROG' then 'C'

when 'SA_REP' then 'D'

when 'ST_CLERK' then 'E'

else '0'

end Grade

from employees;

第五章 使用分组函数聚集数据

1.

select max(salary) "Maximum",min(salary) "Minimum",

sum(salary) "Sum",round(avg(salary),0) "Average"

from employees;

2.

select job_id,

max(salary) "Maximum",min(salary) "Minimum",

sum(salary) "Sum", round(avg(salary),0) "Average"

from employees

group by job_id;

3.

select job_id, count(*) "Count"

from employees

group by job_id;

4.

select count(distinct manager_id) "Manager Count"

from employees;

5.

select max(salary)-min(salary) "DIFFERENCE"

from employees;

6.

select e.manager_id,min(e.salary) "salary"

from employees e

group by e.manager_id

having min(e.salary)<=6000

order by 2;

7.

select d.department_name "Name",d.location_id "Location",

count(*) "Number of People",round(avg(salary),2) "Salary"

from employees e,departments d

where e.department_id=d.department_id

group by d.department_name,d.location_id;

8.

select 行列转换

count(*) total,

sum(decode(to_char(hire_date,'YYYY'),1995,1,0))"1995",

sum(decode(to_char(hire_date,'YYYY'),1996,1,0))"1996",

sum(decode(to_char(hire_date,'YYYY'),1997,1,0))"1997",

sum(decode(to_char(hire_date,'YYYY'),1998,1,0))"1998"

from employees;

9. 行列转换

select job_id "Job",

sum(decode(department_id,20,salary,0)) "dept_20",

sum(decode(department_id,50,salary,0)) "dept_50",

sum(decode(department_id,80,salary,0)) "dept_80",

sum(decode(department_id,90,salary,0)) "dept_90"

from employees

group by job_id;

第六章 多表查询

1.

select e.last_name,d.department_id,d.department_name

from employees e,departments d

where e.department_id=d.department_id;

2.

select distinct j.*

from employees e,jobs j

where j.job_id=e.job_id

and e.department_id=80;

3.

select

e.last_name,d.department_name,d.location_id,

l.city

from employees e,departments d,locations l

where e.department_id=d.department_id

and d.location_id=l.location_id

and e.commission_pct is not null;

4.

select e.last_name,d.department_name

from employees e,departments d

where e.department_id=d.department_id

and lower(e.last_name) like '%a%';

5.

select

e.last_name,e.job_id,d.department_id,d.department_name

from employees e,departments d,locations l

where l.city='Toronto'

and e.department_id=d.department_id

and d.location_id=l.location_id;

6.

select

e.last_name "Employee",e.employee_id "Emp#",

m.last_name "Manager",m.manager_id "Mgr#"

from employees e,employees m

where e.manager_id=m.employee_id;

8.

select e.last_name,e.hire_date

from employees e,employees d

where d.hire_date

and d.last_name='Davies'

;

9.

select e.last_name "Employee",e.hire_date "Emp_Hired",

m.last_name "Manager",m.hire_date "Mgr_Hired"

from employees e,employees m

where e.hire_date

and e.manager_id=m.employee_id;

第七章 子查询

1.

select last_name,hire_date

from employees

where department_id=(select department_id from employees

where last_name='Zlotkey')

and last_name<>'Zlotkey';

2.

select employee_id,last_name

from employees

where salary>(select avg(salary) from employees);

3.

select employee_id,last_name

from employees

where department_id in(

select department_id

from employees

where last_name like '%u%'

);

4.

select last_name,department_id,job_id

from employees

where department_id in

(

select department_id

from departments

where location_id=1700

);

5.

select last_name,salary

from employees

where manager_id in(

select employee_id

from employees

where last_name='King'

);

6.

select department_id,last_name,job_id

from employees

where department_id in

(

select department_id

from departments

where department_name='Executive'

);

7.

select employee_id,last_name,salary

from employees

where department_id in

(

select department_id

from employees

where last_name like '%u%'

) and salary>(

select avg(salary)

from employees

);