Find the best tutors and institutes for MS SQL Development

Find Best MS SQL Development Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

MS SQL Development Updates

Ask a Question

Post a Lesson

All

All

Lessons

Discussion

Lesson Posted on 19/12/2017 IT Courses/MS SQL IT Courses/MS SQL/MS SQL Development

What Is WorkTable In SQL Server?

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

What is WorkTable in SQL Server? USE AdventureWorks2014 GO SET STATISTICS IO ON GO SELECT * FROM Production.Product p CROSS JOIN Production.Product p1 GO If you run above query, there is good chance that it will give you following details in the message windows. Table ‘Product’.... read more

What is WorkTable in SQL Server?

USE AdventureWorks2014

GO

SET STATISTICS IO ON

GO

SELECT *

FROM Production.Product p

CROSS JOIN Production.Product p1

GO

If you run above query, there is good chance that it will give you following details in the message windows.

Table ‘Product’. Scan count 2, logical reads 30, physical reads 1, read-ahead reads 17, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable‘. Scan count 1, logical reads 8110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You will notice in the message there is a table called worktable. However, if you see the original table there is no worktable. This is where the question has originated.

The question in another word is, "Why does statistics show the worktable where it is not there in the original query?"

Well, the answer is very simple, quite often SQL Server has to perform any logical operations for any specific queries and to perform these logical operations SQL Server has to build a worktable. Worktables are built in tempdb and are dropped automatically when they are no longer needed.

Worktables:

The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Worktables are built in tempdb and are dropped automatically when they are no longer needed.

read less
Comments
Dislike Bookmark

Lesson Posted on 31/07/2017 IT Courses/MS SQL IT Courses/MS SQL/MS SQL Development Tuition/BCA Tuition +4 IT Courses Tuition/BCA Tuition/IT IT Courses/MS SQL/MS SQL Integration IT Courses/MS SQL/MS SQL Reporting less

Rename The Logical Name Of SQL Server Database Files Using T-SQL

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

Rename the logical name of SQL Server database files using T-SQL: 1. Problem: SQL Server database files have two names: one is the logical file name and the other is the physical file name. The logical file name is used with some T-SQL commands, such as DBCC SHRINKFILE. If you want to change the logical... read more

Rename the logical name of SQL Server database files using T-SQL:

1. Problem:

SQL Server database files have two names: one is the logical file name and the other is the physical file name. The logical file name is used with some T-SQL commands, such as DBCC SHRINKFILE. If you want to change the logical file names of your database files then this tip will be very useful. This way you can keep your logical database file names unique on your SQL Server instance.

Since you may want to move this database on a production server, or change the database name because of any reason, you may also want to change the names of the database files. You may want the database names consistent with the database names or ant other rules you obey for standardization.

2. Solution:

It is not mandatory to keep logical file names unique on a SQL Server instance, but ideally we should keep them unique to avoid any confusion.

When we restore a database, the logical database file names don't change and are the same as the source database. So if you are restoring to multiple databases with the same backup file, the logical file names will be the same for all of the restored databases.

Let's start with creating a database having three data files and a log file then we will change the logical file name of these database files to show the step by step method.

Rename the logical SQL Server database file name using T-SQL

Step 1: First we will create a database named "Test_DB" with 1 data files and 5 log files and one log file by running the below T-SQL code. You can change the name of the database, file path, file names, size and file growth according to your needs.

USE MASTER;

GO

CREATE DATABASE Test_DB

ON

( NAME = Test_DB,

FILENAME = 'E:\Data\Test_DB.mdf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB )

LOG ON

( NAME = Test_DB_log1,

FILENAME = 'E:\Data\Test_DB_log1.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log2,

FILENAME = 'E:\Data\Test_DB_log2.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log3,

FILENAME = 'E:\Data\Test_DB_log3.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log4,

FILENAME = 'E:\Data\Test_DB_log4.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log5,

FILENAME = 'E:\Data\Test_DB_log5.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ) ;

GO

Step 2: Next I created a backup of the database.

BACKUP DATABASE Test_DB TO DISK = 'D:\Data\Backup\Test_DB.bak'

GO

I ran the below T-SQL command to get the logical file names of database file for the database TEST_DB.

USE Test_DB

GO

SELECT file_id, name as [logical_file_name],physical_name

from sys.database_files

file_id

logical_file_name

physical_name

1.

Test_DB

E:\Data\Test_DB.mdf

2.

Test_DB_log1

E:\Data\Test_DB_log1.ldf

3.

Test_DB_log2

E:\Data\Test_DB_log2.ldf

4.

Test_DB_log3

E:\Data\Test_DB_log3.ldf

5.

Test_DB_log4

E:\Data\Test_DB_log4.ldf

6.

Test_DB_log5

E:\Data\Test_DB_log5.ldf

Step 3: Now we will change the logical file name for the log data files for database "Test_DB" from "Test_DB_log" to "Test_DB_New2_log.ldf" by running an ALTER DATABASE statement using MODIFY FILE according to their file_id. To modify the logical name of a data file or log file, we will specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. Run the below command to create a procedure apply this change.

CREATE PROCEDURE Rename_Logical_Name

(

@Database sysname, @New_name sysname

)

AS

BEGIN

DECLARE @cmd1 nvarchar(200)

DECLARE @cmd2 nvarchar(200)

DECLARE @ldffile sysname

DECLARE @id sysname

DECLARE theCursor CURSOR

FOR (SELECT name,FILE_ID FROM sys.database_files WHERE type = 1)

OPEN theCursor

FETCH NEXT FROM theCursor INTO @ldffile,@id

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd2 = 'ALTER DATABASE ' +@Database+' MODIFY FILE (NAME= '''+ @ldffile + ''', NEWNAME = '''+@ New_name +@ID+'_log.ldf'')'

EXEC (@cmd2)

FETCH NEXT FROM theCursor INTO @ldffile,@id

END

CLOSE theCursor

DEALLOCATE theCursor

--PRINT @cmd1

--PRINT @cmd2

--EXEC (@cmd1)

EXEC (@cmd2);

END

EXEC Rename_Logical_Name @Database = 'Test_DB',@New_name = 'Test_DB_New'

Step 4: Now we will check the logical file names to verify our change we made in the last step. Run the command below to check the logical names.

SELECT file_id, name as [logical_file_name],physical_name

from sys.database_files WHERE type = 1

We can see the logical file names has been changed without any downtime of the database.

file_id

logical_file_name

physical_name

2.

Test_DB_New2_log.ldf

E:\Data\Test_DB_log1.ldf

3.

Test_DB_New3_log.ldf

E:\Data\Test_DB_log2.ldf

4.

Test_DB_New4_log.ldf

E:\Data\Test_DB_log3.ldf

5.

Test_DB_New5_log.ldf

E:\Data\Test_DB_log4.ldf

6.

Test_DB_New6_log.ldf

E:\Data\Test_DB_log5.ldf

 

NOTE: Do not change logical names of any data file of your production database until it is required or needed.

read less
Comments
Dislike Bookmark

Lesson Posted on 18/07/2017 IT Courses/MS SQL Tuition/BA Tuition/Database Management Systems IT Courses/MS SQL/MS SQL Development +1 IT Courses less

SQL SERVER: How To Pass Parameters To The Stored Procedure?

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

SQL SERVER: How To Pass Parameters To The Stored Procedure? After so many years of existence of the stored procedures, I still see developers struggling to execute the stored procedure. The most common mistakes developers do is to execute stored procedures as follows which generates the error. EXEC... read more

SQL SERVER: How To Pass Parameters To The Stored Procedure?

After so many years of existence of the stored procedures, I still see developers struggling to execute the stored procedure.
The most common mistakes developers do is to execute stored procedures as follows which generates the error.

EXEC NameofSP (FirstParam,SecondParam)

I can clearly understand why the above script preferred by developers. As most of the programming languages use the above syntax for calling a function, the developers often think it is the way to call stored procedures. However, if you try to call an SP with above syntax, it will give you an error.

There are two different methods to call stored procedures. Let us see them here, however before we do that, let us first create a sample SP which accepts two parameters.

Creating Stored Procedure

-- Create Stored Procedure

create PROCEDURE [dbo].[ShippedOrder] @salesid VARCHAR(50),@custid VARCHAR(50)
AS
SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]
FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b
ON a.[SalesOrderID] = b.[SalesOrderID]
INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID and a.[SalesOrderID]=@salesid and a.CustomerID=@custid
GO
Now let us see two different methods to call SP.

Method 1:
Let us run following statements, where the order of the parameters is a different order.

-- Execute SP
EXEC [AdventureWorksLT2012].[dbo].ShippedOrder 71780,30113
GO
EXEC [AdventureWorksLT2012].[dbo].ShippedOrder 30113,71780
GO

When you run above script, it will show us result where the order of the columns is changed as SP assigns the first params to the first parameters inside.

However, if you want to assign your passed parameters to specific variable inside SQL Server, you will have to mention that parameter as mentioned in the second method.In the first statement it will show the result but in second statement no output will come.

Method 2:

In this method when we execute the stored procedure, we also include the variable to which we want to assign parameter. This way is the certain way to assign value to parameter inside a SP.

-- Execute SP
EXEC AdventureWorksLT2012.dbo.ShippedOrder @salesid = 71780, @custid = 30113
GO
EXEC AdventureWorksLT2012.dbo.ShippedOrder @custid = 30113,@salesid = 71780
GO

Now when you execute this SP, you will get results in the same order in both the case as we have specified the parameters to assign when we passed variable values.

read less
Comments
Dislike Bookmark

Looking for MS SQL Development Training

Find best MS SQL Development Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 30/05/2017 IT Courses/MS SQL/MS SQL Development Tuition/BA Tuition/Database Management Systems IT Courses/MS SQL

How To Insert The Result Sets Of A Stored Procedure/Functions Into A Temporary Table In SQL Server?

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

How to Insert the Result sets of a Stored Procedure/Functions into a Temporary Table in SQL Server In some cases with SQL Server, there may be an instance where you wish to take the resulting data from a stored procedure and insert it into a temporary table for use in another query. Determining how... read more

How to Insert the Result sets of a Stored Procedure/Functions into a Temporary Table in SQL Server

In some cases with SQL Server, there may be an instance where you wish to take the resulting data from a stored procedure and insert it into a temporary table for use in another query. Determining how to accomplish this task can be somewhat difficult, so we’ll briefly outline a couple options, depending on your specific needs and database configuration.

Using the Openrowset Statement:

One possibility is to use the openrowset statement, which allows you to access remote data from an OLE DB source and can be executed directly from within another SQL statement. Openrowset is a one - time connection and data retrieval method, so it should not be utilized for frequent connections (linking servers is preferable in that case).

Openrowset can be the target of any Insert, Delete or Update statement, which makes it ideal for our purposes of “executing” our stored procedure for us and extracting that data back out to our waiting temporary table. Also supports bulk operations through a built-in bulk provider that enables data from a file to be read and returned as a rowset.

Before using openrowset, it may be necessary to modify some configuration options, specifically by allowing ad hoc access. This can be configured using the following statements:

Now we can utilize Openrowset, which has a particular syntax that must be adhered to:

OPENROWSET (

  ,

  ,

 

)

Step 1: Enable Ad Hoc Distributed Queries:

sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

Step 2: Insert Results of Stored Procedure/Functions into a Temporary Table:

-- Create a Stored Procedure using AdventureWorksLT2012 database

CREATE PROCEDURE GetShippedOrder

AS

SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]

    FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b

        ON a.[SalesOrderID] = b.[SalesOrderID]

        INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID

GO

-- Execute Stored Procedure

EXEC GetShippedOrder

GO

-- Create a Inline Function

CREATE FUNCTION GetShippedOrders()

RETURNS TABLE

AS

RETURN SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]

    FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b

        ON a.[SalesOrderID] = b.[SalesOrderID]

        INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID

    WHERE a.[ShipDate] IS NULL

GO

-- Run the Function

select * from  GetShippedOrders()

Step 3: Insert into Temp Table:

-- Insert into Temp Table from Stored Procedure

SELECT *

INTO #TempTable

FROM OPENROWSET('SQLNCLI', 'Server=Amitava-PC;Trusted_Connection=yes;','EXEC [AdventureWorksLT2012].[dbo].GetShippedOrder')

GO

 -- Select Data from Temp Table

SELECT *

FROM #TempTable

GO

-- Insert into Temp Table from Inline Function

SELECT *

INTO #TempTable

FROM OPENROWSET('SQLNCLI','Server=AMITAVA-PC;Trusted_Connection=yes;','select * from  [AdventureWorksLT2012].[dbo]. GetShippedOrders()')

GO

-- Select Data from Temp Table

SELECT *

FROM #TempTable

GO.

 

 

read less
Comments
Dislike Bookmark

Answered on 27/06/2017 IT Courses/MS SQL/MS SQL Development

Vinod Gummadi

Oracle Fusion Trainer

we will providing training . We do have trainers for SQL server and all.
Answers 3 Comments 1
Dislike Bookmark

Lesson Posted on 23/05/2017 IT Courses/MS SQL IT Courses/MS SQL/MS SQL Development

SQL Server -- CAST/CONVERT to string -- Pad zeroes or spaces to an integer

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

Recently, I came across quite a common question – as part of a data migration, someone wanted to pad integers with zeroes. There are various variations to this question, namely: How do I pad zeroes to convert an integer to a fixed length string? How do I pad zeroes before an integer? How... read more

Recently, I came across quite a common question – as part of a data migration, someone wanted to pad integers with zeroes. There are various variations to this question, namely:

How do I pad zeroes to  convert an integer to a fixed length string?

How do I pad zeroes before an integer?

How to I pad blank spaces before an integer?

All of these questions have quite a simple solution, which I am going to present before you today.

The script demonstrates the process of padding the required values to a set of integers in a test table. The script:

  1. Converts the Integer to a string
  2. Appends this string representation of the integer to the padding string
  3. Finally, returns the required number of characters from the right of the string

For the purposes of this demo, I have shown the result with two padding characters – a zero (0) and an asterisk (*).

Have you ever faced such a requirement as part of a data migration or an integration? Do you use a similar approach? Do share your thoughts and suggestions in the space below.

--Pad zeroes in string representation of a number
USE tempdb;
GO
--Safety Check
IF OBJECT_ID('dbo.TestTable','U') IS NOT NULL
BEGIN
   DROP TABLE dbo.TestTable;
END
GO
 
--Create the test tables
CREATE TABLE dbo.TestTable
            (RecordId    INT NOT NULL IDENTITY(1,1),
             RecordValue INT     NULL
            );
GO
 
--Populate some test data
INSERT INTO dbo.TestTable (RecordValue)
VALUES (123),
       (1023),
       (NULL);
GO
 
/**************** PADDING CHARACTER: ZERO (0) ****************************/
 
--Change the padding character and the number of strings as required
DECLARE @requiredStringLength INT = 10;
DECLARE @paddingCharacter CHAR(1) = '0'
--The script:
--1. Converts the Integer to a string
--2. Appends this string representation of the integer to the padding string
--3. Finally, returns the required number of characters from the right of the string
SELECT RecordId,
       RecordValue AS OriginalValue,
       RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength )
              + CAST(RecordValue AS VARCHAR(20))
              ),
              @requiredStringLength
            ) AS PaddedValue
FROM dbo.TestTable AS tt;
GO
 
/* RESULTS
RecordId    OriginalValue PaddedValue
----------- ------------- ------------
1           123           0000000123
2           1023          0000001023
3           NULL          NULL
 
*/
 
/**************** PADDING CHARACTER: ASTERISK (*) ****************************/
 
--Change the padding character and the number of strings as required
DECLARE @requiredStringLength INT = 10;
DECLARE @paddingCharacter CHAR(1) = '*'
--The script:
--1. Converts the Integer to a string
--2. Appends this string representation of the integer to the padding string
--3. Finally, returns the required number of characters from the right of the string
SELECT RecordId,
       RecordValue AS OriginalValue,
       RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength )
               + CAST(RecordValue AS VARCHAR(20))
              ),
              @requiredStringLength
            ) AS PaddedValue
FROM dbo.TestTable AS tt;
GO
 
/* RESULTS
RecordId    OriginalValue PaddedValue
----------- ------------- ------------
1           123           *******123
2           1023          ******1023
3           NULL          NULL
*/
read less
Comments
Dislike Bookmark

Looking for MS SQL Development Training

Find best MS SQL Development Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 10/04/2017 IT Courses/MS SQL/MS SQL Development

SQL Statements

Ajeet singh

A Microsoft Certified Professional with around 10 years of total IT experience in the Design, Modeling,...

SQL SELECT Statement The SELECT statement is used to select data from a database. The SQL SELECT Statement The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. SQL SELECT Syntax SELECT column_name,column_name FROM table_name; and CustomerID CustomerName ContactName Address City PostalCode Country 1 Alfreds... read more

SQL SELECT Statement

The SELECT statement is used to select data from a database.

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT column_name,column_name
FROM table_name;

and

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

4

Around the Horn

Thomas Hardy

120 Hanover Sq.

London

WA1 1DP

UK

5

Berglunds snabbköp

Christina Berglund

Berguvsvägen 8

Luleå

S-958 22

Sweden

SELECT * FROM table_name;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

SELECT Column Example

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:

Example

SELECT CustomerName,City FROM Customers;

Try it Yourself »

SELECT * Example

The following SQL statement selects all the columns from the "Customers" table:

Example

SELECT * FROM Customers;

Try it Yourself »

Navigation in a Result-set

Most database software systems allow navigation in the result-set with programming functions, like: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.

Programming functions like these are not a part of this tutorial. To learn about accessing data with function calls, please visit our PHP tutorial.

 

SQL SELECT Statement

â?® PreviousNext â?¯

The SELECT statement is used to select data from a database.

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT column_name,column_name
FROM table_name;

and

SELECT * FROM table_name;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

4

Around the Horn

Thomas Hardy

120 Hanover Sq.

London

WA1 1DP

UK

5

Berglunds snabbköp

Christina Berglund

Berguvsvägen 8

Luleå

S-958 22

Sweden

SELECT Column Example

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:

Example

SELECT CustomerName,City FROM Customers;

Try it Yourself »

SELECT * Example

The following SQL statement selects all the columns from the "Customers" table:

Example

SELECT * FROM Customers;

Try it Yourself »

Navigation in a Result-set

Most database software systems allow navigation in the result-set with programming functions, like: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.

Programming functions like these are not a part of this tutorial. To learn about accessing data with function calls, please visit our PHP tutorial.

read less
Comments
Dislike Bookmark

Lesson Posted on 10/04/2017 IT Courses/MS SQL/MS SQL Development

SQL And or Operator

Ajeet singh

A Microsoft Certified Professional with around 10 years of total IT experience in the Design, Modeling,...

SQL AND & OR Operators The AND & OR operators are used to filter records based on more than one condition. The SQL AND & OR Operators The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first... read more

SQL AND & OR Operators

The AND & OR operators are used to filter records based on more than one condition.

The SQL AND & OR Operators

The AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

4

Around the Horn

Thomas Hardy

120 Hanover Sq.

London

WA1 1DP

UK

5

Berglunds snabbköp

Christina Berglund

Berguvsvägen 8

Luleå

S-958 22

Sweden

AND Operator Example

The following SQL statement selects all customers from the country "Germany" AND the city "Berlin", in the "Customers" table:

Example

SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';

Try it Yourself »

OR Operator Example

The following SQL statement selects all customers from the city "Berlin" OR "München", in the "Customers" table: 

Example

SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

Try it Yourself »

Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).

The following SQL statement selects all customers from the country "Germany" AND the city must be equal to "Berlin" OR "München", in the "Customers" table:

Example

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

Try it Yourself »

read less
Comments
Dislike Bookmark

Lesson Posted on 10/04/2017 IT Courses/MS SQL/MS SQL Development

SQL Order By

Ajeet singh

A Microsoft Certified Professional with around 10 years of total IT experience in the Design, Modeling,...

SQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set. The SQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use... read more

SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set.

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by one or more columns.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

4

Around the Horn

Thomas Hardy

120 Hanover Sq.

London

WA1 1DP

UK

5

Berglunds snabbköp

Christina Berglund

Berguvsvägen 8

Luleå

S-958 22

Sweden

ORDER BY Example

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:

Example

SELECT * FROM Customers
ORDER BY Country;

Try it Yourself »

ORDER BY DESC Example

The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

Example

SELECT * FROM Customers
ORDER BY Country DESC;

Try it Yourself »

ORDER BY Several Columns Example

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column:

Example

SELECT * FROM Customers
ORDER BY Country, CustomerName;

Try it Yourself »

ORDER BY Several Columns Example 2

The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

Example

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

read less
Comments
Dislike Bookmark

Looking for MS SQL Development Training

Find best MS SQL Development Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 10/04/2017 IT Courses/MS SQL/MS SQL Development

SQL INSERT into Statement

Ajeet singh

A Microsoft Certified Professional with around 10 years of total IT experience in the Design, Modeling,...

SQL INSERT INTO Statement The INSERT INTO statement is used to insert new records in a table. The SQL INSERT INTO Statement The INSERT INTO statement is used to insert new records in a table. SQL INSERT INTO Syntax It is possible to write the INSERT INTO statement in two forms. The first form does... read more

SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

SQL INSERT INTO Syntax

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,...);

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

87

Wartian Herkku

Pirkko Koskitalo

Torikatu 38

Oulu

90110

Finland

88

Wellington Importadora

Paula Parente

Rua do Mercado, 12

Resende

08737-363

Brazil

89

White Clover Markets

Karl Jablonski

305 - 14th Ave. S. Suite 3B

Seattle

98128

USA

90

Wilman Kala

Matti Karttunen

Keskuskatu 45

Helsinki

21240

Finland

91

Wolski

Zbyszek

ul. Filtrowa 68

Walla

01-012

Poland

INSERT INTO Example

Assume we wish to insert a new row in the "Customers" table.

We can use the following SQL statement:

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');


Try it Yourself »

The selection from the "Customers" table will now look like this:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

87

Wartian Herkku

Pirkko Koskitalo

Torikatu 38

Oulu

90110

Finland

88

Wellington Importadora

Paula Parente

Rua do Mercado, 12

Resende

08737-363

Brazil

89

White Clover Markets

Karl Jablonski

305 - 14th Ave. S. Suite 3B

Seattle

98128

USA

90

Wilman Kala

Matti Karttunen

Keskuskatu 45

Helsinki

21240

Finland

91

Wolski

Zbyszek

ul. Filtrowa 68

Walla

01-012

Poland

92

Cardinal

Tom B. Erichsen

Skagen 21

Stavanger

4006

Norway

Did you notice that we did not insert any number into the CustomerID field?
The CustomerID column is automatically updated with a unique number for each record in the table.

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field will of course also be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');


Try it Yourself »

The selection from the "Customers" table will now look like this:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

87

Wartian Herkku

Pirkko Koskitalo

Torikatu 38

Oulu

90110

Finland

88

Wellington Importadora

Paula Parente

Rua do Mercado, 12

Resende

08737-363

Brazil

89

White Clover Markets

Karl Jablonski

305 - 14th Ave. S. Suite 3B

Seattle

98128

USA

90

Wilman Kala

Matti Karttunen

Keskuskatu 45

Helsinki

21240

Finland

91

Wolski

Zbyszek

ul. Filtrowa 68

Walla

01-012

Poland

92

Cardinal

null

null 

Stavanger

null

Norway

 

read less
Comments
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best MS SQL Development Training in India. Post Your Requirement today and get connected.

Overview

Lessons 22

Total Shares  

+ Follow 2,948 Followers

Top Contributors

Connect with Expert Tutors & Institutes for MS SQL Development

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more