# SQL on BigQuery

# Google BigQuery

* GBQ has access mechanism, so multiple team members can use, analyze and edit the data depending on their role.
* Using Identity and Access management of Google BigQuery, you can grant, read or write access to an individual team or group.

## Three primary parts involved in Google BigQuery

1. Storage 
2. Ingestion
3. Querying.

### Instruction 01

1. Go to Google Cloud Console
2. Create a new project without an organization
3. Go to BQ and create a new dataset "Purchasing"
4. Create new table in the 'Purchasing' dataset. In the source option, choose 'Upload' and then in the filepath, browse the `product_vendor.csv` file from the local machine. Give a name to the table "product_vendor".
5. Repeat step 4 for all `.csv` files provided as a course material.
6. We can overwrite a file by giving same table name and selecting 'overwrite existing tabel' option.

create new table -> upload -> browse new file -> same table name -> 'overwrite' option -> create button.

### Retrieve the records

**1. SELECT and SELECT DISTINCT**

```sql
SELECT * FROM `bigquerycourse.Purchasing.product_vendor` ;
 
SELECT ProductId, BusinessEntityId FROM `bigquerycourse.Purchasing.product_vendor`;
 
SELECT DISTINCT ProductId, BusinessEntityId, onorderqty FROM `bigquerycourse.Purchasing.product_vendor`;
```

**2. CASE**

```sql
SELECT
  PurchaseOrderId,
  totalDue,
  CASE
   WHEN totalDue BETWEEN 0 AND 5000 THEN 'Less Total Due'
   WHEN totalDue BETWEEN 5001 AND 10000 THEN 'Medium Total Due'
   WHEN totalDue> 10000 THEN 'Too much Total Due'
   ELSE 'Undefined'
  END AS total_due_amount
FROM
  `Purchasing.puchase_order_header`;
```

**3. WHERE Clause**

```sql
SELECT  PurchaseOrderId,RevisionNumber, EmployeeId, VendorID  FROM `bigquerycourse.Purchasing.puchase_order_header`
WHERE EmployeeId = 252;

Additional: =, !=, <>, >, >=, <, <= 
```

**4. Logical Operators (AND, OR, NOT)**

```sql
SELECT  PurchaseOrderId,RevisionNumber, EmployeeId, VendorID, OrderDate  FROM `bigquerycourse.Purchasing.puchase_order_header`
WHERE VendorId = 1546 AND OrderDate = '2013-11-09';
```

We can copy an existing table data into a new table by just clicking on 'Copy' button.

> If original table has 1000 rows, and the new table only have 900 rows. Then the new table will consist of 1000 rows in which last 100 rows will be null. And if you apply count(*) on the new table, it will return 1000.`

**5. Arthmetic Operators**

```sql
SELECT
  PurchaseOrderId,
  SubTotal,
  Taxamt,
  Freight,
  TotalDue,
  Subtotal + Taxamt + Freight - Totaldue AS differencedue
FROM
  `bigquerycourse.Purchasing.purchase_order_header`;

SELECT
  orderqty, UnitPrice, orderqty/UnitPrice AS orderyqtyperunitprice
FROM
  `bigquerycourse.Purchasing.purchase_order_detail`
  WHERE orderqty+UnitPrice < 350;
```

### Special Operators

**6. NULL and NOT NULL**

```sql
SELECT productid, onOrderqty  FROM `bigquerycourse.Purchasing.purchase_vendor`
WHERE productid is NOT NULL;
 
SELECT productid, onOrderqty  FROM `bigquerycourse.Purchasing.purchase_vendor`
WHERE productid is NOT NULL AND onOrderqty IS NULL;
```

**7. BETWEEN and NOT BETWEEN**

```sql
SELECT PurchaseOrderId, PurchaseOrderDetailID, DueDate FROM `bigquerycourse.Purchasing.purchase_order_detail` 
WHERE PurchaseOrderID BETWEEN 10 AND 2000;
 
SELECT PurchaseOrderId, PurchaseOrderDetailID, DueDate FROM `bigquerycourse.Purchasing.purchase_order_detail` 
WHERE DueDate NOT BETWEEN '2011-12-29' AND '2014-01-01';
```

**8. IN and NOT IN**

```sql
SELECT PurchaseOrderID, PurchaseOrderDetailId, ProductId FROM `bigquerycourse.Purchasing.purchase_order_detail`
/*
WHERE productid = 352 OR productid = 526 OR productid = 429 OR productid = 512 OR productid = 359
*/
WHERE productid IN (352,526,429,512,359);
```

**9. LIKE Operator**

```sql
SELECT * FROM `bigquerycourse.Purchasing.vendor`
WHERE AccountNumber LIKE '%001%';
 
SELECT * FROM `bigquerycourse.Purchasing.vendor`
WHERE AccountNumber LIKE '%00_';
 
SELECT * FROM `bigquerycourse.Purchasing.vendor`
WHERE Name LIKE '%\'%';
```

### Sorting and Grouping Records

**10. ORDER BY**

```sql
SELECT Purchaseorderid,purchaseorderdetailid FROM `bigquerycourse.Purchasing.purchase_order_detail`
WHERE PurchaseOrderID BETWEEN 2000 AND 2100
ORDER BY purchaseorderid ASC;
 
SELECT Purchaseorderid,purchaseorderdetailid FROM `bigquerycourse.Purchasing.purchase_order_detail`
WHERE PurchaseOrderID BETWEEN 2000 AND 2100
ORDER BY purchaseorderid ASC, purchaseorderdetailid DESC;

SELECT Purchaseorderid,purchaseorderdetailid FROM `bigquerycourse.Purchasing.purchase_order_detail`
WHERE PurchaseOrderID BETWEEN 2000 AND 2100
ORDER BY 1 ASC, 2 DESC;
```

**11. GROUP BY**

* Please note that in most of the cases the group by clause will come with aggregate functions.
* But that does not mean that it cannot exist without aggregate functions.

```sql
SELECT  purchaseorderid, sum(orderqty) AS total_orderQty
FROM 
  `bigquerycourse.Purchasing.purchase_order_detail`
WHERE purchaseorderid = 744
GROUP BY purchaseorderid;
 
SELECT  purchaseorderid, sum(orderqty) AS total_orderQty
FROM 
  `bigquerycourse.Purchasing.purchase_order_detail`
GROUP BY purchaseorderid;
 
SELECT  purchaseorderid, SUM(orderqty) AS total_orderQty, count(orderQty) as Count_OrderQty
FROM 
  `bigquerycourse.Purchasing.purchase_order_detail`
GROUP BY purchaseorderid;
 
SELECT  purchaseorderid, AVG(orderqty) AS avg_orderQty, count(orderQty) as Count_OrderQty
FROM 
  `bigquerycourse.Purchasing.purchase_order_detail`
GROUP BY purchaseorderid;
 
SELECT  purchaseorderid, MAX(orderqty) AS max_orderQty, count(orderQty) as Count_OrderQty
FROM 
  `bigquerycourse.Purchasing.purchase_order_detail`
GROUP BY purchaseorderid;
 
SELECT vendorid, max(taxamt)  FROM `bigquerycourse.Purchasing.purchase_order_header` 
WHERE vendorid = 1636
GROUP BY vendorid;
 
SELECT vendorid, max(taxamt)  FROM `bigquerycourse.Purchasing.purchase_order_header` 
GROUP BY vendorid;
 
SELECT vendorid, min(taxamt)  FROM `bigquerycourse.Purchasing.purchase_order_header` 
GROUP BY vendorid;
```

### GBQ SQL Funstions

**12. Numerical Functions**

```sql
SELECT ABS(-123.45)
 
SELECT ABS(+123.45)
 
SELECT CEILING(123.45)
 
SELECT FLOOR(123.00)
 
SELECT FLOOR(123.80)
 
SELECT RAND()
 
SELECT RAND() + 3;
 
SELECT CEILING(RAND() + 3);
 
SELECT ROUND(RAND() + 3);
 
SELECT ROUND(345.678,2);
 
SELECT ROUND(345.674,2);
 
SELECT ROUND(345.675,2);
```

**13. String Functions**

```sql
SELECT AccountNumber, Name, split(Name,' ') AS split_name FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT AccountNumber, Name, length(AccountNumber) AS length_accountNumber FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT AccountNumber, Name, concat(AccountNumber,Name) AS combine_account_with_name FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT AccountNumber, Name, concat(AccountNumber,' - ', Name) AS combine_account_with_name FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT AccountNumber, UPPER(Name) AS upper_name, concat(AccountNumber,' - ', Name) AS combine_account_with_name FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT lower(AccountNumber) AS lowerer_name, Name , concat(AccountNumber,' - ', Name) AS combine_account_with_name FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT lower(AccountNumber) AS lowerer_name, Name , concat(lower(AccountNumber),' - ', lower(Name)) AS combine_account_with_name FROM `bigquerycourse.Purchasing.vendor`;
```

**14. Ltrim, rtrim, trim and strpos string functions**

`strpos` return first occurance position of the given string.

```sql
SELECT "         Left Side Trimmed            ", ltrim("         Left Side Trimmed") AS trimleadingspaces;
 
SELECT "         Right Side Trimmed            ", rtrim("         Right Side Trimmed            ") AS trimtrailingspaces;
 
SELECT "         Trim both sides            ", trim("         Trim both sides            ") AS trimboth
 
SELECT AccountNumber, strpos(AccountNumber,'N') FROM `bigquerycourse.Purchasing.vendor` LIMIT 1000;
```

**15. replace, repeat, reverse and substr functions**

```sql
SELECT AccountNumber, replace(AccountNumber,'FIRST','LAST') AS replaceString FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT AccountNumber, repeat(AccountNumber,2) AS repeatString FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT AccountNumber, reverse(AccountNumber) AS reverstring FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT AccountNumber, substr(AccountNumber,3) AS substrstring FROM `bigquerycourse.Purchasing.vendor`;
 
SELECT AccountNumber, substr(AccountNumber,3,4) AS substrstring FROM `bigquerycourse.Purchasing.vendor`;
```

* `substr(AccountNumber,3,4)`: start from 3rd position, take 4 characters -> means, 3rd to 6th position

**16. Date Functions in GBQ**

Date Part -> Day, Week, Month, Quarter, Year

```sql
SELECT OrderDate, shipDate, date_add(OrderDate, INTERVAL 5 DAY) AS add_days FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT OrderDate, shipDate, date_add(OrderDate, INTERVAL 2 WEEK) AS add_days FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT OrderDate, shipDate, date_add(OrderDate, INTERVAL 1 MONTH) AS add_days FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT OrderDate, shipDate, date_add(OrderDate, INTERVAL 2 QUARTER) AS add_quarters FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT OrderDate, shipDate, date_add(OrderDate, INTERVAL 2 YEAR) AS add_quarters FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT OrderDate, shipDate, date_diff(ShipDate, OrderDate, DAY) AS diff_days FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT OrderDate, shipDate, date_diff(ShipDate, OrderDate, MONTH) AS diff_days FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT CURRENT_DATE() AS current_default_date;
```

```sql
SELECT OrderDate, shipDate, extract(DAY FROM OrderDate) AS extract_day FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT OrderDate, shipDate, extract(WEEK FROM OrderDate) AS extract_weeknumber FROM `bigquerycourse.Purchasing.purchase_order_header`;
 
SELECT date(2020,08,22) AS date_form;
 
SELECT date(DATETIME "2020-08-22 21:27:44") AS date_datetime;
```

**17. Having Clause**

```sql
SELECT vendorid, max(taxamt)  FROM `bigquerycourse.Purchasing.purchase_order_header` 
GROUP BY vendorid
HAVING max(taxamt) > 1555;
 
SELECT vendorid, max(taxamt)  FROM `bigquerycourse.Purchasing.purchase_order_header` 
WHERE vendorid > 1400
GROUP BY vendorid
HAVING max(taxamt) > 1555;
 
SELECT vendorid, max(taxamt)  FROM `bigquerycourse.Purchasing.purchase_order_header` 
WHERE vendorid > 1400
GROUP BY vendorid
HAVING max(taxamt) > 1555;
 
SELECT vendorid, max(taxamt)  FROM `bigquerycourse.Purchasing.purchase_order_header` 
WHERE vendorid > 1400
GROUP BY vendorid
HAVING max(taxamt) > 3000
ORDER BY vendorid DESC;
```

### JOINs and Subqueries

**18. Subquery**

```sql
SELECT purchaseorderid, Employeeid, OrderDate FROM `bigquerycourse.Purchasing.purchase_order_header`
WHERE purchaseorderid =
( SELECT purchaseorderid from Purchasing.purchase_order_detail
  WHERE purchaseorderdetailid = 8842 AND productid = 881);
 
SELECT purchaseorderid, Employeeid, OrderDate FROM `bigquerycourse.Purchasing.purchase_order_header`
WHERE purchaseorderid =
( SELECT purchaseorderid from Purchasing.purchase_order_detail
  WHERE purchaseorderdetailid = 8842);
 
SELECT purchaseorderid, Employeeid, OrderDate FROM `bigquerycourse.Purchasing.purchase_order_header`
WHERE purchaseorderid IN
( SELECT purchaseorderid from Purchasing.purchase_order_detail
  WHERE purchaseorderdetailid > 8842);
```

**19. UNION ALL Operators**

```sql
SELECT purchaseorderid FROM `bigquerycourse.Purchasing.purchase_order_header`
   UNION ALL
SELECT purchaseorderid FROM `bigquerycourse.Purchasing.purchase_order_detail`;
 
SELECT businessentityid FROM `bigquerycourse.Purchasing.purchase_vendor`
   UNION ALL
SELECT businessentityid FROM `bigquerycourse.Purchasing.vendor`   
ORDER BY businessentityid DESC;
```

**20. INTERSECT DISTINCT**

**Returns only the unique rows that are present in both query result sets.**

```sql
SELECT businessentityid FROM `bigquerycourse.Purchasing.purchase_vendor`
   INTERSECT DISTINCT
SELECT businessentityid FROM `bigquerycourse.Purchasing.vendor`;
```

**21. EXCEPT DISTINCT**

**Returns only the unique rows found in the first query result set but absent from the second.**

```sql
SELECT businessentityid FROM `bigquerycourse.Purchasing.purchase_vendor`
   EXCEPT DISTINCT
SELECT businessentityid FROM `bigquerycourse.Purchasing.vendor`;  
```

**22. INNER JOIN**

```sql
SELECT pv.businessentityid,productid, accountnumber
FROM Purchasing.purchase_vendor pv
   INNER JOIN
Purchasing.vendor v
ON pv.BusinessEntityID = v.BusinessEntityId;
 
SELECT pv.businessentityid,productid, accountnumber
FROM Purchasing.purchase_vendor pv
   JOIN
Purchasing.vendor v
ON pv.BusinessEntityID = v.BusinessEntityId;
```

**23. LEFT JOIN and RIGHT JOIN**

```sql
SELECT c.CustomerId, pcc.BusinessEntityId, c.StoreID,pcc.CreditcardId
FROM Purchasing.customer c
  LEFT JOIN / RIGHT JOIN
Purchasing.person_credit_card pcc
ON c.customerid = pcc.BusinessEntityID;
 
SELECT c.CustomerId, pcc.BusinessEntityId, c.StoreID,pcc.CreditcardId
FROM Purchasing.customer c
  LEFT OUTER JOIN
Purchasing.person_credit_card pcc
ON c.customerid = pcc.BusinessEntityID;
 
SELECT c.CustomerId, pcc.BusinessEntityId, c.StoreID,pcc.CreditcardId
FROM Purchasing.customer c
  LEFT OUTER JOIN
Purchasing.person_credit_card pcc
ON c.customerid = pcc.BusinessEntityID
WHERE pcc.BusinessEntityId IS NOT NULL;
```

**24. FULL JOIN / FULL OUTER JOIN**

```sql
SELECT c.CustomerId, pcc.BusinessEntityId, c.StoreID,pcc.CreditcardId
FROM Purchasing.person_credit_card pcc
  FULL JOIN
Purchasing.customer c
ON c.customerid = pcc.BusinessEntityID;
```

**25. CROSS JOIN**

```sql
SELECT
  v.BusinessEntityId,
  Name,
  ProductId
FROM
  `bigquerycourse.Purchasing.vendor` v
CROSS JOIN
  `bigquerycourse.Purchasing.purchase_vendor` pv;
```

### Derived Tables and CTE

**26. Derived Table**

> A Derived Table is a subquery used specifically in the FROM clause of an outer query. It acts as a temporary, virtual table that exists only for the duration of that specific query execution.

```sql
SELECT purchaseorderid, duedate
FROM
(
SELECT purchaseorderid, duedate, orderqty FROM `bigquerycourse.Purchasing.purchase_order_detail`
) AS po_duedate;
```

**27. CTE**

```sql
SELECT BusinessEntityId, CONCAT(FirstName,' ',MiddleName,' ',LastName) AS FullName
FROM persondet.person
ORDER  BY BusinessEntityID;
 
WITH personcte AS
(
SELECT BusinessEntityId, CONCAT(FirstName,' ',MiddleName,' ',LastName) AS FullName
FROM persondet.person
ORDER  BY BusinessEntityID
)
SELECT * FROM personcte;
```

### Arrays, UNNEST, Struct, etc

**28. Arrays in GBQ**

> Ordered list of collection of data of same data type

```sql
SELECT [1,2,3] AS NumericArray;
 
SELECT ["Welcome","To","BigQuery"] AS StringArray;
 
SELECT 'Computer science' AS Stream, 'Sandeep' AS Name, ['Mainframe','SQL','Java','C','Big Data'] AS Courses
 UNION ALL
SELECT 'Electronics'  AS Stream, 'Rick' AS Name, ['Microprocessor','VLSI','Embedded'] AS Courses
 UNION ALL
SELECT 'Civil'  AS Stream, 'Angelina' AS Name, ['Strucural Engineering','Hydraulics'] AS Courses;
```

**29. UNNEST in Arrays**

> Converts array items into seperate rows

```sql
SELECT ["Welcome","To","BigQuery"] AS StringArray;
 
SELECT X
FROM
(
SELECT ["Welcome","To","BigQuery"] AS StringArray
),
UNNEST(StringArray) AS X;
```

**30. GENERATE_ARRAY() Function**

> To generate sequential array with fixed step value

```sql
GENERATE_ARRAY(StartValue, EndValue, StepValue)

SELECT GENERATE_ARRAY(20,5,-3) AS ga;
```

**31. GENERATE_DATE_ARRAY() Function**

> To generate an array with date field

```sql
GENERATE_DATE_ARRAY(StartDate, EndDate, INTERVAL N date_part)

SELECT GENERATE_DATE_ARRAY('2020-05-24','2020-11-28',INTERVAL 2 DAY) AS ga;
 
SELECT GENERATE_DATE_ARRAY('2020-05-24','2020-11-28',INTERVAL 1 WEEK) AS ga;
 
SELECT GENERATE_DATE_ARRAY('2020-05-24','2020-11-28',INTERVAL 2 WEEK) AS ga;
 
SELECT GENERATE_DATE_ARRAY('2020-05-24','2020-11-28',INTERVAL 2 MONTH) AS ga;
 
SELECT GENERATE_DATE_ARRAY('2024-05-24','2020-11-28',INTERVAL 2 QUARTER) AS ga;
 
SELECT GENERATE_DATE_ARRAY('2020-05-24','2024-11-28',INTERVAL 2 YEAR) AS ga;
```

**32. ARRAY_LENGTH() Function**

> To get length of the array 

```sql
SELECT ARRAY_LENGTH([1,2,3]) AS lengthofArray;
 
SELECT ARRAY_LENGTH(["Welcome","To","BigQuery","Again"]) AS LengthOfArray;
```

**33. STRUCT Function**

> A STRUCT (short for Structure) in SQL is a complex data type that allows you to store multiple related fields inside a single column.

Imagine a table for Users.

* Without Struct: You have separate columns: UserID, Name, Street, City, ZipCode.
* With Struct: You have columns UserID, Name, and a single Address column. Inside Address, you bundle Street, City, and ZipCode together.

```sql
SELECT ("Sandeep",787878788) AS NameDetails  #This will work fine
 
#SELECT ("Sandeep" AS Name,787878788 AS ID) AS NameDetails #This will fail as you have to use either struct or subquery
 
SELECT STRUCT("Sandeep" AS Name,787878788 AS ID) AS NameDetails;
 
SELECT NameDetails.ID FROM (SELECT STRUCT("Sandeep" AS Name,787878788 AS ID) AS NameDetails);
```

### Create Tables

```sql
CREATE TABLE createTableUsingDDL.stores 
  ( storeID INT64 OPTIONS(description="StoreId contains Integer value"),
    storeName String,
    dateFounded DATE,
    address STRUCT<streetDetails ARRAY<string>, laneNumber INT64, isstoreActive BOOL> 
  )

CREATE TABLE IF NOT EXISTS createTableUsingDDL.stores 
  ( storeID INT64 OPTIONS(description="StoreId contains Integer value"),
    storeName String,
    dateFounded DATE,
    phoneNumber String,
    address STRUCT<streetDetails ARRAY<string>, laneNumber INT64, isstoreActive BOOL> 
  );
 
CREATE OR REPLACE TABLE createTableUsingDDL.stores 
  ( storeID INT64 OPTIONS(description="StoreId contains Integer value"),
    storeName String,
    dateFounded DATE,
    phoneNumber String,
    address STRUCT<streetDetails ARRAY<string>, laneNumber INT64, isstoreActive BOOL> 
  );
```

### Views

>  A Virtual Table used to Hide or Protect the Main table

```sql
CREATE VIEW viewName as Query;

CREATE VIEW IF NOT EXISTS ArrayDemo.vw_student_details AS
SELECT "Computer Science" AS Stream,
[STRUCT("Sandeep" AS Name,"S12345" AS RollNumber,['HTML','CSS','Javascript','Mainframe'] AS Courses),

STRUCT("Mike" AS Name,"S23456" AS RollNumber,['C','C++','Java'] AS Courses)] 

AS StudentDetails
```

Delete a view:

```sql
DROP VIEW ArrayDemo.vw_student_details;
```

### INSERT Records

```sql
INSERT INTO createTableUsingDDL.stores(storeId,storeName,datefounded,phoneNumber,streetdetails,lanenumber,isstoreactive) VALUES
(123,"ABC Stores",'2020-05-01','+91-6876876864',(['Park Street 1st Floor', 'Park Street 3rd Floor', 'Park Street 5th Floor'],45,TRUE)),
(234,"XYZ Stores",'2020-05-01','+91-899879874',(['Dummy Street 1st Floor', 'Dummy Street 3rd Floor', 'Dumy Street 5th Floor'],75,FALSE));
 
INSERT INTO createTableUsingDDL.stores VALUES
(123,"ABC Stores",'2020-05-01','+91-6876876864',(['Park Street 1st Floor', 'Park Street 3rd Floor', 'Park Street 5th Floor'],45,TRUE)),
(234,"XYZ Stores",'2020-05-01','+91-899879874',(['Dummy Street 1st Floor', 'Dummy Street 3rd Floor', 'Dumy Street 5th Floor'],75,FALSE));
```