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.

Sql Server Language :- TSQL

SQL Server Tools :-

1. SSMS
2. SSBIDS
3. SQL Server Profiles
4. DB Tuning Advisor.

Transact-SQL (T-SQL):

User a TSQL a SQL Server DataBase 

• TSQL is a language used to communicate with SQL Server.
• TSQL is based on SQL language.
• SQL is introduced by IBM and internal name of that language [SQL]is called “SEQUEL” and later re-named to “SQL”.

SQL   :-  (Structure query language)

User communicates with DB server by submitting in structure is called “queries”.

SQL follows ANSI, ISO standards:

1. SQL server – RDBMS
2. ORACLE – RDBMS product
3. TSQL – RDBMS LANG
4. SQL – RDBMS LANG.

SQL is common to all rdbms

• QBE and QUEL are languages for rdbms Mysql
• QBE- Query by example- which is used in ms access
• QUEL-query language

Which are not standardized, so sql is popular

Depends on operators performed over database so sql is categorized in following sub-languages.

• DDL [data definition language]
• DML [data manipulation language]
• DRL [data  retrieval language]
• TCL [transaction control language]
• DCL [data control language]

DDL :

• Used to define data.

Example:      Empno         Ename         Sal        Data defination

                                1                    XX            1000         Data

It is a set of command to perform operation over data definition.

• Create ->To create data definition
• Alter->To modify data definition
• Drop->To remove data definition
• Truncate->To empty the table

Note    :  Data definition exits without data but not vice versa.

DML    :

DML are set of commands to perform operators or data.

• Insert->To insert data.
• Update->To modify data.
• Delete->To delete data.
• Merge [from 2008]

It is also called [upsent]

• It is combination of insert & update.

DRL:

It is a set of command to retrieve data from db.

• SELECT

TCL  :

Set of commands to process transaction.

• Commit->To Save transactions.
• ROLLBACK->To cancel Transaction
• Save Transaction->To cancel part of transaction.

DCL    :

Set of commands to control data (or)to provide security.

• GRANT->To give permission to users.
• REVOKE->To take back the permission for user
• DENY

SQLServer Normalization:–

Normalization Definition:

• Normalization process  of organizing the data in any table and normalization in the process of reducing redundancy in the table.
• Normalization process determines which attributes should be grouped in a table.
• If the table contains redundancy the following problem in this problem are called “anomolies”.

• Insert  anamoly
• Update anamoly
• Delete anamoly
  • Normalization process is a “set of rules” and each role is called “one normal form”
  • There are 6 normal forms:

1. [1st normal form]  1NF
2. [2nd normal form] 2NF
3. [3rd normal form] 3NF
4. [Boyce-code normal form] BCNF [Boyce-code]
5. [4^th normal form] 4NF
6. [5^th normal form] 5NF

• Normalization [manages] more recommends more tables- less-columns.
• Normalization recommends store data in its own table

Functional Dependency in DBMS with Example  :-

• There is a table R (A, B), it attributes B depends on A then there is a functional dependency between AB and denoted as follows A-> B

Types of Functional Dependency in SQL Database  :

• Full Functional dependency.
• Partial Functional dependency.
• Transitive Functional dependency.

Full Functional Dependency with Example:

•  In a table if non key depends on part of the key then the dependency is called “Full Functional dependency”.

Partial Functional dependency with Example:

• In a table ,if the non key depends on  part of the key then the dependency is called “partial Functional dependency”

Transitive Functional dependency with Example:

• In a table, if the non key attributes depends on  another non key then it is called “Transitive Functional dependency”

First Normal Form in DBMS with Example:-

A table is said to be in first normal form, If there are no repeating groups in it (or) all attributes are atomic (single).

T1 Table

Billno       Bdate     Ccode     Cname     Addr      Icode  Dept     Qty     rate    Volume   THbill

In the above table is  not according  to about 1NF because some of the attributes are “non-atomic attributes” decompose the table as follows.

Bill Table

• Billno        Bdate       Ccode     Cname     Addr     Thbill 

Item Table

• Billno         Icode      Dept       Qty       Rate       Value.

Second Normal Form in DBMS with Example: 2NF

A table is said to be in 2Nf,if it is in 1NF and no partial dependencies should exist in that table.

Example:

• In the above table is not according to 2NF then decompose the table as follows. 

Bill details: Billno         Icode                  Qty          Value

Item           :Icode           Desc.                    Rate 

Bill

Sellno    sdate    ccode   cname    caddr     thbill

Items

Icode        desc Rate 

Bill details:

Billno       Icode     Qty    Value.

Third Normal Form in DBMS with Example:-

Rule:

A  table is said to be in 3NF,if it is in 2NF and

1. No transitive dependencies should exists in that table.

2. No derived attributes should exist in that table.

• In the above table is n’t according to 3NF then decomposing the table 

CUST:

Ccode       Cname        Caddr

BILL:

Billno           Bdate               Ccode

ITEMS:

Icode       Descript           Rate

BILLDETAILS:

Billno             Icode           Qty

 

DATABASE DESIGN in SQL Server:-

Steps followed in database development:

• Analysis
• Design
• Development
• Implementation
• Maintenance.

1. Db designed by “DB Designer(or) DB Architect”
2. The DB designer designed the  db by using two techniques,

                       (i) ER model:  Top-down  Approach

                       (ii) Normalization: Bottom up    Approach

1. DB developed by “DB Developer”
2. The developer the will develop the database by using “RDBMS TOOL” i:e sql server (or) oracle. 

ER MODEL in SQL Server   :–

1. ER stands for “entity relationship” model
2. It is a “Graphical  representation of data”.
3. The  diff. element of ER model

•       Entities
•       Entity sets
•       Attributes
•       Relationships

ENTITY in SQL Server with Example:–  

• It is an object.

Ex: Employee,customer,bank A/c,course etc.

ENTITY SETS in ER Diagram with Example:–

• Collection of entities that share common properties is called “Entity Sets”. 

Example —

 

Symbol: rectangle represents the entity set.

    Example:  Employee

Examples of ATTRIBUTES  in Database:–

• Attributes one the properties of entity.

Example:–

          Empid       ename      job     sal

These are all properties of employee.

• Attributes are indicated by symbol is “Oval or Ellipse”.

RELATIONSHIPS in SQL Server    :–

• A relationship is a association b/w entities of entity sets.

 

• The relationship is indicated by symbol  is “dimension”

Types of Relationships in SQl Server :–

•  1 to 1
• 1 to many
• Many to Many

  

• ER  model can be used to at “Design part”

Difference Between ER model and Relational Model

ER Model                                               Relational Model

Entity sets                                         Here,entity sets are converted to “Tables”

Attributes                                           “Fields”

Relationships                                     “Foreign keys”