Summary of "SQL - Complete Course in 9 Hours | SQL One Shot | SQL Full Course by Sumit Sir"

Summary of SQL Complete Course by Sumit Sir

What is a Database?
- A database is a collection of data stored in tables.
- Tables organize data in rows and columns, similar to Excel spreadsheets.
- Databases are preferred over spreadsheets for handling large volumes of data and multiple related datasets.
Types of Databases:
- Relational Databases: Store data in tables with rows and columns; tables are related via keys (e.g., MySQL, PostgreSQL, SQL Server).
- Non-Relational (NoSQL) Databases: Use key-value stores, document stores, graph databases; tables are independent (e.g., MongoDB, Cassandra).
- This course focuses on relational databases, specifically MySQL.
What is SQL?
- SQL (Structured Query Language) is the language used to interact with relational databases.
- SQL is used for querying, inserting, updating, and deleting data.
- SQL syntax is largely consistent across relational databases with minor variations.

Using Online IDE (Goom IDE) for Practice:
- Recommended to avoid local installation complexities.
- Users can create containers to run MySQL commands via browser.
Basic SQL Commands Covered:
- SHOW DATABASES; — Lists all databases.
- CREATE DATABASE dbname; — Creates a new database.
- DROP DATABASE dbname; — Deletes a database.
- USE dbname; — Selects a database to work on.
- SHOW TABLES; — Lists tables in the current database.
- DESCRIBE tablename; or DESC tablename; — Shows table structure.

Creating and Dropping Tables:

CREATE TABLE employee (
  name VARCHAR(50),
  age INT,
  salary INT
);

Dropping table: DROP TABLE tablename;

Data Types:
- INT for integers.
- VARCHAR(n) or VARCAHR(n) for strings (up to n characters).
- TIMESTAMP for date/time.
- Importance of matching data types during insertions.

CRUD Explained:
- Create: Insert new records (INSERT INTO).
- Read: Retrieve records (SELECT).
- Update: Modify existing records (UPDATE).
- Delete: Remove records (DELETE).
Insert Statements:
```
INSERT INTO employee (first_name, last_name, age, salary) VALUES ('John', 'Doe', 30, 50000);
```
- Syntax with explicit column names is recommended.
- Handling NULL values and default values.
- Inserting multiple rows in one query using comma-separated value lists.
- Escaping single and double quotes in string values using backslash \ or alternate quotes.
Update Statements:
```
UPDATE employee SET salary = 60000 WHERE first_name = 'John';
```
- Importance of WHERE clause to avoid updating all rows unintentionally.
- Using logical operators (AND, OR) in WHERE clause.
Delete Statements:
```
DELETE FROM employee WHERE id = 10;
```
- Deleting all rows by omitting WHERE clause.
- Caution advised to always verify with SELECT before deleting.
Difference Between DELETE and TRUNCATE:
- DELETE is a DML command, deletes rows one by one, can be rolled back.
- TRUNCATE is a DDL command, drops and recreates the table, faster but cannot be rolled back.

Primary Key:
- Uniquely identifies each row.
- Cannot be NULL or duplicated.
- Usually an integer with AUTO_INCREMENT.
- Only one primary key per table (can be composite key).
Unique Key:
- Ensures column values are unique.
- Can accept multiple NULLs (in MySQL).
- Multiple unique keys allowed per table.
Foreign Key:
- Enforces referential integrity by linking columns between tables.
- Child table column references parent table primary key.
- Prevent