Going for the MCSE: Data Management and Analytics

adam9870adam9870 Posts: 53Member ■■□□□□□□□□
Hello All,

Currently I have passed the exams: Microsoft 70-620, 70-642, 70-640, 70-462. I want to hold MCSE: Data Management and Analytics for a year.

I'm starting to prepare for the exam 70-461: Querying Microsoft SQL Server 2012/2014 or 70-761 Querying Data with Transact-SQL and 70-762 Developing SQL Databases.

I use offical traing kit for 70-461 exam. I was reading chapter 1, 2, 3, 4, 5, 6, 7 8. I need to go back to XML and Full Text Search, especially Semantic Search Functions. So far, I was install SQL Server 2014 SP2 on Windows 8.1 64-bit and I create test database from the script attach to the 70-461 Traing Kit.

I was starting to prepare two month ago, but I broke off because of private issues and workload. I went back to study this week. I feel the topic is demanding. The biggest progress I make during weekends. In this weekend I read full "Chapter 8 Creating Tables and Enforcing Data Integrity".

I try to test the solutions presented in the book in a practical way. For example, I processing data from XML file using OPENXML for import data about blocked prescriptions numbers from an XML file into a database and then display the content of the selected node of XML file.

I make a notes into file at Google Docs. This is my first topic of this type. I will try to update it 2-3 times a month. If I understand correctly, for achieve MCSE: Data Management and Analytics just pass the exams:
- First way: MCSA: SQL Server 2012/2014 (70-461, 70-462, 70-463) and 70-473, 70-475, 70-464, 70-465, 70-466 or 70-467 exams.
- Second way: MCSA: SQL 2016 Database Development (70-761/70-762) and 70-473, 70-475, 70-464, 70-465, 70-466 or 70-467 exams.

Sombody currently preparing for 70-461, 70-761 or 70-762 exams? What materials do you use?
«1

Comments

  • EagerDinosaurEagerDinosaur Posts: 114Member
    I'm aiming for MCSE: Data Management and Analytics as well.

    I already have MCSA: SQL Server 2012/2014, which involved passing 70-461. I thought it was one of the easier MS exams, the MS "Training Kit" book covered it quite well. I probably benefitted from having about 20 years general SQL Server experience, which helped guide my guesses when I hadn't studied a topic enough. I found it easier (and more job-relevant) than 462 or 463.

    I've just ordered the MS book for 70-762. That exam looks like a real bargain for me. If I pass it, I will get both MCSA: SQL Server 2016 and MCSE: Data Management and Analytics.

    Also, I think MS have just expanded the list of electives for the MCSE, which is nice:
    https://buildazure.com/2017/02/14/mcse-data-management-and-analytics-certification/
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    From end of June, I was read over 200 pages of 70-461 training kit. There are 130 pages left, I will probably read them within 2 weeks. Sometimes it is difficult to find time to study in the day after work. Sometimes they are topics over which I spend more time than I would like.
    I make notes in the form of short (1-2 minutes) video recordings and blog notes. Also I make notes in google docs. In the coming months I will probably publish these notes in an orderly form.
    Recently I was read and I labbing topics such as:
    • Chapter 9 Designing and Creating Views, Inline Functions, and Synonyms
    • Chapter 10 Inserting, Updating, and Deleting Data
    • Chapter 11 Other Data Modification Aspects
    • Chapter 12 Implementing Transactions, Error Handling, and Dynamic SQL
    Today I have day off and I read about stored procedures and triggers.
    After finishing the book we will all practice again. I have no memory for the code. I have heard that on the exam It is important knowledge of syntax. I have an idea for a database for exercises and some tasks. If you have any ideas for exercises - let me know.

    @EagerDinosaur: thans for you answer. When you plan to pass 70-762?
  • DatabaseHeadDatabaseHead Posts: 2,294Member ■■■■■■■■□□
    Glad those days are over. 461 was rough.
  • aaronchristensonaaronchristenson Posts: 261Member
    70-464 Developing Microsoft SQL Server Databases is next on my list of exams to take. I am taking the 5 day course from New Horizons in a few weeks. We had some training vouchers at work that were about to expire so I signed up to use them. This will complete the MCSE Data Management & Analytics.
    Aaron
    MCSE Cloud Platform and Infrastructure, MCSA Windows Server 2012, MCSA SQL Server 2012/2014, MCSA Windows 10, MCITP Server Admin, Security+, Virtualization with Windows Server Hyper-V and System Center Specialist
  • EagerDinosaurEagerDinosaur Posts: 114Member
    adam9870 wrote: »
    @EagerDinosaur: thans for you answer. When you plan to pass 70-762?

    I passed it about a week ago, MS confirmed the MCSE about 24 hours later. 762 seemed difficult while I was taking it, but I think the scoring criteria must have been fairly generous. It was quite interesting, lots of questions about what kind of index to choose for a particular business scenario. It covers some of the same data-warehouse design issues that 70-463 covers.
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    I just finished reading chapter 15 of 70-461 trainig kit (about topics such as indexes, statistics etc.)
    Currently I do not feel the indexes are my strength. As for me, this is poorly explained in the book.
    I watched a webcast about indexes, I have to sit down to him again after when I finish the book.

    @aaronchristenson: Please let me know how it went after the exam. It may interest not only me.

    @EagerDinosaur: What materials to use to prepare for 70-463? The training kit does not have good ratings on the internet. Or on what topics to focus on? On the forum someone recommended this book
    https://www.amazon.com/Microsoft-Server-2008-Integration-Services/dp/0470525762
    what do You think about it?
    What plans after passing the MCSE? :)
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    Today I'm done 70-461 Training Kit.
    Now it's time to repeat the material, sample tasks etc.
    I plan to take the exam 17 October. (Orientation)
  • nachodbanachodba Posts: 192Member ■■□□□□□□□□
    Good luck with your studying!
    2017 Goals - MCSA SQL 2016 Database Administration, MCSA Windows Server 2016
  • DatabaseHeadDatabaseHead Posts: 2,294Member ■■■■■■■■□□
    Adam good luck on your goals. Stay calm and cool and let the information that you have learned come to the fore front of your mind. You'll do fine....
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    @nachodba / @ DatabaseHead - Thanks.

    I spent the weekend reading Microsoft Docs about XML in SQL Server, including FOR XML AUTO, FOR XML RAW, FOR XML PATH.

    I heard that in the book (Training Kit 70-461) this topic is very modest compared to what is on the exam.

    I like the most FOR XML PATH because it gives you the greatest control of XML structure. In work I prepare XML fileaccording to the guidelines of another system to which my XML file will be imported.

    In my plans I have to try to generate XML according to what I see everyday in the work to exchange data with national health fund.

    I also found a record of a video session about XML in SQL Server from the TechEd conference.
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    Example: how store binary data in the database (for example: .DOC file)
    CREATE TABLE [dbo].[doc]
    (
    id int identity(1,1) not null,
    "file" varbinary(max),
    fileextension nvarchar (10),
    filename nvarchar(255),
    title nvarchar(255)
    )
    
    INSERT INTO [dbo].[doc]
    (fileextension,
    filename,
    title,
    "file"
    )
    SELECT 
    N'doc',
    N'test1.doc',
    N'Lewis Hamilton wins in Singapore after Ferrari crash',
    bulkcolumn
    FROM OPENROWSET(BULK 'C:\test\test1.doc',
    SINGLE_BLOB) AS doc;
    
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    I am playing with Full-Text-Search. icon_smile.gif
    It is very important to write queries in practice, create tables, keys, consider what columns and data types to create, how to insert long text into columns, how to insert text with an apostrophe in the middle etc.
    Recently I use FTS to see if the comments are positive or negative.
    I continue to work...
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    I need some time before I pass the exam.
    I did not sign up for exam in October.
    I am devoting time to working and learning SQL Server - lately I am practicing various topics, for example: create own functions and procedures, views, choosing the right data types, 'partition by', options etc. If you have hints on what to practice - I will be happy to listen.
    The amount of material is high and the exam seems difficult. I prefer to prepare well than to take the exam several times.
    Current - orientation date - my 70-461 exam is: December 2017.
    Greetings to the readers. :)
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    I plan to pass the exam on January 11, 2018.

    Usually, after work, I was tired and now I changed the plan of the day:
    - 3.00 am - wake up call
    - learning until 7 am
    - 7am - preparation for work
    - work from 8 am to 4pm
    - 4 pm - 8 / 9pm - time for chillout

    My recent notes:

    -- step 1
    @TRANCOUNT AS number_of_transactions_step1
    -- step 2
    BEGIN TRANSACTION
    -- step 3
    @TRANCOUNT AS number_of_transactions_step3
    -- step 4
    ROLLBACK TRANSACTION
    -- step 5
    @TRANCOUNT AS number_of_transactions_step5

    XACT_ABORT is using for rollback transaction transaction when SQL statement raises a run-time error. The adoption that I see in practice: data import and rollback if any problem occurs

    SET XACT_ABORT ON
    SET XACT_ABORT OFF

    It is possible to combine XACT_ABORT with XACT_STATE () in TRY / CATCH. XACT_STATE allows you to specify the transaction status.

    I will try to post notes of topics that I work on every few days.

    Links:
    SET XACT_ABORT (Transact-SQL)
    XACT_STATE (Transact-SQL)
    BEGIN TRANSACTION (Transact-SQL)
    COMMIT TRANSACTION (Transact-SQL)
    ROLLBACK TRANSACTION (Transact-SQL)
    @@TRANCOUNT (Transact-SQL)
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    Below is an example of how works SOME / ANY, ALL, IN, EXISTS

    use tempdb
    go

    create table test_city
    (CityId int identity(1,1) not null primary key,
    CityName varchar(255) not null);

    create table test_person
    (PersonId int identity(1,1) not null primary key,
    PersonName varchar(255) not null,
    CityId int not null
    constraint FK_test_person_CityId foreign key
    references test_city(CityId));

    insert into test_city
    (cityName)
    values ('New York'),
    ('Bangkok'),
    ('Dubai'),
    ('Barcelona'),
    ('Shanghai'),
    ('Hong Kong'),
    ('Tokyo'),
    ('Seoul'),
    ('Istanbul'),
    ('Kuala Lumpur');

    select * from test_city

    insert into test_person
    (PersonName, CityId)
    values
    ('Harold Crane', '1'),
    ('James Crow', '2'),
    ('William Gull', '3'),
    ('Logan Quail', '3');

    select * from test_person

    select cityid, cityName from test_city as tc
    where not exists (select * from test_person as tp where tc.CityId = tp.CityId)

    select cityid, cityName from test_city as tc
    where exists (select * from test_person as tp where tc.CityId = tp.CityId)

    select cityid from test_city
    where cityid in (select cityid from test_person)

    select cityid from test_city
    where cityid = any(select cityid from test_person)

    select cityid from test_city
    where cityid = some(select cityid from test_person)

    select cityid from test_city
    where cityid = all(select cityid from test_person)
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    Today I kept repeating how they work NEWSEQUENTIALID, NEWID functions and sequence objects. I wrote post on my blog about this topic. I provide an address for those interested


    https://adam9870.blogspot.com/2017/12/en-sql-server-2014-sp2-surrogate-keys.html


    Cheers! :)
  • aaronchristensonaaronchristenson Posts: 261Member
    I have 70-464 Developing Microsoft SQL Server Databases scheduled for Tuesday morning.
    Aaron
    MCSE Cloud Platform and Infrastructure, MCSA Windows Server 2012, MCSA SQL Server 2012/2014, MCSA Windows 10, MCITP Server Admin, Security+, Virtualization with Windows Server Hyper-V and System Center Specialist
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    @aaronchristenson: Great, good luck! let me know how it went.


    My next post on blog.
    [EN] SQL Server 2014 SP2 - how works TOP and OFFSET?
    https://adam9870.blogspot.com/2017/12/en-sql-server-2014-sp2-how-works-top.html
  • aaronchristensonaaronchristenson Posts: 261Member
    Did not go well, I was not ready and after a holiday I should have known better. I might take it again but it will be awhile.
    Aaron
    MCSE Cloud Platform and Infrastructure, MCSA Windows Server 2012, MCSA SQL Server 2012/2014, MCSA Windows 10, MCITP Server Admin, Security+, Virtualization with Windows Server Hyper-V and System Center Specialist
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    @aaronchristenson: It will be fine, I'll keep my fingers crossed!
    Any suggestions about topics from exams (with respecting NDA) for future candidat?
    Recently, I'am sitting on the .WRITE function and RAISERROR.

    15 days until the my 70-461 exam.
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    I finded good artice about MERGE.
    https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

    In addition to MERGE, today I check how doe EOMONTH function, DATEADD, DATENAME, DATEDIFF.

    14 days until the my 70-461 exam.
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    I signed up for the exam officially. I chose the date 16 January 2018 because others date were unavailable.

    [EN] SQL Server 2014 SP2 - how works IIF?
    https://adam9870.blogspot.com/2018/01/en-sql-server-2014-sp2-how-works-iif-pl.html

    So 15 days until the my 70-461 exam.
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    Today I reading about cursors and optimalization, for example:
    T-SQL: A Simple Example Using a Cursor - Steve Stedman
    https://www.sqlcopilot.com/optimize-for-ad-hoc-workloads.html

    13 days until the my 70-461 exam.
  • ZorodzaiZorodzai Posts: 341Member
    All the best. Am following your journey as my team and I work with SQL and this is a track my team could benefit from.
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    Something from today: foreign key must be created on a column which refers to a column that is the primary key in another table.

    9 days until the my 70-461 exam.

    @Zorodzai: thank you for response. I'm very pleased, but it seems to me that what I am writing about is basics of T-SQL - I have to learn a lot myself. Greetings to You and Your Team from Poland. icon_smile.gif
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    I found a video about XML in SQL Server:

    https://www.youtube.com/watch?v=WIg64plWH80

    My 70-461 exam will be day after tomorrow.
  • adam9870adam9870 Posts: 53Member ■■□□□□□□□□
    I passed 70-461 with 757/1000.
    The exam was demanding. I'm confused.
    There were many tasks which consisted in the fact that you had to write the code. It's good to remember the syntax of the different commands. In normal life, fortunately, there is Microsoft Docs.
    Someone can recommend books to preparation for 70-463 for a beginner in the data warehousing topic?
  • ZorodzaiZorodzai Posts: 341Member
    Congrats, well done on the pass
  • nachodbanachodba Posts: 192Member ■■□□□□□□□□
    Congrats on the pass!
    2017 Goals - MCSA SQL 2016 Database Administration, MCSA Windows Server 2016
«1
Sign In or Register to comment.