Create SQL files for videos

[mlh496]

Upload sql files to create the tables and insert the data used in the videos. This way users will not have to manually create the table.

[joaopcnogueira]

Is there the data to create the tables used the in the 2 join videos?

[idjey-zz]

Hello Socratica,

I am looking python file to generate random people data, as shown in Index video. Could you please send me a link for it.

Thanks.

[chaseydog]

Is the data for the Mars DB used in the Joins videos available?

[smartdataworks]

Here are the SQL commands to make the tables: CREATE TABLE martian ( martian_id SERIAL PRIMARY KEY, first_name CHARACTER VARYING(25), last_name CHARACTER VARYING(25), base_id INTEGER, super_id INTEGER );

INSERT INTO martian (martian_id, first_name, last_name, base_id, super_id) VALUES (DEFAULT, 'Ray', 'Bradbury', 1, NULL), (DEFAULT, 'John', 'Black', 4, 10), (DEFAULT, 'Samuel', 'Hinkston', 4, 2), (DEFAULT, 'Jeff', 'Spender', 1, 9), (DEFAULT, 'Sam', 'Parkhill', 2, 12), (DEFAULT, 'Elma', 'Parkhill', 3, 8), (DEFAULT, 'Melissa', 'Lewis', 1, 1), (DEFAULT, 'Mark', 'Watney', 3, NULL), (DEFAULT, 'Beth', 'Johanssen', 1, 1), (DEFAULT, 'Chris', 'Beck', 4, NULL), (DEFAULT, 'Nathaniel', 'York', 4, 2), (DEFAULT, 'Elon', 'Musk', 2, NULL), (DEFAULT, 'John', 'Carter', NULL, 8);

CREATE TABLE base ( base_id SERIAL PRIMARY KEY, base_name CHARACTER VARYING(30), founded DATE );

INSERT INTO base (base_id, base_name, founded) VALUES (DEFAULT, 'Tharsisland', '2037-06-03'), (DEFAULT, 'Valles Marineris 2.0', '2040-12-01'), (DEFAULT, 'Gale Cratertown', '2014-08-16'), (DEFAULT, 'New New New York', '2042-02-10'), (DEFAULT, 'Olympus Mons Spa & Casino', NULL);

CREATE TABLE visitor ( visitor_id SERIAL PRIMARY KEY, host_id INTEGER, first_name CHARACTER VARYING(25), last_name CHARACTER VARYING(25) );

INSERT INTO visitor (visitor_id, host_id, first_name, last_name) VALUES (DEFAULT, 7, 'George', 'Ambrose'), (DEFAULT, 1, 'Kris', 'Cardenas'), (DEFAULT, 9, 'Priscilla', 'Lane'), (DEFAULT, 11, 'Jane', 'Thornton'), (DEFAULT, NULL, 'Doug', 'Stavenger'), (DEFAULT, NULL, 'Jamie', 'Waterman'), (DEFAULT, 8, 'Martin', 'Humphries');

CREATE TABLE inventory ( base_id INTEGER, supply_id INTEGER, quantity INTEGER );

INSERT INTO inventory (base_id, supply_id, quantity) VALUES (1, 1, 8), (1, 3, 5), (1, 5, 1), (1, 6, 2), (1, 8, 12), (1, 9, 1), (2, 4, 5), (2, 8, 62), (2, 10, 37), (3, 2, 11), (3, 7, 2), (4, 10, 91);

CREATE TABLE supply ( supply_id SERIAL PRIMARY KEY, name CHARACTER VARYING(30), description TEXT, quantity INTEGER );

INSERT INTO supply (supply_id, name, description, quantity) VALUES (DEFAULT, 'Solar Panel', 'Standard 1x1 meter cell', 912), (DEFAULT, 'Water Filter', 'This takes things out of your water so it''s drinkable.', 6), (DEFAULT, 'Duct Tape', 'A 10 meeter roll of duct tape for ALL your repairs', 951), (DEFAULT, 'Ketchup', 'It''s ketchup', 206), (DEFAULT, 'Battery Cell', 'Standard 1000 kAH battery cell for power grid (heavy item).', 17), (DEFAULT, 'USB 6.0 Cable', 'Carbon fiber cooated / 10 TBps spool', 42), (DEFAULT, 'Fuzzy Duster', 'It gets dusty around here. Be prepared!', 19), (DEFAULT, 'Mars Bars', 'The ORIGINAL nutirent bar made with the finest bioengineered ingredients.', 3801), (DEFAULT, 'Air Filter', 'Removes 99% of all Martian dust from your ventilation unit', 23), (DEFAULT, 'Famous Ray''s Frozen Pizza', 'This Martian favourite is covered in all your favourite toppings. 1 flavour only.', 823);

[smartdataworks]

CREATE TABLE martian_confidential ( martian_id SERIAL PRIMARY KEY, first_name CHARACTER VARYING(25), last_name CHARACTER VARYING(25), base_id INTEGER, super_id INTEGER, salary INTEGER, dna_id CHARACTER VARYING(30) );

INSERT INTO martian_confidential (martian_id, first_name, last_name, base_id, super_id, salary, dna_id) VALUES (DEFAULT, 'Ray', 'Bradbury', 1, NULL, 155900, 'gctaggaatgtagaatctcctgttg'), (DEFAULT, 'John', 'Black', 4, 10, 120100, 'cagttaatggttgaagctggggatt'), (DEFAULT, 'Samuel', 'Hinkston', 4, 2, 110000, 'cgaagcgctagatgctgtgttgtag'), (DEFAULT, 'Jeff', 'Spender', 1, 9, 10000, 'gactaatgtcttcgtggattgcaga'), (DEFAULT, 'Sam', 'Parkhill', 2, 12, 125000, 'gttactttgcgaaagccgtggctac'), (DEFAULT, 'Elma', 'Parkhill', 3, 8, 137000, 'gcaggaatggaagcaactgccatat'), (DEFAULT, 'Melissa', 'Lewis', 1, 1, 145250, 'cttcgatcgtcaatggagttccggac'), (DEFAULT, 'Mark', 'Watney', 3, NULL, 121100, 'gacacgaggcgaactatgtcgcggc'), (DEFAULT, 'Beth', 'Johanssen', 1, 1, 130000, 'cttagactaggtgtgaaacccgtta'), (DEFAULT, 'Chris', 'Beck', 4, NULL, 125000, 'gggggggttacgacgaggaatccat'), (DEFAULT, 'Nathaniel', 'York', 4, 2, 105000, 'ggtctccctgggcgggatattggatg'), (DEFAULT, 'Elon', 'Musk', 2, NULL, 155800, 'atctgcttggatcaatagcgctgcg'), (DEFAULT, 'John', 'Carter', NULL, 8, 129500, 'ccaatcgtgcgagtcgcgatagtct');

[Pythonidaer]

INSERT INTO base (base_id, base_name, founded) VALUES (DEFAULT, 'Tharsisland', '2037-06-03'), (DEFAULT, 'Valles Marineris 2.0', '2040-12-01'), (DEFAULT, 'Gale Cratertown', '2014-08-15'), (DEFAULT, 'New New New York', '2042-02-10'), (DEFAULT, 'Olympus Mons Spa & Casino', NULL);

Not sure if this will matter when users follow along with the video, but in the video, Gale Cratertown is 2041-08-15, not 2014-08-16

[francesco1119]

SERIAL

Replace SERIAL with INT

[BustACode]

For MySQL 19.4.13-MariaDB

CREATE DATABASE mars_db CHARACTER SET utf8 COLLATE utf8_general_ci; USE mars_db;

CREATE TABLE martian( martian_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(25), last_name VARCHAR(25), base_id INT, super_id INT);

INSERT INTO martian VALUES (NULL, 'Ray', 'Bradbury', 1, NULL), (NULL, 'John', 'Black', 4, 10), (NULL, 'Samuel', 'Hinkston', 4, 2), (NULL, 'Jeff', 'Spender', 1, 9), (NULL, 'Sam', 'Parkhill', 2, 12), (NULL, 'Elma', 'Parkhill', 3, 8), (NULL, 'Melissa', 'Lewis', 1, 1), (NULL, 'Mark', 'Watney', 3, NULL), (NULL, 'Beth', 'Johanssen', 1, 1), (NULL, 'Chris', 'Beck', 4, NULL), (NULL, 'Nathaniel', 'York', 4, 2), (NULL, 'Elon', 'Musk', 2, NULL), (NULL, 'John', 'Carter', NULL, 8);

CREATE TABLE base( base_id INT AUTO_INCREMENT PRIMARY KEY, base_name VARCHAR(30), founded DATE);

INSERT INTO base (base_id, base_name, founded) VALUES (NULL, 'Tharsisland', '2037-06-03'), (NULL, 'Valles Marineris 2.0', '2040-12-01'), (NULL, 'Gale Cratertown', '2041-08-16'), (NULL, 'New New New York', '2042-02-10'), (NULL, 'Olympus Mons Spa & Casino', NULL);

CREATE TABLE visitor ( visitor_id INT AUTO_INCREMENT PRIMARY KEY, host_id INT, first_name VARCHAR(25), last_name VARCHAR(25) );

INSERT INTO visitor (visitor_id, host_id, first_name, last_name) VALUES (NULL, 7, 'George', 'Ambrose'), (NULL, 1, 'Kris', 'Cardenas'), (NULL, 9, 'Priscilla', 'Lane'), (NULL, 11, 'Jane', 'Thornton'), (NULL, NULL, 'Doug', 'Stavenger'), (NULL, NULL, 'Jamie', 'Waterman'), (NULL, 8, 'Martin', 'Humphries');

CREATE TABLE inventory ( base_id INT, supply_id INT, quantity INT );

INSERT INTO inventory (base_id, supply_id, quantity) VALUES (1, 1, 8), (1, 3, 5), (1, 5, 1), (1, 6, 2), (1, 8, 12), (1, 9, 1), (2, 4, 5), (2, 8, 62), (2, 10, 37), (3, 2, 11), (3, 7, 2), (4, 10, 91);

CREATE TABLE supply ( supply_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), description TEXT, quantity INT );

INSERT INTO supply (supply_id, name, description, quantity) VALUES (NULL, 'Solar Panel', 'Standard 1x1 meter cell', 912), (NULL, 'Water Filter', 'This takes things out of your water so it''s drinkable.', 6), (NULL, 'Duct Tape', 'A 10 meeter roll of duct tape for ALL your repairs', 951), (NULL, 'Ketchup', 'It''s ketchup', 206), (NULL, 'Battery Cell', 'Standard 1000 kAH battery cell for power grid (heavy item).', 17), (NULL, 'USB 6.0 Cable', 'Carbon fiber cooated / 10 TBps spool', 42), (NULL, 'Fuzzy Duster', 'It gets dusty around here. Be prepared!', 19), (NULL, 'Mars Bars', 'The ORIGINAL nutirent bar made with the finest bioengineered ingredients.', 3801), (NULL, 'Air Filter', 'Removes 99% of all Martian dust from your ventilation unit', 23), (NULL, 'Famous Ray''s Frozen Pizza', 'This Martian favourite is covered in all your favourite toppings. 1 flavour only.', 823);

CREATE TABLE martian_confidential ( martian_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(25), last_name VARCHAR(25), base_id INT, super_id INT, salary INT, dna_id VARCHAR(30) );

INSERT INTO martian_confidential (martian_id, first_name, last_name, base_id, super_id, salary, dna_id) VALUES (NULL, 'Ray', 'Bradbury', 1, NULL, 155900, 'gctaggaatgtagaatctcctgttg'), (NULL, 'John', 'Black', 4, 10, 120100, 'cagttaatggttgaagctggggatt'), (NULL, 'Samuel', 'Hinkston', 4, 2, 110000, 'cgaagcgctagatgctgtgttgtag'), (NULL, 'Jeff', 'Spender', 1, 9, 10000, 'gactaatgtcttcgtggattgcaga'), (NULL, 'Sam', 'Parkhill', 2, 12, 125000, 'gttactttgcgaaagccgtggctac'), (NULL, 'Elma', 'Parkhill', 3, 8, 137000, 'gcaggaatggaagcaactgccatat'), (NULL, 'Melissa', 'Lewis', 1, 1, 145250, 'cttcgatcgtcaatggagttccggac'), (NULL, 'Mark', 'Watney', 3, NULL, 121100, 'gacacgaggcgaactatgtcgcggc'), (NULL, 'Beth', 'Johanssen', 1, 1, 130000, 'cttagactaggtgtgaaacccgtta'), (NULL, 'Chris', 'Beck', 4, NULL, 125000, 'gggggggttacgacgaggaatccat'), (NULL, 'Nathaniel', 'York', 4, 2, 105000, 'ggtctccctgggcgggatattggatg'), (NULL, 'Elon', 'Musk', 2, NULL, 155800, 'atctgcttggatcaatagcgctgcg'), (NULL, 'John', 'Carter', NULL, 8, 129500, 'ccaatcgtgcgagtcgcgatagtct');

[MartinFalatic]

(Note: I corrected the data to match the exact lines in the original video, punctuation and all.)

For Postgres 10+ (favoring INT GENERATED BY DEFAULT AS IDENTITY over SERIAL for the primary keys):

• First, create the DB (e.g., createdb -O postgres -E UTF-8 mars_db)

• Then put the following into a file such as martian.sql and import it (e.g., psql mars_db < martian.sql):

CREATE TABLE martian (
    martian_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    base_id INT,
    super_id INT
);
INSERT INTO martian
    (martian_id, first_name, last_name, base_id, super_id)
VALUES
    (DEFAULT, 'Ray', 'Bradbury', 1, NULL),
    (DEFAULT, 'John', 'Black', 4, 10),
    (DEFAULT, 'Samuel', 'Hinkston', 4, 2),
    (DEFAULT, 'Jeff', 'Spender', 1, 9),
    (DEFAULT, 'Sam', 'Parkhill', 2, 12),
    (DEFAULT, 'Elma', 'Parkhill', 3, 8),
    (DEFAULT, 'Melissa', 'Lewis', 1, 1),
    (DEFAULT, 'Mark', 'Watney', 3, NULL),
    (DEFAULT, 'Beth', 'Johanssen', 1, 1),
    (DEFAULT, 'Chris', 'Beck', 4, NULL),
    (DEFAULT, 'Nathaniel', 'York', 4, 2),
    (DEFAULT, 'Elon', 'Musk', 2, NULL),
    (DEFAULT, 'John', 'Carter', NULL, 8);

CREATE TABLE base (
    base_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    base_name VARCHAR(30),
    founded DATE
);
INSERT INTO base
    (base_id, base_name, founded)
VALUES
    (DEFAULT, 'Tharsisland', '2037-06-03'),
    (DEFAULT, 'Valles Marineris 2.0', '2040-12-01'),
    (DEFAULT, 'Gale Cratertown', '2041-08-15'),
    (DEFAULT, 'New New New York', '2042-02-10'),
    (DEFAULT, 'Olympus Mons Spa & Casino', NULL);

CREATE TABLE visitor (
    visitor_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    host_id INT,
    first_name VARCHAR(25),
    last_name VARCHAR(25)
);
INSERT INTO visitor
    (visitor_id, host_id, first_name, last_name)
VALUES
    (DEFAULT, 7, 'George', 'Ambrose'),
    (DEFAULT, 1, 'Kris', 'Cardenas'),
    (DEFAULT, 9, 'Priscilla', 'Lane'),
    (DEFAULT, 11, 'Jane', 'Thornton'),
    (DEFAULT, NULL, 'Doug', 'Stavenger'),
    (DEFAULT, NULL, 'Jamie', 'Waterman'),
    (DEFAULT, 8, 'Martin', 'Humphries');

CREATE TABLE inventory (
    base_id INT,
    supply_id INT,
    quantity INT
);
INSERT INTO inventory
    (base_id, supply_id, quantity)
VALUES
    (1, 1, 8),
    (1, 3, 5),
    (1, 5, 1),
    (1, 6, 2),
    (1, 8, 12),
    (1, 9, 1),
    (2, 4, 5),
    (2, 8, 62),
    (2, 10, 37),
    (3, 2, 11),
    (3, 7, 2),
    (4, 10, 91);

CREATE TABLE supply (
    supply_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(30),
    description TEXT,
    quantity INT
);
INSERT INTO supply
    (supply_id, name, description, quantity)
VALUES
    (DEFAULT, 'Solar Panel', 'Standard 1x1 meter cell', 912),
    (DEFAULT, 'Water Filter', 'This takes things out of your water so it''s drinkable.', 6),
    (DEFAULT, 'Duct Tape', 'A 10 meter roll of duct tape for ALL your repairs.', 951),
    (DEFAULT, 'Ketchup', 'It''s ketchup...', 206),
    (DEFAULT, 'Battery Cell', 'Standard 1000 kAh battery cell for power grid (heavy item).', 17),
    (DEFAULT, 'USB 6.0 Cable', 'Carbon fiber coated / 15 TBps spool', 42),
    (DEFAULT, 'Fuzzy Duster', 'It gets dusty around here. Be prepared!', 19),
    (DEFAULT, 'Mars Bars', 'The ORIGINAL nutirent bar made with the finest bioengineered ingredients.', 3801),
    (DEFAULT, 'Air Filter', 'Removes 99% of all Martian dust from your ventilation unit.', 23),
    (DEFAULT, 'Famous Ray''s Frozen Pizza', 'This Martian favorite is covered in all your favorite toppings. 1 flavor only.', 823);

CREATE TABLE martian_confidential (
    martian_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    base_id INT,
    super_id INT,
    salary INT,
    dna_id VARCHAR(30)
);
INSERT INTO martian_confidential
    (martian_id, first_name, last_name, base_id, super_id, salary, dna_id)
VALUES
    (DEFAULT, 'Ray', 'Bradbury', 1, NULL, 155900, 'gctaggaatgtagaatctcctgttg'),
    (DEFAULT, 'John', 'Black', 4, 10, 120100, 'cagttaatggttgaagctggggatt'),
    (DEFAULT, 'Samuel', 'Hinkston', 4, 2, 110000, 'cgaagcgctagatgctgtgttgtag'),
    (DEFAULT, 'Jeff', 'Spender', 1, 9, 10000, 'gactaatgtcttcgtggattgcaga'),
    (DEFAULT, 'Sam', 'Parkhill', 2, 12, 125000, 'gttactttgcgaaagccgtggctac'),
    (DEFAULT, 'Elma', 'Parkhill', 3, 8, 137000, 'gcaggaatggaagcaactgccatat'),
    (DEFAULT, 'Melissa', 'Lewis', 1, 1, 145250, 'cttcgatcgtcaatggagttccggac'),
    (DEFAULT, 'Mark', 'Watney', 3, NULL, 121100, 'gacacgaggcgaactatgtcgcggc'),
    (DEFAULT, 'Beth', 'Johanssen', 1, 1, 130000, 'cttagactaggtgtgaaacccgtta'),
    (DEFAULT, 'Chris', 'Beck', 4, NULL, 125000, 'gggggggttacgacgaggaatccat'),
    (DEFAULT, 'Nathaniel', 'York', 4, 2, 105000, 'ggtctccctgggcgggatattggatg'),
    (DEFAULT, 'Elon', 'Musk', 2, NULL, 155800, 'atctgcttggatcaatagcgctgcg'),
    (DEFAULT, 'John', 'Carter', NULL, 8, 129500, 'ccaatcgtgcgagtcgcgatagtct');

[abascunana]

for oracle databases

CREATE SEQUENCE martian_id MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE SEQUENCE base_id MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE SEQUENCE visitor_id MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE SEQUENCE supply_id MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE SEQUENCE martian_id1 MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE TABLE base( base_id number(10) NOT NULL, base_name VARCHAR2(50) NOT NULL, founded DATE , PRIMARY KEY(base_id) ); INSERT INTO base(base_id,base_name,founded) Values(base_id.nextval, 'Tharsisland',TO_DATE('2037-06-03','yyyy/mm/dd') ); INSERT INTO base(base_id,base_name,founded) Values(base_id.nextval,'Valles Marineris 2.0', TO_DATE('2040-12-01','yyyy/mm/dd')); INSERT INTO base(base_id,base_name,founded) Values(base_id.nextval, 'Gale Cratertown',TO_DATE('2014-08-15','yyyy/mm/dd') ); INSERT INTO base(base_id,base_name,founded) Values(base_id.nextval, 'New New New York',TO_DATE('2042-02-10' ,'yyyy/mm/dd') ); INSERT INTO base(base_id,base_name,founded) Values(base_id.nextval, 'Olympus Mons Spa & Casino', NULL);

CREATE TABLE visitor( visitor_id number(10) not null, host_id number(10), first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, PRIMARY KEY(visitor_id) ); INSERT INTO visitor(visitor_id,host_id,first_name,last_name) Values(visitor_id.nextval,7, 'George', 'Ambrose'); INSERT INTO visitor(visitor_id,host_id,first_name,last_name) Values(visitor_id.nextval,1, 'Kris', 'Cardenas'); INSERT INTO visitor(visitor_id,host_id,first_name,last_name) Values(visitor_id.nextval,9, 'Priscilla', 'Lane'); INSERT INTO visitor(visitor_id,host_id,first_name,last_name) Values(visitor_id.nextval,11, 'Jane', 'Thornton'); INSERT INTO visitor(visitor_id,host_id,first_name,last_name) Values(visitor_id.nextval,NULL, 'Doug', 'Stavenger'); INSERT INTO visitor(visitor_id,host_id,first_name,last_name) Values(visitor_id.nextval,NULL, 'Jamie', 'Waterman'); INSERT INTO visitor(visitor_id,host_id,first_name,last_name) Values(visitor_id.nextval,8, 'Martin', 'Humphries');

CREATE TABLE inventory( base_id number(10) not null, supply_id number(10), quantity_id number(10)

); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(1, 1, 8); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(1, 3, 5); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(1, 5, 1); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(1, 6, 2); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(1, 8, 12); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(1, 9, 1); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(2, 4, 5); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(2, 8, 62); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(2, 10, 37); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(2, 8, 62); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(3, 2, 11); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(3, 7, 2); INSERT INTO inventory(base_id,supply_id,quantity_id) Values(4, 10, 91);

CREATE TABLE supply( supply_id number(10) not null, name VARCHAR2(50) NOT NULL, description VARCHAR2(100) NOT NULL, quantity number(10) not null, PRIMARY KEY(supply_id) ); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'Solar Panel', 'Standard 1x1 meter cell', 912); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval, 'Water Filter', 'This takes things out of your water so it''s drinkable.', 6); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'Duct Tape', 'A 10 meeter roll of duct tape for ALL your repairs', 951); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'Ketchup', 'It''s ketchup', 206); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'Battery Cell', 'Standard 1000 kAH battery cell for power grid (heavy item).', 17); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'USB 6.0 Cable', 'Carbon fiber cooated / 10 TBps spool', 42); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'Fuzzy Duster', 'It gets dusty around here. Be prepared!', 19); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'Mars Bars', 'The ORIGINAL nutirent bar made with the finest bioengineered ingredients.', 3801);

INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'Air Filter', 'Removes 99% of all Martian dust from your ventilation unit', 23); INSERT INTO supply(supply_id,name,description,quantity) Values(supply_id.nextval,'Famous Ray''s Frozen Pizza', 'This Martian favourite is covered in all your favourite toppings. 1 flavour only.', 823);

CREATE TABLE martian( martian_id number(10) not null, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, base_id number(10) , super_id number(10), PRIMARY KEY(martian_id) ); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Ray', 'Bradbury', 1, NULL); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'John', 'Black', 4, 10); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Samuel', 'Hinkston', 4, 2) ; INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Jeff', 'Spender', 1, 9); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Sam', 'Parkhill', 2, 12); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Elma', 'Parkhill', 3, 8); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Melissa', 'Lewis', 1, 1); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Mark', 'Watney', 3, NULL); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Beth', 'Johanssen', 1, 1); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values( martian_id.nextval,'Chris', 'Beck', 4, NULL); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Nathaniel', 'York', 4, 2); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'Elon', 'Musk', 2, NULL); INSERT INTO martian(martian_id,first_name,last_name,base_id,super_id) Values(martian_id.nextval, 'John', 'Carter', NULL, 8);

CREATE TABLE martian_confidential ( martian_id1 number(10) not null, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, base_id number(10), super_id number(10), salary number(10) not null, dna_id VARCHAR2(50), PRIMARY KEY(martian_id1) );

INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Ray', 'Bradbury', 1, NULL, 155900, 'gctaggaatgtagaatctcctgttg'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'John', 'Black', 4, 10, 120100, 'cagttaatggttgaagctggggatt'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Samuel', 'Hinkston', 4, 2, 110000, 'cgaagcgctagatgctgtgttgtag'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Jeff', 'Spender', 1, 9, 10000, 'gactaatgtcttcgtggattgcaga'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Sam', 'Parkhill', 2, 12, 125000, 'gttactttgcgaaagccgtggctac'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Melissa', 'Lewis', 1, 1, 145250, 'cttcgatcgtcaatggagttccggac'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Mark', 'Watney', 3, NULL, 121100, 'gacacgaggcgaactatgtcgcggc'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Beth', 'Johanssen', 1, 1, 130000, 'cttagactaggtgtgaaacccgtta'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Chris', 'Beck', 4, NULL, 125000, 'gggggggttacgacgaggaatccat'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Nathaniel', 'York', 4, 2, 105000, 'ggtctccctgggcgggatattggatg'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'Elon', 'Musk', 2, NULL, 155800, 'atctgcttggatcaatagcgctgcg'); INSERT INTO martian_confidential(martian_id1,first_name,last_name,base_id,super_id,salary,dna_id) Values(martian_id1.nextval, 'John', 'Carter', NULL, 8, 129500, 'ccaatcgtgcgagtcgcgatagtct');

[Essien-Anko]

Hello please where is the python code that was used in generating the names in the indexes video? I hope you reply to this. thank you

[smartdataworks]

Hi Essien, It is some time ago that I wrote that and as far as I remember I just did it by hand. No python was harmed in the making of this code.

[Essien-Anko]

Hello,Please is It possible to at least create a query for us to download the randomized code?Thank you   Sent from Mail for Windows From: Emanuel BleiSent: Wednesday, April 12, 2023 11:16 PMTo: socratica/sqlCc: Essien-Anko; CommentSubject: Re: [socratica/sql] Create SQL files for videos (#2) Hi Essien,It is some time ago that I wrote that and as far as I remember I just did it by hand. No python was harmed in the making of this code.—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: ***@***.***> Virus-free.www.avg.com

[smartdataworks]

Hi Essien, I am not really sure what you mean. I am not part of Socratica and only suggested some way to create the tables. It seems the owner is not really actively maintaining this repository and hasn't accepted any PRs. I also don't understand when you are talking about randomised code. I don't see what is random about the code but I might be missing something, Kind regards, Emanuel

[OssBozier]

My quick & dirty implementation, but it works for a smaller number of records: I downloaded the ODBC connector for my DB (https://mariadb.com/downloads/connectors/connectors-data-access/odbc-connector/) I was using MariaDB. Then used that to set up a DSN, connected to my DB. I took the three csv files and copied imported them into Excel as sheets. Then got ChatGPT to knock up some VBA code to create random names. It has hard coded connection string in the VBA code. I could have brought that out into the run code sheet but didn't! It can lose focus and never give the final dialog until you click on the sheet too.

GernerateNames.zip

[Kunleiky]

I noticed foreign keys are missing in the tables. Can someone please update that?

[3bars]

Did anyone have issues with importing the earthquake csv into PostgresSQL 16? I'm tripping over the first record because of the double quotes and commas in-between the quotes. 1,1969-01-01 9:07:06,51.096,-179.392,45,5.6,mw,iscgem812771,"Andreanof Islands, Aleutian Islands, Alaska",earthquake

The error I get is: ERROR: extra data after last expected column CONTEXT: COPY earthquake, line 2: "1,1969-01-01 9:07:06,51.096,-179.392,45,5.6,mw,iscgem812771,Andreanof Islands Aleutian Islands Alask..."

when using \copy from inside a connection to the database

[Kunleiky]

Did anyone have issues with importing the earthquake csv into PostgresSQL 16? I'm tripping over the first record because of the double quotes and commas in-between the quotes. 1,1969-01-01 9:07:06,51.096,-179.392,45,5.6,mw,iscgem812771,"Andreanof Islands, Aleutian Islands, Alaska",earthquake

The error I get is: ERROR: extra data after last expected column CONTEXT: COPY earthquake, line 2: "1,1969-01-01 9:07:06,51.096,-179.392,45,5.6,mw,iscgem812771,Andreanof Islands Aleutian Islands Alask..."

when using \copy from inside a connection to the database

Try to add the below to the \COPY parameter: QUOTE ' " ' It will help escape the " present in some of the values

[3bars]

Thank you for you're response @Kunleiky using that value above results in: ERROR: COPY quote must be a single one-byte character It seems that it does not like the included spaces

[mlh496]

Thank you to everyone who contributed! I've reorganized the files, fixed some typos, and have separate SQL files for Postgres, MS SQL and Oracle.