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.
