Learn efficiently and remember over time.
Start Long-Term Learning
Add this set to a folder
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.
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.
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.
A DBMS is a collection of ___
Programs that enables you to enter, organize, and select data in a database.
Flat-type databases are considered "flat" because ___
they are two-dimensional tables consisting of rows and columns.
Hierarchical Databases ???
In this database, each "parent" table can have multiple "children," but each child can have only one parent.
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 column corresponds to ___
One specific attribute or type of information you want to store in the database.
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.
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
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.
XQuery is a ___
query and functional programming language that is designed to query collections of XML data.
Scripts are stored as .sql files, and they are used either for ___
management of databases or to create the database schema during database deployment.
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 ___
Open SSMS by clicking Start > All Programs >
Microsoft SQL Server 2008 > SQL Server
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.
Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server.
Performs insert, update, or delete operations on a target table based on the results of a join with a source table.
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.
Changes the database context.
(changes the database context to the specified database or database snapshot)
This statement allows you to create a variety of SQL Server database objects, including tables, views, and stored procedures.
Changes an existing object.
(...statement changes an existing object; you can use it to add or remove columns from a table)
Remove rows from a table (or view) but does not free the space used by those rows removed.
Create a new table named Planets within a database named AdventureWorks ???
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]
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';
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 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