168 Matching questions
- What are TRANSACTIONS?
- A paramater name must begin with what?
- Scalar functions
- What about an index should you avoid? and why?
- The ORDER BY clause of a SELECT statement is used to?
- The ON DELETE CASCADE option causes, what to happen?
- a self-join
- 1. Can you use a VALUES clause with an UPDATE statement?
2. When can a VALUES clause be used?
- The INSERT INTO with SELECT statement is used (when?)
- Data integrity (SG)
- When no join is provided in a query, what happens?
- when you assign a CHECK (constraint) value to a column?
- 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?
- What does the CASE statement create?
- How is a non-clustered index arranged? (SG)
- Which is Not a result of using indexes?
a. increased speed of data retrieval
b. increased storage requirements
c. increased speed of writing records
- Normalization-(from study guide)
- How can a database of addresses be simplified?
- a differential backup, back ups what? DIFFERENTIAL
- a Query
- In a VIEW what does the WITH CHECK OPTION clause do?
- What does the word inclusion mean?
- A security plan identifies what?
- A physical security plan does what?
- Boolean logical operators (AND,OR,Truth tables)
- Aggregate functions
- UPDATE is used to what?
- A table-valued function
- The condition that always results in true is:
- Normalizing 1st form
- DEFAULT constraint
- To update data in one table from another table, the developer should use: (what syntax?) (Rentals,equipment)
- A database role is assigned to who?
- Which type of index has keywords & pointers to the data, but does not contain the actual data pages or records?
- You define an output parameter by using, which keyword?
- The GROUP BY clause
- The difference between UNION and JOIN is:
- A stored procedure what type of securable?
- To ensure that either both DELETE statements are successful or both fail, you can use what?
- 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:
- 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
- Transactions are useful when updating/deleting/adding records to a database because:
- the EXCEPT operator
- The CREATE FUNCTION statement is used when?
- How does the third normal form (3NF) take normalization a step further than 2NF?
- When creating a view, are any constraints not considered? if so, What is one of those constraints?
- What are characteristics of the Unicode data type?
- What is ONE difference between application roles and database roles?
- 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?
- it is important that backup software should work with what?
- in an Incremental Backup, what takes place? INCREMENTAL
- The INSERT INTO statement is used (when?)
- Is Combining informational columns, part of the normalization process?
- Data types are assigned and stored where?
- What is meant by a data type- variable length
- What does the ON DELETE NO ACTION option do?
- The FROM clause is used in an UPDATE statement, when/used for?
- What is a junction or intersection table.
The table contains columns that references what?
- Data Type
- The LIKE keyword is used in what?
- What does ROLLBACK do?
- Which clause is required when issuing an UPDATE statement?
- how is data about each instance of an entity stored in a relational database?
- If all statements are correct within a single transaction what command records it to the database?
- 1. SCHEMABINDING clause, purpose?
2. when is it available?
- The TRUNCATE TABLE statement does?
- the INTERSECT operator is used to, what?
- In a table scan-
- 1. Can you use a INTO clause with an UPDATE statement?
2. When can a VALUES clause be used? (list two ways)
- What do application roles allow access to?
- The Second Normal Form (2NF)----(SG) GENERAL
- what is one difference between an application role and a database role?
- A column is used to store data for?
- Differences between TRUNCATE and DELETE?
- The INTERSECT query is used:
- privilege escalation and SQL injections are examples of what?
- What do Roles do, regarding security?
- The Third Normal Form (3NF)----(SG) GENERAL
- A primary key (SG)
- The CREATE INDEX statement is a what statement?
- smallmoney (supports, stored to what decimal point, requires how much storage)
- The command that returns an alphabetical list is:
- What does a WHERE clause in an UPDATE statement do?
- When data is missing from a particular column, if you use the INSERT statement what happens to those column?
- A login
- 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
- indexes are used to optimize what?
- a View
- How is a Clustered index arranged? (SG)
- Both the decimal and numeric data types allow you to?
What is precision?
- In which situation is the use of a replication service recommended?
- primary difference between a function and a stored procedure is
- 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
- Normal Form (Study guide answer). What does it try to avoid? What does it promote?
- In a full backup what happens? FULL BACKUP
- A scalar function
- A full outer join
- 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
- 1. What is the value of the case expression?
2. What is the result if none is true?
- What is the correct syntax for inserting multiple rows at one time?
- A predicate subquery:
- The command that would ensure that return visitors are only counted once is:
- Backup is a process of what?
- Stored procedures
- The First Normal Form (1NF)----(SG). GENERAL
- 1. In a well-designed relational database table, a column represents what?
2. The entity is represented how?
- To set up the INSERT to allow the programmer to copy rows from other tables:
- A login is a what?
- An inner join
- when you assign a DEFAULT value to a column?
- Numeric data types (2 types)
- What are three (common) types of data integrity? and what do they focus on?
- in a view can DML statements be used? are there limitations?
- If you do not include a WHERE statement in the DELETE statement: what happens?
- A programmer can enforce data integrity rules when making changes to the records in a database by:
- An INDEX
- 1. The ALTER VIEW statement is?
2. adding a WHERE clause does what in a view?
- what does a user account provide?
- 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?
- a Graphical Designer
- User-defined functions
- Normalizing a database to 2nd normal form. What 2 steps do you make?
- Which function do you use to provide the current date?
- Which clause is REQUIRED when issuing an UPDATE statement?
- a WHERE clause is used to do what, when updating.
(whats an example?)
- The backup strategies that only copies files that have changed since the last full backup is:
- 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?
- Do you need to set a value for a column that has a DEFAULT constraint when issuing an INSERT statement?
- 1. A user-defined function must contain which clause AND which statement?
2. What does the clause do?
3. what does the statement do?
- 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 stored procedure can modify what?
- The SET clause includes what, to be updated? (list two things)
- A cross join
- when a view definition contains a join:
- to control/change the physical sort order of a table what would you use?
- what does Access control do?
- What should be included in the physical security plan? (SG)
- 1. You would issue a DML statement to set, what?
2. DML statements do what?
- DELETE FROM (does what?)
- 1. What is a structure for storing database indexes?
2. In that structure, how are the database indexes stored?
- Replicated services, do? REPLICATED SERVICES
- A foreign key (SG)
- The SELECT INTO statement is used to do what? and what is important when issuing the SELECT INTO statement?
- You can update data if a condition is true and perform an alternative update if the condition is false with the:____statement
- If you group users by roles which statement would you use and what would that do?
- Which clause is specified first? the ORDER BY clause and the GROUP BY clause?
- By, default you can't specify a value for an identity column within an INSERT statement. Can you override that default? if so, how?
- a 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.
- b returns a Cartesian product (a result in which each combination of rows is represented).
- c data type store whole numbers
- d returns all matching rows from both tables and all unmatched rows.
- e that a function can be directly referenced in a SELECT statement. A stored procedure cannot be directly referenced in a SELECT statement or expression.
- f an explicit transaction.
- g Can assign a certain number to it, if that number is not reached the remaining space is not used.
- h 1. used to add rows to a different table.
2. the table must be empty when you execute the query
- i which users can do what action(s) to data in the database.
- j to return only the rows that appear in both tables, similar to a Boolean AND
- k 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.
- l A database role is assigned to users.
- m C! increased speed of writing records
- n 1. each column to be updated, along with..
2. an assignment operator (=) and the value to which the column should be updated.
- o to insert a new row in a table.
- p 1. the value of the first WHEN clause that is true.
2. if non is true, the result is the ELSE.
- q DML can be performed using the view, but only against one base table at a time.
- r Use the GRANT statement to provide access according to
- s DML statements can be performed using the view, but only on a single base table.
- t update existing records in a table.
- u 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.
- v exact and approximate
- w 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.
- x the INSERT statement uses the default value for the column
- y is used when a column references another column in the same table.
- z Allows whole numbers billion 4 bytes
- aa 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.
- ab 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
- ac performs an operation and returns a single value. (a function can't perform any modification on a permanent table)
- ad Security Attacks
- ae all files are copied for possible future retrieval.
- af 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.
- ag the getdate() function
- ah ensures and restricts who can connect and what they can do.
- ai Allows whole numbers 8 bytes
- aj It is arranged similarly to the index of a book, where the index value points to the actual information.
- ak Non-clustered index
- al optimize frequently-used queries by reducing the number of table scans required to locate data.
- am It is arranged in a special order to make retrieval of information faster with direct access to the information.
- an return a single value, calculated from values in a column.
- ao 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.
- ap 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.
- aq reverses changes
- ar The GROUP BY clause.
- as A! provide backups and operational continuity.
---it is not related to the INTERNAL security plan.
- at 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.
- au a schema-scoped securable. A schema-scoped securable is one that is created within a schema and can receive permissions through that schema.
- av Takes up more space but allows the use of multiple languages
- aw are compact segments of user-written SQL code that can accept parameters and return either a value or a table.
- ax 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.
- ay is a saved query that creates a virtual table from the result set of the query
- az 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.
- ba selects data and returns the results as a table.
- bb avoid large index keys for more optimal performance.
- bc 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.
- bd when all statements in a transaction have successfully completed, issuing a commit statement
- be the @ character
- bf deletes rows in a table
- bg are the individuals, groups, and processes granted access to SQL Server
- bh references the primary key in another table.
- bi CASE statement
- bj the SET clause
- bk to filter which rows will be updated.
(SET LastUpdateDt = getdate(), CreditLimit = 2500 WHERE CustID = 893)
- bl stores 1 or 0, true or false
- bm only files that have been changed since the last backup are copied.
- bn The physical security plan Should include:
location of servers with restricted entry access
- bo INSERT INTO table (columnl, column2) VALUES (val1a, val1b), (val2a, val2b);
- bp Storing the zip code only in the primary table and creating a separate table of city, state, and zip.
- bq Normalizing to INF requires that you eliminate repeating groups.
- br combines two or more SELECT statements with an AND function.
- bs are granted to a principal for every SQL Server securable
- bt The time data type stores only time information up to 100 nanosecond accuracy. It does not store a time zone offset.
- bu grant permissions to the database. Defined roles give all users within a group the same permissions.
- bv the data in permanent tables and return multiple values as output parameters.
- bw DDL
- bx 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.
- by re-create a full or incremental reproduction of the database.
- bz the COMMIT command records the changes to the database. if all statements are correct in a single transaction.
- ca UPDATE RENTALS SET kayak = RENTALS.kayak + EQUIPMENT.kayak FROM RENTALS,
- cb sets a few basic rules for a database: eliminate duplicative columns from the same table.
- cc is an application, such as Microsoft Access, that is object-oriented and drag-and-drop driven.
- cd to insert a new row in a table when a sub-select is used instead of the VALUES clause.
- ce are one-word commands that return a single value written in the command set of SQL.
- cf the CHECK constraints on the table are not considered,
- cg uses extended logical constructs in the WHERE clause using AND, OR, LIKE, BETWEEN, AS, and TOP
- ch The SET clause ( You specify the columns or variables that should be updated and, when a FROM clause is not also used, their values.)
- ci 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.
- cj to return a list of only the rows that are in both of the result sets generated by two other separate queries.
- ck are precompiled groups of SQL statements saved to the database.
- cl B. server files such as user security NAT
- cm 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.
- cn each row of a table is loaded to locate the records needed to generate the results.
- co differential backup
- cp To users and groups
- cq Add an Index! (large number of records, the benefits of adding an index out-weighs the necessary time and effort!)
- cr returns only distinct (unique) values.
- cs The timestamp data type does not store date and time information.
- ct order the results of a query.
- cu is a specific set of instructions for extracting/selecting particular data.
- cv Application roles are not granted and revoked like database roles.
- cw is used to query data from two or more tables.
- cx B! has keywords and pointers to the data
- cy UPDATE RENTALS SET value _ added _ tax = .10
- cz prevents rows that are referenced through a FOREIGN KEY constraint from being deleted. (Cascade related)
- da locked records
- db rows related by a FOREIGN KEY constraint to be deleted when the referenced row is deleted.
- dc the WHERE clause of a SELECT statement to return results with character column values that contain specific characters or specific character strings.
- dd 1. entity focuses on the primary key,
2. referential focuses on the foreign key
3. domain specifies that values must be declared.
- de 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.
- df The datetimeoffset data type stores the date and time, including an offset from Greenwich Mean Time (GMT).
- dg ensures that data is consistent, correct, and usable throughout the database.
- dh are the server, database, and objects a database contains
- di SELECT DISTINCT
- dj A column (is assigned a data type)
- dk as a ROW in a table
- dl (equals) = 2NF + removing columns that are not dependent upon the primary key.
- dm 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.
- dn allows you to perform an aggregate calculation over rows that have a matching value in a column.
- do the database must be available 24 hours a day, 7 days a week
- dp returns only matched rows from the two tables.
- dq stores A-Z or 0-9-any digit or letter that math functions will not be applied to.
- dr the data from the table is deleted
- ds the query performs a cross join and returns a Cartesian product.
- dt deletes all rows from the table, but does not log each row deleted.
- du ORDER BY column _ name (either ASC or DESC)
- dv addresses the safety of the actual location of the database.
- dw return a single value, based on the input value of a single field.
- dx 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.
- dy a single entity (Tables are used to store inform ation about unique entities)
- dz (equals) = 1NF + removing subsets of data that apply to multiple rows of a table and place them in separate tables.
- ea are a set of two or more statements grouped into a single unit.
- eb they allow multiple statements to be grouped together to avoid data integrity errors
- ec 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.
- ed The act or state of being included into a group or structure (a larger group). TO INCLUDE SOMEONE!
- ee nest inside another query.
- ef 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.
- eg serves as the unique identifier of a specific row and uniquely defines a relationship
within a database.
- eh ...
- ei A login is a server-level object that is used to grant the ability to establish a connection with an instance of SQL Server.
- ej data types store any real number
- ek to create a user-defined function. User-defined functions must return a value.
- el server-scoped securable. Server-scoped securables are available across all database instances.
- em 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.
- en 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)
- eo columns that are frequently used as join conditions, in WHERE clauses, and in GROUP BY clauses as key columns in an index
- ep is used to update rows in a table based on information stored in another table.
- eq The datetime2 data type stores date and time information at user- defined precision. However, it does not store a time zone offset.
- er 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
- es 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.
- et involves applying a body of techniques to a relational database to minimize the inclusion of
- eu OUTPUT keyword
- ev is the result of structuring (organizing) information to avoid redundancy and inconsistency and to promote efficient maintenance, storage, and updating.
- ew use the INSERT INTO with a SELECT...FROM clause
- ex combines two or more SELECT statements with an OR function.
- ey c. creation of primary and foreign keys
- ez specifies the rows to update. (also, used to specify criteria)
- fa NO
- fb 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
2.You then create foreign key relationships between the tables.
- fc Only files that have been changed since the last full_backup are copied.
- fd provides users with access to the database.
- fe automatically assign sequential integer values to a column.
- ff 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.
- fg the or operator and at least one field = true
- fh DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added.
- fi a single attribute of an entity.
- fj saving all critical data to re-create the database in useful form in a relatively short time.
- fk creates when-then-else functionality.
Give an example using the WHEN and THEN?
(WHEN this condition is met THEN do this.)
- fl 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.)