SQL Test Answer Part-07

 

61. 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?

a. The two statements produce identical results

b. The second statement returns an error

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

Answer: b.

62. 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?

a. Select name from students reverse;

b. Select name from students reverse name;

c. Select name from students order by name descending;

d. Select name from students order by name reverse;

e. Select name from students order by name desc;

f. Select desc name from students;

g. Select reverse name from students;

Answer: e.

63. A production house needs a report about the sale where total sale of the day is more than $20,000. Which query should be used?

  1. Select * from orders where sum(amount) > 20000
  2. Select orderdate , sum(amount) from orders where sum(amount) > 20000 order by OrderDate
  3. Select orderdate , sum(amount) from orders group by orderdate having sum(amount) > 20000
  4. Select orderdate , sum(amount) from orders group by OrderDate where sum(amount) > 20000

Answer: c

64. You are maintaining data for its products in the products table, and wants to see the products which are 50 or greater than 50 from the minimum stock limit. The structure of the products table is:

ProductID

ProductName

CurrentStock

MinimumStock

Two possible queries are:

(a) Select*from products where currentStock > minimumStock + 50

(b) Select*from products where currentStock – 50 > minimumStock

Choose the appropriate option with regard to the above queries.

a. (a) is correct

b. (b) is correct

c. Both (a) and (b) are correct

d. Both (a) and (b) are incorrect

Answer: c.

65. Evaluate the following SQL statement:

SELECT e.employee_id, (.15*e.salary)+(.5*e.commission_pct)+(s.sales_amount*(.35*e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id=s.emp_id;

What will happen if all the parentheses are removed from the calculation?

  1. The value displayed in the CALC_VALUE column will be lower
  2. The value displayed in the CALC_VALUE column will be higher
  3. There will be no difference in the value displayed in the CALC_VALUE column
  4. An error will be reported

Answer: c.

66. 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?

  1. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date <’11/11/2002’ ORDER BY purchase_date:
  2. SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date <’11/11/2002’ ORDER BY purchase_date ASC;
  3. SELECT book_title FROM books WHERE (price <>900) AND purchase_date DESC;
  4. SELECT book_title FROM books WHERE (price <>900) AND purchase_date <’11/11/2002’ ORDER BY purchase_date DESC;

Answer: d.

67. 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?

  1. It returns employees whose salary is 50% more than $23,000
  2. It returns employees who have 50% commission rate or salary greater than $23,000
  3. It returns employees whose salary is 50% less than $23,000
  4. None of the above

Answer: b.

68. 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 the names of the Authors who have written more than 1 book?

  1. select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId having count(*)>1)
  2. select AuthorName from Authors where BookId in (select BookId from Books group by BookId having count(*)>1)
  3. select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId,Authorid having count(*)>1)
  4. select AuthorName from Authors where Authorid in (select Authorid from Books group by Authorid having count(*)>1)
    Answer: a.

69. 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 is the most popular book written in French?

  1. select bookname from books where language =’ French’ and popularityrating = (select max(popularityrating) from books where language=’French’)
  2. select bookname from books where language=’ French’ and popularityrating> (select max(popularityrating) from books having language=’French’)
  3. select bookname, max(popularityrating) from books where language=’French’ and max(popularityrating)
  4. select bookname, max(popularityrating) from books where language=’French’ having max(popularityrating)

Answer: a

70. 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?

  1. select authorname from authors where authorid in (select authorid from books where popularityrating<5 data-blogger-escaped-o:p=”o:p”>
  2. select authorname from authors where authorid in (select authorid from books where popularityrating&lt;=5)
  3. select authorname from authors where authorid in (select BookId from books where popularityrating<5 data-blogger-escaped-o:p=”o:p”>
  4. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5)) 

 

Answer: a.

Comments

comments

Leave a Comment

Time limit is exhausted. Please reload CAPTCHA.