Lesson 7.1
Introduction to SQL
FinTech
© 2022 edX Boot Camps LLC. Confidential and Proprietary. All Rights Reserved.
Learning Outcomes
By the end of this unit, you will be able to:
2
01
Create a data model to represent the objects and relationships in�a dataset.
02
Create schemas, tables, and databases for relational data.
03
Retrieve data using advanced database queries.
Class Objectives
By the end of today’s class, you will:
Install PostgreSQL and pgAdmin on your computer.
Create databases and tables using pgAdmin.
Define SQL data types, primary keys, and unique values.
Load CSV files into a database.
Query data from a database.
Articulate the four basic functions of CRUD and apply them to a database.
Combine data from multiple tables using JOINs.
3
Why SQL?
Data provided by 2018 Stack Overflow Survey
4
|
Structured Query Language (SQL) is one of the main query languages used to access data within relational databases. |
SQL is designed to efficiently handle large amounts of data, resulting in high value to organizations. |
Experienced SQL programmers are in high demand. |
JavaScript
HTML
CSS
SQL
Java
Bash/Shell
Python
C#
PHP
C++
C
TypeScript
Ruby
Swift
69.8%
68.5%
65.1%
57.0%
45.3%
39.8%
38.8%
34.4%
30.7%
25.4%
23.0%
17.4%
10.1%
8.1%
Programming, Scripting, and Markup Languages (all respondents)
Postgres and pgAdmin
5
Postgres
PostgreSQL (usually referred to as "Postgres") is an object-relational database system that uses the SQL language.
6
pgAdmin
pgAdmin is the management tool used for working with Postgres. It simplifies creation, maintenance, and use of database objects.
7
8
<Time to Code>
9
Take a Break!
CRUD
10
CRUD Operations
Create Read Update Delete is a set of operations used with persistent storage.
These tools are fundamental to all programming languages, not just SQL.
11
Create | INSERT INTO table (column1, column2, column3) |
Read | SELECT * FROM table |
Update | UPDATE table SET column1 = VALUE WHERE id = 1 |
Delete | DELETE FROM table WHERE id = 5 |
Wildcards
12
Wildcards: % and _
Wildcards are used to substitute zero, one, or multiple characters in a string. The keyword LIKE indicates the use of a wildcard.
The % will substitute zero, one, or multiple characters in a query.
For example, all of the following will match: Will, Willa, and Willows.
The _ will substitute one, and only one, character in a query.
_AN returns all actors whose first name contains three letters, the second and third of which are AN.
13
SELECT *
FROM actor
WHERE last_name LIKE ‘Will%’;
SELECT *
FROM actor
WHERE first_name LIKE ‘_AN’;
14
<Time to Code>