DBMS multiple choice questions with answers for gate

In this tutorial some dbms multiple choice questions with answers for gate are given for the practice. These questions are asked in previous year GATE Exam. This tutorial covers the dbms gate questions . This Tutorial also covers the questions based on sql queries for practice with answers

DBMS multiple choice questions with answers for gate

In this tutorial some dbms multiple choice questions with answers for gate are explained.These dbms gate questions can be practice for gate exam preparation. These questions are asked in previous year GATE Exam. This tutorial covers the dbms gate questions . This Tutorial also  covers the questions based on sql queries for practice with answers.

sql queries examples with answers pdf

Q1. Read the following statement and find the correct option.

P :  A query in Structural Query Language  can contain HAVING clause without having GROUP BY clause
Q : A  query in Structural Query Language can contain a HAVING clause only when GROUP  BY clause is also there in query.
R : All attributes used in the GROUP BY clause must appear in the SELECT clause
S : No It is not necessary that all attributes used in the GROUP BY clause need to appear in the SELECT clause
a.            P and R
b.            P and S
c.             Q and R
d.            Q and S

Answer :C

Concept : As per the SQL concepts option c is right but suppose if tak about execution of these sql query on different database . Let us take MYSQL then option B will also be right. What happend if  

If we use a HAVING clause without a GROUP BY clause in this situation  the HAVING condition applies to all rows that satisfy the search condition and in the result all rows that satisfy the search condition make up a single group. 

Q2. A database table  named as Loan_Records is given below.

Borrower    Bank_Manager   Loan_Amount

 Ramesh      Sunderajan     10000.00
 Suresh      Ramgopal       5000.00
 Mahesh      Sunderajan     7000.00

What is the output of the following SQL query?

SELECT Count(*)
FROM  ( (SELECT Borrower, Bank_Manager
       FROM   Loan_Records) AS S
        NATURAL JOIN (SELECT Bank_Manager,
                      FROM   Loan_Records) AS T );

a.            3
b.            9
c.             5
d.            6

Answer: C

Concept : Result of first subquery will be Table S having the records -

Borrower    Bank_Manager
 Ramesh      Sunderajan
 Suresh      Ramgqpal
 Mahesh      Sunderjan

Result of Second sub query will be table T having the records-

Bank_Manager   Loan_Amount
Sunderajan      10000.00
Ramgopal        5000.00
Sunderjan       7000.00

When Natural Join is performed on these two table S and  T the will After Join resultant table will have the following records

Borrower  Bank_Manager   Load_Amount
Ramesh    Sunderajan     10000.00
Ramesh    Sunderajan     7000.00
Suresh    Ramgopal       5000.00
Mahesh    Sunderajan     10000.00
Mahesh    Sunderajan     7000.00

Here point ot remember is that in natural join matching occur  on column name with same name

Q.3. Suppose a table T has two
columns X and Y. Type of each column is integer. Once the table is created then
a record X=1, Y=1 is inserted in the table. If MX and My represent  the respective maximum values of X and Y among
all records in the table at any instant of time. Using MX and MY, some  new data is inserted in the table 128 times
with X and Y values being MX+1, 2*MY+1 respectively. It must be remember that
each time  values of MX and MY change
after insertion. If following SQL query after the steps mentioned above is
carried out then what will be the output?


a.            127
b.            255
c.             129
d.            257

Answer: a

In order to understand the logic see Question no 30 for gate (cs/it) 2011 paper with solution here

Q4. Given the following schema:

     employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
     departments(dept-id, dept-name, manager-id, location-id)
Which of following query should be execute to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the:

SELECT last-name, hire-date

     FROM employees

     WHERE (dept-id, hire-date) IN

     (SELECT dept-id, MAX(hire-date)

     FROM employees JOIN departments USING(dept-id)

     WHERE location-id = 1700

     GROUP BY dept-id);

What is the output?
a.            It executes but does not give the correct result
b.            It executes and gives the correct result
c.             generates an error because of pairwise comparison
d.            It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.

Answer B

Concept: This query will generate the correct result because at first the inner query will return the last max hire date in every department located at id 1700. Then the outer query take the result of inner query and provide the correct result.

Q5.Consider the following  relational schema for a train reservation database . Passenger (pid, pname, age) Reservation (pid, class, tid)

Table: Passenger

pid   pname   age
 0    Sachin   65
 1    Rahul    66
 2    Sourav   67
 3    Anil     69

Table : Reservation

pid  class  tid
 0    AC   8200
 1    AC   8201
 2    SC   8201
 5    AC   8203
 1    SC   8204
 3    AC   8202

What  are the pids returned by the following SQL query for the above instance of the tables?

FROM Reservation ,
WHERE class ‘AC’ AND

       FROM Passenger
       WHERE age > 65 AND
       Passenger. pid = Reservation.pid)

a.            1, 0
b.            1, 2
c.             1, 3
d.            1, 5

Answer : C

Concept : The inner query SELECT *

       FROM Passenger
       WHERE age > 65 AND
       Passenger. pid = Reservation.pid) will return 4 tuples

1 AC 8201
2 SC 8201
1 SC 8204
3 AC 8202

Now the outer query  will select those tuple only which have the class AC , as a result Pid return will be 1 and 3 so Option C is correct.

Q6. A relational schema is given below:

Suppliers(sid:integer, sname:string, city:string, street:string)

Parts(pid:integer, pname:string, color:string)

Catalog(sid:integer, pid:integer, cost:real)

If the following sql  query is executed for the  above database:

SELECT S.sname
    FROM Suppliers S
        WHERE S.sid NOT IN (SELECT C.sid
                            FROM Catalog C
                            WHERE C.pid NOT IN (SELECT P.pid 
                                                FROM Parts P
                                                WHERE P.color<> 'blue'))

Suppose some records are inserted in the table. Then find out that which one of the following is the correct interpretation of the above query?

a.            Find the names of all suppliers who have supplied a non-blue part.
b.            Find the names of all suppliers who have not supplied a non-blue part.
c.             Find the names of all suppliers who have supplied only blue parts.
d.            Find the names of all suppliers who have not supplied only blue parts.

Answer: a

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

Q1 : Select e.empId
     From employee e
     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.department = “5”)

a.            Q1 is the correct query
b.            Q2 is the correct query
c.             Both Q1 and Q2 produce the same answer
d.            Neither Q1 nor Q2 is the correct query

Answer b

Q8. For the following two statements

    S1: Declaration of a foreign key can always be replaced by an equivalent check assertion in SQL.
    S2: Given the table R(a,b,c) where a and b together form the primary key, the  following is a valid table definition.

        CREATE TABLE S (     a INTEGER, d INTEGER, e INTEGER,    PRIMARY KEY (d),  FOREIGN KEY (a) references R)

The find the correct option among the following.

a.            S1 is TRUE and S2 is FALSE
b.            Both S1 and S2 are TRUE
c.             S1 is FALSE and S2 is TRUE
d.            Both S1 and S2 are FALSE

Answer : d

Q9. SQL allows tuples in relations  and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below.

    select * from R where a in (select S.a from S)

a.            select R.* from R, S where R.a=S.a (D)
b.            select distinct R.* from R,S where R.a=S.a
c.             select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
d.            select R.* from R,S where R.a=S.a and is unique R

Answer : C

 Q10. There are three table  above tables A, B and C as given below

 Table A

Id   Name    Age
12   Arun    60
15   Shreya  24
99   Rohit   11

Table B

Id   Name   Age
15   Shreya  24
25   Hari    40
98   Rohit   20
99   Rohit   1

Table C

Id   Phone  Area
10   2200   02 
99   2100   01

 Then determine the number of tuples  return in the result of the following SQL query.

WHERE  A.age > ALL (SELECT B.age   FROM   B

                    WHERE  B. name = "arun");

a.            4
b.            3
c.             0
d.            1

Answer: B

Concept: Value of A.age should be greater than all the values return by the execution of subquery. When we look at table B then we found that there is no name as arun which is mentioned in the subquery. So this subquery will return NULL. So in this case condition become true for all the rows of table A. Hence correct answer is 3.

More questions will be updated soon....!



addressing modes types,1,advance-java,2,aktu entrance exam,1,aktu exam schedule,1,ASP,1,bare machine,1,base register and limit register,1,C Programming,18,C Plus Plus,1,c programming notes for gate,18,C programming Tutorials,8,Cache Memory,1,Childcare,1,CJ,1,Cloud Computing,1,CN,4,Computer Architecture,2,Computer architecture based questions for gate exam,11,Computer architecture Tutorials,1,Computer Network,3,Computer Network Study Material,2,Computer network study material for gate,1,Computer Networks,10,Computer networks gate questions with answer,3,computer networks notes,1,computer networks tutorial,1,Computer Science Study Material for Gate,13,computer science study material for gate exam,17,contiguous memory allocation,2,Core Java,3,cyber crime report,1,Cyber crime status,1,cybercrime and security,1,cybercrime examples,1,data communications and networking,1,Data Structure,2,Data Structure Questions,1,Data Transmission Architecture,1,Data Transmission in wsn,1,database normalization,1,dbmas study material,1,DBMS,8,dbms gate questions with answer,1,dbms multiple choice questions with answers for gate,1,dbms question paper,1,DE,1,Digital Electronics,1,DS,4,Electroencephalogram,1,ER diagram Tutorial,1,Gate 2017,3,Gate 2017 Admit card,1,GATE 2020,1,gate cse c programming questions,10,gate cse study material,2,gate cse syllabus,2,gate practice set,7,gate questions on c programming,10,gate study material for computer science,14,Gate study material for computer science 2017,1,gate study material for cse,46,General,3,HCL Aptitude Test,1,HR Interview Questions,1,HTML,4,Important Date of Gate 2017 Exam,1,Information Security Policy,1,internal and external fragmentation,1,Java Tutorials,3,JDBC,2,JDBC Tutorial,1,JS,1,memory fragmentation,1,memory management,1,memory management questions and answer in os,1,Motivational,4,NCER,1,Numerical Techniques Lab,1,OOT,1,Operating System,6,Operating System Gate Questions,2,Operating System Objective Questions,4,Operating System Questions Bank,1,operating system study material for gate exam,11,operating system tutorial notes,8,Operating System tutorials resident monitor,1,page swapping,1,paged memory allocation,1,paged memory allocation in operating system,1,Regression testing,1,relocation register,1,routing table,1,Software Engineering,10,Software Engineering baes study material for gate,1,Software Quality Assurance,3,software verification methods,1,Stack,1,Study Material for gate Computer Science,5,swapping in memory management,1,swapping in operating system,1,TCS Code Vita,1,TCS Interview Questions,1,Technical Interview,1,Technical Questions from DBMS,1,Tips to Learn Coding,1,UML,1,Virtualization,1,What is process control block ?,1,what is software testing?,1,Wireless Sensor Network,3,worst fit algorithm for memory allocation,1,XML,2,
Computer Science Junction: DBMS multiple choice questions with answers for gate
DBMS multiple choice questions with answers for gate
In this tutorial some dbms multiple choice questions with answers for gate are given for the practice. These questions are asked in previous year GATE Exam. This tutorial covers the dbms gate questions . This Tutorial also covers the questions based on sql queries for practice with answers
Computer Science Junction
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share. STEP 2: Click the link you shared to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy