# SQL

## Overview

<figure><img src="https://1374779285-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MFW3x2aqEO8GF2kr3VU%2Fuploads%2FwDT8KvcSIjwqVZV98ldq%2Fimage.png?alt=media&#x26;token=303a530c-6a94-4663-ab75-94a2289debe9" alt=""><figcaption></figcaption></figure>

## Create Table&#x20;

```sql
CREATE TABLE student_info(
	id VARCHAR(100) NOT NULL,
	// allow Chinese Character
	name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	age int NOT NULL,
	class VARCHAR(100),
	PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT charset utf8mb4 COLLATE utf8mb4_unicode_ci;
```

* Engine : define storage engines to decide how and where the table data is stored (Default = InnoDB)
* Charset: define the character Sets to encode the characters (Default = utf8mb4)
* Collate: define how to the field do comparison or ordering (ORDER BY, WHERE will be affected)

## Key

* `key` means a constraint imposed on the behaviour of the column
* Primary Key(PK): a unique key that represent the row (usually a random number) and include unique and non-null constraint and primary index
* Foreign Key(FK): a key that referencing the primary of other table in order to make sure the data in tables are valid and consistent
* Composite Primary Key: combine 2 columns into a primary key

## Constraint

### Introduction

* Constraints are the rules that we can apply on the type of data in a table. That is, we can specify the limit on the type of data that can be stored in a particular column in a table using constraints.

### Type

* **NOT NULL**: This constraint tells that we cannot store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column any more.

```sql
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);
```

* **UNIQUE**: This constraint when specified with a column, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated.

```sql
CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
```

* **CHECK**: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition.

```sql
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18)
);
```

## Date

### Introduction

* Date can be used to search for the range of data
* If a String is entered into a column where its type is a date/ datetime, its format must be 'Year-Month-Day' or 'Year/Month/Day'

### Method

#### Date Format

* Structure : Date\_Format(date, format) and output as a string

```sql
DATE_FORMAT(curdate(), '%d/%m/%Y') 
```

#### String to Date

* Convert the String to date, Structure: STR\_TO\_DATE(input, the input format) &#x20;

```sql
Str_to_date('5/7/2020','%e/%e/%Y') 
// output : 2020-07-05
```

#### &#x20; Date Add/Min

```sql
SELECT "2017-06-15" + INTERVAL 10 DAY;  // 2017-06-25
SELECT "2017-06-15" - INTERVAL 10 DAY;  // 2017-06-05
```

#### Date Diff

```sql
SELECT datediff('2015-06-04','2015-07-04') // -30
SELECT datediff('2015-06-04','2015-05-04') // 31
```

#### Extract

* Obtain the part of date as a string or number

```sql
EXTRACT(YEAR_MONTH FROM '2015-7-15') // 201507
SELECT MONTHNAME("2017-06-15"); // June
```

#### Get the day of week

```sql
SELECT DAYOFWEEK("2017-06-15"); // 5 which means Thursday
SELECT DayName("2017-06-15"); // Thursday
```

## String

### Method

#### Insert

```sql
SELECT insert("hitest", 3, 0, "test");    // hitesttest
SELECT insert("hitest", 3, 1, "example"); // hiexampleest
SELECT insert("hitest", 3, 9, "example"); // hiexample
```

#### Concat

```sql
SELECT CONCAT("SQL ", "Tutorial ", "is ", "fun!"); // SQL Tutorial is fun
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") // SQL-Tutorial-is-fun!
```

#### Sub String

```sql
SELECT left("hitest", 2); // hi
SELECT right("hitest",2); // st
SELECT SUBSTR("test test", 6, 4); //test
```

#### TRIM

```sql
SELECT LTRIM(" test "); //test_
SELECT RTRIM(" test "); //_test
SELECT TRIM(" test "); //test
```

#### String Comparison

```sql
SELECT strcmp("test", "test"); // 0
SELECT strcmp("test123", "test"); // 1
SELECT strcmp("test", "test123"); // -1
```

## Number

### Method

#### ABS

```sql
SELECT ABS(-1) // 1
SELECT ABS(1)  // 1
```

#### Round

```sql
SELECT Ceil(25.25) // 26
SELECT Floor(25.75) // 25
SELECT ROUND(135.775, 0) // 136
SELECT ROUND(135.775, 2) //135.78
```

#### +/-

```sql
SELECT 25-2 // 23
SELECT 25+3 // 28
```

#### Random Number

```sql
SELECT Rand() 
```

## Case Study

### Case 1

```sql
Select * FROM Estate AS e 
JOIN District d ON e.district = d.pk 
GROUP BY d.pk 
ORDER BY 
Case 
    WHEN convert (d.name using big5) LIKE CONCAT(Char(63),'%') then 1 else 0 end
, d.willDelivery DESC;    
```

### Case 2

```sql
INSERT INTO "PermissionGroupFeature" ("groupTypes", "featureName", "featureKey", "isBasic") 
SELECT ARRAY['BOT', 'FLOW', 'INSIGHT']::"GroupType"[], 'Audit Log' , 'audit-log', false WHERE NOT EXISTS
(SELECT 1 FROM "PermissionGroupFeature" WHERE "featureKey" = 'audit-log');
```

## References

{% embed url="https://www.guru99.com/database-normalization.html" %}

{% embed url="https://www.freecodecamp.org/news/database-indexing-at-a-glance-bb50809d48bd/" %}
