what is framework .....................

Relational Database (SQL) 1

First  I should thanks for everyone who viewed my first and second post. This is my third blog post. In here I talk about relational database.

Before that lets see what is a database. Lots of time we can hear this name,  when we create a website or when we create a system  and lots of  time  when we dealing with computer system. Sometimes you have heard that Someone say   “it is a database problem ” or ”it is difficult to connect with database” likewise. So, lots of time  in industry you can hear  this word  “database”.

Database is an organized collection of data

Think , we have lots of data relevant students details and their marks for subjects. So, we organize that data according to a method which we can manipulate data easily. We can say it is a database. Generally we store and access database electrically from a computer system . 

 You may be heard relational database and non-relational database. They are main types of database. In here I talk only about relational database .  now  lets see  what is relational database.

 If we use tables for store data in database then we say it is relational database.  We are called a relational database management system (RDBMS) to this relational database. As well as SQL database. When we use RDBMS , we can use it simple and we have many advantages. We can sort DB based on any field (any column), We can generate reports that contain only certain fields from each record , likewise.  In relational database, there are many kind of database. PostgreSQL, Microsoft SQL server, oracle database, MySQL, IBM DB2 . those are most popular SQL database.

I think you could be able to learn something about SQL database . So, I would like to say about SQL query from now . I mentioned some names of SQL database. Among them I select MySQL database to  say about SQL query.  Although I write only MySQL query ,  there are not big difference between MySQL query and other database query as all of them are SQL database.

There are lots of SQL query for different purposes. Some of them are used for create database or for select records from DB or  insert data to database or delete or update database’s records. Otherwise  some query are used for join tables. Some of them are used for transaction or create views and triggers likewise. I prefer to give small description about these each query from now.

               

 CREATE DATABASE db_name;   

       

  By using this we can create database.

                  Ex-:  CREATE DATABASE SalesCompany;

 CREATE TABLE table_name(    Column_name with type and size with or without constraints );

                You can go inside database and type this query to create tables to database.

                Ex-:

      CREATE TABLE customer (

            Id int(11),

            FirstName varchar(40) ,

            LastName varchar(40) ,

            City varchar(40) ,

            Country varchar(40) ,

            Phone varchar(20) 

         );

In here  Id , LastName , FirstName , Country , City ,  Phone are column names and int, varchar , are types of each columns. After we create types for any field like above we can only add values according to that type. You can not add any string values to Id field as its type is int. as well as you should mention value size In the  brackets .  This is basic query for create tables. But. There are lots of thing which we can add when create tables for database.  You can also add constraints and  AUTO_INCREMENT . 

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK, INDEX are constraints which we can use in SQL query. We can add these when we create tables by using 3 methods except INDEX. We create INDEX after create tables .

    I have given an example with few constraints and AUTO_INCREMENT in here according to first method.

Ex-:  

   CREATE TABLE customer (

            Id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

            FirstName varchar(40) ,

            LastName varchar(40) ,

            City varchar(40) ,

            Country varchar(40) ,

            Phone varchar(20) 

         );

CREATE TABLE orderitem (

            Id int(11) NOT NULL UNIQUE,

            OrderId int(11) NOT NULL ,

            UnitPrice decimal(12,2) NOT NULL DEFAULT '0.00',

            Quantity int(11) CHECK(Quantity >=1),

            ProductId int(11)  FOREIGN KEY REFERENCES product(Id)

       );

     

                You can see  above query, in there I have add constraints with column names. This is the simple method which we can use to add constraints when create tables.

                In next method we can add constraints below the column names . except  NOT NULL and DEFAULT  . as well as without  AUTO_INCREMENT .

              Ex-:

 CREATE TABLE customer (

            Id int(11) NOT NULL AUTO_INCREMENT,

            FirstName varchar(40) ,

            LastName varchar(40) ,

            City varchar(40) ,

            Country varchar(40) ,

            Phone varchar(20),

            PRIMARY KEY (Id);

         );

CREATE TABLE orderitem (

            Id int(11) NOT NULL,

            OrderId int(11) NOT NULL ,

            UnitPrice decimal(12,2) NOT NULL DEFAULT '0.00',

            Quantity int(11) ,

            ProductId int(11) ,

            UNIQUE (Id),

            CHECK(Quantity >=1),

            FOREIGN KEY (ProductId) REFERENCES product(Id)

       );

            

            

            Likewise we can create constraints by using CONSTRAINT key with a reference name for constraints. In here also we cannot use NOT NULL , DEFAULT  and  AUTO_INCREMENT . lots of time we use this method when we add multiple columns for a constraint. If we want add UNIQUE  constraint  for two columns at same time we can use this method. You will be able to understand it by using below examples

Ex-:

CREATE TABLE customer (

            Id int(11) NOT NULL AUTO_INCREMENT,

            FirstName varchar(40) ,

            LastName varchar(40) ,

            City varchar(40) ,

            Country varchar(40) ,

            Phone varchar(20),

            CONSTRAINT PK_customer PRIMARY KEY (Id,FirstName);

         );

CREATE TABLE orderitem (

            Id int(11) NOT NULL,

            OrderId int(11) NOT NULL ,

            UnitPrice decimal(12,2) NOT NULL ,

            Quantity int(11) ,

            ProductId int(11) ,

            supplierId INT(11),

            CONSTRAINT UC_Id UNIQUE (Id,OrderId),

            CONSTRAINT CHK_value CHECK(Quantity >=1 AND UnitPrice >= 0.00),

            CONSTRAINT FK_order FOREIGN KEY (ProductId,supplierId) REFERENCES

            product(Id,SupplierId)     

       );

    Now I give brief description about constraints which we can use when create tables

1.       NOT NULL.

                 default, a column can hold NULL values. This constraint enforces a column to NOT    accept NULL values

2.       UNIQUE

        The UNIQUE constraint ensures that all values in a column are different. Then we can not    keep  same values in                     a column

3.       PRIMARY KEY

This is essential constraint for a table. Usually every table have primary key. We can identify  uniquely each records in table.

4.       FOREIGN KEY

We can create connections between tables using this

5.       DEFAULT

If we have not any values to enter we can give default value to that column

6.       CHECK

We can enter values after checking to a column by using this constraint and limit value range in column.

DROP DATABSE db_name;

      You can delete database using this query.

  Ex-: DROP DATABASE SalesCompany;

DROP TABLE tb_name;

        You can go inside database and delete any table by using this query. After do this you can not see table inside database

  Ex-:  DROP TABLE orderitem ;

 

TRUNCATE TABLE table_name;

 

        We can use this for delete every records inside a table without delete table from database. After do this you can see table inside database without any data records.

  Ex-:  TRUNCATE TABLE orderitem ;

                

                These are SQL query which we can use for create or delete  database and tables. After create database with tables, we can do lots of things inside tables by using many queries . you can insert data,  get data , delete data , modify , join tables, create views and index. There are so many things have to do. This is already a long article. So, I hope to write about another  SQL  queries  in next one.