Wednesday, November 14, 2012

SQL SKILL TEST

Question 1

Examine the two SQL statements given below:


SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
 

The two statements produce identical results
The second statement returns an error

There is no need to specify DESC because the results are sorted in descending order by default

Question 2

Which of the following is not a numeric group function?

 
Avg
Count
Highest

Max
Stdev
Sum 
Question 3

Examine the data in the EMPLOYEES table given below:

ALLEN                10                     3000
MILLER               20                     1500
KING                 20                     2200
DAVIS                30                     5000

 
Which of the following Subqueries work?
 
SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY)); 
Question 4

Which of the following statements are true?

 
With DDL you can create and remove tables, schemas, domains, indexes and views

Select, Insert and Update are DCL commands
Grant and Revoke are DML commands
Commit and Rollback are DCL commands 
Question 5

Which of the following clauses are not allowed in a single row sub-query?

 
From
Where
Group by
Having
Order by
 
Question 6

What is the collection of information stored in a database at a particular moment called?

 
Schema
Instance

Table
Cluster
View
Index 
Question 7

The overall logical structure of a database can be expressed graphically by:

 
Data Flow Chart
Flow Chart
Directed Chart
Entity-Relationship Diagram

None of the above 
Question 8

Consider the following tables:

Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating       (the popularity of the book ON a scale of 1 TO 10)
LanguagŠµ               (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 which German books(if any) are more popular than all the French?
 
select bookname from books where language='German' and popularityrating = (select popularityrating from books where language='French')
select bookname from books where language='German' and popularityrating> (select popularityrating from books where language='French')
select bookname from books where language='French' and popularityrating> (select max(popularityrating) from books where language='German')
select bookname from books where language='German' and popularityrating> (select max(popularityrating) from books where language='French')
 
Question 9

Which statements are true for views?

 
The definition of a view is stored in data dictionary

Views provide a more secure way of retrieving data

Views are actually Tables and store data in the same manner as Tables
All of the above 
Question 10

Consider the following tables:


Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
LanguagŠµ (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 which Authors have written books on two or more subjects?
 
select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId having count(*)>1)

select AuthorName from Authors where BookId in (select BookId from Books group by BookId having count(*)>1)
select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId,Authorid having count(*)>1)
select AuthorName from Authors where Authorid in (select Authorid from Books group by Authorid having count(*)>1) 
Question 11

What does the term DDL stand for?

 
Data Description Language
Dynamic Data Language
Data Definition Language

Data Derived Language
Descriptive Data Language 
Question 12

The concept of data independence is similar to the concept of ________

 
Data type
Abstract data type

Consolidation
Isolation 
Question 13

What are the programs that execute automatically whenever DML operations are performed on tables called?

 
Triggers

Procedures
Functions
None of the above 
Question 14

What clause should be used to display the rows of a table in ascending order of a particular column?

 
Where
Order By

Group By
Having
First Group By and then Having
Like
Between 
Question 15

What is the error in the following query if the Students table contains several records?

SELECT name FROM students WHERE name =
(SELECT name FROM students ORDER BY name);
 

= should be replace by in operator

Order by clause in the subquery should be preceded with a group by clause
Order by clause in the subquery can be used only if the where and group by clauses have been applied
Group by clause should be applied to the outer query
An order by clause is not allowed in a subquery

There is no error 
Question 16

How can data be accessed by users who do not have direct access to the tables?

 
By creating views

By creating triggers
By creating stored procedures
None of the above 
Question 17

Consider the following tables:


Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating       (the popularity of the book ON a scale of 1 TO 10)
LanguagŠµ               (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 which Authors have written at least 1 book with a popularity rating of less than 5?
 
select authorname from authors where authorid in (select authorid from books where popularityrating<5)

select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
select authorname from authors where authorid in (select BookId from books where popularityrating<5)
select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5)) 
Question 18

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:


 
4,3,5,1,2
4,5,3,2,1
5,2,1,4,3

5,2,3,4,1
2,3,1,4,5
2,3,1,5,4
1,2,3,4,5
3,2,1,4,5 
Question 19

_________ is the operation that displays certain columns from the table.

 
Restriction
Intersection
Join
Union
Projection
Selection

Extraction
SubQuery 
Question 20

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?

 
Alter table t add primary key(c1);

Alter table t add constraint pk primary key(c1);
Alter table t add (constraint pk primary key(c1));
Alter table t add pk constraint primary key(c1); 
Question 21

Which of the following are aggregate functions in SQL?

 
Avg

Select
Order By
Sum

Union
Group by
Having 
Question 22

A table Students has a column called name which stores the names of the students. What will be the correct query to display the names of the students in reverse order?

 
Select name from students reverse;
Select name from students reverse name;
Select name from students order by name descending;
Select name from students order by name reverse;
Select name from students order by name desc;

Select desc name from students;
Select reverse name from students; 
Question 23

The primary key index does not allow ________ data in a field.

 
Numeric
Character
Date
Null

Duplicate

All of the above

No comments: