Question

use SQL to do this, include output

apply all types of aggregate functions you have learned so far (functions like avg, sum, asc, desc, count, max, min) on the appropriate data from all the columns in Figure 1.4. Show the SQL query and the resultant output (Table).

building budget salary dept.name ID name 22222 12121 Einstein Wu 95000 Physics Finance History Comp. Sci Elec. Eng Biology Comp. Sci History Comp. Sci Music Physics Finance Watson Painter 70000 120000 90000 32343 El Said 60000 Painter 50000 Taylor Taylor Watson 45565 Katz Kim 75000 100000 98345 80000 85000 76766 10101 58583 Crick Srinivasan Califieri 72000 65000 90000 100000 Taylor Painter 62000 50000 83821 Brandt 92000 Taylor Packard 100000 15151 Mozart 40000 80000 33456 Gold 87000 Watson Painter 70000 76543 Singh 80000 120000 Figure 1.4 The faculty table.

Answer 1

Solution:

I am using mySQL workbench to execute the aggregate function queries.

Aggregate Functions in SQL:

Most commonly used aggregate functions are :

1.Min - This function is used to get the minimum value from set of values in a column.

Syntax: Select min(column_name) from tablename;

2.Max - This function is used to get the minimum value from set of values in a column.

Syntax: Select max(column_name) from tablename;

3.Count - This function is used to count the number of rows in a table.

Syntax: Select count(*) from tablename;

4.Sum - This function is used to calculate the sum of all values in a column.

Syntax: Select sum(column_name) from tablename;

5.Avg - This function is used to calculate the average of all the values in a column.

Syntax: Select avg(column_name) from tablename;

Output:

MySQL Workbench Local instance MySQL80 x View Query Tools Scripting Help File Edit Database Server Query 1 SOLAdditions Navigator Jump to SCHEMAS Limit to 1000 rows a Fiter objects insert into faculty values (45565, Katz', 75000, Comp. Sci.', Taylor', 100000); insert into faculty values (98345, 'Kim', 8ee*e, Elec. Engg. ', 'Taylor', 85000); 15 . Automatic context help is disabled. Use the toolbar to manually get help for the current caret position or to toggle automatic help. first 16 . 17 . insert into faculty values(76766, 'Crick', 72000, Biology', 'Watson', 90000); evs insert into faculty values ( 10101, 'Srinivasan', 6500e, 'Comp. Sci., 'Taylor, 100000) ; 18 # world insert into faculty values(58583, 'Califieri', 62000, 'History', 'Painter', 50000); insert into faculty values (83821, 'Brandt', 92000, Comp. Sci.', 'Taylor', 100000 ); insert into faculty values (15151, Mozart', 40000, Music', 'Packard', 80000) insert into faculty values (33456, Gold', 87000, Physics', Watson', 70000); 19 . 20 . 21 22 . Fiter RowE Edit Export/Import Wrap Cell Content Result Grid salary dept name building budget IC Name Resub 10101 Srinivasan 65000 Comp. Sci. Taylor 100000 Grid Administration Schemas 95000 Physics 22222 Einstein 70000 Watson 32343 El Said 60000 History Painter 50000 Information Edtor 87000 Physics 33456 Gold 70000 Watson 45565 Katz 75000 Comp. Sci. Tavlor 100000 No object selected Calfieri 58583 62000 History Painter 50000 Painter 75543 Singh a0000 Finance 2000 Field Types on Brandt Tavlor 83821 92000 Comp. Sci. 100000 Elec. Engg 98345 Kim 80000 Taylor 85000 121121 Wu NULL 90000 NULL Finance NULL Painter NULL 120000 NULL NULL faculty 2 x Context Help Snippets Revert Output Object Info Session ENG 12:07 AM O Type here to search 38% L . IN 6/2/2019

MySQL Workbench Local instance MySQL80 x Tools Scripting Help View Query File Edit Database Server Query 1: SOLAdditions Navigator Lmt to 1000 rows B AO SO select count(*) from faculty Jump to SCHEMAS a Fiter objects 1 . Automatic context help is disabled. Use the toolbar to manually get help for the current caret position or to toggle automatic help. first evs world | Export: Wrap Cell Content Result Grid Fiter Rows count( Reaut Grid 12 Administration Schemas Information No object selected Field Result 3 x 0 Read Only Context Help Snippets Output Object Info Session ENG 12:09 AM Type here to search 3/% L . IN 6/2/2019

MySQL Workbench Local instance MySQL80 x View Query Tools Scripting Help File Edit Database Server Query 1: SOLAdditions Navigator Jump to SCHEMAS Limit to 1000 rows a Fiter objects select max(salary), max(budget) from faculty; 1 . Automatic context help is disabled. Use the toolbar to manually get help for the current caret position or to toggle automatic help. first evs world Rezult GridHH | Export: Wrap Cell Content Fiter Rowss max(salary) max(budget) Reaut Grid 95000 120000 Administration Schemas Information No object selected Field Result 4 x 0 Read Only Context Help Snippets Output Object Info Session ENG 12:10 AM Type here to search 3/% IN 6/2/2019

MySQL Workbench Local instance MySQL80 x View Query Tools Scripting Help File Edit Database Server Query 1: SOLAdditions Navigator Jump to SCHEMAS Limt to 1000 rows a Fiter objects select min(salary), min(budget) from faculty; 1 . Automatic context help is disabled. Use the toolbar to manually get help for the current caret position or to toggle automatic help. first evs world Result Grid H Fiter Rows | Export: Wrap Cell Content min(salary) min(budget) Reaut Grid 40000 50000 Administration Schemas Information No object selected Field Result 5 x 0 Read Only Context Help Snippets Output Object Info Session ENG 12:10 AM Type here to search 3/% IN 6/2/2019

MySQL Workbench Local instance MySQL80 x View Query Tools Scripting Help File Edit Database Server Query 1: SOLAdditions Navigator Jump to SCHEMAS Limit to 1000 rows a Fiter objects select sum(salary), sum(budget) from faculty; 1 . Automatic context help is disabled. Use the toolbar to manually get help for the current caret position or to toggle automatic help. first evs world Result Grid H Fiter Rows Export Wrap Cell Content sum(salary) sum(budget) Reaut Grid 898000 1035000 Administration Schemas Information No object selected Field Result 6 x 0 Read Only Context Help Snippets Output Object Info Session ENG 12:11AM Type here to search 3/% L . IN 6/2/2019

MySQL Workbench Local instance MySQL80 x View Query Tools Scripting Help File Edit Database Server Query 1: SOLAdditions Navigator Jump to SCHEMAS Limit to 1000 rows a Fiter objects select avg(salary), avg(budget from faculty 1 . Automatic context help is disabled. Use the toolbar to manually get help for the current caret position or to toggle automatic help. first evs world Result Grid | Export: Wrap Cell Content Fiter Rowsr avg(salary) avg(budget) Reaut Grid 74833.3333 86250.0000 Administration Schemas Information No object selected Field Result 7 x 0 Read Only Context Help Snippets Output Object Info Session ENG 12:11AM O Type here to search 3/% L . IN 6/2/2019