Introduction to SQL

Learning outcomes:

Describe the use of SQL in a database
List some advantages of using query languages like SQL
Describe the parts of a basic SQL query such as : Select Title from books where PublishingDate = 2001;

You can import a number of different types of files including text, but CSV files are commonly used
Imports can also happen from other programs including spreadsheet programs or other database programs
If you're using SQL you can use a create table or insert command to get your data into your database
There are also some Graphical options depending on the database type you're using
You can also write a script to enter in your data

Databases can be access by either Graphical User Interface (GUI) or Command line
The system you're using will affect how the data is stored and where it's being used

You can have many different front end options, each may have different steps like switching between MS Word and Gooogle Docs
Dashboards are also commonly used for people that need to see but not change the data
SQL Dashboards
Databases will save the information on your computer but that will depend on what database is installed, which can be different then the front end used
Databases can also be on servers and in the cloud, the front end would connect to those for viewing
Generally only a few people may change the data, but many more will be able to see the data

You can build your own, but a lot of people use already made options
Your front end can be as simple or complex as you like, some are free, some are not.
Some database front end options can be very complex and include data analytics and visualizations, examples include Tableau, MS Power BI, Oracle Analytics Cloud and AIMMS

A data dictionary is an explanation of the data saves in our database
Should be centralized so everyone using the database is seeing the same definitions
Clarity can be an issue
Names listed aren't always obvious, and descriptions can be lacking
Active dictionaries are created within the database and auto updated, passive are separate and must be updated manually

SQL or Structured Query Language is how we can interact with our database
SQL can be used to view, organize and manage the data
SQL commands or queries are done on the command prompt and look similar to command line commands
SQL is relatively human readable once you're used to the syntax
Scripts can be written to automate things and make queries more efficient to use
Select Star SQL: Interactive textbook to learn SQL

SQL is interactive, so you can see your results pretty fast and make sure you're looking at the right info
It's close enough to programming a lot of developers are very comfortable learning it quickly
Because it's popular a lot of people learn and use it, therefore making it more popular
SQL is relatively dependable and has been in use so long a lot of people have done a lot of testing on it
Once it's setup correctly a company is unlikely to move away into a new technology
Because SQL is relatively human readable a lot of people use it in companies including marketing, QA and sales,some larger companies will even offer free courses for people to learn it

SQL was standardized in the 80s, but there are different dialects depending on what database you're using
SQL server is proprietary and owned by MS, Dialects including PostgreSQL and SQLite
For example PostgreSQL is open source and for Object oriented databases (we've been talking about relational), and can also suppose JSON data types

For right now we're just using basic SQL standard
Each table in our database will have fields, each record will have a row and column. We need to know what we want to look at to form our query properly
Each SQL query is going to start and include commands such as SELECT, UPDATE, INSERT and DELETE
You want to figure out what you want first, and then try and translate it into a query. For example if you want to look at all customers in your database you might say "SELECT * FROM Customers;"

Suggested Activities and Discussion Topics:

Beginner Activity: Go to The W3 Schools and try their basic SQL syntax, make sure to go through at least a couple pages so you can see several examples
Intermediate Activity: Go to either the Island Adventure or the Crime Solving Adventure sites and try some of the puzzles
Follow the instructions on this PDF (accessible HTML version)
Activity: Listen to This Podcast That was created using AI from these materials. Transcript for the Podcast What are your thoughts? Did the AI do a good job representing the materials? Did you find any mistakes?
Go through This AI generated study guide, what do you think? Did it capture the week materials well? How did you do on the self quiz? Do you know all the vocab used?

Would you like to see some more classes? Click here