235 Multiple choice questions
- Database objects, DML
- Changes existing data in one or more rows in a table or view(changes the data in the table)
- Isolate semantically related multiple relationships
a database object that can accept values, perform a calculation and
return the result to the caller. Can be directly referenced using a
- Performs insert, update, or delete operations on a target table based on the results of a join with a source table.
- A query and functional programming language designed to query collections of XML data.
- When the foreign key refers to columns in the same table.
a table definition by altering, adding, or dropping columns and
constraints, reassigning partitions or disabling or enabling constraints
and triggers.(cannot change IDENTITY constraint)
- DML or DDL
- Return a single value, calculated from values in a column. Ex. COUNT
- update columns with calculated values
- A single entity attribute
- Isolate independent multiple relationships
- When a schema is created through a series of successive refinements, starting with the first schema.
- 1. If a view definition contains the DISTINCT keyword, rows cannot be deleted through the view.
2. The WITH OBJECT IDENTIFIER clause is used to specify a top level (root) object view.
3. The OR REPLACE option is used to change the definition of a existing view without dropping and recreating.
is the primary means of programming and manageing SQL Server. When you
use an SSMS to perform an action, it is using T-SQL commands in the
background to do the work.
- Creates a SQL Server database object (table, view, or stored procedure)
- To check all pages in a DB are correctly allocated
- TRUNCATE command; does not log each row
you the final result set where values in both of the queries match by
the query on both the left and right sides of the operand.
• The number and order of the columns must be the same in all queries.
• The data types must be compatible.
- Selects data and returns the results as a table. Cannot modify any permanent tables.
- It specifies a percentage that indicates how much free space will be in the leaf level of each index page.
- A-z or 0-9(any digit or letter that math functions will not be applied to)
one row for each function, constraint or trigger created within a
database. Indexes are not stored in this table(sysindexes).
- CREATE TABLE
- shows line stating the number of rows "affected". This is off by default. This will suppress the '(1 row affected)
- preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table
in a UPDATE statement when you need to update the values of columns
that have a large object data type such as varchar(max) or
requires users to specify which data is needed and how to obtain it, it
is a low-level DML, it requires users to know the data structure used
in the db.
- The LIKE operator is used together with the % wildcard character
- Fixed, N
- returns all rows from the left table (table1) and from the right table (table2).
a single value, based on the input value of a single field(EX: UCASE -
Returns the value of the field in all upper case).
reserved Keyword and cannot be used as a object name or column name in a
table unless delimited. Add is used to add a new column in a alter
relational database is similar to a hierarchical database in that data
is stored in tables and any new information is automatically added to
the table without the need to reorganize the table itself. Minimizes the
amount of redundant data stored in the database. database can have
- Server, Database and Table
- • A subset of rows or columns of a base table
• A union of two or more tables
• A join of two or more tables
• A statistical summary of base tables
• A subset of another view or some combination of views and base tables
- A SQL procedure that initiates an action when an event(INSERT, DELETE or UPDATE) occurs.
- Columns frequently uses as a query join conditions.
- are designed to return distinct values by comparing the results of two queries.
- Storage and Programmability
- Specifically identifies which column should not contain duplicate values.
- MDF = Primary data files, NDF = Secondary data files and LDF = Log files
that they can be accessed by multiple users and provide a higher level
of performance. One popular database server is Microsoft SQL Server.
- used to create a user-defines data type or an alias data type.
- sys schema
- 1MB = 1024KB
1 record is 165 bytes - Multiply by amt of records.
the maximum total number of decimal digits that can be stored in a
numeric data type, both to the left and to the right of the decimal
point; this value must be at least 1 and at most 38. The default
precision number is 18.
Check(domain Integrity), Default(domain integrity), Not Null, Primary
Key(entity integrity), Foreign Key(referential integrity)
method of coding that enables a user to focus on the coding rather than
the syntax for a specific database API, allowing them to use generic
methods of access as long as they have the additional codes or libraries
which fill in the blanks.
- 1 or 0, true or false
- To ensure all child tables are deleted inf the parent is deleted. This can be your FOREIGN KEY constraint.
- Can create, alter, and drop disk files. This is a Fixed server role
- A DDL statement used to remove objects, such as tables, views, stored procedures, and triggers , from a database.
- one, two
- can Configure server -wide settings and issue SHUTDOWN command which immediately stops the SQL server.
- the functions cast or convert specifically.
- Integrity between tables using relationships.
character data converted from numeric data. The second parameter is for
the total length of the output including the decimal point, signs,
digits, and spaces. output is round as specified in the third parameter.
be used with a previously taken full backup to recover a database. you
can add a NO RECOVERY clause if you plan to apply a transaction log of
differential backup as part of your recovery process.
- are the most common SQL Server data types used to store numeric information.
- Building a working model of the DB system in order to suggest improvements or add new features.
- Changes the database context
all occurrences of the second given string in the first string
expression with a third expression(SELECT REPLACE('The best TTS
Solution','s','z') - The bezt TTZ ..
- Available across all database instances(network)(LOGIN)
that includes all columns used in the query. Can optimize performance
because the query can be generated from the index without accessing the
actual tables involved in the query.
a result set that contains all the combinations of rows from two tables
that do not have a column in common, also know as a Cartesian product.
WHERE conditions should always be included.
- to specify sorting rules that are different than those used for other columns in the table.
- Logical DB design
- A database will be created.
to ensure that no changes to the underlying data can be made through
the view that would cause data no to conform to the definitions of the
- Generates a continuous chain of transactions without stopping until completed
- can perform backups, checkpoints, and DBCC commands, but not restores(only sysadmins can perform restores)
- Top-down, Bottom-up, Inside-out, Mixed
- Used to insert a default value into a column.
- CREATE, DROP, ALTER
- Physical database design
- SELECT AVG("column name") FROM "table name"
- Storage and Programmability
- series of steps(or rules) called "forms" the more steps you take, the more normalized your tables.
- can issue DDL commands(statements)(create, alter,drop)
command line application that comes with SQL and exposes the management
features of SQL Server. It allows SQL queries to be written and
executed from the command prompt.
- contains one row for each index and table in the database.
process of extracting trails on a regular basis so they can be
transferred to a designated security system where the database admins do
not have access, this ensures a certain level of separation of duties
and provides evidence that the audit trails were not modified.
- 1. Columns contain a large number of distinct values
2. Columns are accessed sequentially
3. Queries return large result sets
- physical structure in non clustered index
is a column in one table that points to the primary key in another
table(defines relationship between two tables). Ensures domain and
referential integrity, not entity integrity. There is not a predefined
limit for foreign keys.
- Changes an existing object(changes the object definition)
- 1. Create a full text catalog in the database
2. Create a full text index on the column in question
- Blocks modifying data in any table in the database. Will override any object-level grant
- UNION; without any duplicates. Duplicates require UNION ALL.
- Database can parse out redundant storage and information obtained from various relational spreadsheets.
users of members of a role from performing an action on a secure-able
object, such as a view, or stored procedure. even if permission is
grated directly through membership in another role.
- used to optimize operations that use spatial data types, such as geometry and geography data types.
- To commit changes made within a manual transaction to the database.
- Command will remove a windows user or group from the SQL server. This will not alter Windows its self
- Requirements and Evolutionary
- A database role is assigned to users
- Database performance
- A function
- INNER JOIN
- To automatically assign sequential integer values to a column
- must contain a RETURNS clause and a RETURN statement.
- Column, localvariable, expression and parameter.
will only produce matching rows from both tables while OUTER will join
all rows from both tables whether they match or not.
- used to define a clustered or non clustered index.
- Because it may be impossible to verify the constraints if a foreign key consists of two or more columns if one of them is NULL.
- 2 bytes, This allows 65,536 characters at almost any language.
- Column; limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered.
- Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server.
- The function of the is to change data in a table or view
type of bottom-up approach, the inside-out method begins with
identifying a few important concepts then proceeds outward radially.
- • Specific rows of tables
• Specific columns of tables
• Specific rows and columns of tables
• Rows obtained by using joins
• Statistical summaries of data in given tables
• Subsets of another view or subsets of views and tables
to a set of rules that determine how data is sorted and compared. By
default, SQL Server has predefined collation precedence. If you wish to
override how data is being sorted, you must use a collation clause.
- worsens, because it's constantly sorting it. Improves it for retrieving it though. Physical sort. Only 1 per table.
- Includes only a single SELECT statement. Can accept parameters.
the process of stepping back through the normalization steps until you
get something that is "comfortable". Improves data retrieval
- transaction design and interface design
- (stored procedure)one that is created within a schema and can receive permissions through that schema.
- 1NF - Eliminate duplicative columns from the same table.
-Create separate tables for each group of related data and identify each row with a unique column (the primary key).
2NF - Move repeating fields into a new table that contains a primary key, and relate it back to the old table using a foreign key. (requirement that each field value in a table is associated with only one row)
3NF - Eliminate columns not- transitively dependent (useless) on key
4NF - all in table are dependent on superkey
5NF - every join is implied by superkeys of the table
the starting position of the specifies expression in a character
string. The third parameter of the function is the character position to
start searching for the first parameter in the second parameter. If
starting ocation is a negative number, search starts at beginning of
which combine columns from multiple different tables. JOIN statements
can be specified in either the FROM or the WHERE clause, but it is
recommended that you specify them in the FROM clause.
- Primary key constraints and Clustered indexes
- CREATE LOGIN
a row for each possible combination of results from the other tables.
the number of rows returned will equal the number of rows in one table
multiplied by the number of rows in the other table.
feature of a transaction processing system which enables DB's to be
returned to the pre-transaction state if some error condition occurs.
All databases are updated or none of them are.
- Use the CREATE or REPLACE VIEW command to redefine it.
- A function can be directly referenced in a select statement, and return only a single scalar or table vaule.
- is used to obtain only subset of rows that fulfill criterion in the WHERE clause
- Can read from any table in the database, this deny will override an object-level grant
- A primary key, a UNIQUE index and a CLUSTERED index
s-scoped secure-able. Created within a database and provides a
namespace and a security container for database objects. Each
secure-able object must have a unique name
indexes are not copies of the table but a sorting of the columns you
specify that "point" back to the data pages in the clustered index. Add
non-clustered indexes for queries that return smaller result sets
the time it takes to build the indexes and the storage space the
indexes require. Note: each table can have only one clustered index ,
data can be sorted in only one way.
have AND; can only admit two expressions(between(10000 and 50000)) cant
add another between(10000 and 50000) and (60000 and 90000)
- Real numbers
similar to a tree structure (such as a family tree). Each parent table
can have multiple children, but each child table can have only one
- The INSERT statement uses the default value for the column.
- allows you to limit the types of data a user can insert into a the database and that they meet a certain condition.
- is simplistic in design. They are two dimensional tables that are consisting to rows and columns. for fast searches.
one or more new rows to a table or a view in SQL Server. Must contain a
table or view name into which to insert the data. Can be used to insert
explicit values into a table or to insert the result set returned by a
query into a table.
simply a virtual table consisting of different columns from one or more
tables. Unlike a table, a view is stored in a database as a query
object; therefore, a view is an object that obtains its data from one or
more tables. View is a database object that can be used in SELECT
queries just like a table. When a view contains a join, DML can be
preformed but only a single base table.
- filter the results grouped by a GROUP BY clause.
- is a data structure that improves the speed of data retrieval operations on a database table.
- ALTER INDEX ALL ON dbo.OrderDetails REBUILD WITH (FILLFACTOR = 60);
- 1 byte, 256 possible characters.
- Can create, alter,drop, and restore databases
- Instances of SQL server, Data-Tier apps, Databse Files, Volumes
- valued function includes only a single SELECT statement.
is the 24 hr clock without time zone awareness(Minimizes storage but
can store time the includes hours, minutes, seconds but not fractional
Date is used to define 1/1 to 12/31 9999
Datetime is accurate to .00333 seconds(1/1 1753 to 9999)
datetime2 is accurate up to 100 nanoseconds
datetimeoffset includes daylight savings time
smalldatetime(1\1 1900 to june 6, 2079)(2 bytes) does not keep track of seconds.(cant use neg numbers)
Time(3) stores fractional seconds, however increases storage space.
timestamp - is uses for row versioning, not to store time data.
- The number of columns.
- To cause the result set to be sorted by the value in a specific column or columns. Not allowed in a CREATE VIEW Statement.
- Would prevent a parent row from being deleted if it had a related child rows in another table.
- Gives a database user or group(defined with a role) specific permissions to data objects. Uses ON clause preceding it.
- Backup copy of only specific file groups.
- False, NULL is not a constraint
- A column of the uniqueidentifer data type. You need to use the NEWID() function in the values list to generate the value.
modify the data in permanent tables and return multiple values as
output parameters. It doesn't control how underlying data is physically
accessed. Can return a value to the caller.
- occurs when you define more than one column as your primary key.
- Grammar and rules governing the structure and content of statements.
- Is optimized to handle a large number of simultaneous updates additions and deletions.
- Set the fillfactor to 60.
- • Columns to retrieve
• Tables to retrieve the columns from
• Conditions, if any, that the data must satisfy
you produce individual lists, but the result may be that you get all
the information you need but in an individual list format.
uniquely identifies a column in the db. Has to have a unique constraint
by default. Cannot be Null and data type must match. Ensures entity
integrity(Uniquely id each row). Not always created as CLUSTERED indexes
if one is already made.
- Checks all tables file group for any damage
- It's used on the primary key to automatically start with 1 and auto-increment by 1.
scoped object and not securable) is a data structure that improves the
speed of data retrieval operations on a database table. The disadvantage
of indexes is that they need to be created and updated,.
a full-text index on a table or indexed view in a database. Only one
full-text index is allowed per table or indexed view, and each full-text
index applies to a single table or indexed view
- to create a computed column that can be included in an index.
- ensures that data is entered in to a cell(cant be blank).
- returns when there is at least one match in both tables.
user access rights or privileges on the database objects granted using
the GRANT statement, but does not prevent permissions from being
obtained through membership in other roles.
- gives you the final result set where data exists in the first query and not in the second dataset.
you to modify data stored in tables using data attributes. Note: If you
don't specify what records to update, all records or rows will be
updated with the new value. To update another table use FROM clause. In
this, SET specifies column to be updated.
filter the results grouped by a GROUP BY clause. Can only reference
aggregate functions or column that are also specified in the GROUP BY
- returns character data converted from numeric data.
that returns the same predictable value when called with same
nvarchar(3500). Meets requirements using least amt of storage. Supports
multiple language input parameters.
- A select statement that is nested within another SQL statement.
- Windows user logon, Membership in a windows group, SQL server logon
- ensures the tables in the DB and the indexes are correctly linked.
- When the server is stopped.
- Can configure linked servers, extend stored procedures and the start-up stored procedure. This is a Fixed server role
- You can identify and delete individual rows from the database
- Allows the admin to limit the types of data a user can insert into that column of the database.
- A code injection technique that exploits security vulnerabilities in the DB layer of an application.
- Removes rows from a table or view(, which deletes data from within a table) Use for smaller databases
primary key is replicated from the primary to secondary table, and all
the key attributes duplicated from the primary table are known as the
- sysadmin, db_owner
- An explicit transaction is one where all events of the transaction either happen together or they don't take place at all.
- 1MB - 1024 KB - 1 field - 165 bytes. a 100k table - = 16113.28125 kb(15.73563 mb)
decimal and numeric. Transact-SQL data types that have a fixed
precision and scale. money and smallmoney are Transact-SQL data types
you would use to represent monetary or currency values.
- Function returns the maximum value of an expression(IE: The maximum salary of all employees)
- MIDUS: MERGE, INSERT, DELETE, UPDATE, SELECT
- In computer programing, create, read, update and delete (CRUD)are the four basic functions of persistent change
- Wild Card character(IE: WHERE lastname LIKE 'J%'); LIKE operator is used with %
- users don't need to learn yet another password and because it leverages the security design of the network.
- MS SQL Server
to ensure that none of the tables references by the view are dropped or
have their structure changes such that the view would not function
properly. Cannot use asterisk in SELECT list., must specify column by
- Ensures domain integrity by ensuring each record has a column value if one is not assigned.
- Breaking down the smaller components so that each describes a basic fragment.
- huge numbers(way bigger than millions) = bigint
Big numbers= int(-2,147,483,648 to 2,147,483,647)Can store dates YYYYMMDD(4 bytes)
small numbers = smallin(-32768 to 32,767 however req 2 bytes of space)
smaller numbers = tinyint(max value of 255 with minimum storage 1 byte)
lots of text = varchar(expands as needed) YYYYMMDD(8 bytes)
text containing Unicode symbols(diff language or symbols) = nvarchar
currency = smallmoney(-214,748.3648 to 214,748.3647 (4 decimal places))
yes/no = bit(value of 0,1 or null)
Float = precision and scale(best for finding percent %)
decimal(3) - 5 bytes
- Multiple tables that are related using primary and foreign keys
- Relationship; Enforces referential integrity
indexes are not copies of the table but a sorting of the columns you
specify that "point" back to the data pages in the clustered index. Add
non-clustered indexes for queries that return smaller result sets.
- used to specify query hints that will allow for optimization of a query's execution.
- Use BEGIN TRAN
- is a database-specific role intended to allow an application to gain access regardless of the user.
Views allow you to limit the type of data users can access. You can
grant view permissions in designated tables, and you can also choose to
deny permissions for certain information.
- Views reduce complexity for end users so they don't have to learn how to write complex SQL queries. Instead, you can write those queries on their behalf and hide them in a view.
- Prior to the Logical design phase.
- You would get all values for D, E but not F. (MUST HAVE AND IN BETWEEN STATEMENT)
- 1. It does not have any usage of SQL group functions or grouping of data.
2. DML operations are allowed on the view
3. It fetches data from one database table only
creating a stored procedure, this can be used to allow the person
running the SP to have the same permissions as the person who owns the
SP. This is better than granting SELECT to the user.
- ALTER TABLE tablename
amount of selects on a table, create a clustered index on the primary
key of the table. This index should be the most common column that is in
your WHERE clauses for queries against this table. So if most of the
time you search by primary key, then leave it as the default. But if you
search by DateCreated or LastName most of the time on this table, then
you might want to consider changing the clustered index to this column
instead. Each table can only have 1 clustered index. (faster searching)