MULTIPLE CHOICE QUESTION SET-2
_______________ returns all the rows from one of the tables mentioned in the from clause even if the condition is not matched
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.
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?
a. It produces an error because COUNT (*) should be only in the HAVING clause and not in the
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.
To display the names of employees who earns more than the average salary of all
SELECT last_name, first_name
WHEREsalary > AVG(salary);
Which change should you make to achieve the desired results?
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.
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