1. Exam 98-364 Database Fundamentals
1.1. Understanding Core Database Concepts
1.1.1. Особенности SQL
1.1.2. Запрос (query)
1.1.3. Подмножества языка SQL
1.1.4. СУБД, ГПИ (DBMS, GUI)
1.1.5. База данных (Database), 3 фунд. хар.
1.1.6. SQL
1.1.7. Виды БД
1.1.7.1. Flat file
1.1.7.2. Hierarchical
1.1.7.3. Relational
1.1.7.4. Non-relational
1.1.8. Таблица, поле, запись
1.1.9. Виды связей, структура
1.1.9.1. One-to-one
1.1.9.2. One-to-many
1.1.9.3. Many-to-one
1.1.9.4. Many-to-many
1.1.10. Constraints
1.1.10.1. Primary key
1.1.10.2. Foreign key
1.1.10.3. Unique
1.1.10.4. NOT NULL / NULL
1.1.10.5. Check
1.1.10.6. Default
1.1.11. Database objects
1.1.12. System tables
1.1.13. Index
1.1.14. MS SQL Server, SSMS, Transact-SQL language
1.2. Creating Database Objects
1.2.1. Data types
1.2.1.1. Exact numbers
1.2.1.2. Approximate numbers
1.2.1.3. Date and time
1.2.1.4. Character strings
1.2.1.5. Unicode character strings
1.2.1.6. Binary strings
1.2.1.7. Other data types
1.2.1.8. CLR data types
1.2.1.9. Spatial data types
1.2.2. Precision, scale
1.2.3. Understanding implicit conversions, cast, convert
1.2.4. Regular character, Unicode character
1.2.5. VAR element
1.2.6. Creating and Using Tables
1.2.6.1. Using T-SQL
1.2.6.2. Using SSMS
1.2.7. Creating Views
1.2.7.1. Using T-SQL
1.2.7.2. Using SSMS
1.2.8. Creating Stored Procedures
1.2.9. SQL injection
1.3. Manipulating Data
1.3.1. SELECT, WHERE, logic operators
1.3.2. JOINS
1.3.3. UNION, EXCEPT, INTERSECT
1.3.4. Inserting data
1.3.4.1. Using T-SQL
1.3.4.2. Using SSMS
1.3.5. Updating Data and Databases
1.3.6. Deleting Data (DELETE, DROP, TRUNCATE)
1.3.7. Referential Integrity
1.4. Understanding Data Storage
1.4.1. Normalization
1.4.1.1. 5 normalization forms
1.4.1.2. How to normalize a DB to the 3 NF
1.4.2. Primary, Foreign, and Composite Keys
1.4.3. Indexes
1.4.3.1. Clustered
1.4.3.2. Non-Clustered
1.5. Administering a Database
1.5.1. Securing Databases
1.5.1.1. database security
1.5.1.2. permission
1.5.1.3. login
1.5.1.4. user account
1.5.1.5. SQL Server Security Model
1.5.1.6. Server-Level Security
1.5.1.7. Database-Level Security
1.5.1.8. Windows Security
1.5.1.8.1. SQL Server service accounts
1.5.1.8.2. Windows Authentication
1.5.1.8.3. SQL Authentication
1.5.1.8.4. Database Server Roles
1.5.1.8.5. Granting Access to a Database
1.5.1.8.6. Fixed Database Roles
1.5.1.8.7. Object Permissions
1.5.1.8.8. Managing Roles
1.5.1.8.9. Ownership Chains
1.5.2. Backing Up and Restoring Databases
1.5.2.1. Recovery Models
1.5.2.1.1. Simple Recovery
1.5.2.1.2. Full Recovery
1.5.2.1.3. Bulk-Logged
1.5.2.2. Database Backups
1.5.2.2.1. Full backup
1.5.2.2.2. Differential backup
1.5.2.2.3. Incremental backup
1.5.2.2.4. Types of database backups
1.5.2.2.5. Types of partial backups
1.5.2.2.6. Types of file backups
1.5.2.3. Database Restores
1.5.2.3.1. Restore scenarios
1.5.2.3.2. Using SSMS
1.5.2.3.3. Using T-SQL RESTORE command
2. Advanced SQL functions
2.1. CASE WHEN
2.2. COALESCE
2.3. CONVERT
2.4. STRING functions
2.4.1. CONCAT
2.4.2. SUBSTRING
2.4.3. REPLACE
2.4.4. TRIM
2.4.5. LEN
2.4.6. LEFT, RIGHT
2.4.7. UPPER, LOWER
2.4.8. CHARINDEX
2.4.9. PATINDEX
2.5. REGEX
2.6. DATE and TIME functions
2.6.1. DATEDIFF
2.6.2. DATEPART
2.6.3. YEAR
2.6.4. NOW
2.7. WINDOW functions
2.7.1. LEAD, LAG
2.7.2. AGGREGATE
2.7.3. RANKING
2.7.4. ROWS BETWEEN
2.8. Stored procedures
2.9. IF statement
2.10. Types of functions
2.10.1. built-in functions
2.10.2. user-defined functions
2.10.3. scalar
2.10.4. table-valued
2.11. PERCENTILE
3. Подготовка к собеседованию
3.1. Чек-лист
3.2. Работа с возражениями
3.3. Code Styling
3.4. Формат Резюме
4. Basic SQL functions
4.1. DML
4.1.1. SELECT
4.1.2. WHERE
4.1.3. INSERT INTO
4.1.4. UPDATE
4.1.5. DELETE
4.1.6. MERGE
4.1.7. Арифметические функции
4.1.7.1. +
4.1.7.2. -
4.1.7.3. *
4.1.7.4. /
4.1.7.5. %
4.1.8. Операторы сравнения
4.1.8.1. =, != (<>), <,<=,>,>=, !>, !<
4.1.9. Логические операторы
4.1.9.1. AND, OR, NOT
4.1.9.2. ALL, ANY, SOME
4.1.9.3. IN, NOT IN, BETWEEN
4.1.9.4. EXISTS
4.1.9.5. LIKE
4.1.9.5.1. %
4.1.9.5.2. _
4.1.9.5.3. [ ]
4.1.9.5.4. [ - ]
4.1.9.5.5. [ ^ ]
4.1.10. Нюансы при работе с NULL
4.1.10.1. IS NULL, IS NOT NULL
4.1.10.2. NOT NULL
4.1.11. ISNULL( )
4.1.12. Агрегатные функции
4.1.12.1. MIN( )
4.1.12.2. MAX( )
4.1.12.3. AVG( )
4.1.12.4. SUM( )
4.1.12.5. COUNT( )
4.1.12.6. SQRT( )
4.1.13. GROUP BY
4.1.14. HAVING
4.1.15. ORDER BY
4.1.15.1. ASC
4.1.15.2. DESC
4.1.16. DISTINCT
4.1.17. TOP / LIMIT
4.1.18. JOINS
4.1.18.1. INNER JOIN
4.1.18.2. OUTER JOIN
4.1.18.2.1. FULL
4.1.18.2.2. LEFT
4.1.18.2.3. RIGHT
4.1.18.3. CROSS JOIN
4.1.18.4. SELF JOIN
4.1.18.5. Advanced Joins
4.1.19. UNION, UNION ALL
4.1.20. INTERSECT, EXCEPT
4.1.21. JOINS VS UNION, INTERSECT, EXCEPT
4.1.22. VIEW
4.1.23. SUBQUERY
4.1.24. CTE
4.1.25. CASE (WHEN-ELSE-END)
4.1.26. Window functions
4.1.26.1. OVER, ORDER BY, PARTITION BY
4.1.26.2. ROW_NUMBER
4.1.26.3. RANK
4.1.26.4. DENSE_RANK
4.2. DDL
4.2.1. CREATE
4.2.2. Constraints
4.2.2.1. Primary key
4.2.2.2. Foreign key
4.2.2.3. Unique
4.2.2.4. NOT NULL / NULL
4.2.2.5. Check
4.2.2.6. Default
4.2.3. Типы данных
4.2.4. ALTER
4.2.5. DROP
4.2.6. TRUNCATE
4.2.7. Разница между операторами DELETE, DROP и TRUNCATE
4.2.8. USE
4.3. DCL
4.3.1. GRANT
4.3.2. REVOKE
4.3.3. DENY
4.4. TCL
4.4.1. BEGIN TRAN
4.4.2. COMMIT TRAN
4.4.3. ROLLBACK
4.5. MS SQL Server , SSMS
4.5.1. Создание таблиц, ввод данных
4.5.2. Модель данных, database diagram
4.5.3. Импорт и экспорт данных
4.5.4. Сравнение MS SQL Server с MySQL