Sql From W3

Front 1

RDBMS

Back 1

Relational Database Management System

Front 2

DML
DDL

Back 2

Data Manipulation Language (DML)
Data Definition Language (DDL)

Front 3

SQL SELECT Syntax

Back 3

SELECT column_name(s)
FROM table_name

Front 4

SELECT * FROM table_name

Back 4

SQL SELECT Syntax

Front 5

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT LastName,FirstName FROM Persons (exmp)

Back 5

LastName FirstName
Hansen Ola
Svendson Tove
Pettersen Kari

Front 6

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT * FROM Persons

Back 6

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Front 7

SQL WHERE Syntax

Back 7

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

Front 8

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT * FROM Persons
WHERE City='Sandnes'

Back 8

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

Front 9

Operators Allowed in the WHERE Clause

Back 9

= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns

Front 10

OR Operator Example

Back 10

SELECT * FROM Persons
WHERE FirstName='Tove'
OR FirstName='Ola

Front 11

Combining AND & OR

Back 11

SELECT * FROM Persons WHERE
LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')

Front 12

The ORDER BY Keyword

Back 12

The ORDER BY keyword is used to sort the result-set by a specified column.

Front 13

SQL ORDER BY Syntax

Back 13

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

Front 14

SQL ORDER BY Syntax with SELECT statement

Back 14

SELECT * FROM Persons
ORDER BY LastName

Front 15

ORDER BY DESC Example

Back 15

Now we want to select all the persons from the table above, however, we want to sort the persons descending by their last name.

SELECT * FROM Persons
ORDER BY LastName DESC

Front 16

SQL INSERT INTO Syntax v1

Back 16

INSERT INTO table_name
VALUES (value1, value2, value3,...)

Front 17

SQL INSERT INTO Syntax v2

Back 17

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Front 18

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

Back 18

INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

Front 19

Insert Data Only in Specified Columns

Back 19

INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, 'Tjessem', 'Jakob')

Front 20

The UPDATE Statement

Back 20

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Front 21

The DELETE Statement

Back 21

DELETE FROM table_name
WHERE some_column=some_value

Front 22

SQL DELETE Example

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger
5 Tjessem Jakob Nissestien 67 Sandnes

Back 22

DELETE FROM Persons
WHERE LastName='Tjessem' AND FirstName='Jakob'

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger

Front 23

Delete All Rows

Back 23

DELETE FROM table_name

or

DELETE * FROM table_name

Front 24

The TOP Clause

Back 24

SELECT TOP number|percent column_name(s)
FROM table_name

Front 25

TOP Clause example

Back 25

SELECT *
FROM Persons
LIMIT 5

Front 26

SQL LIKE Syntax

Back 26

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

Front 27

LIKE Operator Example

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Back 27

SELECT * FROM Persons
WHERE City LIKE 's%'

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Front 28

SQL Wildcards

Back 28

% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist]
or

[!charlist]

Any single character not in charlist

Front 29

SQL Wildcard Examples

Back 29

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT * FROM Persons
WHERE City LIKE 'sa%'

Front 30

Using the _ Wildcard

Back 30

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT * FROM Persons
WHERE FirstName LIKE '_la'

P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes

Front 31

Using the [charlist] Wildcard

Back 31

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'

Front 32

Using the [charlist] Wildcard not

Back 32

we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%'

Front 33

The IN Operator

Back 33

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

Front 34

IN Operator Example

Back 34

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen')

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Front 35

The BETWEEN Operator

Back 35

The BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Front 36

SQL Alias

Back 36

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names.

An alias name could be anything, but usually it is short.

Front 37

SQL Alias Syntax for Tables

Back 37

SELECT column_name(s)
FROM table_name
AS alias_name

Front 38

SQL Alias Syntax for Columns

Back 38

SELECT column_name AS alias_name
FROM table_name

Front 39

Different SQL JOINs

Back 39

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

Front 40

SQL INNER JOIN Keyword

Back 40

The INNER JOIN keyword return rows when there is at least one match in both tables.

Front 41

SQL INNER JOIN Syntax

Back 41

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Front 42

SQL INNER JOIN Example

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Back 42

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678

Front 43

SQL LEFT JOIN Keyword

Back 43

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

Front 44

SQL LEFT JOIN Example

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Back 44

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove

Front 45

SQL RIGHT JOIN Keyword

Back 45

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

Front 46

SQL RIGHT JOIN Syntax

Back 46

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Front 47

The "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Back 47

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
34764

Front 48

SQL FULL JOIN Keyword

Back 48

The FULL JOIN keyword return rows when there is a match in one of the tables.

Front 49

SQL FULL JOIN Syntax

Back 49

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Front 50

SQL FULL JOIN Example

The "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Back 50

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
34764

Front 51

The SQL UNION Operator

Back 51

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Front 52

SQL UNION Syntax

Back 52

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Front 53

SQL UNION ALL Syntax

Back 53

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

Front 54

SQL UNION Example

Back 54

E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Front 55

The SQL SELECT INTO Statement

Back 55

The SELECT INTO statement selects data from one table and inserts it into a different table.

The SELECT INTO statement is most often used to create backup copies of tables.

Front 56

SQL SELECT INTO Syntax

Back 56

We can select all columns into the new table:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
Or we can select only the columns we want into the new table:

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

Front 57

SQL SELECT INTO Example

Back 57

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.

We use the following SQL statement:

SELECT *
INTO Persons_Backup
FROM Persons
We can also use the IN clause to copy the table into another database:

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons
We can also copy only a few fields into the new table:

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

Front 58

SQL SELECT INTO - With a WHERE Clause

Back 58

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'

Front 59

SQL SELECT INTO - Joined Tables

Back 59

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

Front 60

SQL CREATE DATABASE Syntax

Back 60

CREATE DATABASE database_name

Front 61

SQL CREATE TABLE Syntax

Back 61

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

Front 62

CREATE TABLE Example

Back 62

CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 63

SQL Constraints

Back 63

Constraints are used to limit the type of data that can go into a table.

Front 64

SQL Constraints main types

Back 64

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

Front 65

SQL NOT NULL Constraint

Back 65

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

Front 66

SQL NOT NULL Constraint Example

Back 66

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 67

SQL UNIQUE Constraint

Back 67

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Front 68

SQL UNIQUE Constraint on CREATE TABLE

Back 68

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

Front 69

SQL UNIQUE Constraint on ALTER TABLE

Back 69

ALTER TABLE Persons
ADD UNIQUE (P_Id)

Front 70

to DROP a UNIQUE Constraint

Back 70

To drop a UNIQUE constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP INDEX uc_PersonID

Front 71

SQL PRIMARY KEY Constraint

Back 71

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

Front 72

SQL PRIMARY KEY Constraint on CREATE TABLE

Back 72

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 73

for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax

Back 73

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Front 74

SQL PRIMARY KEY Constraint on ALTER TABLE

Back 74

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

Front 75

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax

Back 75

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Front 76

To DROP a PRIMARY KEY Constraint

Back 76

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

Front 77

SQL FOREIGN KEY Constraint

Back 77

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Front 78

SQL FOREIGN KEY Constraint on CREATE TABLE

Back 78

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

Front 79

allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL synta

Back 79

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

Front 80

SQL FOREIGN KEY Constraint on ALTER TABLE

Back 80

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Front 81

To DROP a FOREIGN KEY Constraint

Back 81

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

Front 82

SQL CHECK Constraint

Back 82

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Front 83

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.

Back 83

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 84

allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax

Back 84

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

Front 85

SQL CHECK Constraint on ALTER TABLE

Back 85

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

Front 86

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax

Back 86

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

Front 87

To DROP a CHECK Constraint

Back 87

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

Front 88

SQL DEFAULT Constraint

Back 88

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.

Front 89

SQL DEFAULT Constraint on CREATE TABLE

Back 89

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

Front 90

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE()

Back 90

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

Front 91

SQL DEFAULT Constraint on ALTER TABLE

Back 91

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

Front 92

To DROP a DEFAULT Constraint

Back 92

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

Front 93

SQL CREATE INDEX Statement

Back 93

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.

Front 94

SQL CREATE INDEX Syntax

Back 94

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name)

Front 95

SQL CREATE UNIQUE INDEX Syntax

Back 95

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Front 96

CREATE INDEX Example

Back 96

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:

CREATE INDEX PIndex
ON Persons (LastName)
If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

Front 97

create an index on a combination of columns

Back 97

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

Front 98

DROP INDEX Syntax for MS SQL Server

Back 98

DROP INDEX table_name.index_name

Front 99

The DROP TABLE Statement

Back 99

The DROP TABLE statement is used to delete a table.

DROP TABLE table_name

Front 100

The DROP DATABASE Statement

Back 100

The DROP DATABASE statement is used to delete a database.

DROP DATABASE database_name

Front 101

The TRUNCATE TABLE Statement

Back 101

What if we only want to delete the data inside the table, and not the table itself?

Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name

Front 102

SQL ALTER TABLE Statement

Back 102

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Front 103

SQL ALTER TABLE Syntax

Back 103

ALTER TABLE table_name
ADD column_name datatype

Front 104

To delete a column in a table

Back 104

ALTER TABLE table_name
DROP COLUMN column_name

Front 105

use ALTER TABLE to
change the data type of a column in a table, use the following syntax:

Back 105

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Front 106

SQL ALTER TABLE Example

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Back 106

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date

Front 107

Change Data Type Example

Back 107

Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

Front 108

DROP COLUMN Example

Back 108

Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
DROP COLUMN DateOfBirth

Front 109

SQL AUTO INCREMENT Field

Back 109

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

Front 110

AUTO INCREMENT a Field

Back 110

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

Front 111

let the AUTO_INCREMENT sequence start with another value

Back 111

ALTER TABLE Persons AUTO_INCREMENT=100

Front 112

The following SQL statement defines the "P_Id" column to be an auto-increment primary key field in the "Persons" table:

Back 112

CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 113

SQL statement above would insert a new record into the "Persons" table. The "P_Id" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

Back 113

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

Front 114

SQL statement defines the "P_Id" column to be an auto-increment primary key field in the "Persons" table

Back 114

CREATE TABLE Persons
(
P_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Front 115

SQL CREATE VIEW Statement

Back 115

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 data as if the data were coming from one single table.

Front 116

SQL CREATE VIEW Syntax

Back 116

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Front 117

SQL CREATE VIEW Examples

Back 117

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the view above as follows:

SELECT * FROM [Current Product List]

Front 118

SQL Updating a View

Back 118

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Front 119

SQL Dropping a View

Back 119

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name

Front 120

MySQL Date Functions

Back 120

Function Description
NOW() Returns the current date and time
CURDATE() Returns the current date
CURTIME() Returns the current time
DATE() Extracts the date part of a date or date/time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a specified time interval to a date
DATE_SUB() Subtracts a specified time interval from a date
DATEDIFF() Returns the number of days between two dates
DATE_FORMAT() Displays date/time data in different formats

Front 121

NOW()

Back 121

Returns the current date and time

Front 122

CURDATE()

Back 122

Returns the current date

Front 123

CURTIME()

Back 123

Returns the current time

Front 124

DATE()

Back 124

Extracts the date part of a date or date/time expression

Front 125

EXTRACT()

Back 125

Returns a single part of a date/time

Front 126

DATE_ADD()

Back 126

Adds a specified time interval to a date

Front 127

DATE_SUB()

Back 127

Subtracts a specified time interval from a date

Front 128

DATEDIFF()

Back 128

Returns the number of days between two dates

Front 129

DATE_FORMAT()

Back 129

Displays date/time data in different formats

Front 130

GETDATE()

Back 130

Returns the current date and time

Front 131

DATEPART()

Back 131

Returns a single part of a date/time

Front 132

DATEADD()

Back 132

Adds or subtracts a specified time interval from a date

Front 133

DATEDIFF()

Back 133

Returns the time between two dates

Front 134

CONVERT()

Back 134

Displays date/time data in different formats

Front 135

SQL Server Date Functionsv

Back 135

Function Description
GETDATE() Returns the current date and time
DATEPART() Returns a single part of a date/time
DATEADD() Adds or subtracts a specified time interval from a date
DATEDIFF() Returns the time between two dates
CONVERT() Displays date/time data in different formats

Front 136

MySQL comes with the following data types for storing a date or a date/time value in the database

Back 136

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MM:SS
TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
YEAR - format YYYY or YY

Front 137

SQL Server comes with the following data types for storing a date or a date/time value in the database

Back 137

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MM:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS
TIMESTAMP - format: a unique number

Front 138

OrderId ProductName OrderDate
1 Geitost 2008-11-11
2 Camembert Pierrot 2008-11-09
3 Mozzarella di Giovanni 2008-11-11
4 Mascarpone Fabioli 2008-10-29
Now we want to select the records with an OrderDate of "2008-11-11" from the table above.

We use the following SELECT statement:

Back 138

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

The result-set will look like this:

OrderId ProductName OrderDate
1 Geitost 2008-11-11
3 Mozzarella di Giovanni 2008-11-11

Front 139

SQL NULL Values

Back 139

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 as a placeholder for unknown or inapplicable values.

Front 140

SQL IS NULL
How do we select only the records with NULL values in the "Address" column?

Back 140

We will have to use the IS NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
The result-set will look like this:

LastName FirstName Address
Hansen Ola
Pettersen Kari

Front 141

SQL IS NOT NULL

How do we select only the records with no NULL values in the "Address" column?

Back 141

We will have to use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

Front 142

ISNULL() function

Back 142

MySQL does have an ISNULL() function. However, it works a little bit different from Microsoft's ISNULL() function.

Front 143

SQL Server / MS Access ISNULL()

Back 143

ISNULL() returns a zero if the value is NULL:

Front 144

NVL(), IFNULL(), and COALESCE() use

Back 144

NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result.

Front 145

Microsoft Access Data Types

Back 145

Text, Memo, Byte, Integer, Long, Single, Double, Currency, autoNumber, Date/Time, Yes/No, Ole Object, Hyperlink, Lookup Wizard

Front 146

CHAR(size)
MySQL Data Types

Back 146

Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters

Front 147

VARCHAR(size)
MySQL Data Types

Back 147

Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type

Front 148

TINYTEXT
MySQL Data Types

Back 148

Holds a string with a maximum length of 255 characters

Front 149

TEXT
MySQL Data Types

Back 149

Holds a string with a maximum length of 65,535 characters

Front 150

BLOB
MySQL Data Types

Back 150

For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data

Front 151

MEDIUMTEXT
MySQL Data Types

Back 151

For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data

Front 152

MEDIUMBLOB
MySQL Data Types

Back 152

Holds a string with a maximum length of 4,294,967,295 characters

Front 153

LONGBLOB
MySQL Data Types

Back 153

For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data

Front 154

ENUM(x,y,z,etc.)
MySQL Data Types

Back 154

Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
Note: The values are sorted in the order you enter them.

You enter the possible values in this format: ENUM('X','Y','Z')

Front 155

SET
MySQL Data Types

Back 155

Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Front 156

TINYINT(size)
MySQL Data Types

Back 156

-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 157

SMALLINT(size)
MySQL Data Types

Back 157

-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 158

MEDIUMINT(size)
MySQL Data Types

Back 158

-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 159

INT(size)
MySQL Data Types

Back 159

-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 160

BIGINT(size)
MySQL Data Types

Back 160

-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis

Front 161

FLOAT(size,d)
MySQL Data Types

Back 161

A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Front 162

DOUBLE(size,d)
MySQL Data Types

Back 162

A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Front 163

DECIMAL(size,d)
MySQL Data Types

Back 163

A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Front 164

DATE()
MySQL Data Types

Back 164

A date. Format: YYYY-MM-DD
Note: The supported range is from '1000-01-01' to '9999-12-31'

Front 165

DATETIME()
MySQL Data Types

Back 165

*A date and time combination. Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

Front 166

TIMESTAMP()
MySQL Data Types

Back 166

*A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC

Front 167

TIME()
MySQL Data Types

Back 167

A time. Format: HH:MM:SS
Note: The supported range is from '-838:59:59' to '838:59:59'

Front 168

YEAR()
MySQL Data Types

Back 168

A year in two-digit or four-digit format.
Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

Front 169

char(n)
SQL Server Data Types

Back 169

Fixed-length character string. Maximum 8,000 characters

Front 170

char(n)
SQL Server Data Types

Back 170

Variable-length character string. Maximum 8,000 characters

Front 171

varchar(max)
SQL Server Data Types

Back 171

Variable-length character string. Maximum 1,073,741,824 characters

Front 172

text
SQL Server Data Types

Back 172

Variable-length character string. Maximum 2GB of text data

Front 173

nchar(n)
SQL Server Data Types

Back 173

Fixed-length Unicode data. Maximum 4,000 characters

Front 174

nvarchar(n)
SQL Server Data Types

Back 174

Variable-length Unicode data. Maximum 4,000 characters

Front 175

nvarchar(max)
SQL Server Data Types

Back 175

Variable-length Unicode data. Maximum 536,870,912 characters

Front 176

ntext
SQL Server Data Types

Back 176

Variable-length Unicode data. Maximum 2GB of text data

Front 177

bit
SQL Server Data Types

Back 177

Allows 0, 1, or NULL

Front 178

binary(n)
SQL Server Data Types

Back 178

Fixed-length binary data. Maximum 8,000 bytes

Front 179

varbinary(n)
SQL Server Data Types

Back 179

Variable-length binary data. Maximum 8,000 bytes

Front 180

varbinary(max)
SQL Server Data Types

Back 180

Variable-length binary data. Maximum 2GB

Front 181

image
SQL Server Data Types

Back 181

Variable-length binary data. Maximum 2GB

Front 182

tinyint
SQL Server Data Types

Back 182

Allows whole numbers from 0 to 255

Front 183

smallint
SQL Server Data Types

Back 183

Allows whole numbers between -32,768 and 32,767

Front 184

int
SQL Server Data Types

Back 184

Allows whole numbers between -2,147,483,648 and 2,147,483,647

Front 185

bigint
SQL Server Data Types

Back 185

Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

Front 186

decimal(p,s)
SQL Server Data Types

Back 186

Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

Front 187

numeric(p,s)
SQL Server Data Types

Back 187

Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

Front 188

smallmoney
SQL Server Data Types

Back 188

Monetary data from -214,748.3648 to 214,748.3647

Front 189

money
SQL Server Data Types

Back 189

Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Front 190

float(n)
SQL Server Data Types

Back 190

Floating precision number data from -1.79E + 308 to 1.79E + 308.
The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.

Front 191

real
SQL Server Data Types

Back 191

Floating precision number data from -3.40E + 38 to 3.40E + 38

Front 192

datetime
SQL Server Data Types

Back 192

From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds

Front 193

datetime2
SQL Server Data Types

Back 193

From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds

Front 194

smalldatetime
SQL Server Data Types

Back 194

From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute

Front 195

date
SQL Server Data Types

Back 195

Store a date only. From January 1, 0001 to December 31, 9999

Front 196

time
SQL Server Data Types

Back 196

Store a time only to an accuracy of 100 nanoseconds

Front 197

datetimeoffset
SQL Server Data Types

Back 197

The same as datetime2 with the addition of a time zone offset

Front 198

timestamp
SQL Server Data Types

Back 198

Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Front 199

sql_variant
SQL Server Data Types

Back 199

Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp

Front 200

uniqueidentifier
SQL Server Data Types

Back 200

Stores a globally unique identifier (GUID)

Front 201

xml
SQL Server Data Types

Back 201

Stores XML formatted data. Maximum 2GB

Front 202

cursor
SQL Server Data Types

Back 202

Stores a reference to a cursor used for database operations

Front 203

table
SQL Server Data Types

Back 203

Stores a result-set for later processing

Front 204

SQL Aggregate Functions

Back 204

SQL aggregate functions return a single value, calculated from values in a column.

Front 205

SQL Scalar functions

Back 205

SQL scalar functions return a single value, based on the input value.

Front 206

AVG()
COUNT()
FIRST()
LAST()
MAX()
MIN()
SUM()

Back 206

* 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

Front 207

UCASE()
LCASE()
MID()
LEN()
ROUND()
NOW()
FORMAT()

Back 207

* UCASE() - Converts a field to upper case
* LCASE() - Converts a field to lower case
* MID() - Extract characters from a text field
* LEN() - Returns the length of a text field
* ROUND() - Rounds a numeric field to the number of decimals specified
* NOW() - Returns the current system date and time
* FORMAT() - Formats how a field is to be displayed

Front 208

AVG() function returns the average value of a numeric column.
SQL AVG() Syntax

Back 208

SELECT AVG(column_name) FROM table_name

Front 209

SQL AVG() Example

Back 209

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

Front 210

find the customers that have an OrderPrice value higher than the average OrderPrice value

Back 210

SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

Front 211

SQL COUNT(column_name) Syntax

Back 211

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name

Front 212

SQL COUNT(*) Syntax

Back 212

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name

Front 213

SQL COUNT(DISTINCT column_name) Syntax

Back 213

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name

Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

Front 214

SQL COUNT(column_name) Example

Back 214

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Now we want to count the number of orders from "Customer Nilsen".

We use the following SQL statement:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'

The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:
CustomerNilsen
2

Front 215

SQL COUNT(*) Example

Back 215

we omit the WHERE clause, like this:
SELECT COUNT(*) AS NumberOfOrders FROM Orders

Front 216

SQL COUNT(DISTINCT column_name) Example

Back 216

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

Front 217

SQL FIRST() Function

Back 217

SQL FIRST() Syntax
SELECT FIRST(column_name) FROM table_name

Front 218

SQL LAST() Function

Back 218

SQL LAST() Syntax
SELECT LAST(column_name) FROM table_name

Front 219

SQL LAST() Example

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Back 219

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

Note Tip: Workaround if LAST() function is not supported:
SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

The result-set will look like this:
LastOrderPrice
100

Front 220

SQL MAX() Function

Back 220

The MAX() function returns the largest value of the selected column.
SQL MAX() Syntax
SELECT MAX(column_name) FROM table_name

Front 221

SQL MIN() Function

Back 221

The MIN() function returns the smallest value of the selected column.
SQL MIN() Syntax
SELECT MIN(column_name) FROM table_name

Front 222

SQL SUM() Function

Back 222

The SUM() function returns the total sum of a numeric column.
SQL SUM() Syntax
SELECT SUM(column_name) FROM table_name

Front 223

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

Back 223

OrderTotal
5700

Front 224

The GROUP BY Statement

Back 224

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Front 225

SQL GROUP BY Syntax

Back 225

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

Front 226

GROUP BY More Than One Column

Back 226

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

Front 227

SQL HAVING Clause

Back 227

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Front 228

SQL HAVING Syntax

Back 228

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Front 229

SQL HAVING Example

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Back 229

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

The result-set will look like this:
Customer SUM(OrderPrice)
Nilsen 1700

Front 230

ow we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.

We add an ordinary WHERE clause to the SQL statement:

Back 230

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

The result-set will look like this:
Customer SUM(OrderPrice)
Hansen 2000
Jensen 2000

Front 231

SQL UCASE() Function

Back 231

The UCASE() function converts the value of a field to uppercase.
SQL UCASE() Syntax
SELECT UCASE(column_name) FROM table_name
Syntax for SQL Server
SELECT UPPER(column_name) FROM table_name

Front 232

SQL LCASE() Function

Back 232

The LCASE() function converts the value of a field to lowercase.
SQL LCASE() Syntax
SELECT LCASE(column_name) FROM table_name
Syntax for SQL Server
SELECT LOWER(column_name) FROM table_name

Front 233

The MID() Function

Back 233

The MID() function is used to extract characters from a text field.
SQL MID() Syntax
SELECT MID(column_name,start[,length]) FROM table_name

Parameter Description
column_name Required. The field to extract characters from
start Required. Specifies the starting position (starts at 1)
length Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text

Front 234

SQL MID() Example

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Now we want to extract the first four characters of the "City" column above.

We use the following SELECT statement:

Back 234

SELECT MID(City,1,4) as SmallCity FROM Persons

The result-set will look like this:
SmallCity
Sand
Sand
Stav

Front 235

SQL LEN() Function

Back 235

The LEN() Function

The LEN() function returns the length of the value in a text field.
SQL LEN() Syntax
SELECT LEN(column_name) FROM table_name

Front 236

SQL ROUND() Function

Back 236

The ROUND() Function

The ROUND() function is used to round a numeric field to the number of decimals specified.
SQL ROUND() Syntax
SELECT ROUND(column_name,decimals) FROM table_name

Parameter Description
column_name Required. The field to round.
decimals Required. Specifies the number of decimals to be returned

Front 237

SQL NOW() Function

Back 237

The NOW() Function

The NOW() function returns the current system date and time.
SQL NOW() Syntax
SELECT NOW() FROM table_name

Front 238

SQL FORMAT() Function

Back 238

The FORMAT() function is used to format how a field is to be displayed.
SQL FORMAT() Syntax
SELECT FORMAT(column_name,format) FROM table_name

Parameter Description
column_name Required. The field to be formatted.
format Required. Specifies the format.

Front 239

SQL FORMAT() Example

We have the following "Products" table:
Prod_Id ProductName Unit UnitPrice
1 Jarlsberg 1000 g 10.45
2 Mascarpone 1000 g 32.56
3 Gorgonzola 1000 g 15.67

Now we want to display the products and prices per today's date (with today's date displayed in the following format "YYYY-MM-DD").

We use the following SELECT statement:

Back 239

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products

The result-set will look like this:
ProductName UnitPrice PerDate
Jarlsberg 10.45 2008-10-07
Mascarpone 32.56 2008-10-07
Gorgonzola 15.67 2008-10-07

Front 240

sp_who

Back 240

shows everybody that is runnign a request on the SQL server

Front 241

use of DMV

Back 241

SELECT * FROM sys.dm_exec_sessions
SELECT * FROM sys.dm_exec_requests

Front 242

what is DMV

Back 242

dynamic management views

Front 243

DBCC

Back 243

Transact-SQL programming language provides DBCC statements that act as Database Console Commands for Microsoft® SQL Server™ 2000. These statements check the physical and logical consistency of a database. Many DBCC statements can fix detected problems

Front 244

sys.dm_clr_loaded_assemblies

Back 244

Assemblies in available in SQL Server

Front 245

sys.dm_db_file_space_usage

Back 245

Database file usage to determine if databases are getting low on space and need immediate attention

Front 246

sys.dm_exec_cached_plans

Back 246

Cached query plans available to SQL Server

Front 247

sys.dm_exec_sessions

Back 247

Sessions in SQL Server

Front 248

sys.dm_exec_connections

Back 248

Connections to SQL Server

Front 249

sys.dm_db_index_usage_stats

Back 249

Seeks, scans, lookups per index

Front 250

sys.dm_io_virtual_file_stats

Back 250

IO statistics for databases and log files

Front 251

sys.dm_broker_connections

Back 251

Service Broker connections to the network

Front 252

sys.dm_os_memory_objects

Back 252

SQL Server memory usage

Front 253

sys.dm_tran_active_transactions

Back 253

Transaction state for an instance of SQL Server

Front 254

list of all db query

Back 254

SELECT DISTINCT DB_NAME(database_id)
FROM sys.master_files