DBMS 110 MOAC Lesson 1
SSMS
A 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.
database management system (DBMS)
A collection of programs that enables you to enter, organize, and select data in a database. For example, a ticket agent may run a ticket system program on his or her desk computer that in turn accesses a ticketing database.
index
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, which requires processing resources and takes up disk space.
XQuery
A query and functional programming language that is designed to query collections of XML data.
Drop statement
Actually removes an object from a database, but if other objects are dependent on the object you are attempting to remove, this statement will fail and an error will be raised.
Create statement
Allows you to create a variety of database objects, including tables, views, and stored procedures. For instance, say you want to create a new table named Planets within a database named AdventureWorks.
database (db)
An organized collection of data, typically stored in electronic format. It allows you to input, organize, and retrieve data quickly. Traditional databases are organized by fields, records, and files.
foreign key
Constraints can define entity relationships between tables on a continual basis. They are also referred to as____________ constraints.
databasse objects DML
DDL influences ____________, whereas____________ influences actual data stored in tables.
storage and programmability
Database objects are divided into two categories:____________ and ____________.
database server
Databases are often found on database servers so that they can be accessed by multiple users and provide a high level of performance.
select
In order to use the views object to view a data set, you must use the____________ Transact-SQL statement to show data from underlying tables.
SQLCMD
Is a command-line application that comes with Microsoft SQL Server and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt. It can also act as a scripting language to create and run a set of SQL statements as a script. Such scripts are stored as .sql files, and they are used either for management of databases or to create the database schema during database deployment.
Data Definition Language (DDL)
Is a subset of the Transact-SQL language; it deals with creating database objects like tables, constraints, and stored procedures. The interface used to create these underlying DDL statements is the SSMS user interface.
relational database
Is similar to a hierarchical database in that data is stored in tables and any new information is automatically added into the table without the need to reorganize the table itself. Unlike in hierarchical databases, however, a table in a relational database can have multiple parents. A relational database is a collection of tables.
Constraints
Limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered. For example, if a person's age should be input, then the data that is entered must be a positive number—a person cannot have a negative age. A variety of constraints are available with SQL Server 2008, including the following: • 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.
Self-reference
Possible for a foreign key constraint to reference columns in the same table
SQL Server Management Studio (SSMS)
Primary tool for managing the server and its databases using a graphical interface.
Data Manipulation Language (DML) statements
SELECT, INSERT, UPDATE, DELETE, or MERGE
Indexes should only be created on columns that are frequently searched. b. A self-reference arises when a foreign key constraint references a column in the same table.
Select all of the following statements that are true: a. Indexes should only be created on columns that are frequently searched. b. A self-reference arises when a foreign key constraint references a column in the same table. c. A single INSERT statement can be used to add rows to multiple tables. d. Multiple primary keys can be added to a table.
hierarchical database
Similar to a tree structure (such as a family tree). In this database, each "parent" table can have multiple "children," but each child can have only one parent.
flat-type database
Simplistic in design. They are most commonly used in plain-text formats. Because their purpose is to hold one record per line, they make access, performance, and queries very quick. An example of this type of database would be what you would find in a .txt or .ini file. Flat-type databases are considered "flat" because they are two-dimensional tables consisting of rows and columns.
sys schema
System views belong to the ____________.
create
Tables created using the____________ statement are used to store data.
Mircosoft SQL Server
The Microsoft database server that hosts relational databases is called ____________.
USE, CREATE, ALTER DROP TRUNCATE, DELETE
The core DDL statements are ____________, ____________, ____________, ____________, ____________ and ____________.
SELECT, INSERT, UPDATE,DELETE,MERGE
The core DML statements are ____________, ____________, ____________, ____________, and ____________.
relationship
The foreign key constraint is a(n)____________ identifier
Transact-SQL
The primary means of programming and managing SQL Server. It exposes keywords so that you can create and manage databases and their components and monitor and manage the server itself. When you use SSMS to perform an action or task, you are executing Transact-SQL commands.
Data Definition Language (DDL) statements
USE, CREATE, ALTER , DROP, TRUNICATE, DELETE
constraint
What are limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered?a. Primary key b. index c. Foreign key d. constraint
Structured Query Language
What does SQL stand for? a. Structured Question Language b. Structured Query Language c. Strong Question Language d. Specific Query Language
self-join
When a self-reference is used to query a table, this arrangement is now referenced as a self-join.
System Tables
When you want to query system views to verify whether the object(s) you wish to drop are, in fact, in the database tables, you need to know what tables are the most useful. System views belong to the sys schema. Some of these system tables include the following: • sys.Tables • sys.Columns • sys.Databases • sys.Constraints • sys.Views • sys.Procedures • sys.Indexes • sys.Triggers • sys.Objects
UPDATE
Which SQL statement is used to update data in a database? a. SAVE b. MODIFY c. SAVE AS d. UPDATE
SELECT
Which of the following SQL statements is used to extract data from a database? a. SELECT b. OPEN c. EXTRACT d. GET
Changing the identity constraint of an existing column.
Which of the following actions is not supported by ALTER? a. Adding a new column to a table. b. Deleting multiple columns from an existing table. c. Modifying the data type of an existing column. d. Changing the identity constraint of an existing column.
MERGE
Which of the following is not a DDL statement? a. CREATE b. MERGE c. ALTER d. DROP
REMOVE
Which of the following is not a DML statement? a. REMOVE b. INSERT c. DELETE d. TRUNCATE
Range
Which of the following is not a column constraint? a. Default b. Check c. Range d. Unique
Null
Which of the following is not a constraint? a. Null b. Unique c. Check d. Primary
Alter statement
changes an existing object; you can use it to add or remove columns from a table,
Data Manipulation Language (DML)
the language element that allows you to use the core statements INSERT, UPDATE, DELETE, and MERGE to manipulate data in any SQL Server tables. Core DML statements include the following: • 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. • DELETE: Removes rows from 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.
DDL statements
• USE: Changes the database context. • CREATE: Creates a SQL Server database object (table, view, or stored procedure). • ALTER: Changes an existing object. • DROP: Removes an object from the database. • TRUNCATE: Removes rows from a table and frees the space used by those rows. • DELETE: Remove rows from a table but does not free the space used by those rows removed.