Class Eighteen: Text (PARTITION BY AND TEMPORARY TABLE)

SELECT column_name, aggregate_function(column_name) OVER (PARTITION BY column_name)

FROM table_name;

Example of ‘PARTITION BY’

1). SELECT CookieName,Quantity,RevenuePerCookie,

Count(CookieName) OVER (PARTITION BY cookieName) AS TotalCookie

FROM [dbo].[Product]

JOIN [dbo].[Order_Product]

ON [dbo].[Order_Product].CookieID=[dbo].[Product].CookieID

2). SELECT CustomerName,OrderTotal,

Count(CustomerName) OVER (PARTITION BY CustomerName) As TotalCustomerName

FROM [dbo].[Customers]

JOIN [dbo].[Orders]

ON [dbo].[Orders].CustomerID=[dbo].[Customers].CustomerID

Difference between Partition by and group by

SELECT CookieName,Quantity,RevenuePerCookie,Count(CookieName)

FROM [dbo].[Product]

JOIN [dbo].[Order_Product]

ON [dbo].[Order_Product].CookieID=[dbo].[Product].CookieID

GROUP BY CookieName,Quantity,RevenuePerCookie.

TEMP TABLE

A temporary table is a base table that is not stored in the database but instead exists only while the database session in which it was created is active. At first glance, this may sound like a view, but views and temporary tables are rather different: ▪ A view exists only for a single query.

A temporary SQL table, also known as a temp table, is a table that is created and used within the context of a specific session or transaction in a database management system. It is designed to store temporary data that is needed for a short duration and does not require a permanent storage solution.

What is difference between temp table and view in SQL?

Similar to Temp Tables, Views do not store the data itself but instead presents the result of a query as if it were a table. A very important distinction between Temp Tables and Views is that typically the creation of Views is limited to the Database Administrators.

EXAMPLE

CREATE TABLE EmployeeSalary(

EmployeeID                int,

JobTitle                     varchar(50),

Salary                           int

)

SELECT *

FROM EmployeeSalary

INSERT INTO EmployeeSalary

VALUES(

‘1009’, ‘Salesman’, ‘31000’)

Temp table

CREATE TABLE #tem_Empioyee(

EmplyeeID     int,

JobTitle         varchar(50),

Salary                int)

SELECT *

FROM #tem_Empioyee

INSERT INTO #tem_Empioyee

VALUES(

‘1001’, ‘HR’, ‘80000’

)

Used the existing table information from employee salary to fill into #tem_Employee table

INSERT INTO #tem_Empioyee

SELECT *

FROM EmployeeSalary

VIEWS

Views are another type of virtual table that can be used in SQL. Views are similar to CTEs, but there are some important differences. First, views can be used by multiple users at the same time. Second, views can be used to restrict access to the underlying data, so only certain users can see certain information. And third, views can be used to perform operations like joins and aggregation.

EXAMPLE

CREATE VIEW

vw_Customers_and_Orders AS SELECT

C.CustomerName, Phone,

c.address, c.city, O.OrderDate, O.OrderTotal

FROM [dbo].[Customers]C

JOIN [dbo].[Orders]O 

ON O.CustomerID=C.CustomerID

SELECT *

FROM vw_Customers_and_Orders