Introduction to Oracle.
As you all are aware that Oracle 12c which came to the market in 1 Mar 2017.As the oracle servers were used for running OLTP & DW and a mixture of both (OLTP + DW). Oracle Database 12c Release 2 is available on-premises , on-Cloud, or in a hybrid-Cloud environment.
Features of Oracle 12c databases
The old databases of Oracle which had the features like Scalability, Analytics, Performance, Security, Management, which was available to the Enterprise edition was offered at an extra cost. but with the introduction of Oracle Real Application Clusters which comes together with Oracle Database 12c Standard Edition 2 at the same cost as the previous one.
Introduction to Oracle Databases.
The oracle Database consists of 6 section.
1. Relational Databases
2. Schema Objects
3. Data Access
4. Transaction Management
5. Oracle Database Architecture
6. Oracle Database Documentation Roadmap
Database Management System (DBMS)
DBMS is a software which is used to control the storage of data, organization of data in the required format and retrieval of data.
DBMS has the following elements:
1. Kernel code used for the management of memory and storage of data for DBMS.
2. Repository (A place which is used for central storage)of metadata called a data dictionary.
3. Query language which enables applications for the access of data.
What is a database application?
A database application which comes from DBMS is a software program which interacts with database to access and manipulate data.
There are 2 types of Database Management system.
1. Hierarchical DBMS
2. Network DBMS
Basic SELECT Statement
We make use of the very basic structure of SQL SELECT queries and statements.
There are different databases which are available in the market.Among those the common type is the relation database.
A relation database consists of 1 or more tables.Each table consists of 0 or more records or rows which are horizontal in view and the columns or fields which are vertical in view.
If we want to show the employee table it looks as
Emp Id
Emp Name
DeptNo
Sal
In the database we will fetch the data from a single or multiple tables having the relatinship between them as one-to-one or a one-to-many relationship.The relation between the tables is made of Primary Key and Foreign Key pair, where the foreign key of the table is the primary key of another table.
Fetching data by making use of SELECT Query
The select query is used to fetch a single column or multiple columns or all the columns in a table.
A SELECT statement can be divided into numerous elements,which should start with a keyword.
The Elements of a Select Statement are
SELECT
FROM
WHERE
ORDER BY
which should appear in the same order.
If you want to list only the selected columns use
Select Emp Id, Emp Name from employee;
If you want to list all the columns use
Select * from employee;
1. Select statement is used to retrieve records from one or more tables or from the database.
2. From is used to select either 1 columns or multiple columns from the database.
3. Where clause is used to filter the data.
4. Orderby is used to sort the data in the columns either in ascending order or descending order.
Projection means choosing the columns in a table which is returned by the query.
Selection means rows which are returned in the table.
Column Heading Defaults in SQL.
Default heading display: Uppercase.
Character and Date column headings:Left-aligned.
Number column headings: Right-aligned.
Arithmetic Operators in SQL.
Arithmetic operator in SQL are used to add, subtract, multiply, and divide numeric values.
Operator precedence rules in SQL.
The rules are listed in descending order:
1.Parentheses
2. * /
3. + -
4. = <> < > <= >=
5. IS (IS NULL, IS NOT NULL, IS EMPTY, IS NOT EMPTY)
6. BETWEEN
7. NOT
8. AND
9. OR
All binary operators are left-associative, as are all of the JOIN operators.
Null Value.
Is a value which is blank or a field which does not consists of any value.
Column Alias
It is used to give an alternate name for the column that can be easily understood instead of the original value.
Concatenation Operator
It is used to concatenate two expressions of character data types or numeric data types using concatenation operator (||).
Literal Character Strings
Literal Character Strings in SQL is always enclosed within Single or double quotes
eg: Using single quotes around literal string
Select Emp Id, 'EmpName' from employee;
Using double quotes around literal string
Select Emp Id, "EmpName" from employee;
Duplicate Rows
Duplicate Rows are the rows which consists of more than 1 identitical values.
To find the Duplicate Values
SELECT Emp Id, EmpName, COUNT(Empname) from employee GROUP BY Empname, HAVING COUNT(EmpName) >1;
Where Clause.
The where clause is used for filetring and fetching the data from a single table or multiple tables.
Syntax of where clause
Select column1, column2 from table where [condition];
The condition for specifying the where condition is by making use of the operators like <,>,=,LIKE,NOT, etc
Character string datatype.
There are 4 different character data types for storing character strings: char, varchar, varchar(max) and text.
There are 4 different character data types for storing unicode data: nchar, nvarchar, nvarchar(max) and ntext.
Comparison operator
A comparison (or relational) operator is used to compare two different expression values.
The result of a comparison can be TRUE, FALSE, or UNKNOWN.
Different types of comparison operators -
Operator Description
= Equal to.
> Greater than.
< Less than.
>= Greater than equal to.
<= Less than equal to.
<> Not equal to.
BETWEEN condition
It is used to test an expression is within a range of values (inclusive). It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Select * from employee where Salary between 5000 and 10000;
IN condition
It is used to test if an expression matches any value in a list of values. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Select * from employee where Salary in (2500, 5000 and 10000);
LIKE condition
It allows you to use wildcards to perform pattern matching in a query. The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
LIKE condition has got 2 parts
percent sign (%)
It includes zero, one or multiple characters.
SELECT FROM table_name WHERE column LIKE '%XXXX%'
underscore (_)
It represents a single number or character.
SELECT FROM table_name WHERE column LIKE 'XXXX_'
AND Operator & OR Operator
The AND and OR operators are used to filter records based on more than one condition:
The AND operator displays a record if all the conditions separated by AND is TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
NOT Operator
The NOT operator displays a record if the condition(s) is NOT TRUE.
SQL Functions
There are two types of functions in Oracle.
1) Single Row Functions return a value for every row that is processed in a query.
eg: CONCAT,LENGTH,SUBSTR,INSTR.
2) Group Functions group the rows of data based on the values returned by the query.
Character functions
Character functions operate on values of dataype CHAR or VARCHAR.
eg: LOWER, UPPER, INITCAP, LENGTH, SUBSTR,INSTR,REPLACE,INSTR.
Case manipulation functions
The case manipulation functions are
CONCAT: Joins the first character value to the second character value.
SUBSTR: Returns specific characters from character value starting at a specific character position and going specified character positions long
INSTR: Returns the numeric position of a named string.
LENGTH: Returns the number of characters in the expression
LPAD: Pads the left side of a character.
RPAD: Pads the right-hand side of a character.
TRIM: Removes all specified characters from either the beginning or the ending of a string.
REPLACE: Replaces a sequence of characters in a string with another set of characters.
Character-Manipulation Functions.
VARCHAR2 – A variable-length character datatype whose data is converted by the RDBMS.
CHAR – The fixed-length datatype.
RAW – A variable-length datatype whose data is not converted by the RDBMS, but left in “raw” form.
Round function-used to round the number to the nearest position.
Trunc function-used to truncate/delete the number from some position.
Mod function-used to return the remainder of a dividend divided by a divisor.
Date Functions consists of
MONTHS_BETWEEN,NEXT_DAY,Last_day
General Functions
NVL(expr1, expr2): NVL() converts a null value to an actual value.
NVL2(expr1, expr2, expr3): If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned.
NULLIF() : The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression.
Case (): Facilitates conditional inquiries by doing the work of IF-THEN-ELSE statement.
DECODE(): Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement.
COALESCE(): The COALESCE() function examines the first expression, if the first expression is not null, it returns that expression; Otherwise, it does a COALESCE of the remaining expressions.
Displaying Data from Multiple Tables
Joins
Joins is used to combine the data between 2 or more table having a common relation between the files.
Types of joins.
The joins are classified as Equii Join,Left outer join, Right outer Join.
A Natural join can be an inner join, Left outer join, Right outer Join. The default is inner join
Equii Join
An equijoin is a join containing an equality operator.
An equijoin returns only the rows that have equivalent values for the specified columns.
An inner join is a join of two or more tables that returns only those rows.
Left outer join displays all the records from the left hand side of the table even if there are no matching records from the right hand side of the table.
Right outer join displays all the records from the Right hand side of the table even if there are no matching records from the left hand side of the table.
Full outer join displays all the records from the Right hand side of the table even if there are no matching records from the left hand side of the table and all the records from the Right hand side of the table even if there are no matching records from the left hand side of the table.
Non-equii join is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=.
Self-join is a query in which a table is joined to itself means it is used to compare values in a column with other values in the same column in the same table.
Cartesian Products also referred to as a cross-join, returns all the rows in all the tables listed in the query. Each row in the first table is paired with all the rows in the second table.
Creating Joins with the ON Clause.
The ON clause can be used to join columns that have different names.
The ON clause to specify conditions or specify columns to join.
Aggregating Data Using Group Functions
Group functions are used to operate on sets of rows to give one result per set.
The types of group functions / Aggregate functions are:
AVG, that calculates the average of the specified columns in a set of rows,
COUNT, calculating the number of rows in a set.
MAX, calculating the maximum,
MIN, calculating the minimum,
STDDEV, calculating the standard deviation,
SUM, calculating the sum,
VARIANCE, calculating the variance.
syntax for using Group functions is :
Select <col, ...>, group_function(col) from <table> where <condition> [Group by <column>]
[Order by <column>]
MIN and MAX Functions
MAX Syntax
Select MIN(column_name) from table_name where condition;
MIN Syntax
Select MAX(column_name) from table_name where condition;
COUNT() function.
It returns the number of rows in a table satisfying the condition specified in the WHERE clause. It sets the number of rows or non NULL column values.
COUNT() returns 0 if there were no matching records.
DISTINCT Keyword
It is used to list only the distinct (Removing the duplicate) values.
Having Clause.
It is used to specify that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions.
It was added to the SQL language because the WHERE keyword could not be used with aggregate functions.
Comments
Post a Comment