What is Normalization?
Normalization is the process of reefing tables, keys, columns, and
relationships to create a consistent database design. Normalization is
achieved by applying a number of tests to tables. Three levels of
normalization (First, Second and Third normal Form) are comment only
applied, although others are defined.
1) First Normalization (Separate repeated items)
2) Second Normalization (Separate not depending on main key)
3) Third Normalization (Remove Calculate items)
The steps of Normalization
Example:
Un-Normalized Form
(1) Entity : ALL
: Invno, Date, Customer, CustomerName, {StockCode, Name, Unit,
Quantity, Price, Amount}
First Normalized
(1.1) Entity : Sale
: InvoiceNo, Date , Customer , name
(1.2) Entity : SaleItem
: InvoiceNo, StockCode, CustomerName , Unit , Quantity,
Price , Amount
Page 1
Second Normalized
(1.1.1 ) Entity : Sale
: InvoiceNo, Date , Customer
(1.1.2) Entity : Customer
: Customer , CustomerName
(1.2.1) Entity : SaleItem
: InvoiceNo, StockCode , Quantity
(1.2.2) Entity : Stock
: StockCode , StockName , Unit , Price , Amount
Third Normalized
(1.1.1 ) Entity : Sale
: InvoiceNo, Date , Customer
(1.1.2) Entity : Customer
: Customer , name
(1.2.1) Entity : SaleItem
: InvoiceNo, StockCode , Quantity
(1.2.2) Entity : Stock
: StockCode , StockName , Unit , Price
Exercise
Global company wants to computerize and they have many problem and
weakness in manual. The following purchase form is used in manual
normalized the purchase form for storage the best condition.
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL
values.
A table can have only one primary key, which may consist of single or
multiple fields.
SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the
"Customer" table is created:
CREATE TABLE Customer (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
Page 2
Sample "Customer" Table
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo Avda. de la
Mexico
2 Emparedados y Ana Trujillo Constitución 05021 Mexico
D.F.
helados 2222
Antonio Moreno Antonio Mexico
3 Mataderos 2312 05023 Mexico
Taquería Moreno D.F.
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
Christina
5 Berglunds snabbköp Berguvsvägen 8 Lulea S-958 22 Sweden
Berglund
SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an
existing table.
The ALTER TABLE statement is also used to add and drop various constraints
on an existing table.
ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an "Email" column to the "Customers" table:
Example
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE - Drop Column
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - MODIFY Column
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
Page 3
INSERT INTO SELECT Syntax
Copy all columns from one table to another table:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
Example
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
INSERT *INTO newCustomers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
The SQL CASE Statement
The CASE statement goes through conditions and return a value when the
first condition is met (like an IF-THEN-ELSE statement). So, once a
condition is true, it will stop reading and return the result. If no
conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
Example
SELECT SaleID, Quantity,
CASE
WHEN Quantity > 30 THEN "The quantity is greater than 30"
WHEN Quantity = 30 THEN "The quantity is 30"
ELSE "The quantity is under 30"
END AS QuantityText
FROM SaleItem;
What is a Stored Procedure?
A stored procedure is a prepared SQL code that you can save, so the
code can be reused over and over again.
Page 4
So if you have an SQL query that you write over and over again, save
it as a stored procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure, so that the stored
procedure can act based on the parameter value(s) that is passed.
Stored Procedure Syntax
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
Execute a Stored Procedure
EXEC procedure_name;
Example
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers City = "London";
Example
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode
nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";
Page 5
PIVOT Example
Create DailyIncome
VendorId IncomeDay IncomeAmount
---------- ---------- ------------
SPIKE FRI 100
SPIKE MON 300
FREDS SUN 400
SPIKE WED 500
SPIKE TUE 200
JOHNS WED 900
SPIKE FRI 100
JOHNS MON 300
SPIKE SUN 400
SPIKE WED 500
FREDS THU 800
JOHNS TUE 600
A lot of data that it is hard to make something useful of, for example,
say that we would like to know what the average income is for each vendor
id?
Or what the maximum income is for each day for a particular vendor? Enter
the pivot table.
To find the average for each vendor, run this query:
select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],
[SAT],[SUN])) as AvgIncomePerDay
Outcome:
VendorId MON TUE WED THU FRI SAT
SUN
---------- -------- ----------- ----------- ----------- ----------- -----------
---------
FREDS 500 350 500 800 900 500
400
JOHNS 300 600 900 800 300 800
600
SPIKE 600 150 500 300 200 100
400
Page 6
Create Classes
Day Period Subject
Mon 1 Ch
Mon 2 Ph
Mon 3 Mth
Mon 4 CS
Mon 5 Lab1
Mon 6 Lab2
Mon 7 Lab3
Tue 1 Ph
Tue 2 Ele
Tue 3 Hu
Tue 4 Ph
Tue 5 En
Tue 6 CS2
Tue 7 Mth
I would like it displayed as follows: Kind of crosstab or Pivot
Day P1 P2 P3 P4 P5 P6 P7
Mon Ch Ph Mth CS2 Lab1 Lab2 Lab3
Tue Ph Ele Hu Ph En CS2 Mth
Example
SELECT
dy,
MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,
MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,
MAX(CASE WHEN period = 3 THEN subj ELSE NULL END) AS P3,
MAX(CASE WHEN period = 4 THEN subj ELSE NULL END) AS P4,
MAX(CASE WHEN period = 5 THEN subj ELSE NULL END) AS P5,
MAX(CASE WHEN period = 6 THEN subj ELSE NULL END) AS P6,
MAX(CASE WHEN period = 7 THEN subj ELSE NULL END) AS P7
FROM
Classes
GROUP BY
dy
ORDER BY
CASE dy
WHEN 'Mon' THEN 1
WHEN 'Tue' THEN 2
WHEN 'Wed' THEN 3
WHEN 'Thu' THEN 4
WHEN 'Fri' THEN 5
WHEN 'Sat' THEN 6
WHEN 'Sun' THEN 7
ELSE 8
END
Page 7
Example
SELECT Day, [1] AS P1, [2] AS P2,[3] AS P3, [4] AS P4, [5] AS P5,[6] AS
P6,[7] AS P7 FROM ExampleData PIVOT (Max(Subject) FOR Period IN ([1],
[2],[3],[4],
[5], [6], [7])) AS PivotTable;
Question
1. Select all the different values from the Country column in the Customers table.
2. Select all records where the City column has the value "Berlin".
3. Use the NOT keyword to select all records where City is NOT "Berlin".
4. Select all records where the CustomerID column has the value 32.
5. Select all records where the City column has the value
Berlin' and the PostalCode column has the value 12209.
6. Select all records where the City column has the value 'Berlin', and also the
records where the City column has the value 'London'.
7. Select all records from the Customers where the PostalCode column is empty.
8. Select all records from the Customers where the PostalCode column is NOT
empty.
9. Use the MIN function to select the record from the Products with the smallest
value of the Price column.
10. Use an SQL function to select the record with the highest value of
the Price column.
11. Use the correct function to return the numbers of records that have
the Price value set to 18.
12. Select all records where the value of the City column starts with letter "a" and
ends with the letter "b".
13. Select all records where the value of the City column does NOT start with the
letter "a".
14. Select all records where the second letter of the City is an "a".
15. Select all records where the first letter of the City is an "a" or a "b" or a "c".
Page 8
16. Select all records where the value of the City column does NOT start with the
letter "a".
17. Use the BETWEEN operator to select all the records where the value of
the Price column is NOT between 10 and 20.
18. When displaying the Customers table, make an ALIAS of
the PostalCode column, the column should be called Pno instead.
19. List the number of customers in each country, ordered by the country with the
most customers first.
20. Add a column of type DATE called Birthday.
21. Delete the column Birthday from the Persons table.
22. Find the max income for each day for vendor SPIKE, using PIVOT.
Answer
1. SELECT DISTINCT Country FROM Customers;
2. SELECT * FROM Customer WHERE City= 'Berlin';
3. SELECT * FROM Customers WHERE NOT City= 'Berlin';
4. SELECT * FROM Customers WHERE CustomerID=32;
5. SELECT * FROM Customers WHERE City= 'Berlin' AND
PostalCode=12209;
6. SELECT * FROM Customers WHERE City= 'Berlin' OR City=
'London';
7. SELECT * FROM CUSTOMER WHERE PostalCode IS NULL;
8. SELECT * FROM CUSTOMER WHERE PostalCode IS NOT NULL;
9. SELECT MIN(Price) FROM Products;
10. SELECT MIN(Price) FROM Products;
11. SELECT COUNT(*) FROM Products WHERE Price=18;
12. SELECT * FROM Customers WHERE City LIKE 'a%b';
13. SELECT * FROM Customers WHERE City NOT LIKE 'a%';
14. SELECT * FROM Customers WHERE City LIKE '_a%';
15. SELECT * FROM Customers WHERE City LIKE '[abc]%';
16. SELECT * FROM Customers WHERE City NOT LIKE 'a%';
Page 9
17. SELECT * FROM Products WHERE Price NOT BETWEEN 10; AND
20;
18. SELECT CustomerName, Address, PostalCode FROM
Customers;
19. SELECT COUNT(CustomerID), Country FROM Customers GROUP
BY Country ORDER BY COUNT(CustomerID) DESC;
20. ALTER TABLE Persons ADD Birthday DATE;
21. ALTER TABLE Persons DROP COLUMN Birthday;
22. SELECT * from DailyIncome
pivot (max (IncomeAmount) for IncomeDay in ([MON],
[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerD
ay where VendorId in ('SPIKE');
Page
10