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

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

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

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

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

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

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

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

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

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

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

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 gt 15
• This is a simple filter
• SELECT TRAIN_NAME from TRAINS WHERE
  number_of_coaches gt 15 AND TRAIN_CODE lt 100
• This is a complex filter

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

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

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

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

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

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

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

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

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

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)

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

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
• Cursornamenotfound will be set to true if there
  are no more records to fetch
• Cursornamecount will provide the number of
  records in the cursor

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

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

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