·¢²¼Ê±¼ä : ÐÇÆÚÈÕ ÎÄÕÂsqlÓï¾ä¸üÐÂÍê±Ï¿ªÊ¼ÔĶÁ
----------------------------µÚ¶þÕÂ----------------------- --1.²éѯԱ¹¤±íÖÐËùÓÐÔ±¹¤µÄÐÅÏ¢¡£ SELECT * FROM employees;
--2.²éѯԱ¹¤±íÖÐÔ±¹¤µÄÔ±¹¤ºÅ¡¢ÐÕÃû¡¢Ã¿¸öÔ±¹¤Õǹ¤×Ê100ÔªÒÔºóµÄÄ깤×Ê£¨°´12¸öÔ¼ÆË㣩¡£
SELECT employee_id,last_name,(100+salary)*12 FROM employees;
--3.²éѯԱ¹¤first_nameºÍlast_name£¬ÒªÇó½á¹ûÏÔʾΪ¡°ÐÕlast_nameÃûfirst_name¡±¸ñʽ¡£ SELECT 'ÐÕ'||last_name||'Ãû'||first_name Ô±¹¤ÐÕÃû FROM employees;
--4.²éѯËùÓÐÔ±¹¤Ëù´ÓÊµĹ¤×÷ÓÐÄÄЩÀàÐÍ£¨ÒªÇóÈ¥µôÖظ´Öµ£©¡£ SELECT DISTINCT job_id FROM employees;
SELECT DISTINCT department_id,job_id FROM employees; ----------------------------µÚÈýÕÂ-------------------------- --1.²éѯlast_nameÊÇChenµÄÔ±¹¤µÄÐÅÏ¢¡£ SELECT *
FROM employees
WHERE last_name='Chen';
--2.²éѯ²Î¼Ó¹¤×÷ʱ¼äÔÚ1997-7-9Ö®ºó£¬²¢ÇÒ²»´ÓÊÂIT_PROG¹¤×÷µÄÔ±¹¤µÄÐÅÏ¢¡£ SELECT *
FROM employees
WHERE hire_date>'09-7ÔÂ-1997' AND job_id NOT IN ('IT_PROG');
SELECT * FROM employees WHERE hire_date>='9-7ÔÂ-97' and job_id not like 'IT_PROG';
--3.²éѯԱ¹¤last_nameµÄµÚÈý¸ö×ÖĸÊÇaµÄÔ±¹¤µÄÐÅÏ¢¡£ --£¨oracleÖеÄÄ£ºýͨÅä·ûÊÇ£¥£¬µ¥×ÖͨÅä·û_) SELECT *
FROM employees
WHERE last_name LIKE '__a%';
--4.²éѯ³ýÁË10¡¢20¡¢110ºÅ²¿ÃÅÒÔÍâµÄÔ±¹¤µÄÐÅÏ¢¡£ SELECT *
FROM employees
WHERE department_id NOT IN (10,20,110);
--5.²éѯ²¿ÃźÅΪ50ºÅÔ±¹¤µÄÐÅÏ¢£¬ÏÈ°´¹¤×ʽµÐòÅÅÐò£¬ÔÙ°´ÐÕÃûÉýÐòÅÅÐò¡£ SELECT *
FROM employees
WHERE department_id=50
ORDER BY salary DESC,last_name;
SELECT * FROM employees WHERE department_id=50
order by salary desc,first_name asc,last_name ASC; --6.²éѯûÓÐÉϼ¶¹ÜÀíµÄÔ±¹¤(¾ÀíºÅΪ¿Õ)µÄÐÅÏ¢¡£ SELECT *
FROM employees
WHERE manager_id IS NULL;
--7.²éѯԱ¹¤±íÖй¤×Ê´óÓÚµÈÓÚ4500²¢ÇÒ²¿ÃÅΪ50»òÕß60µÄÔ±¹¤µÄÐÕÃû(last_name), ¹¤×Ê£¬²¿Ãźš£
SELECT last_name,salary,manager_id FROM employees
WHERE salary>=4500 AND department_id IN (50,60);
SELECT last_name,salary,department_id FROM employees WHERE salary>=4500 and department_id in (50,60) --------------------------------µÚËÄÕÂ---------------------
--1.¼ÆËã2000Äê1ÔÂ1ÈÕµ½ÏÖÔÚÓжàÉÙÔ£¬¶àÉÙÖÜ£¨ËÄÉáÎåÈ룩¡£
select round(months_between(sysdate,to_date('2000-01-01','yyyy-mm-dd')),0) from dual; select round((sysdate - to_date('2000-01-01','yyyy-mm-dd'))/7) from dual; --2.²éѯԱ¹¤last_nameµÄµÚÈý¸ö×ÖĸÊÇaµÄÔ±¹¤µÄÐÅÏ¢(ʹÓÃ2¸öº¯Êý)¡£ SELECT last_name,salary,job_id FROM employees
WHERE last_name LIKE '__a%';
SELECT last_name,salary,job_id FROM employees
WHERE substr(last_name,3,1)='a';
--3.ʹÓÃtrimº¯Êý½«×Ö·û´®?hello?¡¢? Hello ?¡¢?bllb?¡¢? hello ?·Ö±ð´¦ÀíµÃµ½ÏÂÁÐ×Ö·û´®ello¡¢Hello¡¢ll¡¢hello¡£
SELECT TRIM('h' FROM 'hello') ello, TRIM(' ' FROM 'Hello') Hello, TRIM('b' FROM 'bllb') LL,
TRIM(' ' FROM 'hello ') AS HELLO FROM dual;
--4.½«Ô±¹¤¹¤×Ê°´ÈçϸñʽÏÔʾ£º123,234.00 RMB
SELECT last_name,to_char(salary,'999,999.00')||'RMB' \FROM employees;
select employee_id ,
to_char(salary,'999,999,999,99.00L')as salary from employees
--5.²éѯԱ¹¤µÄlast_name¼°Æä¾Àí£¨manager_id£©£¬ÒªÇó¶ÔÓÚûÓоÀíµÄÏÔʾ¡°No Manager¡±×Ö·û´®¡£
SELECT last_name,nvl(to_char(manager_id),'No Manager') Manager FROM employees;
--6.½«Ô±¹¤µÄ²Î¼Ó¹¤×÷ÈÕÆÚ°´ÈçϸñʽÏÔʾ£ºÔ·Ý/Äê·Ý¡£
SELECT last_name,extract(MONTH FROM hire_date)||'/'||extract(YEAR FROM hire_date) FROM employees;
--×¢Ò⣺to_char·½·¨¿ÉÒÔÕâÑùÓÃ
select last_name,to_char(hire_date,'mm/yyyy')as hire_date from employees; --7.ÔÚemployees±íÖвéѯ³öÔ±¹¤µÄ¹¤×Ê£¬²¢¼ÆËãÓ¦½»Ë°¿î£º --Èç¹û¹¤×ÊСÓÚ1000,Ë°ÂÊΪ0£¬
--Èç¹û¹¤×Ê´óÓÚµÈÓÚ1000²¢Ð¡ÓÚ2000£¬Ë°ÂÊΪ10£¥£¬ --Èç¹û¹¤×Ê´óÓÚµÈÓÚ2000²¢Ð¡ÓÚ3000£¬Ë°ÂÊΪ15£¥£¬ --Èç¹û¹¤×Ê´óÓÚµÈÓÚ3000£¬Ë°ÂÊΪ20£¥¡£ SELECT last_name,salary, CASE (salary/1000) WHEN 0 THEN 0
WHEN 1 THEN salary*0.1 WHEN 2 THEN salary*0.15 ELSE salary*0.2 END TAX FROM employees;
--------------------------------µÚÎåÕÂ---------------------------
--1.²éѯԱ¹¤µÄ±àºÅ£¬ÐÕÃû£¬ÒÔ¼°²¿ÃÅÃû³Æ(·Ö±ðʹÓÃOracleÓï·¨£¬×ÔÈ»Á¬½Ó£¬using×Ӿ䣬on×Ó¾ä)¡£
select employee_id,last_name,first_name,department_name from employees,departments
select employee_id,last_name,first_name,department_name from employees
natural join departments
select employee_id,last_name,first_name,department_name from employees join departments using(department_id)
SELECT e.employee_id,e.last_name,e.first_name ,d.department_name FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
--2.²éѯ²¿ÃÅÃû³ÆΪShippingµÄÔ±¹¤µÄ±àºÅ¡¢ÐÕÃû¼°Ëù´ÓÊµĹ¤×÷¡£ SELECT employee_id,last_name, job_title FROM employees e JOIN jobs j
ON e.job_id = j.job_id JOIN departments d
ON e.department_id=d.department_id WHERE department_name='Shipping';
--3.²éѯËùÓй¤×Ê´óÓÚµÈÓÚ6000ÔªµÄÔ±¹¤ÐÕÃû¼°ÆäÖ±½ÓÁìµ¼È˵ÄÐÕÃû¡¢¹¤×Ê¡£ÒªÇó²éѯ½á¹ûÖÐÔÚÔ±¹¤ºÍÖ±½ÓÁìµ¼ÈËÖ®¼ä¼ÓÈë×Ö·û´®¡°works for¡±¡£
select e1.last_name,e1.salary,e1.last_name ||' works for '|| e2.last_name work_for,e2.last_name,e2.salary
from employees e1,employees e2
where e1.manager_id=e2.employee_id and e1.salary>=6000 order by e1.salary;
--4.²éѯԱ¹¤µÄ±àºÅ£¬ÐÕÃû£¬ÒÔ¼°²¿ÃÅÃû³Æ£¬°üÀ¨Ã»ÓÐÔ±¹¤µÄ²¿ÃÅ¡£ select employee_id,last_name,department_name from employees e,departments d
where e.department_id(+) = d.department_id
select employee_id,last_name,department_name from employees e
RIGHT OUTER JOIN departments d
ON e.department_id= d.department_id;
--5.²éѯԱ¹¤µÄ±àºÅ£¬ÐÕÃû£¬ÒÔ¼°²¿ÃÅÃû³Æ£¬°üÀ¨²»ÊôÓÚÈκβ¿ÃŵÄÔ±¹¤¡£ select employee_id,last_name,department_name FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id= d.department_id;
--6.ÏÔʾ±ÈÔ±¹¤?Abel?²Î¼Ó¹¤×÷ʱ¼äÍíµÄÔ±¹¤ÐÕÃû£¬¹¤×Ê£¬²Î¼Ó¹¤×÷ʱ¼ä¡£ select e1.last_name,e1.salary,e1.hire_date from employees e1,employees e2 where e1.hire_date>e2.hire_date and e2.last_name like 'Abel'; ----------------------------µÚÁùÕÂ----------------------
--1.²éѯ¸÷²¿ÃÅƽ¾ù¹¤×ÊÔÚ8000ÔªÒÔÉϵIJ¿ÃÅÃû³Æ¼°Æ½¾ù¹¤×Ê¡£ select d.department_name,avg(e.salary) from employees e,departments d
where e.department_id=d.department_id group by d.department_name having avg(e.salary)>8000;
--2.²éѯ¹¤×÷±àºÅÖв»º¬ÓС°SA_¡±×Ö·û´®¼°Æ½¾ù¹¤×ÊÔÚ8000ÔªÒÔÉϵŤ×÷±àºÅ¼°Æ½¾ù¹¤×Ê£¬²¢°´Æ½¾ù¹¤×ʽµÐòÅÅÐò¡£ select j.job_id,avg(e.salary) from jobs j,employees e where j.job_id=e.job_id group by j.job_id
having j.job_id not like'SA_'and avg(e.salary)>8000 order by avg(e.salary) desc
--3.²éѯ²¿ÃÅÈËÊýÔÚ4ÈËÒÔÉϵIJ¿ÃŵIJ¿ÃÅÃû³Æ¼°×îµÍ¹¤×ʺÍ×î¸ß¹¤×Ê¡£ select d.department_name,min(e.salary),max(e.salary) from employees e,departments d