SQL PART 4 E

SQL Create Database, Table, and Index

Create a Database

To create a database:

CREATE DATABASE database_name

Create a Table

To create a table in a database:

CREATE TABLE table_name

(
column_name1 data_type,
column_name2 data_type,
)

Example

This example demonstrates how you can create a table named "Person", with four columns. The column names will be "LastName", "FirstName", "Address", and "Age":

CREATE TABLE Person

(
LastName varchar,

FirstName varchar,

Address varchar,

Age int
)
This example demonstrates how you can specify a maximum length for some columns:
CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)
The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:

Data Type Description

integer(size)

int(size)

smallint(size)

tinyint(size) Hold integers only. The maximum number of digits are specified in parenthesis.

decimal(size,d)

numeric(size,d) Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".

char(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.

varchar(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.

date(yyyymmdd) Holds a date

Create Index

Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.

Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.

A Unique Index
Creates a unique index on a table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name

ON table_name (column_name)
The "column_name" specifies the column you want indexed.

A Simple Index
Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.

CREATE INDEX index_name

ON table_name (column_name)
The "column_name" specifies the column you want indexed.
Example
This example creates a simple index, named "PersonIndex", on the LastName field of the Person table:
CREATE INDEX PersonIndex
ON Person (LastName)
If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:
CREATE INDEX PersonIndex
ON Person (LastName DESC)

If you want to index more than one column you can list the column names within the parentheses, separated by commas:

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)
SQL Drop Index, Table and Database
Drop Index

You can delete an existing index in a table with the DROP statement.

DROP INDEX table_name.index_name

Delete a Table or Database
To delete a table (the table structure, attributes, and indexes will also be deleted):

DROP TABLE table_name

To delete a database:

DROP DATABASE database_name

Truncate a Table

What if we only want to get rid of the data inside a table, and not the table itself? Use the TRUNCATE TABLE command (deletes only the data inside the table):

TRUNCATE TABLE table_name

SQL ALTER TABLE

ALTER TABLE

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

ALTER TABLE table_name

ADD column_name datatypeALTER TABLE table_name

DROP COLUMN column_name

Note: Some database systems don't allow the dropping of a column in a database table (DROP COLUMN column_name).

Person:

LastName FirstName Address

Pettersen Kari Storgt 20

Example

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

ALTER TABLE Person ADD City varchar(30)

Result:

LastName FirstName Address City

Pettersen Kari Storgt 20

Example

To drop the "Address" column in the "Person" table:

ALTER TABLE Person DROP COLUMN Address

Result:

LastName FirstName City

Pettersen Kari

SQL Functions

SQL has a lot of built-in functions for counting and calculations.

Function Syntax
The syntax for built-in SQL functions is:
SELECT function(column) FROM table

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.

Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!

"Persons" table (used in most examples)

Name Age

Hansen, Ola 34

Svendson, Tove 45

Pettersen, Kari 19

Aggregate functions in MS Access

Function Description

AVG(column) 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 the specified field

LAST(column) Returns the value of the last record in the 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)

Aggregate functions in SQL Server

Function Description

AVG(column) Returns the average value of a column

BINARY_CHECKSUM

CHECKSUM

CHECKSUM_AGG

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

COUNT(*) Returns the number of selected rows