SQL Table

by
  • SQL Table is a collection of data which is organized in terms of rows and columns. In DBMS, the table is known as relation and row as a tuple.
  • Table is a simple form of data storage. A table is also considered as a convenient representation of relations.

 Example of the EMPLOYEE table:

Sure! Here’s an example of an “EMPLOYEE” table with some commonly used columns:

CREATE TABLE EMPLOYEE (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(20),
    hire_date DATE,
    job_title VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

In this example, we have the following columns in the “EMPLOYEE” table:

  • id: An integer column representing the unique identifier for each employee. It is set as the primary key of the table.
  • first_name: A variable-length character column (maximum 50 characters) to store the employee’s first name.
  • last_name: A variable-length character column (maximum 50 characters) to store the employee’s last name.
  • email: A variable-length character column (maximum 100 characters) to store the employee’s email address.
  • phone_number: A variable-length character column (maximum 20 characters) to store the employee’s phone number.
  • hire_date: A date column to store the date when the employee was hired.
  • job_title: A variable-length character column (maximum 50 characters) to store the employee’s job title.
  • department_id: An integer column to store the department ID to which the employee belongs.
  • salary: A decimal column with precision 10 and scale 2 to store the employee’s salary.

You can modify the column names, data types, and constraints according to your specific requirements.

Operation on Table:

Certainly! Once you have created the table, you can perform various operations on it, such as inserting data, retrieving data, updating data, and deleting data. Here are some examples of common operations on the “EMPLOYEE” table:

  1. Inserting data into the table:
INSERT INTO EMPLOYEE (id, first_name, last_name, email, phone_number, hire_date, job_title, department_id, salary)
VALUES (1, 'John', 'Doe', '[email protected]', '123-456-7890', '2022-01-01', 'Software Engineer', 1, 5000.00);

2. Retrieving all data from the table:

SELECT * FROM EMPLOYEE;

3. Updating data in the table:

UPDATE EMPLOYEE
SET salary = 5500.00
WHERE id = 1;

4.Deleting data from the table:

DELETE FROM EMPLOYEE
WHERE id = 1;

These are just a few examples of operations you can perform on the table. SQL provides a wide range of operations and functions to manipulate and query the data in the table based on your specific requirements.

SQL Create Table:

Certainly! Here’s an example of creating an SQL table for managing customer information:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    phone_number VARCHAR(20),
    address VARCHAR(200),
    city VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(50)
);

In this example, we have created a table called “Customers” with the following columns:

  • customer_id: An integer column representing the unique identifier for each customer. It is set as the primary key of the table.
  • first_name: A variable-length character column (maximum 50 characters) to store the customer’s first name. The NOT NULL constraint ensures that this field must have a value.
  • last_name: A variable-length character column (maximum 50 characters) to store the customer’s last name. The NOT NULL constraint ensures that this field must have a value.
  • email: A variable-length character column (maximum 100 characters) to store the customer’s email address.
  • phone_number: A variable-length character column (maximum 20 characters) to store the customer’s phone number.
  • address: A variable-length character column (maximum 200 characters) to store the customer’s address.
  • city: A variable-length character column (maximum 50 characters) to store the customer’s city.
  • postal_code: A variable-length character column (maximum 20 characters) to store the customer’s postal code.
  • country: A variable-length character column (maximum 50 characters) to store the customer’s country.

You can modify the column names, data types, and constraints based on your specific requirements.

Drop table:

To drop a table in SQL, you can use the DROP TABLE statement followed by the name of the table you want to drop. Here’s an example:

DROP TABLE Customers;

In this example, the Customers table will be dropped from the database. Please note that dropping a table will permanently delete all the data and structure associated with that table, so be cautious when using this operation. Make sure to back up any important data before performing the drop table operation.

SQL DELETE table:

If you want to delete all the data from a table in SQL without dropping the table itself, you can use the DELETE FROM statement. Here’s an example:

DELETE FROM Customers;

In this example, the DELETE FROM Customers statement will remove all rows and data from the Customers table, but the table structure will remain intact.

Please note that the DELETE FROM statement deletes all rows from the table, so exercise caution when using it. Make sure to have a backup of the data if needed, as the operation cannot be undone.