Structured Query Language SELECT DML( Data Manipulation Language) DDL( Data Definition Language) DCL( Data Control Language) Commit, Rollback, Savepoint

Select StatementSELECT*|{[DISTINCT] column|expression [alias],...}FROMtable;SELECT .FROM .

Select Statement SELECT *FROM departments; SELECT department_id, location_idFROM departments;

SQL SQL . SQL . . . SQL .

.+-* / SELECT last_name, salary, salary + 300FROM employees;

NULL , , , . 0 .SELECT last_name, job_id, salary, commission_pctFROM employees;

NULL SELECT last_name, 12*salary*commission_pctFROM employees; .

Column Alias() . . . AS . . SELECT last_name AS name, commission_pct commFROM employees;

: . (||) . .

SELECTlast_name||job_id AS "Employees"FROM employees;

Literal SELECT , . . (row) .

SELECT last_name||' is a '||job_id AS "Employee Details"FROM employees;

SELECT DISTINCT .SELECT DISTINCT department_idFROM employees;

SQL vs. iSQL*PlusSQL

SQL ANSI

iSQL*Plus iSQL*Plus

WHERE .

WHERE FROM .

SELECT*|{[DISTINCT] column|expression [alias],...}FROMtable[WHEREcondition(s)];

SELECT employee_id, last_name, job_id, department_idFROM employeesWHERE department_id = 90 ;

. . DD-MON-RR.SELECT last_name, job_id, department_idFROM employeesWHERE last_name = 'Whalen';

=> >=<

Between

BETWEEN .SELECT last_name, salaryFROM employeesWHERE salary BETWEEN 2500 AND 3500;

Like

LIKE . .% ._ .SELECTfirst_nameFROM employeesWHEREfirst_name LIKE 'S%';

SELECT last_name, job_id, salaryFROM employeesWHERE job_id = 'SA_REP'OR job_id = 'AD_PRES'AND salary > 15000;

Order By

SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date ;ORDER BY .ASC: , DESC: ORDER BY SELECT .

SQL

LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIM

.LOWER('SQL Course')UPPER('SQL Course')INITCAP('SQL Course')sql courseSQL COURSESql Course

SELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = 'higgins';

CONCAT('Hello', 'World')SUBSTR('HelloWorld',1,5)LENGTH('HelloWorld')INSTR('HelloWorld',O,2)LPAD(salary,10,'*')RPAD(salary,10,'*')TRIM('H' FROM 'HelloWorld')HelloWorldHello107*****2400024000*****elloWorld .

ROUND: .ROUND(45.926, 2) 45.93TRUNC: .TRUNC(45.926, 2)45.92MOD: .MOD(1600, 300)100SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;

. , , , , , , DD-MON-RR. 20 21 . 21 20 . SELECT last_name, hire_dateFROM employeesWHERE last_name like 'G%';

. . 24 .SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;

MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')ADD_MONTHS ('11-JAN-94',6)NEXT_DAY ('01-SEP-95','FRIDAY') LAST_DAY('01-FEB-95') 19.6774194'11-JUL-94''08-SEP-95''28-FEB-95'

ROUND(SYSDATE,'MONTH') 01-AUG-95ROUND(SYSDATE ,'YEAR') 01-JAN-96TRUNC(SYSDATE ,'MONTH') 01-JUL-95 TRUNC(SYSDATE ,'YEAR') 01-JAN-95Assume SYSDATE = '25-JUL-95':

(implicit) (explicit)

NUMBERCHARACTERTO_CHAR

TO_CHAR ( -> ) . . fm . .TO_CHAR(date, 'format_model')

YYYYYEARMMMONTHDYDAY ()() MON (DDD, DD, D) ( Q : ), fm

.

.

.

to_char SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATEFROM employees;

TO_CHAR ( -> )TO_CHAR .TO_CHAR(number, 'format_model')90$L., .0 . $ . . . .

TO_NUMBER, TO_DATETO_NUMBER .

TO_DATE .

fx . TO_DATE .TO_NUMBER(char[, 'format_model'])TO_DATE(char[, 'format_model'])

RR 1995199520012001 27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR 1995201720171995YY 1995191720172095 ( )04904950995099 . . . . ( )

NVL . , . .NVL(commission_pct,0)NVL(hire_date,'01-JAN-97')NVL(job_id,'No Job Yet')

SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SALFROM employees;

NVL2

SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') incomeFROM employees WHERE department_id IN (50, 80);

1212

Coalesce SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;

CASE SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY"FROM employees;IF-THEN-ELSE .

DECODE SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARYFROM employees;

EMPLOYEES DEPARTMENTS

Catersian product . WHERE .

1.

.

WHERE . .SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2;

SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;

. . .

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e , departments dWHERE e.department_id = d.department_id; . . .

EMPLOYEESJOB_GRADES

SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

(Outer join)EMPLOYEESDEPARTMENTS

( (+))

SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id(+) = d.department_id ;

Cross join CROSS JOIN . (Cartesian Product) . SELECT last_name, department_nameFROM employeesCROSS JOIN departments ;

Natural joinNATURAL JOIN . . . SELECT department_id, department_name, location_id, cityFROM departmentsNATURAL JOIN locations ;

Using , NATURAL JOIN USING .USING . .NATURAL JOIN USING .

Using SELECT e.employee_id, e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id) ;

ON . ON . .ON . SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id);

vs. SQL: 1999 . ( ) ( ) . .

Left outer join SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;

SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id) ;Right outer join

SELECT e.last_name, e.department_id, d.department_nameFROM employees eFULL OUTER JOIN departments dON (e.department_id = d.department_id) ;Full outer join

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id)AND e.manager_id = 149 ;

.EMPLOYEESEMPLOYEES

AVG COUNT MAXMIN STDDEV SUMVARIANCE

SELECT[column,] group_function(column), ...FROMtable[WHEREcondition][GROUP BYcolumn][ORDER BYcolumn];

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)FROM employeesWHERE job_id LIKE '%REP%'; AVG SUM .

MIN MAX .SELECT MIN(hire_date), MAX(hire_date)FROM employees;

NULL

SELECT COUNT(*)FROM employeesWHERE department_id = 50;COUNT(*) .

COUNT(expr) expr .EMPLOYEES .SELECT COUNT(commission_pct)FROM employeesWHERE department_id = 80;

NVL

SELECT AVG(commission_pct)FROM employees; .

SELECT AVG(NVL(commission_pct, 0))FROM employees;NVL .

EMPLOYEESEMPLOYEES 440095003500640010033

SELECTcolumn, group_function(column)FROMtable[WHEREcondition][GROUP BYgroup_by_expression][ORDER BYcolumn];GROUP BY .Group by

Group by

SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id ;SELECT GROUP BY .

having

SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)>10000 ;

SELECT job_id, SUM(salary) PAYROLLFROM employeesWHERE job_id NOT LIKE '%REP%'GROUP BY job_idHAVING SUM(salary) > 13000ORDER BY SUM(salary); having

Abel ? Abel ??Abel ??

( ) . ( ) .SELECTselect_listFROMtableWHEREexpr operator (SELECTselect_list FROMtable);

SELECT last_nameFROM employeesWHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

11000

. . ORDER BY Top-N . .

ST_CLERKST_CLERKSA_MAN

. .=> >=<

SELECT last_name, job_id, salaryFROM employeesWHERE salary = (SELECT MIN(salary) FROM employees);2500

SELECT department_id, MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);2500

SELECT employee_id, last_nameFROM employeesWHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);ERROR at line 4:ORA-01427: single-row subquery returns more than one row .

. . IN ANY ALL . . .

9000, 6000, 4200SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id 'IT_PROG';

9000, 6000, 4200SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id 'IT_PROG';

SELECT emp.last_nameFROM employees empWHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr);

no rows selected

Insert INSERT .

.INSERT INTOtable [(column [, column...])]VALUES(value [, value...]);INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES (70, 'Public Relations', 100, 1700);1 row created. . .

NULL Insert

INSERT INTOdepartmentsVALUES(100, 'Finance', NULL, NULL);1 row created.INSERT INTOdepartments (department_id, department_name )VALUES(30, 'Purchasing');1 row created.

INSERT INTO employeesVALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30);1 row created.

Insert INSERT .

VALUES .INSERT .

INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';

4 rows created.

Update UPDATE . .UPDATEtableSETcolumn = value [, column = value, ...][WHERE condition];UPDATE employeesSET department_id = 70WHERE employee_id = 113;1 row updated.UPDATE copy_empSET department_id = 110;22 rows updated.

update

UPDATE employeesSET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114;1 row updated.

UPDATE employees *ERROR at line 1:ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not foundUPDATE employeesSET department_id = 55WHERE department_id = 110; 55 .

Update DELETE .DELETE [FROM] table[WHERE condition]; DELETE FROM departments WHERE department_name = 'Finance';1 row deleted.DELETE FROM copy_emp;22 rows deleted.

update

DELETE FROM employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');1 row deleted.

DELETE FROM departmentsWHERE department_id = 60;DELETE FROM departments *ERROR at line 1:ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found .

DML with check option DML .WITH CHECK OPTION .INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION)VALUES (99998, 'Smith', 'JSMITH', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000);INSERT INTO *ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violation

Default INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, 'Engineering', DEFAULT);UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;INSERT DEFAULT

UPDATE DEFAULT

. DML DDL DCL DML SQL . . DDL DCL ( ) COMMIT ROLLBACK iSQL*Plus

.DDL DCL

iSQL*Plus .COMMIT ROLLBACK (explicit) iSQL*Plus

Lock (exclusive): (share): DML: , : DDL: .

Structured Query Language DDL( Data Definition Language) CREATE ALTER DROP TRUNCATE

: .1 30 .A-Z, a-z, 0-9, _, $, # . .Oracle server .

Create table .

. ,

CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);CREATE TABLE

. . .

, SQL . . .

... hire_date DATE DEFAULT SYSDATE, ...

.

. DESCRIBE dept

VARCHAR2(size) CHAR(size) NUMBER(p,s) DATE LONG 2GB CLOB 4GB RAW LONG RAW BLOB 4GB BFILE ( 4GB)ROWID 64

Timestamp TIMESTAMP INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND , , ,

TIMESTAMP DATE .DATE , , , , . TIMESTAMP . TIMESTAMP[(fractional_seconds_precision)]

CREATE TABLE AS subquery .

. .

CREATE TABLE table [(column, column...)]AS subquery;

DESCRIBE dept80CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;Table created.

Alter table ALTER TABLE , .

ALTER TABLE tableADD (column datatype [DEFAULT expr] [, column datatype]...);ALTER TABLE tableMODIFY (column datatype [DEFAULT expr] [, column datatype]...);

ALTER TABLE tableDROP (column);

ALTER TABLE dept80ADD (job_id VARCHAR2(9));Table altered.ADD .

. , .

Alter table

DROP COLUMN .

ALTER TABLE tableSET UNUSED(column);ALTER TABLE tableSET UNUSED COLUMN column;

ALTER TABLE tableDROP UNUSED COLUMNS;Alter table

. . .DROP TABLE .

DROP TABLE dept80;Table dropped.

RENAME dept TO detail_dept;Table renamed., , RENAME .

TRUNCATE TABLE : . .

TRUNCATE . DELETE .

TRUNCATE TABLE detail_dept;Table truncated.