MTA Database Reviewer PDF

MTA EXAM QUESTIONS REVIEW Name: __________________________ Block: ___________________ 1. What are the default ports to secure a SQL server? a. 1433 and 1434 b. 80 and 82 c. 41 and 42 2. You need to grant a collection of users permissions to...

MTA EXAM QUESTIONS REVIEW Name: __________________________ Block: ___________________ 1. What are the default ports to secure a SQL server? a. 1433 and 1434 b. 80 and 82 c. 41 and 42 2. You need to grant a collection of users permissions to three tables on a temporary basis. You do not have permissions to create new groups. What are two ways to achieve this goal? Choose two a. Grant each user explicit permissions to the role b. Add the a new row, and grant permissions to the role c. Add the users to an existing role that has appropriate access 3. Instruction; Use the drop down menus to select the answer choice that completes each statement based on the information presented in the graphic. Each correct selection is worth one point. a. The user can perform ____ action on data that is on the server. Unlimited- No – read- only- Unlimited b. The user can perform ____ action on database objects that are on the server. Configuration - No – Unlimited 4. Use the ALLOW Select command to give user permission to read the data in a table. Review the underlined text. If it makes the statement correct. No change is needed. a. No change is needed c. Grant select b. Let read d. Permit Read 5. Truncate is a database term used to describe the process of applying a backup to damaged or corrupt database. a. No changed is needed. C, Commit b. Attach d. Restore 6. In a relational database a field that is a foreign key in a table can contain null values. a. No change is needed b. Must be unique for every row of the table c. must be a numeric data type d. cannot refer to the primary key of another table 7. You have a Department table and an Employee table in your database. You need to ensure that an employee can be assigned to only an existing department. What should you apply to the Employee table? a. A foreign Key c. A Unique constraint b. A primary key d. A data type 8. Create a query that returns a set of table data by using the UPDAT statement. a. No change is needed c. Insert b. Replace d. Select 9. Your class project requires that you help a charity to create a web site that registers volunteers. The website must store the following data about the volunteers:  Given name  Surname  Telephone number  Email address  You need to recommend a correct way to store the data  What do you recommend a. Create a table that contains Columns that are name given mane , surname, phone number, and email b. Create a view that contains Columns that are name given mane , surname, phone number, and email 10. You have two table named SalePerson and sales You need to ensure that each record in the Sales table has a valid associated salesperson record in the SalePerson table. Which database object should you add to the sales table? a. Foreign key c. Primary key b. Nonclustered index d. Clustered index 11. You need to delete a database table. Which data definition language (DDL) keyword should you use? a. Delete c. Drop b. Tunicate d. Alter 12. Use the ALTER statement to add a new table in a database a. NO changed c. Update b. Insert d. Create 13. First normal form require that a database excludes: a. Repeating groups c. Composite keys b. Duplicate rows d. Foreign keys 14. First normal form requires that a database excludes repeating group. a. No change is needed c. Composite keys b. Duplicate rows d. Foreign keys 15. Instructions for each of the following statements, select yes if the statements are true. No, if statements are not correct a. A non-clustered index stores all table data order. No b. A clustered index stores all table data in an unsorted No c. A clustered index and a non-clustered index can be used simultaneously on the same table. NO 16. StateID is a/an primary key in the state table a. A non-clustered index stores all table data order. b. A clustered index stores all table data in an unsorted c. StateID is a/an primary key in the Address table 17. You need to establish relationship between the data that is in the two table. a. Default key c. Foreign key b. Link key d. Index key 18. Description of Behavior. Yes or No a. A clustered index sorts and stores the data column of a table or view in order based on the clustered index key. yes b. A non-clustered index is defined on a table or view by using a clustered index or heap No c. A unique index ensures that the index key contains no duplicate value and that every row in the table or view is unique. Yes d. A filtered index is a clustered index that is optimized for queries that select a small percentage of rows from a table. No 19. You have the following SQL query. Select * From ado.ProAthlete Where Salary > 500,000 The query takes too much time to return data You need to improve the performance of the query. Which item should you add to the salary column? a. Foreign key c. Non-null constraint b. Index d. Default constraint 20. Which command should you use to removes a table from a database? a. Update c. Drop table b. Remove table d. Delete 21. A relational database management system employs the concept of An ATRIBUTE to ensure that data entered into a field in a column is valid. a. No change c. A primary key b. A constraint d. An index 22. You work at a coffee shop. They ask you to set up a website that stores charges on purchases. You need to recommend a data type in a database table to run financial functions against the charged amounts. Which data type should you recommend? a. Money c. Bit b. Binary d. Varchar 23. Which command should you use to add a column to an existing table? a. Alter c. Insert b. Update d. Change 24. A row holds information for a single record in a table. a. No Change c. Column b. Data type d. View 25. What is one difference between an update statement and a delete statement? a. An update statement can change only one row b. An update statement does not remove rows from a table c. A delete statement works only within a stored procedure 26. A delete statement cannot use a where clause you are writing an SQL statement to retrieve rows from a table. a. Get c. Select b. Output d. Read 27. Which constraint ensures a unique value in the ID column for each customer? a. Primary Key c. Distinct b. Foreign Key d. Sequential 28. Description of Behavior Yes or NO a. A SQL Server login can use Windows Authentication. yes b. A SQL Server Role provides permission at the server level. yes c. A SQL Server login is granted permission to database view only. No 29. Select retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. a. No Change b. Data type. c. Column d. View 30. What key specify condition? a. Where c. Oder By b. From d. Select 31. Which constraint ensure a unique value in the ID Column for each customers. a. Foreign Key b. Primary Key c. Index Key 32. You are writing a SQL Statement to retrieve rows from a table which data manipulation Language (DML) command should you use? a. Select b. Read c. Get 33. You work at a coffee shop. They ask you to set up a website that store charges on purchases. You need to recommend a data type in a database table to run financial functions against the charged amounts. Which data type should you recommend? a. Int b. Varchar c. Money 34. You have the following SQL query; Select * From dbo.ProAthlete Where salary > 500,000 The query takes too much times to return data. You need to improve the performance of the query. Which item should you add to the salary column? a. Primary Key b. Index c. DML 35. While attending college, you accept an IT internship at a local charity. The charity needs to report on data that is related and exists in two table. You need to establish a relationship between the data that is in the two tables. Which constraint should you define? a. Foreign Key b. Default Key c. Index Key 36. This question requires that you evaluate the underlined to determine if it is correct. First normal form require that a database excludes repeating groups. Instruction: Review the underlined text. If it makes the statement correct. Select “No change is needed.” If the statement is incorrect select the answer choice that makes the statement correct. a. No Change is needed b. Composite Keys c. Duplicate rows 37. Instruction: for each of the following statements, select Yes if the statements is true. Otherwise, Select No. Each correct selection is worth one Point. a. A non-Clustered index store all table data in order. False b. A Clustered index stores all table data in as unsorted order. False c. A clustered index and a non-clustered index can be used simultaneously on the same table. False 38. Select Yes if the statement is true or False it incorrect. a. A clustered index sorts and store the data columns of a table or view in order based on the clustered index key. False b. A non-clustered index is defined on a table or view by using a clustered index or heap. True c. A unique index ensures that the index key contains no duplicated values and that every row in the table or view is unique. True d. A filtered index is a clustered index that is optimized for queries that select a small percentage of rows from a table. False 39. You create the following table, while lists how many books you have on Loan to you friends. Harry in San Francisco returns books which statement will update your table correctly? a. Update loanedBooks set Books = 0 Where (Name = ‘Harry’ AND City = ‘San Francisco’) b. Insert Into LoanedBook set Books = 0 Where ID = 4 c. Update loanedBooks set Books = 0 Where (Name = ‘Harry’ OR City = ‘San Francisco’) e. Update loanedBooks set Books = 0 Where (Name = ‘Harry’ IN City = ‘San Francisco’) 40. Which statement will result in the creation of an index? a. Create table Employee (Employee ID Integer null) b. Create Table Employee (EmployeeID Integer Distinct) c. Create Table Employee ( EmployeeID Integer Primary Key) d. Create Table Employee (EmployeeID Integer Index)  43. You are creating a database named STUDENT to store the following data: ID Name Age 1 Rene 18 2 Tia 22 3 Oliver 25 Which syntax should you use to create the object? a. Create ( c. Create Student ( Table Student ID INT, ID INT, Name Varchar (100), Name Varchar (100)), Age Int ) Age INT) b. Create Table Student ( d. Create Table ( ID INT, ID Int, Name Varchar (100), Name Varchar (100), Age INT ) Age Int ) 44. You need to store project names that very from three to 30 characters. You also need to minimize the amount of storage space that is used. a. Varchar (30) b. Char (3,30) c. Char (30) d. Varchar (3,30) 45. This question requires that you evaluate the underlined text to determine if it is correct. You create a table of recently release video games and their review scores. To create a view that returns a list of game that is alphabetically sorted, the completed SQL statement should be the following: create view MYGames As select Name from Games, Instruction: Review the underlined text. If it makes the statement correct, select “No change is needed.” If the statement is incorrect, select the answer choice that makes the statement correct. a. No change is needed. C. Select Name from Games order by Name b. Select * from Games d. Select * from Games Where Name Between ’A’ And ‘Z’ 46. Which two elements are required to define a column? a. Data type and name b. Index and primary Key c. Name and index 47. You are developing a SQL statements to create a table. Which two SQL keywords are valid to use in a create table statement? a. Constraint and Primary Key c. Update and Select b. Insert into and Order by d. Primary Key and Foreign Key 48. When creating a table, what should you specify for a column that cannot contain duplicate values? a. CHECK b. UNIQUE c. DEFAULT d. NOT NULL 49. What should you include in a CREATE TABLE statement for an integer-type that must be assigned a value between 1 and 10 or not be assigned a value at all? a. A CHECK constraint c. A DEFAULT definition b. A FOREIGN KEY constraint d. A PRIMARY KEY constraint 50. Using a relational database allows you to minimize redundant data and index the data so data can be retrieved: a. quickly and easily. c. As long as no filter is specified. b. Without writing queries. d. Using a data definition language (DDL) trigger.