Sql Server 2778 M01

Front 1

What category of SQL statements would the following statement fragment be classified?
 
UPDATE Price SET Price = Price * 1.25 ...
 
A. DDL
B. DML
C. SQL
D. DCL 

Back 1

B. DML

DDL = Data Definition Language (CREATE USER user_name) 

DML = Data Manipulation Language (INSERT, UPDATE, DELETE)

DQL = SQL Select Statements (SELECT ProductID, Name, List Price FROM Production.Product)

DCL = Data Control Language (GRANT CONNECT TO guest; GRANT CONTROL SERVER TO user_name;)

TCL = Transactional Control Language (COMMIT TRANSACTION Inner2;)

Front 2

What is normalization? 

Back 2

The process of removing redundant data from a database.

Front 3

What are benefits of normalization? 

Back 3

-Accelerates sorting and indexing
-Allows more clustered indexes
-Helps INSERT and UPDATE performance
-More compact databases

Front 4

What are disadvantages of normalization? 

Back 4

 -Increase in tables to join
-slower data retrieval
-insertion of code in tables
-difficulty in data model query
Denormalization (monolithic table) is better for running reports and queries

Front 5

what's a table? 

Back 5

contain all the data in SQL Server databases

Front 6

what's a view? 

Back 6

acts like a virtual table or a stored query

Front 7

what are indexes? 

Back 7

enable fast retrival, built from one or more columns in table or view

Front 8

what are triggers? 

Back 8

execute a batch of SQL code when an insert, update or delete command is executed against a specific table

Front 9

what are procedures? 

Back 9

accept parameters, contain statements, and return values

Front 10

what are constraints? 

Back 10

prevent inconsistent data from being placed in a column

Front 11

what are rules? 

Back 11

specify acceptable values that can be inserted in a column

Front 12

What is DML 

Back 12

Data Manipulation Language - allow you to modify underlying data in your tables. Insert, Update, Delete
ex. INSERT Table1 VALUES (1, 'Row #1')

Front 13

What is DCL 

Back 13

Data Control Language - allows you to work with permissions/security of underlying database objects.
ex. GRANT CONNECT TO guest;
GRANT CONTROL SERVER TO user_name;

Front 14

 
What is DDL 

Back 14

Data Definition Language - allow you to create the objects that comprise your database.
ex. CREATE USER user_name

Front 15

What is TCL 

Back 15

Transactional Control Language - commit, rollback, begin trans
ex. COMMIT TRANSACTION Inner2;

Front 16

What is DQL 

Back 16

SQL Select Statements - allows querying data.
ex. SELECT ProductID, Name, ListPrice
FROM Production.Product

Front 17

How do you declare a variable for a stored procedure?

Back 17

DECLARE @local_variable as <data_type>
sample:
declare @food varchar(20)
set @food = 'ice cream'
...
WHERE Description = @food

Front 18

Control-of-Flow Keywords?

Back 18

BEGIN...END
GOTO
IF...ELSE
RETURN
BREAK
CONTINUE
WHILE
WAITFOR

Front 19

Sample IF?

Back 19

IF Boolean_expression
BEGIN
{ sql_statement

Front 20

What are Batch Directives?

Back 20

They control the movement within a T-SQL file.
GO - if you have a lot of SELECT statements you can use a GO to separate from another batch
EXEC - used for executing a Stored Procedure

BEGIN
END

Front 21

GO

Back 21

Delineates batches of T-SQL statement to tools and utilities'
Is not an actual T-SQL statement

Front 22

EXEC

Back 22

Executes a user-defined function, system procedure, user-defined stored procedure, or an extended stored procedure
Controls the execution of a character string within a Transact-SQL batch

Front 23

How do you add comments in T-SQL?

Back 23

/*
This is a comment
*/
OR
--This is a comment

Front 24

SQL Server Management Studio

Back 24

used for interactive creation of T-SQL scripts
used to access, configure, manage, and create many other SQL Server objects

Front 25

Tools for Querying SQL Server Databases

Back 25

SQL Server management Studio
Microsoft Office Excel
SQLCMD
PowerShell

Front 26

SQLCMD

Back 26

A command used by administrators for command line and batch files processing
SQLCMD -S server\instance -i C:\script

Front 27

PowerShell

Back 27

An environment used by administrators for command line and batch processing

Front 28

Introduction to SQL Server Management Studio

Back 28

Support for writing and editing queries or scripts
Integrated source control for solution and script projects
Supports most administrative tasks for SQL Server
An integrated Web browser for quick browsing

Front 29

What are the SQL Server Components

Back 29

SQL Server Database Engine:
Core service for storing and processing data in your database.

Analysis Services: Tools that allow you to create and manage analytical processing such as statistics

Reporting Services: Allow you to create and deploy reports

Integration Services: Used for moving, copying, or transforming data between databases and different kinds of databases

Front 30

What are the SQL Server Management tools

Back 30

SQL Server Management Studio: An environment to access, configure, manage, and administer SQL components

SQL Server Configuration Manager: An interface to provide management for SQL services, protocols, and client aliases

SQL Server Profiler: A GUI tool to profile and trace the Database Engine and Analysis Services

Database Engine Tuning Advisor: An application to create optimal sets of indexes, indexed views, and partitions

Business Intelligence Development Studio : An IDE for creating Analysis Services, Reporting Services, and Integration Services solutions

Front 31

The SQL Server ____ Studio is an environment that allows you to access, configure and manage SQL Server components within a Graphical interface.
A. Reporting
B. Management
C. Profiler
D. Configuration

Back 31

B. Management

Front 32

Scalar functions return a ___?
A. Cache
B. Result Set
C. XML result
D. Single value

Back 32

D. Single value

Front 33

There are 5 main levels of Database Normalization. What level is the most reasonable level implemented in most database designs?
A. First
B. Second
C. Third
D. Fifth

Back 33

C. Third

Front 34

Which of the following is considered a valid local variable declaration in T-SQL?
A. @age int
B. @@count int
C. #data DateTime2
D. ##date DateTime2

Back 34

A. @age int

Front 35

Which SQL Server component service is responsible for handling the Core storing and processing of data?
A. Integration services
B. Database Engine service
C. Analysis services
D. Query Integration services

Back 35

B. Database Engine service

Front 36

What is the main difference between triggers and stored procedures?
A. Stored procedures are attached to tables
B. A trigger is attached to a single table
C. Stored procedures are written in managed code
D. Triggers can be written with T-SQL

Back 36

B. A trigger is attached to a single table

Front 37

True or False: The primary job of a constraint is to enforce a rule in the database.

Back 37

True

Front 38

What T-SQL statement is used to raise a custom error?
A. RETURNERROR
B. THROW
C. RAISEERROR
D. ERROR.RAISE

Back 38

C. RAISEERROR

Front 39

What SQL Server tool would you use to execute the following statement?

'TOOLNAME' -S servername -i c:\scriptname.sql

A. Query Tool
B. Database Script Tool
C. SQLCMD
D. PowerShell

Back 39

C. SQLCMD

Front 40

What category of SQL statements would the following statement fragment be classified?
UPDATE Price SET Price = Price * 1.25 ..

A. DDL
B. DML
C. SQL
D. DCL

Back 40

B. DML

Front 41

Which SQL Server component service is responsible for handling the Core storing and processing of data?

Integration services
Database Engine service
Analysis services
Query Integration services

Back 41

Database Engine service

The SQL Server Database Engine component provides the Core service for storing and processing data.

Front 42

The SQL Server ______ Studio is an environment that allows you to access, configure and manage SQL Server components within a Graphical Interface.

Reporting
Management
Profiler
Configuration

Back 42

Management

The SQL Server Management Studio (SSMS) is an environment that allows you to access, configure and manage SQL Server components within a Graphical Interface.

Front 43

What is first normal form?

Back 43

Involves making sure that you don't have duplicate data in your tables.

Front 44

Second Normal Form

Back 44

Making sure that fields in table are all related to any one field

Front 45

Third Normal Form

Back 45

Can not have any derived or calculated fields. Calculations would be done in queries or reports

Front 46

what are the 4 primary properties to the SELECT statement?

Back 46

1) the number and attributes of the columns in the result set
SELECT ProductID, Name, ListPrice

2) The tables from which the result set data is retrieved
FROM Production.Product

3) The conditions the rows in the source tables must meet
WHERE ListPrice > $40

4) The sequence which the rows of the result set are ordered
ORDER BY ListPrice ASC

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > $40
ORDER BY ListPrice ASC

Front 47

What are the types of T-SQL functions?

Back 47

Rowset Functions
Return an object that can be used like table references in an SQL statement.
Example: OpenQuery function

Aggregate Functions
Operate on a collection of values but return a single, summarizing value. Example: Average function

Ranking Functions
Return a ranking value for each row in a partition. Example: Rank and Dense functions

Scalar Functions
Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. Example: Cubic Volume

Front 48

How is Microsoft Excel typically used with SQL Server?

Back 48

A spreadsheet used by financial and business professional to retrieve data.