CUET Information practice (IP) Chapter 7 Accessing MySQL Database Using ODBC/JDBC

(c)

Exp: Group Functions are also called multiple-row and aggregate functions. They work with data of multiple rows and return one result per group of rows processed.

(a)

Exp: Distinct option causes a group function to consider only distinct values of argument expression.

(d)

Exp: All option causes a group function to consider all values including all duplicates.

(c)

Exp: The Avg function computes the average of the given data. It returns the average value of parameter(s) n. The syntax is: AVG ([DISTINCT | ALL] n).

(b)

Exp: Count function counts the number of rows in a given column or expression. It returns number of rows in the query. The syntax is: COUNT ({* DISTINCT | ALL} expr). If we specify asterisk (*), this function returns all rows including duplicates and nulls.

(b)

Exp: Max function returns the maximum value from a given column or expression. It returns the maximum value of argument expr. The syntax is: MAX ([DISTINCT | ALL] expr).

(a)

Exp: Min function returns the minimum value from a given column or expression. It returns the minimum value of argument expr. The syntax is: MIN ([DISTINCT | ALL] expr).

(d)

Exp: Sum function returns sum of values in a given column or expression. It returns sum of values of n. The syntax is: SUM ([DISTINCT | ALL] n).

(b)

Exp: Avg, Count, Max, Min and Sum are called aggregate functions because they operate on aggregate of tuples. The result of an aggregate function is a single value.

(b)

Exp: To calculate the total salary for teachers of grade ‘A1’ is: SELECT SUM (gross) FROM teacher

where grade = ‘A1’;

(c)

Exp: Single row or scalar functions work with a single row at a time. They return a result for every row of a queried table. Example of single row functions are the text/character functions such as year(), day() etc.

(a)

Exp: Group Functions are also called multiple-row and aggregate functions. They return one result per group of rows processed.

(b)

Exp: Multiple row or group functions are the group functions that return aggregate value. For example, sum(), count(), max(), min(), avg() etc.

(d)

Exp: The difference between scalar function and multiple function is in the no. of rows they act upon. A single row function works with the data of a single row at a time and returns a single result for each row queried upon; a multiple row function works with a group of rows and returns a single result for that group.

(b)

Exp: Group by clause combines all the records that have identical values in a particular field or a group of fields. This grouping results into one summary record per group if group functions are used with it.

(c)

Exp: With Group by clause, we can create groups within groups. Such type of grouping is called nested grouping. This can be done by specifying in Group by expression, where the first field determines the highest group level, the second field determines the second group level and so on.

(a)

Exp: A non-group field (or expression) is the field that has different values in the rows belonging to the group. MySQL will not create any error in which we include a non-group expression in the select-list.

(c)

Exp: Having clause places conditions on groups. Having conditions include aggregate functions. The having clause can contain either a simple Boolean expression or use aggregate function in the having condition.

(d)

Exp: A join is a query that combines rows from two or more tables. In a join-query, more than one table are listed in FROM clause. The function of combining data from multiple tables is called joining.

(b)

Exp: In unrestricted join or Cartesian product of two tables, all possible concatenations are formed of all rows of both the tables.

(b)

Exp: Qualified field names are very useful in identifying a field if the two joining tables have fields with same name. Therefore, the columns that have similar names in both tables should be given qualified names, i.e., table name.column name, to avoid ambiguity.

(c)

Exp: In an equi-join, the values in the columns being joined are compared for equality. All columns in the tables being joined are included in the results. Here, all columns from joining table appear in the output even if they are identical.

(b)

Exp: From clause lists all the tables involved and where clause specifies the join. Without a where clause, each row of the first table will be joined with every row of the second table which results in a Cartesian product.

(a)

Exp: Non-equi-join is a query that specifies some relationship other than equality between the columns.

(c)

Exp: Through natural join one of the two identical columns can be eliminated by restating the query.

(a)

Exp: The Cartesian product of two tables is also known as cross join. The cross join (or Cartesian product) is a join that matches each row from one table to every row from another table. Cross join, by default, does not apply any filtering condition on the joined data. However, if we want to filter, we can use where clause of SELECT query.

(c)

Exp: With natural join clause, we need not specify a join condition. It will automatically create natural-join through appropriate common field.

(d)

Exp: A table alias is a temporary label given along with table name in FROM clause. It is used to cut down the amount of typing required in queries.

(b)

Exp: The where clause specifies search condition used to determine data that will appear in the result table. The syntax is: SELECT column name 1, column name 2, … FROM table_name [WHERE condition];

(a)

Exp: The query to calculate the sum can be written as follows: SELECT SUM(SALARY) FROM EMPLOYEE;

This query will calculate the sum of all the employees working in the Employee table and the result will give the output as 375000.

(c)

Exp: The query SELECT SUM(SALARY) FROM EMPLOYEE;

will calculate the sum of all the employees working in the Employee table and the result will give the output as 375000.

(d)

Exp: The correct query is: SELECT SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_NAME;

The Group By clause combines all the records that have identical values in a particular field or a group of fields. This grouping results into one summary record per group if group functions are used with it.

(b)

Exp: To know the Sum for each particular department, the query will be SELECT DEPT_NAME, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_NAME;

The Group By clause combines all the records that have identical values in a particular field or a group of fields. This grouping results into one summary record per group if group functions are used with it.

(d)

Exp: To COUNT the number of records in Employee table, the query will be written as: SELECT COUNT (*) “Total” FROM EMPLOYEE

Therefore, the number of records in Employee table is 7.

(c)

Exp: To count the number of Department names in Employee table, the query will be:

SELECT COUNT (DEPT_NAME) “Dept Name Count” FROM EMPLOYEE

Therefore, there are four departments in all.

(a)

Exp: The query for the avg of all the salaries is SELECT AVG(SALARY) FROM EMPLOYEE. This query will return the value as 62500.

The SELECT AVG(SALARY) FROM EMPLOYEE would ignore nulls and the way the average is calculated then would be 50,000+60,000+50,000+70,000+75,000+70,000 / 6 = 62500.

(c)

Exp: Here, Count (*) will return the value 7 and Count (Salary) will return the value as 6, because COUNT(*) is not going to ignore the Nulls in the result whereas COUNT(SALARY) is going to ignore the Nulls.

(c)

Exp: The query to find the minimum salary within a particular department is:

SELECT MIN(SALARY),NAME FROM EMPLOYEE

GROUP BY NAME;

Therefore, the minimum salary will be 50000.

(a)

Exp: The query to find the maximum salary within a particular department is:

SELECT MAX(SALARY),NAME FROM EMPLOYEE GROUP BY NAME;

Therefore, the maximum salary will be 75000.

(d)

Exp: The syntax for Group By function is:

SELECT column name 1, column name 2,.

FROM table name

[GROUP BY column name];

The Group By clause combines all the records that have identical values in a particular field or a group of fields. This grouping results into one summary record per group if group functions are used with it.

(b)

Exp: To join n tables together, we need a minimum of (n-1 join conditions. Therefore, to join 5 tables, we require (n-1 joins. Here, n = 5. So, (5-1 = 4 joins.

(d)

Exp: Cartesian product is also known as unrestricted joinor cross join. It is a binary operation and is denoted by (X). It returns all rows in all tables listed in query.

(d)

Exp: Left join returns all rows from first table where there are matches in second table or not. For unmatched rows of first table, NULL is shown in columns of second table. For example, SELECT <select-list> FROM <table1> LEFT JOIN <table2> ON <joining-condition>;

(a)

Exp: Right join returns all rows from second table where there are matches in first table or not. The syntax is:

SELECT <select-list> FROM <table1> RIGHT JOIN <table2> ON <joining-condition>;

(c)

Exp: The correct query to select all information of patients of cardiology department is

SELECT * FROM HOSPITAL

WHERE Department = ‘Cardiology’;

The SELECT statement is used to pull information from a table. The where clause specifies search condition used to determine data that will appear in the result table.

(b)

Exp: The correct query to list the names of female patients who are in ENT department is

SELECT Name FROM HOSPITAL

WHERE Department = ‘ENT’ and Sex = ‘F’;

The SELECT statement is used to pull information from a table. The where clause specifies search condition used to determine data that will appear in the result table.

(a)

Exp: The correct query to list names of all patients with their date of admission in ascending order is

SELECT Name FROM HOSPITAL

ORDER BY Dateofadm;

The SELECT statement is used to pull information from a table.

(d)

Exp: The correct query to display Patient’s Name, Charges, Age of only female patients is

SELECT Name, Charges, Age FROM HOSPITAL

WHERE Sex = ‘F’;

The SELECT statement is used to pull information from a table. The where clause specifies search condition used to determine data that will appear in the result table.

(c)

Exp: The correct query to count number of patients with Age < 30 is

SELECT COUNT (*) FROM HOSPITAL WHERE Age < 30;

The SELECT statement is used to pull information from a table. The where clause specifies search condition used to determine data that will appear in the result table.

(a)

Exp: ON clause requires a complete join-condition and produces equi-join.