Basic Commands 3

ALTER TABLE


The ALTER TABLE statement is used to add or drop columns in an existing table.

• ALTER TABLE table_name ADD column_name datatype;
• ALTER TABLE table_name DROP COLUMN column_name;

To add a column named "City" in the "Person" table:

• ALTER TABLE Person ADD City varchar(30);
• ALTER TABLE Person DROP COLUMN Address;

SQL Functions


Types of Functions




There are several basic types and categories of functions in SQL. The basic types of functions are:


• Aggregate Functions


• Scalar functions



Aggregate functions




Aggregate functions operate against a collection of values, but return a single value.


Function Description




AVG(column)


select avg(salary) from employees;


Returns the average value of a column


COUNT(column)


Returns the number of rows (without a NULL value) of a column


COUNT(*)


Returns the number of selected rows


FIRST(column) Returns the value of the first record in a specified field


LAST(column) Returns the value of the last record in a specified field


MAX(column)


Returns the highest value of a column


MIN(column)


Returns the lowest value of a column


STDEV(column)


STDEVP(column)


SUM(column)


Returns the total sum of a column


VAR(column)


VARP(column)




SQL GROUP BY and HAVING


SELECT column,SUM(column) FROM table GROUP BY column;


all the coumns selected should be called in group by.


SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

• SELECT Company,SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount)>10000;