SQL Manual.Rmd

---
title: "SQL Manual"
author: "Abu Nayeem"
date: "September 25, 2014"
output: html_document
---


#### Introduction

SQL is used to take information from relational databases. It is significantly much more efficient than R in certain fucnitons  becuase it alegabraically optimize operations. Normally the strategy involves. The operations are extremely simple in comparison to R and other languages. Unfortunately there is several forms of SQL depending on the server. **Note:** SQL commands are capitalized to be read easier. Also semicolon designate the end of a line of code 

##### Basic Commands

Basic Reminders

* WHERE is "if" clause
* BETWEEN is for range (works string); IN for precise subset; EX: Price BETWEEN 10 and 20
* AND/ OR and NOT for negation; Ex: City='blue' and Price NOT IN (10,20)
* IN statement can replace multiple OR statements. [Not also allowedFNU]
* ORDER BY is the arrange fucntion
* Use COUNT(1) instead of COUNT(asterick) if you need to count number of entries
* SELECT DISTINCT is used to return only different values; this varies from UNIQUE
* Date Types vary amongst Database so check out sources
* A `semi-colon` ends a query

A) Selection 

    Columns:
```{r}
# multiple columns
SELECT column_name,column_name 
FROM table_name;
# all columns
SELECT * FROM table_name;
# unique values of a column, i.e. no duplicates
SELECT DISTINCT city FROM Customers;
```

    Rows [Where is used for clause]:
```{r}
# selection of rows on basis of multiple arguements, for numbers dont use quote
SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');
# arguement of specific price not in those groups; NOTICE that parathesis was used
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20) # select between those prices
AND NOT CategoryID IN (1,2,3); # choose category ID where those are missing
# another example with characters
SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M'; # select between products between the characters c and m
```    
Notice that the BETWEEN operator can produce different result in different databases!

    Select Specific number of entries
```{r}
# MYSQL
SELECT *
FROM Persons
LIMIT 5; # limit number of entries to 5
# Oracle
SELECT *
FROM Persons
WHERE ROWNUM <=5; # limit the entries to 5
# Select some ordered amount
SELECT TOP 2 * FROM Customers; # select first two entries
SELECT TOP 50 PERCENT * FROM Customers; # first 50%
# Skip certain entries use OFFSET
SELECT *
FROM Persons
LIMIT 5 OFFSET 2; # there is five entries but the first wo entries are skipped
```

`LIKE` operator acts like a grep function in R: example below
```{r}
SELECT * FROM Customers
WHERE City LIKE 's%'; # pick cities that start with letter s
```
* 's%'; # pick cities that start with letter s
* '%s'; # pick cities the end with letter s
* '%land%'; # pick country with pattern land
* NOT LIKE '%land%'; # pick country with no pattern land
* 'ber%'; # (%) we chose patterns start with ber and the rest don't matter
* '_erlin';# (-) select City starting with any character, followed by "erlin"
* 'L_n_on'; # you get the idea
* '[bsp]%'; # [bsp] select the city starting with b, p, or s
* '[a-c]%'; # [a-c] select starting a,b,c
* '[!bsp]%'; # [!bsp] Not; alternative- NOT LIKE '[bsp]%'
    
    Ordering: Could be important in improving efficiency if properly ordered
```{r}
# the default is ascending, in this case it was double ordering 
SELECT * FROM Customers
ORDER BY Country DESC, city;
```

B) Inserting New Values

Note: The CustomerID column is automatically updated with a unique number for each record in the table.
```{r}
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) # choose datasets and the columns
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway'); #new row in the "Customers" table

# Missing values will be blank
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway')
```

C) Updating Existing Values; if there is no WHERE clause then everything 
```{r}
UPDATE Customers # choose databases
SET ContactName='Alfred Schmidt', City='Hamburg' #choose changes you want 
WHERE CustomerName='Alfreds Futterkiste'; # find the clause to replace this with
```

D) Delete specific rows
```{r}
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
# Keep table inttact but contents gone
DELETE * FROM Customers;
```

E) SQL Aliases- this is used for renaming columns or functions but note it can combine mutiple rows. Also for names with space you need to put brackets
```{r}
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers; # notice the brackets was used because space was used
# Combine 4 columns into one: SQL format [OTHER formats vary where MYSQL use CONCAT function]
SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address
FROM Customers # notice the syntax.
SELECT CustomerName, CONCAT(Address,', ',City,', ',PostalCode,', ',Country) AS Address #MySQL
# Complicated example
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID
# selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively
```

F) SQL Joins

* SQL `INNER JOIN` return all rows from multiple tables where the join condition is met
* `LEFT JOIN` keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). ; RIGHT Join is reversal
* `FULL OUTER JOIN` keyword combines the result of both LEFT and RIGHT joins

Example
```{r}
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate # select categories
FROM Orders # this is the left table
INNER JOIN Customers # this is right table
ON Orders.CustomerID=Customers.CustomerID # the common ID to connect
ORDER BY Customers.CustomerName; # how we want to view the data
```

G) `UNION` - It is special SELECT statement that takes information from multiple tables. However, the column need to have same name, type, and the table need to be similar (?). This is similar to the **Merge** feature in many databases. **Note:** the number of fields need to be the same for the UNION to work

* UNION - the values of the first query are returned with the values of the second query eliminating duplicates. 
* MINUS - the values of the first query are returned with duplicates values of the second query removed from the first query. [mutually exclusive first query]
* UNION ALL - the values of both queries are returned including all duplicates 
* INTERSECT - only the duplicate values are returned from both queries.

Example:
```{r}
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City; # select all (duplicate values also) German cities
```

In this example, the fields have different names but nevertheless match, also *notice* the ORDER BY allows to order on basis of location
```{r}
SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 2000
UNION
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;
```

**Note:** The difference between UNION and INTERSECT is that INTERSECT only keep the duplicated values, while UNION keeps all the unique values of each entry removing duplicate


H) SELECT INTO statement copies data from one table and inserts it into a new table.

```{r}
SELECT CustomerName, ContactName
INTO CustomersBackup2013 # save into new file
FROM Customers 
WHERE Country='Germany' # only a few columns into the new table with Country is Germany

SELECT *
INTO CustomersBackup2013 IN 'Backup.mdb'
FROM Customers; # copy the table into another database

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2013
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID; # Copy data from more than one table into the new table

```

I) INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.

```{r}
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany'; # copy German suppliers into customers which has CustomerName and Country
```

J) Using `IS NULL` condition which determines if there is missing value (TRUE) at an entry; `IS NOT NULL` is the polar opposite 

Examples:
```{r}
# View the entries with suppliers name missing
SELECT *
FROM suppliers
WHERE supplier_name IS NULL;
# Replace missing values with another set of values
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city IS NULL;
# Update/Repace missing values
UPDATE suppliers
SET supplier_name = 'Apple'
WHERE supplier_name IS NULL;
```




#### Creating Data

A) Create Database

```{r}
CREATE DATABASE my_db
```

B) Create Table

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

```{r}
# general form- state column name and the data type; size parameter specifies the maximum length of the column of the table
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
);
# example
CREATE TABLE PersonsNotNull # name of Table
(
P_Id int NOT NULL, # column is an integer and it does not accept null/ missing values [NOT NULL]
#### This is used instead of above for MySQL: P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL, # character also does not accept missing values
FirstName varchar(255),
Address varchar(255),
City varchar(255)
#### UNIQUE (P_Id) is added under Oracle
);
```

Other Table Operations:
```{r}
TRUNCATE TABLE table_name; # delete data in table but keep table structure
# add column with type
ALTER TABLE Persons
ADD DateOfBirth date 
# change columnd data type
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year 
# Auto-increment for each new entry add 1 value;  the default starts at 1
ID int NOT NULL AUTO_INCREMENT # put on column designation
# Auto Increment by 5 starting from 10
 CREATE TABLE Persons(
ID int IDENTITY(10,5) PRIMARY KEY)
```

VIEWS is a virtual table based on the result-set of an SQL statement. It is always up to date upon query
```{r}
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) ; # selects every product in the "Products" table with a unit price higher than the average unit price
# Nested VIEWS
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName; # calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Product Sales for 1997"
```

`CREATE TABLE AS` allows you to create a table that has elements of another table
```{r}
CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
      FROM companies, categories
      WHERE companies.id = categories.id
      AND companies.id > 1000);
```

C) Working with constraints, primary keys [only one primary key allowed], foreign keys

Constraints:
```{r}
# Setting Constraint
CREATE TABLE Blah ()
column, values
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) #name the constraint and its on mutilple cols
)
# Adding a constraint
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
# Removing a constraint 
ALTER TABLE Persons
DROP CONSTRAINT/ INDEX[MySQL only] uc_PersonID 
```

Primary Keys: Only one primary key per table and cannot 
```{r}
# Setting Primary Key
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY, # the PRIMARY Key is used Oracle, normal SQL, MS Access 
City varchar(255),
PRIMARY KEY (P_Id) # this line is only added for MySQL and PRIMARY KEY is missing on previous
)
# for mulitple keys (true for all), Add the following line
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
# Add key
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id);
# Remove key
ALTER TABLE Persons
DROP CONSTRAINT/ INDEX[MySQL only] uc_PersonID 
```

Foreign Keys: For multiple tables they may share a common ID, and maybe you want to extract an idea from another table which is determined as foreign

Insert PNG table

```{r}
# MySQL
CREATE TABLE Orders(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int, # which P_Id dp we choose
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) # the P_ID of interest is from Persons 
# Everything else
CREATE TABLE Orders (
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id) )
# Use the normal constraint trick when adding and removing foreign variable designations
```

CHECK constraint is used to limit the value range that can be placed in a column. The limits can be used by other components in rows

```{r}
# ADD Check Constraint
CREATE TABLE Persons(
P_Id int NOT NULL CHECK (P_Id>0), # Everything except MySQL
City varchar(255))
CHECK (P_Id>0) # MySQL only
# Multiple COnstraint
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
# Adding is same thing
```

DEFAULT constraint is used to insert a default value into a column
```{r}
CREATE TABLE...
OrderDate date DEFAULT GETDATE() # set Default date from system date
```

CREATE INDEX statement is used to create indexes in tables. It is used to search data faster. Updates with indices take longer, so put indices on what is commonly searched. Check database for syntax!

```{r}
CREATE (UNIQUE) INDEX PIndex
ON Persons (LastName, FirstName); # index on table with duplicate values and put unique option for non-duplication
```

D) SQL `Group By` a category

```{r}
# Single Grouping
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders # [pick columns and titles, with left table as Orders]
LEFT JOIN Shippers # left join keeps all data of Orders
ON Orders.ShipperID=Shippers.ShipperID # match I.D
GROUP BY ShipperName; # find the number of orders sent by each shipper- counts as orders are grouped by shippers
# Multiple layer grouping [complex code below]
SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID) # FIRST we create a table that has unique values
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID) # SECOND we combine it with employee data
GROUP BY ShipperName,LastName; # double layer grouping

```

E) The `ALTER TABLE` command allows you to to RENAM (columns and table), MODIFY (change type), DELETE, and ADD columns.

Add:
```{r}
ALTER TABLE supplier
  ADD (supplier_name varchar2(50),
       city varchar2(45));
```

Modify:
```{r}
ALTER TABLE supplier
  MODIFY (supplier_name varchar2(100) not null,
          city varchar2(75));
```

Drop:
```{r}
ALTER TABLE supplier
  DROP COLUMN supplier_name;
```

Rename Columns:
```{r}
ALTER TABLE supplier
  RENAME COLUMN supplier_name to sname;
```

Rename Table:
```{r}
ALTER TABLE suppliers
  RENAME TO vendors;
```

E) SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input. It compromise the security of a web application.

Example #1:

If there is nothing to prevent a user from entering "wrong" input, the user can enter some "smart" input like this:

UserId: (105 or 1=1
```{r}
# Server Result
SELECT * FROM Users WHERE UserId = 105 or 1=1
# The SQL above is valid. It will return all rows from the table Users, since WHERE 1=1 is always true.
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1
# A smart hacker might get access to all the user names and passwords in a database by simply inserting 105 or 1=1 into the input box.
```

Example 2: A smart hacker might get access to user names and passwords in a database by simply inserting " or ""=" into the user name or password text box.

```{r}
#server code
uName = getRequestString("UserName");
uPass = getRequestString("UserPass");9
sql = "SELECT * FROM Users WHERE Name ='" + uName + "' AND Pass ='" + uPass + "'"
# INJECTION
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""
# The result SQL is valid. It will return all rows from the table Users, since WHERE ""="" is always true.
```

Example 3: The user inserts a code to delete information from server

```{r}
# server code
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
# injection code
User id:(
105; DROP TABLE Suppliers
# It would create following code
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers
```

**For Protection** add SQL parameters on SQL Query operation

Strategy:
The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.


Asp.net razor Example
```{r}
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);
# Note that parameters are represented in the SQL statement by a @ marker.

# another example
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);
```

ASP.NET SELECT
```{r}
txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserID);
command.ExecuteReader();
```
ASP.NET INSERT INTO
```{r}
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtNam);
command.Parameters.AddWithValue("@1",txtAdd);
command.Parameters.AddWithValue("@2",txtCit);
command.ExecuteNonQuery();
```
PHP INSERT INTO
```{r}
$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City) 
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':val', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();
```

#### Other Operations

A) Dates (different type and format depending on server)

FUnctions:
MySQL
* NOW() - Returns the current date and time
* CURDATE()- Returns the current date
* CURTIME()- Returns the current time
* DATE() - Extracts the date part of a date or date/time expression
* EXTRACT() - Returns a single part of a date/time
* DATE_ADD() - Adds a specified time interval to a date
* DATE_SUB()- Subtracts a specified time interval from a date
* DATEDIFF() - Returns the number of days between two dates
* DATE_FORMAT() -Displays date/time data in different formats
SQL
* GETDATE()- Returns the current date and time
* DATEPART()- Returns a single part of a date/time
* DATEADD()- Adds or subtracts a specified time interval from a date
* DATEDIFF()- Returns the time between two dates
* CONVERT()- Displays date/time data in different formats

B) Null Values [IS NULL]

Selection:
```{r}
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL # find entries where address is not NULL
```

C) Data Types [there are standard R type integers]; Note- there are many number types respect to size

D) Functions:

Operations
* AVG() - Returns the average value
* COUNT() - Returns the number of rows that fit a criteria
* FIRST() - Returns the first value
* LAST() - Returns the last value
* MAX() - Returns the largest value
* MIN() - Returns the smallest value
* SUM() - Returns the sum

Scalar Operations
* UCASE() - Converts a field to upper case
* LCASE() - Converts a field to lower case
* MID() - Extract characters from a text field
* LEN() - Returns the length of a text field
* ROUND(x,0) - Rounds a numeric field to the number of decimals specified
* NOW() - Returns the current system date and time [leave blank]
* FORMAT() - Formats how a field is to be displayed; Ex: FORMAT(Now(),'YYYY-MM-DD')

Some Examples
```{r}
# Average
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products); # shows product name and price where price is above the average price- Notice the select
# Count
SELECT COUNT(DISTINCT column_name) FROM table_name; # count unique entries
```

HAVING function is similar to where but for functions such as GROUP BY
```{r}
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller' #where clause is added to the SQL statement
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25 # notice it uses on operation as oppose to a value of a cell

```

Useful Question
With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
```{r}
INSERT INTO Persons (LastName) VALUES ('Olsen')
```

`SQL Exist` Condtion allows a *subquery* to be implemented. **Note:** Using a subquery is very iniefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the SQL EXISTS Condition.
Example: with delete statement
```{r}
DELETE FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);
```


##### Exercises

**Selection**

1) Based on the *customers* and *old_customers table*, select the *customer_id* and *customer_name* from the customers table that exist in the *old_customers* table (matching the *customer_id* field from the customers table to the *old_customer_id* field in the *old_customers* table). Order the results in ascending order by *customer_name* and then descending order by *customer_id*.

Creating Tables
```{r}
CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

CREATE TABLE old_customers
( old_customer_id number(10) not null,
  old_customer_name varchar2(50) not null,
  old_city varchar2(50),
  status varchar2(20),
  CONSTRAINT old_customers_pk PRIMARY KEY (old_customer_id)
);
```

**Solution:** Notice we use the `SQL EXIST` clause
```{r}
SELECT customer_id, customer_name
FROM customers
WHERE EXISTS
  ( SELECT old_customers.old_customer_id
    FROM old_customers
    WHERE old_customers.old_customer_id = customers.customer_id )
ORDER BY customer_name ASC, customer_id DESC;
```

**LIKE Command**

1) Based on the employees table populated with the following data, find all records whose employee_name ends with the letter "h".

Creating Tables
```{r}
CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);
```

**Solution:**
```{r}
SELECT *
FROM employees
WHERE employee_name LIKE '%h';
```

2) Based on the employees table populated with the following data, find all records whose employee_name contains the letter "s".

**Solution:**
```{r}
SELECT *
FROM employees
WHERE employee_name LIKE '%s%';
```

3) Based on the suppliers table populated with the following data, find all records whose supplier_id is 4 digits and starts with "500".

Creating Tables:
```{r}
CREATE TABLE suppliers
( supplier_id varchar2(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers(supplier_id, supplier_name, city)
VALUES ('5008', 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5009', 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5010', 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5011', 'NVIDIA', 'New York');
```

**Solution:** Notice it restricts to only 4 digits
```{r}
SELECT *
FROM suppliers
WHERE supplier_id LIKE '500_';
```

**INSERT Command**

**Standard Question:** I am setting up a database with clients. I know that you use the SQL INSERT statement to insert information in the database, but how do I make sure that I do not enter the same client information again?

**Answer:** You can make sure that you do not insert duplicate information by using the SQL EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following SQL INSERT statement:
```{r}
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = suppliers.supplier_id);
```

This SQL INSERT statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following SQL INSERT statement:
```{r}
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = 10345);
```

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

1) Based on the *customers* and *old_customers* table, insert into the customers table all records from the *old_customers* table whose status is DELETED.

Create tables
```{r}
CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

CREATE TABLE old_customers
( old_customer_id number(10) not null,
  old_customer_name varchar2(50) not null,
  old_city varchar2(50),
  status varchar2(20),
  CONSTRAINT old_customers_pk PRIMARY KEY (old_customer_id)
);
```

**Solution:**
```{r}
INSERT INTO customers
(customer_id, customer_name, city)
SELECT old_customer_id, old_customer_name, old_city
FROM old_customers
WHERE status = 'DELETED';
```

**Update Command:**

1) Based on the suppliers table populated with the following data, update the city to "Santa Clara" for all records whose supplier_name is "NVIDIA".

**Solution:**
```{r}
UPDATE suppliers
SET city = 'Santa Clara'
WHERE supplier_name = 'NVIDIA';
```

2) **GOOD ONE:** Based on the *suppliers* and *customers* table populated with the following data, update the city in the *suppliers* table with the city in the customers table when the *supplier_name* in the suppliers table matches the *customer_name* in the customers table. 

Create Tables:
```{r}
CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5005, 'NVIDIA', 'LA');


CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7001, 'Microsoft', 'San Francisco');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7002, 'IBM', 'Toronto');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7003, 'Red Hat', 'Newark');
```

**Solution [The solution seems weak]:**
First, we **only set** the city name if customer city and supplier city match. Hence we implement a **subquery** on the city equal statement.
```{r}
UPDATE suppliers
SET city = (SELECT customers.city
            FROM customers
            WHERE customers.customer_name = suppliers.supplier_name)
WHERE EXISTS (SELECT customers.city
              FROM customers
              WHERE customers.customer_name = suppliers.supplier_name);
```

**My Solution**
```{r}
UPDATE suppliers
SET city = customers.city
WHERE EXISTS (SELECT customers.city
              FROM customers
              WHERE customers.customer_name = suppliers.supplier_name);
```

**DELETE command**

**Standard FAQ**
How would I write a SQL DELETE statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?

```{r}
DELETE FROM TableA
WHERE NOT EXISTS
  ( SELECT *
    FROM TableB
     WHERE TableA.field1 = TableB.fieldx
     AND TableA.field2 = TableB.fieldz );
```

1) Based on the customers and oldcustomers table, delete from the customers table all records that exist in the oldcustomers table (matching the customerid field from the customers table to the oldcustomerid field in the old_customers table).

**Solution:**
```{r}
DELETE FROM customers
WHERE EXISTS
  ( SELECT old_customers.old_customer_id
    FROM old_customers
    WHERE old_customers.old_customer_id = customers.customer_id );
```

**COUNT command:**

1) Based on the employees table populated with the following data, count the number of employees whose salary is over $55,000 per year.

**Solution:**
```{r}
SELECT COUNT(1) AS "Number of employees"
FROM employees
WHERE salary > 55000;
```

2) Based on the suppliers table populated with the following data, count the number of distinct cities in the suppliers table:

**Solution:**
```{r}
SELECT COUNT(DISTINCT city) AS "Distinct Cities"
FROM suppliers;
```

3) **GOOD**: Based on the customers table populated with the following data, count the number of distinct cities for each customer_name in the customers table:

**Solution:** We use group by to resolve this problem
```{r}
SELECT customer_name, COUNT(DISTINCT city) AS "Distinct Cities"
FROM customers
GROUP BY customer_name;
```

**MAX COMMAND**:

**Common Problems:**

1) Question: I'm trying to get the employee with the maximum salary from department 30, but I need to display the employee's full information. I've tried the following query, but it returns the result from both department 30 and 80:
```{r}
SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary)
                FROM employees
                WHERE department_id=30);
```

**Solution:** The SQL SELECT statement that you have written will first determine the maximum salary for department 30, but then you select all employees that have this salary. In your case, you must have 2 employees (one in department 30 and another in department 80) that have this same salary. You need to *make sure that you are refining your query results* to only return employees from department 30.

```{r}
SELECT *
FROM employees
WHERE department_id=30
AND salary = (SELECT MAX(salary)
              FROM employees
              WHERE department_id=30);
```

2) I'm trying to retrieve some info from an Oracle database. I've got a table named Scoring with two fields - Name and Score. What I want to get is the highest score from the table and the name of the player.

**Solution:** We use a *Sub-query* to find the value of the highest
```{r}
SELECT Name, Score
FROM Scoring
WHERE Score = (SELECT MAX(Score) FROM Scoring);
```

3) I have a table in Oracle called orders which has the following fields: order_no, customer, and amount. I need a query that will return the customer who has ordered the highest total amount.

**Solution: [Complicated]**
```{r}
SELECT query1.*
FROM (SELECT customer, SUM(orders.amount) AS total_amt
      FROM orders
      GROUP BY orders.customer) query1,

     (SELECT MAX(query2.total_amt) AS highest_amt
      FROM (SELECT customer, SUM(orders.amount) AS total_amt
            FROM orders
            GROUP BY orders.customer) query2) query3
WHERE query1.total_amt = query3.highest_amt;
```

This SQL SELECT statement will summarize the total orders for each customer and then return the customer with the highest total orders. This syntax is optimized for Oracle and may not work for other database technologies.

4) Question: I need help in a SQL query. I have a table in Oracle called custorder which has the following fields: OrderNo, Customerid, OrderDate, and Amount.

I would like to find the customer_id, who has Highest order count. I tried the following:
```{r}
SELECT MAX(COUNT(*))
FROM CUST_ORDER
GROUP BY CUSTOMER_ID;
```

This gives me the max Count, But, I can't get the CUSTOMER_ID. Can you help me please?

**Solution: [COMPLICATED!]** The following SQL SELECT statement should return the customer with the highest order count in the cust_order table.
```{r}
SELECT query1.*
FROM (SELECT Customer_id, Count(*) AS order_count
      FROM cust_order
      GROUP BY cust_order.Customer_id) query1,
      
     (SELECT max(query2.order_count) AS highest_count
      FROM (SELECT Customer_id, Count(*) AS order_count
            FROM cust_order
            GROUP BY cust_order.Customer_id) query2) query3
WHERE query1.order_count = query3.highest_count;
```

This SQL SELECT statement will summarize the total orders for each customer and then return the customer with the highest order count. This syntax is optimized for Oracle and may not work for other database technologies.

5)  I'm trying to pull some info out of a table. To simplify, let's say the table (report_history) has 4 columns: user_name, report_job_id, report_name, and report_run_date.

Each time a report is run in Oracle, a record is written to this table noting the above info. What I am trying to do is pull from this table when the last time each distinct report was run and who ran it last. My initial query runs fine. However, it does not provide the name of the user who ran the report:
```{r}
SELECT report_name, MAX(report_run_date)
FROM report_history
GROUP BY report_name
```

Adding user_name to both the select list and to the group by clause returns multiple lines for each report; the results show the last time each person ran each report in question. (i.e. User1 ran Report 1 on 01-JUL-03, User2 ran Report1 on 01-AUG-03). I don't want that....I just want to know who ran a particular report the last time it was run.

**Solution:** This is where things get a bit **complicated**. The SQL SELECT statement below will return the results that you want:
```{r}
SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh,
  (SELECT MAX(report_run_date) AS maxdate, report_name
   FROM report_history
   GROUP BY report_name) maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;
```
Let's take a few moments to explain what we've done.
*First*, we've aliased the first instance of the report_history table as rh.
*Second*, we've included two components in our FROM clause. The first is the table called report_history (aliased as rh). 

The second is a select statement:
```{r}
(SELECT MAX(report_run_date) AS maxdate, report_name
 FROM report_history
 GROUP BY report_name) maxresults
```

We've aliased the max(report_run_date) as maxdate and we've aliased the entire result set as maxresults.Now, that we've created this select statement within our FROM clause, Oracle will let us join these results against our original report_history table. So we've joined the report_name and report_run_date fields between the tables called rh and maxresults. This allows us to retrieve the report_name, max(report_run_date) as well as the user_name.

** UNION Operator**

**Question:** I need to compare two dates and return the count of a field based on the date values. For example, I have a date field in a table called last updated date. I have to check if trunc(last_updated_date >= trunc(sysdate-13).

**Answer:** Since you are using the COUNT function which is an aggregate function, we'd recommend using the Oracle UNION operator. For example, you could try the following:
```{r}
SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
AND a.status = 1
AND b.status = 1
AND b.Ncode <> 'a10'
AND TRUNC(last_updated_date) <= TRUNC(sysdate-13)
GROUP BY a.code, a.name
UNION
SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
AND a.status = 1
AND b.status = 1
AND b.Ncode <> 'a10'
AND TRUNC(last_updated_date) > TRUNC(sysdate-13)
GROUP BY a.code, a.name;
```

The Oracle UNION allows you to perform a count based on one set of criteria.
```{r}
TRUNC(last_updated_date) <= TRUNC(sysdate-13)
```
As well as perform a count based on another set of criteria.
```{r}
TRUNC(last_updated_date) > TRUNC(sysdate-13)
```

**CREATE Table**

1) Create a SQL table called customers that stores customer ID, name, and address information. But this time, the customer ID should be the primary key for the table.

**Solution:**
```{r}
CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);
```

2) Based on the departments table below, create a SQL table called employees that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.

Create Table:
```{r}
CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);
```

**Solution:**
```{r}
CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);
```

3) How can I create a SQL table from another table without copying any values from the old table? **Create an empty table that keeps the columns**

**Solution:**
```{r}
CREATE TABLE suppliers
  AS (SELECT *
      FROM companies WHERE 1=2);
```

**ALTERING TABLES**

1) Based on the departments table below, rename the departments table to depts.

Table:
```{r}
CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);
```

**Solution:** 
```{r}
ALTER TABLE departments
  RENAME TO depts;
```

2) Based on the employees table below, add a column called salary that is a number(6) datatype.

Table:
```{r}
CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);
```

**Solution**:
```{r}
ALTER TABLE employees
  ADD salary number(6);
```

3) Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called lastcontacted that is a date datatype.

```{r}
ALTER TABLE customers
  ADD (contact_name varchar2(50),
       last_contacted date);
```

4) Based on the employees table below, change the employee_name column to a varchar2(75) datatype.

```{r}
ALTER TABLE employees
  MODIFY employee_name varchar2(75);
```

**VIEWS Common Questions**

1) Can you update the data in a SQL VIEW?

**Answer:** A VIEW in SQL is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the SQL View.

So, yes, you can update the data in a SQL VIEW providing you have the proper privileges to the underlying SQL tables.

2) Does the SQL View exist if the table is dropped from the database?

**Answer:** Yes, in Oracle, the SQL VIEW continues to exist even after one of the tables (that the SQL VIEW is based on) is dropped from the database. However, if you try to query the SQL VIEW after the table has been dropped, you will receive a message indicating that the SQL VIEW has errors.

If you recreate the table (the table that you had dropped), the SQL VIEW will again be fine.