Microsoft SQL Server: Database Management and Security, Exams of Deductive Database Systems

Download Microsoft SQL Server: Database Management and Security and more Exams Deductive Database Systems in PDF only on Docsity! MTA LinkedIn Learning Questions And Answers 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 - correct answer d. DBMS 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 - correct answer b. flat file True or False: Anything that can't be illustrated with a branching tree diagram would be a good fit for a hierarchical database design. - correct answer False MTA LinkedIn Learning Questions And Answers 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 - correct answer d. key 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 - correct answer b. false A database _______ can either be a dedicated machine or a virtualized machine that is running the database management software. a. table b. server MTA LinkedIn Learning Questions And Answers c. right-click Databases > New Database > Options > Input database name d. right-click Databases > New Database > General > Input database name - correct answer d. right-click Databases > New Database > General > Input database name 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 - correct answer d. Databases > your database name > Tables > Table 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 MTA LinkedIn Learning Questions And Answers c. Databases > your database name > right-click your table name > Design d. Databases > right-click your table name > Design - correct answer c. Databases > your database name > right-click your table name > Design 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 - correct answer c. Databases > right-click database name > Delete > select close exiting connections The mechanism that SQL Server uses to recombine data from multiple related tables on the fly is called a ________. MTA LinkedIn Learning Questions And Answers a. combining b. Sequel c. view d. relations - correct answer c. view The _______ database is what SQL Server uses to manage its own instance and configuration. a. model b. master c. msdb d. tempdb - correct answer b. master The _______ database is essentially the template for all new databases that are created on the server. a. model b. tempdb c. msdb MTA LinkedIn Learning Questions And Answers 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 - correct answer b. add Salary field to the table _______ simply deletes objects from the database. a. Remove b. Delete c. Drop d. Alter - correct answer c. Drop You would use _______ to delete every record from a data table but leave the structure of the table intact. MTA LinkedIn Learning Questions And Answers a. delete b. remove c. truncate d. edit - correct answer c. truncate 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 - correct answer c. delete the table and all of its records _______ processing means that if anything in the statement fails, nothing gets processed and the database rolls back to its prior state. MTA LinkedIn Learning Questions And Answers a. Transactional b. Merge c Manipulation d. Non-transactional - correct answer a. Transactional 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 - correct answer c. It will match all the Item values that start with a V and then any characters after it. MTA LinkedIn Learning Questions And Answers 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 - correct answer a. true _______ will store a whole number between zero and 255. a. Smallint b. Tinyint c. Decimal d. Smallmoney - correct answer b. Tinyint _______ 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 MTA LinkedIn Learning Questions And Answers c. Small money d. Big int - correct answer b. Floating point 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 - correct answer d. DateTime2 Only use a _______ type when the values will dramatically vary in size. a. nchar b. char c. varchar d. decimal - correct answer c. varchar MTA LinkedIn Learning Questions And Answers 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 - correct answer b. flat grid; curve You should use this data type to store external files in your database. a. Varchar b. file c. Bigint d. binary - correct answer d. binary Implicit conversion happens where you don't have to worry about the _______ in the original computation. a. data type mismatch MTA LinkedIn Learning Questions And Answers - correct answer d. CREATE UNIQUE INDEX IX_Departments ON Departments(DepartmentName) 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 - correct answer a. CREATE DATABASE MyDatabase GO USE MyDatabase 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 MTA LinkedIn Learning Questions And Answers 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 - correct answer b. it establishes that the LastName field uses an N VarChar, so it is a Unicode data type 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 - correct answer d. INNER JOIN The following SELECT statement will return valid results: SELECT * FROM Customers WHERE State = 'FL' AND State = 'CA' a. true MTA LinkedIn Learning Questions And Answers b. false - correct answer b. false 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 - correct answer b. BETWEEN 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 - correct answer b. Not What will the following query return? MTA LinkedIn Learning Questions And Answers c. Views can only expose the rows and columns that you specify. d. all of these answers - correct answer d. all of these answers 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 - correct answer a. CREATE VIEW vwMyview AS SELECT ProductListing FROM Product MTA LinkedIn Learning Questions And Answers 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 - correct answer c. create a view that will tell us which colors have sold the most and which have sold the least _______ 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 - correct answer d. Stored procedures MTA LinkedIn Learning Questions And Answers What command executes a stored procedure? a. alter mystoredproc b. mystoredproc GO c. exec mystoredproc d. run mystoredproc - correct answer exec mystoredproc 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" - correct answer a. creates a stored procedure that accepts a user-defined parameter named "state" MTA LinkedIn Learning Questions And Answers a. true b. false - correct answer b. false It's better to delete records using their unique identifier. a. true b. false - correct answer a. true 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 - correct answer d. Where 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? MTA LinkedIn Learning Questions And Answers 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 - correct answer d. the dropped table is referenced by a FOREIGN KEY constraint in another linked table 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 - correct answer a. true _______ is the process of organizing data into multiple tables to reduce redundancy from a database. MTA LinkedIn Learning Questions And Answers a. Deletion b. Consistency c. Simplification d. Normalization - correct answer d. Normalization 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 - correct answer a. 1NF 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 MTA LinkedIn Learning Questions And Answers 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 - correct answer d. composite keys 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 MTA LinkedIn Learning Questions And Answers - correct answer c. hold down the Ctrl key > select both keys > right-click and choose Set Primary Key Once a primary key is established on a table, other tables can't make use of that field. a. true b. false - correct answer b. false 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 - correct answer d. foreign key You can have multiple clustered indexes on a table. a. true MTA LinkedIn Learning Questions And Answers b. false - correct answer b. false 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 - correct answer d. clustered 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 MTA LinkedIn Learning Questions And Answers d. all of these answers - correct answer d. all of these answers 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 - correct answer d. Object Explorer > Security folder > find the Logins folder > right-click on the folder and choose New Login 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 MTA LinkedIn Learning Questions And Answers b. mixed-mode c. group d. users - correct answer a. Windows 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 - correct answer b. false 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 MTA LinkedIn Learning Questions And Answers c. Security > Server Roles > double-click on user's username > Status > Deny d. Security > Credentials > double-click on user's username > Server Roles > Deny - correct answer b. Security > Logins > double-click on user's username > Status > Deny _______ 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 - correct answer c. User-defined _______ roles are predefined permissions that allow users to perform standard server maintenance tasks. a. Public b. User-definied c. Fixed MTA LinkedIn Learning Questions And Answers 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 - correct answer 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 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 - correct answer a. true 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. MTA LinkedIn Learning Questions And Answers a. Role b. users c. security d. schemas - correct answer a. Role 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 - correct answer a. true 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 - correct answer a. guest MTA LinkedIn Learning Questions And Answers Disabling the guest account will accidentally reveal sensitive information to the entire pool of users on the server. a. true b. false - correct answer b. false 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 - correct answer c. simple A differential backup, which is sometimes called a Delta, contains only the data that has changed since the last full backup. a. true