Long-Term Learning
Learn efficiently and remember over time.
Start Long-Term Learning
Add this set to a folder
data type ???
an attribute that specifies the type of data an object can hold, as well as how many bytes each data type takes up.
Microsoft SQL Server 2008's built-in data types are organized into the following general categories:
• Exact numbers
• Approximate numbers
• Date and time
• Character strings
• Unicode character strings
• Binary strings
• Other data types
• CLR data types
• Spatial data types
Money ???
This numeric data type is used in places where you want money or currency involved in your database;
you should use the datetime data type if you will be storing values ___
between the dates of January 1, 1753, and December 31, 9999, that are accurate to 3.33 milliseconds,
datetime2 is the data type to use if you will be storing values ___
between January 1, 1900, and June 6, 2079, that are accurate to only 1 minute.
The integer (int) numeric data type is used to ___
store mathematical computations and is employed when you do not require a decimal point output.
Varchar ???
This character-string data type is commonly used in databases where you are supporting English attributes.
Boolean ???
This data type converts true and false string values to bit values, with true converted to 1 and false converted to 0.
The float numeric data type is commonly used in the ___
scientific community and considered an approximate-number data type. This means that not all values within the data-type range will be represented exactly. In addition, depending on which type of float is used, a 4-byte float supports precision up to 7 digits and an 8-byte float supports precision up to 15 digits.
int ??
is the primary integer (whole number) data type.
Integer data from -2^31(-2,147,483,648) to 2^31-1 (2,147,483,647).
bigint ???
Integer data from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 8 bytes (9,223,372,036,854,775,807).
numeric & decimal ???
Transact-SQL data types that have a fixed precision and scale. Valid values range from -10^38+1 through 10^38-1.
smalldatetime ???
Defines a date that is combined
with a time of day. The time is based on a 24-hour day, with seconds
always zero (:00), meaning there are no fractional
seconds. Range: 1900-01-01 through 2079-06-06 Accuracy: one minute.
time* ???
Defines a time of day. This time is without time-zone awareness and is based on a 24-hour clock. Range: 00:00:00.0000000 through 23:59:59.9999999. Accuracy: 100 nanoseconds.
datetimeoffset* ???
Defines a date that is combined with a time of day that has time-zone awareness and is based on a 24-hour clock. Range: 0001-01-01 through 9999-12-31 ... 00:00:00 through 23:59:59.9999999. Accuracy: 100 nanoseconds.
datetime2* ???
Defines a date that is combined with a time of day that is based on a 24-hour clock. Range: 0001-01-01 through 9999-12-31. Range: 00:00:00 through 23:59:59.9999999. Accuracy: 100 nanoseconds.
text ???
This data type will be removed in future SQL releases; therefore, use varchar(max) instead.
ntext ??
This data type will be removed in future SQL releases; therefore, use nvarchar(max) instead.
image ???
This data type will be removed in future SQL releases; therefore, use varbinary(max) instead.
sql_variant ???
Stores values of various SQL Server-supported data types, except text, ntext, image, timestamp, and sql_variant.
When two expressions (mathematical functions or comparison functions) have different data types, rules for data-type precedence specify that the ___
data type with lower precedence
is converted to the data type with higher precedence.
TRUE and FALSE string values can be converted ___
to bit values. Specifically, TRUE is converted to 1 and FALSE is converted to 0.
Precision (p) ???
is the maximum total number of decimal digits that can be stored, both to the left and the right of the decimal point. This value must be a minimum of 1 and a maximum of 38. The default precision number is 18.
Scale(s) ???
Reflects the maximum number of decimal digits that can be stored to the right of the decimal point. This must be a value from 0 through p, but it can be specified only if precision is also specified. The default scale is 0.
Money and smallmoney are ___
Transact-SQL data types you would use to represent monetary or currency values. Both data types are accurate to 1/10,000th of the monetary units they represent.
The date and time data types include ___
date, datetime, datetime2, datetimeoffset, smalldatetime, and time.
When a conversion (convert values from one data type to another) is done automatically it is called an ___
implicit conversion.
if you multiply an item's cost (represented as a float) with the number of items (represented as an integer), the answer will be ___
expressed as a float.
For example, although a DateTime value is represented as a float, ___
you may not implicitly convert to a float because it is meant to be a date and/or time.
Cast is compliance with ___
ANSI standards, which allow you to import or export to other database management systems.
CONVERT(nvarchar(10), OrderDate, 101) ???
This will convert the OrderDate, which is a DateTime data type to nvarchar value. The 101 style represents USA date with century. mm/dd/yyyy.
A regular character uses ___
one byte of storage for each character, which allows you to define one of 256 (8 bits are in a byte, and 2^8 = 256) possible characters, accommodating English and some European languages.
A Unicode character uses ___
two bytes of storage per character so that you can represent one of 65,536 (16 bits are in 2 bytes, and 2^16 = 65,536) characters. The additional space allows Unicode to store characters from just about any language, including Chinese, Japanese, Arabic, and so on.
A regular character literal is always expressed with ___
single quotes. For example: 'This is how a regular character string literal looks'
when you are expressing a Unicode character literal, it must have the letter ___
N (for National) prefixing the single quote. For example: N'This is how a Unicode character string literal looks'
When you use a VAR element ___
SQL Server will preserve space in the row in which that element resides based on the column's defined size (and not on the actual number of characters in the character string itself ), plus an extra two bytes of data for offset data.
MAX specifier ?
A value with a size identified up to a certain threshold (the default is 8,000) is stored inline in the row.
LOB ?
A value with a size greater than the default threshold, that value will be stored external to the row and identified as a large object.
data set char is identified as ___
char [(n)] and is a fixed-length, non-Unicode character (regular character) with a length of n bytes. The value of n must be between 1 and 8,000, making the storage size n bytes.
Non-Unicode data type varchar[(n|max)] is a ___
variable-length data set that can consist of 1 to 8,000 characters.
You should also avoid using spaces because they add complications that make it necessary for you to use ___
quotes
Set the Default Value of the DateCreated column to (getdate()); this will insert the ___
current date within each new record for that specific field.
how can you create tables using ANSI SQL syntax?
you will use the create table statement to accomplish this task.
INSERT INTO planets (name, diameter) VALUES ('earth', 10000);
if SQL Server didn't support implicit conversion, the following syntax would be needed:
INSERT INTO planets (name, diameter) VALUES ('earth', CAST (10000 as varchar(50)))
A view is ___
a virtual table consisting of different columns from one or more tables;
( or )
an object that obtains its data from one or more tables.
Complex queries can be stored in the form of a view and data from the view can then be mined ___
using simple query statements.
Views ensure the security of data by restricting access to the following data:
• Specific rows of tables
• Specific columns of tables
• Specific rows and columns of tables
• Rows obtained by using joins
• Statistical summaries of data in given tables
• Subsets of another view or subsets of views and tables
Some common examples of views include the following:
• A subset of rows or columns of a base table
• A union of two or more tables
• A join of two or more tables
• A statistical summary of base tables
• A subset of another view or some combination of views and base tables
Database views are designed to create a virtual table that is representative of ___
one or more tables in an alternative way.
Reasons to provide a view instead of enabling users to access the underlying tables in your database:
• Views allow you to limit the
type of data users can access. You can grant view permissions in
designated tables, and you can also choose to deny permissions for
certain information.
• Views reduce complexity for end users so they don't have to learn how
to write complex SQL queries. Instead, you can write those queries on
their behalf and hide them in a view.
indexing plays a role in query time and an even greater role in ___
database performance improvements.
Adding indexes to the schema can actually increase the overhead of your database due to the ___
ongoing maintenance of these indexes.
Add Table dialog box ???
This dialog box allows you to do:
• specify the table to be used as the primary source...
• To use another existing view, click the Views tab...
• generate records from a function (Functions tab).
• If you want to use more than one source, you can click each of the
different tabs to find the table, view, or function you wish to add to
your query.
• Once you have selected the desired source(s) (click the Add button)
• Once you have selected and added all your desired sources (Close button)
CREATE VIEW vwCustomer
AS
SELECT CustomerId, Company Name, Phone
FROM Customers
This creates a view called vwCustomer that will be stored as an object. Here, the data that is queried from the columns comes from the Customers table.
A stored procedure is a ___
previously written SQL statement that has been "stored" or saved into a database.
One way to save time when running the same query over and over again is to create a ___
stored procedure that you can then execute from within the database's command environment.
SELECT * FROM USERLIST
What this select statement does is return all the data that is found in the USERLIST table.
Using a stored procedure ___
allows you to store all the logic inside the database, so by using a simple command, you can query and retrieve all information from all sources.
If you are creating complex query statements, your best bet is to ___
create a stored procedure for them and run that stored procedure from within the Query Analyzer using an execute (exec) command.
Steps to create a stored procedure (SSMS) :
Programmability section >>> Right-click Stored Procedures and choose New Stored Procedure
SQL injection is an ___
attack in which malicious code is inserted into strings that are later passed on to instances of SQL Server waiting for parsing and execution.
The primary form of SQL injection is a direct insertion of code into ___
user-input variables that are concatenated with SQL commands and then executed.
A less direct method of attack injects malicious code into ___
strings that are destined for storage in a table or are considered metadata. When these stored strings are subsequently concatenated into the dynamic SQL command, the malicious code will be executed.
The injection process's function is to ___
terminate a text string prematurely and append a new command directed from it; because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark "—", making subsequent text ignored at execution time.