1
Oracle® Database 10 g Course Contents
System Analyst. Ala'a Abu-Hammour. +962 7 77758361 +962 6 5355000 Ext. : 23809
2
Oracle® Database 10 g Introduction to Oracle 10g SQL & PL/SQL
2
Oracle® Database 10 g Introduction to Oracle 10g SQL & PL/SQL
3
Lesson 01 Basic Introduction First let us understand the basic definitions of elements, fields, and items, columns, records, tables, datatypes, as well as primary and foreign keys.
Elements, Fields and Items In this tutorial, we will use the terms: elements, fields and items interchange ably. A record also is the same as a row in a table. Columns, Tables, Records, and Datatypes To understand the relationships between tables, records, columns, and datatype consider the following. A field is the smallest unit in a table. A record is nothing more than a collection of fields or columns horizontally. A table holds a collection of records. Now each column must have its own datatype. A record can be added or inserted into a table. A value in a column can be changed and a record in a table can be deleted.
Primary and Foreign Keys Relationships between two tables are normally established by defining primary or foreign keys. The following diagram shows relationship between the EMP (Employee) table and the DEPT (Department) table. The DEPTNO column in the EMP table is a foreign key and the DEPTNO column in the DEPT table is the primary key.
4
A primary key has the immutable responsibility of serving as a unique identifier in a table. A foreign key is a column that refers to the primary key of another table. To join two tables, a “where clause” is used to set up a table relationship between primary and foreign keys.
Entity Relationship/Logical data Model "Entity Relationship Diagram" or "Logical Data Model" is u sed to establish relationships between entities. In the following diagram-an Entity Relationship Diagram For Company’s Customers and Their Orders--the relationship between the customer table and the order table, is established by their defined primary and foreign keys. The foreign key of the order table refers to the primary key of the customer table. A customer can have one or many orders. An order, however, can refer to one and only one customer. Composite Index A primary key can be composed of more than one column. We call it a composite index. An example of a composite index is the Item table in the diagram. Note how the combination of two columns in the items table was used to create a primary key. These two columns are "itemid" and "orderid". You are going to use the EMP, ORD, ITEM, CUSTOMERS, PORTFOLIO, PRICE_HISTORY, etc tables in your Hands-On training during the entire tutorial.
5
Entity Relationship Diagram For Company’s Customers and their Orders Now, observe the Entity Relationship Diagram for Company’s Customers and their portfolio carefully. The Customers table contains ID (customer id), first_name, last_name, etc. The customer id (ID) is the primary key of the customer table. In the portfolio table you have a composite index that contains the stock_symbol and customer_id columns. The customer_id column is a foreign key of the portfolio table and it refers to the primary key of the customer table. The stock_history table contains columns such as symbol, sales, high, low, etc. The symbol column in the stock_history table is the primary key. The symbol column as a primary key will refer to the foreign key (stock_symbol) in the portfolio table. As you can see a table can contain many foreign keys but only one primary key. The price table has a composite index. Its composite index contains the symbol and trade_date columns. The symbol column in the price table is a foreign key. It refers to the primary key of the stock_history table. From this ERD, you will know that a customer may have 1 or many stocks; and a customer’s stock may have 1 or many recorded prices and so on. It is very important for a developer to understand the entity relationship diagram of his/her task. It will assist the developer on writing its SQL statement. Study these two entity relationship diagrams and try to understand them. Then start to work on our hands-on exercises.
6
Entity Relationship Diagram For Company’s Customers and their Stocks Portfolio
Database Remember, a collection of all these tables under a single or many different schemas can be stored and maintained in a database. A database, in effect, is a collection of tables. DBA vs. Developers The integrity, security, connectivity, performance, and tuning of a database will be maintained by DBAs. One of the responsibilities of a DBA is to plan a contingency for disaster and ensure recovery of the database.
7
DBAs maintain the integrity of a database by using "backup and recovery". They perform Oracle client/server connectivity and do "performance tuning" to maximize availability and better response time for their clients. They may use Oracle Enterprise Management tools to perform and monitor their tasks. On the other hand developers use front-end and back-end tools along with management tools to perform their tasks. They develop applications to manipulate a database’s data. Their application will query, insert, delete and update a record or records. They use frontend tools such as "form builder," "report builder," and "graphics builder." They use backend tools such as "schema builder," "procedure builder," and "query builder." They use project builder tools to manage and deliver their applications to their clients. We strongly advise you before writing any "SQL" statements to study first your table and have a solid understanding of the table's attributes and its data structure. Now, let's observe the employee table. We abbreviated the employee table to "EMP." The columns in this table are: Employee number, name, job, manager's id, hire date, salary, commission, and department number.
8
Notice that the manager id column references to the employee number. A manager is an employee of his/her company. On this type of table, you can establish a "selfjoin" condition. Before writing any query spend some time to understand the structure of the table and its data. A table contains information that describes an entity. It has ROWS and COLUMNS. A row is a collection of information about a sub-entity. Here, for example, this table contains the company's employee information. A table may have a primary key. In this table, the first column (employee number) is a primary key. A primary key is a unique identifier for each individual employee. A table can have a foreign key. Here, the last column (department number) is a foreign key. A foreign key of a table always references to a primary key of another table. In this table, the foreign key references to the primary key of the department table. A table can have unique keys, composite keys, and index keys. Avoid having too many indexes in a table. Notice, the commission column and manager id columns have "null values." The employee number should not have a "null value", since it is a primary key. Notice that the manager id column refers to the employee number in the employee table. Once you understand the nature of your data in a table, you are ready to write a good query against that table.
Questions: Q: What are the definitions of the following items? column, record, table, item, field, element, primary key,
9 foreign key, and datatype. Q: What is the relationship between primary and foreign keys? Q: Describe the Entity Relationship diagram and Logical Data Model. Q: What is a composite index? Q: What are the responsibilities of an Oracle DBA and Oracle Developer? Q: What is a Database?
10
Lesson 02 Selecting Data (SQL) In this section, you will learn how to query an Oracle Table. Go to MS-DOS and login to "SQLPLUS" as "Alaa” password “test." SQL> CONNECT Alaa/test@ORCL
SELECT statements The SELECT statement is a DML (Data Manipulation Language) statement. DML statements are SQL commands that allows you to retrieve and manipulate a data in the database. SQL commands are divided to three parts: DML - Data Manipulation Language are SQL commands that allows you to query and manipulate data such as UPDATE, SELECT, INSERT, and DELETE. DDL - Data Definition Language are SQL commands that allows you to create, alter, and/or remove an object in the database such as CREATE TABLE, DROP TABLE, ALTER TABLE, etc. DCL - Data Command Language are SQL commands that allows you to execute a command in the SQLPLUS such as CONNECT, etc. Query the employee names and their salaries from the employee table. SQL> SELECT ename, sal FROM emp; Column Heading As you notice from the above query statement, the columns heading were the columns name as a default. These column names most of the time are meaningless. You use the column heading to make them more meaningful. For the same above query, now you use the “as” clause for the column heading. Notice that the column heading is changed. SQL> SELECT ename, sal AS salary FROM emp; Use the same above query without using the “as” clause. The ‘as’ clause is a default option. SQL> SELECT ename, sal salary FROM emp; Use the same above query to use a double quote for the ename and sal as “Full Name” and “Salary” column headings. You use a double quote if the column headings contain “case sensitive” or “blank” characters. SQL> SELECT ename “Full Name”, sal "Salary" FROM emp;
Arithmetic Expression You can use an arithmetic expression in the SELECT statement. Use an arithmetic expression that calculates a 10 p ercent salary bonus for all employees.
11
SQL> SELECT ename, sal salary, sal * .1 bonus FROM emp; Use the dual table as a dummy table to execute the system's function, user's functions or any other arithmetic expressions. SQL> SELECT 100+200 total FROM dual;
NULL value A null value means I don’t know. Note that any number that is added, multiplied, divided, or subtracted from NULL will be NULL. Remember, the result of any arithmetic calculation with a "null" is a "null" value. SQL> SELECT 100+null “Null + 100” FROM dual; Query the employee names and their commissions. Notice that the commission column contains the null value. SQL> SELECT ename, comm commission FROM emp;
NVL function You can use the NVL function to substitute any not NULL value with a NULL column. This way, you will get more control on how to manipulate that column in a WHERE clause. Use the null value function (NVL) to assign a zero to any null value in the commission column. SQL> SELECT ename, NVL(comm,0) commission FROM emp; Concatenation You will use two vertical bars (||) to concatenate two or more strings or columns together. Use two vertical bars or pipe line to concatenate a last and first name with the following format (example: Smith, Joe). SQL> SELECT last_name || ', ' || first_name AS "full name" FROM customers; ORDER BY clause You use the ORDER BY clause to sort one or more columns in a query. Use the "order by" clause to sort a query. Query the employee names sorted by ascending order. SQL> SELECT ename FROM emp ORDER BY ename ASC; Query the employee names sorted by descending order. SQL> SELECT ename FROM emp ORDER BY ename DESC; WHERE clause
12 You use the WHERE clause to query one or more records selectively. Query the employee information whose employee number is 7788. SQL> SELECT * FROM emp WHERE empno = 7788;
LIKE condition The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. The pattern can contain the special pattern-matching characters: % matches any string of any length (including length 0) _ matches any single character. • •
To search for the characters % and _, precede them by the escape character. For example, if the escape character is @, then you can use @% to search for %, and @_ to search for _. Query the employee names whose names start with the letter “M.” SQL> SELECT ename FROM emp WHERE ename LIKE 'M%'; Query the employee names whose names end with the letter “R.” SQL> SELECT ename FROM emp WHERE ename LIKE '%R';
BETWEEN condition The BETWEEN conditions specify a test to query all the records between the selection values inclusively. Query the employees name whose salaries between 2000 and 3000 dollars. SQL> SELECT ename FROM emp WHERE sal BETWEEN 2000 AND 3000; Query the employees’ name that’s their names start with a letter ‘C’ and end with a letter ‘F’. SQL> SELECT ename FROM emp WHERE upper (ename) BETWEEN ‘C’ AND ‘EZZZ’; You used the UPPER function in the above query to convert all names to upper case in the case if the employee’s names are case sensitive. DECODE function The DECODE function is like an IF-THEN-ELSE statement. It compares the content of a column to each of the comparisons. If there is a match, then it replaces the value. If there is no match, then the else action will be performed.
13 Query the employee names and their department names using the “DECODE” function. Check if the "department no" is 10 then print "accounting", else if the "department no" is 20 then print "research," or if the "department no" is 30 then print "sales". Anything else print "others." SQL> SELECT ename, DECODE (deptno, 10, 'Accounting', 20, 'Research', 30, 'Sales', 'Others') AS "Department" FROM emp;
Questions: Q: Query the employee names and their salaries from the employee table. Q: Do the above query and use an “as” clause for the “salary” column aliases or column headings. Q: Repeat the previous query and have “Full Name” for the ename’s column heading and “Salary” for the “sal” column heading. Q: What is the result of 100 + NULL? Q: Query the employee names with their commissions. Q: Use the (NVL) the null value function to assign zero to any null value in the commission column for the previous query. Q: Concatenate the customers’ last name and first name separated by comma. Q: Query the employees name sorted by ascending order. Q: Query the employees name sorted by descending order. Q: Query the employee information whose employee number is 7788. Q: Query the employees name whose names start with the letter “M.” Q: Query the employees name whose names end with the letter “R.” Q: Query the employees name whose salaries between 2000 and 3000 dollars. Q: Query the employees name and their department name using the “DECODE” function. If the department number is 10 then print "accounting.” If the department number is 20 then print "research," or if the department number is 30 then print "sales." Anything else prints others.
14
Lesson 03 Advanced Data Selection Introduction In this section, you will learn about an ambiguous column and how you can prevent it. You will learn how to query against two (such as the department and employee tables) or more tables by joining them together. You also learn what a sub-query is and how to use it. Now, let’s before you join the department table and the employee table, first query them individually. SQL> SELECT * FROM dept; SQL> SELECT * FROM emp; INNER Join or EQUIJOIN Joining two or more tables together is the best relational database usage. You relate the tables using the WHERE clause. The equal sign (=) in a query only retrieves records that have exact match. Query the employees name and their department name using user schemas to avoid the ambiguous column problem. Also, this is an example of the inner join or equijoin. SQL> SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno; Use the previous query and use the table aliases to avoid the ambiguous column. Use the letter “e” for the employee table alias and the letter “d” for the department table alias. SQL> SELECT ename, dname, e.deptno FROM emp e, dept d WHERE e.deptno = d.dpetno;
Ambiguous Column An ambiguous column is a column that is not defined clearly. Having two tables with the same column name, you should reference them such that there is no ambiguity on their ownerships. The column name should be identified by alias to make it clear that to what table that column is belong. If you join two tables that contain a common column name, you should use the table aliases or the user schema. Otherwise the column would be an ambiguous column. The following is an example of an ambiguous column. SQL> SELECT ename, deptno FROM emp, dept WHERE deptno = deptno; In the above example the ‘DEPTNO’ column is an ambiguous column since there is no identifier to indicate that what table the DEPTNO column belongs.
15
Cartesian product Avoid a Cartesian product. A “Cartesian” product is caused by joining “N” number of tables while you have less than “N-1” joins condition in the query. SQL> SELECT * FROM dept, emp; This is an example of a Cartesian product. OUTER JOIN By joining two or more tables using OUTER join, not only you retrieve all matching records but also you retrieve the records that do not match. For example that you may have an employee that you did not assign any department number for him or via versa. Try to query the department table again. SQL> SELECT * FROM dept; Notice, we have only three records. Insert a record to the department. Remember that we have no employee in the department table. SQL> INSERT INTO dept VALUES (40, 'Finance', 'Ohio'); Save the insert transaction. SQL> COMMIT; Now, query all the employee names and their department names including the entire department name with no employees. Use outer join. As you know there is no employee in the "Finance" department. Use + sign next to the column that has no match in it. SQL> SELECT ename, dname FROM emp e, dept d WHERE e.deptno (+) = d.deptno;
SELF JOIN If a table refers to itself in the WHERE clause, we say that join is a selfjoin. Query the manager names with their employees sorted by the manager names. This is an example of a selfjoin. SQL> SELECT mgr.ename “Manager Name”, e.ename “Employee Name” FROM emp mgr, emp e WHERE mgr.empno = e.mgr ORDER BY mgr.ename; SUM, AVG, MIN, and MAX functions Query the department number and their total, average, min, and max salaries for each department. SQL> SELECT deptno, SUM(sal), AVG(sal), MIN(sal), MAX(sal) FROM emp GROUP BY deptno;
16
GROUP BY statement Query the department number and their total salaries that have more than 5 employees in their department. SQL> SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING count(*) > 5; IN clause Use the IN clause, when you are going to use OR. Query the employee names that work for the Research or Sales departments (the department number 20 or 30). SQL> SELECT ename, deptno FROM emp WHERE deptno IN (20, 30); Sub-query If you write a query within a query, you are using sub-query. In the WHERE clause, not all the time, you have a constant value to compare. If you have to query a value from table, then you need to write a sub-query. Query the employee names that work in the "accounting" department. Assuming the department number is unknown. SQL> SELECT ename FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname = "ACCOUNTING"); Runtime variable If you have a query that you have to change its WHERE clause constants or column names over and over, you may use the runtime variables. You can substitute those values without changing or modifying the query script. Query the employee names of each department. Use the runtime variable to substitute the department number. Run to query department number 10. Run to query department number 20. Run to query department number 30. SQL> SELECT ename FROM emp WHERE deptno = &deptno; SQL> / Enter value 10. SQL> run Enter value 20. SQL> /
17 Enter value 30.
EXISTS statement You use the EXISTS clause to test existence of rows in the results of a sub-query. When you specify the exits operation in a WHERE clause, you must include a sub-query that satisfies the EXISTS operation. If the sub-query returns data, then the EXISTS operation will return TRUE and a record from the parent query will be returned. Query the customer names which have more than four orders. SQL> SELECT name FROM customer c WHERE exists (SELECT 'T' FROM ord WHERE custid = c.custid GROUP BY custid HAVING count(*) > 4);
Questions: Q: What is an ambiguous column? Q: How can you resolve an ambiguous column problem? Q: What is a Cartesian product? Q: How can you avoid a Cartesian product? Q: What is an inner join or equi-join? Q: What is an outer join? Q: What is a self join? Q: Query all the employee names and their department including all the departments with no employees. Q: Query the managers’ name with their employees sorted by the manager name. Q: Query the department number and their total, average, min, and max salaries for each department. Q: Query the department no and their total salaries that have more than 5 employees working in their department. Q: Query the employees name that work for the Research or Sales department (the department number 20 or 30). Q: Query the employees name that work in the "accounting" department. Assuming the department number is unknown. Q: Query the employees name and use the runtime variable to substitute the department number? Then run it for following department no 10, 20, and 30. Q: Query the customer names which have more than four orders.
18
Lesson 04 Create the Database objects Create table, Primary key, Foreign Key, Unique, and Delete Cascade Create an employee table that contains five columns: employee id, last name, first name, phone number and department number. The last and first name should be not null. Make a check constraint to check the department number is between 9 and 100. Make a primary constraint on the employee ID column. Make a foreign key constraint on the department number column. Use the "delete cascade" option to delete all records if parent gets deleted. Use the "phone number" as a unique key. SQL> CREATE TABLE employee (empid NUMBER(10), lastname VARCHAR2(20) not null, firstname VARCHAR2 (20) not null, phone_no VARCHAR2 (15), deptno NUMBER(2) CHECK (deptno BETWEEN 9 AND 100), constraint pk_employee_01 PRIMARY KEY (empid), constraint fk_dept_01 FOREIGN KEY (deptno) references dept (deptno) ON DELETE CASCADE, constraint uk_employee_01 UNQUE (phone_no)); Always, start the constraint name with PK prefix for a primary key, FK prefix for a foreign key, UK prefix for a Unique key, or CK prefix for a check constraint. Creating a composite index If you have an index that contains two or more columns, we call them a composite index. When an index is a composite index, you should make sure that the first column position in the index always be in your WHERE clause. The more columns from the composite index that you have in the WHERE clause the faster you will be able to retrieve your data. Avoid creating too many indexes. Create an index table using the “Create Index” statement. Create a composite index that contains two columns (last name and first name). SQL> CREATE INDEX employee_lname_fname_ind_01 ON employee (lastname, firstname); USER_TABLES table You use USER_TABLES to query or view all table objects (schema) that are belong to the user who login to a database. Query the tables that Alaa owns. The “Employee” table should be listed. SQL> SELECT table_name FROM user_tables ORDER BY table_name; USER_INDEXES table
19 You use USER_INDEXES to query or view all index objects (schema) that are belong to the user who login to a database. Indexes are attached to their tables. Dropping a table will drop all its indexes. Query the index tables that belong to the employee table and owns by the Alaa user. SQL> SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'EMPLOYEE'; Notice that there are three index tables of which two are unique. Make sure that table name is in uppercase. All tables’ name are stored in uppercase in the Oracle database.
USER_CONSTRAINTS table You use USER_CONSTRAINTS to query or view all constraint objects (schema) that are belong to the user who login to a database. The same as indexes, the constraints are attached to their tables. Dropping a table will drop all its constraints. You use USER_CONSTRAINTS to view table dependencies with its different types of constraints. Query the constraints name of the employee table including their types and status. On the constraint type column, "C" is for a "null and check" constraint; "U" is for a unique key; "R" is for a foreign key; and "P" is for a primary key. The status column can be enabled or disabled. SQL> SELECT constraint_name, constraint_type, status FROM user_constraints WHERE table_name = 'EMPLOYEE'; COLUMN command You use the COLUMN command to change size or format of your column to be displayed. Syntax: COL[UMN] [{column|expr} [option ...]] options: ALIAS alias Assign an alias to a column CLEAR Reset the display attributes
Format a column (e.g. COL emp_name FORMAT A15) HEA[DING] 'text' Set a column heading JUSTIFY {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} LIKE {expr|alias}Format like another column (already defined) NEWLINE Same as FOLD_BEFORE NOPRINT|PRINT Display the column NUL[L] char Display NULL values as Char ON|OFF Enable or disable column format attributes WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED] How to treat long CHAR strings
Use the column command to change the size of the "column_name" to 30 characters. SQL> COLUMN column_name FORMAT a30
20
USER_IND_COLUMNS table USER_IND_COLUMNS contains all information about those columns that are index. It keeps information such as index_name, column_name, table_name, column position, etc. Query the index columns of the employee table. Remember that on the composite index the sequence of the column would be the same as the column position. Notice that the last name has the first position and the first name has the second position in the composite index. SQL> SELECT index_name, column_name, column_position FROM user_ind_columns WHERE table_name = 'EMPLOYEE'; INSERT statement using column names The INSET statement writes a record in to a table. The following are some of INSERT syntaxes that are frequently used: Syntax: INSERT [hint] INTO [schema.] table [@dblink] [t_alias] (column, column,...) VALUES (expression) INSERT [hint] INTO [schema.] table [@dblink] [t_alias] VALUES (expression) INSERT [hint] INTO [schema.] table [[SUB]PARTITION (ptn_name)] (column, column,...) VALUES (expression) INSERT [hint] INTO subquery WITH [READ ONLY | CHECK OPTION [CONSTRAINT constraint] ] [t_alias] (column, column,...) VALUES (expression)
Insert a record into the "employee" table using column names. In this type of insert, the input data values are inserted by a position of column. For example, 100 goes to employee id; "smith" goes to the "lastname"; 10 goes to the "department number; "joe" goes to the "firstname"; and 703 821 2211 goes to the "phone_no" column. SQL> INSERT INTO employee (empid, lastname, deptno, firstname, phone_no) VALUES (100, 'smith', 10,'joe', ‘7038212211');
COMMIT statement A record will not be added to a table unless you execute the COMMIT statement. All not committed records are stored in UNDO segment. It will give you a chance to undo your transaction. COMMIT means save all none committed transaction. It gu arantees that the DBA will be able to recover your data to the point of failure. Save the transaction. SQL> COMMIT; INSERT statement using the column position Insert a record using the column position format. In this case, the input data are inserted by the sequences of position of columns in the table. For example, 200 goes into the first column of the table; "KING" goes into the second column of the table; and so on. SQL> INSERT INTO employee VALUES (200, 'KING', 'Allen', 5464327532, 10);
21
Save the transaction. SQL> COMMIT; Query the employee table. SQL> SELECT * FROM employee;
UPDATE statement To change a value of a column in a table, you use the UPDATE statement. You must use the WHERE clause for specific record or records. Notice that if you don’t use a WHERE clause then the entire table will be changed. That could be an action that you did not want it. Change "Smith" to “Judd” where "employee id" is 100. SQL> UPDATE employee SET lastname = 'Judd' WHERE empid = 100; Save the transaction; SQL> COMMIT; Query the employee table to see the changes; SQL> SELECT * FROM employee;
DELETE statement A record or records can be deleted from a table by using the DELETE statement. Again the same as UPDATE, you must make sure to have a WHERE clause in your query. Avoiding a WHERE clause will delete your all records in the table. Delete the employee record where its employee id is 200. SQL> DELETE FROM employee WHERE empid = 200; Save the transaction; SQL> COMMIT; Query the table. SQL> SELECT * FROM employee;
DELETE all records As we mentioned before, to delete all records, you only avoid a WHERE clause. You can also truncate a table. If you want to delete all records from a table, you should use the TRUNCATE statement. It will change the table watermark. The table watermark is an address that indicates a last location of a record in a table. On the DELETE statement the watermark will not change. But using the TRUNCATE statement will change the watermark to the beginning of the table.
22 Delete all records from the employee table using the DELETE statement and do not commit. SQL> DELETE FROM employee; Query the table. SQL> SELECT * FROM employee;
ROLLBACK statement If you change, delete, insert a record into a table but not execute the commit statement. All your before transaction block images are in an UNDO segment. You can execute the ROLLBACK statement in order to undo your transaction. It is a perfect statement for correcting a user mistake such as deleting a table’s records. Undo the delete transaction, as long as you did not commit the transaction. SQL> ROLLBACK; Query the employee table again. SQL> SELECT * FROM employee; All records are back. Notice that you only are able to undo a transaction to the last point that you executed a COMMIT statement.
TRUNCATE statement Now, truncate the employee table. SQL> TRUNCATE TABLE employee; Do not commit. Notice that the TRUNCATE command is a DDL statement and all DDL statements have commit inclusive. That is why the ROLLBACK action after truncation does not work. Undo the truncation. SQL> ROLLBACK; Query the employee table again. SQL> SELECT * FROM employee; Note that you lost all the data. Always remember that the truncate statement is a DDL statement and in all the DDL statements the commit is implicit.
Questions: Q: Create an employee table that contains five columns: Such as Employee Id, last name, First name, Phone number and Department number with the following constraints. 1. The last name and first name should be not null. 2. Make a check constraint to check the department number is between 9 and 100. 3. Make a primary constraint on the employee ID column. 4. Make a foreign key on the department number column. 5. Use the "delete cascade" to delete all records if parent gets deleted.
23 6. Use the "phone number" as a unique key. Q: Create a composite index on the employee table that contains two index columns (last name and first name). Q: Query the tables that you as a user own. Q: Query the index tables that belong to the employee table and owns by the Alaa user. Q: Change the size of the "column_name" to 30 characters logically (for display only. Q: Query the indexes columns of the employee table. Q: Insert a record into the "employee" table using column names. Q: Insert a record using the column position format. Q: How do you save the inserted transaction? Q: Change the "last_name" column value from “Smith” to “Judd” where the "employee id" is 100. Q: Delete all the employee records from the "employee" table using the delete command and the truncate command. Q: How do you undo a transaction? Q: What is the difference between the delete statement and the truncate statement?
24
Lesson 05 Creating more Database Objects SET SQLPROMPT Sometime you may want to open multiple SQLPLUS sessions. It is very normal that you may not know that what session you are in. To avoid this confusion, you can use the SQLPROMPT option to stay away from such problem. In this hands-on, you need to open three sessions. Each session will be a different user. For example, in the first session login to "sqlplus" as "Alaa” password “test" MS-DOS> SQLPLUS Alaa/test Set the sql prompt to “Alaa.” SQL> SET SQLPROMPT ‘Alaa > ’ Go to the second session and login to "sqlplus" as “system” password “manager.” MS-DOS> SQLPLUS system/manager Set the sql prompt to “system.” SQL> SET SQLPROMPT ‘system > ’ Go back to the Alaa session.
DROP TABLE When you drop a table all its dependencies such as its indexes, constraints, and triggers will be deleted. All the views, procedures, packages, and functions that reference to that table will be in an invalid status. Drop the employee table. SQL> DROP TABLE employee; CREATE TABLE AS … If you want to copy a table, you can use the CREATE TABLE AS statement. It copies all records. Notice that the new table will not inherit any constraints, indexes, and triggers from original table. Only records will be copied not dependencies. Copy column of employe name, job, commission and department number of the “EMP” table to the employee table. SQL> CREATE TABLE employee AS SELECT ename, job, comm, deptno FROM emp; DESCRIBE command When you create a table, you would like to know what its columns’ name and their attributes are. The DESC command will display such information. Notice that you will not find its indexes information, and constraints’ name. Describe the employee table. SQL> DESC employee
25
ALTER TABLE ADD … Once you create a table, you may want to delete, resize, or add more columns to it. The ALTER TABLE ADD statement will add a column or add a constraint to a table. Add a salary column to the employee table. Use "add" clause to add a column. SQL> ALTER TABLE employee ADD (salary NUMBER(8,2)); Describe the employee table. SQL> DESC employee The "salary" column was added.
ALTER TABLE MODIFY … The MODIFY option in the ALTER TABLE statement, can change a column’s datatype and its length. Note that if you decrease a column’s length, you may truncate some of the data in the column. Therefore, Oracle will not allow you to reduce the length of a column unless it is null. So in order to reduce a column’s size, just copy that column to an alternative table, and then null the column and change the size. Then put the column values back. If the column has constraint, you may have to disable the constraint. Change the "ename" column size from 10 to 15. Use the "modify" clause to modify the column table. SQL> ALTER TABLE employee MODIFY (ename VARCHAR2(15)); Describe the employee table to see the changes. SQL> desc employee
RENAME command The RENAME command will change the table’s name. Remember that when you change the table name, all the status of those views, procedures, functions, and packages that were referencing to the table will be changed to an INVALID mode. You should modify them (views, procedures, functions, and packages) in order to force them to refer to the new table; and once they have been compiled or used, their status will be changed to a VALID mode. Rename the "employee" table to the "Alaa_employee" table. SQL> RENAME employee TO Alaa_employee; Query the Alaa_employee table to make sure that its data was not changed. SQL> SELECT * FROM Alaa_employee;
CREATE VIEW AS … You use view to present rows and columns of a table in the way you want. You may use it for security reason. For example, you may eliminate some rows and columns that are very sensitive information. These changes are transparent to a user. There are two types of views. They are Simple View and Complex View. A Simple View is a view that was
26 created only from one single table. In the contrast a Complex View is a view that it has two or more tables joining together. You may also insert, update, and delete a record using simple views. Do not try to insert, update, or delete a record from a complex view. Create a view to display only the employees name of the “Accounting” department. You may use a view for a security reason. This is an example of a simple view. SQL> CREATE VIEW employee_name AS SELECT ename FROM Alaa_employee WHERE deptno = 10; Query against the new view to see the output. SQL> SELECT * FROM employee_name;
ALTER VIEW … As we mentioned before, when you rename a table, all the status of its associated views, procedures, functions, and packages become in an invalid mode. One way to change the status from invalid to valid is to use the ‘ALTER VIEW’ statement with the COMPILE option. The view should be compiled if its status is invalid. Compile the view. SQL> ALTER VIEW employee_name COMPILE; DROP VIEW … A view can be dropped if it is not needed. When you drop a view, all the tables used in the view will be untouched. But all the views that were created using the dropped view, their status will change to an INVALID mode. Drop the employee_name view. SQL> DROP VIEW employee_name; CREATE INDEX command When you use the WHERE clause in your SQL statement, Oracle’s optimizer will ch eck to see whether you have an index on that column or not. If not, then it will scan the whole table. If your table is a long table which normally it is. It may take a very long time to retrieve that query. Creating an index on a column that you normally have it in the WHERE clause, will eliminate to scan the table. Creating an index will increase a performance of a query. Keep this in mind, just making all columns index because that will increase a query’s performance, it is not a good practice. When you create an index on a column, any insert, update, and delete has to do one more task to maintain the index table. That may cause some performance problem. There are different types of indexes: 1- B-Tree index, 2- Bitmap index, and B-Tree index When you create an index table by default will be the B-Tree index, unless you specify otherwise. The B-Tree index is the traditional index. It stores your data in a treelike
27 fashion. It has the root node, which is an entry point for your search. It contains pointers to other nodes. The pointers in the root node point to another level in the index, which we call them branch nodes. The branch nodes also contain pointers to other nodes to the next level of node in the index. The highest level of the index is called the leaf nodes. Each leaf node is linked to its right and left leaf nodes. You create a B-Tree index on a column, if that column has a very high cardinality. The more unique value in a column represents a higher cardinality. For example: assuming that I have a table that contains 1000 records. If one of its columns (column c1) has 995 unique values against 1000 values, then that column has a high cardinality. In the contrast if one of its columns (columns c2 ) has 25 unique values against all 1000 values, then that column has a low cardinality. Bitmap Index You create a bitmap index against a column when you have a low cardinality. Think of a Bitmap Index as a matrix. Its columns correspond to all unique values in the column. Notice that the higher cardinality brings more columns in your index table. That is the reason; why you should use a Bitmap Index when your column has a very low cardinality. Now, create a B-Tree index on the employee name column on the employee table. SQL> CREATE INDEX employee_ename_ind_01 ON Alaa_employee (ename);
ALTER INDEX REBUILD An index table can be reorganized if it has fragmentation. A table may have fragmentation when you deleted lots of records or your table contains lots of migration or chained records. Assuming that your employee_ename_ind_01 index table, it has an index fragmentation. Reorganize the index table. SQL> ALTER INDEX employee_ename_ind_01 REBUILD; DROP INDEX … An index table can be dropped, if it is not needed. When you drop an index table, its table will not be dropped. Drop the employee_ename index table. SQL> DROP INDEX employee_lname_ind_01; Go to the "system/manager" session. CREATE USER … No one can login to Oracle unless they have userid and password. They should also have been granted to ‘CREATE SESSION.’ Otherwise, they will not able to login to SQLPLUS. They should have some system privileges in order to do their task such as CREATE TABLE, CREATE PROCEDURE, ALTER TABLE, etc. Make sure always to assign a default tablespace and a temporary tablespace to a user. Make sure that they are not able to write in the SYSTEM tablespace. Later in this book, you will learn how to stop users to create an object in the SYSTEM tablespace. Create a user with username “newuser” and password "newpass." Make its default tablespace as "Alaa_data." SQL> CREATE USER newuser IDENTIFIED BY by newpass
28
DEFAULT TABLESPACE Alaa_data; GRANT RESOURCE, CONNECT statement The RESOURCE and CONNECT roles contains some system privileges that allows users to login, create table, alter table, etc. For example: The CONNECT role has the CREATE SESSION system privilege. A user without the CREATE SESSION system privilege can not even login to SQLPLUS. Notice that the user can not connect or create any objects unless the privileges are granted to it. Grant the resource and connect roles to newuser. SQL> GRANT resource, connect TO newuser; Go to the third session and connect as newuser password newpass. SQL> sqlplus newuser/newpass Set the sql prompt to newuser. The SQLPROMPT option is a good command to show a user that what session he/she is in. SQL> SET SQLPROMPT ‘newuser > ‘ ALTER USER … Change the password often. It is a good practice. You as a user can only change your password by using the ALTER USER statement. You can not do any other altering such as changing your default tablespace, etc. The only user that can change your default tablespace is the one has already been granted the ALTER ANY USER system privilege. Change the newuser password to "mypass". SQL> ALTER USER newuser IDENTIFIED BY mypass; Users should be granted access by other users to query their tables. Query the Alaa_employee table. SQL> SELECT * FROM Alaa.Alaa_employee; No access granted. Go to the “system/manager” session
CREATE PUBLIC SYNONYM … You create public synonym so that the users don’t need to type schema name to a table when they query the table. Creating a public synonym does not mean that oracle users can access to that table or object. Still the owner of the object has to grant access to a user on its table. Create a public synonym. The public synonym must be unique to an object. The public synonym ‘employees’ is exit. SQL> CREATE PUBLIC SYNONYM employees FOR Alaa.Alaa_employee; DROP PUBLIC SYNONYM … It comes a time that you may have to drop a public synonym. Dropping a public synonym, it doesn’t drop its dependencies. Drop the public synonym. SQL> DROP PUBLIC SYNONYM employees; Then create it again.
29 Now, all the users that can access to Alaa’s employee table should be able to access it through the public synonym. Go to the "Alaa" session
GRANT command You can grant an specific object privilege to user or all the privileges. For example: GRANT SELECT gives only a query access on a table. GRANT UPDATE gives only a write access on a table. GRANT DELELTE gives only a delete access on a table. GRANT INSERT gives only an insert access on a table and more… GRANT ALL gives all the access on a table to a user. Grant select and update on the employee table to the newuser. The newuser can not delete or insert any record. SQL> GRANT SELECT, UPDATE ON employee TO newuser; Go to the "newuser” session Query the employee table using its public synonym. SQL> SELECT * FROM employee; Private Synonym Sometime when you access to an object, you may have to type a lot of words. For example; I can access to a table like this: SELECT * FROM
[email protected]; Notice that typing
[email protected] is not very practical every time. So, you may create your own private synonym. SQL> CREATE SYNONYM emp FOR
[email protected]; From now on, you can access to the table by using emp. For example: SQL> SELECT * FROM emp; When you create a private synonym, it will be used only by you. No one can use that synonym. Create a private synonym emp_table for the Alaa employee table. SQL> CREATE SYNONYM emp_table FOR Alaa.Alaa_employee; Query the table using the “emp_table” private synonym. SQL> SELECT * FROM emp_table;
DROP SYNOMYM … A user can drop its private synonym if is not needed. Drop the emp_table private synonym. SQL> DROP SYNONYM emp_table; Go to the "Alaa" session and revoke the privileges from "newuser." It comes a time that you may have to revoke some object or system privileges from a user.
30 Notice that an object privilege may have been granted to a user by using WITH GRANT OPTION. If that is the case then when you revoke an object privilege from a user, all the object privileges that were granted by that user to some other Oracle users will be revoked too. In the contrast with the WITH GRANT OPTION, if a system privilege was granted WITH ADMIN OPTION. When you revoke it, all the system privileges that were granted by that user to some other Oracle users will not be revoked. Revoke the update and select privileges on the employee table from newuser. SQL> REVOKE UPDATE, SELECT ON employee FROM newuser; Back to the "newuser" session. Query the employee table. SQL> SELECT * FROM employee; No access.
Questions: Q: Copy the “EMP” table to another table and name the new table "employee." In the new employee table use the employee name, job, commission and department number. Q: Add a salary column to the employee table. Q: Modify the "ename" column size from varchar10 to varchar15. Q: Rename the "employee" table to the "Alaa_employee" table. Q: Create a view to display the employee names of the “Accounting” department only. Q: Why do you use the view? Q: How do you compile the view? Q: How do you delete the view? Q: Create an index on the employee table on the ename column only and name it employee_indx. Q: Reorganize the “employee_indx” index table. Q: Drop the employee_ename index table. Q: Create a user with username “newuser” and password "newpass." Its default tablespace should be the "Alaa_data" tablespace. Q: Grant the resource and connect roles to newuser. Q: Change the newuser password to "mypass". Q: Can the above new user access to any other user tables? Q: What is a public synonym? Q: What is the syntax to create a public synonym? Q: What is the difference between public and private synonym? Q: Create and drop a private synonym. Q: Revoke an object privilege on a table from a user.
31
Lesson 06 Command Line editing Go to MS-DOS and make a new directory to be used for your new scripts and programs. Login to SQLPlus from DOS Change directory to the root directory. DOS> cd .. Make a directory called "Alaa". DOS> mkdir Alaa Change the directory to the Alaa directory. DOS> cd Alaa List directory. Notice that there is nothing in it. DOS> dir Login to “sqlplus” as "Alaa” password “test". SQL> sqlplus Alaa/test
SQLPlus default directory From now on the “Alaa” directory is a default directory for “SQLPLUS.” Query the dept table. SQL> SELECT deptno, dname, loc FROM dept;
Command line editing You should know command line editing since it is a universal editor regardless of the operating system platform. You can use it at IBM, UNIX, LINUX, DOS, and any other operating system. Learn it well, since it may come handy. Oracle Buffer Remember that always your last “SQL” statement is in the Oracle buffer. L command Type the letter "L" to list the last entered “SQL” statement. SQL> l The asterisk next to the line indicates the current line position. RUN commands Run the “SQL” statement in the buffer, using the "run" command. SQL> run -or- the letter "r." SQL> r -or- "/"
32
SQL> / Semi-column in Oracle You enter a semi-column at the end of each SQL statement. It means to terminate a SQL statement and execute the statement in the Oracle buffer. Write a format free “SQL” statement to query the dept table; and enter each word in a line. SQL> SELECT deptno, dname, loc FROM dept; End the SQL statement with “;” to terminate and execute the statement. List the statement from the Oracle buffer. SQL> L
A dot (.) in a SQL statement You enter a dot at the end of your SQL statement to tell Oracle that it is the end of my SQL statement and do not execute the statement. You may do that if for some reason you want to change your SQL statement in the Oracle buffer. Write a format free query and use a dot at the end of the sql statement to end the statement, but not run the statement. The sql statement will be in the Oracle buffer as long as it was not replaced or the user session was not terminated. SQL> SELECT deptno , dname , loc FROM dept . Then run the statement. SQL> r
“c” or CHANGE command Use the command line editor to add ‘location’ a column heading to the "loc" column. First, you should change the current line position to number 6 and then use the "C" or change command to add column heading. SQL> L6 SQL> c/loc/loc "location"/
33
Listing lines within a range List the sql statement lines from 2 to 7. SQL> L 2 7 List the entire query. SQL> L Deleting lines within a range Delete the sql statement lines from 4 to 5. SQL> del 4 5 Run the query. Notice that the department name column was deleted. SQL> /
Positioning a SQL line Position line number 3 to the current line. SQL> L3 “I” or INSERT command Use the "i" or insert command to insert a line. Then use the dot to terminate the insert mode. SQL> i 4i dname, List the query. The line was added. SQL> L Then run the statement. SQL> /
SAVE command Save the sql statement as "d-e-p-t" file in the “Alaa” directory. Notice that the default extension is “s-q-l.” SQL> save dept GET command Use the "get" command to replace the sql file into the buffer. SQL> get dept EXIT command Exit “sqlplus” to see where the file was stored? SQL> exit List the file names in the Alaa directory. DOS> dir
TYPE command
34 Use the "type" command to list the query. DOS> type dept.sql Login to "sqlplus" as “Alaa/test” DOS> sqlplus Alaa/test List the buffer. Notice that there is no query in the buffer. Get the file and then run it.
SQL> l SQL> get dept / “@” command or use the "@" sign command to run the file. SQL> @c: START command Or use the "start" command to run the file. SQL> start dept These are different ways you can run the SQL script file.
Questions Q: What does the LIST or ‘L’ command line editor? Q: What does the INSERT or ‘I’ command line editor? Q: What does the DEL or ‘D’ command line editor? Q: How do you change a string in the Oracle Buffer? Q: How do you save the SQL script in the Oracle Buffer? Q: How do you open the SQL Script into the Oracle Buffer?
35
Lesson 07 Notepad Editing Why notepad editing and use of afiedt.buf Oracle makes provisions for you to utilize your favorite text editor to edit the statement created in "afiedt.buf," the file into which SQL*PLUS stores the most recently executed SQL statement. You simply type edit (abbreviated "ed"). This action causes Oracle to bring up the SQL statement in "afiedt.buf" into the operating system's default text editor. DEFINE_EDITOR command To change the text editor, issue the define_editor='your editor' statement from the SQL*PLUS prompt. Try to use a text editor rather than the Command Line editor. Go to MS-DOS and change the directory to the Alaa folder. SQL> cd .. Login to “sqlplus” as "Alaa/test". SQL> sqlplus Alaa/test Get the dept file. SQL> get dept
“ed” command Your default editor is notepad. Use the "ed" command, to call notepad. On notepad, you can modify the query. Then close the window and click on Yes to replace the changes to the buffer. Notice that when you use the ‘ed’ command in the sqlplus, your defined editor will bring the last SQL statement from Oracle buffer. Now, you should be able to modify the SQL statement. After you modify your query, you can save and exit from the defined editor. By default that is NOTEPAD. Remember that when you bring your SQL statement into your editor the ‘;’ character will be disappeared and instead a ‘/’ character will be replaced. Leave the SQL statement with ‘/’ character and don’t enter ‘;’ character at the end of your SQL statement. Also, you should not work in the defined editor one SQL statement at a time. You can not have multiple SQL statements. Notepad as an independent tool If you want to use multiple SQL statements or to write your SQL script, you may want to open your own independent editor from the SQLPLUS tool. And the technique you may want to use is to open the notepad as an independent tool. To open the dept script from the Alaa directory, open notepad as a separate window and then open the "dept" file from the "Alaa" folder. Add a column heading. Change the statement to query only the department number 10.
36 Save the file. Save the file at the Alaa folder.
Go to “SQLPLUS.” Get the "dept" file. SQL> get dept Notice that the changes are in the buffer, run the query. SQL> run Use the “Start” command to run the query. Go to notepad
Remove the department number column. Save the changes. Go back to “SQLPLUS.” Use the @ sign to run the file. No department number this time.
Questions: Q: How do you use the notepad editor? Q: What is afiedt.buf? Q: How do you change your text editor in the SQLPLUS tool? Q: What does the ed command in the SQLPLUS tool? Q: Can you have multiple SQL statements in the afiedt.buf file? Q: How do you use the notepad editor as an independent tool in the SQLPLUS utility? Q: How do you execute or run a SQL script?
37
Lesson 08 Oracle9i: SQL ANSI statements CONNECT command Connect to SQLPLUS as the Alaa user. SQL> CONNECT Alaa/test SET command Let's begin by setting the linesize to 100 and the pagesize to 55. SQL> SET LINESIZE 100 PAGESIZE 55 Now, let's compare the Oracle9i ANSI (American National Standards Institute) standard JOIN syntax with the original join syntax. Query the employee name, department number, and department name columns from the EMP table and DEPT table. Join command using original syntax First, let us exclude all of the sales department information by using the original Oracle join syntax. SQL> SELECT ename, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND dname <> 'SALES' / JOIN syntax (ANSI) And now, use the Oracle9i ANSI standard JOIN syntax. SQL> SELECT ename, dept.deptno, dname FROM emp JOIN dept ON emp.deptno = dept.deptno AND dname <> 'SALES' / Notice, that you get the same result. NATURAL JOIN syntax (ANSI) Use the Oracle9i ANSI standard NATURAL JOIN syntax to join the EMP and DEPT tables where the employees' salary is greater than 3000 dollars. SQL> SELECT ename, deptno, dname FROM emp NATURAL JOIN dept WHERE sal > 3000 / Notice that in the NATURAL JOIN syntax, you don't need a WHERE clause since the department number is the same. USING clause Use the USING clause to join the EMP and DEPT tables where employees' salary is greater than 3000 dollars. SQL> SELECT ename, deptno, dname FROM emp JOIN dept USING (deptno)
38
WHERE sal > 3000 / Notice that in the USING clause the join column names must be the same. CROSS JOIN syntax (ANSI) Use the Oracle9i ANSI standard CROSS JOIN syntax with no WHERE clause to create a Cartesian product. Query the department name and employee name where the department number is (10 or 30) and the employees are (SMITH, ALLEN, and WARD). SQL> SELECT dname, ename FROM dept d CROSS JOIN emp e WHERE d.deptno IN (10,30) and ename IN ('SMITH','ALLEN','WARD') ORDER BY dname / This is an example of an Oracle9i ANSI standard CROSS JOIN syntax. OUTER JOIN syntax (ANSI) Using the OUTER JOIN option, not only will you query records that have matches but you also see the rows that have no matches. The Oracle9i ANSI Join syntax provides three separate capabilities: RIGHT, LEFT, and FULL OUTER JOIN. The word OUTER is redundant and usually omitted. RIGHT OUTER JOIN syntax (ANSI) Let's demostrate the RIGHT OUTER JOIN syntax usage. First, add a Finance department to the department table with no associated employees. SQL> INSERT INTO dept VALUES (40,'FINANCE','WASHINGTON,DC') / SQL> COMMIT; Query all employee names with their department's name. With that in mind to: -- Exclude all of the sales department information; -- Include all of the departments that have no employees. Use the original oracle join syntax to do the OUTER JOIN query. SQL> SELECT nvl(e.ename,'No Match'), d.deptno, d.dname FROM dept d, emp e WHERE d.deptno = e.deptno (+) AND dname <> 'SALES' / Notice that FINANCE deptartment has no match. Write the same above query, using the Oracle9i ANSI standard RIGHT OUTER JOIN syntax. SQL> SELECT nvl(e.ename,'No Match'), d.deptno, d.dname FROM emp e RIGHT OUTER JOIN dept d ON d.deptno = e.deptno
39
WHERE dname <> 'SALES' / LEFT OUTER JOIN syntax (ANSI) Add an employee who doesn't work in any of the departments yet. Make sure to disable the foreign key before inserting a record. SQL> INSERT INTO emp VALUES (9900,'KAZ','CLERK',7902,TO_DATE(sysdate),1000,NULL,NULL) / COMMIT / Query all of the employee names including those who don't work for any department. Use the original Oracle syntax. SQL> SELECT e.ename, d.deptno, nvl(d.dname,'No Match') as department FROM dept d, emp e WHERE d.deptno (+) = e.deptno / Notice that employee KAZ does not work for any department. List the same above query by using the Oracle8i ANSI standard LEFT OUTER JOIN syntax. SQL> SELECT e.ename, d.deptno, nvl(d.dname,'No Match') as department FROM emp e LEFT OUTER JOIN dept d ON d.deptno = e.deptno / FULL OUTER JOIN syntax (ANSI) Let's perfom an excercise to use the FULL OUTER JOIN option to find all of the records that have no matches in the two joined tables. SQL> SELECT nvl(e.ename,'No Match') as name, d.deptno, nvl(d.dname,'No Match') as department FROM emp e FULL OUTER JOIN dept d ON d.deptno = e.deptno / That would be difficult to do with an original Oracle syntax. Delete the FINANCE department and the KAZ employee. SQL> DELETE FROM dept WHERE deptno = 40 / DELETE FROM emp WHERE empno = 9900 / SQL> COMMIT
40
/ You have deleted the records so that you can repeat these steps over and over again.
Questions: Q: What is the SQL ANSI statement? Q: What is the difference between the SQL ANSI statement and Original Oracle statement? Q: Is the SET command a SQL statement? Q: How do you change your workstation’s page size or line size? Q: What does the JOIN syntax in the Oracle SQL (DML) statement? Q: What is the difference between the JOIN syntax and the NATURAL JOIN syntax? Q: What does the USING clause in the Oracle SQL statement? Q: When can you not use the USING clause? Q: What is the advantage of the NATURAL JOIN syntax? Q: What does the CROSS JOIN syntax in the Oracle SQL statement? Q: What does the IN clause in the Oracle SQL statement? Q: What do the OUTER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN syntax in the Oracle SQL statement? Q: How can you perform the FULL OUTER JOIN syntax using the Original Oracle syntax?
41
Lesson 09 Oracle9i: More SQL New Features Login as the Alaa user. SQL> CONNECT Alaa/test Set the linesize to 100 and the pagesize to 55. SQL> SET LINESIZE 100 PAGESIZE 55 WITH … AS statement If you have a query which it needs to process the same sub-query several times, you should consider using the WITH …AS clause in your statement. This process will help you a great deal of performance. The query will create a temporary table to query it over and over. Use the (WITH ... AS) statement to query all the department names that their total paid salaries are more than 1/3 of the total salary of the company. SQL> WITH summary_totals AS (SELECT dname, SUM(sal) AS totals FROM emp NATURAL JOIN dept GROUP BY dname) SELECT dname, totals FROM summary_totals WHERE totals > (SELECT SUM(totals)*1/3 FROM summary_totals) ORDER BY totals DESC SQL>/ Multiple columns in SQL statement You can use multiple columns to match the multiple columns returned from the subquery. Get the name of all employees who earn the maximum salary in their department. SQL> SELECT deptno, ename, job, sal FROM emp WHERE (deptno, sal) IN (SELECT deptno, MAX(sal) FROM emp GROUP BY deptno) / Inline View If you have a sub-query in a FROM clause in the Oracle SQL statement, is called an inline view. Use the (inline view) to query all of the employee names, jobs, and salaries where their salary is more than 10% of the total company paid salary. SQL> SELECT ename, job, sal
42
FROM (SELECT ename, job, sal FROM emp WHERE sal > (SELECT SUM(sal) * .1 FROM emp) ORDER BY 3) / This is an example of the inline view query. MERGE statement To show an example of how the MERGE statement works, pay careful attention to the following demonstration. First, create a table with two columns. SQL> CREATE TABLE Merge2Here (c1 NUMBER, c2 VARCHAR2(10)) / Write a stored procedure to populate the table. SQL> BEGIN SQL> FOR i IN 1..10 LOOP SQL> INSERT INTO Merge2Here VALUES (i, i+10); SQL> END LOOP; SQL> COMMIT; SQL> END; SQL> / Then, create a second table with three columns. SQL> CREATE TABLE MoreGoodRows (cc1 NUMBER, cc2 VARCHAR2(10), cc3 NUMBER) / Write a stored procedure to populate it. SQL> BEGIN SQL> FOR i IN 1..3 LOOP SQL> INSERT INTO MoreGoodRows VALUES (i, i+10, i+20); SQL> END LOOP; SQL> FOR i IN 104..110 LOOP SQL> INSERT INTO MoreGoodRows VALUES (i, i+10, i+20); SQL> END LOOP; SQL> COMMIT; SQL> END; SQL> / Now, query the Merge2Here table. SQL> SELECT * FROM Merge2Here / Also, query the MoreGoodRows table. SQL> SELECT * FROM MoreGoodRows / Take notes about the data in these two tables. Use the MERGE statement to merge the MoreGoodRows table into the Merge2Here table using the first column as a join column. If there was a match, you should replace the third
43 column value of MoreGoodRows with the second column of the Merge2Here table. On the other hand, if there was no match, just insert the first two columns of d ata into the Merge2Here table. SQL> MERGE INTO Merge2Here USING MoreGoodRows ON (c1 = cc1) WHEN MATCHED THEN UPDATE SET c2 = cc3 WHEN NOT MATCHED THEN INSERT VALUES (cc1, cc2) / Now, query the Merge2Here table. SQL> SELECT * FROM Merge2Here SQL> / Notice that the first three rows were changed and the last seven rows were added. Then, query the MoreGoodRows table. SQL> SELECT * FROM MoreGoodRows SQL> / No changes on this table. Drop both the Merge2Here and MoreGoodRows tables. SQL> DROP TABLE Merge2Here SQL> / SQL> DROP TABLE MoreGoodRows SQL> / SQL> DELETE FROM dept WHERE deptno = 40 SQL> / SQL> COMMIT SQL> / You have dropped your tables so that you can do this Hands-On exercise over.
Questions: Q: When do you use the WITH … AS clause in the SQL statement? Q: How does the WITH … AS clause help your performance? Q: Write a query to list all the department names that their total paid salaries are more than 1/3 of the total salary of the company. Q: What are the multiple columns in the SQL statement? Where or how do you use them? Q: Write a SQL statement to query the name of all employees who earn the maximum salary in their department using the multiple columns syntax. Q: What is the inline view in the Oracle SQL statement? Q: Write a SQL statement to query all of the employee names, jobs, and salaries where their salary is more than 10% of the total company paid salary. Q: What does the MERGE statement in the SQL statement? Q: Can you update, insert, or delete any records while you are using the MERGE statement?
44
Lesson 10 Materialized Views - The view that stores data Introduction Your organization is growing, and their managers from different states are in need of data warehousing. You should pre-compute database information into a summary data aggregated to be easily understood. They are asking you to create a materialized view to pre-compute and store information manually on the number of employees with their total salary paid for each department (to project the employees’ payment budget). They want, when new employees and their salaries are entered into the base tables, to be able to manually update their materialized view with its summary, aggregated data, to reflect these changes from the base tables. They should be able to download a subset of data from the company’s headquarter table to another company associated servers, assuming that their system is not in a distributed environment. In a distributed environment, they could use materialized views to replicate data at distributed sites and synchronize updates between these sites. Now, the company wants the ability to create an aggregated snapshot for the accounting department only. The required data include the total number of employees, and the total paid salaries for the accounting department only. Once the aggregated snapshot was created, it should define, in such a way, that it can update the materialized view once a month without any human intervention. The snapshot must be refreshed, based on the rule that your client asked. Your assignments are: 1- To create a materialized view log, 2- To create a materialized view, 3- To test your materialized view, 4- To execute or update you materialized view manually, 5- To create materialized view and updated monthly, 6- To test it, and 7- To check and test your monthly job. Topics: Creating a materialized view log Listing the snapshot logs' view MLOG$_EMP Creating a materialized view Granting materialized view privilege Listing the user's materialized views Executing the REFRESH procedure Using DBMS_SNAPSHOT package Executing the REMOVE procedure
45 Listing the materialized view in the job’s queue Deleting the materialized view’s job Dropping the created Materialized view Revoking the materialized view privilege Log in to sqlplus as the Alaa user. SQL> CONNECT Alaa/test To do this Hands-On exercise, you must first create a materialized view log. CREATE MATERIALIZED VIEW ... PRIMARY KEY, ROWID, and INCLUDING NEW VALUES A materialized view (MVIEW) is a replica of a target master from a single point in time. The concept was first introduced with Oracle7 termed as SNAPSHOT. You use Materialized Views to create summaries in a data warehouse environment or replicate a data in a distributed environment. In data warehouses, you can use materialized views to pre-compute and store aggregated data such as the sum of sales. In distributed environments, you can use materialized views to replicate data from a master site to other distributed sites. Remember that the following datatypes in the Oracle database are not supported in snapshot replication: - LONG - LONG RAW - BFILE - UROWID (cot supported for updatable snapshots) Materialized View Types Read-Only Materialized Views You can not perform DML on snapshots in this category.
Updatable Materialized Views You can insert, update and delete rows of the updatable materialized view. Example: create materialized view MVIEW_test refresh fast start with sysdate next sysdate+1 FOR UPDATE as select * from mytable@california; Subquery Materialized Views Materialized views that are created with sub-queries in the WHERE clause of the mview query are referred to as subquery materialized views. Example: CREATE MATERIALIZED VIEW myorders REFRESH FAST AS SELECT * FROM myorder@california o WHERE EXISTS
46 (SELECT * FROM mycustomer@dc c WHERE c.customer_id = o.customer_id AND c.credit_limit > 10000);
Rowid vs. Primary Key Materialized Views Fast refresh requires association between rows at snapshot and master sites. Snapshots that use ROWIDs to refresh are called ROWID snapshots while those that use primary keys are called primary key snapshots. Example: CREATE MATERIALIZED VIEW orders REFRESH WITH ROWID AS SELECT * FROM orders@califonia; Create a materialized view log with a PRIMARY KEY, the ROWID, and INCLUDING NEW VALUES options. SQL> CREATE MATERIALIZED VIEW LOG ON emp WITH PRIMARY KEY, ROWID (deptno) INCLUDING NEW VALUES SQL> / Begin by setting your linesize to 1000. SQL> SET LINESIZE 1000; Query your snapshot logs' view. SQL> SELECT * FROM user_snapshot_logs SQL> / This result shows that your log was created successfully and named MLOG$_EMP. BUILD IMMEDIATE and REFRESH ON DEMAND Create a materialized view with the BUILD IMMEDIATE and REFRESH ON DEMAND options. Your materialized view should have the department number, number of employees, and total salaries paid to employees by department. d epartment. SQL> CREATE MATERIALIZED VIEW mv_sal BUILD IMMEDIATE REFRESH ON DEMAND AS SELECT deptno, COUNT(1) AS no_of_emp, SUM(sal) AS salary FROM emp GROUP BY deptno SQL> / Notice on the error message INSUFFICIENT PRIVILEGE. In order to create a materialized view, you must be granted a CREATE MATERIALIZED VIEW privilege. Connect to SQLPLUS as system/manager. SQL> CONNECT system/manager GRANT CREATE MATERIALZED VIEW …
47 Grant the CREATE MATERIALIZED VIEW privilege to the Alaa user. SQL> GRANT CREATE MATERIALIZED VIEW TO Alaa SQL> / Now, log back in as the Alaa user. SQL> CONNECT Alaa/test This time you should be able to create a materialized view without any problems. Create your materialized view again. SQL> CREATE MATERIALIZED VIEW mv_sal BUILD IMMEDIATE REFRESH ON DEMAND AS SELECT deptno, COUNT(1) AS no_of_emp, SUM(sal) AS salary FROM emp GROUP BY deptno SQL> / Note that this time the materialized view was created successfully. Query the user's materialized views. SQL> SELECT * FROM user_mviews SQL> / Query the mv_sal materialized view. SQL> SELECT * FROM mv_sal SQL> / Note the total salary paid to department 10. Query all the information about empno 7934. SQL> SELECT * FROM emp WHERE empno = 7934 SQL> / Make a note about her salary. Change her salary to 5000 and save it into the EMP table. SQL> UPDATE emp SET sal = 5000 WHERE empno = 7934 SQL> / SQL> COMMIT SQL> / The salary column was changed. Query the mv_sal materialized view. SQL> SELECT * FROM mv_sal SQL> / Notice that there are no changes made on the total salary in department 10. DBMS_SNAPSHOT package and REFRESH option Refresh the mv_sal materialized view by executing the REFRESH procedure at the DBMS_SNAPSHOT package and use the letter C for the complete option. SQL> EXECUTE dbms_snapshot.refresh('mv_sal','C') SQL> / Now, query your mv_sal materialized view. SQL> SELECT * FROM mv_sal
48
SQL> / Notice that the total salary paid to department 10 was changed. In fact, you have manually updated the materialized view. START WITH SYSDATE NEXT … Now, let's create a materialized view named mv_account that would be updated every month automatically without any human intervention. SQL> CREATE MATERIALIZED VIEW mv_account BUILD IMMEDIATE REFRESH FORCE START WITH SYSDATE NEXT (SYSDATE+30) WITH PRIMARY KEY AS SELECT * FROM emp WHERE deptno = 10 SQL> / Notice that the START WITH SYSDATE option will create an immediate data, and the NEXT (SYSDATE+30) option will update the table every 30 days. Check to see how many materialized views you have created. SQL> SELECT * FROM user_mviews SQL> / you got one more materialized view. Query the mv_account materialized view. SQL> SELECT * FROM mv_account SQL> / Insert a record to your EMP table. SQL> INSERT INTO emp VALUES (9999,'John','Kazerooni',7782,'04-Apr-02',1400,500,10) SQL> / SQL> COMMIT SQL> / A record was added. Since you have 30 days to see your changes, you can update the mv_account materialized view by executing the REFRESH procedure from the DBMS_SNAPSHOT package and use the C option for the complete option. SQL> EXECUTE dbms_snapshot.refresh('mv_account','C') SQL> / Now, query the mv_account table. SQL> SELECT * FROM mv_account SQL> / Notice that employee number 9999 was added to the materialized view. USER_JOBS table Check your jobs' queue. SQL> SELECT * FROM user_jobs SQL> / Notice that this job is going to run each month. Make a note of it's job number. DBMS_JOB package and REMOVE procedure
49 Execute the REMOVE procedure from the DBMS_JOB package to delete a job from the job's queue. Use the runtime variable to enter your job number. SQL> EXECUTE dbms_job.remove(&Enter_job_number) SQL> / Delete the added employee number 8888 and 9999 and change the employee's salary back to 1000. SQL> DELETE FROM emp WHERE empno IN (8888,9999); UPDATE emp SET sal = 1000 WHERE empno = 7934 SQL> / SQL> COMMIT SQL> / Drop all of the created Materialized views. SQL> DROP MATERIALIZED VIEW LOG ON emp SQL> / SQL> DROP MATERIALIZED VIEW mv_sal SQL> / SQL> DROP MATERIALIZED VIEW mv_account SQL> / REVOKE CREATE MATERIALIZED VIEW … Log back in as system/manager and revoke the CREATE MATERIALIZED VIEW privilege from the ALAA user. SQL> CONNECT system/manager SQL> REVOKE CREATE MATERIALIZED VIEW FROM Alaa SQL> / you have now cleared your session, so that you can perform this excercise over and over again.
Questions: Q: What is a Materialized View? Q: What are the Materialized View types? Q: Write the difference between ROWID and PRIMARY KEY in the Materialized View. Q: What is the difference between a Materialized View and View? Q: When or why do you use a Materialized View? Q: What is a materialized view log? Q: What are the PRIMARY KEY and ROWID in the Materialized View Log? Q: What does the USER_SNAPSHOT_LOGS view contain? Q: Create a materialized view that contains the department number, number of employees, and total salaries paid to employees by department. Q: Who can create a materialized view? Q: What does the USER_MVIEWS view contain? Q: How do you refresh a materialized view? Q: What parameter should be used to update the materialized view every month automatically without human intervention? Q: What does the USER_JOBS view contain?
50 Q: How do you remove a job from the Oracle Job Queue? Q: How do you drop a materialized view log and a materialized view?
51
Lesson 11 The BREAK ON clause Introduction Your organization now wants to get a simple report from the following report layout. Unfortunately, they don’t have the “REPORTS builder” tool and you have to use sql*plus in order to fulfill their user requirements. The client asked you to query all employee names and their departments where their salaries are greater than $3,150.00, sorted by department. The report layout printout: ----------------Salary----------------Department Employee Salary -------------------- -------------------- ----------ACCOUNTING KING $5,250.00 ******************** ----------sum $5,250.00 SALES ALLEN $3,758.57 BLAKE $3,464.20 ******************** ----------sum $7,222.77 ------------ confidential -------------
Your assignments are: 1. To use TTITLE, COLUMN, COMPUTE and BREAK commands, 2. To query department name, employee names and their salaries, and 3. To have subtotal for each department. Topics: REPHEADER REPFOOTER COLUMN
HEADING
FORMAT a20 BREAK ON SKIP n COMPUTE SUM OF ON SPOOL HOST CLEAR BUFFER CLEAR COLUMNS CLEAR COMPUTE
52 Connect to SQLPLUS as the Alaa user. SQL> CONNECT Alaa/test Set the pagesize to 55 and the linesize to 80. SQL> SET PAGESIZE 55 SQL> SET LINESIZE 80 REPHEADER and REPFOOTER commands Make a report header and footer the way it was stated in the case study. SQL> REPHEADER '----------------Salary-----------------' SQL> REPFOOTER '------------ confidential -------------' COLUMN … HEADING … FORMAT …command Make your column heading and format as stated in the case study. SQL> COLUMN dname HEADING 'Department' FORMAT a20 SQL> COLUMN sal HEADING 'Salary' FORMAT $99,999.99 SQL> COLUMN ename HEADING 'Employee' FORMAT a20 BREAK and COMPUTE command Build a break on the department number and compute a sub-total for each department. Skip one line for each control break. SQL> BREAK ON dname SKIP 1 SQL> COMPUTE SUM OF sal ON dname SPOOL command Use the SPOOL command to populate a text file with any thing that you type in the SQLPLUS prompt or you query. You have to SPOOL off in order to be able to open the spool file. If you don’t spool off, you will see a zero byte written in the file and you will not be able to see any output data. Spool it on a file called salary.out in your c: drive. SQL> SPOOL c:.out Query your report based on your case study description. SQL> SELECT dname, ename, sal FROM emp e, dept d WHERE e.deptno = d.deptno AND sal > 3150 ORDER BY dname SQL> / Spool off, of the output. SQL> SPOOL OFF You can use the text editor to open your spool file. CLEAR command Note that all the values in REPHEADER, REPFOOTER, BUFFER, COLUMNS, COMPUTE and etc are going to stay the same during your open session. In order to clean them, you should use the CLEAR command for BUFFER, COLUMNS, and COMPUTE. And input NULL to REPHEADER and REPFOOTER. Clear the buffer, repheader, repfooter, and compute all of the columns from your session. SQL> REPHEADER '' SQL> REPFOOTER '' SQL> CLEAR BUFFER SQL> CLEAR COLUMNS SQL> CLEAR COMPUTE
53
Questions: Q: What does the BREAK ON clause in SQLPLUS? Q: What do the REPHEADER and REPFOOTER commands in SQLPLUS? Q: What does the following commands? COLUMN sal HEADING 'Salary' FORMAT $99,999.99 COLUMN ename HEADING 'Employee' FORMAT a20 REPHEADER ''
BREAK ON dname SKIP 1 COMPUTE SUM OF sal ON dname SPOOL c:.out SPOOL OFF REPFOOTER '' CLEAR BUFFER CLEAR COLUMNS CLEAR COMPUTE Q: What does the CLEAR command in SQLPLUS?
54
Lesson 12 UNION, INTERSET, and MINUS statements Introduction Your organization has another table that contains employee data for the accounting department only. You have been assigned to check the contents of the accounting table with the base EMP table. You need to: 1- List all of the records from the EMP table in the accounting department that are not in the accounting table. 2- List all of the records that are common in both tables. 3- Merge the two tables so that you can query all of the records with no duplicated records. Topics: UNION INTERSECT MINUS Connect to SQLPLUS as the Alaa user. SQL> CONNECT Alaa/test Set the linesize to 100 and the pagesize to 55. SQL> SET LINESIZE 100 PAGESIZE 55 Create a table named accounting and copy all of the accounting employees into it. SQL> CREATE TABLE accounting AS (SELECT * FROM emp WHERE deptno = 10) SQL> / Query the accounting table. SQL> SELECT * FROM accounting SQL> / Now, insert a new record into the accounting table. SQL> INSERT INTO accounting VALUES (9000,'Dana','Kazerooni',7782,'04-Apr-02',1500,null,10) SQL> / SQL> COMMIT; Insert a new record into the EMP table. SQL> INSERT INTO EMP VALUES (9999,'Borna','Kazerooni',7782,'04-Apr-02',1500,null,10) SQL> / SQL> COMMIT;
55 Query the accounting table again. SQL> SELECT * FROM accounting SQL> / Note the employee that was added to accounting table. Query the accounting employees from the EMP table. SQL> SELECT * FROM emp WHERE deptno = 10 SQL> / Note! The employee record was added to the EMP table. MINUS statement It will query all the records that are not matching against your base table. Find all of the records from the EMP table in the accounting department, that are not in the accounting table. SQL> SELECT * FROM emp WHERE deptno = 10 MINUS SELECT * FROM accounting SQL> / Notice that this is the record that you added into the EMP table. INTERSECT statement It will query all the records that match with the base table. It is the same as joining two tables. Search and list for all of the records that are common in both tables. SQL> SELECT * FROM emp WHERE deptno = 10 INTERSECT SELECT * FROM accounting SQL> / Notice the common records. UNION statement It will query all the records that match or not match with the base table. Merge the two tables so that you can query all of the records with no duplicated records. SQL> SELECT * FROM accounting UNION ALL SELECT * FROM emp WHERE deptno = 10 AND empno NOT IN (SELECT empno FROM accounting) SQL> / Now, drop the accounting table. SQL> DROP TABLE accounting SQL> / Delete the record which was added to the EMP table. SQL> DELETE FROM emp WHERE empno = 9999 SQL> / SQL> COMMIT;
Questions: Q: What does the UNION statement in the SQL statement?
56 Q: What does the INTERSET statement in the SQL statement? Q: What does the MINUS statement in the SQL statement?
57
Lesson 13 Troubleshooting Deleting duplicated records Introduction Your organization has a lot of duplicated records in their Account Employee table. This is a daily problem in any organization and you should be prepared to fix the problem. You have been assigned to clean all of the duplicated records. Begin by logging in as the Alaa user. SQL> CONNECT Alaa/test Set the linesize to 100 and the pagesize to 55. SQL> SET LINESIZE 100 PAGESIZE 55 CREATE TABLE AS … First, let's create a table with lots of duplicated records. Create a table named dup_emp and copy all of the EMP's records into it. SQL> CREATE TABLE dup_emp AS (SELECT * FROM emp) SQL> / Query the dup_emp table. SQL> SELECT * FROM dup_emp SQL> / INSERT INTO … SELECT command Insert all the accounting department rows into the dup_emp table. SQL> INSERT INTO dup_emp SELECT * FROM emp WHERE deptno = 10 SQL> / SQL> commit; Query all the duplicated records from the dup_emp table order by the employee name. SQL> SELECT * FROM dup_emp WHERE deptno = 10 ORDER BY ename SQL> / Notice that all of the employees of department 10 have been duplicated. Don’t try to understand the following PL/SQL statements. You will learn how to write a PL/SQL statement at the PL/SQL book. Using PL/SQL to delete duplicated records Write a procedure to delete all of the duplicated records. SQL> DECLARE SQL> -- Get the unique empno. SQL> CURSOR c_empno IS SQL> SELECT DISTINCT empno AS empno FROM dup_emp; SQL> -- Get all duplicated records for an employee. SQL> CURSOR c_duprec (p_empno NUMBER) IS
58
SQL> SELECT rowid FROM dup_emp WHERE empno = p_empno; SQL> first_one BOOLEAN; SQL> BEGIN SQL> FOR v_empno IN c_empno LOOP SQL> first_one := TRUE; SQL> FOR v_duprec IN c_duprec (v_empno.empno) LOOP SQL> IF NOT first_one THEN SQL> DELETE FROM dup_emp SQL> WHERE rowid = v_duprec.rowid; SQL> COMMIT; SQL> END IF; SQL> first_one := FALSE; SQL> END LOOP; SQL> END LOOP; SQL> END; SQL> / Query again the dup_emp table order by the department and employee number. Then check for duplication. SQL> SELECT * FROM dup_emp WHERE deptno = 10 ORDER BY ename SQL> / Notice that there are no duplicated records. Using SQL to delete duplicated records You could write a delete statement to remove all duplicated records. The following statement will delete all records that have the same employee number except the first one. In this time, you did not have any duplicated record to be deleted. SQL> DELETE FROM dup_emp WHERE ROWID IN (SELECT MAX(ROWID) FROM dup_emp GROUP BY empno HAVING COUNT (empno) > 1) SQL> / Drop the dup_emp table. SQL> DROP TABLE dup_emp SQL> /
Questions: Q: Why it is important to eliminate duplicate records? Q: What does the following SQL statement? SQL> DELETE FROM dup_emp WHERE ROWID IN (SELECT MAX(ROWID) FROM dup_emp GROUP BY empno
59
HAVING COUNT (empno) > 1) SQL> /
60
Lesson 14 Data Partitioning Introduction Your organization’s database is growing very fast. They want to have a database that contains each department in a separate tablespace. They projected that their employee’s table is growing to more than 100 million rows in each department. Also, upon their requirements, they want to have each department’s employees in a separate hard disk, and to be independent of other department employee’s data. Also, they want to be sure all the employees are in the same table. They want it to be partitioned in such away, that when they run a query against an employee’s information, they would scan at most on the department’s hard disk that he/she works, rather than the other department’s hard disks on non-partitioned table. You will need to choose a partition key. You have been told that the department’s employees are distributed equally among the partition table. There are two types of partitioning approaches that you, as a database designer, must choose (they are Local and Global). A Local index is one that is partitioned exactly like the table to which it belongs. A Global index, unlike local indexes, you should explicitly partition range boundaries using the “VALUE LESS THAN” methods. They want you to create a “Local Index” for the partition table. Your assignments are: 1- To create folder for each department, 2- To create tablespace with following specification for each department: a. Automatic Allocation for an unlimited size, b. Generates redo logs and recoverable, and c. Type must be permanent. 3- To create the partition table, 4- To copy the EMP table into the partition table, 5- To check the table statistics, 6- To create a local partition indexes, and 7- To check the index table statistics. Connect to SQLPLUS as the Alaa user. SQL> CONNECT Alaa/test Set the linesize to 100 and the pagesize to 55. SQL> SET LINESIZE 100 PAGESIZE 55 Creating OS directory from DOS HOST command Create folders for the accounting department, Reseach department, Sales department, and other departments. Make sure that you have at least 50 Megabytes of hard drive space available on your hard disk. The HOST command tells the SQLPLUS tool that the command is an operating system command. It will execute it at OS level.
61
SQL> HOST mkdir c:dept10 SQL> HOST mkdir c:dept20 SQL> HOST mkdir c:dept30 SQL> HOST mkdir c:deptxx Your folders were created. CREATE TABLESPACE Tablespace is a logical place that you will store your object in it. An Oracle user can store all their tables in their default tablespace. Notice that all procedures, functions and packages will be stored in the SYSTEM tablespace and you have no control to enforce that to any other tablespace. Create a tablespace for the accounting department with the following options: 1- Automatic Allocation, 2- Generates the redo logs, 3- Recoverable, and 4- Tablespace type must be permanent. SQL> CREATE TABLESPACE dept10ts LOGGING DATAFILE 'c:dept10ts_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 5K MAXSIZE UNLIMITED SQL> / Notice that you don't have the sufficient privilege to create a tablespace. Now, connect to SQLPLUS as the system/manager user SQL> CONNECT system/manager GRANT CREATE TABLESPACE and DROP TABLESPACE … Grant the CREATE TABLESPACE and DROP TABLESPACE privilege to the Alaa user. SQL> GRANT CREATE TABLESPACE TO Alaa SQL> / SQL> GRANT DROP TABLESPACE TO Alaa SQL> / The CREATE TABLESPACE privilege and DROP TABLESPACE privilege was granted to the ALAA user. Now, go back and connect to SQLPLUS as the Alaa user. SQL> CONNECT Alaa/test Create tablespaces for the accounting department. SQL> CREATE TABLESPACE dept10ts LOGGING DATAFILE 'c:dept10ts_01.dbf' SIZE 10m AUTOEXTEND ON NEXT 5k MAXSIZE UNLIMITED SQL> / The accounting department tablespace was created.
62 Create tablespaces for the research department. SQL> CREATE TABLESPACE dept20ts LOGGING DATAFILE 'c:dept20ts_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 5K MAXSIZE UNLIMITED SQL> / Here, the research department tablespace was created. Create tablespaces for the sales department. SQL> CREATE TABLESPACE dept30ts LOGGING DATAFILE 'c:dept30ts_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 5K MAXSIZE UNLIMITED SQL> / And here, the sales department tablespace was created. Create tablespaces for the other departments. SQL> CREATE TABLESPACE deptxxts LOGGING DATAFILE 'c:deptxx_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 5K MAXSIZE UNLIMITED SQL> / And now, the other departments tablespace was created. CREATE partition table PARTITION and MAXVALUE options Now, create a range-based partitioning table named p_emp. Make sure that the data entry of the accounting department goes to the dept10ts tablespace, the data entry of the research department goes to the dept20ts tablespace, etc. SQL> CREATE TABLE p_emp ( empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sale NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)) STORAGE (INITIAL 5K NEXT 5K
63
PCTINCREASE 0) PARTITION BY RANGE (deptno) (PARTITION dept10 VALUES LESS THAN (20) TABLESPACE dept10ts, PARTITION dept20 VALUES LESS THAN (30) TABLESPACE dept20ts, PARTITION dept30 VALUES LESS THAN (40) TABLESPACE dept30ts, PARTITION deptxx VALUES LESS THAN (MAXVALUE) TABLESPACE deptxxts) SQL> / Your partition table should be created. Notice that the VALUES LESS THAN clause indicates the partition key value must be less then its assigned value in order to be illegible for any DML transaction on its assigned tab lespace. Insert records into your partition table (p_emp). SQL> INSERT INTO p_emp SELECT * FROM emp SQL> / SQL> COMMIT SQL> / ANALZE TABLE COMPUTE STATISTICS Analyze your partition table. SQL> ANALYZE TABLE p_emp COMPUTE STATISTICS SQL> / USER_TAB_PARTITIONS Partition_name, tablespace_name and num_rows columns Query the PARTITION_NAME, TABLESPACE_NAME, and NUM_ROWS columns from the dictionary view. SQL> SELECT partition_name, tablespace_name, num_rows FROM user_tab_partitions WHERE table_name = 'P_EMP' SQL> / Notice that in the accounting tablespace you have three records and research tablespace you have 5 records, etc. Compare these two tables, EMP and P_EMP. First, query the EMP table. SQL> SELECT * FROM emp SQL> / Now, query the p_emp table. SQL> SELECT * FROM p_emp SQL> / Can you see the difference? Notice that the query scanned the partition table by the sequence of the tablespaces. That is why the list is sorted by department number.
64
SELECT … PARTITION () Query the accounting employees from the partition table. Use the partition option. SQL> SELECT * FROM p_emp PARTITION (dept10) SQL> / Notice that your query is only against the accounting department partition and you don't have to have a WHERE clause. Query employee number 7900 from the sales department. SQL> SELECT * FROM p_emp PARTITION (dept30) WHERE empno = 7900 SQL> / CREATE INDEX … LOCAL Create a local partition index. SQL> CREATE INDEX p_emp_ind ON p_emp (deptno) LOCAL SQL> / Analyze the partition table index. SQL> ANALYZE INDEX p_emp_ind COMPUTE STATISTICS SQL> / Query the statistics on the number of records in each partition. SQL> SELECT index_name, partition_name, num_rows FROM user_ind_partitions SQL> / DROP TABLE … CASCADE CONSTRAINTS Drop the table and all of the tablespaces. SQL> DROP TABLE p_emp CASCADE CONSTRAINTS SQL> / DROP TABLESPACE … INCLUDING CONTENTS SQL> DROP TABLESPACE dept10ts INCLUDING CONTENTS SQL> / SQL> DROP TABLESPACE dept20ts INCLUDING CONTENTS SQL> / SQL> DROP TABLESPACE dept30ts INCLUDING CONTENTS SQL> / SQL> DROP TABLESPACE deptxxts INCLUDING CONTENTS SQL> / Now, connect to SQLPLUS as the system/manager user SQL> CONNECT system/manager REVOKE CREATE TABLESPACE privileges Revoke the CREATE TABLESPACE and DROP TABLESPACE privileges from the ALAA user. SQL> REVOKE CREATE TABLESPACE FROM Alaa SQL> / REVOKE DROP TABLESPACE privileges SQL> REVOKE DROP TABLESPACE FROM Alaa SQL> / RMDIR using /S /Q options
65 Remove all of the created folders. Use the /S option if folder is not empty. And use the /Q option for the QUIET MODE option. SQL> HOST rmdir c:dept10 /S /Q SQL> HOST rmdir c:dept20 /S /Q SQL> HOST rmdir c:dept30 /S /Q SQL> HOST rmdir c:deptxx /S /Q
Questions: Q: What is a data partitioning in the Oracle database? Q: When should you use data partitioning? Q: What is the advantage of using a data partitioning? Q: What is a partition key? Q: What is a local index in the data partitioning? Q: What is a global index in the data partitioning? Q: What are the differences between local and global indexes? Q: How does the ‘VALUE LESS THAN’ method work in the data partitioning? Q: Why do you need multiple tablespaces? Q: Create a range-based partitioning table named p_emp. Be sure that the data entry of the each department goes to its own provided tablespaces such as the accounting department goes to the dept10ts tablespace, the data entry of the research department goes to the dept20ts tablespace, etc. Q: What does the MAXVALUE parameter mean in the data partitioning? Q: How do you analyze a partition table? Q: What does the USER_TAB_PARTITIONS view contain? Q: Write a query to list the accounting employees from the partition table. Use the partition option. Q: Write a query to list employee number 7900 from the sales department? Q: How do you create a local partition index? Q: How do you analyze a partition table index? Q: What does the USER_IND_PARTITIONS view contain?
66
Lesson 15 The ROLLUP and CUBE function Hands-On Data warehousing is a focus in your organization. Management needs more reports in order to truly understand their data. You have been assigned to write simple SQL statement using the “ROLLUP” operator to create salary subtotals for each type of job per year. Also, you should use the CUBE operator to enable a simple statement to calculate all possible combinations of the types of jobs and year for the research department only. Notice: A select statement using the ROLLUP operator returns bo th ‘regular rows’ and ‘super-aggregate rows.’ Super-aggregate rows are rows that con tain a sub-total value. A select statement using the CUBE operator returns cross-tabulation values, thus produces totals in all possible dimensions, and is used for warehousing aggregated data reports. The report layout printout: The ROLLUP layout output: Year JOB SUM(SAL) COUNT(*) ---- --------- ---------- ---------80 CLERK 1050 1 80 1050 1 81 ANALYST 3150 1 81 CLERK 997.5 1 81 MANAGER 9160.45 3 81 PRESIDENT 5250 1 81 SALESMAN 11609.82 4 81 30167.77 10 82 CLERK 1365 1 82 1365 1 87 ANALYST 2000 1 87 CLERK 1337.06 1 87 3337.06 2 35919.83 14 The CUBE layout output: Year JOB SUM(SAL) COUNT(*) ---- --------- ---------- ---------80 CLERK 1050 1 80 1050 1 81 ANALYST 3150 1 81 MANAGER 3123.75 1
67 81 6273.75 2 87 ANALYST 2000 1 87 CLERK 1337.06 1 87 3337.06 2 ANALYST 5150 2 CLERK 2387.06 2 MANAGER 3123.75 1 10660.81 5
Introduction Oracle added the ROLLUP and CUBE functions in Oracle9i to the GROUP BY clause of the SELECT statement. The ROLLUP function creates subtotals at any level of aggregation from the most detailed up to a grand total. The CUBE function is similar to the ROLLUP function, but it calculates all possible combinations of subtotals. Look at the following example to have a better understanding of these two powerful functions. We use these two functions a lot when we have a warehousing application or needs. Connect to SQL*PLUS as the Alaa user. SQL> CONNECT Alaa/test Set the pagesize to 55 and the linesize to 100. SQL> SET PAGESIZE 55 SQL> SET LINESIZE 100 Alter the EMP table, and add a column to store the year that employees were hired. SQL> ALTER TABLE emp ADD (year VARCHAR2(2)) SQL> / Set the column year heading. SQL> COLUMN year FORMAT a4 HEADING "Year" Update the EMP table to set the year each employee was hired, into the newly created YEAR column. SQL> UPDATE emp SET year = TO_CHAR(hiredate,'YY') SQL> / SQL> commit; ROLLUP grouping option Query an aggregation sum report for each job, in each year, using the ROLLUP grouping option. SQL> SELECT year, job, SUM(sal), COUNT(*) FROM emp GROUP BY ROLLUP (year, job) SQL> / This is an example of a GROUP BY ROLLUP option. CUBE grouping option Query an aggregation sum report for each job, in each year using the CUBE option group for the research department only. SQL> SELECT year, job, SUM(sal), COUNT(*) FROM emp WHERE deptno = 20 GROUP BY CUBE (year, job)
68
SQL> / This is an example of a GROUP BY CUBE option. DROP TABLE … DROP column Drop the year column SQL> ALTER TABLE emp DROP (year) SQL> / You have dropped the year column so that you can perform this exercise over and over again.
Questions: Q: What does the ROLLUP operator? Q: What does the CUBE operator? Q: What are the differences between the CUBE and ROLLUP functions? Q: What environments may use the CUBE and ROLLUP functions most? Q: Write a query to list an aggregation sum report for each job, in each year, using the ROLLUP grouping option. Q: Write a query to list an aggregation sum report for each job, in each year, using the CUBE grouping option.
69
Lesson 16 Defining Objects and Collection Types Hands-On introduction Your organization is going to gather information about all the employees’ families in order to keep track of the number of the employee kids, kids’ names and their date of birth. You have been assigned to use the nested object tables for the employees’ family. You should use the “CONSTRUCTOR” operator to refer to the nested table with the “THE” syntax. Your client should be able to insert a record into the nested table directly or update the nested table, and be able to get individual elements from a nested object table using the object columns. Your assignments are: 1- To use nested object tables, 2- To create a record object type, 3- To create a table objects type, 4- To create a table for employee’s table (EMP_FAMILY), 5- To insert record into table, 6- To query information from table, 7- To update information from table, and 8- To delete information from table.
Connect to SQLPLUS as the Alaa user. SQL> CONNECT Alaa/test Start your session by setting the pagesize to 55 and the linesize to 100. Also, change the object_name column format to only 20 characters long. SQL> SET PAGESIZE 55 LINESIZE 100 SQL> COLUMN object_name FORMAT a20 This section contains examples of creating and using user-defined object types and collection types in the Oracle database. CREATE TYPE … AS OBJECT … Create an object type with two columns to hold the employee's child name and date of birth and name it employee_kids. SQL> CREATE TYPE employee_kids AS OBJECT ( NAME VARCHAR2(30), dob DATE ) SQL> / CREATE TYPE … IS TABLE OF … Create a table type using employee_kids and name it employee_kids_table. SQL> CREATE TYPE employee_kids_table IS TABLE OF employee_kids SQL> /
70 Query all of the object types that you created. SQL> SELECT object_name FROM user_objects WHERE object_type = 'TYPE' SQL> / CREATE TABLE … NESTED TABLE … Create the emp_family table containing the kids column with a type of employee_kids_table. SQL> CREATE TABLE emp_family (empno NUMBER, kids employee_kids_table) NESTED TABLE kids STORE AS nested_employee_kids_table SQL> / This is an example of a nested table column. In this column, you will store the name and birth of an employees' child. INSET INTO … VALUES (object_type(), …) Insert two rows into the emp_family table. SQL> INSERT INTO emp_family VALUES (7900, employee_kids_table()) SQL> / SQL> INSERT INTO emp_family VALUES (7788, employee_kids_table()) SQL> / SQL> COMMIT; Note that the constructor creates an empty nested table as opposed to leaving it null. Notice that without using the constructor, it is not po ssible to refer to the nested table with the "THE" clause. Insert another row into the emp_family table, while specifying three employee kids for the nested table at the same time. SQL> INSERT INTO emp_family VALUES (7902, employee_kids_table ( employee_kids('David','08-AUG-01'), employee_kids('Peter','10-JUN-88'), employee_kids('Mark','30-OCT-92') ) ) SQL> / Now, query the emp_family table. SQL> SELECT * FROM emp_family SQL> / Notice that the names David, Peter, and Mark were added. Describe the emp_family table SQL> desc emp_family INSERT using “THE” sub-query Now insert directly into the nested table. The "THE" sub-query is used to identify the nested table to INSERT INTO. SQL> INSERT INTO THE(SELECT kids FROM emp_family WHERE empno = 7900)
71
VALUES ('Fred','10-SEP-89') SQL> / SQL> INSERT INTO THE(SELECT kids FROM emp_family WHERE empno = 7900) VALUES ('Sue','10-DEC-99') SQL> / SQL> commit SQL> / Note that when using this method only one row may be inserted into the nested table at a time - as would also be the case if you were inserting rows into any table. Set off the record separator and then query the emp_family table. SQL> SET RECSEP OFF SQL> COLUMN KIDS FORMAT A55 WORD SQL> SELECT * FROM emp_family SQL> / Notice that the names FRED and SUE were added to the KIDS nested table. Now query directly from the nested table. The "THE" sub-query is used to identify the nested table to query. Query the children names of employee number 7788. SQL> SELECT name FROM THE(SELECT kids FROM emp_family WHERE empno = 7788) SQL> / Notice that this employee does not have any kids. UPDATE … SET … = object_type() Use an UPDATE statement, to change the whole nested table for a given row, in the "emp_family" table. SQL> UPDATE emp_family SET kids = employee_kids_table( employee_kids('Sara','08-OCT-88')) WHERE empno = 7788 SQL> / SQL> COMMIT SQL> / Now, query the emp_family table. SQL> SELECT * FROM emp_family SQL> / Notice that the KIDS nested table was replaced and Sara was inserted into the kids table. UPDATE THE (SELECT …) Now, let's update a single element of the nested table. Update the David name to Dana where the employee number is 7902. SQL> UPDATE THE(SELECT kids FROM emp_family WHERE empno=7902) SET name = 'Dana' WHERE name = 'David' SQL> / SQL> COMMIT; The "THE" sub-query is used to identify the KIDS nested table.
72 Query the emp_family table to see the changes. SQL> SELECT * FROM emp_family SQL> / David was changed to Dana. CREATE UNIQUE INDEX … Add a unique index to the nested table. Note that we must include the nested_table_id column in this case. SQL> CREATE UNIQUE INDEX i_nested_employee_kids_table ON nested_employee_kids_table(nested_table_id,name) SQL> / Insert the duplicated record into the nested table. SQL> INSERT INTO THE(SELECT kids FROM emp_family WHERE empno = 7900) VALUES ('Sue','10-DEC-99') SQL> /
UNIQUE CONSTRAINT VIOLATION As you can see, you get a UNIQUE CONSTRAINT VIOLATION message. DROP TYPE statement Drop the emp_family, employee_kids_table, and employee_kids tables. SQL> DROP TABLE emp_family SQL> / SQL> DROP TYPE employee_kids_table SQL> / SQL> DROP TYPE employee_kids SQL> / You have dropped the objects so that you can practice this Hands-On over.
Questions: Q: What is an object type? Q: What is a collection object? Q: Create an object type with two columns to hold the employee's child name and date of birth and name it employee_kids . Q: Create a table type using employee_kids and name it employee_kids_table. Q: Create the emp_family table containing the kid’s column with a type of employee_kids_table. Q: How do you insert a record in the object type? Q: What is the constructor? Q: What is the ‘THE’ sub-query? Q: How do you query a record using the ‘THE’ sub-query? Q: What is a nested table? Q: How do you insert a record to a nested table? Q: How do you update a record to nested table? Q: How do you add a unique index to a nested table?
73
Lesson 17 Replicating data with a NESTED TABLE collection type Hands-On Introduction Today, a lot of company wants to replicate their data for a reason of backup and recovery, accessibility, performance, etc. This hand-on is intended to introduce the enhancement in Oracle9i to teach how to replicate an object from location “A” to “B” based on a table with a NESTED TABLE collection type. We will learn how to create a materialized view based on a table with a NESTED TABLE collection type. We assume that you have two sites (Master Site with a service name of SITEA and Materialized View Site with a service name of SITEB). Also we assume that we have user schema called REPLICATOR in both sites.
CONNECT …/…@... STEPS to implement replication: Step 1: Connect to “SITEA” (Master Site) as the REPLICATOR user. SQL> CONNECT replicator/…@SITEA CREATE TYPE … AS OBJECT … Step 2: Create the “address_book_type_object” object. Then create a nested table type from created object. SQL> CREATE OR REPLACE TYPE address_book_type_object AS OBJECT ( id_address NUMBER(1), address VARCHAR2(20)); CREATE TYPE … AS TABLE OF … SQL> CREATE TYPE address_book_nested_type AS TABLE OF address_book_type_object; CREATE TABLE … NESTED TABLE … Step 3: Create the “ADDRESS_BOOK” table with created “ADDRESS_BOOK_NESTED_TYPE” type. SQL> CREATE TABLE address_book ( id NUMBER(10) CONSTRAINT address_book_pk PRIMARY KEY, first_name VARCHAR2(15), last_name VARCHAR2(15), address_obj address_book_nested_type)
74
NESTED TABLE address_obj STORE AS address_nested ((PRIMARY KEY (NESTED_TABLE_ID, id_address))); CREATE MATERIALIZED VIEW LOG … Step 4: Now, create a Materialized View Log. SQL> CREATE MATERIALIZED VIEW LOG ON address_book; ALTER MATERIALIZED VIEW LOG ON address_book ADD(address_obj); SQL> CREATE MATERIALIZED VIEW LOG ON address_nested WITH PRIMARY KEY; INSERT INTO … VALUES (object_type(),…) Step 5: Insert some records into your address book table and then commit the transaction. SQL> INSERT INTO address_book VALUES ('100','Borna','Kaz', address_book_nested_type( address_book_type_object (1,‘Company Name’), address_book_type_object (2,'1576 Dunterry place') address_book_type_object (3, 'Orlando','FL, 22101'))); SQL> INSERT INTO address_book VALUES ('200','Dana','Kaz’, address_book_nested_type ( address_book_type_object (1,‘Company Name2’'))); SQL> COMMIT; Query the address book table. SQL> SELECT * FROM address_book; Step 6: Connect to SITEA again with a DBA privilege. SQL> CONNECT system/…@SITEA
Obtaining an Object ID (OID) Step 7: Obtain Object ID of created “ADDRESS_BOOK_TYPE” object. SQL> SELECT OWNER, TYPE_OID FROM DBA_TYPES WHERE TYPE_NAME LIKE 'ADDRESS%'; Step 8: Connect to SITEB (Materialized View Site) as the REPLICATOR user. SQL> CONNECT replicator/...@SITEB Step 9: Assuming that your object IDs are ‘XXXXXXXXXXXXXXXXXXXXX’ and ‘YYYYYYYYYYYYYYYYYYYYY. SQL> CREATE OR REPLACE TYPE address_book_type_object OID ‘XXXXXXXXXXXXXXXXXXXXX’ AS OBJECT ( id_address NUMBER(1), address VARCHAR2(20));
75
Creating type using OID SQL> CREATE TYPE address_book_nested_type OID ‘YYYYYYYYYYYYYYYYYYYYY’ AS TABLE OF address_book_type_object; CREATE MATERIALIZED VIEW …FROM ..@... Step 10: Now, create a Materialized View that it will be updated as soon as there is any changes on the Master table (ADDRESS_BOOK) by executing refresh procedure. SQL> CREATE MATERIALIZED VIEW address_book_mv NESTED TABLE address_obj STORE AS address_nested_mv REFRESH FAST AS SELECT * FROM replicator.address_book@SITEA; DBMS_MVIEW.REFRESH procedure Step 11: From now on, any changes in the ADDRESS_BOOK table in the Master Site (SITEA) will be replicated to the SITEB (Materialized View Site) by executing DBMS_MVIEW.REFRESH procedure on the SITEB. SQL> EXECUTE dbms_mview.refresh(‘address_book_mv’,’F’);
Questions: Q: What is a data replica? Q: What is the difference between a materialized view and a materialized view log? Q: What is an object ID? Q: How do you retrieve an object ID? Q: How do you use an object ID to create an object type?
76
Lesson 18 IMPORTANT-READ CAREFULLY The following are important definitions to remember: "DML" stands for Data Manipulation Language. SELECT, UPDATE, INSERT, and DELETE are the "DML" statements. A "SELECT" statement must consist of a "SELECT" and a "FROM" clause. A Mathematical Operation can be performed on the "SELECT" statement. The "DUAL" table would be used when a user does not want to pull data from a table but rather wants simply to use an arithmetic operation. It contains only one row and one column. The "NULL" value will be used when you don't know the value of the column. Remember that the Null value means "I don't know;" and any mathematical operation on the null value will return a null result. The null value function (NVL) can convert a null value an assigned value. A "column heading" can be used in place of the actual column name. If your column heading is case sensitive, a reserved word, or contains white space, it must be enclosed within double quotes. A "table alias" can be used in place of the actual table name to make a column a unique identifier. Two or more columns or strings can be concatenated using a double-pipe. The "ORDER BY" clause in a select statement will sort the order of a listed table. The "WHERE" clause can contain comparison operations linked together. The "LIKE" clause can be used for pattern matching. The "BETWEEN" clause would be used for a range operation. The "DECODE" function will match the column values with appropriate return values. It continues matching until it has identified all cases. Th e last variable is used for the default return value.
77 A "JOIN" table is: when a query obtains data from more than one table and merges the data together. You may join tables together using "inner join" or "equijoin", "outer join", and "self join". "inner join" also known as equijoin is an equality operation linking the data in the common columns. "outer join" returns data in one table even when there is no match in the other table. A "self join" is based on an equality operation linking the data to itself. A "Cartesian" product" is caused by joining "N" number of tables while you have less than "N-1" join conditions in the query. An "Anonymous Column" is caused by joining two tables when they have a common column name in them. You can use table aliases or table names next to a column name to avoid causing the "anonymous column." The "GROUP BY" clause will assist you in grouping data together. The "EXISTS" operation produces a "TRUE" or "FALSE" value based on the related sub-query data output. You may use the global column name in your sub-query. The "IN" operation produces a "TRUE" or "FALSE" value based on the related subquery data output or list of values. "MIN," "MAX," and "SUM" are grouping functions that allow you to perform operations on data in a column. You can assign a variable in a "SELECT" statement at run time with use of a runtime variable. A Table is a collection of records. Use the "VARCHAR2" datatype when your input data string varies and does not exceed more than 2000 characters. Use the "CHAR" datatype when your input data string is fixed and does not exceed more than 2000 characters. If your input data is number, use the "NUMBER" datatype. The "DATE" datatype should be used when your input data is "date", "time", or "date and time".
78 The "RAW" datatype should be used when your input data contains binary data and does not exceed more than 2000 bytes. If your input data contains text data and does not exceed more than 2 gig, use the "LONG" datatype. The "LONG RAW" datatype is used if your input data is binary and does not exceed more than 2 Gig. Use the "ROWID" datatype when your application references to the "rowid" of a table. The "BLOB" (Binary Large Object) datatype would be used for binary long objects and can store up to 4 gig. Use the "CLOB" (Character Large Object) datatype if you have to store a book in a column. Its size should not exceed more than 4 gig. Try to use "CLOB" instead of the "LONG" datatype. It is searchable; also more than one column can be defined as Large Object in a table. The "BFILE" datatype would be used for the large external files. The content of this column points to system files. The DATA DICTIONARY is a repository of all the databa se objects that were created by different schemas. All the information about the database objects is stored in the data dictionary. You will retrieve the data dictionary information using the data dictionary views. DDL" stands for Data Definition Language. CREATE TABLE, CREATE USER, DROP TABLE, ALTER TABLE are examples of the DDL statements. The "ALTER" command changes an object. The "DROP" command removes an object. The "TRUNCATE" or "DELETE" command removes records from an object. When you use the truncate statement, the "high watermark" will change to the beginning of the table. The truncate statement is a "DDL" statement; and on all DDL statements, the commit is implicit. That is the reason that you can not rollback on the truncate statement. Also, when a table is removed all its indexes, constraints, and references will be removed as well. The Oracle9i ANSI standard JOIN syntax You can use Oracle9i ANSI standard JOIN syntax to join the contents of two or more tables together in a single result according to the following syntax.
79 Syntax: SELECT col1, col2 FROM table1 JOIN table2 ON condition; The ANSI standard NATURAL JOIN syntax A natural join is a join between two or more tables where Oracle joins the tables according to the column(s) in the two or more tables sharing the same name with the following syntax. Syntax: SELECT col1, col2 FROM table1 NATURAL JOIN table2; The USING clause You can use Oracle9i ANSI standard JOIN syntax to join the contents of two or more tables together in a single result according to the columns in the two tables sharing the same name and be used in the USING clause with the following syntax. Syntax: SELECT col1, col2 FROM table1 JOIN table2 USING (col); The ANSI standard CROSS JOIN syntax A cross-join is produced when you use the CROSS keyword in your ANSI/ISO compliant join query. You use it when you want to retrieve a Cartesian product. Syntax: SELECT col1, col2 FROM table1 CROSS JOIN table2; The OUTER JOIN clause A OUTER JOIN is a join between two tables where you want to see information from tables even when no corresponding records exist in the common column. You can have RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN. Syntax: SELECT col1, col2 FROM table1 RIGHT/LEFT/FULL OUTER JOIN table2; RIGHT OUTER JOIN A RIGHT OUTER JOIN is a join between two tables where you want to see information from table on the right side even when no corresponding records exist in the common column. Syntax: SELECT col1, col2 FROM table1 RIGHT OUTER JOIN table2; LEFT OUTER JOIN
80 A LEFT OUTER JOIN is a join between two tables where you want to see information from table on the left side even when no corresponding records exist in the common column. Syntax: SELECT col1, col2 FROM table1 LEFT OUTER JOIN table2; FULL OUTER JOIN A FULL OUTER JOIN is a join between two tables where you want to see information from both tables on the left and right sides even when no corresponding records exist in the common column. Syntax: SELECT col1, col2 FROM table1 FULL OUTER JOIN table2; The (WITH name AS) statement Oracle9i provides you with the WITH clause that lets you factor out the sub-query, give it a name, then reference that name multiple times within the original complex query. The (inline view) A sub-query that appears in the FROM clause is called an inline view. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. The MERGE statement Oracle9i provides you with the MERGE statement so that you can identify a table into which you would like to update data in an existing row or add new data if the row does not already exist. Materialized View Unlike an ordinary view, which only contains an SQL statement, a materialized view contains the rows of data resulting from an SQL query against one or more base tables. The materialized view can be set up to automatically keep itself in synch with those base tables. Materialized view log Whenever a change is made to one of the underlying base tables; the database stores a log on each change. Using DBMS_SNAPSHOT package You can use the REFRESH procedure of the DBMS_SNAPSHOT package to refresh periodically a snapshot manually. UNION The UNION set operator combines the results of two q ueries into a single result with no record duplication.
81
INTERSECT The INTERSECT set operator returns all the rows contained in both tables. MINUS The MINUS set operator returns all the rows in one table minus the rows contained in other table. Tablespace A tablespace is a logical database structure that is designed to store other logical d atabase structures. Oracle sees a tablespace as a large area of space into which Oracle can place new objects. Space in tablespace is allocated in segments. Partitioned table Partitioned tables are just like regular tables excep t for an important small feature-they enable you to reference the individual segments that might support larger tables directly. The ROLLUP function It is simple extension to the SELECT statement’s GROUP BY clau se. It creates subtotals at any level of aggregation needed, from the most detailed up to a grand total. The CUBE function It is simple extension to the SELECT statement’s GROUP BY clause. It calculates subtotals for all the combinations of a group of dimensions. It also calculates a grand total. JAVA JAVA is a language that is portable, due to the fact that it is run within your environment. This environment may be a web browser, a database, or an application server. Nested Table If an object is in an object, it is a nested object. A nested table is a table that is nested in another table. The “THE” sub-query The "THE" sub-query is used to identify the nested table to insert into. Note only one row may be inserted into the nested table at once using this method-as would be the case if you were inserting into any table. The REF Cursor REF cursors hold cursors in the same way that VARCHAR2 variables hold strings. This is an added feature that comes with PL/SQL v2.2. A REF cursor allows a cursor to be opened on the server and passed to the client as a unit rather than one row at a time. One can use a Ref cursor as a target of assignments and can be passed as parameters to the Program Units. Ref cursors are opened with an OPEN FOR statement and in all other ways, they are the same as regular cursors.
82
Table of Records A table of records is a new feature added in PL/SQL v2.3. It is the equivalent of a database table in memory. If you structure the PL/SQL table of records with a primary key (an index) you can have array-like access to the rows. Table of records differ from arrays in that they are not bound by a fixed lower or higher limit. Nor do they require consecutive index numbers as arrays do. Consult a PL/SQL reference manual (version 2.3 or higher) for further explanation. There are three steps involved in creating a table of records. Oracle Server is an object-relational database management system that provides an open, comprehensive, and integrated approach to information management. It consists of an Oracle database and instance. The Oracle Database contains all user data information. It has a physical and a logical structure. The “Physical Structure” is determined by the operating system files; such as data files, control files, and parameter files. The “Logical Structure” is determined by the Oracle Database, such as tablespaces, tables, and segments. An instance is a combination of Oracle background processes and memory buffers. The memory buffer is called System Global Area (SGA) and is shared by the database users. Every time a database is started the system global area is allocated and Oracle background processes are started. Oracle Background Processes Server tasks between memory and disk. A user establishes connection and then requests information using a client application from the Oracle Server. The Listener process waits for connection requests from a client application, and routes each client to a server process. Server processes are created on behalf of each user’s application to read a user request and return the results. The TNSNAMES.ORA and SQLNET.ORA files establish client session connectivity to a server using the local naming option.
General Questions: Q: What does the DML stand for? Q: What are the examples of the DML statement? Q: What should a ‘SELECT’ statement consist of? Q: Can you perform a mathematical operation on a ‘SELECT’ statement? Q: What is the DUAL table? Q: When do you use the DUAL table? Q: What does the DUAL table contain? Q: What is the NULL value? Q: What is the result of 100+NULL? Q: What does the NVL function? Q: What is a COLUMN HEADING? Q: What is a TABLE ALIAS?
83 Q: How can you concatenate two columns or strings? Q: What does the LIKE clause? Q: What does the DECODE function? Q: How many different types of JOIN table do you know? Q: What is an inner join table? Q: What is an outer join table? Q: What is an equi-join table? Q: What is the difference between an inner join table and an outer join table? Q: What is a SELF JOIN table? Q: What is the Cartesian product? Q: How can you avoid having a Cartesian product? Q: What is an anonymous column? Q: How can you avoid having an anonymous column? Q: When do you use the GROUP BY clause? Q: What does the EXISTS operation produce? Q: What does the IN operation produce? Q: What is a runtime variable? Q: What is a table? Q: What is the difference between a VARCHAR2 datatype and a CHAR datatype? Q: What is the difference between a DATE and NUMBER datatypes? Q: How does a DATE store in the Oracle database? Q: What is the difference between a LONG RAW and BLOB datatypes? Q: What is the difference between a LONG and CLOB datatypes? Q: What is a ROWID of a record? Q: What is the BFILE datatype? Q: What is a data dictionary in the Oracle database? Q: What type of data store in a data dictionary? Q: What is a data dictionary view? Q: What is DDL? Q: What does DDL stand for? Q: What are the differences between a TRUNCATE and DELETE commands? Q: What is a high watermark in the Oracle table? Q: What is implicit in a DDL statement? Q: What is the Oracle9i ANSI? Q: What are the differences between the NATURAL JOIN and JOIN syntaxes? Q: When do you use the USING clause in the Oracle ANSI database? Q: Write and describe all different types of JOIN in the Oracle ANSI statements? Q: What is an inline view in the Oracle database? Q: What is the materialized view? Q: What is the view? Q: When do you use the DBMS_SNAPSHOT package? Q: What is the materialized view log? Q: Describe the UNION, INTERSET, and MINUS set operators in the Oracle SELECT statement? Q: What is a tablespace in the Oracle database? Q: What is a partitioned table in the Oracle database?
84 Q: When do you use a partitioned table in the Oracle database? Q: What are the differences between Oracle table and Oracle partitioned table? Q: What is the ROLLUP function? Q: What is the CUBE function? Q: What is the Nested Table in the Oracle database? Q: What is the logical structure in the Oracle database? Q: What is the physical structure in the Oracle database? Q: What is an instance in the Oracle database? Q: What is SGA? Q: What are the Oracle Background Processes? Q: What is the listener process in the Oracle database?
85
Lesson 19 Answers: Q: What are the definitions of the following items? column, record, table, item, field, element, primary key, foreign key, and datatype. A: A column is a smallest unit in a database that you need to deal with. A record is a collection of columns and a table is a collection of records. The terms: elements, columns, fields and items can be used interchangeably. A primary key is a unique identifier in a table. A foreign key is a column in a table (child) that references to a primary key column in another table (parent). Relationships between two tables are normally established by defining primary or foreign keys. A datatype is a format that an input data will be stored in a column. Q: What is the relationship between primary and foreign keys? A: Relationships between two tables are normally established by defining primary or foreign keys. It w ill establish a child and parent relationships. A foreign key is a column in a table (child) that references to a primary key column in another table (parent). Q: Describe the Entity Relationship diagram and Logical Data Model. A: "Entity Relationship Diagram" or "Logical Data Model" is used to establish relationships between entities. Q: What is a composite index? A: If an index key or a primary key were composed of more than one column. We call it a composite index. Q: What are the responsibilities of an Oracle DBA and Oracle Developer? A: The integrity, security, connectivity, performance, and tuning of a database will be maintained by DBAs. One of the responsibilities of a DBA is to plan a contingency for disaster and ensure recovery of the database. On the other hand developers use front-end and back-end tools along with management tools to perform their tasks. They develop applications to manipulate a database’s data. Their application will query, insert, delete and update a record or records. They use front-end tools such as "form builder," "report builder," and "graphics builder." They use back-end tools such as "schema builder," "procedure builder," and "query builder." They use project builder tools to manage and deliver their applications to their clients. Q: What is a Database? A: A collection of all tables under a single or many different schemas can be stored and maintained in a database. A database, in effect, is a collection of objects such as tables, indexes, stored procedures, etc. Q: Query the employee names and their salaries from the employee table. A: SQL> SELECT ename, sal FROM emp; Q: Do the above query and use an “as” clause for the “salary” column aliases or column headings. A: SQL> SELECT ename, sal AS salary FROM emp; Q: Repeat the previous query and have “Full Name” for the ename’s column heading and “Salary” for the “sal” column heading. A: SQL> SELECT ename “Full Name”, sal "Salary" FROM emp; Q: What is the result of 100 + NULL? A: NULL. Q: Query the employee names with their commissions. A: SQL> SELECT ename, comm commission FROM emp;
86 Q: Use the (NVL) the null value function to assign zero to any null value in the commission column for the previous query. A: SQL> SELECT ename, NVL(comm,0) commission FROM emp; Q: Concatenate the customers’ last name and first name separated by comma. A: SQL> SELECT last_name || ', ' || first_name AS "full name" FROM customers; Q: Query the employees name sorted by ascending order. A: SQL> SELECT ename FROM emp ORDER BY ename ASC; Q: Query the employees name sorted by descending order. A: SQL> SELECT ename FROM emp ORDER BY ename DESC; Q: Query the employee information whose employee number is 7788. A: SQL> SELECT * FROM emp WHERE empno = 7788; Q: Query the employees name whose names start with the letter “M.” A: SQL> SELECT ename FROM emp WHERE ename LIKE 'M%'; Q: Query the employees name whose names end with the letter “R.” A: SQL> SELECT ename FROM emp WHERE ename LIKE '%R'; Q: Query the employees name whose salaries between 2000 and 3000 dollars. A: SQL> SELECT ename FROM emp WHERE sal BETWEEN 2000 AND 3000; Q: Query the employees name and their department name using the “DECODE” function. If the department number is 10 then print "accounting.” If the department number is 20 then print "research," or if the department number is 30 then print " sales." Anything else prints others. A: SQL> SELECT ename, DECODE (deptno, 10, 'Accounting', 20, 'Research', 30, 'Sales', 'Others') AS "Department" FROM emp; Q: What is an ambiguous column? A: An ambiguous column is a column that is not defined clearly. Having two tables with the same column name, you should reference them such that there is no ambiguity on their ownerships. Q: How can you resolve an ambiguous column problem? A: The column name should be identified by alias to make it clear that to what table that column is belong. Q: What is a Cartesian product? A: A “Cartesian” product is caused by joining “N” number of tables while you have less than “N-1” joins condition in the query. Q: How can you avoid a Cartesian product? A: To avoid it, just when joining “N” number of tables you should have more or equal “N-1” joins condition in the query. Q: What is an inner join or equi-join? A: Joining two or more tables together using the WHERE clause with the equal sign (=) in a query. This type of query will retrieve records that have exact match and will be called inner join or equi-join. Q: What is an outer join? A: Joining two or more tables using OUTER join, not only you retrieve all matching records but also you retrieve the records that do not match. Q: What is a self join?
87 A: When a table refers to itself in the WHERE clause, we call that join is a self-join. Q: Query all the employee names and their department including all the departments with no employees. A: SQL> SELECT ename, dname FROM emp e, dept d WHERE e.deptno (+) = d.deptno; Q: Query the managers’ name with their employees sorted by the manager name. A: SQL> SELECT mgr.ename “Manager Name”, e.ename “Employee Name” FROM emp mgr, emp e WHERE mgr.empno = e.mgr ORDER BY mgr.ename; Q: Query the department number and their total, average, min, and max salaries for each department. A: SQL> SELECT deptno, SUM(sal), AVG(sal), MIN(sal), MAX(sal) FROM emp GROUP BY deptno; Q: Query the department no and their total salaries that have more than 5 employees working in their department. A: SQL> SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING count(*) > 5; Q: Query the employees name that work for the Research or Sales department (the department number 20 or 30). A: SQL> SELECT ename, deptno FROM emp WHERE deptno IN (20, 30); Q: Query the employees name that work in the "accounting" department. Assuming the department number is unknown. A: SQL> SELECT ename FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname = "ACCOUNTING"); Q: Query the employees name and use the runtime variable to substitute the department number? Then run it for following department no 10, 20, and 30. A: SQL> SELECT ename FROM emp WHERE deptno = &deptno; SQL> / Q: Query the customer names which have more than four orders. A: SQL> SELECT name FROM customer c WHERE exists (SELECT 'T' FROM ord WHERE custid = c.custid GROUP BY custid HAVING count(*) > 4); Q: Create an employee table that contains five columns: Such as Employee Id, last name, First name, Phone number and Department number with the following constraints. 1. The last name and first name should be not null. 2. Make a check constraint to check the department number is between 9 and 100. 3. Make a primary constraint on the employee ID column. 4. Make a foreign key on the department number column. 5. Use the "delete cascade" to delete all records if parent gets deleted.
88 6. Use the "phone number" as a unique key. A: SQL> CREATE TABLE employee (empid NUMBER(10), lastname VARCHAR2(20) not null, firstname VARCHAR2 (20) not null, phone_no VARCHAR2 (15), deptno NUMBER(2) CHECK (deptno BETWEEN 9 AND 100), constraint pk_employee_01 PRIMARY KEY (empid), constraint fk_dept_01 FOREIGN KEY (deptno) references dept (deptno) ON DELETE CASCADE, constraint uk_employee_01 UNQUE (phone_no)); Q: Create a composite index on the employee table that contains two index columns (last name and first name). A: SQL> CREATE INDEX employee_lname_fname_ind_01 ON employee (lastname, firstname ); Q: Query the tables that you as a user own. A: SQL> SELECT table_name FROM user_tables ORDER BY table_name; Q: Query the index tables that belong to the employee table and owns by the Alaa user. A: SQL> SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'EMPLOYEE'; Q: Change the size of the "column_name" to 30 characters logically (for display only). A: SQL> COLUMN column_name FORMAT a30 Q: Query the indexes columns of the employee table. A: SQL> SELECT index_name, column_name, column_position FROM user_ind_columns WHERE table_name = 'EMPLOYEE'; Q: Insert a record into the "employee" table using column names. A: SQL> INSERT INTO employee (empid, lastname, deptno, firstname, phone_no) VALUES (100, 'smith', 10,'joe', ‘7038212211'); Q: Insert a record using the column position format. A: SQL> INSERT INTO employee VALUES ( 200, 'KING', 'Allen', 5464327532, 10); Q: How do you save the inserted transaction? A: COMMIT; Q: Change the "last_name" column value from “Smith” to “Judd” where the "employee id" is 100. A: SQL> UPDATE employee SET lastname = 'Judd' WHERE empid = 100; Q: Delete all the employee records from the "employee" table using the delete command and the truncate command. A: SQL> DELETE FROM employee; OR SQL> TRUNCATE TABLE employee; Q: How do you undo a transaction? A: ROLLBACK; Q: What is the difference between the delete statement and the truncate statement? A: Notice that the TRUNCATE command is a DDL statement and all DDL statements have commit inclusive. That is why the ROLLBACK action after truncation does not work. Also, if you want to delete all records from a table, you should use the TRUNCATE statement. It will change the table watermark. The table watermark is an address that indicates a last location of a record in a table. On the DELE TE statement the watermark will not change. But using the TRUNCATE statement will change the watermark to the beginning of the table.
89 Q: Copy the “EMP” table to another table and name the new table "employee." In the new employee table use the employee name, job, commission and department number. A: SQL> CREATE TABLE employee AS SELECT ename, job, comm, deptno FROM emp; Q: Add a salary column to the employee table. A: SQL> ALTER TABLE employee ADD (salary NUMBER(8,2)); Q: Modify the "ename" column size from varchar10 to varchar15. A: SQL> ALTER TABLE employee MODIFY (ename VARCHAR2(15)); Q: Rename the "employee" table to the "Alaa_employee" table. A: SQL> RENAME employee TO Alaa_employee; Q: Create a view to display the employee names of the “Accounting” department only. A: SQL> CREATE VIEW employee_name AS SELECT ename FROM Alaa_employee WHERE deptno = 10; Q: Why do you use the view? A: You use view to present rows and columns of a table in the way you want. You may use it for security reason. For example, you may eliminate some rows and columns that are very sensitive information. These changes are transparent to a user. Q: How do you compile the view? A: SQL> ALTER VIEW employee_name COMPILE; Q: How do you delete the view? A: SQL> DROP VIEW employee_name; Q: Create an index on the employee table on the ename column only and na me it employee_indx. A: SQL> CREATE INDEX employee_indx ON employee (ename); Q: Reorganize the “employee_indx” index table. A: SQL> ALTER INDEX employee_ indx REBUILD; Q: Drop the employee_ename index table. A: SQL> DROP INDEX employee_indx; Q: Create a user with username “newuser” and password "newpass." Its default tablespace should be the "Alaa_data" tablespace. A: SQL> CREATE USER newuser IDENTIFIED BY by newpass DEFAULT TABLESPACE Alaa_data; Q: Grant the resource and connect roles to newuser. A: SQL> GRANT resource, connect TO newuser; Q: Change the newuser password to "mypass". A: SQL> ALTER USER newuser IDENTIFIED BY mypass; Q: Can the above new user access to any other user tables? A: No. Q: What is a public synonym? A: It is a synonym that public users can use. We create public synonym so that the users don’t need to type schema name to a table when they query the table. Creating a public synonym does not mean that oracle users can access to that table or object. Still the owner of the object has to grant access to a user on its table. Q: What is the syntax to create a public synonym? A: SQL> CREATE PUBLIC SYNONYM employees FOR Alaa.Alaa_employee; Q: What is the difference between public and private synonym? A: The private synonym is only for the user who owns or created the synonym, but the public can be used by every users. Q: Create and drop a private synonym. A: SQL> CREATE SYNONYM emp_table FOR Alaa.Alaa_employee; To drop: SQL> DROP SYNONYM emp_table;
90
Q: Revoke an object privilege on a table from a user. A: SQL> REVOKE UPDATE, SELECT ON employee FROM newuser; Q: What does the LIST or ‘L’ co mmand line editor? A: It lists the current SQL statement that was typed in the Oracle buffer. Q: What does the INSERT or ‘I’ command line editor? A: It inserts a command in the Oracle buffer after the current active line that was indicated with an *. Q: What does the DEL or ‘D’ command line editor? A: It deletes the current active line in the Oracle Buffer. Q: How do you change a string in the Oracle Buffer? A: First, mark the line as a current active line and then type the‘del’ command. Q: How do you save the SQL script in the Oracle Buffer? A: SQL> save c:.sql Q: How do you open the SQL Script into the Oracle Buffer? A: SQL> get c:.sql Q: How do you use the notepad editor? A: Just type: the ed command to open the default editor. Q: What is afiedt.buf? A: The "afiedt.buf" file is a place that into which SQL*PLUS stores the most recently executed SQL statement. Q: How do you change your text editor in the SQLPLUS tool? A: Issue the define_editor='your editor' statement from the SQL*PLUS prompt. Q: What does the ed command in the SQLPLUS tool? A: We use the "ed" command, to open your default word editor. Q: Can you have multiple SQL statements in the afiedt.buf file? A: No. You can only use one SQL statement at a time. Q: How do you use the notepad editor as an independent tool in the SQLPLUS utility? A: Just open your notepad editor outside of your SQLPLUS. Q: How do you execute or run a SQL script? A: SQL> run c:.sql or start c: Q: What is the SQL ANSI statement? A: It is some standard roles that provided by American National Standards Institute. Q: What is the difference between the SQL ANSI statement and Original Oracle statement? A: The Original Oracle statements are not follow the role of American National Standards Institute. Q: Is the SET command a SQL statement? A: No. Q: How do you change your workstation’s page size or line size? A: SQL> SET LINESIZE 100 PAGESIZE 55 Q: What does the JOIN syntax in the Oracle SQL (DML) statement? A: It does innor join using the ON clause. SQL> SELECT ename, dept.deptno, dname FROM emp JOIN dept ON emp.deptno = dept.deptno AND dname <> 'SALES' / Q: What is the difference between the JOIN syntax and the NATUR AL JOIN syntax? A: In the NATURAL JOIN syntax, you don't need the ON clause if the column’s names are the same. Q: What does the USING clause in the Oracle SQL statement? A: It joins two tables and in the USING clause the join column names must be the same. Q: What is the advantage of the NATURAL JOIN syntax? A: It is less typing. Q: What does the CROSS JOIN syntax in the Oracle SQL statement? A: We can use the Oracle9i ANSI standard CROSS JOIN syntax with no WHERE clause to create a Cartesian product. Q: What does the IN clause in the Oracle SQL statement? A: The IN clause in the Oracle SQL statement is an equivalent of the OR condition in the SQL statement.
91 Q: What do the OUTER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN syntax in the Oracle SQL statement? A: We use the OUTER option when w e want all records that have exact match plus those records that have no match. Q: How can you perform the FULL OUTER JOIN syntax using the Original Oracle syntax? A: Although it is possible but it is very difficult to perform the full outer join using the original Oracle syntax. Q: When do you use the WITH … AS clause in the SQL statement? A: If we have a query which it needs to process the same sub-query several times, we should consider using the WITH …AS clause in our statement. Q: How does the WITH … AS clause help your performance? A: The query will create a temporary table to query it over and over. Q: Write a query to list all the department names that their total paid salaries are more than 1/3 of the total salary of the company. A: SQL> WITH summary_totals AS (SELECT dname, SUM(sal) AS totals FROM emp NATURAL JOIN dept GROUP BY dname) SELECT dname, totals FROM summary_totals WHERE totals > (SELECT SUM(totals)*1/3 FROM summary_totals) ORDER BY totals DESC SQL>/ Q: What are the multiple columns in the SQL statement? Where or how do you use them? A: We use multiple columns to match the multiple columns returned from the sub-query. Q: Write a SQL statement to query the name of all employees who earn the maximum salary in their department using the multiple columns syntax. A: SQL> SELECT deptno, ename, job, sal FROM emp WHERE (deptno, sal) IN (SELECT deptno, MAX(sal) FROM emp GROUP BY deptno) / Q: What is the inline view in the Oracle SQL statement? A: If we have a sub-query in a FROM clause in the Oracle SQL statement, is called an inline view. Q: Write a SQL statement to query all of the employee names, jobs, and salaries where their salary is more than 10% of the total company paid salary. A: SQL> SELECT ename, job, sal FROM (SELECT ename, job, sal FROM emp WHERE sal > (SELECT SUM(sal) * .1 FROM emp) ORDER BY 3) / Q: What does the MERGE statement in the SQL statement? A: We use the MERGE statement to merge one table into another table. Q: Can you update, insert, or delete any records while you are using the MERGE statement? A: Yes. Q: What is a Materialized View? A: A materialized view (MVIEW) is a replica of a target master from a single point in time. Q: What are the Materialized View types? A: Read-Only Materialized Views Updatable Materialized Views
92 Sub-query Materialized Views Row-id vs. Primary Key Materialized Views Q: Write the difference between ROWID and PRIMARY KEY in the Materialized View. A: Fast refresh requires association between rows at snapshot and master sites. Snapshots that use ROWIDs to refresh are called ROWID snapshots while those that use primary keys are called primary key snapshots. Q: What is the difference between a Materialized View and View? A: A Materialized View is a physical duplicated data in a table, but a View is just a logical presentation of a table. Q: When or why do you use a Materialized View? A: You use Materialized Views to create summaries in a data warehouse environment or replicate a data in a distributed environment. In data warehouses, you can use materialized views to pre-compute and store aggregated data such as the sum of sales. In distributed environments, you can use materialized views to replicate data from a master site to other distributed sites. Q: What is a materialized view log? A: A materialized view log is a holder that contains updated, inserted, or deleted records’ information in the primary table. Q: What are the PRIMARY KEY and ROWID in the Materialized View Log? A: The Materialized View log that use ROWIDs to refresh are called ROWID view log while those that use primary keys are called primary key view log. Q: What does the USER_SNAPSHOT_LOGS view contain? A: It shows if our log was created successfully and its name (MLOG$_EMP). Q: Create a materialized view that contains the department number, number of employees, and total salaries paid to employees by department. A: SQL> CREATE MATERIALIZED VIEW mv_sal BUILD IMMEDIATE REFRESH ON DEMAND AS SELECT deptno, COUNT(1) AS no_of_emp, SUM(sal) AS salary FROM emp GROUP BY deptno SQL> / Q: Who can create a materialized view? A: The one that was granted the CREATE MATERIALIZED VIEW privilege. Q: What does the USER_MVIEWS view contain? A: It contains all the Materialized Views’ information that were created by the user. Q: How do you refresh a materialized view? A: SQL> EXECUTE dbms_snapshot.refresh('mv_sal','C'); Q: What parameter should be used to update the materialized view every month automatically without human intervention? A: The START WITH SYSDATE option will create an immediate data, and the NEXT(SYSDATE+30) option will update the table every 30 days. Q: What does the USER_JOBS view contain? A: It contains all users’ jobs in the Oracle queue. Q: How do you remove a job from the Oracle Job Queue? A: SQL> EXECUTE dbms_job.remove(job_number); Q: How do you drop a materialized view log and a materialized view? A: SQL> DROP MATERIALIZED VIEW LOG ON emp; To drop it: SQL> DROP MATERIALIZED VIEW mv_sal; Q: What does the BREAK ON clause in SQLPLUS? A: It builds a break on a column. Q: What do the REPHEADER and REPFOOTER commands in SQLPLUS? A: They make a report header and footer. Q: What does the following commands? COLUMN sal HEADING 'Salary' F ORMAT $99,999.99 --Creates heading format. COLUMN ename HEADING 'Employee' FORMAT a20 - Creates heading format.
93 REPHEADER '' - Creates report heading. BREAK ON dname SKIP 1 - Creates control bread on a column and skip 1 line after the break. COMPUTE SUM OF sal ON dname - Computes total salary within a department. SPOOL c:.out -- Activates spooling. SPOOL OFF -- Deactivate spooling. REPFOOTER '' - Creates report footer. CLEAR BUFFER -- Clear the Oracle buffer. CLEAR COLUMNS - Clears columns. CLEAR COMPUTE -- Clears compute functions. Q: What does the CLEAR command in SQLPLUS? A: Note that all the values in REPHEADER, REPFOOTER, BUFFER, COLUMNS, COMPUTE and etc are going to stay the same during your open session. In order to clean them, you should use the CLEAR command for BUFFER, COLUMNS, and COMPUTE. And input NULL to REPHEADER and REPFOOTER. Q: What does the UNION statement in the SQL statement? A: It will query all the records that match or not match with the base table. Q: What does the INTERSET statement in the SQL statement? A: It will query all the records that match with the base table. It is the same as joining two tables. Q: What does the MINUS statement in the SQL statement? A: It will query all the records that are not matching against your base table. Q: Why it is important to eliminate duplicate records? A: To keep your database integrity. Q: What does the following SQL statement? SQL> DELETE FROM dup_emp WHERE ROWID IN (SELECT MAX(ROWID) FROM dup_emp GROUP BY empno HAVING COUNT (empno) > 1) SQL> / A: Deletes all the rows that have the same employee number except the first one. Q: What is a data partitioning in the Oracle database? A: The data partitioning in the Oracle database is that the data will be partitioned in multi-tablespaces for ease of maintenances. Q: When should you use data partitioning? A: When you have a huge data file and can be classified to some partitions. Q: What is the advantage of using a data partitioning? A: It is faster to access. It is easier to maintain. Q: What is a partition key? A: It is used to separate data and associates them to their own assigned tablespace. Q: What is a local index in the data partitioning? A: A Local index is one that is partitioned exactly like the table to which it belongs. Q: What is a global index in the data partitioning? A: A Global index, unlike local indexes, you should explicitly partition range boundaries using the “VALUE LESS THAN” methods. Q: What are the differences between local and global indexes? A: In the local index you don’t define explicitly partition range. Q: How does the ‘VALUE LESS THAN’ method work in the data partitioning? A: The VALUES LESS THAN clause indicates the partition key value must be less then its assigned value in order to be illegible for any DML transaction on its assigned tablespace. Q: Why do you need multiple tablespaces? A: Multiple tablespaces give us more flexibility to maintain a tablespace without affecting any performance or downtime to others. Q: Create a range-based partitioning table named p_emp. Make sure that the data entry of the each department goes to its own provided tablespaces such as the accounting department goes to the dept10ts tablespace, the data entry of the research department goes to the dept20ts tablespace, etc.
94 A: SQL> CREATE TABLE p_emp ( empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2( VARCHAR2(9), 9), mgr NUMBER(4), hiredate DATE, sale NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) NUMBER(2))) STORAGE (INITIAL 5K NEXT 5K PCTINCREASE 0) PARTITION BY RANGE (deptno) (PARTITION dept10 VALUES LESS THAN (20) TABLESPACE TABLESPAC E dept10ts, PARTITION dept20 VALUES LESS THAN (30) TABLESPACE TABLESPAC E dept20ts, PARTITION dept30 VALUES LESS THAN (40) TABLESPACE TABLESPAC E dept30ts, PARTITION deptxx VALUES LESS THAN (MAXVALUE) TABLESPACE TABLESPAC E deptxxts) SQL> / Q: What does the MAXVALUE parameter mean in the data partitioning? A: It means as large as the column can hold. Q: How do you analyze a partition table? A: SQL> ANALYZE TABLE p_emp COMPUTE STATISTICS; Q: What does the USER_TAB_PARTITIONS view contain? A: A user can query its partitions table’s information that was created by the user. Q: Write a query to list the accounting employees from the partition table. Use the partition option. A: SQL> SELECT * FROM p_emp PARTITION (dept10); Q: Write a query to list employee number 7900 from the sales department? A: SQL> SELECT * FROM p_emp PARTITION (dept30) WHERE empno = 7900 SQL> / Q: How do you create a local partition index? A: SQL> CREATE INDEX p_emp_ind ON p_emp (deptno) LOCAL; Q: How do you analyze a partition table index? A: SQL> ANALYZE INDEX p_emp_ind COMPUTE STATISTICS; Q: What does the USER_IND_PARTITIONS view contain? A: It contains information in regard to the user’s partition indexes. Q: What does the ROLLUP operator? A: The ROLLUP operator returns both ‘regular rows’ and ‘super-aggregate rows.’ Super-aggregate rows are rows that contain a sub-total value. Q: What does the CUBE function? A: The CUBE operator returns cross-tabulati cross-tabulation on values, thus produces totals in all possible dimensions, and is used for warehousing aggregated data reports. Q: What are the differences between the CUBE and ROLLUP functions? A: See the output… Q: What environments may use the CUBE and ROLLUP functions most? A: Warehousing.
95 Q: Write a query to list an aggregation sum report for each job, in each year, using the ROLLUP grouping option. A: SQL> SELECT year, job, SUM(sal), COUNT(*) FROM emp GROUP BY ROLLUP (year, job) SQL> / Q: Write a query to list an aggregation sum report for each job, in each year, using the CUBE grouping option. A: SQL> SELECT year, job, SUM(sal), COUNT(*) FROM emp WHERE deptno = 20 GROUP BY CUBE (year, job) SQL> / Q: What is an object type? A: The object type in the Oracle database is like the class eliminate in the C++ developer tool or any object oriented tool. Q: What is a collection object? A: The collection object in the Oracle database is like a nested table and a variable array in a table. Q: Create an object type with two columns to hold the employee's child name and date of b irth and name it employee_kids . A: SQL> CREATE TYPE employee_kids AS OBJECT ( NAME VARCHAR2(30), dob DATE ) SQL> / Q: Create a table type using employee_kids and name it e mployee_kids_table. A: SQL> CREATE TYPE employee_kids_table IS TABLE OF employee_kids; Q: Create the emp_family table containing the kid’s column with a type of employee_kids_table. A: SQL> CREATE TABLE emp_family (empno NUMBER, kids employee_kids_table employee_kids_table)) NESTED TABLE kids STORE AS nested_employee_kids_table SQL> / Q: How do you insert a record in the object type? A: SQL> INSERT INTO emp_family VALUES (7902, employee_kids_table (employee_kids('David','08-AUG-01'), employee_kids('Peter','10-JUN-88'), employee_kids('Mark','30-OCT-92') ) ) SQL> / Q: What is the constructor? A: The constructor creates an empty nested table as opposed to leaving it null. Notice that without using the constructor, it is not possible to refer to the nested table with the "THE" clause. Q: What is the ‘THE’ sub-query? A: To query a nested table you should use the "THE" clause. Also, the "THE" sub-query is used to identify the nested table to INSERT INTO. Q: How do you query a record using the ‘THE’ sub-query? A: SQL> SELECT name FROM THE(SELECT kids FROM emp_family WHERE empno = 7788) SQL> / Q: What is a nested table?
96 A: It is a table within a table. Q: How do you insert a record to a nested table? A: SQL> INSERT INTO THE(SELECT kids FROM emp_family WHERE empno = 7900) VALUES ('Sue','10-DEC ('Sue','10-DEC-99'); -99'); Q: How do you update a record to nested table? A: SQL> UPDATE emp_family SET kids = employee_kids_table( employee_kids('Sara','08-OCT-88')) WHERE empno = 7788 SQL> / Q: How do you add a unique index to a nested table? A: SQL> CREATE UNIQUE INDEX i_nested_employee_kids_table ON nested_employee_kids_tabl nested_employee_kids_table(nested_table_id,nam e(nested_table_id,name) e) SQL> / Q: What is a data replica? A: A duplicated data in a different location. Q: What is the difference between a materialized view and a materialized view log? A: The Materialized view is a real duplicated data from a primary table but the materialized view log is an on going logs generated due to the table changes after the last refresh. Q: What is an OID (Object ID)? A: It is a unique ID assigned to an object by Oracle. Q: How do you retrieve an object ID? A: SQL> SELECT OWNER, TYPE_OID FROM DBA_TYPES WHERE TYPE_NAME LIKE 'ADDRESS%'; Q: How do you use an object ID to create an object type? A: SQL> CREATE OR REPLACE TYPE address_book_type_object OID ‘XXXXXXXXXXXXXXXXXXXXX’ AS OBJECT ( id_address NUMBER(1 NUMBER(1), ), address VARCHAR VARCHAR2(20)); 2(20)); Q: What is the relationship between primary and foreign keys? A: The relationships between two tables are nor mally established by defining primary or foreign keys. A primary key has the immutable responsibility responsibility of serving as a unique identifier in a table. A foreign key is a column that refers to the primary key of another table. To join two tables, a “where clause” is used to set up a table relationship between primary and foreign keys. Q: What is a composite index? A: A primary key can be composed of more than one column. We call it a composite index. Q: What is the result of 100 + NULL? A: NULL value. Q: Write a query to concatenate the customers’ last name and first na me separated by comma. A: SELECT last_name || ‘, ‘ || first_name as “Full Name” FROM customers / Q: Query the employees name and their department name using the “DECODE” function. If the department number is 10 then print "accounting.” If the department number is 20 then print "research," or if the department number is 30 then print " sales." Anything else prints others. A: SELECT ename, DECODE (deptno, 10, 'Accounting', 20, 'Research', 30, 'Sales', 'Others') AS "Department" FROM emp /
97 Q: Query the department number and their total salaries that have more than 5 employees working in their department. A: SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING count(*) > 5 / Q: query the customer names which have more than four orders. A: SELECT name FROM customer c WHERE exists (SELECT 'T' FROM ord WHERE custid = c.custid GROUP BY custid HAVING count(*) > 4) / Q: Create an employee table that contains five columns: Such as Employee Id, last name, First name, Phone number and Department number with the following constraints. 7. The last name and first name should be not null. 8. Make a check constraint to check the department number is between 9 and 100. 9. Make a primary constraint on the employee ID column. 10. Make a foreign key on the department number column. 11. Use the "delete cascade" to delete all records if parent gets deleted. 12. Use the "phone number" as a unique key. A: CREATE TABLE employee (empid NUMBER(10), lastname VARCHAR2(20) not null, firstname VARCHAR2 (20) not null, phone_no VARCHAR2 (15), deptno NUMBER(2) CHECK (deptno BETWEEN 9 AND 100), constraint pk_employee_01 PRIMARY KEY (empid), constraint fk_dept_01 FOREIGN KEY (deptno) references dept (deptno) ON DELETE CASCADE, constraint uk_employee_01 UNQUE (phone_no)) / Q: What is the difference between the delete statement and the truncate statement? A: On the DELETE statement the watermark will not change. But using the TRUNCATE statement will change the watermark to the beginning of the table. Q: Copy the “EMP” table to another table and name the new table "employee." In the new employee table use the employee name, job, commission and department number. A: CREATE TABLE employee AS SELECT ename, job, comm, deptno FROM emp / Q: Reorganize the “employee_indx” index table. A: ALTER INDEX employee_indx REBUILD / Q: What is the difference between public and private synonym? A: You create synonym so that the users don’t need to type schema name to a table when they query the table. The Public Synonym is available to all database users but the Private Synonym is available only to the owner of synonym. Q: Can you have multiple SQL statements in the afiedt.buf file? A: No. Q: How do you execute or run a SQL script? A: SQL> @my_sql_script; or start my_sql_script; Q: Write a query to list all the department names that their total paid salaries are more than 1/3 of the total salary of the company.
