SQLServer | D E W = 露の世

# Database

# Create

1	create database MarketManagement;

# Use

1	use MarketManagement;

# Table

# Create

If the column name includes a keyword or includes a space, enclose it in brackets, such as [Launch Date].
A primary key constraint can be located after the column definition or can be defined separately after all the column definitions.
Char is fixed length. If the inserted data length is less than the fixed length, it will pad space at the end. Such as the Category is char(6) , if you insert 'MEAT' which is only 4 characters, the data will be stored as 'MEAT ' .
decimal [ ( p [ , s ] ) ] and numeric [ ( p [ , s ] ) ]
- p (precision)
  
  The maximum total number of decimal digits to be stored. This number includes both the left and the right sides of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
- s (scale)
  
  The number of decimal digits that are stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 through p, and can only be specified if precision is specified. The default scale is 0 , and so 0 <= s <= p . Maximum storage sizes vary, based on the precision.
If 99.9999 is inserted into a column with type Decimal (18,2), the data will be rounded up to 100.00 . Conversely, if 99.444 ， is inserted, the data will be rounded down to 99.44 .

create table Products (
	--ProductId int identity(1, 1) primary key,
    ProductId int identity(1, 1),
	Category char(6) not null,
 	Name varchar(50) unique not null,
 	Weight float check (Weight >= 0),
 	Price decimal(18, 2),
 	[Launch Date] Date,
 	constraint PK_Products primary key (ProductId)
);

# Get Create Table Script

USE [MarketManagement]
GO

/****** Object:  Table [dbo].[Products]    Script Date: 5/31/2024 5:25:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Products](
	[ProductId] [int] IDENTITY(1,1) NOT NULL,
	[Category] [char](6) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Weight] [float] NULL,
	[Price] [decimal](18, 2) NULL,
	[Launch Date] [date] NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
	[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
	[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [check_price] CHECK  (([price]>=(0)))
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [check_price]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD CHECK  (([Weight]>=(0)))
GO

# View Table Details

1	exec sp_help Products;

# Insert

1
2
3

insert into Products
values ('MEAT', 'Beef', 99.9999, 99.9999, '2024-05-30'),
       ('MEAT', 'Pork', 99.9999, 99.99, '2024-05-30');

# Select

1	select * from Products;

ProductId	Category	Name	Weight	Price	Launch Date
1	`MEAT`	Beef	99.9999	`100.00`	2024-05-30
2	`MEAT`	Pork	99.9999	99.99	2024-05-30

# Select * Expand

select Columns and drag to the destination.

# Top

1
2
3

select top 1 * from Products;

select top 50 percent * from Products;

# Union and Order by

order by execute after union.

select 2
union all
select 1;

--2
--1

select 2
union all
select 1
order by 1;

--1
--2

# Join

DROP TABLE IF EXISTS LeftTable

CREATE TABLE LeftTable
(
    Id INT,
    Name NVARCHAR(10)
)
INSERT INTO LeftTable
(
    Id,
    Name
)
VALUES
(1, 'Red'), (2, 'Green'), (3, 'Blue'), (4, 'Yellow'), (5, 'Purple');

DROP TABLE IF EXISTS RightTable;

CREATE TABLE RightTable
(
    Id INT,
    ReferenceId INT,
    Name NVARCHAR(10)
)
INSERT INTO RightTable
(
    Id,
    ReferenceId,
    Name
)
VALUES
(1, 1, 'Dog'), (2, 1, 'Cat'), (3, 2, 'Bird'), (4, 4, 'Horse'), (5, 3, 'Bear'), (6, 1, 'Deer'), (7, 7, 'Fish');
GO

1	SELECT * FROM LeftTable

Id	Name
1	Red
2	Green
3	Blue
4	Yellow
5	Purple

1	SELECT * FROM RightTable

Id	ReferenceId	Name
1	1	Dog
2	1	Cat
3	2	Bird
4	4	Horse
5	3	Bear
6	1	Deer
7	7	Fish

# Inner Join

SELECT L.Name,
       R.Name
FROM LeftTable L
    INNER JOIN RightTable R
        ON R.ReferenceId = L.Id;

Name	Name
Red	Dog
Red	Cat
Green	Bird
Yellow	Horse
Blue	Bear
Red	Deer

# Left Outer Join

SELECT L.Name,
       R.Name
FROM LeftTable L
    LEFT OUTER JOIN RightTable R
        ON R.ReferenceId = L.Id;

Name	Name
Red	Dog
Red	Cat
Red	Deer
Green	Bird
Blue	Bear
Yellow	Horse
Purple	NULL

# Right Outer Join

SELECT L.Name,
       R.Name
FROM LeftTable L
    RIGHT OUTER JOIN RightTable R
        ON R.ReferenceId = L.Id;

Name	Name
Red	Dog
Red	Cat
Red	Deer
Green	Bird
Blue	Bear
Yellow	Horse
NULL	Fish

# Full Outer Join

SELECT L.Name,
       R.Name
FROM LeftTable L
    FULL OUTER JOIN RightTable R
        ON R.ReferenceId = L.Id;

Name	Name
Red	Dog
Red	Cat
Red	Deer
Green	Bird
Blue	Bear
Yellow	Horse
Purple	NULL
NULL	Fish

# Cross Apply

Cross Apply is similar to Inner Join.

SELECT L.Name,
       R.Name
FROM LeftTable L
    CROSS APPLY
(SELECT Name FROM RightTable R WHERE R.ReferenceId = L.Id) R;

Cross Apply with a Table Expression.

SELECT *
FROM LeftTable L
    CROSS APPLY
(
    SELECT TOP 2
        R.Name
    FROM RightTable R
    WHERE R.ReferenceId = L.Id
    ORDER BY R.Id DESC
) R;

Id	Name	Name
1	Red	Deer
1	Red	Cat
2	Green	Bird
3	Blue	Bear
4	Yellow	Horse

Cross Apply with a Table-valued Functions.

CREATE OR ALTER FUNCTION dbo.GetTopAnimal
(
    @Id AS INT
)
RETURNS TABLE
AS
RETURN
     (SELECT TOP 2 Name
      FROM RightTable R
      WHERE R.ReferenceId = @Id
      ORDER BY R.Id DESC);
GO

SELECT L.Id, L.Name, R.Name
FROM LeftTable L
  Cross APPLY dbo.GetTopAnimal(L.Id) R;

Id	Name	Name
1	Red	Deer
1	Red	Cat
2	Green	Bird
3	Blue	Bear
4	Yellow	Horse

# Outer Apply

Outer Apply is similar to Left Outer Join.

SELECT L.Name,
       R.Name
FROM LeftTable L
    OUTER APPLY
(SELECT Name FROM RightTable R WHERE R.ReferenceId = L.Id) R;

# Update

# Multi Table Join Update

1
2
3

update edi_head
   set transfer_time = cast(b.transfer_time as datetime) from edi_head a, #edi_temp b
 where a.head_id = b.head_id;

# Alter

exec sp_rename Products,Product; --rename table name
exec sp_rename Product,Products;

alter table Products add extra_column int null;
alter table Products drop column extra_column;

alter table Products alter column [Launch Date] datetime;
alter table Products alter column [Launch Date] date;

alter table Products add constraint check_price check (price >= 0);

# Table Relation

# Many to Many

create table Movies
(
	MovieId int identity(1,1) primary key,
	Title nvarchar(100) not null,
	Description nvarchar(max),
	LaunchDate Date
);

create table Actors
(
    ActorId int identity(1,1) primary key,
	Name nvarchar(200) not null,
	Country varchar(100)
);

create table MovieActor
(
    MovieActorId int identity(1,1) primary key,
	MovieId int foreign key references Movies(MovieId),
	ActorId int foreign key references Actors(ActorId),
	PaymentRate decimal(10, 2)
);

# Temp Table & Table Variables

create table #edi_temp (
	head_id varchar(25),
	transfer_time varchar(100)
);

insert into #edi_temp
values ('H00039827', '2019/3/27 14:15:13'),
       ('H00039871', '2019/5/13 13:10:49'),
       ('H00039872', '2019/5/13 13:10:49'),
       ('H00039873', '2019/5/13 13:10:49');


select *
  into #products
  from Products;

declare @emp table (
  empno INT PRIMARY KEY,
  ename NVARCHAR(10),
  job NVARCHAR(9)
)

insert into @emp
select top 2 empno, ename, job from emp

select * from @emp

# Constraint

sys.key_constraints: 这个视图包含了主键约束和唯一键约束的信息。
sys.check_constraints: 这个视图包含了检查约束的信息。
sys.default_constraints: 这个视图包含了默认约束的信息。
sys.foreign_keys: 这个视图包含了外键约束的信息。

-- 查询表的主键约束信息
SELECT kc.name AS constraint_name,
       col.name AS column_name
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE kc.parent_object_id = OBJECT_ID('your_table_name') AND kc.type = 'PK';

-- 查询表的唯一键约束信息
SELECT kc.name AS constraint_name,
       col.name AS column_name
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE kc.parent_object_id = OBJECT_ID('your_table_name') AND kc.type = 'UQ';

-- 查询表的检查约束信息
SELECT cc.name AS constraint_name,
       OBJECT_NAME(parent_object_id) AS table_name,
       cc.definition AS check_definition
FROM sys.check_constraints cc
WHERE cc.parent_object_id = OBJECT_ID('your_table_name');

-- 查询表的默认约束信息
SELECT dc.name AS constraint_name,
       OBJECT_NAME(parent_object_id) AS table_name,
       col.name AS column_name,
       dc.definition AS default_definition
FROM sys.default_constraints dc
JOIN sys.columns col ON dc.parent_object_id = col.object_id AND dc.parent_column_id = col.column_id
WHERE dc.parent_object_id = OBJECT_ID('your_table_name');

-- 查询表的外键约束信息
SELECT fk.name AS constraint_name,
       OBJECT_NAME(fk.parent_object_id) AS parent_table,
       col.name AS parent_column,
       OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
       rcol.name AS referenced_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns col ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
JOIN sys.columns rcol ON fkc.referenced_object_id = rcol.object_id AND fkc.referenced_column_id = rcol.column_id
WHERE fk.parent_object_id = OBJECT_ID('your_table_name');

In SQLServer not null constraint can not find by view and default is a constraint.

create table edi_test(
  a varchar(100) not null,
  b varchar(100) default('-'),
  c varchar(100)
);

SELECT dc.name AS constraint_name,
       OBJECT_NAME(parent_object_id) AS table_name,
       col.name AS column_name,
       dc.definition AS default_definition
FROM sys.default_constraints dc
JOIN sys.columns col ON dc.parent_object_id = col.object_id AND dc.parent_column_id = col.column_id
WHERE dc.parent_object_id = OBJECT_ID('edi_test');

constraint_name	table_name	column_name	default_definition
`DF__edi_test__b__2E1BDC42`	edi_test	b	(’-’)

Drop column need drop default constraint firstly but not null constraint not need.

alter table dbo.edi_test drop column b; --SQL Error [5074] [S1000]: The object 'DF__edi_test__b__2E1BDC42' is dependent on column 'b'.
alter table dbo.edi_test drop constraint DF__edi_test__b__2E1BDC42;
alter table dbo.edi_test drop column b;

alter table dbo.edi_test drop column a;

In Oracle not null constraint can not find by view and default is not a constraint.

create table edi_test(
  a varchar2(100) not null,
  b varchar2(100) default('-'),
  c varchar2(100)
);

select * from user_constraints where table_name = 'EDI_TEST';

OWNER	CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME	SEARCH_CONDITION	R_OWNER	R_CONSTRAINT_NAME	DELETE_RULE	STATUS	DEFERRABLE	DEFERRED	VALIDATED	GENERATED	BAD	RELY	LAST_CHANGE	INDEX_OWNER	INDEX_NAME	INVALID	VIEW_RELATED
APPS	SYS_C001485655	C	EDI_TEST	“A” IS NOT NULL				ENABLED	NOT DEFERRABLE	IMMEDIATE	VALIDATED	GENERATED NAME			2024-03-30 03:52:07 PM

Drop column not need drop not null constraint firstly.

1
2
3

alter table edi_test drop column b;

alter table edi_test drop column a;

# Function & Procedure

# User Defined Procedure

create procedure GetEmpInfo
	@empno int
as
begin
	select *
	  from emp
	 where empno = @empno
end

go

exec GetEmpInfo 7369

# User Defined Function

create function GetHighSalEmp(@sal decimal(7, 2))
returns table
as
return
(
  select *
    from emp
   where sal > @sal
)

go

select * from GetHighSalEmp(3000)

# isnull

isnull fucntion is similar to Oracle nvl function.

1 2	select isnull(null, 'S'); --S select isnull('R', 'S'); --R

# charindex

1	select charindex('home', 'welcome home'); --9

# cast

1	select 'abc' + cast(123 as varchar(10)); --abc123

# getdate

getdate function is similar to Oracle sysdate function.

1	select getdate();

# dateadd

1	select dateadd(day, 2, '2024-06-04'); --2024-06-06 00:00:00.000

# datediff

1	select datediff(day, getdate(), dateadd(day, 2, getdate())); --2

# datepart

1	select datepart(year, getdate()), datepart(week, getdate()); --2024 23

# T-SQL

Example Table

-- Create table similar to scott.emp in SQL Server
CREATE TABLE emp (
    empno INT PRIMARY KEY,
    ename NVARCHAR(10),
    job NVARCHAR(9),
    mgr INT,
    hiredate DATE,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2),
    deptno INT
);

-- Insert sample data into the emp table
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

-- Verify the data is inserted
SELECT * FROM emp;

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1982-12-09	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1983-01-12	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7934	MILLER	CLERK	7782	1982-01-23	1300.00	NULL	10

# Variables

# Set Assignment

declare @num int
set @num = 5
print @num

declare @count int
set @count = (
   select count(*)
     from emp
)
print @count

# Select Assignment

1
2
3

declare @num int
select @num = 5
print @num

declare @avg_sal decimal(7, 2)

select @avg_sal = avg(sal)
from emp
--or
/*
set @avg_sal = (
  select avg(sal)
  from emp
)
*/

select *
from emp
where sal > @avg_sal

empno	ename	job	mgr	hiredate	sal	comm	deptno
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1982-12-09	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10

# Update Assignment

1
2
3

declare @name nvarchar(50)
update emp set @name = ename where empno = 7369
print @name

# Table Variables

declare @emp table (
  empno INT PRIMARY KEY,
  ename NVARCHAR(10),
  job NVARCHAR(9)
)

insert into @emp
select top 2 empno, ename, job from emp

select * from @emp

# Condition Statement

# IF ELSE

declare @price decimal(18,2)

set @price = 60

if @price < 10
	select 'Cheap'
else if @price between 10 and 50
	select 'Affordable'
else
	select 'Expansive'

--Expansive

# WHEN THEN

declare @today int
declare @week varchar(10)

set @today=3

set @week=case
when @today=1 then 'Monday'
when @today=2 then 'Tuesday'
when @today=3 then 'Wednesday'
when @today=4 then 'Thursday'
when @today=5 then 'Fridy'
when @today=6 then 'Saturday'
when @today=7 then 'Sunday'
else 'Error' end

print @week

--Wednesday

# EXISITS

declare @empno int = 7839

if not exists (select * from emp where empno = @empno)
	select 'The emp does not exists'
else if exists (select * from emp where empno = @empno and mgr is null)
	select 'The emp is mgr'
else
	select 'The emp is not mgr'

--The emp is mgr

# While Loop

# Calculate the sum from 1 to 100.

declare @a int
declare @sum int

set @a=1
set @sum=0

while @a<=100
begin
    set @sum+=@a
    set @a+=1
end

print @sum

# Mock Test Data

declare @RowCount int = 0
declare @RandomNumber int

while @RowCount < 10
begin
  set @RandomNumber = floor(rand() * 3)

  select dateadd(day, -floor(rand() * 365), getdate()),
	     'Customer' + cast(floor(rand() * 20) + 1 as varchar(10)) + '@gmail.com',
		 case
			when @RandomNumber = 0 then 'Pending'
			when @RandomNumber = 1 then 'Shipped'
			when @RandomNumber = 2 then 'Canceled'
		 end

  set @RowCount = @RowCount + 1
end

# Cursor

declare @empno int
declare @ename nvarchar(10)
declare @job nvarchar(10)

declare emp_cur cursor for select empno, ename, job from emp

open emp_cur
while @@fetch_status=0
begin
	fetch next from emp_cur into @empno, @ename, @job
	print cast(@empno as varchar(10)) + '-' + @ename + '-' + @job
end

close emp_cur

deallocate emp_cur

--7369-SMITH-CLERK
--7499-ALLEN-SALESMAN
--...

# Trigger

create table #log
(
  msg nvarchar(200)
)
go

create trigger emp_onupdate_trg
on emp
for update
as
declare @msg nvarchar(50)
select @msg = deleted.ename + '->' + inserted.ename from inserted, deleted

insert into #log values(@msg)

go

select * from #log;
update emp set ename = 'SMITHX' where ename = 'SMITH'
select * from #log;
--SMITH->SMITHX

drop trigger emp_onupdate_trg

# Custom Procedure

# With Output Parameter

create procedure CalculateSum
	@a int,
	@b int,
	@sum int output
as
begin
	set @sum=@a+@b
end
go

declare @sum int
exec CalculateSum 1, 2, @sum output
print @sum
--3

# Return

create procedure CalculateSum
	@a int,
	@b int
as
begin
	return @a+@b
end
go

declare @sum int
exec @sum = CalculateSum 1, 2
print @sum
--3

# Custom Function

# Scalar Function

create function CalculateSum(@a int, @b int) returns int
as
begin
	return @a+@b
end
go


declare @sum int
set @sum = dbo.CalculateSum(1, 2)
print @sum
--3

select dbo.CalculateSum(1, 2)
--3

# Table-valued Function

create function GetHighSalEmp(@sal decimal(7, 2))
returns table
as
return
(
  select *
    from emp
   where sal > @sal
)
go

select * from dbo.GetHighSalEmp(3000)

create function GetHighSalEmp(@sal decimal(7, 2))
returns @emp table
(
	empno INT PRIMARY KEY,
    ename NVARCHAR(10),
    job NVARCHAR(9),
    mgr INT,
    hiredate DATE,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2),
    deptno INT
)
as
begin
	insert into @emp select * from emp where sal > @sal
	return
end
go

select * from dbo.GetHighSalEmp(3000)

# SET NOCOUNT

Controls whether a message that shows the number of rows affected by a Transact-SQL statement or stored procedure is returned after the result set. This message is an additional result set.

set nocount off
select * from emp

--Messages
--(12 row(s) affected)

--(1 row(s) affected)

set nocount on
select * from emp
--Messages
Command(s) completed successfully.

# Misc.

# For XML Path

select empno, ename, sal
  from emp
 where sal >= 3000
for xml path ('emp')

<emp>
  <empno>7788</empno>
  <ename>SCOTT</ename>
  <sal>3000.00</sal>
</emp>
<emp>
  <empno>7839</empno>
  <ename>KING</ename>
  <sal>5000.00</sal>
</emp>

1
2
3

select ',' + ename
  from emp
for xml path ('')

1	,SMITH,ALLEN,WARD,JONES,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,MILLER

# For XML Path + STUFF Implements STRING_AGG

STRING_AGG is provided in SQL Server 2017 (14.x) and later

1 2	select string_agg(ename, ',') from emp;

STUFF(character_expression, start, length, replace_with_expression)

select stuff(
	(select ', ' + ename
	  from emp
	for xml path ('')),
	1,
	2,
	'')

1	SMITH, ALLEN, WARD, JONES, BLAKE, CLARK, SCOTT, KING, TURNER, ADAMS, JAMES, MILLER

select distinct
    o.job,
	stuff(
		(select ', ' + i.ename
		  from emp as i
		 where i.job = o.job
		 for xml path ('')),
		1,
		2,
		'') enames
from emp as o

job	enames
ANALYST	SCOTT
CLERK	SMITH, ADAMS, JAMES, MILLER
MANAGER	JONES, BLAKE, CLARK
PRESIDENT	KING
SALESMAN	ALLEN, WARD, TURNER

# Dynamic SQL

1	exec('select * from emp')

# Custom Types

CREATE TYPE dbo.String FROM VARCHAR(255);

DECLARE @StringValue dbo.String = 'some value less than 256 characters';
SELECT  @StringValue;

SELECT * FROM sys.types;

name	system_type_id	user_type_id	schema_id	max_length	precision	scale	collation_name	is_nullable	is_user_defined	is_assembly_type
image	34	34	4	16	0	0		1	0	0
text	35	35	4	16	0	0	SQL_Latin1_General_CP1_CI_AS	1	0	0
uniqueidentifier	36	36	4	16	0	0		1	0	0
date	40	40	4	3	10	0		1	0	0
time	41	41	4	5	16	7		1	0	0
datetime2	42	42	4	8	27	7		1	0	0
datetimeoffset	43	43	4	10	34	7		1	0	0
tinyint	48	48	4	1	3	0		1	0	0
smallint	52	52	4	2	5	0		1	0	0
int	56	56	4	4	10	0		1	0	0
smalldatetime	58	58	4	4	16	0		1	0	0
real	59	59	4	4	24	0		1	0	0
money	60	60	4	8	19	4		1	0	0
datetime	61	61	4	8	23	3		1	0	0
float	62	62	4	8	53	0		1	0	0
sql_variant	98	98	4	8016	0	0		1	0	0
ntext	99	99	4	16	0	0	SQL_Latin1_General_CP1_CI_AS	1	0	0
bit	104	104	4	1	1	0		1	0	0
decimal	106	106	4	17	38	38		1	0	0
numeric	108	108	4	17	38	38		1	0	0
smallmoney	122	122	4	4	10	4		1	0	0
bigint	127	127	4	8	19	0		1	0	0
hierarchyid	240	128	4	892	0	0		1	0	1
geometry	240	129	4	-1	0	0		1	0	1
geography	240	130	4	-1	0	0		1	0	1
varbinary	165	165	4	8000	0	0		1	0	0
`varchar`	`167`	167	4	8000	0	0	SQL_Latin1_General_CP1_CI_AS	1	0	0
binary	173	173	4	8000	0	0		1	0	0
char	175	175	4	8000	0	0	SQL_Latin1_General_CP1_CI_AS	1	0	0
timestamp	189	189	4	8	0	0		0	0	0
nvarchar	231	231	4	8000	0	0	SQL_Latin1_General_CP1_CI_AS	1	0	0
nchar	239	239	4	8000	0	0	SQL_Latin1_General_CP1_CI_AS	1	0	0
xml	241	241	4	-1	0	0		1	0	0
sysname	231	256	4	256	0	0	SQL_Latin1_General_CP1_CI_AS	0	0	0
`String`	`167`	257	1	255	0	0	SQL_Latin1_General_CP1_CI_AS	1	1	0

SELECT  SelectedType.[name] AS SelectedTypeName
        , BaseType.[name] AS BaseTypeName
        , SelectedType.max_length AS SelectedTypeMaxBytes
        , SelectedType.[precision] AS SelectedTypePrecision
        , SelectedType.scale AS SelectedTypeScale
        , SelectedType.default_object_id AS SelectedTypeId
        , BaseType.default_object_id AS BaseTypeId
  FROM  sys.types SelectedType
  JOIN  sys.types BaseType
    ON  BaseType.system_type_id = SelectedType.system_type_id
 WHERE  BaseType.is_user_defined = 0
   AND  SelectedType.is_user_defined = 1
   AND  BaseType.[name] <> 'sysname'
 ORDER BY SelectedType.[name];

SelectedTypeName	BaseTypeName	SelectedTypeMaxBytes	SelectedTypePrecision	SelectedTypeScale	SelectedTypeId	BaseTypeId
String	varchar	255	0	0	0	0

# Database

# Create

# Use

# Table

# Create

# Get Create Table Script

# View Table Details

# Insert

# Select

# Select * Expand

# Top

# Union and Order by

# Join

# Inner Join

# Left Outer Join

# Right Outer Join

# Full Outer Join

# Cross Apply

# Outer Apply

# Update

# Multi Table Join Update

# Alter

# Table Relation

# Many to Many

# Temp Table & Table Variables

# Constraint

# Function & Procedure

# User Defined Procedure

# User Defined Function

# isnull

# charindex

# cast

# getdate

# dateadd

# datediff

# datepart

# T-SQL

# Variables

# Set Assignment

# Select Assignment

# Update Assignment

# Table Variables

# Condition Statement

# IF ELSE

# WHEN THEN

# EXISITS

# While Loop

# Calculate the sum from 1 to 100.

# Mock Test Data

# Cursor

# Trigger

# Custom Procedure

# With Output Parameter

# Return

# Custom Function

# Scalar Function

# Table-valued Function

# SET NOCOUNT

# Misc.

# For XML Path

# For XML Path + STUFF Implements STRING_AGG

# Dynamic SQL

# Custom Types

# Include Execution Plan

Oracle Test Data Generate

Java 11 HttpClient Examples