130189456 oracle sql basic
Post on 03-Jun-2018
235 Views
Preview:
TRANSCRIPT
-
8/11/2019 130189456 Oracle SQL Basic
1/148
-
8/11/2019 130189456 Oracle SQL Basic
2/148
Database Prehistory
ORACLE SQL FUNDAMENTALS 2
ata entryStorage and retrieval
Query processingSorting
-
8/11/2019 130189456 Oracle SQL Basic
3/148
What is Database?
A database (DB) is a more or less well-organized collection of related data.
ORACLE SQL FUNDAMENTALS 3
-
8/11/2019 130189456 Oracle SQL Basic
4/148
Database management system
A database management system (DBMS) is one (orseveral) program that provides functionality for usersto develop, use, and maintain a database.
Thus, a DBMS is a general software system for defining, populating (creating), manipulating and sharingdatabases for different types of applications.For example Oracle, MS SQL Server, MySQL, Sysbase.
ORACLE SQL FUNDAMENTALS 4
-
8/11/2019 130189456 Oracle SQL Basic
5/148
What does a database system do?Manages Very Large Amounts of Data
Supports efficient access to Very Large Amounts ofDataSupports concurrent access to Very Large Amounts ofData
Supports secure , atomic access to Very Large Amounts of Data
ORACLE SQL FUNDAMENTALS 5
-
8/11/2019 130189456 Oracle SQL Basic
6/148
Database system architectureIt is common to describe databases in two ways
The logical level :
What users see, the program or query language interface, The physical level :How files are organised, what indexing mechanisms areused,
ORACLE SQL FUNDAMENTALS 6
-
8/11/2019 130189456 Oracle SQL Basic
7/148
The relational model: Basics A relational database is a collection of relations
A relation consists of two parts:Relation instance : a table, with columns and rowsRelation schema : Specifies the name of the relation,plus the name and type of each column
Can think of a relation instance as a set of rows ortuples
ORACLE SQL FUNDAMENTALS 7
-
8/11/2019 130189456 Oracle SQL Basic
8/148
Relational Schema A relational schema specifies:
name of the relation: Studentsnames of fields : (sid, name, login, age, and gpa)domain of each field: it is type of possible values (someof built-in types in SQL are integer, real, string, and date.)
A field can also be called an attribute or a column .
ORACLE SQL FUNDAMENTALS 8
sid name login age gpa53666 Jones jones@cs 18 3.453688 Smith smith@eecs 18 3.253650 Smith smith@math 19 3.8
Students
-
8/11/2019 130189456 Oracle SQL Basic
9/148
Relational Instance A relation instance contains a set of tuples A relation instance is referred to as a relation . A tuple can also be called a record or a row . A relation instance is a set, and it has no duplicatetuples .Order of tuples is not important.
ORACLE SQL FUNDAMENTALS 9
sid name login age gpa
53666 Jones jones@cs 18 3.453688 Smith smith@eecs 18 3.253650 Smith smith@math 19 3.8
-
8/11/2019 130189456 Oracle SQL Basic
10/148
Example database
ORACLE SQL FUNDAMENTALS 10
-
8/11/2019 130189456 Oracle SQL Basic
11/148
Tables as Themes
ORACLE SQL FUNDAMENTALS 11
Every table has a theme-- e.g. Employees
Every row represents an instance of that theme --e.g. a single Employee
A row represents asingle Employee
empno ename sal commEmployees
7499 ALLEN 1600 300
7654 MARTIN 1250 1400
7698 BLAKE 2850
7839 KING 5000
7844 TURNER 1500 0
7986 STERN 1500
-
8/11/2019 130189456 Oracle SQL Basic
12/148
Columns as Attributes
2008 InnoMind ORACLE SQL FUNDAMENTALS 12
Every column represents an attribute related tothe theme -- e.g. the name or salary of anEmployee
empno ename sal commEmployees
Primary Key isunderlined
Uniquelyidentifies an
employee
7499 ALLEN 1600 300
7654 MARTIN 1250 1400
7698 BLAKE 2850
7839 KING 5000
7844 TURNER 1500 0
7986 STERN 1500
-
8/11/2019 130189456 Oracle SQL Basic
13/148
Rows as Objects/Entities
ORACLE SQL FUNDAMENTALS 13
empno: 7654ename : MARTINsal : 1250comm : 1400
an Employee Object
It can be useful to think of each row as an objector entity and the table as a collection of these
entities.The columns of the table correspond to theinstance variables for each object
empno ename sal comm
Employees
7499 ALLEN 1600 300
7654 MARTIN 1250 1400
7698 BLAKE 2850
7839 KING 5000
7844 TURNER 1500 0
7986 STERN 1500
-
8/11/2019 130189456 Oracle SQL Basic
14/148
Entity Classes
In conceptual modeling, we focus on the entity class which represents the class of entities with thesame theme.
In general (but as we will see, not always), anentity class is implemented by a table in arelational database
ORACLE SQL FUNDAMENTALS 14
-
8/11/2019 130189456 Oracle SQL Basic
15/148
Relationships
and ER Diagrams
ORACLE SQL FUNDAMENTALS 15
( l h )
-
8/11/2019 130189456 Oracle SQL Basic
16/148
ER (Entity-Relationship) Diagrams(Crow Magnum style)
The Crow's foot at Employee means A Dept can have MANY EmployeesNo Crow's foot at Dept, so An Employee works for no more than ONE Dept
ORACLE SQL FUNDAMENTALS 16
Dept
works for
Crows Foot
relationshipcharacterization
empnoenamesalcomm
deptnodname
Employee
Depicts a relationship between Employees and
Depts
-
8/11/2019 130189456 Oracle SQL Basic
17/148
ER & Instance Diagrams
ORACLE SQL FUNDAMENTALS 17
DeptEmployeeworks for
7698 BLAKE 2850
7499 ALLEN 1600 300
7654 MARTIN 1250 1400
7986 STERN 1500
7844 TURNER 1500 0
10 SALES
30 ACCOUNTING
*ER Diagram
Entity Class Entity ClassRelationship
Corresponds to links between instances of the related classes
Instance Diagram Shows example instances and the links between them
Entity Instances
Entity Instances
Links
-
8/11/2019 130189456 Oracle SQL Basic
18/148
SQL HistoryIt is a query language for relational databases.Developed by IBM (system R) in the 1970sTwo standard organizations
ANSI (American National Standard Institutes)ISO (International Organization for Standardization)
Standards:SQL-86, SQL-89, SQL-92, SQL-99 (current standard)
ORACLE SQL FUNDAMENTALS 18
-
8/11/2019 130189456 Oracle SQL Basic
19/148
a a ypes
ORACLE SQL FUNDAMENTALS 19
-
8/11/2019 130189456 Oracle SQL Basic
20/148
SQL Statements
ORACLE SQL FUNDAMENTALS 20
SELECT Data retrieval
INSERTUPDATEDELETEMERGE
Data manipulation language(DML)
CREATEALTERDROP
TRUNCATERENAME
Data definition language(DDL)
COMMITROLLBACK
SAVEPOINT
Transaction control language(TCL)
GRANT
REVOKEData control language (DCL)
-
8/11/2019 130189456 Oracle SQL Basic
21/148
Data Definition Language (DDL)Data Definition is used to create database objects.The statements are
1. CREATE2. ALTER 3. DROP4. TRUNCATE
ORACLE SQL FUNDAMENTALS 21
-
8/11/2019 130189456 Oracle SQL Basic
22/148
Creating Tables
Naming RulesTable names and column names: Must begin with a letter
Must be 1 30 characters long Must contain only A Z, az, 09, _, $, and # Must not duplicate the name of another object owned
by the same user Must not be an Oracle server reserved word
ORACLE SQL FUNDAMENTALS 22
-
8/11/2019 130189456 Oracle SQL Basic
23/148
Confirm table creation.
DESCRIBE dept Name Null? Type
------------- --------- ---------
DEPTNO NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)
Creating Tables
ORACLE SQL FUNDAMENTALS 23
Create the table
CREATE TABLE dept(deptno NUMBER(2),dname VARCHAR2(14),
loc VARCHAR2(13));
-
8/11/2019 130189456 Oracle SQL Basic
24/148
Creating a Table by Using a
Subquery
ORACLE SQL FUNDAMENTALS 24
-
8/11/2019 130189456 Oracle SQL Basic
25/148
The ALTER TABLE StatementUse the ALTER TABLE statement to:
Add a new column Modify an existing column Define a default value for the new column Drop a column
ORACLE SQL FUNDAMENTALS 25
-
8/11/2019 130189456 Oracle SQL Basic
26/148
The ALTER TABLE Statement
ORACLE SQL FUNDAMENTALS 26
-
8/11/2019 130189456 Oracle SQL Basic
27/148
The ALTER TABLE StatementSQL> ALTER TABLE EMP ADD REMARKS CHAR(80);Table altered.
Column NameTable Name
Guidelines for Adding a Column
You can add columns. You cannot specify where the column is to appear.
ORACLE SQL FUNDAMENTALS 27
-
8/11/2019 130189456 Oracle SQL Basic
28/148
The ALTER TABLE StatementSQL> ALTER TABLE EMP MODIFY ENAME CHAR(40);Table altered.
Guidelines for modifying a Column You can increase the width or precision of a numeric column. You can increase the width of numeric or character columns. You can decrease the width of a column only if the column contains onlynull values or if the table has no rows. You can change the data type only if the column contains null values.
ORACLE SQL FUNDAMENTALS 28
-
8/11/2019 130189456 Oracle SQL Basic
29/148
The ALTER TABLE StatementTo Rename the column using The Alter Table Statement.
SQL> ALTER TABLE EMP RENAME COLUMNENAME TO ENAME1;Table altered.
ORACLE SQL FUNDAMENTALS 29
-
8/11/2019 130189456 Oracle SQL Basic
30/148
The ALTER TABLE StatementSQL> ALTER TABLE EMP DROP COLUMNREMARKS;Table altered.
Guidelines to dropping a column
The column may or may not contain data.Using the ALTER TABLE statement, only one column can be dropped at a
time.The table must have at least one column remaining in it after it is altered.
Once a column is dropped, it cannot be recovered .
ORACLE SQL FUNDAMENTALS 30
-
8/11/2019 130189456 Oracle SQL Basic
31/148
The SET UNUSED OptionThe SET UNUSED option marks one or more columns asunused so that they can be dropped when the demand onsystem resources is lower
SQL> ALTER TABLE BILLS SET UNUSED COLUMN ACCOUNT_ID;Table altered.
Guidelines to SET UNUSED option
Unused columns are treated as if they were dropped,even though their column data remains in the tablesrows.
ORACLE SQL FUNDAMENTALS 31
-
8/11/2019 130189456 Oracle SQL Basic
32/148
The DROP UNUSED COLUMNS
OptionDROP UNUSED COLUMNS removes from the table all columnscurrently marked as unused
SQL> ALTER TABLE EMP DROP UNUSED COLUMNS;Table altered.
ORACLE SQL FUNDAMENTALS 32
-
8/11/2019 130189456 Oracle SQL Basic
33/148
The DROP TABLE StatementThe DROP TABLE statement removes the definition ofan Oracle table When you drop a table, the database loses all the datain the tableThe DROP TABLE statement, once executed, isirreversible
SQL> DROP TABLE NEW_BILLS;Table dropped.
ORACLE SQL FUNDAMENTALS 33
-
8/11/2019 130189456 Oracle SQL Basic
34/148
The RENAME StatementThe RENAME statement, which is used to renamedatabase objects.
RENAME old_name TO new_name;
SQL>RENAMEdept TO detail_dept;
Table renamed.
ORACLE SQL FUNDAMENTALS 34
-
8/11/2019 130189456 Oracle SQL Basic
35/148
The TRUNCATE TABLE statementThe TRUNCATE TABLE statement, which is used toremove all rows from a table You cannot roll back row removal
SQL>TRUNCATE TABLE detail_dept;Table truncated.
ORACLE SQL FUNDAMENTALS 35
-
8/11/2019 130189456 Oracle SQL Basic
36/148
Data manipulation language (DML) A DML statement is executed when you:
Add new rows to a table Modify existing rows in a table
Remove existing rows from a table A transaction consists of a collection of DML
statements that form a logical unit of work.
ORACLE SQL FUNDAMENTALS 36
-
8/11/2019 130189456 Oracle SQL Basic
37/148
The SELECT Statements A SELECT statement retrieves information from the database To Select all Columns of all rows
To display the information of all employeesSELECT * FROM emp;To Selecting Specific Columns of All Rows To list employee names and their departmentsSELECT ename, deptno FROM emp;To Eliminate duplicate rows To get the unique values of department numbers in the emp
tableSELECT DISTINCT deptno FROM emp;
ORACLE SQL FUNDAMENTALS 37
-
8/11/2019 130189456 Oracle SQL Basic
38/148
Defining a Column Alias A column alias: Renames a column heading
Is useful with calculations Immediately follows the column name - therecan also be the optional AS keyword between thecolumn name and alias
SQL>SELECT ename AS employee, comm commissionFROM emp;
ORACLE SQL FUNDAMENTALS 38
-
8/11/2019 130189456 Oracle SQL Basic
39/148
Restricting and Sorting DataLimiting the Rows Selected
Restrict the rows returned by using the WHEREclause. The WHERE clause follows the FROM clause.
SQL> SELECT employee_id, last_name, job_id, department_idFROM employees WHERE department_id = 90 ;
ORACLE SQL FUNDAMENTALS 39
-
8/11/2019 130189456 Oracle SQL Basic
40/148
ORDER BY Clause Sort rows with the ORDER BY clause
ASC: ascending order, default DESC: descending order The ORDER BY clause comes last in the SELECT
statement.
SQL>SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ;
ORACLE SQL FUNDAMENTALS 40
-
8/11/2019 130189456 Oracle SQL Basic
41/148
The INSERT Statement Add new rows to a table by using the INSERT statement. Only one row is inserted at a time with this syntax. Insert a new row containing values for each column. List values in the default order of the columns in thetable. Optionally, list the columns in the INSERT clause. Enclose character and date values within single quotationmarks.
SQL>INSERT INTO departments(department_id, department_name,manager_id, location_id) VALUES (70, 'Public Relations', 100,1700);
1 row created.
ORACLE SQL FUNDAMENTALS 41
-
8/11/2019 130189456 Oracle SQL Basic
42/148
The INSERT StatementCreating a Script
Use & substitution in a SQL statement to promptfor values. & is a placeholder for the variable value.
SQL>INSERT INTO departments(department_id, department_name, location_id)
VALUES (&department_id, '&department_name', &location);
ORACLE SQL FUNDAMENTALS 42
-
8/11/2019 130189456 Oracle SQL Basic
43/148
The INSERT StatementCopying Rows from Another Table
Write your INSERT statement with a subquery. Do not use the VALUES clause. Match the number of columns in the INSERT clause to
those in the subquery.
SQL>INSERT INTO sales_reps(id, name, salary, commission_pct)SELECT employee_id, last_name, salary, commission_pctFROM employees WHERE job_id = 2;
1 row created.
ORACLE SQL FUNDAMENTALS 43
-
8/11/2019 130189456 Oracle SQL Basic
44/148
The UPDATE Statement
Modify existing rows with the UPDATE statement. Update more than one row at a time, if required.
Specific row or rows are modified if you specify the WHERE clause. All rows in the table are modified if you omit the WHERE clause.
SQL>UPDATE employees SET department_id = 70 WHERE employee_id = 113;
1 row updated .
ORACLE SQL FUNDAMENTALS 44
-
8/11/2019 130189456 Oracle SQL Basic
45/148
The UPDATE StatementUpdating Two Columns with a SubquerySQL> UPDATE employees
SET 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.
ORACLE SQL FUNDAMENTALS 45
-
8/11/2019 130189456 Oracle SQL Basic
46/148
The DELETE Statement You can remove existing rows from a table by usingthe DELETE statement.Specific rows are deleted if you specify the WHEREclause.
SQL>DELETE FROM departments WHERE department_name = 'Finance';
1 row delete d .
All rows in the table are deleted if you omit the WHERE clause.
SQL>DELETE FROM copy_emp;22 rows deleted.
ORACLE SQL FUNDAMENTALS 46
-
8/11/2019 130189456 Oracle SQL Basic
47/148
Including Constraints What are Constraints?
Constraints enforce rules at the table level. Constraints prevent the deletion of a table ifthere are dependencies. The following constraint types are valid:
NOT NULL UNIQUE
PRIMARY KEY FOREIGN KEY CHECK
ORACLE SQL FUNDAMENTALS 47
-
8/11/2019 130189456 Oracle SQL Basic
48/148
Including ConstraintsConstraint Guidelines Create a constraint either:
At the same time as the table is created, or After the table has been created Define a constraint at the column or table level. View a constraint in the data dictionary.
ORACLE SQL FUNDAMENTALS 48
-
8/11/2019 130189456 Oracle SQL Basic
49/148
The NOT NULL ConstraintEnsures that null values are not permitted for the
column
SQL>CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL , salaryNUMBER(8,2),commission_pct NUMBER(2,2), hire_date DATECONSTRAINT emp_hire_date_nn NOT NULL );
Table Created.
ORACLE SQL FUNDAMENTALS 49
-
8/11/2019 130189456 Oracle SQL Basic
50/148
The UNIQUE Constraint A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique that is, no two rows of a table can have duplicate valuesin a specified column or set of columns.
SQL>CREATE TABLE employees( employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL,emailVARCHAR2(25), salary NUMBER(8,2), commission_pctNUMBER(2,2), hire_date DATE NOT NULL, CONSTRAINTemp_email_uk UNIQUE(email) );
Table Created.
ORACLE SQL FUNDAMENTALS 50
-
8/11/2019 130189456 Oracle SQL Basic
51/148
The PRIMARY KEY Constraint A PRIMARY KEY constraint creates a primary key for the table.Only one primary key can be created for each table.The PRIMARY KEY constraint is a column or set of columns that uniquely identifieseach row in a table.
This constraint enforces uniqueness of the column or column combination andensures that no column that is part of the primary key can contain a null value.
SQL>CREATE TABLE departments(department_id NUMBER(4),department_name VARCHAR2(30)CONSTRAINT dept_name_nn NOTNULL, manager_id NUMBER(6), location_id NUMBER(4),CONSTRAINT dept_id_pk PRIMARY KEY(department_id) );
Table Created.
ORACLE SQL FUNDAMENTALS 51
-
8/11/2019 130189456 Oracle SQL Basic
52/148
The FOREIGN KEY ConstraintThe FOREIGN KEY, or referential integrity constraint,designates a column or combination of columns as aforeign key and establishes a relationship between aprimary key or a unique key in the same table or a
different table. A foreign key value must match an existing value in theparent table or be NULL.
SQL>CREATE TABLE employees(employee_id NUMBER(6),last_nameVARCHAR2(25) NOT NULL,email VARCHAR2(25),salaryNUMBER(8,2),comdepartment_idmission_pct NUMBER(2,2),hire_dateDATE NOT NULL, NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY(department_id)REFERENCES departments(department_id),
ORACLE SQL FUNDAMENTALS 52
-
8/11/2019 130189456 Oracle SQL Basic
53/148
The CHECK ConstraintDefines a condition that each row must satisfySQL>CREATE TABLE employees(employee_id NUMBER(6),last_name
VARCHAR2(25) NOT NULL,email VARCHAR2(25),salaryNUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary >0) );
SQL> ALTER TABLE EMPLOYEES ADD CONSTRAINT CK
CHECK(SALARY>0);
ORACLE SQL FUNDAMENTALS 53
-
8/11/2019 130189456 Oracle SQL Basic
54/148
Adding a Constraint SyntaxUse the ALTER TABLE statement to:
Add or drop a constraint, but not modify its structure Enable or disable constraints Add a NOT NULL constraint by using the MODIFY clause
SQL>ALTER TABLE employees ADD CONSTRAINT emp_manager_fkFOREIGN KEY(manager_id) REFERENCES employees(employee_id);
Table altered.
SQL> alter table st modify sno number not null ; Table altered.
SQL> alter table st modify sno number unique ;Table altered.
SQL> alter table abcd modify ename constraint nn not null ;Table altered.
SQL> alter table abcd modify ename constraint uq unique ;Table altered.
ORACLE SQL FUNDAMENTALS 54
-
8/11/2019 130189456 Oracle SQL Basic
55/148
To View Constraints name in Data
DictionarySQL> select
constraint_name,Constraint_type,search_condition fromuser_constraints where table_name= EMP;
ORACLE SQL FUNDAMENTALS 55
-
8/11/2019 130189456 Oracle SQL Basic
56/148
Dropping a ConstraintRemove the manager constraint from the EMPLOYEEStable.The constraint name in the drop clause is case sensitive.
SQL> ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;Table altered.
Remove the PRIMARY KEY constraint on theDEPARTMENTS table and drop the associated FOREIGN
KEY constraint on the EMPLOYEES.DEPARTMENT_IDcolumn.
SQL> ALTER TABLE departments DROP PRIMARY KEY CASCADE;Table altered.
ORACLE SQL FUNDAMENTALS 56
-
8/11/2019 130189456 Oracle SQL Basic
57/148
OperatorsOperators are the elements you use inside an expressionto articulate how you want specified conditions toretrieve data.
Operators fall into six groups:1. Arithmetic Operator2. Comparison Operator3. Character Operator4. Logical Operator5. Set Operator6. Miscellaneous Operator
ORACLE SQL FUNDAMENTALS 57
-
8/11/2019 130189456 Oracle SQL Basic
58/148
Arithmetic Operator
ORACLE SQL FUNDAMENTALS 58
The arithmetic operators are Plus (+) Minus (-) Divide (/) Multiply (*) Modulo (%)
The first four are self-explanatory. Modulo returns the integer remainder ofa division.
-
8/11/2019 130189456 Oracle SQL Basic
59/148
Comparison Operator
For Example... WHERE hire_date ='01-JAN-95'... WHERE salary >=6000... WHERE last_name ='Smith'
ORACLE SQL FUNDAMENTALS 59
-
8/11/2019 130189456 Oracle SQL Basic
60/148
-
8/11/2019 130189456 Oracle SQL Basic
61/148
Using the BETWEEN ConditionUse the BETWEEN condition to display rows based on a range of values.The range that you specify contains a lower limit and an upper limit.
You must specify the lower limit first .
SQL>SELECT last_name, salary FROM employeesWHERE salary BETWEEN 2500 AND 3500 ;
Lower limit Upper limit
ORACLE SQL FUNDAMENTALS 61
-
8/11/2019 130189456 Oracle SQL Basic
62/148
Using the IN ConditionTo test for values in a specified set of values, use the INcondition.The IN condition can be used with any data type.
SQL>SELECT employee_id, last_name, salary, manager_idFROM employeesWHERE manager_id IN (100, 101, 201) ;
ORACLE SQL FUNDAMENTALS 62
-
8/11/2019 130189456 Oracle SQL Basic
63/148
Using the LIKE Condition Use the LIKE condition to perform wildcardsearches of valid search string values.
Search conditions can contain either literalcharacters or numbers: % denotes zero or many characters. _ denotes one character.
SQL> SELECT first_name FROM employeesWHERE first_name LIKE 'S%' ;
ORACLE SQL FUNDAMENTALS 63
-
8/11/2019 130189456 Oracle SQL Basic
64/148
The ESCAPE Option When you need to have an exact match for the actual %and _ characters, use the ESCAPE option.This option specifies what the escape character is. If you want to search for strings that contain SA_, you can usethe following SQL statement:
SQL>SELECT employee_id, last_name, job_idFROM employeesWHERE job_id LIKE '%SA\_%' ESCAPE '\' ;
ORACLE SQL FUNDAMENTALS 64
-
8/11/2019 130189456 Oracle SQL Basic
65/148
-
8/11/2019 130189456 Oracle SQL Basic
66/148
Using Concatenation (||)The || (double pipe) symbol concatenates two strings
SQL> SELECT FIRSTNAME || LASTNAME ENTIRENAMEFROM FRIENDS;
ORACLE SQL FUNDAMENTALS 66
-
8/11/2019 130189456 Oracle SQL Basic
67/148
Logical Operators
ORACLE SQL FUNDAMENTALS 67
-
8/11/2019 130189456 Oracle SQL Basic
68/148
-
8/11/2019 130189456 Oracle SQL Basic
69/148
Using the OR OperatorOR requires either condition can be true for any recordto be selected.
SQL>SELECT employee_id, last_name, job_id, salaryFROM employees
WHERE salary >= 10000 OR job_id LIKE '%MAN%' ;
ORACLE SQL FUNDAMENTALS 69
-
8/11/2019 130189456 Oracle SQL Basic
70/148
Using the NOT OperatorThe NOT operator can also be used with other SQLoperators, such as BETWEEN, LIKE, and NULL.
... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP')
... WHERE salary NOT BETWEEN 10000 AND 15000
... WHERE last_name NOT LIKE '%A%'
... WHERE commission_pct IS NOT NULL
SQL>SELECT last_name, job_idFROM employeesWHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
2008 InnoMind ORACLE SQL FUNDAMENTALS 70
-
8/11/2019 130189456 Oracle SQL Basic
71/148
Rules of Precedence
ORACLE SQL FUNDAMENTALS 71
-
8/11/2019 130189456 Oracle SQL Basic
72/148
-
8/11/2019 130189456 Oracle SQL Basic
73/148
The UNION SET OperatorThe UNION operator returns all rows selected by either query. Use theUNION operator to return all rows from multiple tables and eliminate anyduplicate rows .
Guidelines The number of columns and the data types of the columns being selected must be
identical in all the SELECT statements used in the query. The names of the columnsneed not be identical.
UNION operates over all of the columns being selected. NULL values are not ignored during duplicate checking. The IN operator has a higher precedence than the UNION operator. By default, the output is sorted in ascending order of the first column of the SELECT
clause.
ORACLE SQL FUNDAMENTALS 73
-
8/11/2019 130189456 Oracle SQL Basic
74/148
-
8/11/2019 130189456 Oracle SQL Basic
75/148
The UNION ALL OperatorUse the UNION ALL operator to return all rows from multiple queries.
Guidelines Unlike UNION, duplicate rows are not eliminated and the output is
not sorted by default. The DISTINCT keyword cannot be used.
SQL>SELECT employee_id, job_id, department_idFROM employeesUNION ALLSELECT employee_id, job_id, department_idFROM job_historyORDER BY employee_id;
ORACLE SQL FUNDAMENTALS 75
-
8/11/2019 130189456 Oracle SQL Basic
76/148
The INTERSECT OperatorUse the INTERSECT operator to return all rows common to
multiple queries.Guidelines
The number of columns and the data types of the columnsbeing selected by the SELECT statements in the queries mustbe identical in all the SELECT statements used in the query.The names of the columns need not be identical.
Reversing the order of the intersected tables does not alter theresult.
INTERSECT does not ignore NULL values.
ORACLE SQL FUNDAMENTALS 76
-
8/11/2019 130189456 Oracle SQL Basic
77/148
The INTERSECT OperatorDisplay the employee IDs and job IDs of employees who are currentlyin a job title that they have held once before during their tenure withthe company
SQL>SELECT employee_id, job_id, department_idFROM employeesINTERSECTSELECT employee_id, job_id, department_idFROM job_history;
ORACLE SQL FUNDAMENTALS 77
-
8/11/2019 130189456 Oracle SQL Basic
78/148
The MINUS OperatorUse the MINUS operator to return rows returned by the firstquery that are not present in the second query (the firstSELECT statement MINUS the second SELECT statement).
Guidelines The number of columns and the data types of the columns
being selected by the SELECT statements in the queriesmust be identical in all the SELECT statements used in
the query. The names of the columns need not beidentical. All of the columns in the WHERE clause must be in the
SELECT clause for the MINUS operator to work.
ORACLE SQL FUNDAMENTALS 78
-
8/11/2019 130189456 Oracle SQL Basic
79/148
The MINUS OperatorDisplay the employee IDs of those employees who havenot changed their jobs even once.
SQL>SELECT employee_idFROM employees
MINUSSELECT employee_idFROM job_history;
ORACLE SQL FUNDAMENTALS 79
-
8/11/2019 130189456 Oracle SQL Basic
80/148
SET Operator Guidelines The expressions in the select lists of the queries must match in number and datatype.
Queries that use UNION, UNION ALL, INTERSECT, and MINUS SET operators in their WHERE clause must have the same number and type of columns in their SELECT list.For example:
SQL>SELECT employee_id, department_idFROM employeesWHERE (employee_id, department_id)IN (SELECT employee_id, department_idFROM employeesUNIONSELECT employee_id, department_idFROM job_history);
The ORDER BY clause: Can appear only at the very end of the statement
Will accept the column name, an alias, or the positional notation The column name or alias, if used in an ORDER BY clause, must be from the first SELECT
list.
SET operators can be used in subqueries .
ORACLE SQL FUNDAMENTALS 80
-
8/11/2019 130189456 Oracle SQL Basic
81/148
SQL FunctionsFunctions are a very powerful feature of SQL and can beused to do the following:
Perform calculations on data Modify individual data items Manipulate output for groups of rows Format dates and numbers for display
Convert column data types SQL functions sometimes take arguments and always
return a value.
ORACLE SQL FUNDAMENTALS 81
-
8/11/2019 130189456 Oracle SQL Basic
82/148
SQL FunctionsTypes of SQL FunctionsThere are two distinct types of functions:
Single-row functions Multiple -row functions
Single-Row FunctionsThese functions operate on single rows only and return one result per row. There aredifferent types of single-row functions.
Character Number Date Conversion
Multiple-Row FunctionsFunctions can manipulate groups of rows to give one result per group of rows. Thesefunctions are known as group functions.
ORACLE SQL FUNDAMENTALS 82
-
8/11/2019 130189456 Oracle SQL Basic
83/148
Aggregate FunctionsThese functions are also referred to as group functions.They return a value based on the values in a columnUnlike single-row functions, group functions operate
on sets of rows to give one result per group.These sets may be the whole table or the table splitinto groups.
Types of Group Functions
AVG COUNT MAX MIN SUM
ORACLE SQL FUNDAMENTALS 83
U i h AVG d SUM F i
-
8/11/2019 130189456 Oracle SQL Basic
84/148
Using the AVG and SUM Functions You can use AVG and SUM for numeric data.
AVGSQL> SELECT AVG(SAL) FROM EMP;
AVG(SAL)----------2073.21429
SUMSQL> SELECT SUM(SAL) AS TOTAL FROM EMP;
TOTAL----------
29025
ORACLE SQL FUNDAMENTALS 84
-
8/11/2019 130189456 Oracle SQL Basic
85/148
Using the MIN and MAX Functions You can use MIN and MAX for any data type.
MINSQL> SELECT MIN(SAL) FROM EMP;
MIN(SAL)----------
800
MAXSQL> SELECT MAX(SAL) FROM EMP;
MAX(SAL)----------
5000
ORACLE SQL FUNDAMENTALS 85
-
8/11/2019 130189456 Oracle SQL Basic
86/148
Using the COUNT FunctionCOUNT(*) returns the number of rows in a table
COUNT
SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)----------
14
ORACLE SQL FUNDAMENTALS 86
-
8/11/2019 130189456 Oracle SQL Basic
87/148
-
8/11/2019 130189456 Oracle SQL Basic
88/148
The HAVING ClauseUse the HAVING clause to restrict groups:1. Rows are grouped.2. The group function is applied.
3. Groups matching the HAVING clause are displayed .
SELECT column, group_functionFROM table[ WHERE condition][ GROUP BY group_by_expression][ HAVING group_condition][ ORDER BY column];
ORACLE SQL FUNDAMENTALS 88
-
8/11/2019 130189456 Oracle SQL Basic
89/148
The HAVING Clause You can use the GROUP BY clause without using agroup function in the SELECT list.If you restrict rows based on the result of a groupfunction, you must have a GROUP BY clause as well asthe HAVING clause.
SQL>SELECT department_id, MAX(salary)
FROM employeesGROUP BY department_idHAVING MAX(salary)>10000;
ORACLE SQL FUNDAMENTALS 89
-
8/11/2019 130189456 Oracle SQL Basic
90/148
-
8/11/2019 130189456 Oracle SQL Basic
91/148
Date and Time FunctionsOracle Date Format
The Oracle database stores dates in an internal numeric format,representing the century, year, month, day, hours, minutes, andseconds.The default display and input format for any date is DD-MON-RR.
Valid Oracle dates are between January 1, 4712 B.C. and A.D.December 31, 9999.This data is stored internally as follows:CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
19 94 06 07 5 10 43
ORACLE SQL FUNDAMENTALS 91
-
8/11/2019 130189456 Oracle SQL Basic
92/148
-
8/11/2019 130189456 Oracle SQL Basic
93/148
Date and Time FunctionsSQL> SELECT ADD_MONTHS('15-MAY-05',5) FROM DUAL;ADD_MONTH---------15-OCT-05
SQL> SELECT MONTHS_BETWEEN('15-NOV-05','07-AUG-05') FROM DUAL;
MONTHS_BETWEEN('15-NOV-05','07-AUG-05')---------------------------------------
3.25806452
SQL> SELECT LAST_DAY('18-NOV-05') FROM DUAL;
LAST_DAY(---------
30-NOV-05
SQL> SELECT NEXT_DAY('18-NOV-05','FRIDAY') FROM DUAL;
NEXT_DAY(---------25-NOV-05
ORACLE SQL FUNDAMENTALS 93
-
8/11/2019 130189456 Oracle SQL Basic
94/148
Date and Time Functions Assume SYSDATE = '25-JUL-95':
ROUND(SYSDATE,'MONTH') 01-AUG-95
ROUND(SYSDATE ,'YEAR') 01-JAN-96
TRUNC(SYSDATE ,'MONTH') 01-JUL-95
TRUNC(SYSDATE ,'YEAR') 01-JAN-95
ORACLE SQL FUNDAMENTALS 94
-
8/11/2019 130189456 Oracle SQL Basic
95/148
The SYSDATE Function
SYSDATE is a date function that returns the currentdatabase server date and time.It is customary to select SYSDATE from a dummy tablecalled DUAL.
SQL>SELECT SYSDATE FROM DUAL;
ORACLE SQL FUNDAMENTALS 95
-
8/11/2019 130189456 Oracle SQL Basic
96/148
-
8/11/2019 130189456 Oracle SQL Basic
97/148
Arithmetic FunctionsFLOORReturns the largest integer less than or equal to the numeric
value you pass to this functionSQL>SELECT FLOOR(15.7) FROM DUAL;
Floor----------15
COSReturns the cosine of a numeric value (expressed in radians).
SQL>SELECT COS(180 * 3.14159265359/180) FROM DUAL;Cosine of 180 degrees----------------------1
COSHReturns the hyperbolic cosine of a numeric value (expressed inradians).
SQL>SELECT COSH(0) FROM DUAL;Hyperbolic cosine of 0----------------------
1 ORACLE SQL FUNDAMENTALS 97
-
8/11/2019 130189456 Oracle SQL Basic
98/148
Arithmetic FunctionsSIN
Returns the sine of a numeric value (expressed in radians).SQL>SELECT SINH(1) FROM DUAL;
Hyperbolic sine of 1--------------------1.17520119
SINHReturns the hyperbolic sine of the numeric value.SQL>SELECT SINH(1) FROM DUAL;
Hyperbolic sine of 1--------------------1.17520119
TANReturns the tangent of a numeric value (expressed in radians).
SQL>SELECT TAN(135 * 3.14159265359/180) FROM DUAL;Tangent of 135 degrees----------------------- 1
ORACLE SQL FUNDAMENTALS 98
Arithmetic Functions
-
8/11/2019 130189456 Oracle SQL Basic
99/148
Arithmetic FunctionsTANHSQL>SELECT TANH(.5) FROM DUAL;
Hyperbolic tangent of .5------------------------.462117157
EXPReturns e raised to the specified power (exponent), where e=2.71828183
SQL>SELECT EXP(4) FROM DUAL;e to the 4th power------------------54.59815
LNReturns the natural logarithm of a numeric value. For example,LN(3)returns 1.098612.
SQL>SELECT LN(95) FROM DUAL;Natural log of 95-----------------4.55387689
ORACLE SQL FUNDAMENTALS 99
Arithmetic Functions
-
8/11/2019 130189456 Oracle SQL Basic
100/148
Arithmetic FunctionsMOD
Returns the remainder of a division calculationSQL>SELECT MOD(11,4) "Modulus" FROM DUAL;Modulus----------3
POWER
Returns a value raised to the exponent you pass to the functionSQL>SELECT POWER(3,2) "Raised" FROM DUAL;
Raised----------9
SIGNReturns whether a numeric value is positive, negative, or 0
SQL>SELECT SIGN(-15) "Sign" FROM DUAL;Sign-----------1
ORACLE SQL FUNDAMENTALS 100
-
8/11/2019 130189456 Oracle SQL Basic
101/148
Arithmetic FunctionsSQRT
Returns the square root of a non-negative numeric value.
SQL>SELECT SQRT(26) "Square root" FROM DUAL;
Square root-----------5.09901951
ORACLE SQL FUNDAMENTALS 101
Character Functions
-
8/11/2019 130189456 Oracle SQL Basic
102/148
Character Functions
ORACLE SQL FUNDAMENTALS 102
-
8/11/2019 130189456 Oracle SQL Basic
103/148
Character Functions
Case Manipulation FunctionsThese functions convert case for character strings.
ORACLE SQL FUNDAMENTALS 103
-
8/11/2019 130189456 Oracle SQL Basic
104/148
Character FunctionsCharacter-Manipulation Functions
These functions manipulate character strings:
ORACLE SQL FUNDAMENTALS 104
-
8/11/2019 130189456 Oracle SQL Basic
105/148
Conversion FunctionsData-Type Conversion
ORACLE SQL FUNDAMENTALS 105
-
8/11/2019 130189456 Oracle SQL Basic
106/148
Conversion FunctionsUsing the TO_CHAR Function with Dates
The format model must be enclosed in singlequotation marks and is case sensitive.The format model can include any valid date formatelement. Be sure to separate the date value from theformat model by a comma.The names of days and months in the output areautomatically padded with blanks.
ORACLE SQL FUNDAMENTALS 106
-
8/11/2019 130189456 Oracle SQL Basic
107/148
-
8/11/2019 130189456 Oracle SQL Basic
108/148
Conversion FunctionsUsing the TO_Number Function with Char
CHAR to NUMBER conversions succeed only if thecharacter string represents a valid number.
SQL> SELECT TO_NUMBER('12345') FROM DUAL;
TO_NUMBER('12345')------------------
12345
Using the TO_Date Function with Char SQL> SELECT TO_DATE('14-DECEMBER-05','DD-MONTH-YYYY') FROM DUAL;
TO_DATE('---------14-DEC-05
ORACLE SQL FUNDAMENTALS 108
-
8/11/2019 130189456 Oracle SQL Basic
109/148
Miscellaneous FunctionsGREATEST
GREATEST returns the greatest of the list of one ormore expressions.
SQL>SELECT GREATEST ( 'HARRY', 'HARRIOT', 'HAROLD' ) "Greatest" FROM DUAL;Greatest--------HARRY
LEAST
LEAST returns the least of the list of expressions.SQL>SELECT LEAST( 'HARRY','HARRIOT','HAROLD' ) "LEAST FROM DUAL; LEAST------HAROLD
ORACLE SQL FUNDAMENTALS 109
-
8/11/2019 130189456 Oracle SQL Basic
110/148
Joining TablesOne of the most powerful features of SQL is its capability togather and manipulate data from across several tables. Without this feature you would have to store all the dataelements necessary for each application in one table.
Without common tables you would need to store the same datain several tablesThere are six types of joins
1. Equi-Join2. Non Equi-Join
3. Left Outer Join4. Right Outer Join5. Full Outer Join6. Self Join
ORACLE SQL FUNDAMENTALS 110
Cartesian Products
-
8/11/2019 130189456 Oracle SQL Basic
111/148
Cartesian Products A Cartesian product is formed when:
A join condition is omitted A join condition is invalid All rows in the first table are joined to all rows in the second table
To avoid a Cartesian product, always include a valid join condition in a WHEREclause.
SQL> select * from t1;
ENAME JOB SAL DEPTNO---------- --------- ---------- ----------CLARK MANAGER 2450 10KING PRESIDENT 5000 10MILLER CLERK 1300 10
SQL> select * from d1;
DEPTNO DNAME LOC---------- -------------- ---------
10 ACCOUNTING NEW YORK20 RESEARCH DALLAS
ORACLE SQL FUNDAMENTALS 111
-
8/11/2019 130189456 Oracle SQL Basic
112/148
Equi JoinEquijoins are also called simple joins or inner joins. When data from more than one table in the databaseis required, a join condition is used. Rows in one table can be joined to rows in another
table according to common values existing incorresponding columns, that is, usually primary andforeign key columns.
SQL>select e.ename,e.job,e.sal,e.deptno,d.deptno,d.dname,d.locfrom t1 e, d1 d where e.deptno=d.deptno ;
ORACLE SQL FUNDAMENTALS 112
Non Equi Join
-
8/11/2019 130189456 Oracle SQL Basic
113/148
Non Equi-JoinSQL> SELECT * FROM SALGRADE;
GRADE LOSAL HISAL---------- ---------- ----------
1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999
SQL> SELECT E.ENAME,E.SAL,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME SAL GRADE-------------------- ---------- ----------SMITH 800 1ADAMS 1100 1JAMES 950 1WARD 1250 2MARTIN 1250 2MILLER 1300 2ALLEN 1600 3
TURNER 1500 3JONES 2975 4BLAKE 2850 4CLARK 2450 4SCOTT 3000 4FORD 3000 4KING 5000 5
ORACLE SQL FUNDAMENTALS 113
L f O J i
-
8/11/2019 130189456 Oracle SQL Basic
114/148
Left Outer Join
ORACLE SQL FUNDAMENTALS 114
SQL> select * from t1;
ENAME JOB SAL DEPTNO---------- --------- ---------- ----------CLARK MANAGER 2450 10KING PRESIDENT 5000 10MILLER CLERK 1300 10RAJ CLERK 5000 50VARUN MANAGER 4500 60
SQL> select * from t1 left outer join d1 on (t1.deptno=d1.deptno);
ENAME JOB SAL DEPTNO DEPTNO DNAME LOC---------- --------- ---------- ---------- ---------- -------------- -------------MILLER CLERK 1300 10 10 ACCOUNTING NEW YORK
KING PRESIDENT 5000 10 10 ACCOUNTING NEW YORKCLARK MANAGER 2450 10 10 ACCOUNTING NEW YORKRAJ CLERK 5000 50 NULL NULL NULLVARUN MANAGER 4500 60 NULL NULL NULL
SQL> select * from d1;
DEPTNO DNAME LOC---------- -------------- ------------
10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 PRODUCTION CALIFORNIA
-
8/11/2019 130189456 Oracle SQL Basic
115/148
Right Outer Join
ORACLE SQL FUNDAMENTALS 115
SQL> select * from t1;
ENAME JOB SAL DEPTNO---------- --------- ---------- ----------CLARK MANAGER 2450 10KING PRESIDENT 5000 10MILLER CLERK 1300 10RAJ CLERK 5000 50VARUN MANAGER 4500 60
SQL> select * from t1 right outer join d1 on (t1.deptno=d1.deptno);
ENAME JOB SAL DEPTNO DEPTNO DNAME LOC---------- --------- ---------- ---------- ---------- -------------- ------------CLARK MANAGER 2450 10 10 ACCOUNTING NEW YORKKING PRESIDENT 5000 10 10 ACCOUNTING NEW YORKMILLER CLERK 1300 10 10 ACCOUNTING NEW YORKNULL NULL NULL NULL 30 PRODUCTION CALIFORNIANULL NULL NULL NULL 20 RESEARCH DALLAS
SQL> select * from d1;
DEPTNO DNAME LOC---------- -------------- ------------
10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 PRODUCTION CALIFORNIA
Full Outer Join
-
8/11/2019 130189456 Oracle SQL Basic
116/148
Full Outer Join
ORACLE SQL FUNDAMENTALS 116
SQL> select * from t1;
ENAME JOB SAL DEPTNO---------- --------- ---------- ----------CLARK MANAGER 2450 10KING PRESIDENT 5000 10MILLER CLERK 1300 10RAJ CLERK 5000 50VARUN MANAGER 4500 60
SQL> select * from t1 full outer join d1 on (t1.deptno=d1.deptno);
ENAME JOB SAL DEPTNO DEPTNO DNAME LOC---------- --------- ---------- ---------- ---------- -------------- -----------MILLER CLERK 1300 10 10 ACCOUNTING NEW YORKKING PRESIDENT 5000 10 10 ACCOUNTING NEW YORKCLARK MANAGER 2450 10 10 ACCOUNTING NEW YORKRAJ CLERK 5000 50 NULL NULL NULL
VARUN MANAGER 4500 60 NULL NULL NULLNULL NULL NULL NULL 30 PRODUCTION CALIFORNIANULL NULL NULL NULL 20 RESEARCH DALLAS
7 rows selected.
SQL> select * from d1;
DEPTNO DNAME LOC---------- -------------- ------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 PRODUCTION CALIFORNIA
-
8/11/2019 130189456 Oracle SQL Basic
117/148
Subqueries A subquery is a SELECT statement that is embedded in a clause of anotherSELECT statement.They can be very useful when you need to select rows from a table with acondition that depends on the data in the table itself. A subquery answers multiple-part questions. A subquery in the FROM clause of a SELECT statement is also called an inline view. A subquery in the WHERE clause of a SELECT statement is also called anested subquery. A subquery can contain another subquery. You can place the subquery in a number of SQL clauses, including:
The WHERE clause The HAVING clause The FROM clause
ORACLE SQL FUNDAMENTALS 117
Subqueries
-
8/11/2019 130189456 Oracle SQL Basic
118/148
qSubquery Syntax
SQL>SELECT last_name FROM employeesWHERE salary > ( SELECT salary FROM employees
WHERE last_name = 'Abel' );
Comparison conditions fall into two classes:Single-row operators (>,
-
8/11/2019 130189456 Oracle SQL Basic
119/148
SubqueriesTypes of Subqueries
Single-row subqueries : Queries that return only onerow from the inner SELECT statementMultiple-row subqueries : Queries that return morethan one row from the inner SELECT statement
ORACLE SQL FUNDAMENTALS 119
S b i
-
8/11/2019 130189456 Oracle SQL Basic
120/148
SubqueriesSingle-Row Subqueries A single-row subquery is one that returns one row
from the inner SELECT statement.This type of subquery uses a single-row operator.
ORACLE SQL FUNDAMENTALS 120
Subqueries
-
8/11/2019 130189456 Oracle SQL Basic
121/148
SubqueriesUsing Aggregate Functions in a Subquery
You can display data from a main query by using a group function in a subqueryto return a single row. The subquery is in parentheses and is placed after the comparison condition.
The HAVING Clause with Subqueries You can use subqueries not only in the WHERE clause, but also in the HAVINGclause.
The Oracle server executes the subquery, and the results are returned into theHAVING clause of the main query.
ORACLE SQL FUNDAMENTALS 121
Subqueries
-
8/11/2019 130189456 Oracle SQL Basic
122/148
qMultiple-Row Subqueries
Subqueries that return more than one row are called multiple-row subqueries. You use a multiple-row operator, instead of a single-row operator, with a multiple-rowsubquery.
Nested SubqueriesNesting is the act of embedding a subquery within anothersubquery.
SQL>SELECT ALL C.NAME, C.ADDRESS, C.STATE,C.ZIPFROM CUSTOMER CWHERE C.NAME IN( SELECT O.NAME FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM AND O.QUANTITY * P.PRICE >( SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM ))
ORACLE SQL FUNDAMENTALS 122
-
8/11/2019 130189456 Oracle SQL Basic
123/148
SubqueriesCorrelated Subqueries
Correlated subqueries enable you to use an outsidereference from outside the subquery
SQL>SELECT * FROM ORDERS OWHERE 'ROAD BIKE' = ( SELECT DESCRIPTIONFROM PART P WHERE P.PARTNUM = O.PARTNUM )
ORACLE SQL FUNDAMENTALS 123
VIEWS
-
8/11/2019 130189456 Oracle SQL Basic
124/148
VIEWS You can present logical subsets or combinations ofdata by creating views of tables. A view is a logical table based on a table or another view.
A view contains no data of its own but is like a windowthrough which data from tables can be viewed orchanged.The tables on which a view is based are called base
tables.The view is stored as a SELECT statement in the datadictionary.
ORACLE SQL FUNDAMENTALS 124
VIEWS
-
8/11/2019 130189456 Oracle SQL Basic
125/148
VIEWS Advantages of Views
Views restrict access to the data because the view candisplay selective columns from the table. Views can be used to make simple queries to retrieve theresults of complicated queries. For example, views can beused to query information from multiple tables without theuser knowing how to write a join statement.One view can be used to retrieve data from several tables. Views provide groups of users access to data according totheir particular criteria.
ORACLE SQL FUNDAMENTALS 125
-
8/11/2019 130189456 Oracle SQL Basic
126/148
VIEWSSimple Views and Complex Views
ORACLE SQL FUNDAMENTALS 126
The CREATE VIEW Statement
-
8/11/2019 130189456 Oracle SQL Basic
127/148
V WGuidelines for creating a view:
The subquery that defines a view can contain complex SELECT syntax, including joins, groups, and subqueries.The subquery that defines the view cannot contain an ORDER BY clause.The ORDER BY clause is specified when you retrieve data from the view.
SQL>CREATE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)
AS SELECT employee_id, last_name, salary*12FROM employeesWHERE department_id = 50;
View created .
Retrieving Data from a View You can retrieve data from a view as you would from any table. You can display either the contents of the entire view or just specific rows andcolumns.
SQL>SELECT * FROM salvu50;
ORACLE SQL FUNDAMENTALS 127
-
8/11/2019 130189456 Oracle SQL Basic
128/148
Creating a Complex ViewCreate a complex view that contains group functions todisplay values from two tables.
SQL>CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)FROM employees e, departments dWHERE e.department_id = d.department_idGROUP BY d.department_name;
View created.
ORACLE SQL FUNDAMENTALS 128
-
8/11/2019 130189456 Oracle SQL Basic
129/148
The DROP VIEW StatementThe statement removes the view definition from thedatabase.Dropping views has no effect on the tables on which
the view was based.
SQL>DROP VIEW empvu80;
View dropped.
ORACLE SQL FUNDAMENTALS 129
-
8/11/2019 130189456 Oracle SQL Basic
130/148
Indexes An index:
Is a schema object Is used by the Oracle Server to speed up the
retrieval of rows by using a pointer Can reduce disk I/O by using a rapid path access
method to locate data quickly Is independent of the table it indexes Is used and maintained automatically by theOracle Server
ORACLE SQL FUNDAMENTALS 130
-
8/11/2019 130189456 Oracle SQL Basic
131/148
IndexesCreating an Index
Create an index on one or more columns by issuing the CREATEINDEX statement .SQL>CREATE INDEX emp_last_name_idx
ON employees(last_name);Index created.
You should create indexes only if:The column contains a wide range of valuesThe column contains a large number of null valuesOne or more columns are frequently used together in a WHERE clauseor join conditionThe table is large and most queries are expected to retrieve less than 2to 4% of the rows
ORACLE SQL FUNDAMENTALS 131
-
8/11/2019 130189456 Oracle SQL Basic
132/148
IndexesIt is usually not worth creating an index if:
The table is small The columns are not often used as a condition inthe query Most queries are expected to retrieve more than
2 to 4% of the rows in the table The table is updated frequently The indexed columns are referenced as part of an
expression
ORACLE SQL FUNDAMENTALS 132
-
8/11/2019 130189456 Oracle SQL Basic
133/148
IndexesRemoving an Index
You cannot modify indexes.To change an index, you must drop it and then re-create it.Remove an index definition from the data dictionaryby issuing the DROP INDEX statement.
SQL>DROP INDEX upper_last_name_idx;Index dropped.
ORACLE SQL FUNDAMENTALS 133
Creating Sequence
-
8/11/2019 130189456 Oracle SQL Basic
134/148
Creating Sequence A sequence is a user created database object that canbe shared by multiple users to generate uniqueintegers. A typical usage for sequences is to create a primary key
value, which must be unique for each row.The sequence is generated and incremented (ordecremented) by an internal Oracle routine.Sequence numbers are stored and generatedindependently of tables.Therefore, the same sequence can be used for multipletables.
ORACLE SQL FUNDAMENTALS 134
Creating a Sequence
-
8/11/2019 130189456 Oracle SQL Basic
135/148
g qSQL>CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10START WITH 120
MAXVALUE 9999 NOCACHE NOCYCLE;
Sequence created.
The example in the slide creates a sequence namedDEPT_DEPTID_SEQ to be used for the DEPARTMENT_IDcolumn of the DEPARTMENTS table.The sequence starts at 120, does not allow caching, anddoes not cycle.Do not use the CYCLE option if the sequence is used togenerate primary key values
ORACLE SQL FUNDAMENTALS 135
Using a Sequence
-
8/11/2019 130189456 Oracle SQL Basic
136/148
g qSQL>INSERT INTO departments(department_id, department_name, location_id)
VALUES ( dept_deptid_seq.NEXTVAL , 'Support', 2500);1 row created.
Modifying a SequenceIf you reach the MAXVALUE limit for your sequence, no additional values from thesequence are allocated and you will receive an error indicating that the sequenceexceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCEstatement.
SQL> ALTER SEQUENCE dept_deptid_seqINCREMENT BY 20
MAXVALUE 999999 NOCACHE NOCYCLE;
Sequence altered.
ORACLE SQL FUNDAMENTALS 136
-
8/11/2019 130189456 Oracle SQL Basic
137/148
Removing a Sequence
Remove a sequence from the data dictionary byusing the DROP SEQUENCE statement.
Once removed, the sequence can no longer bereferenced.
SQL>DROP SEQUENCE dept_deptid_seq;Sequence dropped.
ORACLE SQL FUNDAMENTALS 137
Transactions Control Languaged
-
8/11/2019 130189456 Oracle SQL Basic
138/148
commands A transaction begins when the first DML statement is encountered and ends when one of the following occurs:
A COMMIT or ROLLBACK statement is issued A DDL statement, such as CREATE, is issued A DCL statement is issued
A DDL statement or a DCL statement is automatically committed and therefore itends a transaction.
Advantages of COMMIT and ROLLBACK Statements
With COMMIT and ROLLBACK statements, you can:Ensure data consistencyPreview data changes before making changes permanentGroup logically related operations
ORACLE SQL FUNDAMENTALS 138
Controlling Transactions
-
8/11/2019 130189456 Oracle SQL Basic
139/148
g
ORACLE SQL FUNDAMENTALS 139
Controlling Transactions
-
8/11/2019 130189456 Oracle SQL Basic
140/148
Controlling Transactions
ORACLE SQL FUNDAMENTALS 140
Rolling Back Changes to a
-
8/11/2019 130189456 Oracle SQL Basic
141/148
Savepoint You can create a marker in the current transaction by usingthe SAVEPOINT statement which divides the transactioninto smaller sections. You can then discard pending changes up to that marker byusing the ROLLBACK TO SAVEPOINT statement.If you create a second savepoint with the same name as anearlier savepoint, the earlier savepoint is deleted.
UPDATE...
SAVEPOINT update_done ;Savepoint created.INSERT...ROLLBACK TO update_done ;Rollback complete.
ORACLE SQL FUNDAMENTALS 141
Committing Changes
-
8/11/2019 130189456 Oracle SQL Basic
142/148
Committing ChangesState of the data before COMMIT or ROLLBACK statements are
issued: Data manipulation operations primarily affect the database buffer; therefore, the previous state of the
data can be recovered. The current user can review the results of the data manipulation operations by querying the tables.
Other users cannot view the results of the data manipulation operations made by the current user. The affected rows are locked; other users cannot change the data in the affected rows.
Following a COMMIT statement: Data changes are written to the database. The previous state of the data is permanently lost. All users can view the results of the transaction. The locks on the affected rows are released; the rows are now available for other users to perform new
data changes. All savepoints are erased.
ORACLE SQL FUNDAMENTALS 142
-
8/11/2019 130189456 Oracle SQL Basic
143/148
-
8/11/2019 130189456 Oracle SQL Basic
144/148
Controlling User Access
-
8/11/2019 130189456 Oracle SQL Basic
145/148
Controlling User Access
ORACLE SQL FUNDAMENTALS 145
Controlling User Access
-
8/11/2019 130189456 Oracle SQL Basic
146/148
Controlling User AccessPrivilegesPrivileges are the right to execute particular SQL
statements.The database administrator (DBA) is a high level user with the ability to grant users access to the databaseand its objects.
Schemas A schema is a collection of objects, such as tables, views, and sequences.The schema is owned by a database user and has thesame name as that user.
ORACLE SQL FUNDAMENTALS 146
-
8/11/2019 130189456 Oracle SQL Basic
147/148
Granting Object PrivilegesGrant query privileges on the EMPLOYEES table.
SQL>GRANT select ON employees TO scott;Grant succeeded.
Grant privileges to update specific columns tousers and roles.
GRANT update (department_name, location_id)ON departments TO scott;Grant succeeded.
ORACLE SQL FUNDAMENTALS 147
-
8/11/2019 130189456 Oracle SQL Basic
148/148
Revoking Object Privileges As user Alice, revoke the SELECT and INSERTprivileges given to user Scott on the DEPARTMENTStable.
SQL>REVOKE select, insert ON departments FROM scott;Revoke succeeded.
top related