Payilagam oracle sql training syllabus

Payilagam oracle sql training syllabus

• 1.
  Payilagam Software traininginstitute Payilagam Software Training Institute, No:4/67E, Sri Balaji’s Ishwarya, Vijaya Nagar 3rd Cross Street, Velachery, Chennai – 600042. 044-22592370, 8344777333, 8883775533. Mail : info@payilagam.com, Website : www.payilagam.com Oracle Standard SQL contents (Includes ANSI SQL / ISO SQL) Many new topics added apart from Oracle Standard curriculum based on real-time requirements in various domain (Manufacturing / Finance / Human resource / Inventory / Insurance) various Data Warehouse. 1. Basic DATABASE Concept and SQL Basic history of database concept: DBMS, RDBMS, ORDBMS Advantage of ORACLE database and version information Interface tools usage: sqlplus, isqlplus, sqldeveloper, Toad SQL Language overview: DQL, DML, DDL, DCL, TCL What is the usage of ANSI standard? SELECT Command - Column Alias Rules, String data, Concatenations with various data Null Value handling with number and characters, Arithmetic Operator Concatenation Operator, Eliminating Duplicate Rows 2. Restricting and Sorting Data WHERE Clause - Character Strings and Dates, number General Comparison Conditions = > >= < <= <> Other Comparison BETWEEN, IN, LIKE, NULL Logical Conditions AND OR NOT ORDER BY Clause, Sorting by Column Alias, Column Position, Multiple Columns 3. Single-Row Functions Character Functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, CONCAT, LTRIM, RTRIM, TRIM, REPLACE, TRANSLATE, AND REVERSE Number Functions: ROUND, TRUNC, MOD, POWER, CEIL, FLOOR, ABS 1 Payilagam software training institute | www.payilagam.com | 83 44 777 333 | 8883 77 55 33 |
• 2.
  Payilagam Software traininginstitute Dates Functions: SYSDATE, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ADD_MONTHS, ROUND, TRUNC, Arithmetic on Date Conversion Functions: Implicit Data-Type Conversion & Explicit Data-Type Conversion, TO_CHAR, TO_NUMBER, TO_DATE General Functions: NVL, NVL2, NULLIF, COALESCE CASE Expression, DECODE Nested function with real-time usage 4. JOINS EQUI JOIN / SIMPLE JOIN / NORMAL JOIN ANSI JOIN, LEFT OUTER, RIGHT OUTER, FULL OUTER NATURAL JOIN, NATURAL OUTER JOINS INNER JOIN, JOIN ... USING clause, JOIN ... ON clause, CROSS JOIN, NON-EQUI JOIN, SELF JOIN ORACLE STANDARD OUTER JOINS. Multi table Joins, Complex Joins – How to simplified complex joins. 5. Multi-row Functions Group Functions Rules, SUM, MIN, MAX, COUNT, AVG Creating Groups of Data: GROUP BY Clause Filtering Group Results: The HAVING Clause 6. Sub-queries Single-Row Subqueries- Rules, Operators: = > >= < <= <> Null Values in a Subquery Multi-Row Subqueries- Rules, Operators: IN, ANY, ALL 7. Reporting data using interface commands pagesize, linesize , column heading , column format , colsep tTitle , bTitle , break on column, spool , CSV file generation, Text file generation 8. Data Manipulation Language DML and Transaction Control Language TCL DML: INSERT, UPDATE, DELETE, MERGE TCL: COMMIT, ROLLBACK, SAVEPOINT 9. Data Definition Language - DDL 2 Payilagam software training institute | www.payilagam.com | 83 44 777 333 | 8883 77 55 33 |
• 3.
  Payilagam Software traininginstitute DDL: CREATE, ALTER, RENAME, DROP, TRUNCATE DEFAULT OPTION. Constrain table copy 10. Constraints NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK Column Level Constraint, Table Level Constraint – Naming constraints and usage Adding a Constraint, Dropping a Constraint, Disabling Constraints, Enabling Constraints Validating Constraints 11. Views Simple Views and Complex Views – Create, Drop, Source Code Rules for Performing DML Operations on a View WITH CHECK OPTION, WITH READ ONLY Inline Views Materialized View – Create, Refresh, Drop - Usage 12. Other Database Objects Sequence- NEXTVAL and CURRVAL Index - When to Create an Index, When Not to Create an Index. Synonyms 13. DCL COMMANDS Creating Users Granting / Revoking Privileges Creating and Granting Privileges to a Role 14. Dictionary Tables Tables, Views, Synonyms, Index, Sequence, Constrains, Source and other Dictionary 15. SET Operators UNION, UNION ALL, INTERSECT, MINUS 3 Payilagam software training institute | www.payilagam.com | 83 44 777 333 | 8883 77 55 33 |
• 4.
  Payilagam Software traininginstitute 16. Advanced Date-time Functions TIME ZONES, SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP SESSIONTIMEZONE, Storing time zone data in Table EXTRACT, TO_YMINTERVAL 17. Advanced GROUP BY Clause Group by with ROLLUP, Group by with CUBE, GROUPING SETS 18. Advanced Sub queries Pair wise Comparison Sub query, Non pair wise Comparison Sub query Correlated Sub queries, Correlated UPDATE, Correlated DELETE EXISTS, NOT EXISTS Operator 19. Hierarchical Retrieval The Tree: From the Bottom Up, From the Top Down LEVEL Pseudo column, Connect by prior, 20. Multi-table Insert Unconditional INSERT ALL Conditional INSERT ALL Conditional FIRST INSERT 21. DATA LOADER SQLLDR – Loading CSV file / Flat file into ORACLE table. 4 Payilagam software training institute | www.payilagam.com | 83 44 777 333 | 8883 77 55 33 |
• 5.
  Payilagam Software traininginstitute 22. Analytic Functions WM_CONCAT, LAG, LEAD, RANK, DENSE_RANK Query_by partition_clause with sum, min, max, avg, count, order_by_clause with sum, min, max, avg, count, Psudo column: Rownum, Rowid, - Elimination duplicate data Connect by rownum , Connect by Level – Generating random numbers, random dates, Quote Operator syntax and usage 23. Backup Export / Import SCHEMA - using TOAD - using ORACLE direct command 24. General What is migration? Migration Estimating, Planning, Preparation – Simple Scenario / Complex Scenario. Warm Regards Payilagam software training institute, 8344777333, 8883775533. Mail: info@payilaga.com Website: www.payilagam.com 5 Payilagam software training institute | www.payilagam.com | 83 44 777 333 | 8883 77 55 33 |