Pr.Pg Next Pg

Table Basics and Creating Tables Tutorials

·         A relational database system contains one or more objects called tables.

·         The data or information for the database are stored in these tables.

·         Each table is uniquely identified by their names such as "Sales", "Purchase", "Employee" and so on, and are comprised of columns and rows.

·         Rows contain the records or data for the columns.

·         Columns contain the column name, data type, and any other attributes for the column.

Below is data from "Item_master" table:

 

Item_ID

Item_Description

Rate

I0001

Intel I7 CPU

350.00

I0001

Intel I5 CPU

250.00

I0001

Intel I7 CPU

180.00

I0001

Keyboard

20.00

I0001

32inch LCD

200.00

 

 

Creating Tables

  • SQL Server allows you to use the ANSI SQL standard CREATE TABLE statement to create tables.

  • SQL Server Enterprise Manager provides a front-end, fill-in-the-blanks table designer that you can use but the CREATE TABLE statement is ultimately sent to SQL Server 2000 when you create a table.

  • You can create a table directly using SQL Query Analyzer; from SQL Server Enterprise Manager.

  • If you are designing your own databases, you should design all your database tables and their relationships before you begin to actually create them.

 

Using Transact-SQL to Create a Table

  • At the basic level, creating a table requires little more than knowing what you want to name it, what columns it will contain, and what range of values (domain) that will be allowed in each column.

  • The basic Transact-SQL syntax required to create a table is indicated below:

 

USE <database_name>

GO

CREATE TABLE <table_name>

(

<1st_column_name> <datatype>(<value>),

<2nd_column_name> <datatype>(<value>),

<3rd_column_name> <datatype>(<value>)

)

 

For example

CREATE TABLE Staff

(

Staff_ID SMALLINT IDENTITY(1000,1) NOT NULL,

FirstName VARCHAR(25) NOT NULL,

LastName VARCHAR(25) NOT NULL,

Address VARCHAR(50) ,

Basic_pay decimal

)

 

 

CREATE TABLE country_master (

Country_ID varchar(4) NOT NULL,

Country_Name varchar(30) DEFAULT NULL,

PRIMARY KEY (Country_ID)

)

 

 

CREATE TABLE customer_master

(

Customer_ID varchar(5) NOT NULL,

name varchar(40) DEFAULT NULL,

Address1 varchar(30) DEFAULT NULL,

Address2 varchar(30) DEFAULT NULL,

Address3 varchar(30) DEFAULT NULL,

City varchar(30) DEFAULT NULL,

State varchar(20) DEFAULT NULL,

Country_ID varchar(4) DEFAULT NULL,

EmailID varchar(30) DEFAULT NULL,

Mobile_No decimal(14,0) DEFAULT NULL,

Data_of_Birth date DEFAULT NULL,

PRIMARY KEY (Customer_ID),

foreign KEY (Country_ID) References country_master (Country_ID)

)

 

 

CREATE TABLE item_master (

Item_ID varchar(5) NOT NULL,

Item_Description varchar(30) DEFAULT NULL,

Rate decimal(10,2) DEFAULT NULL,

PRIMARY KEY (Item_ID)

)

 

 

CREATE TABLE tax_master (

Tax_ID varchar(5) NOT NULL,

Tax_Rate decimal(10,2) DEFAULT NULL,

PRIMARY KEY (Tax_ID)

)

 

CREATE TABLE sales

(

Invoiceno int NOT NULL,

Invoicedate date DEFAULT NULL,

Customer_ID varchar(5) DEFAULT NULL,

Item_ID varchar(5) DEFAULT NULL,

quantity decimal(10,2) DEFAULT NULL,

Rate decimal(10,2) DEFAULT NULL,

Gross_Amount decimal(12,2) DEFAULT NULL,

Tax_Rate decimal(10,2) DEFAULT NULL,

Tax_ID varchar(5) DEFAULT NULL,

Tax_Amount decimal(10,2) DEFAULT NULL,

Other_Charges decimal(10,2) DEFAULT NULL,

Other_Deduction decimal(10,2) DEFAULT NULL,

Final_Amount decimal(10,2) DEFAULT NULL,

PRIMARY KEY (Invoiceno),

FOREIGN KEY (Customer_ID) references customer_master (Customer_ID),

FOREIGN KEY (Item_ID) references item_master (Item_ID),

FOREIGN KEY (Tax_ID) references tax_master (Tax_ID)

)

 


 

Pr.Pg border                                              Next Pg