MTA LinkedIn Learning Quiz

A _________ database is a simple spreadsheet or table that follows straightforward organization rules. a. server file b. flat file c. row line d. column file

b. flat file

Implicit conversion happens where you don't have to worry about the _______ in the original computation. a. data type mismatch b. collation type c. bit length d. values

a. data type mismatch

A differential backup, which is sometimes called a Delta, contains only the data that has changed since the last full backup. a. true b. false

a. true

_______ will store a whole number between zero and 255. a. Smallint b. Tinyint c. Decimal d. Smallmoney

b. Tinyint

Disabling the guest account will accidentally reveal sensitive information to the entire pool of users on the server. a. true b. false

b. false

Why is the "N" present in the following query? SELECT LastName AS Last FROM Customers WHERE (LastName = N'Baker') ORDER BY Last a. it sorts "Baker" to the top of the list b. it establishes that the LastName field uses an N VarChar, so it is a Unicode data type c. the letter "N" here is a typo d. it clarifies that "Baker" should be omitted from the query

b. it establishes that the LastName field uses an N VarChar, so it is a Unicode data type

The _______ database is what SQL Server uses to manage its own instance and configuration. a. model b. master c. msdb d. tempdb

b. master

What will the following statement do? ALTER TABLE MyTable DROP CONSTRAINT PK_MyField a. removes then adds the primary key on the table b. removes the primary key on the table c. removes the foreign constraint key on the table d. none of these

b. removes the primary key on the table

One special caveat to the _______ is the need to pay special attention to related data when working with tables that use two or more attributes to define the unique records. a. 1NF b. 3NF c. 2NF d. 4NF

c. 2NF

How to delete an existing database in SSMS? a. Databases > right-click database name > remove > select close exiting connections b. System Databases > Delete > choose database name c. Databases > right-click database name > Delete > select close exiting connections d. none of these answers

c. Databases > right-click database name > Delete > select close exiting connections

_______ simply deletes objects from the database. a. Remove b. Delete c. Drop d. Alter

c. Drop

How do you add rows to an existing table using SSMS? a. right-click tablename > choose script table as b. right-click tablename > choose Select top 1000 rows c. right-click tablename > choose Edit top 200 rows d. right-click tablename > choose Design

c. right-click tablename > choose Edit top 200 rows

The _______ recovery makes backup of all the database objects but does not store any log files. a. full b. bulk c. simple d. bulk-logged

c. simple

You would use _______ to delete every record from a data table but leave the structure of the table intact. a. delete b. remove c. truncate d. edit

c. truncate

Only use a _______ type when the values will dramatically vary in size. a. nchar b. char c. varchar d. decimal

c. varchar

The mechanism that SQL Server uses to recombine data from multiple related tables on the fly is called a ________. a. combining b. Sequel c. view d. relations

c. view

_______ says to isolate independent multiple relationships. a. 1NF b. 3NF c. 2NF d. 4NF

d. 4NF

Which Transact-SQL command creates a table? a. INSERT TABLE tablename b. DROP TABLE tablename c. MAKE TABLE tablename d. CREATE TABLE tablename

d. CREATE TABLE tablename

What command executes a stored procedure? a. alter mystoredproc b. mystoredproc GO c. exec mystoredproc d. run mystoredproc

exec mystoredproc

True or False: Anything that can't be illustrated with a branching tree diagram would be a good fit for a hierarchical database design.

False

In order to get our table into _______ we need to make sure that it's in an entity format and will remove repeating groups of values. a. 1NF b. 4NF c. 2NF d. 3NF

a. 1NF

Explicit conversions are ones where you specifically either call a CAST or _______ function to change the date type. a. CONVERT b. RECALL c. TYPE d. FORMAT

a. CONVERT

Which Transact-SQL command creates a database and uses it? a. CREATE DATABASE MyDatabase GO USE MyDatabase b. CREATE DATABASE MyDatabase USE MyDatabase c. INSERT DATABASE MyDatabase GO USE MyDatabase d. UPDATE DATABASE MyDatabase USE MyDatabase

a. CREATE DATABASE MyDatabase GO USE MyDatabase

Which statement will create a view using Transact-SQL commands? a. CREATE VIEW vwMyview AS SELECT ProductListing FROM Product b. ADD VIEW vwMyview SELECT ProductListing FROM Product c. CREATE VIEW vwMyview SELECT ProductListing FROM Product d. CREATE TABLE vwMyview AS SELECT ProductListing FROM Product

a. CREATE VIEW vwMyview AS SELECT ProductListing FROM Product

Which of the following is not true about SSMS? a. It will handle hardware connections. b. It will allow us to maintain all aspects of our database server. c. It will handle inputting raw data directly into the tables. d. It will handle the creation of individual databases and database objects.

a. It will handle hardware connections.

Which of the following is not an aggregate function? a. ORDER b. MAX c. SUM d. COUNT

a. ORDER

Adding users through the _______ Properties is an easy way to assign database permissions and allows you to see everyone with the same permission settings all at once. a. Role b. users c. security d. schemas

a. Role

_______ processing means that if anything in the statement fails, nothing gets processed and the database rolls back to its prior state. a. Transactional b. Merge c Manipulation d. Non-transactional

a. Transactional

The Windows operating system handles the process of identifying users so that the server itself doesn't have to. This is called _______ Authentication. a. Windows b. mixed-mode c. group d. users

a. Windows

What will the following statement do: CREATE PROCEDURE MyProc @state char(2) = 'CA' AS BEGIN ....... END a. creates a stored procedure that accepts a user-defined parameter named "state" b. creates a stored procedure that accepts a user-defined parameter named "MyProc" c. creates a stored procedure with the value "char CA"

a. creates a stored procedure that accepts a user-defined parameter named "state"

Enabling the _______ account is often considered a high security risk since anyone with access to the server can assume those permissions. a. guest b. grant c. bulkadmin d. db_datareader

a. guest

The _______ database is essentially the template for all new databases that are created on the server. a. model b. tempdb c. msdb d. master

a. model

How do you create a non-clustered index on a table using SSMS? a. right-click table name > Design > From toolbar select "Manage Indexex and keys" > Add b. from toolbar select "Manage Indexex and keys" > Add c. from toolbar select > Add Indexex and keys d. right-click table name > Table > from toolbar select "Manage Indexex and keys" > Add

a. right-click table name > Design > From toolbar select "Manage Indexex and keys" > Add

What does the "Average row size" under the "Fragmentation" page in the "Index Properties" window mean? a. the number of records that are grouped into the lowest level of the B-Tree b. this is the index number c. this is the row number inside the index d. the number of records that are grouped into the highest level of the B-Tree

a. the number of records that are grouped into the lowest level of the B-Tree

Every field is non-transitively dependent on the primary key. The data contained in a field is not determined by another field that's not part of the key. a. true b. false

a. true

From the securable page of the user logins property window, you can see all of the database objects that users have been granted permissions to. a. true b. false

a. true

Inserting data into a table can be accomplished either using the management studio, the user interface, or via data manipulation language in the SQL Editor. a. true b. false

a. true

It's better to delete records using their unique identifier. a. true b. false

a. true

Once you issue the rollback or commit command you are officially outside of that protection of a transaction and you need to start a new one in order to be able to rollback again. a. true b. false

a. true

Ownership chains make it possible to grant users permission to the processed data and keep the original sources tucked safely out of sight. a. true b. false

a. true

The dml keyword for adding data to an existing table is insert. a. true b. false

a. true

When we create a table, SSMS is essentially tagging the table with an identifier of the person that owns this object. This tag is called a schema, used to organize tables into logical groups. a. true b. false

a. true

When you define a column as a table's primary key, SQL Server creates something called a clustered index on that column. a. true b. false

a. true

You can create as many non-clustered indexes on a table as you want. a. true b. false

a. true

What clause specifies that you're only looking for records that fall within a specified range of values? a. NOT b. BETWEEN c. UNION d. EXCEPT

b. BETWEEN

_______ numbers are only really appropriate to use when the number of decimal places that you need to store exceeds the capabilities of any of the exact numeric data types. a. Small int b. Floating point c. Small money d. Big int

b. Floating point

This modifier helps you specify the data that you don't want to see, rather that the data you do want to see. a. Not equal b. Not c. but d. Except

b. Not

How do you give a user a role on a specific table? a. Right-click on the table name > General > in the Users or roles section press the Search button to add user to this list b. Right-click on the table name > choose Properties > Permissions page > in the Users or roles section press the Search button to add user to this list c. Right-click on the table name > Permissions page > in the Users or roles section press the Search button to add user to this list d. Right-click on the table name > choose Properties > Extended properties > Permissions page > in the Users or roles section press the Search button to add user to this list

b. Right-click on the table name > choose Properties > Permissions page > in the Users or roles section press the Search button to add user to this list

Find the error in the UPDATE statement: UPDATE Employees Position = 'Executive Assistant' WHERE EmployeeID = 9 a. table name is missing b. SET keyword is missing c. PUT keyword is missing d. there is no error

b. SET keyword is missing

How do you deny login permission to a specific user in SSMS? a. Security > Logins > double-click on user's username > General > Deny b. Security > Logins > double-click on user's username > Status > Deny c. Security > Server Roles > double-click on user's username > Status > Deny d. Security > Credentials > double-click on user's username > Server Roles > Deny

b. Security > Logins > double-click on user's username > Status > Deny

What will the following script do? USE MyDatabase ALTER TABLE MyTable ADD Salary Money; a. drop the MyTable table from the database b. add Salary field to the table c. add the contents of Salary and Money fields together d. add Money field to the table

b. add Salary field to the table

COMBINE statement merges two tables together after it compares them. a. true b. false

b. false

If you create a new Windows user and then log out of your SQL session in SSMS, you should be able to access SQL server again using the new user's credentials under Windows Authentication mode. a. true b. false

b. false

It is a good idea to name a new table SELECT or CREATE if they are used for these specific reasons. a. true b. false

b. false

It's often easier to delete a record in the SSMS user interface than to write a delete query. a. true b. false

b. false

Once a primary key is established on a table, other tables can't make use of that field. a. true b. false

b. false

One of the basic rules of storing data in a database is you want to store information that can be calculated from other attributes that you're already storing. a. true b. false

b. false

The DELETE removes all records from a data table, while with the TRUNCATE command, you can target specific records for deletion, rather than dumping the entire table all at once. a. true b. false

b. false

The following SELECT statement will return valid results: SELECT * FROM Customers WHERE State = 'FL' AND State = 'CA' a. true b. false

b. false

You can have multiple clustered indexes on a table. a. true b. false

b. false

Geometry datatype and the geography datatype differ because geometry assumes coordinates occur on a _______ while geography more closely follows the _______ of the Earth's surface. a. curved plane; coordinate plane b. flat grid; curve c. flat grid; longitude d. XYZ system; latitude

b. flat grid; curve

In the following query, what does the SCOPE_INDENTITY() do: INSERT INTO Invoices (CustomerID, Date) VALUES (SCOPE_IDENTITY(), GETDATE()); a. fill in the GetDate, then insert the first Invoice value in the Customer ID b. return the most recent value generated by the Identity column, then fill in the customer ID c. fill in the customer ID, then return the most recent value generated by the Identity column d. return the most recent value generated by the Invoices column, then fill in the customer ID

b. return the most recent value generated by the Identity column, then fill in the customer ID

How do you restore a database from a backup file in SSMS? a. right-click Databases > Restore Database > Database > choose backup file from the hard drive b. right-click Databases > Restore Database > Device > choose backup file from the hard drive c. right-click System Databases > Restore Database > restore to > choose backup file from the hard drive d. right-click System Databases > Restore Database > Device > choose backup file from the hard drive

b. right-click Databases > Restore Database > Device > choose backup file from the hard drive

A database _______ can either be a dedicated machine or a virtualized machine that is running the database management software. a. table b. server c. administration d. file

b. server

For a user to have the ability to modify the server settings and shut down the server, you grant the user the ______ role. a. public b. serveradmin c. sysadmin d. bulkadmin

b. serveradmin

What will the following query return? SELECT CustomerID FROM Customers INTERSECT SELECT CustomerID FROM Invoices a. the total number of customer IDs b. the customers that have placed orders c. the customers that have not placed orders d. the total number of customers

b. the customers that have placed orders

How do you modify an existing table in SSMS? a. Databases > your database name > right-click your table name > Modify b. Databases > right-click your table name > Table c. Databases > your database name > right-click your table name > Design d. Databases > right-click your table name > Design

c. Databases > your database name > right-click your table name > Design

_______ roles are predefined permissions that allow users to perform standard server maintenance tasks. a. Public b. User-definied c. Fixed d. Specific

c. Fixed

What will the following script do? SELECT * FROM Product WHERE Item LIKE 'V%' a. It will select all of the items in the table. b. It will match all the Item values that don't start with a V. c. It will match all the Item values that start with a V and then any characters after it. d. none of these answers

c. It will match all the Item values that start with a V and then any characters after it.

How do you assign fixed server roles to a user login? a. Security > Credentials > double-click on user's username > General b. Security > Logins > double-click on user's username > Status c. Security > Logins > double-click on user's username > Server Roles d. Security > Logins > double-click on user's username > User Mapping

c. Security > Logins > double-click on user's username > Server Roles

By adding the _______ keyword to the beginning of your scripts, you assure that the modification are being made to the intended database regardless of what is selected in the toolbar. a. SELECT b. GO c. USE d. ALTER

c. USE

_______ roles allow the server administrators to create their own packages of permissions in order to assign to users with specific needs. a. Specific b. Public c. User-defined d. Fixed

c. User-defined

What will the following query return? SELECT * FROM InvoiceTotals INNER JOIN Invoice ON InvoiceTotals.InvoiceID = Invoices.InvoiceID INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE InvoiceTotal > 150 AND DATE BETWEEN '2-1-2015' AND '2-28-2015'; a. all customers IDs above 150 dollars that placed an order in the month of February b. the first invoice about 150 dollars that was placed in the month of February c. all invoices above 150 dollars that were placed in the month of February d. the sum of all invoices less than 150 dollars that were places in February

c. all invoices above 150 dollars that were placed in the month of February

What does the following query do? CREATE VIEW vwmyView As SELECT Color, SUM(Quantity) AS QuantitySold FROM LineItemDetails ORDER BY Color a. create a view that will return all colors in ascending order b. return NULL c. create a view that will tell us which colors have sold the most and which have sold the least d. return an error because the syntax is incorrect

c. create a view that will tell us which colors have sold the most and which have sold the least

What will the following script do? USE MyDatabase DROP TABLE AvgSalary a. delete selected rows from the table b. leave the table intact but delete all records from the table c. delete the table and all of its records d. drop all of the tables in MyDatabase

c. delete the table and all of its records

How do you create a composite primary keys in SQL Server Management Studio GUI? a. hold down the Ctrl key > select both keys > right-click and choose the Indexex/Keys b. hold down the Ctrl key > select both keys > right-click and choose Relationships c. hold down the Ctrl key > select both keys > right-click and choose Set Primary Key d. hold down the Ctrl key > select both keys > right-click and choose CheckConstraints

c. hold down the Ctrl key > select both keys > right-click and choose Set Primary Key

During the data _______ phase, you'll determine the data that you want to store, which pieces to combine into tables, and how best to relate those tables together. a. processing b. storage c. modeling d. integrity

c. modeling

Which choice will add a Unique Constraint on the department table? a. ALTER TABLE Departments ADD CONSTRAINT CK_BuildingNumber CHECK (BuildingNumber >=1) b. ALTER TABLE Departments ADD CONSTRAINT CK_BuildingNumber c. CREATE UNIQUE INDEX ON Departments(DepartmentName); d. CREATE UNIQUE INDEX IX_Departments ON Departments(DepartmentName)

d. CREATE UNIQUE INDEX IX_Departments ON Departments(DepartmentName)

The _______ command is useful any time you want to establish a brand new object within the server or the database. a. Truncate b. Alter c. Drop d. Create

d. Create

The ________ supports a highly structured and efficient storage mechanism that allows you to enter, organize, protect, and retrieve information. a. file b. system c. SQL d. DBMS

d. DBMS

_______ is used to define data structures in SQL Server. These statements create and manipulate database objects. a. UDL b. DML c. SSMS d. DDL

d. DDL

How do you create a new table in SSMS? a. Databases > Tables > Add Table b. Databases > Tables > Table c. Databases > Tables > New Table d. Databases > your database name > Tables > Table

d. Databases > your database name > Tables > Table

What data type would you use to store the following data: 2015-07-09 00:00:00.0000000 a. DateTime b. DateTimeOffSet c. Timestamp d. DateTime2

d. DateTime2

_______ permission provides a level of access to the object in the database; _______ overrides any Grant permission that the user might have received previously. a. Grant; Revoke b. Update; Revoke c. Update; Deny d. Grant; Deny

d. Grant; Deny

Which of the following clauses will allow you to combine multiple related tables? a. JOINING b. SELECT JOIN c. ORDER BY DESC d. INNER JOIN

d. INNER JOIN

_______ is the process of organizing data into multiple tables to reduce redundancy from a database. a. Deletion b. Consistency c. Simplification d. Normalization

d. Normalization

How do you create a new user login in SSMS? a. Object Explorer > Server Roles > find the Logins folder > right-click on the folder and choose New Login b. Object Explorer > Databases > find the Logins folder > right-click on the folder and choose New Login c. Object Explorer > Credentials > find the Logins folder > right-click on the folder and choose New Login d. Object Explorer > Security folder > find the Logins folder > right-click on the folder and choose New Login

d. Object Explorer > Security folder > find the Logins folder > right-click on the folder and choose New Login

_______ functions take in one or more parameters, process them through the code, and then output a single value. a. Procedural b. Table-valued c. Output d. Scalar

d. Scalar

_______ are used to bundle sets of SQL statements together so that they can be executed as a single unit. a. Procedure views b. Defined parameters c. Aggregate queries d. Stored procedures

d. Stored procedures

TRUNCATE TABLE is similar to DELETE, but it removes all records from the table without the opportunity to limit scope using a _______ clause. a. Basis b. Filter c. Constrain d. Where

d. Where

The default Authentication mode in order to log in to the SQL server is _______ authentication. a. server b. local c. mixed d. Windows

d. Windows

Which of the following is true about the primary key? a. A table can only have one primary key constraint. b. It is an attribute or a set of attributes used to uniquely identify each row. c. It must be a unique value within the table and it cannot be left blank or null. d. all of these

d. all of these

What is a type of relationship that can be created between tables in a relational database? a. many to many b. one to one c. one to many d. all of these answers

d. all of these answers

When it comes to granting permissions to individuals and groups to have access to a database system, what level can control that access? a. access to the server itself b. assigning permissions to users to allow them to access specific databases c. granting permission to the specific objects within the database that they can work with d. all of these answers

d. all of these answers

Which of the following is a way that SQL Server identifies users? a. the Windows user login b. mixed mode authentication c. through the membership in a Windows user group d. all of these answers

d. all of these answers

Which statement about Views is true? a. Views can provide a base for other queries, eliminating the need to reveal all of your data tables. b. Views can be configured to only provide an aggregated summary of anonymous data. c. Views can only expose the rows and columns that you specify. d. all of these answers

d. all of these answers

You should use this data type to store external files in your database. a. Varchar b. file c. Bigint d. binary

d. binary

A _______ index is essentially the primary index of the table. It defines how all of the records in the table will actually get stored on the disk. a. alternate b. foreign c. non-clustered d. clustered

d. clustered

Primary keys can be make up of multiple attributes that, together, provide the unique identifier for our record. These are called _______. a. alternative keys b. multiple keys c. foreign keys d. composite keys

d. composite keys

Other tables can make use of the primary key by establishing relationships, this will link a copy of the primary key data that's stored in the related table in a field called _______. a. alternative key b. relational key c. composite key d. foreign key

d. foreign key

By using unique identifiers for each record, you can relate one table to another. These identifiers are called _______ fields. a. row b. main c. column d. key

d. key

How do you create a new database in SSMS? a. right-click System Databases > New Database > General > Input database name b. right-click Databases > New Database > File Groups > Input database name c. right-click Databases > New Database > Options > Input database name d. right-click Databases > New Database > General > Input database name

d. right-click Databases > New Database > General > Input database name

When you try to drop a table, why do you sometimes get an error message saying the system can't remove it due to a constraint? a. the dropped table has active links to other tables b. you must save/archive the latest data before dropping a table c. the dropped table contains no data d. the dropped table is referenced by a FOREIGN KEY constraint in another linked table

d. the dropped table is referenced by a FOREIGN KEY constraint in another linked table