SQL

Question 1

A relational database contains two tables Student and Performance as shown below:

The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together from the primary key. Consider the SQL query given below:

SELECT S.Student_name, sum (P.Marks)
       FROM Student S, Performance P
       WHERE P.Marks > 84
       GROUP BY S.Student_name;

The number of rows returned by the above SQL query is _____.

A
0
B
9
C
7
D
5
       Database-Management-System       SQL       GATE 2019
Question 1 Explanation: 
(Executed under Oracle Express Edition)
SQL> SELECT S.Student_name,sum(P.Marks)
2 FROM Student S,Performance P
3 WHERE P.Marks>84
4 GROUP BY S.Student_name;
Question 2

Consider the following two tables and four queries in SQL.

     Book (isbn, bname), Stock (isbn, copies)
Query 1:    SELECT B.isbn, S.copies
            FROM Book B INNER JOIN Stock S
            ON B.isbn = S.isbn;

Query 2:    SELECT B.isbn, S.copies
            FROM Book B LEFT OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Query 3:    SELECT B.isbn, S.copies
            FROM Book B RIGHT OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Query 4:    SELECT B.isbn, S.copies
            FROM Book B FULL OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

A
Query 1
B
Query 2
C
Query 3
D
Query 4
       Database-Management-System       SQL       GATE 2018
Question 2 Explanation: 
Given two tables are,
Book (isbn, bname)
Stock (isbn, copies)
isbn is a primary key of Book and isbn is a foreign key of stock referring to Book table.
For example:

Query 1:
INNER JOIN keyword selects records that have matching values in both tables (Book and Stock).

So, the result of Query 1 is,

Query 2:
The LEFT OUTER JOIN keyword returns all records from the left table (Book) and the matched records from the right table (Stock).
The result is NULL from the right side, if there is no match.

So, the result of Query 2 is,

Query 3:
The RIGHT OUTER JOIN keyword returns all records from the right table (Stock), and the matched records from the left table(BOOK).
The result is NULL from the left side, when there is no match.


Query 4:
The FULL OUTER JOIN keyword return all records when there is a match in either left (Book) or right (Stock) table records.

So, the result of Query 4 is,

Therefore, from the result of above four queries, a superset of the outputs of the Query 1, Query 2 and Query 3 is Query 4.
Note:
If we take isbn as a primary key in both the tables Book and Stock and foreign key, in one of the tables then also will get option (D) as the answer.
Question 3

Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

The output of executing the SQL query is ___________.

A
2.6
B
2.7
C
2.8
D
2.9
       Database-Management-System       SQL       GATE 2017 [Set-1]
Question 3 Explanation: 
The given query is

⇾ We start evaluating from the inner query.
The inner query forms DeptName wise groups and counts the DeptName wise EmpIds.
⇾ In inner query DeptName, Count(EmpId) is the alias name DeptName, Num.
So, the output of the inner query is,

The outer query will find the
Avg(Num) = (4+3+3+2+1)/5 = 2.6
Question 4

Consider the following database table named top_scorer.

SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals > ALL (SELECT tb.goals
                  FROM top_scorer AS tb
                  WHERE tb.country = 'Spain')
AND ta.goals > ANY (SELECT tc.goals
                  FROM top_scorer AS tc
                  WHERE tc.country = 'Germany')

The number of tuples returned by the above SQL query is _____.

A
7
B
8
C
9
D
10
       Database-Management-System       SQL       GATE 2017 [Set-2]
Question 4 Explanation: 

In the given database table top_scorer no players are there from ‘Spain’.
So, the query (1) results 0 and ALL (empty) is always TRUE.
The query (2) selects the goals of the players those who are belongs to ‘Germany’.
So, it results in ANY (16, 14, 11, 10).
So, the outer most query results the player names from top_scorer, who have more goals.
Since, the minimum goal by the ‘Germany’ player is 10, it returns the following 7 rows.
Question 5

Consider the following database table named water_schemes :

The number of tuples returned by the following SQL query is _________.

with total(name, capacity) as
   select district_name, sum(capacity)
   from water_schemes
   group by district_name
with total_avg(capacity) as
   select avg(capacity)
   from total
select name
   from total, total_avg
   where total.capacity ≥ total_avg.capacity
A
2
B
3
C
4
D
5
       Database-Management-System       SQL       GATE 2016 [Set-2]
Question 5 Explanation: 
• The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.
The name assigned to the sub-query is treated as though it was an inline view or table.
• First group by district name is performed and total capacities are obtained as following:

• Then average capacity is computed,
Average Capacity = (20 + 40 + 30 + 10)/4
= 100/4
= 25
• Finally, 3rd query will be executed and it's tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25.
• Then average capacity is computed,
Average Capacity = (20 + 40 + 30 + 10)/4
= 100/4
= 25
• Finally, 3rd query will be executed and it's tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25.
Question 6

SELECT operation in SQL is equivalent to

A
the selection operation in relational algebra
B
the selection operation in relational algebra, except that SELECT in SQL retains duplicates
C
the projection operation in relational algebra
D
the projection operation in relational algebra, except that SELECT in SQL retains duplicates
       Database-Management-System       SQL       GATE 2015 [Set-1]
Question 6 Explanation: 
SELECT operation in SQL perform vertical partitioning which is performed by projection operation in relational calculus but SQL is multi sets; hence (D).
Question 7

Consider the following relations:

Consider the following SQL query.

SELECT S. Student_Name, sum(P.Marks)
     FROM Student S, Performance P
     WHERE S.Roll_No = P.Roll_No
     GROUP BY S.Student_Name

The number of rows that will be returned by the SQL query is _________.

A
2
B
3
C
4
D
5
       Database-Management-System       SQL       GATE 2015 [Set-1]
Question 7 Explanation: 
Output table is
Question 8

Consider the following relation

  Cinema (theater, address, capacity)

Which of the following options will be needed at the end of the SQL query

SELECT P1. address
FROM Cinema P1

Such that it always finds the addresses of theaters with maximum capacity?

A
WHERE P1.capacity >= All (select P2.capacity from Cinema P2)
B
WHERE P1.capacity >= Any (select P2.capacity from Cinema P2)
C
WHERE P1.capacity > All (select max(P2.capacity) from Cinema P2)
D
WHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2)
       Database-Management-System       SQL       GATE 2015 [Set-3]
Question 8 Explanation: 
Inner query collects capacities of all the theaters and in outer query we are filtering the tuples with the condition “capacity >= All”.
So the theaters which are having maximum capacity will satisfy the condition.
Question 9

Given the following statements:

    S1: A foreign key declaration 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)

Which one of the following statements is CORRECT?

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.
       Database-Management-System       SQL       GATE 2014 [Set-1]
Question 9 Explanation: 
S1: False
Using a check constraint, we can have the same effect as foreign key while adding elements to the child table. But while deleting elements from the parent table the referential integrity constraint is no longer valid. So, a check constraint cannot replace a foreign key.
S2: False:
Foreign key in one table should be defined as a primary key in other table. In above table definition, table S has a foreign key that refers to field ‘a’ of R. The field ‘a’ in table S is part of the primary key and part of the key cannot be declared as a foreign key.
Question 10

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)

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL> 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 outcome?

A
It executes but does not give the correct result.
B
It executes and gives the correct result.
C
It 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.
       Database-Management-System       SQL       GATE 2014 [Set-1]
Question 10 Explanation: 
The given SQL query will display the last names and hire-dates of all latest hires in their respective departments in the location ID 1700. So, correct option is (B).
Question 11

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
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
       Database-Management-System       SQL       GATE 2014 [Set-2]
Question 11 Explanation: 
Multiplicity of duplicate tuples will be distributed when there is a match between R.a and S.a; and for that match, S.a's value is repeated in each cases except the third case. So, the output of query given in the question matches with the output of (C).
Question 12

Consider the following relational schema:

  employee(empId, empName, empDept)
  customer(custId, custName, salesRepId, rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
        FROM employee E
        WHERE NOT EXISTS (SELECT custId
                         FROM customer C
                         WHERE C.salesRepId = E.empId
                         AND C.rating <> `GOOD`); 
A
Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
B
Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
C
Names of all the employees with none of their customers having a ‘GOOD’ rating.
D
Names of all the employees with all their customers having a ‘GOOD’ rating.
       Database-Management-System       SQL       GATE 2014 [Set-3]
Question 12 Explanation: 

The inner query i.e., ② represents all customers having other than ‘GOOD’ while the entire query represents name of all employees with all their customers having a ‘good rating’.
Question 13

Which of the  following statements are TRUE about an SQL query?

    P:An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause.
    Q:An SQL query can contain a HAVING clause even if it has a GROUP BY clause.
    R: All attributes used in the GROUP BY clause must appear in the SELECT clause.
    S: Not 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
       Database-Management-System       SQL       GATE 2012
Question 13 Explanation: 
The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. The attributes used in GROUP BY clause must present in SELECT statement.
The HAVING Clause enables you to specify conditions that filter which group results appear in the results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. So, we cannot use HAVING clause without GROUP BY clause.
Question 14

Consider the following relations A, B, C.

How many tuples does the result of the following SQL query contain?

SELECT A.id 
FROM A 
WHERE A.age > ALL (SELECT B.age 
                   FROM B 
                   WHERE B. name = "arun")
A
4
B
3
C
0
D
1
       Database-Management-System       SQL       GATE 2012
Question 14 Explanation: 

First query (2) will be executed and 0 (no) rows will be selected because in relation B there is no Name ‘Arun’.
The outer query (1) results the follow and that will be the result of entire query now. (Because inner query returns 0 rows).
Question 15

Database table by name 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, Loan_Amount FROM Loan_Records) AS T 
);
A
3
B
9
C
5
D
6
       Database-Management-System       SQL       GATE 2011
Question 15 Explanation: 
Question 16

Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?

SELECT Y FROM T WHERE X=7;
A
127
B
255
C
129
D
257
       Database-Management-System       SQL       GATE 2011
Question 16 Explanation: 
Question 17

A relational schema for a train reservation database is given below. 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 pids are returned by the following SQL query for the above instance of the tables?

SELECT pid
FROM Reservation ,
WHERE class ‘AC’ AND
    EXISTS (SELECT *
       FROM Passenger
       WHERE age > 65 AND
       Passenger. pid = Reservation.pid)
A
1, 0
B
1, 2
C
1, 3
D
1, 5
       Database-Management-System       SQL       GATE 2010
Question 17 Explanation: 
Passenger:

― 1, 3 Pids are returned
Question 18

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 IN (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?

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.
       Database-Management-System       SQL       GATE 2009
Question 18 Explanation: 



If we execute the given query the output will be S3 and S4 i.e., names of all suppliers who didn’t supply blue parts which is option (A).
Option (D) says names of suppliers who didn’t supply only blue parts that means, supplier should supply all other parts for sure and shouldn’t supply blue part.
Question 19

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 broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.

Query1:
  select A.customer, count(B.customer)
  from account A, account B
  where A.balance <=B.balance
  group by A.customer

Query2:
  select A.customer, 1+count(B.customer)
  from account A, account B
  where A.balance < B.balance
  group by A.customer

Consider these statements about Query1 and Query2.

1. Query1 will produce the same row set as Query2 for 
   some but not all databases.
2. Both Query1 and Query2 are correct implementation 
   of the specification
3. Query1 is a correct implementation of the specification
   but Query2 is not
4. Neither Query1 nor Query2 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?

A
2 and 5
B
1 and 3
C
1 and 4
D
3 and 5
       Database-Management-System       SQL       GATE 2006
Question 19 Explanation: 
Query 1 & 2 gives the same output for all not all data based its true because the salaries may be distinct variables. Statement 1 is true.
The customer with largest balance gets rank 1. Ties are broken with ranks are skipped.
So, both queries may doesn’t give same output. Statement 4 is correct.
Question 20

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 following statements is correct?

A
All queries return identical row sets for any database.
B
Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.
C
There exist databases for which Query3 returns strictly fewer rows than Query2.
D
There exist databases for which Query4 will encounter an integrity violation at runtime.
       Database-Management-System       SQL       GATE 2006
Question 20 Explanation: 
Consider Table examples as:

Query1 : Output
abcd
abcd
PQRS
Query 2 : Output
abcd
PQRS
Query 3 : Output
abcd
PQRS
Query 4 : Output
abcd
PQRS
Query 2 & Query 4 gives same results but Query 1 & Query 3 gives different results.
Question 21

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 10 micro-seconds. Which of the following statements is correct?

A
Plan 1 and Plan 2 will not output identical row sets for all databases.
B
A course may be listed more than once in the output of Plan 1 for some databases.
C
For x = 5000, Plan 1 executes faster than Plan 2 for all databases.
D
For x = 9000, Plan I executes slower than Plan 2 for all databases.
       Database-Management-System       SQL       GATE 2006
Question 21 Explanation: 
Both plans are require the tables such as courses and enrolled to access the disks takes same time for both plans.
While analyze the plan1 and plan2 does lesser number of comparisons compared to plan1.
i) The join table consists of two tables will have more rows. So comparisons are needed to find amount greater than x.
ii) Join operation consists of more number of comparisons as the second table will have more rows in plan2 compared to plan1.
Question 22
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 list?
     select title
     from book as B
     where (select count(*)
        from book as T
        where T.price > B.price) < 5 
A
Titles of the four most expensive books
B
Title of the fifth most inexpensive book
C
Title of the fifth most expensive book
D
Titles of the five most expensive books
       Database-Management-System       SQL       GATE 2005
Question 22 Explanation: 
Which results titles of the five most expensive books.
The where clause of outer query will be true for 5 most expensive books.
Question 23

The employee information in a company is stored in the relation

Employee (name, sex, salary, deptName)

Consider the following SQL query

Select deptName From Employee Where sex = 'M' Group by deptName Having avg (salary) > (select avg (salary) from Employee)

It returns the names of the department in which

A
the average salary is more than the average salary in the company
B
the average salary of male employees is more than the average salary of all male employees in the company
C
the average salary of male employees is more than the average salary of employees in the same department
D
the average salary of male employees is more than the average salary in the company
       Database-Management-System       SQL       GATE 2004
Question 23 Explanation: 
Group by (avg(salary) > (select avg (salary) from employee))
This results the employees who having the salary more than the average salary.
Sex = M
Selects the Male employees whose salary is more than the average salary in the company.
Question 24

Consider the following SQL query

   select distinct al, a2,........., an
   from rl, r2,........, rm
   where P 

For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?

A
B
C
D
       Database-Management-System       SQL       GATE 2003
Question 24 Explanation: 
If we want to get distinct elements then we need to perform cross product in between the relations r1, r2, .... rm.
Question 25

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?

A
Names of students who have got an A grade in all courses taught by Korth
B
Names of students who have got an A grade in all courses
C
Names of students who have got an A grade in at least one of the courses taught by Korth
D
in none of the above
       Database-Management-System       SQL       GATE 2003
Question 25 Explanation: 
The query results a names of students who got an A grade in at least one of the courses taught by korth.
Question 26

Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.

create table geq
(  Ib integer not null
   ub integer not null
   primary key 1b
   foreign key (ub) references geq on delete cascade ) 

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

A
A tuple (z,w) with z > y is deleted
B
A tuple (z,w) with z > x is deleted
C
A tuple (z,w) with w < x is deleted
D
The deletion of (x,y) is prohibited
       Database-Management-System       SQL       GATE 2001
Question 27

Given relations r(w, x) and s(y, z), the result of

    select distinct w, x
    from r, s  

is guaranteed to be same as r, provided

A
r has no duplicates and s is non-empty
B
r and s have no duplicates
C
s has no duplicates and r is non-empty
D
r and s have the same number of tuples
       Database-Management-System       SQL       GATE 2000
Question 27 Explanation: 
r has no duplicate, if r can have duplicates it can be remove in the final state. s in non-empty if s is empty then r*s becomes empty.
Question 28

In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent?

A
x = 5 not AND (not (x = 5)
B
x = 5 AND x > 4 and x < 6, where x is an integer
C
x ≠ 5 AND not (x = 5)
D
None of the above
       Database-Management-System       SQL       GATE 2000
Question 28 Explanation: 
For all values less than five, x<5 is true and if x=5 then it is false.
Question 29

Consider a bank database with only one relation
transaction (transno, acctno, date, amount)
The amount attribute value is positive for deposits and negative for withdrawals.

(a) Define an SQL view TP containing the information.
(acctno, T1.date, T2.amount)
for every pair of transactions T1, T2 such that T1 and T2 are transaction on the same account and the date of T2 is ≤ the date of T1.

(b) Using only the above view TP, write a query to find for each account the minimum balance it ever reached (not including the 0 balance when the account is created). Assume there is at most one transaction per day on each account, and each account has had atleast one transaction since it was created. To simply your query, break it up into 2 steps by defining an intermediate view V.

A
Theory Explanation is given below.
       Database-Management-System       SQL       GATE 2000
Question 30

Which of the following is/are correct?

A
An SQL query automatically eliminates duplicates
B
An SQL query will not work if there are no indexes on the relations
C
SQL permits attribute names to be repeated in the same relation
D
None of the above
       Database-Management-System       SQL       GATE 1999
Question 30 Explanation: 
→ SQL won't remove duplicates like relational algebra projection, we have to remove it explicitly by distinct.
→ If there are no indexes on the relation SQL, then also it works.
→ SQL does not permit 2 attributes to have same name in a relation.
Question 31

Consider a relational database containing the following schemas.

The primary key of each table is indicated by underlying the constituent fields.

SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno = c.sno AND
              Cost > (SELECT AVG (cost)
                      FROM Catalogue
                      WHERE pno = ‘P4’
                      GROUP BY pno);

The number of rows returned by the above SQL query is

A
0
B
5
C
4
D
2
       Database-Management-System       SQL       GATE 2020
Question 31 Explanation: 
The inner query “select avg(cost) from catalogue where pno='P4' group by pno;” returns:
AVG(COST)
------------
225
The outer query “select s.sno, s.sname from suppliers s, catalogue c where s.sno=c.sno” returns:
SNO SNAME
----------------------------------------
S1 M/s Royal furniture
S1 M/s Royal furniture
S1 M/s Royal furniture
S2 M/s Balaji furniture
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
So, the final result of the query is:
SN SNAME
----------------------------------------
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
Therefore, 4 rows will be returned by the query.
Question 32

Consider the following relational schema:

Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-phone)
Enrolment(school-id, sch-roll-no, erollno, examname)
ExamResult(erollno, examname, marks)

What does the following SQL query output?

SELECT  sch-name, COUNT (*)
FROM    School C, Enrolment E, ExamResult R
WHERE   E.school-id = C.school-id
        AND
        E.examname = R.examname AND E.erollno = R.erollno
        AND
        R.marks = 100 AND S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                HAVING COUNT (*) > 200)
GROUP By school-id
A
for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students
B
for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students
C
for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam
D
nothing; the query has a syntax error
       Database-Management-System       SQL       GATE 2008-IT
Question 32 Explanation: 
If select clause consist of aggregate and non-aggregate columns, all non-aggregate columns in the select clause must appear in Group By clause. But in this Group By clause consists school-id instead of school-name.
Question 33

Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available

What is the output of the following SQL query?

select D.dname
from Drivers D
where D.did in (
                 select R.did
                 from Cars C, Reserves R
                 where R.cid = C.cid and C.colour = 'red'
                 intersect
                 select R.did
                 from Cars C, Reserves R
                 where R.cid = C.cid and C.colour = 'green'
                )
A
Karthikeyan, Boris
B
Sachin, Salman
C
Karthikeyan, Boris, Sachin
D
Schumacher, Senna
       Database-Management-System       SQL       GATE 2006-IT
Question 33 Explanation: 
For colour = "Red"
did = {22, 22, 31, 31, 64}
For colour = "Green"
did = {22, 31, 74}
Intersection of Red and Green will be = {22, 31}, which is Karthikeyan and Boris.
Question 34

Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available

Let n be the number of comparisons performed when the above SQL query is optimally executed. If linear search is used to locate a tuple in a relation using primary key, then n lies in the range

A
36 - 40
B
44 - 48
C
60 - 64
D
100 - 104
       Database-Management-System       SQL       GATE 2006-IT
Question 34 Explanation: 
(4) for taking red cars with (20) comparisons for did and (4) for finding green cars with (10) for did.
red did : 22, 31, 64
green did : 22, 31, 74
(6) for intersection
(1) for searching 22 in driver relation, and (3) for searching 31.
Total: 38 + 6 + 4 = 48
Question 35

A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema:
salesinfo = (salespersonid, totalsales, commission)
In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula:
If commission < = 50000, enhance it by 2% If 50000 < commission < = 100000, enhance it by 4% If commission > 100000, enhance it by 6%
The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows:

 T1:	
Update salesinfo
Set commission = commission * 1.02
Where commission < = 50000;
 
 T2:	
Update salesinfo
Set commission = commission * 1.04
Where commission > 50000 and commission is < = 100000;
 
 T3:	
Update salesinfo
Set commission = commission * 1.06
Where commission > 100000;  
Which of the following options of running these transactions will update the commission of all salespersons correctly

A
Execute T1 followed by T2 followed by T3
B
Execute T2, followed by T3; T1 running concurrently throughout
C
Execute T3 followed by T2; T1 running concurrently throughout
D
Execute T3 followed by T2 followed by T1
       Database-Management-System       SQL       GATE 2005-IT
Question 35 Explanation: 
T3 followed by T2 followed by T1 will be the correct execution sequence.
In other cases some people will get two times increment, for example,
if we have T1 followed by T2 and if initial commission is 49500, then he is belonging to <50000.
Hence, 49500 * 1.02 = 50490.
Now again he is eligible for second category. So, he will get again increment as,
50490 * 1.04 = 52509.6
So he will get increment two times, but he is eligible for only one slab of commission.
Question 36

A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below:

Table: Student
ROLL	    NAME	HOSTEL	MARKS
1798	Manoj Rathod	  7	 95
2154	Soumic Banerjee	  5	 68
2369	Gumma Reddy	  7	 86
2581	Pradeep Pendse	  6	 92
2643	Suhas Kulkarni	  5	 78
2711	Nitin Kadam	  8	 72
2872	Kiran Vora	  5	 92
2926	Manoj Kunkalikar  5	 94
2959	Hemant Karkhanis  7	 88
3125	Rajesh Doshi	  5	 82 

Table: hobby
ROLL	HOBBYNAME
1798	chess
1798	music
2154	music
2369	swimming
2581	cricket
2643	chess
2643	hockey
2711	volleyball
2872	football
2926	cricket
2959	photography
3125	music
3125	chess 

The following SQL query is executed on the above tables:

select hostel
from student natural join hobby
where marks >= 75 and roll between 2000 and 3000; 

Relations S and H with the same schema as those of these two tables respectively contain the same information as tuples. A new relation S’ is obtained by the following relational algebra operation:

S’ = ∏hostel ((σs.roll = H.rollmarks > 75 and roll > 2000 and roll < 3000 (S)) X (H))  

The difference between the number of rows output by the SQL statement and the number of tuples in S’ is

A
6
B
4
C
2
D
0
       Database-Management-System       SQL       GATE 2005-IT
Question 36 Explanation: 
SQL query will return:

Total 7 rows are selected.
Where in relational algebra only distinct values of hostels are selected,i.e., 5, 6, 7 (3 rows).
∴ Answer is 7 - 3 = 4
Question 37

In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items along with the stock-level of these items.
Supply = (supplierid, itemcode)
Inventory = (itemcode, warehouse, stocklevel)
For a specific information required by the management, following SQL query has been written

Select distinct STMP.supplierid
From Supply as STMP
Where not unique (Select ITMP.supplierid
                  From Inventory, Supply as ITMP
                  Where STMP.supplierid = ITMP.supplierid
                  And ITMP.itemcode = Inventory.itemcode
                  And Inventory.warehouse = 'Nagpur'); 
For the warehouse at Nagpur, this query will find all suppliers who

A
do not supply any item
B
supply exactly one item
C
supply one or more items
D
supply two or more items
       Database-Management-System       SQL       GATE 2005-IT
Question 37 Explanation: 
Here (not unique) in nested query ensures that only for those suppliers it return True which supplies more than 1 item in which case supplier id in inner query will be repeated for that supplier. Hence, the answer is (D) which supply two or more items.
Question 38

A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:

Insert into department values (1, 'Mathematics')
Insert into department values (2, 'Physics')
Insert into student values (l, 'Navin', 1)
Insert into student values (2, 'Mukesh', 2)
Insert into student values (3, 'Gita', 1)  
How many rows and columns will be retrieved by the following SQL statement?
Select * from student, department

A
0 row and 4 columns
B
3 rows and 4 columns
C
3 rows and 5 columns
D
6 rows and 5 columns
       Database-Management-System       SQL       GATE 2004-IT
Question 38 Explanation: 
Simply, cartesian product of two tables will result
rows = 3 * 2 = 6
Columns = 3 + 2 = 5
Question 39

A table T1 in a relational database has the following rows and columns:

 roll no.	 marks
    1	          10
    2	          20
    3	          30
    4	         Null 
The following sequence of SQL statements was successfully executed on table T1.
Update T1 set marks = marks + 5
Select avg(marks) from T1 
What is the output of the select statement?

A
18.75
B
20
C
25
D
NULL
       Database-Management-System       SQL       GATE 2004-IT
Question 39 Explanation: 
Update on null values gives null. Now, avg function ignores null values. So, here avg will be
(15+25+35)/3 = 25
Question 40
Consider the set of relations given 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 =Sriram
AND Courses.Course_number = Grades.Course_number
AND Grades.Grade = A
Which of the following sets is computed by the above query?
A
Names of Students who have got an A grade in all courses taught by Sriram
B
Names of Students who have got an A grade in all courses
C
Names of Students who have got an A grade in at least one of the courses taught by Sriram
D
None of the above
       Database-Management-System       SQL       ISRO-2018
Question 40 Explanation: 
→ The query results a names of students who got an A grade in at least one of the courses taught by Sriram.
→ The above query they are using AND command, it means it satisfy all conditions.
Question 41
Given relations R(w,x) and S(y,z), the result of SELECT DISTINCT w, x FROM R, S Is guaranteed to be the same as R, if
A
R has no duplicates and S is non-empty
B
R and S have no duplicates
C
S has no duplicates and R is non-empty
D
R and S have the same number of tuples
       Database-Management-System       SQL       ISRO-2018
Question 41 Explanation: 
→ R has no duplicate if R can have duplicates it can be removed in the final state.
→ S in non-empty if S is empty then R*S becomes empty.
Question 42
Database-Management-System
A
Physical Data Independence
B
Logical Data Independence
C
Both (a) and (b)
D
None of the above
       Database-Management-System       SQL       ISRO-2018
Question 42 Explanation: 
Logical data independence:
The ability to change the Conceptual (Logical) schema without changing the External schema (User View) is called logical data independence. For example, the addition or removal of new entities, attributes, or relationships to the conceptual schema or having to rewrite existing application programs.

Physical data independence:
The ability to change the physical schema without changing the logical schema is called physical data independence. For example, a change to the internal schema, such as using different file organization or storage structures, storage devices, or indexing strategy, should be possible without having to change the conceptual or external schemas.
Note: Immunity is when data at one layer is changed, it does not affect the data at another level.
Question 43
Database table by name overtime_allowance is given below

What is the output of the following SQL query?
select count(*) from ((select Employee, Department from Overtime_allowance) as S
natural join (select Department, OT_allowance from Overtime_allowance) as T);
A
16
B
4
C
8
D
None of the above
       Database-Management-System       SQL       ISRO-2017 May
Question 43 Explanation: 

Common attributes in both the table column are the department. So, we apply natural join, it will give the output as common tuples in both the table S and R.
Question 44
Consider the schema
Sailors(sid, sname, rating, age) with the following data

For the query
SELECT S.rating, AVG(S.age) AS average FROM Sailors S
Where S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT(*) FROM Sailors S2 where S.rating = S2.rating)
The number of rows returned is
A
6
B
5
C
4
D
3
       Database-Management-System       SQL       ISRO-2017 December
Question 44 Explanation: 


Question 45
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 list?
select title
from book as B
where (select count(*)
from book as T
where T.price>B.price)<5
A
Titles of the four most expensive books
B
Title of the fifth most inexpensive book
C
Title of the fifth most expensive book
D
Titles of the five most expensive books
       Database-Management-System       SQL       ISRO-2016
Question 45 Explanation: 
→ Which results titles of the five most expensive books.
→ The where clause of outer query will be true for 5 most expensive books.
Question 46
Consider the following relational schema:
Suppliers (sid:integer, sname:string, sadress:string)
Parts (pid:integer, pname:string, pcolor:string)
Catalog (sid:integer, pid:integer, pcost:real)
What is the result of the following query?
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sid = Catalog.pid)
MINUS
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sname <> 'sachin' and Suppliers.sid = Catalog.sid)
A
pid of Parts supplied by all except sachin
B
pid of Parts supplied only by sachin
C
pid of Parts available in catalog supplied by sachin
D
pid of Parts available in catalogs supplied by all except sachin
       Database-Management-System       SQL       ISRO CS 2013
Question 46 Explanation: 
→SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sid = Catalog.pid The above query will gives all pids of both Catalog and Supplier .
→SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sname <> 'sachin' and Suppliers.sid = Catalog.sid
*The above query will gives the pids of all parts which are supplied by any other supplier other than Sachin.
→The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement
→The entire query will get the pids which are supplied by only Sachin.
Question 47

Consider a relation book(title, price) which contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list ?

    SELECT title
    FROM book AS B
    WHERE(SELECT COUNT(*)  FROM book AS T WHERE T.price > B.price) < 7
A
Titles of the six most expensive books.
B
Title of the sixth most expensive books.
C
Titles of the seven most expensive books.
D
Title of the seventh most expensive books.
       Database-Management-System       SQL       UGC-NET CS 2018 JUNE Paper-2
Question 47 Explanation: 
→ SQL query, which results titles of the 7 most expensive books.
→ The where clause of outer query will be true for 7 most expensive books.
Note: All aggregate functions except count(*) ignore NULL values in their input collection.
Question 48
Which type of statement can execute parameterized queries?
A
PreparedStatement
B
Parameterized Statement
C
ParameterizedStatement and CallableStatement
D
All kinds of Statements
       Database-Management-System       SQL       Nielit Scientist-B IT 4-12-2016
Question 48 Explanation: 
The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled.
As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.
Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.
Question 49

Consider the following tables (relations):

Primary keys in the tables are shown using Underline. Now, Consider the following query:

SELECT S.Name, Sum (P.Marks)
FROM Students S, Performance P
WHERE S.Roll-No = P.Roll-No
GROUP BY S.Name

The number of rows returned by the above query is

A
3
B
2
C
0
D
1
       Database-Management-System       SQL       UGC-NET CS 2018 DEC Paper-2
Question 49 Explanation: 
The following table is returned as the result of executing FROM and WHERE commands in given query.

Since in query “GROUP BY S.Name” is given so, firstly Group names having same“Name” value and and then perform SUM( ) operation on those values. The below table is returned as the result of given query :

So, the number of rows returned by given query are 2.
Question 50
The data manipulation language(DML)
A
refers to data using physical addresses
B
cannot interface with high-level programming language
C
is used to define the physical characteristics of each record
D
none of these
       Database-Management-System       SQL       NieLit STA 2016 March 2016
Question 50 Explanation: 
● A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.
● A DML is often a sublanguage of a broader database language such as SQL, with the DML comprising some of the operators in the language.
● Read-only selecting of data is sometimes distinguished as being part of a separate data query language (DQL), but it is closely related and sometimes also considered a component of a DML. some operators may perform both selecting (reading) and writing.
● A popular data manipulation language is that of Structured Query Language (SQL), which is used to retrieve and manipulate data in a relational database.
● Other forms of DML are those used by IMS/DLI, CODASYL databases, such as IDMS and others.
Question 51

________ command is used to remove a relation from an SQL database.

A
Update table
B
Remove table
C
Delete table
D
Drop table
       Database-Management-System       SQL       UGC-NET CS 2018 DEC Paper-2
Question 51 Explanation: 
Update table :
Update table command is used to update the content of the table.
Delete table :
Delete table command is used to delete the data stored in the table. When we use Delete command table is not deleted only data stored in the table is deleted.
Drop table :
Using table command we can delete a table/relation.
Question 52
If an SQl query involves NOT,AND,OR with no parenthesis
A
NOT will be evaluated first; AND will be evaluated second;OR will be evaluated last
B
NOT will be evaluated first; OR will be evaluated second;AND will be evaluated last
C
AND will be evaluated first; OR will be evaluated second;NOT will be evaluated last
D
The order of occurrence determines the order of evaluation
       Database-Management-System       SQL       Nielit Scientist-B IT 22-07-2017
Question 52 Explanation: 
Table: SQL Operator Precedence

The above table shows the SQL operator precedence.
Question 53
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 IN (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?
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 non blue parts.
D
Find the names of all suppliers who have not supplied only non-blue parts.
       Database-Management-System       SQL       ISRO CS 2015
Question 53 Explanation: 


If we execute the given query the output will be S3 and S4 i.e., names of all suppliers who didn’t supply blue parts which is option (A).
Option (D) says names of suppliers who didn’t supply only blue parts that means, supplier should supply all other parts for sure and shouldn’t supply blue part.
Question 54
Consider the following schema:
Emp (Empcode, Name, Sex, Salary, Deptt)
A simple SQL query is executed as follows:
SELECT Deptt FROM Emp WHERE sex = 'M' GROUP by Dept Having avg (Salary) > {select avg (Salary) from Emp}
The output will be
A
Average salary of male employee is the average salary of the organization
B
Average salary of male employee is less than the average salary of the organization
C
Average salary of male employee is equal to the average salary of the organization
D
Average salary of male employee is more than the average salary of the organization
       Database-Management-System       SQL       ISRO CS 2015
Question 54 Explanation: 
Query-1: select avg (Salary) from Emp:
This query will give average salary of all the all employes.
Query-2:SELECT Deptt FROM Emp WHERE sex = 'M' GROUP by Dept Having avg (Salary)
Average salary of employee who is male where we grouping by department
Query-2 > Query-1
Average salary of male employee is more than the average salary of the organization
Question 55
'AS' clause is used in SQL for
A
Selection operation
B
rename operation
C
Join Operation
D
Projection Operation
       Database-Management-System       SQL       Nielit Scientist-B IT 22-07-2017
Question 55 Explanation: 
● AS is a keyword in SQL that allows you to rename a column or table using an alias.
● Syntax: SELECT column_name AS 'Alias' FROM table_name;
Question 56
Given relations R(w,x) and S(y,z) the result of SELECT DISTINCT w,x from R,S
A
R has no duplicates and S is non empty
B
R and S have no duplicates
C
S has no duplicates and R is non empty
D
R and S has the same number of tuples
       Database-Management-System       SQL       Nielit Scientific Assistance IT 15-10-2017
Question 56 Explanation: 
r has no duplicate, if r can have duplicates it can be remove in the final state. s in non-empty if s is empty then r*s becomes empty.
Question 57
Given the following two statements about SQL
(a) An SQL query can contain HAVING clause only if it has GROUP BY clause.
(b) In an SQL query “SELECT_FROM_WHERE_GROUP BY_HAVING”, HAVING is executed before WHERE.
Which of the following is correct?
A
(a) and (b) are true
B
(a) is true, (b) is false
C
(a) is false, (b) is true
D
(a) and (b) both are false
       Database-Management-System       SQL       KVS 22-12-2018 Part-B
Question 57 Explanation: 
→ The GROUP BY clause is a SQL command that is used to group rows that have the same values.
→ HAVING clause is used to filter summarized data or grouped data.
→ WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows
→ As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP → BY.
→ The order of execution of Clauses i.e FROM > WHERE > GROUP BY > HAVING > DISTINCT > SELECT > ORDER BY.
Question 58
Which of the following statements is incorrect?
A
Data definition languages is used by DBA and database designers to define schemas
B
Storage definition language is used to specify the internal schema.
C
Storage definition language is used to insert,delete and update data
D
Data definition languages is used to retrieve data from the database
       Database-Management-System       SQL       KVS 22-12-2018 Part-B
Question 58 Explanation: 
→ A database system provides a data definition language to specify the database schema and a data manipulation language to express database queries and updates.
→ Storage definition language is to specify the internal schema. This language may specify the mapping between two schemas.
→ A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.
Question 59
Given relations R(w,x) and S(y,z) the result of SELECT DISTINCT w,x from R<S
A
R has no duplicates and S is non empty
B
R and S have no duplicates
C
S has no duplicates and R is non empty
D
R and S has the same number of tuples
       Database-Management-System       SQL       Nielit Scientific Assistance CS 15-10-2017
Question 59 Explanation: 
r has no duplicate, if r can have duplicates it can be remove in the final state. s in non-empty if s is empty then r*s becomes empty.
Question 60
Table Employee has 10 records. It has a non-NULL SALARY column which is also
UNIQUE. The SQL statement
SELECT COUNT(*) FROM Employee WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEE);
A
10
B
9
C
5
D
0
       Database-Management-System       SQL       Nielit Scientific Assistance CS 15-10-2017
Question 60 Explanation: 
This query counts the number of employees who get more than the minimum salary. From the 10 employees, you need to exclude all those employees who are getting the minimum salary. Since the SALARY column is UNIQUE, only one employee will be getting the minimum salary.
Question 61
Consider the following EMP table and answer the question below:


Which of the following select statement should be executed if we need to display the average salary of employees who belongs to grade “E4”?
A
Select avg(salary) from EMP whose grade=”E4”;
B
Select avg(salary) from EMP having grade=”E4”;
C
Select avg(salary) from EMP group by grade where grade=”E4”;
D
Select avg(salary) from EMP group by grade having grade=”E4”;
       Database-Management-System       SQL       KVS DEC-2013
Question 61 Explanation: 
→ Condition specified in WHERE clause is used while fetching data (rows) from table, and data which doesn't pass the condition will not be fetched into result set,
→ HAVING clause is used to filter summarized data or grouped data.
→ In the question, we require average salary of employees whose grade is E4. So we require grouped data.
Question 62
The command used to see the fields of the table along with their datatypes in SQL is
A
Select fields from dual where table=”MANAGER”;
B
Select field_names, datatype from dual where table_name=”MANAGER”;
C
Desc MANAGER;
D
Select description from dual where table_name=”MANAGER”
       Database-Management-System       SQL       KVS DEC-2013
Question 62 Explanation: 
●Describes either the columns in a table or the current values, as well as the default values, for the stage properties for a table.
● DESCRIBE can be abbreviated to DESC.
Question 63
Which of the following statements is not true for views in SQL?
A
Select statement used in the view definition cannot include ORDER BY clause
B
A view derives its data from the base tables(s)
C
A view is updatable if it has been defined from a single relation
D
A view contains a copy of the data
       Database-Management-System       SQL       KVS DEC-2013
Question 63 Explanation: 
●In SQL, a view is a virtual table based on the result-set of an SQL statement.
● A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
● You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
Question 64
In order to add a new column to an existing table in SQL, we can use the command
A
MODIFY TABLE
B
EDIT TABLE
C
ALTER TABLE
D
ALTER COLUMNS
       Database-Management-System       SQL       KVS DEC-2013
Question 64 Explanation: 
→ The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
→ The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
→ To add a column in a table, use the following syntax:
→ ALTER TABLE table_name ADD column_name datatype;
Question 65
Which of the following statements is NOT true for Rollback statements in SQL?
A
All save points marked after the save points to which you rollbacked, are erased
B
It does not free any resources held by the transaction
C
The save point to which you rollback is not erased
D
The Rollback statement will erase all data modifications made from the start of the transaction to the savepoint
       Database-Management-System       SQL       KVS DEC-2013
Question 65 Explanation: 
It undoes some or all database changes made during the current transaction.
Question 66
What result set is returned from the following SQL query?
SELECT customer_name, telephone FROM customers
WHERE city IN('Jaipur','Delhi','Agra');
A
The cusomer_name of all customers who are not living IN Jaipur,Delhi OR Agra
B
The customer_name and telephone of all customers
C
The customer_name and telephone of all customers living IN either Jaipur,Delhi OR Agra
D
The customer_name and telephone of all customers living IN Jaipur,Delhi AND Agra
       Database-Management-System       SQL       KVS DEC-2017
Question 66 Explanation: 
Only logic here is, when we are using keyword IN there must be OR keyword.
Question 67

To add attributes to an existing relation __ commands is used:

A
Update table
B
Alter table
C
Change table
D
Add table
       Database-Management-System       SQL       JT(IT) 2016 PART-B Computer Science
Question 67 Explanation: 
ALTER TABLE: changing a relation schema.
SQL allows the owner of the database relation to change it by:
1. Adding one or more attributes to the relation.
2. Removing one or more attributes from the relation.
3. Adding one or more constraints to the relation.
4. Removing one or more constraints from the relation.
Question 68

A relation that is not of connected model but is made visible to a user as a virtual relation is called:

A
Table
B
Query
C
View
D
Joined relations
       Database-Management-System       SQL       JT(IT) 2016 PART-B Computer Science
Question 68 Explanation: 
→ In SQL, a view is a virtual table based on the result-set of an SQL statement.
→ A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
→ You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
CREATE VIEW Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note:
A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
Question 69
With SQL, how can you return the number of records in the "person's" table?
A
SELECT COUNT (*) FROM persons
B
SELECT COUNT () FROM persons
C
SELECT COLUMN () FROM persons
D
SELECT COLUMN (*) FROM Persons
       Database-Management-System       SQL       KVS DEC-2017
Question 69 Explanation: 
→ Normally we want print all records from database, we use SQL command is SELECT * from filename.
→ Suppose we want to return the number of records, it means count total number of records from relation(or table).
SELECT COUNT(*) FROM persons
Question 70
Which of the following is/are true with reference to ‘view’ in DBMS ?
(a) A ‘view’ is a special stored procedure executed when certain event occurs.
(b) A ‘view’ is a virtual table, which occurs after executing a pre-compiled query. code:
A
Only (a) is true
B
Only (b) is true
C
Both (a) and (b) are true
D
Neither (a) nor (b) are true
       Database-Management-System       SQL       UGC NET CS 2017 Nov- paper-2
Question 70 Explanation: 
VIEW is a virtual table based on the result set of a SQL statement.
→ In SQL, a view is a virtual table based on the result-set of an SQL statement.
→ A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
→ You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
CREATE VIEW Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
Question 71
In SQL, __________ is an Aggregate function.
A
SELECT
B
CREATE
C
AVG
D
MODIFY
       Database-Management-System       SQL       UGC NET CS 2017 Nov- paper-2
Question 71 Explanation: 
Aggregate function is AVG. The AVG() function returns the average value of a numeric column.
Question 72
__________ SQL command changes one or more fields in a record.
A
LOOK-UP
B
INSERT
C
MODIFY
D
CHANGE
       Database-Management-System       SQL       UGC NET CS 2017 Nov- paper-2
Question 72 Explanation: 
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Syntax:ALTER TABLE table_name MODIFY COLUMN column_name datatype;
Question 73
Given the following two statements about SQL:
a) An SQL query can contain HAVING clause only if it has GROUP BY clause
b) Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
Which of the following is correct?
A
Both a and b are true
B
Both a and b are false
C
a is true, b is false
D
b is true, a is false
       Database-Management-System       SQL       KVS 30-12-2018 Part B
Question 73 Explanation: 
→The GROUP BY clause is a SQL command that is used to group rows that have the same values.
→HAVING clause is used to filter summarized data or grouped data.
Question 74
SQL automatically does not eliminate duplicate tuples in the results of queries because
a) In aggregation function duplicates are useful
b) Duplicate elimination is expensive
c) memory chips are cheap and large these days, and can accommodate large number of tuples
Which of the following is/are correct?
A
a and b
B
b and c
C
Only b
D
Only c
       Database-Management-System       SQL       KVS 30-12-2018 Part B
Question 74 Explanation: 
→In order to perform aggregate operations , we need to consider duplicate values also.
→Removing duplication tuple is costly operation in which we need to access all the tuples in order to find the duplicate tuples.
Question 75
Consider the database state for the two tables given below:

SQL query- INSERT INTO DEPT(Dname,Dnum,Mgr_SSn) VALUES (Dept2,3,Mgr3)
Is rejected because of
A
Duplication of department name
B
Incorrect Mgr_SSN
C
Violation of referential Integrity
D
Violation of entity integrity
       Database-Management-System       SQL       KVS 30-12-2018 Part B
Question 75 Explanation: 
→Dnum is common field of two tables which may be foreign key.
→Inserting new row in the first table gives some null entries in the second table which is violation of referential integrity
Question 76
An attribute A of data type varchar (20) has value ‘Ram’ and the attribute B of data type char (20) has value ‘Sita’ in oracle. The attribute A has _______ memory spaces and B has _______ memory spaces.
A
20,20
B
3,20
C
3,4
D
20,4
       Database-Management-System       SQL       UGC NET CS 2017 Jan -paper-2
Question 76 Explanation: 
VARCHAR is variable length and CHAR is fixed length.
Given, varchar(20) and has value ‘Ram’ it means 3 spaces.
char(20) and has value ‘Sita’ but char is fixed spaces. It will take 20 spaces.
Question 77
Consider a database table R with attributes A and B. Which of the following SQL queries is illegal ?
A
SELECT A FROM R;
B
SELECT A, COUNT(*) FROM R;
C
SELECT A, COUNT(*) FROM R GROUP BY A;
D
SELECT A, B, COUNT(*) FROM R GROUP BY A, B;
       Database-Management-System       SQL       UGC NET CS 2016 Aug- paper-2
Question 77 Explanation: 
The aggregate functions can't be used without Group By clause.
Common aggregate functions include : COUNT,AVG,MAX,MIN,SUM
Question 78
Consider the following two commands C1 and C2 on the relation R from an SQL database:
C1 : drop table R;
C2 : delete from R;
Which of the following statements is TRUE ?
I. Both C1 and C2 delete the schema for R.
II. C2 retains relation R, but deletes all tuples in R.
III. C1 deletes not only all tuples of R, but also the schema for R.
A
I only
B
I and II only
C
II and III only
D
I, II and III
       Database-Management-System       SQL       UGC NET CS 2016 July- paper-2
Question 78 Explanation: 
→ Dropping the relation means to delete the content of a table and free up the space allocated to a table.
→ Deleting the tuples means keeping the space allocated to a table/relation but deleting the data relation contains.
→ Now DELETE command is used to delete the tuples of a relation while DROP command is used to delete the schema as well as tuples of a relation.
Hence option (C) is correct.
Question 79
Consider a “CUSTOMERS” database table having a column “CITY” filled with all the names of Indian cities (in capital letters). The SQL statement that finds all cities that have “GAR” somewhere in its name, is:
A
Select * from customers where
city = ‘%GAR%’;
B
Select * from customers where
city = ‘$GAR$’;
C
Select * from customers where
city like ‘%GAR%’;
D
Select * from customers where
city as ‘%GAR’;
       Database-Management-System       SQL       UGC NET CS 2015 Dec- paper-2
Question 79 Explanation: 
In above question a specific pattern "GAR" is given for pattern matching.
In SQL "LIKE" clause is used for pattern matching. For LIKE clause we have two wild cards:
1. "%" which represents any sequence of "0" or more characters.
2. "_" is used to replace a single character.
So, Option C is the correct answer because they have used LIKE clause along with "%" which indicates any number of character can be present before and after "GAR" pattern.
Question 80
An Assertion is a predicate expressing a condition we wish database to always satisfy.
The correct syntax for Assertion is :
A
CREATE ASSERTION ‘ASSERTION Name’ CHECK ‘Predicate’
B
CREATE ASSERTION ‘ASSERTION Name’
C
CREATE ASSERTION, CHECK Predicate
D
SELECT ASSERTION
       Database-Management-System       SQL       UGC NET CS 2015 Jun- paper-2
Question 80 Explanation: 
An Assertion is a condition that we wish the database to always satisfy. Domain constraints, functional dependency and referential integrity are special forms of assertion.
The syntax of Assertion in SQL is:
create assertion assertion-name check predicate
Question 81
Division operation is ideally suited to handle queries of the type :
A
customers who have no account in any of the branches in Delhi.
B
customers who have an account at all branches in Delhi.
C
customers who have an account in at least one branch in Delhi.
D
customers who have only joint account in any one branch in Delhi
       Database-Management-System       SQL       UGC NET CS 2014 Dec-Paper-2
Question 81 Explanation: 
→ The DIVISION operation is defined for convenience for dealing with queries that involve universal quantification or the all condition. For a tuple 't' to appear in the result T of the DIVISION, the values in ‘t’ must appear in R in combination with every tuple in S.
→ Note that in the formulation of the DIVISION operation, the tuples in the denominator relation S restrict the numerator relation R by selecting those tuples in the result that match all values present in the denominator. The DIVISION operation can be expressed as a sequence of π, ×, and – operations as follows:
T1 ← πY(R)
T2 ← πY((S × T1) – R)
T ← T1 – T2
Question 82
Which of the following is true ?
I. Implementation of self-join is possible in SQL with table alias.
II. Outer-join operation is basic operation in relational algebra.
III. Natural join and outer join operations are equivalent.
A
I and II are correct.
B
II and III are correct.
C
Only III is correct.
D
Only I is correct.
       Database-Management-System       SQL       UGC NET CS 2014 Dec-Paper-2
Question 82 Explanation: 
NATURAL JOIN requires that the two join attributes (or each pair of join attributes) have the same name in both relations. If this is not the case, a renaming operation is applied first.
OUTER JOIN: A set of operations, called outer joins, were developed for the case where the user wants to keep all the tuples in R, or all those in S, or all those in both relations in the result of the JOIN, regardless of whether or not they have matching tuples in the other relation.
In SQL, the same name can be used for two (or more) attributes as long as the attributes are in different relations. If this is the case, and a multi table query refers to two (or more) attributes with the same name, we must qualify the attribute name with the relation name to prevent ambiguity. The ambiguity of attribute names also arises in the case of queries that refer to the same relation twice. In this case, we are required to declare alternative relation names, called aliases or tuple variables.
An alias can follow the keyword "AS" , as shown below
SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;
In above query relation names E and S, called aliases or tuple variables, for the EMPLOYEE relation.
From above explanation it is clear that only option (D) is correct.
Question 83
Consider the following tables (relations):

Primary keys in the tables are shown using Underline. Now, Consider the following query:
SELECT S.Name, Sum (P.Marks)
FROM Students S, Performance P
WHERE S.Roll-No = P.Roll-No
GROUP BY S.Name
The number of rows returned by the above query is
A
3
B
2
C
0
D
1
       Database-Management-System       SQL       UGC NET CS 2018-DEC Paper-2
Question 83 Explanation: 
The following table is returned as the result of executing FROM and WHERE commands in given query.

Since in query “GROUP BY S.Name” is given so, firstly Group names having same“Name” value and and then perform SUM() operation on those values. The below table is returned as the result of given query :

So the number of rows returned by given query are 2.
Question 84
​ ________ command is used to remove a relation from an SQL database.
A
Update table
B
Remove table
C
Delete table
D
Drop table
       Database-Management-System       SQL       UGC NET CS 2018-DEC Paper-2
Question 84 Explanation: 
Update table : Update table command is used to update the content of the table.
Delete table : Delete table command is used to delete the data
stored in the table. When we use Delete command table is not deleted only data stored in the table is deleted.
Drop table : Using table command we can delete a table/relation.
Question 85
Views are useful for _____ unwanted information, and for collecting together information from more than one relation into a single view.
A
Hiding
B
Deleting
C
Highlighting
D
All of the above
       Database-Management-System       SQL       UGC NET CS 2013 Sep-paper-2
Question 85 Explanation: 
→ Views are useful for hides unwanted information, and for collecting together information from more than one relation into a single view.
→ A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.
Views advantages over tables:
→Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table. →Views can join and simplify multiple tables into a single virtual table.
→Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.
→Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.
→Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
→Depending on the SQL engine used, views can provide extra security.
Question 86
Consider a relation book(title, price) which contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list ?
SELECT title
FROM book AS B
WHERE(SELECT COUNT(*) FROM book AS T WHERE T.price > B.price) < 7
A
Titles of the six most expensive books.
B
Title of the sixth most expensive books.
C
Titles of the seven most expensive books.
D
Title of the seventh most expensive books.
       Database-Management-System       SQL       UGC NET CS 2018 JUNE Paper-2
Question 86 Explanation: 
→ SQL query, which results titles of the 7 most expensive books.
→ The where clause of outer query will be true for 7 most expensive books.
Note: All aggregate functions except count(*) ignore NULL values in their input collection.
Question 87
Database systems that store each relation in a separate operating system file may use the operating system’s authorization scheme, instead of defining a special scheme themselves. In this case, which of the following is false ?
A
The administrator enjoys more control on the grant option.
B
It is difficult to differentiate among the update, delete and insert authorizations.
C
Cannot store more than one relation in a file
D
Operations on the database are speeded up as the authorization procedure is carried out at the operating system level.
       Database-Management-System       SQL       UGC NET CS 2018 JUNE Paper-2
Question 87 Explanation: 
When the Database system will store each relation in seperate operating system file then it will become difficult for the administrator to differentiate among the update, delete and insert authorizations and also to keep track of grant options becomes difficult which is a overhead.
So option 1 is clearly false and option 2 is true.
Option 3: In question it is mentioned that each relation is stored in a separate operating system file. So option 3 is true.
Option 4 : Since for each relation there is a seperate operating system file which may use the operating system’s authorization scheme. So Operations on the database are speeded up as the authorization procedure is carried out at the operating system level.
Question 88
In DML, RECONNCT command cannot be used with
A
OPTIONAL Set
B
FIXED Set
C
MANDATOR Set
D
All of the above
       Database-Management-System       SQL       UGC NET CS 2012 Dec-Paper-2
Question 88 Explanation: 
→ The RECONNECT command can be used with both OPTIONAL and MANDATORY sets, but not with FIXED sets.
→ The RECONNECT command moves a member record from one set instance to another set instance of the same set type. It cannot be used with FIXED sets because a member record cannot be moved from one set instance to another under the FIXED constraint.
Question 89
Given a Relation POSITION (Posting- No, Skill), then query to retrieve all distinct pairs of posting-nos. requiring skill is
A
Select p.posting-No, p.posting No from position p where p.skill = p.skilland p.posting-No < p.posting-No
B
Select p1.posting-No, p2.posting- No from position p1, position p2 where p1.skill = p2.skill
C
Select p1.posting-No, p2posting-No from position p1, position p2 where p1.skill = p2.skill and p1.posting-No < p2.posting-No
D
Select p1.posting-No, p2.posting- No from position p1, position p2 where p1.skill = p2.skill and p1.posting-No = p2.posting-No
       Database-Management-System       SQL       UGC NET CS 2012 Dec-Paper-2
Question 89 Explanation: 
Option (A) is incorrect because No self join is not possible without aliasing. Without aliasing it will through an error.
Option(B) is not correct because it is not resulting into distinct pairs of posting-nos.


Question 90
GO BOTTOM and SKIP-3 commands are given one after another in a database file of 30 records. It shifts the control to
A
28th record
B
27th record
C
3rd record
D
4th record
       Database-Management-System       SQL       UGC NET CS 2013 Dec-paper-2
Question 90 Explanation: 
GO BOTTOM command will directly reach end of the record. Here, total number of records are 30. GO BOTTOM command will reach 30th position.
After SKIP-1, it became 29
After SKIP-2, it became 28
After SKIP-3, it became 27.
In question, they given 3 SKIP operations.
Question 91
Which command classes text file, which has been created using“SET ALTERNATIVE” <FILE NAME>“Command” ?
A
SET ALTERNATE OFF
B
CLOSE DATABASE
C
CLOSE ALTERNATE
D
CLEAR ALL
       Database-Management-System       SQL       UGC NET CS 2011 Dec-Paper-2
Question 91 Explanation: 
SET ALTERNATE: Controls the recording of input and output in an alternate text file.
Syntax:
SET ALTERNATE on | OFF
SET ALTERNATE TO [ | ? | [ADDITIVE]]
→ The default for SET ALTERNATE is OFF. To change the default, set the ALTERNATE parameter in the [OnOffSetting Settings] section of PLUS.ini. To set a default file name for use with SET ALTERNATE, specify an ALTERNATE parameter in the [Command Settings] section of PLUS.ini.
→ Issuing SET ALTERNATE OFF does not close the alternate file. Before accessing the contents of an alternate file, formally close it with CLOSE ALTERNATE or SET ALTERNATE TO (with no file name). This ensures that all data recorded by dBASE Plus for storage in the alternate file is transferred to disk, and automatically turns SET ALTERNATE to OFF.
Question 92
The SQL Expression
Select distinct T. branch name from branch T, branch S where T. assets > S. assets and S. branch-city = DELHI, finds the name of
A
all branches that have greater asset than any branch located in DELHI.
B
all branches that have greater assets than allocated in DELHI.
C
the branch that has the greatest asset in DELHI.
D
any branch that has greater asset than any branch located in DELHI.
       Database-Management-System       SQL       UGC NET CS 2011 Dec-Paper-2
Question 92 Explanation: 

Since the query is co-related that is same table is being used two times for comparison so compare each entry of table T with every entry of table S. Then the outcome for above query will be

So the output contains all branches that have greater asset than any branch located in DELHI.
Question 93
Given relations R(w,x) and S(y,z), the result of
SELECT DISTINCT w, x
FROM R.S is guaranteed to be same as R, if
A
R has no duplicates and S is non-empty
B
R and S have no duplicates
C
S has no duplicates and R is non-empty
D
R and S have the same number of tuples
       Database-Management-System       SQL       NIELIT Technical Assistant_2016_march
Question 94
Which data management language component enabled the DBA to define the schema components ?
A
DML
B
Sub-schema DLL
C
Schema DLL
D
All of these
       Database-Management-System       SQL       UGC NET CS 2010 June-Paper-2
Question 94 Explanation: 
Database task Group(DBTG) specified three distinct data management language components to produce the required standardization
1. Schema DDL: The schema DDL enabled the DBA to define the schema components.
2. Subschema DDL: It allowed the application programs to define the database components that will be used by the program
3. DML: It enables the manipulation of the database contents.
Question 95
The PROJECT Command will create new table that has
A
more fields than the original table
B
more rows than original table
C
both (A) & (B)
D
none of these
       Database-Management-System       SQL       UGC NET CS 2010 June-Paper-2
Question 95 Explanation: 
Project command is used to select a column of a relation based on the condition given in the query.
Option(A) is incorrect because the maximum number of columns Project command can select is equal to the total number of columns in the given relation.
Option(B) is incorrect because project command is related to the columns of a relation, it is not related to the selection of rows in the table. The result of Project command contain same number of rows as that of given relation.
Question 96
(i) DML includes a query language based on both relation algebra and tuple calculus
(ii) DML includes a query language based on tuple calculus
(iii) DML includes a query language based on relational algebra
(iv) DML includes a query language based on none of the relational algebra and tuple calculus
Which one is correct ?
A
(i) only
B
(ii) only
C
(iii) only
D
(iv) only
       Database-Management-System       SQL       UGC NET CS 2009-June-Paper-2
Question 96 Explanation: 
→ DML(Data Manipulation Language) performs to SELECT,UPDATE,INSERT and DELETE operations.
→ DML includes a query language based on both relation algebra and tuple calculus
Question 97
Which construct in SQL is used to test whether a subquery has any tuples in its result ?
A
UNIQUE
B
EXISTS
C
GROUP BY
D
EXCEPT
       Database-Management-System       SQL       UGC NET CS 2009-June-Paper-2
Question 97 Explanation: 
EXISTS:The EXISTS function in SQL is used to check whether the result of a correlated nested query is empty (contains no tuples) or not.
UNIQUE: UNIQUE(Q), which returns TRUE if there are no duplicate tuples in the result of query ; otherwise, it returns FALSE. This can be
used to test whether the result of a nested query is a set or a multiset.
GROUP BY: The GROUP BY clause specifies the grouping attributes, which should also appear in the SELECT clause, so that the value resulting from applying each aggregate function to a group of tuples appears along with the value of the grouping attribute(s).
EXCEPT: The EXCEPT query is like the set difference operation in SQL. The difference between EXCEPT and EXCEPT ALL is EXCEPT do not allow duplicates while EXCEPT ALL allows duplicates in the result of a query.
Hence correct answer is OPTION(B)
Question 98
Consider the query : SELECT student_name FROM student_data WHERE rollno (SELECT rollno FROM student_marks WHERE SEM1_MARK5SEM2_MARK);
Which of the following is true ?
A
It gives the name of the student whose marks in semester 1 and semester 2 are same.
B
It gives all the names and roll nos of those students whose marks in semester 1 and semester 2 are same.
C
It gives the names of all the students whose marks in semester 1 and semester 2 are same.
D
It gives roll numbers of all students whose marks in semester 1 and semester 2 are same.
       Database-Management-System       SQL       UGC NET CS 2008-june-Paper-2
Question 98 Explanation: 
Since both the queries are not related so you can solve inner query first and then can solve outer query.
Question 99
Aggregate functions in SQL are :
A
GREATEST, LEAST and ABS
B
SUM, COUNT and AVG
C
UPPER, LOWER and LENGTH
D
SQRT, POWER and MOD
       Database-Management-System       SQL       UGC NET CS 2007-Dec-Paper-2
Question 99 Explanation: 
Aggregate functions in SQL are SUM,COUNT,AVG,MIN and MAX.
The COUNT() function returns the number of rows that matches a specified criteria.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.
Question 100
The end of an SQL command is denoted by :
A
an end-of-line character
B
an ‘enter-key’ marker
C
entering F4 key
D
a semicolon (;)
       Database-Management-System       SQL       UGC NET CS 2007-Dec-Paper-2
Question 100 Explanation: 
A semicolon (;) tells SQL*Plus that you want to run the command. Type the semicolon at the end of the last line of the command.
Question 101
Consider the query : SELECT student_name FROM students WHERE class_name=(SELECT class_name FROM students WHERE math_marks=100);
what will be the output ?
A
the list of names of students with 100 marks in mathematics
B
the names of all students of all classes in which at least one student has 100 marks in mathematics
C
the names of all students in all classes having 100 marks in mathematics
D
the names and class of all students whose marks in mathematics is 100
       Database-Management-System       SQL       UGC NET CS 2007-Dec-Paper-2
Question 101 Explanation: 

Question 102
Given two relations R1(A, B) and R2(C, D), the result of following query Select distinct A, B from R1, R2 is guaranteed to be same as R1 provided one of the following condition is satisfied.
A
R1 has no duplicates and R2 is empty.
B
R1 has no duplicates and R2 is non - empty.
C
Both R1 and R2 have no duplicates.
D
R2 has no duplicates and R1 is non - empty.
       Database-Management-System       SQL       UGC NET CS 2017 Nov- paper-3
Question 102 Explanation: 
Option(A): Cartesian product with a empty table will result in zero tuple because we can't have any ordered pair with a empty table. Hence option(A) is incorrect option.
Option(B):





Question 103
Works (emp name, company name, salary)
Here, emp_name is primary key. Consider the following SQL query
Select emp name
From works T
Where salary > (select avg (salary)
from works S
where T.company _ name = S.company _name)
The above query is for following :
A
Find the highest paid employee who earns more than the average salary of all employees of his company
B
Find the highest paid employee who earns more than the average salary of all the employees of all the companies.
C
Find all employees who earn more than the average salary of all employees of all the companies.
D
Average salary of all employees of their company.
       Database-Management-System       SQL       UGC NET CS 2017 Jan- paper-3
Question 103 Explanation: 


Question 104
Consider the following ORACLE relations :

R (A, B, C) = {<1, 2, 3>, <1, 2, 0>, <1, 3, 1>, <6, 2, 3>, <1, 4, 2>, <3, 1, 4> }

S (B, C, D) = {<2, 3, 7>, <1, 4, 5>, <1, 2, 3>, <2, 3, 4>, <3, 1, 4>}.

Consider the following two SQL queries SQ1 and SQ2 :

SQ1 : SELECT R⋅B, AVG (S⋅B) FROM R, S WHERE R⋅A = S⋅C AND S⋅D < 7 GROUP BY R⋅B;

SQ2 : SELECT DISTINCT S⋅B, MIN (S⋅C) FROM S GROUP BY S⋅B HAVING COUNT (DISTINCT S⋅D) > 1;

If M is the number of tuples returned by SQ1 and N is the number of tuples returned by SQ2 then

A
M = 4, N = 2
B
M = 5, N = 3
C
M = 2, N = 2
D
M = 3, N = 3
       Database-Management-System       SQL       UGC NET CS 2016 July- paper-3
Question 104 Explanation: 






Question 105
Consider the following ORACLE relations : One (x, y) = {<2, 5>, <1, 6>, <1, 6>, <1, 6>, <4, 8>, <4, 8>}
Two (x, y) = {<2, 55>, <1, 1>, <4, 4>, <1, 6>, <4, 8>, <4, 8>, <9, 9>, <1, 6>}
Consider the following two SQL queries SQ1 and SQ2 :
SQ1 : SELECT * FROM One)
EXCEPT
(SELECT * FROM Two);
SQ2 : SELECT * FROM One)
EXCEPT ALL
(SELECT * FROM Two);
For each of the SQL queries, what is the cardinality (number of rows) of the result obtained when applied to the instances above ?
A
2 and 1 respectively
B
1 and 2 respectively
C
2 and 2 respectively
D
1 and 1 respectively
       Database-Management-System       SQL       UGC NET CS 2016 Aug- paper-3
Question 105 Explanation: 
EXCEPT operation is like subtraction operation.
EXCEPT : EXCEPT operator do not include duplicates i.e if there are duplicate copies of a tuple in a relation the EXCEPT will consider only one copy of that duplicated tuple.
EXCEPT ALL : EXCEPT operator includes duplicates.



Question 106
Suppose ORACLE relation R(A, B) currently has tuples {(1, 2), (1, 3), (3, 4)} and relation S(B, C) currently has {(2, 5), (4, 6), (7, 8)}. Consider the following two SQL queries SQ1 and SQ2 :
SQ1: Select *
From R Full Join S
On R.B = S.B;
SQ2: Select *
From R Inner Join S
On R.B = S.B;
The numbers of tuples in the result of the SQL query SQ1 and the SQL query SQ2 are given by:
A
2 and 6 respectively
B
6 and 2 respectively
C
2 and 4 respectively
D
4 and 2 respectively
       Database-Management-System       SQL       UGC NET CS 2015 Dec - paper-3
Question 106 Explanation: 

Question 107
Consider the following three SQL queries (Assume the data in the people table):
(a)Select Name from people where Age > 21;
(b)Select Name from people where Height > 180;
(c)Select Name from people where (Age > 21) or (Height > 180);
If the SQL queries (a) and (b) above, return 10 rows and 7 rows in the result set respectively, then what is one possible number of rows returned by the SQL query (c)?
A
3
B
7
C
10
D
21
       Database-Management-System       SQL       UGC NET CS 2015 Dec - paper-3
Question 107 Explanation: 


→ In this case query (a) will return 10 rows, query (b) will return 7 rows and query (c) will return 17 rows because of “or” condition given in it.
Case II: When result of queries (a) & (b) have the same row.
→ This case result in minimum no. of rows possible for given table.

→ In this case maximum 10 rows will be resulted.
Option (C) is most suitable, because 10 is minimum no. of rows possible so option (A) & (B) can’t be correct choice.
And maximum no. of rows possible is 17 so option (D) is also not correct choice.
Hence answer is option (C).
Question 108
Which of the following statements are DMl statements?
(a) Update [tablename]
Set [ columnname] = VALUE
(b) Delete [tablename]
(c) Select * from [tablename]
A
(a) and (b)
B
(a) and (d)
C
(a), (b) and (c)
D
(b) and (c)
       Database-Management-System       SQL       UGC NET June-2019 CS Paper-2
Question 108 Explanation: 
Question 109
The STUDENT information in a university stored in the relation STUDENT (Name, SEX, Marks, DEPT_Name) Consider the following SQL Query SELECT DEPT_Name from STUDENT where SEX = 'M' group by DEPT_Name having avg (Marks)>SELECT avg (Marks) from STUDENT. It Returns the Name of the Department for which:
A
The Average marks of Male students is more than the average marks of students in the same Department
B
The average marks of male students is more than the average marks of students in the University
C
The average marks of male students is more than the average marks of male students in the University
D
The average marks of students is more than the average marks of male students in the University
       Database-Management-System       SQL       UGC NET CS 2015 June Paper-3
Question 109 Explanation: 
The query is co-related( same table is used in outer and inner query) so compare each entry of Student table 1 with each entry of Student table 2.

⇒ Consider this table for outer query i.e., LHS query of “>” symbol and here find avg. marks of a department because of group by constraint
i.e., avg. marks for CSE = 87.5
avg. marks for IT = 90


⇒ Consider this table for inner query i.e., RHS query of “>” symbol and here find avg. of total marks
i.e., avg. marks = 88.7
⇓ O/P of given query is
Question 110
Given two tables EMPLOYEE (EID, ENAME, DEPTNO)
DEPARTMENT (DEPTNO. DEPTNAME)
Find the most appropriate statement of the given query:
Select count (*) ‘total’
from EMPLOYEE
where DEPTNO IN (D1, D2)
group by DEPTNO
having count (*) > 5
A
Total number of employees in each department D1 and D2
B
Total number of employees of department D1 and D2 if their total is >5
C
Display total number of employees in both departments D1 and D2
D
The output of the query must have atleast two rows
       Database-Management-System       SQL       UGC-NET DEC-2019 Part-2
Question 111
Which of the following statements about the “DELETE” command is FALSE?
A
It removes tuples from a relation (table).
B
It removes tuples as well as the relation (table).
C
A missing WHERE clause specifies that all tuples in the relation are to be deleted.
D
Depending on the number of tuples selected by the condition in the WHERE clause, zero, one or several tuples can be deleted by a single DELETE command.
       Database-Management-System       SQL       CIL Part - B
Question 111 Explanation: 
→The DELETE statement is used to delete existing records in a table. →DELETE Syntax DELETE FROM table_name WHERE condition;
Question 112

Consider the following relation schema R along with the tuples.

Employee(name, salary) ={< e1, 10000>,< e2, 5000>,< e3, 2500>, < e4, 7500>,< e5, 8900>,< e6, 9800>}

What is the output of following SQL query?

SELECT name , MAX( salary) FROM Employee WHERE salary<( SELECT MAX (salary) FROM Employee;
A
< e3, 2500>
B
< e5, 8900>
C
< e6, 9800>
D
< e1, 10000>
       Database-Management-System       SQL       CIL Part - B
Question 112 Explanation: 
The above query is to find the employee whose salary is the second highest
Question 113
The SQL Query
SELECT columns
FROM TableA
RIGHT OUTER JOIN TableB
ON A.columnName = B.columnName
WHERE A.columnName IS NULL
returns the following:
A
All rows in TableB, ,which meets equality condition above and, none from Table A, which meets the condition
B
All rows in TableA, which meets equality condition above and, none from Table B, which meets the condition
C
All rows in TableB, which meets equality condition
D
All rows in TableA, which meets equality condition
E
None of the above
       Database-Management-System       SQL       ISRO CS 2020
Question 113 Explanation: 
Counter example (Executed under ORACLE Express Edition)
SQL> select * from TableA;
A1 A2 A3
---------- --- ---
1 a21 a31
2 a22 a32
3 a23 a33
3 a23 a34
a24 a35
SQL> select * from TableB;
B1 B2 B3
---------- --- ---
1 b21 b31
2 b22 b31
3 b23 b32
4 a24 b33
5 b25 b34
SQL> select * from TableA RIGHT OUTER JOIN TableB
2 ON TableA.a1=TableB.b1
3 WHERE TableA.a1 IS NULL;
A1 A2 A3 B1 B2 B3
---------- --- --- ---------- --- ---
4 a24 b33
5 b25 b34
So, none of the given options are correct.
Question 114
Properties of ‘DELETE’ and ‘TRUNCATE’ commands indicate that
A
After the execution of ‘TRUNCATE’ operation, COMMIT, and ROLLBACK statements cannot be performed to retrieve the lost data, while ‘DELETE’ allow it
B
After the execution of ‘DELETE’ and ‘TRUNCATE operation retrieval is easily possible for the lost data
C
After the execution of ‘DELETE’ operation, COMMIT and ROLLBACK statements can be performed to retrieve the lost data, while TRUNCATE do not allow it
D
After the execution of ‘DELETE’ and ‘TRUNCATE’ operation no retrieval is possible for the lost data
       Database-Management-System       SQL       ISRO CS 2020
Question 114 Explanation: 
TRUNCATE is a DDL command, it does not require a commit to make the changes permanent. Because of this reason the rows deleted by truncate could not be rollbacked. On the other hand, DELETE is a DML command, hence requires explicit commit to make its effect permanent.After performing a DELETE, you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
Question 115
Emp (eid: integer, ename; string, age; integer, salary; real) Works (eid; integer, did; integer, pct time; integer) Dept (did; integer, budget; real, managerid; integer) CREATE VIEW AvegSalaryByAge (age, avgSalary) AS SELECT E.eid, AVG (E.salary) FROM Emp E GROUP By E age.
A
The above view cannot be updated automatically.
B
The above view on Emp can be updated automatically by updating Emp
C
The above view cannot be created automatically.
D
None of the given options
       Database-Management-System       SQL       APPSC-2016-DL-CS
Question 116

Aggregate functions in SQL are

A
GREATEST, LEAST and ABS
B
SUM, COUNT AND AVG
C
UPPER, LOWER AND LENGTH
D
SQRT, POWER AND MOD
       Database-Management-System       SQL       APPSC-2016-DL-CA
Question 116 Explanation: 
Aggregate functions in SQL are SUM,COUNT and AVG.
Question 117

Which of the following sets of keywords constitutes a mapping in SQL?

A
SELECT, FROM, TABLE
B
SELECT, FROM, WHERE
C
CONNECT, TABLE, CREATE
D
SELECT, TABLE, INSERT
       Database-Management-System       SQL       APPSC-2016-DL-CA
Question 117 Explanation: 
Explanation: SELECT, FROM, WHERE are the keywords that constitute a mapping in SQL.
A SELECT statement retrieves zero or more rows from one or more database tables WHERE specifies which rows to retrieve. The SQL From clause is the source of a row set to be operated upon in a Data Manipulation Language (DML) statement.
Exam[le: SELECT *
FROM mytable
WHERE mycol > 100
Question 118

___ command is used to enable, disable, modify or drop a constraint in SQL

A
MODIFY Table
B
DEFINE Table
C
ADD Column
D
ALTER Table
       Database-Management-System       SQL       CIL 2020
Question 118 Explanation: 
The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You should also use the ALTER TABLE command to add and drop various constraints on an existing table.
Question 119

____includes a query language and commands to insert tuples into, delete tuples from, and modify tuples in the database.

A
Query language
B
Data-manipulation language
C
Data-Definition language
D
Data-calculation language
       Database-Management-System       SQL       CIL 2020
Question 119 Explanation: 
Data manipulation language includes commands,
SELECT
INSERT
UPDATE
DELETE
Question 120

In SQL-the function -avg,min,max,sum,count are called as ____

A
Aggregate function
B
Adjunct function
C
Scalar operation
D
Set Operation
       Database-Management-System       SQL       CIL 2020
Question 120 Explanation: 
In SQL the function avg, min, max, sum, count are called as aggregate function.
Question 121

Consider the following relational schema

Sailors(sid, sname, rating, age)
Reserves(sid, bid, day)
Boats(bid, bname, color) 

What is the equivalent of following relational algebra query in SQL query

Πsname((σcolor='red'Boats)⨝ Reserves ⨝ Sailors) 
A
SELECT S.sname, S.rating
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid = B.bid AND B.color='red'
B
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid=R.sid AND R.bid = B.bid AND B.color='red'
C
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid=R.sid AND B.color='red'
D
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE R.bid=B.bid AND B.color='red'
       Database-Management-System       SQL       CIL 2020
Question 121 Explanation: 
The given relational algebra query is equivalent to the sql query given in option B.
Question 122

In SQL, the ___ command is used to recompile view

A
ALTER VIEW
B
COMPILE VIEW
C
CREATE VIEW
D
DEFINE VIEW
       Database-Management-System       SQL       CIL 2020
Question 123

To find the second highest salary from Employees table, which of the following query is true?

A
Select max (salary) from employees where salary = (select max (salary from employees where salary < (select max (salary) from employees);
B
Select min (salary) from (select salary from employees order by salary desc) where rownum ←2
C
Select max (salary) from employees where salary < (select max(salary from employees)
D
Select max (salary) from employees where salary = (select salary from employees where rownum = 2 order by salary)
       Database-Management-System       SQL       APPSC-2012-DL CA
Question 123 Explanation: 
Select max (salary) from employees where salary < (select max(salary from employees) query gives the second highest salary from Employees table . The second query calculates the highest salary and then the first query selects the highest salary other than the already selected highest salary means the first query outputs the second highest salary.
Question 124

SQL injection consists of

A
Authorized Physicians injecting SQL in patients.
B
A mechanism to improve performance of database
C
A hacking mechanism enters the system by providing erroneous input.
D
Exception handling routines.
       Database-Management-System       SQL       APPSC-2012-DL CA
Question 124 Explanation: 
SQL Injection
SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input.
Question 125
SQL is used for
A
Data processing in batch mode
B
Query for relational databases
C
DTP work
D
None of the above
       Database-Management-System       SQL       APPSC-2012-DL-CS
Question 125 Explanation: 
SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
Question 126
Inheritance achieved by the key word in SQL is:
A
of
B
sub
C
under
D
from
       Database-Management-System       SQL       TNPSC-2017-Polytechnic-CS
Question 126 Explanation: 
Inheritance achieved by the keyword in SQL is ‘under’.
There are 126 questions to complete.
PHP Code Snippets Powered By : XYZScripts.com
error: Content is protected !!