Database QA

Ms SQL Server Interview Question attended:

------------------------------------------------------------------------------------------------------------

                      1.What is  the use of  CTE ( Common Table Expression)?

a.no need of temp table & also no loop required.
------------------------------------------------------------------------------------------------------------
ans.

SQL SERVER – Common Table Expression (CTE) and Few Observation

SQL SERVER – 2005 – 2008 – Delete Duplicate Rows

Deleting duplicate rows
Counting duplicate rows

Delete duplicate rows using CTE & ROW_NUMBER( ) feature of  SQL Server 2005 & SQL Server 2008.

/* Create Table */

CREATE TABLE DuplicateRecordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRecordTable
SELECT 1,1
UNION ALL
SELECT 1,1 --duplicate
UNION ALL
SELECT 1,2
UNION ALL
SELECT 1,2 --duplicate
...
GO

/* Delete Duplicate records */

WITH CTE (Col1,Col2, DuplicateCount)
AS
(
SELECT Col1,Col2,
ROW_NUMBER( ) OVER(PARTITION BY  Col1,Col2 ORDER BY Col1) AS
DuplicateCount
FROM DuplicateRecordTable
)
DELETE  FROM  CTE WHERE DuplicateCount > 1

GO

Note:
MAXRECURSION - Prevent CTE  Infinite Loop

SELECT  *  FROM  Emp_CTE OPTION (MAXRECURSION 5)

------------------------------------------------------------------------------------------------------------
(dba)
2.What is a snap-shot?

ans.
introduction to sql server 2008 create snapshot

create snapshot - sql server 2008 - enterprise edition only.

Microsoft SQL Server Management Studio

yournamedatabase(SQL Server 10.0.1600 - yourname\Administrator)
-Database
  +System Databases
      Database Snapshots
   +AdventureWorks
   +..
+Security
+Server Objects
+Replication

SQLQuery window:By Clicking New Query tab.

CREATE  DATABASE  AdventureWorks_SNAPSHOT_TODAY_DATE_TIME
ON
(NAME = AdventureWorks_data, FILENAME =
'c:\snapshot\AdventureWorks_SNAP_SHOT_TODAY_DATE_TIME.mdf')
AS SNAPSHOT OF AdventureWorks
GO
--After running the above command it creates an empty file, it creates with .mdf
--database and empty file ,its readonly even if its of database
--no transaction lob as there will no write to it.
a.what really happens.
i.when you change some to a table or field, Before writing to the database
it writes the original things of database to the snap shot.

This slows down the database.Keep on modify database the snapshot file
will change.Say you change 50% of database file snap shot will be upto that.

When snapshot is written it writes when first time the modificaiton done.But second , third time whatever
is modified its not captured. Even the first modification won't be , previous to first modification whatever
original it is , written to the snap shot.

b.if  you query the snap shot , what happens? It searches for  the information in the snap shot then when
not found searches in the database .mdf file.It searched in both. As if there would be no modification to the
data it won't be written to the snapshot.

c.You can not have a full indexed database with a snap shot.

i.once you have created a snap shot u can not drop the database.
ii.you can not deattach or restore a database.
(sysdatabase u can not create snapshot)
iii.u can have multiple snapshots but need to delete the previous ones.
u won't be able to revert the data from snap shot.
iv.when you restore the database from snap shot the backup link is broken
so you have to backup fully again.
iv.if you have multiple files for database, each file has to be specified for
snap-shot.

i.what is snap shot?
ans.
Snap shot is not replica of database or copy of database.
At the beginning snap shot is not a copy of database its a  "Parse File" .
Parse File is empty it does not have User Data.
Question is why we have a empty file at the beginning.
If from the begining it has all the data its a very big file that not the idea for snap shot.
Snap shot is not backup to database, backup is required.
Snap is protetion to the database.Backup has to be their.
If anything goes wrong with database it can revert it back, provided if you
are ok to loose the data after the change is done.
Snapshot is good for reporting tool.
------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
3.Slowly changing dimension


http://en.wikipedia.org/wiki/Slowly_changing_dimension

Type I

This methodology overwrites old with new data, and therefore does not track historical data. Its common uses are for misspelled names. (Assuming you won't need to know how it was misspelled in the past.)

Type II

This method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. Unlimited history is preserved for each insert.
For example, if the supplier relocates to Illinois the version numbers will be incremented sequentially:

Type III

This method tracks changes using separate columns and preserves limited history. The Type II preserves unlimited history as it's limited to the number of columns designated for storing historical data. The original table structure in Type I and Type II is the same but Type III adds additional columns. In the following example, an additional column has been added to the to record the supplier's original state - only the previous history is stored.


------------------------------------------------------------------------------------------------------------
(dba)
Tells similar things to prepare.

Learn how to Replicate Data in Microsoft SQL Server 2008-Designing and

Log Shipping, Mirroring are not form of replication.
The above for high availibity of the database.
Both Shipping & Mirroring dealing with the primary database.
There is a secondary database.

                                 Publisher
                                Subscriber
                                 Agent





------------------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------------------
Oracle Database:

1.What is a Materialized Views?

ans.

Materialized Views

Materialized View Concepts and Architecture.

Materialized View Concepts

1. Oracle uses material views ( also known as snapshots in prior releases) to replicate data.

What is a Materialized View?

1. A materialized view is  a replica of  a target master from a single point in time.

Why Use Materialized Views?

ans.

Ease Network Loads:

i. If one goal is to reduce network loads, use materialized views to distribute 

------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------

Oracle Database Related Page

Oracle Database Learning:


------------------------------------------------------------------------------------------------------------
iggy-lesson-1-of-40

fyi: Nothing much to read. The first article that i had got on Optimization Of  Database thats the best.

Only the one the below article fine the "Logical Physical Design" [designing normalized database] .

Little to note and useful that the "Physical Database Design" need to know on Indexes , Partitions , Clusters ,Materialized Views.

Indexes help in restriction , projection , joins of all types and sorting.

Partitions and clusters can be used to co-late data with similar characteristics in order to limit the physical
I/O.

Materialized Views purpose is to improve the efficiency of  database operations as  joins and aggregations ahead of time.

In Depth Research for writing on Practical level.

Resource:

1.Optimization article is best to refer it tells exact practical steps, best article ever.

2. DBA related seems. Partitioning::Clustering

Partitioning:


Summary:



------------------------------------------------------------------------------------------------------------
1.Physical Database Design for Oracle Databases: Necessity & Definition

------------------------------------------------------------------------------------------------------------
Definitions of  Logical  &  Physical Database Design:
i.Database  design can be divided into "Logical Database Design" & a "Physical Database Design".
a.Logical database design is  the process of designing normalized database tables.
example:
Project ( Project number, Project description)
Part ( Part number, part name, part description, quantity-on-hand, quantity-on-order)

When a project makes use of  a part, the quantity of that part commited to the given project.
You will construct one table.
Table details in Yellow notebook.Page 2 , If  the quantity of the column changes multiple rows need to be
updated. Through the process of Data Modelling , database normalization a better solution. Three tables.
Page 3,4  have the create statements.


The above CREATE TABLE commands constructs the table in the Oracle Database. This concludes the
process of  "logical database" design. The database design does not complete here as the question of
performance is their.

The SQL queries , need indexes and other mechanism to retrieve data efficiently. They are transformed by
the query optimizer into sequence of database operations ( restriction , projection , inner join, outer join,
semi-join, anti-join, aggregation, and sorting). These operations are illustrated in the following examples:

Restriction ( rows removed ), Projection (column removed) operations:








------------------------------------------------------------------------------------------------------------
20 Oracle Lessons


------------------------------------------------------------------------------------------------------------
Oracle's MySql Blog


------------------------------------------------------------------------------------------------------------
1. Explain oracle database architecture.

Oracle Architecture in less than 10 minutes

1.What  happens when you start a Database?

Questions that pops like popcorn in mind? The song.. Popcorn popping on the apricot tree..

               Where the Oracle is situated? - Oracle Home

                Which Database? - ORACLE_SID
                 ( The same machine can have different vers. of  oracle running.)

2.what does the administrator does daily in his life from morning to night?

              MyServer> sqlplus       /nolog

              SQL*Plus:  Release 11...
              Copyright (c)  1982, 2007 ,  Oracle...

              SQL> connect    sys     as      sysdba
              Enter  password:
              Connected to an  idle instance.

Here the administrator is not  connected to the database but checking through a dummy connection  all
connection parameters.

The above to check if  the login or say user login credentials have right priveleges to start the
database engine.

3. Then

      SQL > startup

      The above program starts by reading a file

                  $ ORACLE_HOME / dbs

                  spfile$ { ORACLE_SID} . ora
  ( reads all parameters of  the Database which we come to know from ORACLE_SID)

The above  is a binary file.

INT.ORA file - text file which tells on the binary file.

4. Database Parameters:
    i. The above file having database parameters are in SGA area.
      This is where Oracle works.
      No. Of  utility processes also start here. Oracle related windows processes start.
      SGA - System Global Area.
   ii.when memory are located and processes started.
      sql> startup

      ORACLE  instance started.
       Total System Global Area            627....bytes( seems 627 mega bytes - near to a 1gb)
       Fixed Size                                    .........bytes
       Variable Size                                ........bytes
        Database Buffers                         ........bytes
        Redo Buffers                               ........bytes

5. Then Oracle will read an important file : Control Files , location is mentioned in parameter file.
     Here oracle finds names of  data files, temporary files, log files that together make up the database.

----------------------------------------------------------------------------------------------------------
summary:1

Well  as you know the system global area (sga) is  the hidden treasure area having all info.
Why not it be called that , even to start the engine the ORACLE_HOME/db/ORACLE_SID.ora file
which has  the database to start and also this has the path details to Control file ( tells data file, temporary
file, log file which makes the database) and the file is binary so understand it needs the INT.ora file.

Commands:

1.Myserver> sqlplus  /nolog

2.SQL> connect  sys as sysdba

3.SQL> startup

info.
The above program starts reading a file.

${ORACLE_HOME}\dbs

       spfile${ORACLE_SID}.ora

----------------------------------------------------------------------------------------------------------
     
Lesson 2:

1.
  Control file also checks :
          global constitency
          if something need to be restored
          if the database got properly shutdown
          or if  any unfinished transactions need to be rolled back
without control file your database is dead.

2.
SQL>  startup

ORACLE  instance started.

Total size SGa
.....
Database mounted.

The database mounted means all files are identified.

3. Then step is "Opening of  files for Read & Write operations.

shows. after Database mounted , Database opened.

SQL> exit

Disconnected from Oracle instance

4. if you want users from other machines to log on to Application Server , which is
what happens in daily life. you have to launch the listner command.

MyServer> lsnrctl start

LSNRCTL

Starting  /../bin/tnslsnr:

tnslsnr  is  the listner which waits for the incoming connections.

it shows details in configuration of  tnslsnr.ora file , the program runs in the background.

          Now everything in place.

5. Want the client program want to connect to database then it need to provide the below.

     Host Name: Name of  the host in which oracle runs.
     Port: tnslsnr is listening
     Service: the identifier of the database you want to connect to.

Several ways to provide the above info.

Directly specified.: Done in JDBC ( pure java driver)

if  client using oracle libraries then the client get the parameters from tnsnames.ora file.

The above two used.

Other methods are:

LDAP directory
Network Information Services ( as an substitutes to tnsnames.ora file).

6. But then Oracle needs you to sign on.

Username/Password

Authentication: LDAP identification
   
client -> TNSLSNR


Then TNSLSNR will run a process so that client becomes a dedicated server or

redirect to an already existing shared connection which is much

i.Server process is the client proxy in oracle side , it can access SGA , it will locate
private memory area called (PGA). In PGA it stores what is private to the process.

7. Closer look at "SGA" , it is shared between different processes.

Fixed System Area.
Variable Area
      Shared Pool - have compiled version of the queries,
        as well as Buffer Pools - that contain Data Dictionary info.
      Dedicated Memory Pools -

Data - Buffers contains data from tables ( indexed structure, work areas
Spliting locks
blocks - storage unit
when you modify a query you are writing on a block in memory
(Database Buffers)

Log
(Redo Buffer)
Finally data is writen to Log area for transaction constitency.

What one need to understand for understanding Oracle is

How Shared Pool works?
How Data Buffer works?
How Log  Buffer works?

8.Let see how things happen.

Say a client issues a query.
select empno, ename, job from emp where ename like 'S%'

i.query arrives as a text to the server process, then a Hash value is computated.

Hash value = 3046656596

Server will check if this Hash value already executed and chk Shared Pool.
If its found the analysis stops here and its called "Soft Passing".

If  the query not found its Hard Passing then it locks some shared resources.
sql syntax must be analyzed. The object need to be used must be identified.
Then the best execution plan must be determined.

Think queries as functions and pass parameters so the recursive calling to data dicitonary
is avoided.

Its loaded to the shared pool and is executed.

9.updating data
Before modifying data the data are copied to workarea,
when commit is issued the LGWR write to

----------------------------------------------------------------------------------------------------------
summary:2

The Control file as its name says , knows about database constitency. If  the database did shutdown properly or any transaction need to be rolledback.

The way oracle works that in the "Shared Pool"  the compiled queries are their  if any query is modified
its written to the "Database Buffer"  the memory block then for constitency to "Log Buffer" when commit
is issued written to the database. This how oracle works. Also when client issue a query it runs  as a
"Server process" in its PGA ( Private area) as a proxy to client.

SGA area is shared between processes.
Fixed Memory Area.
Variable Memory Area.
   Shared Pool which have compiled queries. Then the Buffer Pool where Data Dictionary is their , this one
if parameters are passed better than accessing recursively this area a optimization tip to send as parameter
and think it as a function.

I do not know on Dedicate Memory Pool.

Then the Data Buffer. Then Log Buffer.

When a query issued by client runs a Hash value is assigned.

1) All about Client :

Now the Client need to able access the application , is not it what in day today life things required.
Then obvious is  Client need to give info. about the where abouts  the Host it want to access and then
which ever one listens to it the Port and  then the Service which connects to the Database.

To listen to a client a Listner to be their.  The tnslsnr.ora file has all the parameter of  the listner named
tnslsnr.


command.
SQL > lsnrctl start

starting /bin/lsnrctl

2) The client connect through JDBC connection or if  using oracle library see the parameters in tnslsnr.ora file.

It can use LDAP for authentication , also Network Information Services (NIS , instead of tnslsnr.ora) file.








----------------------------------------------------------------------------------------------------------

PL/SQL - Oracle programming language page

Ma Gurudeva

PL/SQL Tutorial