Week3 - Aggregate Functions / Explore

The questions can come in any order, so make sure you are selecting right option for all questions.

Select dept_name, dept_id, avg (salary)
from instructor
group by dept_name;
This statement is erroneous because

(b) dept_id is not used in group by clause

Which function returns the largest value of the column?

(c) MAX()

Aggregate functions can be used in the select list or the_______clause of a select statement or subquery.
They cannot be used in a ______ clause.

(b) Having, where

Which of the following is not a aggregate function ?

(c) with

All aggregate functions ignore NULLs except for ________.

(b) count(*)

Using GROUP BY ___________ has the effect of removing duplicates from the data.

(d) without aggregates

Below query is run in Oracle, is this query valid or invalid:

Select count(*) as X from Table_Name Group by column_name

(b) Valid

If you SELECT attributes and use an aggregate function, you must GROUP BY the non-aggregate attributes.

(b) True

Aggregate functions return a single result row based on groups of rows, rather than on single rows.

(b) True

You want all dates when any employee was hired.
Multiple employees were hired on the same date
and you want to see the date only once.

Query - 1
Select distinct hiredate
From hr.employee
Order by hiredate;

Query - 2
Select hiredate
From hr.employees
Group by hiredate
Order by hiredate;

Which of the above query is valid?

(c) Both

Select distinct Dept_name from instructor ;
How many row(s) are displayed ?

(a) 4

Choose the correct query to find the average salary of instructor

(d) SELECT AVG(salary) FROM instructor

Which function is used to identify the title with Least scope ?

(a) Min(Credits)

Which is true about the following query? select job_id, max(sal) from employee group by job_id having sal >=5000

(c) Display job wise highest salary only if the salary is greater than or equal to 5000

The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER (4) NOT NULL
CUSTOMER_NAME VARCHAR2 (100) NOT NULL
STREET_ADDRESS VARCHAR2 (150)
CITY_ADDRESS VARHCAR2 (50)
STATE_ADDRESS VARCHAR2 (50)
PROVINCE_ADDRESS VARCHAR2 (50)
COUNTRY_ADDRESS VARCHAR2 (50)
POSTAL_CODE VARCHAR2 (12)
CUSTOMER_PHONE VARCHAR2 (20)

The CUSTOMER_ID column is the primary key for the table.
You need to determine how dispersed your customer base is.
Which expression finds the number of different countries
represented in the CUSTOMERS table?

(d) COUNT(DISTINCT country_address)

Examine the structure of the EMP_DEPT_VU view:
Column Name Type Remarks
EMPLOYEE_ID NUMBER From the EMPLOYEES table
EMP_NAME VARCHAR2(30) From the EMPLOYEES table
JOB_ID VARCHAR2(20) From the EMPLOYEES table
SALARY NUMBER From the EMPLOYEES table
DEPARTMENT_ID NUMBER From the DEPARTMENTS table
DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table
Which SQL statement produces an error?

(d) None of the statements produce an error; all are valid.

Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of
different departments that have employees
with last name Smith?

(d) SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';