27. SQL

BootCamp by Dr.Angela

1. SQL Commands: CREATE TABLE and INSERT Data

• CRUD Operations : Create(INSERT), Read(SELECT), Update(UPDATE), Delete/Destroy(DELETE)
• Useful Resources
  • SQL Tutorial: https://www.w3schools.com/sql/
  • SQL Data Types: https://www.w3schools.com/sql/sql_datatypes.asp
  • SQL Primary Key: https://www.w3schools.com/sql/sql_primarykey.asp
  • Online SQL Playground: https://sqliteonline.com/
• Creating a Table : ex) CREATE TABLE products ( id INT NOT NULL, name STRING, price MONEY, PRIMARY KEY (id) );
• Primary Key : uniquely identifies each row in a table
  • ex) id INT NOT NULL, PRIMARY KEY (id)
• Inserting Data : Insert values into all columns
  • ex) INSERT INTO products VALUES (1, 'Pen', 1.20);
  • Insert values into specific columns : ex) INSERT INTO products (id, name) VALUES (2, 'Pencil');

2. SQL Commands: READ, SELECT, and WHERE

• Reading Data
  • Retrieve all columns from a table, ex) SELECT * FROM products;
  • Retrieve specific data using a condition, ex) SELECT * FROM products WHERE id = 1;
• WHERE Clause : Used to filter records based on specific conditions.

3. Updating Values and Modifying Tables

• Updating Existing Data
  • ex) UPDATE products SET price = 1.00 WHERE id = 1;
  • SET : specifies the new value.
  • WHERE : determines which row(s) will be updated.
• Adding a New Column
  • ex) ALTER TABLE products ADD stock INT;
  • ALTER TABLE : modifies an existing table structure.

4. SQL Commands: DELETE

• Deleting Data
  • ex) DELETE FROM products WHERE id = 2;
• Removes rows that match the condition.
• Always use a WHERE clause unless you intend to delete all records.

5. Understanding SQL Relationships, Foreign Keys, and INNER JOINs

• Foreign Keys : Creates a relationship between two tables, Helps maintain data integrity
  • https://www.w3schools.com/sql/sql_foreignkey.asp
• INNER JOIN : Used to combine related data from multiple tables.
  • ex) SELECT orders.order_number, customers.first_name, customers.last_name, customers.address FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
• How INNER JOIN Works : Only matching records from both tables are returned.
  • https://www.w3schools.com/sql/sql_join_inner.asp