Introduction to SQL and Its Applications

Study Notes

SQL and its Importance

SQL (Structured Query Language) is used to manage and manipulate relational databases.
It provides commands for creating, reading, updating, and deleting data.
Most relational database management systems support SQL, making it a universal language for database interaction.
Essential for data analysis: allows users to perform queries to extract meaningful insights from datasets.
Designed to handle large volumes of data efficiently.
Easy to learn and use, even without extensive technical knowledge.

Real-World Applications

Business intelligence: companies use SQL to generate reports and analyze business performance.
Finance: banks and financial institutions use SQL for transaction processing and risk management.
Healthcare: used for managing medical records and patient data.
E-commerce: used for inventory management, managing customer data, and tracking sales.

Main Data Types

Data Type	Description	Example
INT	Whole numbers	42
FLOAT	Floating-point numbers (approximate)	3.14
DOUBLE	Double-precision floating-point	2.718281828459
DECIMAL (p,s)	Fixed-point numbers with precision and scale	123.45
VARCHAR (n)	Variable-length text up to n characters	"John Doe"
CHAR (n)	Fixed-length text of n characters	"A"
TEXT	Large amount of text	"This is a long text"
DATE	Date value (YYYY-MM-DD)	2024-07-09
TIME	Time value (HH:MM)	14:30:00
DATETIME	Date and time value	2024-07-09 14:30:00
TIMESTAMP	Date and time, auto-updated	2024-07-09 14:30:00
BLOB	Binary Large Object	Binary data

Creating Tables

Tables consist of columns (defining data type) and rows (containing actual data).
Basic syntax of CREATE TABLE:

CREATE TABLE TableName ( Column1 DataType1, Column2 DataType2, );


### Selecting Records

- Retrieving data from a table using a `SELECT` query.
- Basic syntax of `SELECT` query:
  ```sql
SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.common column = table2.common column
WHERE condition;

Inserting Records

Inserting data into a table.
Basic syntax of INSERT query:

INSERT INTO table name (column1, column2, ...) VALUES (value1, value2, ...);


### Updating Records

- Updating data in a table using an `UPDATE` query.
- Basic syntax of `UPDATE` query:
  ```sql
UPDATE table name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Deleting Records

Deleting data from a table using a DELETE query.
Basic syntax of DELETE query:

DELETE FROM table name WHERE condition;


### Altering Tables

- Adding columns to a table using an `ALTER` query.
- Basic syntax of `ALTER` query:
  ```sql
ALTER TABLE table name
ADD column name datatype;

Dropping Tables

Deleting an entire table using a DROP query.
Basic syntax of DROP query:

DROP TABLE table name;


### Understanding Joins

- `JOINS` combine rows from two or more tables based on a related column.
- Different types of joins (Inner, Left Outer, Right Outer) exist, each returning specific combinations of rows.

### Using Simple Queries for Data Analysis

- Queries cover basic tasks like retrieving data and filtering results.

### Using Aggregations

- `AGGREGATIONS` summarize data, derive insights, and perform calculations.  Examples include `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.


### Filtering Conditions

- Use `WHERE` clause to filter results based on specific conditions.

### Using Operators

- Arithmetic, comparison, logical operators manipulate data and evaluate conditions.

### Using String Functions

- Functions for manipulating string data such as concatenation, extraction, formatting, and searching.


### Using Subqueries


- Subqueries are queries inside other queries, enclosed in parentheses.
- Used for complex data retrieval and manipulation.