1. You First Database1.1 Introduction to Relational Databases (video)1.2 Attributes of Relational Databases1.3 Query Information_Schema with SELECT1. 4 Tables: At the Core of Every Database (video)1.5 CREATE Your First Few TABLEs1.6 ADD a COLUMN with ALTER TABLEs1.7 Update Your Database as the Structure Changes (video)1.8 RENAME and DROP COLUMNs in Affiliations1.9 Migrate Data with INSERT INTO SELECT DISTINCT1.10 Delete tables with DROP TABLE
2. Enforce Data Consistency with Attribute Constrains2.1 Better Data Quality with Constrains (video)2.2 Types of Database Constrains2.3 Conforming with Data Types2.4 Types CASTs2.5 Working with Data Types (video)2.6 Change Types with ALTER COLUMN2.7 Convert Types USING a Function2.8 The Not-Null and Unique Constrains (video)2.9 Disallow NULL values with SET NOT NULL2.10 What Happens If You Try to Enter NULLs?2.11 Make Your Columns UNIQUE with ADD CONSTRAINT
3. Unique Identify Records with Key Constraints3.1 Keys and Superkeys (video)3.2 Get to Know SELECT COUNT DISTINCT3.3 Identify Keys with SELECT COUNT DISTINCT3.4 Primary Keys (video)3.5 Identify the Primary Key3.6 ADD Key CONSTRAINTs to the Tables3.7 Surrogate Keys (video)3.8 ADD A SERIAL Surrogate Key3.9 CONCATenate Columns to A Surrogate Key3.10 Test Your Knowledge before Advancing
4. Glue Together Tables with Foreign Keys4.1 Model 1:N Relationships with Foreign Keys (video)4.2 REFERENCE A Table with A FOREIGN KEY4.3 Explore Foreign Key Constrains4.4 JOIN Tables Linked by A Foreign Key4.5 Model More Complex Relationships (video)4.6 Add Foreign Keys to the “Affiliations” Table4.7 Populate the “professor_id” Column4.8 Drop “firstname” and “lastname”4.9 Referential Integrity (video)4.10 Referential Integrity Violations4.11 Change the Referential Integrity Behavior of A Key4.12 Roundup (video)4.13Count Affiliations Per University4.14 Join All the Table Together

1. You First Database
1.1 Introduction to Relational Databases (video)
1.2 Attributes of Relational Databases
1.3 Query Information_Schema with SELECT
information_schema is a meta-database that holds information about your current database. information_schema has multiple tables you can query with the known SELECT * FROM syntax:
tables: information about all tables in your current databasecolumns: information about all columns in all of the tables in your current database…
In this exercise, you’ll only need information from the 'public' schema, which is specified as the column table_schema of the tables and columns tables. The 'public' schema holds information about user-defined tables and databases. The other types of table_schema hold system information – for this course, you’re only interested in user-defined stuff.
Instruction 1
Get information on all table names in the current database, while limiting your query to the 'public' table_schema.

-- Query the right table in information_schema
SELECT table_name 
FROM information_schema.tables
-- Specify the correct table_schema value
WHERE table_schema = 'public';

Instruction 2
Now have a look at the columns in university_professors by selecting all entries in information_schema.columns that correspond to that table.

-- 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';

Instruction 3
Finally, print the first five rows of the university_professors table.

-- Query the first five rows of our table
SELECT * 
FROM university_professors 
LIMIT 5;

1. 4 Tables: At the Core of Every Database (video)
1.5 CREATE Your First Few TABLEs
You’ll now start implementing a better database model. For this, you’ll create tables for the professors and universities entity types. The other tables will be created for you.
The syntax for creating simple tables is as follows:

CREATE TABLE table_name (
 column_a data_type,
 column_b data_type,
 column_c data_type
);

Attention: Table and columns names, as well as data types, don’t need to be surrounded by quotation marks.
Instruction 1:
Create a table professors with two text columns: firstname and lastname.

-- Create a table for the professors entity type
CREATE TABLE professors (
 firstname text,
 lastname text
);

-- Print the contents of this table
SELECT * 
FROM professors;

Instruction 2:
Create a table universities with three text columns: university_shortname, university, and university_city.

-- Create a table for the universities entity type
CREATE TABLE universities (
 university_shortname text,
 university text,
 university_city text
);

-- Print the contents of this table
SELECT * 
FROM universities;

1.6 ADD a COLUMN with ALTER TABLEs
Oops! We forgot to add the university_shortname column to the professors table. You’ve probably already noticed: