SQL Server 2017 New Functions

Now SQL server 2017 (RC 1) is release in July 2017, with some best features, let’s see some newly introduced String functions:

1.  CONCAT_WS: Concatenates a variable number of arguments with a delimiter specified in the 1st argument.

Example:

SELECT CONCAT_WS(',','1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;

Output:

Address

--------------------------------------

1 Microsoft Way,Redmond,WA,98052

2.   TRANSLATE: Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.

Syntax: TRANSLATE ( inputString, characters, translations)

Example:

SELECT TRANSLATE('[137.4, 72.3]' , '[,]', '( )') AS Point,    TRANSLATE('(137.4 72.3)' , '( )', '[,]') AS Coordinates;

Output

--------------------------------------------

 (137.4 72.3)       [137.4,72.3]

3.  STRING_AGG: Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

EXAMPLE:

                           SELECT town, STRING_AGG (email, ';') AS emails

                             FROM dbo.Employee

                           GROUP BY town;

Output

-------------------------------------------------

Seattle syed0@adventure-works.com;catherine0@adventure-works.com;kim2@adventure-works.com         

SQL Practice Questions

When we planed to learn Database we can divide the learning in four parts:

1. Knowing the DBMS concepts. 
2. Knowing the syntax and concepts of database technology(MS SQL, Oracle etc).
3. Practicing the SQL.
4. Digging the depth of Database engine/ Administration

#3 Practice SQL is most common in all the SQL based database and also the 80% of Database works is done by expert SQL Query Developer. In this blog I am writing some SQL practice question that will help you to test/improve your SQL Skill.


Below SQL Practice question are based on Adventure Works database, you can download the Adventure Works  database from below Link. Click Here to download the database.


To download the Adventure works Data- model you can Click Here

Ad-hoc Query:

1. Show the transaction history of the red color product.
2. List all the product name with category, subcategory along with inventory location.
3. List out the product with the latest price of the product.
4. List out the Product for that their in not a single order in this month.
5. Show the product with special offer, Only the product having special offer.
6. List out all the product with total sales order for that product.
7. Show the product having max number of order this year.
8. Show the details of a employing having max number of order.
9. Show all the person names with home address and with office phone number.
10. Show the employee those who joined this year.

Stored Procedures :

1. Create a procedure to Insert a new Product information, take the parameter as  Product name, category name ,sub category name, Model name etc.
2. Create a procedure to Insert a new employee information, and assign him a department. Along with employee information also take Department name as parameter.
3. Create a procedure to create order for a product.

Note :  You can keep your answer in comment or if you want the query from my side also you can write in comment.