DOC

sql

By Virginia Carpenter,2014-05-17 05:53
11 views 0
sql

1.

     1) insert into location (location_id, city) values (seq_location.nextval, ‘大连')

     2) insert into location (location_id) values (seq_location.nextval) 2. select location_id from location

    3.

     1) insert into department (department_id, department_name, location_id) values (seq_department.nextval, ‘教学部', (select location_id from location where city like ‘大连'))

     2) insert into departion (department_id, department_name, location_id) values (seq_departion.nextval, ‘市场部', 2)

    4. select departmen_id from department

    5.

     1) insert into employees (employees_id, last_name, department_id, job_id, salary, hire_date,

    email) values (seq_employees.nextval, ‘张三', 1, teacher1, 3000, to_date(2006-9-5,

    'yyyy-mm-dd), 'aa@163.com)

     2) insert into employees (employees_id, last_name, department_id, job_id, salary, hire_date,

    phone_number)

    values (seq_employees.nextval,‘张三',2, agora1, 2500, to_date(2006-9-5, 'yyyy-mm-dd),

    82365421)

    6. select * from employees

    7. select * from employees where salary > 2500

    8. select * from employees where salary >= 2500

    9. select * from employees where salary < 2500

    10. select * from employees where salary <= 2500

    11. select * from employees where salary <> 2500 select * from employees where salary != 2500

    12. select * from employees where employees_id between 1 and 10 13. select * from employees where employees_id not in (2) 14. select * from employees where last_name like _%'

    15. select * from employees where phone_number is null

    16. select count (*) from employees

    17. select max (salary) from employees

    18. select min (salary) from employees

    19. select avg (salary) from employees

    20. select sum (salary) from employees

    21. select department_id, avg (salary) from employees group by department_id 22. select * from employees order by last_name

    23. update employees set salary=3500 where last_name like ‘张三'

    update employees set salary=3000 where salary=2500

    24. select nvl(city, ‘大连') from location where location_id=2

    25. select last_name, job_id, salary from employees where job_id= (select job_id from employees where last_name like ‘张三')

    26. select employees_id, last_name, job_id, salary from employees where salary=(select min

    (salary) from employees)

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

    d.location_id from employees e, department d where e.department_id=d.department_id 28. select e.employees_id, e.last_name, e.department_id, d.department_id, d.department_name, d.location_id, l.location_id, l.city from employees e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id

    29. select e.last_name, e.department_id, d.department_name from employees e left outer join department d on e.department_id=d.department_id

    或者

    select e.last_name, e.department_id, d.department_name from employees e, department d where e.department_id=d.department_id (+)

    30. select e.last_name, e.department_id, d.department_name from employees e right join department d on e.department_id=d.department_id

    或者

    select e.last_name, e.department_id, d.department_name from employees e, department d where e.department_id (+)=d.department_id

Report this document

For any questions or suggestions please email
cust-service@docsford.com