Home » oDesk Tests » oDesk SQL Test – Questions with Answers

oDesk SQL Test – Questions with Answers

Examine the two SQL statements given below:


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

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

One Response so far.

Leave a Reply

Your email address will not be published. Required fields are marked *

www.000webhost.com