• Object-relational database system
• supports both SQL (relational) and JSON (non-relational) querying
• Uses and extends SQL
• Getting started

Architectural Fundamentals

• uses a client/server model
• A server process (postgres), which manages the database files, accepts connections to the database from client applications, and performs database actions on behalf of the clients.
• The user's client (frontend) application that wants to perform database operations.

Creating database

createdb <database_name>

Remove database

dropdb <database_name>

Accessing interactive terminal

psql <database_name>

Accessing a database

• Running the PostgreSQL interactive terminal program (psql)
• Can type SQL queries into a work space maintained by psql

psql <database_name>

SQL Language

• Managing data stored in relations (table)
  • Each table is a named collection of rows.
  • Each row of a given table has the same set of named columns (of a specific data type)
  • Columns have a fixed order in each row (however, not rows within the table)
• Tables are grouped into databases, and a collection of databases managed by a single PostgreSQL server instance constitutes a database cluster.

Creating new table

• ****Specifying table name, columns names, and types

CREATE TABLE tablename(

column1 dataType1,
column2 dataType2, -- this is a comment
column3 dataType3

);

Data Types

• int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, and intervals

Populating Table with Rows

INSERT INTO tablename VALUES (val1, val2, val3)

Querying a Table

SELECT * FROM tablename;
-- * means all columns

SELECT column1, column2 FROM tablename;

• Can also write expressions (will be stored with the label

SELECT (column1 + column2) AS name FROM tablename;

• Boolean Expressions

SELECT * FROM name
	WHERE column1 <condition>;

• Ordering

SELECT * FROM name
	ORDER BY column2;

• Remove duplicate column

SELECT DISTINCT column1
	FROM name;

Best Practices

• Google

Interview

• IBM