Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

MTA 98-364 Study Notes > MTA 98-364 Exam Prep > Flashcards

MTA 98-364 Exam Prep Flashcards

Study Guide and Exam Questions

1
Q

Data Definition Language(DDL) Commands: with creating database objects like tables, constraints, and stored procedures.
• CREATE: creates a new database object, such as a table.
• ALTER: used to modify the database object
• DROP: used to delete the objects.
Also include

A
  • USE:
  • RENAME:
  • TRUNCATE:
  • DELETE:
  • COMMENT
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Data Manipulation Language(DML) Commands:
• INSERT: used to insert a new data row record in a table.
• UPDATE: used to modify an existing record in a table.
• DELETE: used delete a record from the table.
Also include

A
  • MERGE:
  • CALL:
  • LOCK TABLE:
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Data Control Language(DCL) Commands:

A
  • GRANT: used to assign permission to users to access database objects.
  • REVOKE: used to deny permission to users to access database objects.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Data Transfer Language(DTL) Commands:

A
  • COMMIT: used to save any transaction into the database permanently.
  • ROLLBACK: restores the database to the last committed state.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Constraints are limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered
The Different types of constraints are

A

A unique constraint Enforces uniqueness on non primary key columns.

  • A check constraint allows the administrator to limit the types of data a user can insert into the database.
  • A default constraint is used to insert a default value into a column. If no other value is specified, the default value will be added to all new records.
  • A not null constraint ensures that data is entered into a cell. In other words, the cell cannot be blank. It also means that you cannot insert a new record or update a record without adding a value to this field.
  • The primary key constraint uniquely identifies each record in a database table. The primary key must contain unique values and it cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key.
  • A foreign key constraint in one table points to a primary key in another table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What common data type you would be used to count objects?

A

integer
Explanation: The int numeric data type is used to store mathematical computations and is used when you do not require a decimal point output. An example of an integer would be: 2 or –2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What common data type is used to store decimal numbers such as 3.14 and 7.07?

A

float

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How many bytes does the int data type take up?

A

4 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How many bytes does the money data type take up?

A

8 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How many bytes does the Smallmoney data type take up?

A

4 bytes ( Remember accuracy to 4 decimal points)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How many bytes does the Smallint data type take up?

A

2 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How many bytes does the Tinyint data type take up?

A

1 byte

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How many bytes does the float data type take up?

A

4-8 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How many bytes does the numeric data type take up?

A

5-17 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How many bytes does the decimal data type take up?

A

5-17 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How many bytes does the datetime data type take up?

A

8 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How many bytes does the Datetime2 data type take up?

A

6-8 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How many bytes does the Datetimeoffset data type take up?

A

8-10 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

How many bytes does the smalldatetime data type take up?

A

4 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

what datatype can store an integer between 0 and 255 and minimize the required storage

A

Tinyint

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Function Types?

A 
Scalar
Ranking
Aggregate
Rowset
User-defined
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Aggregate functions are?

A

Aggregate functions return a single value, calculated from values in a column

avg: returns the average of values in a numeric expression, either all or distinct.
count: returns the number of values in an expression, either all or distinct.
min: returns the lowest value in an expression.
max: returns the highest value in an expression.
sum: returns the total of values in an expression, either all or distinct.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Scalar functions are?

A

Scalar functions return a single value, based on the input value of a single field.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

User-defined functions are?

A

User-defined functions are compact segments of user-written SQL code that can accept parameters and
return either a value or a table.

25
Q

What is a correlated subquery?

A

A correlated subquery can be defined as a query that depends on the outer query for its evaluation.

26
Q

Table Valued functions are?

A

Table Valued functions; that returns a result set anywhere you can put the name of a table or name of a view
Within this there is 2 types a Inline function (Single Select) and Multi-statement

27
Q

To remember using the INSERT statement

A

• The INSERT INTO statement is used to insert a new row in a table.
• The INSERT INTO with SELECT statement is used to insert a new row in a table when a sub-select is used
instead of the VALUES clause.

28
Q

What statement would you use to change data in a table?

A

UPDATE
Explanation: The UPDATE clause allows you to modify the data stored in tables by using data attributes such as the following:

UPDATE
SET =
WHERE

29
Q

when do you use the Having Clause

A

HAVING Clause is used instead of WHERE for aggregate functions.

30
Q

what are Logical Operators?

A

Logical operators are used to SELECT statement to retrieve records based on one or more conditions. More than one logical operator can be combined to apply multiple search conditions.
OR Operator
AND Operator
NOT Operator

31
Q

what is a Range Operator

A

Range operator retrieves data based on range.
Types of Range operators:
BETWEEN
NOT BETWEEN

32
Q

What are the main Comparison Operators

A 
Example of some comparison operators:
=
<
>
<>
!
33
Q

What is CASE?

A

CASE creates when-then-else functionality (WHEN this condition is met THEN do this).
• The value of the case expression is the value of the first WHEN clause that is true.
If none is true, the result is the ELSE

34
Q

Different Types of SQL JOINs?

A 
(INNER) JOIN
LEFT (OUTER) JOIN
RIGHT (OUTER) JOIN 
FULL (OUTER) JOIN
Cross Join
Self Join
35
Q

(INNER) JOIN returns?

A

Returns records that have matching values in both tables

36
Q

LEFT (OUTER) JOIN returns?

A

Returns all records from the left table, and the matched records from the right table

37
Q

RIGHT (OUTER) JOIN returns?

A

Returns all records from the right table, and the matched records from the left table
Fetch all rows from table B, even when the corresponding data in table A are absent

38
Q

FULL (OUTER) JOIN returns?

A

Returns all records when there is a match in either left or right table

39
Q

What is a CROSS JOIN?

A

Also known as the Cartesian Product between two tables joins each row from one table with each row of another table. The rows in the result set is the count of rows in the first table times the count of rows in the second table.

40
Q

When do you use a JOIN?

A

JOIN is used to query data from two or more tables

41
Q

what is a UNION?

A

UNION combines two or more SELECT statements with an OR function

42
Q

What is an INTERSECT?

A

INTERSECT combines two or more SELECT statements with an AND function

43
Q

What is First Normal Form (1NF)

A

The First Normal Form (1NF) sets a few basic rules for a database: eliminate duplicative columns from the same table.

44
Q

What is Second Normal Form (2NF)

A

The Second Normal Form (2NF) = 1NF + removing subsets of data that apply to multiple rows of a table and place them in separate tables

45
Q

What is Third Normal Form (3NF)

A

The Third Normal Form (3NF) = 2NF + removing columns that are not dependent upon the primary key

46
Q

If you define a primary key, which of the following levels of normalization have you reached?

A

1NF
Explanation: Of the three conditions required to meet 1NF, as soon as you have defined a primary key for the table, you have met the first normalized form criteria.

47
Q

Which stage in the normalization process ensures that non-key columns are functionally dependent on the entire primary key?

A

2NF

• All the non-key columns are functionally dependent on the entire primary key.

48
Q

All of the normalisation levels deal with the elimination of redundant data to some extent, but which of the following normalisation levels first deals predominantly with the elimination of redundant data?

A

2NF

49
Q

What Normal form is met when tables are related using foreign keys?

A

2NF

50
Q

True or False

An INNER JOIN and condition is the same as a CROSS JOIN and a WHERE condition

A

True

51
Q

The _____ clause allows you to combine the results of any two or more queries into a resulting single set that will include all the rows which belong to the query.

A

union

52
Q

What is the Alter statement used for?

A

The ALTER statement changes an existing object; you can use it to add or remove columns
from a table, as shown in the following example:
ALTER TABLE Shirt
ADD Price Money;
GO
You can also use ALTER to change the definition of a view, stored procedure, trigger, or
function. For instance, the following command sequence redefines the view to include the
Price column:
ALTER VIEW Size AS
SELECT ProductID, ProductName, Price FROM Shirt
WHERE ProductType = ‘Size’;
GO

53
Q

What is a JOIN

A

Joins are used to retrieve data from more than one table together as a part of a single result set. Two or more tables can be joined based on a common attribute usually PK

54
Q

What is an EXCEPT?

A

The EXCEPT operator returns all records from the first SELECT statement that are not in the second SELECT statement.

55
Q

What is CASE?

A

CASE creates when-then-else functionality (WHEN this condition is met THEN do this).
• The value of the case expression is the value of the first WHEN clause that is true.
If none is true, the result is the ELSE

56
Q

What is the difference between EXCEPT and NOT IN

A

EXCEPT operator compares values in one or more than one columns. This means that the number of columns must be the same. On the other hand, the NOT IN operator compares values in a single column

57
Q

Using Expressions and Operators within a Select Statement

A 
Arithmetic ( + - / * % )
Assignment ( = )
Comparison ( >, =, <=, <>, ! )
Logical ( AND, OR, NOT, ANY, ALL, EXISTS )
String concatenation (+)
These two won't be in the exam
Unary ( +, -, ~ )
Bitwise ( %, | ,^ )
58
Q

5 types of WHERE clause’s?

A 
Comparison operators ( >, =, <=, <>, != )
Range Search ( BETWEEN)
List Search ( IN )
Character Matching Used for Strings ( LIKE )
Unknown Values ( IS NULL , NOT NULL )
59
Q

Pattern Matching-String Comparison Wildcard Operators used with the LIKE Clause in ‘strings’

A

% Any string of zero or more characters
_ Any single character
[] Any single character within the set (example ‘[AB]%’ )
[^] Any single character not within the set

MTA 98-364 Study Notes (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions