Loading...

Follow Database Research & Development Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

In this post, I am sharing the use of “unaccent” extension of PostgreSQL.

Use unaccent, for removing diacritic signs from the string and convert into the valid character as per the specified collation.

When we are importing data from different types of datasets, we can find diacritic signs in the data. We can use unaccent() for removing those signs.

Check below demonstration:

Create an extension:

CREATE EXTENSION unaccent;

Use unaccent():

SELECT unaccent('ô ã À Æ ß Ä');

Result:

unaccent
------------------
'o a A AE ss A'
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Check the below input data and expected output to prepare the report on Manager -> Employee Nth level hierarchy basis on the ManagerID column.

Input Data:

EmpID       EmpName    ManagerID
----------- ---------- -----------
1           Anvesh     NULL
2           Neevan     NULL
3           Mukesh     1
4           Rajesh     3
5           Nupur      2
6           Roy        5
7           Martin     6
8           Manish     1
9           Eric       2
10          Purv       9

Expected Output:

EmpName    Hierarchy FullHierarchyName
---------- --------- --------------------------
Anvesh     1         Anvesh.
Manish     8         Anvesh..Manish.
Mukesh     3         Anvesh..Mukesh.
Rajesh     4         Anvesh..Mukesh..Rajesh.
Neevan     2         Neevan.
Eric       9         Neevan..Eric.
Purv       10        Neevan..Eric..Purv.
Nupur      5         Neevan..Nupur.
Roy        6         Neevan..Nupur..Roy.
Martin     7         Neevan..Nupur..Roy..Martin.

Create a table with sample data:

CREATE TABLE Employees
(
	EmpID INT
	,EmpName VARCHAR(10)
	,ManagerID INT
)
 
INSERT INTO Employees 
VALUES
(1,'Anvesh',NULL)
,(2,'Neevan',NULL)
,(3,'Mukesh',1)
,(4,'Rajesh',3)
,(5,'Nupur',2)
,(6,'Roy',5)
,(7,'Martin',6)
,(8,'Manish',1)
,(9,'Eric',2)
,(10,'Purv',9)

Solution:

;WITH CTE(EmpName , EmpId, Level,FullHierarchyName) AS (
     Select E.EmpName, E.EmpID, 0 Level
     ,Cast(E.EmpName+'.' as Varchar(MAX)) FullHierarchyName         
     From Employees E Where E.ManagerID IS NULL
     UNION ALL
     Select E.EmpName, E.EmpID, c.Level + 1 , c.FullHierarchyName+'.'+E.EmpName+'.' FullHierarchyName  
     From Employees E INNER JOIN CTE c on c.EmpID = e.ManagerID 
)
SELECT H.EmpName ,CAST(H.EmpID AS VARCHAR(2)) Hierarchy , FullHierarchyName FROM CTE H 
ORDER BY H.FullHierarchyName

Please try the different solution for this puzzle and share it via comment...

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this post, I am sharing a SQL Interview questions for Database Developer. If you are database fresher and working with SQL, you must prepare for string/date operations related questions and answers.

One of the example questions is How to remove New Line Character from a string?

Check the below sample code:

DECLARE @str VARCHAR(100)
SET @str = 'I am Database 
Engineer'

SELECT @str AS With_New_Line

With_New_Line
---------------------
I am Database 
Engineer

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '') AS Without_New_Line

Without_New_Line
---------------------------
I am Database Engineer

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Check the below input data and expected output to find the week start day and week end day of given Month and Year.

Input data:

Month=8
Year=2017

Expected Output:

WeekStart  WeekEnd
---------- ----------
2017-08-01 2017-08-06
2017-08-07 2017-08-13
2017-08-14 2017-08-20
2017-08-21 2017-08-27
2017-08-28 2017-08-31

Solution:

DECLARE 
	@Month AS INT = 8
	,@Year AS INT = 2017

;WITH CTE AS
(
     SELECT DISTINCT 
		number
		,DATEFROMPARTS(@Year,@Month,Number) DateValue
		 ,DATEADD(d, (8 - datepart(WEEKDAY, DATEFROMPARTS(@Year,@Month,Number))), DATEFROMPARTS(@Year,@Month,Number)) WeekNumber
     FROM MASTER..SPT_Values
     WHERE Number > 0 AND number < DAY(EOMONTH(CONCAT(@Year,'/',@Month,'/','01'))) 
)
,CTE1 AS 
(
	SELECT 
		CASE WHEN number = 1 THEN DATEADD(d,-1,DateValue) ELSE DateValue End DateValue 
		,CASE WHEN WeekNumber > EOMONTH(DateValue) THEN EOMONTH(DateValue) ELSE WeekNumber END WeekNumber         
     FROM CTE
)
SELECT 
	MIN(DATEADD(d,1,DateValue)) as WeekStart 
	,WeekNumber as WeekEnd 
FROM CTE1
GROUP BY WeekNumber
ORDER BY WeekStart

Please try the different solution for this puzzle and share it via comment...

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this post, I am sharing few mathematical operators for finding factorial, absolute and negation value in PostgreSQL.

I was reading the official the official page of PostgreSQL and found few interesting operators which we can use for the mathematical purpose.

Check below examples:

! operator for factorial:

SELECT 8 ! AS "8 factorial";

8 factorial
------------
'40320'

@ operator for absolute:

SELECT @ '-8.8' AS "absolute value";

absolute values
------------
'40320'

~ operator for negation:

SELECT ~ CAST('16' AS int8) AS "negation value";

negation value
---------------
'-17'
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Whenever you are searching for SQL query optimization tips or prevent SQL injection tips, you can find few common suggestions like: Please do not use Dynamic SQL in your Stored Procedure. Please try to avoid the use of Temp Tables.

I always tell to database developers, please try to avoid the use of Dynamic SQL and Temp Table in your Stored Procedures.

A Temp Table requires additional CPU/IO which degrades the query performance. A Dynamic SQL is not safe against the SQL Injection and It also compiles data every time which requires more Memory.

Here, I am sharing one of the important scripts which gives you list of SQL Server Stored Procedures, which use the Dynamic SQL and Temp Table.
You can easily get the list of Stored Procedures and can make plans of modifying it.

Prepared this script using sys.dm_exec_describe_first_result_set and sys.procedures where error number 4 = Dynamic SQL and error number 10 = Temporary Table.

SELECT  
	SP_Name
	,error_message
	,error_type_desc 
FROM (SELECT schema_name(schema_id)+'.'+name SP_Name FROM sys.procedures) AS T
CROSS APPLY sys.dm_exec_describe_first_result_set (SP_Name, NULL, 0) 
WHERE error_type IN (4,10)
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Beginners are always searching for sample database so, in this post, I am sharing an official download link to a MySQL Sakila sample database which we can use for our testing purpose.

Sakila sample database is a DVD rental database. The database contains sample tables, views, SPs, Functions and Triggers.

The MySQL beginner can install this database and can start the practice with it.
PostgreSQL beginner can also use this database.

Please check the official page here, and you can see all require documentation like, database structure, installation steps.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this post, I am sharing a script for transforming normal DB user to Super User in PostgreSQL.

The Super User is the admin user and it has all kind of access to PostgreSQL Server. We can create multiple superusers in PostgreSQL.

I need this kind of handy commands/scripts because when any new dba join the team, giving basic permission of database and later the same user requires superuser permission.

Convert a user into Super User:

ALTER USER user_name WITH SUPERUSER;

Revoke Super User Permission:

ALTER USER user_name WITH NOSUPERUSER;
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Check the below input data and expected output to replace the NULL value with the previous NON-Null value.

Input Data:

Id          Code
----------- ----
1           ABC
2           XYZ
3           NULL
4           NULL
5           PQR
6           NULL
7           ERT
8           NULL

Expected Output:

Id          Code Previous_NonNullCode
----------- ---- --------------------
1           ABC  ABC
2           XYZ  XYZ
3           NULL XYZ
4           NULL XYZ
5           PQR  PQR
6           NULL PQR
7           ERT  ERT
8           NULL ERT

Create a table with data:

CREATE TABLE tbl_Values
(
   Id INT
  ,Code VARCHAR(3)
)
GO

INSERT INTO tbl_Values 
VALUES
(1,'ABC'),(2,'XYZ'),(3,NULL),(4,NULL)
,(5,'PQR'),(6,NULL),(7,'ERT'),(8,NULL)
GO

Solution:

;WITH CTE AS
(
    SELECT *,ROW_NUMBER() OVER (ORDER BY Id) rnk 
    FROM tbl_Values
)
SELECT 
	Id
	,Code
	,(SELECT TOP 1 Code FROM CTE a WHERE a.rnk = 
	(SELECT MAX(b.rnk) FROM CTE b WHERE b.rnk <= c.rnk AND b.Code IS NOT NULL)) Previous_NonNullCode         
FROM CTE c

Please try the different solution for this puzzle and share it via comment...

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Check the below input data and expected output to start the range from 0 to first value and the first value to second value so on.

Input Data:

Val
-----------
108
116
226
308
416
426

Expected Data:

LowerRange  UpperRange
----------- -----------
0           108
108         116
116         226
226         308
308         416
416         426
426         NULL

Create a table with sample data:

CREATE TABLE Ranges
(Val INT)
GO
 
INSERT INTO Ranges(Val) 
VALUES 
(108),(116),
(226),(308),
(416),(426)

Solution 1:

;with CTE as 
(
select *,row_number()over (order by Val) as rnk from (select 0 Val union select Val from Ranges) AS T
)
select a.Val as LowerRange,b.Val as UpperRange 
from CTE a 
left outer join CTE b on a.rnk = b.rnk-1

Solution 2:

;WITH CTE AS
(
SELECT Val , ROW_NUMBER() OVER (ORDER BY Val ASC) rnk FROM
(     
       SELECT 0 Val 
       UNION ALL
       SELECT Val from Ranges
)a
)
SELECT c.Val LowerRange , (SELECT TOP 1 Val FROM CTE c1 WHERE c1.rnk > c.rnk ) UpperRange
FROM CTE c

Please try the different solution for this puzzle and share it via comment...

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview