INSERT with DEFAULT values in SQL - Mini Project: Build & Apply

Insert Rows Using DEFAULT Values in SQL

📖 Scenario: You are managing a simple employee database for a small company. The table employees has columns for id, name, department, and salary. Some columns have default values set by the database.

🎯 Goal: Learn how to insert new rows into the employees table using the DEFAULT keyword to use default values for some columns.

📋 What You'll Learn

Create the employees table with columns id, name, department, and salary.

Set default values for department as 'General' and salary as 30000.

Insert a new employee providing only id and name, using DEFAULT for department and salary.

Insert another employee providing id, name, and department, but use DEFAULT for salary.

💡 Why This Matters

🌍 Real World

Many databases use default values to simplify data entry and ensure consistent data when some information is not provided.

💼 Career

Knowing how to use DEFAULT values and insert data efficiently is important for database administrators and developers managing real-world data.

1

Create the employees table with default values

Write a SQL statement to create a table called employees with these columns and types: id as INTEGER, name as VARCHAR(50), department as VARCHAR(50) with default value 'General', and salary as INTEGER with default value 30000.

SQL

-- Write the CREATE TABLE statement for employees
-- Your code here

Need a hint?

Use DEFAULT 'General' for the department column and DEFAULT 30000 for the salary column.

2

Insert an employee using DEFAULT for department and salary

Write an INSERT statement to add a new employee with id 1 and name 'Alice'. Use the DEFAULT keyword for department and salary to use their default values.

SQL

CREATE TABLE employees (
  id INTEGER,
  name VARCHAR(50),
  department VARCHAR(50) DEFAULT 'General',
  salary INTEGER DEFAULT 30000
);
-- Write the INSERT statement for Alice below
-- Your code here

Need a hint?

Use DEFAULT for both department and salary in the VALUES clause.

3

Insert an employee with a specific department but default salary

Write an INSERT statement to add a new employee with id 2, name 'Bob', and department 'Sales'. Use DEFAULT for the salary column.

SQL

CREATE TABLE employees (
  id INTEGER,
  name VARCHAR(50),
  department VARCHAR(50) DEFAULT 'General',
  salary INTEGER DEFAULT 30000
);

INSERT INTO employees (id, name, department, salary) VALUES (1, 'Alice', DEFAULT, DEFAULT);
-- Write the INSERT statement for Bob below
-- Your code here

Need a hint?

Provide 'Sales' for department and use DEFAULT for salary.

4

Insert an employee using DEFAULT for all columns except id and name

Write an INSERT statement to add a new employee with id 3 and name 'Charlie'. Use DEFAULT for both department and salary columns again to complete the table entries.

SQL

CREATE TABLE employees (
  id INTEGER,
  name VARCHAR(50),
  department VARCHAR(50) DEFAULT 'General',
  salary INTEGER DEFAULT 30000
);

INSERT INTO employees (id, name, department, salary) VALUES (1, 'Alice', DEFAULT, DEFAULT);

INSERT INTO employees (id, name, department, salary) VALUES (2, 'Bob', 'Sales', DEFAULT);
-- Write the INSERT statement for Charlie below
-- Your code here

Need a hint?

Use DEFAULT for both department and salary again.