Intro to SQL Flashcards
Database
An organized collection of stored and persisted data. A database (or db for short) is an organized collection of stored and persisted data. Databases are responsible for storing data, organized in ways that are useful to us programmers.
Where are databases? Databases are stored on any compatible machine, whether it’s a dedicated database server, or even on our own laptops.
SQL
A query language specifically designed to talk to relational databases. Used in a sentence: “I added 50 new records to the database using SQL statements,” “I forgot the SQL syntax to create a database and create a table in that database”
Postgres
A specific relational database. Synonyms: PostgreSQL, psql, pg. Used in a sentence: “I heard that postgres is a database that’s a little different from mySQL, but not very different and I could learn both,” “When talking to my database, I got an error saying that the psql connection was bad, so I restarted my Postgres services”
Database entity
A single concept that needs to be stored in a table and database, such as a kind of person, product, thing, or object. “When I started my project, I considered all of the database entities I needed to store, such as users, posts, and comments.”
Primary key
An attribute dedicated to being a unique identifier for each row in a table. In a sentence: “Even though two rows had the same name, they had different primary keys,” “I was able to retrieve the correct row each time because I queried using primary keys.”
How does SQL Communicates With Relational Databases?
SQL is a query language. It’s a declarative programming language specifically designed to talk to relational databases.
We will learn SQL syntax and write SQL statements. Relational databases will interpret the SQL statements and give us back the records we need.
We read that SQL is a declarative programming language. Essentially, this means we use the language to express what we would like to have done, but not how to do it. We can ask a database for all kinds of data with SQL, but we will never have to tell it how to iterate over a table, or how to find a certain row. On the other hand, Python is considered an imperative language. We have to tell it how to do everything! If we want to find an item in a list, we have to write the exact steps of how to do that!
Relational Databases
Relational databases are a kind of database.
Relational databases organize data in the form of many tables. Relational databases contain many tables of data, where the columns are attributes and the rows are records.
The family tree of relational database systems is very large indeed. Generally speaking, these database systems all operate similarly and carry out the same responsibilities. They usually differ in how they are designed, ways that they are used, and various lower-level implementation details.
This is a short list of currently supported relational database systems:
MySQL
PostgreSQL
SQLite
Microsoft SQL Server
Microsoft Azure SQL Database
Apache Hive
MariaDB
Oracle
Using Postgres
PostgreSQL, also known as Postgres (and often seen as psql), is a free and open-source relational database system. PostgreSQL is:
- *Free and open-source
- *Built-in compatibility with Heroku, a popular platform used often for free Flask app deployment (Flask is a Python package used to build web applications)
- *It is easy to install on macOS
Parts of a Database
Databases structure and store data that is related to one or more projects.
One database can store data for many different database entities. A database entity is any single concept that needs to be stored, usually a kind of person, product, thing, or object (a noun!). For example, imagine a micro-loan system. One single database for this webapp could have the entities “Borrower,” “Lender,” “Loan,” “Loan Request,” and “Repayment.”
There are several components of a database. The following is an overview of a few key concepts:
* *Schema
* *Tables
- Records and Attributes
- Primary Keys (and IDs)
Schema
A database schema is a set of programming statements that describe the organization of one database.
The schema usually defines:
The name of each table
The attributes (columns) of each table
The data types of each column
Any rules and constraints for each column or table
Data Types and Constraints in SQL
SQL databases enforce data type and other constraints on columns of tables.
Some data types available in Postgres are..
boolean, integer, varchar (Text. The user may define a maximum lengther), text (Text, The user does not define a maximum length), numeric (a number that allows floats with precision), timestamp (Date and time, including time zone), and json (JSON)
Constraints
Constraints are rules on a column or table that can reject invalid data from ever entering the database. Some example constraints that can be defined on a column or table are:
* *A value cannot be a null value (similar to None in Python). This implies that a value is required. * *Integers must be greater than 0. * *A value must be unique compared to the other values in the same column and table.
Tables
Databases can have one or more tables. Each table usually represents one entity.
The columns of the table are the attributes of the entity, and each row is a record of one entity.
text vs. varchar
The text data type is used for strings. What is varchar? The varchar data type is great for text that isn’t expected to be long. It also helps with db performance.
