168 Multiple choice questions
- 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.
- 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
- all files are copied for possible future retrieval.
- reverses changes
- each row of a table is loaded to locate the records needed to generate the results.
- as a ROW in a table
- stores A-Z or 0-9-any digit or letter that math functions will not be applied to.
- 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.
- Non-clustered index
- Use the GRANT statement to provide access according to
- deletes all rows from the table, but does not log each row deleted.
- return a single value, calculated from values in a column.
- is a saved query that creates a virtual table from the result set of the query
- Allows whole numbers billion 4 bytes
- is a specific set of instructions for extracting/selecting particular data.
- is an application, such as Microsoft Access, that is object-oriented and drag-and-drop driven.
- the COMMIT command records the changes to the database. if all statements are correct in a single transaction.
- The physical security plan Should include:
location of servers with restricted entry access
- (equals) = 1NF + removing subsets of data that apply to multiple rows of a table and place them in separate tables.
you identify any columns that have the same value for more than a
single primary key value and separate those columns into a different
2.You then create foreign key relationships between the tables.
- DML can be performed using the view, but only against one base table at a time.
- are a set of two or more statements grouped into a single unit.
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.
- provides users with access to the database.
- the @ character
- Allows whole numbers 8 bytes
- a single entity (Tables are used to store inform ation about unique entities)
- A database role is assigned to users.
- SELECT DISTINCT
- serves as the unique identifier of a specific row and uniquely defines a relationship
within a database.
- INSERT INTO table (columnl, column2) VALUES (val1a, val1b), (val2a, val2b);
- c. creation of primary and foreign keys
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.
- 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.
- returns only matched rows from the two tables.
- the CHECK constraints on the table are not considered,
- the query performs a cross join and returns a Cartesian product.
- which users can do what action(s) to data in the database.
- Takes up more space but allows the use of multiple languages
- UPDATE RENTALS SET kayak = RENTALS.kayak + EQUIPMENT.kayak FROM RENTALS,
- 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.
- It is arranged similarly to the index of a book, where the index value points to the actual information.
- The timestamp data type does not store date and time information.
- rows related by a FOREIGN KEY constraint to be deleted when the referenced row is deleted.
- returns only distinct (unique) values.
- to insert a new row in a table.
- server-scoped securable. Server-scoped securables are available across all database instances.
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.
- 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.
- exact and approximate
- specifies the rows to update. (also, used to specify criteria)
- locked records
- ensures and restricts who can connect and what they can do.
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.
- Application roles are not granted and revoked like database roles.
- B! has keywords and pointers to the data
- grant permissions to the database. Defined roles give all users within a group the same permissions.
- UPDATE RENTALS SET value _ added _ tax = .10
- 1. used to add rows to a different table.
2. the table must be empty when you execute the query
- C! increased speed of writing records
- allows you to perform an aggregate calculation over rows that have a matching value in a column.
- columns that are frequently used as join conditions, in WHERE clauses, and in GROUP BY clauses as key columns in an index
- to return only the rows that appear in both tables, similar to a Boolean AND
- 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.
- optimize frequently-used queries by reducing the number of table scans required to locate data.
- DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added.
- the SET clause
- are the individuals, groups, and processes granted access to SQL Server
- prevents rows that are referenced through a FOREIGN KEY constraint from being deleted. (Cascade related)
- are the server, database, and objects a database contains
- addresses the safety of the actual location of the database.
- deletes rows in a table
- A login is a server-level object that is used to grant the ability to establish a connection with an instance of SQL Server.
- performs an operation and returns a single value. (a function can't perform any modification on a permanent table)
- The datetimeoffset data type stores the date and time, including an offset from Greenwich Mean Time (GMT).
- to create a user-defined function. User-defined functions must return a value.
- they allow multiple statements to be grouped together to avoid data integrity errors
- To users and groups
- 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.
SET clause ( You specify the columns or variables that should be
updated and, when a FROM clause is not also used, their values.)
- Can assign a certain number to it, if that number is not reached the remaining space is not used.
- only files that have been changed since the last backup are copied.
- data type store whole numbers
- data types store any real number
- sets a few basic rules for a database: eliminate duplicative columns from the same table.
- 1. entity focuses on the primary key,
2. referential focuses on the foreign key
3. domain specifies that values must be declared.
- a single attribute of an entity.
- The GROUP BY clause.
- order the results of a query.
- are precompiled groups of SQL statements saved to the database.
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.
- to filter which rows will be updated.
(SET LastUpdateDt = getdate(), CreditLimit = 2500 WHERE CustID = 893)
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
- an explicit transaction.
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.
- are one-word commands that return a single value written in the command set of SQL.
- is used when a column references another column in the same table.
- to return a list of only the rows that are in both of the result sets generated by two other separate queries.
- 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.
- CASE statement
- returns a Cartesian product (a result in which each combination of rows is represented).
- combines two or more SELECT statements with an AND function.
- OUTPUT keyword
- Storing the zip code only in the primary table and creating a separate table of city, state, and zip.
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
- A! provide backups and operational continuity.
---it is not related to the INTERNAL security plan.
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.
the result of structuring (organizing) information to avoid redundancy
and inconsistency and to promote efficient maintenance, storage, and
- is used to query data from two or more tables.
- It is arranged in a special order to make retrieval of information faster with direct access to the information.
- are compact segments of user-written SQL code that can accept parameters and return either a value or a table.
- selects data and returns the results as a table.
- stores 1 or 0, true or false
- when all statements in a transaction have successfully completed, issuing a commit statement
- return a single value, based on the input value of a single field.
- creates when-then-else functionality.
Give an example using the WHEN and THEN?
(WHEN this condition is met THEN do this.)
- use the INSERT INTO with a SELECT...FROM clause
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.
- The time data type stores only time information up to 100 nanosecond accuracy. It does not store a time zone offset.
- the database must be available 24 hours a day, 7 days a week
- references the primary key in another table.
- avoid large index keys for more optimal performance.
schema-scoped securable. A schema-scoped securable is one that is
created within a schema and can receive permissions through that schema.
- 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.
- automatically assign sequential integer values to a column.
- is used to update rows in a table based on information stored in another table.
- re-create a full or incremental reproduction of the database.
- Security Attacks
- combines two or more SELECT statements with an OR function.
- DML statements can be performed using the view, but only on a single base table.
- (equals) = 2NF + removing columns that are not dependent upon the primary key.
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.
- 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 INSERT statement uses the default value for the column
- the or operator and at least one field = true
- the data in permanent tables and return multiple values as output parameters.
- ensures that data is consistent, correct, and usable throughout the database.
- Normalizing to INF requires that you eliminate repeating groups.
- involves applying a body of techniques to a relational database to minimize the inclusion of
- differential backup
is specified for a column to ensure that all column values meet a
specified condition, such as falling within a specified range of values.
- Add an Index! (large number of records, the benefits of adding an index out-weighs the necessary time and effort!)
- the getdate() function
- ORDER BY column _ name (either ASC or DESC)
- are granted to a principal for every SQL Server securable
- Only files that have been changed since the last full_backup are copied.
- saving all critical data to re-create the database in useful form in a relatively short time.
- the data from the table is deleted
WHERE clause of a SELECT statement to return results with character
column values that contain specific characters or specific character
- The act or state of being included into a group or structure (a larger group). TO INCLUDE SOMEONE!
datetime2 data type stores date and time information at user- defined
precision. However, it does not store a time zone offset.
- to insert a new row in a table when a sub-select is used instead of the VALUES clause.
a function can be directly referenced in a SELECT statement. A stored
procedure cannot be directly referenced in a SELECT statement or
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
- 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
- 1. each column to be updated, along with..
2. an assignment operator (=) and the value to which the column should be updated.
- B. server files such as user security NAT
- uses extended logical constructs in the WHERE clause using AND, OR, LIKE, BETWEEN, AS, and TOP
- A column (is assigned a data type)
- update existing records in a table.
- 1. the value of the first WHEN clause that is true.
2. if non is true, the result is the ELSE.
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
- nest inside another query.
- returns all matching rows from both tables and all unmatched rows.
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.)