Intro to Databases

Class 1

About Me

Happy Galentine's Day!

Meet The Team!

  • Who are you?
  • What do you hope to get out of this class?
  • For fun: what's your favorite breakfast food?

What we will cover today

  • What is a database
  • What is SQL
  • Installs & setup
  • Create and load up a sample database
  • Go through basic commands

What is a Database?

  • Databases are logically modelled storage spaces
  • Hold all kinds of different information (data)

What is a Database?


What is a PostreSQL?


What is a SQL?

  • SQL - Structured Query Language
  • Special purpose language for managing data within a relational database
  • Main language used to manipulate data in database platforms
  • SQL standard but there are different versions of SQL. All variations support the major commands in a similar manner.
  • SQL is great to know, because it is everywhere

What does SQL do?

  • Execute queries against a database and retrieve information from a database
  • Create a new database and create tables in that database
  • Insert, update and delete records
  • Create stored procedures and views
  • Set permissions on database objects

Vendors that use SQL

  • Microsoft SQL Server (T-SQL)
  • Oracle (PL-SQL)
  • PostgreSQL
  • MySQL
  • DB2 (IBM Product)
  • Sybase
  • Microsoft Azure

Why PostgreSQL?

  • Great platform to use for learning SQL
  • Huge community support
  • Widely used in the industry and easy to use with various applications
  • It is open source

Installing PostgreSQL

Mac

http://bit.ly/pg-install-mac


Windows

http://bit.ly/pg-install-windows

Open PostgreSQL Terminal

Mac

Open PostgreSQL Terminal

Windows

Let's Develop It!

Spend a few minutes to get familiar with psql.


  • Open postgres: psql
  • List databases: \l or \list
  • Help: \h or \help
  • Command help: \h SELECT or \help SELECT

Quit/Exit PostgreSQL

To quit the database terminal use: \q or \quit

Installing pgAdmin

An app to interact with the PostgreSQL database, without the Terminal.

Mac

http://bit.ly/pg-admin-mac


Windows

http://bit.ly/pg-admin-windows

pgAdmin - Setup Local Server

pgAdmin - Setup Local Server

Relational Databases

  • Is a database whose organization is based on the relational model
  • This model organizes data into tables of rows and columns, with a unique key for each row
  • Tables store unique keys from other tables, which allows tables to be linked/related

Let's Develop It!

Create a sample database and fill it with data.


Download install files from: http://bit.ly/gdiaa-dvdrental

Open Terminal go to where you downloaded the dvdrental.tar file.

createdb dvdrental
pg_restore -d dvdrental dvdrental.tar

Questions?

Homework

Explore your sample dvdrental database.
Get familiar with pgAdmin. Play. Have fun!



Resources

Intro to Databases

Class 1