Course:

Oracle SQL

Course Duration:

Class room training: 40 hours
Online training:         20 hours

Fee Details:

Course Fee: ₹ 15,000

  FREE Sinet Certification

  FREE Placement Registration

  ₹ 500 CASH BACK
      for One time payment.

* Conditions Apply.

  Oracle Certification
      Database SQL 1Z0-071
      Price: ₹ 18538*.

      Show Model

  Sinet Certification
      ISO 9001 2015 logo embossed
      with Online reference.

      Show Model

  SQL Developer
  Oracle DB Developer
  Production Support

For more information contact now

8086 800 700
8086 800 700
sinetlimited@gmail.com

+91 9946 668 186
+91 04933 227 816
info@sinet.in

Oracle SQL Syllabus:

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. Oracle Query Practice and Solutions

Create complex Oracle queries and solutions

* Each Session include:
Faculty training, Lab Practice, Lab Exercises and Portal based assignments.
* Sinet Education reserved the right to alter the course contents, offers, price, certificates etc.