MS SQL Server.ppt

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 • 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 • 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 • 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)

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 • 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 • A SQL Server database has lot of objects like – Tables – Views – Stored Procedures – Functions – Rules – Defaults – Cursors – Triggers

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 • 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 • Changing a database Use <dbname> • Creating a database Create database <dbname> • Dropping a database Drop database <dbname>

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 • Arithmetic • Assignment • Comparison • Logical • String • Unary • Bitwise

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 • 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 Create table tablename ( col1 data type, col2 data type ); - Creates a table with two columns Drop table tablename; - Drops the table structure

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 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 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 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 • 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 • 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 • 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 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 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 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 • 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 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 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 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 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 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 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 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

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 • 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 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 • 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 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 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 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 • 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 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 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

MS SQL Server.ppt

More Related Content

Similar to MS SQL Server.ppt

More from QuyVo27

Recently uploaded

MS SQL Server.ppt