You are currently viewing SQL 1.50 SQL DEFAULT

SQL 1.50 SQL DEFAULT

SQL DEFAULT is used to set a default value for a column when no value is specified during an INSERT operation. This ensures that a column always has a value, even if one isn’t explicitly provided. Here’s a detailed tutorial on how to use SQL DEFAULT:

1. Creating a Table with DEFAULT Value

First, let’s create a sample table called employees with columns for id, name, age, and department. We’ll set a default value for the department column.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(50) DEFAULT 'IT'
);

In this SQL statement:

  • We define a table named employees.
  • It has columns id (integer), name (varchar), age (integer), and department (varchar).
  • The department column has a default value of 'IT'.

2. Inserting Data with and without Specifying the DEFAULT Column

Let’s insert some data into the employees table, sometimes specifying the department and other times not.

-- Inserting data without specifying department
INSERT INTO employees (id, name, age) VALUES (1, 'John Doe', 30);

-- Inserting data with specifying department
INSERT INTO employees (id, name, age, department) VALUES (2, 'Jane Smith', 25, 'HR');

In the first INSERT statement, we don’t specify a value for the department column, so the default value of 'IT' is used. In the second INSERT statement, we provide a specific value for the department.

3. Querying Data

Now, let’s query the employees table to see the data we’ve inserted.

SELECT * FROM employees;

This query will return:

| id |   name    | age | department |
|----|-----------|-----|------------|
| 1  | John Doe  | 30  | IT         |
| 2  | Jane Smith| 25  | HR         |

As you can see, the first record inserted without specifying the department column has the default value 'IT', while the second record has the specified department 'HR'.

4. Modifying Default Value

If you want to change the default value for a column, you can use the ALTER TABLE statement.

ALTER TABLE employees ALTER COLUMN department SET DEFAULT 'Finance';

This statement changes the default value for the department column from 'IT' to 'Finance'.

5. Dropping Default Value

To remove the default value from a column, you can use the ALTER TABLE statement as well.

ALTER TABLE employees ALTER COLUMN department DROP DEFAULT;

This statement removes the default value from the department column.

That’s it! You’ve learned the basics of using SQL DEFAULT to set default values for columns in a table. Feel free to experiment further with different scenarios and SQL statements to deepen your understanding.

Leave a Reply