Monthly Archives: January 2017

Oracle 11g dba questions for freshers and experienced continued

Distinction between SGA and PGA.

– SGA (System Global Area) is a storage place assigned during the set up of an instance.

– SGA is assigned as 40% of RAM dimension by standard.

– SGA dimension is managed by DB_CACHE_SIZE parameter described in initialization parameter file (init.ora details file or SPFILE).

– PGA (Program or Procedure Global Area) is a storage place that stores a user period particular details.

– PGA is assigned as 10% of RAM dimension by standard.

What are the hard drive elements in Oracle?

These are the actual elements which gets saved in the hard drive.

– Data files

– Upgrade Log files

– Control files

– Security password files

– Parameter files

What is System Change Number (SCN)?

– SCN is a exclusive ID that Oracle produces for every dedicated deal.

– It is documented for every alternation in the redo access.

– SCN is also produced for every checkpoint (CKPT) happened.

– It is an ever improving number which is customized for every 3 seconds

– You can get the SCN number by querying choose SCN from v$database from SQLPLUS.

What is Database Writer (DBWR) and when does DBWR create to the data file?

– DBWR is experience procedure that makes details prevents details from Databases shield storage cache to details.

There are 4 essential circumstances when DBWR makes to details file

– Every 3 seconds

– Whenever checkpoint occurs

– When server process needs 100 % free area in database shield storage cache to read new blocks.

– Whenever variety of changed blocks gets to a highest possible value.

What is Log Writer and when does LGWR writes to log file?

– LGWR makes redo or customized details from redo log shield storage cache to redo log details files in database.

– It is accountable for moving redo buffer details to online redo log details files, when you are making and a log switch also happens.

– LGWR makes to redo details files when the redo log shield is 1/3 rd complete.

– It also makes for every 3 seconds.

– Before DBWR writes customized blocks to the datafiles, LGWR makes to the log file

Which Table spaces are designed instantly when you build a database?

– SYSTEM tablespace is designed instantly during database development.

– It will be always on the internet when the database is start.

Other Tablespaces include

– SYSAUX tablespace

– UNDO tablespace

– TEMP tablespace

– UNDO & TEMP tablespace are optionally available when you build a database.

Which data file is utilized first when Oracle database is began and What is the main distinction between SPFILE and PFILE?

– Init<SID>.ora parameter details file or SPFILE is utilized first .( SID is example name)

– Configurations needed for beginning a database are saved as factors in this details file.

– SPFILE is by standard designed during database development whereas PFILE should be designed from SPFILE.

– PFILE is customer part written text details file whereas SPFILE is server part binary details file.

– SPFILE is a binary details file (it can’t be opened) whereas PFILE is a written text details file we can modify it and set parameter principles.

– Changes produced in SPFILE are dynamically impacted with operating database whereas PFILE changes are impacted after jumping the database.

– We can back-up SPFILE using RMAN.

What are benefits of using SPFILE over PFILE?

– SPFILE is available from Oracle 9i and above.

– Parameters in SPFILE are customized dynamically.

– You can’t create any changes to PFILE when the database is up.

– RMAN cant back-up PFILE, It can back-up SPFILE.

– SPFILE factors changes are examined before they are approved as it is managed by Oracle server thereby lowering a persons misprint mistakes.

How can you discover out if the database is using PFILE or SPFILE?

– You can question Powerful efficiency perspective (v$parameter) to know your database is using PFILE or SPFILE.

– SQL> choose value from V$parameter where name= ‘SPFILE’;

– A non-null value indicates the database is using SPFILE.

– Zero value indicates database is using PFILE.

– You can power a database to use a PFILE by providing a start-up control as

– SQL> start-up PFILE = ‘full direction of Pfile location’;

Where are parameter files stored and how can you commence a database using a particular parameter file?

– In UNIX they are saved in the place $ORACLE_HOME/dbs and ORACLE_HOME/database for Ms windows listing.

– Oracle by standard begins with SPFILE situated in $ORACLE_HOME/dbs.

– If you want to begin with the database with particular details file we can add it at the start-up control as

SQL > start-up PFILE = ‘full direction of parameter details file ‘;

– You could create PFILE from SPFILE as create PFILE from SPFILE;

– All the parameter principles are now customized with SPFILE.

– In the same way, create SPFILE from PFILE; control makes SPFILE from PFILE.

Thus you can join the dba training institute to know more about this topic.

Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr

Oracle 11g Database Interview Questions

What is a database?

- A single point of mechanism is offered for storing and retrieving information with the help of tables.

- Table is comprised of columns and rows where each column has particular feature and each row shows a value for the corresponding feature.

- It is a framework that saves data about the features of the entity and relationships among them.

- It also stores data types for features and indices.

- Well known DBMS consist of Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql database and sqlLite.

For information you can join the Oracle dba course in Pune.

What are the different kinds of storage space techniques available and which one is used by Oracle?

Two kinds of storage space techniques are available

- Relational Database Management System (RDBMS) and Hierarchical Storage Management System(HSM)

- Most databases use RDBMS design, Oracle also uses RDBMS design.

- Hierarchical Storage Management (HSM)

- Information Management System (IMS) from IBM.

- Integrated Data Management System (IDMS) from CA.

Explain some types of join methods.

Join methods are of mainly 3 types.

Merge Join -

- Planning both the tables using join key and then merge the rows which are categorized.

Nested loop join –

- It gets a result set after implementing narrow conditions based on the external table.

- Then it joins the inner table with the specific outcome set.

Hash join –

- It uses hash criteria first on smaller table and then on the other table to produce signed up with content. After that related series are came back.

What are the ingredients of sensible data design and list some variations between logical and physical data model?

Components of logical data model are

Entity – Enterprise represents an item that we use to store information. It has its own table.

Attribute – It symbolizes the details of the entity that we are interested in. It is saved as a column available and has particular datatype associated with it.

Record – It represents an assortment of all the qualities associated with an entity for just one condition, showed as row in a table.

Domain – It is the set of all the possible principles for a particular feature.

Relation – Represents a regards between two entities.

Difference between Logical and Physical data design.

- Logical data design symbolizes database in regards to sensible things, such as organizations and connections.

- Physical data design symbolizes database in regards to physical things, such as platforms and restrictions.

What is normalization? What are the different types of normalization?

- Normalization is a procedure of organizing the areas and platforms of a relational database to reduce redundancy and dependency.

- It helps you to save storage space space and guarantees reliability of our data.

There are six different regular forms

First Normal Form – If all actual websites contain atomic numbers only.

Second Normal Form – If it is in first regular form and every non key feature is fully functionally reliant on main key.

Third Normal Form – If it is in 2nd regular form and every non key feature is non transitively reliant on the main key.

Boyce Codd Normal Form – A relation R is in BCNF if and only every determinant is a primary key.

Fourth Normal Form

Fifth Normal Form

Differentiate between a database and instance and explain connection between them?

- Data base is an assortment of three important data files which consist of information, control data files and upgrade log data files which actually are available on a disk

- Whereas instance is a mixture of or acle qualifications procedure (SMON, PMON, DBWR, LGWR) and storage framework (SGA, PGA).

- Oracle background procedures running on a computer share same storage area.

- An example can install and open only an individual database, ever.

- A database may be installed and started out by one or more circumstances (using RAC).

What are the ingredients of SGA?

- SGA is used to store shared information across all database customers.

- It mainly contains Library cache, Data Dictionary storage space cache, Data base buffer Cache, Redo log Shield storage space cache, Shared Share.

Library storage space cache – It is used to shop Oracle claims.

Data Vocabulary Cache – It contains this is of Details source things and rights provided to customers.

Data Base buffer storage space cache – It keeps duplicates of data prevents which are frequently utilized, so that they can be recovered quicker for any future demands.

Redo log buffer storage space cache – It information all changes designed to the details.

Thus our Oracle training course is more than enough for you to make your career in this field.

Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr

Oracle Certification Now Teaches You DBMS Architecture

Oracle Certification now teaches you DBMS architecture

What does Two-Tier Framework mean?

A two-tier architecture is an application package architecture in which a presentation layer or interface operates on a customer, and a data layer or data structure gets saved on a server. Breaking these two elements into different places symbolizes a two-tier architecture, as instead of a single-tier architecture. Other kinds of multi-tier architectures add extra levels in allocated application style.

You can join the oracle dba course to know more about this field.

Two-Tier Architecture

Experts often compare a two-tier architecture to a three-tier architecture, where a third program or business part is included that serves as a middleman between the client or presentation layer and the data layer.

This can improve the efficiency of it and help with scalability. It can also remove many types of problems with misunderstandings, which can be triggered by multi-user accessibility in two-tier architectures.

However, the innovative complexness of three-tier architecture may mean more cost as well as.

An extra note on two-tier architecture is that the term “tier” generally relates to splitting the two application layers onto two different physical components of hardware.

Multi-layer programs can be designed on one level, but because of functional choices, many two-tier architectures use a computer for the first level and a web server for the second tier.

The design of a DBMS relies upon on its architecture. It can be central or decentralized or ordered.

The architecture of a DBMS can be seen as either single tier or multi-tier.

An n-tier architecture distinguishes the whole program into relevant but separate n segments, which can be individually modified, altered, changed, or replaced.

In 1-tier architecture, the DBMS is the only enterprise where the customer directly rests on the DBMS and uses it.

Any changes done here will straight be done on the DBMS itself. It does not provide useful resources for end-users. Databases developers and developers normally want to use single-tier architecture.

If the architecture of DBMS is 2-tier, then it must have a software through which the DBMS can be utilized. Programmers use 2-tier architecture where they connect to the DBMS by indications of a software.

Here the application level is entirely separate of the database with regards to function, style, and development.

3-tier Architecture

A 3-tier architecture distinguishes its levels from each other based on the complexness of customers and how they use the information existing in the database.

It is the preferred architecture to design a DBMS.

Database (Data) Tier − At this level, the database exists along with its query handling ‘languages’. We also have the interaction that determine the information and their restrictions at this level.

Application (Middle) Tier − At this level live the applying web server and the programs that connect to the database. For a customer, this program level provides an abstracted perspective of the database. End-users are unacquainted with any lifestyle of the database beyond the applying. At the other end, the database level is unaware of any other customer beyond the applying level. Hence, the applying part rests in the center and provides an arbitrator between the end-user and the database.

User (Presentation) Tier − End-users function on this level and they know nothing about any lifestyle of the database beyond this part. At this part, several opinions of the database can get offers for by the application. All opinions are produced by programs that live in the application level.

Multiple-tier database architecture is extremely changeable, as almost all its elements are separate and can be customized individually.

Thus you can join the best oracle training to make your career in this field.

Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr

You will get DBA Jobs If You Learn What is Storage System, Hurry Up!

Databases are saved in file formats, which contain records. At physical level, on some device, there are some actual data which is stored in the electromagnetic format. These storage space gadgets can be broadly categorized into three types −

Primary Storage − The storage space that is directly accessible to the CPU comes under this category.

CPU’s internal storage space (registers), fast memory (cache), and main memory (RAM) are directly accessible to the CPU, as they are all placed on the motherboard or CPU chipset.

This storage space is typically very small, very fast, and unstable.

For maintaining the data, this storage requires continous supply of power.

In case of a power failure, all its information is lost.

Join the dba certification course to know about storage space.

Secondary Storage − Secondary storage space used to storedatathrough backup.

This storage includes storage gadgets that are not a part of the CPU or motherboard, for example, magnetic disks, optical disks (DVD, CD, etc.), hard disks, flash drives, and magnetic tapes.

Tertiary Storage − Tertiary storage space is used for storing large number of data.

Since such storage space gadgets are external to the pc, they are the slowest in rate. These storage space mostly used to take the back up of an entire system. Optical disks and magnetic tapes are widely used as tertiary storage space.

Memory Hierarchy

A pc has a well-defined hierarchy of storage. A CPU can access main storage as well as its inbuilt signs up.

The access time of the main storage is obviously less than the CPU rate.

To minimize this rate mismatch, cache memory is introduced. Cache storage provides the fastest accessibility time and it contains information that is most frequently accessed by the CPU.

The storage with the fastest accessibility is the costliest one. Larger storage space gadgets offer slow rate and they are less expensive, however they can store large numbers of information as compared to CPU signs up or storage cache storage.

Magnetic Disks

Hard disk drives are the most common secondary storage space gadgets in the current generation of computers.

Magnetic disk is the name given to it, because they use the concept of magnetization to store information.

Hard disk consist of metal disks coated with magnetizable material. These disks are placed vertically on a spindle.

A read/write head moves in between the disks and is used to magnetize or de-magnetize the spot under it. A magnetized spot can be recognized as 0 (zero) or 1 (one).

Hard disks are formatted in a well-defined order to store information efficiently. A hard disk plate has many concentric circles on it, called tracks. Every track is further divided into sectors. A sector on a difficult drive typically stores 512 bytes of information.

Redundant Range of Independent Disks

RAID or Redundant Array of Independent Disks, is a technology to connect several secondary storage space gadgets and use them as a single storage space media.

RAID consists of an array of disks in which several disks are connected together to achieve different goals.

RAID levels define the use of hard disk arrays.

RAID 0

In this stage, a striped range of disk is implemented. The information is broken down into prevents and the prevents are distributed among pushes. Each difficult drive receives a prevent of information to write/read in parallel. It enhances the pace and performance of the difficult drive. There is no equality and backup in Level 0.

RAID 1

RAID 1 uses mirroring techniques. When information is sent to a RAID controller, it makes a copy of the data and then forward it to the array of disks. RAID stage 1 is also called mirroring and provides 100% redundancy in case of a failure.

RAID 2

RAID 2 stores Error Correction Code using Hamming distance for its information,shared on different disks. Like stage 0, each information bit in a word is recorded on a separate disk drive and ECC codes of the information words are saved on a different set pushes. Due to its complex structure and high cost, RAID 2 is not commercially available.

RAID 3

RAID 3 shares the information onto several disks. The equality bit produced for information word is saved on a different difficult drive. This technique makes it to overcome single disk drive failures.

RAID 4

In this stage, an entire combination of data is written onto information pushes and then the equality is produced and saved on a different disk drive.

Note that stage 3 uses byte-level striping, whereas stage 4 uses block-level striping. Both stage 3 and stage 4 require at least three disk for implementation of RAID.

RAID 5

RAID 5 writes whole information combined onto different disks, but the equality bits produced for information combined stripes are distributed among all the information disks rather than storing them on a different dedicated difficult drive.

RAID 6

RAID 6 is an extension of stage 5. In this stage, two independent parities are produced and saved in shared fashion among several pushes. Two parities provide additional fault tolerance. This stage requires at least four hard disk drives for application of RAID.

Thus our dba institute in Pune is more than enough for you to make your career in this field.

Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr

Learn about Concurrency Protocol in Oracle Certification Courses in Pune

If you consider multiprogramming environment where various transactions work together at the same time, it is vital to manage the concurrency of dealings.

We have concurrency management methods to make sure atomicity, solitude, and serializability of contingency dealings.

Concurrency management methods can be subdivided into two classes −

  1. Lock based protocols
  2. Time stamp based protocols

Learn all these as there are many database jobs in pune for freshers.

Lock-based Protocols

Database techniques prepared with lock-based methods use a procedure by which any deal cannot read or edit data until it gets an proper lock on it.

Locks are of two kinds −

Binary Lock − A data item has a lock and it can be in two states; it is either locked or unlocked.

Shared/exclusive − This type of lock distinguishes the locks based on their uses. If a lock is obtained on a data product to carry out write function, it is a unique lock.

Enabling more than one deal to make on the same data product would cause the data source into an unreliable state.

Read locks are shared because no data value is being modified.

Lock protocols are of 4 different types −

Simplistic Lock Protocol

Simplistic lock-based methods enable dealings to get yourself a lock on every item before a ‘write’ operation is conducted.

After the completion of ‘write’ function, the transactions may unlock the data item.

Pre-claiming Lock Protocol

Pre-claiming methods assess their functions and make a list of items on which they need locks.

Before starting a performance, the deal pre claims the locks it needs in advance. If all the locks are provided, the deal carries out and unveils all the locks when all its functions are over.

If all the locks are not provided, the deal comes back and stays until all the locks are provided.

Two-Phase Lock 2PL

This locking method separates the performance stage of a deal into three areas.

In the first aspect, when the deal begins to work, it looks for authorization for the locks it needs.

The second aspect is where the deal gets all the locks.

As soon as the deal releases its first lock, the third stage begins. In this stage, the deal cannot demand any new locks; it only releases the obtained lock.

Two-phase securing has two stages, one continues to grow, where all the tresses are being obtained by the transaction; and the second stage is reducing, where the hair organised by the deal are developing.

To declare a unique (write) lock, a deal must first obtain a shared (read) lock and then update it to a unique lock.

Strict Two-Phase Locking

The first stage of Strict-2PL is same as 2PL. After obtaining all the lock in the first stage, the deal carries on to operate normally.

But contrary to 2PL, Strict-2PL does not to unveils lock after using it. Strict-2PL keeps all the lock until the commit point and unveils all the locks at one time.

Timestamp-based Protocols

The most widely used concurrency method is the timestamp centered method.

This method uses either program time or logical counter as a timestamp.

Lock-based methods handle the purchase between the inconsistent sets among dealings at the period of performance, whereas timestamp-based methods begin being soon as a deal is designed.

Every deal has a timestamp associated with it, and the purchasing will depend on the age of the deal. A deal designed at 0002 time time would be over the age of all other dealings that come after it.

For example, any deal ‘y’ coming into the program at 0004 is 2 seconds young and the concern would be given to the mature one.

In inclusion, every data item is given the newest study and write-timestamp.

This allows the program know when the last ‘read and write’ function was conducted on the data item.

Timestamp Ordering Protocol

The timestamp-ordering method guarantees serializability among dealings in their inconsistent read and write functions.

This holds to the method program that the inconsistent couple of projects should be implemented according to the timestamp principles of the dealings.

The timestamp of deal Ti is denoted as TS(Ti).

Read time-stamp of data-item X is denoted by R-timestamp(X).

Write time-stamp of data-item X is denoted by W-timestamp(X).

Timestamp purchasing method works as follows −

If a deal Ti provides a read(X) function −

If TS(Ti) < W-timestamp(X)

Operation refused.

If TS(Ti) >= W-timestamp(X)

Operation implemented.

All data-item timestamps modified.

If a deal Ti problems a write(X) function −

If TS(Ti) < R-timestamp(X)

Operation refused.

If TS(Ti) < W-timestamp(X)

Operation refused and Ti rolled back.

Otherwise, function executed.

Thomas’ Write Rule

This concept declares if TS(Ti) < W-timestamp(X), then the procedure is refused and Ti is rolled back.

Time-stamp ordering rules can be customized for making the routine view serializable.

Instead of getting Ti rolled back, the ‘write’ function itself is ignored.

You can join the sql dba training in Pune to make your career in this field.

Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr

Best Oracle Certification Course In Pune With Placement

Introduction Oracle Certification

There are many industries that use Oracle data base, choosing the profile of Oracle DBAservice provider would be extremely – money making job in the present and future. CRB Tech provides real-time and targeted Oracle DBA/OCA certification training. CRB Tech has coined the Oracle DBA/OCA certification course content and curriculum based on learners need so they can work as a database administrator by joining the database administrator course in Pune.

1.What is the future of dba course?

If we choose to see the job prospects in the field of data base especially for freshers, it is really of a great demand. If your aim is to become a dba professional then you need to have an oracle certification and the idea of a database administrator, to maintain the data in a professional way.

2.How to be a skillful dba professional?

Dont worry, here is where CRB Tech comes into the picture by providing you the bestOracle training courses in Pune and there is no doubt. And we offer 100% placement oriented training and will ensure that you are an oracle certified professional.

So you are welcome to get registered for the database administrator courseAnd it is the best dba course offered in Pune.

3. What is the eligiblity criteria for dba training institute in Pune?

Anybody who is really interested to attend the course in the sql training institutes in pune are eligible to do so. Pre-requisite education criteria for doing this course is a degree in computers.

Other candidate preferences:

  1. Excellent communication skills

  2. Dream as a dba professional

  3. Lateral entry after experience

4. Key points of the DBA course

Assured Job offer:

Jobs are provided with 100% assurance but furthermore you will have an amazing DBAcareer by our quality centered extensive training developed only for you.

German language extra benefit:

Training you in a foreign language will make your career special

Candidate apt infrastructure:

We have impressive lab functions and DBA classroom training which is very much comfortable and convenient for all the candidates who are willing to make their career through us.

Tie up with Mid level companies and MNCs:

Ocean of opportunities provided for you and we will also train you in being capable for the DBA jobs.

Curriculum created by innovative level trainers:

Many Professional experts and industry specialized teachers have put their brains to coin this method for your future knowledge

Campus drives solely for you:

Candidates are provided with varied kinds of opportunities from mid level companies to MNCs through our DBA training institute in Pune.

Refining your enterprise presentation skills:

Your business presentation capabilities can be more eye-catching by the training we give you through the sessions and classes you may have to carry out later on.

5. Criteria for placement through CRB Tech

Proper outfit

Communication in English

Non-freshers can get lifetime guarantee

Earn and Learn

Compulsory attendance

6. Certification

You can be an Oracle Certified Professional after the completion of our DBA course in Pune.

7. Placement:

Our previous candidates are placed in IBM,Max secure, Mind Gate, Saturn Infotech and if you relate the facts of the variety of learners placed it is 23. And we likewise have LOI (Letter of Intent) within 15 days of training and it is nothing but the document provided for the agreement between two parties.

8. Syllabus

    1. Introduction :

  • List the features of Oracle 10g

  • Discuss the theoretical and physical aspects of a relational database

  • Describe the Oracle implementation of the RDBMS and ORDBMS

  • Understand the goals of the course

  • Identify the major structural components of the Oracle Database 10g

  • Retrieve row and column data from the table with the SELECT statement

  • Create reports of sorted and restricted data

  • Employ SQL functions to generate and retrieve customized data

  • Run Data Manipulation Language (DML) Systems

  • Obtain metadata by querying the dictionary views

  • Group Discussion

2. Retrieving Data Using the SQL effect statement :

  • Capabilites of SQL select statements

  • execute a basic select statement

  • Arithmetic Expressions, Operator Presidence

  • Defining a Null Value, Null Values in Arithmetic Expressions

  • Defining a Column Alias

  • Concatenation operator, Literal Character Strings

  • Alternate Quote(q) Operator, Duplicate rows, distinct

  • SQL and iSQL * Plus interaction, Logging into iSQL*Plus, Displaying table structure

  • Interacting with script files

  • iSQL*Plus History Page

  • Group Discussion

3. Restricting and Sorting Data :

  • Limiting Rows using a selection

  • Where clause with character strings and Dates, Comparison Conditions

  • Between, IN, LIKE(%,-)Condition

  • Logical Conditions, Not Operator, Rules of Precedence

  • ORDER BY Clause, Sorting asc, desc

  • Substitution Variables

  • Define,verify

  • Group Discussion

Unit test 1

4 .Using Single row functions to customize output :

  • Types of SQL function

  • Single Row functions

  • Character Functions

  • Using Case-Manipulation functions

  • Character Manipulation functions

  • Using the character manipulation functions

  • number function

  • Group discussion

  • Round, Trunc, MOD, sysdate, Function

  • Working with dates, RR Date Format

  • Arithmetic with dates

  • Date Manipulation Function

  • Conversion Function

  • Nesting Function

  • General Functions(NVL, NVL2, NULLIF, Coalesce, Case Expression, Decode Function)

  • Group Discussion

5 . Reporting Aggregated  Data Using Group functions :

  • Group Functions

  • min,max, count, avg, sum

  • group by clause

  • having clause

  • nesting group functions

  • Group Discussion

6. Display Data Using Multiple Tables : 

  • Types of joins

  • Cross join

  • Natural join

  • Using Clause

  • Full(two sided)outer join

  • Group Discussion

Unit test 2

7. Using Subqueries to Solve Queries : 

  • Arbitrary join conditions for outer joins

  • Single row subquery

  • Multirow subquery(IN, ANY,ALL)

  • Null Values in a subquery

  • Group Discussion

8 . Using the Set operators : 

  • Set Operators

  • Union, Union All, Minus, Intersect

  • Group Discussion

9. Manipulating data :

  • DML(insert, update, delete)

  • DDL(Truncate)

  • DCL(commit, Rollback, Savepoint)

  • Group Discussion

10. Using DDL Statements to Create and Manage table : 

  • Database objects

  • Create Table

  • Referencing Another User’s table

  • Default option, data types, including constraints

  • Constraint Guideline

  • NOT NULL constraint

  • Unique Constraint

  • Primary Key, Foreign Key

  • Check Constraint

  • Violating Constraint

  • Create table using subquery

  • Alter table, drop table

  • Group Discussion

Unit test 3

11. Create other schema objects :

  • View(simple, complex view)

  • Rules of view with example

  • Using with check option

  • Denying DML operations

  • Drop View

  • Sequence

  • next val, currval, modifying sequence

  • drop sequence

  • INDEX

  • Create index, Index Guideline, drop index

  • Synonyms

  • Create and remove synonyms

12. Managing objects with Data Dictionary views :

  • The Data Dictionary

  • Data Dictionary Structure

  • How to use the Dictionary views

  • User_objects and all_objects

  • Table,column, constraint, view, sequence, synonyms information

  • Adding comments to a table

  • Group Discussion

13. Controlling UserAccess :

  • Privileges(system level, object level)

  • Create, User, grant, revoke privilige

  • assign tablespace to user, create Role

  • Group Discussion

14. Managing Schema Objects :

  • Alter Table, modify column, Drop Column,

  • rename table name, column name

  • Drop table, set unused, adding droping deleting constraint

  • Enabling/disabling constraint

  • Create index with the create table

  • Function based index

  • Drop index

  • Drop table, purge table, recycle bin

  • Group Discussion

Unit test 4

15. Manipulating large data sets :

  • Using Subquery to manipulate data

  • Copying rows from another table

  • Updating columns with subquery

  • Updating rows Based on another table

  • Deleting rows based on another table

  • With check option on DML statements

  • Types of multiple insert

  • Multiple insert

  • Unconditional insert all

  • Conditional insert all

  • Conditional insert first

  • Pivotig Insert

  • Merge Statement

  • Tracking Changes in Data

  • Flashback version query

  • Version between clause

  • Group Discussion

16 . Generating Reports by grouping related data : 

  • Rollup, Cube, Grouping Function, Grouping Set

17. Managing data in Different TimeZones : 

  • TimeZone, TimeZone session parameter

  • current_date, current_timestamp, localtimestamp, dbtimestamp, sessiontimezone, timestamp datatype

  • Diff between date and time stamp

  • Timestamp with time zone data type

  • Timestamp with local time zone

  • Interval datatype

  • Group Discussion

Unit test 5

18. Retrieving Data Using Subqueries :

  • Multiple Column Subqueries

  • Column Comparison

  • Pairwiaw and Nonpariwise subquery

  • Scalar subquery

  • Correlated Subqueries

  • Exists Operator

  • Correlated Update/Delete

  • Group Discussion

19. Hierarchical Retrieval : 

  • Sample data from employees table

  • Natural tree structure

  • Hierarchical Queries

  • Walking the tree

  • Walking the tree from the Bottom up

  • Walking the tree from the TopDown

  • Ranking rows with the level Pseudocolumn

  • Formating Hierarchical Reports Using LEVEL and LPAD

  • Pruning Branches

  • Group Discussion

20. Regular Expression Support :

  • Regular Expression: Overview

  • Meta Characters

  • Regular Expression Functions

  • REGEXP Function Syntax

  • Performing Basic Searches

  • Checking the presence of a pattern

  • Example of extracting substrings

  • Replacing patterns

  • Regular Expressions Check Constraints

  • Group Discussion

UNIT Test 6

SQL MODULE END TEST

Just join in and we will make you the best.

Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr