Skip to content

Introduction to SQL

Here you can access the books table used in the course.

Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

-A database stores data. -data is organized in rows and columns

Relational Databases -define relationships between tables of data inside the database

Database advantages -more storage than spreadsheet applicatoins -storage is more secure due to encyption -many users can write queries to gather insights of the data at the same time -when a database is queried the data stored inside the database is not changed. Rather it's just accessed

SQL -structured query language -most widely used programming language for databases

-example sql query: SELECT * FROM patrons LIMIT 30

TABLES

-Databases are ogranized in tables. -tables are organized in rows(records) and columns(fields) -a tables fields are limited to those set when database was created -number of rows are ulimited

Table Naming -should be lower case and not include spaces. -use underscores for spacing instead

Records -a row that holds data on an individual oberservation

Fields

  • a field is a column that holds one piece of information about all records
  • field names should be lower case with no spaces
  • name should be singular
  • name should be different from other field names
  • name needs to be different from table name

Identifiers -unique identifiers are used to identify records in a table -they are unique and often numbers

Data -when a table is created the data type of the field needs to be defined -different types of data are stored differently -some operations only apply to certain types -a string is a sequence of characters -VARCHAR can store small and large strings in databases -integers store whole numbers(INT) -Floats are decimal numbers(NUMERIC)

Database schemas are blueprints of databases. These show the design which includes the tables in the database and the relationship between the tables. these also show the data types for the fields

Storage -database storage is physically stored on the hard disk on the server -Servers are computers that perform services via request over a network

Introducing Queries -SQL is used to answer questions in relational database questions -SQL is best for large datasets

Keywords -keywords are reserved for operations -common keywords: SELECT, FROM -Our first query:

SELECT name FROM patrons;

-Query results often called result set

Selecting multiple fields:

SELECT card_num, name FROM patrons;

Selecting all fieds: SELECT * FROM patrons;

Spinner
DataFrameas
books
variable
-- Add your own queries here
SELECT * 
FROM books

Writing Queries

Aliasing -use aliasing to rename columns

  • SELECT name AS first_name, year_hired FROM employees;

Selecting distinct records SELECT DISTINCT year_hired FROM employees;

Views -a view is a virtual table that is the result of a saved SQL SELECT statement -when accessed, views automatically update in response to updates in the underlying data

CREATE VIEW employee_hire_years AS SELECT id, name, yearh_hired FROM employees;

Using views SELECT id, name FROM employee_hire_years;

SQL Flavors

-Both free and paid -all used with relational databases -vast majority of keywords are the same -all must follow universal standards

Two popular SQL flavors:

  1. PostgreSQL -free and open-source relational database system -created at the university of California, Berkeley -"PostgreSQL" refers to both PostgreSQL database system and its associated SQL flavor

  2. SQL Server -has free and paid versions -created by Microsoft -T-SQL is Microsoft's SQL flavor, used with SQL Server databases

Compairing PostgreSQL and SQL Server -Like dialects of the same language PostgreSQL(limit the number of results) SELECT id, name FROM employees LIMIT 2;

SQL Server(limit the number of results) SELECT TOP(2) id, name FROM employees;

Explore Datasets

Use the books table to explore the data and practice your skills!

  • Select only the title column.
  • Alias the title column as book_title.
  • Select the distinct author names from the author column.
  • Select all records from the table and limit your results to 10.