<
  Go Top
advertise here!!!

Library || READ MORE || DOWNLOAD PDF || QUESTION ANSWERS || HOME

  • What is Sql ↓

    • SQL stands for Structured Query Language.
    • SQL is used to communicate with a database.
    • SQL is a query language used for accessing and modifying information in one or more data tables.
    • Sql Used for modify, select, delete, insert data in table using query.

    Database:Collection of data

    Tables:Collection of rows and columns

  • Used and installation Sql ↓

    Firstly you have to install Microsoft sql software. For step by step installation process please visit below Link.

    Visit Step by step Install Sql
  • Create Database ↓

    Once SQL Server 2008 express is installed with management tools do the following

    • On windows Start->Programs->Microsoft SQL Server 2008->SQL Server Management Studio
    • You should see something like computernamehere\SPATIAL and now login with sa (Standard mode) or just the windows account assuming you gave current user admin rights.
    • In login screen you can use without login just choose windows authentication in authentication tab

    Method-1

    Select Databases -> Right mouse click -> New Database

    Give database a name and click the OK button

    Method-2

    Create database Using Command

    Syntax:

    CREATE DATABASE database_Name;

    Example:
    create database college;

  • Drop or Delete Database ↓

    Drop command used for delete or remove database

    Syntax:

    Drop DATABASE database_Name;

    Example:
    Drop database college;

  • Rename Database ↓

    You want to change your database name then use following command.

    Syntax:

    EXEC sp_renamedb 'Old_Dbname’, 'New_DBname'

    Example:
    EXEC sp_renamedb ’College’, ‘School’;

  • Sql Data Types ↓

    Here we are describing mostly used data type.

    In SQL Server, each column, local variable, expression, and parameter have a data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

    int
    Sr.No Data types Name Description
    1 Use for numeric value
    2 Char(n) Use for character string, n is length
    3 Varchar(max) Use for character string,length is different
    4 nvarchar Use for character string, length is different
    5 binary Use for Binary data
    6 varbinary Use for Binary data
  • Creating and Run Table in SQL ↓

    Creating a table in sql usedCreate table command.

    Syantx:

    Create Table tabl_name
    (
    Column 1 datatype(size),
    Column 2 datatype,
    ……………………..column n datatype() 
    );
    

    Example:

    Here we are taking an example for creating a Dummy table.

    Create table user_master
    (
    User_Id int identity (1,1) primary key,
    First_Name varchar(15),
    Last_name varchar(15),
    Contact No. varchar(12),
    Address varchar(100),
    Designation varchar(25),
    Salary int;
    User_create _Date datetime
    )
    

    How to execute table?

    First select your command and press F5 on keyboard. Your table successfully created.and display like that.

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
  • IDENTITY OR AUTOINCREMENT↓

    Identity specify auto increment a number i.e. suppose in dummy table user_id (1) row already insert again I want to insert one row more then user_id by default increment 2.this process will be continue.3.4..5..6..7…8.Plz see example.

    EXAMPLE: In dummy table insert one row which user_id is 1.Now again execute insert command

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015

    INSERT ONE ROW USING INSERT COMMAND

    Insert table dummy set values(‘sandy’,’jha’,’123456’,’delhi’,’web devloper’,’20000’,getdate());

    User_IdFirst_NameLast_nameContact No.AddressDesignationSalaryUser_create _Date
    1MohitGupta1236468New DelhiSoftware Engg50000002/12/2015
    2sandyjha1236468DelhiWeb devloper2000002/12/2015

    In above insert command we are not inserting user_id but in my dummy table user_id auto increment 2.I hope Identity is clear now.

  • Rename table Name or Alter Table Name ↓

    Syntax:

    ALTER TABLE Old_tabl_name RENAME TO new_tabl_name;

    Example:

    ALTER TABLE dummy RENAME TO User_master;

  • SQL-SELECT COMMAND ↓

    Database Table(Dummy)

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015
    3 naveen Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    4 Natik Gupta 1236468 New Delhi student 00000 02/12/2015

    Select command use for select data from database.The most commonly used SQL command is SELECT statement.A query can retrieve data selected information or all data.

    Syantx:

    select column_name1,column_name2..n_colums from table_name 

    Example:

    select user_id,First_name,Salary from Dummy 

    OutPut:

    User_Id First_Name Salary
    1 Mohit 500000
    2 sandy 500000
    3 Naveen 500000
    4 natik 00000

    you have Notice that above query is fetching only selecting column data from Dummy Table.If you want to fetch all data then usedStar(*)

    From:

    Using Star(*) User can select all data from table.

    Syantx:

    select * from table_name 

    Example:

    select * from dummy 

    Output:

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015
    3 naveen Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    4 Natik Gupta 1236468 New Delhi student 00000 02/12/2015

    All Rows and Columns are displaying using star(*).

  • Sql Select-Where Clause ↓

    Where

    When user want to select specfic data or particular row that time used to where Clause.

    Syantx:

    select * from table_name where column_name='value';       Or
                                              
    select column_name1,column_name2 from table_name where column_name='value';

    Example:

    select * from dummy where user_id=3; 
    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    3 naveen Gupta 1236468 New Delhi Software Engg 500000 02/12/2015

    In above table we are selecting all data from dummy table where user_id=3;

    Sql-IN

    IN use with where Clause.Using IN user can select multiple Row data.

    Syantx:

    select * from table_name where col In('value1',value2)

    Example:

    select * from dummy where first_name In('mohit','sandy')
    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015

    In above Command Multiple Row are display.

  • Select Condtional command(AND,OR) ↓

    Database Table(Dummy)

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015
    3 naveen Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    4 Natik Gupta 1236468 New Delhi student 00000 02/12/2015

    AND Operators

    It is condtional Operator,Display data that both condtion are true.

    Syanatx:
    Select * from tbl_nme where Condtion1 AND condtion2 AND con..n
    Example:
    Select * from dummy where user_id=2 AND Address='Delhi'

    OutPut:

    Blank

    Above command output is blank beacuse we use condtion that display all data from user id =2 and adrs is delhi but In Dummy table user id=2 is right but address is wrong.So thatCondtion is false.If both condtion is true then data will display.

    Now see other Example

    Example:
    Select * from dummy where user_id=2 AND Address='New Delhi'

    OutPut:

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015

    Now Both Condtion is true.

    OR Operator

    It is condtional Operator,Display data that if one or more condtion is true.

    Syanatx:
    Select * from tbl_nme where Condtion1 OR condtion2 AND con..n
    Example:
    Select * from dummy where user_id=2 OR Address='Delhi'

    OutPut:

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015

    In above Command only one condtion is true and data is display.

  • SQL-Insert Command ↓

    Insert Command

    Insert Command use to insert data in table.If you want to insert data in your table then used Insert Command.

    Syanatx:
    insert into tabl_nme(column_nme1,column_nme2,...n) values(values1,values2,..n)
    Example:
    insert into dummy(First_name,Last_name,Conatct_no,Address,Designation,Salary,Usr_crte_date) values('manisha','dubey',56565,delhi,account,500,getdate())

    OutPut:

    Database Table(Dummy)

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015
    3 naveen Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    4 Natik Gupta 1236468 New Delhi student 00000 02/12/2015
    5 Manisha Dubey 56565 Delhi Account 500 02/12/2015

    In above Command One row have inserted.i.e userid=5.User id Column is identity(auto_increment).We have already discuss about Identity.

  • SQL- UPDATE COMMAND ↓

    Update Command

    Update Command is used for edit,modify and save existing data.

    Syanatx:
    UPDATE table_name
    SET column1 = value1, column2 = value2...., columnN = valueN
    WHERE [condition];
    Example:
    Update dummy set First_Name='Tannu',salary=5000 whree user_id=5;

    OutPut:

    Database Table(Dummy)

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015
    3 naveen Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    4 Natik Gupta 1236468 New Delhi student 00000 02/12/2015
    5 Tannu Dubey 56565 Delhi Account 5000 02/12/2015

    In dummy table Before updating was First_name=MAnisha and salary= 500.And After Update is First_name=tannu and Salary =5000;

  • DELETE,DROP AND TRUNCATE COMMAND ↓

    Database Table(Dummy)

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    2 Sandy jha 1236468 New Delhi Web devloper 500000 02/12/2015
    3 naveen Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    4 Natik Gupta 1236468 New Delhi student 00000 02/12/2015
    5 Tannu Dubey 56565 Delhi Account 5000 02/12/2015

    Delete Command

    Delete command used for delete all row from table or specify with condition.

    Syanatx:
    Delete from tabl_nme.............for all data removed from table 
    Delete from tabl_nme where column_nme='value'.................for specfic row delete
    Example:
    Delete from dummy
    All rows and columns have removed. Example:
    Delete from dummy where id=2;
    Output:

    Database Table(Dummy)

    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
    1 Mohit Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    3 naveen Gupta 1236468 New Delhi Software Engg 500000 02/12/2015
    4 Natik Gupta 1236468 New Delhi student 00000 02/12/2015
    5 Tannu Dubey 56565 Delhi Account 5000 02/12/2015

    You have notice that user_id=2 row have Removed

    DROP Command

    Drop table command delete all data and index of table.

    Syanatx:
    Drop table Table_name; 
    Example:
    Drop table dummy
    OUTPUT:

    Blank

    Everything removed from Table.


    Truncate Command

    Truncate command used for delete all data from table.it is similar to delete but truncate command not specify where condition.

    Syanatx:
    TRUNCATE TABLE tabl_name;
    Example:
    Truncate table dummy
    OUTPUT:
    User_Id First_Name Last_name Contact No. Address Designation Salary User_create _Date
  • Difference between truncate delete and drop ↓

    Truncate

    Truncate command used for delete all data from table.it is similar to delete but truncate command not specify where condition

    Delete

    Delete command used for delete all row from table or specify with condition

    Truncate

    Drop table command delete all data and index of table

    Note:-For more detail please see above with example.
  • SQL-ALTER ↓

    Add Column in table using alter

    Once we have created a table and now want to add one or more column used Alter Command.

    Syanatx:
    ALTER TABLE tabl_name ADD column_name datatype; 
    Example:
    ALTER TABLE dummy ADD department varchar (50);  

    Drop Column in table using alter

    If want to remove column from table used drop alter command.

    Syanatx:
    ALTER TABLE tabl_name DROP COLUMN column_name; 
    Example:
    ALTER TABLE dummy DROP COLUMN department;  

    Change data type in table using alter

    If want to change data type in table for any column, used alter command.

    Syanatx:
    ALTER TABLE tbl_name ALTER COLUMN column_name datatype; 
    Example:
    ALTER TABLE dummy ALTER COLUMN contact_no int;
  • TEST ↓