Creating Tables in SQL

Table of contents

1. 1. Create your first table

   0/13

   Remember what tables are? You will learn how to create simple tables

   List of exercises Start now
   1. Your first table
      1. Welcome
      2. Insert some data
      3. Query the table
      4. Play time!
   2. CREATE TABLE basics
      1. How tables work
      2. How tables work - continued
      3. CREATE TABLE with one column
      4. CREATE TABLE with multiple columns
      5. The VARCHAR column type
   3. Entity Relationship Diagrams (ERD)
      1. Example of ERD
      2. ERD to SQL code
   4. DROP - how to remove a table
      1. Remove a table
      2. Summary

2. 2. Data types

   0/43

   Text info, numbers, dates, logical values. Learn how to tell your database what data you're going to store

   List of exercises Start now
   1. Introduction
      1. Welcome to data types
   2. Text information
      1. Text information - introduction
      2. Sorting text information
      3. Comparing text information
      4. The VARCHAR data type
      5. VARCHAR - explore the limit
      6. VARCHAR - explore the limit continued
      7. The CHAR data type
      8. CHAR - explore the limit
      9. CHAR - explore the limit continued
      10. TEXT/CLOB
      11. TEXT/CLOB - Playground
      12. Create a table from ERD
   3. Numbers
      1. Number data types: introduction
      2. Integer
      3. Integer - inserting data
      4. Integer and text values
      5. Integer and numbers out of range
      6. Integer and SELECT statements
      7. Fractions
      8. Floating point numbers - notation
      9. Floating point numbers - calculations
      10. Floating point numbers - issues with precision
      11. Decimal numbers
      12. Decimal numbers - inserting data
      13. Decimal numbers - values out of range
      14. Decimal numbers - calculations
      15. Decimal numbers
      16. Let's practice
   4. Boolean
      1. Boolean
      2. Boolean - inserting data
      3. Boolean - querying the table
   5. Date and time
      1. Date
      2. Date - inserting data
      3. Date - comparing dates
      4. Date - create the column
      5. Time
      6. Datetime
      7. Date and time - playground
   6. Summary
      1. Summary - Exercise 1
      2. Summary - Exercise 2
      3. Summary - Exercise 3
      4. Congratulations

3. 3. Creating tables - revision

   0/6

   Let's recap creating tables and the basic data types

   List of exercises Buy course
   1. Revision
      1. Quick revision of creating tables
      2. Text data types - revision
      3. Number data types - revision
      4. Boolean data type - revision
      5. Date and time data types - revision
      6. Revision - Summary

4. 4. Primary keys

   0/30

   How to tell the database that an id column is really an identifier

   List of exercises Buy course
   1. Primary keys - the basics
      1. Primary keys - Introduction
      2. Primary keys - Syntax
      3. Primary keys - Missing values
      4. Primary keys - adding new values
      5. Primary keys - duplicated values
      6. Primary keys - NULL values
      7. Primary keys - summary
   2. Single-column primary keys
      1. Syntax revisited
      2. Primary key data type
      3. Primary keys in ERD
      4. There can only be one...
      5. ...but one there should be!
   3. Multicolumn primary keys
      1. Multicolumn primary keys - introduction
      2. Uniqueness with multicolumn primary keys
      3. Uniqueness with multicolumn primary keys - continued
      4. Uniqueness with multicolumn primary keys - summary
      5. Multicolumn primary keys in ERD
   4. Auto-increment columns and sequences
      1. New keyword IDENTITY
      2. Auto-increment with IDENTITY
      3. Creating columns with IDENTITY
      4. IDENTITY parameters
      5. IDENTITY parameters - continued
      6. The AUTO_INCREMENT keyword
      7. Sequences
      8. Retrieving data from sequences
      9. Auto-numbering in various databases
   5. Revision
      1. Revision #1
      2. Revision #2
      3. Revision #3
      4. Summary

5. 5. Constraints

   0/36

   Price below zero? A person over 1000 years old? It's not going to happen again. Learn how to tell the database what are the restriction for the data it stores

   List of exercises Buy course
   1. Introduction
      1. Constraints - Introduction
   2. UNIQUE
      1. UNIQUE - Introduction
      2. UNIQUE - Adding new rows
      3. UNIQUE - Adding rows with the same value
      4. UNIQUE vs. PRIMARY KEY
      5. UNIQUE - Playing around
      6. UNIQUE on multiple columns
      7. UNIQUE on multiple columns - Playing around
      8. Multiple UNIQUE constraints
   3. NOT NULL
      1. NOT NULL - Introduction
      2. NOT NULL - Adding NULL values
      3. NOT NULL - Creating tables
      4. NOT NULL - default behavior
      5. NOT NULL - default behavior continued
      6. NULL columns
      7. NULL or NOT NULL?
      8. NULL in ERD diagrams
   4. CHECK
      1. CHECK - Introduction
      2. CHECK - adding new values
      3. CHECK - updating rows
      4. CHECK - creating tables
      5. CHECK - complex conditions
      6. CHECK - conditions using a few columns
      7. CHECK - updating rows with complex contraints
      8. CHECK - practice
   5. DEFAULT
      1. DEFAULT - Introduction
      2. DEFAULT - Inserting data
      3. DEFAULT - NULL values
      4. DEFAULT - Creating tables
      5. DEFAULT - Sequences
      6. DEFAULT - Sequences #2
      7. DEFAULT - Sequences #3
   6. Summary
      1. Revision #1
      2. Revision #2
      3. Revision #3
      4. Summary

6. 6. Foreign keys

   0/27

   How to properly reference one table in another table

   List of exercises Buy course
   1. Introduction
      1. Foreign Keys - Introduction
      2. Referring to one table from another
      3. JOIN - revision
      4. Adding new data
      5. Adding incorrect data
      6. Adding incorrect data - explained
   2. Foreign keys
      1. Creating tables with foreign keys
      2. Inserting incorrect data with foreign keys
      3. Updating incorrect data with foreign keys
      4. Updating data in the table the foreign key points to
      5. Creating tables with foreign keys on your own
      6. Foreign keys in ERD diagrams
   3. Multicolumn foreign keys
      1. Creating multicolumn foreign keys
      2. Playing around with multicolumn foreign keys
      3. Creating multicolumn foreign keys on your own
      4. Creating multiple foreign keys in one table
      5. Foreign key columns as primary key columns
      6. Foreign key columns as primary key columns - practice
   4. Updates and deletes
      1. Updating rows with foreign keys
      2. REFERENCES explained
      3. Database behavior with CASCADE - Updating rows
      4. Database behavior with CASCADE - Deleting rows
      5. Database behavior with various kinds of foreign keys
   5. Revision
      1. Revision - #1
      2. Revision - #2
      3. Revision - #3
      4. Revision - Summary

7. 7. Views

   0/12

   It's not just tables in the database. Learn what views are

   List of exercises Buy course
   1. Views
      1. Example of a complex query
      2. Views - Introduction
      3. What views are
      4. Views in ERD
      5. Views in complicated queries
      6. Creating your own view
      7. Querying your own view
      8. Views based on views
      9. Deleting views
   2. Revision
      1. Revision - #1
      2. Revision - #2
      3. Summary

8. 8. Quiz

   0/10

   Time to test your knowledge!

   List of exercises Buy course
   1. Quiz
      1. Task 1 - data types & primary keys 1
      2. Task 2 - Data types & primary keys 2
      3. Task 3 - Deleting tables
      4. Task 4 - Primary keys and constraints
      5. Task 5 - Automatic number generation
      6. Task 6 - More on constraints
      7. Task 7 - Foreign keys
      8. Task 8 - Views
      9. Task 9 - Views in queries
      10. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more