MTA Certification Review

self-reference

A foreign key can reference a primary key in its own table.

rows

A horizontal line of cells in a table.

Data type: Integer

A numeric data type used for fields that contain whole numbers The Integer data type requires more memory than the Double data type.

bit (Transact-SQL)

A contraction of "Binary Digit". A bit is the single unit of information in a computer, typically represented as a 0 or 1.

{ } (braces)

Required syntax items. Don't type the braces.

Data type: double

(double-precision floating-point) Storage size: 8 bytes Range: -1.79769313486231E308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232E308 for positive values

underline

Indicates the default value applied when the clause that contains the underlined value is omitted from the statement.

Byte

Storage size: 1 byte Range: 0 to 255

int

Storage: 4 bytes Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

UPPERCASE

Transact-SQL keywords need to be typed in UPPERCASE.

DBMS (Database Management System)

a program used to create, process, and administer a database

The following example uses the -- commenting characters.

-- Choose the AdventureWorks2012 database. USE AdventureWorks2012; GO -- Choose all columns and all rows from the Address table. SELECT * FROM Person.Address ORDER BY PostalCode ASC; -- We do not have to specify ASC because -- that is the default. GO

Which pair of character strings encode a block comment in a SQL server stored procedure?

/* and */

relational database

A database that represents data as a collection of tables in which all data relationships are represented by common values in related tables

Which character at the start of an object's name indicates that it is a SQL Server local variable?

@

variables

A Transact-SQL local variable is an object that can hold a single data value of a specific type. typically looks like this @p1 Variables in batches and scripts are typically used: As a counter either to count the number of times a loop is performed or to control how many times the loop is performed. To hold a data value to be tested by a control-of-flow statement. To save a data value to be returned by a stored procedure return code or function return value.

Boolean

A single value of either TRUE or FALSE Storage size: 2 bytes Range: True or False

Pascal Casing

A style of creating identifiers in which the first letter of all new words in a variable name, even the first one, is capitalized.

Global Variable

A variable that can be used in any part of the program. @@

What is a view?

A virtual table based on a query. A view is a filtered list of records displaying only those records a user may need to see. Users and administrators may create new views for different scenarios. The result set for a view is determined by its SELECT statement. A view obtains its data from one or more tables.

Line spacing

Always include newlines/vertical space: before AND or OR after semicolons to separate queries for easier reading after each keyword definition after a comma when separating multiple columns into logical groups to separate code into related sections, which helps to ease the readability of large chunks of code. Keeping all the keywords aligned to the righthand side and the values left aligned creates a uniform gap down the middle of query. It makes it much easier to scan the query definition over quickly too.

Naming conventions: Columns

Always use the singular name. Where possible avoid simply using id as the primary identifier for the table. Do not add a column with the same name as its table and vice versa. Always use lowercase except where it may make sense not to such as proper nouns.

Naming conventions: Reserved words

Always use uppercase for the reserved keywords like SELECT and WHERE. It is best to avoid the abbreviated keywords and use the full length ones where available (prefer ABSOLUTE to ABS). Do not use database server specific keywords where an ANSI SQL keyword already exists performing the same function. This helps to make code more portable. SELECT model_num FROM phones AS p WHERE p.release_date > '2014-09-30';

XQuery

An XML transformation language that allows applications to query both relational databases and XML data.

table

An arrangement of data made up of horizontal rows and vertical columns. -most fundamental part of database storage

database query

An inquiry the user poses to a database to extract a meaningful subset of data.

foreign key (FK)

Attribute that allows database tables to be linked together; foreign keys are the primary keys of other tables placed in the current table to support the link between the two tables.

Create (DDL)

CREATE - create a database object, such as a table or view

valid statement for creating a view

CREATE VIEW vwAllPartIDs AS SELECT PartID FROM Parts;

nvarchar

Character data type that is variable-length, Unicode data and use the UNICODE UCS-2 character set. nvarchar [ ( n | max ) ] Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

VARCHAR

Character data type with variable length.

Drop (DDL)

DROP - removes an object from a database

DML (Data Manipulation Language)

Collection of instructions used to move/edit items in databases.

DDL (Data Definition Language)

DDL deals with creating database objects, such as tables, constraints, and stored procedures. DDL statements are used to Create tables, Alter tables and Drop tables. o USE changes the database context to the specified database or database snapshot. · CREATE - create a database object, such as a table or view · ALTER - change an existing database object · DROP - removes an object from a database · TRUNCATE - remove rows from a table and free up the space they were using · DELETE - remove rows from a table without freeing up the space they were using o Delete operations may fail due to foreign key violations

DELETE (DDL)

DELETE - remove rows from a table without freeing up the space they were using o Delete operations may fail due to foreign key violations

Hierarchical database

Data is arranged in a tree structure, with parent records at the top of the database, and a hierarchy of child records in successive layers

Hierarchical database model

Data is organized in tree's according to relationships (one to many)

Statement normally used to execute a SQL Server stored procedure?

EXEC

Naming conventions: General

Ensure the name is unique and does not exist as a reserved keyword. Keep the length to a maximum of 30 bytes—in practice this is 30 characters unless you are using multi-byte character set. Names must begin with a letter and may not end with an underscore. Only use letters, numbers and underscores in names. Avoid the use of multiple consecutive underscores—these can be hard to read. Use underscores where you would naturally include a space in the name (first name becomes first_name). Avoid abbreviations and if you have to use them make sure they are commonly understood. SELECT first_name FROM staff;

Data Type: Decimal

Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal. decimal(6,2) Storage: 14 bytes Range: +/-79,228,162,514,264,337,593,543,950,335 with no decimal point +/-7.9228162514264337593543950335 with 28 places to the right of the decimal Smallest non-zero number is+/-0.0000000000000000000000000001

decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally identical to decimal. 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.

Data Type (Currency)

Identifies entries containing money amounts Storage size: 8 bytes Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807

A DML statement can be used to:

Retrieve, add, change, or remove data from a table. The SELECT statement can be used to retrieve data. The INSERT statement can be used to add data. The UPDATE statement can be used to change data. The DELETE statement can be used to remove data.

LDF files

Log file- keeps track of changes made to the DB

[ ] (brackets)

Optional syntax items. Don't type the brackets.

When working on a stored procedure in SSMS which option on the Query menu enables you to check its syntax?

Parse

Naming conventions: Tables

Rules: Pascal notation; end with an 's' Examples: Products, Customers Group related table names Use a collective name or, less ideally, a plural form. For example (in order of preference) staff and employees. Do not prefix with tbl or any other such descriptive prefix or Hungarian notation. Never give a table the same name as one of its columns and vice versa. Avoid, where possible, concatenating two table names together to create the name of a relationship table. Rather than cars_mechanics prefer services.

When running a stored procedure which of these commands would you use to suppress the count statement that SQ Server produces?

SET NOCOUNT ON

SSMS

SQL Server Managment Studio - GUI to manage SQL server

| (vertical bar)

Separates syntax items enclosed in brackets or braces. You can use only one of the items.

Naming conventions: Aliasing or correlations

Should relate in some way to the object or expression they are aliasing. As a rule of thumb the correlation name should be the first letter of each word in the object's name. If there is already a correlation with the same name then append a number. Always include the AS keyword—makes it easier to read as it is explicit. For computed data (SUM() or AVG()) use the name you would give it were it a column defined in the schema. SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; SELECT SUM(s.monitor_tally) AS monitor_total FROM staff AS s;

Naming conventions: Spaces

Spaces should be used to line up the code so that the root keywords all end on the same character boundary. This forms a river down the middle making it easy for the readers eye to scan over the code and separate the keywords from the implementation detail. Rivers are bad in typography, but helpful here. (SELECT f.species_name, AVG(f.height) AS average_height, AVG(f.diameter) AS average_diameter FROM flora AS f WHERE f.species_name = 'Banksia' OR f.species_name = 'Sheoak' OR f.species_name = 'Wattle' GROUP BY f.species_name, f.observation_date) UNION ALL (SELECT b.species_name, AVG(b.height) AS average_height, AVG(b.diameter) AS average_diameter FROM botanic_garden_flora AS b WHERE b.species_name = 'Banksia' OR b.species_name = 'Sheoak' OR b.species_name = 'Wattle' GROUP BY b.species_name, b.observation_date); Notice that SELECT, FROM, etc. are all right aligned while the actual column names and implementation specific details are left aligned. Although not exhaustive always include spaces: before and after equals (=) after commas (,) surrounding apostrophes (') where not within parentheses or with a trailing comma or semicolon. SELECT a.title, a.release_date, a.recording_date FROM albums AS a WHERE a.title = 'Charcoal Lane' OR a.title = 'The New Danger';

bigint

Storage: 8 Bytes Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

Which of these can best be described as a batch of T-SQL statements that has been given a name and stored in a database?

Stored procedure

Data types: Boolean

Stores True or False only. This is sometimes taught as 1 or 0 only where 1 is true and 0 false

Data types: Character

Stores a single character which can be a letter, number or symbol

Data types: String

Stores alphanumeric combinations and text. String is really a group of characters stored together as one. Numbers to be used for calculations should not be stored as string data even though they can be. They should be stored as INTEGER or REAL

Data Types: Date

Stores dates

Data types: Integer

Stores positive or negative whole numbers

Data Type: Date

Stores year, month, and day values date Storage size: 8 bytes Range: January 1, 100, to December 31, 9999

CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row. CREATE TABLE Persons ( P_Id int NOT NULL CHECK (P_Id>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' );

NOT NULL constraint

The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. CREATE TABLE PersonsNotNull ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

Naming conventions: Uniform suffixes

The following suffixes have a universal meaning ensuring the columns can be read and understood easily from SQL code. Use the correct suffix where appropriate. _id—a unique identifier such as a column that is a primary key. _status—flag value or some other status of any type such as publication_status. _total—the total or sum of a collection of values. _num—denotes the field contains any kind of number. _name—signifies a name such as first_name. _seq—contains a contiguous sequence of values. _date—denotes a column that contains the date of something. _tally—a count. _size—the size of something such as a file size or clothing. _addr—an address for the record could be physical or intangible such as ip_addr.

<label> ::=

The name for a block of syntax. Use this convention to group and label sections of lengthy syntax or a unit of syntax that you can use in more than one location within a statement. Each location in which the block of syntax could be used is indicated with the label enclosed in chevrons: <label>.A set is a collection of expressions, for example <grouping set>; and a list is a collection of sets, for example <composite element list>.

VARCHAR2

This datatype is used to store variable length character strings. The string value's length will be stored on disk with the value itself. It's size depends on how many bytes you are actually going to store in the database. The number you specify is just the maximum number of bytes that can be stored (although 1 byte is minimum). You should use this datatype when you don't know the exact length of stored strings.

Naming conventions: white space

To make the code easier to read it is important that the correct complement of spacing is used. Do not crowd code or remove natural language spaces.

;

Transact-SQL statement terminator. Although the semicolon isn't required for most statements in this version of SQL Server, it will be required in a future version.

bold

Type database names, table names, column names, index names, stored procedures, utilities, data type names, and text exactly as shown.

NDF files

Used with a large DB. Physical files that can be easier to scale secondary files that can be used to store more on other disks.

italic

User-supplied parameters of Transact-SQL syntax.

SMALLINT

Uses less space than INTEGER Storage: 2 Bytes Range: -2^15 (-32,768) to 2^15-1 (32,767)

MDF files

Where most of the data is stored-> on the DB disk

explicit conversion

Writing the code to convert from one data type to another; as opposed to implicit conversion.

database

a collection of organized data that allows access, retrieval, and use of data

camel casing

a style in which an identifier begins with a lowercase letter and subsequent words within the identifier are capitalized

input parameters

allow the caller to pass a data value to the stored procedure or function. Where as arguments are the actual things being passed, the Input Parameters are the things allowed to be passed. So If I had a class with an input parameter of object type Fruit, and someone used the class and passed it an Orange, the Orange would be the argument.

Alter (DDL)

change an existing database object

fields

columns in the table; each field describes a characteristic of the record

SQLCMD

command-line application that comes with Microsoft SQL Server and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt.

database index

contains a list of keys, and each key provides a pointer to the data that contains the rest of the fields related to that key

Database Fundamentals

each table in a relational database can be thought of as a spreadsheet it is easy to add or delete columns adding, updating, or deleting rows is part of data maintenance data can be sorted on any column

database server

is a powerful computer that is configured specifically to host a database also A database server is a software system (DBMS).

Database objects divided into two broad categories

o Storage o Programmability (DBMS)

truncate (DDL)

remove rows from a table and free up the space they were using

implicit conversion

the automatic transformation of one data type to another

Transact-SQL (T-SQL)

the primary means of programming and managing SQL Server. It exposes keywords so that you can create and manage databases and their components and monitor and manage the server itself.

comment out a statement

turn a statement into a comment so the compiler will not execute its command. use the /* text here */ or the -- at the beginning of each line

Flat-type database

two-dimensional tables consisting of rows and columns. most commonly used in plain -text formats. purpose is to hold one record per line.

primary key (PK)

uniquely identifies each record among all other records in a database this column must contain a unique non-null value. A table can only have one primary key

money datatype in SQL Server

use where you need to store money or currency values. This is a fixed-precision data type. Relates to the location. UK - pounds sterling.

Data type: datetime

used to store dates and times or just dates. There are many different forms, but the two main ones are datetime and datetime2. Precision with each range of numbers. Storage space.

CAST

· CAST is compatible with ANSI standards o Syntax: CAST (source-value AS destination-type) o Example: CAST (quantity AS float) o Example: CAST (ID AS varchar (10)) · When converting to a character string type the default number of characters is 30, but it is good practice to always specify the number.

Convert

· CONVERT is specific to T-SQL but is more powerful. o Syntax: CONVERT (datatype [(length)], expression [,style]) o Example: CONVET (varchar (10), DateOfBirth, 101) · The example would convert DateOfBirth to a variable length character string in the USA 101 date format mm/dd/yyyy.

Relational Database Concepts

· Database comprises tables · Each row in each table corresponds to one record · Each column in a table corresponds to one attribute · Relationships between the tables are created

Data Types Used in Databases

· Money - use where you need to store money or currency values. This is a fixed-precision data type. Relates to the location. UK - pounds sterling. · Datetime - used to store dates and times or just dates. There are many different forms, but the two main ones are datetime and datetime2. Precision with each range of numbers. Storage space. · Integer - use to store whole numbers, i.e. where you don't need decimal places · Varchar - use varchar to store character strings. If you need to store non-English characters, you may need to use nvarchar instead · Boolean (bit) - also known as bit. Use boolean to store true/false values, yes/no values. Etc. where there are just 2 options. · Float - use float in scientific and other applications where you may need very large or very small numbers that may not be able to be stored exactly but can be stored to a high-level precision.

Why use views?

· Security. Views can be used to restrict access to some fields or records from the underlying tables, e.g. only the orders for a specific customer. · Simplicity. In a complex database views might be provided to present subsets of complex information. · Analysis. Views can provide summaries and totals. · Support. Views can be provided to reduce the need for users to write complex queries.

SQL Standards

· Standards are needed to be used when setting up a new database · As an example, there are a couple of popular naming standards that are helpful. · PascalCase starts each world with a capital letter · camelCase starts each new word except

Purpose of tables

· To provide a structure for storing data within a relational database · A SQL server database is generally a container for a number of tables · You can use queries to retrieve data from these tables · One advantage that a database has over a series of spreadsheets is that the database can parse out redundant data for you. o Parse out means that you do not need to duplicate data