Mike Droog

CREATE OR REPLACE TABLE sentence_comparison(sentence_1 VARCHAR, sentence_2 VARCHAR);

INSERT INTO sentence_comparison (sentence_1, sentence_2) VALUES
('The cat sat on the', 'The cat sat on thee'),
('Rainbows appear after rain', 'Rainbows appears after rain'),
('She loves chocolate chip cookies', 'She love chocolate chip cookies'),
('Birds fly high in the', 'Birds flies high in the'),
('The sun sets in the', 'The sun set in the'),
('I really like that song', 'I really liked that song'),
('Dogs are truly best friends', 'Dogs are truly best friend'),
('Books are a source of', 'Book are a source of'),
('The moon shines at night', 'The moons shine at night'),
('Walking is good for health', 'Walking is good for the health'),
('Children love to play', 'Children love to play'),
('Music is a universal language', 'Music is a universal language'),
('Winter is coming soon', 'Winter is coming soon'),
('Happiness is a choice', 'Happiness is a choice'),
('Travel broadens the mind', 'Travel broadens the mind'),
('Dogs are our closest companions', 'Cats are solitary creatures'),
('Books are portals to new worlds', 'Movies depict various realities'),
('The moon shines brightly at night', 'The sun blazes hotly at noon'),
('Walking is beneficial for health', 'Running can be hard on knees'),
('Children love to play outside', 'Children love to play'),
('Music transcends cultural boundaries', 'Music is a universal language'),
('Winter is cold and snowy', 'Winter is coming soon'),
('Happiness comes from within', 'Happiness is a choice'),
('Traveling opens up perspectives', 'Travel broadens the mind');

SELECT patent_index.patent_id
    , invention_title
    , patent_type
    , application_date 
    , document_publication_date
FROM cybersyn_us_patent_grants.cybersyn.uspto_contributor_index AS contributor_index
INNER JOIN
    cybersyn_us_patent_grants.cybersyn.uspto_patent_contributor_relationships AS relationships
    ON contributor_index.contributor_id = relationships.contributor_id
INNER JOIN
    cybersyn_us_patent_grants.cybersyn.uspto_patent_index AS patent_index
    ON relationships.patent_id = patent_index.patent_id
WHERE contributor_index.contributor_name ILIKE 'NVIDIA CORPORATION'
    AND relationships.contribution_type = 'Assignee - United States Company Or Corporation'
LIMIT 10

SELECT patent_index.patent_id
    , invention_title
    , patent_type
    , application_date 
    , document_publication_date
FROM cybersyn_us_patent_grants.cybersyn.uspto_contributor_index AS contributor_index
INNER JOIN
    cybersyn_us_patent_grants.cybersyn.uspto_patent_contributor_relationships AS relationships
    ON contributor_index.contributor_id = relationships.contributor_id
INNER JOIN
    cybersyn_us_patent_grants.cybersyn.uspto_patent_index AS patent_index
    ON relationships.patent_id = patent_index.patent_id
WHERE contributor_index.contributor_name ILIKE 'NVIDIA CORPORATION'
    AND relationships.contribution_type = 'Assignee - United States Company Or Corporation'
LIMIT 10

--Schema creation
create or replace schema week_63;

--T1 creation
CREATE TABLE t1 (value CHAR(1));

INSERT INTO t1
SELECT 'a' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS temp1

UNION ALL

SELECT 'b' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) AS temp2

UNION ALL

SELECT 'c' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30) AS temp3

UNION ALL

SELECT 'd' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) AS temp4

;

-- Create table t2 and insert one 'b' value
CREATE TABLE t2 (value CHAR(1));
INSERT INTO t2 (value) VALUES ('b');

-- Create table t3 and insert ten 'c' values
CREATE TABLE t3 (value CHAR(1));
INSERT INTO t3
SELECT 'c' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS temp;

-- Create table t4 and insert one 'd' value
CREATE TABLE t4 (value CHAR(1));
INSERT INTO t4 (value) VALUES ('d');

SELECT *
FROM t1
    LEFT JOIN t2 ON t1.value = t2.value
    LEFT JOIN t3 ON t1.value = t3.value
    LEFT JOIN t4 ON t1.value = t4.value;

CREATE OR REPLACE TABLE measurements (
height INT,
weight INT,
age INT,
gender VARCHAR(),
id INT
);
insert into measurements (height, weight, age, gender, id) values (41, 178, 74, 'other', 1);
insert into measurements (height, weight, age, gender, id) values (188, 145, 87, 'other', 2);
insert into measurements (height, weight, age, gender, id) values (215, 725, 30, 'male', 3);
insert into measurements (height, weight, age, gender, id) values (159, 48, 116, 'female', 4);
insert into measurements (height, weight, age, gender, id) values (243, 204, 6, 'other', 5);
insert into measurements (height, weight, age, gender, id) values (232, 306, 30, 'male', 6);
insert into measurements (height, weight, age, gender, id) values (261, 602, 62, 'other', 7);
insert into measurements (height, weight, age, gender, id) values (143, 829, 113, 'female', 8);
insert into measurements (height, weight, age, gender, id) values (62, 190, 86, 'male', 9);
insert into measurements (height, weight, age, gender, id) values (249, 15, 73, 'male', 10);

-- database and schema creation
CREATE DATABASE F_F_WEEK_54;
CREATE SCHEMA WEEK_54;

--table creation
CREATE TABLE table_a (
id INT,
name VARCHAR,
age INT
);

CREATE TABLE table_b (
id INT,
name VARCHAR,
age INT
);

--data creation
INSERT INTO table_a (id, name, age)
VALUES
(1, 'John', 25),
(2, 'Mary', 30),
(3, 'David', 28),
(4, 'Sarah', 35),
(5, 'Michael', 32),
(6, 'Emily', 27),
(7, 'Daniel', 29),
(8, 'Olivia', 31),
(9, 'Matthew', 26),
(10, 'Sophia', 33),
(11, 'Jacob', 24),
(12, 'Emma', 29),
(13, 'Joshua', 32),
(14, 'Ava', 30),
(15, 'Andrew', 28),
(16, 'Isabella', 34),
(17, 'James', 27),
(18, 'Mia', 31),
(19, 'Logan', 25),
(20, 'Charlotte', 29);

--role creation
CREATE ROLE week_54_role;
GRANT ROLE week_54_role to user <user_name> ;
GRANT USAGE ON database F_F_WEEK_54 TO ROLE week_54_role;
GRANT USAGE ON schema WEEK_54 TO ROLE week_54_role;
GRANT SELECT ON ALL TABLES IN SCHEMA F_F_WEEK_54.WEEK_54 TO ROLE week_54_role;
GRANT INSERT ON ALL TABLES IN SCHEMA F_F_WEEK_54.WEEK_54 TO ROLE week_54_role;
GRANT USAGE ON WAREHOUSE TO ROLE week_54_role;

CREATE OR REPLACE copy_to_table(fromTable STRING, toTable STRING, count INT) RETURNS STRING LANGUAGE PYTHON RUNTIME_VERSION = '3.8' PACKAGES = ('snowflake-snowpark-python') HANDLER = 'copyBetweenTables' AS $$ def copyBetweenTables(snowpark_session, fromTable, toTable, count): snowpark_session.table(fromTable).limit(count).write.mode("append").save_as_table(toTable) return "Success" $$ ;

CREATE OR REPLACE PROCEDURE check_and_generate_data(tableName STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
try {
var tableName = TABLENAME;

var command1 = `SELECT count(*) as count FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = '${tableName.toUpperCase()}'`;
var statement1 = snowflake.createStatement({sqlText: command1});
var result_set1 = statement1.execute();

result_set1.next();
var count = result_set1.getColumnValue('COUNT');

if (count == 0) {
    var command2 = `CREATE TABLE ${tableName} (payload VARIANT, ingested_at TIMESTAMP_NTZ default CURRENT_TIMESTAMP())`;
    var statement2 = snowflake.createStatement({sqlText: command2});
    statement2.execute();

    return `Table ${tableName} has been created.`;
} else {
    for(var i=0; i<40; i++) {
        var jsonObject = {
            "id": i,
            "name": "Name_" + i,
            "address": "Address_" + i,
            "email": "email_" + i + "@example.com",
            "transactionValue": Math.floor(Math.random() * 10000) + 1
        };
        var jsonString = JSON.stringify(jsonObject);

        var command3 = `INSERT INTO ${tableName} (payload) SELECT PARSE_JSON(column1) FROM VALUES ('${jsonString}')`;
        var statement3 = snowflake.createStatement({sqlText: command3});
        statement3.execute();
    }

    return `40 records have been inserted into the ${tableName} table.`;
}

} catch (err) {
return "Failed: " + err;
}
$$;