Which of the following sql command can be included to sort data?

Answer

 SORT WITH
 SORT BY
 ORDER BY Answer
 LIST BY

Which of the following commands is used to explain access to data?

Answer

 EXPLAIN PATH
 EXPLAIN PLAN
 CALL
 EXPLAIN ACCESS

Which of the following clauses can be included in select fetch 10

records only? ( Check all that apply)
Note: There may be more than one right answer.
Answer

 LIMIT 10
 TOP 10
 LIMIT 10 OFFSET 1
 ONLY 10

Referential Integrity means that among relations

Answers

 A record cannot be deleted if it has any associated data

 A foreign key value is a subject of its primary key
 There are no duplicate values in a primary key
 The table must be normalized to 2NF

Which of the following command will work as per below statement?

“We need to replace a string named “foo” with “bar” in a column having ID
<= 4″
Answers

 UPDATE tabel_name SET column_name= REPLACE(‘foo’, ‘bar’) where

ID <= 4;
 UPDATE tabel_name SET column_name= REPLACE(column_name,
‘foo’, ‘bar’) where ID <= 4;
 UPDATE tabel_name SET column_name= REPLACE(column_name,
‘bar’, ‘foo’) where ID <= 4;

Which of the following is the correct syntax for writing sub query?
Answer

 SELECT sub.* FROM (SELECT * FROM abc where day= ‘Friday’) where
[Link] = ‘NONE’
 SELECT * FROM (SELECT * FROM abc where day= ‘Friday’) where
[Link] = ‘NONE’
 SELECT sub.* FROM (SELECT * FROM abc where day= ‘Friday’) sub
where [Link] = ‘NONE’

Is there a difference between AND and & (ampersand) in SQL?

Answer

 Ampersand doesn’t have support in SQL:2008 standard

 AND prioriy is higher than &;
 AND has lower prioriy in comprasion with ampersand
 There is no difference.

What is difference between Decimal and numeric data types

Answer

 decimal – type of numerical data with a fixed precision and scale,

numeric – type of numerical data without fixed precision and scale;
 numeric – type of numerical data with a fixed precision and scale,
decimal – numeric data type without fixed precision and scale;
  decimal – numeric data type with a fixed precision and scale,
numeric – exact numeric data type with integer precision;
 numeric – numeric data type with a fixed precision and scale,
decimal – exact numeric data type with integer precision;
 There is no difference

Which one of the following is the correct way to delete a column

from a table?
Answer

 ALTER TABLE tabel_name DELETE COLOUMN column_name

 UPDATE TABLE tabel_name DROP COLOUMN column_name
 ALTER TABLE tabel_name DROP COLOUMN column_name
 ALTER TABLE tabel_name REMOVE COLOUMN column_name

Transacion, if available, wrap __ operations.

Answer

 DDL
 DML
 DCL
 DDL

Consider two tables A and B having only one column each and having
these values:
A = {0, 1, 2, 3, 4, 5}
B = {5, 6, 7, 8, 9, 10}
The number of records in result of UNION operator on these table will be?
Answer

 1
 11
 0
 12

Which of the following is not a DDL statement?

Answer
  CREATE TABLE
 DROP TABLE
 TRUNCATE TABLE
 INNER JOIN

Which of the following is true for creating SQL Views? (check any
that apply)
There may be more than one right answer.
Answer

 If you have a complex select with losts of joins, you can implement it
in a view and simply call the view without need to consider all these
join.
 Each user can be given permission to access the database only
through a small set of views that contain the specific data the user is
authorized to see, thus restricting the user’s access to stored data
 A view can draw data from several different tables and present it as
a single table, turning multi-label queries into single-table queries
against the view.

To create a table column which should not accept blank values,

which of the following can be used?
Answer

 NULL
 NOT NULL
 BLANK
 EMPTY

Suppose you have the following table with coloumn:

id name work_date salary
1 John 2016-01-04 300
To get an output like ‘1John2016-01-04’ which of the following function can be
used
Answer
  CONCAT
 COMBINE
 STRING

Assuming there are multiple rows in the ‘Comission_Agents’ table

with entries for commission agents from different deparments, what
is the error in the following.

SELECT name, deparment_id, commission_percentage from Comission_Agents

where commission_percentage = (SELECT min(commission_percentage)
FROM Commission_Agents GROUP BY deparment_id)
Answer

 You cannot use GROUP BY clause in sub-query

 An aggregate function cannot be used in sub-query
 You cannot use ‘=’ operator as the sub query is returning multiple
results
 No error

Which operator allows you to specify multiple values in WHERE

clause
Answers

 LIKE
 IN
 ==
 INSIDE

To speed up search in table we can?

Answer

 create Index
 truncate table
 create views

Which of the following statement is/are true?

Answer
  Select and create are DDL commands
 Create and Grant are DCL commands
 Drop and Insert are TCL commands
 Alter and Drop are DDL commands

Which of the following operator can be used to check if a column

contains NULL value
Answer

 EXISTS
 IS NULL
 =
 <>
 NOT

Which of the following can be used to delete all the data from a
table?
Answer

 EMPTY
 DROP
 TRUNCATE

What does MID() function do?

Answer

 Returns the middle character

 Extra characters from a text field
 Returns the length of a text field

For a table with the following columns:

Product_id, product_name, supplier_id, price
Which of the followings queries will give you the supplier with the maximum
average price of products?
Answer
  Select supplier_id, avg(price) from Products group by supplier_id
having avg(price) in (Select max(avg_price) in (select max
(avg_price) from (Select avg(price) as avg_price from products group
by supplier_id))
 Select supplier_id, max(avg(price)) from Products group by
supplier_id
 Select supplier_id, avg(price) from Products group by supplier_id
having avg(price) in (Select max(avg_price) from (Select avg(price)
as avg_price from products)
 Select supplier_id, avg(price) from Products group by supplier_id
having avg(price) in (Select max(avg_price) as avg_price from
products group by supplier_id))

Which of the following commands can use a foreign key constraint?

(Check any that apply)
Note: There may be more than one right answer
Answer

 CREATE
 ALTER
 UPDATE
 ALTER

What keyword is used to filter values obtained by applying

aggregate functions in the query results using GROUP by clause?
Answer

 FILTER;
 WHERE
 HAVING
 None of the above;

For a table of the following data

Name Marks

John 400
 Brown 200

Darwin 350

Kamy 250

Which of the following queries will give you names of all students have above
average marks?
Answer

 select * from Student where Marks > (select avg(Marks) from

[Student])
 select name from Student where Marks > avg(Marks)
 select name from Student where Marks > avg(Marks) GROUP BY
marks

Which of the following is true about commit command?

Answer

 Executing commit without begin transaction will result in a error

 A commit will end all the transaction that were initiated before it
 A commit will end transaction only in the current session
 Commit is opposite of rollback

What will be the output of the following query?

select (round(3.5 mod 4)) from DUAL;
Answer

 3
 3.5
 4
 4.5

What is difference between CHAR and VARCHAR data types?

Answer
  CHAR takes fixed space whereas VARCHAR takes variable space
 CHAR store data as an array of characters, whereas VARCHAR store
data as srings
 CHAR cannot contain NULL values, whereas VARCHAR can contain
NULL values
 CHAR in MySQL is the same as VARCHAR in Oracle

Which statement INSERT or UPDATES Based on the given condition?

Answer

 Insert
 Merge
 Intersect
 Select

Which of the following can you do with SQL

Answer

 Update record in database

 Create new database
 Send data from database
 Insert new records to database

Which Query can be used to copy all records from table1 into table2
provided they have the same columns?
Answers

 CLONE INTO table2 SELECT * FROM table1

 INSERT INTO table1 FROM table2
 INSERT INTO table2 FROM table1
 INSERT INTO table2 SELECT * FROM table1

Which of the following only works with GROUP BY clause?

Answer

 WHERE
 ORDER BY
  HAVING
 None of the above

What will the output of following query?

Select substring(“987654321”, INSTR(‘foobar’.’o’),5) from DUAL
Answer

 98765
 87654
 76543
 65432

Suppose a table CUSTOMERS has the following records:

1 RAMAN 150000

2 Andrew 200000

3 Christi 240000

4 Ivan 240000

5 John 240000

6 Ann 240000

NULL NULL NULL

Which of the following statements will select the customers with

income more than 200000?
Answer

 SELECT * FROM CUSTOMERS WHERE ID IN (SELECT INCOME FROM

CUSTOMERS WHERE income > 200000)
 SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM
CUSTOMERS WHERE income > 200000)
 SELECT * FROM CUSTOMERS WHERE INCOME IN (SELECT
CUSTOMERS from CUSTOMERS WHERE INCOME > 200000)
  SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM
CUSTOMERS WHERE INCOME >= 200000)

Which of the following operator can not used in the ‘WHERE’ clause?
Answer

 <>
 =
 <<
 >=

Which of the following functions are related to XML?

Answer

 PATH
 XML TABLE
 DEPTH
 XMLPATH

To create a column in table with fixed length (n) string which of the
following data type is recommended?
Answer

 VARCHAR
 CHAR
 FIXED
 STRING

Consider the following Attendance table, which contains employee

attendance record for a company:
EmployeeId(Int), LoginTime (time), logouttime(time)
Which of these SELECT statements will list the employee ID that has logged-in
most recently ?
Answer

 SELECT TOP 1 [Link] FROM Attendance a WHERE EXISTS

(SELECT MAX([Link]) FROM Attendance b GROUP BY
 EmployeeID HAVING MAX([Link]) = [Link] AND
[Link] = [Link])
 SELECT TOP 1 [Link] FROM Attendance a WHERE
[Link] = (SELECT MAX([Link]) FROM Attendance b)
 SELECT EmployeeID FROM Attendance GROUP By LoginTime HAVING
MAX(LoginTime)
 None of the above