SQL Test
There are two tables A and B. You
are retreiving data from both tables where all rows from B table and only
matching rows from A table should be displayed. Which type of join you will
apply between A and B tables?
a. Inner join
b. Left outer join
c.
Right outer join
d. Self join
There
is a column c1 in the table t to which a primary key pk is to be added. What
will be the correct syntax?
a. Alter
table t add primary key(c1);
b. Alter table t add constraint pk primary key(c1);
c. Alter
table t add (constraint pk primary key(c1));
d. Alter
table t add pk constraint primary key(c1);
In
which sequence are queries and sub-queries executed by the SQL Engine?
a.
primary query -> sub query -> sub sub query and so on
b. sub
sub query -> sub query -> prime query
c. the
whole query is interpreted at one time
d. there is no fixed sequence of interpretation,
the query parser takes a decision on the fly
What
does the term DDL stand for?
a. Data
Description Language
b.
Dynamic Data Language
c. Data Definition Language
d. Data
Derived Language
e.
Descriptive Data Language
Consider
the following tables:
Books
---------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
-------------
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
------------
AuthorId
AuthorName
Country
What is the query to determine the names of the Authors who have written more than 1 book?
Books
---------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
-------------
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
------------
AuthorId
AuthorName
Country
What is the query to determine the names of the Authors who have written more than 1 book?
a. select AuthorName from Authors where AuthorId in
(select AuthorId from Books group by AuthorId
having count(*)>1)
b. select
AuthorName from Authors, Books where
Authors.AuthorId=Books.AuthorId and count(BookId)>1
c. select
AuthorName from Authors, Books where
Authors.AuthorId=Books.AuthorId group by AuthorName having count(*)>1
d. select
AuthorName from Authors where AuthorId in
(select AuthorId from Books having
count(BookId)>1)
Examine
the query:-
select (2/2/4) from tab1;
where tab1 is a table with one row. This would give a result of:
a. 4
b. 2
c. 1
d. .5
e. .25
f. 0
g. 8
h. 24
The
level of abstraction which describes only part of the entire database is
called?
a.
Conceptual level
b. View level
c.
Procedural level
d. None
of the above
Which
statement is correct for FIRST NORMAL FORM?
a.
Includes only tables that do not have composite primary keys
b. Must have data stored in a two-dimensional table
with no repeating groups
c. Every
non-key column is non transitively dependent upon its primary key
d. None
of the above
Which
of the following statements are incorrect regarding definition of simple and
complex views?
a. A
simple view is one which derives data from only one table
b. A
simple view is one which contains no functions or groups of data
c. A
complex view is one which derives data from many tables
d. A
complex view is one which may contain functions
e. A complex view cannot contain groups of data
f. A complex view is a combination of more than
one simple view
g. A table can either have a simple or complex
view, not both
Which
operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 - 8) from emp
select (age + 3 * 4 / 2 - 8) from emp
a. +
b. –
c. /
d. *
What
is a rollback of transactions normally used for?
a. Recover from the transaction failure
b. Update
the transaction
c.
Retrieve old records
d. None
of the above
<
and > are examples of _________ type of operators
a.
Logical
b.
Arithmetic
c.
Assignment
d.
Ternary
e.
Relational
f.
Numeric
g. Comparison
h. None
of the above
Which
of the following is not a DDL command?
a. Drop
b. Create
c. Alter
d. Revoke
e. Explain plan
f. Insert
g. Update
h. Delete
A company has the following
departments:
Marketing , Designing , Production , Packing
What will be the result of the following query?
select * from table where department < 'Marketing';
Marketing , Designing , Production , Packing
What will be the result of the following query?
select * from table where department < 'Marketing';
a.
The query will return " Designing , Packing "
b. The query will return "
Designing , production ,Packing "
c. The query will return
"Packing"
d. Strings cannot be compared using
< operator
e. The query will return "
Designing "
The overall logical structure of a
database can be expressed graphically by:
a.
Data Flow Chart
b. Flow Chart
c. Directed Chart
d. Entity-Relationship Diagram
e. None of the above
Identify the incorrect statement/s
regarding constraints
a. A primary key constraint can be
disabled
b. Information about the constraints
on a table can be retrieved from the data dictionary
c. Information about the columns on
which the constraints have been applied can be retrieved from the data
dictionary
d.
There can be only one unique key in a table
e. If a column has null values then
the not null constraint cannot be applied to that column
f.
There can be more than one primary key
Consider the following tables:
Books
----------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
-------------
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
-------------
AuthorId
AuthorName
Country
What is the query to determine how many books, with a popularity rating of more than 7, have been written on each subject?
Books
----------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
-------------
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
-------------
AuthorId
AuthorName
Country
What is the query to determine how many books, with a popularity rating of more than 7, have been written on each subject?
a. select subject,count(*) as Books from books,subjects where books.popularityrating > 7 group by subjects.subject
b. select subject,count(*) as Books
from books,subjects where
books.authorid=subjects.authorid and books.popularityrating >
7 group by subjects.subject
c. select subject,count(*) as Books
from books,subjects where
books.subjectid=subjects.subjectid and books.popularityrating = 7
group by subjects.subject
d. select subject,count(*) as Books
from books,subjects where
books.subjectid=subjects.subjectid and books.popularityrating >
7 group by
subjects.subject
What is the collection of
information stored in a database at a particular moment called?
a.
Schema
b. Instance
c. Table
d. Cluster
e. View
f. Index
g. None of the above
Which of the following statement is
correct regarding table creation?
a. Tables once created cannot be
modified to add columns
b. Constraints can only be given
while table creation
c.
One can easily create a table from a given table
d. When a table is created from
another table, all the constraints are copied as well
e. The width of the columns cannot
be modified
f. Columns cannot be removed from a
table
Select all the appropriate options
a. A table is a multiset of rows
b.
A table is a two-dimensional array of rows and columns
c. A table is always dependent on
other tables
d.
A third normal form table is a table free of redundant data
e. A table must have a primary key
What does MOD() function do?
a.
Returns the remainder after division
b. Modifies the column definition
c. Modifies the definition of a
table
d. None of the above
Which of the following statements
are true?
a.
With DDL you can create and remove tables, schemas, domains, indexes and views
b. Select, Insert and Update are DCL
commands
c. Grant and Revoke are DML commands
d. Commit and Rollback are DCL
commands
View
the following Create statement:
1 Create table Pers
2 (EmpNo Number(4) not null,
3 EName Char not null,
4 Join_dt Date not null,
5 Pay Number)
Which line contains an error?
1 Create table Pers
2 (EmpNo Number(4) not null,
3 EName Char not null,
4 Join_dt Date not null,
5 Pay Number)
Which line contains an error?
a. 1
b.
2
c. 3
d. 4
e. 5
What is the correct order of clauses
in the select statement?
1 select
2 order by
3 where
4 having
5 group by
a. 1,2,3,4,5
b.
1,3,5,4,2
c. 1,3,5,2,4
d. 1,3,2,5,4
e. 1,3,2,4,5
f. 1,5,2,3,4
g. 1,4,2,3,5
h. 1,4,3,2,5
Examine the data in the EMPLOYEES
table given below:
LAST_NAME DEPARTMENT_ID SALARY
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
Which of the following Subqueries work?
LAST_NAME DEPARTMENT_ID SALARY
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
Which of the following Subqueries work?
a.
SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees
GROUP BY department_id);
b. SELECT * FROM employees WHERE
salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
c.
SELECT distinct department_id FROM employees Where salary > ANY (SELECT
AVG(salary) FROM employees GROUP BY department_id);
d.
SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary)
FROM employees GROUP BY department_id);
e. SELECT department_id FROM
employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY
AVG(SALARY));
The STUDENT_GRADES table has these
columns:
STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4)
Which of the following statements finds the highest Grade Point Average (GPA) per semester?
STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4)
Which of the following statements finds the highest Grade Point Average (GPA) per semester?
a. SELECT MAX(gpa) FROM
student_grades WHERE gpa IS NOT NULL
b. SELECT (gpa) FROM student_grades
GROUP BY semester_end WHERE gpa IS NOT NULL
c.
SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end
d. SELECT MAX(gpa) GROUP BY
semester_end WHERE gpa IS NOT NULL FROM student_grades
e. SELECT MAX(gpa) FROM
student_grades GROUP BY semester_end WHERE gpa IS NOT NULL
What does the following Update
statement do?
Update OrderTable set OrderDiscount=OrderDiscount*1.10
Update OrderTable set OrderDiscount=OrderDiscount*1.10
a. Increases OrderDiscount of first
row by 10%
b.
Increases OrderDiscount of all rows by 10%
c. Does nothing without where clause
d. Gives an error due to lack of
where clause
Which of the following is not a set
operator?
a. Union
b. Union all
c. Intersect
d.
Minus
e. Minus all
What clause should be used to
display the rows of a table in ascending order of a particular column?
a. Where
b.
Order By
c. Group By
d. Having
e. First Group By and then Having
f. Like
g. Between
With regard to statement (a) and
statement (b), Which of the following option is correct
(a) The FLOOR function returns the smallest integer greater or equal to the argument
(b) The CEIL function gives the largest integer equal to or less that the argument
(a) The FLOOR function returns the smallest integer greater or equal to the argument
(b) The CEIL function gives the largest integer equal to or less that the argument
a. (a) is true
b. (b) is true
c. both (a) and (b) are true
d.
both (a) and (b) are false
The purpose of the domain is to
constrain the set of valid values. True or false?
a.
True
b. False
What will happen if you query the
emp table as shown below:
select empno, DISTINCT ename, Salary from emp;
select empno, DISTINCT ename, Salary from emp;
a. EMPNO, unique value of ENAME and
then SALARY are displayed
b. EMPNO, unique value ENAME and
unique value of SALARY are displayed
c. DISTINCT is not a valid keyword
in SQL
d.
No values will be displayed because the statement will return an error
A production house needs a report
about the sale where total sale of the day is more than $20,000. Which query
should be used?
a. select * from orders where
sum(amount) > 20000
b. select orderdate, sum(amount)
from orders where sum(amount) > 20000 order by OrderDate
c.
select orderdate, sum(amount) from orders group by orderdate having sum(amount)
> 20000
d. select orderdate, sum(amount)
from orders group by OrderDate where sum(amount) > 20000
How many foreign key constraints can
a table have?
a. 1
b. 2
c. 3
d. 4
e. 5
f. 6
g.
None of the above
An RDBMS performs the following
steps:
1) It calculates the results of the group functions of each group
2) It groups those rows together based on the group by clause
3) It orders the groups based on the results of the group functions in the order by clause
4) It chooses and eliminates groups based on the having clause
5) It chooses rows based on the where clause
Arrange the above steps in the correct order of execution:
a. 4,3,5,1,2
b. 4,5,3,2,1
c.
5,2,1,4,3
d. 5,2,3,4,1
e. 2,3,1,4,5
f. 2,3,1,5,4
g. 1,2,3,4,5
h. 3,2,1,4,5
The Employee table uses alphanumeric
characters for their Employee ID field, which contains 7 digits prefixed with 3
characters. The alphanumeric characters refer to the department code. You want
to generate a list of all the department codes displayed in capital letters.
Which function(s) you will use for this query?
a.
CONCAT()
b. SUBSTR()
c. UPPER()
d. LOWER()
e. REPLACE()
Which component of an RDBMS
validates the syntax of the user's query?
a. Query Parser
b. The Database Manager
c.
Query Optimization
d. Database Administrator
What is a cluster?
a. Group of users
b. Group of indexes
c.
Method of storing tables that are intimately related and often joined together
into the same area of the disk
d. Compressed datafile
e. Segment of a partitioned table
f. Group of constraints
g. Group of databases
h. Group of roles
A production house has two sales
outlets. Both outlets are maintaining their data separately in schemas A and B
respectively. The Management wants to see the sale of both outlets in one report. Both outlets are using tables called
Sales which have identical structure. Which method you will adopt to create the
report?
a.
Select * from A.Sales join B.Sales
b. Select * from A.Sales union all
B.Sales
c. Select * from A.Sales, B.Sales
d. None of the above
_________ is an operation that
displays rows which meet a condition
a.
Restriction
b. Extraction
c. Projection
d. Intersection
e. Union
f. Minus
g. None of the above
What is the java.net.IDN class in
1.6?
a. Methods to resolve integrated
domain names (IDNs), such domain names are special embedded names
b. Methods to swap bytes between
network byte order and host byte order
c. Methods to convert
internationalized domain names (IDNs) between a normal Unicode representation
and an ASCII Compatible Encoding (ACE) representation
d. This class does not exist
e. None of the above
What is true regarding the socket
API?
a. All socket API classes and
methods are endian independent
b. The socket API can be used with
UNIX sockets
c. The socket API cannot be used
with UDP, only TCP
d. None of them is true
Which class contains a method to
create a directory?
a. File
b. DataOutput
c. Directory
d. FileDescriptor
e. FileOutputStream
0 comments:
Post a Comment