teste

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

    • Database

    An organized collection of data, typically stored in electronic format.

    Databases are organized by ___

    Fields, records, and files

    Database query is an ___

    inquiry into the database that returns information back from the database.

    A query is ___

    used to ask for information from a database.

    Index ???

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

    The disadvantage of indexes ???

    They need to be created and updated, which requires processing resources and takes up disk space.

    One popular database server:

    Microsoft SQL Server.

    Database servers ???

    Entirely optimized to serve only the purposes of the database itself, usually using advanced hardware that can handle the high processing needs of the database.

    Three types of files to store databases (Microsoft SQL Server):

    Primary data files, secondary data files, transaction log files.

    Extension of Primary data files ???

    .mdf

    Extension of secondary data files ???

    .ndf

    Extension of transaction log files ???

    .ldf

    Primary data files ???

    Contain user-defined objects, such as tables and views, as well as system tables that SQL Server requires for keeping track of the database

    Secondary data files ???

    Database becomes too large and you run out of room on your first hard disk.

    DBMS ???

    Database management system.

    A DBMS is a collection of ___

    Programs that enables you to enter, organize, and select data in a database.

    Types of databases ???

    Flat-type, Hierarchical and relacional databases.

    Flat-type databases ???

    databases most commonly used in plain-text formats.

    Flat-type databases are considered "flat" because ___

    they are two-dimensional tables consisting of rows and columns.

    column ???

    field

    row ???

    record

    Hierarchical Databases ???

    In this database, each "parent" table can have multiple "children," but each child can have only one parent.

    A hierarchical database is similar to a ___

    tree structure

    A table in a relational database can have ___

    multiple parents.

    Three fundamental characteristics of databases:

    • They are designed to store billions of rows of data.
    • They are limited to the computer's available hard disk space.
    • They are optimized to use all a computer's available memory to improve performance.

    Each row corresponding to ___

    one record or one instance of each column.

    Each column corresponds to ___

    One specific attribute or type of information you want to store in the database.

    Database objects are divided into two categories:

    Storage and programmability.

    A table is structured by ___

    columns and rows

    Each column stores data classified as ___

    a data type.

    SQL Server, periodically analyze queries and create indexes as needed to ___

    optimize performance.

    Constraints are limitations ___

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

    A unique constraint ___

    allows the database administrator to specifically identify which column should not contain duplicate values.

    A check constraint ___

    allows the administrator to limit the types of data a user can insert into the database.

    A default constraint ___

    is used to insert a default value into a column. If no other value is specified, the default value will be added to all new records.

    A not null constraint ___

    ensures that data is entered into a cell. In other words, the cell cannot be blank. It also means that you cannot insert a new record or update a record without adding a value to this field.

    The primary key constraint ___

    uniquely identifies each record in a database table. The primary key must contain unique values and it cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key.

    A foreign key constraint in one table ___

    points to a primary key in another table.

    Columns marked as foreign keys can ___

    contain null values.

    it may be impossible to verify the constraints if a foreign key consists of ___

    two or more columns and contains null values.

    This is known as a self-reference

    It is possible for a foreign key constraint to reference columns in the same table

    self-join ???

    When a self-reference is used to query a table

    SSMS ???

    SQL Server Management Studio

    SQL Server Management Studio, the primary tool for ___

    managing the server and its databases using a graphical interface.

    The central feature of SSMS is the ___

    Object Explorer, which allows users to browse, select, and manage any of the objects within the server.

    SSMS can also be used to view and optimize ___

    database performance, as well as to create and modify databases, tables, and indexes.

    Query Analyzer

    provides a GUI-based interface to write and execute queries.

    The Query Analyzer supports:

    XQuery, SQLCMD, Transact-SQL

    XQuery is a ___

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

    SQLCMD is a ___

    command-line application that comes with Microsoft SQL

    SQLCMD ___

    ...allows SQL queries to be written and executed from the command prompt.

    Scripts are stored as .sql files, and they are used either for ___

    management of databases or to create the database schema during database deployment.

    SQL scripts are stored as ___

    .sql files

    Transact-SQL is the ___

    primary means of programming and managing SQL Server.

    When you use SSMS to perform an action or task, you are executing ___

    Transact-SQL commands.

    You can install SQL Server Management Studio on any ___

    Windows desktop operating system so that you can remotely connect to and manage a SQL server.

    The SQL Server Management Studio can be used to perform most of the activities you are required to do and can be considered a ___

    "one-stop" tool.

    Open SSMS by clicking Start > All Programs >

    Microsoft SQL Server 2008 > SQL Server
    Management Studio.

    DML?

    Data Manipulation Language

    Data Manipulation Language (DML) is the language element that allows you to use the core statements (INSERT, UPDATE, DELETE, and MERGE) to ___

    manipulate data in any SQL Server tables.

    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.

    UPDATE ???

    Changes existing data in one or more columns in a table or view.

    MERGE ???

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

    Core DML statements include the following:

    SELECT, INSERT, UPDATE, DELETE, MERGE

    When you use DML statements such as INSERT, UPDATE, DELETE, or MERGE, you need to realize that ___

    on the whole, they either succeed or fail.

    Data Definition Language (DDL) statements ___

    form part/subset of the Transact-SQL portion of SQL Server and can be used to create database objects such as tables, views, constraints, and stored procedures.

    DDL ?

    Data Definition Language

    The main DDL statements are ___

    USE, CREATE, ALTER, DROP, TRUNCATE, DELETE

    USE ???

    Changes the database context.
    (changes the database context to the specified database or database snapshot)

    CREATE ???

    This statement allows you to create a variety of SQL Server database objects, including tables, views, and stored procedures.

    ALTER ???

    Changes an existing object.
    (...statement changes an existing object; you can use it to add or remove columns from a table)

    DROP ???

    Removes an object from the database.

    TRUNCATE ???

    Removes/delete rows from a table and frees the space used by those rows.

    DELETE ???

    Remove rows from a table (or view) but does not free the space used by those rows removed.

    USE TESTDB ???

    To select a database named TESTDB

    Create a new table named Planets within a database named AdventureWorks ???

    USE [AdventureWorks]
    GO
    CREATE TABLE [dbo].[Planets](
    [IndvidualID] [int] NOT NULL,
    [PlanetName] [varchar](50) NULL,
    [PlanetType] [varchar](50) NULL,
    [Radius] [varchar](50) NULL,
    [TimeCreated] [datetime] NULL
    ) ON [PRIMARY]
    GO

    USE [AdventureWorks] ???

    changes the database context to AdventureWorks

    GO command ???

    Executes the previous set of commands.

    The CREATE TABLE [dbo].[Planets] command is used to ___

    create the Planets table.

    ...
    [IndvidualID] [int] NOT NULL, ???
    ...

    Individual ID cannot be NULL.

    ALTER TABLE Shirt ADD Price Money;
    GO
    ???

    Used to add a Price column to the Shirt table.

    UPDATE Shirt SET Price = 13.50 WHERE ProductID = 2;

    If you then wanted to set the prices

    You can also use ALTER to change the definition of a ___

    view, stored procedure, trigger, or function.

    Command sequence redefines the view to include the Price column ___

    ALTER VIEW Size AS
    SELECT ProductID, ProductName, Price FROM Shirt
    WHERE ProductType = 'Size';
    GO

    Don't confuse ALTER with UPDATE:

    Remember, ALTER changes the object definition, but UPDATE changes the data in the table.

    if other objects are dependent on the object you are attempting to remove, this statement will ___

    fail and an error will be raised.

    Remember to not confuse DROP, which removes an object from the database, with ___

    ...DELETE, which deletes data from within a table.

    DELETE FROM user; ???

    to delete all rows from a table named User

    DELETE FROM user WHERE id = 200; ???

    to delete an employee with the identification number 200 from the User table

    system tables/views ???

    sys.Tables, sys.Columns, sys.Databases, sys.Constraints, sys.Views, sys.Procedures, sys.Indexes, sys.Triggers, sys.Objects

    sys.Objects ???

    View contains a row for every object in the database with key column names of name, object_id, type_desc, type, create_date, and modify_date.

    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