Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

CPT - 272 > Module 1 > Flashcards

Module 1 Flashcards

1
Q

A common reason that SQL Server 2012 installation might fail is because the Windows OS is not up to date with service packs or security updates.

A

True 21

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

A string concatenation operation evaluates to null if all of the input columns have a value.

A

False 155

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Client applications can only connect to the SQL Server instance on the local computer.

A

False 89

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Define the logical operators AND, OR, and NOT.

A

• AND – Null conditions must be true. • OR = Any condition must be true. • NOT – Reverses the truth of the original condition. (Page 156)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Describe how to create a database using the SQL programming language and explain why it might be the preferred method.

A

User databases can be created by executing the CREATE database syntax (STOP)

and supplying a set of configuration parameters using the SQL programming language. (STOP)

The CREATE statement is a SQL instruction to create an object. (STOP)

This option is sometimes preferred by experienced database administrators because it allows them to create standardized scripts, rather than having to manually change each configuration parameter in a Windows form. (Page 117)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Describe the logical organization of a database.

A

Each database is organized using a logical structure of database objects that overlay the physical average. (STOP)

A logical structure is a way of organizing data, with defined rules for storing, manipulating, and retrieving the data; (STOP)

client applications interact with these logical objects within a database rather than the physical files (Page 75)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Describe the use of the SELECT statement.

A

The SELECT statement is used to retrieve data from a database, and it is the most commonly used statement in the SQL language. (STOP)

Data retrieved by a SELECT query is returned as a two-dimensional result set. (STOP)

When writing a SELECT statement, you follow the SELECT keyword with a list of the column names that you want to be included in the result set. (STOP)

After specifying the column names, you follow the FROM keyword with a list of the table and view names that will be used to retrieve data (Page 153)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How do you combine data using joins?

A

A join is a database operation that can be used to merge and retrieve data that is stored in more than one table or view.(STOP)

Inner and outer joins use join conditions – using the ON clause – that specify which columns from each table should be used as the matching key. (Page 163)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How does the SQL language handle case and what are the recommendations on using case when constructing SQL statements?

A

The SQL language is not case sensitive, however, the normal convention is to write all keywords (e.g. SELECT) in uppercase. (STOP)

Consistent use of naming conventions make your SQL query easier to read and follow,(STOP)

especially once you start writing more complex queries. (Page 153)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

If you want to compare a value in a column against a list of values, which syntax would you use? a. WHERE [Column Name] LIKE ([Value1], [Value2], [Value3]) c. WHERE [Column Name] IN ([Value1], [Value2], [Value3]) b. WHERE [Column Name] = ([Value1], [Value2], [Value3]) d. WHERE [Column Name] EXISTS ([Value1], [Value2], [Value3])

A

(C – Page 156)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

If you want to remove duplicates from a result set, which command should you use? a. SELECT AS c. SELECT ONLY b. SELECT UNIQUE d. SELECT DISTINCT

A

(D – Page 154)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

If you want to set the maximum number of digits a numeric data type can hold, what should you set? a. precision c. scale b. length d. size

A

C 122

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

If you want to set the number of places after the decimal point in a numeric data type, what should you set? a. precision c. scale b. length d. size

A

A 122

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

It’s best to just install SQL Server 2012 using default settings and fine-tune it later.

A

False 28

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Linked servers can be created, modified, or dropped by using a SQL query.

A

True 99

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

List and describe three of the five system databases found in an SQL Server 2012 system.

A
  • master – Stores information on the structure of other databases and their components; maintains configuration settings , including security
  • tempdb – Stores temporary data that is generated when the database engine executes processes
  • model – Stores a database template that is used as a blueprint when creating a new user database
  • resource – Stores the system objects in a read-only database; these logically appear in each database in the sys schema
  • msdb – Stores information related to jobs, schedules, and database backups (Page 75)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Many tasks can be performed with SQL Server Management Studio, but to delete a database, you need to use a SQL statement in the SQLCMD command prompt.

A

False 132

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Since it is easy to make configuration changes after a database is created, it’s best to get the database created and make changes as necessary afterward.

A

False 116

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

SQL is an imperative programming language.

A

False 152

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

SQL Server 2012 is only available in 64-bit versions.

A

False 2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

SQL Server 2012 stores the majority of configuration values within one of the user databases that was created automatically during installation.

A

False 59

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

The database schema is defined by the DDL components in the SQL programming language.

A

True 172

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

The NEW table SQL language statement is used to make a new table.

A

False 137

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

The two main physical file types in an SQL Server database are data files and log files.

A

True 119

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Transact-SQL is the variant of SQL developed by Microsoft and Sybase.

A

True 152

26
Q

User databases contain data associated with a client application.

A

True 28

27
Q

What are int, date, and bit examples of in a SQL database? a. data type c. column name b. condition d. record

A

A 76

28
Q

What happens if a client connects to the database server without specifying a named instance? a. a new instance is created c. the client application is terminated b. it connects to the default instance d. the user is prompted for a named instance

A

B 29

29
Q

What happens if there is no index defined for a query? What is its impact?

A

If no index is defined, a query will require the database engine to undertake a full table scan on every record within the table in order to find matches. (STOP)

For large tables, this has a serious impact on performance (Page 77)

30
Q

What is a data type and how are they used in tables?

A

The data type is a column attribute that defines what type of data it can store. (STOP)

It acts as a constraint because it imposes a limitation on the type of values that can be stored in a column. (STOP)

The main data types include unique identifiers, number of varying precision, dates and times, and text strings. (Page 76)

31
Q

What is a null value and how does it affect arithmetic or string operations in SQL statements?

A

A null value is a column value that does not exist. (STOP)

An arithmetic or string concatenation operation evaluates to null of one of the input columns is null. (STOP)

For example, the expression 10 multiplied by null equals null. (STOP)

In addition, combining a text string with a null value results in a null value. (Page 155)

32
Q

What is a qualified name?

A

A qualified name is a combination of identifiers that uniquely defines the location of a database object. (STOP)

A qualified name is constructed by using up to four identifiers that represent the logical structure and hierarchy of the SQL Server instance, the database name, the database schema, and the database object. (Page 153)

33
Q

What is a statement terminator and how are they used in the SQL language?

A

The statement terminator is a semicolon placed to mark the end of a SQL statement.(STOP)

It is not currently required for all statements, but it is good practice to use a semicolon to future-proof your queries as the SQL standards evolve (Page 153)

34
Q

What is the name for the type of query where a SQL query is embedded within another SQL query? a. nested query c. subquery b. embedded query d. inner query

A

(C – Page 165)

35
Q

What is the primary purpose of a database?

A

To organize and store data in a secure manner while providing a flexible set of access methods fix data retrieval and manipulation by client applications. (Page 74)

36
Q

What keyword can you include in a SELECT statement to create a column alias for the column returned in the result set? a. FROM c. ALIAS b. FOR d. AS

A

(D – Page 154)

37
Q

What should you define if you want to grant several users the same permissions to database objects? a. schema c. role b. user d. access list

A

C 121

38
Q

What type of functions are used to summarize data in a grouped column or for all records in a table? a. Summary procedures c. Grouping statements b. Aggregate functions d. Totaling processes

A

(B – Page 157)

39
Q

What type of Windows Server should you NOT install SQL Server 2012 for security reasons? a. file server c. domain controller b. application server d. authentication controller

A

C 10

40
Q

Which attribute include binary and numeric as categories? a. column c. row b. data type d. record labels

A

B 122

41
Q

Which clause do you use with a join operation to specify which columns from each table should be used as the matching key? a. FROM c. BY b. ON d. WITH

A

(B – Page 163)

42
Q

Which command changes the database context? a. OPEN [Database Name] c. SELECT [Database Name]; b. USE [Database Name]; d. SWITCH [Database Name]

A

(B – 153)

43
Q

Which command returns all the columns for records in which the value of Col1 is greater than 10 but has a value less than Col2? a. SELECT * FROM Table1 WHERE Col1 10 c. SELECT * FROM Table1 IF Col1 > 10 BUT Col1 10 d. SELECT ALL FROM Table1 LIKE Col1 > 10 NOT Col1

A

(A – Page 156)

44
Q

Which command returns the product of two columns returned in a column named ProdC1C2? a. SELECT Col1 * Col2 AS ProdC1C2 c. SELECT FROM Col1 * Col2 TOTAL ProdC1C2 b. SELECT AS ProdC1C2 Col1 x Col2 d. SELECT (Col1, Col2) PRODUCT ProdC1C2

A

(A – Page 154)

45
Q

Which command selects all columns in a table? a. SELECT # c. SELECT * b. SELECT ; d. SELECT @

A

(C – Page 153)

46
Q

Which command will result in records that have a Col1 value greater than 100? a. SELECT Col1 FROM Table1 IF Col1 GT 100 c. SELECT Col1 > 100 FROM Table1 b. SELECT Col1 FROM Table1 WHERE Col1 > 100 d. IF Col1 > 100 SELECT * FROM Table1

A

(B - Page 155)

47
Q

Which of the following is a basic building block of a database that is used to improve the speed and efficiency of data access? a. table c. constraint b. index d. time stamp

A

B 77

48
Q

Which of the following is a logical container that groups together collections of objects within a database and allows them to be managed as a group? a. filegroup c. transaction b. data file d. schema

A

D 119

49
Q

Which of the following is an edition of SQL Server 2012? a. Datacenter c. Enterprise b. Public d. Essential

A

C 2

50
Q

Which of the following is commonly referred to as a record? C 76 a. constraint c. row b. column d. attribute

A

C 76

51
Q

Which of the following is NOT true about an SQL Server 2012 installation? a. there is an installation wizard c. you can use SysPrep for rapid deployment b. you can install from the command prompt d. the wizard provides an unattended install option

A

D 31

52
Q

Which of the following is the core component within a database that control data storage and access requests from client applications? a. replication server c. control module b. query handler d. database engine

A

D 2

53
Q

Which of the following is true about the SQL language? a. it is case sensitive c. the semicolon is a statement terminator b. keywords must be written in uppercase d. statement terminators are required at the end of a statement

A

(C - Page 153)

54
Q

Which of the following represents correct syntax for a SELECT statement? a. SELECT [Column Name] FROM [Table Name] c. SELECT [Database Name] From [Column Name]; b. SELECT [Object Name] FOR [Column Name]; d. SELECT [Table Name] FROM [Row Name]

A

A – Page 153

55
Q

Which of the following SQL statements deletes an object? a. ALTER c. DROP b. REMOVE d. DEL

A

C 132

56
Q

Which of the following ultimately determines which SQL Server 2012 edition is most suited for a particular application? a. business requirements c. product cost b. application version d. system requirements

A

A 2

57
Q

Which SQL Server 2012 component offers the capability to extract, transform, and load data from external sources? a. Analysis Services c. Integration Services b. Reporting Services d. Transformation Services

A

C 29

58
Q

Which statement is used to modify column values of an existing row in a table? a. ALTER c. MODIFY b. CHANGE d. UPDATE

A

(D – Page 170)

59
Q

Which syntax should you use to sort the result set in reverse alphabetic order. a. ORDER ON [Column Name] Z-A c. SORT ON [Column Name] REVERSE b. SORT REVERSE BY [Column Name] d. ORDER BY [Column Name] DESC

A

(D – Page 156)

60
Q

Which type of join returns all columns from both tables and only returns rows where the join column values are equal? a. inner c. right outer b. left outer d. full outer

A

(A – Page 164)

61
Q

Which type of merge statement only combines rows that exist in two result sets? a. UNION c. INTERSECT b. EXCEPT d. JOIN

A

(C - Page 164)

62
Q

Which Windows Server security feature ensures that administrative changes require explicit authorization? a. Windows Defender c. User Account Control b. WMI Control d. Device Manager

A

C 10

CPT - 272 (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions