21-sql.Rmd

# SQL {#sql}


## SQL Overview

We will teach SQLite, Microsoft Sequal Server, and PostgreSQL.

### Basic SQL commands


#### Selecting Columns and Dispalying the Data.

```{r, eval = F}

SELECT * FROM table;

SELECT column1, column2, column3, ... FROM table;

SELECT DISTINCT column_name FROM table;

```

#### Counting the Rows and Groups.

```{r, eval = F}

SELECT COUNT(*) FROM table;

```


#### GROUP BY is Amazing.

I think COUNT() with the GROUP BY command is one of the most useful commands.

```{r, eval = F}

SELECT COUNT(*) FROM table GROUP BY column1;

SELECT COUNT(*) FROM table GROUP BY column1, column2, ...;

```

Present the distinct group counts by descending order by n. 

```{r, eval=F}

SELECT column1, COUNT(column1) FROM table_name GROUP BY column1 
ORDER BY COUNT(column1) DESC;

SELECT Country, COUNT(Country) FROM Customers GROUP BY Country ORDER BY COUNT(Country) DESC;

```



Joining tables.

```{r, eval = F}

```


#### Deleting all rows from a table.


```{r, eval = F}

DELETE FROM table_name;

```


#### Deleting the entire table.

```{r, eval = F}

DROP TABLE table_name;

```


#### Deleting the entire database.

```{r, eval = F}

DROP DATABASE database_name;

```


#### How to insert data into tables. 

```{r, eval = F}

INSERT INTO table_name (colum_name) VALUES ('text');

INSERT INTO table_name (column_name) VALUES (2);

```



## PostgreSQL


Question 2
What is the psql command to show all of the databases in a server?
\l

What is the psql command to show the schema of a relation?
\dt  wrong

What is the psql command to show all the relations in a database?
\dd wrong

## Install linux in Windows

Windows 10 and onward, including the Windows 11 operating system which this book was written on, you can install a linux subsystem in windows and use that. The default distro that is downloaded is Ubuntu, however, you can download any particular distro you like. 

Here is the link to install a linux subsystem in Windows 11 OS: https://techcommunity.microsoft.com/t5/windows-11/how-to-install-the-linux-windows-subsystem-in-windows-11/m-p/2701207


### Installing Linux subsystem in windows. 

### Commands in Linux (Terminal, Command Line).

`pwd`

`ls`

`cd ~` This takes you to the home directory.

`wget`

`psql` Linux command to move into psql client where you can run all SQL commands we listed above.

### Installing psql client / server in the Linux subsystem.

Easiest way to do this is to follow the instructions here: https://askubuntu.com/questions/1040765/how-to-install-psql-without-postgres/1040766


### Commands in psql client.

In the psql client you can run all SQL commands, however, in psql it also allows to you run psql specific functions as well as the SQL commands. These commands let you see all the tables you have in the database, the relations that exist, etc. all the commands that is not easy to see through SQL commands as SQL does not have a convenient function/command to show all the tables in the database.

`\dt` - Show all the tables in the database.

`\dd` - 

`\d+` - Show the schema of the current table. 

`\q` - Getting out of psql client back into Linux

However, once you are

## Dr. Chuck Severance Questions - SQL Taught in PostgreSQL

```{r, eval = F}

```


### Auto Increment

```{r, eval = F}

CREATE TABLE table (
  id SERIAL,
  name VARCHAR(128),
  email VARCHAR(128) UNIQUE,
  PRIMARY KEY(id)
);

```

### Time

Use the function NOW() to get the current date and time. Also, the function TIMESTAMP can be used to get time and date.

Time zones - can get really complicated.

In PostgreSQL have to choose/pick your time zone.

The below tells you want is going on in the database you are working in.
```{r, eval = F}

# This is amazing. So using this you can always have a table to use and play with. 

SELECT * FROM pg_timezone_names;

```

### Altering Tables. 

#### Adding columns to a table.

```{r, eval = F }

ALTER TABLE table_name ADD COLUMN new_column_name data_type;

```

#### Removing/Dropping columns in a table.

```{r, eval = F}
ALTER TABLE table_name DROP COLUMN column_name;
```

A very common SQL command you will often be doing is finding the distinct values of a column in descending order of the count, with displaying only the top 5 or 10. Below is the complete SQL command for that.

```{r, eval=F}

SELECT state, count(state) FROM taxdata GROUP BY state ORDER BY COUNT(state) DESC LIMIT 5;

```

### Stored Procedures

Add a stored procedure so that every time a record is updated, the updated_at variable is automatically set to the current time. This was covered in the lecture materials. The auto grader will insert some records, then update them and check to see if updated_at is updated appropriately.

```{r, eval=F}

CREATE TABLE keyvalue ( 
  id SERIAL,
  key VARCHAR(128) UNIQUE,
  value VARCHAR(128) UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY(id)
);

```