SQL interview questions and answers

18 Comments on SQL interview questions and answers

1. db don
   Posted 5/16/2005 at 7:55 pm | Permalink

   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

2. Ajay Kumar Peddireddy
   Posted 2/6/2006 at 7:52 am | Permalink

   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.

3. Mrs Bisht
   Posted 10/17/2006 at 11:11 am | Permalink

   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.

4. Acrien
   Posted 10/28/2006 at 7:21 pm | Permalink

   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.

5. pravasis
   Posted 3/6/2007 at 4:32 am | Permalink

   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

6. Thiyagu
   Posted 3/24/2007 at 10:55 am | Permalink

   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

7. johnny
   Posted 6/12/2007 at 8:30 am | Permalink

   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;

8. Srikanth
   Posted 7/9/2007 at 7:47 am | Permalink

   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

9. gaurav gaur
   Posted 10/22/2007 at 11:22 am | Permalink

   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

10. gaurav gaur
    Posted 10/22/2007 at 11:26 am | Permalink

    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

11. masti
    Posted 12/2/2007 at 9:32 pm | Permalink

    .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

12. preppy
    Posted 1/2/2008 at 2:20 am | Permalink

    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?

13. RaiS
    Posted 1/16/2008 at 6:37 am | Permalink

    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

14. Sireesha
    Posted 3/6/2008 at 1:00 am | Permalink

    1. option 4 is an incorrect option.
    From Keyword is missing in the querry.

15. Rambabu Makireddi
    Posted 4/23/2008 at 3:24 am | Permalink

    1. Ans 4: since query doesn’t have from clause

16. Rambabu Makireddi
    Posted 4/23/2008 at 5:36 am | Permalink

    2. Ans:2. You can give alias using AS keyword.

    3.Ans:3

    4.Ans:4

    5.Ans:1. We use the DEFINE_EDITOR = command to define our favourite editor.

    6.Ans:2

    7.Ans:2, 1.first of all, INVALID string should be in single colon 2. Outcome of NVL function should be number

    8.Ans:3

    9.Ans:2

    10.Ans:3

    11.Ans:1

    12.Ans:3, if you want all records in A than B then use A=B(+)

    13.Ans:3

    14.Ans:1

    15.Ans: 1, 2 and 4, there is no BOOLEAN data type as far as Tables are concerned. But, procedures and functions can have.

    16.Ans:4

    17.Ans:1, the Foreign Key in detail table and its Primary or Unique Key in master table should have same datatype.

    18.Ans:2

    19.Ans:1

    20. The create statement is wrong. CHECK doesn’t allow the SELECT statement in it.

    21.Ans:1

    22.Ans: at line 1. Strings in Decode should be in single quotes.

    23.Ans: 2 we can use Case also

    24.Ans:2 and 3

    25.Ans:5

    16.

17. Satyam
    Posted 5/15/2008 at 9:48 pm | Permalink

    Answer:
    1. 4.
    2. 2
    3. 3
    4. 4
    5. 1
    6. 2
    7. 2
    8. 3
    9. 2
    10. 4
    11. 1
    12. 4
    13. 3
    14. 1
    15. 1,2,4
    16. 4
    17. 1
    18. 2
    19. 1
    20. 3
    21. 1
    22. 3
    23. 2
    24. 2,3
    25. It will display the employee name starting by any character but second character must be ‘a’ and after that combination of many characters.
    Ex: satyam

18. vishal
    Posted 9/25/2008 at 5:52 am | Permalink

    /*
    AUTHOR : VISHAL MEHRA
    DATE: 25/09/08

    */

    /******TO GET THE DATE’S DAY OF THE PERTICULAR DAY**************************/

    CREATE FUNCTION G4_WEEK_DAY_IN_MONTH_FUNC (@DATE DATETIME)
    RETURNS @TABLE TABLE
    (

    DATE DATETIME ,
    WEEK_DAY VARCHAR(30) ,
    WEEK_DAY_NO INT
    )

    AS

    BEGIN

    DECLARE @SRAT_DATE TABLE
    (

    START_DATE DATETIME,
    LAST_DATE DATETIME
    )

    DECLARE @WEEK_DAY_TABLE TABLE
    (
    DATE DATETIME,
    WEEK_DAY VARCHAR(30),
    WEEK_DAY_NO INT
    )

    INSERT INTO @SRAT_DATE
    SELECT CONVERT(DATETIME,’01/’+CONVERT(VARCHAR(12),MONTH(@DATE))+’/'+ CONVERT(VARCHAR(12),YEAR(@DATE)),103),
    LAST_DATE=DBO.G4_LAST_DATE_OF_MONTH_FUNC(@DATE)

    DECLARE @FIRST_DATE DATETIME,
    @LAST_DATE DATETIME

    SELECT @FIRST_DATE=START_DATE,
    @LAST_DATE=LAST_DATE
    FROM @SRAT_DATE

    WHILE @FIRST_DATE<=@LAST_DATE

    BEGIN

    INSERT INTO @WEEK_DAY_TABLE
    SELECT @FIRST_DATE, CASE DATEPART(DW,@FIRST_DATE)
    WHEN 1 THEN ‘SUNDAY’
    WHEN 2 THEN ‘MONDAY’
    WHEN 3 THEN ‘TUESDAY’
    WHEN 4 THEN ‘WEDNESDAY’
    WHEN 5 THEN ‘THURDSDAY’
    WHEN 6 THEN ‘FRIDAY’
    WHEN 7 THEN ‘SATURDAY’
    END, DATEPART(DW,@FIRST_DATE)

    SELECT @FIRST_DATE=@FIRST_DATE+1
    END

    INSERT INTO @TABLE

    SELECT *
    FROM @WEEK_DAY_TABLE

    RETURN

    END

    /*********HOW TO CALL***********/

    SELECT * FROM G4_WEEK_DAY_IN_MONTH_FUNC(’2008-01-01′)

    –OUTPUT

    DATE WEEK_DAY WEEK_DAY_NO
    —————————————————— —————————— ———–
    2008-01-01 00:00:00.000 TUESDAY 3
    2008-01-02 00:00:00.000 WEDNESDAY 4
    2008-01-03 00:00:00.000 THURDSDAY 5
    2008-01-04 00:00:00.000 FRIDAY 6
    .
    .
    .
    .
    .
    —————————————————————————————————–
    /*********TO GET THE NO OF WEEK DAYS IN A MONTH***********/

    CREATE FUNCTION G4_WEEK_DAYS_IN_MONTH_FUNC (@DATE DATETIME)
    RETURNS @TABLE TABLE
    (

    WEEK_DAY VARCHAR(30),
    WEEK_DAY_NO INT
    )

    AS

    BEGIN

    DECLARE @SRAT_DATE TABLE
    (

    START_DATE DATETIME,
    LAST_DATE DATETIME
    )

    DECLARE @WEEK_DAY_TABLE TABLE
    (
    DATE DATETIME,
    WEEK_DAY VARCHAR(30),
    WEEK_DAY_NO INT
    )

    INSERT INTO @SRAT_DATE
    SELECT CONVERT(DATETIME,’01/’+CONVERT(VARCHAR(12),MONTH(@DATE))+’/'+ CONVERT(VARCHAR(12),YEAR(@DATE)),103),
    LAST_DATE=DBO.G4_LAST_DATE_OF_MONTH_FUNC(@DATE)

    DECLARE @FIRST_DATE DATETIME,
    @LAST_DATE DATETIME

    SELECT @FIRST_DATE=START_DATE,
    @LAST_DATE=LAST_DATE
    FROM @SRAT_DATE

    WHILE @FIRST_DATE<=@LAST_DATE

    BEGIN

    INSERT INTO @WEEK_DAY_TABLE
    SELECT @FIRST_DATE, CASE DATEPART(DW,@FIRST_DATE)
    WHEN 1 THEN ‘SUNDAY’
    WHEN 2 THEN ‘MONDAY’
    WHEN 3 THEN ‘TUESDAY’
    WHEN 4 THEN ‘WEDNESDAY’
    WHEN 5 THEN ‘THURDSDAY’
    WHEN 6 THEN ‘FRIDAY’
    WHEN 7 THEN ‘SATURDAY’
    END, DATEPART(DW,@FIRST_DATE)

    SELECT @FIRST_DATE=@FIRST_DATE+1
    END

    INSERT INTO @TABLE

    SELECT WEEK_DAY, COUNT(WEEK_DAY_NO)NO_OF_DAYS
    FROM @WEEK_DAY_TABLE
    GROUP BY WEEK_DAY_NO,WEEK_DAY

    RETURN

    END

    ———————————————————————————————————–
    /**********TO GET THE LAST DAY IN A MONTH**********************/

    CREATE FUNCTION G4_LAST_DAY_IN_MONTH_FUNC (@LEAPE_DATE DATETIME)
    RETURNS INT
    AS
    BEGIN

    RETURN
    DATEPART(DAY,DATEADD(DAY,-DATEPART(DAY,@LEAPE_DATE),DATEADD(MM,1,@LEAPE_DATE)))

    END

    ————————————————————————————————————–
    /********TO GET THE LAST DATE OF THE MONTH********************/

    CREATE FUNCTION G4_LAST_DATE_OF_MONTH_FUNC (@LEAPE_DATE DATETIME)
    RETURNS DATETIME
    AS
    BEGIN

    RETURN DATEADD(DAY,-DATEPART(DAY,@LEAPE_DATE) , DATEADD(MM,1,@LEAPE_DATE))

    END

    ————————————————————————————————————–
    /*******TO GET THE LEAP YEAR **********************************/

    CREATE FUNCTION G4_LEAP_YEAR_FUNC (@LEAPE_DATE DATETIME)
    RETURNS VARCHAR(30)
    AS
    BEGIN

    RETURN
    CASE WHEN
    DATEPART(DY, CONVERT(DATETIME,’31/’+'12/’+CONVERT(VARCHAR(12),YEAR(@LEAPE_DATE)),103))=366
    THEN ‘LEAP YEAR’
    ELSE
    ‘NOT LEAP YEAR’
    END

    END

    —————————————————————————————————————
    /*********TO GET THE WEEK DAY***********************************/

    CREATE FUNCTION G4_WEEK_DAY_FUNC (@LEAPE_DATE DATETIME)
    RETURNS VARCHAR(20)
    AS
    BEGIN

    RETURN CASE DATEPART(DW,@LEAPE_DATE)
    WHEN 1 THEN ‘SUNDAY’
    WHEN 2 THEN ‘MONDAY’
    WHEN 3 THEN ‘TUESDAY’
    WHEN 4 THEN ‘WEDNESDAY’
    WHEN 5 THEN ‘THURDSDAY’
    WHEN 6 THEN ‘FRIDAY’
    WHEN 7 THEN ‘SATURDAY’
    END

    END
    ————————————————————-

    /********HOW TO CALL THESE FUNCTIONS*******/

    SELECT VALUE=DBO.G4_WEEK_DAY_FUNC (’2008-01-01′)

    –OUTPUT
    VALUE
    ——————–
    TUESDAY