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

Triggers are most useful to enforce which type of integrity?

  1. Referential integrity

  2. Entity integrity

  3. Domain integrity

  4. User-defined integrity

  5. None of these
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Rows cannot be deleted which are used by other records. Triggers are most useful to enforce referential integrity. Referential integrity enforces rules used to ensure that data remains valid across multiple tables.

Multiple choice

Select * from employees where (total_sales-25000) between (select avg(total_sales) from employees) and (select avg(total_sales)*1.2 from employees); What will be the output of the above query? Table: Employees(emp_id,fname,lname,salary,total_sales)

  1. It retrieves the details of employees whose total sales volume is greater than the average sales volume.

  2. It lists those employees whose total sales volume minus 25000 is between the average sales volume and 120 percent of the average.

  3. It lists those employees whose total sales volume minus 25000 is between the average sales and 1.2% of the sales.

  4. It lists those employees whose total sales volume minus 25000 is between the average sales and 1.2% of the average.

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

Condition: (total_sales-25000) Implies whose total sales volume minus 25000(select avg(total_sales) from employees) and (select avg(total_sales)*1.2 from employees) Implies the average sales volume and 120 percent of the average.

Multiple choice

Which of the following syntaxes is not correct for insert statement (consider table in description)? Table: rolno(numeric),fname(varchar),mname(varchar),lname(varchar)

  1. INSERT INTO tableVALUES ('xyz', 'abc');

  2. INSERT INTO table fname, lnameVALUES 'xyz', 'abc';

  3. INSERT INTO table ('fname', 'lname')VALUES ('xyz', 'abc');

  4. INSERT INTO table (fname, lname)VALUES ('xyz', 'abc');

  5. INSERT INTO table (rolno, lname)VALUES (34, abc);
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Insert Data Only in Specified Columns: It is also possible to only add data in specific columns. INSERT INTO table (fname, lname)VALUES ('xyz', 'abc'); It is the correct option.

Multiple choice

What would be the SQL query/queries to list the regno of examinees who have a score greater than the average score? Table: Examinee(regno,name,score) Where regno is primary key.

  1. Select regno from examine where score>avg(score);

  2. Select regno, avg(score) from examine where score>avg(score);

  3. Select regno from examine where score>(select avg(score) from examinee);

  4. All of the above

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

Option 3 is the correct answer as avg(score) is used in select statement. The subquery is used to get the average score.

Multiple choice

What will be the SQL query to retrieve the sp and cp of all products whose item (name) is not entered?

Table:Product(id,item,sp,cp)

  1. Select sp,cp from product where item is NULL;

  2. Select sp,cp from product where item = NULL;

  3. Select sp,cp from product where item = 0;

  4. All of the above

  5. None of these
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Output: to retrieve the sp,cp of all products Condition: whose item (name) is not entered implies null entries in item column. Option 1 is the correct answer.

Multiple choice

What is the difference between drop and delete?

  1. Both remove columns from table.

  2. Both remove rows from table.

  3. DELETE Statement is used to remove rows in a table while drop removes a column in a table.

  4. Both work in a similar way.

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

As per the explanation of options 1 and 2, DELETE statement is used to remove rows in a table while drop removes a column in a table.

Multiple choice

What will be the output of the following query? select dept from employee e group by dept having (select count() from employee cs_e where e.dept = cs_e.dept and (position like %managers' or position like %supervisor or position like '%president%'))>(select count() from employees cs_e where e.dept = cs_e.dept and (position not like '%manager' and position not like '%supervisor%' and position not like 'president%')) order by dept; Table: Employee(emp_id,fname,lname,dept,position)

  1. It will display the name of the departments that have managers, supervisors and president.

  2. It will display the name of the departments that have more employees with job titles such as managers, supervisors or president than not.

  3. It will display the name of the departments that have manager, assistant manager, supervisor, vice president or president.

  4. It will display the name of the departments that have more employees with job titles such as managers, assistant manager, supervisors, vice president or president than not.

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

As per explanation given in option 2, option 4 is the correct answer.

Multiple choice

Which of the following statements is/are true about having and group by clause? (a) Group by clause can work alone. (b) Having clause can work alone. (c) If group by clause is used, then having clause must be used in a query. (d) The having clause is used only if group by clause is used in a query.

  1. Only c

  2. a and b

  3. a, b, c and d

  4. a and d

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

The having clause is used only if group by clause is used in a query.

Multiple choice

Select distinct name from students, courses, grades where students.roll_no = grade.roll_no and courses.instructor = Korth and courses.course_no = grades.course_no and grades.grade = A; Which of the following sets is computed by the above query? Tables: Students(roll_no,name,dob) Courses(course_no,course_name,instructor) Grades(roll_no,course_no,grade)

  1. Names of students who have got an A grade in all courses.

  2. Names of students who have got an A grade in all courses taught by Korth.

  3. Names of students who have got an A grade in at least one of the courses taught by Korth.

  4. Names of students who have got an A grade in at least one of the courses.

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

Consider the following entries in tables:Students(12,asd,15june)Courses({3,C,korth}, {4,C++,korth})Grades({12,3,A}, {12,4,B})Output: asd Condition: 12 = 12 and korth = Korth and 3 = 3 and A = A As all conditions are true, output is given as Condition: 12 = 12 and korth = Korth and 4 = 4 and B! = AB! = A returns false. As and is used even if one condition is false it will return false. As the condition is true for course 3, and is shown as output.

Multiple choice

Which of the following is not true about the limitations of view?

  1. If you use the DISTINCT clause to create a view, you cannot update or insert records within that view.

  2. If you do insert or update records through a join view, all records that are updated must belong to the same physical table.

  3. Virtual columns can be updated.

  4. You cannot use DELETE statements on multiple table views.

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

We cannot update a virtual column (a column that is the result of an expression or function).

Multiple choice

What will be the output of the following SQL statement? Select * from guest where guestno = (select guestno from booking where datefrom< = current_date and dateto> = current_date and hotelno = (select hotelno from hotel where hname = 'XYZ'));

Tables:
Guest(guestno,gname,gadd)
Hotel(hotelno,hname,city)
Booking(hotelno,guestno,datefrom,datto,roomno)
Room(roomno,hotelno,type,price)

  1. Guest details who are staying in XYZ hotel from the specific date to the current date.

  2. Guest details who are staying in XYZ hotel as on date.

  3. Guest details who are staying in XYZ hotel.

  4. Guest details who are not staying in XYZ hotel as on date.

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

As per explanation for option 1, option 2 is the correct answer.

Multiple choice

Which of the following statements is/are true? (a) The where clause searches the rows after they are grouped by using 'group by' clause. (b) The 'group by' clause collects rows that meet the where clause search conditions and places those rows into a group for each unique value in the ' group by' clause. (c) Omitting the 'group by' clause creates a single group for the whole table. Table1

NAME

ABLE BAKER CHARLIE DEAN

Table2

NAME

ABLE BAKER BRAVO CHARLIE DECON

  1. a and b

  2. b and c

  3. a and c

  4. All of these

  5. Only b

Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

As per explanation for option 1, statement 'b' is true. If it doesn't use the 'group by' clause, a single group is created for the whole table.