advance sql for software development

iFour Consultancy

Advance SQL

SQL CommandsCREATESELECTINSERT, UPDATE and DELETEGroup by and Having ClauseJOINSStored ProcedureFunctionsViewsTriggersCursorsSQL Server Version History and Management Studio

INDEX

http://www.ifourtechnolab.com/


SQL Commands

Some of the most important SQL Commands• SELECT - extracts data from a database• UPDATE - updates data in a database• DELETE - deletes data from a database• INSERT INTO - inserts new data into a database• CREATE DATABASE - creates a new database• ALTER DATABASE - modifies a database• CREATE TABLE - creates a new table• ALTER TABLE - modifies a table• DROP TABLE - deletes a table• CREATE INDEX - creates an index (search key)• DROP INDEX - deletes an index

ASP.NET Software Development Companies India http://www.ifourtechnolab.com/



SQL CREATE DATABASE Statement

Used to create a database• CREATE DATABASE dbname;

Database tables can be added with the CREATE TABLE statement




It is used to create a table in a databaseTables are organized into rows and columns; and each table must have a nameSyntax

CREATE TABLE table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....);

The column_name parameters specify the names of the columns of the tableThe data_type parameter specifies what type of data the column can holdThe size parameter specifies the maximum length of the column of the table

SQL CREATE TABLE Statement




Used to select data from a databaseThe result is stored in a result table, called the result-setSQL SELECT Syntax:

SELECT column_name,column_name FROM table_name; and SELECT * FROM table_name;

SELECT Statement




Used to insert new records in a table It is possible to write the INSERT INTO statement in two forms The first form does not specify the column names where the data will be inserted, only

their values: INSERT INTO table_name VALUES (value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:INSERT INTO table_name (column1,column2,column3,...) VALUES

(value1,value2,value3,...);

INSERT INTO Statement




Used to update existing records in a tableSQL UPDATE Syntax

UPDATE table_nameSET column1=value1,column2=value2,... WHERE some_column=some_value;

SQL UPDATE Statement




Used to delete records in a tableSQL DELETE Syntax:

DELETE FROM table_name WHERE some_column=some_value;It is possible to delete all rows in a table without deleting the table. This means that the

table structure, attributes, and indexes will be intact:DELETE FROM table_name;orDELETE * FROM table_name;

SQL DELETE Statement




Used in conjunction with the aggregate functions to group the result-set by one or more columns

SQL GROUP BY Syntax:

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;

SQL Group By Statement




The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions

SQL HAVING Syntax:

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value;

SQL having Clause




Used to combine rows from two or more tablesIt is used to combine rows from two or more tables, based on a common field between

themThe most common type of join is: SQL INNER JOIN (simple join)An SQL INNER JOIN return all rows from multiple tables where the join condition is met

SQL Joins




Different SQL JOINs• INNER JOIN: Returns all rows when there is at least one match in BOTH tables

Syntax:SELECT table1.Column_name,table2.Column_nameFROM table1INNER JOIN table2ON table1.column_name=table2.column_name;

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right tableSyntax:

SELECT table1.Column_name,table2.Column_nameFROM table1LEFT OUTER JOIN table2

ON table1.column_name=table2.column_name;

Types Of JOINs




• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left tableSyntax:SELECT table1.Column_name,table2.Column_nameFROM table1RIGHT OUTER JOIN table2ON table1.column_name=table2.column_name;

• FULL JOIN: Return all rows when there is a match in ONE of the tablesSyntax:SELECT table1.Column_name,table2.Column_nameFROM table1FULL OUTER JOIN table2ON table1.column_name=table2.column_name;

Types Of JOINs




Different SQL JOINs SELF JOIN: It is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement

Syntax: SELECT a.column_name, b.column_name FROM table1 a, table1 b WHERE a.common_field = b.common_field; Example SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY;

Types Of JOINs




It is a group of sql statements that has been created and stored in the database. It will accept input parameters so that a single procedure can be used over the network by

several clients using different input dataIt will reduce network traffic and increase the performance. If we modify stored procedure

all the clients will get the updated stored procedureSyntax:

CREATE PROCEDURE dbo.sp_whoAS

SELECT FirstName, LastName FROM Person.Person;GOEXEC sp_who;GO

Stored Procedure




Special kind stored program that returns a single valueYou use functions to encapsulate common formulas or business rules that are reusable

among SQL statements or stored programsSyntax:

CREATE FUNCTION function_name(param1,param2,…) RETURNS datatype [NOT] DETERMINISTICstatements

Function




Difference Between Function and Stored ProcedureFunction Stored Procedure

It must return a value In Stored Procedure it is optional( Procedure can return zero or n values)

It will allow only input parameters, doesn’t support output parameters

It can have input/output parameters

It can be called from Procedure It cannot be called from Function

Allows only SELECT statement in it It allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it

It will not allow us to use try-catch blocks For exception handling we can use try catch blocks

We can use only table variables, it will not allow using temporary tables

It Can use both table variables as well as temporary table in it

It can be called from a select statement It can’t be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure




In some cases, it is not desirable for all users to see the entire logical model (that is, all the actual relations stored in the database)

Consider a person who needs to know a customer’s name, loan number and branch name, but has no need to see the loan amount. This person should see a relation described, in SQL, by

(select customer_name, borrower.loan_number, branch_name from borrower, loan

where borrower.loan_number = loan.loan_number )It provides a mechanism to hide certain data from the view of certain usersAny relation that is not of the conceptual model but is made visible to a user as a “virtual

relation” is called a view

Views




A view consisting of branches and their customers:create view all_customer as

(select branch_name, customer_name

from depositor, account where depositor.account_number = account.account_number

) union (

select branch_name, customer_name from borrower, loan where borrower.loan_number = loan.loan_number

)

Views (Cont.)




Special kind of a stored procedure that executes in response to certain action on the table like insertion, deletion or updating of data.

It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers.

Types of Triggers• After Triggers (For Triggers)• Instead Of Triggers

Triggers




After Triggers (For Triggers)• These triggers run after an insert, update or delete on a table, they are not supported for views

• AFTER IN AFTER TRIGGERS can be classified further into three types as:SERT Trigger - This trigger is fired after an INSERT on the table• AFTER UPDATE Trigger - This trigger is fired after an update on the table• AFTER DELETE Trigger - This trigger is fired after a delete on the table

Triggers




Instead of Triggers• These can be used as an interceptor for anything that anyone tried to do on our table or view • INSTEAD OF TRIGGERS can be classified further into three types as:

• INSTEAD OF INSERT Trigger - This trigger is fired instead of an INSERT on the table• INSTEAD OF UPDATE Trigger - This trigger is fired instead of an update on the table• INSTEAD OF DELETE Trigger - This trigger is fired instead of a delete on the table

Triggers




Example Of TriggerCREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] (FOR|After|INSTEAD OF) (INSERT|Update|Delete)AS

declare @empid int;declare @empname varchar(100);declare @empsal decimal(10,2);declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;select @empname=i.Emp_Name from inserted i;select @empsal=i.Emp_Sal from inserted i;set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'GO

Triggers




Database object to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. Use cursor when we need to update records in a database table in singleton fashion means row by row.Syntax DECLARE cursor_name CURSOR [LOCAL | GLOBAL] --define cursor scope [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward) [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks FOR select_statement --define SQL Select statement FOR UPDATE [col1,col2,...coln] --define columns that need to be updated OPEN [GLOBAL] cursor_name --by default it is local

Cursor




Example Of Cursor SET NOCOUNT ON DECLARE @Id int DECLARE @name varchar(50) DECLARE @salary int DECLARE cur_emp CURSOR STATIC FOR SELECT EmpID,EmpName,Salary from Employee OPEN cur_emp IF @@CURSOR_ROWS > 0 BEGIN FETCH NEXT FROM cur_emp INTO @Id,@name,@salary WHILE @@Fetch_status = 0 BEGIN PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary) FETCH NEXT FROM cur_emp INTO @Id,@name,@salary END END CLOSE cur_emp DEALLOCATE cur_emp SET NOCOUNT OFF

Cursor




Version history (2000,2005,2008,2012)

Sql-2000 Sql-20051) No XML data type is introduced XML data type is introduced2) We can create maximum of 65535 databases We can create 2(pow(20))-1 databases3) Exception handling is not here Exception handling is there4) can't compress the tables and indexes Can compress the table and indexes5) No varchar(max) or varbinary(max) is available

varchar(max) or varbinary(max) is available

6)Pivot and Unpivot function are not used Pivot and Unpivot function are used7) Can't bulk copy update Bulk copy update8) Can't encrypt the database Can encrypt the database




Version history (2000,2005,2008,2012) (cont.)

Sql-2005 Sql-20081) Doe's not provide backup encryption Introduced Back-Up encryption2) XML Data type is introduced XML Data type is implemented and used3)File stream is not there File stream is introduced4)Linq is not there Linq is introduced for retrieving multiple type of

data5)Merge statement is not included Merge statement is included6)Table-valued parameter is not there Table-valued parameter is introduced 7)Data Synchronization is not introduced Data synchronization is introduced





Sql-2008 Sql- 2008-r2

1) it support maximum of 64 logical process It support maximum of 256 logical process

2) Master data services part of BI is not included in sql-2008

Master Data service part of Bi is included in sql-2008r2

3)Power Pivot in BI is not implemented Power Pivot Bi is implemented

4) introduced geospatial data types with few common feature in ssrs2008

Add geospatial data types in ssrs 2008





Sql-2008 Sql- 2012

1)It can support only 1000 partition It can support 15000 partition

2) Sql server 2008 uses 27 bit precision for spatial

Sql server 2012 uses 48 bit precision for spatial

3) String function concate and format are not available

String function concate and format are available

4) Analysis services in sql server not have Bi concept

Analysis services will include a new BISM with 3 Layer model




SQL Server 2014 Features

New cardinality estimator• Improves the execution plans that we're seeing for some of our more complex queries, and definitely assists with the

ascending key problem AlwaysOn enhancements

• Microsoft has enhanced AlwaysOn integration by expanding the maximum no of secondary replicas from 4 to 8. • Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable.

In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.

Delayed durability• It is a way to defer the acknowledgement of the transaction log records getting written to disk - which means your

transaction can continue without waiting, and assume that the log record *will* be written. In-Memory OLTP

• SQL Server 2014 enables memory optimization of selected tables and stored procedures. • The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control

mechanism to eliminate locking delays• Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when

using this new feature.




SQL Server 2014 Features

Updateable columnstore indexes• Columnstore indexes in SQL Server 2014 brought a dramatic boost to data warehouse performance, but with a hitch:

They couldn't be updated. With SQL Server 2014, now they can. • This means you no longer have to drop and re-create columnstore indexes every time you need to load your

warehouse tables. Not only that, but updateability also means you may be able to look at columnstore indexes for certain OLTP applications.

SSD buffer pool extension• SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL

Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). • With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have

maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads. Storage I/O control

• The Resource Governor lets you limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resource Governor to manage storage I/O usage as well.

• The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool.




SQL Server 2016 compared to SQL 2014

Query Store• The Query Store feature maintains a history of query execution plans with their performance data, and

quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed.

Polybase• This feature will benefit you if your regular data processing involves dealing with a lot of large text files,

they can be stored in Azure Blob Storage or Hadoop, and queried as if they were database tables Stretch Database

• The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion.

• When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly.

JSON Support• Providing the ability to quickly move JSON data into tables




SQL Server 2016 compared to SQL 2014

Row Level Security• This restricts which users can view what data in a table, based on a function. • SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments

where you may want to limit data access based on customer ID. Always Encrypted

• Always Encrypted is new functionality through the use of an enhanced client library at the application so the data stays encrypted in transit, at rest and while it is alive in the database

In-Memory Enhancements• Optimally designed for high-speed loading of data with no locking issues or high-volume

session state issues




http://www.w3schools.com/sql/ http://www.tutorialspoint.com/sql/

References




Questions?




advance sql for software development

Technology