Delete duplicate rows in SQL Server and Oracle

 SQL SERVER

DELETE FROM Table1 WHERE ID NOT IN
(  SELECT MAX(ID) FROM Table1
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3 )






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



AND In ORACLE 
 



DELETE FROM TABLE1
WHERE ROWID NOT IN (SELECT   MIN (ROWID)
FROM TABLE1
GROUP BY COLUMN1, COLUMN2, COLUMN3, COLUMN4);

Managing Backup & Recovery

What are the different methods of backing up oracle database ?
- Logical Backups
- Cold Backups
- Hot Backups (Archive log)

What is a logical backup ?
Logical backup involves reading a set of databse records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.

What is cold backup ? What are the elements of it ?
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
- All Data files.
- All Control files.
- All on-line redo log files.
- The init.ora file (Optional)

What are the different kind of export backups ?
Full back - Complete database
Incremental - Only affected tables from last incremental date/full backup date.
Cumulative backup - Only affected table from the last cumulative date/full backup date.

What is hot backup and how it can be taken ?
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up.
All data files. All Archive log, redo log files. All control files.

What is the use of FILE option in EXP command ?
To give the export file name.

What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress fragmented segments into single extents.

What is the use of GRANT option in EXP command ?
A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.

What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be exported.

What is the use of ROWS option in EXP command ?
Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the databse objects will be created.

What is the use of CONSTRAINTS option in EXP command ?
A flag to indicate whether constraints on table need to be exported.

What is the use of FULL option in EXP command ?
A flag to indicate whether full databse export should be performed.

What is the use of OWNER option in EXP command ?
List of table accounts should be exported.

What is the use of TABLES option in EXP command ?
List of tables should be exported.

What is the use of RECORD LENGTH option in EXP command ?
Record length in bytes.

What is the use of INCTYPE option in EXP command ?
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL.

What is the use of RECORD option in EXP command ?
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.

What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.

What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.

What is the use of ANALYSE ( Ver 7) option in EXP command ?
A flag to indicate whether statistical information about the exported objects should be written to export dump file.

Logical & Physical Architecture of Database

What is Database Buffers ?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters. DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.

 What is dictionary cache ?
Dictionary cache is information about the database objects stored in a data dictionary table.

What is meant by recursive hits ?
Number of times processes repeatedly query the dictionary table is called recursive hits. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.

What is meant by redo log buffer ?
Changes made to entries are written to the on-line redo log files so that they can be used in roll forward operation during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

 How will you swap objects into a different table space for an existing database ?
Export the user
Perform import using the command imp system/manager file=export.dp indexfile=newfile.sql. This will create all definitions into newfile.sql.
Drop necessary objects.

Run the script newfile.sql after altering the tablespaces.
Import from the backup for the necessary objects.

List the Optimal Flexible Architecture (OFA) of Oracle database ? or
How can we organise the tablespaces in Oracle database to have maximum performance ?
SYSTEM - Data dictionary tables
DATA - Standard operational tables
DATA2 - Static tables used for standard operations
INDEXES - Indexes for Standard operational tables
INDEXES1 - Indexes of static tables used for standard operations
TOOLS - Tool table
TOOLS1 - Indexes for tools table
RBS - Standard Operations Rollback Segments
RBS1,RBS2 - Additional/Special rollback segments
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users
USERS - User tablespaces.

How will you force database to use particular rollback segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name

What is meant by free extent ?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.

How free extents are managed in Ver 6 and Ver 7. ?
Free extents cannot be merged together in Ver 6.0
Free extents are periodically coalesces with the neighboring free extent Ver 7.0.

Which parameter in Storage clause will reduce no of rows per block ?
PCTFREE parameter
Row size also reduces no of rows per block.

What is significance of having storage clause ?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.

How does space allocation take place within a block ?
Each block contains entries as follows :
Fixed block header
Variable block header

Row header, row date (Multiple rows may exists)
PCTFREE (% of free space for row updation in future)

What is the role of PCTFREE parameter is Storage clause ?
This is used to reserve certain amount of space in a block for expansion of rows.

What is the OPTIMAL parameter ?
It is used to set the optimal length of rollback segment.

What is the functionality of SYSTEM tablespace ?
To manage the database level of transactions such as modifications of the data dictionary table that record information about the free space usage.

How will you create multiple rollback segments in a database ?
Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace.
Create a Second Rollback Segment name R0 in the SYSTEM tablespace.
Make new rollback segment available (After shutdown, modify init.ora file and Start database)
Create other tablespace (RBS) for rollback segments.
Create additional Rollback segment in tablespace (RBS)
Deactivate Rollback Segment R0 and activate the newly created rollback segments.

How the space utilisation takes place within rollback segments ?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent. (No of extents is based on the OPTIMAL size).

Importent Oracle & Sql Query

1) How delete duplicate records in a table
delete from emp where rowid not in (select max(rowid) from emp group by empno);

2) How to make a column into not null column.
Ans: alter table emp
modify (empno not null); ->this can be done only when all the values in empno are non null (i.e not empty)

?h To make a not null column into null column
alter table emp
modify (empno null);
The fallowing are the rules for

i. adding null or not null property
>You may change a column??s null property to not null only when that field does contain null values(i. It can??t be empty)
>At any time you may change a column??s not null property to null

ii. adding a colum to a table
>You may add a column at any time if NOT NULL isn??t specified (i.e when new column can accept null values )
>You may add a NOT NULL column in three steps.
a. Add the column without NOT NULL specified.
b. Fill every row in that column with data.
c. Modify the column to be NOT NULL.

iii. modifying a column
>You can increase a CHAR column??s width at any time.
>You can increase the number of digits and the number of decimal places in a NUMBER column at any time
>To CHANGE data types or to DECREASE column??s width the column should be null for every row

3)Write queries for the fallowing
ex. select * from emp_self;

EMP_NO EMPN_NAME SAL MGR DEPTID
--------- -------------------- ---------- ---------- ----------
400 jane 20000 110 20
102 Mary 19000 110 20
101 charles 8000 105 50
104 Linda 9000 100 10
110 john 25000 105 20
105 newton 2000 50
100 ALEN 15000 105 50
200 BORIS 3000 110 20
103 DAVID 10000 100 10
300 monica 7000 105 50
i) Query to get the employees who are working under mgr with salary > 10000
select emp_no,mgr from emp_self where mgr in (select emp_no from emp_self where sal > 10000);

ii) Query to get the employees who are getting salaries more than their managers
select a.emp_no from emp_self a,emp_self b where a.mgr =b.emp_no and a.sal > b.sal;
EMP_NO
-------
101
100
110
300
iii) Query to find the nth highest salary
select a.empn_name,a.sal from emp_self a where &n = (select count(*) from emp_self b where a.sal < b.sal);
When n=1 , sal =20000 ->second highest salary ; n=4 , sal =10000 ->fifth highest salary ;
iv) Query to find the second highest salary in different departments.
select a.deptid,min(a. sal) from emp_self a where 1 in (select count(*) from emp_self b
where a.sal < b.sal group by b.deptid) group by a.deptid;

DEPTID MIN(A.SAL)
------- ----------
10 9000
20 20000
50 8000
v) Query to find departments with total salary >25000

select deptid from emp_self having sum(sal) >25000 group by deptid;

DEPTID
------
20
50

4) study the fallowing pl/sql block and find the type of error ->syntax,semantic(logical) or precedence
begin
for i in 1..5 loop
update emp
set sal = 1000 where empno =100 ;
end loop;
end;


5) Difference between (MAX,MIN) and (GREATEST ,LEAST)
The functions max and min compares different rows . Whereas greatest and least work on a group of columns ,either actual or calculated values within a single row.

Ex. select * from emp_self;

EMP_NO EMPN_NAME SAL MGR
------ -------------------- ------- ----------
100 alen 9,000 105
200 boris 10,000 110
101 charles 8,000 105

SQL> select max(sal),min(sal) from emp_self;

MAX(SAL) MIN(SAL)
---------- ----------
10000 8000

SQL> select greatest(sal),least(sal) from emp_self;

GREATEST(SAL) LEAST(SAL)
------------- ----------
9000 9000
10000 10000
8000 8000

Introduction to DBA

1. What is a database instance ? Explain
A database instance (server) is a set of memory structures and background processes that access a set of database files.

The process can be shared by all users.

The memory structures that are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file.

2. What is parallel server?
Multiple instances accessing the same database (Only in Multi-CPU environments).

3. What is Schema ?
The set of objects owned by user account is called the schema

4. What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table.
An index is automatically created when a unique or primary key constraint clause is specified in create table command (Ver 7.0)

5. What is clustres ?
Group of tables physically stored together because they share common columns and are often used together is called Clusters.

6. What is a cluster key ?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.

7. What are the basic element of Base configuration of an oracle Database ?
It consists of
one or more data files
one or more control files
two or more redo log files

The database contains
Multiple users/schemas
one or more rollback segments
one or more tablespaces
Data dictionary tables

User objects (tables,indexes,views etc)

The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)
SMON
PMON
LGWR
DBWR
ARCH
CKPT
RECO
Dispatcher
User process with associated PGA

8. What is deadlock ? Explain.
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.

In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.

These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

Oracle Interview Question Part 1

What is the difference between file server and a database server ?
A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output.

What is inheritance ?
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.

What are the two components of ODBC ?
1. An ODBC manager/administrator and
2. ODBC driver.

What is the function of a ODBC manager ?
The ODBC Manager manages all the data sources that exists in the system.

What is the function of a ODBC Driver ?
The ODBC Driver allows the developer to talk to the back end database.

What description of a data source is required for ODBC ?
The name of the DBMS, the location of the source and the database dependent information.

How is a connection establised by ODBC ?
ODBC uses the description of the datasource available in the ODBC.INI file to load the required drivers to access that particular back end database.

Oracle Interview Question

How to implement ISNUMERIC function in SQL *Plus ?

Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;

Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)

Method 2:
select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;

It returns 0 if it is a number, 1 if it is not.

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

What is the difference between Truncate and Delete interms of Referential Integrity?

DELETE removes one or more records in a table, checking referential
Constraints (to see if there are dependent child records) and firing any
DELETE triggers. In the order you are deleting (child first then parent)
There will be no problems.

TRUNCATE removes ALL records in a table. It does not execute any triggers.
Also, it only checks for the existence (and status) of another foreign key
Pointing to the table. If one exists and is enabled, then you will get
The following error. This is true even if you do the child tables first.

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

You should disable the foreign key constraints in the child tables before
issuing the TRUNCATE command, then re-enable them afterwards.