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

  • What is the UNIQUE constraint?

    Specifically identifies which column should not contain duplicate values.

    What is the CHECK constraint?

    Allows the admin to limit the types of data a user can insert into that column of the database.

    What is a default constraint?

    Used to insert a default value into a column.

    What is the primary key?

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

    What is a foreign key?

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

    Why is it a bad idea to let a foreign key contain a NULL value?

    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.

    What is a self-reference?

    When the foreign key refers to columns in the same table.

    What is XQuery?

    A query and functional programming language designed to query collections of XML data.

    What is SQLCMD?

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

    What is TRANSACT-SQL

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

    What are the three types of files in SQL?

    MDF = Primary data files, NDF = Secondary data files and LDF = Log files

    Database objects are divided into two categories:

    Storage and Programmability

    Tables created using the ________ statement are used to store data.

    CREATE TABLE

    Constraints are also referred to as ____ constraints.

    Column; limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered.

    In order to use views, you must use the ______ T-SQL statement to show data from the tables.

    SELECT

    DDL influences _____, while _______ influences actual data stored in tables.

    Database objects, DML

    The MS database server that hosts relational databases is called _____

    MS SQL Server

    The code DML statements are (5)

    MIDUS: MERGE, INSERT, DELETE, UPDATE, SELECT

    System views belong to the ______

    sys schema

    What are 6 constraint types?

    Unique, Check(domain Integrity), Default(domain integrity), Not Null, Primary Key(entity integrity), Foreign Key(referential integrity)

    A bit is the T-SQL integer data type that can take a ___ of 1, 0 or NULL.

    value

    A regular character uses ___ bytes of storage, whereas a unicode character requires ____ bytes.

    one, two

    When querying a database you can obtain faster results from properly ______ tables and views.

    indexed

    What prefix must you have in front of a string to use Unicode?

    N

    What is the default length for the CAST function?

    30

    What is native auditing?

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

    What is a two-phase commit system?

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

    Name 4 types of decomposition

    Top-down, Bottom-up, Inside-out, Mixed

    What is the inside-out approach to db design?

    A type of bottom-up approach, the inside-out method begins with identifying a few important concepts then proceeds outward radially.

    What is the top-down approach?

    When a schema is created through a series of successive refinements, starting with the first schema.

    What is the With Execute Owner clause?

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

    What is abstraction?

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

    What is the correct way to select an avg of a column?

    SELECT AVG("column name") FROM "table name"

    What is a clustered index?

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

    What is a non-clustered index?

    Non-clustered 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. (Optimal Performance)

    What are 3 characteristics of a simple view?

    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

    Which DB design process allows you to create a data model independent of a specific DBMS?

    Logical DB design

    What's the difference between an INNER join and an OUTER join?

    INNER will only produce matching rows from both tables while OUTER will join all rows from both tables whether they match or not.

    What command do you use to invoke a stored procedure?

    Execute

    If you have already defined a view on a specific table, then you add columns to that table, what must you do to the view?

    Use the CREATE or REPLACE VIEW command to redefine it.

    What are three things true about views?

    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.

    What is DB prototyping?

    Building a working model of the DB system in order to suggest improvements or add new features.

    When is the best time to back up dynamic log files?

    When the server is stopped.

    Define Normal Form

    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

    How do you adjust indexes to reduce fragmentation from page splits?

    Set the fillfactor to 60.

    What is a fillfactor?

    It specifies a percentage that indicates how much free space will be in the leaf level of each index page.

    How would you set fillfactor?

    ALTER INDEX ALL ON dbo.OrderDetails REBUILD WITH (FILLFACTOR = 60);

    What would happen if you had a BETWEEN operator of BETWEEN 'D' and 'F'

    You would get all values for D, E but not F. (MUST HAVE AND IN BETWEEN STATEMENT)

    What is the bottom up approach?

    Breaking down the smaller components so that each describes a basic fragment.

    Which form of database design uses secondary storage media?

    Physical database design

    Mark works as a Database Designer for Reon Inc. He is assigned the task to create a database for the company. He issues the following query to create the database.

    CREATE DATABASE '24342'

    What will be the output of the query?

    A database will be created.

    When do you pick the DBMS?

    Prior to the Logical design phase.

    Which of the following statements are true regarding the procedural data manipulation language?

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

    What does the IDENTITY constraint do?

    It's used on the primary key to automatically start with 1 and auto-increment by 1.

    Database objects are divide into two categories:

    Storage and Programmability

    What always returns a value but never updates data?

    A function

    The core DDL statements are:

    CREATE, DROP, ALTER

    The foreign key constraint is a ____ identifier.

    Relationship; Enforces referential integrity

    Name 3 things which can be used to improve query performance

    A primary key, a UNIQUE index and a CLUSTERED index

    T/F: A single INSERT statement can be used to add rows to multiple tables.

    False

    Can you change the IDENTITY constraint of an existing column with an ALTER statement?

    No

    T/F: Null is a valid constraint

    False, NULL is not a constraint

    Which 4 things always have a related data type?

    Column, localvariable, expression and parameter.

    SQL server supports ____ conversions without using actual callout functions CAST or CONVERT

    Implicit,(automatic)

    A regular character uses how much storage?

    1 byte, 256 possible characters.

    A unicode characters uses how much storage?

    2 bytes, This allows 65,536 characters at almost any language.

    For the CHAR data set, it is a _____ length and uses ___ bytes:

    Fixed, N

    What is one thing to consider when creating a view?

    Database performance

    An ___ ____ is the same thing as a CROSS JOIN with a WHERE condition:

    INNER JOIN

    What's the most efficient way to delete all rows from a table?

    TRUNCATE command; does not log each row

    How do you start a transaction?

    Use BEGIN TRAN

    A ____ will combine the results of two or more queries into a resulting set that includes all the rows belonging to the query:

    UNION; without any duplicates. Duplicates require UNION ALL.

    If you are querying the same table for two different things you'd use a....

    UNION

    A clustered index usually _____ performance when inserting data.

    worsens, because it's constantly sorting it. Improves it for retrieving it though. Physical sort. Only 1 per table.

    What 2 things speeds up data retrieval?

    Primary key constraints and Clustered indexes

    What 3 reasons should you consider using a clustered index?

    1. Columns contain a large number of distinct values
    2. Columns are accessed sequentially
    3. Queries return large result sets

    Which normal form ensures that each attribute describes the entity?

    2nf

    What command allows a Windows account to access SQL-Server?

    CREATE LOGIN

    Any ___ permission will always override a GRANT permission.

    DENY

    A ____ backup contains only the data that has changed since the last FULL backup.

    Differential

    All users are automatically members of the ______ database role.

    Public

    Use the _____ command to recover data that was accidentally deleted by a user.

    Restore

    Name 3 levels of security supported by SQL Server

    Server, Database and Table

    The sa account is only used in

    mixed-mode

    The ___ role gives access to anything on the SQL server, while the ____ role gives full access to a specific database:

    sysadmin, db_owner

    What language are triggers written in?

    DML or DDL

    What happens if data is missing for a particular column when designing the INSERT SQL statement?

    The INSERT statement uses the default value for the column.

    Name the two types of prototyping

    Requirements and Evolutionary

    What steps should you take to create full-text searching?

    1. Create a full text catalog in the database
    2. Create a full text index on the column in question

    What command would you use to track changes to a table?

    ALTER TABLE tablename
    ENABLE CHANGE_TRACKING

    Application design involves 2 important activities:

    transaction design and interface design

    What does degree refer to?

    The number of columns.

    index

    (table 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,.

    Flat-type databases

    is simplistic in design. They are two dimensional tables that are consisting to rows and columns. for fast searches.

    hierarchical database

    is 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 parent.

    relational database

    A 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 multiple parents.

    SELECT

    Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server.

    INSERT

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

    UPDATE

    Changes existing data in one or more rows in a table or view(changes the data in the table)

    DELETE

    Removes rows from a table or view(, which deletes data from within a table) Use for smaller databases

    MERGE

    Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

    USE

    Changes the database context

    CREATE

    Creates a SQL Server database object (table, view, or stored procedure)

    ALTER

    Changes an existing object(changes the object definition)

    DROP

    A DDL statement used to remove objects, such as tables, views, stored procedures, and triggers , from a database.

    Collation

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

    Transact-SQL data types

    bit, 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.

    view

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

    Views ensure the security of data by restricting access to the following data

    • 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

    Some common examples of views include the following:

    • 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

    two major reasons you might want to provide a view instead of enabling users to access the underlying tables in your database:

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

    A stored procedure

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

    SQL injection

    A code injection technique that exploits security vulnerabilities in the DB layer of an application.

    Precision (p)

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

    Explicit conversions require you to use

    the functions cast or convert specifically.

    three things you need to be sure to identify in your statement to form a proper SELECT query:

    • Columns to retrieve
    • Tables to retrieve the columns from
    • Conditions, if any, that the data must satisfy

    SET NOCOUNT statement

    shows line stating the number of rows "affected". This is off by default. This will suppress the '(1 row affected)

    JOIN statements

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

    Both the EXCEPT and the INTERSECT statements

    are designed to return distinct values by comparing the results of two queries.

    EXCEPT clause

    gives you the final result set where data exists in the first query and not in the second dataset.

    INTERSECT clause

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

    SELECT statement query

    lets you produce individual lists, but the result may be that you get all the information you need but in an individual list format.

    UPDATE statement

    The function of the is to change data in a table or view

    UPDATE clause

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

    DELETE syntax

    You can identify and delete individual rows from the database

    Normalization

    series of steps(or rules) called "forms" the more steps you take, the more normalized your tables.

    Define 5NF

    Isolate semantically related multiple relationships

    what happens when you connect two tables

    the primary key is replicated from the primary to secondary table, and all the key attributes duplicated from the primary table are known as the foreign key.

    composite primary key

    occurs when you define more than one column as your primary key.

    primary drawbacks to using indexes

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

    Windows Authentication mode is superior to mixed mode because

    users don't need to learn yet another password and because it leverages the security design of the network.

    application role

    is a database-specific role intended to allow an application to gain access regardless of the user.

    database index

    is a data structure that improves the speed of data retrieval operations on a database table.

    Databases are often put on database servers for what reason

    so that they can be accessed by multiple users and provide a higher level of performance. One popular database server is Microsoft SQL Server.

    Exact numeric data types

    are the most common SQL Server data types used to store numeric information.

    serveradmin fixed server role

    can Configure server -wide settings and issue SHUTDOWN command which immediately stops the SQL server.

    DROP LOGIN

    Command will remove a windows user or group from the SQL server. This will not alter Windows its self

    WHERE

    is used to obtain only subset of rows that fulfill criterion in the WHERE clause

    REVOKE

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

    DENY

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

    Dbcreator

    Can create, alter,drop, and restore databases

    Diskadmin

    Can create, alter, and drop disk files. This is a Fixed server role

    Setupadmin

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

    db_denydatareader

    Can read from any table in the database, this deny will override an object-level grant

    db_denydatawriter

    Blocks modifying data in any table in the database. Will override any object-level grant

    db_ddladmin

    can issue DDL commands(statements)(create, alter,drop)

    db_backupoperator

    can perform backups, checkpoints, and DBCC commands, but not restores(only sysadmins can perform restores)

    Table sizes

    1MB - 1024 KB - 1 field - 165 bytes. a 100k table - = 16113.28125 kb(15.73563 mb)

    CRUD

    In computer programing, create, read, update and delete (CRUD)are the four basic functions of persistent change

    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