New

Long-Term Learning

Learn efficiently and remember over time.

Start Long-Term Learning

Get personalized study reminders at intervals optimized for better retention.
Track your progress on this set by creating a folder
Or add to an existing folder

Add this set to a folder

  • Numeric data types (2 types)

    exact and approximate

    bigint

    Allows whole numbers 8 bytes

    int

    Allows whole numbers billion 4 bytes

    Data Type

    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.

    Integer

    data type store whole numbers

    Floating-Point

    data types store any real number

    Character

    stores A-Z or 0-9-any digit or letter that math functions will not be applied to.

    Boolean

    stores 1 or 0, true or false

    a View

    is a saved query that creates a virtual table from the result set of the query

    a Query

    is a specific set of instructions for extracting/selecting particular data.

    a Graphical Designer

    is an application, such as Microsoft Access, that is object-oriented and drag-and-drop driven.

    Functions

    are one-word commands that return a single value written in the command set of SQL.

    Aggregate functions

    return a single value, calculated from values in a column.

    Scalar functions

    return a single value, based on the input value of a single field.

    User-defined functions

    are compact segments of user-written SQL code that can accept parameters and return either a value or a table.

    Stored procedures

    are precompiled groups of SQL statements saved to the database.

    The command that would ensure that return visitors are only counted once is:

    SELECT DISTINCT

    The command that returns an alphabetical list is:

    ORDER BY column _ name (either ASC or DESC)

    The condition that always results in true is:

    the or operator and at least one field = true

    DISTINCT

    returns only distinct (unique) values.

    Boolean logical operators (AND,OR,Truth tables)

    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.

    A predicate subquery:

    uses extended logical constructs in the WHERE clause using AND, OR, LIKE, BETWEEN, AS, and TOP

    The difference between UNION and JOIN is:

    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

    The INTERSECT query is used:

    to return only the rows that appear in both tables, similar to a Boolean AND

    UNION

    combines two or more SELECT statements with an OR function.

    JOIN

    is used to query data from two or more tables.

    Subqueries

    nest inside another query.

    INTERSECT

    combines two or more SELECT statements with an AND function.

    When data is missing from a particular column, if you use the INSERT statement what happens to those column?

    the INSERT statement uses the default value for the column

    To set up the INSERT to allow the programmer to copy rows from other tables:

    use the INSERT INTO with a SELECT...FROM clause

    What is the correct syntax for inserting multiple rows at one time?

    INSERT INTO table (columnl, column2) VALUES (val1a, val1b), (val2a, val2b);

    The INSERT INTO statement is used (when?)

    to insert a new row in a table.

    The INSERT INTO with SELECT statement is used (when?)

    to insert a new row in a table when a sub-select is used instead of the VALUES clause.

    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:

    UPDATE RENTALS SET value _ added _ tax = .10

    UPDATE is used to what?

    update existing records in a table.

    You can update data if a condition is true and perform an alternative update if the condition is false with the:____statement

    CASE statement

    To update data in one table from another table, the developer should use: (what syntax?) (Rentals,equipment)

    UPDATE RENTALS SET kayak = RENTALS.kayak + EQUIPMENT.kayak FROM RENTALS,
    EQUIPMENT

    What does a WHERE clause in an UPDATE statement do?

    specifies the rows to update. (also, used to specify criteria)

    What does the CASE statement create?

    creates when-then-else functionality.
    Give an example using the WHEN and THEN?
    (WHEN this condition is met THEN do this.)

    1. What is the value of the case expression?
    2. What is the result if none is true?

    1. the value of the first WHEN clause that is true.
    2. if non is true, the result is the ELSE.

    If you do not include a WHERE statement in the DELETE statement: what happens?

    the data from the table is deleted

    Transactions are useful when updating/deleting/adding records to a database because:

    they allow multiple statements to be grouped together to avoid data integrity errors

    A programmer can enforce data integrity rules when making changes to the records in a database by:

    when all statements in a transaction have successfully completed, issuing a commit statement

    DELETE FROM (does what?)

    deletes rows in a table

    What are TRANSACTIONS?

    are a set of two or more statements grouped into a single unit.

    What does ROLLBACK do?

    reverses changes

    If all statements are correct within a single transaction what command records it to the database?

    the COMMIT command records the changes to the database. if all statements are correct in a single transaction.

    Securables

    are the server, database, and objects a database contains

    Principals

    are the individuals, groups, and processes granted access to SQL Server

    Permissions

    are granted to a principal for every SQL Server securable

    Data types are assigned and stored where?

    A column (is assigned a data type)

    What are characteristics of the Unicode data type?

    Takes up more space but allows the use of multiple languages

    What is meant by a data type- variable length

    Can assign a certain number to it, if that number is not reached the remaining space is not used.

    How can a database of addresses be simplified?

    Storing the zip code only in the primary table and creating a separate table of city, state, and zip.

    Normalization-(from study guide)

    involves applying a body of techniques to a relational database to minimize the inclusion of
    duplicate information.

    What does the word inclusion mean?

    The act or state of being included into a group or structure (a larger group). TO INCLUDE SOMEONE!

    Normal Form (Study guide answer). What does it try to avoid? What does it promote?

    is the result of structuring (organizing) information to avoid redundancy and inconsistency and to promote efficient maintenance, storage, and updating.

    The First Normal Form (1NF)----(SG). GENERAL

    sets a few basic rules for a database: eliminate duplicative columns from the same table.

    The Second Normal Form (2NF)----(SG) GENERAL

    (equals) = 1NF + removing subsets of data that apply to multiple rows of a table and place them in separate tables.

    The Third Normal Form (3NF)----(SG) GENERAL

    (equals) = 2NF + removing columns that are not dependent upon the primary key.

    What are three (common) types of data integrity? and what do they focus on?

    1. entity focuses on the primary key,
    2. referential focuses on the foreign key
    3. domain specifies that values must be declared.

    A primary key (SG)

    serves as the unique identifier of a specific row and uniquely defines a relationship
    within a database.

    A foreign key (SG)

    references the primary key in another table.

    Data integrity (SG)

    ensures that data is consistent, correct, and usable throughout the database.

    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?

    Add an Index! (large number of records, the benefits of adding an index out-weighs the necessary time and effort!)

    Which type of index has keywords & pointers to the data, but does not contain the actual data pages or records?

    Non-clustered index

    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

    B! has keywords and pointers to the data

    Which is Not a result of using indexes?
    a. increased speed of data retrieval
    b. increased storage requirements
    c. increased speed of writing records

    C! increased speed of writing records

    An INDEX

    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.)

    1. What is a structure for storing database indexes?
    2. In that structure, how are the database indexes stored?

    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.

    How is a non-clustered index arranged? (SG)

    It is arranged similarly to the index of a book, where the index value points to the actual information.

    How is a Clustered index arranged? (SG)

    It is arranged in a special order to make retrieval of information faster with direct access to the information.

    If you group users by roles which statement would you use and what would that do?

    Use the GRANT statement to provide access according to
    their role.

    What should be included in the physical security plan? (SG)

    The physical security plan Should include:
    location of servers with restricted entry access

    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

    A! provide backups and operational continuity.
    ---it is not related to the INTERNAL security plan.

    privilege escalation and SQL injections are examples of what?

    Security Attacks

    A security plan identifies what?

    which users can do what action(s) to data in the database.

    A physical security plan does what?

    addresses the safety of the actual location of the database.

    what does Access control do?

    ensures and restricts who can connect and what they can do.

    what does a user account provide?

    provides users with access to the database.

    What do Roles do, regarding security?

    grant permissions to the database. Defined roles give all users within a group the same permissions.

    it is important that backup software should work with what?

    locked records

    The backup strategies that only copies files that have changed since the last full backup is:

    differential backup

    In which situation is the use of a replication service recommended?

    the database must be available 24 hours a day, 7 days a week

    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

    B. server files such as user security NAT

    Backup is a process of what?

    saving all critical data to re-create the database in useful form in a relatively short time.

    In a full backup what happens? FULL BACKUP

    all files are copied for possible future retrieval.

    in an Incremental Backup, what takes place? INCREMENTAL

    only files that have been changed since the last backup are copied.

    a differential backup, back ups what? DIFFERENTIAL

    Only files that have been changed since the last full_backup are copied.

    Replicated services, do? REPLICATED SERVICES

    re-create a full or incremental reproduction of the database.

    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

    c. creation of primary and foreign keys

    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?

    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.

    IDENTITY

    automatically assign sequential integer values to a column.

    Do you need to set a value for a column that has a DEFAULT constraint when issuing an INSERT statement?

    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.

    DEFAULT constraint

    ...

    When no join is provided in a query, what happens?

    the query performs a cross join and returns a Cartesian product.

    The ON DELETE CASCADE option causes, what to happen?

    rows related by a FOREIGN KEY constraint to be deleted when the referenced row is deleted.

    What does the ON DELETE NO ACTION option do?

    prevents rows that are referenced through a FOREIGN KEY constraint from being deleted. (Cascade related)

    what is one difference between an application role and a database role?

    A database role is assigned to users.

    A database role is assigned to who?

    To users and groups

    What do application roles allow access to?

    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.

    What is ONE difference between application roles and database roles?

    Application roles are not granted and revoked like database roles.

    Normalizing 1st form

    Normalizing to INF requires that you eliminate repeating groups.

    Normalizing a database to 2nd normal form. What 2 steps do you make?

    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 table.
    2.You then create foreign key relationships between the tables.

    How does the third normal form (3NF) take normalization a step further than 2NF?

    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.

    Which clause is REQUIRED when issuing an UPDATE statement?

    the SET clause

    Which function do you use to provide the current date?

    the getdate() function

    The SELECT INTO statement is used to do what? and what is important when issuing the SELECT INTO statement?

    1. used to add rows to a different table.
    2. the table must be empty when you execute the query

    when you assign a DEFAULT value to a column?

    DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added.

    when you assign a CHECK (constraint) value to a column?

    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.

    The SET clause includes what, to be updated? (list two things)

    1. each column to be updated, along with..
    2. an assignment operator (=) and the value to which the column should be updated.

    a WHERE clause is used to do what, when updating.
    (whats an example?)

    to filter which rows will be updated.
    (SET LastUpdateDt = getdate(), CreditLimit = 2500 WHERE CustID = 893)

    The FROM clause is used in an UPDATE statement, when/used for?

    is used to update rows in a table based on information stored in another table.

    1. Can you use a VALUES clause with an UPDATE statement?
    2. When can a VALUES clause be used?

    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.

    1. Can you use a INTO clause with an UPDATE statement?
    2. When can a VALUES clause be used? (list two ways)

    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.

    What is a junction or intersection table.
    The table contains columns that references what?

    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.

    By, default you can't specify a value for an identity column within an INSERT statement. Can you override that default? if so, how?

    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.

    The GROUP BY clause

    allows you to perform an aggregate calculation over rows that have a matching value in a column.

    Which clause is specified first? the ORDER BY clause and the GROUP BY clause?

    The GROUP BY clause.

    When creating a view, are any constraints not considered? if so, What is one of those constraints?

    the CHECK constraints on the table are not considered,

    1. SCHEMABINDING clause, purpose?
    2. when is it available?

    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

    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?

    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.

    To ensure that either both DELETE statements are successful or both fail, you can use what?

    an explicit transaction.

    An inner join

    returns only matched rows from the two tables.

    A cross join

    returns a Cartesian product (a result in which each combination of rows is represented).

    A full outer join

    returns all matching rows from both tables and all unmatched rows.

    a self-join

    is used when a column references another column in the same table.

    Is Combining informational columns, part of the normalization process?

    NO

    1. A user-defined function must contain which clause AND which statement?
    2. What does the clause do?
    3. what does the statement do?

    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 CREATE FUNCTION statement is used when?

    to create a user-defined function. User-defined functions must return a value.

    indexes are used to optimize what?

    optimize frequently-used queries by reducing the number of table scans required to locate data.

    In a table scan-

    each row of a table is loaded to locate the records needed to generate the results.

    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?

    columns that are frequently used as join conditions, in WHERE clauses, and in GROUP BY clauses as key columns in an index

    What about an index should you avoid? and why?

    avoid large index keys for more optimal performance.

    1. The ALTER VIEW statement is?
    2. adding a WHERE clause does what in a view?

    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.

    In a VIEW what does the WITH CHECK OPTION clause do?

    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.

    A login

    A login is a server-level object that is used to grant the ability to establish a connection with an instance of SQL Server.

    the INTERSECT operator is used to, what?

    to return a list of only the rows that are in both of the result sets generated by two other separate queries.

    the EXCEPT operator

    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.

    The ORDER BY clause of a SELECT statement is used to?

    order the results of a query.

    1. In a well-designed relational database table, a column represents what?
    2. The entity is represented how?

    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.

    You define an output parameter by using, which keyword?

    OUTPUT keyword

    A paramater name must begin with what?

    the @ character

    A stored procedure can modify what?

    the data in permanent tables and return multiple values as output parameters.

    A table-valued function

    selects data and returns the results as a table.

    A scalar function

    performs an operation and returns a single value. (a function can't perform any modification on a permanent table)

    datetimeoffset

    The datetimeoffset data type stores the date and time, including an offset from Greenwich Mean Time (GMT).

    datetime2

    The datetime2 data type stores date and time information at user- defined precision. However, it does not store a time zone offset.

    time

    The time data type stores only time information up to 100 nanosecond accuracy. It does not store a time zone offset.

    See More

    Please allow access to your computer’s microphone to use Voice Recording.

    Having trouble? Click here for help.

    We can’t access your microphone!

    Click the icon above to update your browser permissions above and try again

    Example:

    Reload the page to try again!

    Reload

    Press Cmd-0 to reset your zoom

    Press Ctrl-0 to reset your zoom

    It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

    Please upgrade Flash or install Chrome
    to use Voice Recording.

    For more help, see our troubleshooting page.

    Your microphone is muted

    For help fixing this issue, see this FAQ.

    Star this term

    You can study starred terms together

    NEW! Voice Recording

    This is a Plus feature