Computer Knowledge

Database and SQL

4,213 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 tables

Table Name- Jo ||| |---|---| |job_ID|Designation| |101|Clerk| |102|Analyst| |103|Manager|

Table Name- Employe |||||| |---|---|---|---|---| |EMPLOYEE_ID|FIRST_NAME|LAST_NAME|job_ID|MANAGER_ID| |7271|Amit|Saxena|102|4101| |7272|Anil|Sharma|103|4102| |7273|Mohit|Rajput|101|4103|

Find the Employee's first name and last name according to their job id. Select the correct option.

  1. select First_name, Last_name from employee1 where employee1.Job_Id=job.Job_ID;

  2. select First_name, Last_name from employee1 e, job j where e.employee_Id=j.Job_ID;

  3. select First_name, Last_name from employee1 e, job j where e.Job_Id=j.Job_ID;

  4. select First_name, Last_name from employee1 e, job j where e.Manager_id=j.Job_ID;

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

This is the correct query for the desired result.

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-June-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|

Display the maximum and mimimum price of each specific item, if the price that item is more than 1500. Select the correct option.

  1. Select item, max(price), min(price) from items_ordered group by item where max(price)>1500

  2. Select max(price), min(price) from items_ordered group by item having max(price)>1500

  3. Select item, max(price), min(price) from items_ordered group by item having max(price)>1500

  4. Select item, max(price), min(price) from items_ordered group by item having count(price)>1500

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

This is the correct SQL statement to display the desire 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|

Calculate the average price of all of the items ordered that were purchased in the month of June. Select the correct option.

  1. SELECT avg(price) FROM items_ordered WHERE order_date '%Jun%';

  2. SELECT avg(price) FROM items_ordered WHERE order_date LIKE '%Jun%';

  3. SELECT avg(price) FROM items_ordered WHERE order_date LIKE '%Jun';

  4. SELECT avg(price) FROM items_ordered WHERE order_date LIKE 'Jun';

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

This is the correct query for the desired output.

Multiple choice

Consider the following database table

Table Name- Employe ||||| |---|---|---|---| |Employee_ID|Dept no|Job|Salary| |120|201|Clerk|20,000| |121|202|Manager|22,000| |122|203|Head|32,000| |123|204|Sr. Manager|46,000| |124|205|Boss|40,000| |125|206|HR|23,000| |126|207|Shoes|26,000| ||

Which of the following is the correct query to find the highest salaries for each department in it for each job from the employee table?

  1. SELECT MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB

  2. SELECT DEPTNO, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB

  3. SELECT DEPTNO, JOB, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB

  4. SELECT JOB, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB

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

This is the correct sql query for the desired output.

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|

Select the firstname, city, and state from the customers table, where the state is either Arizona, Washington, Oklahoma, Colorado.

  1. SELECT firstname, city, state FROM customers WHERE state LIKE ('Arizona', 'Washington', 'Oklahoma', 'Colorado');

  2. SELECT firstname, city, state FROM customers WHERE state ('Arizona', 'Washington', 'Oklahoma', 'Colorado');

  3. SELECT firstname, city, state FROM customersAND state LIKE('Arizona', 'Washington', 'Oklahoma', 'Colorado');

  4. SELECT firstname, city, state FROM customers WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado');

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

This is the correct query for the desired result.

Multiple choice

Consider the following database table

Table Name- Em ||||| |---|---|---|---| |Deptno|job|department|city| |001|Manager|Sales|Kolkata| |002|Coordinator|Ministry|Delhi| |003|Clerk|HR|Delhi| |004|Clerk|Industry|Mumbai| |005|Developer|Technical dept|Banglore|

Find the number of Clerk’s working for each department.

  1. SELECT DEPTNO, JOB FROM EMP GROUP BY DEPTNO

  2. SELECT DEPTNO FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO

  3. SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO

  4. SELECT DEPTNO, COUNT() FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO

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

This is the correct query 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 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.