Sql Server 2000

Contents Part 1 Basic Database Concepts Part 2 Introduction to SQL Server Part 3 Transact – SQL Part 4 Advanced Transact – SQL

PART 1 Basic Database Concepts Contents Database fundamentals Relational Database Design Basics Normal Forms Relationships Peek into SQL Server Database

Database Fundamentals What is a Database ? A Database is a repository for structured collection of Data/Records that provides a standard access method. Important Objects used in Databases Table – Set of Data Elements that is organized using a model of Rows and Columns. Index – An Index is a data Structure that improves speed of Operation in a table. Clustered Non Clustured Query – Standard language to fetch and store data.

Relational Design Basics Relational v/s Flat File Structure Relational Flat File ---- ---- ---- ---- ----- ---- …… … . Producer Album Artist CD No Artist Album Producer

Relational Design Basics contd…. Entity – Relationship Model ER model is a conceptual data model that views the real world as entities and relationships. Entities - Entities are the principal data object about which information is to be collected. e.g . Employee, Department etc. Attributes - Attributes describe the entity of which they are associated. A particular instance of an attribute is a value . e.g . Employee can have attributes like Employee Name, Employee Number etc. Relationships – Relationship represents an association between two or more entities.

Relational Design Basics contd…. Entity – Relationship Diagram Employee Department Employee ID Department ID One Many Manages Entity Name Attribute Name Relationship Name

Normal Form First Normal Form No Duplication of Data Within a Row Each Row must have a unique identifier Jack John Bob 8 Sam Brat Paul 11 Student_3 Student_2 Student_1 Room_No John 8 Paul 11 Bob 8 Student Room_No 222 8 2 111 8 1 333 11 3 Student_ID Room No ID

Normal Form contd… Second Normal Form First Normal Form No Duplication of Data Across Rows Place Data in a separate Table and create a relationship to the data via a Foreign Key XYZ Street ABC Shipping 1/1/07 111 XYZ Street ABC Shipping 1/1/07 222 Address Shipper Date Order No 1 1/1/07 222 1 1/1/07 111 Shipper No Date Order No XYZ Street ABC Shipping 1 Address Name Shipper No

ASP.NET Architecture Normal Form contd… Third Normal Form First Normal Form and Second Normal Form. Data must be completely dependent on the Primary Key. 14 8 6 21 16 4 12 32 Total Female Students Male Students Class ID

Relationships One to One One to Many Many to Many Bob 135 Name Customer ID 32 135 Age Customer ID 1 1 1 136 1/1/07 222 Bob 135 Name Customer ID ABC Shipping 1 Name Shipper ID 1 1 Shipper ID 136 135 Customer ID 1/1/07 111 1/1/07 222 Date Order No 1 Many 1 Many Bob 135 Name Customer ID ABC Shipping 1 Name Shipper ID 1 Shipper ID 135 Customer ID 1/1/07 111 Date Order No 1 Many 1 Many 1 1/1/07 222 ABC Shipping 1 Name Shipper ID 1 1/1/07 111 Date Shipper ID Order No 1 Many

Peek into SQL Server Database SQL Server 2000 Enterprise Manager SQL Query Analyzer

Part 2 An Introduction to SQL Server Contents Introduction to SQL Server SQL Server Database Database Objects System Tables and Informational Views

Introduction to SQL Server What is SQL Server ? SQL Server is a Server Side processing Relational Database. All activities take place on the server side rather than the client side. MS Access is an example of a Client Side processing Database where all processing can be done only on the client side. Client Request Response Server 1 Server n SQL Server participating in an N-Tier Environment

Introduction to SQL Server contd… Database Types SQL Server supports 2 main types of Databases : OLTP OLAP OLTP – Data directly updated by Users. OLAP – Data Migrated from OLTP Systems. SQL Server Services MSSQLServer - Runs the whole SQL Server environment. SQLServerAGENT - Scheduling and Automating jobs. Microsoft Distributed Transaction coordinator - Transaction across multiple servers. Microsoft Search - Supports Full Text Indexing. Allow us to do searches against forms of words which isn't possible with a standard T-SQL query. Advanced string search capability.

SQL Server Databases SYSTEM Database SAMPLE Database TempDB Pubs Northwind Model MSDB Master Master – Contains information about the whole SQL Server System including login accounts, configuration setting etc. MSDB – SQL Server Agent Database. Stores Scheduling alerts, jobs, backups, and replication tasks. Model – It is the Basic Template for all databases created on the System. TempDB – Holds all temporary tables, cursors, stored procedures etc.

Database Objects Tables Constraints Indexes Rules Views Defaults Data Types Stored Procedures Triggers User Defined Functions Database Objects

System Tables and Informational Views To get info about the Views and Tables in Database query the following: Select * from Information_Schema.Tables Select * from Information_Schema.Columns . To see only tables use the following: Select * from Information_Schema.Tables where Table_Type = ‘Base Table’

Part 3 Transact – SQL Contents What is Transact – SQL Creating, Altering and Deleting Database Creating, Altering and Deleting Table SELECT keyword JOIN Keyword Inserting, Updating and Deleting Data. Programming with Transact – SQL

What is Transact – SQL ? Transact-SQL (T-SQL) is a procedural programming language used for communicating with RDBMS. T-SQL are grouped into 4 categories: Data Definition Language (DDL) Deals with defining database structures – Creating Table or Index, dropping or modifying Objects. Data Manipulation Language (DML) Manipulation of Data – INSERT, UPDATE, DELETE Data Query Language (DQL) Deals with extracting data from Database – SELECT. Data Control Language (DCL) SQL statements used to control permissions on database objects. Permissions are controlled using the GRANT and REVOKE statements.

Creating, Altering, Dropping Database Creating Database Create Database <Database_Name> ON ( NAME = ‘logical_file_name’, FILENAME = ‘OS_File_Name’(e.g c:\MyData\SampleData.mdf), SIZE = size, MAXSIZE = max_size | UNLIMITED, FILEGROWTH = growth_increment ) LOG ON ( NAME = <log_file_name>, FILENAME = ‘C:\program files\MyData\mydatabaselog.ldf’, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) FILEGROUP filegroup_name < filespec >

Creating, Altering, Deleting Database contd… Altering Database ALTER DATABASE database ( ADD FILE < filespec > | ADD LOG FILE < filespec > | REMOVE FILE logical_file_name | MODIFY FILE < filespec > | MODIFY NAME = new_dbname | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name {filegroup_property} ) Deleting database drop Database <Database_Name>

Creating, Altering and Deleting Table Create Table CREATE TABLE <Table_Name> ( Field1 int PRIMARY KEY, Field2 char(10) NOT NULL), Field3 datetime ) Alter Table ALTER TABLE <Table_Name> ADD Field4 VARCHAR(10) NULL ALTER TABLE <Table_Name> DROP COLUMN Field4 Deleting Table DROP TABLE <Table_Name>

SELECT Keyword SELECT * FROM authors SELECT au_id, au_lname, au_fname FROM authors SELECT * FROM authors WHERE city = ‘Bangalore’ SELECT DISTINCT zip from authors WHERE city = ‘Bangalore’ SELECT au_lname + ‘ ’ + au_fname FROM authors. SELECT au_lname + ‘ ’ + au_fname + ‘ ’ + phone FROM authors. //ERROR – Data Type Mismatch.

JOIN Keyword JOIN statements allow to combine data in multiple tables to quickly and efficiently process large quantities of data. 5 Types of JOINS INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN CROSS JOIN 1500 NULL NULL Jeffrey 3 Employee 2000 20 200 Bob 2 1000 10 100 Leo 1 Salary JobID DeptNo EmpName EmpID Engineering 300 Management 200 Production 100 DeptName DeptNo Department Engineer 30 Manager 20 Worker 10 JobDesc JobID Jobs

JOIN Keyword - INNER JOIN Example : OLD STYLE SELECT empid, empname, salary, E.deptno, deptname FROM Employees AS E, Departments AS D WHERE E.deptno = D.deptno NEW STYLE SELECT empid, empname, salary, E.deptno, deptname FROM Employees AS E INNER JOIN Departments AS D ON E.deptno = D.deptno INNET JOIN is a join of two tables that returns records for which there is a matching value in the field on which the tables are joined. deptname deptno salary empname empid Management 200 2000 Bob 2 Production 100 1000 Leo 1

JOIN Keyword - OUTER JOIN In Outer Join besides returning matching rows from both tables, the rows from the preserved table that have no match in the other table are also returned, padded with NULLs instead of the values that were supposed to come from the other table. Left Outer Join Right Outer Join Full Outer Join

JOIN Keyword - Left Outer Join SELECT * FROM Employees AS E, Departments AS D WHERE E.deptno *= D.deptno SELECT * FROM Employees AS E LEFT OUTER JOIN Departments AS D ON E.deptno = D.deptno New Style Old Style 30 20 10 JobID NULL NULL 1500 Jeffrey 3 Deptname Deptno Salary Empname Empid Management 200 2000 Bob 2 Production 100 1000 Leo 1

JOIN Keyword - Right Outer Join SELECT * FROM Employees AS E, Departments AS D WHERE E.deptno =* D.deptno SELECT * FROM Employees AS E RIGHT OUTER JOIN Departments AS D ON E.deptno = D.deptno Old Style New Style NULL 20 10 JobID Engineering 300 NULL NULL NULL Deptname Deptno Salary Empname Empid Management 200 2000 Bob 2 Production 100 1000 Leo 1

JOIN Keyword - FULL Outer Join SELECT * FROM Employees AS E FULL OUTER JOIN Departments AS D ON E.deptno = D.deptno NULL 30 20 10 JobID Engineering 300 NULL NULL NULL NULL NULL 1500 Jeffrey 3 Deptname Deptno Salary Empname Empid Management 200 2000 Bob 2 Production 100 1000 Leo 1

JOIN Keyword - Cross Join A cross join produces a Cartesian product of the tables involved. It matches all the rows from one table to each of the rows in the other table. If no filter is used, the number of rows in the result set is the number of rows in one table multiplied by the number of rows in the other. SELECT deptname, jobdesc FROM Departments CROSS JOIN Jobs Manager Production Manager Management Manager Engineering Worker Engineering Worker Management Worker Production JobDesc Deptname

Inserting Updating and Deleting Data INSERT The INSERT command in SQL is used to add records to an existing table . Insert into Employee values (4, ‘Jack’, 100, 30, 2000) UPDATE The UPDATE command can be used to modify information contained within a table, either in bulk or individually. UPDATE Employee SET EmpName = ‘Greg’ where EmpId = 4 DELETE Delete command is used to delete records from a table. Delete from Employee where EmpName = ‘Greg’

Programming with Transact – SQL Declaring T-SQL variables DECLARE @FirstName varchar(25) DECLARE @Age int Assigning values to Variables SET @FirstName = ‘Sam’ SET @Age = 25 SQL using Variables and Control Statements Select * from Employee_Details where First_Name = @FirstName SELECT First_Name, Suggestion = CASE Age WHEN '25' THEN 'salary need to be updated' WHEN '22' THEN 'salary looks good' ELSE 'no suggestions‘ END FROM Employee_Details WHILE (SELECT SUM(Bonus) FROM Employee_Details) < 1000000 BEGIN UPDATE Employee_Details SET Bonus = Salary * 0.02 END Bonus Salary Age ID First_Name Last_Name Employee_Details

Part 4 Advanced Transact - SQL Contents Creating and Using Views Creating and Using Constraints Creating and Using Stored Procedures Creating and Using Cursors Creating and Using Triggers Creating and Using Rules Creating and Using User-Defined Functions

Creating and Using Views Views are saved SQL Statements. It cannot store data. It can only refer to data present in tables. CREATE VIEW <view name> [optional] WITH SCHEMABIND [optional] WITH ENCRYPTION AS <select statement> GO Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema. If a view is created with ENCRYPTION option, Users will not be able to see the definition of the View after it is created. The view definition will be stored in an encrypted format in the system table named ‘syscomments’ Views can be used to insert, update and delete data. Cannot use order by in SELECT statement in VIEW.

Creating and Using Constraints Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. 5 Types of Constraints NOT NULL CHECK UNIQUE PRMARY KEY FOREIGN KEY Create TABLE Employee ( emp_id int PRIMARY KEY, -- PRIMARY Key Constraint emp_name varchar(20) NOT NULL, -- NOT NULL constraint salary float FOREIGN KEY REFERENCES Salary_Structure (emp_salary) ON DELETE NO ACTION|CASCADE, -- FOREIGN Key Constraint bonus float CHECK (bonus BETWEEN 10 and 10000) -- CHECK Constraint )

Creating and Using Stored Procedures Stored Procedures are created using T_SQL SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly. Create Procedure [dbo.][displayCurstomers] ( @customer_id int ) AS DECLARE @count_product int SELECT @count_product = count(product) from Customers WHERE Customerid = @customer_id RETURN @count_product Call the Stored Procedure as follows : dbo.displayCustomers <customer_id> A built-in security mechanism enables you to assign privileges for executing particular stored procedures.

Creating and Using Cursors Cursors are database objects used to manipulate data in a set on a row-by-row basis. We can fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language. Following commands are used to execute a cursor: Declare Open Fetch Update/Delete Close Deallocate

Creating and Using Cursors contd… Cursor Example - Using PUBS Database DECLARE @FirstName VARCHAR(20) DECLARE @LastName VARCHAR(20) DECLARE cur_California CURSOR FOR SELECT au_lname,au_fname FROM authors WHERE state = 'CA' ORDER BY au_lname OPEN cur_California FETCH NEXT FROM cur_California INTO @FirstName, @LastName WHILE @@FETCH_STATUS = 0 BEGIN PRINT @LastName+ ',' + @FirstName FETCH NEXT FROM cur_California INTO @FirstName, @LastName END CLOSE cur_California DEALLOCATE cur_California

Creating and Using Triggers Trigger is a means to enforce business logic to Relational Database. Trigger executes automatically in response to some event. A trigger is created for a specific base table or view and cannot exist without one. Only an INSERT, UPDATE and DELETE event can fire a trigger. Triggers are of mainly 2 types AFTER (or FOR) INSTEAD OF

Creating and Using Triggers contd… Trigger Example Create Trigger tri_MyTable ON MyTable For (or AFTER) INSERT|UPDATE|DETELE AS --- Code as per business logic GO Create Trigger tri_MyTable ON MyTable INSTEAD OF INSERT|UPDATE|DELETE AS -- Code as per business logic GO

Creating and Using Rules Rule is a database object bound to a column or user-defined data type that specifies what data can be entered in that column. Every time a user enters or modifies a value, SQL Server checks it against the most recent rule bound to the specified column. Create Rule test_rule AS @range >= 1000 AND @range < 2000 Bind a rule to a column or to a user-defined data type . i) EXEC sp_bindrule ' test_rule ','employees.[hire_date]‘ Here the data for the hire_date column in employee is Checked against the given rule. ii) EXEC sp_bindrule ' test_rule ', ‘ssn’ Here ssn is a user-defined data-type

Creating and Using User-Defined Functions A user-defined function is a module attached to the Database. It can accept parameters and can also return values. CREATE FUNCTION dbo.mult ( @num1 INT, @num2 INT ) RETURNS INT AS BEGIN RETURN (@num1 * @num2) END

Sql Server 2000

More Related Content

What's hot

Similar to Sql Server 2000

More from Om Vikram Thapa

Recently uploaded

Sql Server 2000