Ch 5 · SQL CREATE TABLE and Data Types · SQL
Topic 5 of 8 in SQL — Query a Real Database — 4 lessons.
Table Creation
Design tables with proper data types, constraints, and keys. CREATE TABLE sets out each column and the type of data it holds, such as INTEGER, VARCHAR, DECIMAL or DATE. Constraints add rules: NOT NULL forbids empty values, UNIQUE stops duplicates, a PRIMARY KEY identifies each row and a FOREIGN KEY links to another table. Good design now saves painful changes later.
Table Creation in Code
This example shows how the two related tables you have been querying, departments and employees, could be built. departments comes first, because a FOREIGN KEY can only point at a table that already exists. Look at how each column gets a type and, where needed, a rule: NOT NULL for required fields, UNIQUE for email, and DEFAULT values for salary and timestamps. INTEGER PRIMARY KEY numbers the rows for you, and the FOREIGN KEY links each employee to a department. Money uses DECIMAL, not FLOAT. This block is for reading only: the sample database already has tables with these names, so running it would stop with "table already exists". You will create and run your own tables in the next card.
Practice: Table Creation
Now design two tables of your own for a small shop: categories and products. You will choose sensible data types, give each table a primary key and link every product to its category with a foreign key. A correct answer creates the referenced table first, marks required columns NOT NULL and stores prices in a type that handles money accurately.
Solution & Challenge: Table Creation
Compare your tables with the worked solution, paying attention to the order they are created in, the price column's type and the foreign key at the end. The challenge scales this up into a full online-shop design with users, products, orders, order items and reviews. A strong answer uses at least five tables, links them with keys and runs without any errors.