Intro to Database Systems - Part 8 to 10 : Intermediate SQL

Using the next common keywords will return a column with only one row. They are called aggregate functions:

1) COUNT : returns the number of rows matching a specific criteria.

2) AVG : returns the average value of a column

3) SUM : returns the sum value of a column

4) MAX/MIN : returns the largest/lowest value of the selected column.

Let’s investigate on some other common keywords now:

5) GROUP BY : a keyword to group elements by columns.

It’s important to keep it mind that the general form of GROUP BY is :

6) HAVING : the keyword HAVING is to groups what WHERE is to columns. It’s a filter. It restricts the groups of returned rows to only those whose the condition is TRUE.

For now we’ve covered a pretty good intro to SQL tables. Let’s talk about 2 features of SQL that are a little more abstract now :

1) VIEWS

The view is just like a table. Here is the general syntax:

Now why should you use a VIEW instead of a normal table? For 3 reasons:

We can CREATE views and then manipulate them:

To DROP views :

But what if we want to drop the table, and the table has a view associated it with? That’s when we use DROP TABLE CASCADE.

2) NULL VALUES

To select records that are null in our db we :

We can NOT compare to null. We get an “unknown” answer. We can evaluate this result either to “true”, “false” or again “unknown”. If we
NOT unknown we’ll obtain unknown once more.

Also * A OR B = unknown* when one of the following situations occur:

Same for A AND B = unknown.

As a last note on NULL, please be aware that COUNT ( * ) actually counts the null values. The other aggregate operations simply ignore the NULL values.

 
42
Kudos
 
42
Kudos

Now read this

Intro to Database Systems - Part 4 & 5 : Relational Algebra

Let’s start by understanding what relational algebra is: it is a query language (not a programming language) takes instances of relations as input (basically rows from a table, also called tuples) yields instances of relations as output... Continue →