Skip to content

Latest commit

 

History

History
34 lines (24 loc) · 1.36 KB

ddl_exercise.md

File metadata and controls

34 lines (24 loc) · 1.36 KB
Raw

SQL DDL exercise

People

  1. Script (init.sql)

Create a database called ddl_exercise. Create a table named people in this database, with columns id integer, name varchar(50) and surname varchar(50). Column id should be an auto incrementing primary key.

  1. Script (migration1.sql)
  • Now add columns age integer, sex char(1) and position varchar(50) to this table.
  • increase the size for surname to 100 characters.
  • get rid of position column (we changed our mind)
  1. Script (seeds.sql)

Fill the table with some rows (it's up to you to make them) so that we have 5 men and 5 women in it and:

  • 2 people of less than 10 years,
  • 2 people between 10-20,
  • 2 people between 20-30,
  • 2 people between 30-40,
  • 2 people older than 40 years.
  1. Script (migration2.sql)
  • Add column title varchar(10) and set it for all the people in following way
    • people older than 20 should have it set to Eng.
    • people older than 30 years should have it set to Dr.
    • people older than 40 years should have it set to Prof.
  • Delete all men younger than 10 years
  • Create table positions with columns id integer, person_id integer, position varchar(50) and set the person_id as a foreign key to table people and its column id
  • Create professors, which should be a copy (schema & data) of people table, but holding only records about professors.