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 set of relations shown below and the SQL query that follows: Students: (Roll_ number, Name, Date_ of_ birth) Courses: (Course_ number, Course_ name, instructor) Grades: (Roll_ number, Course_number, grade) Select distinct name from students, courses, Grades where students. Roll_number = Grades. Roll_number and Courses. Instructor = Korth Courses. Courses_ number = Grades Courses_ number and Grades. grade = A

Which of the following sets is computed by the above query?

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

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

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

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

The query gives the name of all the students who have scored “A” grade in any of the courses that are taught by Korth. So, 3 is the correct choice.

Multiple choice

Consider a relation geq, which represents “greater than or equal to”, i.e. (x, y)$\in$ geq only if y > = x. Create table geq: ( lb integer not null ub integer not null primary key lb foreign key (ub) references geq on delete cascade )

If a tuple (x, y) is deleted, which of the following is possible?

  1. A tuple (z, w), with z > y is deleted.

  2. A tuple (z, w), with z > x is deleted.

  3. A tuple (z, w), with w < x is deleted.

  4. The deletion of (x, y) is prohibited.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In the above question, the relation schema is ( lb , ub ), where lb is the primary key, and ub is the foreign key which is referencing the primary key of its own relation. Hence the table geq is both the master ( which has the referenced key ) as well as the child table (which has the referencing key). The table has two constraint, one is that if there is a tuple ( x, y ), then y is greater than or equal to x, And the other is referential integrity constraint, which is on-cascade-delete on the foreign key. On-cascade-delete says, that “When the referenced row is deleted from the other table (master table), then delete also from the child table”. Suppose the instance in the given relation is the following:

x  y

5  6 4  5 3  4 6  6 Now if we delete tuple (5,6) then tuple ( 4,5 ) should also be deleted ( as 5 in the tuple (4, 5) was referencing to 5 in the tuple(5,6) which no longer exist, hence the referencing tuple should also be deleted), and as (4,5) got deleted hence tuple (3,4) should also be deleted for the same reason. Therefore in total 3 rows have to be deleted if tuple ( 5,6 ) is deleted. Now from the above instance we can say that if (x,y), i.e. ( 5,6 ) gets deleted then a tuple ( z, w) i.e, ( 3, 4) is also deleted. And we can see here that w < x. Hence option 3.

Multiple choice

Which property will you set to force the user to enter value in a field?

  1. Format

  2. Default Value

  3. Required

  4. Indexed

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

The Required property forces users to enter a value in a field - it cannot be left blank. Format controls display appearance, Default Value provides an automatic value, and Indexed improves search performance but doesn't enforce data entry.

Multiple choice

Which property identifies a record uniquely in a table?

  1. Default Value

  2. Primary Key

  3. Indexed

  4. Required

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

The Primary Key property uniquely identifies each record in a table - no two records can have the same primary key value. Default Value sets automatic values, Indexed speeds up searches, and Required prevents blank fields but doesn't guarantee uniqueness.

Multiple choice

Which of the following methods in DatabaseMetaData interface retrieves the maximum number of characters the database allows for a column name?

  1. getMaxColumnLength()

  2. getMaxColumnNameLength()

  3. getMaxColNameLength()

  4. getMaxColLength()

  5. getColumnNameLength()
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This is the correct option because getMaxColumnNameLength() method is defined in DatabaseMetaData interface and it retrieves the maximum number of characters this database allows for a column name.

Multiple choice

A primary key _________.

  1. uniquely identifies the data in a row

  2. is also referred to as composite key

  3. is a unique identifier in a column

  4. must be accompanied by a corresponding foreign key

  5. is always a super key
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Yes, it is correct by defination.

Multiple choice

What is the function of the SAVEPOINT command?

  1. To save information in a database after updation

  2. To identify a point in a transaction to which you can later roll back

  3. To save a table

  4. To save after RENAME a table

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

This is the function of a SAVEPOINT command.