odesk SQL test with answer
ODESK SQL TEST WITH ANSWER
SQL Test
Syllabus of the test Duration 40 minutes No of question 40
- SQL Concepts
- Constraints
- RDBMS Concepts
- SQL Operators
- SQL Commands
- Data Retrieval
- DDL
- SQL Functions
- Sub Queries
- DML
1 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
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
2 Which of the following is not a numeric group function?
Avg Count
Highest
Max
Stdev
Sum
3 Examine the data in the EMPLOYEES table given below:
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
4 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));
5 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
6 Which of the following clauses are not allowed in a single row sub-query?
From
Where
Group by
Having
Order by
7 What is the collection of information stored in a database at a particular moment called?
Schema
Instance
Table
Cluster
View
Index
8 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
9 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='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')
10 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
11 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)
12 What does the term DDL stand for?
Data Description Language
Dynamic Data Language
Data Definition Language
Data Derived Language
Descriptive Data Language
Data Description Language
Dynamic Data Language
Data Definition Language
Data Derived Language
Descriptive Data Language
13 The concept of data independence is similar to the concept of _______ Data type
Abstract data type
Consolidation
Isolation
14 What are the programs that execute automatically whenever DML operations are performed on tables called?
Triggers
Procedures
Functions
None of the above
15 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
16 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
17 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
18 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
19 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))
20 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
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
21 _________ is the operation that displays certain columns from the table.
Restriction
Intersection
Join
Union
Projection
Selection
Extraction
SubQuery
Restriction
Intersection
Join
Union
Projection
Selection
Extraction
SubQuery
22 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);
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);
23 Which of the following are aggregate functions in SQL?
Avg
Select
Order By
Sum
Union
Group by
Having
Avg
Select
Order By
Sum
Union
Group by
Having
24 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;
25 The primary key index does not allow ________ data in a field. 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;
Numeric
Character
Date
Null
Duplicate
All of the above
No comments:
Post a Comment