[placeholder]

• Primary Key

• Foreign Key

• Move order of operations here

2022-04-22 - Unit 1.2 - Data Fundamentals - Working with Databases - String Matching ^0 Advanced SQL

Create or Modify Data

• To create a schema:

  • Right-click under the SCHEMA window (top left) and click ‘Create Schema’

  • OR run these three statements; DROP DATABASE IF EXISTS <schema name>; CREATE DATABASE <schema name>; USE <schema name>;

• To create a table:

  • Right-click ‘Tables’ under the schema (top left) and click ‘Table Data Import Wizard’

  • OR run the statement; CREATE TABLE <table name> (<column1><data type>, <column2><data type>, …) INSERT INTO <table name> (<column1>, <column2>, …) VALUES (<1st row column1 value>, <1st row column2 value> …), (<2nd row column1 value>, <2nd row column2 value>, …);

REPLACE - replace a letter or a longer string; case sensitive; syntax SELECT REPLACE(column_name, ‘original_value’, ‘new_value’) FROM table_name

INSERT INTO - insert a new record into table; provide value for each field; check table settings for data type; e.g., INSERT INTO table_name VALUES (default, “John”, 35, ‘1992-01-19’, NULL) or specify which fields by INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3)

UPDATE SET - change information that’s already in the table, e.g., misspellings; syntax UPDATE table_name SET column_name=’new_value’ WHERE key_column=’value2’

DELETE - delete record; syntax DELETE FROM table_name WHERE key_column = ‘value’

CREATE TABLE - copy one table to another as a subquery; syntax CREATE TABLE new_table AS SELECT FROM old_table

ALTER TABLE - add, delete, or modify columns in an existing table

Add a column; syntax ALTER TABLE table_name ADD column_name datatype
Delete a column; syntax ALTER TABLE table_name DROP column_name
Change data type; syntax ALTER TABLE table_name MODIFY column_name datatype