* Sinet course can be customized as per student requirement.
JOB PROFILE
Database Administrator
Data Server Manager
Oracle DBA Executive
Oracle DB Support
ORACLE SQL COURSE CONTENTS
( Can be customized )
1. Introduction in ORACLE SQL
What is DBMS (Database Management System).What is RDBMS (Relational Database Management System).What is oracle. Client / Server Architecture. Login Authentication. Handling data in a data base.
What is query?Select query:retrieve all records from a table, retrieve specific columns from a table.
Operator Precedence:Multiplication take priority over addition and subtraction.
Null values in arithmetic expression, Defining column alias, Concatenation operator, Literal character string, Eliminating duplicate rows, Eliminating duplicate rows.
2. Restricting and sorting data in ORACLE SQL
Limiting rows using a selection, Restrict the rows returned by Using where clause,
Comparison conditions- =,<,>,<=,>=,<>, Other comparison operators-Between… And(between two values),IN(match any of a list of values), LIKE (match a character pattern), IS NULL (is a null value)
Logical conditions-AND(returns true if both conditions are true), OR(returns true if either conditions is true), NOT(returns true if conditions is false)- Not In(match not of a list of values)
Sorting-sort in ascending order and descending order, Sort by column alias, sorting by multiple columns
3. Single row functions in ORACLE SQL
Describe the various type of functions available.Character functions:
Case Manipulation functions-LOWER(),UPPER(),INITCAP(), Character Manipulation functions:CONCAT(),SUBSTR(),LENGTH(),INSTR(),LPAD(),RPAD(),TRIM(),REPLACE()
Number/Mathematical functions-ROUND(),TRUNC(),MOD()Date functions:SYSDATE,MONTHS_BETWEEN(),ADD_MONTHS(),NEXT_DAY(),LAST_DAY(),ROUND(),TRUNC(),Arithematic with date
Conversion Functions:TO_CHAR() with dates, elements of date format, TO_CHAR function with numbers, TO_NUMBER(),TO_DATE()
Nesting functions-single row functions can be nested to any level.
General functions-work with any data type, NVL(), NVL2(), NULLIF(), COALESCE()
4. Conditional Expressions in ORACLE SQL
Conditional expressions use of if then else login within a sql statements.
CASE expression: facilitates conditional inquires by doing the work of an if…then…else statement.
DECODE function: facilitates conditional inquires by doing the work of a CASE or if…then…else statement.
5. Displaying data from multiple tables in ORACLE SQL
Cartesian product/ Cross join: joins one or more table without specify criteria.
Equijoins/inner join: joins one or more tables specify criteria.
Using table aliases: simplify queries by using table aliases.
Retrieving record with nonequijoins.Outer join: join to also see rows that do not meet join condition.
Self join: joining a table to itself. Natural Join: join two table that have equal values in all matched columns.
Creating Joins with the USING clause. Creating Joins with the ON clause.
Left outer join: joins two tables, includes all rows from the first table and matching.
Right outer join: joins tables, includes all rows from the second table and matching.
Full outer join: joins tables, includes all rows from first and second table corresponding matching rows.
6. Aggregate data using group functions in ORACLE SQL
The Group by clause is returns an aggregate value for the groups defined by the select statement.
Type of Group Functions: AVG(),COUNT(),MAX(),MIN(),SUM() Using Distinct Keyword.
Grouping by More than one column. The Having Clause is used to select particular rows from the result set obtained using the group by clause.
Nesting Group function.
7. subqueries in ORACLE SQL
Define Sub query, sub query syntax, type of sub query.
Single Row subqueries: returns only one row. Using comparison operators, group function in a subquery,
Having clause with subquery. Multiple row subqueries: return more than one row. Using ANY operator Using All operator
8. Substitution variables in ORACLE SQL
Using the & substitution variable, Character and date values with substitution variables. Specifying column names, expressions and text.
Using the && substitution variable: reuse the variable value without prompting.
9. Manipulating data in ORACLE SQL
Data Manipulation Language(DML) Adding a new row to a table using Insert statement, inserting rows with null values, insert specified values to table, copying rows from another table.
Edit data in a table using update statement, update two columns with subquery, updating rows based on another table.
Remove a row from table using Delete Statement, deleting rows based on another table.
Using a subquery in an insert statement. With check option keyword. Merge Statement.
10. Configuring and managing in ORACLE SQL
Naming rules Create table using CREATE TABLE statement. Querying in data dictionary : user_tables,user_objects, user_catalog.
Datatypes, datatime data types. Add a new column using ALTER command, modify an existing table using ALTER command, Drop a column using ALTER.
Rename table using RENAME command. Delete table using TRUNCATE.
Adding comments to a table.
11. Constraints (1) in ORACLE SQL
What are constraints? Defining constraints. Not null constraint: is defined at the column level
Primary key: requires the values in the column, to which it is attached to be unique and contain values other than NULL.
Set primary key on table creation. Remove primary key from table.
Set primary key to existing table Unique key-- requires the values in the column, to which it is attached to be unique. Set unique key on table creation. Remove unique key from table. Set unique key to existing table
12. Constraints (2) in ORACLE SQL
Foreign key refers to a column of another table or another column of the same table.
Set foreign key on table creation,. Remove foreign key from table.
Set foreign key to existing table Check constraint: defines a condition that each row must satisfy.
13. View in ORACLE SQL
View: Views are logical representation of data contained in a table. Why use Views. Create View, edit view using CREATE OR REPLACE VIEW, Creating complex view. Using with CHECK option. Denying DML operations .Remove view using DROP VIEW. Inline Views.
Top-n analysis: the n largest or smallest values of a column.
14. Other database objects in ORACLE SQL
Define a sequence to generate sequential numbers automatically.
Create sequence using SEQUENCE statement. Confirming sequences, NEXTVAL: return next available sequence value. CURRVAL: obtains the current sequence value Modifying sequence Remove Sequence
15 Index in ORACLE SQL
Index: The process of retrieving data, index also quickens the various select, updates and deletes issued on a table.
Create index using CREATE INDEX statement. When to create an index.
Confirming indexes. Function based index: based on an expressions.
Remove index using DROP. Synonyms: Simply access to objects .Create synonym using CREATE SYNONYM.
Remove synonym using DROP.
16. Controlling user access in ORACLE SQL
Creating users-DBA creates users by the CREATE USER statement.
Grant privilege to users: grant specific system privileges to a user.
Role: named group of related privileges. Creating and granting privileges to a role using CREATE ROLE and GRANT.
Change user password using ALTER statement. Object privilege: vary from object to object.
Grant object privilege using GRANT. Using WITH GRANT OPTION and PUBLIC Keyword.
Confirming Privileges granted.
17. SET Operators in ORACLE SQL
SET operator to combine multiple queries into a single query.
UNION: returns results from both queries after eliminating duplications.
UNION ALL: returns results from both queries including all duplications.
INTERSECT: returns results that are common to both queries.
MINUS: returns results rows from the first query that are not present in the second query.
18. Enhancements to the group by clause in ORACLE SQL
Review of the GROUP By clause, Review of HAVING clause.
ROLLUP operator: an extension to the group by clause. CUBE operator: extension to the group by clause.
Grouping function can be used with either the CUBE or ROLLUP operator.
Grouping set are a further extension of the group by clause.
Composite columns: collection of columns that are treated as a unit.
Concatenated groupings.
19. Advanced Subquerie in ORACLE SQL
Subquery is a select statement embedded in a clause of another SQL statement.
Multiple column subquery, pairwise comparison subquery, pairwise comparison subquery, usinf subquery in the FROM clause , Scalar subquery expressions,Correlated subquery
Exists operator, Not exists operator, Correlated update, correlated delete, With clause.
20. Hierarchical retrieval in ORACLE SQL
From the Bottom Up,From the top Down, Formatting hierarchical reports using LEVEL and LPAD.
DML and DDl statements Multitable conditional insert all
Conditional first Insert statement Pivoting insert create index with table statement
21. PRACTICE (1) in ORACLE SQL
PRACTICE QUESTIONS
22. PRACTICE (2) in ORACLE SQL
PRACTICE QUESTIONS
23. PRACTICE (3) in ORACLE SQL
PRACTICE QUESTIONS
24. PRACTICE (4) in ORACLE SQL
PRACTICE QUESTIONS
25. PRACTICE (5) in ORACLE SQL
PRACTICE QUESTIONS
SINET IS AN ISO 9001 : 2015 CERTIFIED SOFTWARE TRAINING INSTITUTE