Scribd Logo
Upload

Welcome to Scribd!

  • 0 views

    SQLSERVER-CLASS4

    Uploaded by

    hanuman sqlboy

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    SQLSERVER-CLASS4

    Uploaded by

    hanuman sqlboy
    0 views9 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    0 views9 pages

    SQLSERVER-CLASS4

    Uploaded by

    hanuman sqlboy

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 9

    STRUCTURE QUERY LANGUAGE:

    It is a non procedural language which is used to communicate with any database


    such as Oracle, sqlserver etc.

     This Language was developed by the German Scientist Mr. E.F.Codd in


    1968
     ANSI (American National Standard Institute) approved this concept and in
    1972 sql was released into the market

    Features of SQL:
     SQL is not a case sensitive language it means that all the commands of Sql
    are not case sensitive
     Every command of sql should ends with a semicolon (;) (It is exemption for
    SQL Server)
     SQL can be pronounced as Sequel (Structured English Query Language)
     SQL can be called as Common Language Interface, which is used to
    communicate with any type of database
     SQL can be called as NLI (Natural Language Interface). It means that all the
    SQL Commands are almost similar to normal English language
     Structured query language is mainly divided into 4 sub languages
    1. DDL (Data Definition Language)
    2. DML (Data Manipulation Language)
    3. TCL (Transaction Control Language)
    4. DCL(Data Control Language)
    5. DQL (Data Query Language)

    DATA DEFINITION LANGUAGE


    Data Definition Language: This is a 1st sub Language in SQL which is
    used to define the database objects such as table, view etc.

     This language contains five commands


    1. Create
    2. Alter
    3. SP_Rename
    4. Truncate
    5. Drop

    1. Create:
     This command is used to create the database objects within the database
    Syntax: CREATE TABLE <TABLE NAME>
    (COL 1 DATA TYPE (size),
    COL2 DATA TYPE (size),
    :
    :
    :
    COLN DATA TYPE (size));
    Ex: CREATE TABLE EMP (EID Int, ENAME Varchar (15), SAL
    DECIMAL (6, 2));

    Rules for Creating a Table:


     Table name must be unique under the database.
     Column names must be unique within the table.
     Never start table name with numeric or special characters except
    underscore’_’.
     Do not use space in table name if we want give space in table name then use
    underscore symbol only.
     Every object name should contain minimum one character and maximum
    128 characters.
     The maximum no. of columns a table can have 1024 columns.

    2. ALTER:
     This command is used to modify the structure of a table using this
    command, we can perform four different operations
     Using this command we can increase (or) decrease the size of the data
    type & also we can change the data type from old data type to new
    data type
     We can add a new column to the existing table
     We can change the column name from old column name to new
    column name
     We can remove the column from the existing table
     This command contains 4 sub commands
    1. ALTER- ALTER COLUMN
    2. ALTER- ADD
    3. SP_RENAME
    4. ALTER- DROP

    a. ALTER-ALTER COLUMN:

     Syntax: ALTER TABLE <TABLE NAME> ALTER COLUMN


    <COLUMN NAME> DATA TYPE (SIZE)
     Ex: ALTER TABLE EMP ALTER COLUMN ENAME char (25);

    b. ALTER-ADD:

     Syntax: ALTER TABLE <TABLE NAME> ADD <COLUMNNAME


    >DATA TYPE(size);
     Ex: ALTER TABLE EMP ADD DEPTNO int;

    c. ALTER-DROP:

     Syntax: ALTER TABLE <TABLE NAME> DROP COLUMN <COLUMN


    NAME>;
     Ex: ALTER TABLE EMP DROP COLUMN SAL;

    d. SP_RENAME:
     Syntax: SP_RENAME ‘TABLENAME.OLDCOLUMN’,’NEW COLUMN
    NAME’,’COLUMN,;
     Ex: SP_RENAME ‘EMP.SAL’,’SALARY’,’COLUMN’

    3. SP_RENAME:

     This command is used to change the table name from old table name to new
    table name
     Syntax: SP_Rename ‘old table name’,’ New table name’
     Ex: SP_Rename ‘EMP’,’EMP1’

    4. TRUNCATE:

     This command is used for to delete all the records from existing table
    permanently
     Syntax: TRUNCATE TABLE <TABLE NAME>
     Ex: TRUNCATE TABLE EMP;

    5. DROP:

     This command is used to remove the table permanently from the database
     Syntax: DROP TABLE <TABLE NAME>
     Ex: DROP TABLE EMP;

    Note: SP_help: This command is used to see the structure of table

     Syntax: SP_help <table name>


     Ex: SP_help EMP

    Data Manipulating Language: This is the 2nd sub language in SQL,


    which is used to manipulate the data within database. This Language contains 4
    commands
    1. Insert
    2. Update
    3. Delete

    1. INSERT:

     Using this command we can Insert the records into the existing table
     We can insert the records into the table in two methods
    Explicit method
    Implicit method

    Explicit method:

     In this method user has to enter all the values into all the columns without
    anything omitting (or) left any column data
     Syntax: INSERT INTO <TABLE NAME> VALUES <VAL1, VAL2,
    ….VALN>;
    (OR)
    INSERT <TABLE NAME> VALUES <VAL1, VAL2, .VALN>;
    (Here “INTO” Keyword is optional)

     Ex1: INSERT INTO EMP VALUES (101,’RAJ’,9500);


     Ex2: INSERT EMP VALUES (101,’RAJ’,9500);
    1 Row(s) affected

    Implicit method:

     In this method we can enter the values into the required columns in the table,
    so that user can omit (or) left some columns data while he enters the records
    into the table
     If the user omit any column data in the table then it automatically takes
    NULL
     Syntax: INSERT INTO <TABLE NAME> (COL1, COL2….COLN)
    VALUES (VAL1, VAL2… VALN);
     Ex: INSERT INTO EMP (EID, SAL) VALUES (106,9999);

    2. UPDATE:

     This command is used to modify the data in the existing table


     By using this command we can modify all the records in the table & also
    specific records in the table (Using ‘where’ clause)
     Syntax: UPDATE <TABLE NAME> SET COL=VALUE;
     Ex: UPDATE EMP SET SAL=10000;

    Syntax change for more than one data simultaneously

     Syntax: UPDATE <TABLE NAME> SET COL1=VALUE,


    COL2=VALUE………COLN=VALUE;
     Ex: UPDATE EMP SET EID=007,SAL=10000;

    3. DELETE:

     This command is used to delete the records from existing table


     Using this command we can delete all the records and also to delete specific
    record (by using ‘where’ clause)
     Syntax: DELETE FROM <TABLE NAME>
     Ex: DELETE FROM EMP;
    10 row(s) affected

    Difference between TRUNCATE and DELETE Command:

    SRNO TRUNCATE DELETE


    01 It is a DDL command It is a DML command

    02 It is a permanent deletion It is temporary deletion

    03 Specific record deletion is not We can delete the specific record


    possible

    04 It doesn’t support WHERE It supports WHERE clause


    clause

    05 We cannot Rollback the data We can Rollback the data

    06 Truncate will reset the identity Delete will not reset the identity
    Values value

    DATA QUERY LANGUAGE:

    1. SELECT:

     This command is used to retrieve the data from existing table.


     Using this command we can retrieve all the records & also specific records
    from existing table (by using ‘where’ clause)

    Syntax: SELECT * FROM <TABLE NAME> [WHERE <CONDITION>];

     Ex: SELECT * FROM EMP;


     * represents all columns

    WHERE CLAUSE:

     This clause is used to check the condition based on the condition, we can
    retrieve, update, delete specific records in the table
     So we can apply the where clause only in select, update & delete

    Select Command With Where clause:

     Syntax: SELECT * FROM <TABLE NAME> WHERE <CONDITION>


     Ex: SELECT * FROM EMP WHERE EID=102;

    Update Command With Where clause:

     Syntax: UPDATE <TABLE NAME> SET <COLUMN NAME>=VALUE


    WHERE (CONDITION);
     Ex: UPDATE EMP SET ENAME=”sai” WHERE EID=102;

    Delete Command With Where clause:

     Syntax: DELETE FROM <TABLE NAME>WHERE <CONDITION>


     Ex: DELETE FROM EMP WHERE EID=102;

    ALIAS:

     ALIAS is a duplicate name (or) alternate name for the original column name
    (or) Table name (or) an expression name.

     Column level Alias + Table level alias :


     Syntax: SELECT COLUMN NAME AS “ALIAS NAME”,
    COLUMN NAME AS “ALIAS NAME”,
    :
    :
    COLUMN NAME AS “ALIAS NAME” FROM <TABLE NAME>;
     EX: SELECT EID AS “EMPLOYEE ID”, ENAME AS “EMPLOYEE
    NAME”, SAL AS “SALARY” FROM EMP;
     NOTE: In the above example the keyword ‘as’ is optional
     EX: SELECT EID “EMPLOYEE ID”, ENAME “EMPLOYEE NAME”,
    SAL “SALARY” FROM EMP;
     NOTE: In the above example quotations is also optional but there should not
    be space between column name
     EX: SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME, SAL
    SALARY FROM EMP;
     Ex: SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME, SAL
    SALARY, SAL*12 ANNUALSALARY FROM EMP;

    You might also like