SQL

What is SQL?

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

SQL is a Standard – BUT….

Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

Using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

• An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
• To use a server-side scripting language, like PHP or ASP
• To use SQL to get the data you want
• To use HTML / CSS to style the page

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

Look at the “Customers” table:

Example

SELECT * FROM Customers;

Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. A field is a column in a table that is designed to maintain specific information about every record in the table.

A record, also called a row, is each individual entry that exists in a table. For example, there are 91 records in the above Customers table. A record is a horizontal entity in a table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.

In this tutorial we will use the well-known Northwind sample database (included in MS Access and MS SQL Server).

Below is a selection from the “Customers” table:

The table above contains five records (one for each customer) and seven columns (CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country).

SQL Statements

Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement selects all the records in the “Customers” table:

Example

SELECT * FROM Customers;

In this tutorial we will teach you all about the different SQL statements.

Keep in Mind That…

SQL keywords are NOT case sensitive: select is the same as SELECT

In this tutorial we will write all SQL keywords in upper-case.

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

In this tutorial, we will use semicolon at the end of each SQL statement.

Some of The Most Important SQL Commands

• SELECT – extracts data from a database
• UPDATE – updates data in a database
• DELETE – deletes data from a database
• INSERT INTO – inserts new data into a database
• CREATE DATABASE – creates a new database
• ALTER DATABASE – modifies a database
• CREATE TABLE – creates a new table
• ALTER TABLE – modifies a table
• DROP TABLE – deletes a table
• CREATE INDEX – creates an index (search key)
• DROP INDEX – deletes an index

CONTENT

• Fundamentals of Database
• Introduction to Database
• Database Models
• Introduction to Oracle Database
• Oracle Database History
• Oracle 11g Server and DB Architecture

SQL

• Introduction to SQL
• Introduction to SQL *Plus
• Role of SQL in Oracle 11g
• Classification of SQL Commands
• Data Definition Languages (DDL) commands
• Oracle database 11g Schema Objects
• Oracle Data Dictionary
• Oracle Naming conventions
• Oracle Data types
• Alternation of Table Definition and it’s options
• Pseudo columns Introduction
• Table Truncation and it’s advantages
• Data Manipulation Language (DML) Commands
• Insertion of Data (Value , Address and Select method )
• Insertion of Nulls and Overriding the Nulls with User defined Values
• Insertion of Data in required formats
• Data Loading methods in Oracle 11g
• Data Updation
• Techniques of updation
• Complex Data Updation
• Correlated Query mechanism in Update
• Data Deletion
• Simple Data Deletion
• Critical Data Deletion 
• Table Delete Vs Table Truncation
• Transaction Control Language commands
• Data Retrieving Language(DRL) command SELECT
• Conditions
• Expressions
• Restricting ans Sorting data
• SELECT command and it’s clauses
• Operators Types of Operators in Oracle 11g & Filters
• Functions
• Single row functions,
• Multiple row functions
• Null functions
• Analytical functions
• Search functions
• Hierarchical functions
• Error functions
• Regular expressions(10g);
• Types of functions in Oracle 11g
• Pseudo Columns of Oracle 11g
• Displaying data from multiple tables(joins)
• Introduction to Table Join
• Types of Joins
• Inner & Outer Join/partition outer join(10g)
• Equi / Non Equi / Self Join / Cartesian Join/natural joins
• Vertical joins(set operators)
• Sub Queries and Simple sub query
• Complex Sub Query on multiple data source and Co-related Sub Query
• Top-n queries, hierarchical queries, scalar queries, flashback queries,version queries.
• Intigrity Constraints
• Oracle Database Objects
• Index and Introduction to Index
• Clusters and Introduction to Cluster
• Type of Clusters and Their Usage
• Explain plan Command Usage and Oracle Scripts
• Views and Introduction to Views
• Type of Views and usage
• Performance issues with Views
• Background process of Views
• DML restrictions on Views
• Materialized View and Usage
• Synonym and Introduction to Synonyms and Usage
• Sequence  and Introduction to Sequence and Usage
• Pseudo Columns Usage in Sequence
• Data Control Language commands
• What is privilege
• What is role
• Grating Privilege
• Removing Privilege
• Cascading Privilege
• Sql  * Plus Commands
• Environment setting commands
• Alter session language
• Alter system language
• Sql developer

PL/SQL

• Introduction to Programming Language
• Procedures Vs Non-Procedures Language
• Limitation of ANSI SQL and Oracle SQL
• Introduction to Oracle PL/SQL
• PL/SQL Usage in Production Database
• Key benefits of PL/SQL over SQL
• Anchor datatypes or Attributes
• Composite datatypes
• Collections
• PL/SQL block structure & Designing
• Scope and  Visibility
• Constructs of PL/SQL
• Assignment operations 
• Debugging statement 
• Flow Control Statement 
• IF / NESTED IF / EXIT / GOTO /
• Iterative statements
• Simple Loop / While Loop / For Loop
• Continue statement(11g)
• Embedded SQL
• Introduction to Embedded SQL
• Role of Embedded SQL in PL/SQL
• Constructs of Embedded SQL
• Transaction Mngmt Using Embedded SQL
• Dynamic SQL 
• Introduction to Dynamic SQL
• Usage of Dynamic SQL in PL/SQL
• Introduction to Exceptions
• Importance of Exceptions in PL/SQL
• Type of Exceptions
• Exception handling
• Save exceptions
• Introduction and STANDARD Package
• Introduction to User Defined Exceptions
• Non predefined exceptions
• Exception cases
• Usage of PRAGMA EXCEPTION_INIT()
• Cursor Management in PL/SQL
• Introduction to cursor management
• Pictorial presentation of cursor mechanism
• Introduction and usage of implicit cursor
• Introduction and usage of Explicit cursors
• Cursor attributes
• Cursor using simple loop
• Cursor using while loop
• Cursor using for loop
• Cursor exceptions
• Cursor expression
• Data Locking
• Data Manipulation through Cursor
• REF cursor(strong and weak)
• Using ref cursor variable as parameter
• Bulk Fetch and Bulk Data Retrieval in PL/SQL
• Bulk Collection
• Bulk Binding mechanism of Cursor (for all statement)
• Dynamic behavior of cursor mnt.
• SUB PROGRAMS
• Types of PL/SQL blocks
• Labelled blocks
• Anonymous PL/SQL blocks
• Named PL/SQL blocks
• Forward Declaration of Local Block
• Introduction to storage PL/SQL block
• Stored Procedures
• Stored Functions
• Nocopy(9i)
• Autonomous Transaction Management of PL/SQL
• PACKAGES
• Introduction to Package
• Stand-alone schema Vs Packaged Object
• Encapsulation mechanism of Package
• Data security
• Function overloading mechanism of Package
• Introduction and Usage of Package
• Oracle supplied packages
• Package data
• Restrict Reference and Complex Hints
• Usage of Pragma Serially_ reusable
• DB TRIGGERS
• Introduction to Database Trigger
• Types of Triggers
• Triggering events
• Usage of Old & New Reference
• Instead of  Trigger
• Enforcing the referential integrity constraint
• Compound Trigger(11g);
• Follows key word(11g);
• Defining a disable trigger(11g)
• Trigger Cascading
• Enabling/Disabling Trigger
• Schema Trigger
• Table Mutation Error
• Transaction Audit Trigger
• Advanced Pl/sql Topics
• User Defined Types (RECORDS)
• Subtypes of Pl/sql
• Automation Transaction
• Advantages of Autonomous Transaction
• Usage of Autonomous Transaction
• Scope of autonomous Transaction
• Usage of Autonomous Transaction in Trigger
• Suing FORALL Statement
• About % BULK_ROWCOUNT
• FGA and FGAC(VPD)
• Table functions
• Managing database dependencies
• Designing pl/sql code
• Using collections
• Working with lobs
• Using secure file lobs
• C ompiling pl/sql code
• Tuning pl/sql code
• Pragma inline(11g)
• Caching  to improve performance
• Analysing pl/sql code
• Profiling pl/sql code
• Tracing pl/sql code
• Safeguarding pl/sql code against sql injection
• Pl/sql Architecture.

NARAYANA REDDY

15+ EXPERIANCE IN SQL TEACHING AND WOKR

SQL CERTIFIED CANDIDATE