---
title: "Lesson 70.110 SQL Query Solutions"
subtitle: "Connect to Database"
author: Martin Schedlbauer, PhD
output: html_notebook
---

```{r connect2DB, echo=F, warning=F}
library(RSQLite)

dbfile = "orgdb.db"

dbcon <- dbConnect(RSQLite::SQLite(), dbfile)
```

```{sql connection=dbcon, echo=F}
PRAGMA foreign_keys = ON
```

## Solutions

Note that SQL is not case sensitive, but table and column names generally are. So, it does not matter whether you write 'select" or "SELECT" but it matters whether you specify "LastName" versus "lastname".

There may be more than one solution to the query. However, just because your query works, it may not be correct as it may not work for new data cases.

### Retrieve the last names of all workers under the new column name LastName.

```{sql connection=dbcon}
select LAST_NAME as 'LastName' 
  from Worker;
```

### Retrieve the first names of all workers in upper case.

```{sql connection=dbcon}
select upper(FIRST_NAME) 
  from Worker;
```

### Retrieve the names of all departments without duplicates.

```{sql connection=dbcon}
SELECT DISTINCT DEPARTMENT 
  FROM Worker;
```

### Retrieve the first three characters of all last names of all workers.

```{sql connection=dbcon}
SELECT SUBSTRING(LAST_NAME,1,3) 
  FROM Worker;
```

### Which worker's last names end with the character 'a'?

```{sql connection=dbcon}
SELECT LAST_NAME, FIRST_NAME
  FROM Worker
 WHERE LAST_NAME LIKE '%a';
```

### Retrieve the first names of all workers and remove any potential leading whitespace.

```{sql connection=dbcon}
SELECT ltrim(FIRST_NAME)
  FROM Worker
```

### Assume that some department names have trailing whitespace. Retrieve the unique names of all departments with any trailing whitespace removed.

```{sql connection=dbcon}
SELECT DISTINCT rtrim(DEPARTMENT)
  FROM WORKER
```

### Write an SQL query to list the FIRST_NAME and LAST_NAME from the Worker table into a single column COMPLETE_NAME in the form last, first, e.g., Patel, Vivek. (Hint: SQLite does not support the concat() function; instead it uses the \|\| operator).

```{sql connection=dbcon}
SELECT LAST_NAME||', '||FIRST_NAME AS 'COMPLETE_NAME'
  FROM WORKER
```

### Retrieve all Worker information sorted by LAST_NAME in ascending order.

```{sql connection=dbcon}
SELECT *
  FROM Worker
 ORDER BY LAST_NAME ASC;
```

### Retrieve the details for all workers with the first name of "Vipul" or "Satish".

```{sql connection=dbcon}
SELECT *
  FROM Worker
 WHERE FIRST_NAME = 'Vipul' OR FIRST_NAME = 'Satish'
```

```{sql connection=dbcon}
SELECT *
  FROM Worker
 WHERE FIRST_NAME IN ('Vipul','Satish')
```

### Retrieve the details for all workers that **do not** have the first name of either "Vipul" or "Satish".

```{sql connection=dbcon}
SELECT *
  FROM Worker
 WHERE FIRST_NAME <> 'Vipul' OR FIRST_NAME <> 'Satish'
```

```{sql connection=dbcon}
SELECT *
  FROM Worker
 WHERE FIRST_NAME NOT IN ('Vipul','Satish');
```

### Which workers have a salary between 100,000 and 500,000? List their last name, first name, and salary in thousands (e.g., 100 rather than 100,000), in that order.

```{sql connection=dbcon}
SELECT LAST_NAME, FIRST_NAME, SALARY/1000 as 'SALARY'
  FROM Worker
 WHERE SALARY BETWEEN 100000 AND 500000;
```

```{sql connection=dbcon}
SELECT LAST_NAME, FIRST_NAME, SALARY/1000 as 'SALARY'
  FROM Worker
 WHERE SALARY >= 100000 AND SALARY <= 500000;
```

### How many workers have a last name that starts with 'S'?

```{sql connection=dbcon}
SELECT COUNT(*) AS 'NUM'
  FROM Worker
 WHERE LAST_NAME LIKE 'S%';
```

### Which employees joined the company in February 2014?

```{sql connection=dbcon, eval=F}
SELECT LAST_NAME, JOINING_DATE
  FROM Worker
 WHERE strftime('%y', JOINING_DATE) = '14';
```

```{sql connection=dbcon}
SELECT LAST_NAME, JOINING_DATE
  FROM Worker
 WHERE substr(JOINING_DATE, 1, 2) = '14'
   AND substr(JOINING_DATE, 4, 2) = '02';
```

### How many employees work in the department 'Admin'?

```{sql connection=dbcon}
SELECT DEPARTMENT, COUNT(*)
  FROM Worker
 WHERE DEPARTMENT = 'Admin'
```

This shows the number of workers for two departments. Now we need a GROUP BY.

```{sql connection=dbcon}
SELECT DEPARTMENT, COUNT(*)
  FROM Worker
 WHERE DEPARTMENT IN ('Admin','HR')
 GROUP BY DEPARTMENT
```

### How many workers work for each department? List the department and number of workers in the descending order of numbers.

```{sql connection=dbcon}
SELECT DEPARTMENT, COUNT(*)
  FROM Worker
 GROUP BY DEPARTMENT;
```

### List the first and last name of all workers who are managers.

```{sql connection=dbcon}
SELECT LAST_NAME, FIRST_NAME
  FROM Worker w INNER JOIN TITLE t ON (w.WORKER_ID = t.WORKER_REF_ID)
 WHERE WORKER_TITLE = 'Manager';
```

### Retrieve the first five records (rows) from the Worker table sorted by salary from highest to smallest.

```{sql connection=dbcon}
SELECT *
  FROM Worker
 ORDER BY SALARY DESC
 LIMIT 10;
```

### Which employees have the same salary? List their last name and salary.

```{sql connection=dbcon}
SELECT w.LAST_NAME, w.SALARY
  FROM Worker w JOIN Worker k ON (w.SALARY = k.SALARY AND w.WORKER_ID != k.WORKER_ID);
```

### Which departments that have fewer than three people in it?

```{sql connection=dbcon}
SELECT COUNT(*) AS 'NumWorkers'
  FROM Worker
 GROUP BY DEPARTMENT
 HAVING NumWorkers < 3;
```

### Which employees have the highest salary in each department?

```{sql connection=dbcon}
SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary 
  FROM (SELECT max(Salary) as TotalSalary,DEPARTMENT 
          FROM Worker 
         GROUP BY DEPARTMENT) AS TempNew INNER JOIN Worker t 
                                         ON TempNew.DEPARTMENT=t.DEPARTMENT and TempNew.TotalSalary=t.Salary;
```

### What is the total salary paid out to all employees for each department?

```{sql connection=dbcon}
SELECT DEPARTMENT, SUM(SALARY)
  FROM Worker
 GROUP BY DEPARTMENT
```

### Who are the employees who earn the highest salary?

```{sql connection=dbcon}
SELECT LAST_NAME, SALARY
  FROM Worker
 WHERE SALARY = (SELECT MAX(SALARY) FROM Worker);
```

This query also works, although it is potentially less efficient because you are forcing an ordering (or sorting).

```{sql connection=dbcon}
SELECT LAST_NAME, SALARY
  FROM Worker
 WHERE SALARY = (SELECT SALARY FROM Worker ORDER BY SALARY DESC LIMIT 1);
```

### Who are the employees who earn below the average salary?

```{sql connection=dbcon}
SELECT LAST_NAME, SALARY
  FROM Worker
 WHERE SALARY < (SELECT AVG(SALARY) FROM Worker);
```

```{sql connection=dbcon}
SELECT MAX(SALARY)
  FROM Worker
```

```{r disconnectDB, echo=F, warnings=F}
dbDisconnect(dbcon)
```
