Wednesday, March 26, 2014

SQL SERVER BASIC

SQL SERVER BASIC

1.  Open SQL Server:

  • Click  on  ―> Start ―> Programs―> Microsoft  SQL  Server  2005 ―> SQL  Server Management Studio.
  • It displays Connect to Server dialog box.
SQL SERVER Basic
  • Enter the following values:
1.  Server type: Database Engine
2.  Server name: Name of the system (You can see the computer name in the My Computer‖ properties)
3.  Authentication: SQL Server authentication    
   -->  Login: sa
   -->  Password: xxxx (The password can be given at the time of SQL Server software Installation) Ex: 123         (or) Authentication: Windows Authentication
  • After successful login to the server, it displays SQL Server Management Studio window.
SQL SERVER Management Studio

2.  Object Explorer:

object Explorer
  • In  SQL  Server  Management  Studio,  the Object Explorer displays  the information about the databases, tables, stored procedures and functions.
  • First  of  all,  expand  the  option Databases;  then  it  displays  list  of databases  that  currently  exist  on  this system.
  • If  you  expand  any  database  (For  ex: sample),  it  displays  some  folders  like Tables, Views, Programmability etc.
  • When you expand the Tables folder, it displays  the  list  of  tables  that  exist  the selected database.
  • If  you  want  to  see  the  table  structure, right  click  on  that  table  and  choose Modify  option.  There  you  can  make any  changes  in  the  table  design (structure).
  • If you want to open the table data, right click on that table and choose Open Table. Then the table rows will be opened. Here  also  you  can  make  changes  in  the  table  data,  and  also  you  can  add  new rows here.

3. Creating New DataBase :

  • A database is a collection of tables.
  • To  create  a  new  database,  right  click  on  Databases  and  choose  New Database.
  • Then enter the new database name. Ex: mydata
  • Click on OK.
Create New Database

4. Creating a New Table:

  • Right click on Tables option in the Object Explorer and choose New Table.
  • Enter the table structure of the new table
Create SQL Server Table
  • Click on Save button to save the table. Then it asks for the table name. Enter the desired table name 

Choose name
  • Close the window finally.

5.  Important Data Types in SQL Server:

  • varchar(width)
  • datetime
  • numeric(width)
  • int
  • float
  • decimal(width,dec)
  • bit
  • image

6.  Working with Query window :

  • Query window is a window, where you can  enter the SQL queries and execute them.
  • Open  the Query  window,  by  clicking  on New  Query  window  option  in  the toolbar.
  • Select  the  database  from  the  database  list,  in  which  your  query  is  to  be executed.
  • Enter the required SQL query in the window.
  • To execute, press F5 (or) click on Execute button in the toolbar
SQL Query

7.  IMP SQL Statements:

  • DDL:
           1.  CREATE
                create  table  tablename(column1  datatype(width),  column2 datatype(width), …);
           2.  DROP
                drop table tablename;
           3.  ALTER
             ->   alter table tablename add columnname datatype(width);
             ->   alter table tablename drop column columnname; 
             ->   alter  table  tablename  alter  column  columnname datatype(width);
  • DML:
          1.  SELECT
          ->  select * from tablename;
          ->  select column1, column2, .. from tablename;
          ->  select * from tablename where condition;
          ->  select column1, column2, … from tablename where condition;
          2.  INSERT
          ->  insert into tablename values(value1, value2,…);
          3.  DELETE
          ->  delete from tablename;
          ->  delete from tablename where condition;
          4.  UPDATE
          ->  update tablename set column1=value1, column2=value2;
          ->  update  tablename  set  column1=value1,  column2=value2  where condition;

Some Exercise on SQL SERVER

use master
drop database test
GO

create database test
GO

use test
GO

create table Products
(ProductID int primary key,
ProductName varchar(40),
Price decimal(18,2)) 

insert into products values(101,'Monitors',7890)
insert into products values(102,'Keyboards',450)
insert into products values(103,'Mouses',590)
insert into products values(104,'Processors',6202)
insert into products values(105,'RAM',2829)
select * from products
select ProductID,ProductName from Products
select Price*10/100 Tax from Products
select * from Products where Productid=104
select * from Products order by price desc
select * from Products where price between 2000 and 8000
select * from Products where price not between 2000 and 8000
select * from Products where price like '%0.00'
select * from Products where price like '___.00'
update products set price=price+1000
select * from products
delete from products where price<1500
select * from products

8.  T-SQL (Transaction SQL):

  • Procedures:
           create procedure procedurename(@variable datatype(width),…)
           as begin
           declare @variable datatype(width)
           …….
           …….
           end
  • Functions:
           create  function  functionname(@variable  datatype(width),…) 
           returns returndatatype
           as begin
           declare @variable datatype(width)
           …….
           …….
           return returnvalue
           end
Note:  The procedure can’t return any value; and a function should return any  value. For every variable in T-SQL, we have to prefix “@” symbol without fail.

Some Exercise on T-SQL :

Procedure:


create procedure ShowSquare(@a int)
as begin
declare @sq int
set @sq = @a * @a
print @sq
end
GO
execute ShowSquare 5

Function:

create function GetCube(@a int) returns int
as begin
declare @cb int
set @cb = @a * @a * @a
return @cb
end
GO
select dbo.GetCube(5) 

No comments:

Post a Comment