MTA Exam 1

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What are the default ports to secure a SQL server?

41 and 42

80 and 82

1433 and 1434 (correct)

8080 and 8090

Which two methods can be used to grant temporary permissions to users when you cannot create new groups?

Add the users to an existing role that has appropriate access (correct)

Grant each user explicit permissions to the role (correct)

Create a view with shared permissions

Remove existing role permissions temporarily

What action can a user perform on data that is on the server if granted unlimited access?

Read-only

Delete only

Unlimited (correct)

To allow a user to read data in a table, which command should be used?

Grant select Signup and view all the answers

What does the term 'truncate' refer to in a database context?

Removing data Signup and view all the answers

What is the purpose of using a unique index in a database?

It ensures that the index key contains no duplicate values. Signup and view all the answers

In a relational database, what is the requirement for a foreign key in a table?

Must refer to a primary key of another table Signup and view all the answers

Which command is used to remove a table from a database?

Drop table Signup and view all the answers

What should be applied to the Employee table to ensure an employee can only be assigned to an existing department?

A foreign key Signup and view all the answers

What type of data should be recommended for storing financial amounts in a database?

Money Signup and view all the answers

Which command is used to create a query returning a set of table data by using the UPDAT statement?

No change is needed Signup and view all the answers

What type of constraint ensures that data entered into a column is valid?

Constraint Signup and view all the answers

Which command would you use to add a column to an existing table?

Alter Signup and view all the answers

Which SQL statement is used to retrieve rows from a table?

Select Signup and view all the answers

How is a non-clustered index defined on a table?

Using a clustered index or view Signup and view all the answers

What is one limitation of a delete statement in SQL?

It cannot specify a where clause. Signup and view all the answers

What is the recommended method to store volunteer data with given name, surname, telephone number, and email address?

Create a table that contains columns for given name, surname, phone number, and email. Signup and view all the answers

Which database object should you add to the Sales table to ensure valid associated salesperson records?

Foreign key Signup and view all the answers

Which DDL keyword is used to delete a database table?

Drop Signup and view all the answers

Which statement is true regarding the ALTER statement?

It is used to modify an existing table. Signup and view all the answers

First normal form requires a database to exclude which of the following?

Repeating groups Signup and view all the answers

Which statement about clustered and non-clustered indexes is true?

A clustered index stores all table data in order based on the index key. Signup and view all the answers

What is the purpose of a foreign key in a database?

To ensure data integrity and establish relationships between tables. Signup and view all the answers

What is the relationship of StateID in the state table?

It is a primary key in the state table. Signup and view all the answers

Which statement about SQL Server logins is incorrect?

A SQL Server login is granted permission to database view only. Signup and view all the answers

What is the purpose of the SELECT statement in SQL?

To retrieve rows from one or many tables. Signup and view all the answers

Which SQL clause is used to specify criteria for selecting records?

WHERE Signup and view all the answers

Which constraint ensures that each value in the ID column is unique?

Primary Key Signup and view all the answers

If you want to retrieve rows from a table, which DML command should you use?

SELECT Signup and view all the answers

What data type is recommended for storing financial transactions?

Money Signup and view all the answers

To improve query performance on a column, which item should you consider adding?

Index Signup and view all the answers

What type of constraint would you use to establish a relationship between two tables?

Foreign Key Signup and view all the answers

Which data type should be used to store project names that range from three to 30 characters while minimizing storage space?

Varchar (30) Signup and view all the answers

What is required to define a column in a SQL table?

Data type and name Signup and view all the answers

What should you specify for a column that cannot contain duplicate values in a SQL table?

UNIQUE Signup and view all the answers

What SQL keywords are valid to use in a CREATE TABLE statement?

Primary Key and Foreign Key Signup and view all the answers

Which SQL statement correctly creates a view that returns a list of games sorted alphabetically?

Select Name from Games order by Name Signup and view all the answers

When creating an integer-type column that must be assigned a value between 1 and 10, what should you include?

A CHECK constraint Signup and view all the answers

Using a relational database minimizes redundant data and allows data retrieval:

Quickly and easily Signup and view all the answers

If you want to ensure a column in a SQL table cannot be empty, what constraint should you specify?

NOT NULL Signup and view all the answers

What is the characteristic of a non-clustered index?

Does not affect the physical order of data storage. Signup and view all the answers

Which statement correctly describes a unique index?

Ensures each row has a unique index key. Signup and view all the answers

Which of the following statements about clustered indexes is true?

They sort and store data based on the indexed columns. Signup and view all the answers

Which SQL statement correctly updates a table named loanedBooks?

Update loanedBooks set Books = 1 Where (Name = 'Harry' AND City = 'San Francisco') Signup and view all the answers

What is the appropriate SQL statement to create a table named Student with specified attributes?

Create Table Student (ID INT, Name Varchar(100), Age Int) Signup and view all the answers

What is true about filtered indexes?

They help optimize queries that select a small percentage of rows. Signup and view all the answers

Which SQL statement can be used to create an index on a column?

Create Index Employee_Index on Employee(EmployeeID) Signup and view all the answers

Which statement describes the relationship between clustered and non-clustered indexes?

Non-clustered indexes do not affect the order of records. Signup and view all the answers

Study Notes

MTA Exam Questions Review

SQL Server Default Ports: The default ports for securing a SQL server are 1433 and 1434.
Temporary User Permissions (2 methods): Two ways to grant temporary permissions to users on multiple tables without creating new groups are granting explicit permissions to the role, and adding users to an existing role with appropriate access.
User Actions on Server Data: Users can perform actions on database objects on the server. The extent of these actions can vary.
Granting Read Access: The ALLOW SELECT command grants select permission to read data in a table. If it makes the statement correct to read data, state No change is needed. If the statement is incorrect, correct the statement by saying Grant select. Or Permit read.
Database Operations (Truncate): Truncate is a database operation used to clear/correct data if a database is damaged or corrupt. (NOT for backups). Other terms that could pertain, and should be used if appropriate, are Attach and Commit (or Restoring).
Foreign Key Constraints: In relational databases, foreign keys in a table must be unique in that table and cannot refer to the primary key of another table. Foreign keys cannot contain null values.
Employee Table Assignment: To ensure that employees can only be assigned to existing departments, a foreign key should be added to the Employee table linking the Employee table with department table.
SQL Query for Table Data: The statement UPDATE is not used to retrieve data, but SELECT is.

Database Table Structure and Data Types

SQL Database Table Keywords: To delete a table use DROP.
Data Types and Normal Form: First normal form requires elimination of repeating groups and duplicate rows in table data.
SQL Database Table Relationships: A primary key is a constraint ensuring each row in the Table is unique and is related to other tables. A foreign key in one table can point to a primary key in another table.

Database Objects and Constraints

Removing Tables: The command to remove a table is DROP TABLE.
Data Integrity: Using Constraints, especially Foreign Keys, keeps the reference data consistent across related tables, preventing orphaned rows and inconsistencies.
Data Types: Use INT for numerical data in a table column. Use VARCHAR for string data, but specify the maximum length.
Indexes: Indexes improve query performance by speeding up data retrieval. A clustered index sorts data physically on disk. A non-clustered index doesn't. Indexes are often added to commonly queried fields.

Data Validation and Relationships

Data Types For Financial Transactions: Recommendations for financial transactions in a database table are MONEY and DECIMAL to support precise calculations and avoid rounding errors.
Unique Values & Data Integrity (Constraints): A primary key ensures a unique value for each row in a table, guaranteeing distinct identification for all records.
Relationships and Data Consistency: Use foreign keys to ensure data consistency by enforcing connections between related tables, preventing invalid or orphaned data.
Adding Columns: The correct command to add a column to an existing table is ALTER TABLE.
Data Consistency: A database with constraints guarantees a consistent set of data in related tables, improving data integrity.

Query and Data Manipulation

Query Performance Optimization: Use indexes on columns frequently searched.
Data Modification Statements:
- UPDATE for changing data in a table
- DELETE to eliminate rows.
- INSERT to add new rows.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.