SQL: Data Type

In SQL, data types define the type of data that can be stored in a column of a table. Each column in a table is assigned a specific data type, which determines the kind of values that can be inserted into that column. SQL provides various data types to represent different types of data such as numbers, strings, dates, etc.

Here are some commonly used data types in SQL with examples:

INTEGER: Represents whole numbers (both positive and negative) without decimal places.
Example: age INTEGER
VARCHAR(n): Represents variable-length character strings with a maximum length of ‘n’.
Example: name VARCHAR(50)
DECIMAL(p, s): Represents fixed-point decimal numbers with ‘p’ total digits and ‘s’ digits after the decimal point.
Example: price DECIMAL(8, 2)
BOOLEAN: Represents a logical value of either TRUE or FALSE.
Example: is_active BOOLEAN
DATE: Represents a date value in the format ‘YYYY-MM-DD’.
Example: birth_date DATE
TIME: Represents a time value in the format ‘HH:MM:SS’.
Example: start_time TIME
DATETIME: Represents a combination of date and time values.
Example: created_at DATETIME
BLOB: Represents binary large objects for storing large binary data such as images, documents, etc.
Example: image BLOB
ENUM: Represents a set of predefined values that a column can take.
Example: status ENUM('active', 'inactive', 'pending')

These are just a few examples of commonly used data types in SQL. The actual available data types may vary depending on the specific database system you are using. It’s important to choose the appropriate data type for each column to ensure data integrity and efficient storage and retrieval of data.

SQL: Create Table

To create a table with multiple rows and columns in SQL, you can use the CREATE TABLE statement. Each column is specified along with its data type, and multiple rows can be inserted using the INSERT INTO statement. Here’s an example to illustrate how to create a table with multiple rows and columns:

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

INSERT INTO employees (id, name, age, department)
VALUES
  (1, 'John Doe', 30, 'HR'),
  (2, 'Jane Smith', 28, 'IT'),
  (3, 'Michael Johnson', 35, 'Sales'),
  (4, 'Emily Davis', 32, 'HR');

In this example, we’re creating a table called “employees” with four columns: “id” of type INT, “name” of type VARCHAR(50), “age” of type INT, and “department” of type VARCHAR(50).

The INSERT INTO statement is used to insert multiple rows into the “employees” table. We provide the column names in parentheses after the table name and then use the VALUES keyword to specify the values for each row. In this case, we’re inserting four rows, each with values for the “id”, “name”, “age”, and “department” columns.

After executing these statements, the “employees” table will be created, and it will contain the following data:

id	name	age	department
1	John Doe	30	HR
2	Jane Smith	28	IT
3	Michael Johnson	35	Sales
4	Emily Davis	32	HR

This table now has multiple rows representing different employees, and each row has values for the respective columns.

Remember, the column names, data types, and values in the INSERT INTO statement should match the defined structure of the table.

SQL: Primary Key

In SQL, a primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures that each row has a unique identifier and provides a way to uniquely identify and access individual records within a table. Here’s an example to explain the concept of a primary key:

Let’s consider a table called “students” with the following structure:

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  grade VARCHAR(10)
);

In this example, the “students” table has four columns: “student_id”, “name”, “age”, and “grade”. The “student_id” column is defined as the primary key using the PRIMARY KEY constraint.

Now, let’s insert some data into the “students” table:

INSERT INTO students (student_id, name, age, grade)
VALUES
  (1, 'John Doe', 18, 'A'),
  (2, 'Jane Smith', 17, 'B'),
  (3, 'Michael Johnson', 19, 'A'),
  (4, 'Emily Davis', 18, 'B');

In this example, each row in the “students” table has a unique value for the “student_id” column. The primary key constraint ensures that no two rows can have the same value for the primary key column. It enforces the uniqueness of the “student_id” values, guaranteeing that each student has a unique identifier.

Having a primary key has several benefits, including:

Uniquely identifies rows: The primary key ensures that each row in the table has a unique identifier, making it easy to reference and access specific records.
Enables efficient data retrieval: The primary key serves as an index, allowing for faster searching and retrieval of data based on the primary key column.
Supports data integrity: The primary key constraint prevents duplicate or null values in the primary key column, ensuring data integrity and consistency.

It’s important to choose a primary key that uniquely identifies each row and remains stable over time. Common choices for primary keys are auto-incrementing integers, unique identifiers, or natural keys such as employee IDs or student IDs.

amit verma

Website | + posts

Technical content writer with data scientist, artificial intelligence, programming language, database. He has a bachelor’s degree in IT and a certificate in digital marketing, Digital transformation web development android app development He has written for website like Boomi techie, tech mantra, information hub, Tech all