CREATE DATABASE AND TABLES

We came to know from my previous post 'INTRODUCTION TO DATABASE-2' that data is present in tables in a database. For example, consider the following table which contains the department details in the AdventureWorks database(AdventureWorks is the default database added in the SQL SERVER which contains the data of a fictitious bicycle manufacturer, Adventure Works Cycles).

However, in order to store data in the database, we need to create our own database in which we create our tables and store our work.

CREATING AND USING DATABASE:

Use the following command to create a database, say 'sqlTutorials '.

CREATE DATABASE teachSqlServer;

The above command creates a database by the specified name. To use a particular database among all the databases present, one can use the following command.

USE teachSqlServer;

You can do all your work in that database you specify so that you can isolate your work.

CREATING TABLES:

The information inside the database is organized into tables. While creating tables, we have to specify the name of the column, data type it holds and any constraints to be imposed on that column(we will get to know about constraints in the later sessions). A sample command is as follows:

                                                      CREATE TABLE prodType
                                                      (
                                                      productionTypeID TINYINT,
                                                      prodTypeName VARCHAR(15)
                                                      )

NOTE:

SQL SERVER is not case sensitive i.e, create table is same as CREATE TABLE. However, to differentiate between user defined and predefined statements, names or commands, it is always better to follow certain standards. Some of the basic rules are;

1. Use upper case letters for all the predefined commands, statements or names.
2. In general, user defined naming conventions differ from one client to other but, it is advisable to follow a particular standard unless mentioned. Some of the common practices are:

•  First letter of the words should be in uppercase with no spaces in between the words. Example: SqlServerTutorials
• First letters of  all the words except the first word should be in uppercase with no spaces in between the words.                                                                                                    Example: sqlServerTutorials
• All the letters in lowercase with the words separated by underscore between them.       Example: sql_server_tutorials

In the next session, we will discuss regarding certain basic concepts which one needs to know before working with SQL-SERVER.

SQL SERVER DATA TYPES

In this session we will know SQL-SERVER data types. We came to know from the previous posts that every column in a table has a specific data type and allows only that particular type of data to be inserted into that column. A data type is the term used to specify the type of data that an object is carrying such as character data, integer data, date and time data, binary data etc.

Based on the type of data, data types can be categorized as follows1) String data types
2) Integer data types

3) Date and time data types

4) Unicode data types

5) Binary data types

6) Miscellaneous data types

We shall  know what are the data types under each category mentioned above.

  STRING DATA TYPES:

The following are the list of string data types supported by SQL SERVER.

INTEGER DATA TYPES:

DATE DATA TYPES:

 MISCELLANEOUS DATA TYPES:

If one needs any further information regarding these data types, you can use the following link,

http://msdn.microsoft.com/en-IN/library/ms187752.aspx

In the next session,we will learn some basic sql commands and how to create database, tables in SQL-server.