Home › Quizzes › Online Test 2 - Database Management System

Online Test 2 - Database Management System

GATE DBMS Online Test covering relational algebra, SQL queries, transaction management, database models, and query optimization concepts.

24 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

To be considered minimally relational, the DBMS must support of the following key relational functions.

  1. SELECT, PROJECT, UNION
  2. SELECT, PROJECT, JOIN
  3. SELECT, UNION, JOIN
  4. SELECT, UNION, INTERSECT
Question 2 Multiple Choice (Single Answer)

The successful execution of a database despite the possibility of system failure is called

  1. atomicity
  2. consistence
  3. durability
  4. integrity
Question 3 Multiple Choice (Single Answer)

Find the odd man out.

  1. Desktop database
  2. Work group database
  3. Distributed database
  4. Enterprise database
Question 4 Multiple Choice (Single Answer)

Database that are designed and managed specifically to meet information needs are called

  1. production databases
  2. transaction database
  3. data warehouses
  4. database management system
Question 5 Multiple Choice (Single Answer)

A file system is said to exhibit data dependence, because

  1. a change in any file's structure such as the addition or deletion of a field, requires the modification of all programs using that file
  2. organization of the data, within the file is determined by the data's expected use
  3. data is the most important part of a file system, which also includes hardware software, people and procedures
  4. when any of the files data characteristics change, all data access programs are subjects to change
Question 6 Multiple Choice (Single Answer)

Which of the following statements/ statement are true?
(i) In SQL you can create virtual tables.
(ii) SQL is only data manipulation language.
(iii) WHERE clause applies to output of a GROUP BY command.
(iv) HAVING clause applies to column and expressions for individual rows.

  1. (i), (ii), (iii)
  2. All the above
  3. (i)
  4. (i), (iii), (iv)
Question 7 Multiple Choice (Single Answer)

Choose the odd man out.

  1. AVG
  2. INT
  3. DATE
  4. BOOLEAN
Question 8 Multiple Choice (Single Answer)

The data retrieval time factor is most critical for

  1. transactional DBMS
  2. decision DBMS
  3. data warehouse
  4. both (2) and (3)
Question 9 Multiple Choice (Single Answer)

Match the following.
1. SELECT (a) Vertical subset of a tuple
2. PROJECT (b) Horizontal subset of a tuple
(c) Common tuples
  1. 1 - a, 2 - b
  2. 1 - b, 2 - a
  3. 1 - c, 2 - a
  4. 1 - c, 2 - b
Question 10 Multiple Choice (Single Answer)

Which of the following statements are equivalent?
(I) LIKE '_ _ _ %' (II) LIKE '% _ _ _'
(III) LIKE '%_ _%' (IV) LIKE '%_ _ _ %'
(V) '%%%_%'
  1. I and II only
  2. I, II, IV, and V
  3. I, II, III, IV
  4. I, II, III, IV, V
Question 11 Multiple Choice (Single Answer)

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”.
Question 12 Multiple Choice (Single Answer)

Which of the following statements is/are correct?

(i) SQL allows use of DISTINCT with count (*).

(ii) If is not legal to use DISTANCE with MAX and MIN.

(iii) If is ‘WHERE’ clause and a ‘HAVING’ clause appear in the same clause is applied first.

(iv) By default, the ‘ORDER BY’ clause list items in a descending order.

  1. All of the above
  2. (i), (ii), (iii)
  3. (ii), (iii)
  4. (iii)
Question 13 Multiple Choice (Single Answer)

LIKE “abcd%” ESCAPE ‘’- The statement matches

  1. all strings beginning with “abcd”
  2. all strings ending with “abcd”
  3. all string beginning with “abcd%”
  4. error in SQL
Question 14 Multiple Choice (Single Answer)

Which of the following is not supported by the hierarchical database model?

  1. Structural independence
  2. Data independence
  3. Database integrity
  4. Database security
Question 15 Multiple Choice (Single Answer)

Which of the following is (are) true with respect to SQL?

(I) SQL is case insensitive

(II) Patterns used for comparison of string are case sensitive

(III) FROM and fROM are equivalent and mean the same keyword

(IV) String 'Mr' is not equal to string 'MR'

  1. (I) , (II) and (III) only
  2. (I) and (IV) only
  3. (II), (III) and (IV) only
  4. (I) , (II), (III) and (IV) only
Question 16 Multiple Choice (Single Answer)

Consider the relation schema as follows.
Employee (employee-name, sheet, city)
Works (employee-name, company-name, salary)
Company (Company-name, city)
Manages (Employee-name, manager-name)

Write a SQL query that gives the names of the employee who works in the same city and having salary greater than equal to 17000 and less than equal to 35500.

  1. SELECT employee-name
    FROM employee, works, company
    WHERE salary between 17000 and 35500
  2. SELECT employee-name
    FROM employee, works, company
    GROUP BY company, city having salary between 17000 and 35500
  3. SELECT employee-name
    FROM employee, works, company
    GROUP BY city having salary between 17000 and 35500
  4. SELECT employee-name
    FROM employee, works, company
    GROUP BY city having salary > = 17000 and 35500 salary < = 35500
Question 17 Multiple Choice (Single Answer)

Find all employees in the database who do not work for State Bank of India.

  1. SELECT employee-name
    FROM employee
    WHERE employee-name IN
    (SELECT employee-name
    FROM employee-work
    WHERE company name
    = “State Bank of India”
  2. SELECT employee-name
    FROM employee
    WHERE employee-name except
    (SELECT employee-name
    FROM employee-work
    WHERE company name
    = “State Bank of India”
  3. SELECT employee-name
    FROM employee
    WHERE employee-name <>
    (SELECT employee-name
    FROM employee-work
    WHERE company name
    = “State Bank of India”
  4. SELECT employee-name
    FROM employee
    WHERE Employee-name not IN
    (SELECT employee-name
    FROM employee-work
    WHERE company name
    = “State Bank of India”
Question 18 Multiple Choice (Single Answer)

6 Files F1, F2, F3, F4, F5 and F6 have 100, 200, 30, 80, 120, 150 records respectively.
In what order should they be stored so as to optimize activity? Assume each file is accessed with the same frequency.

  1. F2, F3, F1, F5, F6, F2
  2. F2, F6, F5, F1, F4, F3
  3. F1, F2, F3, F4, F5, F6
  4. Ordering is immaterial as all files are accessed with the same frequency.
Question 19 Multiple Choice (Single Answer)

Match the following.
(i) SMALLINT (I) Calendar date
(ii) SYSDATE + 60 (II) Integer values up to 16 digits
(iii) NUMERIC(3,1) (III) Date 60 days from today's date
(iv) DATE (IV) 0.32
(V) 44.5
  1. i - IV, ii - III, iii - IV, iv - I
  2. i - II, ii - III, iii - V, iv - IV
  3. i - IV, ii - III, iii - V, iv - II
  4. i - II, ii - III, iii - V, iv - I
Question 20 Multiple Choice (Single Answer)

Consider the given schemes (20 - 2) Branch scheme = (Branch name, assets, branch city) Customer scheme = (Customer name, street, Customer city) Deposit scheme = (Branch name, account name, customer name, balance) Borrow scheme = (Branch name, loan number, customer name, amount) Client scheme = (Customer name, banker name) Using the relational algebra the query that finds customers who have a balance of over 1000 is

  1. $\pi_{customer name} (\sigma_{balance} > 1000(Deposits)$
  2. $\sigma_{customer name} (\sigma_{balance} >1000(Deposits)$
  3. $\pi_{customer name} (\sigma_{balance} > 1000(Borrow)$
  4. $\sigma_{customer name} (\sigma_{balance} >1000(Borrow)$
Question 21 Multiple Choice (Single Answer)

Consider the following transaction T0 and T1<To start><To , A, 1050, 970><To, B, 2030, 2070><To commit><T1 start><T1, C, 720,640><T1 commit>
If the system crashes just after log record for the step write C of transaction T1 has been written to stable storage, the log at the time of crash will be

  1. <T0 start> <T0, A, 935><T0, B, 2055>
  2. <T0 start><T0, A, 957><T0, B, 2055><T0 commit><T1 start><T1, C, 640>
  3. <T0 start><T0, A, 957><T0, B, 2055><T0 commit><T1 start><T1, C, 62><T1, Commit>
  4. none of the above
Question 22 Multiple Choice (Single Answer)

Which of the following queries finds the clients of banker Agassi and the city they live in?

  1. $\pi_{client, customer\ name, customer\ city} (\sigma_{Banker name} = “Agassi” (client × customers))$
  2. $\pi_{client, customer\ name, customer\ city} (\sigma_{Banker name} = “Agassi” (client × customers)$
  3. $\pi_{client, customer\ name, customer\ city} \\\\ (\sigma_{Banker name} = “Agassi” (\sigma_{client} × customer name) = \\\\ customer, customer\ name (client × customers)$
  4. $\pi_{customer\ name, customer\ city }(\sigma_{Banker name} = “Agassi” (client × customers)$
Question 23 Multiple Choice (Single Answer)

What will be the average access time?

  1. 268 units
  2. 293 units
  3. 256 units
  4. 210 units
Question 24 Multiple Choice (Single Answer)

Which of the following is used to add new attribute (column) in the existing relation schema?

  1. ADD
  2. INSERT
  3. CREATE
  4. DROP
← Back to Quizzes