# SQL

Structured Query Language(SQL) is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert, etc. to carry out the required tasks.

These SQL commands are mainly categorized into four categories as

* Data definition language (DDL): `CREATE`, `DROP`, `ALTER`, `ADD`, `CHECK`
* Data Manipulation language (DML): `SELECT`, `INSERT INTO`, `UPDATE`, `DELETE`, `SET`
* Data Authorization language (DAL): `GRANT`, `REVOKE`
* Data control language (DCL):`CREATE INDEX`

See also <https://www.w3schools.com/SQL/>

The most commonly used are explained below

### CREATE A TABLE

A table is created with the statement CREATE TABLE \<tablename> ( \<attributename> \<datatype>, \<attributename> \<datatype>.....)&#x20;

```sql
CREATE TABLE Users( 
  name VARCHAR(128), 
  email VARCHAR(128)
)

```

There are many more configurations for a table, such as auto-increment keys, unique conditions, or not null conditions. These are required when you want to relate data tables to each other, which is often the case in a relational database. In the example below an automated index is created as a primary key and the pubsource\_id is the foreign key referring to the primary key in another table. Furthermore, there is a restriction on uniqueness for the title of the Article. &#x20;

```sql
CREATE TABLE Article (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    pubsource_id  INTEGER,
    title   TEXT UNIQUE
);
```

### SQL INSERT INTO .... VALUES

The Insert statement inserts a row into a table, for each attribute, a value is given

```sql
INSERT INTO Users (name, email) VALUES ('funny', 'f.feenstra@pl.hanze.nl')

```

### SQL UPDATE .... SET

The `UPDATE` statement allows the updating of a field with a `WHERE` clause

```sql
UPDATE Users SET name='fenna' WHERE email='f.feenstra@pl.hanze.nl'

```

### SQL SELECT ... FROM

The `SELECT`  statement retrieves a group of records - you can either retrieve all the records or a subset of the records with a `WHERE` clause. The star `*` means all the attributes

```sql
SELECT * FROM Users
SELECT * FROM Users WHERE email='f.feenstra@pl.hanze.nl'
SELECT name FROM Users
```

### SQL DELETE FROM

The statement `DELETE`Deletes a row in a table based on selection criteria

```sql
DELETE FROM Users WHERE email='f.feenstra@pl.hanze.nl'

```

### Sorting with ORDER BY

You can add an `ORDER BY` clause to `SELECT` statements to get the results sorted in ascending or descending order

```sql
SELECT * FROM Users ORDER BY name DESC
```

## Mind you! You have to commit changes

Since more clients can assess and modify the data at the same time it is important to control these changes. Supose you want to retrieve data but some other client wants to delete the same data? In order to control this data is modified on the client side and only if all changes are completed, then use `connection.commit()` to **persist the changes in the database** to maintain the data integrity and consistency. If commit() is not called, the effect of any data manipulation will get lost.

If any of the operations fail inside of a running transaction, you can **revert the changes** made by all of the previous operations using a `connection.rollback()`

## Pandas and SQL

Pandas has a `read_sql_query` function that can read tables from querys. It needs the SQL query in the data manipulation language and a connection.

```python
import pandas as pd
df = pd.read_sql_query('SELECT * FROM Artist', conn)
print(type(df))
df
```

|   | id | name  |
| - | -- | ----- |
| 0 | 1  | Opeth |
| 1 | 2  | Drake |