How would you find out the total number of rows in a table?
Use SELECT COUNT(*) ... in query
How do you eliminate duplicate values in SELECT ?
Use SELECT DISTINCT ... in SQL query
How you insert records into a table
Using SQL INSERT statement
How do you delete record from a table ?
Using DELETE statement
Example : DELETE FROM EMP
How do you select a row using indexes?
Specify the indexed columns in the WHERE clause of query.
How do you find the maximum value in a column?
Use SELECT MAX(...) .. in query
How do you retrieve the first 5 characters of FIRSTNAME column of table EMP ?
SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP
My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results. Why?
Because SALARY is not declared to have NULLs and the employees for whom the
salary is not known are also counted.
How do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a complete name?
SELECT FIRSTNAME || ‘ ‘ || LASTNAME FROM EMP
What is UNION,UNION ALL in SQL?
UNION : eliminates duplicates
UNION ALL: retains duplicates
Both these are used to combine the results of different SELECT statements.
Suppose I have five SQL SELECT statements connected by UNION/UNION ALL, how many times
should I specify UNION to eliminate the duplicate rows?
Once.
In the WHERE clause what is BETWEEN and IN?
BETWEEN supplies a range of values while IN supplies a list of values.
Is BETWEEN inclusive of the range values specified?
Yes.
What is 'LIKE' used for in WHERE clause? What are the wildcard characters?
LIKE is used for partial string matches. ‘%’ ( for a string of any character )
and ‘_’ (for any single character ) are the two wild card characters.
When do you use a LIKE statement?
To do partial search e.g. to search employee by name, you need not specify
the complete name; using LIKE, you can search for partial string matches.
Example SQL : SELECT EMPNO FROM EMP
WHERE EMPNAME LIKE 'RAMESH%'
% is used to represent remaining all characters in the name.
This query fetches all records contains RAMESH in six characters.
What do you accomplish by GROUP BY ... HAVING clause?
GROUP BY partitions the selected rows on the distinct values of the column on
which you group by. HAVING selects GROUPs which match the criteria specified
Consider the employee table with column PROJECT nullable. How can you get a list
of employees who are not assigned to any project?
SQL : SELECT EMPNO
FROM EMP
WHERE PROJECT IS null;
What are the large objects supported by oracle and db2?
Blob , Clob ( Binary Large Objects, Character Large Objects)
What's the difference between a primary key and a unique key?
Primary key wont allow nulls, unique key allow nulls.
Both Primary key and Unique key enforce the uniqueness of the column on which they are defined.
What is a join and explain different types of joins?
INNER JOIN
OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
INNER JOIN
What is a self join?
Joining two instances of a same table.
Sample SQL : SELECT A.EMPNAME , B.EMPNAME
FROM EMP A, EMP B
WHERE A.MGRID = B.EMPID
What is a transaction and ACID?
Transaction - A transaction is a logicl unint of work. All steps must be commited or rolled back.
ACID - Atomicity, Consistency, Isolation and Duralbility, these are properties of a transaction.
Materialized Query Tables in db2 ( This feature might not be available in oracle) ?
Materialized Query Tables or MQTs are also known as automatic summary
tables. A materialized query table (MQT) is a table whose definition is based upon the result of a
query. The data that is contained in an MQT is derived from one or more tables on which the materialized
query table definition is based. MQT improve the query performance.
Sample SQL to creat MQT.
CREATE TABLE CUSTOMER_ORDER AS
(SELECT SUM(AMOUNT) AS TOTAL_SUM,
TRANS_DT,
STATUS
FROM DB2INST2.CUSTOMER_ORDER
WHERE TRANS_DT BETWEEN '1/1/2001' AND '12/31/2001'
GROUP BY TRANS_DT,
STATUS)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
SQL interview questions
Thanks to Sachin Rastogi for posting these.
Which of the following statements contains an error?
SELECT * FROM emp WHERE empid = 493945;
SELECT empid FROM emp WHERE empid= 493945;
SELECT empid FROM emp;
SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
Which of the following correctly describes how to specify a column alias?
Place the alias at the beginning of the statement to describe the table.
Place the alias after each column, separated by white space, to describe the column.
Place the alias after each column, separated by a comma, to describe the column.
Place the alias at the end of the statement to describe the table.
The NVL function
Assists in the distribution of output across multiple columns.
Allows the user to specify alternate output for non-null column values.
Allows the user to specify alternate output for null column values.
Nullifies the value of the column output.
Output from a table called PLAYS with two columns, PLAY_NAME and AUTHOR, is shown below. Which of the following SQL statements produced it?
PLAY_TABLE
————————————-
“Midsummer Night’s Dream”, SHAKESPEARE
“Waiting For Godot”, BECKETT
“The Glass Menagerie”, WILLIAMS
SELECT play_name || author FROM plays;
SELECT play_name, author FROM plays;
SELECT play_name||’, ‘ || author FROM plays;
SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;
Issuing the DEFINE_EDITOR=”emacs” will produce which outcome?
The emacs editor will become the SQL*Plus default text editor.
The emacs editor will start running immediately.
The emacs editor will no longer be used by SQL*Plus as the default text editor.
The emacs editor will be deleted from the system.
The user issues the following statement. What will be displayed if the EMPID selected is 60494?
SELECT DECODE(empid,38475, “Terminated”,60494, “LOA”, “ACTIVE”)
FROM emp;
60494
LOA
Terminated
ACTIVE
SELECT (TO_CHAR(NVL(SQRT(59483), “INVALID”)) FROM DUAL is a valid SQL statement.
TRUE
FALSE
The appropriate table to use when performing arithmetic calculations on values defined within the SELECT statement (not pulled from a table column) is
EMP
The table containing the column values
DUAL
An Oracle-defined table
Which of the following is not a group function?
avg( )
sqrt( )
sum( )
max( )
Once defined, how long will a variable remain so in SQL*Plus?
Until the database is shut down
Until the instance is shut down
Until the statement completes
Until the session completes
The default character for specifying runtime variables in SELECT statements is
Ampersand
Ellipses
Quotation marks
Asterisk
A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?
select e.empid, d.head from emp e, dept d;
select e.empid, d.head from emp e, dept d where e.dept# = d.dept#;
select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+);
select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;
Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?
A table named ANIMALS will be created in the MASTER schema with the same data as the ANIMALS table owned by ANJU.
A table named ANJU will be created in the ANIMALS schema with the same data as the ANIMALS table owned by MASTER.
A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
A table named MASTER will be created in the ANIMALS schema with the same data as the ANJU table owned by ANIMALS.
User JANKO would like to insert a row into the EMPLOYEE table, which has three columns: EMPID, LASTNAME, and SALARY. The user would like to enter data for EMPID 59694, LASTNAME Harris, but no salary. Which statement would work best?
INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
INSERT INTO employee VALUES (59694,’HARRIS’);
INSERT INTO employee (EMPID, LASTNAME, SALARY) VALUES (59694,’HARRIS’);
INSERT INTO employee (SELECT 59694 FROM ‘HARRIS’);
Which three of the following are valid database datatypes in Oracle? (Choose three.)
CHAR
VARCHAR2
BOOLEAN
NUMBER
Omitting the WHERE clause from a DELETE statement has which of the following effects?
The delete statement will fail because there are no records to delete.
The delete statement will prompt the user to enter criteria for the deletion
The delete statement will fail because of syntax error.
The delete statement will remove all records from the table.
Creating a foreign-key constraint between columns of two tables defined with two different datatypes will produce an error.
TRUE
FALSE
Dropping a table has which of the following effects on a nonunique index created for the table?
No effect.
The index will be dropped.
The index will be rendered invalid.
The index will contain NULL values.
To increase the number of nullable columns for a table,
Use the alter table statement.
Ensure that all column values are NULL for all rows.
First increase the size of adjacent column datatypes, then add the column.
Add the column, populate the column, then add the NOT NULL constraint.
Which line of the following statement will produce an error?
CREATE TABLE goods
(good_no NUMBER,
good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)),
CONSTRAINT pk_goods_01
PRIMARY KEY (goodno));
There are no errors in this statement.
MAXVALUE is a valid parameter for sequence creation.
TRUE
FALSE
Which of the following lines in the SELECT statement below contain an error?
SELECT DECODE(empid, 58385, “INACTIVE”, “ACTIVE”) empid
FROM emp
WHERE SUBSTR(lastname,1,1) > TO_NUMBER(’S')
AND empid > 02000
ORDER BY empid DESC, lastname ASC;
There are no errors in this statement.
Which function below can best be categorized as similar in function to an IF-THEN-ELSE statement?
SQRT
DECODE
NEW_TIME
ROWIDTOCHAR
Which two of the following orders are used in ORDER BY clauses? (choose two)
ABS
ASC
DESC
DISC
You query the database with this command
SELECT name
FROM employee
WHERE name LIKE ‘_a%’;
Which names are displayed?
Names starting with “a”
Names starting with “aR
or “A”
Names containing “aR
as second character
Names containing “aR
as any letter except the first
Posted in: General |
13 Responses to “SQL interview questions”
db don Says:
May 16th, 2005 at 7:55 pm
1) “SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;” is incorrect
2) “Place the alias after each column, separated by white space, to describe the column.” Correct
3) Ans is 3 it is same as isNull in T SQL
Ajay Kumar Peddireddy Says:
February 6th, 2006 at 7:52 am
1)SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
2)Place the alias after each column, separated by white space, to describe the column
3)Allows the user to specify alternate output for null column values.
4)SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;
5)The emacs editor will become the SQL*Plus default text editor. (Not sure about this answer)
6)LOA
7)FALSE
8)DUAL
9)max( )
10)Until the statement completes
11)Ampersand
12)select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;
13)A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER
14)INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
15) VARCHAR2 , BOOLEAN ,NUMBER
16) The delete statement will remove all records from the table.
17) TRUE
18) The index will be dropped.
19) Ensure that all column values are NULL for all rows. (Not sure about this answer)
20) SELECT statement is not allowed in CHeck constraint.
21)
22) No error if all the values are given in single quotes instead of double quotes.
23) DECODE
24) ASC, DESC
25) will give all the enames that has ‘a’ in the second place and followed by any number of characters. _ specifies as a single character and % as many characters.
Mrs Bisht Says:
October 17th, 2006 at 11:11 am
1)SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
2)Place the alias after each column, separated by white space, to describe the column
3)Allows the user to specify alternate output for null column values.
4)SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;
5)The emacs editor will become the SQL*Plus default text editor. (Not sure about this answer)
6)LOA
7)FALSE
8)DUAL
9)max( )
10)Until the statement completes
11)Ampersand
12)select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;
13)A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER
14)INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
15) VARCHAR2 , BOOLEAN ,NUMBER
16) The delete statement will remove all records from the table.
17) TRUE
18) The index will be dropped.
19) Ensure that all column values are NULL for all rows. (Not sure about this answer)
20) SELECT statement is not allowed in CHeck constraint.
21)
22) No error if all the values are given in single quotes instead of double quotes.
23) DECODE
24) ASC, DESC
25) will give all the enames that has ‘a’ in the second place and followed by any number of characters. _ specifies as a single character and % as many characters.
Acrien Says:
October 28th, 2006 at 7:21 pm
some wrong answers up top.
1. Which of the following statements contains an error?
1. SELECT * FROM emp WHERE empid = 493945;
2. SELECT empid FROM emp WHERE empid= 493945;
3. SELECT empid FROM emp;
*** 4. SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
No from clause.
2 Which of the following correctly describes how to specify a column alias?
1. Place the alias at the beginning of the statement to describe the table.
*** 2. Place the alias after each column, separated by white space, to describe the column.
3. Place the alias after each column, separated by a comma, to describe the column.
4. Place the alias at the end of the statement to describe the table.
3 The NVL function
1. Assists in the distribution of output across multiple columns.
2. Allows the user to specify alternate output for non-null column values.
****3. Allows the user to specify alternate output for null column values.
4. Nullifies the value of the column output.
4 Output from a table called PLAYS with two columns, PLAY_NAME and AUTHOR, is shown below. Which of the following SQL statements produced it?
PLAY_TABLE
————————————-
“Midsummer Night’s Dream”, SHAKESPEARE
“Waiting For Godot”, BECKETT
“The Glass Menagerie”, WILLIAMS
1. SELECT play_name || author FROM plays;
2. SELECT play_name, author FROM plays;
3. SELECT play_name||’, ‘ || author FROM plays;
***4. SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;
1 will produce titleauthor
2 will produce title author
3 will produce title, author but column name will be play_name||’,'||author
5 Issuing the DEFINE_EDITOR=”emacs” will produce which outcome?
****1. The emacs editor will become the SQL*Plus default text editor.
2. The emacs editor will start running immediately.
3. The emacs editor will no longer be used by SQL*Plus as the default text editor.
4. The emacs editor will be deleted from the system.
6 The user issues the following statement. What will be displayed if the EMPID selected is 60494?
SELECT DECODE(empid,38475, “Terminated”,60494, “LOA”, “ACTIVE”)
FROM emp;
1. 60494
*** 2. LOA
3. Terminated
4. ACTIVE
decode says if(empid = 38475, output ‘terminated
if(empid = 60409, output ‘loa’
(for everything else, output ‘active’)
7 SELECT (TO_CHAR(NVL(SQRT(59483), “INVALID”)) FROM DUAL is a valid SQL statement.
1. TRUE
**** 2. FALSE
Cannot set value to another datatype when using NVL, hence if SQRT(59483) is null somehow, you must assign it a number, “invalid” is a string.
8 The appropriate table to use when performing arithmetic calculations on values defined within the SELECT statement (not pulled from a table column) is
1. EMP
2. The table containing the column values
***3. DUAL
4. An Oracle-defined table
Dual is a dummy table.
9 Which of the following is not a group function?
1. avg( )
*****2. sqrt( )
3. sum( )
4. max( )
all other functions can work on more than one row of data.
10 Once defined, how long will a variable remain so in SQL*Plus?
1. Until the database is shut down
2. Until the instance is shut down
**** 3. Until the statement completes
**** 4. Until the session completes
This one depends on how you defined it. If you define variable using & then 3, && then 4.
11 The default character for specifying runtime variables in SELECT statements is
*** 1. Ampersand
2. Ellipses
3. Quotation marks
4. Asterisk
This is basic syntax. In pl/sql though, this is compile time, not run time.
12 A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?
1. select e.empid, d.head from emp e, dept d;
2. select e.empid, d.head from emp e, dept d where e.dept# = d.dept#;
*****3. select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+);
4. select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;
Always put the (+) on the deficient side, so since emp has more data that cannot be matched in dept table, dept table has less data than emp hence it is the deficient side!
13 Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?
1. A table named ANIMALS will be created in the MASTER schema with the same data as the ANIMALS table owned by ANJU.
2. A table named ANJU will be created in the ANIMALS schema with the same data as the ANIMALS table owned by MASTER.
**** 3. A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
4. A table named MASTER will be created in the ANIMALS schema with the same data as the ANJU table owned by ANIMALS.
refer to another’s table by using schemaname.objectname.
14 User JANKO would like to insert a row into the EMPLOYEE table, which has three columns: EMPID, LASTNAME, and SALARY. The user would like to enter data for EMPID 59694, LASTNAME Harris, but no salary. Which statement would work best?
****1. INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
2. INSERT INTO employee VALUES (59694,’HARRIS’);
3. INSERT INTO employee (EMPID, LASTNAME, SALARY) VALUES (59694,’HARRIS’);
4. INSERT INTO employee (SELECT 59694 FROM ‘HARRIS’);
The only statement that does not contain error.
15 Which three of the following are valid database datatypes in Oracle? (Choose three.)
***1. CHAR
***2. VARCHAR2
3. BOOLEAN
***4. NUMBER
Oracle does not support boolean. Boolean can be represented by 0 or 1 and therefore no need to create a datatype for it.
16 Omitting the WHERE clause from a DELETE statement has which of the following effects?
1. The delete statement will fail because there are no records to delete.
2. The delete statement will prompt the user to enter criteria for the deletion
3. The delete statement will fail because of syntax error.
***4. The delete statement will remove all records from the table.
If select statement does not have a where clause, it would select all rows. Hence delete without where clause would delete all rows.
17 Creating a foreign-key constraint between columns of two tables defined with two different datatypes will produce an error.
***1. TRUE
2. FALSE
18 Dropping a table has which of the following effects on a nonunique index created for the table?
1. No effect.
***2. The index will be dropped.
3. The index will be rendered invalid.
4. The index will contain NULL values.
19 To increase the number of nullable columns for a table,
***1. Use the alter table statement.
2. Ensure that all column values are NULL for all rows.
3. First increase the size of adjacent column datatypes, then add the column.
4. Add the column, populate the column, then add the NOT NULL constraint.
Bad or badly phrased question, but to increase the number of any kind of column, you’d use alter table statement.
20 Which line of the following statement will produce an error?
1. CREATE TABLE goods
2. (good_no NUMBER,
***3. good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)),
4. CONSTRAINT pk_goods_01
5. PRIMARY KEY (goodno));
6. There are no errors in this statement.
Cannot have subquery in check.
21 MAXVALUE is a valid parameter for sequence creation.
***1. TRUE
2. FALSE
22 Which of the following lines in the SELECT statement below contain an error?
1. SELECT DECODE(empid, 58385, “INACTIVE”, “ACTIVE”) empid
2. FROM emp
***3. WHERE SUBSTR(lastname,1,1) > TO_NUMBER(’S’)
4. AND empid > 02000
5. ORDER BY empid DESC, lastname ASC;
6. There are no errors in this statement.
cannot convert a letter to a number.
23 Which function below can best be categorized as similar in function to an IF-THEN-ELSE statement?
1. SQRT
***2. DECODE
3. NEW_TIME
4. ROWIDTOCHAR
24 Which two of the following orders are used in ORDER BY clauses? (choose two)
1. ABS
***2. ASC
***3. DESC
4. DISC
25 You query the database with this command
SELECT name
FROM employee
WHERE name LIKE ‘_a%’;
Which names are displayed?
(should read as following I think)
1. Names starting with “a”
2. Names starting with “aR or “A”
***3. Names containing “aR” as second character
4. Names containing “aR” as any letter except the first
It still doesn’t make much sense as ‘aR’ cannot both be second character. the pattern would be to have lower case ‘a’ as second character, followed by anything and start with anything.
pravasis Says:
March 6th, 2007 at 4:32 am
1.SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’; (no from)
2.Place the alias after each column, separated by white space, to describe the column.
3.Allows the user to specify alternate output for null column values.
4.SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;
5.The emacs editor will become the SQL*Plus default text editor.
6.LOA(decode(column_name,if_value1,show_value1,if_value2,show_value2,rest)
7.false(Datatype mismatch)
8.DUAL
9.sqrt(mathematical function)
10.Until the session completes
11.Ampersand
12.select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#; (left outer join)
13.A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
14.INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
15.CHAR,VARCHAR2,NUMBER
16.The delete statement will remove all records from the table.
17.TRUE
18.The index will be dropped.
19.Ensure that all column values are NULL for all rows.
20.good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)), (select may not be allowed)
21.TRUE
22.WHERE SUBSTR(lastname,1,1) > TO_NUMBER(’S’) (datatype mismatch)
23.DECODE
24.ASC DESC
25.a as second character
Thiyagu Says:
March 24th, 2007 at 10:55 am
how to add salary of two tables and store into a result in any one table
i need this output
salarycolumn of emp salarycolumn of emp result
1100 1200 1300
johnny Says:
June 12th, 2007 at 8:30 am
Hi Thiyagu,
This is johnny…
the answer for ur query…
if u hav any queries plz do let me know…
leave a comment
PRE-REQUISITE:
CREATE 2 TABLES(EMP1,EMP2) BASED ON EMP TABLE.AND ADD COLUMN (result NUMBER(6)) in EMP1
– PROGRAM STARTS FROM HERE
DECLARE
V_SAL1 NUMBER;
V_SAL2 NUMBER;
V_RESULT NUMBER;
V_EMPNO1 NUMBER:=7654;
V_EMPNO2 NUMBER:=7369;
BEGIN
SELECT SAL INTO V_SAL1 FROM EMP1 WHERE EMPNO=V_EMPNO1;
SELECT SAL INTO V_SAL2 FROM EMP2 WHERE EMPNO=V_EMPNO2;
V_RESULT := V_SAL1 + V_SAL2 ;
DBMS_OUTPUT.PUT_LINE(’RESULT IS:’||V_RESULT);
– SENDING THE RESULT INTO EMP1 TABLE
UPDATE EMP1 SET RESULT = V_RESULT WHERE EMPNO=V_EMPNO1;
END;
Srikanth Says:
July 9th, 2007 at 7:47 am
1.
4. SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
2.
2. Place the alias after each column, separated by white space, to describe the column.
3. The NVL function
3. Allows the user to specify alternate output for null column values.
4.
4. SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;
5.
1. The emacs editor will become the SQL*Plus default text editor.
6.
2. LOA
7.
2. FALSE ( invalid number error occurred checked).
8.
3. DUAL
9.
2. sqrt( )
single row function
10.
4. Until the session completes
11.
1. Ampersand
12.
4. select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;
13.
3. A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
14.
1. INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
15. Which three of the following are valid database datatypes in Oracle? (Choose three.)
1. CHAR
2. VARCHAR2
4. NUMBER
boolean is an pl/sql datatype in oracle.
16.
4. The delete statement will remove all records from the table.
17.
1. TRUE
18.
2. The index will be dropped.
19.
1. Use the alter table statement.
20.
3. good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)),
21.
1. TRUE
22.
3. WHERE SUBSTR(lastname,1,1) > TO_NUMBER(’S’)
23.
2. DECODE
24.
2. ASC
3. DESC
25.
5. as second character
gaurav gaur Says:
October 22nd, 2007 at 11:22 am
LTER procedure gaurav
@tablename nvarchar(40)
as
declare @column_name nvarchar(34)
declare @gencode nvarchar(234)
declare @sqlstring varchar(234)
declare @commandobjectname nvarchar(2345)
set @gencode=”
set @commandobjectname=’mycommand’
select @gencode=@gencode +char(13)+’mycommand.parameters.addwithvalue(”@’+column_name+’”‘+column_name+’)’ from information_columns.columns where table_name=@tablename
print @gencode
gaurav gaur Says:
October 22nd, 2007 at 11:26 am
aLTER procedure gaurav
@tablename nvarchar(40)
as
declare @column_name nvarchar(34)
declare @gencode nvarchar(234)
declare @sqlstring varchar(234)
declare @commandobjectname nvarchar(2345)
set @gencode=”
set @commandobjectname=’mycommand’
select @gencode=@gencode +char(13)+’mycommand.parameters.addwithvalue(”@’+column_name+’”‘+column_name+’)’ from information_columns.columns where table_name=@tablename
print @gencode
“this stored procedure id best those who are working as asp.net developer bcz in .net we use to cm.parametrs.addwithvalue(”columnname’@columnname)
we did not write any above code if we foolow above stored procedure just execute it
with the help of command exe gaurav ‘tablename”
happy coding
masti Says:
December 2nd, 2007 at 9:32 pm
.SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’; (no from)
2.Place the alias after each column, separated by white space, to describe the column.
3.Allows the user to specify alternate output for null column values.
4.SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;
5.The emacs editor will become the SQL*Plus default text editor.
6.LOA(decode(column_name,if_value1,show_value1,if_value2,show_value2,rest)
7.false(Datatype mismatch)
8.DUAL
9.sqrt(mathematical function)
10.Until the session completes
11.Ampersand
12.select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#; (left outer join)
13.A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
14.INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
15.CHAR,VARCHAR2,NUMBER
16.The delete statement will remove all records from the table.
17.TRUE
18.The index will be dropped.
19.Ensure that all column values are NULL for all rows.
20.good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)), (select may not be allowed)
21.TRUE
22.WHERE SUBSTR(lastname,1,1) > TO_NUMBER(’S’) (datatype mismatch)
23.DECODE
24.ASC DESC
25.a as second character
preppy Says:
January 2nd, 2008 at 2:20 am
Which of the following statements contains an error?
SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
Which of the following correctly describes how to specify a column alias?
Place the alias after each column, separated by white space, to describe the column.
The NVL function
Allows the user to specify alternate output for null column values.
Output from a table called PLAYS with two columns, PLAY_NAME and AUTHOR, is shown below. Which of the following SQL statements produced it?
PLAY_TABLE
————————————-
“Midsummer Night’s Dream”, SHAKESPEARE
“Waiting For Godot”, BECKETT
“The Glass Menagerie”, WILLIAMS
SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;
Issuing the DEFINE_EDITOR=”emacs” will produce which outcome?
The emacs editor will become the SQL*Plus default text editor.
The user issues the following statement. What will be displayed if the EMPID selected is 60494?
SELECT DECODE(empid,38475, “Terminated”,60494, “LOA”, “ACTIVE”)
FROM emp;
LOA
SELECT (TO_CHAR(NVL(SQRT(59483), “INVALID”)) FROM DUAL is a valid SQL statement.
TRUE
The appropriate table to use when performing arithmetic calculations on values defined within the SELECT statement (not pulled from a table column) is
EMP
DUAL
Which of the following is not a group function?
sqrt( )
Once defined, how long will a variable remain so in SQL*Plus?
Until the session completes
The default character for specifying runtime variables in SELECT statements is
Ampersand
A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?
select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+);
Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?
A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
User JANKO would like to insert a row into the EMPLOYEE table, which has three columns: EMPID, LASTNAME, and SALARY. The user would like to enter data for EMPID 59694, LASTNAME Harris, but no salary. Which statement would work best?
INSERT INTO employee (EMPID, LASTNAME, SALARY) VALUES (59694,’HARRIS’);
Which three of the following are valid database datatypes in Oracle? (Choose three.)
CHAR
VARCHAR2
NUMBER
Omitting the WHERE clause from a DELETE statement has which of the following effects?
The delete statement will remove all records from the table.
Creating a foreign-key constraint between columns of two tables defined with two different datatypes will produce an error.
TRUE
Dropping a table has which of the following effects on a nonunique index created for the table?
The index will be dropped.
To increase the number of nullable columns for a table,
Use the alter table statement.
Which line of the following statement will produce an error?
good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)),
MAXVALUE is a valid parameter for sequence creation.
TRUE (not sure)
Which of the following lines in the SELECT statement below contain an error?
WHERE SUBSTR(lastname,1,1) > TO_NUMBER(’S’)
Which function below can best be categorized as similar in function to an IF-THEN-ELSE statement?
DECODE
Which two of the following orders are used in ORDER BY clauses? (choose two)
ASC
DESC
You query the database with this command
SELECT name
FROM employee
WHERE name LIKE ‘_a%’;
Which names are displayed?
RaiS Says:
January 16th, 2008 at 6:37 am
1.Which of the following statements contains an error?
SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;
2.Which of the following correctly describes how to specify a column alias?
Place the alias after each column, separated by white space, to describe the column.
3.The NVL function
Allows the user to specify alternate output for non-null column values.
4.Output from a table called PLAYS with two columns, PLAY_NAME and AUTHOR, is shown below. Which of the following SQL statements produced it?
PLAY_TABLE
————————————-
“Midsummer Night’s Dream”, SHAKESPEARE
“Waiting For Godot”, BECKETT
“The Glass Menagerie”, WILLIAMS
None of the above
5.Issuing the DEFINE_EDITOR=”emacs” will produce which outcome?
The emacs editor will become the SQL*Plus default text editor.
6. The user issues the following statement. What will be displayed if the EMPID selected is 60494?
SELECT DECODE(empid,38475, “Terminated”,60494, “LOA”, “ACTIVE”)
FROM emp;
LOA
Which of the following is not a group function?
sqrt( )
Once defined, how long will a variable remain so in SQL*Plus?
Until the session completes
The default character for specifying runtime variables in SELECT statements is
Ampersand
A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?
select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;
Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?
A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
User JANKO would like to insert a row into the EMPLOYEE table, which has three columns: EMPID, LASTNAME, and SALARY. The user would like to enter data for EMPID 59694, LASTNAME Harris, but no salary. Which statement would work best?
INSERT INTO employee VALUES (59694,’HARRIS’);
Which three of the following are valid database datatypes in Oracle? (Choose three.)
CHAR
VARCHAR2
NUMBER
Omitting the WHERE clause from a DELETE statement has which of the following effects?
The delete statement will remove all records from the table.
Creating a foreign-key constraint between columns of two tables defined with two different datatypes will produce an error.
TRUE
Dropping a table has which of the following effects on a nonunique index created for the table?
The index will be dropped.
Which line of the following statement will produce an error?
CREATE TABLE goods
(good_no NUMBER,
good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)),
CONSTRAINT pk_goods_01
PRIMARY KEY (goodno));
There are no errors in this statement.
PRIMARY KEY (goodno));
MAXVALUE is a valid parameter for sequence creation.
TRUE
FALSE
Which two of the following orders are used in ORDER BY clauses? (choose two)
ASC
DESC
You query the database with this command
SELECT name
FROM employee
WHERE name LIKE ‘_a%’;
Which names are displayed?
Names containing “aR
as second character
PHP interview questions
What does a special set of tags <?= and ?> do in PHP? - The output is displayed directly to the browser.
What’s the difference between include and require? - It’s how they handle failures. If the file is not found by require(), it will cause a fatal error and halt the execution of the script. If the file is not found by include(), a warning will be issued, but execution will continue.
I am trying to assign a variable the value of 0123, but it keeps coming up with a different number, what’s the problem? - PHP Interpreter treats numbers beginning with 0 as octal. Look at the similar PHP interview questions for more numeric problems.
Would I use print "$a dollars" or "{$a} dollars" to print out the amount of dollars in this example? - In this example it wouldn’t matter, since the variable is all by itself, but if you were to print something like "{$a},000,000 mln dollars", then you definitely need to use the braces.
How do you define a constant? - Via define() directive, like define ("MYCONSTANT", 100);
How do you pass a variable by value? - Just like in C++, put an ampersand in front of it, like $a = &$b
Will comparison of string "10" and integer 11 work in PHP? - Yes, internally PHP will cast everything to the integer type, so numbers 10 and 11 will be compared.
When are you supposed to use endif to end the conditional statement? - When the original if was followed by : and then the code block without braces.
Explain the ternary conditional operator in PHP? - Expression preceding the ? is evaluated, if it’s true, then the expression preceding the : is executed, otherwise, the expression following : is executed.
How do I find out the number of parameters passed into function? - func_num_args() function returns the number of parameters passed in.
If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b? - 100, it’s a reference to existing variable.
What’s the difference between accessing a class method via -> and via ::? - :: is allowed to access methods that can perform static operations, i.e. those, which do not require object initialization.
Are objects passed by value or by reference? - Everything is passed by value.
How do you call a constructor for a parent class? - parent::constructor($value)
What’s the special meaning of __sleep and __wakeup? - __sleep returns the array of all the variables than need to be saved, while __wakeup retrieves them.
Why doesn’t the following code print the newline properly? <?php
$str = ‘Hello, there.nHow are you?nThanks for visiting TechInterviews’;
print $str;
?>
Because inside the single quotes the n character is not interpreted as newline, just as a sequence of two characters - and n.
Would you initialize your strings with single quotes or double quotes? - Since the data inside the single-quoted string is not parsed for variable substitution, it’s always a better idea speed-wise to initialize a string with single quotes, unless you specifically need variable substitution.
How come the code <?php print "Contents: $arr[1]"; ?> works, but <?php print "Contents: $arr[1][2]"; ?> doesn’t for two-dimensional array of mine? - Any time you have an array with more than one dimension, complex parsing syntax is required. print "Contents: {$arr[1][2]}" would’ve worked.
What is the difference between characters
$formatted = ucwords("TECHINTERVIEWS IS COLLECTION OF INTERVIEW QUESTIONS");
print $formatted;
What will be printed is TECHINTERVIEWS IS COLLECTION OF INTERVIEW QUESTIONS.
ucwords() makes every first letter of every word capital, but it does not lower-case anything else. To avoid this, and get a properly formatted string, it’s worth using strtolower() first.
What’s the difference between htmlentities() and htmlspecialchars()? - htmlspecialchars only takes care of <, >, single quote ‘, double quote " and ampersand. htmlentities translates all occurrences of character sequences that have different meaning in HTML.
What’s the difference between md5(), crc32() and sha1() crypto on PHP? - The major difference is the length of the hash generated. CRC32 is, evidently, 32 bits, while sha1() returns a 128 bit value, and md5() returns a 160 bit value. This is important when avoiding collisions.
So if md5() generates the most secure hash, why would you ever use the less secure crc32() and sha1()? - Crypto usage in PHP is simple, but that doesn’t mean it’s free. First off, depending on the data that you’re encrypting, you might have reasons to store a 32-bit value in the database instead of the 160-bit value to save on space. Second, the more secure the crypto is, the longer is the computation time to deliver the hash value. A high volume site might be significantly slowed down, if frequent md5() generation is required.
How do you match the character ^ at the beginning of the string? - ^^
Posted in: Unix/Linux, Web dev |
19 Responses to “PHP interview questions”
Pushpan Says:
December 15th, 2005 at 1:29 am
Correction for the 11 the Question of PHP interview questions
————————————————————-
# If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b? -
100, it’s a reference to existing variable.
Correction
———-
$$b=$a Where as the value of $a=5;
so the Actual answer is 5.
John Says:
December 25th, 2005 at 5:24 am
#26 is in error
CRC32 is 32 bits long, md5 is 128 bits, and sha1() returns a 160-bit value.
Divya Says:
January 31st, 2006 at 6:50 am
Correction for the 11 the Question of PHP interview questions
————————————————————-
# If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b? -
100, it’s a reference to existing variable.
Correction
———-
$$b=$a Where as the value of $a=5;
so the Actual answer is 5.
vasi Says:
June 30th, 2006 at 3:01 am
i like to that wht is different b/w the php4.3 to php5 ?
how can note that string varible?
phpUser Says:
July 22nd, 2006 at 12:05 am
Correction for 6
It asks how to pass a variable by value. The question should of course be how to pass a variable by *reference*. & does by reference, all else is by value as Q13 indicates.
santraj Says:
October 2nd, 2006 at 12:30 am
Q.N11 If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b? - 5.
phplearner Says:
December 23rd, 2006 at 2:39 pm
this is really nice for PHP self-learner like me, but i like to view more and as few expert
here made corrections, thx to them, But you should make post Q&A after recheck as learner do not confuse.
i am looking for my first php job interview , please let me know the more interview question or free online test on php and any book or link to refer . at limelightworld AT yahooo Dot c0m
thanks to all,
Kiran Says:
March 15th, 2007 at 7:09 am
Correction for the 11 the Question of PHP interview questions
————————————————————-
# If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b? -
100, it’s a reference to existing variable.
Correction
———-
$$b=$a Where as the value of $a=5;
so the Actual answer is 5.
Daddy Says:
April 15th, 2007 at 8:15 pm
I just had an interview with Yahoo and they asked that what’s the fastest way to read 1 million lines from an Apache log file with PHP?
I answered like this. I might use Unix command in PHP to split up my file to smaller sizes, then I tried to read them. I couldn’t think of any other ways. I know you could setup your Apache to split log files daily. Then, you can rotate and process them daily, but this is not the case. The log file already contains 1M lines. After interview, I made a research little bit, and I found that fseek() function allows you to read large files faster.
Anybody knows any other ways?
alok jain Says:
April 19th, 2007 at 6:58 am
If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b?
Ans: its would be 5.
Ian Says:
April 26th, 2007 at 4:18 pm
13. Are objects passed by value or by reference? - Everything is passed by value.
The correct answer is for PHP 5, it is by reference and for PHP 4 is by value.
arehalliravi Says:
May 1st, 2007 at 7:59 am
what’s the fastest way to read 1 million lines from an Apache log file with PHP?
can anyone answer this question pls…?
demon Says:
May 2nd, 2007 at 10:10 am
When working with VERY large files, php tends to fall over sideways and die.
Here is a neat way to pull chunks out of a file very fast and won’t stop in mid line, but rater at end of last known line. It pulled a 30+ million line 900meg file through in ~ 24 seconds.
NOTE:
$buf just hold current chunk of data to work with. If you try “$buf .=” (note ‘dot’ in from of ‘=’) to append $buff, script will come to grinding crawl around 100megs of data, so work with current data then move on!
//File to be opened
$file = "huge.file";
//Open file (DON'T USE a+ pointer will be wrong!)
$fp = fopen($file, 'r');
//Read 16meg chunks
$read = 16777216;
//n Marker
$part = 0;
while(!feof($fp)) {
$rbuf = fread($fp, $read);
for($i=$read;$i > 0 || $n == chr(10);$i--) {
$n=substr($rbuf, $i, 1);
if($n == chr(10))break;
//If we are at the end of the file, just grab the rest and stop loop
elseif(feof($fp)) {
$i = $read;
$buf = substr($rbuf, 0, $i+1);
break;
}
}
//This is the buffer we want to do stuff with, maybe thow to a function?
$buf = substr($rbuf, 0, $i+1);
//Point marker back to last n point
$part = ftell($fp)-($read-($i+1));
fseek($fp, $part);
}
fclose($fp);
waylon Says:
May 6th, 2007 at 2:58 pm
The answer for question 4 is incorrect. Printing:
“$a,000,000 mln dollars” will work fine since ‘,’ cannot be part of a variable name. It would not work for “$a000,000 mln dollars” though, and you would have to have the braces:
“${a}000,000 mln dollars”. The reason for this is because the parser looks for as many tokens as it can that make up a valid variable name, so when a token such as ‘,’ is encountered, it knows that it cannot be part of the variable name.
Devin Says:
October 2nd, 2007 at 2:12 pm
Answer to Question #1 is incorrect.
The tags tell the PHP engine that what is between these two tags is PHP code and should be interpreted. Another way of saying this is that these tags seperate the php “script” from the XHTML markup tags on a web page
Trey Says:
October 16th, 2007 at 12:19 am
NOW A CORRECTION TO MYSELF, I USED TAGS, SHOULD HAVE USED SPECIAL CHARS
Correction to Devin,
Actually whatever is between the tags will be printed. It is synonymous with <?= echo ?>
I.E. <?= ‘hello’; ?> would be the same as <?php echo ‘hello’; ?>
Its just another, shorter way to skin a cat, but you must have the short_open_tags directive turned on in the ini.
Cheers.
nithya Says:
November 7th, 2007 at 12:18 am
# If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b? -
100, it’s a reference to existing variable.
Correction
———-
$a=5;
$$b=$a;
echo $$b;
the answer is 5
debashis Says:
November 8th, 2007 at 12:46 am
I want to take two inputs from the user :1) year, 2)day. Then i have to find the first given day of every month of that particular year given by the user.As example (inputs are 2007 and Friday…then i have to show every first Friday of every month of the given year 2007)
Scott Says:
February 19th, 2008 at 12:22 pm
Demon,
With #13, that’s a pretty good technique for slurping in Apache logs one chunk at a time.
Presumably if you’re reading in Apache logs, you want to DO something with them using iteration. You can’t easily iterate over a multi-line string… sure, you can explode() the string into an array (but doing so would double memory use!).
I’m actually curious now how to do this so that the read creates a $rbuf_array instead of $rbuf…
1. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
It will be a good idea to read up a database designing fundamentals text book.
2. What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but nique key allows one NULL only.
3. What are user defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.
4. What is bit datatype and what's the information that can be stored inside a bit column?
Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.
5. Define candidate key, alternate key, composite key
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
6. What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.
7. What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
8. Explain different isolation levels
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
9. CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
10. What's the maximum size of a row?
8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
11. Explain Active/Active and Active/Passive cluster configurations
Hopefully you have experience setting up cluster servers. But if you don't, at least be familiar with the way clustering works and the two clustering configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site.
12. Explain the architecture of SQL Server
This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.
13. What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
14. What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
15. Explain the storage models of OLAP
Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more infomation.
16. What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?
This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled "What's New", which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled "Backward Compatibility" in books online which talks about the changes that have taken place in the new version.
1. What are constraints? Explain different types of constraints
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
2. What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
3. What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, check out the RAID advisory board's homepage.
4. What are the steps you will take to improve performance of a poor performing query?
This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.
Download the white paper on performance tuning SQL Server from Microsoft web site. Don't forget to check out sql-server-performance.com
5. What are the steps you will take, if you are tasked with securing an SQL Server?
Again this is another open ended question. Here are some things you could talk about: Preferring NT authentication, using server, database and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc.
6. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
7. What is blocking and how would you troubleshoot it?
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions.
8. Explain CREATE DATABASE syntax
Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for more information.
9. How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal configuration mode. Check out SQL Server books online for more parameters and their explanations.
10. As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.
11. What are statistics, under what circumstances they go out of date, how do you update them?
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.
Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3) Database is upgraded from a previous version
Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats
12. What are the different ways of moving data/databases between servers and databases in SQL Server?
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.
13. Explain different types of BACKUPs available in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.
14. What is database replication? What are the different types of replication you can set up in SQL Server?
Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:
o Snapshot replication
o Transactional replication (with immediate updating subscribers, with queued updating subscribers)
o Merge replication
See SQL Server books online for indepth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.
15. How to determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Cursors allow row-by-row processing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row.
17. Write down the general syntax for a SELECT statements covering all the options
Here's the basic syntax: (Also checkout SELECT in books online for advanced syntax).
SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]
18. What is a join and explain different types of joins
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
19. Can you have a nested transaction?
Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT
20. What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL,just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy.
21. What is the system function to get the current user's user id?
USER_ID().Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
22. What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.
23. There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?
Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.
24. What is a self join? Explain it with an example
Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid
1. How do I indent the first line in my paragraphs?
This isn't really possible in a reliable way, until style sheets are more widely supported. At this moment, there are several browser-specific kludges and tricks available, but these are not guaranteed to work.
1) Use a number of ( ) characters. Netscape and related browsers do not collapse these, like normal spaces, so this appears as an indent in these browsers. Other browsers can display it as one space.
2) Put a <DD> at the beginning of the line. This is syntactically invalid, but Netscape works around this by indenting the line at this ball. Of course, other browsers will handle this differently, and there is no guarantee that Netscape will keep doing this.
3) Use a blank, transparent GIF, using WIDTH and HEIGHT to indicate the desired white space. This is a very ugly solution, as it only works if you have image loading on, otherwise you get the "Image" icon at the beginning of the line. Not all browsers support resizing using these attributes, and you can only "indent" a certain number of pixels, not characters. So the amount of "indentation" varies with the font size used to display your document.
2. How do I indent a lot of text?
Again, there is no reliable way to do this. Netscape will indent text inside a <BLOCKQUOTE>, but other browsers don't have to do this. These could show the text in italics, or perhaps with quotation marks around the text. This could come out very strange.
An alternative is to use <DL> without <DT> and <DD>, which is invalid HTML, but several browsers work around this error by indenting the text inside it. This is not guaranteed to work.
If you are willing to use tables for layout purposes, there is another option. Create a one-cell table, as follows:
<CENTER>
<TABLE width="805px">
<TR><TD><DIV align=left>
<!-- The text goes here -->
</DIV>
</TD></TR>
</TABLE>
</CENTER>
A drawback to this solution is that very long blocks inside a table may take a while to download and may not appear until the entire table has been downloaded. Another drawback is that it may force users to resize their viewing window after they have become accustomed to their preferred settings.
3. Can I put markup in ALT text?
No. Character entities (©, &#nnn; and such) are permitted, though.
If you want to know how to write good ALT texts without markup, please see Alan Flavell's essay on choosing ALT texts.
4. How do I include one file in another?
Use server-side includes, if your server supports them. Ask your Webmaster if this is the case, and if so, what the exact syntax is for your server.
Since server-side includes make the document slower, they are not always desirable. If your documents only have a static footer, which doesn't change every day, you might be better off by using an editor which can insert files in the current document, or a preprocessor. The C preprocessor can do this, but there are also several HTML-specific preprocessors available.
5. How do I get scrolling text in the status bar?
This is not an HTML question, it's done with a Javascript. Check any page which has this feature, and copy the script from the source.
This script has two big problems. One, usually it uses the decrement operator (c--) at some ball. The "--" sequence in a comment actually closes it on some browsers, so your code may "leak" on those browsers. The same goes for ">".
Second, keep in mind that many people consider this even worse than <BLINK>, and that it also suppresses the status information which normally appears there. It prevents people from knowing where a link goes to.
6. How do I hide my source?
You can't. The source is necessary for the browser to display your document. You have to send the complete, unencrypted source to the browser. Even if a particular browser doesn't have a "View source" option, there are many that do, and you can always retrieve the document by hand (using telnet) to get its source. Or check the browser's cache.
You can of course put a few hundred empty lines above the actual source, then newbies who don't see the scrollbars will think there is nothing there.
7. How can I make a custom rule, or a list with custom bullets?
There was a proposal in the now-expired HTML 3 draft to handle exactly this: just add SRC to the <HR> or <UL> tag, indicating where the image can be found. But until this is more widely supported, you have to use <IMG> for the rule, with a lot of "--" characters as ALT text for text browsers, and using a <DL> with only <DD> tags for each item. Make sure you use ALIGN for the image, which should go at the beginning of the item, of course. This isn't as beautiful as a "real" list.
An alternative is using a two column table, with the bullets in the left column, and the text in the right. But this won't work well on non-table supporting browsers.
8. How do I display the current date or time in my document?
With server-side includes. Ask your webmaster if this is supported, and what the exact syntax is for your server. But this will display the local time on the server, not for the client. And if the document is cached, the date will of course be incorrect after some time.
JavaScript can be used to display the local time for the client, but as most people already have one or more clocks on their screen, why display another one?
9. For what screen size should I write?
HTML does not depend on screen size. The text will be wrapped by the browser when the end of the screen is encountered. The only exception to this is when you use <PRE>-formatted text, which will only wrap at the line breaks you indicate. So make sure these lines are no longer than 70 characters, otherwise text mode users will see ugly line breaks on their terminals. And users of graphical browsers might have to scroll horizontally to see the rest, which is one of the most hated things to do when you read a document.
Of course, an image cannot be wrapped, so you have to be careful with that. It seems that 400 or 500 pixels is a reasonable width; anything above 600 will mean a certain percentage of users will have to scroll to see the rightmost bit. This percentage increases with your image width. Keep in mind that not everyone runs his browser at full screen!
10. How do I get my visitor's e-mail addresses?
You can't. Although each request for a document is usually logged with the name or address of the remote host, the actual username is almost never logged as well. This is mostly because of performance reasons, as it would require that the server uses the ident protocol to see who is on the other end. This takes time. And if a cache proxy is doing the request, you don't get anything sensible.
In Netscape 2.0, it was possible to automatically submit a form with a mailto as action, using Javascript. This would send e-mail to the document's owner, with the address the visitor configured in the From line. Of course, that can be "mickey.mouse@disney.com". This is fixed in Netscape 2.01.
The most reliable way is to put up a form, asking the visitor to fill in his e-mail address. If you offer him something in return, he will most likely do it.
11. How do I do a pagebreak?
You don't. HTML is not a page layout language. It's up to the browser to decide where and how to insert page breaks when the document is being printed.
However, style sheets (not widely supported yet, although Microsoft's Internet Explorer is beginning to use it) will include support to indicate preferred balls for page breaks, probably somewhat like the way LaTeX handles this.
12. How do I make a table which looks good on AOL and Prodigy?
The best way is probably to include a version in preformatted text. This can be seen by any browser, including Lynx.
If you absolutely must have a table, check out Alan Flavell's document on tables for a good discussion.
13. How do I center a table?
The "correct" way of doing it is <TABLE ALIGN=CENTER>, but this doesn't work in several popular browsers. Put <CENTER> around the entire table for these browsers.
This causes some problems with browser that do support CENTER but not tables, such as Lynx. In these browsers, the contents of the cells is now displayed centered, which is not what is intended. To avoid this, you can put the cell's contents in <P ALIGN=left> or <DIV ALIGN=left> depending on the amount of text in the cell.
14. How do I make animated GIFs?
Ask on the comp.infosystems.www.authoring.images group.
15. Is there a way to get indexed better by the search engines?
Yes. Put these two statements in the <HEAD> part of your documents:
<META NAME="keywords" CONTENT="keyword keyword keyword keyword">
<META NAME="description" CONTENT="description of your site">
Both may contain up to 1022 characters. If a keyword is used more than 7 times the keywords tag will be ignored altogether. Also, you can't put markup (other than entities) in the description or keywords list. Infoseek and Alta Vista are using this.
16. How do I redirect someone to my new page?
The most reliable way is to configure the server to send out a redirection instruction when the old URL is requested. Then the browser will automatically get the new URL. This is the fastest way to do this. You can of course also simply put up a small page with a text like "This page has moved to http://new.url/, please adjust your bookmarks".
A Netscape-only solution, which doesn't work on other browsers, and screws up the "back" button in Netscape, is
<META HTTP-EQUIV="Refresh" CONTENT="x; URL=new.URL">
which will load the new URL after x seconds. This should go in the HEAD of the document. But if you do this, also include a short text saying "Document moved to new URL so-and-so" for other browsers.
(The screwing-up bit refers to the fact that if you press "Back" after you have been redirected, you will be taken to the document with the META refresh. Then the refresh will be activated, and so you'll jump to the page you just tried to leave.)
17. How do I get a back button on my page?
In HTML, this is impossible. Going "back" means that you go to the previous page in your history. You might be able to create a link to the URL specified in the "HTTP_REFERER" environment variable in your document, but that only creates a link to a new location in your history. Even worse, the information in that variable can be plain wrong. Some browsers incorrectly send the variable when you use a bookmark or type in an URL manually, and some don't send that variable at all. Then you would end up with an empty link.
A JavaScript could use "history.back()" to do this, but this only works in Netscape 2.
18. How do I force a download?
You can't. When someone downloads a document, the server tells the browser what type of file it is. The browser then picks the appropriate helper application, or displays it himself. If the server doesn't know the file type, it tells the browser that the file is "text/plain", or just plain text. You will have to ask your server admin to configure this particular file with the MIME type you want.
"Forcing" a download is not what you are supposed to do. After all, what is more convenient than having the proper application started when I download a particular file? Browsing through a download directory can be quite a pain. And most browsers allow the user to download to disk if they want to.
If the file must be saved to disk, as there is absolutely NO other way to handle it, the MIME type should be "application/octet-stream".
19. Why is my binary file not downloaded, but shown on the screen?
Actually, the browser has downloaded the document, it is just treating it as a plain text file. This is because the server said it was a plain text file. To get the file in the helper application (or plug-in), you will have to configure the server to send out the right MIME type, and the browser to start the appropriate helper application for files with that MIME type.
20. How do I use an image instead of the standard submit button?
Use <INPUT NAME=foo TYPE=image SRC="http://url.to/image.gif"> instead of the normal submit tag. There is no way to do this for the reset button.
Note that some browsers will also send the x and y coordinates of the location where the user clicked on the image to the server. They are available as "foo.x=000&foo.y=000" in the CGI input.
21. How do I get a so-and-so character in my HTML?
HTML text is supposed to be written in the ISO Latin-1 character set. A complete overview of all the characters in this set is available from:
o http://www.w3.org/pub/WWW/MarkUp/html3/latin1.html
o http://ppewww.ph.gla.ac.uk/~flavell/iso8859/
o http://uts.cc.utexas.edu/~churchh/latin1.html
22. How do I get a counter?
Either ask your Webmaster for access to the log files, or for a server-side include which can do this, or use one of the freeware counters available at the CGI archives. There is no HTML tag to do this.
Counters are quite ball less, though. They can be set to any value the owner wants, so they don't give you any information. Because of the delay that often occurs when using an external counter, your visitors may get annoyed with the long loading time of your document. The server's log file provides a lot more reliable information for you, and you don't have to bother your readers with it.
23. How do I detect what browser is being used?
Many browsers identify themselves when they request a document. A CGI script will have this information available in the HTTP_USER_AGENT environment variable, and it can use that to send out a version of the document which is optimized for that browser.
Keep in mind not all browsers identify themselves correctly. Microsoft Internet Explorer, for example, claims to be "Mozilla 1.2" to get at Netscape enhanced documents.
And of course, if a cache proxy keeps the Netscape enhanced document, someone with an other browser will also get this document if he goes through the cache.
24. I want to get an audio file to play automatically when someone visits my site!
Bleh. What if I visit your site at 3am, and there's someone sleeping in the next room?
For Netscape, this is done using the <EMBED> tag. You can also do this with the Netscape <META> refresh tag, as described earlier. Just put the URL of the audio file in the CONTENT field.
There is also a MS Internet Explorer specific tag to do this: <BGSOUND SRC=URL> which plays the file specified in the SRC attribute automatically. You can add LOOP followed by a value or the keyword "INFINITE" to indicate how many times the sound should be played.
25. Should I put quotes around attribute values or not?
It depends. It is never wrong to use them, but you don't have to if the attribute value consists only of letters, digits, periods and/or hyphens. This is explained in the HTML 2.0 specs.
Oh, and keep in mind that if you use double quotes, you should escape any quotes inside the value with """ so you don't accidentally terminate the value prematurely.
26. Should I use lower case or upper case for tags?
Tags are case insensitive, so it doesn't matter. This is just a matter of style. Many people prefer upper case, as it makes the tags "stand out" better amongst the text.
28. My images/hyperlinks are coming out all wrong, or don't load! What's up?
Most likely you forgot to close a quote at the end of an HREF or SRC. Alternatively, perhaps you used a ">" character in an ALT text or somewhere else inside a tag. Although this is legal, several older browsers will think the tag ends there, so the rest is displayed as normal text.
This especially happens if you use comment tags to "comment out" text with HTML tags. Although the correct syntax is <!-- --> (without "--" occurring anywhere inside the comment), some browsers will think the comment ends at the first > they see.
29. How do I get a button which takes me to a new page?
This is done with a small form:
<FORM ACTION="http://url.you.want.to.go.to/" METHOD=GET>
<INPUT TYPE=submit VALUE="Text on button" NAME=foo>
</FORM>
If you want to line up buttons next to each other, you will have to put them in a one-row table, with each button in a separate cell.
Describe the role that XSL can play when dynamically generating HTML pages from a relational database.
Even if candidates have never participated in a project involving this type of architecture, they should recognize it as one of the common uses of XML. Querying a database and then formatting the result set so that it can be validated as an XML document allows developers to translate the data into an HTML table using XSLT rules. Consequently, the format of the resulting HTML table can be modified without changing the database query or application code since the document rendering logic is isolated to the XSLT rules.
Give a few examples of types of applications that can benefit from using XML.
There are literally thousands of applications that can benefit from XML technologies. The ball of this question is not to have the candidate rattle off a laundry list of projects that they have worked on, but, rather, to allow the candidate to explain the rationale for choosing XML by citing a few real world examples. For instance, one appropriate answer is that XML allows content management systems to store documents independently of their format, which thereby reduces data redundancy. Another answer relates to B2B exchanges or supply chain management systems. In these instances, XML provides a mechanism for multiple companies to exchange data according to an agreed upon set of rules. A third common response involves wireless applications that require WML to render data on hand held devices.
What is DOM and how does it relate to XML?
The Document Object Model (DOM) is an interface specification maintained by the W3C DOM Workgroup that defines an application independent mechanism to access, parse, or update XML data. In simple terms it is a hierarchical model that allows developers to manipulate XML documents easily Any developer that has worked extensively with XML should be able to discuss the concept and use of DOM objects freely. Additionally, it is not unreasonable to expect advanced candidates to thoroughly understand its internal workings and be able to explain how DOM differs from an event-based interface like SAX.
What is SOAP and how does it relate to XML?
The Simple Object Access Protocol (SOAP) uses XML to define a protocol for the exchange of information in distributed computing environments. SOAP consists of three components: an envelope, a set of encoding rules, and a convention for representing remote procedure calls. Unless experience with SOAP is a direct requirement for the open position, knowing the specifics of the protocol, or how it can be used in conjunction with HTTP, is not as important as identifying it as a natural application of XML.
Can you walk us through the steps necessary to parse XML documents?
Superficially, this is a fairly basic question. However, the ball is not to determine whether candidates understand the concept of a parser but rather have them walk through the process of parsing XML documents step-by-step. Determining whether a non-validating or validating parser is needed, choosing the appropriate parser, and handling errors are all important aspects to this process that should be included in the candidate's response.
Give some examples of XML DTDs or schemas that you have worked with.
Although XML does not require data to be validated against a DTD, many of the benefits of using the technology are derived from being able to validate XML documents against business or technical architecture rules. Polling for the list of DTDs that developers have worked with provides insight to their general exposure to the technology. The ideal candidate will have knowledge of several of the commonly used DTDs such as FpML, DocBook, HRML, and RDF, as well as experience designing a custom DTD for a particular project where no standard existed.
Using XSLT, how would you extract a specific attribute from an element in an XML document?
Successful candidates should recognize this as one of the most basic applications of XSLT. If they are not able to construct a reply similar to the example below, they should at least be able to identify the components necessary for this operation: xsl:template to match the appropriate XML element, xsl:value-of to select the attribute value, and the optional xsl:apply-templates to continue processing the document.
Extract Attributes from XML Data
Example 1.
Attribute Value:
<xsl:value-of select="@attribute"/>
When constructing an XML DTD, how do you create an external entity reference in an attribute value?
Every interview session should have at least one trick question. Although possible when using SGML, XML DTDs don't support defining external entity references in attribute values. It's more important for the candidate to respond to this question in a logical way than than the candidate know the somewhat obscure answer.
How would you build a search engine for large volumes of XML data?
The way candidates answer this question may provide insight into their view of XML data. For those who view XML primarily as a way to denote structure for text files, a common answer is to build a full-text search and handle the data similarly to the way Internet portals handle HTML pages. Others consider XML as a standard way of transferring structured data between disparate systems. These candidates often describe some scheme of importing XML into a relational or object database and relying on the database's engine for searching. Lastly, candidates that have worked with vendors specializing in this area often say that the best way the handle this situation is to use a third party software package optimized for XML data.
Here you will find java Interview Questions for job Interviews. Before appearing in a job interview it is very important to prepare well. The easy way to prepare for a job interview is to be pre prepared by going through important job interview questions. I created these java interview faqs database, by asking many people to contribute the questions they faced during job interviews and found the best way possible to present and answer these questions on this website.
This handy site has a complete set of Java job interview questions and provides a complete method for accurately assessing the technical abilities. Here we discuss each interview question in detail. The specialty of this site is Core Java Interview Questions.
FREE JAVA GUIDE JAVA INTERVIEW QUESTIONS PAGE. You may be an experienced Java developer with hands on experience or you may be a fresher. If you are looking for a job , you may need to refresh some of the basic java job interview questions or you may need to know what are the commonly asked questions to prepare yourself to for java technical interview. But you as a fresher you may need to know what can be the possible questions by going through the below frequently asked job interview questions. We put our maximum effort to make these answers error free.
1. What is the difference between private, protected, and public?
These keywords are for allowing privileges to components such as java methods and variables.
Public: accessible to all classes
Private: accessible only to the class to which they belong
Protected: accessible to the class to which they belong and any subclasses.
Access specifiers are keywords that determines the type of access to the member of a class. These are:
* Public
* Protected
* Private
* Defaults
2. What's the difference between an interface and an abstract class? Also discuss the similarities. (Very Important)
Abstract class is a class which contain one or more abstract methods, which has to be implemented by sub classes. Interface is a Java Object containing method declaration and doesn't contain implementation. The classes which have implementing the Interfaces must provide the method definition for all the methods
Abstract class is a Class prefix with a abstract keyword followed by Class definition. Interface is a Interface which starts with interface keyword.
Abstract class contains one or more abstract methods. where as Interface contains all abstract methods and final declarations
Abstract classes are useful in a situation that Some general methods should be implemented and specialization behavior should be implemented by child classes. Interfaces are useful in a situation that all properties should be implemented.
Differences are as follows:
* Interfaces provide a form of multiple inheritance. A class can extend only one other class.
* Interfaces are limited to public methods and constants with no implementation. Abstract classes can have a partial implementation, protected parts, static methods, etc.
* A Class may implement several interfaces. But in case of abstract class, a class may extend only one abstract class.
* Interfaces are slow as it requires extra indirection to to find corresponding method in in the actual class. Abstract classes are fast.
Similarities:
* Neither Abstract classes or Interface can be instantiated.
How to define an Abstract class?
A class containing abstract method is called Abstract class. An Abstract class can't be instantiated.
Example of Abstract class:
abstract class testAbstractClass {
protected String myString;
public String getMyString() {
return myString;
}
public abstract string anyAbstractFunction();
}
How to define an Interface?
Answer: In Java Interface defines the methods but does not implement them. Interface can include constants. A class that implements the interfaces is bound to implement all the methods defined in Interface.
Example of Interface:
public interface sampleInterface {
public void functionOne();
public long CONSTANT_ONE = 1000;
}
3. Question: How you can force the garbage collection?
Garbage collection automatic process and can't be forced. You could request it by calling System.gc(). JVM does not guarantee that GC will be started immediately.
Garbage collection is one of the most important feature of Java, Garbage collection is also called automatic memory management as JVM automatically removes the unused variables/objects (value is null) from the memory. User program can't directly free the object from memory, instead it is the job of the garbage collector to automatically free the objects that are no longer referenced by a program. Every class inherits finalize() method from java.lang.Object, the finalize() method is called by garbage collector when it determines no more references to the object exists. In Java, it is good idea to explicitly assign null into a variable when no more in use. I Java on calling System.gc() and Runtime.gc(), JVM tries to recycle the unused objects, but there is no guarantee when all the objects will garbage collected.
4. What's the difference between constructors and normal methods?
Constructors must have the same name as the class and can not return a value. They are only called once while regular methods could be called many times and it can return a value or can be void.
5. Can you call one constructor from another if a class has multiple constructors
Yes. Use this() to call a constructor from an other constructor.
6. Explain the usage of Java packages.
This is a way to organize files when a project consists of multiple modules. It also helps resolve naming conflicts when different packages have classes with the same names. Packages access level also allows you to protect data from being used by the non-authorized classes.
7. Explain in your own words the "bottom line" benefits of the use of an interface.
The interface makes it possible for a method in one class to invoke methods on objects of other classes, without the requirement to know the true class of those objects, provided that those objects are all instantiated from classes that implement one or more specified interfaces. In other words, objects of classes that implement specified interfaces can be passed into methods of other objects as the generic type Object, and the methods of the other objects can invoke methods on the incoming objects by first casting them as the interface type.
8. What are some advantages and disadvantages of Java Sockets?
Some advantages of Java Sockets:
Sockets are flexible and sufficient. Efficient socket based programming can be easily implemented for general communications. Sockets cause low network traffic. Unlike HTML forms and CGI scripts that generate and transfer whole web pages for each new request, Java applets can send only necessary updated information.
Some disadvantages of Java Sockets:
Security restrictions are sometimes overbearing because a Java applet running in a Web browser is only able to establish connections to the machine where it came from, and to nowhere else on the network Despite all of the useful and helpful Java features, Socket based communications allows only to send packets of raw data between applications. Both the client-side and server-side have to provide mechanisms to make the data useful in any way.
9. Explain the usage of the keyword transient?
Transient keyword indicates that the value of this member variable does not have to be serialized with the object. When the class will be de-serialized, this variable will be initialized with a default value of its data type (i.e. zero for integers).
10. What's the difference between the methods sleep() and wait()
The code sleep(1000); puts thread aside for exactly one second. The code wait(1000), causes a wait of up to one second. A thread could stop waiting earlier if it receives the notify() or notifyAll() call. The method wait() is defined in the class Object and the method sleep() is defined in the class Thread.
11. What would you use to compare two String variables - the operator == or the method equals()?
I'd use the method equals() to compare the values of the Strings and the == to check if two variables point at the same instance of a String object.
12. Why would you use a synchronized block vs. synchronized method?
Synchronized blocks place locks for shorter periods than synchronized methods.
13. What access level do you need to specify in the class declaration to ensure that only classes from the same directory can access it?
You do not need to specify any access level, and Java will use a default package access level.
14. Can an inner class declared inside of a method access local variables of this method?
It's possible if these variables are final.
15. What can go wrong if you replace && with & in the following code:
String a=null; if (a!=null && a.length()>10) {...}
A single ampersand here would lead to a NullPointerException.
16. What's the main difference between a Vector and an ArrayList?
Java Vector class is internally synchronized and ArrayList is not synchronized.
17. Describe the wrapper classes in Java.
Wrapper class is wrapper around a primitive data type. An instance of a wrapper class contains, or wraps, a primitive value of the corresponding type.
Following table lists the primitive types and the corresponding wrapper classes:
Primitive Wrapper
boolean - java.lang.Boolean
byte - java.lang.Byte
char - java.lang.Character
double - java.lang.Double
float - java.lang.Float
int - java.lang.Integer
long - java.lang.Long
short - java.lang.Short
void - java.lang.Void
18. How could Java classes direct program messages to the system console, but error messages, say to a file?
The class System has a variable out that represents the standard output, and the variable err that represents the standard error device. By default, they both point at the system console. This how the standard output could be re-directed:
Stream st = new Stream(new FileOutputStream("output.txt")); System.setErr(st); System.setOut(st);
19. How do you know if an explicit object casting is needed?
If you assign a superclass object to a variable of a subclass's data type, you need to do explicit casting. For example:
Object a; Customer b; b = (Customer) a;
20. When you assign a subclass to a variable having a supeclass type, the casting is performed automatically. Can you write a Java class that could be used both as an applet as well as an application?
Yes. Add a main() method to the applet.
21. If a class is located in a package, what do you need to change in the OS environment to be able to use it?
You need to add a directory or a jar file that contains the package directories to the CLASSPATH environment variable. Let's say a class Employee belongs to a package com.xyz.hr; and is located in the file c:devcomxyzhrEmployee.javIn this case, you'd need to add c:dev to the variable CLASSPATH. If this class contains the method main(), you could test it from a command prompt window as follows:
c:>java com.xyz.hr.Employee
22. What's the difference between J2SDK 1.5 and J2SDK 5.0?
There's no difference, Sun Microsystems just re-branded this version.
23. Does it matter in what order catch statements for FileNotFoundException and IOExceptipon are written?
Yes, it does. The FileNoFoundException is inherited from the IOException. Exception's subclasses have to be caught first.
24. Name the containers which uses Border Layout as their default layout?
Containers which uses Border Layout as their default are: window, Frame and Dialog classes.
25. You are planning to do an indexed search in a list of objects. Which of the two Java collections should you use:
ArrayList or LinkedList?
ArrayList
26. When should the method invokeLater()be used?
This method is used to ensure that Swing components are updated through the event-dispatching thread.
27. How can a subclass call a method or a constructor defined in a superclass?
Use the following syntax: super.myMethod(); To call a constructor of the superclass, just write super(); in the first line of the subclass's constructor.
28. What do you understand by Synchronization?
Synchronization is a process of controlling the access of shared resources by the multiple threads in such a manner that only one thread can access one resource at a time. In non synchronized multithreaded application, it is possible for one thread to modify a shared object while another thread is in the process of using or updating the object's value. Synchronization prevents such type of data corruption.
E.g. Synchronizing a function:
public synchronized void Method1 () {
// Appropriate method-related code.
}
E.g. Synchronizing a block of code inside a function:
public myFunction (){
synchronized (this) {
// Synchronized code here.
}
}
29. What's the difference between a queue and a stack?
Stacks works by last-in-first-out rule (LIFO), while queues use the FIFO rule
30. You can create an abstract class that contains only abstract methods. On the other hand, you can create an interface that declares the same methods. So can you use abstract classes instead of interfaces?
Sometimes. But your class may be a descendent of another class and in this case the interface is your only option.
31. If you're overriding the method equals() of an object, which other method you might also consider?
hashCode()
32. What is Collection API?
The Collection API is a set of classes and interfaces that support operation on collections of objects. These classes and interfaces are more flexible, more powerful, and more regular than the vectors, arrays, and hashtables if effectively replaces.
Example of classes: HashSet, HashMap, ArrayList, LinkedList, TreeSet and TreeMap.
Example of interfaces: Collection, Set, List and Map.
33. How would you make a copy of an entire Java object with its state?
Have this class implement Cloneable interface and call its method clone().
34. How can you minimize the need of garbage collection and make the memory use more effective?
Use object pooling and weak object references.
35. There are two classes: A and B. The class B need to inform a class A when some important event has happened. What Java technique would you use to implement it?
If these classes are threads I'd consider notify() or notifyAll(). For regular classes you can use the Observer interface.
36. Explain the Encapsulation principle.
Encapsulation is a process of binding or wrapping the data and the codes that operates on the data into a single entity. This keeps the data safe from outside interface and misuse. One way to think about encapsulation is as a protective wrapper that prevents code and data from being arbitrarily accessed by other code defined outside the wrapper.
37. Explain the Inheritance principle.
Inheritance is the process by which one object acquires the properties of another object.
38. Explain the Polymorphism principle.
The meaning of Polymorphism is something like one name many forms. Polymorphism enables one entity to be used as as general category for different types of actions. The specific action is determined by the exact nature of the situation. The concept of polymorphism can be explained as "one interface, multiple methods".
From a practical programming viewpoint, polymorphism exists in three distinct forms in Java:
* Method overloading
* Method overriding through inheritance
* Method overriding through the Java interface
39. Is Iterator a Class or Interface? What is its use?
Iterator is an interface which is used to step through the elements of a Collection.
40. Explain the user defined Exceptions?
User defined Exceptions are the separate Exception classes defined by the user for specific purposed. An user defined can created by simply sub-classing it to the Exception class. This allows custom exceptions to be generated (using throw) and caught in the same way as normal exceptions.
Example:
class myCustomException extends Exception {
/ The class simply has to exist to be an exception
}
41. What is OOPS?
OOP is the common abbreviation for Object-Oriented Programming.
There are three main principals of oops which are called Polymorphism, Inheritance and Encapsulation.
39. Read the following program:
public class test {
public static void main(String [] args) {
int x = 3;
int y = 1;
if (x = y)
System.out.println("Not equal");
else
System.out.println("Equal");
}
}
What is the result?
The output is “Equal”
B. The output in “Not Equal”
C. An error at " if (x = y)" causes compilation to fall.
D. The program executes but no output is show on console.
Answer: C
Answer: Transient variable can't be serialize. For example if a variable is declared as transient in a Serializable class and the class is written to an ObjectStream, the value of the variable can't be written to the stream instead when the class is retrieved from the ObjectStream the value of the variable becomes null.
Difference between Store Procedure and Trigger
Answer : 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.
Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.
These 109 questions include both traditional and behavioral interview questions. For more information about types of interview questions, go to our Interviewing Resources section of Quintessential Careers.
How would you describe yourself?
What specific goals, including those related to your occupation, have you established for your life?
How has your college experience prepared you for a business career?
Please describe the ideal job for you following graduation.
What influenced you to choose this career?
At what point did you choose this career?
What specific goals have you established for your career?
What will it take to attain your goals, and what steps have you taken toward attaining them?
What do you think it takes to be successful in this career?
How do you determine or evaluate success? Give me an example of one of your successful accomplishments.
Do you have the qualifications and personal characteristics necessary for success in your chosen career?
What has been your most rewarding accomplishment?
If you could do so, how would you plan your college career differently?
Are you more energized by working with data or by collaborating with other individuals?
How would you describe yourself in terms of your ability to work as a member of a team?
What motivates you to put forth your greatest effort?
Given the investment our company will make in hiring and training you, can you give us a reason to hire you?
Would you describe yourself as goal-driven?
Describe what you've accomplished toward reaching a recent goal for yourself.
What short-term goals and objectives have you established for yourself?
Can you describe your long-range goals and objectives?
What do you expect to be doing in five years?
What do you see yourself doing in ten years?
How would you evaluate your ability to deal with conflict?
Have you ever had difficulty with a supervisor or instructor? How did you resolve the conflict?
Tell me about a major problem you recently handled. Were you successful in resolving it?
Would you say that you can easily deal with high-pressure situations?
What quality or attribute do you feel will most contribute to your career success?
What personal weakness has caused you the greatest difficulty in school or on the job?
What were your reasons for selecting your college or university?
If you could change or improve anything about your college, what would it be?
How will the academic program and coursework you've taken benefit your career?
Which college classes or subjects did you like best? Why?
Are you the type of student for whom conducting independent research has been a positive experience?
Describe the type of professor that has created the most beneficial learning experience for you.
Do you think that your grades are a indication of your academic achievement?
What plans do you have for continued study? An advanced degree?
Before you can make a productive contribution to the company, what degree of training do you feel you will require?
Describe the characteristics of a successful manager.
Why did you decide to seek a position in this field?
Tell me what you know about our company.
Why did you decide to seek a position in this company?
Do you have a geographic preference?
Why do you think you might like to live in the community in which our company is located?
Would it be a problem for you to relocate?
To what extent would you be willing to travel for the job?
Which is more important to you, the job itself or your salary?
What level of compensation would it take to make you happy?
Tell me about the salary range you're seeking.
Describe a situation in which you were able to use persuasion to successfully convince someone to see things your way?
Describe an instance when you had to think on your feet to extricate yourself from a difficult situation.
Give me a specific example of a time when you used good judgment and logic in solving a problem.
By providing examples, convince me that you can adapt to a wide variety of people, situations and environments.
Describe a time when you were faced with problems or stresses that tested your coping skills.
Give an example of a time in which you had to be relatively quick in coming to a decision.
Describe a time when you had to use your written communication skills to get an important point across
Give me a specific occasion in which you conformed to a policy with which you did not agree.
Give me an example of an important goal which you had set in the past and tell me about your success in reaching it.
Describe the most significant or creative presentation that you have had to complete.
Tell me about a time when you had to go above and beyond the call of duty in order to get a job done.
Give me an example of a time when you were able to successfully communicate with another person even when that individual may not have personally liked you (or vice versa).
Sometimes it's easy to get in "over your head." Describe a situation where you had to request help or assistance on a project or assignment.
Give an example of how you applied knowledge from previous coursework to a project in another class.
Describe a situation where others you were working with on a project disagreed with your ideas. What did you do?
Describe a situation in which you found that your results were not up to your professor's or supervisor's expectations. What happened? What action did you take?
Tell of a time when you worked with a colleague who was not completing his or her share of the work. Who, if anyone, did you tell or talk to about it? Did the manager take any steps to correct your colleague? Did you agree or disagree with the manager's actions?
Describe a situation in which you had to arrive at a compromise or guide others to a compromise.
What steps do you follow to study a problem before making a decision.
We can sometimes identify a small problem and fix it before it becomes a major problem. Give an example(s) of how you have done this.
In a supervisory or group leader role, have you ever had to discipline or counsel an employee or group member? What was the nature of the discipline? What steps did you take? How did that make you feel? How did you prepare yourself?
Recall a time from your work experience when your manager or supervisor was unavailable and a problem arose. What was the nature of the problem? How did you handle that situation? How did that make you feel?
Recall a time when you were assigned what you considered to be a complex project. Specifically, what steps did you take to prepare for and finish the project? Were you happy with the outcome? What one step would you have done differently if given the chance?
What was the most complex assignment you have had? What was your role?
How was your transition from high school to college? Did you face any particular problems?
Tell of some situations in which you have had to adjust quickly to changes over which you had no control. What was the impact of the change on you?
Compare and contrast the times when you did work which was above the standard with times your work was below the standard.
Describe some times when you were not very satisfied or pleased with your performance. What did you do about it?
What are your standards of success in school? What have you done to meet these standards?
How have you differed from your professors in evaluating your performance? How did you handle the situation?
Give examples of your experiences at school or in a job that were satisfying. Give examples of your experiences that were dissatisfying.
What kind of supervisor do you work best for? Provide examples.
Describe some projects or ideas (not necessarily your own) that were implemented, or carried out successfully primarily because of your efforts.
Describe a situation that required a number of things to be done at the same time. How did you handle it? What was the result?
Have you found any ways to make school or a job easier or more rewarding or to make yourself more effective?
How do you determine priorities in scheduling your time? Give examples.
Tell of a time when your active listening skills really paid off for you - maybe a time when other people missed the key idea being expressed.
What has been your experience in giving presentations? What has been your most successful experience in speech making?
Tell of the most difficult customer service experience that you have ever had to handle -- perhaps an angry or irate customer. Be specific and tell what you did and what was the outcome.
Give an example of when you had to work with someone who was difficult to get along with. Why was this person difficult? How did you handle that person?
Describe a situation where you found yourself dealing with someone who didn't like you. How did you handle it?
Give me a specific example of something you did that helped build enthusiasm in others.
Tell me about a difficult situation when it was desirable for you to keep a positive attitude. What did you do?
Give me an example of a time you had to make an important decision. How did you make the decision? How does it affect you today?
Give me an example of a time you had to persuade other people to take action. Were you successful?
Tell me about a time when you had to deal with a difficult person. How did you handle the situation?
Tell me about a time you had to handle multiple responsibilities. How did you organize the work you needed to do?
Tell me about a time when you had to make a decision, but didn't have all the information you needed.
What suggestions do you have for our organization?
What is the most significant contribution you made to the company during a past job or internship?
What is the biggest mistake you've made?
Describe a situation in which you had to use reference materials to write a research paper. What was the topic? What journals did you read?
Give me a specific example of a time when a co-worker or classmate criticized your work in front of others. How did you respond? How has that event shaped the way you communicate with others?
Give me a specific example of a time when you sold your supervisor or professor on an idea or concept. How did you proceed? What was the result?
Describe the system you use for keeping track of multiple projects. How do you track your progress so that you can meet deadlines? How do you stay focused?
Tell me about a time when you came up with an innovative solution to a challenge your company/class/organization was facing. What was the challenge? What role did others play?
Describe a specific problem you solved for your employer or professor. How did you approach the problem? What role did others play? What was the outcome?
Describe a time when you got co-workers or classmates who dislike each other to work together. How did you accomplish this? What was the outcome?
Tell me about a time when you failed to meet a deadline. What things did you fail to do? What were the repercussions? What did you learn?
Describe a time when you put your needs aside to help a co-worker or classmate understand a task. How did you assist him or her? What was the result?
QUINTESSENTIAL JOB INTERVIEWING BOOK:
The Quintessential Guide to Job Interview Preparation, by Katharine Hansen, Ph.D., and Randall Hansen, Ph.D. (Quintessential Careers Press). A free book that covers all the essential information job-seekers need to know about job interviewing. This comprehensive guide to succeeding in job interviews shows you exactly what to expect -- and what's expected of you every step of the way. You'll learn the secrets of interview preparation that will enable you to wow your interviewer... and much more. A must read for all job-seekers.
DB Interview Questions
Question: What is SQL?
Question: What is SELECT statement?
Question: How can you compare a part of the name rather than the entire name?
Question:
What is the INSERT statement?
Question: How do you delete a record from a database?
Question: How could I get distinct entries from a table?
Question: How to get the results of a Query sorted in any order?
Question: How can I find the total number of records in a table?
Question: What is GROUP BY?
Question: What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table?
Question: What are the Large object types suported by Oracle?
Question: Difference between a "where" clause and a "having" clause ?
Question: What's the difference between a primary key and a unique key?
Question: What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Question: What are triggers? How to invoke a trigger on demand?
Question: What is a join and explain different types of joins.
Question: What is a self join?
Q:
What is SQL?
A: SQL stands for 'Structured Query Language'.
TOP
Q:
What is SELECT statement?
A: The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query.
TOP
Q:
How can you compare a part of the name rather than the entire name?
A: SELECT * FROM people WHERE empname LIKE '«%'
Would return a recordset with records consisting empname the sequence 'ab' in empname .
TOP
Q:
What is the INSERT statement?
A: The INSERT statement lets you insert information into a database.
TOP
Q:
How do you delete a record from a database?
A: Use the DELETE statement to remove records or any particular column values from a database.
TOP
Q:
How could I get distinct entries from a table?
A: The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table would of course depend on the various conditions that are specified in the SQL query. Example
SELECT DISTINCT empname FROM emptable
TOP
Q:
How to get the results of a Query sorted in any order?
A: You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.
SELECT empname, age, city FROM emptable ORDER BY empname
TOP
Q:
How can I find the total number of records in a table?
A:
You could use the COUNT keyword , example
SELECT COUNT(*) FROM emp WHERE age>40
TOP
Q:
What is GROUP BY?
A: The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.
TOP
Q:
What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
A: Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes
Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete
Delete : (Data alone deleted), Doesn’t perform automatic commit
TOP
Q:
What are the Large object types suported by Oracle?
A: Blob and Clob.
TOP
Q:
Difference between a "where" clause and a "having" clause.
A: Having clause is used only with group functions whereas Where is not used with.
TOP
Q:
What's the difference between a primary key and a unique key?
A:
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
TOP
Q:
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
A:
Cursors allow row-by-row prcessing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors.
TOP
Q:
What are triggers? How to invoke a trigger on demand?
A: Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Q:
What is a join and explain different types of joins.
A:
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
TOP
Q:
What is a self join?
A: Self join is just like any other join, except that two instances of the same table will be joined in the query.
Interview questions for Oracle database administrator
Differentiate between TRUNCATE and DELETE
What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
Can you use a commit statement within a database trigger?
What is an UTL_FILE.What are different procedures and functions associated with it?
Difference between database triggers and form triggers?
What is OCI. What are its uses?
What are ORACLE PRECOMPILERS?
What is syntax for dropping a procedure and a function? Are these operations possible?
Can a function take OUT parameters. If not why?
Can the default values be assigned to actual parameters?
What is difference between a formal and an actual parameter?
What are different modes of parameters used in functions and procedures?
Difference between procedure and function.
Can cursor variables be stored in PL/SQL tables.If yes how. If not why?
How do you pass cursor variables in PL/SQL?
How do you open and close a cursor variable.Why it is required?
What should be the return type for a cursor variable.Can we use a scalar data type as return type?
What is use of a cursor variable? How it is defined?
What WHERE CURRENT OF clause does in a cursor?
Difference between NO DATA FOUND and %NOTFOUND
What is a cursor for loop?
What are cursor attributes?
Difference between an implicit & an explicit cursor.
What is a cursor?
What is the purpose of a cluster?
How do you find the numbert of rows in a Table ?
Display the number value in Words?
What is a pseudo column. Give some examples?
How you will avoid your query from using indexes?
What is a OUTER JOIN?
Which is more faster - IN or EXISTS?
When do you use WHERE clause and when do you use HAVING clause?
There is a % sign in one field of a column. What will be the query to find it?
What is difference between SUBSTR and INSTR?
Which datatype is used for storing graphics and images?
What is difference between SQL and SQL*PLUS?
What is difference between UNIQUE and PRIMARY KEY constraints?
What is difference between Rename and Alias?
What are various joins used while writing SUBQUERIES?
^Back to Top
MS SQL Server interview questions
This one always gets asked. For a while the database interview questions were limited to Oracle and generic database design questions. This is a set of more than a hundred Microsoft SQL Server interview questions. Some questions are open-ended, and some do not have answers.
What is normalization? - Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.
What is a Stored Procedure? - Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.
Can you give an example of Stored Procedure? - sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.
What is a trigger? - Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.
What is a view? - If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.
What is an Index? - When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.
What are the types of indexes available with SQL Server? - There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.
What is the basic difference between clustered and a non-clustered index? - The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
What are cursors? - Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.
When do we use the UPDATE_STATISTICS command? - This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
Which TCP/IP port does SQL Server run on? - SQL Server runs on port 1433 but we can also change it for better security.
From where can you change the default port? - From the Network Utility TCP/IP properties –> Port number.both on client and the server.
Can you tell me the difference between DELETE & TRUNCATE commands? - Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
Can we use Truncate command on a table which is referenced by FOREIGN KEY? - No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
What is the use of DBCC commands? - DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
Can you give me some DBCC command options?(Database consistency check) - DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
What command do we use to rename a db? - sp_renamedb ‘oldname’ , ‘newname’
Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? - In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? - Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
What do you mean by COLLATION? - Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.
What is a Join in SQL Server? - Join actually puts data from two or more tables into a single result set.
Can you explain the types of Joins that we can have with Sql Server? - There are three types of joins: Inner Join, Outer Join, Cross Join
When do you use SQL Profiler? - SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..
What is a Linked Server? - Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.
Can you link only other SQL Servers or any database servers such as Oracle? - We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.
Which stored procedure will you be running to add a linked server? - sp_addlinkedserver, sp_addlinkedsrvlogin
What are the OS services that the SQL Server installation adds? - MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
Can you explain the role of each service? - SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintanance, Backups DTC - Is for linking and connecting to other SQL Servers
How do you troubleshoot SQL Server if its running very slow? - First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes
Lets say due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot? - First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection ——Makepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues.
What are the authentication modes in SQL Server? - Windows mode and mixed mode (SQL & Windows).
Where do you think the users names and passwords will be stored in sql server? - They get stored in master db in the sysxlogins table.
What is log shipping? Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.
Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow? - For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER .m which will basically bring it into the maintenance mode after which we can restore the master db.
Let us say master db itself has no backup. Now you have to rebuild the db so what kind of action do you take? - (I am not sure- but I think we have a command to do it).
What is BCP? When do we use it? - BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.
What should we do to copy the tables, schema and views from one SQL Server to another? - We have to write some DTS packages for it.
What are the different types of joins and what dies each do?
What are the four main query statements?
What is a sub-query? When would you use one?
What is a NOLOCK?
What are three SQL keywords used to change or set someone’s permissions?
What is the difference between HAVING clause and the WHERE clause?
What is referential integrity? What are the advantages of it?
What is database normalization?
Which command using Query Analyzer will give you the version of SQL server and operating system?
Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table?
What is the purpose of using COLLATE in a query?
What is a trigger?
What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”
What is an execution plan? When would you use it? How would you view the execution plan?
What is the STUFF function and how does it differ from the REPLACE function?
What does it mean to have quoted_identifier on? What are the implications of having it off?
What are the different types of replication? How are they used?
What is the difference between a local and a global variable?
What is the difference between a Local temporary table and a Global temporary table? How is each one used?
What are cursors? Name four types of cursors and when each one would be applied?
What is the purpose of UPDATE STATISTICS?
How do you use DBCC statements to monitor various aspects of a SQL server installation?
How do you load large data to the SQL server database?
How do you check the performance of a query and how do you optimize it?
How do SQL server 2000 and XML linked? Can XML be used to access data?
What is SQL server agent?
What is referential integrity and how is it achieved?
What is indexing?
What is normalization and what are the different forms of normalizations?
Difference between server.transfer and server.execute method?
What id de-normalization and when do you do it?
What is better - 2nd Normal form or 3rd normal form? Why?
Can we rewrite subqueries into simple select statements or with joins? Example?
What is a function? Give some example?
What is a stored procedure?
Difference between Function and Procedure-in general?
Difference between Function and Stored Procedure?
Can a stored procedure call another stored procedure. If yes what level and can it be controlled?
Can a stored procedure call itself(recursive). If yes what level and can it be controlled.?
How do you find the number of rows in a table?
Difference between Cluster and Non-cluster index?
What is a table called, if it does not have neither Cluster nor Non-cluster Index?
Explain DBMS, RDBMS?
Explain basic SQL queries with SELECT from where Order By, Group By-Having?
Explain the basic concepts of SQL server architecture?
Explain couple pf features of SQL server
Scalability, Availability, Integration with internet, etc.)?
Explain fundamentals of Data ware housing & OLAP?
Explain the new features of SQL server 2000?
How do we upgrade from SQL Server 6.5 to 7.0 and 7.0 to 2000?
What is data integrity? Explain constraints?
Explain some DBCC commands?
Explain sp_configure commands, set commands?
Explain what are db_options used for?
What is the basic functions for master, msdb, tempdb databases?
What is a job?
What are tasks?
What are primary keys and foreign keys?
How would you Update the rows which are divisible by 10, given a set of numbers in column?
If a stored procedure is taking a table data type, how it looks?
How m-m relationships are implemented?
How do you know which index a table is using?
How will oyu test the stored procedure taking two parameters namely first name and last name returning full name?
How do you find the error, how can you know the number of rows effected by last SQL statement?
How can you get @@error and @@rowcount at the same time?
What are sub-queries? Give example? In which case sub-queries are not feasible?
What are the type of joins? When do we use Outer and Self joins?
Which virtual table does a trigger use?
How do you measure the performance of a stored procedure?
Questions regarding Raiseerror?
Questions on identity?
If there is failure during updation of certain rows, what will be the state?
^Back to Top
Read more at TechInterviews.com
JDBC and JSP interview questions
What is the query used to display all tables names in SQL Server (Query analyzer)?
select * from information_schema.tables
How many types of JDBC Drivers are present and what are they?- There are 4 types of JDBC Drivers
JDBC-ODBC Bridge Driver
Native API Partly Java Driver
Network protocol Driver
JDBC Net pure Java Driver
Can we implement an interface in a JSP?- No
What is the difference between ServletContext and PageContext?- ServletContext: Gives the information about the container. PageContext: Gives the information about the Request
What is the difference in using request.getRequestDispatcher() and context.getRequestDispatcher()?- request.getRequestDispatcher(path): In order to create it we need to give the relative path of the resource, context.getRequestDispatcher(path): In order to create it we need to give the absolute path of the resource.
How to pass information from JSP to included JSP?- Using <%jsp:param> tag.
What is the difference between directive include and jsp include?- <%@ include>: Used to include static resources during translation time. JSP include: Used to include dynamic content or static content during runtime.
What is the difference between RequestDispatcher and sendRedirect?- RequestDispatcher: server-side redirect with request and response objects. sendRedirect : Client-side redirect with new request and response objects.
How does JSP handle runtime exceptions?- Using errorPage attribute of page directive and also we need to specify isErrorPage=true if the current page is intended to URL redirecting of a JSP.
How do you delete a Cookie within a JSP?
Cookie mycook = new Cookie("name","value");
response.addCookie(mycook);
Cookie killmycook = new Cookie("mycook","value");
killmycook.setMaxAge(0);
killmycook.setPath("/");
killmycook.addCookie(killmycook);
How do I mix JSP and SSI #include?- If you’re just including raw HTML, use the #include directive as usual inside your .jsp file.
<!--#include file="data.inc"-->
But it’s a little trickier if you want the server to evaluate any JSP code that’s inside the included file. If your data.inc file contains jsp code you will have to use
<%@ vinclude="data.inc" %>
The <!–#include file="data.inc"–> is used for including non-JSP files.
I made my class Cloneable but I still get Can’t access protected method clone. Why?- Some of the Java books imply that all you have to do in order to have your class support clone() is implement the Cloneable interface. Not so. Perhaps that was the intent at some point, but that’s not the way it works currently. As it stands, you have to implement your own public clone() method, even if it doesn’t do anything special and just calls super.clone().
Why is XML such an important development?- It removes two constraints which were holding back Web developments: dependence on a single, inflexible document type (HTML) which was being much abused for tasks it was never designed for; the complexity of full SGML, whose syntax allows many powerful but hard-to-program options. XML allows the flexible development of user-defined document types. It provides a robust, non-proprietary, persistent, and verifiable file format for the storage and transmission of text and data both on and off the Web; and it removes the more complex options of SGML, making it easier to program for.
What is the fastest type of JDBC driver?- JDBC driver performance will depend on a number of issues:
the quality of the driver code,
the size of the driver code,
the database server and its load,
network topology,
the number of times your request is translated to a different API.
In general, all things being equal, you can assume that the more your request and response change hands, the slower it will be. This means that Type 1 and Type 3 drivers will be slower than Type 2 drivers (the database calls are make at least three translations versus two), and Type 4 drivers are the fastest (only one translation).
How do I find whether a parameter exists in the request object?
boolean hasFoo = !(request.getParameter("foo") == null
|| request.getParameter("foo").equals(""));
or
boolean hasParameter =
request.getParameterMap().contains(theParameter); //(which works in Servlet 2.3+)
How can I send user authentication information while makingURLConnection?- You’ll want to use HttpURLConnection.setRequestProperty and set all the appropriate headers to HTTP authorization.
^Back to Top
SQL Server interview questions
How do you read transaction logs?
How do you reset or reseed the IDENTITY column?
How do you persist objects, permissions in tempdb?
How do you simulate a deadlock for testing purposes?
How do you rename an SQL Server computer?
How do you run jobs from T-SQL?
How do you restore single tables from backup in SQL Server 7.0/2000? In SQL Server 6.5?
Where to get the latest MDAC from?
I forgot/lost the sa password. What do I do?
I have only the .mdf file backup and no SQL Server database backups. Can I get my database back into SQL Server?
How do you add a new column at a specific position (say at the beginning of the table or after the second column) using ALTER TABLE command?
How do you change or alter a user defined data type?
How do you rename an SQL Server 2000 instance?
How do you capture/redirect detailed deadlock information into the error logs?
How do you remotely administer SQL Server?
What are the effects of switching SQL Server from ‘Mixed mode’ to ‘Windows only’ authentication mode? What are the steps required, to not break existing applications?
Is there a command to list all the tables and their associated filegroups?
How do you ship the stored procedures, user defined functions (UDFs), triggers, views of my application, in an encrypted form to my clients/customers? How do you protect intellectual property?
How do you archive data from my tables? Is there a built-in command or tool for this?
How do you troubleshoot ODBC timeout expired errors experienced by applications accessing SQL Server databases?
How do you restart SQL Server service automatically at regular intervals?
What is the T-SQL equivalent of IIF (immediate if/ternary operator) function of other programming languages?
How do you programmatically find out when the SQL Server service started?
How do you get rid of the time part from the date returned by GETDATE function?
How do you upload images or binary files into SQL Server tables?
How do you run an SQL script file that is located on the disk, using T-SQL?
How do you get the complete error message from T-SQL while error handling?
How do you get the first day of the week, last day of the week and last day of the month using T-SQL date functions?
How do you pass a table name, column name etc. to the stored procedure so that I can dynamically select from a table?
Error inside a stored procedure is not being raised to my front-end applications using ADO. But I get the error when I run the procedure from Query Analyzer.
How do you suppress error messages in stored procedures/triggers etc. using T-SQL?
How do you save the output of a query/stored procedure to a text file?
How do you join tables from different databases?
How do you join tables from different servers?
How do you convert timestamp data to date data (datetime datatype)?
Can I invoke/instantiate COM objects from within stored procedures or triggers using T-SQL?
Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or How do you generate output with row number in SQL Server?
How do you specify a network library like TCP/IP using ADO connect string?
How do you generate scripts for repetitive tasks like truncating all the tables in a database, changing owner of all the database objects, disabling constraints on all tables etc?
Is there a way to find out when a stored procedure was last updated?
How do you find out all the IDENTITY columns of all the tables in a given database?
How do you search the code of stored procedures?
How do you retrieve the generated GUID value of a newly inserted row? Is there an @@GUID, just like @@IDENTITY?
1. Explain the difference between a database administrator and a data administrator.
Database Administrator :- A person (or group of people) responsible for the maintenance and performance of a database and responsible for the planning, implementation, configuration, and administration of relational database management systems.
Data Administrator :- The individual or organization responsible for the specification, acquisition, and maintenance of data management software and the design, validation, and security of files or databases. The DA is in charge of the data dictionary and data model.
2. Explain the difference between an explicit and an implicit lock.
Explicit Lock :- Lock is explicitly requested for a record or table.
Implicit Lock :- Lock is implied but is not acquired
3. What is lock granularity?
There are many locks available for the database system to have like
Intent Shared, Shared, Intent exclusive, exclusive and Shared Intent exclusive.
Locking granularity refers to the size and hence the number of locks used to ensure the consistency of a database during multiple concurrent updates.
4. In general, how should the boundaries of a transaction be defined?
A transaction ensures that one or more operations execute as an atomic unit of work. If one of the operations within a transaction fails, then all of them are rolled-back so that the application is returned to its prior state. The boundaries that define a group of operations done within a single transaction.
5. Explain the meaning of the expression ACID transaction.
ACID means Atomic, Consistency, Isolation, Durability, so when any transaction happen it should be Atomic that is it should either be complete or fully incomplete. There should not be anything like Semi complete. The Database State should remain consistent after the completion of the transaction. If there are more than one Transaction then the transaction should be scheduled in such a fashion that they remain in Isolation of one another.Durability means that Once a transaction commits, its effects will persist even if there are system failures.
6. Explain the necessity of defining processing rights and responsibilities. How are such responsibilities enforced?
One of the reason to define rights is the security in the database system. If any user is allowed to define the data or alter the data then the database would just be of no use and so processing rights and responsibilities are clearly defined in any database system. The resposibilities are enforced using the table space provided by the database system.
7. Describe the advantages and disadvantages of DBMS-provided and application-provided security.
DBMS provided security :- Any database system requires you to login and then process the data depending on the rights given by the DBA to the user who has logged in. The advatage of such a system is securing the data and providing the user and the DBA the secured platform. Any user who logs in cannot do whatever he want but his role can be defined very easily. There is no major disadvantage about the DBMS provided security apart from overhead of storing the rights and priviledges about the users.
Application-provided security :- It is much similar to the DBMS provided security but the only difference is that its the duty of the programmer creating the application to provide all the seurities so that the data is not mishandled.
8. Explain how a database could be recovered via reprocessing. Why is this generally not feasible?
If we reprocess the transaction then the database can be made to come to a state where the database is consistent and so reprocessing the log can recover the database. Reprocessing is not very feasible for a very simple reason that its very costly from time point of view and requires lots of rework and many transaction are even rollback giving more and more rework.
9. Define rollback and roll forward.
Rollback :- Undoing the changes made by a transaction before it commits or to cancel any changes to a database made during the current transaction
RollForward :- Re-doing the changes made by a transaction after it commits or to overwrite the chnaged calue again to ensure consistency
10. Why is it important to write to the log before changing the database values?
The most important objective to write the log before the database is changed is if there is any need to rollback or rollforward any transaction then if the log are not present then the rollback rollforward cannot be done accurately.
Question write the Sql query for creating database backup?
select table_name,tablespace_name from user_tables;
or
create table backup1 as select * from user_source;
or
if you need to take tha backup of the database on ur hard
disk then u can use the following query:
exp userid=system/manager@orcl file=locationabc.dmp full=y
log= abc
Question What is a transaction?
#1. set of related dml operations to perform a task are called
a transaction.
# 2. It starts with a first executable statement and ends when
commit or rollback statement occurs.