Sql Server Basics

1. Open Sql Server:

 Click on “Start” – “Programs” – “Microsoft Sql Server 2005” – “Sql Server Management Studio”.
 It displays “Connect to Server” dialog box.
connect  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)
(or) 4. Authentication: Windows Authentication
 After successful login to the server, it displays “Sql Server Management Studio” window.
sqlserver

2. 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 existing on this system.
 If you expand any database (For ex: sample), it displays some options like “Tables”, “Views”, “Programmability” etc.
 When you expand the “Tables” option, it displays the list of tables that are existing 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.
sqlser table

3. 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.
 Click on “Save” button to save the table. Then it askes for the table name.
 Close the window finally.

4. Data Types in Sql Server:

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

5. 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.
 Then the SQL statement will be executed.

6. IMP SQL Statements:

 DDL:
1.CREATE
 create table tablename(col1 datatype1(width1), col2 datatype2(width2), …);
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 col1,col2,.. from tablename;
 select * from tablename where condition;
2. INSERT
 insert into tablename values(val1,val2,…);
3. DELETE
 delete from talename;
 delete from tablename where condition;
4. UPDATE
 update tablename set column1=value1, column2=value2;
 update tablename set column1=value1, column2=value2 where condition;

PL/SQL:

 Procedures:

create procedure procedurename(@variable1 datatype1(width1),…)
as begin
declare @variable datatype(width)
…….
……. end

 Functions:

create function functionname(@variable1 datatype1(width1),…) returns returndatatype
as begin
declare @variable datatype(width)
…….
…….
return returnvalue
end