7.1.1 Basic Terminology

A database is a collection of information that is organized so that it can be easily accessed, managed and updated.

A relational database is a set of tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables.

7.1.2 CAP Theorem

CAP Theorem is a concept that a distributed database system can only have 2 of the 3: Consistency, Availability, and Partition Tolerance.

• Consistency: Every read receives the most recent write or an error
• Availability: Every request receives a (non‐error) response – without the guarantee that it contains the most recent write
• Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc.

Eventually‐consistent services are sometimes classified as providing BASE (Basically Available, Soft state, Eventual consistency), in contrast to traditional ACID.

Database systems designed with traditional ACID guarantees in mind such as RDBMS choose consistency over availability, whereas systems designed around the BASE philosophy, common in the NoSQL databases, choose availability over consistency.

7.1.3 SQL in SAS and R

SQL can be used in R by the sqldf package and using the sqldf() function whereas in SAS we use PROC SQL for using SQL. Proc sql does not require a run statement because proc sql statements are executed immediately.

7.2.1 SQL WHERE

We use the WHERE clause along with SELECT to conditionally select rows.

For example:

To select only rows which have cyl=6

In R

7.2.2 SQL Order By

“Order by” is used to display the output sorted. It can be sorted in ascending, descending, or alphabetical order.

In SAS:

Sort/Order Data in SAS

In R

7.2.3 AND, OR, NOT in SQL

AND, OR, and NOT operators can be used along with the WHERE clause.

AND operator displays only those rows which meet all conditions in the WHERE clause.

OR operator displays those rows which meet at least one of the conditions in the WHERE clause.

NOT operator displays those rows which do not meet the condition specified in the WHERE clause.

In SAS:

To select only those rows from mtcars which have cyl=6 as well as gear=3

To select only those rows which have either carb = 1 or carb = 4

To select only and all those rows which do not have am=0:

In R:

To select only those rows from mtcars which have cyl=6 as well as gear=3

To select only those rows which have either carb=1 or carb=4

To select only and all those rows which do not have am=0

7.2.4 SQL Select Distinct

We can select only the unique values of a column using the SELECT DISTINCT statement:

For example:

To know what the distinct values are that the variable gear takes in mtcars:‐

In SAS:

In R

7.2.5 SQL INSERT INTO

INSERT INTO statement is used to add rows to a data table.

• While using statements like INSERT INTO, ALTER TABLE, UPDATE which make changes to a data table in R, it is important to remember that sqldf() makes a copy of the data table provided to it, makes the required changes and returns the copy with the required changes.
• To make changes on the original data table, we need to assign the copy of the table returned by sqldf() to the object that stored the original copy.
• To use multiple SQL statements in sqldf(), we use the c() function.
• Proc Sql on the other hand makes the changes on the original copy itself.

To Insert a Row in SAS:

Note Var1 has taken the value of row.names from input R dataset.

To insert a row in mtcars in R:

Note the use of c() function and that we have assigned the result of sqldf ()to mtcars. Also note the output has 33 rows instead of the initial 32 with the new row having the input values.

7.2.6 SQL Delete

The DELETE statement is used along with WHERE to conditionally delete rows.

In SAS:

In R:

7.2.7 SQL Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Some aggregate functions are min(),max(),avg.(),sum(),count() are:

Aggregate functions ignore missing values except count().

• min() gives the minimum value of the column.
• max() gives the maximum value of the column.
• avg.() gives the average value of the values in the column.
• sum() gives the sum of all values in the column.
• count() gives the number of non missing values in a column.

In SAS:

Here, create table creates a new table mtcars2

In R:

7.2.8 SQL ALIASES

Aliases are used to give a temporary name. The AS clause is used with SELECT to do so.

In SAS:

In R:

7.2.9 SQL ALTER TABLE

ALTER TABLE is used to add, delete or modify column names in SAS.

ADD,DELETE,MODIFY are clauses that can be used with ALTER TABLE.

The new column added contains null values by default.

To make a new column in mtcars with the label ‘name’ and of type char.

In SAS:

In R:

7.2.10 SQL UPDATE

UPDATE is used to make changes to the rows of a table and is used with the SET and WHERE clause.

In SAS:

In R:

7.2.11 SQL IS NULL

Missing values in SQL are checked by IS NULL or IS NOT NULL. We take the airquality dataset from https://vincentarelbundock.github.io/Rdatasets/csv/datasets/airquality.csv

Example:

To select all rows with missing values in the Ozone column:of airquality:

In SAS (first we replace NA missing value of R with missing value in SAS using compress. We use outobs in Proc SQL to limit output to five rows.

In R:

7.2.12 SQL LIKE and BETWEEN

The LIKE option is used along with WHERE and wildcards like % and _ to select rows that have values with a specified pattern in a column:

• % is used to denote a string of characters
• _ is used to denote a single character.

In SAS:

In R:

BETWEEN operator can also be used with WHERE as follows:

7.2.13 SQL GROUP BY

The GROUP BY statement is used with aggregate functions to calculate the corresponding values separately for all groups of a column. It is similar to the CLASS operator in PROC MEANS and group by operator in Hmisc in R and in data table. For multiple variables in R, we use llist in Hmisc::summarize and .(var1,var2) in data.table.

To calculate the average of mpg for each of the distinct values that cyl can take:

In SAS:

In R:

7.2.14 SQL HAVING

SQL HAVING

HAVING is used after GROUP BY to select only certain groups from the grouped aggregate values.

To display only those values of avg(mpg) grouped by cyl which have avg(mpg) >19 and sorted in ascending order of avg(mpg).

In SAS:

In R:

7.2.15 SQL CREATE TABLE and SQL CONSTRAINTS

CREATE TABLE clause can be used to create a table in SQL. NOT NULL and UNIQUE are CONSTRAINTS in SQL. They are used in front of the variable to specify that values in the user_id column cannot be missing and cannot be repeated respectively.

In SAS:

Note: The string values have been truncated in proc sql.

In R:

There is only one INSERT INTO statement is used in R and data values for multiple rows are separated by commas whereas the number of INSERT INTO statements needed in SAS equals the number of rows to be inserted.

PRIMARY KEY is another constraint in SQL which is used to uniquely identify a row in a table.

Columns with only the UNIQUE constraint can have null values whereas PRIMARY KEY columns cannot have null values. There can be several columns with UNIQUE constraint in a data table whereas there can be only one PRIMARY KEY column in a data table.

7.2.16 SQL UNION

UNION clause with select is used to select all observations which lie in at least one of the result sets. UNION ALL can be used to select the observations that lie in both the result sets twice along with the observations that lie in only one of them.

To select all observations which have either cyl=6 or gear=4 or both

In SAS:

In R:

7.2.17 SQL JOINS

SQL JOINS can be used to merge data from more than one table into a single table.

INNER JOIN

Inner Join is used to select only those records that have the same value for a particular column.

LEFT JOIN

Left Join is used to select all records from the first table and those records from the second table which have common values in both tables for the specified column.

Let’s take these tables ‐issued, book and user.

And book table.

And

User

In SAS – Inner Join:

In SAS – Left Join:

In R:

There are many types of JOINs in SQL:

• Inner Join:
• Full Outer Join:
• Left Outer Join:
• Right Outer Join:
• Self‐Join:
• Cross Join: