Scribd
Upload
129 views5 pages

Textbook exercise-G7-M6

1. The document provides examples of using SQL functions like MID(), LCASE(), ROUND(), aggregate functions like AVG(), COUNT(), MAX(), and clauses like GROUP BY, HAVING. Multiple choice, fill in the blank, true/false and matching questions are given to test understanding. 2. Scalar functions operate on each value individually and return single values like UCASE(), LCASE(), aggregate functions calculate a single value from multiple values like SUM(), COUNT(), MAX(). 3. The GROUP BY clause groups the result set while the HAVING clause filters the groups after applying aggregate functions.

Uploaded by

ahmed shazin
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
129 views5 pages

Textbook exercise-G7-M6

1. The document provides examples of using SQL functions like MID(), LCASE(), ROUND(), aggregate functions like AVG(), COUNT(), MAX(), and clauses like GROUP BY, HAVING. Multiple choice, fill in the blank, true/false and matching questions are given to test understanding. 2. Scalar functions operate on each value individually and return single values like UCASE(), LCASE(), aggregate functions calculate a single value from multiple values like SUM(), COUNT(), MAX(). 3. The GROUP BY clause groups the result set while the HAVING clause filters the groups after applying aggregate functions.

Uploaded by

ahmed shazin
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Answers of textbook exercises

Fill in the Blanks

1. M I D ( ) is used for extracting characters from a text field.


2. We Use L C A S E to convert a text to lower case letters
3. H A V I N G filters data after applying an aggregate function.
4. R O U N D is used for limiting decimal places of a real number.
5. An aggregate function returns a single value for multiple r o w s.

State True or False

1. Aggregate function average() can be applied to number fields


only.-True
2. Aggregate functions min() and max() can be applied to number
fields only.-False
3. HAVING clause can be used with any select query for filtering
data.-False
4. GROUP BY can be used with aggregate functions only.-True
5. SQL function mid() can be used with number fields.-False

Match the Following

1. Having Filtering
2. sum( ) Aggregate function
3. ucase( ) Capital letters

4. Now( ) Date and time


5. Mid( ) (column name, start, length)

Multiple choice questions


Questions 1-5 are based on the table given below.

tbl_data

ID NAME SCORE DOB

1 Joseph 90 2001-10-01
2 Tom 85 2003-09-21
3 Harry 88 2007-02-17
4 Paul 76 2005-07-14
5 George 79 2001-11-06

1. select Max (Score) from tb1_data;


a. 90
b. 85
c. 88
d. 76

2. Select sum (name) from tb1 data;


a. Error
b. 0
c. 1
d. 255

3. Select count (name) from tb1_data;


a. 4
b. 3
c. 5
d. 8

4. Select avg (Score) from tb1_data;


a. 81.9
b. 83.6
c. 86.3
d. 85

5. select Min (name) from tbl data;


a. Harry
b. PAUL
c. George
d. Joseph

Answer in one word or one sentence

1. write a query to list the year of birth of all candidates in the


above table.
SELECT name,YEAR(birth_date) from candidates;

2. Write a query to update the name of “Tom” to “Johnny”.


UPDATE table_name SET = "Johnny" where name = "Tom";

3. What is the difference between WHERE clause and HAVING


clause?
The HAVING clause was added to SQL because the WHERE keyword
could not be used with aggregate functions.

4. What is the use of GROUP BY clause?


GROUP BY clause in SQL allows you to take your result set,group it
into logical groups and then run aggregate queries on the groups.

5. Write the SQL query to print current date and time.


SELECT NOW();

Answer the following

1. What are functions in SQL?


● Aggregate Functions
● Scalar functions

2. What are aggregate functions?


● AVG()
● COUNT()
● FIRST()
● LAST()
● MAX()
● MIN()
● SUM()

3. What are scalar functions?


● UCASE()
● LCASE()
● MID()
● LENGTH()
● ROUND()
● NOW()
● FORMAT()

4. What are the differences between aggregate functions and


scalar functions?
SQL aggregate functions return a single value, calculated from the
values in a column.
SQL scalar functions return a single value for each value of a
particular column given as input.
5. Explain the clauses used with aggregate functions.
● GROUP BY
- GROUP BY allows you to take your result set, group it into logical
groups, and then run aggregate queries on the groups.
● HAVING
- The HAVING clause is used with an aggregate function and allows
us to filter the result of SQL, after applying aggregate functions.

You might also like