Skip to content
New Workbook
Sign up
Course Notes: Introduction to Relational Databases in SQL

Chapter 1: Your First Database

Introduction to Relational Databases

A relational database:

  • real-life entities become tables
  • reduced redundancy
  • data integrity by relationships

In the SQL cell below:

  • Set the source to 'Course Databases' to query databases used in SQL courses.
  • Set the source to 'DataFrames and CSVs' to query this course's CSV files (if any) with SQL
  • Write SQL! Note that you cannot run queries that modify the database.
Spinner
DataFrameavailable as
df
variable
SELECT table_schema, table_name
FROM information_schema.tables;
Spinner
DataFrameavailable as
df1
variable
-- Query the right table in information_schema
SELECT table_name 
FROM information_schema.tables
-- Specify the correct table_schema value
WHERE table_schema = 'public';
Spinner
DataFrameavailable as
df2
variable
-- Query the right table in information_schema to get columns
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'university_professors' AND table_schema = 'public';

Tables: At the core of every database

Entity types

Entity types are categories of objects or concepts that are represented in a database. They can include things like people, places, events, or products, and are used to organize and classify data in a meaningful way. In a database, each entity type is typically represented by a table, with each row in the table representing a specific instance or occurrence of that entity type. By defining and organizing entity types in this way, databases can efficiently store and retrieve large amounts of data, and provide a structured framework for analyzing and understanding complex information.

Examples of entity types include:

  • Customers
  • Products
  • Orders
  • Employees
  • Invoices
  • Suppliers
  • Shipment
  • Patients
  • Doctors
  • Hospitals

Create new tables with CREATE TABLE

Spinner
DataFrameavailable as
df3
variable
CREATE TABLE weather (
	clouds text,
	temperature numeric,
	weather_station char(5));
Spinner
DataFrameavailable as
df4
variable
ALTER TABLE table_name
ADD COLUMN column_name data_type;

Update your database as the structure changes

Spinner
DataFrameavailable as
df5
variable
INSERT INTO organizations
SELECT DISTINCT organization,
	organization_sector
FROM university_professors;
Spinner
DataFrameavailable as
df6
variable
INSERT INTO table_name (column_a, column_b)
VALUES ("value_a", "value_b");
Spinner
DataFrameavailable as
df7
variable
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

DROP

‌
‌
‌