Sql Server material 3

Populating Data into Tables: after the table gets created to populate the data into it we use

Insert Statement:

Syntax for Insert statement:

            -INSERT INTO <table_name> [(col1, col2, ……..coln)]

             VALUES (val1, val2, …….valn)

Examples:

            INSERT INTO BANK VALUES (101, ‘VENKAT’, 4500)

 -In this case we need to provide the values for all the columns in the same order they are present in the table.

-String and Date values have to be enclosed in single quotes.

INSERT INTO BANK (CUSTID, CNAME, BAL) VALUES (102, ‘SUBASH’, 5600)

-This statement is same as above statement.

-If we want to change the order of columns while inserting:

INSERT INTO BANK (CNAME, CUSTID, BAL) VALUES (‘SURESH’, 103, 6500)

-If we want to insert data only into required columns then:

            INSERT INTO BANK (CUSTID, BAL) VALUES (104, 3600)

-In this case the columns into which values are not supplied are filled with Null value.

-We can also insert Null’s explicitly into the column in the following way:

INSERT INTO BANK VALUES (105, NULL, 5400)

Retrieving the data from Tables: if we want to retrieve the information from the table use

Select Statement:

Basic Syntax for Select statement:

            SELECT < * | COLLIST > FROM <TNAME> [CONDITIONS]

-          ‘*’ Represents all the columns of the table in the same order.

-          COLLIST is used for specifying the required no of columns and in required order.

-          CONDITIONS are optional which can be used for retrieving the required rows.

-SELECT * FROM BANK

-SELECT CUSTID, CNAME, BAL FROM BANK

-SELECT CNAME, BAL, CUSTID FROM BANK

-SELECT CUSTID AS ACCNO, CNAME, BAL FROM BANK

-We can specify an alias name for any required column while retrieving known as Column Alias.

-If we want to retrieve required rows then we use a conditional statement where:

            -SELECT * FROM BANK WHERE CUSTID=104

            -SELECT CUSTID, BAL FROM BANK WHERE CNAME=’SURESH’

-SQL Server does not have any case restrictions while writing the conditions.

Handling Null Values: The value NULL means the data value for the column is unknown or not available, so we cannot use equality condition while getting the data based on null values.

            SELECT * FROM EMP WHERE CNAME=NULL

-The above statement will not get any result because no 2 null values can be compared so to get the data based on Null values we should use the IS NULL operator as following:

            SELECT * FROM EMP WHERE CNAME IS NULL 


<<< Previous                                                                                                                       Next >>>