SQL

Question 1
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 1 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 2
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 2 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 3
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 3 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 4
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 4 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 5
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 5 Explanation: 


Question 6
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 6 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 7
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 7 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 8

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 JUNE Paper-2
Question 8 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 9
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 9 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 10

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 DEC Paper-2
Question 10 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 11

________ 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 DEC Paper-2
Question 11 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 12
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 12 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 13
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 13 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 14
​ ________ 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 14 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 15
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 15 Explanation: 
Table: SQL Operator Precedence

The above table shows the SQL operator precedence.
Question 16
'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 16 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 17
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 17 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 18
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 18 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 19
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 19 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 20
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 20 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 21
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 21 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 22
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 22 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 23
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 23 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 24
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 24 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 25
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 25 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 26
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 26 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 27
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 27 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 28
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 28 Explanation: 
It undoes some or all database changes made during the current transaction.
Question 29
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 29 Explanation: 
Only logic here is, when we are using keyword IN there must be OR keyword.
Question 30
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 30 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 31

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 31 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 32

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 32 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 33
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 33 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 34
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 34 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 35
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 35 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 36
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 36 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 37
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 37 Explanation: 
Aggregate function is AVG. The AVG() function returns the average value of a numeric column.
Question 38
__________ 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 38 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 39
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 39 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 40
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 40 Explanation: 
The aggregate functions can't be used without Group By clause.
Common aggregate functions include : COUNT,AVG,MAX,MIN,SUM
Question 41
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 41 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 42
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 42 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 43
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 43 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 44
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 44 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 45
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 45 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 46
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 46 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 47
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 47 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 48
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 48 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 49
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 49 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 50
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 50 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 51
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 51 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 52
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 52 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 53
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 53 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 54
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 55
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 55 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 56
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 56 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 57
(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 57 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 58
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 58 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 59
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 59 Explanation: 
Since both the queries are not related so you can solve inner query first and then can solve outer query.
Question 60
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 60 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 61
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 61 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 62
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 62 Explanation: 

Question 63
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 63 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 64
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 64 Explanation: 


Question 65
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 65 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 66
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 66 Explanation: 






Question 67
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 67 Explanation: 

Question 68
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 68 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).
There are 68 questions to complete.
PHP Code Snippets Powered By : XYZScripts.com