SQL Basic Functions Chapter1

SQL Functions

 Ø  SUM Function: to get sum the values of the column

Syntax:

SELECT SUM (column_name) FROM Table_Name

Example:

SELECT SUM (Hours) FROM tbl_Abc

Ø  AVG Function: to get the avg of the column

SELECT AVG (hours) FROM tbl_Abc

Ø  Min/MAX Function:

Syntax:

SELECT MIN (wage) AS [Minimum Wage],
MAX(wage) AS [Maximum Wage] FROM tbl_Abc

Example:
MIN Function With BETWEEN Operator

SELECT OrderID, MIN(Quantity) as Quantity
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID

OrderID	Quantity
11000	25
11001	6
11002	15

Modify it one more time for the MAX function:

SELECT OrderID, MAX(Quantity) Quantity
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID

OrderID	Quantity
11000	30
11001	60
11002	56

Ø  Round Function

Syntax:

Select Round (number, n)

Example:

SELECT ROUND (1.2536, 3) as Roundof;
SELECT ROUND (1.2536, 2) as roundof;

Result:

Roundof: - 1.2540Roundof: - 1.2500

Ø  Ceiling Function

Syntax:

Ceiling (number value)

 Number value is the value which is used to find the smallest integer value. Example:

Select Ceiling (32.65) as Ceiled                                                   would return, Ceiled: 33
select Ceiling (32) as Ceiled                                                          would return, Ceiled: 32
Select Ceiling (-32.65) as Ceiled                                                  would return, Ceiled: 32
Select Ceiling (-32) as Ceiled                                                        would return, Ceiled: -32

 Ø  Floor Function:

        Syntax:

Floor (Any number)

 Example:

SELECT FLOOR(4.88)

would return 4

 

Ø  Sqrt Function:

Syntax:

sqrt( value)

Example:

SELECT SQRT(9)

would return 3

 

Ø  Top Function:

Syntax:
Select Top (any number) from table name

 Example:

SELECT TOP 5 Name, CreditRating

FROM tbl_Vendor
ORDER BY CreditRating DESC, Name;

Result:

Name	CreditRating
Merit Bikes	5
Victory Bikes	5
Proseware, Inc.	4
Recreation Place	4
Consumer Cycles	3

 Ø  Bottom Function:

Let’s we’ve below table

Name	Wage
Mike	$250
Jack	$328
Gonaa	$450
Darek	$678
jacob	$1,000
Michel	$870 Bottom of Form

 

 

 

Syntax with Example:

SELECT TOP 2 names, wage FROM tbl_Employee
ORDER BY wage DESC

Result:

Name	Wage
Michel	$870
jacob	$1,000 Bottom of Form

Ø  Distinct Function:

Let’s we’ve below table

Category

A

B

C

A

F

None

 

 

 

Syntax:

SELECT DISTINCT columns FROM tables WHERE predicates;

Example:

SELECT DISTINCT Category FROM Tbl_Category;

Result:

Category

Michel

jacob

 Ø  SQL LEFT/RIGHT

Syntax for LEFT:

SELECT names, LEFT (names, 3) AS [left] FROM tbl_Employee

Syntax for RIGHT:

SELECT names, Right (names, 3) AS [Right] FROM tbl_Employee

Names	Left	Right
Sumon Bagui	Sum	gui
Sudip Bagui	Sud	gui
Priyashi Saha	Pri	aha
Ed Evans	Ed	ans

 

 

SQL Views

SQL VIEWS

The SQL view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.

Creating an SQL VIEW

The syntax for creating a VIEW in SQL is:

CREATE VIEW view_name AS

  SELECT columns

  FROM table

  WHERE predicates;

SQL View Creation - Example

CREATE VIEW SQLTest AS

  SELECT *  FROM suppliers, orders

  WHERE suppliers.supplier_id = orders.supplier_id

  and suppliers.supplier_name = 'IBM';

This SQL View (Create statement) would create a virtual table based on the result set of the select statement. You can now query the view as follows:

SELECT *

FROM sup_orders;

Alter/Updating an SQL VIEW

You can modify the definition of a VIEW in SQL without dropping it by using the following syntax:

ALTER VIEW view_name AS

  SELECT columns

  FROM table

  WHERE predicates;

SQL View Modify - Example

ALTER VIEW sup_orders AS

  SELECT suppliers.supplier_id, orders.quantity, orders.price

  FROM suppliers, orders

  WHERE suppliers.supplier_id = orders.supplier_id

  and suppliers.supplier_name = 'Microsoft';

This SQL View (Create/Replace statement) would update the definition of the SQL View without dropping it. If the SQL View did not yet exist, the SQL View would merely be created for the first time.

Dropping an SQL VIEW

The syntax for dropping a VIEW in SQL is:

DROP VIEW view_name;

SQL View Drop - Example

DROP VIEW sup_orders;

This SQL View (Drop statement) would drop/delete the SQL View called sup_orders.