SQL

Course Overview:

SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). Originally based upon relational algebra and tuple relational calculus, SQL consists of a data definition language, data manipulation language, and Data Control Language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control

Course Content:

The Oracle database environment

   ♦ Reviewing basic architecture concepts
     Oracle 10g Architecture
     Main features of 9i and 10g

Basic SQL*PLUS commands

 ♦ Using script files, START, @, GET, SAVE, LIST, PROMPT, PAUSE, ACCEPT,   DEFINE, SPOOL

Basic SELECT Statement

♦ Writing the statement in sql plus, Running the SELECT statement

Ordering the output

♦ Single Column, Descending order, multiple column sorts

Conditional retrieval of data

♦ Working with complex conditions, AND OR NOT LIKE BETWEEN

Working with variables

♦ Creating and using variables, Command substitution

Pseudo columns and functions

♦ Pseudo Columns, Row num, Sys date, User & UID, the Dual Table
♦ Working with character functions
♦ UPPER, LOWER, INITCAP, RPAD(), RTRIM(), SUBSTR(), INSTR()

TRANSLATE(), REPLACE(), GREATEST(), LEAST(), DECODE

♦ Working with date functions
♦ TO_CHAR(), TO_DATE(), MONTHS_BETWEEN(), ADD_MONTHS(), LAST_DAY(), NEXT_DAY()

Using non-character function

♦ ROUND (), TRUNC (), SIGN (), Working with multiple tables, Different type of Joins, Writing Outer Joins

Using the SET operators

♦ Union, Intersect, minus

Aggregating data using group functions

♦ GROUP BY, HAVING

Creating Sub queries

♦ Single Row sub queries, Multiple row Sub queries

Enhancing groups function

♦ ROLLUP, CUBE

Transaction Control Language

♦ Rollback, Commit, save point

Processing hierarchies

♦ creating the Tree structure, LEVEL, CONNECT BY

Data Manipulation Language

♦ INSERT, UPDATE, DELETE

Data Definition Language

♦ CREATE, TRUNCATE, ALTER, DROP, RENAME, DESCRIBE

Using Declarative Constraints

♦Views, Sequences, Synonyms, Indexes, USER_TABLES, USER_TAB_COLUMNS,
USER_OBJECTS, USER_IND_COLUMNS, USER_UPDATEABLE_COLUMNS, Materialized
Views, Other Data dictionary views

Database Security

♦ Object Privileges, Granting access to objects
 

Improving query performance

♦ Planning and managing the tuning process, Employing tuning tools,
♦ Running EXPLAIN PLAN and auto trace, SQL Trace and TKPROF output,
♦ Optimizer concepts, Fundamentals of access paths,
♦ Gathering object and system statistics with DBMS_STATS,
♦ Utilizing hints and optimizer mode,
♦ Specifying first-rows optimization,
♦ Determining the driving table

Other Concepts

♦ The Relational Database Model, The system Development Lifecycle, Overview of normalization

We can assure a 100% job guarantee and Placement. Contact us for Free - Demo