All Tools Combinations applied in this post for Data Science to Explain in a way to reach more audience
news
code
analysis
Author
Sumendar
Published
March 10, 2023
USE salesdb;
Commands completed successfully.
Total execution time: 00:00:00.025
SELECT TOP 15*FROM Customers;
(15 rows affected)
Total execution time: 00:00:00.127
CustomerID
FirstName
MiddleInitial
LastName
1
Aaron
NULL
Alexander
2
Aaron
NULL
Bryant
3
Aaron
NULL
Butler
4
Aaron
NULL
Chen
5
Aaron
NULL
Coleman
6
Aaron
NULL
Con
7
Aaron
NULL
Edwards
8
Aaron
NULL
Flores
9
Aaron
NULL
Foster
10
Aaron
NULL
Gonzales
11
Aaron
NULL
Griffin
12
Aaron
NULL
Henderson
13
Aaron
NULL
Hernandez
14
Aaron
NULL
Hill
15
Aaron
NULL
Jai
--https://docs.microsoft.com/en-us/sql/azure-data-studio/notebooks/notebooks-sql-kernel--SELECT distinct Title FROM Person.Person--SELECT distinct Type, Category FROM Sales.SpecialOffer
(7 rows affected)
(8 rows affected)
Total execution time: 00:00:01.895
Title
Sr.
Mrs.
NULL
Sra.
Ms.
Ms
Mr.
Type
Category
Discontinued Product
Reseller
Excess Inventory
Customer
Excess Inventory
Reseller
New Product
Reseller
No Discount
No Discount
Seasonal Discount
Customer
Seasonal Discount
Reseller
Volume Discount
Reseller
SELECTdistinctsize, color from Production.Product
(68 rows affected)
Total execution time: 00:00:00.021
size
color
NULL
NULL
NULL
Black
NULL
Blue
NULL
Grey
NULL
Multi
NULL
Red
NULL
Silver
NULL
Silver/Black
38
Black
38
Silver
38
Yellow
40
Black
40
Silver
40
Yellow
42
Black
42
Silver
42
Yellow
44
Black
44
Blue
44
Red
44
Silver
44
Yellow
46
Black
46
Blue
46
Silver
46
Yellow
48
Black
48
Red
48
Silver
48
Yellow
50
Blue
50
Yellow
52
Black
52
Red
52
Silver
54
Blue
54
Yellow
56
Red
58
Black
58
Blue
58
Red
58
Yellow
60
Black
60
Blue
60
Red
60
Yellow
62
Black
62
Blue
62
Red
62
Yellow
70
Silver
L
Black
L
Blue
L
Multi
L
White
L
Yellow
M
Black
M
Blue
M
Multi
M
White
M
Yellow
S
Black
S
Blue
S
Multi
S
Yellow
XL
Black
XL
Multi
XL
Yellow
SQL Server Coding (Direct copied from Azure Data Studion After executing output got there then pasted here)
Data Types
Imgur
Date Datatype
Declare @myDate date='02-02-2016'Select @myDate as MyDate
(1 row affected)
Total execution time: 00:00:00.011
MyDate
2016-02-02
Declare @myDateTime datetime ='02-02-2016'Select @myDateTime as MyDateTime
(1 row affected)
Total execution time: 00:00:00.006
MyDateTime
2016-02-02 00:00:00.000
Declare @myTime time='16:25:42.1214'Select @myTime as MyTime
(1 row affected)
Total execution time: 00:00:00.039
MyTime
16:25:42.1214000
Declare @myTime time(2) ='16:25:42.1214'Select @myTime as MyTime
(1 row affected)
Total execution time: 00:00:00.018
MyTime
16:25:42.1200000
Charecter Datatype
-- CharacterDECLARE @myChar CHAR(20) ='PDI'SELECT @myChar ASChar, DATALENGTH (@myChar) as Datalength, LEN(@myChar) AS Len
(1 row affected)
Total execution time: 00:00:00.040
Char
Datalength
Len
Edvancer
20
8
-- VarcharDECLARE @myChar VARCHAR(20) ='PDI Lmtd'SELECT @myChar ASVarchar, DATALENGTH (@myChar) as Datalength, LEN(@myChar) AS Len
(1 row affected)
Total execution time: 00:00:00.014
Varchar
Datalength
Len
Edvancer
8
8
Numeric Datatype
DECLARE @num NUMERIC(7,2) =12376.7854SELECT @num as NumericData
(1 row affected)
Total execution time: 00:00:00.024
NumericData
12376.79
DECLARE @SmallInt SMALLINT=12376.7854SELECT @SmallInt AS SmallIntData
(1 row affected)
Total execution time: 00:00:00.023
SmallIntData
12376
-- DECLARE @SmallInt SMALLINT = -32769 -- SELECT @SmallInt AS SmallIntData-----------------Errors when we un comment above and run codes-- if we give more/less than --affordable range it will gives an error--Msg 220, Level 16, State 1, Line 1--Arithmetic overflow error for data type smallint, value = -32769.
Commands completed successfully.
Total execution time: 00:00:00.001
DECLARE @TinyInt SMALLINT=254SELECT @TinyInt AS TinyIntData
--Data type precedenceDECLARE @float1 FLOAT(10), @int INTSET @float1 =12376.7854SET @int =570SELECT @float1 + @int as Result
(1 row affected)
Total execution time: 00:00:00.007
Result
12946.785
Filter the data using where
USE AdventureWorks2014;SELECT TOP 10*FROM AdventureWorks2014.HumanResources.Employee WHERE SickLeaveHours >50;
(10 rows affected)
Total execution time: 00:00:00.195
BusinessEntityID
NationalIDNumber
LoginID
OrganizationNode
OrganizationLevel
JobTitle
BirthDate
MaritalStatus
Gender
HireDate
SalariedFlag
VacationHours
SickLeaveHours
CurrentFlag
rowguid
ModifiedDate
1
295847284
adventure-works\ken0
NULL
NULL
Chief Executive Officer
1969-01-29
S
M
2009-01-14
1
99
69
1
f01251e5-96a3-448d-981e-0f99d789110d
2014-06-30 00:00:00.000
4
112457891
adventure-works\rob0
0x5AD6
3
Senior Tool Designer
1974-12-23
S
M
2007-12-05
0
48
80
1
59747955-87b8-443f-8ed4-f8ad3afdf3a9
2014-06-30 00:00:00.000
8
811994146
adventure-works\diane1
0x5AE158
4
Research and Development Engineer
1986-06-05
S
F
2008-12-29
1
62
51
1
31112635-663b-4018-b4a2-a685c0bf48a4
2014-06-30 00:00:00.000
9
658797903
adventure-works\gigi0
0x5AE168
4
Research and Development Engineer
1979-01-21
M
F
2009-01-16
1
63
51
1
50b6cdc6-7570-47ef-9570-48a64b5f2ecf
2014-06-30 00:00:00.000
10
879342154
adventure-works\michael6
0x5AE178
4
Research and Development Manager
1984-11-30
M
M
2009-05-03
1
16
64
1
eaa43680-5571-40cb-ab1a-3bf68f04459e
2014-06-30 00:00:00.000
25
519899904
adventure-works\james1
0x78
1
Vice President of Production
1983-01-07
S
M
2009-02-03
1
64
52
1
d7314f24-2af1-429c-9bbb-4038f45f3e6c
2014-06-30 00:00:00.000
27
446466105
adventure-works\jo0
0x7AD6
3
Production Supervisor - WC60
1956-10-08
S
F
2008-02-27
0
80
60
1
83ffaac6-b895-481f-b897-14f965d4da47
2014-06-30 00:00:00.000
40
309738752
adventure-works\jolynn0
0x7ADA
3
Production Supervisor - WC60
1956-01-16
S
F
2007-12-26
0
82
61
1
2cc71b96-f421-485e-9832-8723337749bb
2014-06-30 00:00:00.000
47
33237992
adventure-works\andrew0
0x7ADE
3
Production Supervisor - WC10
1988-09-06
S
M
2009-02-22
0
65
52
1
08365b05-c143-4260-a93c-6b69418b1946
2014-06-30 00:00:00.000
48
690627818
adventure-works\ruth0
0x7ADEB0
4
Production Technician - WC10
1956-06-04
M
F
2008-01-06
0
83
61
1
3e3b6905-209e-442e-b8a8-9a0980241c6a
2014-06-30 00:00:00.000
SELECT TOP 10*FROM Sales.CreditCard WHERE ExpYear =2008;
(10 rows affected)
Total execution time: 00:00:00.021
CreditCardID
CardType
CardNumber
ExpMonth
ExpYear
ModifiedDate
10
SuperiorCard
33332126386493
8
2008
2011-08-31 00:00:00.000
11
SuperiorCard
33335352517363
10
2008
2014-05-04 00:00:00.000
12
SuperiorCard
33334316194519
4
2008
2012-05-30 00:00:00.000
15
SuperiorCard
33336866065599
11
2008
2013-01-29 00:00:00.000
17
ColonialVoice
77771220960729
8
2008
2014-01-16 00:00:00.000
26
Distinguish
55554195847998
11
2008
2013-08-27 00:00:00.000
27
SuperiorCard
33333837392825
12
2008
2013-11-11 00:00:00.000
31
Vista
11112040199986
10
2008
2009-01-15 00:00:00.000
38
Distinguish
55555420711099
7
2008
2013-09-24 00:00:00.000
40
Distinguish
55555841603354
4
2008
2013-08-25 00:00:00.000
USE Training
Commands completed successfully.
Total execution time: 00:00:00.001
-- selecting products tableSELECT*FROM products;
(10 rows affected)
Total execution time: 00:00:00.010
ProductID
ProductCode
ProductDescription
color
1
AR-5381
Adjustable Race
Black
2
BA-8327
Bearing Ball
Silver
3
BE-2349
BB Ball Bearing
Grey
4
BE-2908
Headset Ball Bearings
Black
5
BL-2036
Blade
Grey
6
CA-5965
LL Crankarm
Grey
7
CA-6738
ML Crankarm
Silver
8
CA-7457
HL Crankarm
Silver
9
CB-2903
Chainring Bolts
Silver
10
CN-6137
Chainring Nut
Grey
-- selecting city tableSELECT*FROM city;
(12 rows affected)
Total execution time: 00:00:00.009
CityID
CityCode
CityName
PopulationDensity
sampleColumn
1
ADI
Ahmedabad
High
1
2
BLR
Bangalore
High
1
3
CHD
Chandigarh
Low
1
4
CHN
Chennai
High
1
5
DDN
Dehradun
Medium
1
6
DEL
Delhi
High
1
7
JPR
Jaipur
Medium
1
8
KOL
Kolkata
High
1
9
MUM
Mumbai
High
1
10
MYS
Mysore
Low
1
11
PDY
Puducherry
Low
1
12
RNC
Ranchi
Medium
1
SELECT*from City;
(12 rows affected)
Total execution time: 00:00:00.032
CityID
CityCode
CityName
PopulationDensity
sampleColumn
1
ADI
Ahmedabad
High
1
2
BLR
Bangalore
High
1
3
CHD
Chandigarh
Low
1
4
CHN
Chennai
High
1
5
DDN
Dehradun
Medium
1
6
DEL
Delhi
High
1
7
JPR
Jaipur
Medium
1
8
KOL
Kolkata
High
1
9
MUM
Mumbai
High
1
10
MYS
Mysore
Low
1
11
PDY
Puducherry
Low
1
12
RNC
Ranchi
Medium
1
Use Training;
Commands completed successfully.
Total execution time: 00:00:00.007
SELECT*from City where populationDensity ='High';
(6 rows affected)
Total execution time: 00:00:00.050
CityID
CityCode
CityName
PopulationDensity
sampleColumn
1
ADI
Ahmedabad
High
1
2
BLR
Bangalore
High
1
4
CHN
Chennai
High
1
6
DEL
Delhi
High
1
8
KOL
Kolkata
High
1
9
MUM
Mumbai
High
1
SELECT*FROM City WHERE PopulationDensity ='High'
(6 rows affected)
Total execution time: 00:00:00.007
CityID
CityCode
CityName
PopulationDensity
sampleColumn
1
ADI
Ahmedabad
High
1
2
BLR
Bangalore
High
1
4
CHN
Chennai
High
1
6
DEL
Delhi
High
1
8
KOL
Kolkata
High
1
9
MUM
Mumbai
High
1
SELECTMIN([DATE_KEY]) AS [MIN_DATE],MAX([DATE_KEY]) AS [MAX_DATE], DATEDIFF(YEAR, MIN([DATE_KEY]), MAX([DATE_KEY])) [SPAN_IN_YEARS]FROM [EDW].[F_COLLISIONS]
: Msg 208, Level 16, State 1, Line 1
Invalid object name 'EDW.F_COLLISIONS'.
Total execution time: 00:00:00.032
DECLARE @myDate date='02-02-2016';SELECT @myDate as MyDate;
(1 row affected)
Total execution time: 00:00:00.024
MyDate
2016-02-02
DECLARE @myDate datetime ='02-02-2016';SELECT @myDate as MyDate;