learn sql server online tutorial

Views:
 
Category: Education
     
 

Presentation Description

Learn SQL Server online tutorial, which provides a descriptive learning of the Database concepts along with the working of the relational databases.

Comments

Presentation Transcript

Introduction to Structured Query Language (SQL) :

Introduction to Structured Query Language (SQL) By Techandmate.com Learn SQL Server With US

Objectives:

Objectives 2 Explore basic commands and functions of SQL How to use SQL for data administration (to create tables, indexes, and views) How to use SQL for data manipulation (to add, modify, delete, and retrieve data) How to use SQL to query a database to extract useful information

Agenda:

Agenda What is SQL ? Data Types  Constraints Database Relationships SQL Queries  Transact- SQL Commands | DDL , DCL ,DML Retrieving Data Customizing Data Grouping Data SQL Operators  Joining Data

Agenda continue:

Agenda continue Inserting , Updating and Deleting Data Working With Tables Working with Views Working With Constraints  Generating Scripts  Working with Stored Procedures Working  with Functions

Introduction to SQL:

Introduction to SQL 5 SQL stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems SQL functions fit into two broad categories: Data definition language SQL includes commands to: Create database objects, such as tables, indexes, and views Define access rights to those database objects Data manipulation language Includes commands to insert, update, delete, and retrieve data within database tables

SQL Database Objects:

SQL Database Objects A SQL Server database has lot of objects like Tables Views Stored Procedures Functions Rules Defaults Cursors Triggers

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.

Not null Constraints :

Not null Constraints A null value is an unknown Null value is not as zero or space. Database Constraints

Unique Constraints :

Unique Constraints every value in a column or set of columns must be unique. When there is not the primary key. Example no two employees can have the same phone number

Primary key :

Primary key The primary key value must be unique and not null. Multiple UNIQUE constraints and only one Primary key in a Table .

Foreign key :

Foreign key FOREIGN KEY in one table points to a PRIMARY KEY in another table. prevents that invalid data form being inserted into the foreign key column

Check Constraints :

Check Constraints used to limit the value range that can be placed in a column. Example : A column must only include integers greater than 0

DEFAULT Constraint:

DEFAULT Constraint used to insert a default value into a column

Transact-SQL Commands :

Transact-SQL Commands Data Definition Language (DDL) Create , Alter , Drop , Truncate meant to deal with the structure of the database objects (the object itself) like tables, views, procedures and so on. Data Manipulation Language (DML) Insert , Delete , Update , SELECT deal with the contents of the tables rather than the structure of the tables Data Control Language (DCL) GRANT , DENY , REVOKE maintain security of the database objects access and use

Data Definition Language (DDL) :

Data Definition Language (DDL) CREATE adding a new database object to the database ALTER changing the structure of an existing database object DROP removing a database object from the database permanently TRUNCATE removing all rows from a table without logging the individual row deletions

Data Manipulation Language (DML) :

Data Manipulation Language (DML) SELECT retrieving data from the database by specifying which columns and rows to be retrieved INSERT adding a new row (and not column) into the table UPDATE modifying the existing data in the tabl DELETE removing an existing data from the table

Data Control Language (DCL) :

Data Control Language (DCL) GRANT giving privileges to the users DENY denies permissions from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships REVOKE removing privileges from the users that are previously granted those permissions

SELECT :

SELECT Definition Retrieve data from database Syntax SELECT * | column1 [, column2, …….] FROM table

Example:

Example

WHERE:

WHERE Definition Specify a condition to limit the result Syntax SELECT * | column1 [, column2, …….] FROM table [ WHERE conditions]

Example:

Example

IS Null Function:

IS Null Function

Order BY:

Order BY Definition sort the retrieved rows by a specific column or set of columns Syntax SELECT * | column1 [, column2, …….] FROM table [ WHERE conditions] [ ORDER BY column1 [, column2, ……..] ASC , DESC ]

Example:

Example

Examples : Sort Descending :

Examples : Sort Descending 1 2

Concatenate:

Concatenate

Aliases (as) :

Aliases (as)

Distinct:

Distinct eliminates duplicate row values from the results Example : the Next Slide

Example: Distinct :

Example: Distinct

Grouping Data :

Grouping Data

Grouping Functions:

Grouping Functions

Example 1: SUM:

Example 1: SUM

Example 2 : SUM:

Example 2 : SUM

Group By:

Group By Definition used to divide the rows in a table into smaller groups Syntax SELECT * | column1, group_function (column2), ……. FROM table [ WHERE conditions] [ GROUP BY column1, ……..] [ ORDER BY column1 [, column2, ……..] ASC , DESC ]

Example : Group By:

Example : Group By

Having:

Having Definition used to restrict the group results Syntax SELECT * | column1, group_function (column2), ……. FROM table [ WHERE conditions] [ GROUP BY column1, ……..] HAVING [conditions] [ ORDER BY column1 [, column2, ……..] ASC , DESC ]

Example : Having:

Example : Having

SQL Operators:

SQL Operators

Using SQL Operators :

Using SQL Operators Arithmetic operators Comparison operators Logical operators Set Operators Other operators

Arithmetic operators :

Arithmetic operators addition (+) subtraction (-) multiplication (*) division (/).

Comparison operators :

Comparison operators compare two or more values Description Operator Equal = Lease than < Greater than > Less than or equal <= Greater than or equal >= Not Equal <>

Example:

Example

Logical operators :

Logical operators Used to get a logical value (True or False)

Example:

Example

Set Operators :

Set Operators Definition combine the results of two or more queries into one result Keywords UNION/ UNION ALL INTERSECT EXCEPT

UNION & Intersect & Except:

UNION & Intersect & Except EmpLacation Egypt Canada Egypt VacLocation Egypt Canada France Union Egypt Canada France Intersect Egypt Canada Except France

Union Operator:

Union Operator Create a Single Result Set from Multiple Queries Each Query Must Have: Similar data types Same number of columns Same column order in select list USE northwind SELECT (firstname + ' ' + lastname) AS name , city, postalcode FROM employees UNION SELECT companyname, city, postalcode FROM customers GO

INTERSECT Operator:

INTERSECT Operator Definition returns only the records that have the same values in the selected columns in both tables . Syntax USE northwind SELECT (firstname + ' ' + lastname) AS name , city, postalcode FROM employees INTERSECT SELECT companyname, city, postalcode FROM customers GO

EXCEPT Operator:

EXCEPT Operator Definition return rows returned by the first query that are not present in the second query Syntax USE northwind SELECT (firstname + ' ' + lastname) AS name , city, postalcode FROM employees EXCEPT SELECT companyname, city, postalcode FROM customers GO

Other operators :

Other operators

Joining Data:

Joining Data

Using Aliases for Table Names:

Using Aliases for Table Names Example 1 (without an alias name) Example 2 (with an alias name) USE joindb SELECT buyer_name, s.buyer_id, qty FROM buyers AS b INNER JOIN sales AS s ON b.buyer_id = s.buyer_id GO USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id GO This slide from 2071b from Microsoft

Combining Data from Multiple Tables:

Combining Data from Multiple Tables Introduction to Joins Using Inner Joins Using Outer Joins Using Cross Joins Joining More Than Two Tables Joining a Table to Itself

Introduction to Joins:

Introduction to Joins Selects Specific Columns from Multiple Tables JOIN keyword specifies that tables are joined and how to join them ON keyword specifies join condition Queries Two or More Tables to Produce a Result Set Use primary and foreign keys as join conditions Use columns common to specified tables to join tables

Inner Join :

Inner Join Definition return rows only when there is at least one row from both tables that matches the join condition Syntax SELECT <select list> FROM table1 inner join table2 ON (table1.column1 = table2.column2)

Using Inner Joins:

USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id GO sales buyer_id prod_id qty 1 1 4 3 2 3 1 5 15 5 37 11 4 2 1003 buyers buyer_name Adam Barr Sean Chai Eva Corets Erin O’Melia buyer_id 1 2 3 4 Result buyer_name Adam Barr Adam Barr Erin O’Melia Eva Corets buyer_id qty 1 1 4 3 15 5 37 11 Erin O’Melia 4 1003 Example 1 Using Inner Joins

Left Outer Join:

Left Outer Join Definition includes all rows in the left table in the results whether or not there are matching values on the common column in the right table. in addition to the matching values in Both table. In other words : All rows from the Left table are returned Syntax SELECT <select list> FROM table1 left outer join table2 ON (table1.column1 = table2.column2)

Using Outer Joins:

USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_id GO sales buyer_id prod_id qty 1 1 4 3 2 3 1 5 15 5 37 11 4 2 1003 buyers buyer_name Adam Barr Sean Chai Eva Corets Erin O’Melia buyer_id 1 2 3 4 Result buyer_name Adam Barr Adam Barr Erin O’Melia Eva Corets buyer_id qty 1 1 4 3 15 5 37 11 Erin O’Melia 4 1003 Sean Chai NULL NULL Example 1 Using Outer Joins

Right Outer join:

Right Outer join Definition the reverse of LEFT OUTER joins All rows from the right table are returned Null values are returned for the left table any time a right table row has no matching row in the left table. Syntax SELECT <select list> FROM table1 right outer join table2 ON (table1.column1 = table2.column2)

Full Join:

Full Join returns all the rows from the left table , and all the rows from the right table Also it returns rows in the left table that do not have matches in the right table, or if there are rows in right table that do not have matches in the left table.

Slide 61:

Name ID Some1 1 Some2 2 Some3 3 Country ID Loc1 1 Loc2 2 Loc4 4 SELECT a.id ,b.id, a.name , b.country FROM LeftTable as a Full Join RightTable as b On a.id = b.id Full Join

Self- Join :

Self- Join Definition Join table with it self Syntax SELECT a.column , b.column FROM Table1 as a JOIN Table1 as b ON a.column = b.column

Problem:

Problem I want Every Employee Name with his Manager

Solution:

Solution

Cross Join:

Cross Join return all rows from both tables Each row from the left table is combined with all rows from Second Table the number of rows in the left table multiplied by the number of rows in the right table . Table1 >> 2 rows, Table2 >> 5 rows, Result >>10 rows Example : Possible ways

Slide 66:

Name Ali Islam Country Egypt Cairo Country Name Egypt Ali Cairo Ali Egypt Islam Cairo Islam Cross Join

Using Cross Joins:

USE joindb SELECT buyer_name, qty FROM buyers CROSS JOIN sales GO Result buyer_name Adam Barr Adam Barr Adam Barr Adam Barr qty 15 5 37 11 Adam Barr 1003 Sean Chai 15 Sean Chai 5 Sean Chai 37 Sean Chai 11 Sean Chai 1003 Eva Corets 15 ... ... sales buyer_id prod_id qty 1 1 4 3 2 3 1 5 15 5 37 11 4 2 1003 buyers buyer_id 1 2 3 4 buyer_name Adam Barr Sean Chai Eva Corets Erin O’Melia Example 1 Using Cross Joins

Data Manipulation Language:

Data Manipulation Language

Inserting Data :

Inserting Data

INSERT Statement:

INSERT Statement Definition is responsible for adding a new row into the table Syntax INSERT [ INTO ] table [column1, column2, ………..] VALUES (value1, value2, ………….. )

Example 1:

Example 1 Inserting Data without columns name

Example 2:

Example 2 Inserting Data with columns name Note : You can verify the result by write SELECT statement.

The SELECT INTO statement :

The SELECT INTO statement SELECT …… <select_list> INTO <new_table> FROM <table_name> [ WHERE <search_condition>]

Example:

Example

verify the result:

verify the result

Updating and Deleting Data :

Updating and Deleting Data

Update Statement:

Update Statement Definition : is responsible for modifying the existing data in the table Syntax Note : What happened without where ? UPDATE < table> SET column1 = value1 [, column2 = value2, ……] WHERE [conditions]

Example:

Example

Delete Statement:

Delete Statement Definition is responsible for removing an existing data from the table Syntax Note : What happened without Where ? DELETE <table> WHERE [conditions]

Example:

Example

Truncate Statement:

Truncate Statement Definition : removes all rows from a table Syntax TRUNCATE Table <table>

MERGE statement:

MERGE statement Definition is new statement in SQL Server 2008 INSERT , UPDATE, and DELETE in a single statement . Based on Condition

Merge Syntax:

Merge Syntax MERGE INTO table_name table_alias USING ( table|view|sub_query ) alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT ( column_list ) VALUES ( column_values );

Example1 : Subjects Total :

Example1 : Subjects Total

Result :

Result

Example:

Example

Data Definition Language & Working with Tables :

Data Definition Language & Working with Tables

Creating Table:

Creating Table Creating a new table Using User Interface

CREATE TABLE statement :

CREATE TABLE statement Definition create new tables Syntax CREATE TABLE < table_name> ( column1 datatype , column2 datatype , ............... (

Example:

Example

Modifying Tables:

Modifying Tables Altering an existing table Using User Interface

ALTER TABLE statement:

ALTER TABLE statement Definition : modify existing tables Syntax ALTER TABLE < table_name> Add colum_name datatype Add Column ALTER TABLE < table_name> ALTER COLUMN colum_name datatype Modify Column ALTER TABLE < table_name> DROP COLUMN colum_name datatype Drop Column

Example: Add Column:

Example: Add Column

Example: Modify column:

Example: Modify column Disallow null , and decrease length

Example: Drop Column:

Example: Drop Column

Removing Tables :

Removing Tables Dropping an existing table

Example : Drop Table:

Example : Drop Table

DROP TABLE statement :

DROP TABLE statement Definition drop existing tables Syntax Note : Don’t forget to Press Refresh DROP TABLE < table_name >

Working with Views :

Working with Views

View Description:

View Description Definition is simply a SELECT statement that has been given a name and stored in a database Advantages Restricting data access Making complex queries easy

Create View:

Create View Syntax Example : CREATE VIEW <view_name> AS <select_statement> Create View LondonEmployees as SELECT EmloyeeId , LastName , FirstName ,City , Address FROM Employees

Alter View:

Alter View

WITH CHECK OPTION :

WITH CHECK OPTION Any changes to View that may cause a record disappear from the view , will raises runtime error . Example : Create view SomeOrders as SELECT * FROM Orders where Orders.ShipCity = 'London‘ With Check Option

Drop View:

Drop View

Working with Constraints:

Working with Constraints

Constraint Types :

Constraint Types Column- level constraints Table-level constraints

Dealing with Constraints :

Dealing with Constraints Creating tables with constraints

Example: Relationship:

Example: Relationship

Inserting Data:

Inserting Data

Violating constraints :

Violating constraints

Example: Enter ID Not Exists:

Example: Enter ID Not Exists

Example: TraineeName Not Null:

Example: TraineeName Not Null

Example:

Example

Adding and dropping constraints:

Adding and dropping constraints add a new constraint

drop a constraint:

drop a constraint

Dealing with Databases:

Dealing with Databases

Creating Databases:

Creating Databases Create a new Database >> New Database Attach Existing Database >> Attach

Moving Database:

Moving Database removes the database entry from the instance closes all files associated to the database releases all operating system locks Detaching a database

Detaching a database:

Detaching a database

Database Diagrams:

Database Diagrams Definition Database diagrams graphically show the structure of the database Create One Select your database from the Databases node in the Object Explorer. Right-click the Database Diagrams folder. Select the New Database Diagram

Step 2 : Adding Table :

Step 2 : Adding Table

Step 3 : drag and Drop:

Step 3 : drag and Drop

Interacting with Scripts:

Interacting with Scripts

Generating database scripts:

Generating database scripts

Generating table scripts:

Generating table scripts

Saving and loading script files:

Saving and loading script files Files with Extension (.sql ) Example : script1.sql

Using Database Tools:

Using Database Tools

Backing up a database:

Backing up a database

Restoring a database:

Restoring a database

Database Transactions:

Database Transactions

Transaction :

Transaction Transaction : a sequence of operations performed as a single logical unit of work. all of its data modifications are performed or none of them is performed.

Starting Transaction:

Starting Transaction Autocommit transactions The default for SQL Server Each individual DML or DDL statement is committed when it Completes You do not have to specify any statements to control transactions Implicit transactions SET IMPLICIT_TRANSACTIONS ON Each Transact-SQL statement (INSERT,UPDATE,DELETE) execute as a Transaction Explicit transactions Starting with BEGIN TRANSACTION Statement

Ending Transaction:

E nding Transaction Successful : The COMMIT statement. Error : The ROLLBACK statement .

Errors during transaction processing:

Errors during transaction processing Fatal Error >> automatically Rollback If it prevents the successful completion of a transaction Non Fatal Error >> need to Check @@Error constraint violation (Check Constraint )

Example:

Example

Begin Transaction:

Begin Transaction

Save Point :

Save Point Definition Define a location to which a transaction can return like save point in any game Syntax SAVE TRANSACTION <savepoint_name> ROLLBACK TRANSACTION <savepoint_name>

Step1 : Saving transactions Point :

Step1 : Saving transactions Point

STEP 2 : already deleted :

STEP 2 : already deleted

Step 3 : Rollback to Save point:

Step 3 : Rollback to Save point

Step4 : Check if it Deleted :

Step4 : Check if it Deleted

Basic Concepts of Stored Procedure:

Basic Concepts of Stored Procedure

Stored procedure definition:

Stored procedure definition You Can : Contains Statement that Perform Operations Accept input Parameters Return Status Value success or failure Return Multiple Output parameters

Stored procedure benefits:

Stored procedure benefits They are registered (permanently stored) at the server. They can have security attributes (such as permissions). They can enhance the security of your application. They allow modular programming. They are named code and so they allow repeating execution. They can reduce network traffic.

Stored procedure types :

Stored procedure types User-defined stored procedures System stored procedures

Using the CREATE PROCEDURE statement :

Using the CREATE PROCEDURE statement Syntax CREATE PROC | PROCEDURE <procedure_name> [@parameter data_type] AS <sql_statement>

Example:

Example

EXECUTE :

EXECUTE

Using the ALTER PROCEDURE statement :

Using the ALTER PROCEDURE statement Syntax ALTER PROC | PROCEDURE <procedure_name> [@parameter data_type] AS <sql_statement>

Using the DROP PROCEDURE statement :

Using the DROP PROCEDURE statement Syntax DROP PROC | PROCEDURE <procedure_name>

Working with Functions :

Working with Functions

Basic Concepts of Functions :

Basic Concepts of Functions Function definition Like functions in programming languages accept parameters perform an action return the result of that action as a value Function benefits They allow modular programming. They allow faster execution. They always return a value to the calling program.

Function types :

Function types User-defined scalar functions Built-in Functions

Creating and Calling Functions :

Creating and Calling Functions

Using the CREATE FUNCTION statement :

Using the CREATE FUNCTION statement Syntax CREATE FUNCTION <function_name> [(@parameter data_type)] RETURNS return_data_type | table [ AS ] BEGIN function_code RETURN scalar_expression | select statement END

Creating a Scalar-Valued function :

Creating a Scalar-Valued function

Return the result :

Return the result

Exercise 1:

Exercise 1 Create “Say Hello” function

Exercise 2:

Exercise 2 Create a function to replace Null Value with words ‘Not Applicable’

Creating a Table-Valued function :

Creating a Table-Valued function Definition specifies TABLE as a return data type In other words returns a SELECT statement rather than a single value

Example: Creating :

Example: Creating

Display the result:

Display the result

Creating a Multi-statement Table-Valued function:

Creating a Multi-statement Table-Valued function

Modifying and Removing Functions:

Modifying and Removing Functions Using the ALTER FUNCTION statement Syntax ALTER FUNCTION <function_name> [(@parameter data_type)] RETURNS return_data_type [ AS ] BEGIN function_code RETURN scalar_expression END

Using the DROP FUNCTION command:

Using the DROP FUNCTION command Syntax DROP FUNCTION <function_name>

Working with Triggers:

Working with Triggers

Trigger definition and types :

Trigger definition and types Definition a special type of stored procedure. Invoked automatically Not called directly Types DDL triggers DML triggers

DML trigger benefits :

DML trigger benefits They can query other tables and include complex Transact-SQL statements They can reference columns in other tables with more complex than check

Managing INSTEAD OF triggers :

Managing INSTEAD OF triggers Definition override the standard actions Syntax CREATE TRIGGER <trigger_name> on <table | view> INSTEAD OF INSERT | UPDATE | DELETE AS BEGIN <trigger_code> END

Creating INSTEAD OF triggers :

Creating INSTEAD OF triggers INSTEAD OF INSERT triggers INSTEAD OF UPDATE triggers INSTEAD OF DELETE triggers

Example 1 : Create a trigger that protects all the data in the Course table from deletion :

Example 1 : Create a trigger that protects all the data in the Course table from deletion

Test the result :

Test the result

Example: create trigger that insert new trainee when inserting new course :

Example : create trigger that insert new trainee when inserting new course

Test it:

Test it

Modifying and removing triggers :

Modifying and removing triggers Alter and Drop statements Example : Drop trigger

Let us Begin :

Let us Begin This is not every thing about Transact-SQL This is just an introduction May you read : 2071B Book ( Querying MS SQL Server with Transact-SQL ) 2073A Book( Programming MS SQL Server Database ) MSDN : http://msdn.microsoft.com/en-us/library/aa299742(v=SQL.80).aspx Google

Thank You:

Thank You

authorStream Live Help