sql interview questions nd answers

Question :
What are the advantages and disadvantages of primary key and foreign key in SQL?

Answers: Primary key

Advantages

1) It is a unique key on which all the other candidate keys are functionally dependent

Disadvantage

1) There can be more than one keys on which all the other attributes are dependent on.

Foreign Key

Advantage

1)It allows refrencing another table using the primary key for the other table

First do remember that a table can have only 1 primary key, though we can have many columns with a single primary key, called composite primary key.
Advantages
1. It prevents to enter null data.
2. It prevents to enter duplicate data.
3. It helps to force integrety constraints.

Disadvantage :
On primary key index will be created so during updation of table index need to be adjusted accordingly. this process makes the updation slower

Question :
Difference between decode and case.
in which case we are using case and in which case we are using decode?
with an example.

Answers:First I will give one example using 'decode'
sql>SELECT ENAME,SAL,DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','OTHERS') "DEPARTMENTS" FROM EMP;
I have used the decode function on 'deptno' column. It will give the user-friendly output. instead of using 'accounting', 'research'.. We can use anything we want to get the friendly outputs.
I have to check-out the 'case' function after that I will give an example

Using CASE expression we can use all comparative operators (<,>,==, etc), where as using DECODE we should always use = condition.

Question :
What is the difference between TRUNCATE and DELETE commands?

Answer:TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.<br>WHERE clause can be used with DELETE and not with TRUNCATE.

Truncate resets the identity and delete does not

Question :
What is the use of CASCADE CONSTRAINTS?

Answers:When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

For identifying the continuous constraints

Question :
How can we delete records in a table using update table?

Answers:update table employee
set emp_name = null
where emp_id=10

Question :
What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );

Answers: NO.
Explanation :
The query checks whether a given string is a numerical digit.

I have query regarding this. I guess the Translate part will give the result as 1111111111, and its a numerical digit.So answer should be Yes.
plz correct me , if I am wrong.

Question :
How to programmatically find out when the SQL Server service started?

Answer:-There is one simple but awkward method is there to find out when the SQL server service is started.
The following query will give you the time when the SQL server service is started;

select crdate from [master].dbo.sysdatabases
where name=?tempdb?
This is because, the tempdb will be created whenever SQL server service is started or restarted.

Question :
How can i hide a particular table name of our schema.

 Answers: you can hide the table name by creating synonyms.

e.g) you can create a synonym y for table x

create synonym y for x;

you can hide the table name by creating synonyms.
e.g) you can create a synonym y for table x
create synonym y for x;Question :
How we get second highest salary in
database. Please show me this coding.Answers:

select top 1 from (select top 2 from (select salary from employee order by salary) order by salary dsc)

Question :
What is the difference between cross product & Cartesian product?

Answers: There is no difference between Cartesian product and cross join.
although there syntax are different but they work as a same.

Cartesian or cross product selects all rows from both the table.
example
tableA 6 rows
tableB 6 rows
Cartesian or cross product
36 rows.Question :
What is the use of DESC in SQL?

Answers: DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order. Question :
given a table of student(rollno,name,dno,dateofadmission,city) and department(dno,dname)...How u will found the rollno,class,deptname of students in ascending order of their departments? Answers:

Select s.rollno,d.dname from student s,department d where s.dno=d.dno order by s.name ASC, d.name ASC;

Question :
What is a default TCP/IP socket assigned for SQL Server?

Answers:

1433 is default tcp/ip socket sql server.

Question :
What is the difference between varchar& varchar2?

Answers: varchar2 and varchar both are support strings,but varchar2 support alphanumeric string.and another one differece the total byte of the datatype are differ.

Question :
Explain UNION, MINUS, UNION ALL and INTERSECT?

Answers:INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates.

INTERSECT - return all distinct rows but comman records exist in both queries.

Union- Retrun all distinct rows from both queries.

Union All - Returns all rows of both queries.

MINUS - In sql server 2005 -both queries work sepratelly

Question :
What is the difference between "select * from table name" and "select column_name from table name" The above quiries are simple select quiries without having any "where" clause. Secondly which query will be performed faster and why??

Answers: column name will perform quickly.

Question :
what is the difference between replace and translate functions?

Answers: I Think here we r discussing abtSQL.Replace function is used to replace a string as well as character to another string or character,Translate function exists in ORACLE for translating single character to some other character........

Question :
What is SQLPlus?

Answers: SQLPlus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.

Question :
Explain about SQL related to RDBMS?

Answers: SQL is known as structured query language. It is especially designed to retrieve and store information of data in relational database management systems. It creates, modifies and makes the data base object user access the control system. It is primarily specialized software for RDBMS.

Question :
How to find out the database name from SQL*PLUS command prompt?

Answers:Select * from global_name;
This will give the datbase name which u r currently connected to.....

select name from v$database

will give u the database name from which u r currently Connected

Question :
Difference between Stored Procedure and Trigger?

Answers:Information related to Stored procedure you can see in USER_SOURCE,USER_OBJECTS(current user) tables.

Information related to triggers stored in USER_SOURCE,USER_TRIGGERS (current user) Tables.

Stored procedure can't be inactive but trigger can be Inactive.

we can call stored procedure explicitly.

but trigger is automatically invoked when the action defined in trigger is done.
ex:
create trigger <trig_Name> after Insert on <table>


this trigger invoked after we insert something on that table.

Question :
What is the default format of date in Oracle? H ow can I change my default date format?

Answers: Oracle's default format for DATE is "DD-MON-YY"

Question :
You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?

Answers: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.

Question :
Which of the following is not a feature of a cursor FOR loop?

Answers: 1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.

Question :
Which system table contains information on constraints on all the tables created?

Answers: yes,

USER_CONSTRAINTS,

system table contains information on constraints on all the tables created

user_cons_columns table consists the information of the table name,column name ,constraint name
user_constraints contains information about table name, constraint type,constraint name e.t.c

Question :
What is reference cursor ?

Answers: Refereence cursor is dynamic cursor used with SQL statement like For select* from emp;

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time.
declare
type r_cursor is REF CURSOR;
c_empr_cursor;
en emp.ename%type;
begin
open c_emp for select ename from emp;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end;

Question :
Which system tables contain information onprivileges granted and privileges obtained?

Answers: USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD.

user_tab_privs_made,user_tab_privs_recd.

Question :
Can we connect two different relational tables from different data sources and get the output as one flat file? Answers: Flat files are data files that contain records with no Structured Relationship

Question :
Explain about XML related specifications?

Answers: XML is a very powerful DOM language. Often this language is used with many database applications as a front end, SQL acts as a backend to support the Database queries. This specification has several extensions which defines routines, functions, data type mappings, storage, etc.

Question :
What is a join? Explain the different types of joins?

Answers:Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

Question :
When we give SELECT * FROM EMP; How does oracle respond:

Answers: When u give SELECT * FROM EMP;

the server check all the data in the EMP file and it displays the data of the EMP file

when we give select * from emp it will shows the records of table emp if the table contain records. If it hasn't any records then it will shows no rows selected.

Question :
How to generate OIDS ?

Answers: In MS SQL Server, we can use function NEWID() to generate OIDs(Object Identifiers) or GUIDsQuestion :
Cursor Syntax brief history?

Answers: To retrieve data with SQL one row at a time you need to use cursor processing.Not all relational databases support this, but many do.Here I show this in Oracle with PL/SQL, which is Procedural Language SQL.Cursor processing is done in several steps:1. Define the rows you want to retrieve. This is called declaring the cursor.2. Open the cursor. This activates the cursor and loads the data. Note that declaring the cursor doesn't load data, opening the cursor does.3. Fetch the data into variables.4. Close the cursor.

Question :
What are the wildcards used for patternmatching.

Answers: _ for single character substitution and % for multi-character substitution.

% for multi character and ?for single character .

Question :
What is output of following query
Select 2 from employee;

Answers: It depends upon number of rows in table. This query will print 2 as many times as rows in table.

Question :
What is outer join?Explain with examples.

Answers:The Left Outer Join returns all the rows from the Left Table with the values that match with the Key from the second table.

The Right Outer Join returns all the rows from the Right Table with the values that match with the key from the first table.

Question :
What are the two components of ODBC ?

Answers: 1. An ODBC manager/administrator and
2. ODBC driver.

Question :
Write a SQL statement to draw the second highest salary in Employee Table (Using Sub query)
Table structure is:
Employe ID
Name
Salary

Answers:For MS SQL database following query should work..

select min(salary) from emp_salary where salary IN(select top 2 salary from emp_salary order by salary desc)

Question :
Why is it better to use an integrity constraint to validate data in a table than to use a stored procedure?

Answers: Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.

Question :
Write a Query to pull out the maximum unit price for each in the database. (Using Northwind database)

Answers: select empname,max(salary) from employee group by empname

Question :
What the difference between UNION and UNIONALL?

Answers: Union will remove the duplicate rows from the result set while Union all does'nt.

unionwont allow the duplicates,where asunionall allows the duplicates.
while we use union the result will be sorted in the asc order of the first col of the select statement by default.where as no sorting in unionall by default.

Question :
What is deadlock?

Answers: Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.

Question :
What are the advantages of client/server model ?

Answers: Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.

Question :
I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated.

Answers: SELECT COL1 FROM TAB1
WHERE COL1 IN
(SELECT MAX(COL1) FROM TAB1
GROUP BY COL1
HAVING COUNT(COL1) > 1 )

Question :
Identify error in following SQL:
SELECT EMPNO, SUM(SAL)
FROM EMP
GROUP BY EMPNO
HAVING ENAME= ?KENT?
a) Syntax Error
b) SUM() is not an aggregate function
c) HAVING can not be used with GROUP BY
d) The Having condition has to be based on some column that appears in the select list.

Answers: d)

We can use this condition: "ENAME= ?KENT?" in the where clause. Then it will run fine.

Question :
What command is used to create a ta ble by copying the structure of another table?

Answers: CREATE TABLE .. AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

create table <table> as
Select * from <existing Table>
where 1=2;

Question :
What is join in sql?

Answers: JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.
Self Join joins a table to itself as though it were two separate tables.

Question :
Explain the concept of joins and list out the different type of joins?

Answers: Joins are used to combine tables. There are 3 type of joins. selfjoines, outer joines and inner joins

Question :
Why do I get "Invalid Cursor State" errors when I insert/update/delete data with executeQuery()?

Answers: IF u get this error, then there is a problem with the Java program, U may open the cursor with the Readonlymode.Change the Resultset type then you will get it.This is not problem with the Oracle

Question :
i have a table

empidempnamedept
1 abc d1
2 bcd d2
3 adf d3
4 gtf d2
5 tgh d1

i need the output d1=2,d2=2,d3=1 .
please write the sql query

Answers: CREATE TABLE emp_detail (emp_idNUMBER,emp_name VARCHAR(10),dept_name VARCHAR2(10))

insert into emp_detail values(1,'abc','d1')
insert into emp_detail values(2,'bcd','d2')
insert into emp_detail values(3,'adf','d3')
insert into emp_detail values(4,'gtf','d2')
insert into emp_detail values(5,'tgh','d1')

select *
from emp_detail

SELECT dept_name,
(CASE
WHEN dept_name = 'd1' THEN 2
WHEN dept_name = 'd2' THEN 2
WHEN dept_name = 'd3' THEN 1
ELSE 0
END) dept_no
FROM emp_detail

Question :
Explain normalization with examples.

Answers: Normalization is a process of eleminating the redundancy and increasing the integrity.

Normalisation means refining the redundancy and maintainstablisation.
there are four types of normalisation are
first normal forms, second normal forms, third normal forms and fourth Normal forms.

Question :
What is normalization and different kinds of normalization?

Answers: It is set of rules that have been established to aid in the design of tables that are meant to
be connected through relationships. This set of rules is known as Normalization.
Benefits of normalizing your database will include:
√ Avoiding repetitive entries
√ Reducing required storage space
√ Preventing the need to restructure existing tables to accommodate new data.
√ Increased speed and flexibility of queries, sorts, and summaries.

Question :
Explain the ORDER BY clause?

Answers: Orderby clause identifies the columns which are used to sort the data and the order in which they should be sorted out. SQL query needs to specify orderby clause of they want the data to be returned in a defined manner of rows and columns.

Question :
How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?

Answers: Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

Question : What description of a data source is required for ODBC ?

Answers: The name of the DBMS, the location of the source and the database dependent information.

Question :
What is difference between DBMS and RDBMS?

Answers: 1.RDBMS=DBMS+Refrential Integrity

2. An RDBMS ia one that follows 12 rules of CODD.

RDBMS we can make it as a server, it connect more systems but DBMS only woke in host system,we can't make dbms as global..

Question :
How do I list all of my tables?
How do I list all the indexed created for a particulartable(say EMP)?
How do I SELECT FROM DIFFERENT USERS TABLE?

Answers: select table_name from information_schema.tables

sp_helpindex '<tablename>'

Question :
How to display duplicate rows in a table?

Answers: select * from emp

group by (empid)

having count(empid)>1

select * from emp where EmpID in(select EmpID
from emp
group by EmpID having count(EmpID)>1)

Question :
How to testdata loading in database testing?

 Answers: No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.

Question :
What is diffrence between Co-related sub query and nested sub query??

Answers: Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

Question :
What is denormalization and difference between normalization and denormalization?

Answers: Denormalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data.
Normalization is the process of de-composing a relation with anomalies into a well structured relation.

Question :
what is the query for to select multiple columns to generate single row in the sql and how come we can bring grid views in sql for getting those datas are demolished

Answers: i need sql& asp db2 asp.net all other interview questuions

Question :
What is the parameter substitution symbol used with INSERT INTO command?

Answers: &

& is substitution variable.
eg. insert into table_name values (&column1, &column2);
The statement prompts for values of column1 and column2.

Question :
on index
why u need indexing ? where that is stroed
and what u mean by schema object?
for what purpose we are using view

Answers: We cant create an Index on Index.. Index is stoed in user_indextable.Every object that has been created on Schema is Schema Object like Table,Viewetc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view.

Indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

Question :
How can variables be passed to a SQL routine?

Answers:By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.

Question :
What is the use of the DROP option in the ALTER TABLE command?

Answers: It is used to drop constraints specified on the table.

Question :
Which command executes the contents of a specified file?

Answers: START <filename> or @<filename>.

Question :
What is the main difference between the IN and EXISTS clause in subqueries??

Answers: The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.

IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.

EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times.

Question :
What is the main difference between the IN and EXISTS clause in subqueries??

Answers: The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.

IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.

EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times.

Question :
What is DML,DDL?

Answers: Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

Question :
what is the difference between oracle,sql and sql server?

Answers: Oracle is based on RDBMS.

SQL is Structured Query Language.

SQL Server is another tool for RDBMS provided by MicroSoft.

SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structured English query language) was designed by an IBM research center.

Oracle is Relational Database product.

SQL server is a Relational Data Base Management System (RDBMS) from Sybase Corporationfor client/server use and is accessed by applications using SQL.

Question :
What is the fastest way of accessing a row in a table?

Answers:The fastest way of accessing a row in a table using ROWID CONSTRAINTS.

Question :
What is the difference between Single row sub-Query and Scalar sub-Query?

Answers: SINGLE ROW SUBQUERY RETURNS A VALUE WHICH IS USED BY WHERE CLAUSE , WHEREAS SCALAR SUBQUERY IS A SELECT STATEMENT USED IN COLUMN LIST CAN BE THOUGHT OF AS AN INLINE FUNCTION IN SELECT COLUMN LIST

A single row sub-query can retrive more than 1 column, but 1 row. Where-as a Scaler sub-qry retrieves only 1 row 1 colums.

Question :                
Why are the integrity constraints preferred to database triggers ?

Answers: Because it is easier to define an integrity constraint than a database trigger.

because integrity constraint checks the data inserted before , and the new inserted data and it doesn't created unless we correct the whole table..
but when using database trigger , it checks only the new inserted data and doesn't interfere with the old data ...Question :
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?

Answers:The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

Question :
What is spool command?

Answers:Spool command creates a print file of the report.

Question :
State true or false. EXISTS, SOME, ANY are operators in SQL.

Answers:True.

Question :
What is a event handler ?

Answers:An event handler is a routine that is written to respond to a particular event.

Question :
1. How to create a new table?
2. How to insert another column into the table?

Answers:create table employee(name varchar2(10),id number(10),mobileno number(10));

Question :
There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all themployees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?

Answers: What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected coloumns of the right table. eg in this query which uses the key-word LEFT OUTER JOIN. syntax though varies across databases. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id

SQL Server/Sybase :
Employee_table.Dept_id(+) = Dept_table.Dept_id

Select e.fname,e.....,d.name,d....,from employee e LEFT JOIN department d
where e.deptid=d.id