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
Tonight we got started with SQL, learning about the following:
- SQL Management Studio
- What is SQL? Structured Query Language
- SQL Management Studio
- SQL Data Types
- select statements
- insert statements
- update statements
- delete statements
- filtering data
- what is null? means the field has no value, which is different than blank.
- charindex - where is a character in a string?
- ltrim+rtrim - removing spaces from left and right side of string, respectively
- right + left - getting specified number of charaters from right or left side of string
- substring - get a portion of a string by specifying start and length
- convert - change one data type to another
- isnull - specifying default if the field is null
- declaring variables - declare @variable_name data_type
- SCOPE_IDENTITY() - used to get ID from newly inserted record
- escaping single quotes
- grouping, summing
- joining tables
- inner join - only if all records have matching ID
- outer join (left or right) - the joined table's record matches are optional, null if nothing matches that ID
- Stored procs
- creating and altering
- passing in params
- multi-purpose functions like inserting or updating
- 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
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
- Entity Framework - this is the portion of C# that allows the language to speak to SQL more easily
- HTML - this is the formatting language websites are composed of.
- CSS - this styles (color, appearance) the HTML components you learned about in the HTML class.