The first chapter basic SELECT statement Oracle database architecture. SYS and SYSTEM users.
SQL statements are not case sensitive. The difference between '' and "". Strings are case sensitive.
NULL value. Joint operator: || The default format of time is DD-MON-YY.
Select * from tab;
Field alias Select Job_title As Title, Min_Salary AS "Minimum Salry" from Jobs;
Guaranteed uniqueness select distinct department_id, job_id from Employees;
Dual Table Select sysdate, user from dual;
Not equal to (! =, <> Or ^ =) SELECT First_name || '|| Last_name "Name", commission_pct from Employees where commission_pct! = 0.35;
<= (Less than or equal) SELECT first_NAME || '' || Last_name "Name", commission_pct from Employees where commission_pct <= 0.35;
> = (Greater than or equal) SELECT first_NAME || '|| Last_name "name", commission_pct from Employees where commission_pct> = 0.35;
ANY (equivalent to OR) SELECT First_Name || '' || Last_Name "Name", Department_id from Employees Where Department_ID <= ANY (10, 15, 20, 25);
All (equivalent to AND) SELECT First_Name || '' || Last_Name "Name", Department_id from Employees Where Department_ID> = All (80, 90, 100);
IN and NOT INSELECT First_Name, Last_name, Department_ID from Employees Where Department_ID in (10, 20, 90);
IN is equivalent to = any.
SELECT first_NAME, LAST_NAME, Department_ID from Employees Where Department_id Not in (10, 30, 40, 50, 60, 80, 90, 110, 100);
Not in is equivalent to! = All.
Note: Last_name Not in ('Smith', 'Thomas', NULL) does not return any records. Any comparison with the NULL value is a null value.
BetWeenselect First_name, Last_name, Salary from Employees Where Salary BetWeen 5000 and 6000;
EXISTSSELECT LAST_NAME, First_Name, Department_ID from Employees Ewhere EXISTS (SELECT 1 from Departments Dwhere D.Department_ID = E.DEPARTMENT_ID AND D.DEPARTMENT_NAME = 'Administration');
IS NULL and IS NOT NULLSELECT LAST_NAME, Department_ID from Employees Where Department_ID is NULL Download Adobe Reader
Likeselect first_name, Last_name from Employees where first_name like 'su%' and last_name not like 's%';%, _ meaning. SELECT JOB_ID, JOB_TITLE from Jobs Where Job_id Like 'AC / _%' Escape '/';
Results Sort Sylct First_Name || '' || Last_name "Employee Name" from Employees Where Department_ID = 90 ORDER BY LAST_NAME; ASC, DESC. SELECT First_Name, Hire_Date, Salary, Manager_id Mid from Employees Where Department_ID in (110, 100) Order by MID ASC, SALARY DESC, HIRE_DATE
Error: Select Distinct 'Region' || Region_id from Countries ORDER BY Region_ID; Correct: Select Distinct 'Region' || Region_id from Countries Order By 'Region' || Region_ID;
Select first_name, hire_date, salary, manager_id mid from Employees Where Department_ID in (110, 100) Order BY 4, 2, 3;
NULL sort by default, the NULL value is finally in the ascending arrangement. SELECT LAST_NAME, Commsions_pct from Employees Where Last_name Like 'R%' Order by Commission_PCT ASC, Last_name DESC;
SELECT LAST_NAME, Commission_PCT from Employees WHERE LAST_NAME LIKE 'R%' Order by Commission_PCT ASC NULLS FIRST, LAST_NAME DESC;
Case expression (9i) Select Country_name, region_id, Case region_id when 1 Then 'Europe' When 2 Then 'AmericaWhen 3 Ten' Asia'Else 'Other' End ContinentFrom Countries Where Country_name Like 'i%'
SELECT first_name, department_id, salary, CASE WHEN salary <6000 THEN 'Low' WHEN salary <10000 THEN 'Medium' WHEN salary> = 10000 THEN 'High' END CategoryFROM employees WHERE department_id <= 30 ORDER BY first_name;
Chapter II SQL * Plus uses the common command of SQL * Plus.
Chapter 3 Oracle 9i's Functions NVLSelect First_Name, Last_Name, Salary, Bonus, Salary Bonus Total_Comp from Employees
SELECT first_name, last_name, salary, bonus, salary NVL (bonus, 0) total_comp FROM employees; NVL2 (9i new) SELECT first_name, last_name, salary, bonus, NVL2 (bonus, salary bonus, salary) total_comp FROM employees;
String Function Select ASCII ('A') BIG_A, ASCII ('Z') Little_z from Dual; SELECT CHR (65), Chr (223) from Dual; Select Concat ('Peter', 'Mackovy') from Dual; SELECT INITCAP ('The Three Musketeers') Book_Title from Dual; SELECT INSTR (' Mississippi ',' I ', 3, 3) TEST1, INSTR (' mississippi ',' i ', 1, 3) Test2 Instr (' mississippi ", 'I', - 2, 3) Test3 from Dual;
Analysis Data TEXT_STRING = 'Sunday | Monday | Tuesday'Select Text_String, Substr (Text_String, INSTR (Text_String,' | ', 1, 1) 1, INSTR (Text_String,' | ', 1, 2) - Instring, '|', 1, 1) - 1) from Dual;
INSTRBSELECT LENGTH ( 'the three musketeers') title_length FROM dual; LENGTHBLOWERSELECT LPAD ( '.' 'Yes', 7,) FROM dual; SELECT LTRIM ( 'Mississippi', 'Mis') test1, LTRIM ( 'RPadded') test2, LTRIM ('RPADDED') TEST3, LTRIM ('RPADDED', 'Z') TEST4 from Dual;
Mathematics Functions ABS, ACOS, ASIN, Atan, Atan2, Bitand, CEIL, COS, COSH, Exp, Floor, LN, Log, Mod, Power, Sign, Sin, Sinh, SQRT, TAN, TANH. Round = truncselect Trunc (123.456, 2) POS, Trunc (123.456, -1) NEG from DUAL
Date function add_months, last_day, month_between, next_time, next_day, round, sysdate.
Conversion Function Chartorowid, Convert, Hextoraw, NLS_Charset_ID, NLS_CHARSET_NAME, RAWTOHEX, ROWIDTOCHAR, TO_CHAR, TO_DATE, TO_ Label, TO_MULTI_BYTE, TO_NUMBER, TO_SINGLE_BYTE.
Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual; select to_char (sysdate, 'month') from dual; select to_char (ssssdate, 'sssss') from dual;
INSERT INTO DEMO (DEMO_KEY, DATE_COL) VALUES (1, to_date ('2004-7-1', 'YYYY-MM-DD');
Other functions Select coalesce (NULL, 'ORACLE', '24') String_Type, Coalesce (3, 14, COS (0)) BNR_TYPE from DUAL; Returns the first non-NULL value.
Select decode (Rating, NULL, 1000, 'C', 2000, 'B', 3000.'d ', 4000, 5000) from acccts;
SELECT User, Uid from Dual;
Greatest, Least.
Select Ename, Mgr, Comm, Nullif (Comm, 0) Test1 from Scott.emp Where Empno in (7844, 7839, 7654, 736); (9i) If x1 = x2, return NULL. Otherwise x1.
SELECT SYS_CONTEXT ('useerenv', 'ip_address') from dual; (9i added)
The Userenv function is deactivated in 9i, and it is recommended to use the SYS_CONTEXT function.
SELECT VSIZE (User), User from Dual
Chapter 4 Group Summary Statement Group By Statement Select Cust_State_Province, Count (*) Customer_Count from Sh.Customers Group By Cust_State_Province; Heart: Restrictions in Select Fields in the Group By Statement: You can only be a group field and a collection function. The result set of Group By.
Select Deptno, Min (SAL), Max (SAL) from Emp where Job = 'Clerk'Group By Deptno; (Note This is different from SQL Server!)
SELECT cust_state_province, count (*) customer_count FROM sh.customers GROUP BY cust_state_province ORDER BY COUNT (*) DESC; or: SELECT cust_state_province, count (*) customer_count FROM sh.customers GROUP BY cust_state_province ORDER BY 2 DESC;
How many rows of data in a data block in the table: SELECT AVG (Row_count), Max (Row_Count), Min (Row_count) from (SELECT Count (*) Row_count from Employees Group by Substr (RowID, 1, 15)) ;
Havingselect t.fiscal_month_desc, s.channel_id, sum (s.quantity_sold), SUM (S.Amount_sold) from Sh.Times T, Sh.Sales s where t.time_id = s.time_id and s.promo_id <> 9999Group By t. Fiscal_Month_Desc, S.Channel_id Having Sum (S.Amount_soLD)> 2000000; Having = Where.
Error: SELECT T.FISCAL_MONTH_DESC, S.CHANNEL_ID, SUM (S.Quantity_Sold), SUM (S.Amount_Sold) from Sh.Times T, Sh.Sales s where t.time_id = s.time_id and s.promo_id <> 9999 and Sum (S.Amount_soLD)> 2000000 Group by t.fiscal_month_desc, schannel_id;
CUBE and ROLLUPSELECT cust_gender gender, NVL (cust_marital_status, 'unknown') marital_status, COUNT (*) FROM sh.customers GROUP BY cust_gender, NVL (cust_marital_status, 'unknown'); SELECT cust_gender gender, NVL (cust_marital_status, 'unknown') marital_status , Count (*) from sh.customers group by Cust_Gender, ROLLUP (NVL (Cust_Marital_status, 'UNKNOWN');
SELECT CUST_GENDER GENDER, NVL (Cust_Marital_status, 'unknown') Marital_status, count (*) from sh.customers group by rollup (Cust_Gender, NVL (Cust_Marital_status, 'UNKNOWN');
SELECT CUST_GENDER GENDER, NVL (Cust_marital_status, 'unknown') Marital_status, count (*) from sh.customers group by cube (Cust_Gender, NVL (Cust_Marital_status, 'UNKNOWN');
Nested Functions Select Deptno, Greatest (Count (Distinct Job), Count (Distinct Mgr)) CNT, Count (Distinct Job) Jobs, Count (Distinct Mgr) mgrsfrom Emp Group by Deptno;
SELECT MAX (Count (Distinct Job) from Emp Group by DePtno;
Collection function Select Empno, Sal from scott.emp where deptno = 20 ORDER BY SAL
Distinct and ALL SELECT AVG (SAL) AVG, AVG (ALL SAL) AVG_ALL, AVG (Distinct Sal Avg_Dist, Count (SAL) CNT (Distinct Sal) CNT_Dist, SUM (SAL) SUM_ALL, SUM (Distinct Sal) Sum_dist from scott.emp where deptno = 20;
The default is all.
SELECT JOB_ID, AVG (SALARY) from hr.employees where job_id like 'ac%' group by job_id;
Select count (*) EMP_COUNT, Count (Distinct Department_id) DEPT_COUNT, Count (all department_id) not_null_dept_count from hr.employees;
SELECT cust_gender gender, NVL (cust_marital_status, 'unknown') marital_status, COUNT (*) emp_count, GROUPING (cust_gender) gender_superagg, GROUPING (NVL (cust_marital_status, 'unknown')) martial_superaggFROM sh.customers GROUP BY CUBE (cust_gender, NVL (cust_marital_status , 'unknown'));
Create table tmp as SELECT cust_gender gender, NVL (cust_marital_status, 'unknown') marital_status, COUNT (*) emp_count, GROUPING (cust_gender) gender_superagg, GROUPING (NVL (cust_marital_status, 'unknown')) martial_superaggFROM sh.customers GROUP BY CUBE (cust_gender NVL (Cust_Marital_status, 'UNKNOWN')); Chapter 5, the join statement Oracle 9i adds the JOIN clause to comply with the ANSI SQL 1999 standard.
Peer-oriented (inner linked) Oracle Syntax: SELECT LOCATIONS.LOCATION_ID, City, Department_name from Locations, DepartmentSwhere Locations.Location_ID = Departmentments.Location_ID;
SELECT LOCATIONS.LOCATION_ID, City, Department_name from location_id = department_id! = 'US';
Use the alias Select L.Location_ID, City, Department_Name from Locations L, Departments D where l.Location_id = d.location_id and country_id! = 'US';
When using the aliasing alias, you can only use the alias to limit the table name. Error: select local, department, divartment_name from local, departments d Where local, iv;
ANSI Syntax: Natural Joinselect Location_ID, City, Department_name from Locations Natural Join Department; Automatically uses the same name fields of the two tables. Do not use the alias of the table name or table to limit the column name.
Select * from region Natural Join Countries;
SELECT region_name, country_name, city FROM regions NATURAL JOIN countries NATURAL JOIN locations; equivalent to: SELECT region_name, country_name, city FROM regions, countries, locationsWHERE regions.region_id = countries.region_id AND countries.country_id = locations.country_id;
Join ... USING When the common field data type of two tables is not. Select location_id, city, divartment_name from location, join department;
SELECT Region_name, country_name, city from region join countries Using (region_id) Join Locations Using (Country_ID);
SELECT region_name, country_name, city FROM regions JOIN countries USING (region_id) JOIN locations USING (country_id) WHERE country_id = 'US' ORDER BY 1; JOIN ONSELECT region_name, country_name, city FROM regions rJOIN countries c ON r.region_id = c.region_id JOIN LOCATIONS L on c.country_id = l.country_idwhere country_id = 'US'
Select first_name, divartment_name, cityfrom Employees e join departments d on (e.Department_id = D.DEPARTMENT_ID) JOIN LOCATIONS L on (D.Location_ID = L.Location_ID);
Cartesian product (cross-coupled) Oracle syntax: SELECT region_name, country_name FROM regions, countries WHERE countries.country_id LIKE 'I%'; ANSI syntax: SELECT region_name, country_name FROM regions CROSS JOIN countries WHERE countries.country_id LIKE 'I%';
Out-of-line (non-equivalent coupled) Oracle grammar: select c.country_name, l.city from countries C, Locations l where c.country_id = l.country_id ( );
SELECT ENAME, EMP.DEPTNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO ( ) = dept.deptno ( );
Outline contacts ( ) are used for all conditions. Select c.country_name, l.city from countries C, Locations L where c.country_id = l.country_id ( ) and l.city like 'b%'
Select c.country_name, l.city from countries C, Locations L where c.country_id = l.country_id ( ) and l.city ( ) Like 'b%';
Out-in-line contacts ( ) cannot be used for OR and IN. Error: select c.country_name, l.city from countries C, Locations l where c.country_id = l.country_id ( ) or l.city ( ) Like 'b%';
Correct: select c.country_name, l.city from countries C, Locations l where c.country_id = l.country_id ( ) and c.country_name in ('India', 'Israel');
ANSI Syntax: Left Search Select C.Country_Name, L.City from Country C Left Outer Join Locations L on C.COUNTRY_ID = L.COUNTRY_ID;
Select ename, Emp.Deptno, DName from Emp Left Outer Join Dept on Emp.deptno = Dept.deptno; Outer is optional. SELECT country_name, city FROM countries LEFT JOIN locations USING (country_id); SELECT country_name, city FROM countries NATURAL LEFT JOIN locations; right outer coupling SELECT country_name, city FROM locations NATURAL RIGHT OUTER JOIN countries;
Select c.country_name, l.city from location, l, l.city, = l.country_id;
Full outline (9i new) Select E.employeE_ID, E.last_name, D.Department_ID, D.DEPARTMENT_NAMEFROM EMPLOYEES E FULL OUIN Department Join Departments D On (E.Department_ID = D.DEPARTMENT_ID)
Error: select ename, Emp.deptno, DName from Emp, Dept where Emp.Deptno ( ) = dept.deptno ( );
Self-contained for a hierarchical database. Select E.last_name Employee, M.last_name Manager from Employees E, Employees Mwhere M.employee_Id = E.Manager_ID;
SELECT E.last_name Employee, M.last_name Manager from Employees E Inner Join Employees M on M.employeE_Id = E.Manager_ID;
Chapter VI Collective Statement and Subquoix
Collection statement: Set the result of multiple SELECT statements into a result set. UNION ALL combines multiple result sets without eliminating duplicate values. Union combines multiple result sets to eliminate repetition values. INTERSECTMINUS
SELECT LAST_NAME, HIRE_DATE FROM Employees Where Department_ID = 90; SELECT LAST_NAME, HIRE_DATE FROM Employees Where Last_name Like 'K%';
SELECT LAST_NAME, HIRE_DATE FROM Employees Where Department_ID = 90UNION AllSelect Last_name, Hire_Date from Employees Where Last_name Like 'K%';
SELECT LAST_NAME, HIRE_DATE FROM EMPLOYEES Where Department_ID = 90UNIONSELECT LAST_NAME, HIRE_DATE FROM EMPLOYEES Where Last_name Like 'K%';
SELECT LAST_NAME, HIRE_DATE FROM EMPLOYEES Where Department_ID = 90INTERSECTSELECT LAST_NAME, HIRE_DATE AROYEES where last_name Like 'K%';
SELECT last_name, hire_date FROM employees WHERE department_id = 90MINUSSELECT last_name, hire_date FROM employees WHERE last_name LIKE 'K%'; Error: SELECT last_name, hire_date FROM employees WHERE department_id = 90 ORDER BY last_nameMINUSSELECT first_name, hire_date FROM employees WHERE last_name LIKE 'K%' ORDER by first_name;
SELECT LAST_NAME, HIRE_DATE "JOIN DATE" from Employees Where Department_ID = 90 minusselect first_name, hire_date from Employees Where Last_name Like 'K%' Order by Last_name, "Join Date";
SELECT LAST_NAME, HIRE_DATE "JOIN DATE" from Employees Where Department_ID = 90 minusselect first_name, hire_date from Employees Where Last_name Like 'K%' Order BY 1, 2;
Subrid subquery characteristics: subquery should be parentheses. Divided into nested subqueries and related subsequins. (Difference: Whether the subquery contains the join.) Divided into single values and multiple values. Related subquers can be replaced by connector.
Example of single-value nested subqueries: select lastname, firstname, Salary from Employeeswhere Salry = (SELECT MAX (SALARY) FROM EMPLOYEES;
Select Lastname, Firstname, Salary from Employeeswhere Department_ID = (Select Department_id from Departments Where Department_name = 'Accounting');
Example of multi-value nested subqueries: select lastname, firstname, Salary from Employeeswhere Department_ID = (Select Department_id from Departments where first_name = 'john');
Examples of related subsections: Select Department_ID, Last_name, Salary from Employees E1 WHERE SALY = (SALECT MAX (SALARY) from Employees E2 WHERE E1.DEPARTMENT_ID = E2.Department_ID);
Comparison: Select Department_ID, Last_Name, Salary from Employees Where Salry = (SELECT MAX (SALARY) FROM EMPLOYEES
SELECT last_name, first_name, department_id FROM employees e1 WHERE EXISTS (SELECT 'x' FROM employees e2 WHERE first_name = 'John' AND e1.department_id = e2.department_id); subqueries used in case expression: SELECT city, country_id, ( Case when country_id in (SELECT Country_id from counted where country_name = 'india') Then 'indian'lse' Non_indian'end "India?" From location s the city like '' ";
Subproof use in the SELECT clause: SELECT LAST_NAME, Department_ID, (SELECT MAX (SALARY) from Employees Sq Where Sq.DDEPARTMENT_ID = E.DEPARTMENT_ID) HSAL from Employees E WHERE LAST_NAME LIKE 'R%';
Update Employees E1SET SALY = (Select Max (Salary) from Employees E2 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID);
Delete from Employees E WHERE SALY <(SALARY) from Employees Where Department_ID = E.DEPARTMENT_ID);
INSERT INTO Employee_archive Select * from Employees;
INSERT INTO Departments (Department_ID, Department_Name) VALUES ((Select Max (Department_ID) 10 from Departments, 'EDP');
You can use a subquery in Insert, Update, and DELETE statements instead of the table name. Delete from (SELECT * from Departments Where Department_ID <20) Where department_id = 10;
Delete from (select * from department where divartment_id <20) Where department_id = 10;
INSERT INTO (SELECT Department_ID, Department_Name from Department Where Department_ID <20) VALUES (35, 'Marketing');
With read only, with check option.
INSERT INTO (SELECT Department_ID, Department_name from Departments Where Department_ID <20 with check option) VALUES (45, 'Marketing');
Chapter 7 Modify Data (Insert, Update, DELETE Statement) Insert Statement Insert Into Checking (Account_ID, CREATE_DATE, Balance) Values ('Kiesha', Sysdate, 5000);
INSERT INTO brokerage (account_id, create_date, balance) SELECT account_id, SYSDATE, 0 FROM checking WHERE account_type = 'C'; INSERT INTO e_checking SELECT * FROM checking WHERE account_type = 'C';
UPDATE statement UPDATE order_rollup SET (qty, price) = (SELECT SUM (qty), SUM (price) FROM order_lines WHERE customer_id = 'KHOL') WHERE customer_id = 'KHOL' AND order_period = TO_DATE ('01 -Oct-2001 ') ;
Update Order_rollup Set Phone = '123456', Fax = '234567' Where Customer_ID = 'Khol'
DELETE statement delete from customer_ '; delete from order_staging; dmltruncate statement truncate table order_staging; truncate statement is a DDL statement.
SELECT ... for Update statement
Transaction statement commit, commit work. ROLLBACK. Savepoint.
DDL and statements do not need to be submitted.
RENAME statement RENAME T1 to T2;
Create Table ... AS SELECT ... statement create table EMP_TMP AS SELECT * from Emp where deptno = 10; Copy Table: Create Table T2 As SELECT * from T1; Copy Table Structure: Create Table T2 As SELECT * from T1 Where 1 = 2;
Top n Statement Select Last_name, Salary from Employees Where Rownum <= 5 Order by Salary DESC;
