Please enable JavaScript.

Coggle requires JavaScript to display documents.

Module 3, Intro to Databases: A database stores a collection of…

- - - - The type of databases we'll be focusing on in this course are relational databases, which utilize the table format of organizing data. Relational databases are common and widely used.
      - A relational database can consist of multiple tables, where each table represents a single entity or resource type. For example, a Contacts table and a Conversations table might be stored in a single database to organize the data related to contacts, and the conversations between them. Each table would include a data record on each row, and the attribute values for that record in the corresponding columns.
      - Relational databases make it possible to specify relationships between data elements, such as linking a specific set of contacts to a specific conversation wihtout repeating data. There is a lot you can learn about relational databases, but this course will focus on the fundamental concepts you'll need in order to understand concepts and build something using a basic database.
- - - - Persistent Data
        Once a database has been opened in your command prompt, you can enter SQL commands that will be executed on that database. Any operations you perform using SQL statements in this window, such as inserting or deleting data records to a table, will permanently affect your database and the changes will be saved to your database file.
      - Non-Persistent
        If you simply wish to test or practice, you can instead open a non-persistent database in your SQLite shell. In this case, you can enter SQL statements and work with a temporary database that will not be saved once you exit the window.
        In that case just run: sqlilte3
- - - - CREATE: Create a new database or tables
      - ALTER: Change the structure of a tables
      - DROP: Delete a table, database or column
      - RENAME: Rename a table
      - TRUNCATE: Delete all of the data from a table
    - - INSERT: Save a new record into a database
      - UPDATE: Update a record in a database with new fields
      - DELETE: Delete a record from a database
    - - SELECT: Retrieve records from the database
- - - - The syntax for the CREATE TABLE statement is CREATE TABLE <TableName>(Column1 DataType1, Column2 DataType2, ...);
      - You must provide a name for the table and define the schema for the table. This includes the column names and data types for each column.
      - Use the CREATE TABLE statement to create a new table in your database.
      - Insert data into a table using the INSERT INTO statement: INSERT INTO <TableName> <(Column1, Column2, ...)> VALUES <(Value1, Value2, ...)>; The Columns list is optional.
    - - 2. Provide the column names and the values
        INSERT INTO Contacts (FirstName, LastName, EmailAddress, PhoneNumber, ZipCode) VALUES ('Thomas', 'Axen', 'taxen@email.com', '111-111-1111', '90110');
      - 1. Provide the values
        INSERT INTO Contact VALUES (12, 'John', 'Doe', 'johndoe@gmail.com', '999-999-999', '29384');
  - - - Filtering by Column: The SELECT FROM Statement
        Select All Columns
        SELECT * FROM TableName;
        Select Specific Columns
        SELECT Column1, Column2, ... FROM TableName;
      - Filtering by Row: The SELECT FROM WHERE Statement
        
        The WHERE clause consists of a condition based on the value of a column.
        1.SELECT * FROM Contacts WHERE FirstName = 'Roya';
        2. SELECT FirstName FROM Contacts WHERE ID <= 4
        3. SELECT * FROM Contacts WHERE ID <= 4 AND ZipCode = '90210';
      - Organizing Results - The ORDER BY clause
        The ORDER BY clause allows you to specify which column the results should be ordered based on, and whether the order should sorted in ascending or descending order.
        1. SELECT * FROM Contacts ORDER BY ID DESC;
        NOTE: Even if you are not including a column in your results, you may still order your results by that column.
    - - Update a Single Record
        UPDATE Contacts SET ZipCode = '33333' WHERE ID = 3;
      - Update Multiple Columns for a Single Record
        UPDATE Contacts SET LastName = 'Smith', ZipCode = '44444' WHERE ID = 4;
      - Update Multiple Records
        UPDATE Contacts SET ZipCode = '22222' WHERE ZipCode = '90210';
      - Update All Records
        If you omit a WHERE clause in your UPDATE statement, your update will affect ALL records.
        UPDATE Contacts SET ZipCode = '00000';
    - - Deleting Data
        The WHERE clause filters which rows will be deleted from the table.
        1. DELETE FROM Contacts WHERE ID = 5;
        2. DELETE FROM Contacts WHERE ZipCode = '90210';
        NOTE: If you do not include a WHERE clause, you will delete all of the rows from your table.
      - Deleting a Table
        You can use the DROP TABLE statement to delete a table from your database. Use this statement wisely- you will be deleting all of the data associated with the table!
        DROP TABLE <TableName>;