Relational Database (SQL) 2

                    This is the second part of Relational database post. We talk about create and delete database and  tables in last post. So, in this post I ‘ll hope to give knowledge relevant  operations inside tables . Okey , let’s start.

                    First I would like to mentions that what are the kind of operations we can do inside table. You know them. They are insert data, select and get data, delete data, update data as well as join tables , create views and index like wise.  Lets refer those things as below. After create database and tables very first we should insert data. Because we don’t do any other operations without any data.  So, let’s start from insert data

Insert data

        INSERT INTO table_name( column_names) VALUES (values);

Ex-:   Lets think we have table called user with columns called id, name, address, email . now let’s insert data to that table

                INSERT INTO user(id, name, address, email) VALUES (1, ‘lakshmi’, ’Colombo‘, ‘lakshmi@gmail.com’);

 In here you should give attention below things

    ·         Columns order and values order should match

    ·         Column data type and values type should match

    ·         Should give attention to primary key, unique key , not null columns as well

You can write above query like below

        INSERT INTO user  VALUES (1, ‘lakshmi’, ’Colombo‘, ‘lakshmi@gmail.com’);

        For this you have to know order of columns in table and have to enter values for every columns. If you don’t need insert value for any column you can put it as below. Think you don’t need enter value for city so, you can use as below.

        INSERT INTO user  VALUES (1, ‘lakshmi’, ’  ‘, ‘lakshmi@gmail.com’);

        If you have any column with auto increment then you don’t need mention column name and any values if you use first method. Other wise you can use thread method for enter auto increment values. But I think if you have like this column it is good if you can use first method.

        I think you may be some times know that you can use this query with select statement to copy values from another table to another one according to any conditions or without any conditions.

I'll give one example.

          INSERT INTO user(name, address) 

          SELECT stu_name, stu_address  FROM student  WHERE age>15;

     Then you can copy student name and address from student  table whom age is greater than 15 to name and address of user table.

Select and get data

        SELECT (column name) FROM table_name WHERE condition;

        Ex-: let’s  take above example

                    SELECT id, name, email FROM user ;

        You can write this statement with or without where conditions. As well as if you want get all data in table you can use below query

                    SELECT * FROM user;

        By using select statement you can select distinct values, select counts, sum, average, minimum values, maximum values, select number of records, as well as copy records from another table to other one. When you copy using this  statement you can copy records from one table have in one database to another table have in another database.  This is useful if you want to keep backups of databases.

                SELECT  *  INTO newtable IN newdb FROM oldtable;

Update data

            UPDATE table_name SET coulnm_name= new_value WHERE condition;

            Ex-: UPDATE user SET email=’samarapura@gmail.com’, name='samarapura' WHERE id=1;

            If you want update one or few records you can use this statement with where conditions other wise you want update all records in table you can ignore where  condition. Then set new values for that columns in table for all records. So, put where conditions according to your necessary.

Delete data

            DELETE FROM table_name WHERE condition;

            Ex-: DELETE FROM user WHERE id=2;

    If you want delete all records in table you can use 

DELETE  FROM table_name;

so, at the end of this post we knew about insert, select, update and delete operations in tables. so, there are another operations like create joins between tables and create views , create triggers . as there are lots let's discuss them from another article. so, thank u all of you. 

                                                                                                      written by lakshmi samarapura