Defining Columns and Data Types in SQL

In SQL, defining the columns and their respective data types is a crucial step in creating a table. The columns represent the attributes of the data you want to store, while data types define what kind of data can be stored in each column.

1. Defining Columns

Columns in a table are used to store specific types of data. When creating a table, you need to define the column names and specify their data types. Each column has a name and a data type, and you can also apply constraints to enforce rules on the data in each column.

Example of Defining Columns:

sql
CREATE TABLE students ( student_id INT, first_name VARCHAR(50), last_name VARCHAR(50), birth_date DATE );

In this example, the students table is created with four columns: student_id, first_name, last_name, and birth_date. Each column has its own data type specified after the column name.

2. SQL Data Types

SQL provides several data types that can be used to define the type of data that each column in a table can store. Data types are grouped into the following categories:

2.1 Numeric Data Types

Numeric data types are used to store numerical values.

  • INT: Stores integer values (whole numbers).
  • DECIMAL(p, s): Stores fixed-point numbers where p represents the total number of digits and s represents the number of digits after the decimal point.
  • FLOAT: Stores floating-point numbers (approximate values).
  • BIGINT: Stores large integer values.

Example:

sql
CREATE TABLE products ( product_id INT, price DECIMAL(10, 2) );

Here, the products table has a product_id column that stores integer values and a price column that stores decimal values with up to 10 digits, 2 of which are after the decimal point.

2.2 Character/String Data Types

Character or string data types are used to store text.

  • CHAR(n): Stores fixed-length strings with a length of n characters.
  • VARCHAR(n): Stores variable-length strings with a maximum length of n characters.
  • TEXT: Stores large text data.

Example:

sql
CREATE TABLE customers ( customer_id INT, customer_name VARCHAR(100) );

In this example, the customers table has a customer_id column and a customer_name column that can store up to 100 characters.

2.3 Date and Time Data Types

Date and time data types are used to store date and time values.

  • DATE: Stores date values (year, month, and day).
  • TIME: Stores time values (hours, minutes, seconds).
  • TIMESTAMP: Stores both date and time.

Example:

sql
CREATE TABLE orders ( order_id INT, order_date DATE, delivery_time TIME );

In this example, the orders table has an order_date column that stores dates and a delivery_time column that stores time values.

3. Applying Constraints to Columns

In addition to defining the data type, you can apply constraints to columns to enforce rules on the data being stored. Common constraints include:

  • NOT NULL: Ensures that the column cannot have a NULL value.
  • PRIMARY KEY: Uniquely identifies each record in a table.
  • UNIQUE: Ensures that all values in a column are unique.
  • CHECK: Ensures that all values in a column satisfy a specific condition.
  • DEFAULT: Sets a default value for a column when no value is specified.

Example with Constraints:

sql
CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, salary DECIMAL(10, 2) CHECK (salary > 0), hire_date DATE DEFAULT CURRENT_DATE );

In this example, the employees table has several constraints applied:

  • The employee_id column is the primary key.
  • The first_name and last_name columns cannot have NULL values.
  • The salary column must be greater than 0, enforced by the CHECK constraint.
  • The hire_date column has a default value of the current date.

4. Altering Column Definitions

If you need to modify an existing column’s definition, such as changing its data type or adding a constraint, you can use the ALTER TABLE command.

Example of Altering a Column:

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

This modifies the salary column to increase its precision to allow for a total of 12 digits, with 2 digits after the decimal point.

Conclusion

Defining columns and data types is a critical part of creating tables in SQL. By carefully choosing data types and applying constraints, you can ensure data integrity and consistency. SQL offers a wide range of data types for storing different kinds of information, such as numbers, text, and dates. In addition, constraints like NOT NULL, PRIMARY KEY, and CHECK provide further control over the data stored in the database.

line

Copyrights © 2024 letsupdateskills All rights reserved