Oracle

1. Oracle Table of contents • What is a database? • Fundamentals of database • Table management • Inserting data • Updating data • Deleting data • Query management • Simple queries • Aggregate functions • String manipulation • Date manipulation • Sub queries • Equi join • Outer join • Self join • Union, intersection, minus • View management • Sequence Management • Index Management • Constraints Management • Triggers • Stored procedures • Exceptions • Sequences • Constraints • DBA features Softsmith Infotech

2. Fundamentals of database • Applications are used by end users and they enter a group of raw data • This data is later collated and used by management to arrive at meaningful information • Before we first understand the technical aspects of database, we must understand the business data clearly • Rule 1: In any application, first identify raw data • Rule 2: Group related data and associate data type and size (summary and detail) • Rule 3: Create a set of samples for each of these groups for better clarity • Rule 4: Identify unique and non-unique data • Rule 5: Identify the relationship between the data Softsmith Infotech

3. Raw Data and Grouping • Let us take railways reservation as the application • The raw data could be • Passenger name • Age • Date of journey • Train name • From station • To Station • PNR number • Route codes • Stations covered in the route • The data groups would be • Train related data – train number, train name, route in which it is running • Station related data – station code, station name, station RMS Pincode, station type (junction, station etc) • Ticket related data – PNR Number, passenger name, date of journey, set or berth, age, from station, to station, train code etc Softsmith Infotech

4. Unique and non-unique data • Unique data means a data that does not repeat itself • Station code is unique across country • Train code is unique across country • PNR number is unique across country • Coach number is unique within a train, but not unique across system • Seat number is unique within a coach and train • Passenger name is not unique • Ticket price is not unique Softsmith Infotech

5. Relationship between data • One PNR number is associated with one train code • One PNR number is associated with one or more passenger names • One train code is associated with one or more stations • One train code is associated with one route code • All relationships will fall under one-to-one, one-to-many, many-to-one • Many-to-many is a combinations of the above • We need to identify the relationships between the data to understand clearly the dependency between data Softsmith Infotech

6. Components of database • A physical database installation in a machine has the following logical entities • Database (group of tables) • Tables (that contain data) • Views • Index files • Triggers • Sequences • Stored procedures (packages, functions, procedures) • Users to access the tables Softsmith Infotech

7. Table Management • Create, alter and drop are the operations on a table • To create a table, we need to supply the table name, column names and attributes, for each column whether it is a null or not null column, what is the default value for that column, whether the column is primary key or not • CREATE TABLE is the command to be used • When we want to add a column to a table or to change the attributes of a column in a table, we use ALTER TABLE command • We cannot remove a column from a table • If we want to remove the table from the database, we need to use DROP TABLE command • When a table is dropped, the data is also lost • Dual is a default built-in table to do any on the fly calculations • The usual column data types will be number, varchar2, char, date Softsmith Infotech

8. Inserting Data • INSERT operation into a table requires data to be entered • We can insert all column values or selective column values • When insert is successful, we will get a success message • When we try to enter a duplicate value into a table, we will get duplicate constraint violation message • During insert, if we do not supply a value, the default value for a column is used to fill up that column • We can insert data only to one table at a time Softsmith Infotech

9. Update Operation • When we want to modify data in a table, we can use UPDATE command • We can update only one table at a time • When we update we need to use the SET clause to set the values for a particular column • During update also, if try to update a record, such that it results in duplicate record, we will get a unique constraint violation message • If we use update command without WHERE clause, it will update all the records • It is not recommended to update, without giving a where clause • Examples • Update employee set salary = 1000 • Update employee set salary = 2000 where designation = ‘SALESMANAGER’ Softsmith Infotech

10. Delete Operation • When we want to remove data in a table, we can use DELETE command • We can delete only one table at a time • We cannot delete a particular column; it is always one row or multiple rows • If we use delete command without WHERE clause, it will delete all the records • It is not recommended to delete, without giving a where clause • Example • delete from employee • Delete from employee where designation = ‘HRMANAGER’ Softsmith Infotech

11. SELECT Queries • When we want to retrieve data from a table, we use SELECT clause • Most of the times, testers use SELECT queries only • SELECT has a lot of variations and clauses. Hence SELECT is a must for all testers • SELECT queries can handle date and string manipulation commands as well • SELECT helps to filter, search and sort data • SELECT helps to find out relationships in data Softsmith Infotech

12. Simple query • SELECT * from TRAINS • this will get all columns in the table • SELECT TRAIN_CODE, TRAIN_NAME from TRAINS • this will return only 2 columns from the table • SELECT TRAIN_NAME from TRAINS WHERE number_of_coaches > 15 • This is a simple filter • SELECT TRAIN_NAME from TRAINS WHERE number_of_coaches > 15 AND TRAIN_CODE < 100 • This is a complex filter Softsmith Infotech

13. Aggregate functions • Aggregate functions work on the whole set of records • SELECT max(salary) from EMPLOYEE • this will get the maximum salary data from the table • SELECT min(salary) from EMPLOYEE • this will get the minimum salary data from the table • SELECT avg(salary) from EMPLOYEE • this will get the average salary data from the table • SELECT count(*) from EMPLOYEE • this will get the number of records in the table • SELECT sum(salary) from EMPLOYEE • this will get the sum of all salary data from the table Softsmith Infotech

14. String/Numeric/Date Manipulation Functions • Lower – this will convert to lower case • Upper – this will convert to upper case • Substr – this will return a portion of the string • Soundex – will look for sounding matches • Floor – will show the lower side value • Ceil – will show the upper side value • Round – will round off the number to the nearest value • Sysdate – will show the current date • Current_timestamp – will show the current timestamp • To_date and to_char – to manipulate date formats Softsmith Infotech

15. Operators • We can use arithmetic operators like +, -, *, / • Logical operators are AND, OR, NOT • Open and close brackets are used to group items • % is used along with LIKE operator • Where firstname LIKE ‘%raj%’ will search for raj anywhere in firstname column • Distinct is an operator on a column to fetch distinct values present in all rows • IN operator is used to provide a set of values in the WHERE clause • BETWEEN operator is used to provide a range of values in the WHERE clause Softsmith Infotech

16. SubQuery • Sub query is query within a query • This is used to ensure that the WHERE condition is further getting values from another query • Select employee_name, salary from employee where designation = (select designation from positionmaster where positioncategory like ‘%MANAGER%’) • The innermost select is executed first and then the outer select is executed • In sub queries, if we use = in where condition, the subquery must return only one value • In sub queries, if we use IN clause in where condition, the subquery can return more than one values Softsmith Infotech

17. JOINs • Joins are nothing but a cross product of one or more tables • Inner join is used to get records that match a condition in all tables • In joins we use alias to denote table names • Outer join is used to get records from more than one tables, irrespective of the condition match in left or right table • Example • SELECT a.empname, b.projectname from employees a, projects b where a.employeecode = b.employeecode and b.projectstatus = ‘Active’ • To create outer join we use + sign to the left table or right table Softsmith Infotech

18. Union, Intersect, Minus • Union is used to combine many SELECT queries. The result will be all records fetched by all queries. If 2 records match in 2 different queries, it is printed once in the result • Intersect is used to combine many SELECT queries. The result will be the common records between the 2 select queries • Minus is used to find out the difference between 2 SELECT queries • In all these cases, the columns selected must be the same in all select statements • We can use all these to find the referential integrity of the database Softsmith Infotech

19. Views • A view is a virtual table • A view is used to simplify a complex select statement • A view can bring multiple data from many tables • For all query purposes, a view is treated as a table • Create View SalesPeople As Select employeename from employees where designation like ‘%SALES%’ • When we create views, our job to write complex queries get reduced Softsmith Infotech

20. Index Files, Transactions • Index files are pointers to the exact location of a record on the disk • These help us to search faster. Hence a query executes faster • If we do not have index on a column, and we search based on that column, it becomes sequential. The performance will be poor • Index can be unique or non-unique • By using index files, search works as binary search • When we do insert or update or delete, we can make that permanent by issuing a COMMIT statement • If we want to ignore the previous insert, update, delete statements, we use ROLLBACK statement Softsmith Infotech

21. Triggers • Trigger is an automatic event that happens based on insert or update or delete • We cannot pass parameters to triggers • A trigger can happen before or after the operation • Create or replace trigger is used to get the trigger in database • :NEW.column or :OLD.column is used to retrieve internal values when the triggers are fired • Drop trigger is used to remove the trigger • To test a trigger, first do the required insert or delete or update operation on the primary table • Do a select query on the other tables on which the trigger operated Softsmith Infotech

22. Stored Procedures • These are executable code written in PLSQL • These reside inside oracle • We can write loops, conditions etc including exception handling • This comes in 2 parts – package header and package body • The package header contains procedures and function definitions • The package body contains actual executable code of functions and procedures • Procedures and functions take parameters • They must be explicitly executed and they are not automatic • Before executing procedures, set serveroutput on • Functions must return a value to the left hand side • Usually functions are used when there is only one return value or there is a true/false returns • Inside stored procedures, people write any database queries as required by application logic • To execute, from the prompt, give • exec packagename.procedurename(paramaters) Softsmith Infotech

23. Exceptions • Exceptions must be handled inside stored procedures or triggers • This will ensure that the code does not crash • When Others is the generic exception • SQLCODE and SQLERRM will hold the error number and error text • Usually no_data_found and dup_val_on_index are checked in most of the places Softsmith Infotech

24. Cursors • When a stored procedure needs to retrieve only one record, we use into clause in the select statement • When the procedure needs to process a record set, we use cursor • Declare the cursor and provide the select statement • Use OPEN, FETCH and CLOSE commands to access the cursor • Cursorname%notfound will be set to true if there are no more records to fetch • Cursorname%count will provide the number of records in the cursor Softsmith Infotech

25. Sequences • Sequences are used to generate auto increment values • Use Create sequence command and provide minimum, maximum, start and increment values • Sequcen.nextval will provide the next sequence number • This can be used in any insert statements subsequently Softsmith Infotech

26. Constraints • Not Null is a constraint while creating a table • Primary Key is a constraint • Foreign Key is a constraint • Check is a constraint to ensure that a column has only specified values • Unique index is also a constraing Softsmith Infotech

27. DBA Features • This list gives only the features and not detailed information • Creating database • Creating users • Providing access rights to users across tables • Backup and restore • Crash recovery • Replication • Loading of bulk data • Partitioning of data across disks Softsmith Infotech