You are currently viewing SQL 1.70 SQL COLUMN

SQL 1.70 SQL COLUMN

Introduction to SQL COLUMN

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It is widely used to perform operations on data stored in relational database management systems (RDBMS).

The COLUMN Keyword

In SQL, the term “COLUMN” is often associated with various operations involving table columns. Although SQL does not have a standalone COLUMN keyword used in commands, columns are fundamental when performing tasks such as selecting data, adding or removing columns, and modifying column attributes.

Sample Database

We will use a sample database with a table named employees. Here’s the SQL to create and populate the table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

INSERT INTO employees (id, name, position, salary, hire_date) VALUES
(1, 'John Doe', 'Software Engineer', 75000, '2020-01-15'),
(2, 'Jane Smith', 'Data Analyst', 65000, '2019-07-30'),
(3, 'Mike Johnson', 'Project Manager', 85000, '2018-03-22');

1. Selecting Specific Columns

The SELECT statement is used to query the database and retrieve data. You can specify the columns you want to retrieve.

Example: Retrieve the name and salary columns from the employees table.

SELECT name, salary
FROM employees;

Explanation:

  • SELECT name, salary: Specifies the columns to be retrieved.
  • FROM employees: Specifies the table from which to retrieve the columns.

Output:

name         | salary
-------------|--------
John Doe     | 75000
Jane Smith   | 65000
Mike Johnson | 85000

2. Adding a New Column

To add a new column to an existing table, use the ALTER TABLE statement with the ADD COLUMN clause.

Example: Add a column department to the employees table.

ALTER TABLE employees
ADD COLUMN department VARCHAR(50);

Explanation:

  • ALTER TABLE employees: Specifies the table to be modified.
  • ADD COLUMN department VARCHAR(50): Adds a new column named department with a data type of VARCHAR(50).

Output:
The employees table now includes the department column, but it will be empty for existing records.

3. Modifying an Existing Column

To modify the properties of an existing column, use the ALTER TABLE statement with the MODIFY or ALTER COLUMN clause (syntax may vary between RDBMS).

Example: Modify the salary column to increase its precision.

ALTER TABLE employees
MODIFY salary DECIMAL(12, 2);

Explanation:

  • ALTER TABLE employees: Specifies the table to be modified.
  • MODIFY salary DECIMAL(12, 2): Changes the salary column to have a precision of 12 digits and 2 decimal places.

4. Deleting a Column

To delete a column from a table, use the ALTER TABLE statement with the DROP COLUMN clause.

Example: Remove the hire_date column from the employees table.

ALTER TABLE employees
DROP COLUMN hire_date;

Explanation:

  • ALTER TABLE employees: Specifies the table to be modified.
  • DROP COLUMN hire_date: Removes the hire_date column from the table.

Complete Example

Let’s go through a complete sequence of operations on the employees table.

  1. Create and Populate the Table:
   CREATE TABLE employees (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       position VARCHAR(50),
       salary DECIMAL(10, 2),
       hire_date DATE
   );

   INSERT INTO employees (id, name, position, salary, hire_date) VALUES
   (1, 'John Doe', 'Software Engineer', 75000, '2020-01-15'),
   (2, 'Jane Smith', 'Data Analyst', 65000, '2019-07-30'),
   (3, 'Mike Johnson', 'Project Manager', 85000, '2018-03-22');
  1. Select Specific Columns:
   SELECT name, salary
   FROM employees;
  1. Add a New Column:
   ALTER TABLE employees
   ADD COLUMN department VARCHAR(50);
  1. Modify an Existing Column:
   ALTER TABLE employees
   MODIFY salary DECIMAL(12, 2);
  1. Delete a Column:
   ALTER TABLE employees
   DROP COLUMN hire_date;

Conclusion

In this tutorial, you learned how to perform basic column-related operations in SQL. You now know how to:

  • Select specific columns from a table.
  • Add a new column to an existing table.
  • Modify an existing column.
  • Delete a column from a table.

Practicing these operations on your own database will help solidify your understanding of SQL column manipulation.

Leave a Reply