An adventure in Project: M36 Relational Algebra Engine

Tutd Schema: employee :: {id Integer, name Text, salary Integer, departmentId Integer}; employee := relation{tuple{id 1, name "Joe", salary 70000, departmentId 1},                     tuple{id 2, name "Jim", salary 90000, departmentId 1},                     tuple{id 3, name "Henry", salary 80000, departmentId 2},                     tuple{id 4, name "Sam", salary 60000, departmentId 2},                     tuple{id 5, name "Max", salary 90000, departmentId 1}}; key employee_pkey {id} employee; department :: {id Integer, name Text}; department := relation{tuple{id 1, name "IT"}, tuple{id 2, name "Sales"}} key department_pkey {id} department; Thinking Process: TutorialD (master/main): grouped := employee group ({all but departmentId} as byDepartment)  TutorialD (master/main): :showexpr grouped ┌───────────────────────────────────────────────────────────────┬─────────────────────┐                                 

Tutd Schema: person :: {id Integer, email Text}; person := relation{tuple{id 1, email "join@example.com"},                   tuple{id 2, email "bob@example.com"},                   tuple{id 3, email "join@example.com"}}; Thinking Process: TutorialD (master/main): person := ((person group ({all but email} as ids):{minId := min(@ids)}){all but ids}) rename {minId as id} TutorialD (master/main): :showexpr person ┌──────────────────┬───────────┐ │email::Text       │id::Integer│ ├──────────────────┼───────────┤ │"bob@example.com" │2          │ │"join@example.com"│1          │ └──────────────────┴───────────┘

Tutd Schema: person :: {id Integer, email Text}; person := relation{tuple{id 1, email "a@b.com"},                   tuple{id 2, email "b@c.com"},                   tuple{id 3, email "a@b.com"}};  Thinking process:   TutorialD (master/main): :showexpr (person group ({id} as ids)) where ^gt(count(@ids),1) ┌───────────┬───────────────────────────┐ │email::Text│ids::relation {id::Integer}│ ├───────────┼───────────────────────────┤ │"a@b.com"  │┌───────────┐              │ │           ││id::Integer│              │ │           │├───────────┤              │ │           ││1          │              │ │           ││3          │              │ │           │└───────────┘              │ └───────────┴───────────────────────────┘ Bonus1: Find ids that should be kept. TutorialD (master/main): minIds := (person group ({all but email} as ids):{minId := min(@ids)}){all but ids} TutorialD (master/main): :showexpr minIds ┌───────────┬──────────────┐ │email::Text│

Tutd Schema: customers := relation{tuple{id 1, name "Joe"}, tuple{id 2, name "Henry"}, tuple{id 3, name "Sam"}, tuple{id 4, name "Max"}}; orders := relation{tuple{id 1, customerId 3}, tuple{id 2, customerId 1}}; key customers_pkey {id} customers; key orders_pkey {id} orders; Thinking Process: TutorialD (master/main): :importtutd "183__Customers_Who_Never_Order.tutd" TutorialD (master/main): :showexpr (customers not matching (orders{customerId} rename {customerId as id})){name} ┌──────────┐ │name::Text│ ├──────────┤ │"Max"     │ │"Henry"   │ └──────────┘ What a one-liner! How nice!

Tutd Schema: employee := relation{id Int, name Text, salary Integer, managerId Maybe Int}                    {tuple{id int(1), name "Joe", salary 70000, managerId (Just int(3))},                     tuple{id int(2), name "Henry", salary 80000, managerId (Just int(4))},                     tuple{id int(3), name "Sam", salary 60000, managerId Nothing},                     tuple{id int(4), name "Max", salary 90000, managerId Nothing}}; key employee_pkey {id} employee; Thinking Process: TutorialD (master/main): :importtutd "181__Employees_Earning_More_Than_Their_Manager.tutd" -- get employees who has manager. TutorialD (master/main): employee_ := (((employee where ^isJust(@managerId)):{managerId_ := fromMaybe(int(0), @managerId)}){all but managerId} rename {managerId_ as managerId}) -- get managers who doesn't have manager. TutorialD (master/main): manager := ((employee where not ^isJust(@managerId)){all but managerId}) rename {name

-- PrjectM36 Schema for LeetCode175 -- if sql's nullable is not considered. person :: {personId Int, firstName Text, lastName Text};   key person_pkey {personId} person; address :: {addressId Int, personId Int, city Text, state Text}; key address_pkey {addressId} address; foreign key personId_in_address address{personId} in person{personId};   person := relation{tuple{personId int(1), firstName "Ted", lastName "Liao"},                   tuple{personId int(2), firstName "Johnny", lastName "Lee"}}; address := relation{tuple{addressId int(1), personId int(1), city "Taipei", state "R.O.C"}}; -- if nullable is considered personNullable :: {personId Int, firstName Maybe Text, lastName Maybe Text}; key personNullabe_pkey {personId} personNullable; addressNullable :: {addressId Int, personId Maybe Int, city Maybe Text, state Maybe Text}; key addressNullabe_pkey {addressId} addressNullable; personNullable := relation{tuple{person