/
romanchel1s
/
sql-translator
Обзор
Документация
Войти
/
romanchel1s
/
sql-translator
Код
Запросы
0
Задачи
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
backend/cases/case08.sql
46 строк
2 KB
Roman Purtov
innit
01 сен 2024, 11:27
01 сен 2024, 11:27
58a1772
Код
Авторство
О чём код?
CREATE TABLE people ( person_id INTEGER NOT NULL PRIMARY KEY, given_name VARCHAR2(100) NOT NULL, family_name VARCHAR2(100) NOT NULL, title VARCHAR2(20), birth_date DATE ); CREATE TABLE patients ( patient_id INTEGER NOT NULL PRIMARY KEY REFERENCES people (person_id), last_admission_date DATE ); CREATE TABLE staff ( staff_id INTEGER NOT NULL PRIMARY KEY REFERENCES people (person_id), hired_date DATE ); INSERT ALL /* Everyone is a person, so insert all rows into people */ WHEN 1=1 THEN INTO people (person_id, given_name, family_name, title) VALUES (id, given_name, family_name, title) /* Only people with an admission date are patients */ WHEN admission_date IS NOT NULL THEN INTO patients (patient_id, last_admission_date) VALUES (id, admission_date) /* Only people with a hired date are staff */ WHEN hired_date IS NOT NULL THEN INTO staff (staff_id, hired_date) VALUES (id, hired_date) WITH names AS ( SELECT 4 id, 'Ruth' given_name, 'Fox' family_name, 'Mrs' title, NULL hired_date, DATE'2009-12-31' admission_date FROM dual UNION ALL SELECT 5 id, 'Isabelle' given_name, 'Squirrel' family_name, 'Miss' title , NULL hired_date, DATE'2014-01-01' admission_date FROM dual UNION ALL SELECT 6 id, 'Justin' given_name, 'Frog' family_name, 'Master' title, NULL hired_date, DATE'2015-04-22' admission_date FROM dual UNION ALL SELECT 7 id, 'Lisa' given_name, 'Owl' family_name, 'Dr' title, DATE'2015-01-01' hired_date, NULL admission_date FROM dual ) SELECT * FROM names;