100 Days of Data Engineering Day 4

partitionby, CTE, stored procedures

The video is about SQL Partition By clause compared to Group By.

Here are the key points:

• Partition By divides the result set into partitions based on a specified column, while Group By reduces the number of rows by grouping them.
• Partition By allows you to use additional columns that wouldn’t be possible with Group By in the same query.
• The video demonstrates the syntax for Partition By and Group By with an example using employee data.
• Partition By is placed within the Select statement and allows you to perform aggregate functions on a specific partition.

In summary, the video explains the concept of Partition By in SQL and highlights its advantages over Group By in certain scenarios.

---

Here is a summary of the video Common Table Expressions (CTEs) in SQL by Bard:

The video is about common table expressions (CTEs) in SQL.

A CTE is a temporary named result set that is used to manipulate complex subquery data. It exists only within the scope of the statement and disappears once the statement is executed. CTEs are created in memory and not stored in a temp database like temp tables.

The video provides a step-by-step tutorial on how to create a CTE. Here are the steps:

1. Use the WITH clause followed by the CTE name and AS.
2. Write a select statement within parentheses to define the CTE.
3. After the CTE definition, use a regular SELECT statement to query the data from the CTE.

The video also highlights some important points about CTEs:

• CTEs are not stored anywhere and need to be defined every time they are used in a query.
• The SELECT statement referencing the CTE must be written immediately after the CTE definition.

Overall, CTEs are a powerful way to simplify complex queries by breaking them down into smaller, more manageable pieces.

---

Here is a summary of the video about stored procedures in SQL:

Stored procedures are a group of SQL statements that can be created and stored in a database. They can accept input parameters and can be used by multiple users. Stored procedures can reduce network traffic and increase performance.

The video starts with a very simple stored procedure that selects all the data from the employee demographics table. The video then shows how to create a more complicated stored procedure that inserts data into a temporary table and then selects all the data from that table.

The video then goes over how to modify a stored procedure to include a parameter. A parameter allows you to specify an input value when you execute the stored procedure. This can be useful for filtering data or getting specific results. The video ends by showing how to execute a stored procedure with a parameter.