ANSI SQL Test 2016 – Upwork Test Answers

Upwork ANSI SQL Test 2016

30 Answered Test Questions:
1. The simplest query must include at least________ and _________.
Answers:
a. A select clause
a. A where clause
a. A from clause
a. A group by clause
a. A having clause
a. An order by clause
2. Which of the following are aggregate functions in SQL?
Answers:
a. Avg
a. Select
a. Order By
a. Sum
a. Union
a. Group by
a. Having
3. Which logical operator can reverse the result?
Answers:
a. AND
a. OR
a. NOT
a. ANY
4. What are the programs that execute automatically whenever DML operations are performed on tables called?
Answers:
a. Triggers
a. Procedures
a. Functions
a. None of the above
5. The primary key index does not allow ________ data in a field.
Answers:
a. Numeric
a. Characte
a. Date
a. Null
a. Duplicate
a. All of the above
6. Which character function should be used to return a specified portion of a character string?
Answers:
a. CONCAT
a. LENGTH
a. SUBSTR
a. INITCAP
7. Which one of the following correctly selects rows from the table myTable that have null in column column1?
Answers:
a. SELECT * FROM myTable WHERE column1 is null
a. SELECT * FROM myTable WHERE column1 = null
a. SELECT * FROM myTable WHERE column1 EQUALS null
a. SELECT * FROM myTable WHERE column1 NOT null
a. SELECT * FROM myTable WHERE column1 CONTAINS null
8. What does the following Update statement do?

Update OrderTable set OrderDiscount=OrderDiscount*1.10
Answers:
a. Increases OrderDiscount of first row by 10%
a. Increases OrderDiscount of all rows by 10%
a. Does nothing without where clause
a. Gives an error due to lack of where clause
9. Consider the following table structure of students:
rollno number(4)
name varchar(20)
course varchar(20)
What will be the query to display the courses in which the number of students
enrolled is more than 5?
Answers:
a. Select course from students where count(course) > 5;
a. Select course from students where count(*) > 5 group by course;
a. Select course from students group by course;
a. Select course from students group by course having count(*) > 5; 
a. Select course from students group by course where count(*) > 5;
a. Select course from students where count(group(course)) > 5;
a. Select count(course) > 5 from students;
a. None of the above
10. 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 which Authors have written at least 1 book with a popularity rating of less than 5?
Answers:
a. select authorname from authors where authorid in (select authorid from books where popularityrating<5) 
a. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
a. select authorname from authors where authorid in (select BookId from books where popularityrating<5) a. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5)) 11. Examine the code given below: SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statements is correct with regard to this code?
Answers:
a. It returns employees whose salary is 50% more than $23,000
a. It returns employees who have 50% commission rate or salary greater than $23,000
a. It returns employees whose salary is 50% less than $23,000
a. None of the above
12. Which component of an RDBMS validates the syntax of the user’s query?
Answers:
a. Query Parse
a. The Database Manage
a. Query Optimization
a. Database Administrato
13. What does MOD() function do?
Answers:
a. Returns the remainder after division
a. Modifies the column definition
a. Modifies the definition of a table
a. None of the above
14. Which of the following is not a SQL operator?
Answers:
a. Between..and..
a. Like
a. In
a. Is null
a. Having
a. Not in
15. Which of the following operation is invalid with respect to dates?
Answers:
a. date + numbe
a. date * numbe
a. date – numbe
a. date – date
a. date + number/24
16. What does the term DDL stand for?
Answers:
a. Data Description Language
a. Dynamic Data Language
a. Data Definition Language
a. Data Derived Language
a. Descriptive Data Language
17. 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 which German books(if any) are more popular than all the French?
Answers:
a. select bookname from books where language=’German’ and popularityrating = (select popularityrating from books where language=’French’)
a. select bookname from books where language=’German’ and popularityrating > (select popularityrating from books where language=’French’)
a. select bookname from books where language=’French’ and popularityrating > (select max(popularityrating) from books where language=’German’)
a. select bookname from books where language=’German’ and popularityrating > (select max(popularityrating) from books where language=’French’) 
18. 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 which is the most popular book written in French?
Answers:
a. select bookname from books where language=’French’ and popularityrating = (select max(popularityrating) from books where language=’French’) 
a. select bookname from books where language=’French’ and popularityrating = (select max(popularityrating) from books Having language=’French’)
a. select bookname,max(popularityrating) from books where language=’French’ and max(popularityrating)
a. select bookname,max(popularityrating) from books where language=’French’ having max(popularityrating)
19. How many foreign key constraints can a table have?
Answers:
a. 1
a. 2
a. 3
a. 4
a. 5
a. 6
a. None of the above
20. When a table is dropped using a simple DROP statement, SQL performs some more operations simultaneously, select all the valid operations?
Answers:
a. Removes all rows from the table
a. Drops all the table’s indexes
a. Removes all dependent views
a. Removes all dependent procedures
21. You want to display the titles of books that meet the following criteria:

1. Purchased before November 11, 2002
2. Price is less than $500 or greater than $900

You want to sort the result by the date of purchase, starting with the most recently bought book.
Which of the following statements should you use?
Answers:
a. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’11/11/2002′ ORDER BY purchase_date;
a. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< ’11/11/2002′ ORDER BY purchase_date ASC;
a. SELECT book_title FROM books WHERE (price < 500 OR price >900) AND purchase_date DESC;
a. SELECT Book_title FROM books WHERE (price < 500 OR price >900) AND purchase_date<’11/11/2002′ ORDER BY purchase_date DESC; 
22. Which operator will be evaluated first in the following statement:

select (age + 3 * 4 / 2 – 8) from emp
Answers:
a. +
a. –
a. /
a. *
23. What clause should be used to display the rows of a table in ascending order of a particular column?
Answers:
a. Where
a. Order By
a. Group By
a. Having
a. First Group By and then Having
a. Like
a. Between
24. What is the correct order of clauses in the select statement?
1 select
2 order by
3 where
4 having
5 group by
Answers:
a. 1,2,3,4,5
a. 1,3,5,4,2
a. 1,3,5,2,4
a. 1,3,2,5,4
a. 1,3,2,4,5
a. 1,5,2,3,4
a. 1,4,2,3,5
a. 1,4,3,2,5
25. Which of the following statements is true?

(a)The Insert statement creates new rows
(b)The Update statement modifies the table structure
Answers:
a. only (a) is true
a. only (b) is true
a. oth (a) and (b) are true
a. oth (a) and (b) are false
26. 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?
Answers:
a. Select name from students reverse;
a. Select name from students reverse name;
a. Select name from students order by name descending;
a. Select name from students order by name reverse;
a. Select name from students order by name desc; 
a. Select desc name from students;
a. Select reverse name from students;
27. Choose the appropriate query for the Products table where data should be displayed primarily in ascending order of the ProductGroup column. Secondary sorting should be in descending order of the CurrentStock column.
Answers:
a. Select * from Products order by CurrentStock,ProductGroup
a. Select * from Products order by CurrentStock DESC,ProductGroup
a. Select * from Products order by ProductGroup,CurrentStock
a. Select * from Products order by ProductGroup,CurrentStock DESC
a. None of the above
28. 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

What is true about them?
Answers:
a. The two statements produce identical results
a. The second statement returns an error
a. There is no need to specify DESC because the results are sorted in descending order by default
29. The overall logical structure of a database can be expressed graphically by:
Answers:
a. Data Flow Chart
a. Flow Chart
a. Directed Chart
a. Entity-Relationship Diagram
a. None of the above
30. In an RDBMS, which term is used to describe ‘data about data’?
Answers:
a. Meta data
a. Data dictionary
a. Database
a. None of the above