Question { ABC, 49219 }

Can we create clustered index on non primary key column

Answer

No, Because Constraints must have integrity. These are,
Clustered and Non-Clustered. So, to use Primary key with
any one Clustered or Non-Clustered index.

Example in SQL Server 2000:
Query :
-------
create table BookDetails
(
Book_ID int identity(1,1) not null clustered,
Book_Title varchar(20),
Book_Type varchar(15),
Book_Author varchar(25),
Book_Edition float,
Book_Publisher varchar(20),
Book_Pub_Year datetime,
Book_Price int
)

Answer:
-------
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'clustered'.

Prevent this Error To use Clustered Primary Key:
------------------------------------------------
Query In SQL Server 2000:
-------------------------

create table BookDetails
(
Book_ID int identity(1,1) not null primary key clustered,
Book_Title varchar(20),
Book_Type varchar(15),
Book_Author varchar(25),
Book_Edition float,
Book_Publisher varchar(20),
Book_Pub_Year datetime,
Book_Price int
)

Answer:
-------
The command(s) completed successfully.

Question { 9335 }

What are Sql Reporting Services and analysis services? Can
u Explain

Answer

Sql Reporting Services :
-----------------------

Full Reporting Life Cycle Support
SQL Server Reporting Services supports the full reporting
life cycle, including:

• Report authoring. Report developers can create reports to
be published to a report server by using Microsoft or other
design tools that use Report Definition Language (RDL), an
XML-based industry standard used to define reports.

• Report management. Report definitions, folders, and
resources are published and managed as a Web service.
Managed reports can be executed either on demand or on a
specified schedule, and are cached for consistency and
performance. New in SQL Server 2005 Reporting Services,
administrators can use SQL Server Management Studio to
organize reports and data sources, schedule report
execution and delivery, and track reporting history.

• Report delivery. SQL Server Reporting Services supports
both on-demand (pull) and event-based (push) delivery of
reports. Users can view reports in a Web-based format or in
e-mail.

• Report security. SQL Server Reporting Services implements
a flexible, role-based security model to protect reports
and reporting resources. The product includes extensible
interfaces for integrating other security models as well.

Analysis Services :
------------------

SQL Server 2005 Analysis Services (SSAS) provides, for the
first time, a unified and integrated view of all your
business data as the foundation for all of your traditional
reporting, online analytical processing (OLAP) analysis,
Key Performance Indicator (KPI) scorecards, and data mining.

Question { Microsoft, 11677 }

What r sql reporting services and analysis services? how
can we use it.

Answer

Sql Server Reporting Services:
------------------------------
SQL Server 2000 Reporting Services relies on several major
components to function correctly.
Report Manager (RM) is used to administer the RS web
service and can be used to serve up reports from a folder /
list interface. RM is actually a web application that calls
the RS web service.

Database Server: This machine hosts the SQL Server 2000
databases required to persist application information.
Reports can be drawn from SQL Server, ODBC or Oracle data
sources.

SQL Server Reporting Services (SSRS) is a server based
report generation environment developed by Microsoft. It
can be used to deliver a variety of interactive and printed
reports. It is administered via a web interface. Reporting
services features a web services interface to support the
development of custom reporting applications.

SSRS is a competitor of Crystal Reports and other Business
intelligence tools, and is included in Express, Workgroup,
Standard, and Enterprise editions of Microsoft SQL Server
as an install option. Reporting Services was first released
in 2004 as an add-on to SQL Server 2000. The current
version was released as a part of SQL Server 2005 in
November 2005.

Question { Systematix, 26062 }

is there more the two primary key in a single table?

Answer

In SQL Server 2000,

Query :
-------

create table BookDetails
(
Book_ID int identity(1,1) not null primary key,
Book_Title varchar(20),
Book_Type varchar(15),
Book_Author varchar(25),
Book_Edition float,
Book_Publisher varchar(20),
Book_Pub_Year datetime,
Book_Price int primary key
)

Answer :
--------

Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to
table 'BookDetails'.

Reply Answer:
-------------
So,Can't Create more Primary Keys in a Table.

Question { iGate, 25493 }

how to get second highest salary in SQL(as/4000

Answer

SELECT * FROM Employee e
WHERE 2=(SELECT COUNT(DISTINCT Salary) FROM Employee x
WHERE x.Salary >= e.Salary);

Question { Ramco, 8898 }

select 1,col1,col2 from table1. output?

Answer

SQL> SELECT 1,emp_no,emp_name FROM emp;

1 EMP_NO EMP_NAME
--------- --------- -------------------------
1 100 SELVA
1 101 RAJ
1 102 A S KALA
1 103 JESLIN FANTA MALAR
1 104 ANITA
1 105 MURUGU
1 106 SRIVATSAN
1 107 SARABOOT
1 108 KARTHI SIR
1 109 SUDHA
1 110 MERCHI
1 111 SAVI

12 rows selected.

Question { Sony, 12712 }

Anybody can tell me, how do we find second largest emp
salary from emp table.. Thanks in advance ...

Answer

Oracle 10g Using : Find 2nd Highest Salary?

SELECT * FROM emp e WHERE 2=(SELECT COUNT(DISTINCT salary)
FROM emp x
WHERE x.salary >= e.salary);

Answer :

EMP_NO EMP_NAME SALARY
--------- ------------------------- ---------
107 SARABOOT 550000

Question { Oracle, 10869 }

why use cursors?

Answer

Cursors allow row-by-row processing of the result sets.
Retrieve more than one rows from table or database to use
Curosrs.

Question { 19995 }

HOW CAN I FIND MAX SAL ,ENAME FROM EMP TABLE.

Answer

select ename, salary from emp where salary=(select max
(salary) from emp) order by emp_no;

Question { Polaris, 13819 }

One Table having two rows with one colomn having values
Like"Male" and "Female". how to upadte these values
Like "Female" and "Male" in single update statement.

Answer

UPDATE Eptbl SET Sex=DECODE
(Sex,'Male','Female','Female','Male');

Question { 11849 }

How to copy a table in another table with datas?

Answer

In Oracle 10g :
---------------

Create New Table in another table with same data's:
---------------------------------------------------

copy from coeot1a2/coeot1a2@coeau to scott/tiger@coeau
CREATE s11 using select * from major_list;

Insert data's into another table with same data's:
---------------------------------------------------

copy from coeot1a2/coeot1a2@coeau to scott/tiger@coeau
INSERT s11 using select * from major_list;

Append data's into another table with same data's:
---------------------------------------------------

copy from coeot1a2/coeot1a2@coeau to scott/tiger@coeau
APPEND s11 using select * from major_list;

Replace data's into another table with same data's:
---------------------------------------------------

copy from coeot1a2/coeot1a2@coeau to scott/tiger@coeau
REPLACE s11 using select * from major_list;

Question { IBM, 11606 }

suppose I have two table one Emp and other is dpt.
Emp table has a field ,dept id,name ,sal and dpt table has a
field dept id,dept name.
Now I want to find out the emplyee list whose sal is between
2000-3000 from dept x.

Answer

SELECT * FROM EMP E,DEPTS D WHERE E.DEPT_ID=D.DEPT_ID AND
E.SALARY BETWEEN 2000 AND 3000 ORDER BY EMP_NO;

Question { 10576 }

What are the different types of joins and explain them
briefly.

Answer

Joins in Oracle 10g :
---------------------

A join is a query that combines rows from two or more
tables, views, or materialized views ("snapshots"). Oracle
performs a join whenever multiple tables appear in the
query's FROM clause. The query's select list can select any
columns from any of these tables. If any two of these
tables have a column name in common, you must qualify all
references to these columns throughout the query with table
names to avoid ambiguity.

Join Conditions
---------------
Most join queries contain WHERE clause conditions that
compare two columns, each from a different table. Such a
condition is called a join condition. To execute a join,
Oracle combines pairs of rows, each containing one row from
each table, for which the join condition evaluates to TRUE.
The columns in the join conditions need not also appear in
the select list.

To execute a join of three or more tables, Oracle first
joins two of the tables based on the join conditions
comparing their columns and then joins the result to
another table based on join conditions containing columns
of the joined tables and the new table. Oracle continues
this process until all tables are joined into the result.
The optimizer determines the order in which Oracle joins
tables based on the join conditions, indexes on the tables,
and, in the case of the cost-based optimization approach,
statistics for the tables.

In addition to join conditions, the WHERE clause of a join
query can also contain other conditions that refer to
columns of only one table. These conditions can further
restrict the rows returned by the join query.

Equijoin / Inner Join
---------------------

An equijoin is a join with a join condition containing an
equality operator. An equijoin combines rows that have
equivalent values for the specified columns.

Cartesian Product
-----------------
If two tables in a join query have no join condition,
Oracle returns their Cartesian product. Oracle combines
each row of one table with each row of the other. A
Cartesian product always generates many rows and is rarely
useful. For example, the Cartesian product of two tables,
each with 100 rows, has 10,000 rows. Always include a join
condition unless you specifically need a Cartesian product.
If a query joins three or more tables and you do not
specify a join condition for a specific pair, the optimizer
may choose a join order that avoids producing an
intermediate Cartesian product.

Outer Join
----------

An outer join extends the result of a simple join. An outer
join returns all rows that satisfy the join condition and
those rows from one table for which no rows from the other
satisfy the join condition. Such rows are not returned by a
simple join. To write a query that performs an outer join
of tables A and B and returns all rows from A, apply the
outer join operator (+) to all columns of B in the join
condition. For all rows in A that have no matching rows in
B, Oracle returns NULL for any select list expressions
containing columns of B.

Outet join has Two types :
--------------------------
1. Left Outer Join
2. Right Outer Join

Self Join
---------

A self join is a join of a table to itself. This table
appears twice in the FROM clause and is followed by table
aliases that qualify column names in the join condition. To
perform a self join, Oracle combines and returns rows of
the table that satisfy the join condition.

Question { 15300 }

what is the output of this query selet * from employee
where 1=2 ??

Answer

In Oracle 10g :
---------------

no rows selected.


E.g:
----

SQL> SELECT * FROM job;

EMPNO JOBTITLE
--------- --------------------
1 Tester
2 Accountant
3 Developer
4 COder
5 Director
6 Mediator
7 Proffessor
8 Programmer
9 Developer

9 rows selected.

SQL> SELECT * FROM job WHERE 1=2;

no rows selected.

Question { DELL, 8658 }

HOW TO DISPLAY MAXIMUM SALARIES FROM EMP DEPARTMENT VISE ALONG
WITH DEPARTMENT NAMES? E.g EMP,DEPT

Answer

In oracle 10g :

SELECT * FROM
(SELECT DEPTNO,
EMPNO,
ENAME,
SAL,
DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL
DESC) RANK
FROM EMP);

OUTPUT :
------------>

DEPTNO EMPNO ENAME SAL RANK
---------- ---------- ---------- ---------- ----------
10 7839 KING 5000 1
10 7782 CLARK 2450 2
10 7934 MILLER 1300 3
20 7788 SCOTT 3000 1
20 7902 FORD 3000 1
20 7566 JONES 2975 2
20 7876 ADAMS 1100 3
20 7369 SMITH 800 4
30 7698 BLAKE 2850 1
30 7499 ALLEN 1600 2
30 7844 TURNER 1500 3

DEPTNO EMPNO ENAME SAL RANK
---------- ---------- ---------- ---------- ----------
30 7521 WARD 1250 4
30 7521 WARD 1250 4
30 7654 MARTIN 1250 4
30 7900 JAMES 950 5

15 rows selected.