Udjuni.com

Mathematics

Science

Computing

Accounting

Business

History

Agriculture

Biology

Creating database tables with SQL

To create a table in SQL, you use a command that requires the name of the table as well as names and data types of the fields of that table. In general, the syntax for creating a table is as follows:

CREATE table_name ( column_name  data_type);

The table names and names of the columns or fields are dependent on your requirements, but the general guideline is to use the same names for the tables and field as the names of the physical attribute they represent. For example, if you were creating a table to hold customer information, you make sure that the name of the table reflects the data that is in it, which in this case is customer data. This by implication means the table should be called customer. Here is an example of what our customer table would look like (I will be using MySQL database, but the syntax is the same with all database types):

CREATE TABLE customer (

               customerid  bigint primary key auto_increment,

               firstname varchar(50),

               lastname varchar(50),

               idnumber varchar(50),

               gender varchar(20),

               dateofbirth datetime);

Note here, that the data in this table is strictly limited to customer information. This is to ensure that we do not duplicate records every time the customer buys something.

Explanation of the code : - After defining the name customer, we defined the field customerid, this field serves as a unique identifier for any given customer. The phrase primary key means that values in this field can not be duplicated or repeated. Thus, you can have multiple customers with the same name or date of birth, but each one of them is uniquely identified by the primary key (customerid). The phrase or keyword auto_increment tells the system to generate a new value every time a new record is created.

Using the insert command to create data records

To add data to our newly created table we use the INSERT command. The command requires the use of the word INSERT then you have to specify the table to add data into, all the fields that you would like to populate and then the list of values for corresponding fields. In general you use the following syntax:

INSERT INTO table_name (column_name) VALUES (value);

Note that all values of string and date and time data types must be surrounded in quotation marks (usually single quotes). Numeric values do not require quotes.
For example, to insert a record into the customer table, we would use the following syntax:

 INSERT INTO customer (firstname, lastname,gender,dateofbirth,idnumber)

           VALUES(‘Markus’,’Makushe’,’Male’,’1957-12-23’,’68884774777’);

Note here that all our values are enclosed with single quotes, this is because they are all of string and date types. Of importance here is that the order of the values specified must match the order of the specified fields or columns. If all values are strings and you did not assign the values in the same order that you specified the columns, the database system will take it, but your database will be messy with all the misplaced data. However trying to put a string in the date field is not acceptable and the system, depending on how it is configured may raise an error message or simply discard your data.

A detailed look at the INSERT statement

If you knew the order of the fields in the table and just wanted to insert values without listing the fields, you still could, but in this case you would have to provide values for all fields. In our case you could do the following:

INSERT INTO  customer VALUES(‘Hash-Hash’,’Djami’,’Male’,’1957-12-232’,’68884774778’)

This statement will add the record into the database, but if you skipped one column, this query would raise an error message. Why? Since we did not specify the column list that we want to populate, the database assumes that we want to insert data in all fields. In the eyes of the database, it looks like we are telling it to populate 6 fields and only providing 5 values; which under normal circumstances would raise an error message.

This is the most basic form of data entry into the database. Once you feel comfortable with this method, we will cover other and more complex methods of achieving the same results. For now keep practicing the concept taught here until you master it.

Creating and dropping SQL Views
In SQL, a view is like a virtual table. It be haves like a table except it does not store any data. Meaningful use of a view is usually to gather and simplify presentation of data from multiple tables. A view is compiled once and is stored on the database server and can be used wh

Selecting data from multiple tables
So far we have covered methods of retrieving data from single table. In real life however, you hardly get to only retrieve data from one table. In other words, to retrieve and interpret information, you normally get such information from different tables each of which will contain only information a

Creating your business web site
There are thousands of business websites on the World Wide Web. These websites serves as a means to inform your customers, suppliers, media contacts and other interested parties about your business and its offerings. Thus it is important to deliver essential information in a wa

Database Design
After gathering and analyzing your requirements, and depending on whether or not your application needs a database to store information; database design is the next big thing. This comes even before you start coding the application program. The reason for this is that, the application program

The state of Periodic table elements
- In addition to the group 8 or O elements, the following elements also naturally occur in gas state: H, N, O, F, Cl and Br -Among metals and non-metals, the following occurs in liquid state: Na, K, Rb, Cs, Fr, Hg a

breaking code periodic table

Joining a table with itself (SQL self-join)
Joining a table with itself is sometimes the only way to make sense of information. Consider a scenario where everybody's information in a company including managers and ordinary employees. That same table contains information on who reports to who?

Database schemas, domains and constraints
There is more to a database than just a collection of tables. In addition to tables, there are structural aspects that maintain the integrity of data stored in databases. This tutorial will cover the structural aspect of any relational database. These include schemas, domains and constraints.

Copying data from one table to the other
here are several ways to copy or move data from one table to another. Some methods are applicable when copying data from one table to another on the same database and other methods are applicable for copying data from one database to another and even or a different server. Bellow are several methods

Udjuni.com

Creating database tables with SQL

Using the insert command to create data records

A detailed look at the INSERT statement

RELATED ARTICLES: