DAT 601 - Robin Le Couteur - Class reflection - 11/05/2018

Friday, May 11, 2018

Class Reflection

In this class we covered some useful SQL queries.

GROUP BY

The first statement we covered was the GROUP BY statement.

This statement groups results under the specified columns like this:

SELECT Country, Region, SUM(Sales) As TotalSales

FROM Sales

GROUP BY Country, Region;

This statement will group the sum of sales per region, and each region will be grouped under the country it belongs to.

There is also GROUP BY ROLLUP and GROUP BY CUBE statements which group them differently.

You can also group GROUP BY statements together with a GROUPING SET statement.

It is a simplified version of a UNION that concatenates GROUP BY statements into one table of results.

VIEW

A view is a useful for giving certain users a certain view of the database. In a view you can set the tables and columns that are able to be seen in that view, then if someone uses that view they can only access the data available in the view. It's a sort of permissions system in a way.

Other stuff that was covered was stuff like the nvar or nvarchar. The N means that the text is converted to Unicode to allow more character types.

We then did some exercises with GROUP BY, WHERE, and HAVING statements.

WHERE comes before the GROUP BY and only selects rows that conform to the where condition, whereas HAVING comes after the GROUP BY and selects all of the rows in groups that contain at least one row that conforms to the having condition.