@manhnguyenv

Welcome to my blog!

TSQL

December 14, 2017 18:02

T-SQL

  • COALESCE
  • FOR XML PATH
  • STUFF
  • REPLACE
  • SUBSTRING
  • DENSE_RANK
  • TABLE-VALUED USER DEFINED FUNCTION
  • CROSS APPLY
  • OUTER APPLY
  • GROUP BY with ROLLUP & CUBE
  • ER Model

Websites:

http://www.sqlservercentral.com/

https://www.mssqltips.com/

https://www.red-gate.com/simple-talk/sql/t-sql-programming/

https://blog.sqlauthority.com/

Entity Relationship Model

Mô hình quan hệ thực thể

An entity–relationship model describes inter-related things of interest in a specific domain of knowledge. An ER model is composed of entity types and specifies relationships that can exist between instances of those entity types.

Liên quan đến sự phát triển của mô hình cơ sở dữ liệu mà không liên quan đến việc thực hiện vật lý hoặc DBMS

https://viblo.asia/p/mo-hinh-quan-he-thuc-the-entity-relationship-model-oOVlYEenl8W

Tools

https://sourceforge.net/projects/erdcreator/

http://www.erdiagrams.com/

Câu hỏi trong SQL

https://career.guru99.com/top-50-sql-question-answers/

http://onlydifferencefaqs.blogspot.com/2012/08/sql-server-difference-between-questions.html

http://a4academics.com/interview-questions/53-database-and-sql/397-top-100-database-sql-interview-questions-and-answers-examples-queries

http://a4academics.com/interview-questions/53-database-and-sql/411-sql-interview-questions-and-answers-database

https://dwbi.org/database/sql/72-top-20-sql-interview-questions-with-answers

https://intellipaat.com/interview-question/sql-interview-questions/

GROUP BY with ROLLUP & CUBE

https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

Hàm xử lý chuỗi trong SQL

-- 13. replace
-- thay thế tất cả các chuỗi con nào đó trong 1 chuỗi thành 1 chuỗi con khác
select replace('hanh trang lap trinh dot com accom','com','net')
-- Tất cả các từ 'com' trong chuỗi sẽ được thay bằng từ 'net'
-- Kết quả: hanh trang lap trinh dot net acnet
-- 14. replicate
-- nhân 1 chuỗi lên nhiều lần 
select replicate('acb',5) 
-- chuỗi 'acb' được lặp lại 5 lần. 
-- kết quả: acbacbacbacbacb
-- 15. reverse 
-- quay ngược chuỗi lại 
select reverse('abcdef') 
-- kết quả: fedcba
-- 18. stuff 
-- hàm này sẽ thay thế n ký tự bắt đầu từ 1 vị trí xác định bằng 1 chuỗi muốn thay vào 
SELECT STUFF('abcdef', 2, 3, 'ijklmn'); 
-- giải thích: từ vị trí 2 tức là ký tự 'b' lấy ra 3 ký tự -> 'bcd' và thay thế bằng 'ijklmn'  
-- kết quả: aijklmnef
-- 19. substring 
-- cắt ra 1 chuỗi nhỏ từ chuỗi ban đầu 
select substring('abcdefgh',2,5)
-- giải thích: từ vị trí thứ 2 lấy ra 5 ký tự liên tiếp 
-- kết quả: bcdef

CROSS APPLY & OUTER APPLY

https://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql

Table-Valued user defined function

https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

Write to query to print 1 to 100 in Sql Server?

--------- USING WHILE LOOP --------------

DECLARE @V1 INT
SET @V1=0
WHILE (@V1<100)
BEGIN
SET @V1=@V1+1
PRINT @V1
END

--------- USING GOTO --------------

DECLARE @V1 INT
SET @V1=0
LABEL:
SET @V1=@V1+1
PRINT @V1
IF (@V1<>100)
GOTO LABEL

------ RECURSIVE METHOD USING COMMON TABLE EXPRESSION -------

;WITH CTE
AS
(
SELECT COUNT=1
UNION ALL
SELECT COUNT=COUNT+1
FROM CTE WHERE COUNT<100
)
SELECT COUNT FROM CTE

------ Recursive method using CTE with OPTION -------

;WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number <= 999999
)
SELECT * FROM Numbers

OPTION (MAXRECURSION 0) 

------ ROW_NUMBER() OVER (ORDER BY ...) -------

SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY GETDATE())
FROM Master.sys.SysColumns sc1
CROSS JOIN Master.sys.SysColumns sc2

------ Using CTE with FUNCTION -------

CREATE FUNCTION dbo.fnItzekNumsTest(@N AS BIGINT)
RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)
SELECT N FROM Nums WHERE n <= 100

SELECT * FROM dbo.fnItzekNumsTest(100)

----------- USING CURSOR ----------------------

DECLARE @TB TABLE
(
V2 INT
)
INSERT INTO @TB
SELECT 0
DECLARE @V1 INT
DECLARE CUR CURSOR
FOR SELECT V2 FROM @TB
OPEN CUR
FETCH NEXT FROM CUR INTO @V1
WHILE (@@FETCH_STATUS<>-1)
BEGIN
SET @V1=@V1+1
PRINT @V1
IF(@V1=100)
BREAK
END
FETCH NEXT FROM CUR INTO @V1
CLOSE CUR
DEALLOCATE CUR

----------- USING master.dbo.spt_values ----------------------

select number from master..spt_values
where type = 'p'
and number between 1 and 100
order by number

Printing 1 to 100 in SQL Server as a batch

https://www.sqlservercentral.com/Forums/Topic739946-145-1.aspx 

COALESCE and ISNULL

FOR XML PATH

SQL Server Concatenate Multiple Rows into String 01

COALESCE

 

SQL Server Concatenate Multiple Rows into String

STUFF

SELECT TABLE_NAME ,
STUFF(( SELECT ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS Columns
WHERE Tables.TABLE_NAME = Columns.TABLE_NAME
ORDER BY COLUMN_NAME
FOR
XML PATH('')
), 1, 1, '') ConcatColumnNames
FROM INFORMATION_SCHEMA.COLUMNS Tables
GROUP BY TABLE_NAME;

 

Other sample

Users per sector sales

 

DENSE_RANK

 

 

 

Categories

Recent posts