ms sql server l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
MS SQL Server PowerPoint Presentation
Download Presentation
MS SQL Server

Loading in 2 Seconds...

play fullscreen
1 / 44

MS SQL Server - PowerPoint PPT Presentation


  • 263 Views
  • Uploaded on

MS SQL Server. Introduction. MS SQL Server is a database server Product of Microsoft Enables user to write queries and other SQL statements and execute them Consists of several features. A few are: Query Analyzer Profiler Service Manager Bulk Copy Program (BCP). Profiler.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'MS SQL Server' - alden


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
introduction
Introduction
  • MS SQL Server is a database server
  • Product of Microsoft
  • Enables user to write queries and other SQL statements and execute them
  • Consists of several features. A few are:
    • Query Analyzer
    • Profiler
    • Service Manager
    • Bulk Copy Program (BCP)
profiler
Profiler
  • Monitoring tool
  • Used for performance tuning
  • Uses traces – an event monitoring protocol
  • Event may be a query or a transaction like logins etc
service manager
Service Manager
  • Helps us to manage services
  • More than one instance of SQL server can be installed in a machine
  • First Instance is called as default instance
  • Rest of the instances (16 max) are called as named instances
  • Service manager helps in starting or stopping the instances individually
instances
Instances
  • Each instance is hidden from another instance
  • Enhances security
  • Every instance has its own set of Users, Admins, Databases, Collations
  • Advantage of having multiple instance is
    • Multi company support (Each company can have its own instance and create databases on the same server, independent on each other)
    • Server consolidation (Can host up to 10 server applications on a single machine)
slide6
BCP
  • Bulk Copy Program
  • A powerful command line utility that enables us to transfer large number of records from a file to database
  • Time taken for copying to and from database is very less
  • Helps in back up and restoration
query analyzer
Query Analyzer
  • Allows us to write queries and SQL statements
  • Checks syntax of the SQL statement written
  • Executes the statements
  • Store and reload statements
  • Save the results in file
  • View reports (either as grid or as a text)
sql database objects
SQL Database Objects
  • A SQL Server database has lot of objects like
    • Tables
    • Views
    • Stored Procedures
    • Functions
    • Rules
    • Defaults
    • Cursors
    • Triggers
system databases
System Databases
  • By default SQL server has 4 databases
    • Master : System defined stored procedures, login details, configuration settings etc
    • Model : Template for creating a database
    • Tempdb : Stores temporary tables. This db is created when the server starts and dropped when the server shuts down
    • Msdb : Has tables that have details with respect to alerts, jobs. Deals with SQL Server Agent Service
creating a database
Creating a database
  • We need to use Master database for creating a database
  • By default the size of a database is 1 MB
  • A database consists of
    • Master Data File (.mdf)
    • Primary Log File (.ldf)
database operations
Database operations
  • Changing a database

Use <dbname>

  • Creating a database

Create database <dbname>

  • Dropping a database

Drop database <dbname>

sql server data types
SQL Server Data types
  • Integer : Stores whole number
  • Float : Stores real numbers
  • Text : Stores characters
  • Decimal: Stores real numbers
  • Money : Stores monetary data. Supports 4 places after decimal
  • Date : Stores date and time
  • Binary : Stores images and other large objects
  • Miscellaneous : Different types special to SQL Server.

(Refer to notes for more info)

operators
Operators
  • Arithmetic
  • Assignment
  • Comparison
  • Logical
  • String
  • Unary
  • Bitwise
select statements
Select Statements
  • To execute a statement in MS SQL, Select the statement and Click on the Execute button in the query analyser or press F5
  • This is used to retrive records from a table
  • Eg. Select * from table1;
    • This will fetch all rows and all columns from table1
  • Eg. Select col1,col2 from table1
    • This will fetch col1 and col2 from table1 for all rows
  • Eg. Select * from table1 where <<condn>>
    • This will fetch all rows from table1 that satisfies a condition
  • Eg. Select col1,col2 from table1 where <<condn>>
    • This will fetch col1 and col2 of rows from table1 that satisfies a condition
select options
Select Options
  • Aggregate functions
    • Sum(col1): sum of data in the column col1
    • Max(col1): data with maximum value in col1
    • Min(col1): data with minimum value in col1
    • Avg(col1): Average of data in col1
    • Count(col1): Number of not null records in table
  • Grouping – Group by col1 : Groups data by col1
  • Ordering – Order by col1 : Orders the result in ascending order (default order) of col1
  • Filtering – Where <<condn>> and Having <<condn>>
table management
Table management

Create table tablename

(

col1 data type,

col2 data type

);

- Creates a table with two columns

Drop table tablename;

- Drops the table structure

insert statements
Insert statements
  • Inserting data to all columns
    • Insert into tablename(col1,col2) values(v1,v2)
    • Insert into tablename values(v1,v2)
  • Inserting data to selected columns
    • Insert into tablename(col1) values (v1)
    • Insert into tablename(col2) values (v2)
update statement
Update statement

Update table tablename

Set colname=value

- This updates all rows with colname set to value

Update table tablename

Set colname=value

Where <<condition>>

- This updates selected rows with colname as value only if the row satisfies the condition

delete statements
Delete statements

Delete from table1;

Deletes all rows in table1

Delete from table1 where <<condition>>

Deletes few rows from table1 if they satisfy the condition

truncate statement
Truncate statement
  • Truncate table tablename
  • Removes all rows in a table
  • Resets the table.
  • Truncate does the following, where as delete statement does not
    • Releases the memory used
    • Resets the identity value
    • Does not invoke delete trigger
alter statements
Alter statements
  • Used to modify table structure
    • Add new column
    • Change data type of existing column
    • Delete a column
    • Add or remove constraints like foreign key, primary key
more table commands
More table commands
  • Viewing tables in a data base:
    • Exec sp_tables “a%”
    • This gives all tables in the current database that starts with “a”
  • Viewing table strucure:
    • Exec sp_columns <<tablename>>
    • Exec sp_columns student;
joins
Joins
  • Cross Join
    • Cartesian product. Simply merges two tables.
  • Inner Join
    • Cross join with a condition. Used to find matching records in the two tables
  • Outer Join
    • Used to find un matched rows in the two tables
  • Self Join
    • Joining a table with itself
cross join
Cross Join

There are two tables A and B

A has a column Id and data (1,2,3)

B has a column Id and data (A,B)

If I put

Select A.Id, B.Id from A,B

This generates output as

A 1

B 1

C 1

A 2

B 2

C 2

self join
Self Join

There is a table called Emp with the following structure:

empid ename mgrid

1 A null

2 B 1

3 C 1

4 D 2

If I want to print all managers using self join, I should write quey as:

select e1.ename from

emp e1,emp e2

where e1.mgrid = e2.empid

inner join
Inner Join

I have 2 tables Student(sid,Name) and Marks(Sid,Subject,Score)

If I want to print the marks of all students in the following format,

Name Subject Score

Select Name,Subject,Score from

Student s join Marks m

On s.sid = m.sid

outer join
Outer Join
  • Right outer Join
    • Print all the records in the second table with null values for missing records in the first table
  • Left outer Join
    • Print all the records in the first table with null values for missing records in the second table
  • Full outer Join
    • Prints all records in both the table with null values for missing records in both the table
left outer join
Left Outer Join

I have a table Employee (Eid, Ename, Mid) and

a table Machine (Mid,ManufacturerName)

Employee

Eid EName Mid

1 ABC 1

2 DEF 3

Machine

Mid ManufacturerName

1 Zenith

2 HP

left outer join29
Left Outer Join

I want to print the employee name and machine name.

If I write a query using inner join, then the second employee will

not be displayed as the mid in his record is not avilable with the second

table.

So I go for left outer join. The query is as shown below:

Select Ename, ManufacturerName from Employee e left outer join

Machine m on e.Mid = m.Mid

right outer join
Right outer Join

Assume data in the tables like this:

Employee

Eid EName Mid

1 ABC 1

2 DEF

Machine

Mid ManufacturerName

1 Zenith

2 HP

right outer join31
Right Outer Join

If I want to find which machine is unallocated, I can use right outer join.

The query is as follows:

Select Ename, ManufacturerName from Employee e right outer join

Machine m on e.Mid = m.Mid

This yields a result

ABC Zenith

HP

full outer join
Full Outer Join

Assume data in the tables like this:

Employee

Eid EName Mid

1 ABC 1

2 DEF

3 GHI 2

Machine

Mid ManufacturerName

1 Zenith

2 HP

3 Compaq

full outer join33
Full Outer Join

If I want to find people who have been un allocated with a system and

machines that are been un allocated, I can go for full outer join.

Query is like this:

Select Ename, ManufacturerName from Employee e full outer join

Machine m on e.Mid = m.Mid

This yields a result

ABC Zenith

DEF

GHI HP

Compaq

views
Views
  • Views are logical tables
  • They are pre compiled objects
  • We can select few columns or rows from a table and put the data set in a view and can use view in the same way as we use tables
views35
Views
  • Create views:

Create view viewname as select stmt

Create view view_emp as select empid,

empname from employee;

  • Select from views:

Select * from viewname

Select empid,empname view_emp;

  • Drop views:

Drop view viewname

Drop view view_emp;

string functions
String Functions
  • Substring(string,start,length) – Will fetch characters starting at a specific index extending to length specified.
  • Left(string,length) – Fetches number of characters specified by length from left of the string
  • Right(string,length) – Fetches number of characters specified by length from right of the string
  • Len(string) – Returns the length of a string
string functions37
String Functions
  • Ltrim(string) – Removes leading spaces in a string
  • Rtrim(string) – Removes trailing spaces in a string
  • Lower(string) – Converts the characters in a string to lower case
  • Upper(string) – Converts the characters in a string to upper case
numeric functions
Numeric Functions
  • ABS(Number) – Fetches the modulo value (Positive value) of a number
  • CEILING(Number) – Fetches the closest integer greater than the number
  • FLOOR(Number) – Fetches the closest integer smaller than the number
  • EXP(Number) – Fetches the exponent of a number
numeric functions39
Numeric Functions
  • POWER(x,y) – Fetches x raised to the power of y
  • LOG(Number) – Fetches the natural logarithmic value of the number
  • LOG10(Number) – Fetches log to the base 10 of a number
  • SQRT(Number) – Fetches the square root of a number
indexes
Indexes
  • Indexes make search and retrieve fast in a database
  • This is for optimizing the select statement
  • Types of index
    • Unique
    • Non unique
    • Clustered
    • Non clustered
index
Index

Create index indexname on

tablename(columnname)

This creates a non clustered index on a table

Create unique clustered index index_name on

Student(sname);

This creates a unique and clustered index on the

Column Sname.

sequences
Sequences
  • This creates an auto increment for a column
  • If a table has a column with sequence or auto increment, the user need not insert data explicitly for the column
  • Sequence is implemented using the concept of Identity
identity
Identity
  • Identity has
    • A seed
    • An increment
  • Seed is the initial value
  • Increment is the value by which we need to skip to fetch the nextvalue
  • Identity(1,2) will generate sequence numbers 1,3,5,7…
sample
Sample

Create table table1

(

Id integer identity(1,1),

Name varchar(10)

)

It is enough if we insert like this:

Insert into table1(name) values(‘Ram’);

Ram will automatically assigned value 1 for id