What are Tables?
- Tables are the basic structure where data is stored in the database.
- Tables are divided into rows and columns.
- Each row represents one piece of data.
- Each column can be thought of as representing a component of that piece of data.
- For example: If we have a table for recording customer information, then the columns may include information such as First Name, Last Name, Address, City, Country, Birth Date, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column.
So what are data types?
- Typically, data comes in a variety of forms.
- It could be an integer such as 1, 2, 1002, -2002 etc.
- It could be real number. The real numbers include both rational numbers, such as 42 and −23/129, and irrational numbers, such as pi and the square root of two
- It could be a string such as 'sql', 'my name is burhan'
- It could be a date/time expression such as '2000-JAN-25 03:22:22'
- It coold be in binary format.
- When we specify a table, we need to specify the data type associated with each column. For example we will specify that 'First Name' (the column of any table) is of type char(50) - meaning it is a string with 50 characters).
- Many database tools allow you to create tables without writing SQL, but given that tables are the container of all the data.
- SQL is a standard language for accessing and manipulating databases.
- You can use SQL to access and manipulate data in MySQL, SQL Server, MS Access, Oracle, Sybase, DB2, and other database systems.
A table is look like:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
|---|---|---|---|---|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
The table above contains three records (one for each person) and five columns (P_Id, LastName, FirstName, Address, and City).
So, if we are to create the customer table specified which have First Name, Last Name, Address, City, Country, Birth Date. So we would type in:
CREATE TABLE customer (First_Name char(50),Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date date);
here:
* "customer" is table name.
* "CREATE TABLE" is a key word use to make a new table.
- Sometimes, we want to provide a default value for each column.
- A default value is used when you do not specify a column's value when inserting data into the table.
- To specify a default value, add "Default [value]" after the data type declaration.
- In the above example, if we want to default column "Address" to "Unknown" and City to "Karachi", we would type:
CREATE TABLE customer (First_Name char(50), Last_Name char(50), Address char(50) default 'Unknown', City char(50) default 'Mumbai', Country char(25), Birth_Date date);
