Data Warehousing

and Business analytics

Review of SQL server

 Data Types in SQL Server (1)

String, text data types:

Data type Explication

CHAR(n) Fixed-length string of up to n characters (n<=8000)

(‘foo’ string will be stored as ‘foo ’ for an attribute of CHAR(5))
VARCHAR(n) Variable length string of up to n characters (n<=8000)
Text Variable length string of up to 2GB text data
NCHAR(n) Fixed-length Unicode string of up to n characters (n<=4000)
NVARCHAR(n) Variable length Unicode string of up to n characters (n<=4000)
Ntext Variable length Unicode string of up to 2GB text data
 Data Types in SQL Server (2)

Date, time data types:

Data type Explication

Datetime From January 1, 1753 to December 31, 9999

(Format: YYYY-MM-DD hh:mm:ss[.nnn])
Datetime2 From January 1, 0001 to December 31, 9999
(Format: YYYY-MM-DD hh:mm:ss[.nnnnnnn])
smalldatetime From January 1, 1900 to June 6, 2079
(Format: YYYY-MM-DD hh:mm:ss)
Date Store a date only. From January 1, 0001 to December 31, 9999
(Format: YYYY-MM-DD)
Time Store a time only
(Format: hh:mm:ss[.nnnnnnn])

E.g.: ‘2018-12-24’, ‘24 December 2018’, ‘12/24/2018’, ‘20181224’,

‘2018/12/24’ are different ways to represent the date 24 December 2018.
 Data Types in SQL Server (3)

Number data types:

Data type Explication
Integer that can be 0, 1, or NULL, could be used as boolean data
bit
(0 (FALSE), 1 (TRUE))
tinyint Allows whole numbers from 0 to 255 (1 byte)
smallint Allows whole numbers between -32,768 and 32,767 (2 bytes)
Allows whole numbers between -2,147,483,648 and
int
2,147,483,647 (4 bytes)
Allows whole numbers between -9,223,372,036,854,775,808
bigint
and 9,223,372,036,854,775,807 (8 bytes)
float Floating precision number data from -1.79E + 308 to 1.79E +
308.
real Floating precision number data from -3.40E + 38 to 3.40E + 38
Decimal(p,s) or Fixed precision and scale number from -10^38 +1 to 10^38 –1 (p
Numeric(p,s) decimal digits and maximum s digits stored to the right of the
decimal point)
 Create/Modify and Delete Database

Syntax:
CREATE DATABASE database_name
ALTER DATABASE database_name
Modify Name = new_database_name
DROP DATABASE database_name
 Simple Table Declarations

Use ‘CREATE TABLE’ followed by the name of the relation

and a parenthesized, comma-separated list of the attribute
names and their types:

[Use Database_Name]
CREATE TABLE TableName(
Attribute1 datatype [options],
Attribute2 datatype [options],
...
AttributeN datatype [options]
)
 Example

CREATE TABLE Movies(

title char(100),
year int,
length int,
genre char(20),
studioName char(30),
starName char(30)
)
 Modifying Relation Schemas

Delete table:
[USE Database_Name]
DROP TABLE TableName;
Add attribute:
[USE Database_Name]
ALTER TABLE TableName
ADD
Attribute1 Datatype [Options],
Attribute2 Datatype [Options],
…;
 Modifying Relation Schemas

Modify attribute:
[USE Database_Name]
ALTER TABLE TableName
ALTER COLUMN Attribute Datatype [Options];
Delete attribute:
[USE Database_Name]
ALTER TABLE TableName
DROP COLUMN Attribute1, Attribute2,…;
 Declaring Keys

 PRIMARY KEY:
[USE DatabaseName]
CREATE TABLE Table_Name(
Attribute1_Name Datatype PRIMARY KEY,
Attrbute2_Name DataType [OPTIONS],
…
);
 Or
[USE DatabaseName]
CREATE TABLE Table_Name(
Attribute1_Name Datatype [OPTIONS],
…
PRIMARY KEY(AttributeKey1, AttributeKey2,…)
);
 Or
[USE DatabaseName]
ALTER TABLE Table_Name ADD PRIMARY KEY(AttributeKey1, AttributeKey2,…);
Declaring Keys

UNIQUE KEY:
[USE DatabaseName]
CREATE TABLE Table_Name(
Attribute1_Name Datatype [OPTIONS],
Attrbute2_Name DataType [OPTIONS] UNIQUE, …);
 Or
[USE DatabaseName]
CREATE TABLE Table_Name(
Attribute1_Name Datatype [OPTIONS],
Attrbute2_Name DataType [OPTIONS],
…
UNIQUE(UniqueAttribute1, UniqueAttributeKey2,…)
);
 Or
[USE DatabaseName]
ALTER TABLE Table_Name ADD UNIQUE(UniqueAttribute1,
UniqueAttributeKey2,…));
 Identity option

IDENTITY:
IDENTITY (seed_value, increment_value);
 Seed_value: initial value
 Increment_value: increment step

CREATE TABLE Students(

ID int IDENTITY(1,1),
Name nvarchar(30)
)
DML – Data Manipulation Language
1 – Insertion with INSERT command
Example: Insert command
 UPDATE data in table

UPDATE:
UPDATE Table_Name
SET AttributeName = value,
[,…, Attribute_k= value_k]
[FROM ...]
[WHERE Conditions]
Example: Update command
 DELETE data in table

DELETE:
DELETE From Table_Name
[FROM ...]
[WHERE Conditions]
 Example: Delete command

Use PRODUCTS table

Delete the tuples with the price less than 20$

 Basic Syntax for a simple SELECT queries

SELECT [ ALL | DISTINCT ]

[ TOP n [ PERCENT ] ]
* | {column_name | expression [alias],…}
FROM tableName
[WHERE conditional expressions]

 SELECT identifies which attributes are produced as part of the answer

 ALL: Specifies that duplicate rows can appear in the result set. ALL is the default
 DISTINCT: Specifies that only unique rows can appear in the result set. Null values
are considered equal for the purposes of the DISTINCT keyword
 TOP n [ PERCENT ]:Specifies that only the first n rows are to be output from the
query result set. n is an integer between 0 and 4294967295. If PERCENT is also
specified, only the first n percent of the rows are output from the result set.

When specified with PERCENT, n must be an integer between 0 and 100

 FROM identifies which table
 WHERE identifies the conditions for filtering the results, like selection-condition
in relational algebra
 Example: SELECT all columns

 Create the table and perform this statement

 Projection in SQL

We can, if we wish, eliminate some of the components of the

chosen tuples; that is, we can project the relation produced by a
SQL query onto some of its attributes
In place of the * of the SELECT clause, we may list some of the
attributes of the relation mentioned in the FROM clause. The
result will be projected onto the attributes listed

SELECT ColumnName1, ColumnName2, …

FROM tableName
 Example: Projection in SQL

 Apply this statement on your table

 Projection in SQL: Renaming Column headers

We could rename a column header in the result by using

column alias
Follow the name of attribute by the keyword as and an
alias.

SELECT ColumnName1 as Alias1, ColumnName2 as Alias 2, …

FROM tableName
 Example: Renaming column headers

select ProductCode, Product

Original table as "Product's Name"
from Products

 The example displays the “Name” as “Product’s Name”

 Because “Product’s Name” contain a space, it has been
enclosed in double quotation marks.
 Example: Select statement

 Show the information of INVOICEs that have the amount more than
300$
Queries involving more than one relation
 Capabilities of SQL SELECT Statements

 Projection: You can use the projection capability in SQL to choose

the columns in a table that you want returned by your query (with
SELECT clause).
 Selection: You can use the selection capability in SQL to choose
the rows in a table that you want returned by a query (with WHERE
clause)
 Joining: You can use the join capability in SQL to bring together
data that is stored in different tables by creating a link between
them.
 JOIN in SQL

To display data from two or more related tables:

 List each relation in the FROM clause

 Write a simple join condition in the WHERE clause.

To join n tables together, you need a minimum of n-1 join

conditions.
JOINs in SQL
 INNER JOIN in SQL

SELECT *
FROM Customer A, Invoice B
WHERE A.CUST_NUM = B. CUST_NUM

SELECT *
FROM Customer A INNER JOIN Invoice B
on A. CUST_NUM = B. CUST_NUM
SET operations
Example: UNION
 INTERSECT

{A ∩ B} = {a | a is in A and B}
 Difference

SELECT employee_id, job_id FROM employees

EXCEPT
SELECT employee_id, job_id FROM job_history

SQL Server

Oracle
 UNION & UNION ALL

The UNION operator eliminates any

duplicated rows.
 UNION & UNION ALL

But UNION ALL still returns duplicated

rows
 Set operations

STUDENT1 STUDENT2

Create two tables STUDENT1, STUDENT2 and

perform the set operations (UNION, UNION ALL ,
INTERSECT, EXCEPT) on two these tables.
AGGREGATION operators, TRANSACTIONs,
STORED PROCEDUREs, TRIGGERs, VIEWs,
HAVING, GROUP BY, …