AVG Function
AVG function returns the average value of an expression.
Syntax
SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];
select avg (salary) from customer where dept_name=’cs’;
Example - Using DISTINCT
SQL statement below returns the average salary of unique salary values where
the salary is above $40,000 / year.
SELECT AVG(DISTINCT salary) AS "Average Salary"
FROM employees
WHERE salary > 40000;
If there were two salaries of $50,000/year, only one of these values would be used in the AVG
function
Example - Using GROUP BY
SELECT name, AVG(age) AS "Average age"
FROM customer
GROUP BY name;
MIN Function
MIN function returns the minimum value of an expression.
Syntax
SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];
Example
SELECT MIN(quantity) AS "Lowest Quantity"
FROM products;
Example - Using GROUP BY
SELECT product_type, MIN(quantity) AS "Lowest Quantity"
FROM products
GROUP BY product_type;
MAX Function
MAX function returns the maximum value of an expression.
Syntax
SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];
Example
SELECT MAX(quantity) AS "Highest Quantity"
FROM products;
Example - Using GROUP BY
SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;
COUNT Function
COUNT function returns the count of an expression.
Syntax
SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];
Only includes NOT NULL Values
the COUNT function will only include the records in the count where the value of expression in
COUNT(expression) is NOT NUL
SELECT
COUNT(website_id)
FROM websites;
Result: 3
SELECT COUNT(website_name)
FROM websites;
Result: 1
UPDATE Statement
The SQL Server (Transact-SQL) UPDATE statement is used to update existing records in a
table in a SQL Server database. There are 3 syntaxes for the UPDATE statement depending on
whether you are performing a traditional update or updating one table with data from another
table.
Syntax
UPDATE table
SET column1 = expression1,
column2 = expression2,
...
[WHERE conditions];
Example
UPDATE employees
SET last_name = 'mohamed'
WHERE employee_id = 10
Example - Update multiple columns
UPDATE employees
SET first_name = 'ahmed',
employee_id = 14
WHERE last_name = 'mohamed';
The syntax for the UPDATE statement when updating one table with data from another
table in SQL Server (Transact-SQL) is:
UPDATE table1
SET column1 = (SELECT expression1
FROM table2
WHERE conditions)
[WHERE conditions];
Example - Update table with data from another table
UPDATE employees
SET first_name = (SELECT first_name
FROM contacts
WHERE contacts.last_name = employees.last_name)
WHERE employee_id > 95;
