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

execSQL() method of SQLiteDatabase is used to

  1. execute query

  2. update data

  3. insert data

  4. Both (2) and (3)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

execSQL() in SQLiteDatabase is a general-purpose method for executing single SQL statements that don't return rows - primarily for queries (SELECT). For insert/update operations, Android provides dedicated methods like insert(), update(), delete() which are safer and more convenient.

Multiple choice

Which of the following parameters is not available in managedQuery() method?

  1. An array of properties of instances from the content provider

  2. A constraint statement

  3. An optional set of parameters to bind into the constraint clause

  4. An optional sort statement

  5. The identifier for a layout
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The managedQuery() method does not have any parameter like the layout identifier.

Multiple choice

Which of the following is the correct syntax of onUpgrade method in SQLite database?

  1. public void onUpgrade(SQLiteDatabase db) { db.execSQL(DROP TABLE IF EXISTS + TABLE_CONTACTS); onCreate(db); }

  2. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(DROP TABLE IF EXISTS + TABLE_CONTACTS); onCreate(db); }

  3. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(DROP TABLE IF EXISTS + TABLE_CONTACTS); onCreate(db); db.insert(); }

  4. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(DROP TABLE IF EXISTS + TABLE_CONTACTS); onCreate(db); db.close(); }

  5. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(DROP TABLE IF EXISTS + TABLE_CONTACTS); onCreate(db); db.delete(); }
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

onUpgrade() method is called when there is a database version mismatch.

Multiple choice

Consider the following SQL query. select distinct a1, a2, ..., an from r1, r2, ..., rm where P

For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?
  1. $\Pi_{a_1, a_2, … a_n} \sigma_p \left(r_1 \times r_2 \times \dots \times r_m\right)$
  2. $\Pi_{a_1, a_2, … a_n} \sigma_p \left(r_1 \bowtie r_2 \bowtie \dots \bowtie r_m \right)$
  3. $\Pi_{a_1, a_2, … a_n} \sigma_p \left(r_1 \cup r_2 \cup \dots \cup r_m \right)$
  4. $\Pi_{a_1, a_2, … a_n} \sigma_p \left(r_1 \cap r_2 \cap \dots \cap r_m \right)$
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

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 and Courses.Course_number = Grades.Course_number and Grades.grade = A Which of the following sets is computed by the above query?

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

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

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

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

Names are unique since they are selected distinct, so one person will be selected only once, even when he has got grade A in any number of courses taught by Korth. So, if a name is appearing it is not necessary that he scored grade A in all the courses, but scored grade A in at least one course.

Multiple choice

Consider the following functional dependencies in a database.

Date_of_Birth $\rightarrow$ Age Age $\rightarrow$ Eligibility Name $\rightarrow$ Roll_number Roll_number $\rightarrow$ Name Course_number $\rightarrow$ Course_name Course_number $\rightarrow$ Instructor (Roll_number, Course_number) $\rightarrow$ Grade

The relation (Roll_number, Name, Date_of_Birth, Age) is

  1. in second normal form but not in third normal form

  2. in third normal form but not in BCNF

  3. in BCNF

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

Multiple choice

Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. Ties are not broken but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.

Consider these statements about Query 1 and Query 2.

  1. Query 1 will produce the same row set as Query 2 for some but not all databases.
  2. Both Query 1 and Query 2 are correct implementation of the specification.
  3. Query 1 is a correct implementation of the specification but Query 2 is not.
  4. Neither Query 1 nor Query 2 is a correct implementation of the specification.
  5. Assigning rank with a pure relational query takes less time than scanning in decreasing balance order assigning ranks using ODBC.

Which two of the above statements are correct?

  1. 2 and 5

  2. 1 and 3

  3. 1 and 4

  4. 3 and 5
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Multiple choice

Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries: Query1:select student from enrolled where student in (select student from paid) Query2:select student from paid where student in (select student from enrolled) Query3:select E.student from enrolled E, paid P where E.student = P.student Query4:select student from paid where exists (select * from enrolled where enrolled. student = paid. student) Which one of the above statements is correct?

  1. All queries return identical row sets for any database.

  2. Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.

  3. There exist databases for which Query3 returns strictly fewer rows than Query 2.

  4. There exist databases for which Query4 will encounter an integrity violation at runtime.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Multiple choice

Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts 6000, 7000, 8000, 9000 and 10000 were each paid by 20% of the students. Consider these query plans (Plan 1 on left, Plan 2 on right) to “list all courses taken by students who have paid more than x”

A disk seek takes 4ms, disk data transfer bandwidth is 300 MB/s and checking a tuple to see if amount is greater than x takes 10Zs. Which of the following statements is correct?

  1. Plan 1 and Plan 2 will not output identical row sets for all databases

  2. A course may be listed more than once in the output of Plan 1 for some databases

  3. For x = 5000, Plan 1 executes faster than Plan 2 for all databases

  4. For x = 9000, Plan I executes slower than Plan 2 for all databases.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Multiple choice

Consider the table employee (empId, name, department, salary) and the two queries Q1, Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary in department 5, which one of the statements is TRUE for any arbitrary employee table?

$Q_1$ : Select e. empId
            From employee 
            Where not exists 
            (Select * From employee s where s. department = “5” and s. salary >= e. salary) 
$Q_2$ : Select e. empId
            From employee e
            Where e. salary > any
            (select distinct salary From employee s where s. Where s. department = “5”)

  1. Q1 is the correct query.

  2. Q2 is the correct query.

  3. Both Q1 and Q2 produce the same answer.

  4. Neither Q1 nor Q2 is the correct query.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Multiple choice

By which command can we create a table from a query?

  1. Simple query wizard

  2. Make table query

  3. Cross table query

  4. Append query

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

A Make-Table query creates a new table from the results of a select query. It is used to archive data, create backups, or export data in a specific format. The other query types serve different purposes - Simple Query Wizard builds basic queries, CrossTab query creates summary tables, and Append query adds records to existing tables.

Multiple choice

How many modes or views are available to open a query?

  1. 3

  2. 5

  3. 6

  4. 7

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

Queries in MS Access can be opened in three different views: Design View (for visually designing the query), Datasheet View (for viewing query results in a table format), and SQL View (for viewing and editing the SQL statement). These three views provide different ways to interact with the query object.

Multiple choice

Which menu keeps the relationship command?

  1. Tools

  2. Insert

  3. Format

  4. Table

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

In MS Access, the Tools menu contains the Relationships command for defining relationships between tables. This is where you go to establish referential integrity, define one-to-many or one-to-one relationships, and enforce cascading updates and deletes between related tables.

Multiple choice

How many types of queries are available in the query menu?

  1. 4

  2. 5

  3. 6

  4. 7

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

The MS Access Query menu typically shows 4 main query categories: Select Query, Make-Table Query, Update Query, and Append Query. Some versions may show different categorizations, but 4 is the standard count in the basic Query menu.