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 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 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 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 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? Q1 : Select e. empId From employee Where not exists (Select * From employee s where s. department = “5” and s. salary > = e. salary) Q2 : 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

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 following relational schema: Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) Consider the following relational query on the above database: SELECT S.sname FROM Suppliers S WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C WHERE C.pid NOT (SELECT P.pid FROM Parts P WHERE P.color<> 'blue'))

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

  1. Find the names of all suppliers who have supplied a non-blue part.

  2. Find the names of all suppliers who have not supplied a non-blue part.

  3. Find the names of all suppliers who have supplied only blue parts.

  4. Find the names of all suppliers who have not supplied only blue parts.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Multiple choice

Consider the following relation schema pertaining to a students database: Student (rollno, name, address) Enroll (rollno, courseno, coursename) where the primary keys are shown underlined. The number of tuples in the student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student*Enroll), where '*' denotes natural join?

  1. 8, 8

  2. 120, 8

  3. 960, 8

  4. 960, 120
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The boundary cases are when either all the tuples of Enroll table belong to one roll number, so there can be at most 8 roll number and course number combinations or the other case is when all the tuples belong to different roll no. this also has 8 tuples. So (8,8) = (max,min)

Multiple choice

The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:

  1. (3,4) and (6,4)

  2. (5,2) and (7,2)

  3. (5,2)(7,2) and (9,5)

  4. 1
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

On delete cascade says that deletion of a primary key value should delete its all foreign key references. So in (2,4) 2 is primary key so tuples (5,2) & (7,2) should be deleted, but in (5,2) 5 is also a key so (9,5) also deleted cascade.

Multiple choice

Consider a relation geq which represents “greater than or equal to”, that is, (x,y) $\epsilon$geq only if y $\le$ x: Create table gaq (Ib integer not null ub integer not null primary key Ib foreign key (ub) references geq on delete cascade):

Which of the following is possible if a tuple (x,y) is deleted?

  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

Multiple choice

The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query select? select title from book as B where ( select count(*) from book as T where T. price>B.Price)<5

  1. Titles of the four most expensive books

  2. Title of the fifth most inexpensive book

  3. Title of the fifth most expensive book

  4. Titles of the five most expensive books
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

This SQL query selects the titles of the five most expensive books by comparing with each other.

Multiple choice

Given the relations employee (name, salary, deptno), and department (deptno, deptname, address). Which of the following queries cannot be expressed using the basic relational algebra operations ($\sigma, \pi, \cup, \cap -$)?

  1. Department address of every employee

  2. Employee whose name is the same as its department name

  3. The sum of all employee salaries

  4. All employees of a given department
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The six basic operators of relational algebra are the selection(σ ), the projection(π), the Cartesian product (x) (also called the cross product or cross join), the set union (U), the set difference (-), and the rename (p). These six operators are fundamental in the sense that none of them can be omitted without losing expressive power. Many other operators have been defined in terms of these six. Among the most important are set intersection, division, and the natural join, but aggregation is not possible with these basic relational algebra operations. So, we cannot run sum of all employees’ salaries with the six operations.

Multiple choice

Consider the relation schema: Parts (Pno, pname) Supplier (Sid, Sname) SP (sid, pno, qty) The SQL query which display all the parts having more than one supplier is

  1. select distinct p.pname from parts P, SP a, SP b where a.Pno = b.Pno and a.Sid = b.sid and a.Pno = P.pno

  2. select distinct P.name from parts P, SP a, SP b where a.pno = b.pno and a.sid < > b.sid and P.Pno = a.Pno

  3. cannot be determined

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

To find parts with more than one supplier, we need to identify parts supplied by at least two DIFFERENT suppliers. Option B correctly self-joins the SP table twice with alias a and b, requiring the same part number (a.pno = b.pno) but DIFFERENT supplier IDs (a.sid <> b.sid), then joins with Parts to get the name. Option A is incorrect because it requires a.Sid = b.sid, which would find the same supplier twice.

Multiple choice

Suppose we are having relation schema as student (name, student-id, degree-level) with primary key as student-id. (i) INSERT INTO student VALUES. (“Himani”, 1928, “B.E.”) (ii) DELETE FROM student (iii) INSERT INTO student VALUES (“Hemant”, 1220, “B. Tech”) (iv) ALTER TABLE student ADD ( subject varchar(20)) (v) ALTER TABLE student DROP subject. What will be the results of the above sequence?

  1. Gives error, since we are deleting the table.
  2. In student $\theta$ table we add new attributes as subject.

  3. In student table we have one tuple with values “Hitesh”, 1220,”B.tech”.

  4. In student table we have one tuple with value “Hitesh”, 1928. an “B.E”.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

First we insert values in student table as “Hitesh”, 1928, “B.E”. Then we delete tuple from student but table is still known to database, if it is an empty relation.After that we again inserting tuple into student having values “Hitesh”, 1220, and “B.Tech”After that by using ALTER command we add one attribute as subject.After that we again use ALTER command to drop attribute subject.So we remain with student relation having values “Hitesh”, 1220,'B.Tech”