What’s wrong in the following query?
SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;
Answer:
The WHERE clause cannot be used to restrict groups. The HAVING clause should be used.
SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;View
What’s wrong in the following query?
SELECT subject_code, count(name) FROM students;
Answer:
It doesn’t have a GROUP BY clause. The subject_code should be in the GROUP BY clause.
SELECT subject_code, count(name) FROM students GROUP BY subject_code;View
Say True or False. Give explanation if False.
COUNT(*) returns the number of columns in a table.
Answer:
False. COUNT(*) returns the number of rows in a table.
ViewSay True or False. Give explanation if False.
By default the group functions consider only distinct values in the set.
Answer:
By default, group functions consider all values including the duplicate values.
ViewWhat is the purpose of the group functions in SQL? Give some examples of group functions.
Answer:
Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
ViewWhat is the difference between cross joins and natural joins?
Answer:
The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.
ViewYou want to display a result query from joining two tables with 20 and 10 rows respectively. Erroneously you forget to write the WHERE clause. What would be the result?
Answer:
The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.
ViewWhich expressions or functions allow you to implement conditional processing in a SQL statement?
Answer:
There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement.
Using CASE expression
Using the DECODE function
Discuss the syntax and use of the COALESCE function?
Answer:
The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)
It returns the first non-null expression given in the parameter list.
ViewWhat is the use of the NULLIF function?
Answer:
The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.
ViewWhat is the difference between the NVL and the NVL2 functions?
Answer:
The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1.
The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.
ViewWhat is the purpose of the NVL function?
Answer:
The NVL function converts a NULL value to an actual value.
ViewWhich function returns the remainder in a division operation?
Answer:
The MOD function returns the remainder in a division operation.
ViewWhat are the case manipulation functions of SQL?
Answer:
LOWER, UPPER, INITCAP
ViewWhat are the specific uses of SQL functions?
Answer:
SQL functions have the following uses −
Performing calculations on data
Modifying individual data items
Manipulating the output
Formatting dates and numbers
Converting data types
What is the default ordering of data using the ORDER BY clause? How could it be changed?
Answer:
The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.
ViewHow do you search for a value in a database table when you don’t have the exact value to search for?
Answer:
In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called ‘wildcard’ search.
ViewWhat is the purpose of the condition operators BETWEEN and IN?
Answer:
The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.
ViewIf a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?
Answer:
A query result displays all rows including the duplicate rows. To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause.
ViewIs a NULL value same as zero or a blank space? If not then what is the difference?
Answer:
A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.
View© 2017 QuizBucket.org