Computer Knowledge

Database and SQL

3,929 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow|

Find the details of employees who are earning the highest salary in each department.

  1. SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO

  2. SELECT * FROM EMP WHERE SAL>1 GROUP BY DEPTNO

  3. SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP GROUP BY DEPTNO)

  4. SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)

  5. None of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

This is a correct syntax for desired output.

Multiple choice

Consider the following database table

Table Name- Items_Ordere |||||| |---|---|---|---|---| |Customer_ID|Order_date|Item|Quantity|Price| |77120|6-Jul-1999|Belt|1|140| |77121|1-Jul-1999|Watch|4|1230| |77122|30-Jun-1999|T-shirt|6|1020| |77123|12-Jun-1999|Fan|1|950.50| |77124|30-Aug-1999|Cooler|1|4010.20| |77125|30-May-1999|Bag|7|2950| |77126|1-Jun-1999|Shoes|2|1280| |77121|3-Sep-2000|Watch|5|1800| |77125|12-Feb-2001|Jeans|6|4050| |77122|12-Feb-1999|Jeans|2|2000| |77124|1-Aug-2000|Jacket|1|1260|

Select the item and price for all of the items in the items_ordered table that the price is greater than 1500. Display the results in ascending order based on the price. Select the correct option.

  1. SELECT item FROM items_ordered WHERE price > 1500 ORDER BY price ASC;

  2. SELECT price FROM items_ordered WHERE price > 1500 ORDER BY price ASC;

  3. SELECT item, price FROM items_ordered WHERE price > 1500 ORDER BY item ASC;

  4. SELECT item, price FROM items_ordered WHERE price > 1500 ORDER BY price ASC;

  5. None of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

This is the correct query for desired result.

Multiple choice

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow|

Find the details of employees earning the highest salary.

  1. SELECT * FROM EMP WHERE SAL>1

  2. SELECT * FROM EMP WHERE SAL>0

  3. SELECT * FROM EMP WHERE SAL IN(SELECT COUNT(DISTINCT SAL) FROM EMP )

  4. SELECT * FROM EMP E WHERE 0=(SELECT COUNT( SAL) FROM EMP WHERE SAL>E.SAL)

  5. SELECT * FROM EMP E WHERE 0=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

This is the correct syntax for the desired result.

Multiple choice

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow|

Find the details of employees earning the second highest salary.

  1. SELECT COUNT(DISTINCT SAL) FROM EMP E WHERE SAL>E.SAL

  2. SELECT MAX(DISTINCT SAL) FROM EMP E WHERE SAL>E.SAL

  3. SELECT * FROM EMP E WHERE 1=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)

  4. SELECT * FROM EMP E WHERE 0=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)

  5. None of the above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

This is the correct query as we need second highest salary so we need to query as the salary less than the first employee.

Multiple choice

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow| and Table name-Dep ||| |---|---| |Deptno|deptname| |501|sales| |502|Hr| |503|CS| |504|EC|

Find the details of departments in which employees are not working.

  1. SELECT * FROM DEPT WHERE DEPTNO !=(SELECT DISTINCT DEPTNO FROM EMP)

  2. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP)

  3. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DEPTNO FROM EMP)

  4. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO )

  5. None of the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This is the correct syntax as it will show the those departments from the employee table that do not belongs to working employees. Here we used Distinct keyword to escape those results.

Multiple choice

Consider the following database table

Table Name- Customer |||||| |---|---|---|---|---| |customerid|firstname|lastname|city|state| |001|Adam|Smith|Pinetop|Arizona| |002|Sarah|Graham|Nogales|Arizona| |003|Linda|Giles|Durango|Colorado| |004|Lisa|Howell|Kailua|Hawaii| |005|Anthony|Davids|Greensboro|North Carolina| |006|Kevin|Smith|Yuma|Arizona| |007|Dalton|Howell|Tillamook|Oregon| |008|Donald|Steyn|Lynden|Washington| |009|Angelo|Mathhews|Pocatello|Idaho| |010|Albie|Morne|Snoqualmie|Washington|

How many people are in each unique state in the customers table?

  1. SELECT customerid FROM customers GROUP BY state;

  2. SELECT customerid, count(state) FROM customers GROUP BY state;

  3. SELECT state, count(state) FROM customers GROUP BY state;

  4. SELECT * FROM customers GROUP BY state;

  5. SELECT count(state) FROM customers GROUP BY state;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

This is the correct syntax as this will display the result for the unique state for all the customers.

Multiple choice

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow| and Table name-Dep ||| |---|---| |Deptno|deptname| |501|sales| |502|Hr| |503|CS| |504|EC|

Find the details of departments in which employees are working.

  1. SELECT * FROM EMP WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP)

  2. SELECT * FROM DEPT WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP)

  3. SELECT * FROM DEPT WHERE EXISTS(SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO)

  4. Both (2) and (3)

  5. None of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Both are the correct syntax for the correct output.

Multiple choice

How many sections or parts are there in a PL/SQL program?

  1. 1

  2. 2

  3. 3

  4. 4
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

There are three parts in a PL/SQL program. They are - declaration section (where the variables are declared), begin section (where executable code is written) and exception section (where the exceptions are handled). The declaration and exception sections are optional but the begin part is compulsory. A PL/SQL program ends with an 'end' statement.

Multiple choice

Which of the following is NOT a valid loop in PL/SQL?

  1. For loop.. end loop

  2. loop..end loop

  3. While loop.. end loop

  4. Do while loop..end loop
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

There is no 'Do while' loop in PL/SQL. To write conditional loops, we can either use the 'simple' or the 'while' loop.

Multiple choice

When a 'Select' command is written inside a PL/SQL program, the ____ keyword must be included in it.

  1. where

  2. in

  3. group by

  4. into
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

All PL/SQL 'select' statements must contain the 'into' keyword. The 'into' keyword is used to copy the values returned by a select statement into variables. For example, the statement - 'select bookno, bookname, price into bkno, bkname,bkprice from books where title='Oracle''- will copy the values of bookno, bookname and price columns in the variables bkno, bkname and bkprice, respectively. The 'into' clause is not used when the 'select' statement is used at the SQL prompt.

Multiple choice

Which of the following is NOT a valid cursor attribute?

  1. %ISOPEN <!--?xml:namespace prefix = o /--><o:p></o:p> <o:p></o:p>

  2. %EXISTS

  3. %FOUND <o:p></o:p>

  4. %ROWCOUNT
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The %exists is not a valid cursor attribute. If used with a cursor name, it will result in compilation error.

Multiple choice

For which of the following SQL statements can a trigger not be written?

  1. Alter

  2. Update

  3. Delete

  4. Insert
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A trigger is a PL/SQL block structure, which is fired when DML statements like Insert, Delete, Update are executed on a database table. 'Alter' is not a DML statement but a DDL statement that is used to alter or change the structure of a table like adding, dropping and resizing a column. We cannot write a trigger for a DDL command.

Multiple choice

In PL/SQL, which of the following is the correct syntax of the 'if-else' statement?

  1. If condition then statements elseif condition then statements else statements end if;

  2. if condition statements elseif condition statements else statements end if;

  3. if condition then statements elif condition then statements else statements end if;

  4. if condition statements elsif condition statements else statements endif;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

'If-else' is a condition statement that is used to write conditional statement in a PL/SQL program. For example, the following code will print a message depending on whether the value of A is greater, B is greater or both of them have the same values - if a>b then      dbms_output.put_line('A is greater'); elsif b>a then      dbms_output.put_line('B is greater'); else      dbms_output.put_line('Both are equal'); end if;