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

In SQL, which of the following problems generally occur?

  1. The user had to have knowledge of the table and index structures.

  2. The same query can be written in many ways and varying internal execution plans.

  3. SQL syntax is too difficult.

  4. Independence of table relationships.

  5. The introduction of data redundancy.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

the declarative nature of SQL makes it possible to write an individual query in many different forms, each with identical results. For example, a query he can be written with a standard join, a non-correlated sub-query or a correlated sub-query, each producing identical results but with widely varying internal execution plans.

Multiple choice

Which constraint can be defined only at the column level?

  1. UNIQUE

  2. NOT NULL

  3. CHECK

  4. PRIMARY KEY

  5. FOREIGN KEY
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column.

Multiple choice

Which one of the following sorts rows in SQL?

  1. CUBE BY

  2. GROUP BY

  3. ROLLUP BY

  4. HAVING BY

  5. ORDER BY
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

SQL's ORDER BY clause organizes data in alphabetic or numeric order. Consequently, similar values sort together in what appear to be groups. However, the apparent groups are a result of the sort

Multiple choice

In a PL/SQL, block be executed by

  1. using %ROWTYPE

  2. using a slash (/)at the end

  3. using comma and a blank space

  4. using a semi colon at the end

  5. using slash-asterisk (/*)
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

the executable block and place a forward slash (/) after the closing END.

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| |77122|12-Feb-1999|Jeans|2|2000| |77124|1-Aug-2000|Jacket|1|1260|

How will you find the number of orders made by each customer? Select the correct option.

  1. Select customer_id, sum(price) from items_ordered group by customer_id

  2. Select customer_id, count(customer_id), price from items_ordered group by customer_id

  3. Select customer_id, count(customer_id), count(price) from items_ordered group by customer_id

  4. Select customer_id, count(customer_id), count(price) from items_ordered group by item

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

This will give the desired output.

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.