IT SPECIALIST EXAM -Databases

Product Description

IT SPECIALIST EXAM: Databases Exam 35 – 45 Questions

Databases: Candidates should be able to design and query relational databases, such as MySQL, Microsoft SQL Server, or Oracle. for this exam

Exam Objective : Databases Exam:

Database Design

1.1 Given a scenario, design tables for storing data

• Identify entities, rows/records, columns/fields

1.2 Given a scenario, identify the appropriate primary key

• Primary key, composite/compound key

1.3 Given a scenario, choose data types to meet the requirements

• Definition and importance of data types; how data types affect storage requirements; data types for storing text, numbers, dates and times, and Boolean values

1.4 Given a scenario, design relationships between tables

• How to establish relationships using primary and foreign keys, entity relationship diagrams (ERDs), referential integrity

1.5 Normalize a database

• Reasons for normalization, how to normalize a database to third normal form (3NF)

1.6 Given a scenario, identify data protection measures

• Backups, restore, principle of least privilege, GRANT, WITH GRANT OPTION, REVOKE, purpose of roles

 Database Object Management using DDL

2.1 Construct and analyze queries that create, alter, and drop tables

• Create, alter, and drop tables by using proper ANSI SQL syntax; NULL and NOT NULL

2.2 Construct and analyze queries that create, alter, and drop views

• Create, alter, and drop views by using proper ANSI SQL syntax; purpose of views

2.3 Construct and analyze stored procedures and functions

• Input and output parameters, return values, purpose of stored procedures

2.4 Given a scenario, choose between clustered and non-clustered indexes

• When to use clustered vs. non-clustered indexes, syntax for creating indexes

Data Retrieval

3.1 Construct and analyze queries that select data

• INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN (Cartesian product), and FULL OUTER JOIN; self joins; combine result sets by using UNION and INTERSECT; DISTINCT; column alias; computed columns

3.2 Construct and analyze queries that sort and filter data

• ORDER BY, WHERE, LIKE, BETWEEN, AND, OR, NOT, TOP (LIMIT), IN, NOT IN, ANY, ALL, NULL, NOT NULL, comparison operators

3.3 Construct and analyze queries that aggregate data

• GROUP BY, HAVING, MIN, MAX, COUNT, AVG (AVERAGE), SUM

Data Manipulation using DML

4.1 Construct and analyze INSERT statements

• INSERT INTO SELECT, INSERT INTO VALUES

4.2 Construct and analyze UPDATE statements

• Update data in a single table

4.3 Construct and analyze DELETE statements

• Delete data from a single table

Troubleshooting

5.1 Troubleshoot data object management query failures

• Syntax and runtime errors

5.2 Troubleshoot data retrieval query failures

• Syntax and runtime errors

5.3 Troubleshoot data manipulation query failures

• Syntax and runtime errors