Data ware house

Sample database diagram

AdventureWork2008R2 Database Schema

Common terms

Pages

• fundamental unit of data storage in SQL Server is the page
• The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.

Extents
Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.

SQL Server - Group By Example

Common rule:

---

1. columns without aggregate function, must have to add into GROUP BY clause
2. to filter by aggregate value, have to use having & agg. columns based on requirements

Derived column / Calculated Column:

---

The Derived Column transformation creates new column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input.

SQL Server code:

---

select sh.TerritoryID

   ,sh.CustomerID

   ,sh.DueDate

   ,sum(sh.TaxAmt) totalTax

   ,sum(sh.SubTotal) totalSales

from Sales.SalesOrderHeader sh

where sh.TerritoryID in (4,5,6) and

  YEAR(sh.DueDate)='2006' and   MONTH(sh.DueDate) = '6' and

  datepart(YY, DueDate) = '2006' and --datepart

  DATEDIFF(mm, '2006-06-01', DueDate) = 0

group by sh.TerritoryID, sh.CustomerID, sh.DueDate

having sum(sh.SubTotal) > 10000 and

  sum(sh.SubTotal) > 4000  --alias doesn't support

--order by sh.SubTotal desc, sh.TaxAmt desc

order by 4 desc, 3 desc

Table:

---

SQL Server - Subquery

Tips:

---

All tasks of sub-query can be replaced with SQL server JOIN. So, it is recommend to use JOIN. But learn sub-query for understanding.

Careful:

---

• does not support ORDER BY
• You can’t use BETWEEN in the main query. But sub-query allows BETWEEN.
• sub-query is a filter. so, inside sub-query, you can’t use more than one column

SQL Server code:

---

-- find the personal details using the phone number

select BusinessEntityID

from   Person.PersonPhone

where  PhoneNumber='612-555-0100'

select FirstName, LastName from Person.Person

where BusinessEntityID in (4, 2115, 2167)

select FirstName, LastName

from Person.Person

where BusinessEntityID in (

               select BusinessEntityID

               from Person.PersonPhone

               where PhoneNumber='612-555-0100'

               )