SQL-part-2.md

Instructor Notes SQL sequel

Objectives

• Add a not null constraint to a column
• Insert rows into a table
• Select rows from a table
• Update rows from a table
• Delete rows from a table
• Drop tables
• Add primary and foreign key constraints to a table
• Add rows to a tables that have one-to-many and many-to-many relationships
• Select using join tables

How do you add a not null constraint to a column?

Create a table with the following columns

Instructors

• id - serial
• fname - varchar(255) not null
• lname - varchar(255) not null
• github - varchar(255) not null
• title - varchar(255) not null
• dndclass - varchar(255)

How do you insert rows into a table?

• insert into instructors (fname, lname, github, title, dndclass) values ('Roger', 'Schmidt', 'rogerwschmidt', 'Lead Instructor', 'barbarian');
• Insert 3 more instructors into the instructors table, make sure that at least one does not have a dndclass

How do you select rows from a table?

• select * from instructors;
• Select an instructor by their id
• Select an instructor by their title
• Select an instructor if they do not have a dnd class

How do you update rows from a table?

• update instructors set fname='Rogelio' where id=1 (note: id may be different)
• Update the dnd class for the instructor that is missing it

How do you delete rows from a table?

• delete from instructors where id=1
• Delete an instructor based on their id

How do you drop tables?

• drop table instructors

How do you add primary and foreign key constraints to a table?

create table cohorts (
  id serial primary key,
  name varchar(255)
);

create table students (
  id serial primary key,
  fname varchar(255),
  lname varchar(255),
  cohort_id integer not null references cohorts(id)
);

create table instructors (
  id serial primary key,
  fname varchar(255) not null,
  lname varchar(255) not null,
  github varchar(255) not null,
  title varchar(255) not null,
  dndclass varchar(255) 
);

create table instructors_cohorts (
  id serial primary key,
  instructors_id integer not null references instructors (id),
  cohorts_id integer not null references cohorts (id)
);

How do you add rows to a tables that have one-to-many and many-to-many relationships?

• insert into instructors (fname, lname, github, title, dndclass) values ('Roger', 'Schmidt', 'rogerwschmidt', 'Lead Instructor', 'barbarian');
• Insert 3 more instructors into the instructors table
• Insert 2 cohorts into the cohorts table
• Insert 10 students into the students table
• Create the association between the isntructors and cohorts table

How do you select join tables?

• Select all students with their cohorts