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.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/
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
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
-- 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
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
COALESCE
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
DENSE_RANK
