Category: Tutorials

In operation :-

• It is used to compare column values with multiple values.

Syntax: – in (v1, v2, v3—)

                      Not in (v1, v2, v3—)

Example:- Select * from emp where job in (‘clerk’,’manager’)

• Display employee list who are not working for 10 (or) 20th departments.

Example:- select * from emp where deptno not in (10, 20)

Display employee records earning between 2000&5000 and earning multiple of 50 working as clerk (or) manager & not dept in 10&20.

Example:– Select * from emp where sal between2000 and 5000 and Sal%50=0 and Job in(‘clerk’,’manager’) and Deptno not in (10, 20)

Between operation:-

• Between operation is used to compare column Value with range of values [it includes lower & upper values]

Syntax:-Between v1 and v2    (or) Not between v1 and v2.

Example:-Select * from emp  Where sal between 2000 and 5000

Note: – Between operator always works with lower limit & Upper limit but not upper limit & lower limit.

Display employee records working as clerk (or) manager

Select * from emp Where job=’clerk’ on job=’manager’

Clauses in SQL SERVER:-

Different clauses in sql server

1. Where
2. Order by
3. Distinct
4. Group by
5. Having
6. With
7. On

where clauses in sql server  :-

• Where clauses is used to get particular records from the table based on a condition

Syntax: Select<collist>from<Tabname> where <condition>.

Condition in Where clause query:

• Colname operatior value:  It must be relational (or) special operatior
• Display employeerecord whose empid=2

                        Select *from emp

Where empid=2

Order of declaration  :- [user follows this one]

Means which clause

•                      Select
•                      From
•                      Where
•                      Group by
•                      Having
•                      Order by

Order of execution    : – [system follows]

•   From
•   Where
•   Group by
•    Having
•    Select

Display employee records earning more than 2000.

                  Select * from emp where    sal>2000.

Display employee records earning more than 2000 and less than 5000

                Select * from emp where sal>2000 and sal<5000

ALIAS:-

• Alias means another name (or) alternative name.
• It is two types.

            (i)  Column Alias -> Alias declared for column is called column alias

            (ii) Table alias   -> Alias declared for table is called Table alias

Column Alias:

Syntax: Colname/

Example   : Select ename, sal, sal*12 as Anusal from emp.

Ename                       Sal                 Anusal

A                                500                  6000

B                                 600                 7200

C                                 700                  8400

• If column alias contains space, alias must be given to Double quotes.

Example    : – Select ename, sal, sal*12 as “Anual sal” from emp.

• Display ename,sal,hra,da,tax and totsal

       Hra      30%sal

       Da       20%

       Tax      10%

    Total   sal+hra+da-tax.

Example  :- Select ename,sal,sal*0.3as hra,sal*0.2 da,sal*0.1 tax,sal+ (sal*0.3)+ (sal*0.2)-(sal*0.1) total from EMP.

Table alias:

        Syntax: tablename [as] alias.

Example:  Select * from emp as e

Retrieving Data from Table   :-

• “Select “command is used to retrieve data from table.
• “Select” command is used to the following operations.

(i)  Select
(ii)  Project                    
(iii)  Join
(iv) Set operations -> union, intersect

These operations are data retrieval operations.

SELECT  :-

• Selecting particular records from Table.

Example   :- sal>200

Project   :

• Selecting particular columns from table

Example: – ename^π, sal^(emp)

JOIN

• Greeting data from multiple tables.

Syntax:                 

    Select<collist>

    From<tabname>

   Example   : Display ename and salaries

Select ename, sal from emp

• Display all the data from emp table

Select * from EMP

• By default Sql server doesn’t column headings for derived columns

Select ename, sal, sal*12  From emp

Ename             sal          no column name

A                     500           6000

B                      600           7200

C                      700            8400

Sql Server Operators   :-

Arithmetic Operators:

• +,-,*,/,%

Relational Operators:

• >, <,>=, =, <> (or)! =

Logical Operators:

• And, or, not

Special operators:

• Between, in , like, is, any, all, exists, pivot [from 2008]

Set operators:

• Union, union all, intersect, except.

Creating a table:–

       Syntax: Create table <tablename>

     (Column name data type[(size)]

      Colname data type [(size)],

       ———– )

Rules:

(i)The table name should start with “alphabet”.

(ii) Spaces and special symbol aren’t allowed except(underscore_).

(iii)A table name can contain maximum of 128 character.

(iv)A table can contain maximum of 1024cols.

Example:   Create table emp

(empid     smallint

Ename       varchar(20),

Job            varchar(20),

Sal              small money,

Hiredate     smalldate time) 

Inserting A Data       :–

• “Insert” command is used to insert data into a table.

Syntax:-insert into<table name>

                  Values (v1, v2, v3, ——–vn).

Note: — String & dates must be enclosed with single queries

          Eg: – insert into emp

                 Values (1, Anum, Hr, 5000, 07-oct-09)

Inserting null values:-

• Null values can be inserted into two ways

(i)                 Explicitly—means——–by user

(ii)               implicitly————-by system

• Inserting null value explicitly, we have to specify ”null”.

Example:-insert into emp values (2, B, NULL, NULL, 07-10-09)

• Inserting null values implicitly   :

Syntax: insert into<tabname> (sal list) Values (value list)

Example:  insert into emp (empid, ename, hiredate) values (8,’c’,’08-oct-09’)

DATA TYPES IN SQL SERVER:-

1) String type: –

In sql server, string comparison is not case sensitive Small or alphabet, lower or upper case

(a) Char  :

It allows char Data up to 4,000 bytes

Example:- ename char (20)

• It is called fixed length character data types.
• Example:  ravi               20bytes

                                  Arungini       20bytes.

Note: “here memory is wasted”.

(b) varchar:

• It allows character data up to 8,000bytes (or) character.

Example:-ename    varchar(20).

• It is also variable length character data type.

Example:- Ravi         4bytes

                       Arungini      9 bytes

Note: – Here memory is not wasted

When no of character more than 8000 then we have declared like this

Comments varchar (max)

(c) nchar / nvarchar :

• Char/varchar is based on Ascii.
• Nchar/varchar is based on Unicode. -> range(0-65535bytes) -> it is char of diff. languages

Char/varchar -> 1bytes occupies 1 char

nchar/nvarchar -> 2bytes occupies 1char

(2) Integer Types:-

It allows whole number.

• Tinyint -> 1 bytes
• Smallint ->2bytes
• Int ->4bytes
• Bigint ->8bytes

Example:  empno smallint

(3) Decimal types:-

• It allows real number

Example: decimal (p.s)                  

•                   P -> precision -> total no of digits -> max38
•                   S -> scale -> the number of digits allowed after decimal -> it can be maximum 0 to p

   Example:   sal decimal (7, 2)

              5000.50

(4) CURRENCY:-

The currency fields have two data types:

•  small money->it occupies 4bytes
• Money->it occupies 8bytes.

Example: –       salary                          small money

                       Bank balance                       money

(5) DATE TYPES   :–

• This types also two data types

      (i) Small date time———–4bytes

       (ii) Date time————-=8bytes

Example: —  dob               smalldatetime

                         Date                 datetime

• If small datetime it is range is Jan1 1900 to dec 31 9999.
• If datetime,it’s range is Jan 1753 to 31 dec 9999.

(6) Pictures & Images   :-

it’s have 2 data types

•     (i)Binary -> 4000bytes
•     (ii)Varbinary ->8000bytes

Example: –   empno to                   binary(500)

If picture size exceeds more than 8000bytes then declared

           Empno                           varbinary (max)

Columns are declared with varchar (max) and varbinary(max).its called “LOBS”.

(7) UNQUE IDENTIFIER:–

• A column declared with unique identifier will uniquely identify each record.
• It’s similar to row id.

Example: – F1 UNQUE IDENTIFIER

• Unique identifier can be assign to f1 by using a function called “NEWID”.

(8) XML:

• It allows xml document. 

(9) Sql variant:-

• It allows any type of data.
• Example :strings, numbers etc

(10) Time stamp:-

It allows date and time.

Empid             ename          sal            last update

 1                        x                  5000              null                                               

Here time stamp is last update.

CREATING A NEW DATABASE:

To create a new db in an object explorer select databases -> right click  -> SSMS ->Select ->new data base ->Object explorer side ->Server(SQL server 9.0.1399-sa) ->it is a instance ->Database ->Right click and  Select ->New db

In New DB:

Enter DB Name   : DB6PM

• At the time of creating DB sql server mainly creates two files, one file is called “data file” and second file is called “log file”
• The data file contains “actual data” and log file contains “operations performed over DB”

In sql server database is a collection of objects.

DB6PM  [it is a new DB Name] Contains below:

• Tables
• Views
• Synonyms
• Indexes
• Indexed views

The above five objects comes under Non procedural part

• Procedures
• Functions
• Triggers

These Three comes under procedural part 

How to open the Database:–

• Select the Database
  • Right click
  • Select
  • Select new query

How to delete the DB :–

• Select the database
• Right click
• Select
• Delete

When we install sql server, we will be getting the following services :- 

1. DATA BASE ENGINE
2. Integration Services
3. Reporting Services
4. Analysis Services

• Among those services integration service is common to all sql server instances.
• We configure each instance separately.
• The first installation is called “default-instance “and next installation is called “Named Instance”

When you install sql server the following tools are installed :-

These tools are categorized into two types

1) Developer Tools 

• SSMS –> OLTP
• SSBIDS –>OLAP  

2) Database Administrator Tools

• SSMS
• SSBIDS
• SQL server profiler
• DB tuning advisor
• Configuration Manager.

When you opened the “SSMS”,it will be asking following Input  :-

Server Type   :–

• It will shows the list of services,from the that select the “DB ENGINE”.

Server Name  :–

• Name  of the server[computer name]
• You cannot to default instance,server name must be name of the server.
• To connect default named instance,give to server name is server\inst2.

Authentication  :–

1) There are two types of authentication.

•        Windows authentication
•        Sql server authentication

2) If authentication is windows authentication then window user can connected to sql server
 
3) If authentication is sql server authentication,then so the user must sql server user

the user must provide that

• Login        : sa[system administration]
• Password   :*** 

Start –> All programs –> Sql server2005 –> SSMS(or) –> SSBIDS

By default sql server comes with following databases  :-

• Master DB
• Model DB
• MSDB
• TempDB

Sql Server Versions    :–

•         Sql server 6.5
•         Sql server 7.0
•         Sql  server 8.0[sql server 2000]
•         Sql server 9.0 [sql server 2005]
•         Sql server 10.0[sql server 2005]

Sql Server Instances    :–

• Each installation of sql server is called “one instance”
• In sql server 2005, so we can have 64 instances in one system.