CREATE TABLE [SQLInterview].[dbo].
[NumericDatatypes]
(
[mybit] [bit] NULL,
[mytinyint] [tinyint] NULL,
[mysmallint] [smallint] NULL,
[myint] [int] NULL,
[mybigint] [bigint] NULL,
[mynumeric] [numeric](18, 0) NULL,
[mydecimal] [decimal](18, 2) NULL,
[mysmallmoney] [smallmoney] NULL,
[mymoney] [money] NULL,
[myfloat] [float] NULL,
[myreal] [real] NULL
)
Go
CREATE TABLE [SQLInterview].[dbo].[CharandStringDatatypes]
(
[mychar] [char](10) NULL,
[myvarchar] [varchar](10) NULL,
[mynchar] [nchar](10) NULL,
[mynvarchar] [nvarchar](10) NULL
)
Go
CREATE TABLE [SQLInterview].[dbo].[DatetimeDatatypes]
(
[mydate] [date] NULL,
[mysmalldatetime] [smalldatetime] NULL,
[mydatetime] [datetime] NULL,
[mydatetime2] [datetime2](7) NULL,
[mydatetimeoffset] [datetimeoffset](7) NULL,
[mytime] [time](7) NULL
)
Go
CREATE TABLE [SQLInterview].[dbo].[BinaryDatatypes]
(
[mybinary] [binary](50) NULL,
[myvarbinary] [varbinary](50) NULL
)
Go
CREATE TABLE [SQLInterview].[dbo].[OtherDatatypes]
(
[mysqlvariant] [sql_variant] NULL,
[myuniqueidentifier] [uniqueidentifier] NULL,
[mytimestamp] [timestamp] Null
)
Go
CREATE TABLE [SQLInterview].[dbo].[Employee](
[Empid] [int] NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Title] [varchar](100) NULL,
[Managerid] [int] NULL
)
Go
CREATE TABLE [dbo].[Xmldatatype]
(
[myxml] [xml] NULL
)
Go
Insert into [SQLInterview].[dbo].[NumericDatatypes]
Values(1,255,32767,2147483647,922337203685477807,99.99,99.99,$99.99,$99.99,99.99,99.99
)
Go
Insert into [SQLInterview].[dbo].[CharandStringDatatypes]
Values('abcd','abcd','abcd','abcd')
Go
Insert into [SQLInterview].[dbo].[DatetimeDatatypes]
Values
(
'2020-07-31',
'2020-07-31 00:00:00',
'2020-07-31 00:00:00.000',
'2020-07-31 00:00:00.000',
'07-31-20 19:00:00 +05:30',
'11:00:00'
)
Go
Insert into [SQLInterview].[dbo].[BinaryDatatypes]
Values(0x44,0x4F6E6C696E65436F75727365)
Go
--sql variant, GUID, timestamp data types
Insert into [SQLInterview].[dbo].[OtherDatatypes]([mysqlvariant],[myuniqueidentifier])
Values(10,NEWID())
Go
Insert into [SQLInterview].[dbo].[OtherDatatypes]([mysqlvariant],[myuniqueidentifier])
Values('a',NEWID())
Go
Insert into [SQLInterview].[dbo].[OtherDatatypes]([mysqlvariant],[myuniqueidentifier])
Select mydatetime,NEWID() from [SQLInterview].[dbo].[DatetimeDatatypes]
Go
--Insert into [SQLInterview].[dbo].[Employee] Values(1,'Kevin','Brown','CEO',0)
--Insert into [SQLInterview].[dbo].[Employee] Values(2,'John','Wood','Vice President
Marketing',1)
--Insert into [SQLInterview].[dbo].[Employee] Values(3,'Rebecca','Williams','Vice
President Finance',1)
--Insert into [SQLInterview].[dbo].[Employee] Values(4,'Chris','Wallace','Manager
Marketing',2)
--Insert into [SQLInterview].[dbo].[Employee] Values(5,'Denise','Smith','Manager
Finance',3)
--Insert into [SQLInterview].[dbo].[Employee] Values(6,'Jane','Smith','Supervisor',4)
--Insert into [SQLInterview].[dbo].[Employee] Values(7,'Lee','White','Clerk',5)
--Insert into [SQLInterview].[dbo].[Employee] Values(8,'A','B',NULL,NULL)
Insert into [SQLInterview].[dbo].[Employee]
Values(888,'Kevin','Brown','President',null)
Insert into [SQLInterview].[dbo].[Employee] Values(889,'Peter','Haynes','GM',888)
Insert into [SQLInterview].[dbo].[Employee]
Values(890,'Ben','Williams','Supervisor',889)
Insert into [SQLInterview].[dbo].[Employee] Values(891,'John','Smith','Clerk',890)
Go
Insert into Xmldatatype Select Firstname from [SQLInterview].[dbo].[Employee]
Go
