diff options
Diffstat (limited to 'graphs/sql')
92 files changed, 989 insertions, 0 deletions
diff --git a/graphs/sql/a_new_dawn/req01.sql b/graphs/sql/a_new_dawn/req01.sql new file mode 100644 index 0000000..78015ec --- /dev/null +++ b/graphs/sql/a_new_dawn/req01.sql @@ -0,0 +1,66 @@ +CREATE SCHEMA nexus; +CREATE SCHEMA nexus_it; +CREATE SCHEMA techwave; +CREATE SCHEMA nexus_food; +CREATE SCHEMA guardforce; +CREATE TYPE nexus.position AS ENUM ( + 'JUNIOR', + 'SENIOR', + 'MANAGER', + 'DIRECTOR' +); +CREATE TYPE nexus.relationship_type AS ENUM ( + 'PARENT', + 'SUBSIDIARY', + 'AFFILIATE' +); +CREATE TYPE nexus_food.nutriscore AS ENUM ( + 'A', + 'B', + 'C', + 'D', + 'E' +); +CREATE TABLE nexus.employees +( + id SERIAL PRIMARY KEY, + first_name VARCHAR(25), + last_name VARCHAR(25), + employee_position nexus.position, + salary NUMERIC(10, 2) +); +CREATE TABLE nexus.nexus_relations +( + id SERIAL PRIMARY KEY, + parent_company VARCHAR(25), + child_company VARCHAR(25), + relationship_type nexus.relationship_type +); +CREATE TABLE nexus_it.software_assets +( + id SERIAL PRIMARY KEY, + software_name VARCHAR(50), + license_key UUID, + expiration_date DATE +); +CREATE TABLE techwave.project_milestones +( + id SERIAL PRIMARY KEY, + milestone_name VARCHAR(50), + due_date DATE, + completion_status BOOLEAN +); +CREATE TABLE nexus_food.products +( + id SERIAL PRIMARY KEY, + product_name VARCHAR(25), + product_nutriscore nexus_food.nutriscore +); +CREATE TABLE guardforce.incident_reports +( + id SERIAL PRIMARY KEY, + incident_description TEXT, + incident_date TIMESTAMP, + incident_location VARCHAR(50), + reported_by VARCHAR(50) +); diff --git a/graphs/sql/a_new_dawn/req02.sql b/graphs/sql/a_new_dawn/req02.sql new file mode 100644 index 0000000..6c705da --- /dev/null +++ b/graphs/sql/a_new_dawn/req02.sql @@ -0,0 +1,8 @@ +ALTER TABLE nexus.employees +ADD email VARCHAR(50); +ALTER TABLE nexus_it.software_assets +DROP IF EXISTS license_key; +ALTER TABLE nexus_food.products +RENAME COLUMN product_name TO product_title; +ALTER TABLE nexus.employees +ALTER COLUMN salary TYPE INTEGER; diff --git a/graphs/sql/a_new_dawn/req03.sql b/graphs/sql/a_new_dawn/req03.sql new file mode 100644 index 0000000..ef8c903 --- /dev/null +++ b/graphs/sql/a_new_dawn/req03.sql @@ -0,0 +1,6 @@ +DROP TABLE nexus.employees; +DROP TABLE nexus.nexus_relations; +DROP TABLE nexus_it.software_assets; +DROP TABLE techwave.project_milestones; +DROP TABLE nexus_food.products; +DROP TABLE guardforce.incident_reports; diff --git a/graphs/sql/a_new_dawn/req04.sql b/graphs/sql/a_new_dawn/req04.sql new file mode 100644 index 0000000..e3eb7cf --- /dev/null +++ b/graphs/sql/a_new_dawn/req04.sql @@ -0,0 +1,3 @@ +DROP TYPE nexus.position; +DROP TYPE nexus.relationship_type; +DROP TYPE nexus_food.nutriscore; diff --git a/graphs/sql/a_new_dawn/req05.sql b/graphs/sql/a_new_dawn/req05.sql new file mode 100644 index 0000000..d99e6e8 --- /dev/null +++ b/graphs/sql/a_new_dawn/req05.sql @@ -0,0 +1,5 @@ +DROP SCHEMA nexus; +DROP SCHEMA nexus_it; +DROP SCHEMA techwave; +DROP SCHEMA nexus_food; +DROP SCHEMA guardforce; diff --git a/graphs/sql/a_new_dawn/req06.sql b/graphs/sql/a_new_dawn/req06.sql new file mode 100644 index 0000000..87eb6e3 --- /dev/null +++ b/graphs/sql/a_new_dawn/req06.sql @@ -0,0 +1,5 @@ +DROP SCHEMA IF EXISTS nexus CASCADE; +DROP SCHEMA IF EXISTS nexus_it CASCADE; +DROP SCHEMA IF EXISTS nexus_food CASCADE; +DROP SCHEMA IF EXISTS techwave CASCADE; +DROP SCHEMA IF EXISTS guardforce CASCADE; diff --git a/graphs/sql/battle_plan/req01.sql b/graphs/sql/battle_plan/req01.sql new file mode 100644 index 0000000..8adb788 --- /dev/null +++ b/graphs/sql/battle_plan/req01.sql @@ -0,0 +1,19 @@ +EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE, FORMAT YAML) +WITH RECURSIVE fib AS ( + SELECT + 1 AS n, + 1::bigint AS fibn, + 1::bigint AS fibn_1 + UNION ALL + SELECT + n + 1 AS n, + fibn_1 AS fibn, + fibn + fibn_1 AS fibn_1 + FROM fib + WHERE n < 80 +) + +SELECT + n, + fibn +FROM fib; diff --git a/graphs/sql/battle_plan/req02.sql b/graphs/sql/battle_plan/req02.sql new file mode 100644 index 0000000..a9d8e9b --- /dev/null +++ b/graphs/sql/battle_plan/req02.sql @@ -0,0 +1,8 @@ +SELECT * +FROM ( + VALUES + ('a', 2), + ('b', 3), + ('b', 4), + ('c', 1) +) AS answers(question, choice); diff --git a/graphs/sql/battle_plan/req03.sql b/graphs/sql/battle_plan/req03.sql new file mode 100644 index 0000000..5a3ba51 --- /dev/null +++ b/graphs/sql/battle_plan/req03.sql @@ -0,0 +1,17 @@ +SELECT + people.first_name, + people.last_name, + gusto_tables.number_of_seats AS guest_number, + round(reservations.bill_total::numeric, 2) AS bill_total +FROM gusto_reservations AS reservations, + gusto_tables, + gusto_guests AS guests, + people +WHERE reservations.table_id = gusto_tables.id + AND reservations.id = guests.reservation_id + AND reservations.cancelled = FALSE + AND guests.guest_id = people.id + AND people.last_name LIKE concat(reservations.reservation_name, '%') +ORDER BY + gusto_tables.number_of_seats ASC, reservations.bill_total DESC NULLS LAST +LIMIT 10; diff --git a/graphs/sql/battle_plan/req04.sql b/graphs/sql/battle_plan/req04.sql new file mode 100644 index 0000000..33988c6 --- /dev/null +++ b/graphs/sql/battle_plan/req04.sql @@ -0,0 +1,8 @@ +SELECT * +FROM ( + VALUES + ('a', 1), + ('b', 1), + ('b', 3), + ('c', 1) +) AS answers(question, choice); diff --git a/graphs/sql/corrupted_friends/req01.sql b/graphs/sql/corrupted_friends/req01.sql new file mode 100644 index 0000000..bc759f7 --- /dev/null +++ b/graphs/sql/corrupted_friends/req01.sql @@ -0,0 +1,18 @@ +CREATE TYPE public.prefix AS ENUM ( + 'MRS', + 'MS', + 'MR', + 'DR' +); + +CREATE TABLE dtf.madelines_contacts +( + id INTEGER PRIMARY KEY, + title public.prefix, + first_name TEXT, + last_name TEXT, + phone TEXT, + email TEXT, + favorite BOOLEAN, + created_at TIMESTAMP +); diff --git a/graphs/sql/corrupted_friends/req02.sql b/graphs/sql/corrupted_friends/req02.sql new file mode 100644 index 0000000..11f9947 --- /dev/null +++ b/graphs/sql/corrupted_friends/req02.sql @@ -0,0 +1,41 @@ +INSERT INTO dtf.madelines_contacts +SELECT +id, +upper(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 1))::public.prefix AS title, +initcap(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 2)) AS first_name, +initcap(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 3)) AS last_name, +concat( + lpad( + regexp_replace( + regexp_replace(phone, '[^0-9\.\-]', '', 'g'), + '(.?.?.?)[\.\-].*', + '\1', + 'g' + ), + 3, '0' + ), + '.', + lpad( + regexp_replace( + regexp_replace(phone, '[^0-9\.\-]', '', 'g'), + '.*[\.\-](.?.?.?)[\.\-].*', + '\1', + 'g' + ), + 3, '0' + ), + '.', + lpad( + regexp_replace( + regexp_replace(phone, '[^0-9\.\-]', '', 'g'), + '.*[\.\-](.?.?.?)', + '\1', + 'g' + ), + 3, '0' + ) +) AS phone, +regexp_replace(trim(email), '[^[[:ascii:]]]', '', 'g') AS email, +trim(favorite) LIKE '1' AS favorite, +created_at::timestamp AS created_at +FROM dtf.madelines_contacts_corrupted AS c diff --git a/graphs/sql/corrupted_friends/req03.sql b/graphs/sql/corrupted_friends/req03.sql new file mode 100644 index 0000000..baa5863 --- /dev/null +++ b/graphs/sql/corrupted_friends/req03.sql @@ -0,0 +1,2 @@ +UPDATE dtf.madelines_contacts +SET email = concat(lower(first_name), '.', lower(last_name),'@roger_roger.com'); diff --git a/graphs/sql/daily_gazette/req01.sql b/graphs/sql/daily_gazette/req01.sql new file mode 100644 index 0000000..35ce8b8 --- /dev/null +++ b/graphs/sql/daily_gazette/req01.sql @@ -0,0 +1,14 @@ +CREATE SCHEMA rr_times; +CREATE TABLE rr_times.locales +( + language_code VARCHAR(2), + country_code VARCHAR(2), + name VARCHAR(50) UNIQUE NOT NULL, + PRIMARY KEY (language_code, country_code) +); +CREATE TABLE rr_times.translated_articles +( + id SERIAL PRIMARY KEY, + title TEXT NOT NULL, + body TEXT NOT NULL +); diff --git a/graphs/sql/daily_gazette/req02.sql b/graphs/sql/daily_gazette/req02.sql new file mode 100644 index 0000000..57eeb94 --- /dev/null +++ b/graphs/sql/daily_gazette/req02.sql @@ -0,0 +1,16 @@ +CREATE TABLE rr_times.translated_headlines +( + id SERIAL PRIMARY KEY, + title TEXT NOT NULL, + subtitle TEXT +); +CREATE TABLE rr_times.issues +( + id SERIAL PRIMARY KEY, + issued_at DATE NOT NULL, + language VARCHAR(2), + country VARCHAR(2), + FOREIGN KEY (language, country) REFERENCES rr_times.locales (language_code, country_code) ON DELETE SET NULL, + translated_headline_id INTEGER NOT NULL, + FOREIGN KEY (translated_headline_id) REFERENCES rr_times.translated_headlines (id) ON DELETE RESTRICT +); diff --git a/graphs/sql/daily_gazette/req03.sql b/graphs/sql/daily_gazette/req03.sql new file mode 100644 index 0000000..f0173c8 --- /dev/null +++ b/graphs/sql/daily_gazette/req03.sql @@ -0,0 +1,28 @@ +CREATE TYPE rr_times.rubric_theme AS ENUM +( + 'TECHNOLOGY', + 'ECONOMY', + 'HEALTH', + 'SPORT', + 'CULTURE', + 'POLITICS', + 'SCIENCE', + 'TRAVEL', + 'SOCIETY', + 'ENVIRONMENT', + 'EDUCATION', + 'MEDIA', + 'FASHION', + 'ARCHITECTURE', + 'BUSINESS', + 'SPACE' +); +CREATE TABLE rr_times.rubrics +( + id SERIAL UNIQUE NOT NULL, + theme rr_times.rubric_theme NOT NULL, + nb_columns INTEGER NOT NULL, + issue_id INTEGER NOT NULL, + FOREIGN KEY (issue_id) REFERENCES rr_times.issues (id) ON DELETE CASCADE, + PRIMARY KEY (id, theme, issue_id) +); diff --git a/graphs/sql/daily_gazette/req04.sql b/graphs/sql/daily_gazette/req04.sql new file mode 100644 index 0000000..a8e3eff --- /dev/null +++ b/graphs/sql/daily_gazette/req04.sql @@ -0,0 +1,11 @@ +CREATE TABLE rr_times.articles +( + id SERIAL PRIMARY KEY, + translated_article_id INTEGER NOT NULL, + rubric_id INTEGER NOT NULL, + language VARCHAR(2) NOT NULL, + country VARCHAR(2) NOT NULL, + FOREIGN KEY (language, country) REFERENCES rr_times.locales (language_code, country_code) ON DELETE CASCADE, + FOREIGN KEY (rubric_id) REFERENCES rr_times.rubrics (id) ON DELETE CASCADE, + FOREIGN KEY (translated_article_id) REFERENCES rr_times.translated_articles (id) ON DELETE CASCADE +); diff --git a/graphs/sql/daily_gazette/req05.sql b/graphs/sql/daily_gazette/req05.sql new file mode 100644 index 0000000..da15caa --- /dev/null +++ b/graphs/sql/daily_gazette/req05.sql @@ -0,0 +1,2 @@ +ALTER TABLE rr_times.translated_articles ADD UNIQUE (title); +ALTER TABLE rr_times.rubrics ADD CHECK (nb_columns >= 0); diff --git a/graphs/sql/fast_and_murderous/req01.sql b/graphs/sql/fast_and_murderous/req01.sql new file mode 100644 index 0000000..ac044ad --- /dev/null +++ b/graphs/sql/fast_and_murderous/req01.sql @@ -0,0 +1,2 @@ +SELECT person_id,case_id,content FROM justice.testimonies +WHERE content LIKE '%Nexus N%' diff --git a/graphs/sql/fast_and_murderous/req02.sql b/graphs/sql/fast_and_murderous/req02.sql new file mode 100644 index 0000000..2caffa9 --- /dev/null +++ b/graphs/sql/fast_and_murderous/req02.sql @@ -0,0 +1,2 @@ +SELECT person_id,case_id,content FROM justice.testimonies +WHERE content LIKE '%Nexus __%' diff --git a/graphs/sql/fast_and_murderous/req03.sql b/graphs/sql/fast_and_murderous/req03.sql new file mode 100644 index 0000000..4ae4836 --- /dev/null +++ b/graphs/sql/fast_and_murderous/req03.sql @@ -0,0 +1,3 @@ +SELECT T.person_id,C.id,T.content,C.description FROM justice.testimonies AS T,justice.cases AS C +WHERE T.content LIKE '%Nexus N3%' + AND C.description SIMILAR TO '%(speeding|speed|fast|reckless)%' AND T.case_id = C.id diff --git a/graphs/sql/fast_and_murderous/req04.sql b/graphs/sql/fast_and_murderous/req04.sql new file mode 100644 index 0000000..d2f6e0c --- /dev/null +++ b/graphs/sql/fast_and_murderous/req04.sql @@ -0,0 +1,2 @@ +SELECT name FROM nexus_stores.products +WHERE name ILIKE '%apple%' diff --git a/graphs/sql/fast_and_murderous/req05.sql b/graphs/sql/fast_and_murderous/req05.sql new file mode 100644 index 0000000..4dc8233 --- /dev/null +++ b/graphs/sql/fast_and_murderous/req05.sql @@ -0,0 +1,2 @@ +SELECT I.receipt_id,P.name,I.quantity,P.price FROM nexus_stores.products AS P,nexus_stores.receipt_items AS I +WHERE I.quantity > 10 AND P.name ILIKE '%apple%' AND I.product_id = P.id diff --git a/graphs/sql/fast_and_murderous/req06.sql b/graphs/sql/fast_and_murderous/req06.sql new file mode 100644 index 0000000..5f9f7af --- /dev/null +++ b/graphs/sql/fast_and_murderous/req06.sql @@ -0,0 +1,5 @@ +SELECT DISTINCT R.purchase_date,R.first_name,R.last_name,R.email,P.name,P.price,P.category FROM nexus_stores.receipts as R,nexus_stores.products AS P,nexus_stores.receipt_items AS I +WHERE R.email SIMILAR TO 's(a|e)m[a-z]{3,6}\_w[a-z]{6}[0-9]*@roger\_[a-z]+.[a-z]{3}' +AND I.receipt_id = R.id +AND I.product_id = P.id +AND P.name ILIKE '%apple%' diff --git a/graphs/sql/file_archaeology/req01.sql b/graphs/sql/file_archaeology/req01.sql new file mode 100644 index 0000000..9a9d059 --- /dev/null +++ b/graphs/sql/file_archaeology/req01.sql @@ -0,0 +1,2 @@ +SELECT MIN(size) FILTER (WHERE filename ILIKE '%secret%') AS smallest_secret_file_size, MIN(size) AS smallest_file_size +FROM dtf.madelines_files diff --git a/graphs/sql/file_archaeology/req02.sql b/graphs/sql/file_archaeology/req02.sql new file mode 100644 index 0000000..c4cf1d3 --- /dev/null +++ b/graphs/sql/file_archaeology/req02.sql @@ -0,0 +1,9 @@ +SELECT + filename, + size +FROM dtf.madelines_files +WHERE + size = (SELECT MAX(size) FROM dtf.madelines_files WHERE created_at BETWEEN '2059-12-03 23:59:59'::timestamp - interval '1 week' AND '2059-12-03 23:59:59'::timestamp) + AND created_at BETWEEN '2059-12-03 23:59:59'::timestamp + - interval '1 week' AND '2059-12-03 23:59:59'::timestamp +ORDER BY filename diff --git a/graphs/sql/file_archaeology/req03.sql b/graphs/sql/file_archaeology/req03.sql new file mode 100644 index 0000000..7e84aa1 --- /dev/null +++ b/graphs/sql/file_archaeology/req03.sql @@ -0,0 +1,7 @@ +SELECT + filename, + size +FROM dtf.madelines_files +WHERE + size >= (SELECT 0.75 * AVG(size) FROM dtf.madelines_files) +ORDER BY size DESC,filename diff --git a/graphs/sql/file_archaeology/req04.sql b/graphs/sql/file_archaeology/req04.sql new file mode 100644 index 0000000..57d86d3 --- /dev/null +++ b/graphs/sql/file_archaeology/req04.sql @@ -0,0 +1,7 @@ +SELECT + parent_id AS folder_id, + COUNT(filename) FILTER (WHERE permissions LIKE '%__x%') AS nb_executables +FROM dtf.madelines_files +GROUP BY parent_id +HAVING COUNT(filename) FILTER (WHERE permissions LIKE '%__x%') >= 3 +ORDER BY parent_id ASC NULLS FIRST diff --git a/graphs/sql/file_archaeology/req05.sql b/graphs/sql/file_archaeology/req05.sql new file mode 100644 index 0000000..a53e4de --- /dev/null +++ b/graphs/sql/file_archaeology/req05.sql @@ -0,0 +1,14 @@ +SELECT + owner, + COUNT(filename) AS num_files +FROM dtf.madelines_files +GROUP BY owner +HAVING + COUNT(filename) + = ( + SELECT MIN(test) + FROM + (SELECT COUNT(filename) AS test FROM dtf.madelines_files GROUP BY owner + ) AS n + ) +ORDER BY owner diff --git a/graphs/sql/following_you/req01.sql b/graphs/sql/following_you/req01.sql new file mode 100644 index 0000000..bbbff29 --- /dev/null +++ b/graphs/sql/following_you/req01.sql @@ -0,0 +1,9 @@ +SELECT p.id,first_name,last_name +FROM public.people AS p +WHERE EXISTS ( + SELECT * + FROM transport.metro_usage_logs, transport.metro_stations AS s + WHERE p.id = person_id + AND s.id = station_id + AND s.name = 'Morgane Abeille' +) diff --git a/graphs/sql/following_you/req02.sql b/graphs/sql/following_you/req02.sql new file mode 100644 index 0000000..0498e4e --- /dev/null +++ b/graphs/sql/following_you/req02.sql @@ -0,0 +1,7 @@ +SELECT id,street_id,created_at,person_id +FROM backup.street_logs +WHERE id NOT IN ( + SELECT id + FROM public.street_logs +) +-- INTERSECT diff --git a/graphs/sql/following_you/req03.sql b/graphs/sql/following_you/req03.sql new file mode 100644 index 0000000..a4cb4bf --- /dev/null +++ b/graphs/sql/following_you/req03.sql @@ -0,0 +1,3 @@ +SELECT * +FROM +((SELECT * FROM public.street_logs) INTERSECT (SELECT * FROM backup.street_logs)) AS res diff --git a/graphs/sql/following_you/req04.sql b/graphs/sql/following_you/req04.sql new file mode 100644 index 0000000..9cb46ed --- /dev/null +++ b/graphs/sql/following_you/req04.sql @@ -0,0 +1,25 @@ +SELECT + place_type, + entries, + place_id +FROM + ( + ( + SELECT + 'metro' AS place_type, + station_id AS place_id, + COUNT(DISTINCT person_id) AS entries + FROM transport.metro_usage_logs + GROUP BY station_id + ) + UNION + ( + SELECT + 'shop' AS place_type, + shop_id AS place_id, + COUNT(DISTINCT person_id) AS entries + FROM public.shop_entrance_logs + GROUP BY shop_id + ) + ) AS res +ORDER BY entries DESC, place_id ASC, place_type DESC diff --git a/graphs/sql/following_you/req05.sql b/graphs/sql/following_you/req05.sql new file mode 100644 index 0000000..691442b --- /dev/null +++ b/graphs/sql/following_you/req05.sql @@ -0,0 +1,51 @@ +SELECT * +FROM + ( + ( + ( + SELECT + person_id, + p.first_name AS person_first_name, + p.last_name AS person_last_name, + created_at, + 'metro' AS place, + station_id AS place_id + FROM transport.metro_usage_logs + INNER JOIN public.people AS p + ON p.id = person_id + WHERE + created_at BETWEEN timestamp '2059-12-03 17:00:00' AND timestamp '2059-12-03 21:59:59' + ) + UNION + ( + SELECT + person_id, + p.first_name AS person_first_name, + p.last_name AS person_last_name, + created_at, + 'shop' AS place, + shop_id AS place_id + FROM public.shop_entrance_logs + INNER JOIN public.people AS p + ON p.id = person_id + WHERE + created_at BETWEEN timestamp '2059-12-03 17:00:00' AND timestamp '2059-12-03 21:59:59' + ) + ) + UNION + ( + SELECT + person_id, + p.first_name AS person_first_name, + p.last_name AS person_last_name, + created_at, + 'street' AS place, + street_id AS place_id + FROM public.street_logs + INNER JOIN public.people AS p + ON p.id = person_id + WHERE + created_at BETWEEN timestamp '2059-12-03 17:00:00' AND timestamp '2059-12-03 21:59:59' + ) + ) AS res +ORDER BY created_at ASC, person_id ASC diff --git a/graphs/sql/following_you/req06.sql b/graphs/sql/following_you/req06.sql new file mode 100644 index 0000000..a8b0558 --- /dev/null +++ b/graphs/sql/following_you/req06.sql @@ -0,0 +1,15 @@ +SELECT person_id,validation,created_at +FROM( + ( + SELECT person_id,validation,created_at + FROM transport.metro_usage_logs + WHERE created_at BETWEEN timestamp '2059-12-03 12:00:00' AND timestamp '2059-12-03 13:59:59' +) +UNION +( + SELECT person_id,validation,created_at + FROM transport.metro_usage_logs + WHERE created_at BETWEEN timestamp '2059-12-03 20:00:00' AND timestamp '2059-12-03 21:59:59' + LIMIT 10 +)) AS res +ORDER BY created_at DESC, person_id ASC diff --git a/graphs/sql/gluttonous_order/req01.sql b/graphs/sql/gluttonous_order/req01.sql new file mode 100644 index 0000000..bd13ae6 --- /dev/null +++ b/graphs/sql/gluttonous_order/req01.sql @@ -0,0 +1,2 @@ +SELECT S.id FROM public.scrooge_eats_accounts AS S,public.companies AS C +WHERE C.name = 'DTF' AND S.company_id = C.id diff --git a/graphs/sql/gluttonous_order/req02.sql b/graphs/sql/gluttonous_order/req02.sql new file mode 100644 index 0000000..d695cff --- /dev/null +++ b/graphs/sql/gluttonous_order/req02.sql @@ -0,0 +1,7 @@ +INSERT INTO public.scrooge_eats_orders +SELECT 10000,ID.id,now(),0 +FROM +( + SELECT S.id FROM public.scrooge_eats_accounts AS S,public.companies AS C + WHERE C.name = 'DTF' AND S.company_id = C.id +) AS ID diff --git a/graphs/sql/gluttonous_order/req03.sql b/graphs/sql/gluttonous_order/req03.sql new file mode 100644 index 0000000..bfd5e83 --- /dev/null +++ b/graphs/sql/gluttonous_order/req03.sql @@ -0,0 +1,7 @@ +INSERT INTO public.scrooge_eats_basket_items ( order_id,item_id,quantity ) +SELECT 10000,I.id,1 +FROM +( + SELECT id FROM public.scrooge_eats_items + WHERE name = 'Kinder Bueno White' +) AS I diff --git a/graphs/sql/gluttonous_order/req04.sql b/graphs/sql/gluttonous_order/req04.sql new file mode 100644 index 0000000..0967a4a --- /dev/null +++ b/graphs/sql/gluttonous_order/req04.sql @@ -0,0 +1,7 @@ +INSERT INTO public.scrooge_eats_basket_items ( order_id,item_id,quantity ) +SELECT DISTINCT 10000,I.id,1 +FROM +( + SELECT id FROM public.scrooge_eats_items + WHERE name SIMILAR TO '(Kinder Bueno White)|(Tacos)|(Coca-Cola)' +) AS I diff --git a/graphs/sql/gluttonous_order/req05.sql b/graphs/sql/gluttonous_order/req05.sql new file mode 100644 index 0000000..e8bb1c3 --- /dev/null +++ b/graphs/sql/gluttonous_order/req05.sql @@ -0,0 +1,14 @@ +INSERT INTO public.scrooge_eats_orders +SELECT 10001,ID.id,now(),0 +FROM +( + SELECT S.id FROM public.scrooge_eats_accounts AS S,public.companies AS C + WHERE C.name = 'DTF' AND S.company_id = C.id +) AS ID; +INSERT INTO public.scrooge_eats_basket_items ( order_id,item_id,quantity ) +SELECT 10001,I.id,1 +FROM +( + SELECT id,price FROM public.scrooge_eats_items + WHERE name SIMILAR TO '(Paella)|(Cider)' +) AS I diff --git a/graphs/sql/gustos_night/req01.sql b/graphs/sql/gustos_night/req01.sql new file mode 100644 index 0000000..33c00aa --- /dev/null +++ b/graphs/sql/gustos_night/req01.sql @@ -0,0 +1,2 @@ +SELECT reservation_name,bill_total FROM gusto_reservations +WHERE bill_total < 100 diff --git a/graphs/sql/gustos_night/req02.sql b/graphs/sql/gustos_night/req02.sql new file mode 100644 index 0000000..cf05f88 --- /dev/null +++ b/graphs/sql/gustos_night/req02.sql @@ -0,0 +1,2 @@ +SELECT reservation_name,reservation_date,bill_total,table_id FROM gusto_reservations +WHERE reservation_name != 'Whitman' diff --git a/graphs/sql/gustos_night/req03.sql b/graphs/sql/gustos_night/req03.sql new file mode 100644 index 0000000..a3ae8e2 --- /dev/null +++ b/graphs/sql/gustos_night/req03.sql @@ -0,0 +1,2 @@ +SELECT name FROM gusto_menu_items +WHERE price = 12.99 OR price = 10.99 OR price > 42.44 diff --git a/graphs/sql/gustos_night/req04.sql b/graphs/sql/gustos_night/req04.sql new file mode 100644 index 0000000..91cb955 --- /dev/null +++ b/graphs/sql/gustos_night/req04.sql @@ -0,0 +1,2 @@ +SELECT id,reservation_name,reservation_date FROM gusto_reservations +WHERE reservation_date >= '2059-12-03 18:00:00' AND reservation_date < '2059-12-03 21:00:00' AND cancelled = FALSE diff --git a/graphs/sql/hello_roger_roger/req01.sql b/graphs/sql/hello_roger_roger/req01.sql new file mode 100644 index 0000000..525d95e --- /dev/null +++ b/graphs/sql/hello_roger_roger/req01.sql @@ -0,0 +1 @@ +SELECT 'Hello Roger Roger!' diff --git a/graphs/sql/hello_roger_roger/req02.sql b/graphs/sql/hello_roger_roger/req02.sql new file mode 100644 index 0000000..72f10ac --- /dev/null +++ b/graphs/sql/hello_roger_roger/req02.sql @@ -0,0 +1 @@ +SELECT * FROM tests diff --git a/graphs/sql/job_hunt/req01.sql b/graphs/sql/job_hunt/req01.sql new file mode 100644 index 0000000..c1d362d --- /dev/null +++ b/graphs/sql/job_hunt/req01.sql @@ -0,0 +1 @@ +SELECT * FROM public.jobs diff --git a/graphs/sql/job_hunt/req02.sql b/graphs/sql/job_hunt/req02.sql new file mode 100644 index 0000000..d5af78e --- /dev/null +++ b/graphs/sql/job_hunt/req02.sql @@ -0,0 +1 @@ +SELECT job_id,contract_type,start_date FROM public.job_offers diff --git a/graphs/sql/job_hunt/req03.sql b/graphs/sql/job_hunt/req03.sql new file mode 100644 index 0000000..4e8f291 --- /dev/null +++ b/graphs/sql/job_hunt/req03.sql @@ -0,0 +1 @@ +SELECT DISTINCT contract_type FROM public.job_offers diff --git a/graphs/sql/maas/correct_values.sql b/graphs/sql/maas/correct_values.sql new file mode 100644 index 0000000..3c9efbd --- /dev/null +++ b/graphs/sql/maas/correct_values.sql @@ -0,0 +1,41 @@ +INSERT INTO "memorin"."zoned_devices_logs" ( + "device_serial", + "version_id", + "zone_id", + "lat", + "long", + "temp", + "battery", + "signal_strength" +) +SELECT + -- The device should not be deactivated + ( + SELECT "serial_number" + FROM "memorin"."devices" + WHERE "deactivated_at" IS NULL + ORDER BY random() + LIMIT 1 + ), + -- The version should only exist + ( + SELECT "id" + FROM "memorin"."device_versions" + ORDER BY random() + LIMIT 1 + ), + "zone"."id" AS "zone_id", + -- Latitude and Longitude should be in the boundary box of the zone + random() * ("zone"."max_latitude" - "zone"."min_latitude") + + "zone"."min_latitude" AS "lat", + random() * ("zone"."max_longitude" - "zone"."min_longitude") + + "zone"."min_longitude" AS "long", + -- Temperature should be between 34 and 45 + floor(random() * (45 - 34 + 1) + 34) AS "temp", + -- Battery percentage should be between 0 and 100 + floor(random() * (100 + 1)) AS "battery", + -- Signal strength should be between 0 and 5 + floor(random() * (5 + 1)) AS "signal_strength" +FROM + "memorin"."geographic_zones" AS "zone" +ORDER BY random() LIMIT 1; diff --git a/graphs/sql/maas/incorrect_values.sql b/graphs/sql/maas/incorrect_values.sql new file mode 100644 index 0000000..07ec90d --- /dev/null +++ b/graphs/sql/maas/incorrect_values.sql @@ -0,0 +1,19 @@ +INSERT INTO "memorin"."zoned_devices_logs" ( + "device_serial", + "version_id", + "zone_id", + "lat", + "long", + "temp", + "battery", + "signal_strength" +) VALUES ( + 'W4JGVEMW51LE', + '0.0.0', + 39, + -12.3, + 0, + 2, + 290, + 12 +); diff --git a/graphs/sql/maas/req01.sql b/graphs/sql/maas/req01.sql new file mode 100644 index 0000000..d017c93 --- /dev/null +++ b/graphs/sql/maas/req01.sql @@ -0,0 +1,15 @@ +CREATE VIEW memorin.devices_summary AS + SELECT + total_devices, + active_devices, + total_devices - active_devices AS inactive_devices + FROM + ( + SELECT COUNT(*) AS total_devices + FROM memorin.devices + ) AS d, + ( + SELECT COUNT(*) AS active_devices + FROM memorin.devices + WHERE deactivated_at IS NULL + ) AS a; diff --git a/graphs/sql/maas/req02.sql b/graphs/sql/maas/req02.sql new file mode 100644 index 0000000..1d95021 --- /dev/null +++ b/graphs/sql/maas/req02.sql @@ -0,0 +1,22 @@ +CREATE TEMPORARY VIEW devices_metrics AS ( + SELECT + device_serial, + version_id, + logs.id AS log_id, + latitude AS lat, + longitude AS long, + temperature AS temp, + battery_percentage AS battery, + signal_strength + FROM + ( + SELECT serial_number + FROM memorin.devices + WHERE deactivated_at IS NULL + ) AS active, + memorin.device_logs AS logs + INNER JOIN memorin.device_versions AS v + ON version_id = v.id + WHERE serial_number = device_serial + ORDER BY device_serial, v.released_at DESC, log_id +); diff --git a/graphs/sql/maas/req03.sql b/graphs/sql/maas/req03.sql new file mode 100644 index 0000000..9b724cc --- /dev/null +++ b/graphs/sql/maas/req03.sql @@ -0,0 +1,47 @@ +CREATE VIEW memorin.zoned_devices_logs AS + SELECT + device_serial, + version_id, + zone_id, + latitude AS lat, + longitude AS long, + temperature AS temp, + battery_percentage AS battery, + signal_strength + FROM + memorin.device_logs AS logs + WHERE + device_serial IN ( + SELECT serial_number + FROM memorin.devices + WHERE deactivated_at IS NULL + ) + AND version_id IN ( + SELECT id + FROM memorin.device_versions + WHERE id = logs.version_id + ) + AND temperature BETWEEN 34 AND 45 + AND signal_strength BETWEEN 0 AND 5 + AND battery_percentage BETWEEN 0 AND 100 + AND longitude BETWEEN ( + SELECT min_longitude + FROM memorin.geographic_zones + WHERE id = logs.zone_id + ) + AND ( + SELECT max_longitude + FROM memorin.geographic_zones + WHERE id = logs.zone_id + ) + AND latitude BETWEEN ( + SELECT min_latitude + FROM memorin.geographic_zones + WHERE id = logs.zone_id + ) + AND ( + SELECT max_latitude + FROM memorin.geographic_zones + WHERE id = logs.zone_id + ) + WITH CHECK OPTION; diff --git a/graphs/sql/maas/req04_select.sql b/graphs/sql/maas/req04_select.sql new file mode 100644 index 0000000..8829df5 --- /dev/null +++ b/graphs/sql/maas/req04_select.sql @@ -0,0 +1,4 @@ +REFRESH MATERIALIZED VIEW memorin.outdated_devices; +SELECT * +FROM memorin.outdated_devices +ORDER BY versions_behind DESC, serial_number diff --git a/graphs/sql/maas/req04_view.sql b/graphs/sql/maas/req04_view.sql new file mode 100644 index 0000000..bd00c7d --- /dev/null +++ b/graphs/sql/maas/req04_view.sql @@ -0,0 +1,21 @@ +CREATE MATERIALIZED VIEW memorin.outdated_devices AS + SELECT + serial_number, + version_id, + released_at, + eol_timestamp, + ( + SELECT COUNT(*) AS versions_behind + FROM memorin.device_versions AS vs + WHERE vs.released_at > v.released_at + ) AS versions_behind + FROM + memorin.devices AS d, + memorin.device_versions AS v + WHERE eol_timestamp IS NOT NULL + AND v.id = version_id + AND serial_number IN ( + SELECT serial_number + FROM memorin.devices + WHERE deactivated_at IS NULL + ); diff --git a/graphs/sql/maas/req05_select.sql b/graphs/sql/maas/req05_select.sql new file mode 100644 index 0000000..2a8e816 --- /dev/null +++ b/graphs/sql/maas/req05_select.sql @@ -0,0 +1,12 @@ +SELECT + name AS zone_name, + COUNT(DISTINCT data_center_id) AS data_center_count, + COUNT(DISTINCT sh.id) AS total_servers, + SUM(core_count) AS total_cores, + SUM(ram) AS total_ram, + SUM(storage) AS total_storage +FROM memorin.server_hierarchy AS sh +INNER JOIN memorin.geographic_zones AS z +ON z.id = zone_id +GROUP BY name +ORDER BY data_center_count DESC, total_servers DESC, zone_name diff --git a/graphs/sql/maas/req05_view.sql b/graphs/sql/maas/req05_view.sql new file mode 100644 index 0000000..b6e792f --- /dev/null +++ b/graphs/sql/maas/req05_view.sql @@ -0,0 +1,22 @@ +CREATE RECURSIVE VIEW memorin.server_hierarchy (id,zone_id,core_count,ram,storage,data_center_id) AS ( + SELECT + id, + zone_id, + core_count, + ram, + storage, + id AS data_center_id + FROM memorin.servers + WHERE master_id IS NULL + UNION ALL + SELECT + servers.id, + servers.zone_id, + servers.core_count, + servers.ram, + servers.storage, + sh.data_center_id + FROM memorin.servers + INNER JOIN server_hierarchy AS sh + ON sh.id = servers.master_id +); diff --git a/graphs/sql/memory_leaks/req01.sql b/graphs/sql/memory_leaks/req01.sql new file mode 100644 index 0000000..50e15f8 --- /dev/null +++ b/graphs/sql/memory_leaks/req01.sql @@ -0,0 +1,2 @@ +CREATE INDEX subconscious_memories +ON memorin_test.memories (title) diff --git a/graphs/sql/memory_leaks/req02.sql b/graphs/sql/memory_leaks/req02.sql new file mode 100644 index 0000000..d4919c9 --- /dev/null +++ b/graphs/sql/memory_leaks/req02.sql @@ -0,0 +1,5 @@ +CREATE INDEX specific_vitals_index +ON memorin_test.vitals (heart_rate, respiratory_rate); + +CREATE INDEX vitals_in_index +ON memorin_test.memories (vitals_id, created_at) diff --git a/graphs/sql/memory_leaks/req03.sql b/graphs/sql/memory_leaks/req03.sql new file mode 100644 index 0000000..8a7fd22 --- /dev/null +++ b/graphs/sql/memory_leaks/req03.sql @@ -0,0 +1,7 @@ +CREATE INDEX meme +ON memorin_test.memories (id) +WHERE created_at +BETWEEN '2059-12-03 16:00:00' AND '2059-12-04 00:00:00'; + +CREATE UNIQUE INDEX emote +ON memorin_test.emotions (emotion_type,memory_id) diff --git a/graphs/sql/memory_leaks/req04.sql b/graphs/sql/memory_leaks/req04.sql new file mode 100644 index 0000000..c08affa --- /dev/null +++ b/graphs/sql/memory_leaks/req04.sql @@ -0,0 +1,3 @@ +CREATE INDEX memid +ON memorin_test.emotions (memory_id) +WHERE level >= 4; diff --git a/graphs/sql/memory_leaks/req05.sql b/graphs/sql/memory_leaks/req05.sql new file mode 100644 index 0000000..e0a55c7 --- /dev/null +++ b/graphs/sql/memory_leaks/req05.sql @@ -0,0 +1,2 @@ +CREATE INDEX description +ON memorin_test.memories (length(description)) diff --git a/graphs/sql/outsourcing/req01.sql b/graphs/sql/outsourcing/req01.sql new file mode 100644 index 0000000..caf3c1f --- /dev/null +++ b/graphs/sql/outsourcing/req01.sql @@ -0,0 +1,6 @@ +SELECT T.id AS trial_id,case_id,classification,description +INTO public.definitive_trials +FROM justice.trials AS T, justice.cases AS C +WHERE T.case_id = C.id +AND T.status = 'FINISHED' +ORDER BY classification, case_id diff --git a/graphs/sql/outsourcing/req02.sql b/graphs/sql/outsourcing/req02.sql new file mode 100644 index 0000000..1c2dd3d --- /dev/null +++ b/graphs/sql/outsourcing/req02.sql @@ -0,0 +1,6 @@ +SELECT person_id,first_name,last_name,birth_date,O.created_at::date AS conviction_date,serving_time,(date '2059-12-03' <= T.created_at::date - concat(serving_time, ' month')::interval) AS could_be_released +INTO public.outcome_status +FROM justice.defendants AS D, public.people AS P, justice.trials as T, justice.outcomes AS O, justice.cases AS C +WHERE D.person_id = P.id AND D.trial_id = T.id AND T.id = O.trial_id AND T.case_id = C.id +AND O.outcome = 'GUILTY' +AND C.classification = 'CRIME' diff --git a/graphs/sql/outsourcing/req03.sql b/graphs/sql/outsourcing/req03.sql new file mode 100644 index 0000000..226a7e1 --- /dev/null +++ b/graphs/sql/outsourcing/req03.sql @@ -0,0 +1,5 @@ +SELECT person_id, first_name, last_name, birth_date, classification, description +INTO TEMPORARY TABLE pg_temp.not_guilty +FROM justice.trials AS T, justice.cases AS C, justice.outcomes AS O, public.people AS P, justice.defendants AS D +WHERE T.case_id = C.id AND O.trial_id = T.id AND D.trial_id = T.id AND D.person_id = P.id +AND O.outcome = 'NOT_GUILTY' diff --git a/graphs/sql/outsourcing/req04.sql b/graphs/sql/outsourcing/req04.sql new file mode 100644 index 0000000..badca1a --- /dev/null +++ b/graphs/sql/outsourcing/req04.sql @@ -0,0 +1,47 @@ + SELECT + person_id, + first_name, + last_name, + o.created_at, + serving_time, + o.created_at::timestamp + + concat(serving_time, ' month')::interval AS release_date + INTO TEMPORARY TABLE a + FROM justice.trials AS t, justice.cases AS c, justice.defendants AS d, + public.people AS p, justice.outcomes AS o, + justice.sentence_reductions AS r + WHERE + t.case_id = c.id + AND t.id = d.trial_id + AND d.person_id = p.id + AND o.trial_id = t.id + AND r.outcome_id != o.id + AND o.outcome = 'GUILTY'; + + SELECT + person_id, + first_name, + last_name, + o.created_at, + serving_time, + o.created_at + concat(serving_time - r.amount, ' month')::interval AS release_date + INTO TEMPORARY TABLE b + FROM justice.trials AS t, justice.cases AS c, justice.defendants AS d, + public.people AS p, justice.outcomes AS o, + justice.sentence_reductions AS r + WHERE + t.case_id = c.id + AND t.id = d.trial_id + AND d.person_id = p.id + AND o.trial_id = t.id + AND r.outcome_id = o.id + AND o.outcome = 'GUILTY'; + +INSERT INTO b +SELECT DISTINCT * FROM a +WHERE NOT EXISTS (SELECT * FROM b WHERE a.person_id = b.person_id); + +SELECT DISTINCT * +INTO public.release_dates +FROM b +ORDER BY b.release_date DESC, serving_time DESC diff --git a/graphs/sql/red_flag/req01.sql b/graphs/sql/red_flag/req01.sql new file mode 100644 index 0000000..cbafe43 --- /dev/null +++ b/graphs/sql/red_flag/req01.sql @@ -0,0 +1,8 @@ +BEGIN; + + DELETE FROM public.epix_posts + WHERE id = 836; + + SELECT * FROM public.epix_posts WHERE id BETWEEN 830 AND 840 ORDER BY id; + +COMMIT; diff --git a/graphs/sql/red_flag/req02.sql b/graphs/sql/red_flag/req02.sql new file mode 100644 index 0000000..3c379db --- /dev/null +++ b/graphs/sql/red_flag/req02.sql @@ -0,0 +1,7 @@ +BEGIN; + + UPDATE public.epix_posts + SET downvotes = upvotes * ((SELECT SUM((downvotes::numeric/upvotes::numeric)::numeric)::numeric FROM public.epix_posts WHERE author_id = 1056 AND id != 139)::numeric / (SELECT COUNT(*) FROM public.epix_posts WHERE author_id = 1056 AND id != 139)::numeric) + WHERE id = 139; + +COMMIT; diff --git a/graphs/sql/red_flag/req03.sql b/graphs/sql/red_flag/req03.sql new file mode 100644 index 0000000..c13cc56 --- /dev/null +++ b/graphs/sql/red_flag/req03.sql @@ -0,0 +1,4 @@ +DELETE FROM public.epix_posts AS p +USING public.epix_hashtags AS h,public.people AS pp, public.epix_accounts AS a +WHERE h.id = p.hashtag_id AND h.name = 'EndSurveillance' AND pp.id = a.person_id AND p.author_id = a.id +RETURNING first_name,last_name,username,p.body AS post_content; diff --git a/graphs/sql/red_flag/req04.sql b/graphs/sql/red_flag/req04.sql new file mode 100644 index 0000000..35b998f --- /dev/null +++ b/graphs/sql/red_flag/req04.sql @@ -0,0 +1,5 @@ +BEGIN; + UPDATE public.epix_hashtags + SET deleted_at = localtimestamp + WHERE name = 'EndSurveillance'; + COMMIT; diff --git a/graphs/sql/safe_haven/req01.sql b/graphs/sql/safe_haven/req01.sql new file mode 100644 index 0000000..4c0d403 --- /dev/null +++ b/graphs/sql/safe_haven/req01.sql @@ -0,0 +1,11 @@ +CREATE TABLE banking_transactions_poc +( + id serial PRIMARY KEY, + sender varchar(22) NOT NULL, + receiver varchar(22) NOT NULL, + amount numeric(16, 2) NOT NULL, + CHECK (sender SIMILAR TO 'RR[0-9]{2}[A-Z]{4}[0-9]{14}'), + CHECK (receiver SIMILAR TO 'RR[0-9]{2}[A-Z]{4}[0-9]{14}'), + CHECK (sender != receiver), + CHECK (amount > 0) +); diff --git a/graphs/sql/socially_unfit/req01.sql b/graphs/sql/socially_unfit/req01.sql new file mode 100644 index 0000000..cd0c60b --- /dev/null +++ b/graphs/sql/socially_unfit/req01.sql @@ -0,0 +1,2 @@ +SELECT * FROM public.people +LIMIT 100 diff --git a/graphs/sql/socially_unfit/req02.sql b/graphs/sql/socially_unfit/req02.sql new file mode 100644 index 0000000..2178826 --- /dev/null +++ b/graphs/sql/socially_unfit/req02.sql @@ -0,0 +1,2 @@ +SELECT id,first_name,last_name FROM public.people +LIMIT 100 OFFSET 42 diff --git a/graphs/sql/socially_unfit/req03.sql b/graphs/sql/socially_unfit/req03.sql new file mode 100644 index 0000000..4f1d62d --- /dev/null +++ b/graphs/sql/socially_unfit/req03.sql @@ -0,0 +1,3 @@ +SELECT DISTINCT first_name,last_name,death_date,social_credit_balance FROM public.people +WHERE death_date IS NOT NULL +ORDER BY death_date DESC,social_credit_balance DESC LIMIT 100 diff --git a/graphs/sql/socially_unfit/req04.sql b/graphs/sql/socially_unfit/req04.sql new file mode 100644 index 0000000..a11948a --- /dev/null +++ b/graphs/sql/socially_unfit/req04.sql @@ -0,0 +1,6 @@ +SELECT first_name,last_name,birth_date,job_id,social_credit_balance +FROM public.people +WHERE job_id IS NOT NULL +AND social_credit_balance > 0 +AND death_date IS NULL +ORDER BY social_credit_balance DESC,birth_date,job_id LIMIT 100 OFFSET 200 diff --git a/graphs/sql/socially_unfit/req05.sql b/graphs/sql/socially_unfit/req05.sql new file mode 100644 index 0000000..45029d8 --- /dev/null +++ b/graphs/sql/socially_unfit/req05.sql @@ -0,0 +1,5 @@ +SELECT id,person_id,credits_change,action_description +FROM public.social_credit_history +WHERE date >= '2059-12-03 17:00' AND date <= '2059-12-03 22:00' +ORDER BY credits_change,id +LIMIT 10 diff --git a/graphs/sql/traffic_offense/req01.sql b/graphs/sql/traffic_offense/req01.sql new file mode 100644 index 0000000..3258fef --- /dev/null +++ b/graphs/sql/traffic_offense/req01.sql @@ -0,0 +1,2 @@ +SELECT * FROM public.traffic_violations +WHERE license_plate LIKE 'AZ__36_' diff --git a/graphs/sql/traffic_offense/req02.sql b/graphs/sql/traffic_offense/req02.sql new file mode 100644 index 0000000..d775b0c --- /dev/null +++ b/graphs/sql/traffic_offense/req02.sql @@ -0,0 +1,4 @@ +SELECT * FROM public.traffic_violations +WHERE license_plate LIKE 'AZ__36_' +AND violation_date >= timestamp '2059-12-03 21:00:00' - interval '1 h' +AND violation_date <= timestamp '2059-12-03 21:00:00' + interval '1 h' diff --git a/graphs/sql/traffic_offense/req03.sql b/graphs/sql/traffic_offense/req03.sql new file mode 100644 index 0000000..daebf4d --- /dev/null +++ b/graphs/sql/traffic_offense/req03.sql @@ -0,0 +1,8 @@ +SELECT * FROM public.traffic_violations +WHERE license_plate LIKE 'AZ__36_' +AND violation_date >= timestamp '2059-12-03 21:00:00' - interval '1 h' +AND violation_date <= timestamp '2059-12-03 21:00:00' + interval '1 h' +AND ( + description = 'SPEED_AND_TRAFFIC_VIOLATION' + OR description = 'RECKLESS_AND_DANGEROUS_DRIVING' +) diff --git a/graphs/sql/traffic_offense/req04.sql b/graphs/sql/traffic_offense/req04.sql new file mode 100644 index 0000000..f616e3a --- /dev/null +++ b/graphs/sql/traffic_offense/req04.sql @@ -0,0 +1,9 @@ +SELECT V2.id,V2.description,V2.violation_date,V2.street_id,V2.license_plate FROM public.traffic_violations AS V1,public.traffic_violations AS V2 +WHERE V1.license_plate LIKE 'AZ__36_' +AND V1.violation_date >= timestamp '2059-12-03 21:00:00' - interval '1 h' +AND V1.violation_date <= timestamp '2059-12-03 21:00:00' + interval '1 h' +AND ( + V1.description = 'SPEED_AND_TRAFFIC_VIOLATION' + OR V1.description = 'RECKLESS_AND_DANGEROUS_DRIVING' +) +AND V1.license_plate = V2.license_plate diff --git a/graphs/sql/traffic_offense/req05.sql b/graphs/sql/traffic_offense/req05.sql new file mode 100644 index 0000000..41aaebd --- /dev/null +++ b/graphs/sql/traffic_offense/req05.sql @@ -0,0 +1,10 @@ +SELECT V2.id,V2.description,V2.violation_date,V2.street_id,V2.license_plate FROM public.traffic_violations AS V1,public.traffic_violations AS V2 +WHERE V1.license_plate LIKE 'AZ__36_' +AND V1.violation_date >= timestamp '2059-12-03 21:00:00' - interval '1 h' +AND V1.violation_date <= timestamp '2059-12-03 21:00:00' + interval '1 h' +AND ( + V1.description = 'SPEED_AND_TRAFFIC_VIOLATION' + OR V1.description = 'RECKLESS_AND_DANGEROUS_DRIVING' +) +AND V1.license_plate = V2.license_plate +AND V2.violation_date::date = '2059-12-03 21:00:00'::date diff --git a/graphs/sql/vault_cataloging/req01.sql b/graphs/sql/vault_cataloging/req01.sql new file mode 100644 index 0000000..25394d0 --- /dev/null +++ b/graphs/sql/vault_cataloging/req01.sql @@ -0,0 +1,12 @@ +SELECT + F.id, + F.filename, + CASE + WHEN F.decrypted THEN 'File was successfully decrypted.' + WHEN P.decrypted THEN 'File was successfully decrypted because its containing folder was successfully decrypted.' + ELSE 'File remains encrypted.' + END AS decryption_status +FROM dtf.madelines_files_results AS F +LEFT JOIN dtf.madelines_files_results AS P +ON P.id = F.parent_id +ORDER BY F.id diff --git a/graphs/sql/vault_cataloging/req02.sql b/graphs/sql/vault_cataloging/req02.sql new file mode 100644 index 0000000..bdfecaa --- /dev/null +++ b/graphs/sql/vault_cataloging/req02.sql @@ -0,0 +1,14 @@ +SELECT + id, + madelines_files_results.size AS stored_size, + d.decrypted, + COALESCE(madelines_files_results.size, d.size) AS calculated_size +FROM dtf.madelines_files_results +LEFT JOIN +( + SELECT decrypted,AVG(size)::bigint AS size FROM dtf.madelines_files_results + WHERE parent_id IS NOT NULL + GROUP BY decrypted +) AS d ON madelines_files_results.decrypted = d.decrypted +WHERE parent_id IS NOT NULL +ORDER BY madelines_files_results.id diff --git a/graphs/sql/vault_cataloging/req03.sql b/graphs/sql/vault_cataloging/req03.sql new file mode 100644 index 0000000..12dd9cf --- /dev/null +++ b/graphs/sql/vault_cataloging/req03.sql @@ -0,0 +1,11 @@ +SELECT + filename, + LEAST(rsa_time,hyper_pulse_time,quantum_x_time,aes_time,d_crypt_time) AS best_time, + GREATEST(rsa_time,hyper_pulse_time,quantum_x_time,aes_time,d_crypt_time) AS worst_time +FROM dtf.madelines_files_results +ORDER BY ( + CASE + WHEN filename LIKE '.%' THEN 1 + ELSE 2 + END +),filename diff --git a/graphs/sql/vault_cataloging/req04.sql b/graphs/sql/vault_cataloging/req04.sql new file mode 100644 index 0000000..9276b1c --- /dev/null +++ b/graphs/sql/vault_cataloging/req04.sql @@ -0,0 +1,32 @@ +WITH filtered_times AS ( + SELECT + COALESCE( + NULLIF(rsa_time,0), + (SELECT MAX(rsa_time) FROM dtf.madelines_files_results) + ) AS rsa_time, + COALESCE( + NULLIF(hyper_pulse_time,0), + (SELECT MAX(hyper_pulse_time) FROM dtf.madelines_files_results) + ) AS hyper_pulse_time, + COALESCE( + NULLIF(quantum_x_time,0), + (SELECT MAX(quantum_x_time) FROM dtf.madelines_files_results) + ) AS quantum_x_time, + COALESCE( + NULLIF(aes_time,0), + (SELECT MAX(aes_time) FROM dtf.madelines_files_results) + ) AS aes_time, + COALESCE( + NULLIF(d_crypt_time,0), + (SELECT MAX(d_crypt_time) FROM dtf.madelines_files_results) + ) AS d_crypt_time + FROM dtf.madelines_files_results +) + +SELECT + AVG(rsa_time)::numeric(10,2) AS avg_rsa_time, + AVG(hyper_pulse_time)::numeric(10,2) AS avg_hyper_pulse_time, + AVG(quantum_x_time)::numeric(10,2) AS avg_quantum_x_time, + AVG(aes_time)::numeric(10,2) AS avg_aes_time, + AVG(d_crypt_time)::numeric(10,2) AS avg_d_crypt_time +FROM filtered_times diff --git a/graphs/sql/where_is_waldo/req01.sql b/graphs/sql/where_is_waldo/req01.sql new file mode 100644 index 0000000..9ab6dfb --- /dev/null +++ b/graphs/sql/where_is_waldo/req01.sql @@ -0,0 +1,8 @@ +SELECT sales.id AS sales_id,product_id,sales.checkout_id,sale_timestamp,cashier_shifts.id AS shift_id,employee_id,start_timestamp AS shift_start_timestamp,end_timestamp AS shift_end_timestamp +INTO dtf.shifts_and_sales_78 +FROM nexus_stores.sales +INNER JOIN nexus_stores.cashier_shifts ON sales.checkout_id = cashier_shifts.checkout_id +AND sales.store_id = 78 +AND start_timestamp = timestamp '2059-12-01 13:00:00' +AND sale_timestamp >= start_timestamp +AND sale_timestamp < end_timestamp diff --git a/graphs/sql/where_is_waldo/req02.sql b/graphs/sql/where_is_waldo/req02.sql new file mode 100644 index 0000000..d7064a4 --- /dev/null +++ b/graphs/sql/where_is_waldo/req02.sql @@ -0,0 +1,6 @@ +SELECT * +FROM nexus_stores.cashier_shifts AS s +FULL JOIN nexus_stores.cashier_shifts as c +ON s.id = c.id +WHERE s.employee_id IS NOT NULL +AND s.store_id IS NOT NULL diff --git a/graphs/sql/where_is_waldo/req03.sql b/graphs/sql/where_is_waldo/req03.sql new file mode 100644 index 0000000..70a712a --- /dev/null +++ b/graphs/sql/where_is_waldo/req03.sql @@ -0,0 +1,4 @@ +SELECT COUNT(s.*) AS store_id_null_count,COUNT(c.*) AS employee_id_null_count +FROM (SELECT * FROM nexus_stores.cashier_shifts WHERE store_id IS NULL) AS s +FULL JOIN (SELECT * FROM nexus_stores.cashier_shifts WHERE employee_id IS NULL) AS c +ON s.id = c.id diff --git a/graphs/sql/where_is_waldo/req04.sql b/graphs/sql/where_is_waldo/req04.sql new file mode 100644 index 0000000..3c83d6f --- /dev/null +++ b/graphs/sql/where_is_waldo/req04.sql @@ -0,0 +1,4 @@ +SELECT s.id,checkout_id,employee_id,s.store_id AS shift_store_id,c.store_id AS checkout_store_id,start_timestamp,end_timestamp +FROM nexus_stores.cashier_shifts AS s +INNER JOIN nexus_stores.checkouts AS c +ON c.id = s.checkout_id diff --git a/graphs/sql/where_is_waldo/req05.sql b/graphs/sql/where_is_waldo/req05.sql new file mode 100644 index 0000000..8a03061 --- /dev/null +++ b/graphs/sql/where_is_waldo/req05.sql @@ -0,0 +1,14 @@ +INSERT INTO nexus_stores.cashier_shifts +VALUES +( + 30000, + '68cdd3ec', + NULL, + 78, + timestamp '2059-12-01 13:00:00', + timestamp '2059-12-01 15:00:00' +); +INSERT INTO nexus_stores.sales +SELECT 325000, id AS product_id,'68cdd3ec',78,timestamp '2059-12-01 14:47:07' +FROM nexus_stores.products +WHERE name = 'Golden Apple'; diff --git a/graphs/sql/where_is_waldo/req06.sql b/graphs/sql/where_is_waldo/req06.sql new file mode 100644 index 0000000..8a7a35e --- /dev/null +++ b/graphs/sql/where_is_waldo/req06.sql @@ -0,0 +1,20 @@ +SELECT + e.id AS employee_id, + person_id, + first_name, + last_name +FROM nexus_stores.employees AS e +INNER JOIN public.people AS p + ON e.person_id = p.id +WHERE + e.id NOT IN ( + SELECT employee_id + FROM nexus_stores.cashier_shifts + INNER JOIN nexus_stores.employees + ON employee_id = employees.id AND position = 'CASHIER' + WHERE + start_timestamp = timestamp '2059-12-01 13:00:00' + AND cashier_shifts.store_id = 78 + ) + AND e.position = 'CASHIER' + AND e.store_id = 78 |