Session 1 - data types and tables Flashcards
CURRENT_TIMESTAMP
Returns DateTime datatype - LEGACY
GETDATE()
Returns DateTime
SYSDATETIME()
Returns datetime2
How to get name of day, month from a date
DATENAME(interval, DateTime)
ADDS AN INCREMENT TO A DATE (DAY, MONTH, YEAR)
DATEADD(INTERVAL, INCREMENT, DATETIME)
EXTRACTS THE INTERVAL FROM THE GIVEN DATE
DATEPART(INTERVAL, DATETIME)
SHOWS THE DIFFERENCE IN INTERVAL BETWEEN TWO DATES
DATEDIFF(INTERVAL, BEGIN DATE, END DATE)
CONVERT DATE TO STRING using convert
CONVERT(NVARCHAR(20), DATE, 101 or other number for dates)
Convert date time using parse
PARSE(‘DATE STRING’ AS DATE [USING ‘es-ES’]for other languages)
Convert date time to string using cast
FORMAT(CAST(DATE AS DATETIME), ‘dd-MM-yyyy’, ‘language’)
If you’re filtering on summarized data, use _____ otherwise use where
Having (vs where)
Default number of rows you can insert is _____
1000 rows (pertaining to defaults)
Database Diagrams can create _____ _____ between tables
foreign keys (pertaining to database diagrams)
Query order of operations
- FROM, including JOINS
- WHERE
- GROUP BY
- HAVING
- WINDOW functions
- SELECT
- DISTINCT
- UNION
- ORDER BY
- LIMIT and OFFSET
List all types of joins
INNER - RECORDS EXIST IN BOTH
LEFT (outer) - ALL RECORDS IN FIRST
RIGHT (outer) -ALL RECORDS IN LAST
FULL (outer) - ALL RECORDS FROM BOTH
Cross - A join that puts all rows in table A against all rows in table B (IE if table A has 3 rows 1,2,3 and table B has 3 rows A,B,C the result set would be 1,A, 1,B, 1,C, 2,A, 2,B, 2,C, etc.)
4 standard databases
Master
Tempdb
Model
Msdb
What is masterdb
Primary configuration database
Contains information on all the databases that exist on the server
physical database files and their locations.
SQL Server’s configuration settings
login account information.
What is tempdb
used as a store of:
internal objects, row versions, work tables, temporary tables, indexes.
Available for use to all participants connected to a SQL Server instance (it is a global resource)
What is model database
Template for all databases created on an instance of SQL Server.
Model is required because of TempDB.
Newly created user databases use the same recovery model as the model db
What is msdb
Used by SQL Server Agent for:
scheduling alerts
jobs
and by other features such as:
SQL Server Management Studio,
Service Broker
Database Mail.
backup-and-restore history
Convert Syntax
Convert(dataTypeToConvertTo, variableToConvert)
Cast Syntax
Cast(@myvar as DataType)
What is type used when working with SIN, COS, TAN, etc
Radians
What are the three functions for rounding
FLOOR
CEILING
ROUND
What does the function FLOOR do
Rounds down to the nearest 1 (15.6 - 15)
What does the function CEILING do?
Rounds up to the nearest 1 (15.3 - 16)
What does the Round function do?
What’s its syntax?
Rounds to the nearest place you specify
ROUND(1543.3435, 2) - 1543.3400
How do you get value of PI?
select PI() as PI
How do you get value of E
select EXP(1) as E
How do you use exponents?
POWER(@myvar, powerToRaiseTo)
How do you get a number squared?
SQUARE(@myvar)
How do you get a square root of a number?
SQRT(@myvar)
POWER(@myvar, .5)
What’s the syntax for converting but giving null if it doesn’t work?
TRY_CONVERT(tinyint, @myvar) OR TRY_CAST(@myvar as tinyint)
What is difference between varchar and nvarchar (char and nchar as well)
varchar uses roman alphabet (ASCII) and nvarchar uses Unicode (same with char/nchar)
What is a derived table?
A result set (from select statement) that you then use as a FROM to generate a new query. It’s almost like using a WITH command
TinyInt Range and Storage
0–255
1 byte
Smallint range and storage
–2^15 (–32,768) to 2^15–1 (32,767)
~–32,700 – ~32,700
2 Bytes
Int Range and Storage
–2^31 (–2,147,483,648) to 2^31–1 (2,147,483,647)
~–2 trillion – ~2 trillion
4 Bytes
Bigint Range and Storage
–2^63 (–9,223,372,036,854,775,808) to 2^63–1 (9,223,372,036,854,775,807)
~–9 quintillion – 9 quintillion
8 Bytes
How much space is required for char(10)
10 bytes (It’s whatever the number is in the parentheses. It requires 1 byte for every character it holds)
How much space is required for varchar(10)
Varchar is variable. It will require 1 character per character USED + 2. So ‘test’ would require 6 bytes, and ‘a’ would require 3 bytes.
How much space is required for nchar(10)
- NChar requires 2 bytes per character specified in the parentheses.
How much space is required for nvarchar(10)
It is however long the string that is being held * 2 + 2. So ‘test’ would be 10 bytes (4 length * 2 + 2) and ‘a’ would be 4 bytes (1 length * 2 + 2).
datetime Storage Size, range, accuracy Format
YYYY–MM–DD hh:mm:ss[.nnn]
1753–01–01 through 9999–12–31
.00333 second
8 bytes
date Storage Size, range, accuracy Format
YYYY–MM–DD
0001–01–01 through 9999–12–31
1 day
3 bytes
datetime2 Storage Size, range, accuracy Format
YYYY–MM–DD hh:mm:ss[.nnnnnnn]
0001–01–01 00:00:00.0000000 through 9999–12–31 23:59:59.9999999
100 nanoseconds
6 to 8
smalldatetime Storage Size, range, accuracy format
YYYY–MM–DD hh:mm:ss
1900–01–01 through 2079–06–06
1 minute
4 bytes
time Storage Size, range, accuracy format
hh:mm:ss[.nnnnnnn]
00:00:00.0000000 through 23:59:59.9999999
100 nanoseconds
3 to 5 bytes
Datetimeoffset Storage Size, format and range
YYYY–MM–DD hh:mm:ss[.nnnnnnn] [+|–]hh:mm
0001–01–01 00:00:00.0000000 through 9999–12–31 23:59:59.9999999 (in UTC)
100 nanoseconds
8 to 10
bit data type Storage and values
8 or fewer columns – 1 byte
9 – 16 columns – 2 bytes etc.
values: 1, 0 and null
numeric and decimal data type storage, values and syntax
–10^38 – 10^38 Precision: 1–10 – 5 bytes 11–20 – 9 bytes 21–29 – 13 bytes 30–38 – 17 bytes
syntax: decimal(p[,s])
p – precision is the total number of digits to be stored (including left and right of decimal)
s – scale (numbers to right of decimal)
Numeric and decimal are synonyms and can be used interchangibly
Money data type storage and range
–922,337,203,685,477.5808 to 922,337,203,685,477.5807
~–922 trillion – ~922 trillion
8 bytes
smallmoney data type storage and range
– 214,748.3648 to 214,748.3647
– ~214 thousand to ~214 thousand
4 bytes
Exact numeric Datatypes
bigint int smallint tinyint numeric decimal bit smallmoney money
Approximate numeric data types
Float
Real
Date and time datatypes
date datetimeoffset datetime2 smalldatetime datetime time
Character strings data types
varchar
char
text
Unicode strings data types
nvarchar
nchar
ntext
binary strings data types
binary
varbinary
image
Other data types
Cursor Rowversion Hierarchyid Uniqueidentifier Sql_variant Xml Spatial geometry types Spatial geography types Table
What is difference between varchar and nvarchar (char and nchar as well)
varchar uses roman alphabet (ASCII) and nvarchar uses Unicode (same with char/nchar)
