DbSchema Database Designer

DbSchema Tutorial | SQL Tables

Publish Article >>>

SQL Server alt >

Table of Contents:

  1. What is table?
  2. Create Table
  3. Drop Table
  4. Alter Table
  5. Conclusion

What is table?

Tables are the fundamental way to store data
in a relational database management system. A table is a database object which is composed of rows and columns in SQL. In simple terms, it’s a collection of related data held in a table format.

Let’s see an example of a Student table:

_Student_Name_ AGE _Gender_
Sam 32 Male
Bob 45 Male
Anne 23 Female

In the above table, Student is the table
name,Student_Name, Age and Gender are the
column names. The combination of data of
multiple columns forms a row e.g. Sam,
32 and Male are the data of one row.

Create Table

SQL CREATE TABLE statement is used to create table in a database.

  1. Creating a table which involves name of the
    table.
  2. Defining its columns which are part of the table
  3. Assigning a data type to each column

Let’s see the simple syntax to create the table.

    CREATE TABLE table_name (   column1 datatype,
                                column2 datatype,
                                ...
                                columnN datatype,
                                PRIMARY KEY
                                (one or more columns) );

where CREATE TABLE is the keyword, tablename is the name of the table name, columns1 to columnN are the set of columns, and PRIMARY KEY is a constraint followed by a semicolon
Let’s create an employee table.
Let us take an example to create a STUDENTS table with ID as primary key and NOT NULL are the constraint showing that these fields cannot be NULL while creating records in the table.

SQL> CREATE TABLE STUDENTS (  
         ID INT NOT NULL,  
         NAME VARCHAR (20) NOT NULL,  
         AGE INT NOT NULL,           
         PRIMARY KEY (ID)  
);  
  • Not null is the constraint used to signify
    that all cells of this column must have a value
  • Varchar stands for variable length character,
    and the value passed is the maximum length of the character
  • Primary key constraints help uniquely
    identify all records from the table, and a table cannot have more than one primary key

Now you have the STUDENTS table available in your database and you can use to store required information related to students.

Drop Table

A SQL DROP TABLE statement is used to delete a table definition and all data from a table.

This is very important to know that once a table is deleted all the information available in the table is lost forever, so we have to be very careful when using this command.

Let’s see the syntax to drop the table from the database.

DROP TABLE "table_name";

This shows that STUDENTS table is available in the database, so we can drop it as follows:

SQL>DROP TABLE STUDENTS;

Alter Table

The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table. This statement also allows database users to add and remove various SQL constraints on the existing tables.

Any user can also change the name of the table using this statement.

  • Add a new column using the ADD clause.
  • Modify attribute of a column such as
    constraint, default value, etc. using the
    MODIFY clause.
  • Remove columns using the DROP clause.

ALTER TABLE ADD Column statement in SQL

To add the columns in the existing table.
Instead of creating a whole table or database again you can easily add single and multiple columns using the ADD keyword.
Let’s see the syntax to add column to the table.

ALTER TABLE table_name ADD column_name column-definition;

Let’s look at example of adding new
columns to the STUNDETS table that we
created in
the create table.

SQL>ALTER TABLE STUDENTS ADD Address Varchar
(100);  

ALTER TABLE MODIFY Column statement in SQL

The MODIFY clause allows you to change some
attributes of the existing column e.g., NOT NULL ,UNIQUE, and data type.
Let’s see the syntax to modify column from the
table.

ALTER TABLE table_name MODIFY column_definition ;

Let’s look at example of modifying column
NAME to
the STUNDETS table that we
created in
the create table.

SQL>ALTER TABLE STUDENTS MODIFY NAME VARCHAR 
(15) NULL ;

ALTER TABLE DROP Column statement in SQL

To delete the columns from the existing table.
Instead of deleting the whole table or database you can use DROP keyword for deleting the columns.
Let’s see the syntax to delete column from the
table.

ALTER TABLE table_name DROP column_name ;

Let’s look at example of deleting column to the STUNDETS table that we
created in
the create table.

SQL>ALTER TABLE STUDENTS DROP Address ;

Conclusion

In this tutorial, you have learned step by
step how to CREATE, ALTER AND DROP table.

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.


Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.


Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.


Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.


SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.


Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.


Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.


Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.


DbSchema can be downloaded for free. No registration is required.