2-Introduction-to-Relational-Databases-in-SQL

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 database
• columns: 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: