# DB Basics
## Database
> A database is a collection of data that is systematically organized, integrated, and managed for the purpose of sharing and use by multiple people.
### DBMS (Database Management System)
* RDBMS
* Database management system based on relational model
### Relational Database (RDBMS)
* Consists of a collection of tables organized as relationships with columns and rows
* Each column records a specific type of data
* Table rows are collections of values related to each object/entity
### RDBMS vs NOSQL
\
### Basic Terms
#### Schema
* An overall specification regarding the structure and constraints (structure, representation method, relationships, etc.) of data in a database
#### Table (Relation)
* A collection of data elements organized using a model of columns and rows
#### PK
* A unique value for each row that can uniquely identify stored records
\
## SQL (Structured Query Language)
> A language specifically designed to manage data in RDBMS
### Creating Tables
```sql
CREATE TABLE table (
column1 datatype [constraints],
column2 datatype [constaraints],
);
```
ex)
```sqlite
CREATE TABLE contacts (
contact_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
phone TEXT NOT NULL UNIQUE
);
```
\
### Rename Table
```sqlite
ALTER TABLE table_name
RENAME TO new_table_name;
```
\
### Data Types (in `SQLite`)
\
### SELECT
* `DISTINCT`
* Retrieve without duplicates
```sqlite
sqlite> SELECT DISTINCT waypoint FROM flights;
waypoint
----------
Beijing
Moscow
Beijing
```
* `COUNT()`
* Retrieve specific count
* `LIKE`
* Wild cards
* `%` : There may or may not be a string
* `_` : There must be exactly one character
* `LIMIT ... OFFSET`
* Limit count .... up to what number
* ex)
```sql
-- Returns the first 10 rows
SELECT * FROM test LIMIT 10;
-- The above SQL and the below SQL have the same result
SELECT * FROM test LIMIT 10 OFFSET 0;
-- Returns 10 rows starting from the 11th.
SELECT * FROM test LIMIT 10 OFFSET 10;
```
```sql
.schema ____
```
\
### CREATE
```sql
sqlite> CREATE TABLE flights(
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> flight_num TEXT NOT NULL,
...> departure TEXT NOT NULL,
...> waypoint TEXT NOT NULL,
...> arrival TEXT NOT NULL,
...> price INTEGER NOT NULL
...> );
```
#### IF NOT EXISTS
* Create only when the table does not exist
```sqlite
sqlite> CREATE TABLE IF NOT EXISTS flights(
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> flight_num TEXT NOT NULL,
...> departure TEXT NOT NULL,
...> waypoint TEXT NOT NULL,
...> arrival TEXT NOT NULL,
...> price INTEGER NOT NULL
...> );
```
#### View Tables
```sqlite
sqlite> .tables
```
\
### INSERT
```sql
lite> INSERT INTO flights(flight_num, departure, waypoint, arrival, price) VALUES('RT9122', 'Madrid', 'Beijing', 'Incheon', 200);
id flight_num departure waypoint arrival price
---------- ---------- ---------- ---------- ---------- ----------
1 RT9122 Madrid Beijing Incheon 200
```
\
### UPDATE
```sqlite
sqlite> UPDATE flights SET waypoint = 'Tokyo' WHERE flight_num = 'SQ0972';
```
\
### DELETE
```sqlite
sqlite> DROP TABLE flights;
sqlite> .tables
sqlite> select*from flights;
Error: no such table: flights
```
\
### Select exercise
#### 1. Retrieve all data from the flights table
```sqlite
sqlite> SELECT * FROM flights;
id flight_num departure waypoint arrival price
---------- ---------- ---------- ---------- ---------- ----------
1 RT9122 Madrid Beijing Incheon 200
2 XZ0352 LA Moscow Incheon 800
3 SQ0972 London Beijing Sydney 500
```
#### 2. Retrieve all waypoints
```sqlite
sqlite> SELECT waypoint FROM flights;
waypoint
----------
Beijing
Moscow
Beijing
```
#### 3. Retrieve id and flight\_num of flights with airfare less than 600
```sqlite
sqlite> SELECT id, flight_num from flights WHERE price<600;
id flight_num
---------- ----------
1 RT9122
3 SQ0972
```
#### 4. Retrieve departure of flights with destination Incheon and price 500 or above
```sqlite
sqlite> SELECT departure FROM flights WHERE arrival = 'Incheon' AND 500 <= price;
departure
----------
LA
```
#### 5. Retrieve id and flight\_num of flights where the numeric part starts with 0 and ends with 2, and the waypoint is Beijing
> ver 1
```sqlite
sqlite> SELECT id, flight_num FROM flights where waypoint = 'Beijing' AND flight_num LIKE '__0__2';
id flight_num
---------- ----------
3 SQ0972
```
> ver 2
```sqlite
sqlite> SELECT id, flight_num FROM flights where waypoint = 'Beijing' AND flight_num LIKE '__0%2';
id flight_num
---------- ----------
3 SQ0972
```
> ver 3
```sql
sqlite> SELECT id, flight_num FROM flights where waypoint = 'Beijing' AND flight_num LIKE '%0%2';
id flight_num
---------- ----------
3 SQ0972
```
### .read
```sqlite
sqlite> .read hello_user.sql
1,"정호","유",40,"전라북도",016-7280-2855,370
2,"경희","이",36,"경상남도",011-9854-5133,5900
3,"정자","구",37,"전라남도",011-4177-8170,3100
4,"미경","장",40,"충청남도",011-9079-4419,250000
5,"영환","차",30,"충청북도",011-2921-4284,220
6,"서준","이",26,"충청북도",02-8601-7361,530
7,"주원","민",18,"경기도",011-2525-1976,390
8,"예진","김",33,"충청북도",010-5123-9107,3700
9,"서현","김",23,"제주특별자치도",016-6839-1106,43000
10,"서윤","오",22,"충청남도",011-9693-6452,49000
```
\
`+`
### Pretty Display in CLI
```sqlite
sqlite> .headers on
sqlite> .mode column
```
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://chloe-codes1.gitbook.io/til/db/db-101/01_db_basics.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.