oDesk test answers - SQL Test
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
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
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:
Post a Comment