Creating table

In this article you are able to find out basic syntax for how to create table. This has been tested on Microsoft SQL Server so this might differ if you are using Oracles or MySQL database management systems (DBMS).

In this short article I am going to use name of the table in all commands. This means that there were is stated PLAYER can be another table names. It is only used to display the whole syntax for each example.

Syntax

This is basic syntax for how to create table.
(NOTE! Some DBMS systems might have another kind of solutions to create tables!)

CREATE TABLE PLAYER
(player_id         VARCHAR(8) PRIMARY KEY,
first_name         VARCHAR(20) NOT NULL,
last_name          VARCHAR(30) NOT NULL,
height             DECIMAL(6,2) NOT NULL,
weight             DECIMAL(6,2) NOT NULL,
racket_hand        CHAR(1),
team_id            VARCHAR(10) NOT NULL,
FOREIGN KEY (team_id) REFERENCES TEAM );

And now to open this sentence, because I think it is good to know what this actually means.

  • First line means that you are going to create a new table and you are going to give it name PLAYER
  • Then the second row means that you are creating a new attribute for the table and you are giving some specific information about it properties. In this case attribute name is player_id and its type is VARCHAR(8) and it is set as a Primary Key
  • Rows 3-6 indicates other attributes and their properties that one object also gets.
  • Last row set’s a foreign key for the table and it references to another table named TEAM . This is also how to make connection for the other tables making the relational database

As you can see, the syntax seems clearly, almost like plain English. What SQL means, it is Structured Query Language and it’s one of kind.

Alter table

Some times it is necessary to alter some of the properties of the table, so how is this done? Well here are some ways to do it

  • You can use this statement: ALTER TABLE PLAYER
  • There are some thinks you can alter like creating a new attribute with ADD(birthday DATE); AND altering the type of attribute: ALTER(first_name VARCHAR(12) );
  • If you need to only update one row you could use statement: UPDATE
  • For example: UPDATE PLAYER set weight=80 WHERE player_id = 00000001;
  • For previous example to actually work, you need to have some data stored into the database.

Drop table

To erase entire table with all of it contents simply type DROP TABLE PLAYER;

If you want to erase only one row of the database you can use statement:DELETE FROM PLAYER WHERE player_id = 00000001

It is not recommend to drop entire table because it will have direct consequences with all foreign keys references to it.

– Tuomas Törmä

Sources

  • Introduction to Database Systems lectures At Haaga-Helia autumn 2013 teacher Tanja Bergius.

Leave a comment