SUBQUERY

MULTIPLE CHOICE QUESTION SET-2

Answers:

1)

_______________ returns all the rows from one of the tables mentioned in the from clause even if the condition is not matched

a.Non-Equi join

b.Self-join

c.outer join

d.Equi join

2)

In which two cases would you use the USING clause? (Choose two)

Select one or more:

a. The tables to be joined have columns with the same name and compatible data types.

 b. The tables to be joined have multiple NULL columns.

 c. You want to create a nonequijoin.

d. The tables to be joined have columns of the same name and different data types.

3)

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times:

 

SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*)>10

GROUP BY prod_id HAVING COUNT(*)>10;

Which statement is true regarding this SQL statement?

Select one:

a. It produces an error because COUNT (*) should be only in the HAVING clause and not in the

WHERE clause.

 b. It produces an error because COUNT (*) should be specified in the SELECT clause also.

 c. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).

 d. It executes successfully and generates the required result.

4)

To display the names of employees who earns more than the average salary of all

employees.

 

SELECT last_name, first_name

FROMemployee

WHEREsalary > AVG(salary);

 

Which change should you make to achieve the desired results?

Select one:

 a. Move the function to the SELECT clause and add a GROUP BY clause.

b. Use a subquery in the WHERE clause to compare the average salary value.

 c. Change the function in the WHERE clause.

 d. Move the function to the SELECT clause and add a GROUP BY clause and a HAVING clause.

5)

Which statements are true regarding the USING and ON clauses in table joins?

 

Select one or more:

a. The WHERE clause can be used to apply additional conditions in SELECT statement

containing the ON or the USING clause.

b. The ON clause can be used to join tables on columns that have different names but compatible data types.

c. Both USING and ON clauses can be used for equijoins and nonequijoins

d. Maximum of one pair of columns can be joined between two tables using the ON clause

Answers:

  • Loginhackers Blogger
  • Contact us via Twitter Loginhackers
  • Contact us via Instagram
  • loginhackers facebook contact id

Free Premium | Spotify premium for free | Grammarly free premium | Netflix free accounts | Netflix mod | Spotify mod | Hotstar mod | Spotify premium mod apk | free amazon prime account