Advanced SQL Queries in Oracle and SQL Server

Analytic Functions - Part 2
Hello and welcome back to Advance SQL. My name is Scott Hecht, and in this module, I'd like to continue our discussion of Analytic Functions and concentrate on the order by clause. Let's go over the module contents first though. We'll talk about the Data used in the Module. We'll dive into the ORDER BY Clause, and then we'll end with a summary. Let's talk about the data used in the module. We have a table called CHILDSTAT, which contains the following columns; FIRSTNAME, which is the child's first name, GENDER, which is the child's gender, M for male, F for female. We have the child's BIRTHDATE, which is the date, dated type in Oracle, or small day time in SQL server. A height of the child in inches and the weight of the child in pounds, and this is what the data looks like.

Analytic Functions - Part 3
Hello, welcome back to Advanced SQL. My name is Scott Hecht, and in this module I'd like to continue our discussion of analytic functions, specifically talking about the Window Clause. Let's go over the module contents first though. We'll talk about the data used in the module. We'll dive right into the Windowing Clause, and we'll talk about the rows key word and range key word. And then we'll end module with a summary. Let's talk about the data use of the module first. We have a table called CHILDSTAT, which contains the following column, the first name, which is the child's first name, the gender, which is the child's gender, M for male and F for female. The birthdate, which is the child's date of birth, date data type in Oracle, small date time in SQL Server. Height, child's height in inches, and weight, the child's weight in pounds. And this is what the data looks like.

Analytic Functions - Part 4
Hello, and welcome back to Advanced SQL. My name is Scott Hecht and in this module I'd like to conclude our discussion of analytic functions by talking about the keep clause as well as take a look into the statistical functions available in both Oracle and SQL Server. First let's go through the module contents. We're going to talk about the data used in the module. We'll dive into the keep clause, and we'll talk about statistic related analytic functions specifically the MEDIAN function, the NTILE function, the PERCENT_RANK function, the CUME_DIST function, the PERCENTILE_DISC function, and the PERCENTILE_CONT function and then we conclude the module with a summary. Let's talk about the data used in the module though. We have a table called CHILDSTAT which contains the columns FIRSTNAME, GENDER, M for male, F for female, BIRTHDATE which is the child's date of birth, the HEIGHT of the child in inches, and the WEIGHT of the child in pounds. And here's what the data looks like.

The WITH Clause - Part 1
Hello, welcome back to Advanced SQL. My name is Scott Hecht. And in this module, I'd like to talk to you about the WITH clause, but specifically non-recursive simple version of the WITH clause. Let's go through the module contents first. Let's start off with an introduction, we'll talk about the data used in a module. And let's talk about the non-recursive WITH Clause, specifically the SELECT and WITH Clause. We'll talk about the CREATE TABLE or into, with the WITH Clause, and we'll talk about using previously Defined WITH Clauses and subsequently Defined WITH Clauses. So, why learn the WITH clause? Specifically, why learn the simple or non-recursive WITH clause? Well it allows you to neaten up very large SQL queries. Now, we've all been there, we've all created these huge SQL queries. And we've gone back six months later and just looked at it and said, what where we thinking. WITH clause allows you to, neaten up your SQL query, makes it nice to look at, quick to understand. It's very, very nice. It also allows you to prevent repeated sub-queries from being processed repeatedly, which is also very nice. And you can refer to previously defined, with clauses in subsequently define with clauses, which is very nice as well. Availability is Oracle 9i release one, and it's known as the sub-query factoring clause. In SQL 2005 on forward where it's known as common table expressions. I'll just refer to it as the with clause. Let's talk about the data used in the module. We have one table, and that's called child stat, which contains the following columns. The first name, is the child's first name. The gender is m for male, f for female. The child's birth date, a date in Oracle or small day time SQL server. The height of the child in inches, and the weight of the child in pounds. And the data looks like this. Very simple, just a few rows.

The PIVOT and UNPIVOT Features
Hello and welcome back to Advanced SQL. My name is Scott Hecht and in this lecture I'd like to talk to you about the pivot and unpivot features, which allow you to transpose rows into columns, and columns into rows. Let's take a look at the module contents. We're going to first start off talking about some vintage data transposition. An introduction followed by turning columns into rows using our old friend, a multi-table INSERT and UNION, and turning rows into columns using CASE statements and an aggregate function like the sum function. And then we'll talk about the for modern data transposition. For example, using the UNPIVOT to transpose columns into rows, or using the PIVOT feature, which transposes rows into columns. Then we'll about Oracle's multi-column pivot feature. We'll have to talk a little bit about Oracle's extension to the in function and then we'll dive right into the multi-column pivot and then we'll follow up with a summary. Let's talk about the data used in the module. We have a table called candy bar consumption data. Which contains the column's consumer ID, which is a unique identifier for a consumer. The candy bar name, for example Mars bar, Twix bar, and so on. The survey year, which is a year of survey responses. For example 2009, 2010, and 2011. The gender, m for male, f for female. The overall rating of the candy bar, for example one is low and ten's high. And finally the number of bars consumed, that is the number of candy bars consumed during a year. And the data looks like this. And finally like this. There's 36 total rows of data.