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.
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.
sqlCREATE 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.
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:
Numeric data types are used to store numerical values.
Example:
sqlCREATE 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.
Character or string data types are used to store text.
Example:
sqlCREATE 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.
Date and time data types are used to store date and time values.
Example:
sqlCREATE 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.
In addition to defining the data type, you can apply constraints to columns to enforce rules on the data being stored. Common constraints include:
sqlCREATE 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:
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.
sqlALTER 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.
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.
Copyrights © 2024 letsupdateskills All rights reserved