Long-Term Learning
Learn efficiently and remember over time.
Start Long-Term Learning
Add this set to a folder
Data Type
specify the possible range of values of the set, the operations that can be performed
on the values, and the way in which the values are stored in memory.
a Graphical Designer
is an application, such as Microsoft Access, that is object-oriented and drag-and-drop driven.
User-defined functions
are compact segments of user-written SQL code that can accept parameters and return either a value or a table.
Boolean logical operators (AND,OR,Truth tables)
AND:displays a record if both the
first condition and the second condition are true. OR:displays a record
if either the first condition or the second conditions are true.
Truth Tables:show the relationships of the Boolean logical operators.
A predicate subquery:
uses extended logical constructs in the WHERE clause using AND, OR, LIKE, BETWEEN, AS, and TOP
The difference between UNION and JOIN is:
UNION combines the results of two queries (note: must have same number of columns and data types)
JOIN returns rows when there is at least one column match
The INTERSECT query is used:
to return only the rows that appear in both tables, similar to a Boolean AND
When data is missing from a particular column, if you use the INSERT statement what happens to those column?
the INSERT statement uses the default value for the column
To set up the INSERT to allow the programmer to copy rows from other tables:
use the INSERT INTO with a SELECT...FROM clause
What is the correct syntax for inserting multiple rows at one time?
INSERT INTO table (columnl, column2) VALUES (val1a, val1b), (val2a, val2b);
The INSERT INTO with SELECT statement is used (when?)
to insert a new row in a table when a sub-select is used instead of the VALUES clause.
To update all records in a database to reflect an increase in value-added tax from 8 percent to 10 percent the developer should use:
UPDATE RENTALS SET value _ added _ tax = .10
You can update data if a condition is true and perform an alternative update if the condition is false with the:____statement
CASE statement
To update data in one table from another table, the developer should use: (what syntax?) (Rentals,equipment)
UPDATE RENTALS SET kayak = RENTALS.kayak + EQUIPMENT.kayak FROM RENTALS,
EQUIPMENT
What does a WHERE clause in an UPDATE statement do?
specifies the rows to update. (also, used to specify criteria)
What does the CASE statement create?
creates when-then-else functionality.
Give an example using the WHEN and THEN?
(WHEN this condition is met THEN do this.)
1. What is the value of the case expression?
2. What is the result if none is true?
1. the value of the first WHEN clause that is true.
2. if non is true, the result is the ELSE.
If you do not include a WHERE statement in the DELETE statement: what happens?
the data from the table is deleted
Transactions are useful when updating/deleting/adding records to a database because:
they allow multiple statements to be grouped together to avoid data integrity errors
A programmer can enforce data integrity rules when making changes to the records in a database by:
when all statements in a transaction have successfully completed, issuing a commit statement
If all statements are correct within a single transaction what command records it to the database?
the COMMIT command records the changes to the database. if all statements are correct in a single transaction.
What are characteristics of the Unicode data type?
Takes up more space but allows the use of multiple languages
What is meant by a data type- variable length
Can assign a certain number to it, if that number is not reached the remaining space is not used.
How can a database of addresses be simplified?
Storing the zip code only in the primary table and creating a separate table of city, state, and zip.
Normalization-(from study guide)
involves applying a body of techniques to a relational database to minimize the inclusion of
duplicate information.
What does the word inclusion mean?
The act or state of being included into a group or structure (a larger group). TO INCLUDE SOMEONE!
Normal Form (Study guide answer). What does it try to avoid? What does it promote?
is the result of structuring (organizing) information to avoid redundancy and inconsistency and to promote efficient maintenance, storage, and updating.
The First Normal Form (1NF)----(SG). GENERAL
sets a few basic rules for a database: eliminate duplicative columns from the same table.
The Second Normal Form (2NF)----(SG) GENERAL
(equals) = 1NF + removing subsets of data that apply to multiple rows of a table and place them in separate tables.
The Third Normal Form (3NF)----(SG) GENERAL
(equals) = 2NF + removing columns that are not dependent upon the primary key.
What are three (common) types of data integrity? and what do they focus on?
1. entity focuses on the primary key,
2. referential focuses on the foreign key
3. domain specifies that values must be declared.
A primary key (SG)
serves as the unique identifier of a specific row and uniquely defines a relationship
within a database.
If a database will contain a large number of records, what should you think about adding, even if it requires more time and effort to do?
Add an Index! (large number of records, the benefits of adding an index out-weighs the necessary time and effort!)
Which type of index has keywords & pointers to the data, but does not contain the actual data pages or records?
Non-clustered index
Which is a characteristic of a non-clustered index?
a. contains the actual data pages or records
b. has keywords and pointers to the data
c. includes foreign keys
B! has keywords and pointers to the data
Which is Not a result of using indexes?
a. increased speed of data retrieval
b. increased storage requirements
c. increased speed of writing records
C! increased speed of writing records
An INDEX
What contains 'keywords' and 'associated data' that point to the 'location' of more comprehensive information, (such as files and records on a disk or record keys in a database.)
1. What is a structure for storing database indexes?
2. In that structure, how are the database indexes stored?
1. b-tree structure.
2. each node in the tree contains a sorted list of key values and links
that correspond to ranges of key values between the listed values.
How is a non-clustered index arranged? (SG)
It is arranged similarly to the index of a book, where the index value points to the actual information.
How is a Clustered index arranged? (SG)
It is arranged in a special order to make retrieval of information faster with direct access to the information.
If you group users by roles which statement would you use and what would that do?
Use the GRANT statement to provide access according to
their role.
What should be included in the physical security plan? (SG)
The physical security plan Should include:
location of servers with restricted entry access
Which strategy is not related to an INTERNAL security plan?
a. provide backups and operational continuity
b. delete old and unused user accounts
c. enforce user accounts to have strong passwords
A! provide backups and operational continuity.
---it is not related to the INTERNAL security plan.
What do Roles do, regarding security?
grant permissions to the database. Defined roles give all users within a group the same permissions.
The backup strategies that only copies files that have changed since the last full backup is:
differential backup
In which situation is the use of a replication service recommended?
the database must be available 24 hours a day, 7 days a week
When performing a full backup, what information is optional?
a. data that has not changed
b. server files such as user security NAT
c. data that has not changed since the last full backup
B. server files such as user security NAT
Backup is a process of what?
saving all critical data to re-create the database in useful form in a relatively short time.
in an Incremental Backup, what takes place? INCREMENTAL
only files that have been changed since the last backup are copied.
a differential backup, back ups what? DIFFERENTIAL
Only files that have been changed since the last full_backup are copied.
Replicated services, do? REPLICATED SERVICES
re-create a full or incremental reproduction of the database.
Which of the following are not methods to manipulate data?
a. select, insert, update, and delete
b. creation of stored procedures and functions
c. creation of primary and foreign keys
c. creation of primary and foreign keys
Which columns do you need to set a value for when issuing an INSERT statement?
When you do, which function do you need to apply and where?
1.You need to set a value for a column that stores values of the uniqueidentifier data type.
2. you need to use the NEWID() function in the VALUES list to generate the value.
Do you need to set a value for a column that has a DEFAULT constraint when issuing an INSERT statement?
you do not need to set a value for a column that has a DEFAULT constraint. If the value is not specified, the value determined by the DEFAULT constraint is used.
When no join is provided in a query, what happens?
the query performs a cross join and returns a Cartesian product.
The ON DELETE CASCADE option causes, what to happen?
rows related by a FOREIGN KEY constraint to be deleted when the referenced row is deleted.
What does the ON DELETE NO ACTION option do?
prevents rows that are referenced through a FOREIGN KEY constraint from being deleted. (Cascade related)
what is one difference between an application role and a database role?
A database role is assigned to users.
What do application roles allow access to?
Application roles allow access to
database objects, but only through an application. Application roles
are not assigned to users. Instead, a single login is
associated with an application.
What is ONE difference between application roles and database roles?
Application roles are not granted and revoked like database roles.
Normalizing a database to 2nd normal form. What 2 steps do you make?
1. you identify any columns that
have the same value for more than a single primary key value and
separate those columns into a different table.
2.You then create foreign key relationships between the tables.
How does the third normal form (3NF) take normalization a step further than 2NF?
by ensuring that each piece of data in the table depends only on the primary key. each attribute must be related to the primary key in order to be in the 3rd form.
The SELECT INTO statement is used to do what? and what is important when issuing the SELECT INTO statement?
1. used to add rows to a different table.
2. the table must be empty when you execute the query
when you assign a DEFAULT value to a column?
DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added.
when you assign a CHECK (constraint) value to a column?
CHECK is specified for a column to ensure that all column values meet a specified condition, such as falling within a specified range of values.
The SET clause includes what, to be updated? (list two things)
1. each column to be updated, along with..
2. an assignment operator (=) and the value to which the column should be updated.
a WHERE clause is used to do what, when updating.
(whats an example?)
to filter which rows will be updated.
(SET LastUpdateDt = getdate(), CreditLimit = 2500 WHERE CustID = 893)
The FROM clause is used in an UPDATE statement, when/used for?
is used to update rows in a table based on information stored in another table.
1. Can you use a VALUES clause with an UPDATE statement?
2. When can a VALUES clause be used?
VALUES clause is not valid for the UPDATE statement.
2. The VALUES clause can be used with an INSERT statement to specify the values that should be inserted.
1. Can you use a INTO clause with an UPDATE statement?
2. When can a VALUES clause be used? (list two ways)
1.INTO clause is not valid for the UPDATE statement.
2. he INTO clause can be used with a SELECT statement to insert the results into a new table.
& It is also an optional keyword that can be specified in the syntax of an INSERT statement.
What is a junction or intersection table.
The table contains columns that references what?
1.An additional table added When you have a many-to-many relationship.
2.references the primary key of the table on the one side of the
relationship and another column that references the primary key of the
table on the other side of the relationship.
By, default you can't specify a value for an identity column within an INSERT statement. Can you override that default? if so, how?
you can override by using SET IDENTITY_INSERT to turn the identity insert option ON. After you issue the INSERT statement, you should turn identity insert OFF because only a single table can be enabled for identity insert within a session.
The GROUP BY clause
allows you to perform an aggregate calculation over rows that have a matching value in a column.
When creating a view, are any constraints not considered? if so, What is one of those constraints?
the CHECK constraints on the table are not considered,
1. SCHEMABINDING clause, purpose?
2. when is it available?
1.it binds the schema of referenced ob jects and columns to the schema of the referencing object.
2. is available to views and UDFs
If you have two DELETE statements, deleting rows from separate tables. By default when you execute a DML statement (such as DELETE) what happens automatically?
the changes are automatically saved to the database if the statement is successful. So if you issue two statements at once and only one is successful, by default only the one would be implemented to the database.
To ensure that either both DELETE statements are successful or both fail, you can use what?
an explicit transaction.
A cross join
returns a Cartesian product (a result in which each combination of rows is represented).
1. A user-defined function must contain which clause AND which statement?
2. What does the clause do?
3. what does the statement do?
1. a RETURNS clause and a RETURN statement.
2. RETURN clause, to specify the type of data that will be returned by the function.
3. RETURN statement is used to return the value from the function.
The CREATE FUNCTION statement is used when?
to create a user-defined function. User-defined functions must return a value.
indexes are used to optimize what?
optimize frequently-used queries by reducing the number of table scans required to locate data.
In a table scan-
each row of a table is loaded to locate the records needed to generate the results.
you can give the query optimizer a more efficient way to locate the rows required to generate the results by selecting certain columns, which kinds of columns?
columns that are frequently used as join conditions, in WHERE clauses, and in GROUP BY clauses as key columns in an index
1. The ALTER VIEW statement is?
2. adding a WHERE clause does what in a view?
1. a DDL statement that is used to modify the definition of an existing view.
2. Adding a WHERE clause to the view's definition will ensure that only
the rows that meet the WHERE clause condition are visible through the
view.
In a VIEW what does the WITH CHECK OPTION clause do?
used to ensure that no changes to the underlying data can be made through the view that would cause the data to not conform to the view's definition.
A login
A login is a server-level object that is used to grant the ability to establish a connection with an instance of SQL Server.
the INTERSECT operator is used to, what?
to return a list of only the rows that are in both of the result sets generated by two other separate queries.
the EXCEPT operator
returns all rows in the results generated by the query on the left-hand side that are not included in the results generated by the query on the right-hand side.
1. In a well-designed relational database table, a column represents what?
2. The entity is represented how?
1. a column represents a single entity attribute.
2. The entity is represented with a table, and its attributes are represented by columns within the table.
A stored procedure can modify what?
the data in permanent tables and return multiple values as output parameters.
A scalar function
performs an operation and returns a single value. (a function can't perform any modification on a permanent table)
datetimeoffset
The datetimeoffset data type stores the date and time, including an offset from Greenwich Mean Time (GMT).
datetime2
The datetime2 data type stores date and time information at user- defined precision. However, it does not store a time zone offset.
time
The time data type stores only time information up to 100 nanosecond accuracy. It does not store a time zone offset.
smallmoney (supports, stored to what decimal point, requires how much storage)
The smallmoney data type supports values between -214,748.3648 and 214,748.3647. All values are stored with accuracy to four decimal points. The smallmoney data type requires 4 bytes of storage capacity.
Money
money data type stores data with the accuracy of four decimal points. It Requires 8 bytes of storage. The money data type supports values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.
Both the decimal and numeric data types allow you to?
What is precision?
store numbers with a fixed scale and precision. The precision is the total number of digits, including those to the left of the decimal and to the right of the decimal.
1. You would issue a DML statement to set, what?
2. DML statements do what?
1.an attribute value for an entity instance.
2.A DML statement is a statement that retrieves, adds, deletes, or
modifies a row of data in a table. You would use an UPDATE statement to
modify the data in a row.
to control/change the physical sort order of a table what would you use?
You would use the CREATE INDEX statement to create a clustered index if you wanted to change the physical sort order of a table. (optimize data retrieval)
Which clause is required when issuing an UPDATE statement?
The SET clause ( You specify the columns or variables that should be updated and, when a FROM clause is not also used, their values.)
To m inim ize the am ount o f redundant data that is stored, you should design your tables to include only information about what?
a single entity (Tables are used to store inform ation about unique entities)
when a view definition contains a join:
DML statements can be performed using the view, but only on a single base table.
in a view can DML statements be used? are there limitations?
DML can be performed using the view, but only against one base table at a time.
A login is a what?
server-scoped securable. Server-scoped securables are available across all database instances.
A stored procedure what type of securable?
a schema-scoped securable. A schema-scoped securable is one that is created within a schema and can receive permissions through that schema.
The LIKE keyword is used in what?
the WHERE clause of a SELECT statement to return results with character column values that contain specific characters or specific character strings.
The TRUNCATE TABLE statement does?
deletes all rows from the table, but does not log each row deleted.
Differences between TRUNCATE and DELETE?
Because the TRUNCATE TABLE
statement does not log each deletion, it is faster and requires fewer
locks than the DELETE statement.
The TRUNCATE TABLE statement requires fewer resources than the DELETE statement.