SQL Functions

SQL has many built-in functions for performing calculations on data. SQL Aggregate Functions SQL aggregate functions return a single value, calculated from values in a column. Useful aggregate functions: AVG() – Returns the average value COUNT() – Returns the number of rows FIRST() – Returns the first value LAST() – Returns the last value MAX() – Returns the largest value MIN() – Returns the smallest value SUM() – Returns the sum SQL Scalar functions SQL scalar functions return a…

Read More

Posted in SQL Comments Off on SQL Functions
SQL NULL Functions

SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions We have the following SELECT statement: SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder) FROM Products In the example above, if any of the “UnitsOnOrder” values are NULL, the result is NULL. Microsoft’s ISNULL() function is used to specify how we want to treat NULL values. The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result. In this case we want NULL values to be zero. Below, if “UnitsOnOrder” is NULL it will…

Read More

Posted in SQL Tagged Comments Off on SQL NULL Functions
SQL NULL Values

NULL values represent missing unknown data. By default, a table column can hold NULL values. This chapter will explain the IS NULL and IS NOT NULL operators. SQL NULL Values If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value. NULL values are treated differently from other values. NULL is used…

Read More

Posted in SQL Tagged Comments Off on SQL NULL Values
SQL Date Functions

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated. Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates. MySQL Date Functions The following table lists the most important built-in date functions in MySQL: Function Description NOW() Returns the current date and time CURDATE() Returns the current date CURTIME() Returns…

Read More

Posted in SQL Tagged Comments Off on SQL Date Functions
SQL Views

A view is a virtual table. This chapter shows how to create, update, and delete a view. SQL CREATE VIEW Statement In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the…

Read More

Posted in SQL Tagged Comments Off on SQL Views
SQL AUTO INCREMENT Field

Auto-increment allows a unique number to be generated when a new record is inserted into a table. AUTO INCREMENT a Field Very often we would like the value of the primary key field to be created automatically every time a new record is inserted. We would like to create an auto-increment field in a table. Syntax for MySQL The following SQL statement defines the “ID” column to be an auto-increment primary key field in the “Persons” table: CREATE TABLE…

Read More

Posted in SQL Tagged Comments Off on SQL AUTO INCREMENT Field
SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. SQL ALTER TABLE Syntax To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name To change the data type of a column in a table, use the following syntax:…

Read More

Posted in SQL Tagged Comments Off on SQL ALTER TABLE Statement
SQL DROP INDEX – DROP TABLE – DROP DATABASE

Indexes, tables, and databases can easily be deleted/removed with the DROP statement. The DROP INDEX Statement The DROP INDEX statement is used to delete an index in a table. DROP INDEX Syntax for MS Access: DROP INDEX index_name ON table_name DROP INDEX Syntax for MS SQL Server: DROP INDEX table_name.index_name DROP INDEX Syntax for DB2/Oracle: DROP INDEX index_name DROP INDEX Syntax for MySQL: ALTER TABLE table_name DROP INDEX index_name   The DROP TABLE Statement The DROP TABLE statement is…

Read More

Posted in SQL Tagged Comments Off on SQL DROP INDEX – DROP TABLE – DROP DATABASE
SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table. Indexes An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only…

Read More

Posted in SQL Comments Off on SQL CREATE INDEX Statement
SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. SQL DEFAULT Constraint on CREATE TABLE The following SQL creates a DEFAULT constraint on the “City” column when the “Persons” table is created: My SQL / SQL Server / Oracle / MS Access: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City…

Read More

Posted in SQL Tagged Comments Off on SQL DEFAULT Constraint