SQL TUTORIALS

How to Create a Database in PostgreSQL

Summary:

• There are two ways to create a database in PgAdmin: via GUI or SQL.
• To create a database using the GUI, right click on your server, choose the "Create" menu, and select "Database". Give the database a descriptive name and designate an owner (the "postgres" user is the master account).
• The CREATE DATABASE statement is used to define the name of the new database. This statement has the following syntax: CREATE DATABASE [db_name];
• NOTE: A Schema is a collection of tables in a database. These are used for separating sensitive data from publicly accessible data.

How to Create Tables in a Relational Database

Summary:

• If you're using PgAdmin, you can create a table using either the GUI or SQL.
• To create a table using the PgAdmin GUI, open your database schema, right click on "Tables", click "Create".Give the table a descriptive name and specify the columns and data types in the "Columns" Tab.
• The CREATE TABLE statement is used to create a table in a database using SQL. This statement has the following syntax:
  CREATE TABLE [table_name] ([column1] [data_type], [column2] [data type], etc.);
• If you want to have your column names to have specific capitalization, put them between double quotes. Just know that when you run queries against any table, the capitalization doesn't matter (as we'll see later on).
• NOTE: In most query editors, if you have multiple queries lined up, you can highlight a single query to execute, rather than executing them all.
• NOTE: If you are worried that you're going to create a duplicate table on accident, you can use the IF NOT EXISTS keyword. This has the following syntaxes:
  CREATE DATABASE IF NOT EXISTS [db_name];
CREATE TABLE IF NOT EXISTS [table_name] ([column1] [data_type]...);

How to Delete Tables in a Relational Database

Summary:

• Although you can drop a table using the PgAdmin GUI, we'll focus on using SQL. To drop a table using SQL, use the DROP TABLE statement. This statement has the following syntax:
  DROP TABLE [table_name];
• Just like with the CREATE TABLE statement, you can use the IF NOT EXISTS keyword to avoid any coding errors. However, when dropping tables it's likely you'll want to use the IF EXISTS keyword to make an actual change in your database.
• If you want to drop multiple tables at the same time, you simply just separate the table names with commas after the DROP TABLE statement:
  DROP TABLE [table_name1], [table_name2], [table_name3];

How to Add Columns to Tables in a Relational Database

Summary:

• Although you can add a column to a table using the PgAdmin GUI, we'll focus on using SQL. To add a column to a table using SQL, use the ALTER TABLE statement. This statement has the following syntax:
  ALTER TABLE [table_name] ADD COLUMN [column_name] [data_type];
• If you want to add multiple columns to a table at the same time, you simply just separate the ADD COLUMN keyword with a comma:
  ALTER TABLE ADD COLUMN [column_name1] [data_type], ADD COLUMN [column_name2] [data_type], etc.;

What are Common Constraints Applied to Columns?

Part I

Part II

Summary:

• In SQL, Constraints are used to specify rules on data. These are used to enforce data quality rules and can be applied at either the table or column level.
• There are six primary constraints: Not Null, Unique, Primary Key, Foreign Key, Check, and Default.
• The Not Null constraint forces each row to have a value. If a null is ingested, the transaction will be aborted. This constraint can be applied to a column when you first create it or are adding it later on.
• The Unique constraint forces each value in the column to be unique. This is very useful when working with large datasets that can have duplicate entries. Similar to the Not Null constraint, you can apply this constraint to a column when you first create it or are adding it later on.
• As you'd expect, the Primary Key constraint designates a column as the Primary Key for the table. This infers that all values in the column be unique AND none of the values are null. You can add this either when you first create the table or add the constraint later on.
• The Foreign Key constraint designates a column as the Foreign Key for the table. This infers that all values in the column match AT LEAST one record in the related table (e.g., if you have a column in the other table titled, "StudentID" and it ranges from 1-10 and your current table has a value of 11, the relationship would be invalid).
• To add a Foreign Key Constraint in the CREATE TABLE statement, use the following syntax:
  CREATE TABLE [child_table_name] ([column_name] [data_type], [column_name] [data type], FOREIGN KEY ([column_name]) REFERENCES [parent_table_name]([primary_key]));
• The table containing the Primary Key is the parent table, whereas the table containing the foreign key is the child table.
• The Check constraint is used to limit the allowed values in a column. This is commonly used with catrgorical columns (e.g., Product Categories or Departments in an organization). When used in the CREATE TABLE statement, this has the following syntax:
  CREATE TABLE [table_name] ([column_name] [data_type], [column_name] [data_type] CHECK([column_name] IN([value1], [value2], [value3])));
• The Default constraint is used to set a default value when no value is specified. This is useful for when there is a Not Null constraint applied. When used with the CREATE TABLE statement, this has the following syntax:
  CREATE TABLE [table_name] ([column_name] [data_type], [column_name] [data_type] DEFAULT [default_value]);
• NOTE: When creating your tables, you must create the parent tables BEFORE the child tables (just like in the real-world).

Exercise

Congratulations! You just completed the DDL Tutorial! We hope you learned a lot and feel more prepared to enter the workforce. To help test your knowledge, let's complete a simple exercise.

Instructions:

1. Open PgAdmin.
2. Create a Database named "Sales".
3. In the "Sales" Database, create the following tables:
   1. Table Name: Employees
      Columns:
      id (INT) [PRIMARY KEY]
      first_name (VARCHAR(100)) [NOT NULL]
      last_name (VARCHAR(100)) [NOT NULL]
      hire_date (DATE)
      wage (FLOAT) [NOT NULL]
   2. Table Name: Customers
      Columns:
      id (INT) [PRIMARY KEY]
      first_name (VARCHAR(100)) [NOT NULL]
      last_name (VARCHAR(100)) [NOT NULL]
      phone_num (CHAR(10)) [NOT NULL] [UNIQUE]
      address (VARCHAR(250))
      city (VARCHAR(100))
      state (CHAR(2))
      zip (CHAR(5))
      
   3. Table Name: Categories
      Columns:
      id (INT) [PRIMARY KEY]
      name (VARCHAR(25)) [NOT NULL]
      description (VARCHAR(100))
      
   4. Table Name: Colors
      Columns:
      id (INT) [PRIMARY KEY]
      name (VARCHAR(50)) [NOT NULL]
      description (VARCHAR(100))
   5. Table Name: Inventory
      Columns:
      id (INT) [PRIMARY KEY]
      name (VARCHAR(100)) [NOT NULL]
      description (VARCHAR(250)) [NOT NULL]
      quantity (INT) [NOT NULL]
      price (FLOAT) [NOT NULL]
      name_brand (VARCHAR(5)) [NOT NULL]
      category_id (INT) [NOT NULL] [FOREIGN KEY] REFERENCES categories(id)
      
4. Drop the "Colors" table.
5. Alter the "Customers" table by adding the following column: age (INT).
6. Alter the "Inventory" table by adding a Check constraint to the "name_brand" column ensuring only the following values are entered: 'yes', 'no', 'unknown'.
7. Design an ERD to visualize the relationships. This can be done in a tool such as Word or Lucid Charts.
8. Exercise Completed! Click here to view the answers.

Have any issues with the above exercise? Post your question on Discord!