department_id和job_id
Select employee_id,department_id,job_id from employees where department_id = any (select department_id from departments where location_id=1700);
30. 查询管理者是king的员工姓名和工资
Select last_name,salary from employees where manager_id=(select employee_id from employees where last_name='King');
创建和管理表 31. 创建表dept name id name Create table dept
( id Number(7),name varchar(25));
Null? type Number(7) Varchar2(25) 32. 将表departments中的数据插入表dept中 Insert into dept(
SELECT department_id, department_name FROM departments);
33. 创建表emp name Null? type id First_name Last_name Dept_id Number(7) Varchar2(25) Varchar2(25) Number(7) Create table emp (id Number(7),First_name varchar(25),Last_name varchar(25),Dept_id number(7)); 34. 将列Last_name的长度增加到50
Alter table emp modify (last_name varchar2(50)); 35. 查询数据字典视图user_tables检查刚才的操作 Select * from user_tables;
36. 根据表employees创建employees2 Create table employees2 as select * from employees; 37. 删除表emp Drop table emp;
38. 将表employees2重命名为emp Rename employees2 to emp;
39. 在表dept和emp中添加新列test_column，并检查所作的操作 Alter table emp add(test_column varchar(10)); 单行函数 40. 显示系统时间
Select to_char(sysdate 'DD-MON-YYYY')from dual;
41. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
Select employee_id,last_name||' '||first_name,salary,salary*1.2 newsalary from employees;
42. 将员工的姓名按首字母排序，并写出姓名的长度（length） Select
last_name,length(last_name)
from
employees
order
by
substr(last_name,1,1) desc;
43. 查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）。 Select
last_name||\
'||first_name,months_between(sysdate,hire_date)
worked_month from employees;
44. 查询员工的姓名和工资，按下面的形式显示
Last_name king employees;
SALARY $$$$$$$$$$24000 Select lower(last_name) \\from
45. 查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列 Select
last_name||'
'||first_name,months_between(sysdate,hire_date)
worked_month from employees order by worked_month desc; 46. 做一个查询，产生下面的结果
Dream Salary King earns $24000 monthly but wants $72000 Select 'King'||' earns '||lpad(salary,6,'$')||' monthly but wants '||lpad(salary*3,6,'$') \Dream Salary\47. 做一个查询，产生下面的结果add_month(6)+4 Last_name king Hire_date 17-jun-87 reiew Monday,the twenty-first of December , 1987 Select
lower(last_name)
\,MONTH,YYYY') \9做一个查询，产生下面的结果
Employees_and_their_salarys King*************************** 其中每一个*代表一千元。 Select
rpad(last_name,salary/1000+length(last_name),'$')
from
employees where last_name like 'King'; 48. 使用decode函数，按照下面的条件： job grade AD_PRES A ST_MAN B
IT_PROG C SA_REP D ST_CLERK E 产生下面的结果
Last_name king Select last_name,job_id,
Job_id AD_PRES Grade A Decode (job_id ,'AD_PRES',' A ', 'ST_MAN',' B ', 'IT_PROG',' C ', 'SA_REP',' D ' ,
'ST_CLERK',' E ',') Grade from employees; 49. 将第9题的查询用case函数再写一遍。 Select last_name,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 ' end \ from employees;