ITS Database Reviewer

ADD

only _____ is reserved keyword in SQL server 2008

INNER JOIN

returns rows when there is at least one match in both tables.

DATEDIFF

returns the number of days between two date values.

RETURN keyword

not necessary in the stored procedure as OUTPUT implies the value to be returned.

DBCC CHECKCATALOG

performs various consistency checks between system metadata tables.

DENY

refuses permission to principal

REVOKE

removes user access rights to db objects

Character Data

sorted using rules that define the correct character sequence with options for specifying case sensitive, accent marks, and character width. The other options refer to Data warehousing, normalization and linked servers.

AS BEGIN

starts with the stored precedure with the subsequent SELECT statement selecting the orderID based on the orderdetailID that is input

DBCC Maintenance

tasks on a database, index, or filegroup.

DBCC Miscellaneous

tasks such as enabling trace flags or removing a DLL from memory.

BIT

the ____ type allows two values in a field: 0 and 1.

WITH GRANT OPTION

to allow a granted resource the ability to grant the same permission to others

Foreign Key

used to build relationships between two tables

UNIQUE

used to ensure a value is unique in a field when entering data into that field.

DROP VIEW view_name

what SQL statement will remove view from database w/o removing any of underlying data the view shows?

a table containing only one FOREIGN key

what could not be used as primary key?

relational database management system (RDBMS)

what is RDMS / RDBMS

View

A ______ can restrict user to specific rows or columns in a table

Data Control Language

DCL is ____________ includes GRANT and REVOKE

DBCC CLEANTABLE

Reclaims space from dropped variable-length columns in tables or indexed views.

TRUNCATE TABLE

-delete data in table, not table -removes all rows from table w/o logging the individual row deletions

TRUNCATE TABLE

-removes all rows from a table without logging the individual row deletions. -faster than DELETE statement, uses fewer system and transaction log resources

non-aggregated fields

A GROUP BY clause MUST contain all _____________________ in a query.

HAVING , WHERE

A GROUP BY clause can be present without a ________ and __________ clause.

CROSS JOIN

A ________ _____ produces a cartesian product, which is the relating of every record from one table to every record in another table.

copy backup

A ________________ is similar to a full backup except that transaction log files are not deleted after the backup. A _____________ is used to create a full backup of the Exchange Server database without disrupting any backup procedures that use an incremental or differential backup. from IBM

Referential integrity

A foreign key ensures ____________ by ensuring only valid data is stored

defines a new procedure A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. :from w3Schools

CREATE PROCEDURE, what is it?

False

ALTER TABLE can modifty data type of existing column, true or false

False

ALTER Table can change IDENTITY specification of an existing column

uninterruptible power supply (UPS)

An _____________________________________ is like a surge protector with a battery backup. If you experience a power interruption, devices connected to the ____ will stay powered on as long as the battery lasts.

Deleting an order deletes all the details of the order.

An orders table is used as a lookup to an orderdetails table with the orderID column from an orders table as the column used to establish the relationship between the two tables. Which type of deletion will cause a cascading delete between the two tables?

Every non-prime attribute is non-transitively dependent on every candidate key in the table

An organization storing climate data needs to have its database tables be in third normal form. Which statement best defines a table in third normal form?

setupadmin

Can configure linked servers, extend stored procedures and the start-up stored procedure. This is a Fixed server role

Diskadmin

Can create, alter and drop disk files

fixed-length string

Char(size) and Varchar(size) holds a

DBCC CHECKCONSTRAINTS (Transact-SQL)

Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKTABLE

Checks the integrity of all the pages and structures that make up the table or indexed view.

Data Definition Language

DDL is _________ includes CREATE, ALTER, DROP

DROP keyword should be used instead of DELETE

DELETE view UtahCustomers. A dev runs this statement and gets error message that it's unable to be deleted. What is most likely to cause the error?

Data Manipulation Language

DML is _________ includes UPDATE, INSERT, DELETE

Data Query Language

DQL is _____________ it includes SELECT

- Maintenance - Miscellaneous - Informational - Validation

Following categories of Database Console Commands are:

false

INSERT INTO customers (lastname, firstname) SELECT lastname, firstname from CustomersNew True or false: There is a syntax error in this statement

optional

It is ____________ for stored procedure to return a value

collation

It refers to a set of rules that determine how data is sorted and compared.

Stored procedures are pre-compiled objects which are compiled for first time, and its compiled format is saved which executes whenever it is called. Function is compiled and executed every time it is called.

Major difference between stored procedure and a function

E. TAble

On which database structure does an insert statement operate? A. Role B. Trigger C. User D. Stored procedure E. Table

FULL OUTER JOINR

Returns rows when there is a match in one of the tables.

DBCC Informational

Tasks that gather and display various types of information.

Referential integrity, which is the assurance that a value exists in a parent of a table relationship before a child value is allowed, is being enforced.

What is Referential Integrity?

Database Console Commands

The Transact-SQL programming language provides DBCC statements that act as _________________________ for SQL Server.

Truncate Table

The _________________________ command is used to delete all the records from an existing table by reinitializing the table's structure. This command instructs the database to deallocate the space for all records in a table and change this table's structure by resetting the table size.

ORDER BY

The clause ______ __ is not allowed in the CREATE VIEW statement

Compound key

The primary key created for this table is a ________________, as both fields used for the primary key are primary keys in other tables.

account lockout after a reasonable number of failed attemps

To prevent this type of attack (COOLCOOL, COOLDOOL, COOLER, COOLGIRL, COOLGUYS33, etc...), you should enable

- can add new column - can drop multiple columns from a table - can modify data type of an existing column - it cannot change IDENTITY specs of existing column

What can ALTER TABLE do?

It's used to group tables within a database for permission purposes.

What does schema do?

GRANT EXECUTE ON sp_current_sales TO inside_sales

What statement gives the inside_sales team the ability to execute the sp_current_sales stored procedure?

cascading deletes

When _____________________ are allowed, a deletion of a record from a parent table causes all matching records in a child table to be deleted.

SET NOCOUNT ON

When you use ______________________, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results.

GRANT SELECT ON vw_sales_today TO sales_manager WITH GRANT OPTION

Which statement allows the sales_manager role to run the vw_sales_today view and allow the manager to give others the same permission?

non-sequential

Which type of index changes the order in which the data is stored in a table?

Dictionary Attack or Brute Force Attack

Word lists are used to guess commonly used passwords

composite key

is a primary key that uses two or more fields from a table

Security identifier

is used to uniquely identify a security principal or security group.

Data warehousing, normalization and linked servers.

it is a data design and organization process applied to data structures, based on rules that help build relational database.

DISTINCT

keyword returns each combination of matching values for columns specified in a query only once.

No rows will be deleted.

You execute a statement inside a transaction to delete 100 rows from a table. The transaction fails after only 40 rows are deleted.What is the result in the database?

REVOKE SELECT ON customer FROM User1

You need to disable User1's access to view the data in the Customer table.Which statement should you use?

Roles

______ contain sets of permissions that can be granted to users and groups in a database.

REVOKE

_________ command removes granted permissions to an object

DBCC Validation

__________ operations on a database, table, index, catalog, filegroup, or allocation of database pages.

full backup

___________ backs up the specified database and associated transaction logs. The Exchange Server deletes the committed log files after the integrity of the database and logs are verified and backed up. If the database is not mounted, the backup fails and the transaction logs are not truncated. from IBM

Function Stored procedure

___________ can have input parameters ____________ can have input/output parameters

incremental backup

____________ backs up only transaction logs from IBM

Granting Permission

________________ on an object involves using a GRANT keyword, to action a grant.

stored function

_________________ cannot be called on a function

differential backups

__________________ backs up transaction logs. The log files are not deleted. When you run a full backup followed by only ___________________, the last full backup and the latest ______________________ contain all the data that is required to bring the database back to the most recent state. from IBM

Data warehousing, normalization and linked servers.

a concept of SQL Server by which we can add other SQL server to a group and query both the SQL servers dbs using T-SQL statements

relationship

a data connection between two tables using a field with like values.

Candidate key

a field that can be used as a primary key

full backup and snapshot backups

backups entire copies of database

- more efficient data storage as numbers use fewer bytes than text - numbers and dates can be used in calculations - more restrictions in entering data as, for example, a date field will require a date - constraints can be placed on most data types, not just text

benefits of using proper data type to define columns when creating a table in a database include:

- speed - code reuse - security - reduced traffic between client and server

benefits of using stored procedure:

user defined functions

can be used in select, where, or case statements. they also can be used to create joins

Dbcreator

can create, alter, drop and restore databases

serveradmin

can issue SHUTDOWN command

Bulkadmin

can perform bulk insert operations

GRANT

command used to provide access on db objects to users

Composite Keys

consists of two fields but the fields are not primary keys in other tables.

column

defines a field for a table

TRUNCATE TABLE

empties the table of all its data

repeating groups

first normal form requires that database exclude ________ ________

serveradmin

fixed server role can shut down SQL server

normalization

helps makes data less redundant and less dependent within database tables

5

how many levels of normal forms are there?

Data warehousing, normalization and linked servers.

means that the data in the db is organized so that all the data elements relating to the same real-world event or object are linked together.