Which Of The Following Is True Concerning Animal Form And Function?

Using the Grouping Functions Questions

one. Which of the post-obit is NOT a Grouping BY office?

Answer: C. NVL is a general function used to provide alternate value to the Zip values. The functions MAX, MIN and AVG can be used every bit GROUP Past functions.

ii. Which of the following functions can be used without Group BY clause in SELECT query?

COUNT
MAX
MIN
AVG

Answer: A, B, C, D. All the listed grouping functions can be used in a query provided no other columns are selected in the SELECT query.

3. Which of the following SELECT query returns the department number with maximum bacon compensated to an employee? (Consider the table structure equally given)

SQL> DESC employees  Proper name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Nada NUMBER(vi)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Non Zero VARCHAR2(25)  EMAIL			 Non Zippo VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT NULL Date  JOB_ID 		 NOT Cipher VARCHAR2(10)  Bacon 			  NUMBER(8,ii)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

SELECT department_id , max(salary ) FROM employees ;

SELECT department_id , max(salary ) FROM employees  GROUP BY department_id ;

SELECT max(salary ) FROM employees  GROUP BY department_id ;

```
SELECT max(bacon ) FROM employees ;
```

Answer: B. The MAX function can be used to return the maximum bacon in a department where each grouping is formed by a department.

4. Which of the following statements are true about the COUNT function?

The COUNT function counts the number of rows
The COUNT(*) function counts the number of rows with duplicates and NULL values
The COUNT(DISTINCT) role counts the number of distinct rows
COUNT(*) is equivalent to COUNT(ALL)

Answer: B. The COUNT(*) counts the number of rows including duplicates and NULLs. Utilize DISTINCT and ALL keyword to restrict duplicate and NULL values.

five. What are the appropriate data types accepted by GROUP BY functions?

Nested Tables
NUMBER
CLOB
DATE

Answer: B. The data types for the functions with an argument may be CHAR, VARCHAR2, NUMBER or DATE.

6. A table T_COUNT has 12 number values as 1, ii, 3, 32, 1, 1, nil, 24, 12, naught, 32, cipher. Predict the output of the beneath query.

SELECT COUNT (*) FROM t_count;

12
six
9
Throws exception because COUNT function doesn't works with Goose egg values

Reply: A. The COUNT(*) counts the number of rows including duplicates and NULLs. Apply Distinct and ALL keyword to restrict duplicate and NULL values.

7. A tabular array T_COUNT has 12 number values as 1, 2, three, 32, 1, one, cypher, 24, 12, null, 32, null. Predict the output of the beneath query.

SELECT COUNT (num) FROM t_count;

12
6
9
Throws exception because COUNT function doesn't works with NULL values

Answer: C. COUNT (column) ignores the Naught values but counts the duplicates.

8. A table T_COUNT has 12 number values every bit 1, 2, 3, 32, ane, 1, zero, 24, 12, naught, 32, nix. Predict the output of the below query.

SELECT COUNT (ALL num) FROM t_count;

12
half-dozen
9
Throws exception because COUNT role doesn't works with NULL values

Answer: C. COUNT(ALL column) ignores the Goose egg values simply counts the duplicates.

9. A table T_COUNT has 12 number values as i, ii, 3, 32, 1, one, null, 24, 12, cypher, 32, null. Predict the output of the below query.

SELECT COUNT (DISTINCT num) FROM t_count;

12
half dozen
ix
Throws exception because COUNT function doesn't works with NULL values

Respond: B. COUNT (Singled-out cavalcade) counts the distinct not nil values.

x. What happens when the below query is executed in SQL* Plus?

SELECT COUNT() FROM dual;

Executes successfully and returns no output
Executes successfully and returns output as 'ane'
Throws exception "ORA-00909: invalid number of arguments"
Throws exception "ORA-00904: "COUNT": invalid identifier" because COUNT function doesn't works with DUAL table

Answer: C. COUNT role requires minimum one statement which tin can exist either the column with [ALL | Singled-out] modifier or '*'.

11. Here are few statements about VARIANCE part in SQL.

i. The part accepts multiple numeric inputs and returns variance of all the values

ii. The function accepts a number column and returns variance of all column values including NULLs

iii. The function accepts a number column and returns variance of all column values excluding NULLs
Chose the correct combination from the below options.

i and iii
i and ii
ii
three

Answer: C. The VARIANCE function accepts unmarried numeric statement equally the column proper name and returns variance of all the column values considering NULLs.

12. Which of the following is NOT a Grouping BY extensions in SQL?

GROUP Past
Group SETS
CUBE
ROLLUP

Answer: A. GROUPING SETS operations can be used to perform multiple GROUP By aggregations with a unmarried query.

13. Select the correct statements nigh the below query. Consider the tabular array structure as given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Non NULL VARCHAR2(25)  Electronic mail			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Non NULL DATE  JOB_ID 		 NOT Cipher VARCHAR2(x)  SALARY 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

SELECT department_id , SUM(salary ) FROM employees  GROUP BY department_id ;

SUM is a group past function considering information technology processes group of employees working in a department
SUM is an aggregate function because it produces one outcome per group of data
SUM is a single row function because it returns single value for a group i.eastward. section
SUM is a grouping by extension function because it uses GROUP BY clause to logically grouping the departments

Answer: A. SUM is a group function which calculates the sum of salaries of a group of employees working in a department.

14. Which clause is used to filter the query output based on aggregated results using a group past part?

WHERE
LIMIT
GROUP WHERE
HAVING

Reply: D. HAVING Clause is used for restricting group results. You employ the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the ground of aggregate data. The HAVING clause can precede the Grouping By clause, but it is recommended that you place the Grouping Past clause first considering it is more than logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

15. Examine the given tabular array construction and predict the event of the following query.

SQL> DESC employees  Proper noun			 Nada?	  Blazon  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non NULL NUMBER(half dozen)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT Aught VARCHAR2(25)  EMAIL			 NOT Zip VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT NULL Engagement  JOB_ID 		 Not NULL VARCHAR2(ten)  Salary 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(half-dozen)  DEPARTMENT_ID			  NUMBER(4)

SELECT count(*) FROM employees  WHERE comm = NULL;

The query returns the number of employees who have no commission
The query throws error because equal sign cannot be used when searching for NULL value
The query returns the number of employees in a department whose commission is NULL value
The query throws mistake because GROUP BY clause is missing in the query

Answer: B. Excluding out NULLs using WHERE condition is a way to direct the query to ignore NULLs. But hither the usage of IS Nil operator is wrong. The status should be 'WHERE comm IS Nix'.

16. Which of the following statements is true well-nigh the group functions?

The MIN role can be used merely with numeric data.
The MAX function tin can be used merely with appointment values.
The AVG function can exist used just with numeric data.
The SUM function canít be role of a nested function.

Answer: C. The AVG function can be only used with numeric values. Other functions which have such restriction are SUM, STDDEV and VARIANCE.

17. Which of the following is a valid SELECT argument?

SELECT AVG(retail-cost) FROM books GROUP BY category;

SELECT category, AVG(retail-toll) FROM books;

SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-price) > 8.56 GROUP BY category;

SELECT category, AVG(retail-toll) Profit FROM books Group Past category HAVING profit > 8.56;

Answer: A. Column aliases cannot exist used in GROUP Past or HAVING clause.

18. Which of the following statements is right?

The WHERE clause can contain a group function only if the office isnít also listed in the SELECT clause.
Group functions canít be used in the SELECT, FROM, or WHERE clauses.
The HAVING clause is ever candy before the WHERE clause.
The GROUP Past clause is always candy before the HAVING clause.

Answer: D. Though Oracle doesn't raise fault if HAVING clause precedes the Group BY clause but information technology is processed only later on the GROUP BY clause is processed and group are ready to be filtered.

19. Which of the following is not a valid SQL argument?

SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = iv;

SELECT MIN(pubdate) FROM books WHERE category = 'COOKING';

SELECT COUNT(*) FROM orders WHERE client# = 1005;

SELECT MAX(COUNT(customer#)) FROM orders Grouping Past customer#;

Reply: A.

twenty. Which of the following statements is correct?

The COUNT function can exist used to determine how many rows comprise a Cypher value.
Only distinct values are included in grouping functions, unless the ALL keyword is included in the SELECT clause.
The WHERE clause restricts which rows are candy.
The HAVING clause determines which groups are displayed in the query results.

Answer: C, D. The WHERE clause restricts the rows before they are grouped and processed while HAVING clause restricts the groups.

21. Which of the following is a valid SQL argument?

SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders Grouping BY customer# WHERE customer# = 1001;

SELECT customer#, COUNT(order#) FROM orders Grouping By customer#;

SELECT customer#, COUNT(guild#) FROM orders GROUP BY COUNT(order#);

SELECT client#, COUNT(order#) FROM orders Grouping By gild#;

Reply: B. The Group BY clause must comprise all the columns except the one which is used inside the group function.

22. Which of the following SELECT statements lists just the volume with the largest profit?

SELECT title, MAX(retail-cost) FROM books Grouping BY title;

SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-price);

SELECT title, MAX(retail-price) FROM books;

None of the above

Reply: A.

23. Which of the post-obit statement(s) is/are correct?

i. A grouping function can be nested inside a group function.

2. A group function can be nested inside a unmarried-row office.

3. A single-row part tin be nested inside a grouping office.

1
two
3
one and iii

Answer: A, B, C. Grouping functions can be nested only to a depth of 2. Group functions can be nested inside unmarried-row functions (AVG embedded in a TO_CHAR function). In addition, unmarried-row functions can be nested inside group functions.

24. Which of the following functions is used to summate the full value stored in a specified column?

COUNT
ADD
TOTAL
SUM

Reply: D. SUM function is used to get the addition of numeric values.

25. Which of the following SELECT statements lists the highest retail price of all books in the Family category?

SELECT MAX(retail) FROM books WHERE category = 'Family';

SELECT MAX(retail) FROM books HAVING category = 'FAMILY';

SELECT retail FROM books WHERE category = 'Family unit' HAVING MAX(retail);

None of the above

Answer: A. Since the category FAMILY has to be restricted earlier group, table rows must be filtered using WHERE clause and non HAVING clause.

26. Which of the post-obit functions can be used to include Cypher values in calculations?

Respond: B.NVL is a general function to provide alternate values to the NULL values. It can really make a deviation in arithmetic calculations using AVG, STDDEV and VARIANCE group functions.

27. Which of the following is non a valid argument?

You must enter the ALL keyword in a group function to include all duplicate values.
The AVG role can be used to find the average calculated difference betwixt two dates.
The MIN and MAX functions tin can be used on VARCHAR2 columns.
All of the in a higher place

Answer: A. The ALL keyword counts duplicates but ignores NULLs. Duplicates are also included with '*' and column name specification.

28. Which of the following SQL statements determines how many total customers were referred by other customers?

SELECT customer#, SUM(referred) FROM customers GROUP Past customer#;

```
SELECT COUNT(referred) FROM customers;
```
```
SELECT COUNT(*) FROM customers;
```

SELECT COUNT(*) FROM customers WHERE referred IS NULL;

Answer: B. Considering all customers as one group, COUNT(referred) volition count merely those who are referred by someone. COUNT(referred) volition ignore Zip values of the cavalcade.

29. Determine the correct society of execution of following clauses in a SELECT argument.

1.SELECT

2.FROM

3.WHERE

4.Grouping Past

v.HAVING

6.ORDER By

2-3-4-5-1-half dozen
1-2-iii-4-5-6
6-5-four-3-2-1
5-4-ii-3-1-vi

Answer: A. Processing society starts from FROM clause to get the table names, then restricting rows using WHERE clause, grouping them using Grouping BY clause, restricting groups using HAVING clause. Lodge By clause is the last i to be processed to sort the final data set.

30. Which of the beneath clauses is used to group a prepare of rows based on a cavalcade or prepare of columns?

HAVING
WHERE
GROUP BY
GROUPING

Answer: C. GROUP Past clause forms the groups of the data based on the column list specified.

31. Which of the following group functions can be used for population variance and population standard difference problems?

VAR_POP
STDDEV_POP
VARIANCE
STDDEV_SASMP

Respond: A, B.

32. Select the positions in a SELECT query where a grouping function tin appear.

SELECT statement
WHERE clause
ORDER BY clause
GROUP By clause

Reply: A, C, D. Group functions tin appear in SELECT, ORDER BY and HAVING clause. Oracle raises exception if group functions are used in WHERE or Group BY clauses.

33. Examine the structure of the EMPLOYEES tabular array as given. Which query volition return the minimum salary in each section?

SQL> DESC employees  Proper noun			 Zippo?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not NULL NUMBER(six)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Not Naught VARCHAR2(25)  Email			 Not Naught VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Not Cypher Appointment  JOB_ID 		 Non Cypher VARCHAR2(10)  Bacon 			  NUMBER(8,two)  COMMISSION_PCT 		  NUMBER(ii,2)  MANAGER_ID			  NUMBER(half-dozen)  DEPARTMENT_ID			  NUMBER(4)

SELECT department_id , MIN (salary ) from EMPLOYEES ;

SELECT department_id , MIN (salary ) from EMPLOYEES  Group Past department_id ;

SELECT department_id , MIN (salary ) from EMPLOYEES  Grouping By bacon ;

SELECT department_id , MIN (salary ) from EMPLOYEES  Group BY employee_id ;

Answer: B. MIN role returns the minimum salary in a group formed by section.

34. Examine the structure for the tabular array EMPLOYEES and Translate the output of the beneath query

SQL> DESC employees  Name			 Zero?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Nada NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT Zero VARCHAR2(25)  EMAIL			 Non Goose egg VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(twenty)  HIRE_DATE		 Not NULL DATE  JOB_ID 		 Not NULL VARCHAR2(10)  Bacon 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(ii,two)  MANAGER_ID			  NUMBER(half dozen)  DEPARTMENT_ID			  NUMBER(4)

SELECT COUNT(*), COUNT(all comm) FROM employees ;

It throws fault because but one aggregate function can be used in a query.
Information technology throws error because GROUP By clause is missing.
It executes successfully and returns aforementioned values for both.
Information technology executes successfully where COUNT(*) including NULLs and COUNT(all comm) excluding NULLs.

Respond: D.

35. Which of the following are true almost group functions?

You tin use group functions in any clause of a SELECT statement.
Yous can employ group functions but in the cavalcade listing of the select clause and in the WHERE clause of a SELECT statement.
You tin mix single row columns with group functions in the column list of a SELECT statement past group on the single row columns.
You tin laissez passer column names, expressions, constants, or functions as parameter to an grouping office.

Respond: C. Group functions can be nested just to a depth of two. Group functions can be nested inside single-row functions (AVG embedded in a TO_CHAR function). In add-on, unmarried-row functions can be nested inside grouping functions.

36. Examine the structure of the table EMPLOYEES as given. You want to create a "emp_dept_sales" view past executing the following SQL statements.

SQL> DESC employees  Proper name			 Null?	  Blazon  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Goose egg NUMBER(half-dozen)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT Naught VARCHAR2(25)  EMAIL			 NOT Cipher VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Non NULL DATE  JOB_ID 		 Not NULL VARCHAR2(ten)  SALARY 			  NUMBER(8,two)  COMMISSION_PCT 		  NUMBER(two,2)  MANAGER_ID			  NUMBER(half dozen)  DEPARTMENT_ID			  NUMBER(4)

CREATE VIEW emp_dept_sales AS SELECT d.department_name , sum(e.salary ) FROM employees  east, departments  d where e.department_id =d.department_id  Grouping past d.department_name ;

Which statement is truthful regarding the execution of the above argument?

The view will exist created and you tin perform DLM operations on the view
The view volition not exist created because the join statements are not allowed for creating a view
The view will not be created because the Grouping By clause is not allowed for creating a view
The view will be created but no DML operations will be allowed on the view

Respond: D. Rules for Performing DML Operations on a View. You cannot add data through a view if the view includes group functions or a Grouping BY clause or Singled-out keyword. The pseudo column ROWNUM keyword Columns defined past expressions Not Nothing columns in the base of operations tables that are not selected by the view.

37. Which of the following statements are truthful regarding views?

A sub query that defines a view cannot include the Grouping BY clause
A view is created with the sub query having the Singled-out keyword tin can exist updated
A Data Manipulation Linguistic communication (DML) operation can be performed on a view that is created with the sub query having all the NOT Nothing columns of a table
A view that is created with the sub query having the pseudo column ROWNUM keyword cannot exist updated

Answer: C, D. Rules for Performing DML Operations on a View. You lot cannot add together data through a view if the view includes grouping functions or a Group BY clause or Singled-out keyword. The pseudo column ROWNUM keyword Columns defined by expressions NOT Zippo columns in the base tables that are not selected by the view.

38. Examine the table construction equally given.

SQL> DESC departments  Proper noun			 Goose egg?	  Blazon  ----------------------- -------- ----------------  DEPARTMENT_ID		 NOT NULL NUMBER(four)  DEPARTMENT_NAME	 Not NULL VARCHAR2(thirty)  MANAGER_ID			  NUMBER(half dozen)  LOCATION_ID			  NUMBER(four)

Which clause in the beneath SQL query generates error?

SELECT department_id , avg(salary ) FROM departments  WHERE upper(chore) in ('SALES','CLERK') Grouping BY job ORDER BY department_id ;

WHERE
SELECT
ORDER BY
GROUP Past

Answer: D. GROUP Past clause must comprise all the columns appearing in the SELECT statement. It raises error because Chore is not a selected column. It should take used DEPARTMENT_ID in placed of JOB.

39. Examine the table structure equally given.

SQL> DESC employees  Name			 Zilch?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Aught NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Not NULL VARCHAR2(25)  EMAIL			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT Goose egg Date  JOB_ID 		 Non NULL VARCHAR2(x)  SALARY 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,ii)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

Which of the below SELECT query will display the maximum and minimum salary earned by each job category?

SELECT job, MAX(salary ), MIN (bacon ) FROM employees  Group By department_id ;

SELECT job, MAX(bacon ), MIN (bacon ) FROM employees  Group BY chore;

SELECT job, MAX(salary ), MIN (salary ) FROM employees ;

Ii amass functions cannot be used together in SELECT statement.

Answer: B. More 1 grouping office can announced in the SELECT statement.

twoscore. Consider the table structure as given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not Zero NUMBER(half dozen)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 NOT Aught VARCHAR2(25)  Electronic mail			 NOT Zilch VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(twenty)  HIRE_DATE		 Not Cipher Appointment  JOB_ID 		 Not Nothing VARCHAR2(10)  Salary 			  NUMBER(viii,2)  COMMISSION_PCT 		  NUMBER(2,ii)  MANAGER_ID			  NUMBER(half-dozen)  DEPARTMENT_ID			  NUMBER(4)

Examine the mistake in the below query.

SELECT department_id  FROM employees  WHERE hiredate > '01-Jan-1985' AND COUNT(*) > 2 Group by department_id  HAVING SUM (salary ) > 1000;

Information technology executes successfully and generates the required result.
It produces an error because COUNT(*) should be specified in the SELECT clause also.
It executes successfully simply produces no result because COUNT(prod_id) should be used instead of COUNT(*).
Information technology produces an error because COUNT(*) should be merely in the HAVING clause and not in the WHERE clause.

Answer: D. Group functions cannot be used in WHERE clause. The tin appear in SELECT, HAVING and ORDER BY clause.

41. Examine the tabular array structure every bit given.

SQL> DESC employees  Proper name			 Cipher?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(twenty)  LAST_NAME		 NOT NULL VARCHAR2(25)  Electronic mail			 Non NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Non NULL DATE  JOB_ID 		 NOT NULL VARCHAR2(10)  SALARY 			  NUMBER(viii,2)  COMMISSION_PCT 		  NUMBER(two,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

Predict the outcome of the below query

SELECT chore, COUNT(employee_id ),sum(salary ) FROM employees  Grouping BY job HAVING SUM (salary ) > 5000;

It executes successfully and lists the count of employees under each job category but ignores the HAVING clause since "bacon " is non in Grouping By clause.
It throws mistake considering HAVING clause is invalid.
It throws mistake because "salary " is non included in the GROUP BY clause.
It executes successfully and lists the count of employees under each category having sum of salary greater than 5000.

Answer: D. The HAVING clause restricts the grouping results. COUNT role is used for counting while SUM is used for adding the numeric values.

42. What is true of using group functions on columns that contain Naught values?

Group functions on columns ignore NULL values.
Group functions on columns returning dates include NULL values.
Grouping functions on columns returning numbers include NULL values.
Group functions on columns cannot be accurately used on columns that comprise NULL values.

Answer: A. Except COUNT office, all the group functions ignore NULL values.

43. Which of the following statetments are true about the usage of GROUP Past columns in a subquery?

Subqueries can contain Grouping BY and Order BY clauses.
Subqueries cannot incorporate GROUP Past and Lodge BY clauses.
Subqueries can contain ORDER Past only non the GROUP By clause.
Subqueries cannot contain Guild By just tin can accept Grouping By clause.

Reply: A. Like the chief query, a subquery can comprise a GROUP BY as well every bit Social club BY clause.

Examine the table construction as given and respond the questions 44 to 49 that follow.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Not Null VARCHAR2(25)  Electronic mail			 NOT Nothing VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(twenty)  HIRE_DATE		 NOT Zippo Date  JOB_ID 		 Non Nil VARCHAR2(10)  Salary 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,ii)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

44. Predict the consequence of the below query

SELECT avg(max(salary )) FROM employees  GROUP By department_id  HAVING avg(max(salary ))>100;

Information technology executes successfully.
It gives an error because the HAVING clause is not valid.
It gives an error because the Group BY expression is not valid.
It gives an error because aggregate functions cannot exist nested in SELECT statement.

Answer: B. The HAVING clause doesn't allows nesting of aggregate functions.

45. Predict the output of the beneath query

SELECT avg(salary ), department_id  FROM employees  Group BY department_id ;

Information technology gives error because an aggregate role cannot appear just subsequently SELECT clause.
It gives mistake because Group Past clause is invalid.
It executes without errors just produces no output.
Information technology executes successfully and gives average bacon in each department.

Answer: D. Group functions tin can be used in whatsoever sequence (before or afterward the grouping by columns) in a SELECT query.

46. Predict the output of the below query

SELECT lower(task),avg(bacon ) FROM employees  Grouping Past upper(job);

Information technology executes successfully and displays "task" in lower example.
It executes successfully but display "chore" in original example.
Information technology throws error considering singe row and aggregate functions cannot be used together.
It throws error because example conversion in the SELECT listing mismatches with the case conversion GROUP Past clause.

Respond: D. The role LOWER, being a single row role must be specified in the Group By clause to base of operations the grouping of EMPLOYEES data.

47. Which of the below query executes successfully?

SELECT employee_id , COUNT(hiredate-sysdate) FROM employees ;

SELECT AVG(salary ), MAX(bacon ) FROM employees ;

SELECT AVG(bacon ), MAX(salary ) FROM employees  GROUP Past department_id ;

```
SELECT AVG(hiredate) FROM employees ;
```

Respond: B, C. The showtime query operates of the whole EMPLOYEES data while the second one processes the data in groups of section.

48. Identify the mistake in the beneath SELECT statement.

SELECT department_id , AVG (bacon ) FROM employees  GROUP BY department_id  HAVING department_id  > 10;

Information technology executes successfully and displays average salary of departments higher than ten.
It throws error because non aggregated cavalcade cannot exist used in HAVING clause.
It executes successfully merely displays incorrect result for the departments.
It throws error considering HAVING clause must be placed before GROUP BY clause.

Answer: A. Group By expressions can be used in HAVING clause to filter out the groups from the final data set.

49. Predict the output of the below query

SELECT department_id , AVG (salary ) FROM employees  Grouping Past department_id  HAVING (department_id >10 and AVG(salary )>2000);

It throws error because multiple weather cannot exist given in HAVING clause.
It throws fault because a non aggregate cavalcade cannot be used in HAVING clause.
It executes successfully and displays average salary of department higher than ten and greater than 2000.
It executes successfully but no upshot is displayed.

Reply: C. The HAVING clause can impose multiple weather joined using AND or OR operator filter the groups.

50. Which of the following group functions tin be used with DATE values?

AVG
MIN
SUM
COUNT

Reply: B, D. The group function AVG and SUM can be used with numeric data simply.

51. Which of the post-obit statements are true?

AVG and SUM can be used only with numeric information types.
STDDEV and VARIANCE tin can be used only with numeric information types.
MAX can be used with LONG information type.
MAX and MIN cannot be used with LOB or LONG data types.

Respond: A, B, D. The group functions AVG,SUM, VARIANCE and STDDEV can be used with numeric data only. None of the group functions tin be used with LONG data type.

52. Examine the tabular array structure as given.

SQL> DESC employees  Proper name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(twenty)  LAST_NAME		 NOT NULL VARCHAR2(25)  EMAIL			 Not NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Non Null Engagement  JOB_ID 		 NOT NULL VARCHAR2(ten)  Bacon 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(ii,two)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

Identify the fault in the below query.

SELECT department_id , avg(salary ), count(hiredate) FROM employees  GROUP BY department_id ;

Multiple aggregate functions cannot exist used in a single SELECT query
GROUP BY clause is invalid
COUNT function cannot exist used with Engagement values
No errors and it executes successfully

Answer: D.

53. Which of the following group role tin be used with LOB data types?

MAX
MIN
COUNT
None of these

Answer: D. No aggregate part can be used with LOB data types.

54. Examine the table structure as given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Zippo NUMBER(6)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 NOT Aught VARCHAR2(25)  EMAIL			 NOT Nix VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 NOT Cipher Appointment  JOB_ID 		 Non Nix VARCHAR2(10)  SALARY 			  NUMBER(8,ii)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

Predict the output of the below two queries

Query - one

SELECT avg(comm) FROM employees ;

Query - 2

SELECT avg(nvl(comm,0)) FROM employees ;

Both the queries produce same result
Query - ane and Query - 2 produce different results considering Query-1 considers Zero values of COMM and Query-2 substitutes NULL values of COMM with zero
Query - 1 produces error considering COMM has NULL values
Query - 2 produces error considering NVL cannot be nested with amass function.

Answer: B. The AVG function ignores NULL values while computing the average of numeric information. AVG(cavalcade) volition summate average for only non zippo values. However, if NVL is used to substitute NULLs with a zero, all the values will be considered.

55. Choose the correct statements well-nigh the GROUP BY clause.

Cavalcade alias tin be used in the Grouping BY clause.
GROUP Past cavalcade must be in the SELECT clause.
Group Past clause must appear together with HAVING clause a SELECT query.
GROUP Past clause must announced subsequently WHERE clause in a SELECT query.

Answer: D. As per the processing sequence, the GROUP By clause must announced later the WHERE clause in a SELECT query.

56. Examine the table structure as given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Zippo NUMBER(six)  FIRST_NAME			  VARCHAR2(twenty)  LAST_NAME		 Non NULL VARCHAR2(25)  EMAIL			 Non NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 NOT NULL DATE  JOB_ID 		 NOT Cypher VARCHAR2(10)  SALARY 			  NUMBER(viii,two)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(four)

Predict the outcome of the beneath query

SELECT department_id ,avg(salary ) FROM employees  Group Past department_id , task Social club By department_id ;

It throws error because GROUP Past column list doesn't matches with SELECT column list.
Information technology executes successfully and produces average salary of a job category in each section.
Information technology executes successfully and produces average salary for a department in each job category.
Information technology throws error because Grouping BY and ORDER BY clause have different list of columns.

Answer: B. Though Group By clause implicitly sorts the groups, the Group BY and Lodge BY clauses tin can be used together in a query.

57. Which clause should you lot use to exclude grouping results in a query using group functions?

WHERE
HAVING
GROUP By
ORDER Past

Answer: B. HAVING clause is used to restrict the groups.

Examine the tabular array structure as given and answer the questions 58 and 59 that follow.

SQL> DESC employees  Name			 Zero?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Nada NUMBER(6)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 NOT NULL VARCHAR2(25)  EMAIL			 Not NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 NOT NULL DATE  JOB_ID 		 NOT NULL VARCHAR2(10)  Salary 			  NUMBER(8,two)  COMMISSION_PCT 		  NUMBER(two,two)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

58. Predict the upshot of the beneath query

SELECT department_id ,avg(salary ) FROM employees  HAVING avg(salary )>2000 Group BY department_id  Society BY department_id

It executes successfully.
Information technology throws error because HAVING clause precedes the Group Past clause.
It throws mistake because HAVING clause uses the aggregate function.
Information technology executes but no results are displayed because HAVING clause precedes the Grouping By clause.

Reply: A. HAVING clause can precede the GROUP BY clause but it is candy only later on the group results are calculated.

59. Predict the result of the below query

SELECT department_id , COUNT(first_name ) FROM employees  WHERE job IN ('SALESMAN','CLERK','MANAGER','Analyst') Grouping BY department_id  HAVING AVG(salary ) BETWEEN 2000 AND 3000;

Information technology returns an error considering the BETWEEN operator cannot be used in the HAVING clause.
Information technology returns an error because WHERE and HAVING clauses cannot exist used in the aforementioned SELECT statement.
It returns an error because WHERE and HAVING clauses cannot be used to apply atmospheric condition on the same cavalcade.
Information technology executes successfully.

Answer: D. The WHERE clause restricts the number of rows participating in group clause processing.

60. Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?

The HAVING clause can be used with group functions in subqueries.
The WHERE clause can exist used to exclude rows after dividing them into groups.
The WHERE clause can be used to exclude rows earlier dividing them into groups.
The WHERE and HAVING clauses tin can be used in the same statement only if they are practical to different columns in the tabular array.

Answer: A, C. WHERE and HAVING clause tin be used together in a query. WHERE excludes the rows before grouping processing while HAVING restricts the groups.

Examine the tabular array structure every bit given and respond the questions 61 and 62 that follow.

SQL> DESC employees  Name			 Zilch?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT NULL NUMBER(half-dozen)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 NOT NULL VARCHAR2(25)  EMAIL			 Not NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT Cypher DATE  JOB_ID 		 NOT Cypher VARCHAR2(10)  SALARY 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(two,ii)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

61. Predict the outcome of the below query.

SELECT department_id , avg(salary ) FROM employees  HAVING avg(salary ) > min(salary ) Grouping BY department_id ;

It throws an fault considering the amass functions used in HAVING clause must be in SELECT list.
It throws an error because the HAVING clause appears before Group Past clause.
It displays the departments whose boilerplate bacon is greater than the minimum salary of the department.
Information technology displays the departments whose average bacon is greater than the minimum salary of the organization.

Respond: C. Group functions tin can exist used by HAVING clause to filter the groups.

62. Interpret the output of the below query.

SELECT SUM(AVG(LENGTH(first_name ))) FROM employees   Grouping By department_id ;

It calculates the sum of averages of length of employee's name in each section.
It calculates the average length of employee'southward name in each department.
It throws error because unmarried row function cannot be used with group functions.
Information technology throws error because group column DEPARTMENT_ID is not used in the SELECT listing.

Respond: A. Group functions tin can be used with single row or general functions in the SELECT query.

63. Up to how many levels, the grouping functions can be nested?

1
two
three
No limits

Answer: B. Group functions can be nested maximum upwards to 2 levels. Nevertheless, unmarried row functions can exist nested up to any number of levels.

64. What is the limit of number of groups within the groups created by Group BY clause?

1
two
3
No Limit

Answer: D. There is no limit to the number of groups and subgroups that can be formed.

65. Choose the correct statements near the HAVING clause.

The HAVING clause is an optional clause in SELECT statement.
The HAVING clause is a mandatory clause if SELECT argument uses a GROUP BY clause.
The HAVING clause can appear in a SELECT statement only if information technology uses a Group BY clause.
The HAVING clause is a mandatory clause if SELECT statement uses a Grouping By clause.

Answer: A, C. HAVING clause can merely appear in a query if Grouping BY clause is present, only vice versa is non truthful.

66. What is the output of the beneath query.

SELECT count(*) FROM dual GROUP Past dummy;

i
0
NULL
Throws error because group functions cannot be applied on DUAL tabular array.

Answer: A. The DUAL table contains single column DUMMY of type CHAR(1) whose value is 'X'.

Based on the below scenario, answer the question from 67 to 74.

An organization has 14 employees who work on stock-still salary of thou. The company recruits 5 new employees whose bacon is non yet fixed by the payroll department. Even so, during the calendar month end processing, the Hr payroll section generates several reports to reconcile the financial information of the system. Examine the table structure equally given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Zero NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT NULL VARCHAR2(25)  EMAIL			 Not NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Not Zip DATE  JOB_ID 		 NOT Aught VARCHAR2(x)  Bacon 			  NUMBER(viii,2)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(iv)

67. What is the output of the below query?

SELECT SUM (salary ) FROM employees ;

Cipher
14000
19000
0

Answer: B. The SUM function adds the salaries of the employees.

68. What is the output of the below query?

SELECT AVG (salary ) FROM employees ;

m
736.84
NULL
0

Reply: A. The AVG (salary ) role calculates the average of salaries and ignoring the Goose egg values. In this instance, AVG(salary)=(fourteen*1000)/14=1000.

69. What is the output of the below query?

SELECT AVG (nvl(salary ,0)) FROM employees ;

1000
Nothing
736.84
0

Respond: C. The AVG(NVL(bacon ,0)) gives an alternate value to the NULLs and enables them to participate in average adding. In this case, (14*thousand)/19 = 736.84.

70. What is the output of the below query?

SELECT VARIANCE (salary ) FROM employees ;

1000
0
Goose egg
204678.36

Answer: B. The VARIANCE (salary ) calculates the variance of salary column values ignoring NULLs.

71. What is the output of the below query?

SELECT VARIANCE (nvl(bacon ,0)) FROM employees ;

yard
0
NULL
204678.36

Respond: D. The VARIANCE (NL(salary ,0)) calculates the variance of salary cavalcade values including NULLs.

72. What is the output of the beneath query?

SELECT STDDEV (salary ) FROM employees ;

i
yard
0
NULL

Answer: C. The STDDEV (bacon ) calculates the standard divergence of bacon column values ignoring NULLs.

73. What is the output of the below query?

SELECT STDDEV (nvl(salary ,0)) FROM employees ;

0
452.41
g
Null

Answer: B. The STDDEV (nvl(salary ,0)) calculates the standard difference of salary cavalcade values including NULLs.

74. What is the output of the below query?

select count(*),count(salary ) from employees ;

19,xix
14,19
19,14
xiv,14

Answer: C. COUNT(*) includes NULLs while COUNT(salary ) ignores Nothing values.

75. Examine the table structure equally given.

SQL> DESC employees  Proper noun			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Null NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Not NULL VARCHAR2(25)  EMAIL			 NOT Nada VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Not Nix Appointment  JOB_ID 		 Non NULL VARCHAR2(10)  Bacon 			  NUMBER(viii,two)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

Which of the below query will give the department who have more than 5 employees working in it?

SELECT department_id  FROM employees  WHERE COUNT(*) > 5 GROUP BY department_id ;

SELECT department_id  FROM employees  HAVING COUNT(*) > 5;

SELECT department_id  FROM employees  GROUP BY employee_id  HAVING COUNT(*) > 5;

SELECT department_id  FROM employees  GROUP Past department_id  HAVING COUNT(*) > 5;

Answer: D.

76. Which of the following are true most the CUBE extension of GROUP BY?

Enables performing multiple Group BY clauses with a single query.
Performs aggregations for all possible combinations of columns included.
Performs increasing levels of cumulative subtotals, based on the provided column list.
None of the above

Answer: B. CUBE, ROLLUP are the Grouping BY extensions used for OLAP processing. CUBE aggregates the results whenever a new permutation of column is formed.

Use the following SELECT statement to respond below questions 77 to 82:

1 SELECT client#, COUNT(*) 2 FROM customers Bring together orders USING (customer#) 3 WHERE orderdate > '02-April-09' four Grouping BY customer# v HAVING COUNT(*) > ii;

77. Which line of the SELECT statement is used to restrict the number of records the query processes?

1
three
4
5

Reply: B. WHERE clause is used to restrict the rows before the groups are formed.

78. Which line of the SELECT argument is used to restrict groups displayed in the query results?

ane
three
four
5

Respond: D. HAVING is used to restrict the group results subsequently the group processing is over.

79. Which line of the SELECT statement is used to group information stored in the database?

one
three
4
5

Reply: C. Grouping BY clause uses the group by columns to group the data in the table.

eighty. Which clause must be included for the query to execute successfully?

Answer: C. Because the SELECT clause contains the CUSTOMER# cavalcade, it is mandatory to have GROUP Past clause with the Customer# cavalcade.

81. What is the purpose of using COUNT(*) in the SELECT query?

The number of records in the specified tables
The number of orders placed past each client
The number of NULL values in the specified tables
The number of customers who take placed an order

Answer: B. It counts the number of rows processing under a grouping. In this instance, group is formed by the client and COUNT(*) counts the orders placed by each client.

82. Which of the post-obit functions can be used to determine the earliest send engagement for all orders recently candy by JustLee Books?

COUNT function
MAX function
MIN function
STDDEV function

Reply: C. MIN function is used to retrieve the least value of the cavalcade. When used with date columns, it fetches the minimum date from the column.

83. Which of the following is not a valid SELECT statement?

```
SELECT STDDEV(retail) FROM books;
```

SELECT AVG(SUM(retail)) FROM orders NATURAL Join orderitems NATURAL JOIN books Group BY customer#;

SELECT lodge#, TO_CHAR(SUM(retail),'999.99') FROM orderitems JOIN books USING (isbn) GROUP BY order#;

SELECT title, VARIANCE(retail-cost) FROM books GROUP Past pubid;

Answer: D. The GROUP Past clause must specify a column or set of columns contained in the SELECT clause. Here PUBID is not contained in the SELECT clause, hence the query is non valid.

84. Which of the below statements are true about the nesting of group functions?

The inner most office is resolved first.
Oracle allows nesting of grouping function up to 3 levels.
Single row functions can exist nested with group functions.
Oracle allows nesting of group function up to 2 levels.

Respond: A, C, D. In an expression containing nested functions, the innermost function is executed showtime whose result is fed into the side by side function moving in outwards direction. Single row functions tin can be well used with group functions which can be maximum nested up to ii levels.

85. What are the statistical group functions in Oracle?

AVG
STDDEV
VARIANCE
STATS

Reply: B, C. VARIANCE and STATS are the statistical group functions bachelor in Oracle SQL.

86. If the SELECT list contains a cavalcade and a group functions, which of the post-obit clause must be mandatorily included?

ORDER By
HAVING
Group By
None of these

Answer: C. GROUP By clause should necessarily contain the cavalcade or set of columns independent in the SELECT clause.

87. Examine the table structure as given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT NULL NUMBER(half-dozen)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 Not Naught VARCHAR2(25)  EMAIL			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT Aught Engagement  JOB_ID 		 NOT Aught VARCHAR2(10)  SALARY 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,two)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

What is the best explanation as to why this SQL statement volition Non execute?

SELECT department_id "Department", AVG (salary)"Boilerplate" FROM employees Grouping By Department;

Salaries cannot be averaged every bit not all the numbers will divide evenly.
You cannot use a column alias in the Group By clause.
The GROUP Past clause must accept something to GROUP.
The department id is not listed in the departments tabular array.

Answer: B. Neither Grouping BY clause nor HAVING clause works with column alias.

88. Which of the post-obit information types are compatible with AVG, SUM, VARIANCE, and STDDEV functions?

But numeric data types
Integers only
Any data type
All except numeric

Respond: A. The functions AVG, SUM, VARIANCE and STDDEV mandatorily work with numeric data type just.

Examine the table structure as given beneath and answer the questions 89 and 90 that follow.

SQL> DESC employees  Name			 Goose egg?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not Cypher NUMBER(vi)  FIRST_NAME			  VARCHAR2(twenty)  LAST_NAME		 NOT NULL VARCHAR2(25)  Electronic mail			 Not Cipher VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT NULL DATE  JOB_ID 		 Non NULL VARCHAR2(10)  SALARY 			  NUMBER(8,two)  COMMISSION_PCT 		  NUMBER(two,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

89. Which of the below query will display the number of singled-out job categories working in each department?

SELECT department_id , COUNT(DISTINCT job) FROM employees  GROUP BY job;

SELECT department_id , COUNT(chore) FROM employees  Group BY employee_id ;

SELECT department_id , COUNT(job) FROM employees  GROUP By department_id ;

SELECT department_id , COUNT(Distinct job) FROM employees  GROUP Past department_id ;

Respond: D. Apply DISTINCT modifier to filter out the duplicates.

90. Evaluate this SQL statement:

SELECT employee_id , first_name , department_id , SUM(bacon ) FROM employees  WHERE salary  > 1000 GROUP Past department_id , employee_id , first_name  Gild By hiredate;

Why will this statement cause an error?

The HAVING clause is missing.
The WHERE clause contains a syntax error.
The SALARY column is Not included in the GROUP BY clause.
The HIRE_DATE column is Not included in the GROUP Past clause.

Reply: D. All the columns actualization in SELECT and Social club BY clause must be included in the Group BY clause.

91. Which of the post-obit statements is true about the GROUP By clause?

To exclude rows before dividing them into groups using the GROUP By clause, you use should a WHERE clause.
You lot must utilise the HAVING clause with the GROUP By clause.
Cavalcade alias can exist used in a GROUP BY clause.
By default, rows are non sorted when a Group BY clause is used.

Answer: A. Using a WHERE clause, you tin exclude rows earlier dividing them into groups.

92. Examine the table structure as given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Zilch NUMBER(six)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Not Nada VARCHAR2(25)  E-mail			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Non Zero Appointment  JOB_ID 		 NOT Zilch VARCHAR2(10)  SALARY 			  NUMBER(eight,2)  COMMISSION_PCT 		  NUMBER(ii,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(iv)

Interpret the outcome of the below query.

SELECT department_id , MIN (hiredate) FROM employees  GROUP by department_id ;

The primeval hire appointment in the organization.
The latest rent appointment in the organization.
The earliest hire date in a department.
The latest hire date in a department.

Answer: C. The query returns the earliest hired employee in each section.

93. Which statement nigh group functions is true?

Group functions except COUNT(*), ignore aught values.
A query that includes a grouping function in the SELECT list must include a GROUP By clause.
Group functions can exist used in a WHERE clause.
Group functions tin only be used in a SELECT list.

Answer: A. All the grouping functions except COUNT(*), ignore Naught values. It is because they process the values direct contained in a specific cavalcade.

94. Which of the post-obit clauses represent valid uses of grouping functions?

Group BY MAX(bacon)
ORDER BY AVG(salary)
HAVING MAX(salary) > 10000
SELECT AVG(NVL(salary, 0))

Reply: B, C, D. Group functions can announced in SELECT, HAVING and Gild Past clauses just.

95. Which of the following statements are true nigh the GROUP Past clause?

The last column listed in the GROUP Past clause is the well-nigh major grouping.
The commencement column listed in the Group By clause is the near major grouping.
A GROUP Past clause cannot be used without an Lodge BY clause.
The GROUP BY clause do not ensure the sorting of output.

Answer: B. The grouping of data is based on the sequence of columns appearing in the Group Past clause.

96. What is divergence between WHERE clause and HAVING clause?

WHERE clause restrict rows before group while HAVING clause restricts groups.
WHERE clause cannot contain a group function merely HAVING clause can take.
WHERE clause can join multiple conditions using AND or OR operators but HAVING clause cannot.
WHERE clause can appear in SELECT query without Group Past clause just HAVING clause cannot.

Answer: A, B, D. WHERE clause restricts the rows earlier grouping but HAVING restricts the groups.

97. Examine the table structure as given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Zippo NUMBER(6)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 NOT NULL VARCHAR2(25)  Email			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 NOT Goose egg Engagement  JOB_ID 		 NOT Aught VARCHAR2(10)  SALARY 			  NUMBER(viii,2)  COMMISSION_PCT 		  NUMBER(two,2)  MANAGER_ID			  NUMBER(half dozen)  DEPARTMENT_ID			  NUMBER(4)

Predict the consequence of the below query.

SELECT department_id ,job,count(*) FROM employees  GROUP BY department_id ,chore ORDER BY department_id ,count(*);

It executes successfully.
It throws error because ORDER By clause is invalid.
It throws error because Grouping Past clause is invalid.
Information technology throws error because GROUP BY and Social club BY clause cannot be used together.

Reply: A. ORDER Past clause tin employ the group functions for sorting.