Learn SQL in One Day and Learn It Well (Jamie Chan, LCF Publishing) (Z-Library)

📄 File Format: PDF

💾 File Size: 2.2 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

Learn SQL (using MySQL) in One Day and Learn It Well SQL for Beginners with Hands-on Project The only book you need to start coding in SQL immediately By Jamie Chan http://www.learncodingfast.com/sql Copyright © 2018 All rights reserved. No part of this publication may be reproduced, distributed, or transmitted in any form or by any means, including photocopying, recording, or other electronic or mechanical methods, without the prior written permission of the publisher, except in the case of brief quotations embodied in critical reviews and certain other noncommercial uses permitted by copyright law. Preface This book is written to help you learn SQL programming FAST and learn it WELL. We'll be using MySQL in the book, which is a free database management system that is widely used. If you are an absolute beginner to SQL, you'll find that this book explains complex concepts in an easy to understand and concise manner. If you are an experienced coder, you'll appreciate that this book covers a wide range of topics. Topics covered include basic concepts like table creation and data manipulation, to more advanced concepts like triggers, cursors, stored routines and more. These topics are carefully selected to give you a broad exposure to SQL, while not overwhelming you with information overload.

📄 Page 3

In addition, as Richard Branson puts it: "The best way of learning about anything is by doing". Throughout the book, we'll be building a database together. This hands-on approach to learning will help you gain a deeper understanding of the language. At the end of the course, you'll also be guided through a new project that gives you a chance to put what you've learned to use. You can download the source code for the examples and project at http://www.learncodingfast.com/sql Any errata can be found at http://www.learncodingfast.com/errata Contact Information I would love to hear from you. For feedback or queries, you can contact me at jamie@learncodingfast.com. More Books by Jamie Python: Learn Python in One Day and Learn It Well (1st Edition)

📄 Page 4

Python: Learn Python in One Day and Learn It Well (2nd Edition) C#: Learn C# in One Day and Learn It Well

📄 Page 5

Java: Learn Java in One Day and Learn It Well CSS: Learn CSS in One Day and Learn It Well

📄 Page 6

Contents Chapter 1: Introduction What is SQL? Getting Ready to Code Installing MySQL applications Launching MySQL Workbench Writing our first MySQL code Comments Chapter 2: Defining the Database How to Use this Book Creating the Database Using the Database Deleting the Database Chapter 3: Defining Tables Creating Tables Specifying Columns Specifying Table Constraints Altering Tables Table Names Columns and Table Constraints Deleting Tables

📄 Page 7

Chapter 4: Inserting, Updating and Deleting Data Inserting Data Updating Data Deleting Data Constraints Chapter 5: Selecting Data Part 1 Basic Select Syntax Selecting Everything Filtering Columns Using Aliases Filtering Rows LIMIT DISTINCT WHERE clause Subqueries Sorting Rows Chapter 6: Selecting Data Part 2 What is a Function? MySQL Functions Aggregate Functions GROUP BY

📄 Page 8

HAVING Chapter 7: Selecting Data Part 3 Joins Unions Chapter 8: Views What is a view? Creating a View Altering a View Deleting a View Chapter 9: Triggers What is a Trigger? Creating a Trigger Deleting a Trigger Chapter 10: Variables and Stored Routines Variables Stored Routines Stored Procedures Stored Functions Deleting Stored Routines Chapter 11: Control Flow Tools

📄 Page 9

IF statement CASE statement WHILE statement REPEAT statement LOOP statement Chapter 12: Cursors What is a Cursor? Example Chapter 13: Project About the Project Creating the Database Using the Database Adding Tables Inserting Data View Stored Procedures Trigger Stored Function Testing the Database Appendix A: Tables for companyHR Appendix B: Tables for sportsDB

📄 Page 10

Appendix C: Suggested Solution for Project

📄 Page 11

Chapter 1: Introduction Welcome to SQL and thank you so much for picking up my book. I sincerely hope that this book can help you master SQL fast and introduce you to the exciting world of databases. This book adopts a hands-on approach to learning. As we progress from one chapter to another, we’ll be doing various exercises. You are strongly encouraged to follow along these exercises. At the end of the book, we’ll also be working on a new project together. This project involves building a SQL database for a sports complex. We’ll learn to build the database, insert data, perform queries, write routines, views, cursors, and more. Excited and ready to start embarking on our SQL learning journey? Let’s do it! What is SQL? Simply stated, SQL stands for Structured Query Language and is a language used to manage data stored in a relational database. This brings us to the next question - What is a database? A database is a collection of data organized in some format so that the data can be easily accessed, managed and updated. The predominant type of database is a relational database. Relational databases organize data in the form of tables. In addition, they contain queries, views and other elements to help us interact with the data. In order to manage our database, we need to use a software application known as a database management system (DBMS). Clear?

📄 Page 12

So far, we have the following terminologies: 1) SQL is a language 2) A database is a structured collection of data 3) A DBMS is a software that we use to manage our databases With regards to DBMS, there are a large number of them available. Some are free to download and use while others are not. The most commonly used DBMS include MySQL, Oracle, Microsoft SQL Server and IBM DB2. Each of these DBMS have their own versions of SQL. While this may sound intimidating, rest assured that all DBMS support the major SQL commands (such as SELECT, UPDATE, DELETE, INSERT) in a similar manner. Hence, if you know one version of SQL, it is very easy to pick up other versions. In this book, we'll be using MySQL. This is one of the most popular DBMS available. Best of all, it’s free! From this point forward, whenever I mention SQL, I’m referring to the MySQL version. Getting Ready to Code In order to start using MySQL, we need to first download and install two applications: MySQL Server and My SQL Workbench. Installing MySQL applications Windows For Windows users, go to https://dev.mysql.com/downloads/windows/installer/. Scroll down and click on the first “Download” button to download the application. You’ll be directed to another page. Scroll to the bottom of the page and click on “No thanks, just start my download.”

📄 Page 13

Once you have downloaded the program, double-click on the file and follow the instructions to install the software. When prompted to choose a setup type, select “Custom” and click “Next”.

📄 Page 14

(This page has no text content)

📄 Page 15

You’ll be asked to select the products and features to install.

📄 Page 16

(This page has no text content)

📄 Page 17

Under “Available Products”, fully expand “MySQL Servers” by clicking on the + signs. Once fully expanded, click on the last item and click on the green arrow to move this item to the “Products/Features To Be Installed” section. Next, expand “Applications” and fully expand “MySQL Workbench”. Click on the last item and click on the green arrow to move this item to the “Products/Features To Be Installed” section. Once you are done, click on “Next” and follow the instructions to continue with the installation. Stick to the default options selected at each stage. When prompted to enter a password, enter your desired password and jot down the password. You’ll need this password later. Click on “Next” to continue and complete the installation. Mac OS For Mac users, to install MySQL Server, go to https://dev.mysql.com/downloads/mysql/. Scroll down and click on the first “Download” button to download the application. Once you do that, you’ll be directed to another page. Scroll to the bottom of the page and click on “No thanks, just start my download.” Once you have downloaded the program, double-click on the file to unzip it. Next, double-click on the unzipped file and follow the instructions to install the software. At the configuration stage, choose “Use Strong Password Encryption” and click “Next”. Enter your desired password and jot down the password. You’ll need this password later. Ensure that the "Start MySQL Server once the installation is complete" option is selected and click on "Finish" to complete the installation. Once you are done installing MySQL Server, you need to install another

📄 Page 18

software known as MySQL Workbench. This software provides us with a graphical user interface to make it easier for us to interact with MySQL. To download MySQL Workbench, go to https://dev.mysql.com/downloads/workbench/. Click on the first “Download” button to download the application. Once again, you’ll be directed to another page. Scroll to the bottom of the page and click on “No thanks, just start my download.” to download the program. Double-click on the downloaded file and follow the instructions to install it after downloading. Launching MySQL Workbench Once you have installed the necessary applications, we are ready to do some coding. First, launch MySQL Workbench. You’ll get the screen below:

📄 Page 19

(This page has no text content)

📄 Page 20

Click on the grey rectangle under "MySQL Connections" (named "Local instance 3306" in the image above). You’ll be prompted for a password. Enter the password that you keyed in previously when you installed MySQL Server and select “Save password in vault” (or “Save password in keychain” for Mac users). Next, press OK to proceed. If all goes well, you should be directed to the screen below: