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

What will be the conceptual order of evaluation of the following select statements? (a) First, the Cartesian product of all tables in the 'form' clause is formed. (b) From this, rows not satisfying the 'where' condition are eliminated. (c) Groups not satisfying the having clause are then eliminated. (d) The remaining rows are grouped in accordance with 'group by' clause. (e) The UNION, INTERSECT, EXCEPT is taken after each subquery is evaluated. (f) If the keyword DISTINCT is present, duplicate rows are eliminated. (g) The expressions of the select clause target list are evaluated. (h) The set of all selected rows is sorted if an ORDER BY is present.

  1. a - b - c - d - e - f - g - h

  2. a - b - d - c - g - f - e - h

  3. a - b - d - h - c - g - f - e

  4. a - b - e - g - f - c - d - h

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

Option 2 is the correct answer. If we alter any of the statements in the sequence, we get a wrong result.

Multiple choice

Alter command is used: (a) to delete a column in a table (b) to change the data type of a column in a table (c) to add a column in a table (d) to rename a table

  1. only a

  2. a and b

  3. a, b and c

  4. all of these

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

Option 4 is the correct answer as alter statement is used to change the data type of a column in a table, to delete a column in a table, to add a column in a table and to rename a table. Syntax: ALTER TABLE table_name RENAME TO new_table_name

Multiple choice

Which of the following is not a difference between EXISTS and ANY/ALL?

  1. EXISTS is more powerful than ANY/ALL.

  2. All queries represented by ANY/ALL can be obtained by EXISTS operator.

  3. All queries represented by EXISTS may not be possible with ANY/ALL operator.

  4. ALL returns TRUE only if all the results of a subquery meet the condition, while EXISTS takes a subquery as an argument and returns TRUE if the subquery returns anything.

  5. Replacing EXISTS with ANY produces an identical result.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Replacing EXISTS with ANY may or may not produce identical result.

Multiple choice

Which of the following is/are correct syntax(es) for insert statement? (a) INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN); (b) INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN); (c) INSERT INTO CUSTOMER (column1,column5,columnN-1)VALUES (value1,value5,valueN-1);

  1. a and b

  2. a, b and c

  3. only a

  4. only b

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

It is also possible to only add data in specific columns. Example: INSERT INTO CUSTOMER (Customer Number, Last Name, First Name) VALUES ('1000', 'Smith', 'John')

Multiple choice

What will be the output of the following query? Select sum(price) from room r where roomno not in(select roomno from booking b, hotel h where(datefrom<=current_date and dateto>=current_date)and b. hotelno=h.hotelno and hotelname='XYZ')

Hotel(hotelno, hotelname, city) Room(roomno, hotelno, type, price) Booking(hotelno, guestno, datefrom, dateto, roomno) Guest(gustno, guestname, guestadd)

  1. Total revenue/expenditure lost from unoccupied room of XYZ hotel in a specific period

  2. Total revenue/expenditure lost from unoccupied room of XYZ hotel as on date

  3. Total revenue/expenditure lost from occupied room of XYZ hotel as on date

  4. Total revenue/expenditure gained from occupied room of XYZ hotel as on date

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

The query retrieves total revenue/expenditure lost from unoccupied room of XYZ hotel as on date. Output: sum(price) or revenue / expenditure Condition: hotelname='XYZ'datefrom<=current_date and dateto>=current_date implies as on date or current date roomno not in implies unoccupied roomno.

Multiple choice

Which of the following is/are the difference(s) between having and where clause? (a) Where is used to filter rows while having is used to filter groups. (b) There is no alternative for 'where'. To filter groups, we have operators other than 'having'. (c) Aggregate cannot be used in where clause. Aggregate can be used in having clause.

  1. Only a

  2. Both a and b

  3. All a, b and c

  4. Both b and c

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

Aggregate can be used in having clause, e.g. select name from student where branch='cse' group by year having avg(marks)>50.

Multiple choice

Which of the following explains 'correlated subquery'?

  1. Query evaluated once for every row processed by the parent statement

  2. Query evaluated once for the entire parent statement

  3. Query that retrieves multiple columns but single row

  4. Query displaying one column and one row

  5. Query which retrieves multiple columns and multiple rows

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

Yes, it is correct. The multiple subqueries can be combined to form a single query.

Multiple choice

What will be the output of the following query? select count(*) from employees e1, e2, department d where e1.lname='abc , e1.fname='xyz', e1.empid=d.mgrempid and d.dptno=e2.dptno. Tables: Employee(empid,fname,lname,add,dob,position,deptno) Dpartment(deptno,deptname,mgrempid) Project(projno,projname,deptno) Workson(empid,projno,hoursworked)

  1. It finds out how many employees are managed by 'xyz abc'.

  2. It finds the list of employees that are managed by 'xyz abc'.

  3. It counts the number of managers bearing the name 'xyz abc'

  4. It finds out the managers bearing the name 'xyz abc'

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

Output : count(*)Condition : Manager name 'xyz abc'Table : Employee, Department This query finds out how many employees are managed by 'xyz abc'.

Multiple choice

What will be the result of the following query?

Select title_id,copies_sold=sum(qty) from sales where ord_date between '1/1/2010 and '12/31/2010' group by all title_id Sales(title_id,qty,ord_date)

  1. All the title id's and quantity of books sold

  2. All the title id's and total quantity sold

  3. Total quantity of books that were sold during the year 2010

  4. All the title id's and quantity of books sold during the year 2010

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

Output: title_id,copies_sold=sum(qty)Condition: ord_date between '1/1/2010 and '12/31/2010' group by all title_id Option 4 is the correct answer.

Multiple choice

What does the following select statement do? Select fname,lname from employees where exists (select * from dependent where ssn=essn) and exists (select * from department where ssn=mgrssn);

Consider Table: Employee(fname,minit,lname,ssn,bdate,add,salary,dno)
Department(dmname,dno,mgrssn,mgrstartdate)
Work_on(essn,pno,hours)
Project(pname,pno,plocation,dno)
Dept_locations(dno,dlocation)

  1. Lists the names of all the employees who have managers

  2. Lists the names of all the managers who have dependents

  3. Lists the names of managers who have at least one dependent

  4. Lists the names of employees with at least one dependent

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

The 'and' condition exists between two subqueries. So, the complete query retrieves list of names of managers who have at least one dependent.

Multiple choice

What would be the query for enlisting the faculty member who does not teach any class using outer join?

Tables: Faculty(facid,facname,......) Student(stuid,stuname,.......) Class(facid,course,.......)

  1. select f.facid, f.facname from class c,faculty f where c.facid(+)=f.facid and c.course is null order by f.facname;

  2. select f.facid, f.facname from class c right join faculty f on c.facid(+)=f.facid where c.course is null order by f.facname;

  3. select f.facid, f.facname from class c,faculty f where c.facid(+)=f.facid(+) and c.course is null order by f.facname;

  4. select f.facid, f.facname from class c full join faculty f on c.facid=f.facid where c.course is null order by f.facname;

  5. all of the above
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Option 5 is the correct answer, as the query can be written in all the given four ways.

Multiple choice

______________ rule followed by database software states that two simultaneous transactions cannot interfere with one another and intermediate results within a transaction are not visible to other transactions.

  1. Atomicity

  2. Consistency

  3. Isolation

  4. Durability

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

In computer science, ACID (Atomicity Consistency Isolation Durability) is a set of properties of database transactions. Isolation states that two simultaneous transactions cannot interfere with each other and intermediate results within a transaction are not visible to other transactions.