Which Of The Following Are True Regarding Working With Data Sets?

Using the Grouping Functions Questions

1. Which of the following is Not a GROUP BY function?

Answer: C. NVL is a full general function used to provide alternate value to the NULL values. The functions MAX, MIN and AVG can be used every bit Group BY functions.

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

COUNT
MAX
MIN
AVG

Answer: A, B, C, D. All the listed group functions tin 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 construction equally given)

SQL> DESC employees  Name			 Null?	  Blazon  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 Non NULL VARCHAR2(25)  EMAIL			 Non NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 Non Zip Appointment  JOB_ID 		 NOT Cipher VARCHAR2(ten)  SALARY 			  NUMBER(eight,ii)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(iv)

SELECT department_id , max(salary ) FROM employees ;

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

SELECT max(salary ) FROM employees  Group By department_id ;

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

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

4. Which of the post-obit statements are true about the COUNT office?

The COUNT part counts the number of rows
The COUNT(*) role counts the number of rows with duplicates and Zero values
The COUNT(Singled-out) function 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. Use DISTINCT and ALL keyword to restrict duplicate and NULL values.

5. What are the appropriate information types accepted by Group BY functions?

Nested Tables
NUMBER
CLOB
DATE

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

6. A tabular array T_COUNT has 12 number values as 1, two, three, 32, 1, 1, cypher, 24, 12, cipher, 32, nothing. Predict the output of the below query.

SELECT COUNT (*) FROM t_count;

12
vi
9
Throws exception because COUNT part doesn't works with NULL values

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

seven. A table T_COUNT has 12 number values equally 1, 2, 3, 32, 1, 1, zero, 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 Zilch values

Answer: C. COUNT (cavalcade) ignores the Cipher values but counts the duplicates.

8. A table T_COUNT has 12 number values as 1, 2, 3, 32, one, 1, null, 24, 12, zilch, 32, null. Predict the output of the below query.

SELECT COUNT (ALL num) FROM t_count;

12
half-dozen
9
Throws exception considering COUNT function doesn't works with Aught values

Answer: C. COUNT(ALL cavalcade) ignores the Aught values but counts the duplicates.

9. A table T_COUNT has 12 number values as 1, 2, three, 32, i, 1, null, 24, 12, null, 32, null. Predict the output of the beneath query.

SELECT COUNT (Distinct num) FROM t_count;

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

Answer: B. COUNT (DISTINCT cavalcade) counts the distinct not zippo values.

10. 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 '1'
Throws exception "ORA-00909: invalid number of arguments"
Throws exception "ORA-00904: "COUNT": invalid identifier" because COUNT office doesn't works with DUAL tabular array

Answer: C. COUNT role requires minimum one statement which can be either the column with [ALL | Distinct] modifier or '*'.

11. Here are few statements almost VARIANCE function in SQL.

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

2. The role 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 beneath options.

i and iii
i and ii
ii
iii

Answer: C. The VARIANCE function accepts single numeric statement as the column 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 BY
Grouping SETS
CUBE
ROLLUP

Reply: A. Grouping SETS operations can be used to perform multiple Group Past aggregations with a single query.

13. Select the correct statements most the below query. Consider the table structure as given.

SQL> DESC employees  Proper noun			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(twenty)  LAST_NAME		 Not Zilch VARCHAR2(25)  EMAIL			 Not Zippo VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(twenty)  HIRE_DATE		 NOT NULL Engagement  JOB_ID 		 NOT Nix VARCHAR2(ten)  SALARY 			  NUMBER(8,two)  COMMISSION_PCT 		  NUMBER(2,two)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

SELECT department_id , SUM(salary ) FROM employees  Grouping By department_id ;

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

Respond: A. SUM is a group office 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 by function?

WHERE
LIMIT
GROUP WHERE
HAVING

Answer: D. HAVING Clause is used for restricting group results. You utilize the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate data. The HAVING clause can precede the GROUP BY clause, just information technology is recommended that you place the Grouping Past clause first considering it is more 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 structure and predict the outcome of the following query.

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

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

The query returns the number of employees who have no committee
The query throws mistake because equal sign cannot exist used when searching for NULL value
The query returns the number of employees in a department whose commission is Nada value
The query throws error 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. Only here the usage of IS Null operator is incorrect. The status should be 'WHERE comm IS Null'.

sixteen. Which of the post-obit statements is truthful most the grouping functions?

The MIN function can be used only with numeric data.
The MAX part can be used simply with engagement values.
The AVG role can be used only with numeric data.
The SUM role canít exist part of a nested office.

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

17. Which of the post-obit is a valid SELECT argument?

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

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

SELECT category, AVG(retail-toll) FROM books WHERE AVG(retail-cost) > eight.56 GROUP Past category;

SELECT category, AVG(retail-toll) Profit FROM books GROUP By category HAVING profit > viii.56;

Reply: A. Cavalcade aliases cannot be used in Group By or HAVING clause.

18. Which of the post-obit statements is correct?

The WHERE clause can contain a group function just if the part isnít as well listed in the SELECT clause.
Group functions canít be used in the SELECT, FROM, or WHERE clauses.
The HAVING clause is e'er candy earlier the WHERE clause.
The Group By clause is always processed before the HAVING clause.

Answer: D. Though Oracle doesn't raise fault if HAVING clause precedes the GROUP By clause just it is candy but after the Grouping BY clause is processed and group are gear up to exist filtered.

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

SELECT MIN(pubdate) FROM books GROUP By category HAVING pubid = 4;

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

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

SELECT MAX(COUNT(client#)) FROM orders GROUP Past customer#;

Answer: A.

20. Which of the following statements is correct?

The COUNT function tin be used to make up one's mind how many rows contain a NULL 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 processed.
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 statement?

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

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

SELECT customer#, COUNT(order#) FROM orders GROUP By COUNT(social club#);

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

Answer: B. The GROUP BY clause must incorporate all the columns except the one which is used within the grouping function.

22. Which of the following SELECT statements lists only the volume with the largest turn a profit?

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

SELECT title, MAX(retail-cost) FROM books Grouping Past title HAVING MAX(retail-price);

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

None of the in a higher place

Answer: A.

23. Which of the following argument(s) is/are correct?

1. A group office can exist nested inside a group function.

two. A group office can exist nested inside a single-row function.

3. A single-row function can exist nested inside a group office.

i
2
three
i and 3

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

24. Which of the post-obit functions is used to calculate 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 post-obit SELECT statements lists the highest retail price of all books in the Family category?

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

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

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

None of the higher up

Reply: A. Since the category FAMILY has to be restricted before grouping, tabular array rows must be filtered using WHERE clause and not HAVING clause.

26. Which of the following functions tin can exist used to include Zip values in calculations?

Answer: B.NVL is a general part to provide alternate values to the NULL values. It tin really make a difference in arithmetic calculations using AVG, STDDEV and VARIANCE group functions.

27. Which of the post-obit is not a valid statement?

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

Respond: A. The ALL keyword counts duplicates only ignores NULLs. Duplicates are likewise included with '*' and column proper noun specification.

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

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

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

SELECT COUNT(*) FROM customers WHERE referred IS Goose egg;

Reply: B. Because all customers equally one grouping, COUNT(referred) volition count simply those who are referred by someone. COUNT(referred) will ignore Goose egg values of the column.

29. Decide the right order of execution of following clauses in a SELECT argument.

1.SELECT

2.FROM

iii.WHERE

4.GROUP BY

5.HAVING

half dozen.ORDER Past

two-3-4-v-1-half-dozen
ane-2-3-4-5-6
half dozen-v-4-3-2-1
5-4-2-3-1-6

Answer: A. Processing order starts from FROM clause to go the table names, then restricting rows using WHERE clause, grouping them using Group By clause, restricting groups using HAVING clause. ORDER BY clause is the last one to be candy to sort the final data fix.

30. Which of the below clauses is used to group a fix of rows based on a column or set of columns?

HAVING
WHERE
GROUP BY
GROUPING

Respond: C. GROUP BY clause forms the groups of the data based on the cavalcade list specified.

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

VAR_POP
STDDEV_POP
VARIANCE
STDDEV_SASMP

Answer: A, B.

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

SELECT statement
WHERE clause
ORDER BY clause
Group BY clause

Respond: A, C, D. Group functions tin can announced in SELECT, ORDER BY and HAVING clause. Oracle raises exception if group functions are used in WHERE or Grouping By clauses.

33. Examine the structure of the EMPLOYEES tabular array every bit given. Which query volition return the minimum salary in each department?

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 Non Nil VARCHAR2(25)  EMAIL			 NOT Zip VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(twenty)  HIRE_DATE		 NOT NULL DATE  JOB_ID 		 Not NULL VARCHAR2(10)  Bacon 			  NUMBER(8,ii)  COMMISSION_PCT 		  NUMBER(ii,2)  MANAGER_ID			  NUMBER(six)  DEPARTMENT_ID			  NUMBER(iv)

SELECT department_id , MIN (salary ) from EMPLOYEES ;

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

SELECT department_id , MIN (salary ) from EMPLOYEES  GROUP BY salary ;

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

Answer: B. MIN function returns the minimum salary in a group formed past department.

34. Examine the construction for the table EMPLOYEES and Interpret the output of the below query

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Aught NUMBER(6)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 Not NULL VARCHAR2(25)  EMAIL			 Non Goose egg VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 Non Cypher Engagement  JOB_ID 		 Not NULL VARCHAR2(ten)  Bacon 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,two)  MANAGER_ID			  NUMBER(half dozen)  DEPARTMENT_ID			  NUMBER(4)

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

It throws error because only one amass function can exist used in a query.
It throws fault because Group BY clause is missing.
It executes successfully and returns same values for both.
Information technology executes successfully where COUNT(*) including NULLs and COUNT(all comm) excluding NULLs.

Answer: D.

35. Which of the following are true well-nigh group functions?

You tin use grouping functions in any clause of a SELECT statement.
You lot tin can apply group functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
You lot tin mix unmarried row columns with group functions in the cavalcade list of a SELECT statement by group on the single row columns.
You lot tin can laissez passer cavalcade names, expressions, constants, or functions every bit parameter to an group function.

Respond: C. Group functions can be nested only to a depth of two. Group functions can be nested inside single-row functions (AVG embedded in a TO_CHAR function). In addition, single-row functions can be nested inside group functions.

36. Examine the construction of the table EMPLOYEES as given. Yous want to create a "emp_dept_sales" view by executing the post-obit SQL statements.

SQL> DESC employees  Name			 Goose egg?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not NULL NUMBER(half-dozen)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT Zip VARCHAR2(25)  EMAIL			 Non NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT NULL Appointment  JOB_ID 		 NOT NULL VARCHAR2(10)  SALARY 			  NUMBER(viii,two)  COMMISSION_PCT 		  NUMBER(two,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

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

Which argument is true regarding the execution of the in a higher place statement?

The view will be created and yous can perform DLM operations on the view
The view will not exist created because the bring together statements are non allowed for creating a view
The view will non be created because the GROUP By clause is not allowed for creating a view
The view volition be created just no DML operations will be allowed on the view

Answer: D. Rules for Performing DML Operations on a View. You cannot add together information through a view if the view includes group functions or a GROUP BY clause or DISTINCT keyword. The pseudo column ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected past the view.

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

A sub query that defines a view cannot include the GROUP BY clause
A view is created with the sub query having the Singled-out keyword can be updated
A Data Manipulation Language (DML) performance can exist performed on a view that is created with the sub query having all the Not NULL columns of a table
A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

Answer: C, D. Rules for Performing DML Operations on a View. Yous cannot add information through a view if the view includes group functions or a Group BY clause or DISTINCT keyword. The pseudo cavalcade ROWNUM keyword Columns defined past expressions Not NULL columns in the base tables that are not selected by the view.

38. Examine the table construction as given.

SQL> DESC departments  Proper name			 Null?	  Type  ----------------------- -------- ----------------  DEPARTMENT_ID		 NOT Goose egg NUMBER(4)  DEPARTMENT_NAME	 Not Nothing VARCHAR2(thirty)  MANAGER_ID			  NUMBER(6)  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') Group BY task Guild BY department_id ;

WHERE
SELECT
Club BY
GROUP BY

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

39. Examine the table structure as given.

SQL> DESC employees  Proper name			 Cipher?	  Blazon  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not Zilch NUMBER(vi)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT NULL VARCHAR2(25)  Electronic mail			 Non Zip VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 NOT NULL Engagement  JOB_ID 		 Not NULL VARCHAR2(10)  Bacon 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(ii,2)  MANAGER_ID			  NUMBER(half-dozen)  DEPARTMENT_ID			  NUMBER(4)

Which of the beneath SELECT query will display the maximum and minimum bacon earned past each job category?

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

SELECT job, MAX(salary ), MIN (salary ) FROM employees  Grouping Past chore;

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

Two aggregate functions cannot be used together in SELECT argument.

Respond: B. More than than 1 grouping part tin can appear in the SELECT argument.

40. Consider the table structure as given.

SQL> DESC employees  Name			 Cipher?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non NULL NUMBER(half-dozen)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Not Naught VARCHAR2(25)  E-mail			 Non NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT NULL Engagement  JOB_ID 		 Not NULL VARCHAR2(10)  SALARY 			  NUMBER(viii,2)  COMMISSION_PCT 		  NUMBER(ii,2)  MANAGER_ID			  NUMBER(half-dozen)  DEPARTMENT_ID			  NUMBER(4)

Examine the fault in the below query.

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

It executes successfully and generates the required issue.
It produces an error considering COUNT(*) should be specified in the SELECT clause as well.
It executes successfully but produces no result because COUNT(prod_id) should exist used instead of COUNT(*).
Information technology produces an error because COUNT(*) should exist just in the HAVING clause and not in the WHERE clause.

Answer: D. Grouping functions cannot be used in WHERE clause. The tin can appear in SELECT, HAVING and Society Past clause.

41. Examine the table structure as given.

SQL> DESC employees  Proper name			 Aught?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(twenty)  LAST_NAME		 NOT NULL VARCHAR2(25)  Email			 Not NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT Nada Date  JOB_ID 		 NOT Cypher VARCHAR2(10)  Bacon 			  NUMBER(eight,2)  COMMISSION_PCT 		  NUMBER(2,ii)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

Predict the outcome of the below query

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

It executes successfully and lists the count of employees nether each job category but ignores the HAVING clause since "salary " is not in GROUP By clause.
It throws error because HAVING clause is invalid.
It throws fault because "bacon " is not 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 group results. COUNT part 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 Null values?

Grouping functions on columns ignore NULL values.
Group functions on columns returning dates include Zip values.
Group functions on columns returning numbers include Zilch values.
Group functions on columns cannot be accurately used on columns that contain Nothing values.

Reply: A. Except COUNT function, all the group functions ignore NULL values.

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

Subqueries can contain Grouping BY and ORDER By clauses.
Subqueries cannot contain Group By and ORDER BY clauses.
Subqueries can contain ORDER By but not the GROUP BY clause.
Subqueries cannot contain Guild By but can have Grouping BY clause.

Answer: A. Like the primary query, a subquery tin can contain a Group BY also as Gild Past clause.

Examine the table structure as given and answer the questions 44 to 49 that follow.

SQL> DESC employees  Name			 Zero?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Naught NUMBER(vi)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT Cypher VARCHAR2(25)  Electronic mail			 Non Zippo VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Not Zip DATE  JOB_ID 		 NOT Null VARCHAR2(10)  Salary 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,ii)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(iv)

44. Predict the issue of the beneath query

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

It executes successfully.
It gives an error because the HAVING clause is not valid.
It gives an error because the Grouping By expression is not valid.
It gives an error considering aggregate functions cannot be nested in SELECT statement.

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

45. Predict the output of the below query

SELECT avg(bacon ), department_id  FROM employees  Grouping BY department_id ;

It gives mistake because an amass office cannot appear simply after SELECT clause.
It gives mistake because GROUP BY clause is invalid.
It executes without errors but produces no output.
It executes successfully and gives average bacon in each department.

Respond: D. Grouping functions tin can be used in whatever sequence (before or subsequently the grouping by columns) in a SELECT query.

46. Predict the output of the below query

SELECT lower(job),avg(bacon ) FROM employees  GROUP BY upper(job);

Information technology executes successfully and displays "job" in lower instance.
It executes successfully but display "task" in original case.
It 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 Grouping By clause.

Answer: D. The function LOWER, being a single row role must be specified in the GROUP By clause to base 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(salary ), MAX(bacon ) FROM employees  Grouping BY department_id ;

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

Answer: B, C. The first query operates of the whole EMPLOYEES data while the second one processes the data in groups of department.

48. Identify the fault in the below SELECT statement.

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

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

Reply: A. Group BY expressions tin can exist used in HAVING clause to filter out the groups from the final data set.

49. Predict the output of the beneath query

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

Information technology throws error because multiple atmospheric condition cannot be given in HAVING clause.
It throws error considering a not aggregate column cannot be used in HAVING clause.
Information technology executes successfully and displays average salary of department higher than 10 and greater than 2000.
It executes successfully simply no result is displayed.

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

50. Which of the following group functions can be used with Engagement values?

AVG
MIN
SUM
COUNT

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

51. Which of the following statements are true?

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

Reply: A, B, D. The grouping functions AVG,SUM, VARIANCE and STDDEV can be used with numeric data merely. None of the group functions tin be used with LONG data blazon.

52. Examine the table structure as given.

SQL> DESC employees  Name			 Nix?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Cypher NUMBER(6)  FIRST_NAME			  VARCHAR2(twenty)  LAST_NAME		 NOT Nix VARCHAR2(25)  E-mail			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT Zippo DATE  JOB_ID 		 NOT NULL VARCHAR2(10)  Bacon 			  NUMBER(viii,2)  COMMISSION_PCT 		  NUMBER(two,2)  MANAGER_ID			  NUMBER(half dozen)  DEPARTMENT_ID			  NUMBER(4)

Identify the error in the below query.

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

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

Reply: D.

53. Which of the following group function can be used with LOB data types?

MAX
MIN
COUNT
None of these

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

54. Examine the table construction as given.

SQL> DESC employees  Proper name			 Naught?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 NOT NULL VARCHAR2(25)  EMAIL			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 NOT Nada DATE  JOB_ID 		 NOT NULL VARCHAR2(10)  SALARY 			  NUMBER(8,two)  COMMISSION_PCT 		  NUMBER(ii,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(four)

Predict the output of the below two queries

Query - i

SELECT avg(comm) FROM employees ;

Query - 2

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

Both the queries produce aforementioned result
Query - one and Query - 2 produce different results considering Query-1 considers Nothing values of COMM and Query-ii substitutes Nix values of COMM with aught
Query - 1 produces error because COMM has NULL values
Query - 2 produces error because NVL cannot be nested with aggregate role.

Respond: B. The AVG function ignores Zip values while calculating the average of numeric data. AVG(column) will calculate average for only non zilch values. However, if NVL is used to substitute NULLs with a zilch, all the values volition be considered.

55. Cull the right statements about the GROUP Past clause.

Column alias can exist used in the GROUP BY clause.
GROUP Past column must be in the SELECT clause.
Grouping Past clause must appear together with HAVING clause a SELECT query.
GROUP Past clause must announced after WHERE clause in a SELECT query.

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

56. Examine the tabular array construction every bit given.

SQL> DESC employees  Proper name			 Nix?	  Blazon  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Naught NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT NULL VARCHAR2(25)  EMAIL			 Not Nil VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Not Aught Appointment  JOB_ID 		 NOT Zero VARCHAR2(10)  SALARY 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

Predict the issue of the below query

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

Information technology throws error considering GROUP Past cavalcade list doesn't matches with SELECT column list.
It executes successfully and produces average bacon of a job category in each section.
Information technology executes successfully and produces boilerplate salary for a department in each task category.
Information technology throws fault because GROUP BY and Guild By clause have unlike list of columns.

Answer: B. Though Group BY clause implicitly sorts the groups, the GROUP BY and ORDER By clauses 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
Grouping BY
ORDER BY

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

Examine the table construction every bit given and reply the questions 58 and 59 that follow.

SQL> DESC employees  Proper name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Nix NUMBER(half-dozen)  FIRST_NAME			  VARCHAR2(twenty)  LAST_NAME		 NOT NULL VARCHAR2(25)  EMAIL			 Not NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Non Null DATE  JOB_ID 		 NOT NULL VARCHAR2(10)  SALARY 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,two)  MANAGER_ID			  NUMBER(vi)  DEPARTMENT_ID			  NUMBER(4)

58. Predict the outcome of the below query

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

It executes successfully.
Information technology throws error because HAVING clause precedes the GROUP BY clause.
It throws error considering HAVING clause uses the amass part.
It executes just no results are displayed because HAVING clause precedes the Group By clause.

Answer: A. HAVING clause tin can precede the GROUP By clause but it is processed merely after the group results are calculated.

59. Predict the event of the beneath query

SELECT department_id , COUNT(first_name ) FROM employees  WHERE job IN ('SALESMAN','CLERK','Manager','Annotator') GROUP By department_id  HAVING AVG(salary ) Between 2000 AND 3000;

It returns an error considering the BETWEEN operator cannot exist used in the HAVING clause.
It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT argument.
It returns an fault because WHERE and HAVING clauses cannot exist used to apply weather on the same column.
It executes successfully.

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

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

The HAVING clause tin be used with group functions in subqueries.
The WHERE clause can be used to exclude rows afterward dividing them into groups.
The WHERE clause tin can be used to exclude rows before dividing them into groups.
The WHERE and HAVING clauses can be used in the same statement but if they are practical to different columns in the table.

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

Examine the tabular array construction as given and reply the questions 61 and 62 that follow.

SQL> DESC employees  Name			 Cypher?	  Blazon  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT NULL NUMBER(six)  FIRST_NAME			  VARCHAR2(xx)  LAST_NAME		 NOT Nada VARCHAR2(25)  EMAIL			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(twenty)  HIRE_DATE		 NOT NULL DATE  JOB_ID 		 Not NULL VARCHAR2(10)  SALARY 			  NUMBER(eight,two)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

61. Predict the event of the below query.

SELECT department_id , avg(salary ) FROM employees  HAVING avg(salary ) > min(salary ) GROUP Past department_id ;

Information technology throws an error because the amass functions used in HAVING clause must be in SELECT listing.
Information technology throws an error because the HAVING clause appears before GROUP BY clause.
Information technology displays the departments whose average salary is greater than the minimum salary of the department.
It displays the departments whose boilerplate salary is greater than the minimum salary of the organization.

Respond: C. Group functions 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   GROUP BY department_id ;

Information technology calculates the sum of averages of length of employee's proper name in each department.
It calculates the average length of employee's proper name in each department.
It throws mistake because unmarried row role cannot be used with grouping functions.
It throws error because grouping column DEPARTMENT_ID is not used in the SELECT list.

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

63. Upwards to how many levels, the group functions can exist nested?

ane
2
3
No limits

Reply: B. Group functions tin exist nested maximum upwardly to 2 levels. Nevertheless, single row functions tin be nested up to whatever number of levels.

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

1
two
iii
No Limit

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

65. Choose the correct statements almost the HAVING clause.

The HAVING clause is an optional clause in SELECT statement.
The HAVING clause is a mandatory clause if SELECT statement uses a GROUP Past clause.
The HAVING clause can appear in a SELECT statement only if it uses a GROUP Past clause.
The HAVING clause is a mandatory clause if SELECT statement uses a GROUP By clause.

Answer: A, C. HAVING clause tin can only appear in a query if Group BY clause is nowadays, but vice versa is not true.

66. What is the output of the below query.

SELECT count(*) FROM dual GROUP Past dummy;

1
0
Goose egg
Throws error considering grouping functions cannot be applied on DUAL tabular array.

Reply: A. The DUAL table contains single cavalcade DUMMY of type CHAR(i) whose value is 'X'.

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

An arrangement has xiv employees who work on fixed salary of g. The company recruits v new employees whose salary is not withal stock-still past the payroll section. Notwithstanding, during the calendar month end processing, the Hr payroll department generates several reports to reconcile the financial data of the system. Examine the table structure as given.

SQL> DESC employees  Name			 Nil?	  Blazon  ----------------------- -------- ----------------  EMPLOYEE_ID		 Not Zippo NUMBER(vi)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT Cypher VARCHAR2(25)  Email			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 Not Cipher DATE  JOB_ID 		 NOT Naught VARCHAR2(10)  SALARY 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(ii,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

67. What is the output of the beneath query?

SELECT SUM (bacon ) FROM employees ;

Nix
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 ;

1000
736.84
Zippo
0

Answer: A. The AVG (salary ) office calculates the average of salaries and ignoring the Nada values. In this case, AVG(salary)=(14*thousand)/fourteen=1000.

69. What is the output of the below query?

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

grand
NULL
736.84
0

Answer: C. The AVG(NVL(salary ,0)) gives an alternate value to the NULLs and enables them to participate in average calculation. In this case, (fourteen*grand)/nineteen = 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 ;

1000
0
Zip
204678.36

Answer: D. The VARIANCE (NL(bacon ,0)) calculates the variance of salary column values including NULLs.

72. What is the output of the below query?

SELECT STDDEV (salary ) FROM employees ;

i
1000
0
NULL

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

73. What is the output of the beneath query?

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

0
452.41
1000
NULL

Answer: B. The STDDEV (nvl(salary ,0)) calculates the standard deviation of bacon column values including NULLs.

74. What is the output of the below query?

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

19,19
fourteen,19
19,fourteen
14,14

Respond: C. COUNT(*) includes NULLs while COUNT(salary ) ignores Zippo values.

75. Examine the table structure as given.

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

Which of the beneath query will give the department who accept more 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(*) > five;

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

Answer: D.

76. Which of the following are true about the CUBE extension of Grouping By?

Enables performing multiple GROUP Past 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

Reply: B. CUBE, ROLLUP are the Grouping Past extensions used for OLAP processing. CUBE aggregates the results whenever a new permutation of cavalcade is formed.

Use the post-obit SELECT statement to answer below questions 77 to 82:

ane SELECT customer#, COUNT(*) 2 FROM customers JOIN orders USING (customer#) three WHERE orderdate > '02-APR-09' iv GROUP BY customer# 5 HAVING COUNT(*) > 2;

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

1
3
4
five

Respond: B. WHERE clause is used to restrict the rows earlier the groups are formed.

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

Answer: D. HAVING is used to restrict the group results afterward the grouping processing is over.

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

Answer: C. Grouping BY clause uses the group past columns to group the data in the table.

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

Respond: C. Because the SELECT clause contains the CUSTOMER# column, it is mandatory to have Group BY clause with the CUSTOMER# column.

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 by each customer
The number of NULL values in the specified tables
The number of customers who have placed an order

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

82. Which of the following functions tin be used to make up one's mind the earliest ship engagement for all orders recently processed past JustLee Books?

COUNT function
MAX function
MIN function
STDDEV office

Answer: C. MIN role is used to retrieve the least value of the column. When used with appointment columns, it fetches the minimum appointment from the cavalcade.

83. Which of the post-obit is not a valid SELECT statement?

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

SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL Bring together books GROUP By customer#;

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

SELECT title, VARIANCE(retail-toll) FROM books GROUP BY pubid;

Answer: D. The Group By clause must specify a column or set up of columns contained in the SELECT clause. Here PUBID is not independent in the SELECT clause, hence the query is not valid.

84. Which of the below statements are true most the nesting of grouping functions?

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

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

85. What are the statistical group functions in Oracle?

AVG
STDDEV
VARIANCE
STATS

Answer: B, C. VARIANCE and STATS are the statistical group functions available 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 column or set up of columns independent in the SELECT clause.

87. Examine the table structure every bit given.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 NOT Aught NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 NOT NULL VARCHAR2(25)  EMAIL			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(xx)  HIRE_DATE		 NOT NULL Appointment  JOB_ID 		 NOT NULL VARCHAR2(10)  Salary 			  NUMBER(8,2)  COMMISSION_PCT 		  NUMBER(2,two)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(four)

What is the best explanation every bit to why this SQL statement will NOT execute?

SELECT department_id "Department", AVG (bacon)"Average" FROM employees GROUP By Department;

Salaries cannot be averaged as non all the numbers volition divide evenly.
You lot cannot use a column alias in the GROUP Past clause.
The GROUP BY clause must have something to Grouping.
The section id is not listed in the departments table.

Reply: B. Neither Group BY clause nor HAVING clause works with column alias.

88. Which of the following data types are compatible with AVG, SUM, VARIANCE, and STDDEV functions?

Only numeric data types
Integers just
Whatsoever data type
All except numeric

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

Examine the table construction every bit given beneath and answer the questions 89 and 90 that follow.

SQL> DESC employees  Name			 Null?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Nix NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Not Aught VARCHAR2(25)  E-mail			 Not Nix VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Non Nothing DATE  JOB_ID 		 NOT NULL VARCHAR2(ten)  SALARY 			  NUMBER(eight,2)  COMMISSION_PCT 		  NUMBER(ii,2)  MANAGER_ID			  NUMBER(half-dozen)  DEPARTMENT_ID			  NUMBER(4)

89. Which of the below query will display the number of distinct task categories working in each department?

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

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

SELECT department_id , COUNT(job) FROM employees  Group BY department_id ;

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

Answer: D. Use Singled-out modifier to filter out the duplicates.

ninety. Evaluate this SQL argument:

SELECT employee_id , first_name , department_id , SUM(salary ) FROM employees  WHERE salary  > g Group BY department_id , employee_id , first_name  Social club BY hiredate;

Why will this statement cause an mistake?

The HAVING clause is missing.
The WHERE clause contains a syntax mistake.
The SALARY column is NOT included in the Grouping By clause.
The HIRE_DATE cavalcade is NOT included in the Group By clause.

Respond: D. All the columns appearing in SELECT and ORDER By clause must exist included in the GROUP By clause.

91. Which of the following statements is true about the Grouping BY clause?

To exclude rows before dividing them into groups using the GROUP By clause, you employ should a WHERE clause.
Yous must utilise the HAVING clause with the GROUP By clause.
Column alias tin be 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 can exclude rows before dividing them into groups.

92. Examine the table structure equally given.

SQL> DESC employees  Proper noun			 Zero?	  Blazon  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non NULL NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Non NULL VARCHAR2(25)  EMAIL			 NOT NULL VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Not Nix Date  JOB_ID 		 Non NULL VARCHAR2(10)  Salary 			  NUMBER(eight,2)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(4)

Interpret the result of the beneath query.

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

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

Respond: C. The query returns the earliest hired employee in each department.

93. Which statement about group functions is truthful?

Group functions except COUNT(*), ignore zilch values.
A query that includes a grouping function in the SELECT listing must include a GROUP BY clause.
Grouping functions can be used in a WHERE clause.
Group functions can just be used in a SELECT listing.

Answer: A. All the group functions except COUNT(*), ignore NULL values. It is because they process the values straight contained in a specific column.

94. Which of the following clauses represent valid uses of group functions?

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

Answer: B, C, D. Group functions can appear in SELECT, HAVING and Lodge Past clauses only.

95. Which of the post-obit statements are true about the GROUP BY clause?

The last column listed in the Group Past clause is the most major grouping.
The first column listed in the GROUP BY clause is the nigh major grouping.
A GROUP Past clause cannot exist used without an ORDER By clause.
The GROUP BY clause do not ensure the sorting of output.

Reply: 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 grouping while HAVING clause restricts groups.
WHERE clause cannot comprise a grouping function but HAVING clause tin can have.
WHERE clause can join multiple weather using AND or OR operators but HAVING clause cannot.
WHERE clause can appear in SELECT query without Group BY clause but HAVING clause cannot.

Answer: A, B, D. WHERE clause restricts the rows before grouping just HAVING restricts the groups.

97. Examine the table structure as given.

SQL> DESC employees  Name			 Nil?	  Type  ----------------------- -------- ----------------  EMPLOYEE_ID		 Non Nil NUMBER(6)  FIRST_NAME			  VARCHAR2(20)  LAST_NAME		 Not NULL VARCHAR2(25)  EMAIL			 Not Naught VARCHAR2(25)  PHONE_NUMBER			  VARCHAR2(20)  HIRE_DATE		 Non NULL Engagement  JOB_ID 		 NOT NULL VARCHAR2(10)  Salary 			  NUMBER(eight,2)  COMMISSION_PCT 		  NUMBER(2,2)  MANAGER_ID			  NUMBER(6)  DEPARTMENT_ID			  NUMBER(four)

Predict the issue of the below query.

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

It executes successfully.
It throws mistake because ORDER By clause is invalid.
It throws error because Group BY clause is invalid.
It throws error because GROUP Past and ORDER BY clause cannot exist used together.

Reply: A. ORDER By clause tin can use the group functions for sorting.