Sylvia Moestl Vasilik - SQL Practice Problems: 57 beginning, intermediate, and advanced challenges for you to solve using a learn-by-doing approach

SQL Practice Problems 57 beginning, intermediate, and advanced challenges for you to solve using a "learn-by-doing" approach Sylvia Moestl Vasilik Copyright 2016 by Sylvia Moestl Vasilik All rights reserved. This book or any portion thereof may not be reproduced
or used in any manner whatsoever without the express written permission
of the publisher except for the use of brief quotations in a book review. ISBN: 978-1540422651 Ordering Information: Special discounts are available on quantity purchases by corporations, associations, and others. For details, contact the publisher at . How to use this book This edition of SQL Practice Problems assumes that you have some basic background knowledge about relational databases and tables. However, Ive added some beginner level questions to gradually introduce the various parts of the SQL Select statement for those with less experience in SQL.

A note on the database usedthe database used for these problems, which you will set up in the in the Installation Instructions, is not the standard Northwind database. There have been multiple modifications made to it, including additional tables, and modified data, to support the problems in this book. Do not try to use the standard Northwind sample database that came with previous installations of SQL Server, many of the problems will not work. Do you need to finish all the problems? Absolutely not. The introductory problems are fairly simple, so you may want to skip directly to the Intermediate Problems section. If youre not a beginner, but not sure where you should start, just take a look at the problems and expected results in the Introductory Problems section and make sure you understand the concepts.

If you do, start working on the Intermediate Problems section. If youre uncertain about how to start on a problem, the hints are designed to gradually walk you through how to approach each problem. Try hard to solve the problems first without the hints! The information will stick better if you can do that. But if youre stuck, the hints will get you starting in thinking with a data mindset. If theres code you want to copy from this book and run on your serverbelieve it or not, I recommend that you actually type it out, instead of copying and pasting. Why go to the hassle of re-typing something? Science shows that the act of typing establishes it more firmly in your mind.

Sometimes when you just copy and paste, the code just goes directly from one window in your computer to another, without making much impression on your memory. But when you type it out, you have to focus much more, and that helps tremendously with retaining the information. Should you search online for answers, examples, etc.? Absolutely. I expect you do research online throughout the book, and in many places its necessary. I do not include all the syntax in this book. In my day-to-day work as a data engineer, I would be lost without being able to do online research.

Sometimes I search online just for a reminder of a certain syntax, sometimes for examples of a particular type of code, and sometimes for approaches to specific problems. Learning to find answers online effectively can cut your problem-solving time dramatically. Once you finish all the questions, youll have some very useful skills in data analysis and advanced Select statement usage. This isnt all there is to SQL, of course. Theres also the syntax that lets you actually modify data (update, insert, delete), DDL (data definition language, i.e. how to create and modify database objects), programming concept such as stored procedures, and of course many other topics.

In this book, Im only presenting problems involving retrieving data with Select statements, because thats an area where its hard for people to get solid practice with real life data problems, without actually working as a data engineer or programmer. Its also a critical first step for almost any of the other database topics. Any feedback would be greatly appreciated. For any questions or issues, please send email to and I will be happy to respond. Thank you for purchasing this book! Table of Contents Setup This section will help you install Microsoft SQL Server 2016, SQL Server Management Studio (SSMS) and also walk you through setting up the practice database. The setup of Microsoft SQL Server 2016 and SSMS will take about 45 minutes, with about 5 minutes of interaction here and there.

It may take one or two reboots of your system, depending on which version of certain support files you have (dot.net framework). SQL Server 2016 will run with more recent versions of Windows, including Windows 8 and Windows 10. Please review this requirements page ( https://msdn.microsoft.com/en-us/library/ms143506.aspx ) for full details. Install Steps Pre-setup - To download the backup file necessary in step 3, as well as a PDF version of this book and a SQL setup script to use if you already have SQL Server 2012 or SQL Server 2014, go to www.SQLPracticeProblems.com. Click on the Buy Now button. Dont worry, you dont actually have to buy anything.

Use the 100% off coupon code KCSPurch to bypass the credit card information, and get a download link sent directly to your email for free. You may want to consider viewing this book via the PDF instead of the paper or Kindle copy, since youll be able to click on the links, and copy and paste code more easily. Note: If you already have SQL Server 2012 or 2014 installed, and dont want to install SQL Server 2016, you dont need to. Theres a setup script called Northwind2012.sql (also works for SQL Server 2014) included in the zipped file that will allow you to use your existing version. Open that file, and follow the instructions. 1. 1.

Install MS SQL Server Express Edition 2016 - Download and install MS SQL Server Express Edition 2016 ( https://www.microsoft.com/en-cy/sql-server/sql-server-editions-express ). Its a free download, and the Express Edition, unlike the Developer Edition, doesnt require you to jump through any hoops with Live.Microsoft.com subscriptions.Feel free to do the Basic install unless you need it to go in a particular location on your hard drive. The install of Express Edition and SSMS (in the next step) will take about 45 minutes. Almost all of this is hands-off. A reboot may be required, depending on some of your system files. 2.

SQL Server Management Studio (SSMS) 2016 - Download and install SQL Server Management Studio (SSMS) 2016 ( https://msdn.microsoft.com/en-us/library/mt238290.aspx ).This is the tool that allows you to interact with SQL Server. You can either do this as a part of the MS SQL Server Express Edition 2016 install (theres a link at the bottom), or download it directly. 3. Move the practice database - The Northwind database backup file is included in the zip file that you downloaded. Unzip the file Northwind2016.bak and place it in the backup directory of your SQL Server Express Edition install. With the Basic install, the default location is here: C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Backup.

This practice database is based on the Microsoft sample Northwind database, but its been substantially modified, so in order to be able to solve the problems, youll need this version. 4. Setup the practice database - Follow the instructions on this video ( https://www.youtube.com/embed/mBLhXiXIHW0?rel=0 ) to restore the practice database that you just downloaded onto your freshly installed SQL Server. Questions or problems with the setup? Email me at Introductory Problems