Database programming exam 1 review part1

This is typically modeled after a real-world entity, such as an invoice or a vendor.

table

A relational database consists of one or more what?

tables

If you define a column with a default value, that value is used whenever a row

that doesn't include a value for that column is added to the table

Which of the following is not a SQL DML statement?

CreateTable

To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause?

DESC

One limitation of the Query Designer is that you can't use it for

certain types of complex queries

To override the order of precedence in an arithmetic expression, you can use

parentheses

When a column in a table is defined, what determines the kind of data it can store?

a data type

To retrieve or update the data in a database, the client sends a ________________ to the database.

query

What is the most common type of relationship between two tables?

one-to-many

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

In Management Studio, if a statement returns data, that data is displayed in the __________ tab.

Results tab

Which keyword lets you control the number of rows that are returned by a query? a. all of the above b. DISTINCT c. TOP d. ALL

All of the above

If you try to move a database file that's attached to a server, you'll get an error message that indicates the file is in use. To get around this, you need to ________________ the database from the server.

detach

When you use Windows authentication to connect to a database, SQL Server

uses the login name and password that you use for your PC to authorize your connection

The search condition of a WHERE clause consists of one or more

Boolean expressions

When using the Query Designer, you select the tables and columns you want to use in the

Diagram pane

The order of precedence for the logical operators in a WHERE clause is

Not, And, Or

Which clause specifies the number of rows that should be skipped before rows are returned from the result set?

OFFSET

Which ORDER BY clause causes 10 rows to be retrieved from the result set, starting with the 21st row?

ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS

When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

Within the Management Studio, you can build a SQL statement without having to write your own code by using the ________________.

Query Designer

When you code a SELECT statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

When you set the compatibility level of SQL Server 2016, you make sure it is compatible with a specific version of

SQL Server

A graphical tool that you can use to start and stop the database server is called what?

SQL Server Configuration Manager

When using the Query Designer, where is the generated SQL statement displayed?

SQL pane

The Query Editor of the Management Studio lets you enter and execute all types of

SQL statements

Which of the following recommendations won't improve the readability of your SQL statements?

Use comments to describe what each statement does.

If you want to filter the result set that's returned by a SELECT statement, you must include which clause?

WHERE

Insert, Update, and Delete statements can be referred to as ________________ queries.

action

The processing that's done by the DBMS is typically referred to as

back-end processing

In Management Studio, the Query Editor uses the IntelliSense feature to automatically display ________________ lists that you can use to enter parts of the SQL statement.

completion

A database ________________ is a schematic drawing that shows you the relationships between the tables you're working with.

diagram

Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL.

dialect/variant

To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table.

foreign key

What uniquely identifies each row in a table?

primary key

Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily ________________ it.

restore

A view is a SELECT statement that is stored with the ________________.

database

Which of the following types of statements isn't an action query?

Select

If you have the files for an existing SQL Server database, the easiest way to create the database is to ________________ those files to the database server.

attach

To return all of the columns from the base table, which wildcard character do you include in the SELECT clause?

*

To concatenate character strings in a string expression, which operator do you use?

+

Which keyword can you use to retrieve rows in which an expression matches a string pattern called a mask?

LIKE

When using the Query Designer, you can sort the sequence for the query in the

Criteria pane

To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause?

DISTINCT

SQL statements that define the tables in a database are referred to as ________________ statements.

Data Definition Language (DDL)

If you want to use the Management Studio to modify the data for a table, you can right-click on the table and select what?

Edit Top 200 Rows command

Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows?

FETCH

Which clause of the SELECT statement names the table that contains the data to be retrieved?

FROM

Which of the following isn't a common error when entering and executing SQL statements?

Forgetting to attach the required database

Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?

InvoiceTotal - CreditTotal - PaymentTotal / 10

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?

InvoiceTotal IN (0,1000)

When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?

TOP 10 InvoiceTotal

In Management Studio, if you want to modify a column, or view more detailed information about the columns in a table, what tool would you use?

Table Designer

Which of the following is not a valid column alias name?

Total Sales

What SQL dialect does Microsoft SQL Server use?

Transact-SQL

If you define a column as an identity column,

a number is generated for that column whenever a row is added to the table

A combination of column names and operators that evaluate to a single value is called

an expression

You can create a database diagram for

any combination of the tables in a database

The intersection of a row and a column is commonly called what?

cell

Unless you assign a ________________, the column name in the result set is the same as the column name in the base table.

column alias

A string expression can consist of b. a combination of character columns and literal values c. one or more character columns d. all of the above

d. all of the above

The interface between an application program and the DBMS is usually provided by the

data access API

What can you use to combine data from two or more tables into a single result set?

join

A SQL Server database consists of two files: a database file and a ___________.

log file

The three main hardware components of a client/server system are the clients, the server, and the ________________.

network