Information Management

MAX

What group function is used to return the maximum value ignoring nulls?

JOIN

This type of JOIN clause is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in all matched columns. Which of the following refers to the given statement?

SELECT AVG(salary), department_id FROM Employees GROUP BY department_id;

The HR wants to retrieve the information of the employee’s average salary from each department. Display the average salary and department id. Which of the following SQL statements below satisfies the condition?

Subquery

It is an inner query that is nested within an outer query.

MIN

What group function is used to return minimum value ignoring nulls?

Single-row

Which type of inner query returns only one row to the outer query?

Cross

What type of join returns the Cartesian product from two tables?

SELECT SUM(salary) FROM f_staffs WHERE id IN(12, 9);

Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9. Select the correct SQL statement below.

SUM

What function is used to calculate the sum ignoring null values?

ON

The use of ______ clause allows a natural join based on an arbitrary condition or two columns with different names.

Alias

It is used to give a table another name to simplify queries and improve performance

Full Outer

Performs a join on two tables, retrieves all the rows in the Left table, even if there is no match in the Right table. It also retrieves all the rows in the Right table, even if there is no match in the Left table.

Equijoin

The values in a column in one table are equal to a value in another table is what type of join?

SELECT AVG(MAX(salary)) FROM Employees GROUP BY job_id;

SQL statement that displays the maximum average salary?

Outer

What type of join returns the unmatched rows as well as matched rows?

MAX and MIN

Which of the following group functions can be used for numeric, character and date data types?

USING

It is use to perform an equijoin based on one specified column name.

SELECT department_id, COUNT(last_name) FROM employees GROUP BY department_id;

You write a query that will display the number of employees from each department but your query (below) encounters an error ‘ORA-00937: not a single-group function’. Which of the following SQL statements will solve this error?

SELECT department_id, count (last_name) FROM employees;

Right

aA type of outer join performs a join on two tables, retrieves all the rows in the Right table even if there is no match in the Left table?

Outer Query

It accepts a value from the inner query to complete its SELECT statement.

SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id)

SQL statement to join the Oracle database locations and departments table using the location_id column?

Join condition containing something other than an equality operator

What condition requires the creation of a non-equijoin?

IN

The _____ operator is used within the outer query WHERE clause to select only those rows which are IN the list of values returned from the inner query.

GROUP BY

Which of the following clause specifies how rows must be grouped?

SELECT COUNT(*) FROM d_songs;

What correct SQL statement will give us information about how many songs are listed in the DJs on Demand D_SONGS table?

COUNT = 4 DISTINCT = 3

What values will be returned when the statement below is issued? Based on the given data sets and SQL statement below.

SELECT COUNT(shoe_color), COUNT(DISTINCT shoe_color) FROM shoes;

Distinct

What keyword is used to return only non-duplicate values or combinations of non-duplicate values in a query?

Single-row

Which type of inner query returns only one row to the outer query?

Alias

It is used to give a table another name to simplify queries and improve performance

>

Select the correct multiple-row comparison operators in the outer query WHERE clause of each of the following: 

Which employees have salaries more than the average salary of Abel?

WHERE salary __________(SELECT salary ...