Introduction to SQL with
PostgreSQL
Master the fundamentals of database queries using PostgreSQL from the
command line
What is SQL?
Structured Query Language Database Communication Real-World Power
Write commands to retrieve, update, Behind banking systems, social media
The standard language for accessing and manage data efficiently platforms, and school databases
and manipulating databases
Think: Where have you interacted with databases without knowing it?
Understanding Databases
What is a Database?
A systematically organized collection of data stored electronically
for easy access, management, and updating.
Structure Overview
• Tables store related data
• Rows represent individual records
• Columns define data fields
• Each cell holds specific values
Students Table Structure
id Unique identifier
name Student's full name
course Program enrolled in
year_level Current academic year
This simple structure demonstrates how data is organized into logical fields that can be queried and manipulated.
What other useful columns could we add to this table?
CRUD Operations
The four fundamental database operations you'll master in this course
Create Read
INSERT new records into tables SELECT and view existing data
Update Delete
UPDATE modify existing records DELETE remove unwanted data
Which operation do you think is the most dangerous? Why?
PostgreSQL Command Line
Workflow
01
Access PostgreSQL
Connect using the command line interface
02
Launch psql
Use the psql tool to execute SQL commands
03
Write Commands
Enter SQL statements directly without a GUI
04
Verify Results
Use pgAdmin as a visual verification tool
Connecting to PostgreSQL
Step 1: Launch Connection Step 2: Create Database
psql -U postgres CREATE DATABASE student_db;
Connect to PostgreSQL using the default postgres Initialize a new database named student_db
user account
Step 3: Connect to Database Success!
You now have a working database ready for table creation and data operations.
\c student_db;
Switch to your newly created database
Creating Tables
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
course VARCHAR(50),
year_level INT
);
SERIAL PRIMARY KEY VARCHAR(100)
Auto-incrementing unique identifier Variable character string up to 100 characters
NOT NULL INT
Field must contain a value Integer data type for numerical values
Inserting & Querying Data
INSERT Command SELECT Commands
INSERT INTO students SELECT * FROM students;
(name, course, year_level)
VALUES Retrieve all columns and rows
('Juan Dela Cruz',
'BSIT', 2); SELECT name, course
FROM students;
Add a new student record with specified field values
Retrieve specific columns only
UPDATE & DELETE: The Danger Zone
UPDATE with WHERE
UPDATE students
SET course='BSCS'
WHERE name='Juan';
Safely updates only matching records
DELETE with WHERE
DELETE FROM students
WHERE name='Juan';
Removes only specified records
⚠ WARNING: Never run UPDATE or DELETE without a WHERE clause! Without WHERE, these
commands affect ALL rows, potentially destroying your entire dataset.