Duckdb Sample - 01

Launch Duckdb

duckdb

.open newdbname.duckdb

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR NOT NULL,
    email VARCHAR UNIQUE,
    department VARCHAR,
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

INSERT INTO employees (id,name, email, department, salary) VALUES
(1,'Rachel Green', 'rachel.green@friends.com', 'Fashion', 55000.00);

INSERT INTO employees (id,name, email, department, salary) VALUES
(2,'Monica Geller', 'monica.geller@friends.com', 'Culinary', 62000.50);

INSERT INTO employees (id,name, email, department, salary) VALUES
(3,'Phoebe Buffay', 'phoebe.buffay@friends.com', 'Massage Therapy', 48000.00);

INSERT INTO employees (id,name, email, department, salary) VALUES
(4,'Joey Tribbiani', 'joey.tribbiani@friends.com', 'Acting', 40000.75);

INSERT INTO employees (id,name, email, department, salary) VALUES
(5,'Chandler Bing', 'chandler.bing@friends.com', 'Data Analysis', 70000.25);

INSERT INTO employees (id,name, email, department, salary) VALUES
(6,'Ross Geller', 'ross.geller@friends.com', 'Paleontology', 65000.00);

select * from employees

.databases or show databases;
.tables or show tables;
.mode list
.mode table
.mode csv
summarize employees;
summarize select name,email from employees;
.exit


CREATE SEQUENCE dept_id_seq;

drop table if exists departments;

CREATE TABLE departments (
    id INTEGER PRIMARY KEY DEFAULT nextval('dept_id_seq'),
    dept_name VARCHAR NOT NULL
);

insert into departments(dept_name) values('Fashion');
insert into departments(dept_name) values('Culinary');
insert into departments(dept_name) values('Massage');
insert into departments(dept_name) values('Acting');
insert into departments(dept_name) values('Data Analysis');
insert into departments(dept_name) values('Paleontology');


CREATE SEQUENCE emp_id_seq;

DROP TABLE if exists employees;

CREATE TABLE if not exists  employees (
    id INTEGER PRIMARY KEY DEFAULT nextval('emp_id_seq'),
    name VARCHAR NOT NULL,
    email VARCHAR UNIQUE,
    department VARCHAR,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    hire_date DATE DEFAULT CURRENT_DATE,
);


INSERT INTO employees (name, email, department, salary) VALUES
('Rachel Green', 'rachel.green@friends.com', 'Fashion', 55000.00);

INSERT INTO employees (name, email, department, salary) VALUES
('Monica Geller', 'monica.geller@friends.com', 'Culinary', 62000.50);

INSERT INTO employees (name, email, department, salary) VALUES
('Phoebe Buffay', 'phoebe.buffay@friends.com', 'Massage Therapy', 48000.00);

INSERT INTO employees (name, email, department, salary) VALUES
('Joey Tribbiani', 'joey.tribbiani@friends.com', 'Acting', 40000.75);

INSERT INTO employees (name, email, department, salary) VALUES
('Chandler Bing', 'chandler.bing@friends.com', 'Data Analysis', 70000.25);

INSERT INTO employees (name, email, department, salary) VALUES
('Ross Geller', 'ross.geller@friends.com', 'Paleontology', 65000.00);


INSERT INTO employees (id,name, email, department, salary) VALUES
(8,'Ben Geller', 'ben.geller@friends.com', 'Student', 1.00);

INSERT INTO employees (name, email, department, salary) VALUES
('Emma Green', 'emma.green@friends.com', 'Kid', 1.00);


CREATE SEQUENCE emp_dept_id_seq;

CREATE TABLE emp_dept (
	id INTEGER PRIMARY KEY DEFAULT nextval('emp_dept_id_seq'),
    dept_id INTEGER NOT NULL,
    emp_id INTEGER NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(id),
    FOREIGN KEY (emp_id) REFERENCES employees(id)
);

INSERT INTO emp_dept(emp_id,dept_id) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

INSERT INTO emp_dept(emp_id,dept_id) VALUES (9,7);


CREATE SEQUENCE product_id_seq;

CREATE TABLE products (
    id INTEGER PRIMARY KEY DEFAULT nextval('product_id_seq'),
    name VARCHAR NOT NULL,
    price DECIMAL(10, 2),
    discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * 0.9) VIRTUAL
);


CREATE SEQUENCE orders_id_seq;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY DEFAULT nextval('orders_id_seq'),
    customer_name VARCHAR,
    items INTEGER[],
    shipping_address STRUCT(
        street VARCHAR,
        city VARCHAR,
        zip VARCHAR
    )
);


INSERT INTO orders (customer_name, items, shipping_address) VALUES
('John Doe', [1, 2, 3], {'street': '123 Elm St', 'city': 'Springfield', 'zip': '11111'}),
('Jane Smith', [3, 4, 5], {'street': '456 Oak St', 'city': 'Greenville', 'zip': '22222'}),
('Emily Johnson', [6, 7, 8, 9], {'street': '789 Pine St', 'city': 'Fairview', 'zip': '33333'});

Query Orders


select * from orders;

select id,customer_name,items,shipping_address.city from orders;

select id,customer_name,items,shipping_address['city'] from orders;

select id,customer_name,shipping_address.* from orders;

-- Array

select id,customer_name,len(items) from orders;

select id,customer_name,list_contains(items,3) from orders;

select id,customer_name,list_distinct(items) from orders;

select id,customer_name,unnest(items),shipping_address.* from orders;

More Functions

https://duckdb.org/docs/sql/functions/list

select current_catalog(), current_schema();

-- Returns the name of the currently active catalog. Default is memory. -- Returns the name of the currently active schema. Default is main.

Advance Data Warehousing

Duckdb Sample - 01

Launch Duckdb