Difference between a WHERE clause and HAVING clause?

• A WHERE clause used to filter the physical rows in your table(s) whereas the HAVING clause operates on groups of a result-set (usually aggregate) and specifies which of the summary rows are returned in your resultset.  
• A WHERE clause is independent of GROUP BY clause but the HAVING clause does depend on this, although it can be used without this as well.
•  First SQL Server filters the physical rows using WHERE conditions and then performs GROUP BY on filtered data in memory and then filters the aggregated rows again with HAVING clause.

Example:

List of all the employee with basic greater than 3000.

Now I want to calculate the Dept codes for which total of basic is greater than 20,000 for those employees who are getting basic more than 3000.

as

Concatenating Row Values using Transact-SQL

Sometime SQL programmers come across a requirement to concatene a result-set using T-SQL query.

Below is an example of this scenarion:

SELECT Dept_Code,Emp_Name

FROM Employee (NOLOCK)

ORDER BY Dept_Code,Emp_Name

And user wants to see the data in below format:

You can do these using Transact SQL. Below is the T-SQL query to achieve this solution:

Method1: Concatenation that uses the FOR XML clause with PATH mode:

SELECT E1.Dept_Code,

(

   SELECT Emp_Name + ',' FROM Employee E2

   WHERE E2.Dept_Code = E1.Dept_Code

   ORDER BY Emp_Name

   FOR XML PATH('')

) AS Emp_Name

FROM Employee E1

GROUP BY Dept_Code

Method2: There is a similar approach using the CROSS APPLY operator.

SELECT DISTINCT E1.Dept_Code, Temp.Emp_Name

FROM Employee E1 CROSS APPLY

(

   SELECT Emp_Name + ',' FROM Employee E2

   WHERE E2.Dept_Code = E1.Dept_Code

   ORDER BY Emp_Name

   FOR XML PATH('')

) AS Temp (Emp_Name)

Regular Expression Problem in T-SQL

I have one SQL Challenge for you:

A table has one column Code. Here is the sample data.

DECLARE @T TABLE(Code varchar(20))

INSERT @T VALUES

('STQ-309-A65'),('XYZ-999-A65'),

('AZZ-345-B66'),('CzA-123-C671'),

('GUP-999-C67'),('STQ-123-c67'),

('AtT-456-B66'),('ATT-000-B66'),

('AWT-101-A65'),('AUV-111-d68'),

('stq-007-c67'),('att-123-A97'),

('stq-777-c99'),('byz-789-d100'),

('stq-111-250'),('1at-p2a-149')

You need to filter the codes based on below conditions:

1. Code can be only 11 or 12 CHAR long.

2. First char must be a - s or A - S

3. Second char must be t - z to T - Z

4. Third char can be any char a - z or A - Z but not a digit.

5. Digit 4th and 8th must be "-"

6. Char 5th, 6th, and 7th must be a digit.

7. Char 5th should be non-zero digit.

8. Char 8th can be a - z but not a digit

9. Position 9th and 10th must be ASCCI value of 8th CHAR. If ASCII code is of three digit then 9th, 10th, and 11th position should be occupy by ASCII code.

How to connect to SQL Server using RUNAS command from command prompt?

How to connect to SQL Server Management Studio through RUNAS command from command prompt using credentials like Security Group and Password?

Here you go:

Step 1: Open a new command window by simply typing cmd in Run.

Step 2: Type RUNAS /user:UserID "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe" and press enter. Here UserID is your login credential.




Note: Check the path of Ssms.exe in your system. This path can be different, so make sure you are entering correct path.

Step 3: Now enter Password to connect to SQL Server.





Step 4: as soon as you enter the password and press enter, it will open SQL Server Management Studio for given credentials