First Class - SQL

Files from tonight:

For creating database (run first if you didn't create DB): http://www.phoenixvillepal.org/sql/create_database_12012020.sql

The SQL we created in class (run step by step if you didn't already do this): http://www.phoenixvillepal.org/sql/1212020.sql

Questions? cbrickhouse@gmail.com

Summary:

Tonight we got started with SQL, learning about the following:

1. SQL Management Studio
2. What is SQL? Structured Query Language
3. SQL Management Studio
4. SQL Data Types
5. select statements
6. insert statements
7. update statements
8. delete statements
9. filtering data
10. what is null? means the field has no value, which is different than blank.
11. functions
12. charindex - where is a character in a string?
13. ltrim+rtrim - removing spaces from left and right side of string, respectively
14. right + left - getting specified number of charaters from right or left side of string
15. substring - get a portion of a string by specifying start and length
16. convert - change one data type to another
17. isnull - specifying default if the field is null
18. declaring variables - declare @variable_name data_type
19. SCOPE_IDENTITY() - used to get ID from newly inserted record
20. escaping single quotes
21. grouping, summing
22. joining tables
23. inner join - only if all records have matching ID
24. outer join (left or right) - the joined table's record matches are optional, null if nothing matches that ID
25. Stored procs
26. creating and altering
27. passing in params
28. multi-purpose functions like inserting or updating
29. returning IDs for newly inserted records

Next time we will finish SQL and move onto C#. Some C# reading: https://www.codecademy.com/learn/learn-c-sharp

Prerequisite Information

Here's the needed software list again if anyone isn't set up

For Mac you might need to run Windows virtually or follow this guide to install SQL Server: https://database.guide/how-to-install-sql-server-on-a-mac/

Needed software (all free to download and cross-platform)

• Visual Studio 2019 Community Edition - https://visualstudio.microsoft.com/downloads/
• SQL Server Developer Edition - https://www.microsoft.com/en-us/sql-server/sql-server-downloads - be sure to install all developer tools. after install, make sure you have SQL Server Tools --> SQL Management Studio installed
• Git - https://git-scm.com/downloads - this is for versioning
• GitHub account -  https://github.com/ - sign up for this - we will be storing our code here. this and Git combined allow you to have version control so you can view old versions, compare, etc.
• Zoom - download/make an account https://zoom.us/

If you have any questions or concerns about installing any of these let me know.

This is the Material we are going to cover, and some references to get you started:

• Microsoft SQL - This is a database platform (similar to MS Access but more complex and server-based) and language to query said data. https://www.sqlservertutorial.net/getting-started/
• C# - This is a Microsoft language, free and open-source - reference guide
• https://docs.microsoft.com/en-us/dotnet/csharp/
• Entity Framework - this is the portion of C# that allows the language to speak to SQL more easily
• https://docs.microsoft.com/en-us/ef/core/get-started/overview/first-app?tabs=netcore-cli
• HTML - this is the formatting language websites are composed of. 
• https://developer.mozilla.org/en-US/docs/Learn/HTML/Introduction_to_HTML/Getting_started
• CSS - this styles (color, appearance) the HTML components you learned about in the HTML class.
• https://developer.mozilla.org/en-US/docs/Learn/CSS/First_steps/Getting_started
• JavaScript - this ties HTML and CSS together and allows you to start building interactive websites. 
• https://developer.mozilla.org/en-US/docs/Learn/Getting_started_with_the_web/JavaScript_basics