Skip to content

Instantly share code, notes, and snippets.

@AlexRogalskiy

AlexRogalskiy/clickhouse.sql

Last active April 24, 2024 07:38
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save AlexRogalskiy/55be3bfcae3b2138f85188826f1fbeae to your computer and use it in GitHub Desktop.
Save AlexRogalskiy/55be3bfcae3b2138f85188826f1fbeae to your computer and use it in GitHub Desktop.
Download ZIP
db-refcard
Raw
clickhouse.sql
--==========================================================================================
CREATE TABLE IF NOT EXISTS session_keys
(
key UInt32,
value String TTL INTERVAL 5 SECOND,
start_time DateTime
)
ENGINE = EmbeddedRocksDB
PRIMARY KEY key;
INSERT INTO session_keys
SELECT
toUInt32(rand() % 100) as key,
toString(rand()) as value,
toDateTime(now()) + (rand() % (60*60*24)) as start_time
FROM numbers_mt(10);
SELECT 'Выданные ключи (session_keys)';
SELECT * FROM session_keys LIMIT 5;
CREATE TABLE IF NOT EXISTS user_accounts
(
u_id UInt32,
login String
)
ENGINE = Log;
INSERT INTO user_accounts
SELECT
toUInt32(rand() % 100) as u_id, -- Используем number чтобы гарантировать повторение u_id
concat(randomPrintableASCII(2), '@', randomPrintableASCII(2), '.com') as login
FROM numbers_mt(10);
SELECT '';
SELECT 'Логины пользователей (user_accounts)';
SELECT * FROM user_accounts LIMIT 5;
SET join_algorithm = 'direct';
SELECT '';
SELECT 'Ключи по пользователям (результаты соединения)';
SELECT session_keys.value, user_accounts.login, session_keys.start_time
FROM user_accounts
JOIN session_keys ON session_keys.key = user_accounts.u_id
ORDER BY start_time DESC;
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
Raw
data.sql
INSERT INTO department(id, NAME) VALUES
(1, 'department-1'),
(2, 'department-2'),
(3, 'department-3'),
(4, 'department-4');
INSERT INTO employee(ID, NAME, salary, department_id) VALUES
(1, 'employee-1', 100, 1),
(2, 'employee-2', 200, 2),
(3, 'employee-3', 300, 3),
(4, 'employee-1', 400, 1),
(5, 'employee-2', 400, 2),
(6, 'employee-3', 500, 3);
// additional tables
insert into organization (id, name) values (1, 'Test1');
insert into organization (id, name) values (2, 'Test2');
insert into organization (id, name) values (3, 'Test3');
insert into organization (id, name) values (4, 'Test4');
insert into organization (id, name) values (5, 'Test5');
insert into department (id, name, organization_id) values (1, 'Test1', 1);
insert into department (id, name, organization_id) values (2, 'Test2', 1);
insert into department (id, name, organization_id) values (3, 'Test3', 1);
insert into department (id, name, organization_id) values (4, 'Test4', 2);
insert into department (id, name, organization_id) values (5, 'Test5', 2);
insert into department (id, name, organization_id) values (6, 'Test6', 3);
insert into department (id, name, organization_id) values (7, 'Test7', 4);
insert into department (id, name, organization_id) values (8, 'Test8', 5);
insert into department (id, name, organization_id) values (9, 'Test9', 5);
insert into employee (id, first_name, last_name, position, salary, age, department_id, organization_id) values (1, 'John', 'Smith', 'Developer', 10000, 30, 1, 1);
insert into employee (id, first_name, last_name, position, salary, age, department_id, organization_id) values (2, 'Adam', 'Hamilton', 'Developer', 12000, 35, 1, 1);
insert into employee (id, first_name, last_name, position, salary, age, department_id, organization_id) values (3, 'Tracy', 'Smith', 'Architect', 15000, 40, 1, 1);
insert into employee (id, first_name, last_name, position, salary, age, department_id, organization_id) values (4, 'Lucy', 'Kim', 'Developer', 13000, 25, 2, 1);
insert into employee (id, first_name, last_name, position, salary, age, department_id, organization_id) values (5, 'Peter', 'Wright', 'Director', 50000, 50, 4, 2);
insert into employee (id, first_name, last_name, position, salary, age, department_id, organization_id) values (6, 'Alan', 'Murray', 'Developer', 20000, 37, 4, 2);
insert into employee (id, first_name, last_name, position, salary, age, department_id, organization_id) values (7, 'Pamela', 'Anderson', 'Analyst', 7000, 27, 4, 2);
Raw
hive.sql
--==========================================================================================
/*
Rename Table
ALTER TABLE table_name RENAME TO new_table_name;
This statement lets you change the name of a table to a different name.
As of version 0.6, a rename on a managed table moves its HDFS location as well. (Older Hive versions just renamed the table in the metastore without moving the HDFS location.)
Alter Table Properties
ALTER TABLE table_name SET TBLPROPERTIES table_properties;
table_properties:
: (property_name = property_value, property_name = property_value, ... )
You can use this statement to add your own metadata to the tables. Currently last_modified_user, last_modified_time properties are automatically added and managed by Hive. Users can add their own properties to this list. You can do DESCRIBE EXTENDED TABLE to get this information.
Alter Table Comment
To change the comment of a table you have to change the comment property of the TBLPROPERTIES:
ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);
Add SerDe Properties
ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
serde_properties:
: (property_name = property_value, property_name = property_value, ... )
*/
/*
Add Partitions
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec
[LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
You can use ALTER TABLE ADD PARTITION to add partitions to a table. Partition values should be quoted only if they are strings. The location must be a directory inside of which data files reside. (ADD PARTITION changes the table metadata, but does not load data. If the data does not exist in the partition's location, queries will not return any results.) An error is thrown if the partition_spec for the table already exists. You can use IF NOT EXISTS to skip the error.
Version 0.7
Although it is proper syntax to have multiple partition_spec in a single ALTER TABLE, if you do this in version 0.7 your partitioning scheme will fail. That is, every query specifying a partition will always use only the first partition.
Specifically, the following example will FAIL silently and without error in Hive 0.7, and all queries will go only to dt='2008-08-08' partition, no matter which partition you specify.
com.sensiblemetrics.api.ws.web2docs.annotations.general.Example:
ALTER TABLE page_view ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';
*/
--==========================================================================================
/*
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];*/
create DATABASE IF NOT EXISTS dmpDB
COMMENT "dmp project hive db"
LOCATION ${HIVE_HOME}
WITH DBPROPERTIES ("creator"="morty","data="2016-5-17");
--- describe database dmpDB;
-- DROP DATABASE
DROP DATABASE IF_EXISTES dmpDB ;
--- ALTER DATABASE
/*
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- (Note: SCHEMA added in Hive 0.14.0)
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
*/
ALTER DATABASE dmpDB SET DBPROPERTIES("creator"="morty","edit_by"="morty");
ALTER DATABASE dmpDB SET OWNER USER morty;
USE dmpDB;
USE DEFAULT;
--==========================================================================================
/*
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
data_type
: primitive_type
| array_type
| map_type
| struct_type
| union_type -- (Note: Available in Hive 0.7.0 and later)
primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later)
array_type
: ARRAY < data_type >
map_type
: MAP < primitive_type, data_type >
struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>
union_type
: UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
*/
--创建表
CREATE TABLE IF NOT EXISTS dmp_useraccount(
userid BINGINT COMMENT "userid",
userpassword STRING COMMENT "userpassword",
userpasswordmd5 STRING COMMENT "userpasswordencryption"
useremail STRING COMMENT "useremail",
) COMMENT "user account table"
PARTIONED BY(rgtime STRING)
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
STORED AS TEXTFILE;
--创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS dmpbean_user(
userid INT COMMENT "user_id",
username STRING COMMENT "user_name",
userage SMALLINT COMMENT "user_age",
userbirth DATE COMMENT "userbirthday",
userfamliy MAP<STRING,STRING> COMMENT "userfamilyinfo",
useremail ARRAY<STRING> COMMENT "useremail",
userincome STRING COMMENT "user_income",
userdescribe STRING COMMENT "user_describe",
userpostcode INT COMMENT "user_postcode",
userAddress STRUCT <province:STRING COMMENT "user_province",city:STRING COMMENT "user_city",street:STRING COMMENT "user_street"> COMMNET "user_address",
) COMMENT "USER BEAN TABLE DEMO"
PARTITIONED BY(province STRING,city STRING)
CLUSTERED BY(userage) INTO 10 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003' --[ROW FORMAT DELIMITED]关键字，是用来设置创建的表在加载数据的时候，支持的列分隔符。不同列之间用一个'\001'分割,集合(例如array,map)的元素之间以'\002'隔开,map中key和value用'\003'分割。
STORED AS TEXTFILE --不同的文件格式
LOCATION "hdfs://dev/data" --数据位置
;
-- Create Table As Select(CTAS)
/*
The target table cannot be a partitioned table.
The target table cannot be an external table.
The target table cannot be a list bucketing table
*/
CREATE TRABLE dmpbean_uservipaccount
ROW FORMAT SERED "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
STORED AS RCFile
AS
SELECT userid vipid,userpasswordmd5 vippassword,useremail vipemail,
FROM dmpbean_useraccount
/*
Pages LanguageManual LanguageManual Select
Skip to end of banner
Go to start of banner
Common Table Expression
Skip to end of metadata
Created by Harish Butani, last modified by Lars Francke on Sep 02, 2014 Go to start of metadata
A Common Table Expression (CTE) is a temporary result set derived from a simple query specified in a WITH clause,
which immediately precedes a SELECT or INSERT keyword. The CTE is defined only within the execution scope of a single statement.
One or more CTEs can be used in a Hive SELECT, INSERT, CREATE TABLE AS SELECT, or CREATE VIEW AS SELECT statement.
Common Table Expression Syntax
withClause: cteClause (, cteClause)*
cteClause: cte_name AS (select statment)
*/
--EXAMPLE CTE in SELECT Statements
WITH q1 AS (SELECT userid FROM dmpbean_user WHERE userid ='5')
SELECT *
FROM q1
-- from style
WITH q1 AS (SELECT * FROM dmpbean_user WHERE userid='5')
FROM q1
SELECT
*;
--chain CTES
WITH q1 AS (SELECT userid from q2 WHERE userid='5'),
q2 AS (SELECT userid from dmpbean_user WHERE userid='5')
SELECT *FROM (SELECT userid FROM q1) a;
-- union example
WITH q1 AS (SELECT * FROM dmpbean_user WHERE userid= '5'),
q2 AS (SELECT * FROM dmpbean_user s2 WHERE userid = '4')
SELECT * FROM q1 union all SELECT * FROM q2;
--CTE in Views, CTAS, and Insert Statements
-- insert example
CREATE TABLE s1 like dmpbean_user
WITH q1 as (SELECT userid,username FROM dmpbean_user WHERE userid=5)
FROM q1
INSERT OVERWRITE TABLE s1
SELECT *;
-- ctas example
create table s2 as
with q1 as ( select key from src where key = '4')
select * from q1;
-- view example
CREATE VIEW v1 AS
WITH q1 AS ( SELECT userid FROM dmpbean_user WHERE userid = '5')
SELECT * FROM q1;
SELECT * FROM v1;
-- VIEW example, name collision
CREATE VIEW v1 AS
WITH q1 AS ( SELECT userid FROM dmpbean_user WHERE userid = '5')
SELECT * FROM q1;
WITH q1 AS ( SELECT userid FROM dmpbean_user WHERE userid = '4')
SELECT * FROM v1;
--复制表结构
CREATE TABLE dmpbean_vipuser LIKE dmpbean_user;
--DROP TABLE
DROP TABLE IF EXISTS dmpbean_user [PURGE]
--TRUNCATE TABLE
TRUNCATE TABLE dmpbean_user [PARTITION partition_spec]
CREATE INDEX employess_index ON TABLE emplopyes(country)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD
--==========================================================================================
CREATE DATABASE ${DATABASE} IF NOT EXISTS ;
create table du_u_data(
userid INT COMMENT '用户id',
itemid INT COMMENT '电影ID',
ratings DOUBLE COMMENT '评分',
dataTime STRING COMMENT '时间'
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
CREATE TABLE IF NOT EXISTS du_u_item(
movieid INT COMMENT'电影ID',
movietitle STRING COMMENT '电影名称',
releasedate String COMMENT'发行时间',
videoreleasedae String COMMENT '光碟发行时间',
IMDbURL STRING COMMENT 'IMBD_URL',
unknown_type STRING COMMENT '未知',
Action_type STRING COMMENT '动作',
Adventure STRING COMMENT '探险',
Animation STRING COMMENT '动物',
Childrens STRING COMMENT '',
Comedy STRING COMMENT '喜剧',
Crime STRING COMMENT '',
Documentary STRING COMMENT '',
Drama STRING COMMENT '歌剧',
Fantasy STRING COMMENT '',
Film_Noir STRING COMMENT '',
Horror STRING COMMENT '',
Musical STRING COMMENT '音乐片',
Mystery STRING COMMENT '悬疑片',
Romance STRING COMMENT '爱情片',
Sci_Fi STRING COMMENT '',
Thriller STRING COMMENT '',
War STRING COMMENT '战争片',
Western STRING COMMENT '西部片'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE;
load data local inpath '/user/zeus/duliang/warehouse/hive_db/ml_data/u.item' overwrite into table du_u_item;
--==========================================================================================
Raw
misc.sql
--ecm
select count(*) from ecm.t_order_main;
select count(*) from temp.ecm_aggregation_table_with_id;
select count(*) from temp.temp_ecm_id_integration_result;
select count(distinct(temp_id)) from temp.ecm_aggregation_table_with_id;
select count(distinct(user_id)) from temp.temp_ecm_id_integration_result;
--dbs
select count(*) from dbs.HSCHM_RETAILORDER_H_3YA;
select count(*) from temp.dbs_aggregation_table_with_ID;
select count(*) from temp.temp_dbs_id_integration_result;
select count(distinct(uuid)) from temp.dbs_aggregation_table_with_ID;
select count(distinct(user_id)) from temp.temp_dbs_id_integration_result;
--css
select count(*) from css.t_cs_customer;
select count(*) from temp.dw_ccs_merge_customer;
select count(*) from temp.temp_css_id_integration_result;
select count(distinct(temp_id)) from temp.dw_ccs_merge_customer;
select count(distinct(user_id)) from temp.temp_css_id_integration_result;
--外部融合表：数量
select count(*) from dw_dmp.dw_user_base_info;
select count(distinct(user_id)) from dw_dmp.dw_user_base_info;
select count(*) from dw_dmp.dw_user_phone_info;
select count(distinct(user_id)) from dw_dmp.dw_user_phone_info;
select count(*) from dw_dmp.dw_user_addr_info;
select count(distinct(user_id)) from dw_dmp.dw_user_addr_info;
select count(*) from dw_dmp.dw_user_pay_info;
select count(distinct(user_id)) from dw_dmp.dw_user_pay_info;
select count(*) from dw_dmp.dw_user_email_info;
select count(distinct(user_id)) from dw_dmp.dw_user_email_info;
--一对多关系校验：
--内部融合表：一个用户对应多个手机号
select
count(*)
from(
select
count(*) as count
from(
select
temp_id, receiver_mobile, count(*) as count
from
temp.ecm_aggregation_table_with_id
group by
temp_id, receiver_mobile
) temp
group by
temp.temp_id
having
count(*) > 5
) temp1;
select
count(*)
from(
select
count(*) as count
from(
select
uuid, receivermobile, count(*) as count
from
temp.dbs_aggregation_table_with_id
group by
uuid, receivermobile
)temp
group by
temp.uuid
having
count(*) > 5
) temp1;
select
count(*)
from(
select
count(*) as count
from(
select
temp_id, stelephone3, count(*) as count
from
temp.dw_ccs_merge_customer
group by
temp_id, stelephone3
)temp
group by
temp.temp_id
having
count(*) > 5
) temp1;
--内部融合表：多个手机号的时间跨度
select
count(*)
from (
select
temp2.temp_id,max(unix_timestamp(temp2.update_time))-min(unix_timestamp(temp2.update_time)) difftime,max(temp2.update_time),min(temp2.update_time)
from
temp.ecm_aggregation_table_with_id temp2
where
temp2.temp_id in(
select
temp1.temp_id
from(
select
temp_id, receiver_mobile, count(*) as count
from
temp.ecm_aggregation_table_with_id
group by
temp_id, receiver_mobile
) temp1
group by
temp1.temp_id
having
count(*) > 5
)
group by
temp2.temp_id
)temp3
where
temp3.difftime > 2592000*3;
select
count(*)
from (
select
temp2.uuid,max(unix_timestamp(temp2.created))-min(unix_timestamp(temp2.created)) difftime,max(temp2.created),min(temp2.created)
from
temp.dbs_aggregation_table_with_id temp2
where
temp2.uuid in(
select
temp1.uuid
from(
select
uuid, receivermobile, count(*) as count
from
temp.dbs_aggregation_table_with_id
group by
uuid, receivermobile
) temp1
group by
temp1.uuid
having
count(*) > 5
)
group by
temp2.uuid
)temp3
where
temp3.difftime > 2592000*3;
select
count(*)
from (
select
temp2.temp_id,max(unix_timestamp(temp2.pub_create_date))-min(unix_timestamp(temp2.pub_create_date)) difftime,max(temp2.pub_create_date),min(temp2.pub_create_date)
from
temp.dw_ccs_merge_customer temp2
where
temp2.temp_id in(
select
temp1.temp_id
from(
select
temp_id, stelephone3, count(*) as count
from
temp.dw_ccs_merge_customer
group by
temp_id, stelephone3
) temp1
group by
temp1.temp_id
having
count(*) > 5
)
group by
temp2.temp_id
)temp3
where
temp3.difftime > 2592000*3;
--内部融合表：是否存在一个手机号对应多个用户
select
count(*)
from(
select
count(*) as count
from(
select
temp_id, receiver_mobile, count(*) as count
from
temp.ecm_aggregation_table_with_id
group by
temp_id, receiver_mobile
)temp
group by
temp.receiver_mobile
having
count(*) > 1
) temp1;
select
count(*)
from(
select
count(*) as count
from(
select
uuid, receivermobile, count(*) as count
from
temp.dbs_aggregation_table_with_id
group by
uuid, receivermobile
)temp
group by
temp.receivermobile
having
count(*) > 1
) temp1;
select
count(*)
from(
select
count(*) as count
from(
select
temp_id, stelephone3, count(*) as count
from
temp.dw_ccs_merge_customer
group by
temp_id, stelephone3
)temp
group by
temp.stelephone3
having
count(*) > 1
) temp1;
--外部融合表：一个用户对应多个手机号、地址、支付号、邮箱
select
count(*)
from(
select
count(*) as count
from
dw_dmp.dw_user_phone_info
group by
user_id
having
count(*) > 20
) temp;
select
count(*)
from(
select
count(*) as count
from
dw_dmp.dw_user_addr_info
group by
user_id
having
count(*) > 20
) temp;
select
count(*)
from(
select
count(*) as count
from
dw_dmp.dw_user_pay_info
group by
user_id
having
count(*) > 20
) temp;
select
count(*)
from(
select
*
from
dw_dmp.dw_user_email_info
group by
user_id
having
count(*) > 20
) temp;
--外部融合表：是否存在一个手机号对应多个用户
表关系一对多，user_id是phone的外键，不可能存在。
--质量校验
--内部融合表：字段质量校验
select count(*) from temp.ecm_aggregation_table_with_id where temp_id is null;
select count(*) from temp.ecm_aggregation_table_with_id where receiver_mobile is null;
select count(*) from temp.dbs_aggregation_table_with_ID where uuid is null;
select count(*) from temp.dbs_aggregation_table_with_ID where receivermobile is null;
select count(*) from temp.dw_ccs_merge_customer where temp_id is null;
select count(*) from temp.dw_ccs_merge_customer where stelephone3 is null;
--外部融合表：字段质量校验
select count(*) from dw_dmp.dw_user_base_info where createtime is null;
select count(*) from dw_dmp.dw_user_base_info where unix_timestamp(createtime,'yyyy-MM-dd HH:mm:ss.S') == 0;
select count(*) from dw_dmp.dw_user_base_info where updatetime is null;
select count(*) from dw_dmp.dw_user_base_info where unix_timestamp(updatetime,'yyyy-MM-dd HH:mm:ss.S') == 0;
select count(*) from dw_dmp.dw_user_phone_info where mobile is null;
select count(*) from dw_dmp.dw_user_phone_info where mobile not regexp "^1\\d{10}$";
select count(*) from dw_dmp.dw_user_phone_info where mobile_source is null;
select count(*) from dw_dmp.dw_user_phone_info where mobile_source not regexp "^[1-9]$";
select count(*) from dw_dmp.dw_user_phone_info where starttime is null;
select count(*) from dw_dmp.dw_user_phone_info where unix_timestamp(starttime,'yyyy-MM-dd HH:mm:ss.S') == 0;
select count(*) from dw_dmp.dw_user_addr_info where address is null;
select count(*) from dw_dmp.dw_user_addr_info where address_source is null;
select count(*) from dw_dmp.dw_user_addr_info where address_source not regexp "^[1-9]$";
select count(*) from dw_dmp.dw_user_addr_info where createtime is null;
select count(*) from dw_dmp.dw_user_addr_info where unix_timestamp(createtime,'yyyy-MM-dd HH:mm:ss.S') == 0;
select count(*) from dw_dmp.dw_user_pay_info where pay_no is null;
select count(*) from dw_dmp.dw_user_pay_info where pay_no_source is null;
select count(*) from dw_dmp.dw_user_pay_info where pay_no_source not regexp "^[1-9]$";
select count(*) from dw_dmp.dw_user_pay_info where starttime is null;
select count(*) from dw_dmp.dw_user_pay_info where unix_timestamp(starttime,'yyyy-MM-dd HH:mm:ss.S') == 0;
select count(*) from dw_dmp.dw_user_email_info where email is null;
select count(*) from dw_dmp.dw_user_email_info where email not regexp "^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$";
select count(*) from dw_dmp.dw_user_email_info where email_source is null;
select count(*) from dw_dmp.dw_user_email_info where email_source not regexp "^[1-9]$";
select count(*) from dw_dmp.dw_user_email_info where starttime is null;
select count(*) from dw_dmp.dw_user_email_info where unix_timestamp(starttime,'yyyy-MM-dd HH:mm:ss.S') == 0;
--外部融合表：联合字段质量校验
select
count(*)
from
dw_dmp.dw_user_base_info
where
usrname is not null and usrname_sourece is null;
select
count(*)
from
dw_dmp.dw_user_base_info
where
cardid is not null and cardid_source is null;
select
count(*)
from
dw_dmp.dw_user_base_info
where
sex is not null and sex_source is null;
select
count(*)
from
dw_dmp.dw_user_base_info
where
birthdate is not null and birthdate_source is null;
select
count(*)
from
dw_dmp.dw_user_phone_info
where
mobile is not null and mobile_source is null;
select
count(*)
from
dw_dmp.dw_user_phone_info
where
telephone is not null and telephone_source is null;
select
count(*)
from
dw_dmp.dw_user_addr_info
where
address is not null and address_source is null;
select
count(*)
from
dw_dmp.dw_user_pay_info
where
pay_no is not null and pay_no_source is null;
select
count(*)
from
dw_dmp.dw_user_pay_info
where
buyernick is not null and buyernick_source is null;
select
count(*)
from
dw_dmp.dw_user_email_info
where
email is not null and email_source is null;
--融合效果验证
select
temp_id, count(*) as count
from
temp.ecm_aggregation_table_with_id
group by
temp_id
having
count(*) > 10
limit 10;
select
receiver_name_ori, *
from
temp.ecm_aggregation_table_with_id
where
temp_id = ;
select
uuid, count(*) as count
from
temp.dbs_aggregation_table_with_id
group by
uuid
having
count(*) > 10
limit 10;
select
*
from
temp.dbs_aggregation_table_with_id
where
uuid = ;
select
temp_id, count(*) as count
from
temp.dw_ccs_merge_customer
group by
temp_id
having
count(*) > 10
limit 10;
select
*
from
temp.dw_ccs_merge_customer
where
temp_id = ;
Raw
mysql.sql
# alex <alexbujduveanu>
# To connect to a database
mysql -h localhost -u root -p
# To backup all databases
mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql
# To restore all databases
mysql -u root -p < ~/fulldump.sql
# To create a database in utf8 charset
CREATE DATABASE owa CHARACTER SET utf8 COLLATE utf8_general_ci;
# Types of user permissions:
# ALL PRIVILEGES - gives user full unrestricted access
# CREATE - allows user to create new tables or databases
# DROP - allows user to delete tables or databases
# DELETE - allows user to delete rows from tables
# INSERT- allows user to insert rows into tables
# SELECT- allows user to use the Select command to read through databases
# UPDATE- allow user to update table rows
# GRANT OPTION- allows user to grant or remove other users' privileges
# To grant specific permissions to a particular user
GRANT permission_type ON database_name.table_name TO 'username'@'hostname';
# To add a user and give rights on the given database
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'IDENTIFIED BY 'password' WITH GRANT OPTION;
# To change the root password
SET PASSWORD FOR root@localhost=PASSWORD('new_password');
# To delete a database
DROP DATABASE database_name;
# To reload privileges from MySQL grant table
FLUSH PRIVILEGES;
# Show permissions for a particular user
SHOW GRANTS FOR 'username'@'hostname';
# Find out who the current user is
SELECT CURRENT_USER();
# To delete a table in the database
DROP TABLE table_name;
#To return all records from a particular table
SELECT * FROM table_name;
# To create a table (Users table used as example)
# Note: Since username is a primary key, it is NOT NULL by default. Email is optional in this example.
CREATE TABLE Users (
username VARCHAR(80),
password VARCHAR(80) NOT NULL,
email VARCHAR(80),
PRIMARY KEY (username)
);
# To disable general logging
set global general_log=0;
Raw
query.sql
--==========================================================================================
SELECT d.name, MAX(e.salary) as salary
FROM department d JOIN employee e
ON d.id = e.department_id
GROUP BY d.id;
--==========================================================================================
SELECT d.name, MAX(e.salary) as salary
FROM department d, employee e
WHERE d.id = e.department_id
GROUP BY d.id;
--==========================================================================================
SELECT DISTINCT d.name, e.salary
FROM employee e, department d
WHERE e.department_id = d.id
AND e.salary IN (SELECT max(e.salary) FROM employee e GROUP BY e.department_id)
--==========================================================================================
SELECT DISTINCT d.name, e.salary
FROM employee e, department d
WHERE e.department_id = d.id AND e.salary = ANY (SELECT max(e.salary) FROM employee e GROUP BY e.department_id)
--==========================================================================================
SELECT d2.name, d2.salary FROM (
SELECT d.name, MAX(e.salary) as salary
FROM department d JOIN employee e
ON d.id = e.department_id
GROUP BY d.id
) as d2;
--==========================================================================================
SELECT temp.* FROM (
SELECT d.id, d.name, MAX(e.salary) as salary
FROM department d JOIN employee e
ON d.id = e.department_id
GROUP BY 1
ORDER BY 1
) temp
--==========================================================================================
SELECT d.name, e.salary
FROM department d JOIN (
SELECT e.department_id as id, MAX(e.salary) as salary FROM employee e GROUP BY 1
) e
ON d.id = e.id;
--==========================================================================================
SELECT e2.name, e.salary
FROM employee e JOIN (
SELECT e.department_id, max(d.name) as name, max(e.salary) as max_salary FROM employee e, department d WHERE e.department_id = d.id GROUP BY 1
) e2
ON e.department_id = e2.department_id AND e.salary = e2.max_salary;
--==========================================================================================
SELECT e2.name, e.salary
FROM employee e JOIN (
SELECT d.id, d.name, max(e.salary) as max_salary
FROM employee e JOIN department d
ON e.department_id = d.id
GROUP BY 1
) e2
ON e.department_id = e2.id AND e.salary = e2.max_salary;
--==========================================================================================
SELECT e2.name, e.salary
FROM employee e, (
SELECT e.department_id, max(d.name) as name, max(e.salary) as max_salary
FROM employee e, department d
WHERE e.department_id = d.id
GROUP BY 1
) e2
WHERE e.department_id = e2.department_id AND e.salary = e2.max_salary;
--==========================================================================================
WITH d2 as (
SELECT d.name as name, MAX(e.salary) as salary
FROM department d JOIN employee e
ON d.ID = e.department_id
GROUP BY d.id
)
SELECT name, salary FROM d2 ORDER BY 1;
--==========================================================================================
SELECT DISTINCT d.name, e.salary
FROM department d join employee e
ON d.id = e.department_id
WHERE (e.department_id, e.salary) IN (SELECT e.department_id, max(e.salary) FROM employee as e GROUP by 1);
--==========================================================================================
SELECT DISTINCT d.name, e.salary
FROM department d JOIN employee e
ON d.id = e.department_id AND (e.department_id, e.salary) IN (SELECT e.department_id, max(e.salary) FROM employee as e GROUP by 1);
--==========================================================================================
SELECT DISTINCT d.name, e.salary
FROM employee e, department d
WHERE e.department_id = d.id
AND (d.id, e.salary) = ANY (SELECT e.department_id as id, max(e.salary) as salary FROM employee as e GROUP by 1);
--==========================================================================================
SELECT DISTINCT d.name, e.salary
FROM department d JOIN (
SELECT name, salary, department_id, rank() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employee e
) e
ON e.department_id = d.id AND rnk = 1;
--==========================================================================================
SELECT DISTINCT d.name, e.salary
FROM department d JOIN (
SELECT name, salary, department_id, rank() OVER department_rank AS rnk
FROM employee e
WINDOW department_rank AS (PARTITION BY department_id ORDER BY salary DESC)
) e
ON e.department_id = d.id AND rnk = 1
--==========================================================================================
SELECT d.name, e.salary
FROM department d, (
SELECT e.department_id as id, MAX(e.salary) as salary
FROM employee e
GROUP BY 1
) e
WHERE d.id = e.id;
--==========================================================================================
SELECT word ,count(1) as count FROM
(SELECT explode(split(line,"\s")) AS word FROM DOCS) w
GROUP BY word
ORDER BY word;
--==========================================================================================
Raw
query2.sql
--==========================================================================================
SELECT uid
FROM subscribes
GROUP BY uid
HAVING COUNT(*) > 2
AND max( CASE "subscription_type" WHEN 'type1' THEN 1 ELSE 0 END ) = 0
--==========================================================================================
SELECT Дата_продажи
FROM Продажи
GROUP BY Дата_продажи
HAVING COUNT(DISTINCT Менеджер_ID) = (SELECT COUNT(DISTINCT Менеджер_ID) FROM Продажи);
--==========================================================================================
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) > 1
--==========================================================================================
SELECT DISTINCT a.Email FROM Person a JOIN Person b ON a.Email = b. Email WHERE a.Id != b.Id
--==========================================================================================
SELECT DISTINCT p1.Email FROM Person p1 WHERE EXISTS( SELECT * FROM Person p2 WHERE p2.Email = p1.Email AND p2.Id != p1.Id )
--==========================================================================================
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='test';
--==========================================================================================
SELECT
e.first_name, d.department_name, e.salary
FROM
employees e
JOIN
departments d
ON
(e.department_id = d.department_id)
WHERE
e.first_name
IN
(SELECT TOP 2
first_name
FROM
employees
WHERE
department_id = d.department_id);
--==========================================================================================
-- postgresql sample sql
create view v2 as
SELECT distributors.name
FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
FROM actors
WHERE actors.name LIKE 'W%';
WITH t AS (
SELECT random() as x FROM generate_series(1, 3)
)
SELECT * FROM t
UNION ALL
SELECT * FROM t
;
create view v3 as
WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
SELECT 1, employee_name, manager_name
FROM employee
WHERE manager_name = 'Mary'
UNION ALL
SELECT er.distance + 1, e.employee_name, e.manager_name
FROM employee_recursive er, employee e
WHERE er.employee_name = e.manager_name
)
SELECT distance, employee_name FROM employee_recursive;
WITH upd AS (
UPDATE employees SET sales_count = sales_count + 1 WHERE id =
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
/* not implemented
CREATE RECURSIVE VIEW nums_1_100 (n) AS
VALUES (1)
UNION ALL
SELECT n+1 FROM nums_1_100 WHERE n < 100;
*/
--==========================================================================================
insert into emp (id,first_name,last_name,city,postal_code,ph)
select a.id,a.first_name,a.last_name,a.city,a.postal_code,b.ph
from emp_addr a
inner join emp_ph b on a.id = b.id;
--==========================================================================================
Select distinct W.WORKER_ID, W.FIRST_NAME, W.Salary
from Worker W, Worker W1
where W.Salary = W1.Salary
and W.WORKER_ID != W1.WORKER_ID;
--==========================================================================================
Select max(Salary) from Worker
where Salary not in (Select max(Salary) from Worker);
--==========================================================================================
SELECT * INTO newTable
FROM EmployeeDetails
WHERE 1 = 0;
--==========================================================================================
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
--==========================================================================================
UPDATE table SET emp_name = CASE WHEN emp_name = 'chuck' THEN 'charles' ELSE emp_name END WHERE 'chuck' IN (emp_name);
--==========================================================================================
create function reverse(IN instring VARCHAR(20))
RETURNS VARCHAR(20)
LANGUAGE SQL
DETERMINISTIC
BEGIN
if char_length(instring) in (0, 1)
then return (instring)
else return (reverse(substring(instring from (char_length(instring) / 2 + 1))
|| reverse(substring(instring from 1 for char_length(instring)/ 2))));
end if;
end;
--==========================================================================================
select * from department d, employee e;
select * from department d, employee e where 1=1;
select * from department d join employee e on 1=1;
--==========================================================================================
create table Personnel(
emp_nbr integer default 0 not null primary key,
emp_name varchar(10) default '{{vacant}}' not null,
emp_address varchar(35) not null,
birtdh_date date not null
);
create table OrgChart(
job_title varchar(30) not null primary key,
emp_nbr integer default 0 not null references Personnel(emp_nbr) on delete set default on update cascade,
boss_emp_nbr integer references Personnel(emp_nbr),
salary_amt decimal(12,4) not null check(salary_amt >= 0.00)
);
create function treetest() returns char(6)
language sql
deterministic
begin atomic
insert into temptree select emp_nbr, boss_emp_nbr from OrgChart;
while(select count(*) from temptree) - 1 = (select count(boss_emp_nbr) from TempTree)
do delete from temptree
where temptree.emp_name not in (select t2.boss_emp_nbr from temptree as t2 where t2.boss_emp_nbr is not null);
if not exists (select * from temptree)
then return ('tree');
else return ('cycles');
end if;
end while;
end;
create assertion validtree
check (
(select count(*) from Tree) = (select count(*) from (select parent_node from Tree) union (select child_node from Tree))
);
create view v1(emp_nbr, emp_name, boss_emp_nbr, boss_emp_name)
as
select e1.emp_nbr, e1.emp_name, e1.boss_emp_nbr, b1.emp_name from Personnel E1, Personnel B1, OrgChart P1
where b1.emp_nbr = p1.boss_emp_nbr and e1.emp_nbr = p1.emp_nbr;
select distinct boss_emp_nbr from OrgChart where boss_emp_nbr not in (select emp_nbr from OrgChart);
--==========================================================================================
create or replace function should_increase_salary(
cur_salary numeric,
max_salary numeric DEFAULT 80,
min_salary numeric DEFAULT 30,
increase_rate numeric DEFAULT 0.2
) returns bool AS $$
declare
new_salary numeric;
begin
if cur_salary >= max_salary or cur_salary >= min_salary then
return false;
end if;
if cur_salary < min_salary then
new_salary = cur_salary + (cur_salary * increase_rate);
end if;
if new_salary > max_salary then
return false;
else
return true;
end if;
end;
$$ language plpgsql;
create or replace function get_season(month_number int) returns text AS $$
declare
season text;
begin
if month_number NOT BETWEEN 1 and 12 THEN
RAISE EXCEPTION 'Invalid month. You passed:(%)', month_number USING HINT='Allowed from 1 up to 12', ERRCODE=12882;
end if;
if month_number BETWEEN 3 and 5 then
season = 'Spring';
elsif month_number BETWEEN 6 and 8 then
season = 'Summer';
elsif month_number BETWEEN 9 and 11 then
season = 'Autumn';
else
season = 'Winter';
end if;
return season;
end;
$$ language plpgsql;
create or replace function get_season_caller1(month_number int) returns text AS $$
declare
err_ctx text;
err_msg text;
err_details text;
err_code text;
BEGIN
return get_season(15);
EXCEPTION
WHEN SQLSTATE '12882' then
GET STACKED DIAGNOSTICS err_ctx = PG_EXCEPTION_CONTEXT,
err_msg = MESSAGE_TEXT,
err_details = PG_EXCEPTION_DETAIL,
err_code = RETURNED_SQLSTATE;
RAISE INFO 'My custom handler:';
RAISE INFO 'Error msg:%', err_msg;
RAISE INFO 'Error details:%', err_details;
RAISE INFO 'Error code:%', err_code;
RAISE INFO 'Error context:%', err_ctx;
RETURN NULL;
END;
$$ language plpgsql;
create or replace function get_season_caller2(month_number int) returns text AS $$
declare
err_ctx text;
text_var1 text;
text_var2 text;
text_var3 text;
BEGIN
return get_season(15);
EXCEPTION
--when others then
WHEN SQLSTATE '12882' then
--won't catch by another code
RAISE INFO 'My custom handler:';
RAISE INFO 'Error Name:%',SQLERRM;
RAISE INFO 'Error State:%', SQLSTATE;
RETURN NULL;
END;
$$ language plpgsql;
--==========================================================================================
select *
into tmp_customers
from department;
select *
from tmp_customers
create or replace function fix_customer_region() returns void AS $$
update tmp_customers
set region = 'unknown'
where region is null
$$ language sql
--show functions section in pgAdmin
--then demonstrate
select fix_customer_region()
--hw
select *
into tmp_order
from employee;
create or replace function fix_orders_ship_region() returns void AS $$
update tmp_order
set ship_region = 'unknown'
where ship_region is null
$$ language sql
select fix_orders_ship_region()
--==========================================================================================
create or replace function get_total_number_of_goods() returns bigint AS $$
select sum(units_in_stock)
from products
$$ language sql;
create or replace function get_total_number_of_goods() returns real AS $$
select avg(unit_price)
from products
$$ language sql;
select get_total_number_of_goods() as total_goods --as в самой функции будет проигнорирован
--hw
create or replace function get_max_price_from_discontinued() returns real AS $$
select max(unit_price)
from products
where discontinued = 1
$$ language sql;
select get_max_price_from_discontinued()
--Unless the function is declared to return void,
--the last statement must be a SELECT, or an INSERT, UPDATE, or DELETE that has a RETURNING clause.
--*DO*--
--DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.
--The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.
DO $$
BEGIN
select sum(units_in_stock)
from products
END$$;
--==========================================================================================
drop function if exists get_price_boundaries;
create or replace function get_price_boundaries(out max_price real, out min_price real) AS $$
SELECT MAX(unit_price), MIN(unit_price)
FROM products
$$ language sql;
select get_price_boundaries()
--
drop function if exists get_price_boundaries_by_discontinuity;
create or replace function get_price_boundaries_by_discontinuity(in is_discontinued int, out max_price real, out min_price real) AS $$
SELECT MAX(unit_price), MIN(unit_price)
FROM products
where discontinued = is_discontinued
$$ language sql;
select get_price_boundaries_by_discontinuity(1)
--HW
drop function if exists get_freight_boundaries_by_shipped_dates;
create or replace function get_freight_boundaries_by_shipped_dates(
start_date date, end_date date, out max_price real, out min_price real
) AS $$
SELECT MAX(freight), MIN(freight)
FROM orders
where shipped_date BETWEEN start_date and end_date
$$ language sql;
select get_freight_boundaries_by_shipped_dates('1997-06-01', '1997-06-12')
select *
from orders
--==========================================================================================
drop function if exists get_price_boundaries_by_discontinuity;
create or replace function get_price_boundaries_by_discontinuity
(in is_discontinued int DEFAULT 1, out max_price real, out min_price real) AS $$
SELECT MAX(unit_price), MIN(unit_price)
FROM products
where discontinued = is_discontinued
$$ language sql;
select get_price_boundaries_by_discontinuity(1);
select get_price_boundaries_by_discontinuity(); --with default
--hw
drop function if exists get_freight_boundaries_by_shipped_dates;
create or replace function get_freight_boundaries_by_shipped_dates(
start_date date DEFAULT '1997-06-01', end_date date DEFAULT '1997-06-12', out max_price real, out min_price real
) AS $$
SELECT MAX(freight), MIN(freight)
FROM orders
where shipped_date BETWEEN start_date and end_date
$$ language sql;
select get_freight_boundaries_by_shipped_dates('1997-06-01', '1997-06-12');
select get_freight_boundaries_by_shipped_dates();
--==========================================================================================
--*How to return a set of primitive type values*--
drop function if exists get_average_prices_by_product_categories;
create or replace function get_average_prices_by_product_categories()
returns setof double precision as $$
select AVG(unit_price)
from products
group by category_id
$$ language sql;
select * from get_average_prices_by_product_categories()
--to name the resulting column use 'as'
select * from get_average_prices_by_product_categories() as average_prices
--*How to return a set of columns*--
--*With OUT parameters*--
drop function if exists get_average_prices_by_product_categories;
create or replace function get_average_prices_by_product_categories(out sum_price real, out avg_price float8)
returns setof record as $$
select SUM(unit_price), AVG(unit_price)
from products
group by category_id;
$$ language sql;
select sum_price from get_average_prices_by_product_categories();
select sum_price, avg_price from get_average_prices_by_product_categories();
--won't work
select sum_of, in_avg from get_average_prices_by_product_categories();
--will work
select sum_price as sum_of, avg_price as in_avg
from get_average_prices_by_product_categories();
--*How to return a set of columns*--
--*WithOUT OUT parameters*--
drop function if exists get_average_prices_by_product_categories;
create or replace function get_average_prices_by_product_categories()
returns setof record as $$
select SUM(unit_price), AVG(unit_price)
from products
group by category_id;
$$ language sql;
--won't work in all 4 syntax options
select sum_price from get_average_prices_by_product_categories();
select sum_price, avg_price from get_average_prices_by_product_categories();
select sum_of, in_avg from get_average_prices_by_product_categories();
select * from get_average_prices_by_product_categories();
--works only this
select * from get_average_prices_by_product_categories() as (sum_price real, avg_price float8);
--returns table
drop function if exists get_customers_by_country;
create or replace function get_customers_by_country(customer_country varchar)
returns table(char_code char, company_name varchar) as $$
select customer_id, company_name
from customers
where country = customer_country
$$ language sql;
--правила селекта все те же что и при returns setof
select * from get_customers_by_country('USA');
select company_name from get_customers_by_country('USA');
select char_code, company_name from get_customers_by_country('USA');
--setof table
drop function if exists get_customers_by_country;
create or replace function get_customers_by_country(customer_country varchar)
returns setof customers as $$
-- won't work: select company_name, contact_name
select *
from customers
where country = customer_country
$$ language sql;
select * from get_customers_by_country('USA');
-- получим просто простыню текста: select get_customers_by_country('USA');
select contact_name, city
from get_customers_by_country('USA');
--hw
drop function if exists sold_more_than;
create or replace function sold_more_than(min_sold_boundary int)
returns setof products as $$
select * from products
where product_id IN (
select product_id from
(select sum(quantity), product_id
from order_details
group by product_id
having sum(quantity) >min_sold_boundary
) as filtered_out
)
$$ language sql;
select sold_more_than(100)
--==========================================================================================
--*RETURN in plpgsql*--
CREATE OR REPLACE FUNCTION get_total_number_of_goods() RETURNS bigint AS $$
BEGIN
RETURN sum(units_in_stock)
FROM products;
END;
$$ LANGUAGE plpgsql;
SELECT get_total_number_of_goods();
CREATE OR REPLACE FUNCTION get_max_price_from_discontinued() RETURNS real AS $$
BEGIN
RETURN max(unit_price)
FROM products
WHERE discontinued = 1;
END;
$$ LANGUAGE plpgsql;
SELECT get_max_price_from_discontinued();
CREATE OR REPLACE FUNCTION get_price_boundaries(OUT max_price real, OUT min_price real) AS $$
BEGIN
--max_price := MAX(unit_price) FROM products;
--min_price := MIN(unit_price) FROM products;
SELECT MAX(unit_price), MIN(unit_price)
INTO max_price, min_price
FROM products;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_price_boundaries();
CREATE OR REPLACE FUNCTION get_sum(x int, y int, out result int) AS $$
BEGIN
result = x + y;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_sum(2, 3);
DROP FUNCTION IF EXISTS get_customers_by_country;
CREATE FUNCTION get_customers_by_country(customer_country varchar) RETURNS SETOF customers AS $$
BEGIN
RETURN QUERY
SELECT *
FROM customers
WHERE country = customer_country;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_customers_by_country('USA');
--* Declaring variables*--:
drop function if exists get_square;
create or replace function get_square(ab real, bc real, ac real) returns real AS $$
declare
perimeter real;
begin
perimeter:=(ab+bc+ac)/2;
return sqrt(perimeter * (perimeter - ab) * (perimeter - bc) * (perimeter - ac));
end;
$$ language plpgsql;
select get_square(6, 6, 6)
--*Final example here*--
CREATE OR REPLACE FUNCTION middle_priced()
RETURNS SETOF products AS $$
DECLARE
average_price real;
bottom_price real;
top_price real;
BEGIN
SELECT AVG(unit_price) INTO average_price
FROM products;
bottom_price := average_price * .75;
top_price := average_price * 1.25;
RETURN QUERY SELECT * FROM products
WHERE unit_price between bottom_price AND top_price;
END;
$$ LANGUAGE plpgsql;
--==========================================================================================
--*IF-THEN-ELSE*--
drop function if exists convert_temp_to;
create or replace function convert_temp_to(temperature real, to_celsius bool DEFAULT true) returns real AS $$
declare
result_temp real;
begin
if to_celsius then
result_temp = (5.0/9.0)*(temperature-32);
else
result_temp:=(9*temperature+(32*5))/5.0;
end if;
return result_temp;
end;
$$ language plpgsql;
select convert_temp_to(80);
select convert_temp_to(26.7, false);
--*IF-ELSIF-ELSE*--
drop function if exists get_season;
create or replace function get_season(month_number int) returns text AS $$
declare
season text;
begin
if month_number BETWEEN 3 and 5 then
season = 'Spring';
elsif month_number BETWEEN 6 and 8 then
season = 'Summer';
elsif month_number BETWEEN 9 and 11 then
season = 'Autumn';
else
season = 'Winter';
end if;
return season;
end;
$$ language plpgsql;
select get_season(12)
--==========================================================================================
CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
RETURNS INTEGER AS $$
DECLARE
counter INTEGER := 0 ;
i INTEGER := 0 ;
j INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
WHILE counter <= n
LOOP
counter := counter + 1 ;
SELECT j, i + j INTO i, j;
END LOOP ;
RETURN i ;
END ;
-- rewritten with explicit exit instead if WHILE--
CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
RETURNS INTEGER AS $$
DECLARE
counter INTEGER := 0 ;
i INTEGER := 0 ;
j INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
LOOP
EXIT WHEN counter = n ;
counter := counter + 1 ;
SELECT j, i + j INTO i, j ;
END LOOP ;
RETURN i ;
END ;
$$ LANGUAGE plpgsql;
-- FOR IN --
DO $$
BEGIN
FOR counter IN 1..5 LOOP
RAISE NOTICE 'Counter: %', counter;
END LOOP;
END; $$
DO $$
BEGIN
FOR counter IN REVERSE 5..1 LOOP
RAISE NOTICE 'Counter: %', counter;
END LOOP;
END; $$
DO $$
BEGIN
FOR counter IN 1..6 BY 2 LOOP
RAISE NOTICE 'Counter: %', counter;
END LOOP;
END; $$
--*Continue and Iterate Over Array*--
CREATE OR REPLACE FUNCTION filter_even(variadic numbers int[]) returns setof int
AS $$
BEGIN
FOR counter IN 1..array_upper(numbers, 1)
LOOP
CONTINUE WHEN counter % 2 != 0;
return next counter;
END LOOP;
END;
$$ LANGUAGE plpgsql;
select * from filter_even(1, 2, 3, 4, 5, 6)
--*FOREACH*--
CREATE OR REPLACE FUNCTION filter_even(variadic numbers int[]) returns setof int
AS $$
DECLARE
counter int;
BEGIN
FOREACH counter IN ARRAY numbers
LOOP
CONTINUE WHEN counter % 2 != 0;
return next counter;
END LOOP;
END;
$$ LANGUAGE plpgsql;
select * from filter_even(1, 2, 3, 4, 5, 6)
-- Iterate Over a Query --
CREATE OR REPLACE FUNCTION iter_over_query(n INTEGER DEFAULT 5)
RETURNS VOID AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT *
FROM products
ORDER BY unit_price
LIMIT n
LOOP
RAISE NOTICE '%', rec.product_name; --don't forget to look at messages
END LOOP;
END;
$$ LANGUAGE plpgsql;
select * from iter_over_query()
--*RETURN NEXT*--
-- Иногда нам необходима построчная обработка данных и уже затем построчный их возврат из функции
-- в таком случае надо использовать выражение RETURN NEXT для возврата каждой строки.
-- Это выражение можно вызывать несколько раз и результатом каждого вызова будет новая строка в выходном наборе данных.
-- Вот простейший пример:
CREATE OR REPLACE FUNCTION return_setof_int() RETURNS SETOF int AS
$$
BEGIN
RETURN NEXT 1;
RETURN NEXT 2;
RETURN NEXT 3;
RETURN; -- Необязательный
END
$$ LANGUAGE plpgsql;
--RETURN NEXT:
CREATE OR REPLACE FUNCTION test0()
RETURNS TABLE(y integer, result text) AS $$
BEGIN
FOR y, result IN
SELECT s.y, 'hi' result FROM generate_series(1,10,1) AS s(y)
LOOP
RETURN NEXT y;
END LOOP;
END
$$ LANGUAGE plpgsql;
SELECT * FROM test0();
--а вот пример посложнее--
CREATE OR REPLACE FUNCTION after_christmas_sale() RETURNS SETOF products AS $$
DECLARE
product record;
BEGIN
FOR product IN
SELECT * FROM products
LOOP
IF product.category_id IN (1,4,8) THEN
product.unit_price = product.unit_price * .80;
ELSIF product.category_id IN (2,3,7) THEN
product.unit_price = product.unit_price * .75;
ELSE
product.unit_price = product.unit_price * 1.10;
END IF;
RETURN NEXT product;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM after_christmas_sale();
--==========================================================================================
CREATE OR REPLACE FUNCTION random_between(low INT ,high INT)
RETURNS INT AS
$$
BEGIN
RETURN floor(random()* (high-low + 1) + low);
END;
$$ language 'plpgsql' STRICT;
--==========================================================================================
--string functions--
select chr(72);
select concat('Abra', '-abra-', 'cadabra');
select upper('abcd'); -- удобно для сравнения
select lower('ABCD');
select initcap('hello, John');
--substring-related funcs
select position('lo' in 'hello'); --starting at 4
select overlay('h___o' placing 'ell' from 2 for 3); -- where from and how many chars
select substring('abra_cadabra_abra' from 6 for 7) -- by index & number of chars
--select using regex (by posix and sql)
select trim(both ' ' from ' Hello ');
select trim(leading ' ' from ' Hello ');
select trim(trailing ' ' from ' Hello ');
--select convert('text_in_ascii', 'ascii', 'UTF8')
--replace
--numerical functions--
select abs(-1), abs(1);
--what about rounding mode?
select round(3.4),round(3.6),round(3.6);
select ceil(3.4),ceil(3.5),ceil(3.6),ceil(4);
select floor(3.4),floor(3.5),floor(3.6), floor(4);
select sign(-1), sign(1);
select mod(5, 2), mod(4, 2);
select mod(5,2)=0; --if it is even?
select sqrt(16), sqrt(4.5);
select format(sqrt(4.5), 2);
select power(2, 5) --or pow in short
select greatest(1,2,3,4,5);
--DATES & TIMES--
select current_date;
select
extract(day from date '2020-02-20') as day,
extract(month from date '2020-01-15') as month,
extract(year from date '2020-01-15') as year
select date_trunc('month', date'2020-01-15') -- cut off days
select date_trunc('year', date'2020-01-15') -- cut off months & days
select current_date + integer '3';
select current_date + interval '2 hours 30 minutes';
select current_date + interval '1 day';
select localtimestamp + interval '2 hours 30 minutes';
select localtimestamp + time '02:30';
--conversion--
--todo: casting example
select convert(int, 2.5);
--==========================================================================================
SELECT product_name, unit_price,
CASE WHEN units_in_stock >= 100 THEN 'lots of'
WHEN units_in_stock >= 50 AND units_in_stock < 100 THEN 'average'
WHEN units_in_stock < 50 THEN 'low number'
ELSE 'unknown'
END AS amount
FROM products;
SELECT order_id, order_date,
CASE WHEN date_part('month', order_date) BETWEEN 3 and 5 THEN 'spring'
WHEN date_part('month', order_date) BETWEEN 6 and 8 THEN 'summer'
WHEN date_part('month', order_date) BETWEEN 9 and 11 THEN 'autumn'
ELSE 'winter'
END AS bla
FROM orders;
--==========================================================================================
SELECT *
FROM orders
LIMIT 10;
SELECT order_id, order_date, COALESCE(ship_region, 'unknown') AS ship_region
FROM orders
LIMIT 10;
SELECT *
FROM employees;
SELECT last_name, first_name, COALESCE(region, 'N/A') as region
FROM employees;
SELECT contact_name, COALESCE(NULLIF(city, ''), 'Unknown') as city
FROM customers;
CREATE TABLE budgets
(
dept serial,
current_year decimal NULL,
previous_year decimal NULL
);
INSERT INTO budgets(current_year, previous_year) VALUES(100000, 150000);
INSERT INTO budgets(current_year, previous_year) VALUES(NULL, 300000);
INSERT INTO budgets(current_year, previous_year) VALUES(0, 100000);
INSERT INTO budgets(current_year, previous_year) VALUES(NULL, 150000);
INSERT INTO budgets(current_year, previous_year) VALUES(300000, 250000);
INSERT INTO budgets(current_year, previous_year) VALUES(170000, 170000);
INSERT INTO budgets(current_year, previous_year) VALUES(150000, NULL);
SELECT dept,
COALESCE(TO_CHAR(NULLIF(current_year, previous_year), 'FM99999999'), 'Same as last year') AS budget
FROM budgets
WHERE current_year IS NOT NULL;
--==========================================================================================
SELECT contact_name, city, country
FROM customers
ORDER BY contact_name,
(
CASE WHEN city IS NULL THEN country
ELSE city
END
);
INSERT INTO customers(customer_id, contact_name, city, country, company_name)
VALUES
('AAAAAB', 'John Mann', 'abc', 'USA', 'fake_company'),
('BBBBBV', 'John Mann', 'acd', 'Austria', 'fake_company');
SELECT product_name, unit_price,
CASE WHEN unit_price >= 100 THEN 'too expensive'
WHEN unit_price >= 50 AND unit_price < 100 THEN 'average'
ELSE 'low price'
END AS price
FROM products
ORDER BY unit_price DESC;
SELECT DISTINCT contact_name, COALESCE(order_id::text, 'no orders')
FROM customers
LEFT JOIN orders USING(customer_id)
WHERE order_id IS NULL;
SELECT CONCAT(last_name, ' ', first_name), COALESCE(NULLIF(title, 'Sales Representative'), 'Sales Stuff') AS title
FROM employees;
--==========================================================================================
CREATE OR REPLACE VIEW heavy_orders AS
SELECT *
FROM orders
WHERE freight > 100;
SELECT *
FROM heavy_orders
ORDER BY freight;
INSERT INTO heavy_orders
VALUES(11900, 'FOLIG', 1, '2000-01-01', '2000-01-05', '2000-01-04', 1, 80, 'Folies gourmandes', '184, chaussee de Tournai',
'Lille', NULL, 59000, 'FRANCE');
SELECT *
FROM heavy_orders
WHERE order_id = 11900;
CREATE OR REPLACE VIEW heavy_orders AS
SELECT *
FROM orders
WHERE freight > 100
WITH LOCAL CHECK OPTION;
CREATE OR REPLACE VIEW heavy_orders AS
SELECT *
FROM orders
WHERE freight > 100
WITH CASCADE CHECK OPTION;
--==========================================================================================
CREATE VIEW orders_customers_employees AS
SELECT order_date, required_date, shipped_date, ship_postal_code,
company_name, contact_name, phone,
last_name, first_name, title
FROM orders
JOIN customers USING (customer_id)
JOIN employees USING (employee_id);
SELECT *
FROM orders_customers_employees
WHERE order_date > '1997-01-01';
--
CREATE OR REPLACE VIEW orders_customers_employees AS
SELECT order_date, required_date, shipped_date, ship_postal_code, ship_country, --add ship_country
company_name, contact_name, phone, postal_code, --add postal_code
last_name, first_name, title, reports_to --add reports_to
FROM orders
JOIN customers USING (customer_id)
JOIN employees USING (employee_id);
SELECT *
FROM orders_customers_employees
ORDER BY ship_country;
--
ALTER VIEW products_suppliers_categories RENAME TO products_detailed;
--
DROP VIEW IF EXISTS orders_customers_employees;
select * from products;
drop view active_products;
create or replace view active_products
as
select product_id, product_name, supplier_id, category_id, quantity_per_unit, unit_price,
units_in_stock, units_on_order, reorder_level, discontinued
FROM products
where discontinued <> 1
with local check option;
insert into active_products
values(78, 'abc', 1, 1, 'abc', 1, 1, 1, 1, 1);
--
SELECT product_name, unit_price,
CASE WHEN unit_price>95 THEN 'expensive'
WHEN unit_price>= 50 and unit_price < 95 THEN 'middle range'
WHEN unit_price < 50 THEN 'cheap'
END AS expensiveness
FROM products
ORDER BY unit_price DESC;
--
select company_name, coalesce(region, 'unknown region')
from suppliers;
--==========================================================================================
select constraint_name
from information_schema.key_column_usage
where table_name = 'chair'
and table_schema = 'public'
and column_name = 'cathedra_id';
--==========================================================================================
CREATE TABLE customer
(
customer_id serial,
full_name text,
status char DEFAULT 'r',
CONSTRAINT PK_customer_id PRIMARY KEY(customer_id),
CONSTRAINT CHK_customer_status CHECK (status = 'r' or status = 'p')
);
INSERT INTO customer
VALUES
(1, 'name');
SELECT *
FROM customer;
INSERT INTO customer
VALUES
(1, 'name', 'd');
ALTER TABLE customer
ALTER COLUMN status DROP DEFAULT;
ALTER TABLE customer
ALTER COLUMN status SET DEFAULT 'r';
--==========================================================================================
CREATE SEQUENCE seq;
SELECT nextval('seq');
SELECT currval('seq');
SELECT lastval();
--
SELECT setval('seq', 10);
SELECT currval('seq');
SELECT nextval('seq');
SELECT setval('seq', 16, false);
SELECT currval('seq');
SELECT nextval('seq');
--
CREATE SEQUENCE IF NOT EXISTS seq2 INCREMENT 16;
SELECT nextval('seq2');
--
CREATE SEQUENCE IF NOT EXISTS seq3
INCREMENT 16
MINVALUE 0
MAXVALUE 128
START WITH 0
SELECT nextval('seq3');
ALTER SEQUENCE seq3 RENAME TO seq4
ALTER SEQUENCE seq4 RESTART WITH 16
SELECT nextval('seq4');
DROP SEQUENCE seq4;
--==========================================================================================
CREATE SEQUENCE IF NOT EXISTS book_book_id_seq
START WITH 1 OWNED BY book.book_id;
-- doesn't work
INSERT INTO book (title, isbn, publisher_id)
VALUES ('title', 'isbn', 1);
--we need to set default
ALTER TABLE book
ALTER COLUMN book_id SET DEFAULT nextval('book_book_id_seq');
--now should work
INSERT INTO book (title, isbn, publisher_id)
VALUES ('title', 'isbn', 1);
INSERT INTO book (title, isbn, publisher_id)
VALUES ('title3', 'isbn3', 1)
RETURNING book_id;
--==========================================================================================
INSERT INTO book(title, isbn, publisher_id)
VALUES ('title', 'isbn', 3)
RETURNING *;
UPDATE author
SET full_name = 'Walter', rating = 5
WHERE author_id = 1
RETURNING author_id;
DELETE FROM author
WHERE rating = 5
RETURNING *;
--==========================================================================================
SELECT * FROM author;
UPDATE author
SET full_name = 'Elias', rating = 5
WHERE author_id = 1;
DELETE FROM author
WHERE rating < 4.5;
DELETE FROM author;
TRUNCATE TABLE author;
DROP TABLE book;
CREATE TABLE book
(
book_id serial,
title text NOT NULL,
isbn varchar(32) NOT NULL,
publisher_id int NOT NULL,
CONSTRAINT PK_book_book_id PRIMARY KEY(book_id)
);
INSERT INTO book(title, isbn, publisher_id)
VALUES ('title', 'isbn', 3)
RETURNING *;
UPDATE author
SET full_name = 'Walter', rating = 5
WHERE author_id = 1
RETURNING author_id;
DELETE FROM author
WHERE rating = 5
RETURNING *;
--==========================================================================================
-- Subquery: что если мы хотим найти все компании поставщиков из тех стран, в которые делают заказы заказчики?
SELECT company_name
FROM suppliers
WHERE country IN (SELECT country FROM customers)
--equivalent query
SELECT DISTINCT suppliers.company_name
FROM suppliers
JOIN customers USING(country)
SELECT category_name, SUM(units_in_stock)
FROM products
INNER JOIN categories ON products.category_id = categories.category_id
GROUP BY category_name
ORDER BY SUM(units_in_stock) DESC
LIMIT (SELECT MIN(product_id) + 4 FROM products)
--среднее кол-во товаров в наличии
SELECT AVG(units_in_stock)
FROM products
-- а если мы хотим вывести такие товары, количество которого в наличии больше чем в среднем
SELECT product_name, units_in_stock
FROM products
WHERE units_in_stock >
(SELECT AVG(units_in_stock)
FROM products)
ORDER BY units_in_stock
--==========================================================================================
--выбрать все уникальные компании заказчиков которые делали заказы на более чем 40 единиц товаров
--с джойнами
SELECT DISTINCT company_name
FROM customers
JOIN orders USING(customer_id)
JOIN order_details USING(order_id)
WHERE quantity > 40;
--с подзапросом
SELECT DISTINCT company_name --from course
FROM customers
WHERE customer_id = ANY(SELECT customer_id FROM orders
JOIN order_details USING(order_id)
WHERE quantity > 40);
-- можно комбинировать джойны с подзапросами
-- это у нас просто среднее количество единиц товара по всем заказам
SELECT AVG(quantity)
FROM order_details;
-- давайте выберем такие продукты, количество которых больше среднего по заказам
-- используя предыдущий запрос в качестве подзапроса можно написать следующий запрос:
SELECT DISTINCT product_name, quantity
FROM products
JOIN order_details USING(product_id)
WHERE quantity >
(SELECT AVG(quantity)
FROM order_details);
-- найти все продукты количество которых больше среднего значения количества заказанных товаров из групп, полученных группированием по product_id
SELECT AVG(quantity)
FROM order_details
GROUP BY product_id;
SELECT DISTINCT product_name, quantity
FROM products
JOIN order_details USING(product_id)
WHERE quantity > ALL
(SELECT AVG(quantity)
FROM order_details
GROUP BY product_id)
ORDER BY quantity;
--==========================================================================================
SELECT product_name, units_in_stock
FROM products
WHERE units_in_stock < ALL
(SELECT AVG(quantity)
FROM order_details
GROUP BY product_id)
ORDER BY units_in_stock DESC;
SELECT AVG(quantity)
FROM order_details
GROUP BY product_id
order by AVG(quantity)
SELECT o.customer_id, SUM(o.freight) AS freight_sum
FROM orders AS o
INNER JOIN (SELECT customer_id, AVG(freight) AS freight_avg
FROM orders
GROUP BY customer_id) AS oa
ON oa.customer_id = o.customer_id
WHERE o.freight > oa.freight_avg
AND o.shipped_date BETWEEN '1996-07-16' AND '1996-07-31'
GROUP BY o.customer_id
ORDER BY freight_sum;
SELECT customer_id, ship_country, order_price
FROM orders
JOIN (SELECT order_id,
SUM(unit_price * quantity - unit_price * quantity * discount) AS order_price
FROM order_details
GROUP BY order_id) od
USING(order_id)
WHERE ship_country IN ('Argentina' , 'Bolivia', 'Brazil', 'Chile', 'Colombia', 'Ecuador', 'Guyana', 'Paraguay',
'Peru', 'Suriname', 'Uruguay', 'Venezuela')
AND order_date >= '1997-09-01'
ORDER BY order_price DESC
LIMIT 3;
SELECT product_name
FROM products
WHERE product_id = ANY (SELECT product_id FROM order_details WHERE quantity = 10);
SELECT distinct product_name, quantity
FROM products
join order_details using(product_id)
where order_details.quantity = 10
--==========================================================================================
CREATE TABLE employee (
employee_id int PRIMARY KEY,
first_name varchar(256) NOT NULL,
last_name varchar(256) NOT NULL,
manager_id int,
FOREIGN KEY (manager_id) REFERENCES employee(employee_id);
);
INSERT INTO employee
(employee_id, first_name, last_name, manager_id)
VALUES
(1, 'Windy', 'Hays', NULL),
(2, 'Ava', 'Christensen', 1),
(3, 'Hassan', 'Conner', 1),
(4, 'Anna', 'Reeves', 2),
(5, 'Sau', 'Norman', 2),
(6, 'Kelsie', 'Hays', 3),
(7, 'Tory', 'Goff', 3),
(8, 'Salley', 'Lester', 3);
SELECT e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM employee e
LEFT JOIN employee m ON m.employee_id = e.manager_id
ORDER BY manager;
--==========================================================================================
SELECT COUNT(*) AS employees_count
FROM employees;
SELECT COUNT(DISTINCT country) AS country
FROM employees;
SELECT category_id, SUM(units_in_stock) AS units_in_stock
FROM products
GROUP BY category_id
ORDER BY units_in_stock DESC
LIMIT 5;
SELECT category_id, SUM(unit_price * units_in_stock) AS total_price
FROM products
WHERE discontinued <> 1
GROUP BY category_id
HAVING SUM(unit_price * units_in_stock) > 5000
ORDER BY total_price DESC;
--==========================================================================================
-- Найти заказчиков и обслуживающих их заказы сотрудкников
-- таких, что и заказчики и сотрудники из города London, а доставка идёт компанией Speedy Express.
-- Вывести компанию заказчика и ФИО сотрудника.
SELECT c.company_name AS customer,
CONCAT(e.first_name, ' ', e.last_name) AS employee
FROM orders as o
JOIN customers as c USING(customer_id)
JOIN employees as e USING(employee_id)
JOIN shippers as s ON o.ship_via = s.shipper_id
WHERE c.city = 'London'
AND e.city = 'London'
AND s.company_name = 'Speedy Express';
-- Найти активные (см. поле discontinued) продукты из категории Beverages и Seafood, которых в продаже менее 20 единиц
-- Вывести наименование продуктов, кол-во единиц в продаже, имя контакта поставщика и его телефонный номер.
SELECT product_name, units_in_stock, contact_name, phone
FROM products
JOIN categories USING(category_id)
JOIN suppliers USING(supplier_id)
WHERE category_name IN ('Beverages', 'Seafood')
AND discontinued = 0
AND units_in_stock < 20
ORDER BY units_in_stock;
-- Найти заказчиков, не сделавших ни одного заказа
-- Вывести имя заказчика и order_id
SELECT distinct contact_name, order_id
FROM customers
LEFT JOIN orders USING(customer_id)
WHERE order_id IS NULL
ORDER BY contact_name;
--Переписать предыдущий запрос, использовав симметричный вид джойна (подсказа: речь о LEFT и RIGHT)
SELECT contact_name, order_id
FROM orders
RIGHT JOIN customers USING(customer_id)
WHERE order_id IS NULL
ORDER BY contact_name;
--==========================================================================================
SELECT ship_country, COUNT(*)
FROM orders
WHERE freight > 50
GROUP BY ship_country
ORDER BY COUNT(*) DESC;
SELECT category_id, SUM(UnitsInStock)
FROM products
GROUP BY category_id
ORDER BY SUM(units_in_stock) DESC;
LIMIT 5
SELECT category_id, SUM(unit_price * units_in_stock)
FROM products
WHERE discontinued <> 1
GROUP BY category_id
HAVING SUM(unit_price * units_in_stock) > 5000
ORDER BY SUM(unit_price * units_in_stock) DESC;
--==========================================================================================
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'testdb'
AND pid <> pg_backend_pid()
--==========================================================================================
CREATE TABLE person
(
person_id int PRIMARY KEY,
first_name varchar(64) NOT NULL,
last_name varchar(64) NOT NULL
);
CREATE TABLE passport
(
passport_id int PRIMARY KEY,
serial_number int NOT NULL,
fk_passport_person int UNIQUE REFERENCES person(person_id)
);
INSERT INTO person VALUES (1, 'John', 'Snow');
INSERT INTO person VALUES (2, 'Ned', 'Stark');
INSERT INTO person VALUES (3, 'Rob', 'Baratheon');
ALTER TABLE passport
ADD COLUMN registration text NOT NULL;
INSERT INTO passport VALUES (1, 123456, 1, 'Winterfell');
INSERT INTO passport VALUES (2, 789012, 2, 'Winterfell');
INSERT INTO passport VALUES (3, 345678, 3, 'King''s Landing');
--==========================================================================================
-- a) have a gander at monthly & weekly volume patterns of 2012
-- b) session volume & order volume
-- a
select
year(website_sessions.created_at),
month(website_sessions.created_at),
count(distinct website_sessions.website_session_id) as sessions,
count(distinct orders.order_id) as orders
from website_sessions
left join orders
on orders.website_session_id=website_sessions.website_session_id
where website_sessions.created_at > '2012-01-01'
and website_sessions.created_at < '2013-01-02'
group by
1,2
order by 1,2;
-- b
select
min(date(website_sessions.created_at)) as week_start_date,
count(distinct website_sessions.website_session_id) as sessions,
count(distinct orders.order_id) as orders
from website_sessions
left join orders
on orders.website_session_id=website_sessions.website_session_id
where website_sessions.created_at > '2012-01-01'
and website_sessions.created_at < '2013-01-02'
group by
yearweek(website_sessions.created_at);
--==========================================================================================
-- average website session volume by hour of day & by day week
select
hr,
avg(case when wkday = 1 then website_sessions else NULL end) as tue,
avg(case when wkday = 2 then website_sessions else NULL end) as wed,
avg(case when wkday = 3 then website_sessions else NULL end) as thu,
avg(case when wkday = 4 then website_sessions else NULL end) as fri,
avg(case when wkday = 5 then website_sessions else NULL end) as sat,
avg(case when wkday = 6 then website_sessions else NULL end) as sun
from
(
select
date(created_at) as date,
weekday(created_at) as wkday,
hour(created_at) as hr,
count(distinct website_session_id) as website_sessions
from website_sessions
where created_at > '2012-09-15' and created_at < '2012-11-15'
group by 1,2,3
) as date_table
group by 1
order by 1;
--==========================================================================================
-- count pageviews to identify 'bounces' and summarize by week
select
min(date(session_created)) as week_start_date,
-- COUNT + CASE is a Pivot method
count(distinct case when count_pageveiws = 1 then session else NULL end)*1.0/count(distinct session) as bounce_rate,
count(distinct case when landing_page = '/home' then session else NULL end) as home_session,
count(distinct case when landing_page = '/lander-1' then session else NULL end) as lander_sessions
from landing_pages
group by
yearweek(session_created);
--==========================================================================================
-- pull data on how many of business website visitors come back for another session
DROP TEMPORARY TABLE IF EXISTS repeat_sessions;
create temporary table repeat_sessions
select
inner_table.user_id,
inner_table.website_session_id as new_session_id,
website_sessions.website_session_id as repeat_session_id
from
(
select
user_id,
website_session_id
from website_sessions
where created_at < '2014-11-03'
and created_at >= '2014-01-01'
and is_repeat_session = 0
) as inner_table
-- 'inner_table' will have only new queries
left join website_sessions
on website_sessions.user_id=inner_table.user_id
and website_sessions.is_repeat_session = 1 -- can be, but redundant
and website_sessions.website_session_id > inner_table.website_session_id
-- above one specifies that repeat session should be further than initial one
and website_sessions.created_at < '2014-11-03'
and website_sessions.created_at >= '2014-01-01';
-- result
select
repeat_session_id,
count(distinct user_id) as users
from
(
select
user_id,
count(distinct new_session_id) as new_session_id,
count(distinct repeat_session_id) as repeat_session_id
from repeat_sessions
group by 1
order by 3 desc
) as users
group by 1;
--==========================================================================================
-- compare new vs repeat sessions by channel
-- output
select
case
when utm_source is NULL and http_referer in ('https://www.gsearch.com', 'https://www.bsearch.com')
then 'organic_search'
when utm_source is NULL and http_referer is NULL then 'direct_type'
when utm_campaign = 'nonbrand' then 'paid_nonbrand'
when utm_campaign = 'brand' then 'paid_brand'
when utm_source = 'socialbook' then 'paid_social'
end as channel_group,
-- utm_source,
-- utm_campaign,
-- http_referer,
count(case when is_repeat_session = 0 then website_session_id else NULL end) as new_sessions,
count(case when is_repeat_session = 1 then website_session_id else NULL end) as repeat_sessions
from website_sessions
where created_at >= '2014-01-01'
and created_at < '2014-11-05'
group by 1
order by repeat_sessions desc;
--==========================================================================================
-- min, max, avg time between the first and the second session
-- retrieve users with repeat sessions & created_at data
DROP TEMPORARY TABLE IF EXISTS first_second_sessions;
create temporary table first_second_sessions
select
first_session.created_at as first_created,
first_session.user_id,
first_session.website_session_id as first_sessions,
website_sessions.website_session_id as second_sessions,
website_sessions.created_at as second_created
from
(
select
website_session_id,
user_id,
created_at
from website_sessions
where created_at >='2014-01-01'
and created_at < '2014-11-03'
and is_repeat_session = 0
) as first_session
left join website_sessions
on website_sessions.user_id=first_session.user_id
and website_sessions.is_repeat_session = 1
and website_sessions.website_session_id > first_session.website_session_id
and website_sessions.created_at >= '2014-01-01'
and website_sessions.created_at < '2014-11-03';
-- analyzing 'created_at'
DROP TEMPORARY TABLE IF EXISTS pre_final;
create temporary table pre_final
select
datediff(second_created, first_created) as days_first_second_session,
user_id
from
(
select
first_created,
first_sessions,
user_id,
min(second_created) as second_created,
-- first session that is not new (repeat one)
min(second_sessions) as second_session
from first_second_sessions
where second_sessions is not NULL
group by 1,2,3
) as user_created;
-- result
select
avg(days_first_second_session) as avg_days_first_second,
min(days_first_second_session) as min_days_first_second,
max(days_first_second_session) as max_days_first_second
from pre_final;
--==========================================================================================
-- breakdown by UTM source, campaign, referring domain
select
utm_source,
utm_campaign,
http_referer,
count(distinct web.website_session_id) as sessions
from website_sessions as web
where created_at < '2012-04-12'
group by utm_source, utm_campaign, http_referer
order by 4 desc;
--==========================================================================================
-- conversion rates from session to order by device type
select
device_type,
count(distinct web.website_session_id) as sessions,
count(distinct ord.order_id) as orders,
count(distinct ord.order_id)/count(distinct web.website_session_id) as session_order_conv
from website_sessions as web
left join orders as ord
on ord.website_session_id=web.website_session_id
where web.created_at < '2012-05-11'
and web.utm_campaign = 'nonbrand'
and web.utm_source = 'gsearch'
group by device_type;
-- weekly trends for both desktop and mobile
select
min(date(web.created_at)) as week_start_date,
count(distinct case when web.device_type = 'desktop' then web.website_session_id else NULL end) as dtop_sessions,
count(distinct case when web.device_type = 'mobile' then web.website_session_id else NULL end) as mob_sessions
from website_sessions as web
where web.created_at < '2012-06-09'
and web.created_at > '2012-05-19'
and web.utm_source = 'gsearch'
and web.utm_campaign = 'nonbrand'
group by yearweek(web.created_at);
--==========================================================================================
-- conversion rate from session to order with at least 4% CVR
select
count(distinct a.website_session_id) as sessions,
count(distinct b.order_id) as orders,
count(distinct b.order_id)/count(distinct a.website_session_id) as session_order_conversion
from website_sessions as a
left join orders as b
on b.website_session_id=a.website_session_id
where a.created_at < '2012-04-14' and a.utm_source = 'gsearch'
and utm_campaign = 'nonbrand';
--==========================================================================================
--
select
count(distinct website_session_id) as sessions,
min(date(created_at)) as week_start,
week(created_at),
year(created_at)
from website_sessions
where website_session_id between 100000 and 115000
group by 4,3;
-- COUNT with CASE inside can help to mimick Excel's Pivot.
-- Use GROUP BY to define your row labels, and CASE to pivot to columns
-- Below we want to know number of orders where 1 or 2 items were purchased and total of orders
select
primary_product_id,
count(distinct case when items_purchased = 1 then order_id else NULL end) as orders_w_1_item,
count(distinct case when items_purchased = 2 then order_id else NULL end) as orders_w_2_items,
count(distinct order_id) as total_orders
from orders
where order_id between 31000 and 32000
group by 1;
--==========================================================================================
-- gsearch nonbrand trended session volme by week
select
date_format(web.created_at, '%Y-%m-%d') as week_start_date,
count(distinct web.website_session_id) as sessions
from website_sessions as web
where web.created_at < '2012-05-10' and web.utm_source = 'gsearch'
and web.utm_campaign = 'nonbrand'
group by week_start_date
order by week_start_date asc;
select
min(date(web.created_at)) as week_start_date,
count(distinct web.website_session_id) as sessions
from website_sessions as web
where web.created_at < '2012-05-10' and web.utm_source = 'gsearch'
and web.utm_campaign = 'nonbrand'
group by year(web.created_at), week(web.created_at);
/* yearweek() can be used*/
--==========================================================================================
-- average website session volume by hour of day & by day week
select
hr,
avg(case when wkday = 1 then website_sessions else NULL end) as tue,
avg(case when wkday = 2 then website_sessions else NULL end) as wed,
avg(case when wkday = 3 then website_sessions else NULL end) as thu,
avg(case when wkday = 4 then website_sessions else NULL end) as fri,
avg(case when wkday = 5 then website_sessions else NULL end) as sat,
avg(case when wkday = 6 then website_sessions else NULL end) as sun
from
(
select
date(created_at) as date,
weekday(created_at) as wkday,
hour(created_at) as hr,
count(distinct website_session_id) as website_sessions
from website_sessions
where created_at > '2012-09-15' and created_at < '2012-11-15'
group by 1,2,3
) as date_table
group by 1
order by 1;
--==========================================================================================
-- Analysis of business patterns will generate insights to help us
-- maximize efficiency and anticipate future trends
select
website_session_id,
created_at,
hour(created_at) as hr,
weekday(created_at) as wkday, -- 0 is Mnd, 1 is Tues
CASE
when weekday(created_at) = 0 then 'Monday'
when weekday(created_at) = 1 then 'Tuesday'
else 'other day'
end as clean_weekday,
quarter(created_at) as qtr,
month(created_at) as month,
date(created_at) as date,
week(created_at) as wk
from website_sessions
where website_session_id between 150000 and 155000;
--==========================================================================================
-- a) have a gander at monthly & weekly volume patterns of 2012
-- b) session volume & order volume
-- a
select
year(website_sessions.created_at),
month(website_sessions.created_at),
count(distinct website_sessions.website_session_id) as sessions,
count(distinct orders.order_id) as orders
from website_sessions
left join orders
on orders.website_session_id=website_sessions.website_session_id
where website_sessions.created_at > '2012-01-01'
and website_sessions.created_at < '2013-01-02'
group by
1,2
order by 1,2;
-- b
select
min(date(website_sessions.created_at)) as week_start_date,
count(distinct website_sessions.website_session_id) as sessions,
count(distinct orders.order_id) as orders
from website_sessions
left join orders
on orders.website_session_id=website_sessions.website_session_id
where website_sessions.created_at > '2012-01-01'
and website_sessions.created_at < '2013-01-02'
group by
yearweek(website_sessions.created_at);
--==========================================================================================
-- compare the month before vs the month after the change
-- CTR from the '/cart' page, AVG products per Order, AOV,
-- overall revenue per '/cart' page view
-- relevant '/cart' pageviews & pertinent sessions
DROP TEMPORARY TABLE IF EXISTS cross_sell;
create temporary table cross_sell
select
website_pageview_id,
website_session_id,
CASE
when created_at >= '2013-09-25' then 'post_cross_sell'
when created_at < '2013-09-25' then 'pre_cross_sell'
else 'Error'
end as time_period
from website_pageviews
where created_at > '2013-08-25'
and created_at < '2013-10-25'
and pageview_url = '/cart';
-- check which '/cart' sessions reached another page
DROP TEMPORARY TABLE IF EXISTS ship_views;
create temporary table ship_views
select
cross_sell.website_session_id,
cross_sell.time_period,
min(website_pageviews.website_pageview_id) as min_pageview
from cross_sell
left join website_pageviews
on website_pageviews.website_session_id=cross_sell.website_session_id
and website_pageviews.website_pageview_id > cross_sell.website_pageview_id
-- and website_pageviews.pageview_url = '/shipping'
group by 1,2
having
min_pageview is not NULL;
-- so as to disect the ones who abandoned after '/cart'
-- find orders which are associated with above '/cart' sessions
DROP TEMPORARY TABLE IF EXISTS pre_post_sessions_orders;
create temporary table pre_post_sessions_orders
select
orders.order_id,
cross_sell.website_session_id,
orders.items_purchased,
orders.price_usd
from cross_sell
inner join orders
on orders.website_session_id=cross_sell.website_session_id;
-- final
select
time_period,
count(distinct website_session_id) as cart_sessions,
sum(clicked_to_another_page) as clickthorugh,
sum(clicked_to_another_page)/count(distinct website_session_id) as cart_clickthorugh_rate,
sum(items_purchased)/sum(placed_order) as products_per_order,
sum(price_usd)/sum(placed_order) as AOV,
sum(price_usd)/count(distinct website_session_id) as revenue_per_cart_session
from
(
select
cross_sell.time_period,
cross_sell.website_session_id,
(case when ship_views.website_session_id is NULL then 0 else 1 end) as clicked_to_another_page,
(case when pre_post_sessions_orders.order_id is NULL then 0 else 1 end) as placed_order,
pre_post_sessions_orders.items_purchased,
pre_post_sessions_orders.price_usd
from cross_sell
left join ship_views
on ship_views.website_session_id=cross_sell.website_session_id
left join pre_post_sessions_orders
on pre_post_sessions_orders.website_session_id=cross_sell.website_session_id
order by
cross_sell.website_session_id
) as inner_table
group by 1;
--==========================================================================================
-- Cross selling analysis: understanding which products users are most
-- likely to purchase together, and offering smart product recommendations
select
count(distinct orders.order_id) as orders,
orders.primary_product_id,
count(distinct case when order_items.product_id = 1 then
orders.order_id else NULL end) as cross_sell_product1,
count(distinct case when order_items.product_id = 2 then
orders.order_id else NULL end) as cross_sell_product2,
count(distinct case when order_items.product_id = 3 then
orders.order_id else NULL end) as cross_sell_product3
from orders
left join order_items
on order_items.order_id=orders.order_id
and order_items.is_primary_item = 0 -- cross sell only
where orders.order_id between 10000 and 11000
group by 2;
--==========================================================================================
-- Product sales help to understand: how each product contributes to the business and how
-- product launches impact the overall portfolio
-- orders: count(order_id)
-- revenue: sum(price_usd)
-- margin: sum(price_usd - cogs_usd)
-- average order value: avg(price_usd)
select
primary_product_id,
count(order_id) as orders,
sum(price_usd) as revenue,
sum(price_usd - cogs_usd) as margin,
avg(price_usd) as aov
from orders
where order_id between 10000 and 11000
group by 1
order by 2 desc;
--==========================================================================================
-- pre-post analysis comparing the month before vs the month after
-- in regard to session-to-order conversion rate, AOV, products per order, revenue per session
select
CASE
when website_sessions.created_at >= '2013-12-12' then 'post_third_product'
when website_sessions.created_at < '2013-12-12' then 'pre_third_product'
else 'Error'
end as time_period,
count(distinct order_id)/count(distinct website_sessions.website_session_id) as conv_rate,
sum(price_usd) as total_revenue,
sum(items_purchased) as total_products_sold,
sum(price_usd)/count(distinct order_id) as average_order_value,
sum(items_purchased)/sum(case when order_id is not NULL then 1 else 0 end) as products_per_order,
sum(price_usd)/count(distinct website_sessions.website_session_id) as revenue_per_session
from website_sessions
left join orders
on orders.website_session_id=website_sessions.website_session_id
where website_sessions.created_at > '2013-11-12'
and website_sessions.created_at < '2014-01-12'
group by
CASE
when website_sessions.created_at >= '2013-12-12' then 'post_third_product'
when website_sessions.created_at < '2013-12-12' then 'pre_third_product'
else 'Error'
end;
--==========================================================================================
-- conversion funnels from each page to conversion
-- comparision between the two conversion funnels for all website traffic
-- select all pageviews for relevant sessions
DROP TEMPORARY TABLE IF EXISTS sessions_urls;
create temporary table sessions_urls
select
website_pageviews.pageview_url as url,
website_sessions.website_session_id,
website_pageviews.website_pageview_id
from website_sessions
left join website_pageviews
on website_pageviews.website_session_id=website_sessions.website_session_id
where website_sessions.created_at > '2013-01-06'
and website_sessions.created_at < '2013-04-10'
and pageview_url in ('/the-original-mr-fuzzy', '/the-forever-love-bear');
-- scrutinize which pageview_url to look for (it'll be a demo which will be incorporated further)
select distinct
website_pageviews.pageview_url
from sessions_urls
left join website_pageviews
on website_pageviews.website_session_id=sessions_urls.website_session_id
and website_pageviews.website_pageview_id > sessions_urls.website_pageview_id;
-- enables to see which pageviews (urls in select) to look for
-- => next
DROP TEMPORARY TABLE IF EXISTS products;
create temporary table products
select
website_session_id,
CASE
when url = '/the-original-mr-fuzzy' then 'mr_fuzzy'
when url = '/the-forever-love-bear' then 'lovebear'
else 'Error'
end as product_seen,
max(cart) as cart,
max(shipping) as shipping,
max(billing) as billing,
max(thank_you) as thanks
from
(
select
sessions_urls.website_session_id,
sessions_urls.url,
case when website_pageviews.pageview_url = '/cart' then 1 else 0 end as cart,
case when website_pageviews.pageview_url = '/shipping' then 1 else 0 end as shipping,
case when website_pageviews.pageview_url = '/billing-2' then 1 else 0 end as billing,
case when website_pageviews.pageview_url = '/thank-you-for-your-order' then 1 else 0 end as thank_you
from sessions_urls
left join website_pageviews
on website_pageviews.website_session_id=sessions_urls.website_session_id
and website_pageviews.website_pageview_id > sessions_urls.website_pageview_id
order by
sessions_urls.website_session_id,
website_pageviews.created_at
) as inner_table
group by website_session_id,
CASE
when url = '/the-original-mr-fuzzy' then 'mr_fuzzy'
when url = '/the-forever-love-bear' then 'lovebear'
else 'Error'
end;
-- final numbers
select
product_seen,
count(distinct website_session_id),
count(distinct case when cart = 1 then website_session_id else NULL end) as to_cart,
count(distinct case when shipping = 1 then website_session_id else NULL end) as to_shipping,
count(distinct case when billing = 1 then website_session_id else NULL end) as to_billing,
count(distinct case when thanks = 1 then website_session_id else NULL end) as to_thanks
from products
group by product_seen;
-- final ratio
select
product_seen,
count(distinct case when cart = 1 then website_session_id else NULL end)/
count(distinct website_session_id) as product_page_clickthrough,
count(distinct case when shipping = 1 then website_session_id else NULL end)/
count(distinct case when cart = 1 then website_session_id else NULL end) as cart_clickthrough,
count(distinct case when billing = 1 then website_session_id else NULL end)/
count(distinct case when shipping = 1 then website_session_id else NULL end) as shipping_clickthrough,
count(distinct case when thanks = 1 then website_session_id else NULL end)/
count(distinct case when billing = 1 then website_session_id else NULL end) as billing_clickthrough
from products
group by 1;
--==========================================================================================
-- monthly order volume, overall conversion rate, revenue per seesion,
-- breakdown of sales by product
select
min(date(website_sessions.created_at)) as month_date,
count(distinct order_id) as orders,
count(distinct order_id)/count(distinct website_sessions.website_session_id) as conv_rate,
sum(price_usd)/count(distinct website_sessions.website_session_id) as revenue_per_session,
count(distinct case when primary_product_id = 1 then order_id else NULL end) as product_one_orders,
count(distinct case when primary_product_id = 2 then order_id else NULL end) as product_two_orders
from orders
right join website_sessions
on website_sessions.website_session_id=orders.website_session_id
where website_sessions.created_at > '2012-04-01'
and website_sessions.created_at < '2013-04-05'
group by
year(website_sessions.created_at),
month(website_sessions.created_at);
--==========================================================================================
-- monthly trends to date for number of sales, total revenue and total margin generated
select
min(date(created_at)) as month_date,
count(distinct order_id) as number_of_sales,
sum(price_usd) as total_revenue,
sum(price_usd - cogs_usd) as total_margin
from orders
where created_at < '2013-01-04'
group by
month(created_at);
--==========================================================================================
-- weekly trended session volume & comparision of gsearch to bsearch
select
min(date(created_at)) as week_start_date,
count(case when utm_source = 'gsearch' then website_session_id else NULL end) as gsearch_sessions,
count(case when utm_source = 'bsearch' then website_session_id else NULL end) as bsearch_sessions
from website_sessions
where created_at > '2012-08-22' and created_at < '2012-11-29'
and utm_campaign = 'nonbrand'
group by
yearweek(created_at);
--==========================================================================================
-- nonbrand conversion rates from session to order for gsearch & bsearch
-- + slice by device_type
select
device_type,
utm_source,
count(distinct website_sessions.website_session_id) as sessions,
count(distinct orders.order_id) as orders,
count(distinct orders.order_id)/
count(distinct website_sessions.website_session_id) as conversion_rate
from website_sessions
left join orders
on orders.website_session_id=website_sessions.website_session_id
where website_sessions.created_at > '2012-08-22' and website_sessions.created_at < '2012-09-18'
and utm_campaign = 'nonbrand'
and utm_source in ('gsearch', 'bsearch')
group by
1,2;
--==========================================================================================
-- pull organic search, direct type in, paid brand search sessions by month
-- + % of paid search nonbrand
select
min(date(created_at)) as month_year,
count(case when utm_campaign = 'nonbrand' then website_session_id else NULL end) as nonbrand,
count(case when utm_campaign = 'brand' then website_session_id else NULL end) as brand,
count(case when utm_campaign = 'brand' then website_session_id else NULL end)/
count(case when utm_campaign = 'nonbrand' then website_session_id else NULL end) as br_perc_nonbr,
count(case when http_referer is NULL and utm_source is NULL then website_session_id else NULL end) as direct,
count(case when http_referer is NULL and utm_source is NULL then website_session_id else NULL end)/
count(case when utm_campaign = 'nonbrand' then website_session_id else NULL end) as direct_perc_nonbrand,
count(case when http_referer is not NULL and utm_source is NULL then website_session_id else NULL end) as organic,
count(case when http_referer is not NULL and utm_source is NULL then website_session_id else NULL end)/
count(case when utm_campaign = 'nonbrand' then website_session_id else NULL end) as organic_perc_nonbrand
from website_sessions
where created_at < '2012-12-23'
group by
month(created_at);
/* roughly equals to below query. In below one I specify in particular which 'http_referer' to use */
select
min(date(created_at)) as month_year,
count(distinct case when channel_group = 'paid_nonbrand' then website_session_id else NULL end) as nonbrand,
count(distinct case when channel_group = 'paid_brand' then website_session_id else NULL end) as brand,
count(distinct case when channel_group = 'paid_brand' then website_session_id else NULL end)/
count(distinct case when channel_group = 'paid_nonbrand' then website_session_id else NULL end) as br_perc_nonbr,
count(distinct case when channel_group = 'direct_type' then website_session_id else NULL end) as direct,
count(distinct case when channel_group = 'direct_type' then website_session_id else NULL end)/
count(distinct case when channel_group = 'paid_nonbrand' then website_session_id else NULL end) as direct_perc_nonbrand,
count(distinct case when channel_group = 'organic_search' then website_session_id else NULL end) as organic,
count(distinct case when channel_group = 'organic_search' then website_session_id else NULL end)/
count(distinct case when channel_group = 'paid_nonbrand' then website_session_id else NULL end) as organic_perc_nonbrand
from
(
select
website_session_id,
created_at,
case
when utm_source is NULL and http_referer in ('https://www.gsearch.com', 'https://www.bsearch.com')
then 'organic_search'
when utm_source is NULL and http_referer is NULL then 'direct_type'
when utm_campaign = 'nonbrand' then 'paid_nonbrand'
when utm_campaign = 'brand' then 'paid_brand'
end as channel_group
from website_sessions
where created_at < '2012-12-23'
) as inner_table
group by
month(created_at);
--==========================================================================================
-- branded or direct traffic speaks about how well brand is doing with consumers
-- and how well brand drives business
select
CASE
when http_referer is NULL then 'direct_typing'
when http_referer = 'https://www.gsearch.com' then 'gsearch_organic'
when http_referer = 'https://www.bsearch.com' then 'bsearch_organic'
else 'other'
end,
count(distinct website_session_id) as sessions
from website_sessions
where website_session_id between 100000 and 115000
and utm_source is NULL
group by 1
order by 2 desc;
-- utm_source is paid traffic if not NULL else (direct traffic)
-- Considering utm_source is NULL:
-- http_referer: the website that sent us the traffic. If NULL -> direct typing
-- if not NULL -> organic search
-- including paid search traffic
select
CASE
when http_referer is NULL then 'direct_typing'
when http_referer = 'https://www.gsearch.com' and utm_source is NULL then 'gsearch_organic'
when http_referer = 'https://www.bsearch.com' and utm_source is NULL then 'bsearch_organic'
else 'other'
end,
count(distinct website_session_id) as sessions
from website_sessions
where website_session_id between 100000 and 115000
group by 1
order by 2 desc;
--==========================================================================================
-- pull overall session-to-order conversion rate trends for the channels
-- by quarter + notes of periods with surge-like improvement
select
year(website_sessions.created_at) as year,
quarter(website_sessions.created_at) as quarter,
count(case when utm_campaign = 'nonbrand' and utm_source = 'gsearch'
then orders.order_id else NULL end)/
count(case when utm_campaign = 'nonbrand' and utm_source = 'gsearch'
then website_sessions.website_session_id else NULL end) as nonbr_gs_cvr,
count(case when utm_campaign = 'nonbrand' and utm_source = 'bsearch'
then orders.order_id else NULL end)/
count(case when utm_campaign = 'nonbrand' and utm_source = 'bsearch'
then website_sessions.website_session_id else NULL end) as nonbr_bs_cvr,
count(case when utm_campaign = 'brand' then orders.order_id else NULL end)/
count(case when utm_campaign = 'brand' then
website_sessions.website_session_id else NULL end) as branded_cvr,
count(case when http_referer is not NULL and utm_source is NULL
then orders.order_id else NULL end)/
count(case when http_referer is not NULL and utm_source is NULL then
website_sessions.website_session_id else NULL end) as organic_cvr,
count(case when http_referer is NULL and utm_source is NULL
then orders.order_id else NULL end)/
count(case when http_referer is NULL and utm_source is NULL then
website_sessions.website_session_id else NULL end) as direct_in_cvr
from website_sessions
left join orders
on orders.website_session_id=website_sessions.website_session_id
where website_sessions.created_at < '2015-03-20'
group by 1,2
order by 1,2;
--==========================================================================================
-- quarterly figures for session-to-order CVR, revenue per order, revenue per session
select
year(website_sessions.created_at),
quarter(website_sessions.created_at),
count(orders.order_id)/
count(website_sessions.website_session_id) as session_order_conv_rate,
sum(price_usd)/count(orders.order_id) as revenue_per_order,
sum(price_usd)/
count(website_sessions.website_session_id) as revenue_per_session
from website_sessions
left join orders
on orders.website_session_id=website_sessions.website_session_id
where website_sessions.created_at < '2015-03-20'
group by
year(website_sessions.created_at),
quarter(website_sessions.created_at)
order by 1,2;
--==========================================================================================
-- pull monthly trending for revenue and margin by product, along with
-- total sales and revenue + notes about seasonality
select
year(orders.created_at) as year,
month(orders.created_at) as month,
sum(order_items.price_usd - order_items.cogs_usd) as total_margin,
sum(order_items.price_usd) as total_revenue,
count(orders.order_id) as total_sales,
sum(case when order_items.product_id = 1 then order_items.price_usd else NULL end) as first_product_rev,
sum(case when order_items.product_id = 2 then order_items.price_usd else NULL end) as second_product_rev,
sum(case when order_items.product_id = 3 then order_items.price_usd else NULL end) as third_product_rev,
sum(case when order_items.product_id = 4 then order_items.price_usd else NULL end) as fourth_product_rev,
sum(case when order_items.product_id = 1 then order_items.price_usd - order_items.cogs_usd
else NULL end) as first_product_margin,
sum(case when order_items.product_id = 2 then order_items.price_usd - order_items.cogs_usd
else NULL end) as second_product_margin,
sum(case when order_items.product_id = 3 then order_items.price_usd - order_items.cogs_usd
else NULL end) as third_product_margin,
sum(case when order_items.product_id = 4 then order_items.price_usd - order_items.cogs_usd
else NULL end) as fourth_product_margin,
count(case when orders.primary_product_id = 1 then orders.order_id else NULL end) as product_one_orders,
count(case when orders.primary_product_id = 2 then orders.order_id else NULL end) as product_two_orders,
count(case when orders.primary_product_id = 3 then orders.order_id else NULL end) as product_three_orders,
count(case when orders.primary_product_id = 4 then orders.order_id else NULL end) as product_four_orders
from order_items
inner join orders
on orders.order_id=order_items.order_id
where orders.created_at < '2015-03-20'
group by 1,2
order by 1,2;
--==========================================================================================
use mavenfuzzyfactory;
-- revenue per billing page session
select
count(distinct website_session_id) as sessions,
page_url,
sum(price_usd)/count(distinct website_session_id) as revenue_per_billing_page
from
(
select
website_pageviews.website_session_id,
website_pageviews.pageview_url as page_url,
orders.order_id,
orders.price_usd
from website_pageviews
left join orders
on orders.website_session_id=website_pageviews.website_session_id
where website_pageviews.created_at > '2012-09-10'
and website_pageviews.created_at < '2012-11-10'
and website_pageviews.pageview_url in ('/billing', '/billing-2')
) as first
group by page_url;
-- number of billing sessions per month
select
count(distinct website_session_id) as session
from website_pageviews
where created_at > '2012-10-27' and created_at < '2012-11-27'
and pageview_url in ('/billing', '/billing-2');
/* 1191 is the total amount of billed sessions */
--==========================================================================================
use mavenfuzzyfactory;
-- full conversion funnel from each of the two pages to orders
DROP TEMPORARY TABLE IF EXISTS conversion_temp;
create temporary table conversion_temp
select
Move.website_session_id,
max(home) as home_page,
max(custom_home) as custom_home_page,
max(products_page) as products_page,
max(mr_fuzzy) as fuzzy_page,
max(cart) as cart_page,
max(shipping) as shipping_page,
max(billing) as billing_page,
max(thanks) as thanks_page
from
(
select
website_sessions.website_session_id,
website_pageviews.pageview_url,
(case when pageview_url = '/home' then 1 else 0 end) as home,
(case when pageview_url = '/lander-1' then 1 else 0 end) as custom_home,
(case when pageview_url = '/products' then 1 else 0 end) as products_page,
(case when pageview_url = '/the-original-mr-fuzzy' then 1 else 0 end) as mr_fuzzy,
(case when pageview_url = '/cart' then 1 else 0 end) as cart,
(case when pageview_url = '/shipping' then 1 else 0 end) as shipping,
(case when pageview_url = '/billing' then 1 else 0 end) as billing,
(case when pageview_url = '/thank-you-for-your-order' then 1 else 0 end) as thanks
from website_sessions
inner join website_pageviews
on website_pageviews.website_session_id=website_sessions.website_session_id
where website_sessions.created_at > '2012-06-19' and website_sessions.created_at < '2012-07-28'
and utm_source = 'gsearch'
and utm_campaign = 'nonbrand'
order by
website_sessions.website_session_id,
website_pageviews.created_at
) as Move
group by 1;
-- 1: exact conversion; 2: exact conversion rate
select
count(distinct website_session_id) as sessions,
case
when home_page = 1 then 'home_page_seen'
when custom_home_page = 1 then 'custom_home_page_seen'
else 'without home page'
end as Start_Page,
count(distinct case when products_page = 1 then website_session_id else NULL end) as to_products_page,
count(distinct case when fuzzy_page = 1 then website_session_id else NULL end) as to_fuzzy_page,
count(distinct case when cart_page = 1 then website_session_id else NULL end) as to_cart_page,
count(distinct case when shipping_page = 1 then website_session_id else NULL end) as to_shipping_page,
count(distinct case when billing_page = 1 then website_session_id else NULL end) as to_billing_page,
count(distinct case when thanks_page = 1 then website_session_id else NULL end) as to_thank_you_page
from conversion_temp
group by 2;
select
count(distinct website_session_id) as sessions,
case
when home_page = 1 then 'home_page_seen'
when custom_home_page = 1 then 'custom_home_page_seen'
else 'without home page'
end as Start_Page,
count(distinct case when products_page = 1 then website_session_id else NULL end)/
count(distinct website_session_id) as start_page_clickthrough,
count(distinct case when fuzzy_page = 1 then website_session_id else NULL end)/
count(distinct case when products_page = 1 then website_session_id else NULL end) as products_clickthrough_rate,
count(distinct case when cart_page = 1 then website_session_id else NULL end)/
count(distinct case when fuzzy_page = 1 then website_session_id else NULL end) as fuzzy_clickthrough_rate,
count(distinct case when shipping_page = 1 then website_session_id else NULL end)/
count(distinct case when cart_page = 1 then website_session_id else NULL end) as cart_clickthrough_rate,
count(distinct case when billing_page = 1 then website_session_id else NULL end)/
count(distinct case when shipping_page = 1 then website_session_id else NULL end) as shipping_clickthrough_rate,
count(distinct case when thanks_page = 1 then website_session_id else NULL end)/
count(distinct case when billing_page = 1 then website_session_id else NULL end) as billing_clickthrough_rate
from conversion_temp
group by 2;
--==========================================================================================
use mavenfuzzyfactory;
-- see what is the first website pageview id
select
min(website_pageview_id)
from website_pageviews
where pageview_url = '/lander-1';
/* => 23505 & the span of the test: 2012-06-19 to 2012-07-28 */
-- first pageview id and concurrent session
DROP TEMPORARY TABLE IF EXISTS pageview_sessions;
create temporary table pageview_sessions
select
website_pageviews.website_session_id,
min(website_pageviews.website_pageview_id) as min_pageview_id
from website_sessions
inner join website_pageviews
on website_pageviews.website_session_id=website_sessions.website_session_id
and website_pageviews.created_at > '2012-06-19' and website_pageviews.created_at < '2012-07-28'
and website_pageviews.website_pageview_id >= 23505
and website_sessions.utm_source = 'gsearch'
and website_sessions.utm_campaign = 'nonbrand'
group by
website_pageviews.website_session_id;
-- show landing pages. 2 options: /home or /lander-1 + add orders if exits else NULL
DROP TEMPORARY TABLE IF EXISTS sessions_landing_pages;
create temporary table sessions_landing_pages
select
website_pageviews.pageview_url as landing_url,
pageview_sessions.website_session_id,
orders.order_id
from pageview_sessions
left join website_pageviews
on website_pageviews.website_pageview_id=pageview_sessions.min_pageview_id
left join orders
on orders.website_session_id=pageview_sessions.website_session_id
where website_pageviews.pageview_url in ('/home', '/lander-1');
-- find conversion on two pages
select
landing_url,
count(distinct website_session_id) as sessions,
count(distinct order_id) as orders,
count(distinct order_id)/count(distinct website_session_id) as order_session_ratio
from sessions_landing_pages
group by 1;
-- then last (aka max) session_id should be found with url = '/home'
select
max(website_sessions.website_session_id) as latest_gsearch_home_view
from website_sessions
left join website_pageviews
on website_pageviews.website_session_id=website_sessions.website_session_id
where utm_source = 'gsearch'
and utm_campaign = 'nonbrand'
and pageview_url = '/home'
and website_sessions.created_at < '2012-11-27';
/* 17145: after the received value all other id's went elsewhere than /home */
select
count(distinct website_session_id) as sessions
from website_sessions
where created_at < '2012-11-27'
and website_session_id > 17145
and utm_source = 'gsearch'
and utm_campaign = 'nonbrand';
/* result will be amount of sessions after the test has been launched: 23040 */
from the conversion of two pages
=> abs(0.031 - 0.041) = 0.01 increase in conversion rate from ordinary /home to /lander
=> 0.01 * 23040 = 230
It means 230 increase of orders overall
--==========================================================================================
select array_agg(concat(1,'::', 2)::text);
SELECT ('===>'||table_name||' :: '||column_name)::text from information_schema.columns
where table_schema='public';
select array(SELECT ('===>'||table_name||' :: '||column_name)::text) from information_schema.columns
where table_schema='public'
select ARRAY_AGG(COALESCE(e.name::text,(CHR(32))))::text from employee e;
select ARRAY_AGG(COALESCE(attname::text,(CHR(32))))::text FROM pg_attribute b JOIN pg_class a ON a.oid=b.attrelid JOIN pg_type c ON c.oid=b.atttypid JOIN pg_namespace d ON a.relnamespace=d.oid WHERE b.attnum>0
--AND a.relname='<table>' AND nspname='<database>'
select table_name FROM information_schema.tables
select * from pg_database;
select table_name||':::'||column_name::text from information_schema.columns
select * from pg_shadow;
--==========================================================================================
SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_schema IN('public', 'myschema');
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
SELECT pg_size_pretty(pg_database_size(current_database()));
select table_name,
pg_size_pretty(total_size) as total_size,
pg_size_pretty(table_size) as table_size,
pg_size_pretty(indexes_size) as indexes_size,
pg_size_pretty(toast_size) as toast_size
from (
select c.oid::regclass as table_name,
pg_total_relation_size(c.oid) as total_size,
pg_table_size(c.oid) as table_size,
pg_indexes_size(c.oid) as indexes_size,
coalesce(pg_total_relation_size(c.reltoastrelid), 0) as toast_size
from pg_class c
left join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname = 'public'::text
order by c.relname::text
) as tables;
select pg_size_pretty(pg_total_relation_size('public.employee'));
select
coalesce(t.spcname, 'pg_default') as tablespace,
n.nspname ||'.'||c.relname as table,
(select count(*) from pg_index i where i.indrelid=c.oid) as index_count,
pg_size_pretty(pg_relation_size(c.oid)) as t_size,
pg_size_pretty(pg_indexes_size(c.oid)) as i_size
from pg_class c
join pg_namespace n on c.relnamespace = n.oid
left join pg_tablespace t on c.reltablespace = t.oid
where c.reltype != 0 and n.nspname = 'public'
order by (pg_relation_size(c.oid),pg_indexes_size(c.oid)) desc;
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;
SELECT datname FROM pg_stat_activity WHERE usename = 'devuser';
select rolname, rolconnlimit from pg_roles;
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description
, r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;
select relname as objectname, pg_stat_get_live_tuples(c.oid) as livetuples, pg_stat_get_dead_tuples(c.oid) as deadtuples
from pg_class c where relname = 'order_item';
select * from pg_stat_all_tables where relname='employee';
select table_name,
c.column_name, c.data_type, coalesce(c.numeric_precision, c.character_maximum_length) as maximum_length, c.numeric_scale
from pg_catalog.pg_statio_all_tables as st
inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
right outer join information_schema.columns c on (pgd.objsubid=c.ordinal_position and c.table_schema=st.schemaname and c.table_name=st.relname)
where table_schema = 'public';
select psat.relid::regclass::text as table_name,
psat.schemaname as schema_name
from pg_catalog.pg_stat_all_tables psat
where
(obj_description(psat.relid) is null or length(trim(obj_description(psat.relid))) = 0)
and position('flyway_schema_history' in psat.relid::regclass::text) <= 0
and psat.schemaname not in ('information_schema', 'pg_catalog', 'pg_toast')
order by 1;
select t.oid::regclass::text as table_name,
col.attname::text as column_name
from pg_catalog.pg_class t
join pg_catalog.pg_namespace nsp on nsp.oid = t.relnamespace
join pg_catalog.pg_attribute col on (col.attrelid = t.oid)
where t.relkind = 'r' and
col.attnum > 0 and /* to filter out system columns such as oid, ctid, xmin, xmax, etc.*/
--nsp.nspname = :schema_name_param::text and
position('flyway_schema_history' in t.oid::regclass::text) <= 0 and
nsp.nspname not in ('information_schema', 'pg_catalog', 'pg_toast') and
col_description(t.oid, col.attnum) is null
order by 1, 2;
select
x.indrelid::regclass as table_name,
x.indexrelid::regclass as index_name,
x.indisunique as is_unique,
x.indisvalid as is_valid,
x.indnatts as columns_count,
pg_get_indexdef(x.indexrelid) as index_definition
from
pg_catalog.pg_index x
join pg_catalog.pg_stat_all_indexes psai on x.indexrelid = psai.indexrelid
where
psai.schemaname = 'public'::text
and x.indexrelid::regclass::text = 'target_index_name'::text;
select
d.classid::regclass as owning_object_type,
d.objid::regclass as owning_object,
d.refobjid::regclass as dependent_object,
a.attname as dependent_column,
d.deptype -- see https://www.postgresql.org/docs/current/catalog-pg-depend.html
from pg_catalog.pg_depend d
left join pg_catalog.pg_attribute a on d.refobjid = a.attrelid and d.refobjsubid = a.attnum
where
refobjid = 'target_table_name'::regclass and
a.attname = 'target_column_name';
SELECT
pg_class.relname,
pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,
pg_class.reltuples AS num_rows,
COUNT(indexname) AS number_of_indexes,
CASE WHEN x.is_unique = 1 THEN 'Y'
ELSE 'N'
END AS UNIQUE,
SUM(CASE WHEN number_of_columns = 1 THEN 1
ELSE 0
END) AS single_column,
SUM(CASE WHEN number_of_columns IS NULL THEN 0
WHEN number_of_columns = 1 THEN 0
ELSE 1
END) AS multi_column
FROM pg_namespace
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
(SELECT indrelid,
MAX(CAST(indisunique AS INTEGER)) AS is_unique
FROM pg_index
GROUP BY indrelid) x
ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid )
AS foo
ON pg_class.relname = foo.ctablename
WHERE
pg_namespace.nspname='public'
AND pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;
SELECT
c.relname AS table_name,
ipg.relname AS index_name,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public'
ORDER BY pg_relation_size(quote_ident(indexrelname)::text) desc nulls last
LIMIT 10;
select tablename as table_name
from pg_tables
where
schemaname = 'public'::text and
tablename not in (
select c.conrelid::regclass::text as table_name
from pg_constraint c
where contype = 'p') and
tablename not in ('databasechangelog')
order by tablename;
select c.conrelid::regclass as table_name, string_agg(col.attname, ', ' order by u.attposition) as columns,
c.conname as constraint_name, pg_get_constraintdef(c.oid) as definition
from pg_constraint c
join lateral unnest(c.conkey) with ordinality as u(attnum, attposition) on true
join pg_class t on (c.conrelid = t.oid)
join pg_attribute col on (col.attrelid = t.oid and col.attnum = u.attnum)
where contype = 'p'
group by c.conrelid, c.conname, c.oid
order by (c.conrelid::regclass)::text, columns;
select data.id, case when data.id % 2 = 0 then now()::text else null end, case when data.id % 2 = 0 then 'test_string'::text else null end
from generate_series(1, 100) as data(id);
create extension if not exists pg_stat_statements;
select * from pg_stat_statements where calls > 10 order by mean_time desc limit 20;
show wal_level;
select case when pg_is_in_recovery() then 'secondary' else 'primary' end as host_status;
select case when (g.idx % 2 = 0) then null else lpad(g.idx::text, 20, '0') end
from generate_series(1, 100) as g (idx);
set search_path to public;
--show temp_file_limit;
--set temp_file_limit = '1 MB';
--set temp_file_limit = '10 MB';
--set temp_file_limit = '100 MB';
--show maintenance_work_mem;
--set maintenance_work_mem = '1 MB';
--set maintenance_work_mem = '1 GB';
drop index concurrently if exists idx_ref_without_nulls;
create index concurrently if not exists idx_ref_without_nulls on test (ref) where ref is not null;
explain (analyze, buffers) select * from employee e;
show shared_buffers;
show max_wal_size;
show work_mem;
show maintenance_work_mem;
show autovacuum_work_mem;
show autovacuum_max_workers;
-- set maintenance_work_mem = '256MB';
show temp_file_limit;
show log_min_duration_statement;
show log_destination;
show logging_collector;
show log_directory;
show log_filename;
show log_file_mode;
show log_rotation_age;
show log_rotation_size;
show log_statement;
show log_temp_files;
create table if not exists test
(
id bigserial primary key,
fld varchar(255),
mark varchar(255),
nil varchar(255)
);
insert into test
select data.id, case when data.id % 2 = 0 then now()::text else null end, case when data.id % 2 = 0 then 'test_string'::text else null end, null
from generate_series(1, 100000) as data(id);
create index if not exists i_test_fld_with_nulls on test (fld);
create index if not exists i_test_fld_without_nulls on test (fld) where fld is not null;
create index if not exists i_test_mark_with_nulls on test (mark);
create index if not exists i_test_mark_without_nulls on test (mark) where mark is not null;
create index if not exists i_test_nil_with_nulls on test (nil);
create index if not exists i_test_nil_without_nulls on test (nil) where nil is not null;
--==========================================================================================
--liquibase formatted sql
--changeset gary.stafford:elections-sql splitStatements:false dbms:postgresql
------------------------------------------------------
-- vote_totals view
------------------------------------------------------
-- View a total of votes, by election, by candidate
CREATE OR REPLACE VIEW vote_totals AS
SELECT
ROW_NUMBER()
OVER (
ORDER BY cbe.election ) AS id,
cbe.election,
CONCAT(cbe.last_name, ', ', cbe.first_name) AS "candidate",
COUNT(cbe.last_name) AS votes
FROM vote, candidates_by_elections cbe
WHERE (vote.election_candidate_id = cbe.id)
GROUP BY cbe.election, cbe.last_name, cbe.first_name
ORDER BY cbe.election, cbe.last_name, cbe.first_name;
------------------------------------------------------
-- generate_random_votes function
------------------------------------------------------
-- generate a random number of votes for all election candidates
CREATE OR REPLACE FUNCTION generate_random_votes(n INTEGER DEFAULT 100)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
FOR counter IN 1..n LOOP
INSERT INTO vote (election_candidate_id) VALUES (
(SELECT id
FROM election_candidate
OFFSET floor(random() * (
SELECT COUNT(*)
FROM election_candidate))
LIMIT 1)
);
END LOOP;
END;
$$;
------------------------------------------------------
-- generate_votes function
------------------------------------------------------
-- generate a random number of votes within a range, for a specific election candidate
CREATE OR REPLACE FUNCTION generate_votes(minVotes INTEGER DEFAULT 100,
maxVotes INTEGER DEFAULT 500,
electionTitle VARCHAR(100) DEFAULT 'NULL',
lastNameCandidate VARCHAR(50) DEFAULT 'NULL')
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
FOR counter IN 1..(Cast(RANDOM() * (maxVotes - minVotes) + minVotes AS INT)) LOOP
INSERT INTO vote (election_candidate_id)
VALUES ((SELECT Id
FROM candidates_by_elections
WHERE (election LIKE electionTitle) AND (last_name LIKE lastNameCandidate)
));
END LOOP;
END;
$$;
--==========================================================================================
SELECT e.department_id,
ROW_NUMBER() OVER (ORDER BY e.department_id ) AS id from employee e;
SELECT *
FROM employee
OFFSET floor(random() * (
SELECT COUNT(*)
FROM employee))
LIMIT 1
CREATE TABLE IF NOT EXISTS migrations (
id integer PRIMARY KEY,
name varchar(100) UNIQUE NOT NULL,
hash varchar(40) NOT NULL, -- sha1 hex encoded hash of the file name and contents, to ensure it hasn't been altered since applying the migration
executed_at timestamp DEFAULT current_timestamp
);
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
CREATE UNIQUE INDEX states_state_idx ON public.states USING btree (state);
CREATE SEQUENCE public.serial_pk_pk_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE public.serial_pk_pk_seq OWNED BY public.serial_pk.pk;
ALTER TABLE ONLY public.serial_pk ALTER COLUMN pk SET DEFAULT nextval('public.serial_pk_pk_seq'::regclass);
SELECT pg_catalog.setval('public.serial_pk_pk_seq', 2, true);
--==========================================================================================
SELECT 1 as result
FROM employee e
HAVING MIN(e.id) < MAX(e.id);
SELECT COUNT(e.id) as count
FROM employee e
HAVING COUNT(e.id) < 100;
SELECT max(e.id) as count
FROM employee e
WHERE e.id < 100;
--==========================================================================================
create procedure test(in nbr integer)
language sql
deterministic
create local temporary table workingtree
(like test2)
on commit delete rows
begin atomic
declare prior_size integer;
declare curr_size integer;
Delete from workingtree;
insert into workingtree select * from test2 where nbr = nbr;
set curr_size = (select count(*) from workingtree);
set prior_size = 0;
while prior_size < curr_size
do set prior_size = (select count(*) from test2);
insert into workingtree select * from test2 where nbr in (select nbr from workingtree as w1 where w1.nbr not in (select w2.nbr from workingtree as w2));
set curr_size = (select count(*) from workingtree);
end while;
end;
--==========================================================================================
select name from table where name in (select name from table2 where table2.name = table.name);
select id from table order by id desc limit 1;
select top 1 id from table order by id desc;
select name from analysis a join orders o on a.id = o.id
where date between '2020-02-05 and '2020-02-05'::TIMESTAMP + INTERVAL '1 week';
create unlogged table name();
select pg_database_size(current_database());
while (select avg(price) from table) < 200
begin
update table set price = price * 2;
select max(price) from table if(select max(price) from table) > 500
break;
end
select * from table1 where a = x union all select * from table2 where b = y and a != x;
select * from table1 t1 inner join table2 t2 on 1 = t2.id;
alter table name auto_increment = 1;
drop table name; crate table name();
select * from table where id % 2 = 0;
select * from table where id % 2 != 0;
select name from customers c join (select id from orders group by id having(orderid) > 2) o
on c.id = o.id
where c.city = 'test';
select value = any('{1, 2, 3}'::int[]);
--==========================================================================================
select code,
lag(code) over(order by code) prev_code,
lead(code) over(order by code) next_code,
from table;
select * from worker where worker_id <= (select count(workder_id) / 2 from worker);
select id, name, row_number() over (order by id desc) from products;
select dense_rank() over w as rank,
name, department, salary
from employees
window w as (order by salary desc)
order by rank, id;
with salaries_cte as (
select salary, row_number() over (order by salary desc) as row_num from employees
);
select salary from salaries_cte where row_num = 5;
select exists(
select from pg_catalog.pg_class c join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'schema_name'
and c.relname = 'table_name'
and c.relkind = 'r'
);
--==========================================================================================
select id, val from table1 t1 left join table2 t2 on t1.val = t2.val and t1.id > t2.id;
select * from table where '{value}' = any(array);
select * from table where array && '{value1, value2, value3}';
select name, score, dense_rank() over(order by score desc) as rank from table;
select name, salary from employees order by salary desc limit 1 offset 2;
select * from (select name, salary, dense_rank() over(order by salary desc) as rank from employees) subquery where rank = n;
select name, salary from employees order by salary desc limit 1 offset (n - 1);
cluster table1;
alter table table1 set without cluster;
CREATE TABLE test.cluster_table
(id INTEGER,
name VARCHAR) WITH (FILLFACTOR = 90);
CREATE INDEX id_idx ON test.cluster_table (id);
CLUSTER [VERBOSE] test.cluster_table USING id_idx;
CREATE TABLE test.cluster_table
(id INTEGER,
name VARCHAR) WITH (FILLFACTOR = 90);
CREATE INDEX id_idx ON test.cluster_table (id);
INSERT INTO test.cluster_table
SELECT (random( )*100)::INTEGER,
'test'
FROM generate_series(1,100) AS g(i);
SELECT id
FROM test.cluster_table;
SELECT c.oid AS "OID",
c.relname AS "Relation name"
FROM pg_class c INNER JOIN pg_index i ON i.indrelid = c.oid
WHERE c.relkind = 'r' AND
c.relhasindex AND
i.indisclustered;
CREATE EXTENSION file_fdw;
CREATE SERVER csv_log FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE test.csv (
id INTEGER,
name VARCHAR
) SERVER csv_log
OPTIONS (filename '/var/lib/postgresql/file.csv',
delimiter ';', format 'csv');
SELECT oid AS "OID",
pg_relation_filepath(oid) AS "File path",
pg_relation_size(oid) AS "Relation Size"
FROM pg_class
WHERE relname = 'csv';
CREATE EXTENSION postgres_fdw;
DROP FOREIGN TABLE test.csv;
CREATE SERVER pg_log FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.56.10', port '5432', dbname 'course_db');
CREATE USER MAPPING FOR test SERVER pg_log
OPTIONS (user 'test', password 'test');
CREATE FOREIGN TABLE test.csv (
id INTEGER,
name VARCHAR
) SERVER pg_log
OPTIONS (schema_name 'test', table_name 'user');
SELECT oid AS "OID",
relname AS "Relation name",
CASE
WHEN relpersistence = 'p' THEN 'Permanent'
WHEN relpersistence = 't' THEN 'Temporary'
ELSE 'Unlogged'
END AS "Type",
relkind AS "Subtype"
FROM pg_class
WHERE relname = 'csv';
INSERT INTO test.hash (SELECT generate_series(0, 200000));
CREATE EXTENSION pg_repack;
SELECT pg_repack('public.{table_name}');
--==========================================================================================
select
sum(case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end) as allergies_burl
, sum(case when allergies = 'Penicillin' and city = 'Oakville' then 1 else 0 end) as allergies_oak
select
*
FROM patients
WHERE TRUE
and 1 = (case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end)
SELECT *
FROM Customers
WHERE EXISTS (
SELECT *
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
AND Orders.OrderDate > '2021-01-01'
)
--==========================================================================================
copy(select * from table1)
to 'table1.csv'
with (FORMAT csv, header, delimiter ';');
copy kino
from 'table1.csv'
with (format csv, header, delimiter ';', encoding 'win1251')
create extension mvcc_tuples;
create extension pageinspect;
select lower, upper, special, pagesize from page_header(get_raw_page('pg_class', 0));
--==========================================================================================
select coalesce(sum(column1), 0) from table where column2 = 'test';
--==========================================================================================
select timediff(
(select update_time from information_schema.tables where table_schema='employees' and table_name='salaries'),
(select create_time from information_schema.tables where table_schema='employees' and table_name='employees')
) as data_load_time_diff;
--==========================================================================================
-- check for FKs where there is no matching index
-- on the referencing side
-- or a bad index
WITH fk_actions ( code, action ) AS (
VALUES ( 'a', 'error' ),
( 'r', 'restrict' ),
( 'c', 'cascade' ),
( 'n', 'set null' ),
( 'd', 'set default' )
),
fk_list AS (
SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid,
conname, relname, nspname,
fk_actions_update.action as update_action,
fk_actions_delete.action as delete_action,
conkey as key_cols
FROM pg_constraint
JOIN pg_class ON conrelid = pg_class.oid
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code
JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code
WHERE contype = 'f'
),
fk_attributes AS (
SELECT fkoid, conrelid, attname, attnum
FROM fk_list
JOIN pg_attribute
ON conrelid = attrelid
AND attnum = ANY( key_cols )
ORDER BY fkoid, attnum
),
fk_cols_list AS (
SELECT fkoid, array_agg(attname) as cols_list
FROM fk_attributes
GROUP BY fkoid
),
index_list AS (
SELECT indexrelid as indexid,
pg_class.relname as indexname,
indrelid,
indkey,
indpred is not null as has_predicate,
pg_get_indexdef(indexrelid) as indexdef
FROM pg_index
JOIN pg_class ON indexrelid = pg_class.oid
WHERE indisvalid
),
fk_index_match AS (
SELECT fk_list.*,
indexid,
indexname,
indkey::int[] as indexatts,
has_predicate,
indexdef,
array_length(key_cols, 1) as fk_colcount,
array_length(indkey,1) as index_colcount,
round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb,
cols_list
FROM fk_list
JOIN fk_cols_list USING (fkoid)
LEFT OUTER JOIN index_list
ON conrelid = indrelid
AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols
),
fk_perfect_match AS (
SELECT fkoid
FROM fk_index_match
WHERE (index_colcount - 1) <= fk_colcount
AND NOT has_predicate
AND indexdef LIKE '%USING btree%'
),
fk_index_check AS (
SELECT 'no index' as issue, *, 1 as issue_sort
FROM fk_index_match
WHERE indexid IS NULL
UNION ALL
SELECT 'questionable index' as issue, *, 2
FROM fk_index_match
WHERE indexid IS NOT NULL
AND fkoid NOT IN (
SELECT fkoid
FROM fk_perfect_match)
),
parent_table_stats AS (
SELECT fkoid, tabstats.relname as parent_name,
(n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes,
round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb
FROM pg_stat_user_tables AS tabstats
JOIN fk_list
ON relid = parentid
),
fk_table_stats AS (
SELECT fkoid,
(n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes,
seq_scan as table_scans
FROM pg_stat_user_tables AS tabstats
JOIN fk_list
ON relid = conrelid
)
SELECT nspname as schema_name,
relname as table_name,
conname as fk_name,
issue,
table_mb,
writes,
table_scans,
parent_name,
parent_mb,
parent_writes,
cols_list,
indexdef
FROM fk_index_check
JOIN parent_table_stats USING (fkoid)
JOIN fk_table_stats USING (fkoid)
WHERE table_mb > 9
AND ( writes > 1000
OR parent_writes > 1000
OR parent_mb > 10 )
ORDER BY issue_sort, table_mb DESC, table_name, fk_name;
--==========================================================================================
select
n.nspname as "Schema"
,t.relname as "Table"
,c.relname as "Index"
from
pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_index i on i.indexrelid = c.oid
join pg_catalog.pg_class t on i.indrelid = t.oid
where
c.relkind = 'i'
and n.nspname not in ('pg_catalog', 'pg_toast')
--and pg_catalog.pg_table_is_visible(c.oid)
order by
n.nspname
,t.relname
,c.relname
--==========================================================================================
--
-- function: missing_fk_indexes
-- purpose: List all foreing keys in the database without and index in the referencing table.
-- author: Based on the work of Laurenz Albe
-- see: https://www.cybertec-postgresql.com/en/index-your-foreign-key/
--
create or replace function missing_fk_indexes ()
returns table (
referencing_table regclass,
fk_columns varchar,
table_size varchar,
fk_constraint name,
referenced_table regclass
)
language sql as $$
select
-- referencing table having ta foreign key declaration
tc.conrelid::regclass as referencing_table,
-- ordered list of foreign key columns
string_agg(ta.attname, ', ' order by tx.n) as fk_columns,
-- referencing table size
pg_catalog.pg_size_pretty (
pg_catalog.pg_relation_size(tc.conrelid)
) as table_size,
-- name of the foreign key constraint
tc.conname as fk_constraint,
-- name of the target or destination table
tc.confrelid::regclass as referenced_table
from pg_catalog.pg_constraint tc
-- enumerated key column numbers per foreign key
cross join lateral unnest(tc.conkey) with ordinality as tx(attnum, n)
-- name for each key column
join pg_catalog.pg_attribute ta on ta.attnum = tx.attnum and ta.attrelid = tc.conrelid
where not exists (
-- is there ta matching index for the constraint?
select 1 from pg_catalog.pg_index i
where
i.indrelid = tc.conrelid and
-- the first index columns must be the same as the key columns, but order doesn't matter
(i.indkey::smallint[])[0:cardinality(tc.conkey)-1] @> tc.conkey) and
tc.contype = 'f'
group by
tc.conrelid,
tc.conname,
tc.confrelid
order by
pg_catalog.pg_relation_size(tc.conrelid) desc
$$;
--==========================================================================================
--
-- function: missing_fk_indexes2
-- purpose: List all foreing keys in the database without and index in the referencing table.
-- The listing contains create index sentences
-- author: Based on the work of Laurenz Albe
-- see: https://www.cybertec-postgresql.com/en/index-your-foreign-key/
--
create or replace function missing_fk_indexes2 ()
returns setof varchar
language sql as $$
select
-- create index sentence
'create index on ' ||
tc.conrelid::regclass ||
'(' ||
string_agg(ta.attname, ', ' order by tx.n) ||
')' as create_index
from pg_catalog.pg_constraint tc
-- enumerated key column numbers per foreign key
cross join lateral unnest(tc.conkey) with ordinality as tx(attnum, n)
-- name for each key column
join pg_catalog.pg_attribute ta on ta.attnum = tx.attnum and ta.attrelid = tc.conrelid
where not exists (
-- is there ta matching index for the constraint?
select 1 from pg_catalog.pg_index i
where
i.indrelid = tc.conrelid and
-- the first index columns must be the same as the key columns, but order doesn't matter
(i.indkey::smallint[])[0:cardinality(tc.conkey)-1] @> tc.conkey) and
tc.contype = 'f'
group by
tc.conrelid,
tc.conname,
tc.confrelid
order by
pg_catalog.pg_relation_size(tc.conrelid) desc
$$;
--==========================================================================================
SELECT
indexname,
indexdef
FROM
pg_indexes
WHERE
tablename = 'table_name';
--==========================================================================================
WITH test(x) AS (
VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),
('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))
SELECT x
, x ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$' AS isnumeric
FROM test;
--==========================================================================================
CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
DECLARE x NUMERIC;
BEGIN
x = $1::NUMERIC;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;
--==========================================================================================
WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),
('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))
SELECT x, isnumeric(x) FROM test;
--==========================================================================================
SELECT m.title, SUM(m.body::numeric)
FROM messages as m
WHERE jsonb_typeof(m.body) = 'number'
GROUP BY m.title;
--==========================================================================================
create function isnumeric(text) returns boolean
immutable
language plpgsql
as $$
begin
if $1 is not null then
return (select $1 ~ '^(([-+]?[0-9]+(\.[0-9]+)?)|([-+]?\.[0-9]+))$');
else
return false;
end if;
end;
$$
;
--==========================================================================================
SELECT id FROM mytable
WHERE message ~ '[АаБбВвГгДдЕеЁёЖжЗзИиЙйКкЛлМмНнОоПпРрСсТтУуФфХхЦцЧчШшЩщЪъЫыЬьЭэЮюЯя]';
--==========================================================================================
create table charfreq (
c text,
f float,
lang text
);
insert into charfreq values
('а', 8.04, 'ru'),
('б', 1.55, 'ru'),
('в', 4.75, 'ru'),
...
('a', 8.167, 'en'),
('b', 1.492, 'en'),
('c', 2.782, 'en'),
...
('ï', 0.005, 'fr'),
('ô', 0.023, 'fr'),
('ù', 0.058, 'fr'),
('û', 0.06 , 'fr');
insert into test values
(1, 'hi'),
(2, 'ok'),
(3, 'துய'),
(4, 'нет'),
(5, 'été'); -- a French message, just for fun
select id, message, lang, score
from (
select *, row_number() OVER (partition by id, message order by score desc) as rownum
from (
select id, message, lang, coalesce(sum(f), 0)/length(message) as score
from (
select *, unnest(STRING_TO_ARRAY(message, NULL)) as c from test
) as a
left join charfreq b
using (c)
group by 1,2,3
) as a
) as a
where rownum = 1;
--==========================================================================================
CREATE TABLE MESSAGE (
Id INTEGER PRIMARY KEY,
MESSAGE VARCHAR (50)
);
INSERT INTO MESSAGE VALUES (1, 'hi');//False
INSERT INTO MESSAGE VALUES (2, 'ok');//False
INSERT INTO MESSAGE VALUES (3, 'துய');//False
INSERT INTO MESSAGE VALUES (4, 'нет');//True
INSERT INTO MESSAGE VALUES (5, 'нет-_*/?/()=.,123 ');//True
INSERT INTO MESSAGE VALUES (6, 'нет 123');//True
INSERT INTO MESSAGE VALUES (6, 'нет 123AAAA');//False
SELECT * FROM message m
WHERE
ARRAY(SELECT ASCII(unnest(STRING_TO_ARRAY(REGEXP_REPLACE(m.message, '[^[:alnum:]]+', '', 'g'), NULL)))) <@
ARRAY(SELECT ASCII(unnest(STRING_TO_ARRAY('АаБбВвГгДдЕеЁёЖжЗзИиЙйКкЛлМмНнОоПпРрСсТтУуФфХхЦцЧчШшЩщЪъЫыЬьЭэЮюЯя0123456789', NULL))))
--==========================================================================================
SELECT ns.nspname AS schema
, class.relname AS "table"
, con.conname AS "constraint"
, con.condeferrable AS "deferrable"
, con.condeferred AS deferred
FROM pg_constraint con
INNER JOIN pg_class class ON class.oid = con.conrelid
INNER JOIN pg_namespace ns ON ns.oid = class.relnamespace
WHERE con.contype IN ('p', 'u')
AND ns.nspname != 'pg_catalog'
ORDER BY 1, 2, 3;
--==========================================================================================
CREATE TABLE `products` (
`product_id` bigint(20) NOT NULL,
`product_name` varchar(100) NOT NULL,
`price` decimal(16, 2) NOT NULL,
`brand_id` int(11) NOT NULL,
PRIMARY KEY (`product_id`),
CONSTRAINT `fk_brand_id` FOREIGN KEY (`brand_id`)
REFERENCES `brands` (`brand_id`)
);
--==========================================================================================
CREATE TABLE `products` (
`product_id` bigint(20) NOT NULL,
`product_name` varchar(100) NOT NULL,
`price` decimal(16, 2) NOT NULL,
`brand_id` int(11) NOT NULL,
/* Note : `extra` column store extra information and is declared as JSON data type column. */
`extra` JSON NOT NULL,
PRIMARY KEY (`product_id`),
CONSTRAINT `fk_brand_id` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`brand_id`)
);
SELECT product_id, product_name, price, extra
FROM products
WHERE extra -> '$.size' = 'L'
LIMIT 5;
--==========================================================================================
SELECT
product_id, product_name, price, category_code
FROM products
WHERE
brand_id = (SELECT brand_id FROM brands WHERE brand_name = 'nike')
ORDER BY price DESC
LIMIT 10;
--==========================================================================================
-- It is a particularly resource-intensive analytical query.
-- The events table generates an average of 1.8 million records per day.
WITH top_merchandises AS (
SELECT
product_id,
COUNT(*) AS viewed
FROM events
WHERE event_type = 'view'
AND event_time >= '2019-11-30 17:59:59'
AND event_time <= '2019-11-30 23:59:59'
GROUP BY product_id
ORDER BY viewed DESC
LIMIT 10
);
SELECT
products.product_id,
products.product_name,
top_merchandises.viewed
FROM top_merchandises
JOIN products ON top_merchandises.product_id = products.product_id
ORDER BY viewed DESC
--==========================================================================================
-- It is a particularly resource-intensive analytical query.
-- The events table generates an average of 1.8 million records per day.
SELECT (
COUNT(CASE WHEN event_type = 'purchase' THEN 1 ELSE NULL END) /
COUNT(CASE WHEN event_type = 'view' THEN 1 ELSE NULL END)
) AS bought_rate
FROM events
WHERE event_time >= '2019-11-30 17:59:59'
AND event_time <= '2019-11-30 23:59:59'
--==========================================================================================
-- It is a particularly resource-intensive analytical query.
-- The events table generates an average of 1.8 million records per day.
WITH top_sellers AS (
SELECT
product_id,
SUM(price) AS sold
FROM events
WHERE event_type = 'purchase'
AND event_time >= '2019-11-30 11:59:59'
AND event_time <= '2019-11-30 23:59:59'
GROUP BY product_id
ORDER BY sold DESC
LIMIT 10
)
SELECT
products.product_id,
products.product_name,
top_sellers.sold
FROM top_sellers
JOIN products ON top_sellers.product_id = products.product_id
ORDER BY sold DESC
--==========================================================================================
-- SET search_path TO TPCC;
-- Condition 1: W_YTD = sum(D_YTD)
SELECT * FROM (SELECT w.w_id, w.w_ytd, d.sum_d_ytd
FROM bmsql_warehouse w,
(SELECT d_w_id, SUM(d_ytd) sum_d_ytd
FROM bmsql_district
GROUP BY d_w_id) d
WHERE w.w_id = d.d_w_id) as x
WHERE w_ytd != sum_d_ytd;
-- Condition 2: D_NEXT_O_ID - 1 = max(O_ID) = max(NO_O_ID)
SELECT * FROM (SELECT d.d_w_id, d.d_id, d.d_next_o_id, o.max_o_id, no.max_no_o_id
FROM bmsql_district d,
(SELECT o_w_id, o_d_id, MAX(o_id) max_o_id
FROM bmsql_oorder
GROUP BY o_w_id, o_d_id) o,
(SELECT no_w_id, no_d_id, MAX(no_o_id) max_no_o_id
FROM bmsql_new_order
GROUP BY no_w_id, no_d_id) no
WHERE d.d_w_id = o.o_w_id AND d.d_w_id = no.no_w_id AND
d.d_id = o.o_d_id AND d.d_id = no.no_d_id) as x
WHERE d_next_o_id - 1 != max_o_id OR d_next_o_id - 1 != max_no_o_id;
-- Condition 3: max(NO_O_ID) - min(NO_O_ID) + 1
-- = [number of rows in the NEW-ORDER table for this bmsql_district]
SELECT * FROM (SELECT no_w_id, no_d_id, MAX(no_o_id) max_no_o_id,
MIN(no_o_id) min_no_o_id, COUNT(*) count_no
FROM bmsql_new_order
GROUP BY no_w_id, no_d_Id) as x
WHERE max_no_o_id - min_no_o_id + 1 != count_no;
-- Condition 4: sum(O_OL_CNT)
-- = [number of rows in the ORDER-LINE table for this bmsql_district]
SELECT * FROM (SELECT o.o_w_id, o.o_d_id, o.sum_o_ol_cnt, ol.count_ol
FROM (SELECT o_w_id, o_d_id, SUM(o_ol_cnt) sum_o_ol_cnt
FROM bmsql_oorder
GROUP BY o_w_id, o_d_id) o,
(SELECT ol_w_id, ol_d_id, COUNT(*) count_ol
FROM bmsql_order_line
GROUP BY ol_w_id, ol_d_id) ol
WHERE o.o_w_id = ol.ol_w_id AND
o.o_d_id = ol.ol_d_id) as x
WHERE sum_o_ol_cnt != count_ol;
-- Condition 5: For any row in the ORDER table, O_CARRIER_ID is set to a null
-- value if and only if there is a corresponding row in the
-- NEW-ORDER table
SELECT * FROM (SELECT o.o_w_id, o.o_d_id, o.o_id, o.o_carrier_id, no.count_no
FROM bmsql_oorder o,
(SELECT no_w_id, no_d_id, no_o_id, COUNT(*) count_no
FROM bmsql_new_order
GROUP BY no_w_id, no_d_id, no_o_id) no
WHERE o.o_w_id = no.no_w_id AND
o.o_d_id = no.no_d_id AND
o.o_id = no.no_o_id) as x
WHERE (o_carrier_id IS NULL AND count_no = 0) OR
(o_carrier_id IS NOT NULL AND count_no != 0);
-- Condition 6: For any row in the ORDER table, O_OL_CNT must equal the number
-- of rows in the ORDER-LINE table for the corresponding order
SELECT * FROM (SELECT o.o_w_id, o.o_d_id, o.o_id, o.o_ol_cnt, ol.count_ol
FROM bmsql_oorder o,
(SELECT ol_w_id, ol_d_id, ol_o_id, COUNT(*) count_ol
FROM bmsql_order_line
GROUP BY ol_w_id, ol_d_id, ol_o_id) ol
WHERE o.o_w_id = ol.ol_w_id AND
o.o_d_id = ol.ol_d_id AND
o.o_id = ol.ol_o_id) as x
WHERE o_ol_cnt != count_ol;
-- Condition 7: For any row in the ORDER-LINE table, OL_DELIVERY_D is set to
-- a null date/time if and only if the corresponding row in the
-- ORDER table has O_CARRIER_ID set to a null value
SELECT * FROM (SELECT ol.ol_w_id, ol.ol_d_id, ol.ol_o_id, ol.ol_delivery_d,
o.o_carrier_id
FROM bmsql_order_line ol,
bmsql_oorder o
WHERE ol.ol_w_id = o.o_w_id AND
ol.ol_d_id = o.o_d_id AND
ol.ol_o_id = o.o_id) as x
WHERE (ol_delivery_d IS NULL AND o_carrier_id IS NOT NULL) OR
(ol_delivery_d IS NOT NULL AND o_carrier_id IS NULL);
-- Condition 8: W_YTD = sum(H_AMOUNT)
SELECT *
FROM (SELECT w.w_id, w.w_ytd, h.sum_h_amount
FROM bmsql_warehouse w,
(SELECT h_w_id, SUM(h_amount) sum_h_amount FROM bmsql_history GROUP BY h_w_id) h
WHERE w.w_id = h.h_w_id) as x
WHERE w_ytd != sum_h_amount;
-- Condition 9: D_YTD = sum(H_AMOUNT)
SELECT *
FROM (SELECT d.d_w_id, d.d_id, d.d_ytd, h.sum_h_amount
FROM bmsql_district d,
(SELECT h_w_id, h_d_id, SUM(h_amount) sum_h_amount
FROM bmsql_history
GROUP BY h_w_id, h_d_id) h
WHERE d.d_w_id = h.h_w_id
AND d.d_id = h.h_d_id) as x
WHERE d_ytd != sum_h_amount;
--==========================================================================================
-- ----
-- Extra Schema objects/definitions for history.hist_id in PostgreSQL
-- ----
-- ----
-- This is an extra column not present in the TPC-C
-- specs. It is useful for replication systems like
-- Bucardo and Slony-I, which like to have a primary
-- key on a table. It is an auto-increment or serial
-- column type. The definition below is compatible
-- with Oracle 11g, using a sequence and a trigger.
-- ----
-- Adjust the sequence above the current max(hist_id)
select setval('bmsql_hist_id_seq', (select max(hist_id) from bmsql_history));
-- Make nextval(seq) the default value of the hist_id column.
alter table bmsql_history
alter column hist_id set default nextval('bmsql_hist_id_seq');
-- Add a primary key history(hist_id)
alter table bmsql_history add primary key (hist_id);
--==========================================================================================
copy bmsql_config
(cfg_name, cfg_value)
from '/tmp/csv/bmsql_config.csv' WITH CSV;
copy bmsql_warehouse
(w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip)
from '/tmp/csv/bmsql_warehouse.csv' WITH CSV;
copy bmsql_item
(i_id, i_name, i_price, i_data, i_im_id)
from '/tmp/csv/bmsql_item.csv' WITH CSV;
copy bmsql_stock
(s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data,
s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05,
s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10)
from '/tmp/csv/bmsql_stock.csv' WITH CSV;
copy bmsql_district
(d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1,
d_street_2, d_city, d_state, d_zip)
from '/tmp/csv/bmsql_district.csv' WITH CSV;
copy bmsql_customer
(c_id, c_d_id, c_w_id, c_discount, c_credit, c_last, c_first, c_credit_lim,
c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_street_1,
c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_middle, c_data)
from '/tmp/csv/bmsql_customer.csv' WITH CSV;
copy bmsql_history
(hist_id, h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data)
from '/tmp/csv/bmsql_history.csv' WITH CSV;
copy bmsql_oorder
(o_id, o_w_id, o_d_id, o_c_id, o_carrier_id, o_ol_cnt, o_all_local, o_entry_d)
from '/tmp/csv/bmsql_oorder.csv' WITH CSV NULL AS 'NULL';
copy bmsql_order_line
(ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d,
ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info)
from '/tmp/csv/bmsql_order_line.csv' WITH CSV NULL AS 'NULL';
copy bmsql_new_order
(no_w_id, no_d_id, no_o_id)
from '/tmp/csv/bmsql_new_order.csv' WITH CSV;
--==========================================================================================
# Can be used with
# mysql < check-placement.sql | grep -v us-east-1
# -or-
# mysql < check-placement.sql | grep us-east-1
use information_schema;
WITH store_index AS (SELECT store_id, substring(address, -1) as node_number, label->>"$[0].value" as aws_region from tikv_store_status)
SELECT
node_number as node, aws_region, is_leader, count(*) as c
FROM tikv_region_peers
INNER JOIN TIKV_REGION_STATUS USING (region_id)
INNER JOIN store_index USING (store_id)
WHERE db_name = 'test'
GROUP BY
node_number, is_leader
ORDER BY node_number;
--==========================================================================================
update users, prospect_users
set users.about = prospect_users.about
where prospect_users.username = users.username;
==========================================================================================
delete users
from users, prospect_users
where users.username = prospect_users.username
and NOT prospect_users.active
--==========================================================================================
SELECT users.*, posts.*
FROM users
LEFT JOIN posts
ON posts.user_id = users.id
WHERE posts.title LIKE '%SQL%';
--==========================================================================================
SELECT users.*, posts.*
FROM users
LEFT JOIN posts
ON posts.user_id = users.id
AND posts.title LIKE '%SQL%';
--==========================================================================================
SELECT users.*, posts.*
FROM posts
LEFT JOIN users
ON posts.user_id = users.id;
==========================================================================================
SELECT users.*, posts.*
FROM users
RIGHT JOIN posts
ON posts.user_id = users.id;
--==========================================================================================
UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (
SELECT AGE
FROM CUSTOMERS_BKP
WHERE AGE >= 27
);
--==========================================================================================
DELETE FROM CUSTOMERS
WHERE AGE IN (
SELECT AGE
FROM CUSTOMERS_BKP
WHERE AGE >= 27
);
==========================================================================================
SELECT id, name, amount, date
FROM customer
LEFT JOIN orders
ON customers.id = orders.customer_id
UNION
SELECT id, name, amount, date
FROM customer
RIGHT JOIN orders
ON customers.id = orders.customer_id
--==========================================================================================
SELECT customer_name, SUM(price) AS Total_Purchase
FROM purchase
WHERE customer_name
LIKE "S%"
GROUP BY customer_name
HAVING SUM(price) > 1000;
--==========================================================================================
SELECT customer_name, AVG(price) AS Average_Purchase
FROM purchase
GROUP BY customer_name
HAVING AVG(price) > 550
ORDER BY customer_name DESC;
--==========================================================================================
SELECT
mz_catalog.mz_sources.name AS source_name,
source_id AS source_id,
count(*) AS error_count
FROM mz_internal.mz_source_status_history h
JOIN mz_catalog.mz_sources ON h.source_id = mz_catalog.mz_sources.id
WHERE h.error IS NOT NULL
GROUP BY 1, 2;
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
--==========================================================================================
Raw
schema.sql
CREATE TABLE IF NOT EXISTS department(
id INT PRIMARY KEY,
name VARCHAR
);
create TABLE IF NOT EXISTS employee(
id INT PRIMARY KEY,
name VARCHAR,
salary INT,
department_id INT,
CONSTRAINT fk_deparment_id
FOREIGN KEY(department_id)
REFERENCES department(ID) ON DELETE CASCADE
);
Raw
schema2.sql
/* START */
DROP TABLE IF EXISTS "payment" CASCADE;
DROP TABLE IF EXISTS "bank_transaction" CASCADE;
DROP TABLE IF EXISTS "orderdetail" CASCADE;
DROP TABLE IF EXISTS "order" CASCADE;
DROP TABLE IF EXISTS "product" CASCADE;
DROP TABLE IF EXISTS "productline" CASCADE;
DROP TABLE IF EXISTS "top3product" CASCADE;
DROP TABLE IF EXISTS "productlinedetail" CASCADE;
DROP TABLE IF EXISTS "office_has_manager" CASCADE;
DROP TABLE IF EXISTS "manager" CASCADE;
DROP TABLE IF EXISTS "customer" CASCADE;
DROP TABLE IF EXISTS "customerdetail" CASCADE;
DROP TABLE IF EXISTS "sale" CASCADE;
DROP TABLE IF EXISTS "daily_activity" CASCADE;
DROP TABLE IF EXISTS "token" CASCADE;
DROP TABLE IF EXISTS "employee" CASCADE;
DROP TABLE IF EXISTS "employee_status" CASCADE;
DROP TABLE IF EXISTS "department" CASCADE;
DROP TABLE IF EXISTS "office" CASCADE;
DROP TABLE IF EXISTS "office_flights" CASCADE;
DROP SEQUENCE IF EXISTS "manager_seq";
DROP SEQUENCE IF EXISTS "product_seq";
DROP SEQUENCE IF EXISTS "order_seq";
DROP SEQUENCE IF EXISTS "sale_seq";
DROP SEQUENCE IF EXISTS "customer_seq";
DROP SEQUENCE IF EXISTS "employee_seq";
DROP SEQUENCE IF EXISTS "token_seq";
DROP TYPE IF EXISTS "rate_type";
DROP TYPE IF EXISTS "vat_type";
DROP TYPE IF EXISTS "evaluation_criteria";
DROP DOMAIN IF EXISTS "postal_code";
DROP FUNCTION IF EXISTS "make_array";
DROP FUNCTION IF EXISTS "dup";
DROP FUNCTION IF EXISTS "get_avg_sale";
DROP FUNCTION IF EXISTS "get_salary_stat";
DROP FUNCTION IF EXISTS "swap";
DROP FUNCTION IF EXISTS "new_salary";
DROP FUNCTION IF EXISTS "get_customer";
DROP FUNCTION IF EXISTS "get_offices_multiple";
DROP FUNCTION IF EXISTS "employee_office_arr";
DROP FUNCTION IF EXISTS "sale_price";
DROP FUNCTION IF EXISTS "top_three_sales_per_employee";
DROP FUNCTION IF EXISTS "product_of_product_line";
DROP FUNCTION IF EXISTS "update_msrp";
DROP VIEW IF EXISTS "customer_master";
DROP VIEW IF EXISTS "office_master";
DROP VIEW IF EXISTS "product_master";
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- TABLE OFFICE
CREATE DOMAIN "postal_code" AS VARCHAR(15)
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^[A-Z]{2}[0-9]{3}[A-Z]{2}$'
);
CREATE TABLE "office" (
"office_code" VARCHAR(10) NOT NULL,
"city" VARCHAR(50) DEFAULT NULL,
"phone" VARCHAR(50) NOT NULL,
"address_line_first" VARCHAR(50) NOT NULL,
"address_line_second" VARCHAR(50) DEFAULT NULL,
"state" VARCHAR(50) DEFAULT NULL,
"country" VARCHAR(50) DEFAULT NULL,
"postal_code" postal_code NOT NULL,
"territory" VARCHAR(10) NOT NULL,
"location" POINT DEFAULT NULL,
"internal_budget" INT NOT NULL,
CONSTRAINT "office_pk" PRIMARY KEY ("office_code"),
CONSTRAINT "office_postal_code_uk" UNIQUE ("postal_code")
);
-- TABLE DEPARTMENT
CREATE TABLE "department" (
"department_id" SERIAL NOT NULL,
"name" VARCHAR(50) NOT NULL,
"phone" VARCHAR(50) NOT NULL,
"code" INT NOT NULL,
"office_code" VARCHAR(10) NOT NULL,
"topic" TEXT[] DEFAULT NULL,
"dep_net_ipv4" INET DEFAULT NULL,
"local_budget" FLOAT DEFAULT NULL,
"profit" FLOAT DEFAULT NULL,
"forecast_profit" FLOAT DEFAULT NULL,
"cash" FLOAT DEFAULT NULL,
"accounts_receivable" FLOAT DEFAULT NULL,
"inventories" FLOAT DEFAULT NULL,
"accounts_payable" FLOAT DEFAULT NULL,
"st_borrowing" FLOAT DEFAULT NULL,
"accrued_liabilities" FLOAT DEFAULT NULL,
CONSTRAINT "department_pk" PRIMARY KEY ("department_id"),
CONSTRAINT "department_code_uk" UNIQUE ("code"),
CONSTRAINT "department_office_fk" FOREIGN KEY ("office_code") REFERENCES "office" ("office_code")
);
ALTER SEQUENCE "department_department_id_seq" RESTART WITH 20;
-- TABLE EMPLOYEE
CREATE TABLE "employee" (
"employee_number" BIGINT NOT NULL,
"last_name" VARCHAR(50) NOT NULL,
"first_name" VARCHAR(50) NOT NULL,
"extension" VARCHAR(10) NOT NULL,
"email" VARCHAR(100) NOT NULL,
"office_code" VARCHAR(10) NOT NULL,
"salary" INT NOT NULL,
"commission" INT DEFAULT NULL,
"reports_to" BIGINT DEFAULT NULL,
"job_title" VARCHAR(50) NOT NULL,
"employee_of_year" INT[] DEFAULT NULL,
"monthly_bonus" INT[] DEFAULT NULL,
CONSTRAINT "employee_pk" PRIMARY KEY ("employee_number"),
CONSTRAINT "employee_employee_fk" FOREIGN KEY ("reports_to") REFERENCES "employee" ("employee_number"),
CONSTRAINT "employees_office_fk" FOREIGN KEY ("office_code") REFERENCES "office" ("office_code")
);
-- this sequence is not used automatically
CREATE SEQUENCE "employee_seq" START 100000 INCREMENT 10 MINVALUE 100000 MAXVALUE 10000000 OWNED BY "employee"."employee_number";
-- TABLE EMPLOYEE_STATUS
CREATE TABLE "employee_status" (
"id" SERIAL NOT NULL,
"employee_number" BIGINT NOT NULL,
"status" VARCHAR(50) NOT NULL,
"acquired_date" DATE NOT NULL,
CONSTRAINT "id_pk" PRIMARY KEY ("id"),
CONSTRAINT "employee_status_employee_fk" FOREIGN KEY ("employee_number") REFERENCES "employee" ("employee_number")
);
-- TABLE SALE
CREATE SEQUENCE "sale_seq" START 1000000;
CREATE TYPE "rate_type" AS enum('SILVER', 'GOLD', 'PLATINUM');
CREATE TYPE "vat_type" AS enum('NONE', 'MIN', 'MAX');
CREATE TABLE "sale" (
"sale_id" BIGINT NOT NULL DEFAULT NEXTVAL ('"sale_seq"'),
"fiscal_year" INT NOT NULL,
"sale" FLOAT NOT NULL,
"employee_number" BIGINT DEFAULT NULL,
"hot" BOOLEAN DEFAULT FALSE,
"rate" rate_type DEFAULT NULL,
"vat" vat_type DEFAULT NULL,
"fiscal_month" INT NOT NULL,
"revenue_growth" FLOAT NOT NULL,
"trend" VARCHAR(10) DEFAULT NULL,
CONSTRAINT "sale_pk" PRIMARY KEY ("sale_id"),
CONSTRAINT "sale_employee_fk" FOREIGN KEY ("employee_number") REFERENCES "employee" ("employee_number") ON UPDATE CASCADE
);
-- TABLE DAILY_ACTIVITY
CREATE TABLE "daily_activity" (
"day_id" SERIAL NOT NULL,
"day_date" DATE NOT NULL,
"sales" FLOAT NOT NULL,
"visitors" FLOAT NOT NULL,
"conversion" FLOAT NOT NULL,
CONSTRAINT "daily_activity_pk" PRIMARY KEY ("day_id")
);
-- TABLE TOKEN
CREATE SEQUENCE "token_seq" START 1000000;
CREATE TABLE "token" (
"token_id" BIGINT NOT NULL DEFAULT NEXTVAL ('"token_seq"'),
"sale_id" BIGINT NOT NULL,
"amount" FLOAT NOT NULL,
"updated_on" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "token_pk" PRIMARY KEY ("token_id"),
CONSTRAINT "token_sale_fk" FOREIGN KEY ("sale_id") REFERENCES "sale" ("sale_id") ON DELETE CASCADE ON UPDATE CASCADE
);
-- TABLE CUSTOMER
CREATE SEQUENCE "customer_seq" START 1000000;
CREATE TABLE "customer" (
"customer_number" BIGINT NOT NULL DEFAULT NEXTVAL ('"customer_seq"'),
"customer_name" VARCHAR(50) NOT NULL,
"contact_last_name" VARCHAR(50) NOT NULL,
"contact_first_name" VARCHAR(50) NOT NULL,
"phone" VARCHAR(50) NOT NULL,
"sales_rep_employee_number" BIGINT DEFAULT NULL,
"credit_limit" DECIMAL(10,2) DEFAULT NULL,
"first_buy_date" INT DEFAULT NULL,
CONSTRAINT "customer_pk" PRIMARY KEY ("customer_number"),
CONSTRAINT "customer_name_uk" UNIQUE ("customer_name"),
CONSTRAINT "customer_employee_fk" FOREIGN KEY ("sales_rep_employee_number") REFERENCES "employee" ("employee_number") ON UPDATE CASCADE
);
-- TABLE CUSTOMERDETAIL
CREATE TABLE "customerdetail" (
"customer_number" BIGINT NOT NULL,
"address_line_first" VARCHAR(50) NOT NULL,
"address_line_second" VARCHAR(50) DEFAULT NULL,
"city" VARCHAR(50) DEFAULT NULL,
"state" VARCHAR(50) DEFAULT NULL,
"postal_code" VARCHAR(15) DEFAULT NULL,
"country" VARCHAR(50) DEFAULT NULL,
CONSTRAINT "customerdetail_pk" PRIMARY KEY ("customer_number"),
CONSTRAINT "customer_address_line_first_uk" UNIQUE ("address_line_first"),
CONSTRAINT "customerdetail_customer_fk" FOREIGN KEY ("customer_number") REFERENCES "customer" ("customer_number")
);
-- TABLE MANAGER
CREATE TYPE "evaluation_criteria" AS ("communication_ability" INT, "ethics" INT, "performance" INT, "employee_input" INT);
CREATE SEQUENCE "manager_seq" START 1000000;
CREATE TABLE "manager" (
"manager_id" BIGINT NOT NULL DEFAULT NEXTVAL ('"manager_seq"'),
"manager_name" VARCHAR(50) NOT NULL DEFAULT '"anonymous"',
"manager_detail" JSON DEFAULT NULL,
"manager_evaluation" evaluation_criteria DEFAULT NULL,
CONSTRAINT "manager_pk" PRIMARY KEY ("manager_id")
);
-- TABLE OFFICE_HAS_MANAGER
CREATE TABLE "office_has_manager" (
"offices_office_code" VARCHAR(10) NOT NULL,
"managers_manager_id" BIGINT NOT NULL,
CONSTRAINT "office_manager_uk" UNIQUE ("offices_office_code", "managers_manager_id"),
CONSTRAINT "office_fk" FOREIGN KEY ("offices_office_code") REFERENCES "office" ("office_code") ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "manager_fk" FOREIGN KEY ("managers_manager_id") REFERENCES "manager" ("manager_id") ON UPDATE NO ACTION ON DELETE NO ACTION
);
-- TABLE PRODUCTLINE
CREATE TABLE "productline" (
"product_line" VARCHAR(50) NOT NULL,
"code" BIGINT NOT NULL,
"text_description" VARCHAR(4000) DEFAULT NULL,
"html_description" XML DEFAULT NULL,
"image" BYTEA DEFAULT NULL,
"created_on" DATE NOT NULL DEFAULT NOW(),
CONSTRAINT "productline_pk" PRIMARY KEY ("product_line", "code"),
CONSTRAINT "productline_uk" UNIQUE("product_line")
);
-- TABLE PRODUCTDETAIL
CREATE TABLE "productlinedetail" (
"product_line" VARCHAR(50) NOT NULL,
"code" BIGINT NOT NULL,
"line_capacity" VARCHAR(20) NOT NULL,
"line_type" INT DEFAULT 0,
CONSTRAINT "productlinedetail_pk" PRIMARY KEY ("product_line","code"),
CONSTRAINT "productlinedetail_uk" UNIQUE("product_line"),
CONSTRAINT "productlinedetail_productline_fk" FOREIGN KEY ("product_line","code") REFERENCES "productline" ("product_line","code")
);
-- TABLE PRODUCT
CREATE SEQUENCE "product_seq" START 1000000;
CREATE TABLE "product" (
"product_id" BIGINT NOT NULL DEFAULT NEXTVAL ('"product_seq"'),
"product_name" VARCHAR(70) DEFAULT NULL,
"product_line" VARCHAR(50) DEFAULT NULL,
"code" BIGINT NOT NULL,
"product_scale" VARCHAR(10) DEFAULT NULL,
"product_vendor" VARCHAR(50) DEFAULT NULL,
"product_description" TEXT DEFAULT NULL,
"quantity_in_stock" INT DEFAULT 0,
"buy_price" DECIMAL(10,2) NOT NULL DEFAULT 0.0,
"msrp" DECIMAL(10,2) NOT NULL DEFAULT 0.0,
"specs" HSTORE DEFAULT NULL,
"product_uid" BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10),
CONSTRAINT "product_pk" PRIMARY KEY ("product_id"),
CONSTRAINT "product_productline_fk" FOREIGN KEY ("product_line","code") REFERENCES "productline" ("product_line","code")
);
-- TABLE ORDER
CREATE SEQUENCE "order_seq" START 1000000;
CREATE TABLE "order" (
"order_id" BIGINT NOT NULL DEFAULT NEXTVAL ('"order_seq"'),
"order_date" DATE NOT NULL,
"required_date" DATE NOT NULL,
"shipped_date" DATE DEFAULT NULL,
"status" VARCHAR(15) NOT NULL,
"comments" TEXT DEFAULT NULL,
"customer_number" BIGINT NOT NULL,
"amount" DECIMAL(10,2) NOT NULL,
CONSTRAINT "order_pk" PRIMARY KEY ("order_id"),
CONSTRAINT "order_customer_fk" FOREIGN KEY ("customer_number") REFERENCES "customer" ("customer_number")
);
-- TABLE ORDERDETAIL
CREATE TABLE "orderdetail" (
"orderdetail_id" SERIAL NOT NULL,
"order_id" BIGINT NOT NULL,
"product_id" BIGINT NOT NULL,
"quantity_ordered" INT NOT NULL,
"price_each" DECIMAL(10,2) NOT NULL,
"order_line_number" INT NOT NULL,
CONSTRAINT "orderdetail_pk" PRIMARY KEY ("orderdetail_id"),
CONSTRAINT "orderdetail_uk" UNIQUE ("order_id", "product_id"),
CONSTRAINT "orderdetail_order_fk" FOREIGN KEY ("order_id") REFERENCES "order" ("order_id"),
CONSTRAINT "orderdetail_product_fk" FOREIGN KEY ("product_id") REFERENCES "product" ("product_id")
);
-- TABLE TOP3PRODUCT
CREATE TABLE "top3product" (
"product_id" BIGINT NOT NULL,
"product_name" VARCHAR(70) DEFAULT NULL,
CONSTRAINT "top3product_pk" PRIMARY KEY ("product_id")
);
-- TABLE PAYMENT
CREATE TABLE "payment" (
"customer_number" BIGINT NOT NULL,
"check_number" VARCHAR(50) NOT NULL,
"payment_date" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"invoice_amount" DECIMAL(10,2) NOT NULL,
"caching_date" TIMESTAMP DEFAULT NULL,
"version" INT NOT NULL DEFAULT 0,
"modified" TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT "payment_pk" PRIMARY KEY ("customer_number","check_number"),
CONSTRAINT "check_number_uk" UNIQUE("check_number"),
CONSTRAINT "payment_customer_fk" FOREIGN KEY ("customer_number") REFERENCES "customer" ("customer_number")
);
-- TABLE BANK_TRANSACTION
CREATE TABLE "bank_transaction" (
"transaction_id" SERIAL NOT NULL,
"bank_name" VARCHAR(50) NOT NULL,
"bank_iban" VARCHAR(50) NOT NULL,
"transfer_amount" DECIMAL(10,2) NOT NULL,
"caching_date" TIMESTAMP NOT NULL DEFAULT NOW(),
"customer_number" BIGINT NOT NULL,
"check_number" VARCHAR(50) NOT NULL,
"card_type" VARCHAR(50) NOT NULL,
"status" VARCHAR(50) NOT NULL DEFAULT 'SUCCESS',
CONSTRAINT "bank_transaction_pk" PRIMARY KEY ("transaction_id"),
CONSTRAINT "bank_transaction_customer_fk" FOREIGN KEY ("customer_number","check_number") REFERENCES "payment" ("customer_number","check_number")
);
ALTER SEQUENCE "bank_transaction_transaction_id_seq" RESTART WITH 100;
-- TABLE OFFICE_FLIGHTS
CREATE TABLE "office_flights" (
"depart_town" VARCHAR(32) NOT NULL,
"arrival_town" VARCHAR(32) NOT NULL,
"distance_km" INT NOT NULL,
CONSTRAINT "office_flights_pk" PRIMARY KEY ("depart_town", "arrival_town")
);
/* USER-DEFINED FUNCTIONS */
CREATE FUNCTION "make_array"(anyelement, anyelement) RETURNS anyarray
AS $$
SELECT ARRAY[$1, $2];
$$ LANGUAGE sql;
CREATE FUNCTION "dup" (IN "f1" anyelement, OUT "f2" anyelement, OUT "f3" anyarray)
AS 'select $1, array[$1,$1]'
LANGUAGE sql;
CREATE OR REPLACE FUNCTION "get_avg_sale"(IN "len_from" INT, IN "len_to" INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE "avg_count" INT;
BEGIN
SELECT avg("sale"."sale")
INTO "avg_count"
FROM "sale"
WHERE "sale"."sale" BETWEEN "len_from" AND "len_to";
RETURN "avg_count";
END;
$$;
CREATE OR REPLACE FUNCTION "get_salary_stat"(
OUT "min_sal" INT, OUT "max_sal" INT, OUT "avg_sal" NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT MIN("public"."employee"."salary"),
MAX("public"."employee"."salary"),
AVG("public"."employee"."salary")::NUMERIC(7,2)
INTO "min_sal", "max_sal", "avg_sal"
FROM "public"."employee";
END;
$$;
CREATE OR REPLACE FUNCTION "swap"(
INOUT "x" INT, INOUT "y" INT) RETURNS RECORD
LANGUAGE plpgsql
AS $$
BEGIN
SELECT "x","y" INTO "y","x";
END;
$$;
CREATE OR REPLACE FUNCTION "new_salary"(IN "salary" INT, IN "bonus" INT DEFAULT 50, IN "penalty" INT DEFAULT 0)
RETURNS INT
LANGUAGE sql
AS $$
SELECT $1 + $2 - $3;
$$;
CREATE OR REPLACE FUNCTION "get_customer"(IN "cl" INT) RETURNS REFCURSOR
AS $$
DECLARE
"cur" REFCURSOR;
BEGIN
OPEN "cur" FOR SELECT * FROM "customer" WHERE "credit_limit" > "cl" ORDER BY "customer_name";
RETURN "cur";
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION "get_offices_multiple"() RETURNS SETOF REFCURSOR
AS $$
DECLARE
"ref1" REFCURSOR;
"ref2" REFCURSOR;
BEGIN
OPEN "ref1" FOR SELECT "public"."office"."city", "public"."office"."country"
FROM "public"."office" WHERE "public"."office"."internal_budget" < 100000;
RETURN NEXT "ref1";
OPEN "ref2" FOR SELECT "public"."office"."city", "public"."office"."country"
FROM "public"."office" WHERE "public"."office"."internal_budget" > 100000;
RETURN NEXT "ref2";
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION "employee_office_arr"(VARCHAR(10))
RETURNS BIGINT[]
AS $$
SELECT ARRAY(SELECT "public"."employee"."employee_number"
FROM "public"."employee" WHERE "public"."employee"."office_code" = $1)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION "department_topic_arr"(IN "id" BIGINT)
RETURNS text[]
AS $$
SELECT "public"."department"."topic"
FROM "public"."department" WHERE "public"."department"."department_id" = "id"
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION "sale_price"(
"quantity" INT, "list_price" REAL, "fraction_of_price" REAL)
RETURNS REAL LANGUAGE plpgsql
AS $$
BEGIN
RETURN ("list_price" - ("list_price" * "fraction_of_price")) * "quantity";
END;
$$;
CREATE OR REPLACE FUNCTION "top_three_sales_per_employee"(IN "employee_nr" BIGINT)
RETURNS TABLE("sales" FLOAT) LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT
"public"."sale"."sale" AS "sales"
FROM
"public"."sale"
WHERE
employee_nr = "public"."sale"."employee_number"
ORDER BY
"public"."sale"."sale" DESC
LIMIT 3;
END;
$$;
CREATE OR REPLACE FUNCTION "product_of_product_line"(IN "p_line_in" VARCHAR)
RETURNS TABLE("p_id" BIGINT, "p_name" VARCHAR, "p_line" VARCHAR) LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT
"public"."product"."product_id" AS "p_id",
"public"."product"."product_name" AS "p_name",
"public"."product"."product_line" AS "p_line"
FROM
"public"."product"
WHERE
"p_line_in" = "public"."product"."product_line";
END;
$$;
CREATE OR REPLACE FUNCTION "update_msrp" (IN "id" BIGINT, IN "debit" INT)
RETURNS REAL
AS $$
UPDATE "public"."product"
SET "msrp" = "public"."product"."msrp" - "debit"
WHERE "public"."product"."product_id" = "id"
RETURNING "public"."product"."msrp";
$$ LANGUAGE sql;
/* USER-DEFINED VIEWS */
CREATE OR REPLACE VIEW "customer_master" AS
SELECT "customer"."customer_name",
"customer"."credit_limit",
"customerdetail"."city",
"customerdetail"."country",
"customerdetail"."address_line_first",
"customerdetail"."postal_code",
"customerdetail"."state"
FROM "customer"
JOIN "customerdetail" ON "customerdetail"."customer_number" = "customer"."customer_number"
WHERE "customer"."first_buy_date" IS NOT NULL;
CREATE OR REPLACE VIEW "office_master" AS
SELECT "office"."office_code",
"office"."city",
"office"."country",
"office"."state",
"office"."phone",
"office"."postal_code"
FROM "office"
WHERE "office"."city" IS NOT NULL;
CREATE OR REPLACE VIEW "product_master" AS
SELECT "product"."product_line",
"product"."product_name",
"product"."product_scale"
FROM "product";
/* END */
/*
*********************************************************************
http://www.mysqltutorial.org
*********************************************************************
Name: MySQL Sample Database classicmodels
Link: http://www.mysqltutorial.org/mysql-sample-database.aspx
*********************************************************************
This is a modified version of the original schema for MySQL
*/
Raw
schema3.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" varchar NOT NULL PRIMARY KEY);
INSERT INTO schema_migrations VALUES('20200619185311');
INSERT INTO schema_migrations VALUES('20200619185427');
INSERT INTO schema_migrations VALUES('20200619185837');
INSERT INTO schema_migrations VALUES('20200619193022');
INSERT INTO schema_migrations VALUES('20200619193650');
INSERT INTO schema_migrations VALUES('20200619193721');
INSERT INTO schema_migrations VALUES('20200619193737');
INSERT INTO schema_migrations VALUES('20200620042743');
INSERT INTO schema_migrations VALUES('20200620045102');
CREATE TABLE IF NOT EXISTS "ar_internal_metadata" ("key" varchar NOT NULL PRIMARY KEY, "value" varchar, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);
INSERT INTO ar_internal_metadata VALUES('environment','development','2020-07-23 01:59:21.368379','2020-07-23 01:59:21.368379');
CREATE TABLE IF NOT EXISTS "roles" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);
INSERT INTO roles VALUES(1,'admin','2020-07-23 01:59:24.656549','2020-07-23 01:59:24.656549');
INSERT INTO roles VALUES(2,'accountant','2020-07-23 01:59:24.683004','2020-07-23 01:59:24.683004');
INSERT INTO roles VALUES(3,'employee','2020-07-23 01:59:24.709098','2020-07-23 01:59:24.709098');
CREATE TABLE IF NOT EXISTS "users" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "email" varchar, "title" varchar, "location_id" integer, "organization_id" integer, "manager_id" integer, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);
INSERT INTO users VALUES(1,'alice@foo.com','CEO',1,1,NULL,'2020-07-23 01:59:24.803843','2020-07-23 01:59:24.803843');
INSERT INTO users VALUES(2,'keri@foo.com','CFO',3,1,1,'2020-07-23 01:59:24.849942','2020-07-23 01:59:24.849942');
INSERT INTO users VALUES(3,'bhavik@foo.com','Senior Accountant',1,1,2,'2020-07-23 01:59:24.893020','2020-07-23 01:59:24.893020');
INSERT INTO users VALUES(4,'cora@foo.com','Accountant',1,1,3,'2020-07-23 01:59:24.935930','2020-07-23 01:59:24.935930');
INSERT INTO users VALUES(5,'deirdre@foo.com','Director of Engineering',1,1,1,'2020-07-23 01:59:24.986995','2020-07-23 01:59:24.986995');
INSERT INTO users VALUES(6,'ebrahim@foo.com','Engineering Manager',1,1,5,'2020-07-23 01:59:25.045160','2020-07-23 01:59:25.045160');
INSERT INTO users VALUES(7,'frantz@foo.com','Software Engineer',1,1,6,'2020-07-23 01:59:25.082724','2020-07-23 01:59:25.082724');
INSERT INTO users VALUES(8,'greta@foo.com','Director of Sales',1,1,1,'2020-07-23 01:59:25.116264','2020-07-23 01:59:25.116264');
INSERT INTO users VALUES(9,'han@foo.com','Regional Sales Manager',2,1,8,'2020-07-23 01:59:25.169940','2020-07-23 01:59:25.169940');
INSERT INTO users VALUES(10,'iqbal@foo.com','Sales Rep',2,1,9,'2020-07-23 01:59:25.213552','2020-07-23 01:59:25.213552');
INSERT INTO users VALUES(11,'jose@foo.com','Accountant',2,1,3,'2020-07-23 01:59:25.257120','2020-07-23 01:59:25.257120');
INSERT INTO users VALUES(12,'lola@foo.com','CTO',3,1,1,'2020-07-23 01:59:25.299858','2020-07-23 01:59:25.299858');
INSERT INTO users VALUES(13,'matz@foo.com','CEO',1,2,NULL,'2020-07-23 01:59:30.488827','2020-07-23 01:59:30.488827');
CREATE TABLE IF NOT EXISTS "roles_users" ("user_id" integer NOT NULL, "role_id" integer NOT NULL);
INSERT INTO roles_users VALUES(1,1);
INSERT INTO roles_users VALUES(2,1);
INSERT INTO roles_users VALUES(3,2);
INSERT INTO roles_users VALUES(4,2);
INSERT INTO roles_users VALUES(5,3);
INSERT INTO roles_users VALUES(6,3);
INSERT INTO roles_users VALUES(7,3);
INSERT INTO roles_users VALUES(8,3);
INSERT INTO roles_users VALUES(9,3);
INSERT INTO roles_users VALUES(10,3);
INSERT INTO roles_users VALUES(11,2);
INSERT INTO roles_users VALUES(12,1);
INSERT INTO roles_users VALUES(13,1);
CREATE TABLE IF NOT EXISTS "expenses" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" integer, "amount" integer, "description" varchar, "project_id" integer, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, CONSTRAINT "fk_rails_c3ee69df61"
FOREIGN KEY ("user_id")
REFERENCES "users" ("id")
, CONSTRAINT "fk_rails_f097e0a9ca"
FOREIGN KEY ("project_id")
REFERENCES "projects" ("id")
);
INSERT INTO expenses VALUES(1,4,64165,'Trust fund pour-over.',6,'2020-07-23 01:59:26.793029','2020-07-23 01:59:26.793029');
INSERT INTO expenses VALUES(2,1,17743,'Pug irony.',8,'2020-07-23 01:59:26.829541','2020-07-23 01:59:26.829541');
INSERT INTO expenses VALUES(3,12,8092,'Gastropub viral.',7,'2020-07-23 01:59:26.862951','2020-07-23 01:59:26.862951');
INSERT INTO expenses VALUES(4,3,55013,'Intelligentsia health.',2,'2020-07-23 01:59:26.902323','2020-07-23 01:59:26.902323');
INSERT INTO expenses VALUES(5,11,7703,'Paleo kickstarter.',3,'2020-07-23 01:59:26.944763','2020-07-23 01:59:26.944763');
INSERT INTO expenses VALUES(6,10,6022,'Plaid tote bag.',3,'2020-07-23 01:59:26.977392','2020-07-23 01:59:26.977392');
INSERT INTO expenses VALUES(7,4,1075,'Locavore migas.',5,'2020-07-23 01:59:27.006154','2020-07-23 01:59:27.006154');
INSERT INTO expenses VALUES(8,2,72414,'Diy semiotics.',7,'2020-07-23 01:59:27.055030','2020-07-23 01:59:27.055030');
INSERT INTO expenses VALUES(9,5,62919,'Retro pabst.',6,'2020-07-23 01:59:27.097562','2020-07-23 01:59:27.097562');
INSERT INTO expenses VALUES(10,12,82684,'Shabby chic brooklyn.',7,'2020-07-23 01:59:27.132183','2020-07-23 01:59:27.132183');
INSERT INTO expenses VALUES(11,9,20696,'Biodiesel chartreuse.',1,'2020-07-23 01:59:27.165553','2020-07-23 01:59:27.165553');
INSERT INTO expenses VALUES(12,5,29639,'Swag cornhole.',5,'2020-07-23 01:59:27.210909','2020-07-23 01:59:27.210909');
INSERT INTO expenses VALUES(13,10,92267,'Tilde ramps.',1,'2020-07-23 01:59:27.248681','2020-07-23 01:59:27.248681');
INSERT INTO expenses VALUES(14,9,75724,'Yolo butcher.',1,'2020-07-23 01:59:27.277687','2020-07-23 01:59:27.277687');
INSERT INTO expenses VALUES(15,10,40051,'Carry pinterest.',1,'2020-07-23 01:59:27.316445','2020-07-23 01:59:27.316445');
INSERT INTO expenses VALUES(16,12,60910,'Food truck ethical.',7,'2020-07-23 01:59:27.355072','2020-07-23 01:59:27.355072');
INSERT INTO expenses VALUES(17,5,35384,'Polaroid mlkshk.',8,'2020-07-23 01:59:27.394964','2020-07-23 01:59:27.394964');
INSERT INTO expenses VALUES(18,6,42905,'Sustainable humblebrag.',6,'2020-07-23 01:59:27.436173','2020-07-23 01:59:27.436173');
INSERT INTO expenses VALUES(19,7,53212,'Ugh master.',5,'2020-07-23 01:59:27.472859','2020-07-23 01:59:27.472859');
INSERT INTO expenses VALUES(20,1,55754,'Bitters hashtag.',8,'2020-07-23 01:59:27.507571','2020-07-23 01:59:27.507571');
INSERT INTO expenses VALUES(21,12,4376,'8-bit dreamcatcher.',7,'2020-07-23 01:59:27.548086','2020-07-23 01:59:27.548086');
INSERT INTO expenses VALUES(22,3,8772,'Blog vhs.',2,'2020-07-23 01:59:27.582775','2020-07-23 01:59:27.582775');
INSERT INTO expenses VALUES(23,5,93244,'Gentrify flannel.',5,'2020-07-23 01:59:27.611932','2020-07-23 01:59:27.611932');
INSERT INTO expenses VALUES(24,3,13393,'Blog distillery.',6,'2020-07-23 01:59:27.647973','2020-07-23 01:59:27.647973');
INSERT INTO expenses VALUES(25,2,70243,'Vinegar meh.',7,'2020-07-23 01:59:27.689016','2020-07-23 01:59:27.689016');
INSERT INTO expenses VALUES(26,9,4656,'Portland semiotics.',1,'2020-07-23 01:59:27.718580','2020-07-23 01:59:27.718580');
INSERT INTO expenses VALUES(27,2,28668,'Sartorial mlkshk.',7,'2020-07-23 01:59:27.748297','2020-07-23 01:59:27.748297');
INSERT INTO expenses VALUES(28,8,59060,'Cornhole meh.',8,'2020-07-23 01:59:27.789877','2020-07-23 01:59:27.789877');
INSERT INTO expenses VALUES(29,4,3914,'Beard narwhal.',5,'2020-07-23 01:59:27.831786','2020-07-23 01:59:27.831786');
INSERT INTO expenses VALUES(30,8,73241,'Flexitarian farm-to-table.',8,'2020-07-23 01:59:27.867501','2020-07-23 01:59:27.867501');
INSERT INTO expenses VALUES(31,10,84047,'Chambray vice.',1,'2020-07-23 01:59:27.899671','2020-07-23 01:59:27.899671');
INSERT INTO expenses VALUES(32,6,22716,'Occupy bespoke.',2,'2020-07-23 01:59:27.937669','2020-07-23 01:59:27.937669');
INSERT INTO expenses VALUES(33,11,46972,'You probably haven''t heard of them mustache.',3,'2020-07-23 01:59:27.969329','2020-07-23 01:59:27.969329');
INSERT INTO expenses VALUES(34,8,71749,'Church-key locavore.',8,'2020-07-23 01:59:28.015755','2020-07-23 01:59:28.015755');
INSERT INTO expenses VALUES(35,12,93152,'Sustainable portland.',7,'2020-07-23 01:59:28.054563','2020-07-23 01:59:28.054563');
INSERT INTO expenses VALUES(36,3,94527,'Diy chillwave.',6,'2020-07-23 01:59:28.088886','2020-07-23 01:59:28.088886');
INSERT INTO expenses VALUES(37,6,62378,'Vhs poutine.',2,'2020-07-23 01:59:28.129478','2020-07-23 01:59:28.129478');
INSERT INTO expenses VALUES(38,6,33062,'Etsy scenester.',5,'2020-07-23 01:59:28.163469','2020-07-23 01:59:28.163469');
INSERT INTO expenses VALUES(39,12,73320,'Vhs carry.',7,'2020-07-23 01:59:28.197837','2020-07-23 01:59:28.197837');
INSERT INTO expenses VALUES(40,9,67408,'Slow-carb art party.',3,'2020-07-23 01:59:28.245418','2020-07-23 01:59:28.245418');
INSERT INTO expenses VALUES(41,11,63633,'Banh mi cleanse.',3,'2020-07-23 01:59:28.282211','2020-07-23 01:59:28.282211');
INSERT INTO expenses VALUES(42,1,21737,'Selfies loko.',5,'2020-07-23 01:59:28.316213','2020-07-23 01:59:28.316213');
INSERT INTO expenses VALUES(43,1,45714,'Phlogiston hoodie.',8,'2020-07-23 01:59:28.355364','2020-07-23 01:59:28.355364');
INSERT INTO expenses VALUES(44,11,81268,'Slow-carb tote bag.',3,'2020-07-23 01:59:28.390195','2020-07-23 01:59:28.390195');
INSERT INTO expenses VALUES(45,1,79875,'Waistcoat lumbersexual.',6,'2020-07-23 01:59:28.429581','2020-07-23 01:59:28.429581');
INSERT INTO expenses VALUES(46,8,77942,'Echo polaroid.',9,'2020-07-23 01:59:28.470106','2020-07-23 01:59:28.470106');
INSERT INTO expenses VALUES(47,3,82163,'Deep v migas.',6,'2020-07-23 01:59:28.510865','2020-07-23 01:59:28.510865');
INSERT INTO expenses VALUES(48,12,54165,'Swag trust fund.',4,'2020-07-23 01:59:28.542343','2020-07-23 01:59:28.542343');
INSERT INTO expenses VALUES(49,2,10209,'Whatever viral.',7,'2020-07-23 01:59:28.578791','2020-07-23 01:59:28.578791');
INSERT INTO expenses VALUES(50,3,21358,'Farm-to-table lomo.',5,'2020-07-23 01:59:28.610228','2020-07-23 01:59:28.610228');
INSERT INTO expenses VALUES(51,7,90780,'Typewriter carry.',2,'2020-07-23 01:59:28.649679','2020-07-23 01:59:28.649679');
INSERT INTO expenses VALUES(52,11,22351,'Quinoa neutra.',1,'2020-07-23 01:59:28.672660','2020-07-23 01:59:28.672660');
INSERT INTO expenses VALUES(53,7,12513,'Squid iphone.',6,'2020-07-23 01:59:28.715897','2020-07-23 01:59:28.715897');
INSERT INTO expenses VALUES(54,3,74361,'Vhs mumblecore.',8,'2020-07-23 01:59:28.756867','2020-07-23 01:59:28.756867');
INSERT INTO expenses VALUES(55,11,56747,'Iphone salvia.',1,'2020-07-23 01:59:28.783876','2020-07-23 01:59:28.783876');
INSERT INTO expenses VALUES(56,11,97009,'Crucifix distillery.',1,'2020-07-23 01:59:28.824702','2020-07-23 01:59:28.824702');
INSERT INTO expenses VALUES(57,5,62993,'Bicycle rights quinoa.',9,'2020-07-23 01:59:28.864023','2020-07-23 01:59:28.864023');
INSERT INTO expenses VALUES(58,5,47652,'Pbr&b leggings.',2,'2020-07-23 01:59:28.908834','2020-07-23 01:59:28.908834');
INSERT INTO expenses VALUES(59,2,53979,'Xoxo celiac.',4,'2020-07-23 01:59:28.941396','2020-07-23 01:59:28.941396');
INSERT INTO expenses VALUES(60,12,47715,'Tousled ugh.',7,'2020-07-23 01:59:28.966652','2020-07-23 01:59:28.966652');
INSERT INTO expenses VALUES(61,3,40337,'Tofu viral.',5,'2020-07-23 01:59:28.994543','2020-07-23 01:59:28.994543');
INSERT INTO expenses VALUES(62,4,74212,'Forage master.',2,'2020-07-23 01:59:29.023798','2020-07-23 01:59:29.023798');
INSERT INTO expenses VALUES(63,2,2836,'Echo drinking.',10,'2020-07-23 01:59:29.061061','2020-07-23 01:59:29.061061');
INSERT INTO expenses VALUES(64,10,34003,'Irony portland.',3,'2020-07-23 01:59:29.101656','2020-07-23 01:59:29.101656');
INSERT INTO expenses VALUES(65,12,44382,'Post-ironic stumptown.',4,'2020-07-23 01:59:29.135824','2020-07-23 01:59:29.135824');
INSERT INTO expenses VALUES(66,2,89275,'Phlogiston church-key.',4,'2020-07-23 01:59:29.169962','2020-07-23 01:59:29.169962');
INSERT INTO expenses VALUES(67,12,52967,'Mlkshk flannel.',7,'2020-07-23 01:59:29.197831','2020-07-23 01:59:29.197831');
INSERT INTO expenses VALUES(68,12,18522,'Synth green juice.',10,'2020-07-23 01:59:29.238667','2020-07-23 01:59:29.238667');
INSERT INTO expenses VALUES(69,12,4736,'Fashion axe semiotics.',4,'2020-07-23 01:59:29.274958','2020-07-23 01:59:29.274958');
INSERT INTO expenses VALUES(70,8,46020,'Bitters ennui.',8,'2020-07-23 01:59:29.311330','2020-07-23 01:59:29.311330');
INSERT INTO expenses VALUES(71,12,7245,'Williamsburg butcher.',10,'2020-07-23 01:59:29.345242','2020-07-23 01:59:29.345242');
INSERT INTO expenses VALUES(72,9,46437,'Polaroid occupy.',3,'2020-07-23 01:59:29.381216','2020-07-23 01:59:29.381216');
INSERT INTO expenses VALUES(73,12,57370,'Pop-up flexitarian.',4,'2020-07-23 01:59:29.421942','2020-07-23 01:59:29.421942');
INSERT INTO expenses VALUES(74,8,92729,'Tattooed brunch.',8,'2020-07-23 01:59:29.444160','2020-07-23 01:59:29.444160');
INSERT INTO expenses VALUES(75,2,40273,'Vinegar polaroid.',7,'2020-07-23 01:59:29.471505','2020-07-23 01:59:29.471505');
INSERT INTO expenses VALUES(76,10,48961,'Truffaut wolf.',1,'2020-07-23 01:59:29.506802','2020-07-23 01:59:29.506802');
INSERT INTO expenses VALUES(77,12,19606,'Lumbersexual tofu.',7,'2020-07-23 01:59:29.530067','2020-07-23 01:59:29.530067');
INSERT INTO expenses VALUES(78,2,1413,'Wes anderson drinking.',10,'2020-07-23 01:59:29.556927','2020-07-23 01:59:29.556927');
INSERT INTO expenses VALUES(79,9,27631,'Intelligentsia iphone.',1,'2020-07-23 01:59:29.612244','2020-07-23 01:59:29.612244');
INSERT INTO expenses VALUES(80,12,8181,'Selfies kogi.',10,'2020-07-23 01:59:29.658125','2020-07-23 01:59:29.658125');
INSERT INTO expenses VALUES(81,6,35172,'Skateboard synth.',9,'2020-07-23 01:59:29.698597','2020-07-23 01:59:29.698597');
INSERT INTO expenses VALUES(82,3,50251,'Umami fingerstache.',6,'2020-07-23 01:59:29.735382','2020-07-23 01:59:29.735382');
INSERT INTO expenses VALUES(83,7,85491,'Mumblecore sriracha.',9,'2020-07-23 01:59:29.782195','2020-07-23 01:59:29.782195');
INSERT INTO expenses VALUES(84,5,23815,'Deep v mlkshk.',9,'2020-07-23 01:59:29.812659','2020-07-23 01:59:29.812659');
INSERT INTO expenses VALUES(85,3,67684,'Church-key wes anderson.',9,'2020-07-23 01:59:29.853565','2020-07-23 01:59:29.853565');
INSERT INTO expenses VALUES(86,5,91711,'Williamsburg vhs.',6,'2020-07-23 01:59:29.885666','2020-07-23 01:59:29.885666');
INSERT INTO expenses VALUES(87,6,11407,'Mustache heirloom.',5,'2020-07-23 01:59:29.929122','2020-07-23 01:59:29.929122');
INSERT INTO expenses VALUES(88,1,58385,'Street shabby chic.',8,'2020-07-23 01:59:29.958955','2020-07-23 01:59:29.958955');
INSERT INTO expenses VALUES(89,5,31278,'Neutra bitters.',9,'2020-07-23 01:59:29.999538','2020-07-23 01:59:29.999538');
INSERT INTO expenses VALUES(90,9,43679,'Microdosing polaroid.',3,'2020-07-23 01:59:30.031723','2020-07-23 01:59:30.031723');
INSERT INTO expenses VALUES(91,12,13581,'Listicle squid.',7,'2020-07-23 01:59:30.072248','2020-07-23 01:59:30.072248');
INSERT INTO expenses VALUES(92,4,72905,'Whatever truffaut.',6,'2020-07-23 01:59:30.102800','2020-07-23 01:59:30.102800');
INSERT INTO expenses VALUES(93,7,83728,'Neutra banh mi.',6,'2020-07-23 01:59:30.135553','2020-07-23 01:59:30.135553');
INSERT INTO expenses VALUES(94,2,40782,'Taxidermy retro.',10,'2020-07-23 01:59:30.164569','2020-07-23 01:59:30.164569');
INSERT INTO expenses VALUES(95,9,90313,'Literally sartorial.',1,'2020-07-23 01:59:30.199102','2020-07-23 01:59:30.199102');
INSERT INTO expenses VALUES(96,4,71548,'Stumptown portland.',5,'2020-07-23 01:59:30.234590','2020-07-23 01:59:30.234590');
INSERT INTO expenses VALUES(97,9,19812,'90''s helvetica.',3,'2020-07-23 01:59:30.274835','2020-07-23 01:59:30.274835');
INSERT INTO expenses VALUES(98,12,7354,'Dreamcatcher yolo.',4,'2020-07-23 01:59:30.304072','2020-07-23 01:59:30.304072');
INSERT INTO expenses VALUES(99,7,81731,'Yr literally.',9,'2020-07-23 01:59:30.348982','2020-07-23 01:59:30.348982');
INSERT INTO expenses VALUES(100,8,43546,'Blue bottle pug.',9,'2020-07-23 01:59:30.391978','2020-07-23 01:59:30.391978');
INSERT INTO expenses VALUES(101,13,45476,'Hire a copywriter to create some job postings.',11,'2020-07-23 01:59:30.560484','2020-07-23 01:59:30.560484');
CREATE TABLE IF NOT EXISTS "projects" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "team_id" integer, "location_id" integer, "name" varchar, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);
INSERT INTO projects VALUES(1,3,2,'Equity Investment Instruments','2020-07-23 01:59:26.410163','2020-07-23 01:59:26.410163');
INSERT INTO projects VALUES(2,1,1,'Clothing & Accessories','2020-07-23 01:59:26.453004','2020-07-23 01:59:26.453004');
INSERT INTO projects VALUES(3,2,2,'Medical Supplies','2020-07-23 01:59:26.477936','2020-07-23 01:59:26.477936');
INSERT INTO projects VALUES(4,3,3,'Apparel Retailers','2020-07-23 01:59:26.506309','2020-07-23 01:59:26.506309');
INSERT INTO projects VALUES(5,2,1,'Medical Supplies','2020-07-23 01:59:26.537835','2020-07-23 01:59:26.537835');
INSERT INTO projects VALUES(6,3,1,'Electronic Equipment','2020-07-23 01:59:26.587487','2020-07-23 01:59:26.587487');
INSERT INTO projects VALUES(7,1,3,'Iron & Steel','2020-07-23 01:59:26.625163','2020-07-23 01:59:26.625163');
INSERT INTO projects VALUES(8,3,1,'Reinsurance','2020-07-23 01:59:26.666440','2020-07-23 01:59:26.666440');
INSERT INTO projects VALUES(9,3,1,'Forestry','2020-07-23 01:59:26.696504','2020-07-23 01:59:26.696504');
INSERT INTO projects VALUES(10,2,3,'Brewers','2020-07-23 01:59:26.731228','2020-07-23 01:59:26.731228');
INSERT INTO projects VALUES(11,5,1,'Hire first employee','2020-07-23 01:59:30.528606','2020-07-23 01:59:30.528606');
CREATE TABLE IF NOT EXISTS "teams" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar, "organization_id" integer, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, CONSTRAINT "fk_rails_f07f0bd66d"
FOREIGN KEY ("organization_id")
REFERENCES "organizations" ("id")
);
INSERT INTO teams VALUES(1,'Engineering',1,'2020-07-23 01:59:24.535388','2020-07-23 01:59:24.535388');
INSERT INTO teams VALUES(2,'Leadership',1,'2020-07-23 01:59:24.568823','2020-07-23 01:59:24.568823');
INSERT INTO teams VALUES(3,'Finance',1,'2020-07-23 01:59:24.593442','2020-07-23 01:59:24.593442');
INSERT INTO teams VALUES(4,'Sales',1,'2020-07-23 01:59:24.615934','2020-07-23 01:59:24.615934');
INSERT INTO teams VALUES(5,'Leadership',2,'2020-07-23 01:59:30.447400','2020-07-23 01:59:30.447400');
CREATE TABLE IF NOT EXISTS "organizations" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);
INSERT INTO organizations VALUES(1,'Foo Industries','2020-07-23 01:59:24.481020','2020-07-23 01:59:24.481020');
INSERT INTO organizations VALUES(2,'Bar, Inc.','2020-07-23 01:59:30.418613','2020-07-23 01:59:30.418613');
CREATE TABLE IF NOT EXISTS "locations" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);
INSERT INTO locations VALUES(1,'NYC','2020-07-23 01:59:24.383830','2020-07-23 01:59:24.383830');
INSERT INTO locations VALUES(2,'Milwaukee','2020-07-23 01:59:24.411004','2020-07-23 01:59:24.411004');
INSERT INTO locations VALUES(3,'Crestone','2020-07-23 01:59:24.444765','2020-07-23 01:59:24.444765');
CREATE TABLE IF NOT EXISTS "teams_users" ("user_id" integer NOT NULL, "team_id" integer NOT NULL);
INSERT INTO teams_users VALUES(1,2);
INSERT INTO teams_users VALUES(2,2);
INSERT INTO teams_users VALUES(3,3);
INSERT INTO teams_users VALUES(4,3);
INSERT INTO teams_users VALUES(5,1);
INSERT INTO teams_users VALUES(6,1);
INSERT INTO teams_users VALUES(7,1);
INSERT INTO teams_users VALUES(8,4);
INSERT INTO teams_users VALUES(9,4);
INSERT INTO teams_users VALUES(10,4);
INSERT INTO teams_users VALUES(11,3);
INSERT INTO teams_users VALUES(12,2);
INSERT INTO teams_users VALUES(13,5);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('locations',3);
INSERT INTO sqlite_sequence VALUES('organizations',2);
INSERT INTO sqlite_sequence VALUES('teams',5);
INSERT INTO sqlite_sequence VALUES('roles',3);
INSERT INTO sqlite_sequence VALUES('users',13);
INSERT INTO sqlite_sequence VALUES('projects',11);
INSERT INTO sqlite_sequence VALUES('expenses',101);
CREATE INDEX "index_users_on_location_id" ON "users" ("location_id");
CREATE INDEX "index_users_on_organization_id" ON "users" ("organization_id");
CREATE INDEX "index_users_on_manager_id" ON "users" ("manager_id");
CREATE INDEX "index_roles_users_on_user_id" ON "roles_users" ("user_id");
CREATE INDEX "index_roles_users_on_role_id" ON "roles_users" ("role_id");
CREATE INDEX "index_expenses_on_user_id" ON "expenses" ("user_id");
CREATE INDEX "index_expenses_on_project_id" ON "expenses" ("project_id");
CREATE INDEX "index_projects_on_team_id" ON "projects" ("team_id");
CREATE INDEX "index_projects_on_location_id" ON "projects" ("location_id");
CREATE INDEX "index_teams_on_organization_id" ON "teams" ("organization_id");
CREATE INDEX "index_teams_users_on_user_id" ON "teams_users" ("user_id");
CREATE INDEX "index_teams_users_on_team_id" ON "teams_users" ("team_id");
COMMIT;
Raw
schema4.sql
-- Sakila Spatial Sample Database Schema
-- Version 0.9
-- Copyright (c) 2014, Oracle Corporation
-- All rights reserved.
-- Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
-- * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
-- * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
-- * Neither the name of Oracle Corporation nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
-- Modified in September 2015 by Giuseppe Maxia
-- The schema and data can now be loaded by any MySQL 5.x version.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
DROP SCHEMA IF EXISTS sakila;
CREATE SCHEMA sakila;
USE sakila;
--
-- Table structure for table `actor`
--
CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),
KEY idx_actor_last_name (last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `address`
--
CREATE TABLE address (
address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
address VARCHAR(50) NOT NULL,
address2 VARCHAR(50) DEFAULT NULL,
district VARCHAR(20) NOT NULL,
city_id SMALLINT UNSIGNED NOT NULL,
postal_code VARCHAR(10) DEFAULT NULL,
phone VARCHAR(20) NOT NULL,
/*!50705 location GEOMETRY NOT NULL,*/
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (address_id),
KEY idx_fk_city_id (city_id),
/*!50705 SPATIAL KEY `idx_location` (location),*/
CONSTRAINT `fk_address_city` FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `category`
--
CREATE TABLE category (
category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (category_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `city`
--
CREATE TABLE city (
city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
city VARCHAR(50) NOT NULL,
country_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (city_id),
KEY idx_fk_country_id (country_id),
CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `country`
--
CREATE TABLE country (
country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
country VARCHAR(50) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `customer`
--
CREATE TABLE customer (
customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
store_id TINYINT UNSIGNED NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
email VARCHAR(50) DEFAULT NULL,
address_id SMALLINT UNSIGNED NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
create_date DATETIME NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (customer_id),
KEY idx_fk_store_id (store_id),
KEY idx_fk_address_id (address_id),
KEY idx_last_name (last_name),
CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `film`
--
CREATE TABLE film (
film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
release_year YEAR DEFAULT NULL,
language_id TINYINT UNSIGNED NOT NULL,
original_language_id TINYINT UNSIGNED DEFAULT NULL,
rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
length SMALLINT UNSIGNED DEFAULT NULL,
replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (film_id),
KEY idx_title (title),
KEY idx_fk_language_id (language_id),
KEY idx_fk_original_language_id (original_language_id),
CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `film_actor`
--
CREATE TABLE film_actor (
actor_id SMALLINT UNSIGNED NOT NULL,
film_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id,film_id),
KEY idx_fk_film_id (`film_id`),
CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `film_category`
--
CREATE TABLE film_category (
film_id SMALLINT UNSIGNED NOT NULL,
category_id TINYINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (film_id, category_id),
CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `film_text`
--
CREATE TABLE film_text (
film_id SMALLINT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
PRIMARY KEY (film_id),
FULLTEXT KEY idx_title_description (title,description)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- After MySQL 5.6.10, InnoDB supports fulltext indexes
/*!50610 ALTER TABLE film_text engine=InnoDB */;
--
-- Triggers for loading film_text from film
--
DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
INSERT INTO film_text (film_id, title, description)
VALUES (new.film_id, new.title, new.description);
END;;
CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
IF (old.title != new.title) OR (old.description != new.description) OR (old.film_id != new.film_id)
THEN
UPDATE film_text
SET title=new.title,
description=new.description,
film_id=new.film_id
WHERE film_id=old.film_id;
END IF;
END;;
CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
DELETE FROM film_text WHERE film_id = old.film_id;
END;;
DELIMITER ;
--
-- Table structure for table `inventory`
--
CREATE TABLE inventory (
inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
film_id SMALLINT UNSIGNED NOT NULL,
store_id TINYINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (inventory_id),
KEY idx_fk_film_id (film_id),
KEY idx_store_id_film_id (store_id,film_id),
CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `language`
--
CREATE TABLE language (
language_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (language_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `payment`
--
CREATE TABLE payment (
payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id SMALLINT UNSIGNED NOT NULL,
staff_id TINYINT UNSIGNED NOT NULL,
rental_id INT DEFAULT NULL,
amount DECIMAL(5,2) NOT NULL,
payment_date DATETIME NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (payment_id),
KEY idx_fk_staff_id (staff_id),
KEY idx_fk_customer_id (customer_id),
CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `rental`
--
CREATE TABLE rental (
rental_id INT NOT NULL AUTO_INCREMENT,
rental_date DATETIME NOT NULL,
inventory_id MEDIUMINT UNSIGNED NOT NULL,
customer_id SMALLINT UNSIGNED NOT NULL,
return_date DATETIME DEFAULT NULL,
staff_id TINYINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (rental_id),
UNIQUE KEY (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `staff`
--
CREATE TABLE staff (
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
address_id SMALLINT UNSIGNED NOT NULL,
picture BLOB DEFAULT NULL,
email VARCHAR(50) DEFAULT NULL,
store_id TINYINT UNSIGNED NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
username VARCHAR(16) NOT NULL,
password VARCHAR(40) BINARY DEFAULT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (staff_id),
KEY idx_fk_store_id (store_id),
KEY idx_fk_address_id (address_id),
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `store`
--
CREATE TABLE store (
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
manager_staff_id TINYINT UNSIGNED NOT NULL,
address_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (store_id),
UNIQUE KEY idx_unique_manager (manager_staff_id),
KEY idx_fk_address_id (address_id),
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- View structure for view `customer_list`
--
CREATE VIEW customer_list
AS
SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name, a.address AS address, a.postal_code AS `zip code`,
a.phone AS phone, city.city AS city, country.country AS country, IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
JOIN country ON city.country_id = country.country_id;
--
-- View structure for view `film_list`
--
CREATE VIEW film_list
AS
SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id, category.name;
--
-- View structure for view `nicer_but_slower_film_list`
--
CREATE VIEW nicer_but_slower_film_list
AS
SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id, category.name;
--
-- View structure for view `staff_list`
--
CREATE VIEW staff_list
AS
SELECT s.staff_id AS ID, CONCAT(s.first_name, _utf8' ', s.last_name) AS name, a.address AS address, a.postal_code AS `zip code`, a.phone AS phone,
city.city AS city, country.country AS country, s.store_id AS SID
FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
JOIN country ON city.country_id = country.country_id;
--
-- View structure for view `sales_by_store`
--
CREATE VIEW sales_by_store
AS
SELECT
CONCAT(c.city, _utf8',', cy.country) AS store
, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN store AS s ON i.store_id = s.store_id
INNER JOIN address AS a ON s.address_id = a.address_id
INNER JOIN city AS c ON a.city_id = c.city_id
INNER JOIN country AS cy ON c.country_id = cy.country_id
INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
GROUP BY s.store_id
ORDER BY cy.country, c.city;
--
-- View structure for view `sales_by_film_category`
--
-- Note that total sales will add up to >100% because
-- some titles belong to more than 1 category
--
CREATE VIEW sales_by_film_category
AS
SELECT
c.name AS category
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN film AS f ON i.film_id = f.film_id
INNER JOIN film_category AS fc ON f.film_id = fc.film_id
INNER JOIN category AS c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY total_sales DESC;
--
-- View structure for view `actor_info`
--
CREATE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW actor_info
AS
SELECT
a.actor_id,
a.first_name,
a.last_name,
GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
(SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
FROM sakila.film f
INNER JOIN sakila.film_category fc
ON f.film_id = fc.film_id
INNER JOIN sakila.film_actor fa
ON f.film_id = fa.film_id
WHERE fc.category_id = c.category_id
AND fa.actor_id = a.actor_id
)
)
ORDER BY c.name SEPARATOR '; ')
AS film_info
FROM sakila.actor a
LEFT JOIN sakila.film_actor fa
ON a.actor_id = fa.actor_id
LEFT JOIN sakila.film_category fc
ON fa.film_id = fc.film_id
LEFT JOIN sakila.category c
ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name;
--
-- Procedure structure for procedure `rewards_report`
--
DELIMITER //
CREATE PROCEDURE rewards_report (
IN min_monthly_purchases TINYINT UNSIGNED
, IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
, OUT count_rewardees INT
)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 'Provides a customizable report on best customers'
proc: BEGIN
DECLARE last_month_start DATE;
DECLARE last_month_end DATE;
/* Some sanity checks... */
IF min_monthly_purchases = 0 THEN
SELECT 'Minimum monthly purchases parameter must be > 0';
LEAVE proc;
END IF;
IF min_dollar_amount_purchased = 0.00 THEN
SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
LEAVE proc;
END IF;
/* Determine start and end time periods */
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
SET last_month_end = LAST_DAY(last_month_start);
/*
Create a temporary storage area for
Customer IDs.
*/
CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
/*
Find all customers meeting the
monthly purchase requirements
*/
INSERT INTO tmpCustomer (customer_id)
SELECT p.customer_id
FROM payment AS p
WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
GROUP BY customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased
AND COUNT(customer_id) > min_monthly_purchases;
/* Populate OUT parameter with count of found customers */
SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
/*
Output ALL customer information of matching rewardees.
Customize output as needed.
*/
SELECT c.*
FROM tmpCustomer AS t
INNER JOIN customer AS c ON t.customer_id = c.customer_id;
/* Clean up */
DROP TABLE tmpCustomer;
END //
DELIMITER ;
DELIMITER $$
CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
DETERMINISTIC
READS SQL DATA
BEGIN
#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
# 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
# 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
# 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
FROM film, inventory, rental
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
FROM rental, inventory, film
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id;
RETURN v_rentfees + v_overfees - v_payments;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
READS SQL DATA
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE film_not_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
READS SQL DATA
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND NOT inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INT
READS SQL DATA
BEGIN
DECLARE v_customer_id INT;
DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
SELECT customer_id INTO v_customer_id
FROM rental
WHERE return_date IS NULL
AND inventory_id = p_inventory_id;
RETURN v_customer_id;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
READS SQL DATA
BEGIN
DECLARE v_rentals INT;
DECLARE v_out INT;
#AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
#FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
SELECT COUNT(*) INTO v_rentals
FROM rental
WHERE inventory_id = p_inventory_id;
IF v_rentals = 0 THEN
RETURN TRUE;
END IF;
SELECT COUNT(rental_id) INTO v_out
FROM inventory LEFT JOIN rental USING(inventory_id)
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;
IF v_out > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END $$
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Raw
schema5.sql
-- Sample employee database
-- See changelog table for details
-- Copyright (C) 2007,2008, MySQL AB
--
-- Original data created by Fusheng Wang and Carlo Zaniolo
-- http://www.cs.aau.dk/TimeCenter/software.htm
-- http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip
--
-- Current schema by Giuseppe Maxia
-- Data conversion from XML to relational by Patrick Crews
--
-- This work is licensed under the
-- Creative Commons Attribution-Share Alike 3.0 Unported License.
-- To view a copy of this license, visit
-- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to
-- Creative Commons, 171 Second Street, Suite 300, San Francisco,
-- California, 94105, USA.
--
-- DISCLAIMER
-- To the best of our knowledge, this data is fabricated, and
-- it does not correspond to real people.
-- Any similarity to existing people is purely coincidental.
--
DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;
SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
DROP TABLE IF EXISTS dept_emp,
dept_manager,
titles,
salaries,
employees,
departments;
/*!50503 set default_storage_engine = InnoDB */;
/*!50503 select CONCAT('storage engine: ', @@default_storage_engine) as INFO */;
CREATE TABLE employees (
emp_no INT NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
);
CREATE TABLE departments (
dept_no CHAR(4) NOT NULL,
dept_name VARCHAR(40) NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE KEY (dept_name)
);
CREATE TABLE dept_manager (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,dept_no)
);
CREATE TABLE dept_emp (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,dept_no)
);
CREATE TABLE titles (
emp_no INT NOT NULL,
title VARCHAR(50) NOT NULL,
from_date DATE NOT NULL,
to_date DATE,
# FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,title, from_date)
);
/*!50130
ALTER TABLE titles
partition by range (to_days(from_date))
(
partition p01 values less than (to_days('1985-12-31')),
partition p02 values less than (to_days('1986-12-31')),
partition p03 values less than (to_days('1987-12-31')),
partition p04 values less than (to_days('1988-12-31')),
partition p05 values less than (to_days('1989-12-31')),
partition p06 values less than (to_days('1990-12-31')),
partition p07 values less than (to_days('1991-12-31')),
partition p08 values less than (to_days('1992-12-31')),
partition p09 values less than (to_days('1993-12-31')),
partition p10 values less than (to_days('1994-12-31')),
partition p11 values less than (to_days('1995-12-31')),
partition p12 values less than (to_days('1996-12-31')),
partition p13 values less than (to_days('1997-12-31')),
partition p14 values less than (to_days('1998-12-31')),
partition p15 values less than (to_days('1999-12-31')),
partition p16 values less than (to_days('2000-12-31')),
partition p17 values less than (to_days('2001-12-31')),
partition p18 values less than (to_days('2002-12-31')),
partition p19 values less than (to_days('3000-12-31'))
) */;
CREATE TABLE salaries (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
# FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
);
/*!50130
ALTER TABLE salaries
partition by range (to_days(from_date))
(
partition p01 values less than (to_days('1985-01-01')),
partition p02 values less than (to_days('1986-01-01')),
partition p03 values less than (to_days('1987-01-01')),
partition p04 values less than (to_days('1988-01-01')),
partition p05 values less than (to_days('1989-01-01')),
partition p06 values less than (to_days('1990-01-01')),
partition p07 values less than (to_days('1991-01-01')),
partition p08 values less than (to_days('1992-01-01')),
partition p09 values less than (to_days('1993-01-01')),
partition p10 values less than (to_days('1994-01-01')),
partition p11 values less than (to_days('1995-01-01')),
partition p12 values less than (to_days('1996-01-01')),
partition p13 values less than (to_days('1997-01-01')),
partition p14 values less than (to_days('1998-01-01')),
partition p15 values less than (to_days('1999-01-01')),
partition p16 values less than (to_days('2000-01-01')),
partition p17 values less than (to_days('2001-01-01')),
partition p18 values less than (to_days('2001-02-01')),
partition p19 values less than (to_days('2001-03-01')),
partition p20 values less than (to_days('2001-04-01')),
partition p21 values less than (to_days('2001-05-01')),
partition p22 values less than (to_days('2001-06-01')),
partition p23 values less than (to_days('2001-07-01')),
partition p24 values less than (to_days('2001-08-01')),
partition p25 values less than (to_days('2001-09-01')),
partition p26 values less than (to_days('2001-10-01')),
partition p27 values less than (to_days('2001-11-01')),
partition p28 values less than (to_days('2001-12-01')),
partition p29 values less than (to_days('2002-01-01')),
partition p30 values less than (to_days('2002-02-01')),
partition p31 values less than (to_days('2002-03-01')),
partition p32 values less than (to_days('2002-04-01')),
partition p33 values less than (to_days('2002-05-01')),
partition p34 values less than (to_days('2002-06-01')),
partition p35 values less than (to_days('2002-07-01')),
partition p36 values less than (to_days('2002-08-01')),
partition p37 values less than (to_days('2002-09-01')),
partition p38 values less than (to_days('2002-10-01')),
partition p39 values less than (to_days('2002-11-01')),
partition p40 values less than (to_days('2002-12-01')),
partition p41 values less than (to_days('3000-01-01'))
)
*/;
CREATE OR REPLACE VIEW dept_emp_latest_date AS
SELECT emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
FROM dept_emp
GROUP BY emp_no;
# shows only the current department for each employee
CREATE OR REPLACE VIEW current_dept_emp AS
SELECT l.emp_no, dept_no, l.from_date, l.to_date
FROM dept_emp d
INNER JOIN dept_emp_latest_date l
ON d.emp_no=l.emp_no AND d.from_date=l.from_date AND l.to_date = d.to_date;
flush /*!50503 binary */ logs;
SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;
SELECT 'LOADING employees' as 'INFO';
source load_employees.dump ;
SELECT 'LOADING dept_emp' as 'INFO';
source load_dept_emp.dump ;
SELECT 'LOADING dept_manager' as 'INFO';
source load_dept_manager.dump ;
SELECT 'LOADING titles' as 'INFO';
source load_titles.dump ;
SELECT 'LOADING salaries' as 'INFO';
source load_salaries1.dump ;
source load_salaries2.dump ;
source load_salaries3.dump ;
source show_elapsed.sql ;
Raw
schema6.sql
use employees;
delimiter //
drop function if exists emp_dept_id //
drop function if exists emp_dept_name //
drop function if exists emp_name //
drop function if exists current_manager //
drop procedure if exists show_departments //
--
-- returns the department id of a given employee
--
create function emp_dept_id( employee_id int )
returns char(4)
reads sql data
begin
declare max_date date;
set max_date = (
select
max(from_date)
from
dept_emp
where
emp_no = employee_id
);
set @max_date=max_date;
return (
select
dept_no
from
dept_emp
where
emp_no = employee_id
and
from_date = max_date
limit 1
);
end //
--
-- returns the department name of a given employee
--
create function emp_dept_name( employee_id int )
returns varchar(40)
reads sql data
begin
return (
select
dept_name
from
departments
where
dept_no = emp_dept_id(employee_id)
);
end//
--
-- returns the employee name of a given employee id
--
create function emp_name (employee_id int)
returns varchar(32)
reads SQL data
begin
return (
select
concat(first_name, ' ', last_name) as name
from
employees
where
emp_no = employee_id
);
end//
--
-- returns the manager of a department
-- choosing the most recent one
-- from the manager list
--
create function current_manager( dept_id char(4) )
returns varchar(32)
reads sql data
begin
declare max_date date;
set max_date = (
select
max(from_date)
from
dept_manager
where
dept_no = dept_id
);
set @max_date=max_date;
return (
select
emp_name(emp_no)
from
dept_manager
where
dept_no = dept_id
and
from_date = max_date
limit 1
);
end //
delimiter ;
--
-- selects the employee records with the
-- latest department
--
CREATE OR REPLACE VIEW v_full_employees
AS
SELECT
emp_no,
first_name , last_name ,
birth_date , gender,
hire_date,
emp_dept_name(emp_no) as department
from
employees;
--
-- selects the department list with manager names
--
CREATE OR REPLACE VIEW v_full_departments
AS
SELECT
dept_no, dept_name, current_manager(dept_no) as manager
FROM
departments;
delimiter //
--
-- shows the departments with the number of employees
-- per department
--
create procedure show_departments()
modifies sql data
begin
DROP TABLE IF EXISTS department_max_date;
DROP TABLE IF EXISTS department_people;
CREATE TEMPORARY TABLE department_max_date
(
emp_no int not null primary key,
dept_from_date date not null,
dept_to_date date not null, # bug#320513
KEY (dept_from_date, dept_to_date)
);
INSERT INTO department_max_date
SELECT
emp_no, max(from_date), max(to_date)
FROM
dept_emp
GROUP BY
emp_no;
CREATE TEMPORARY TABLE department_people
(
emp_no int not null,
dept_no char(4) not null,
primary key (emp_no, dept_no)
);
insert into department_people
select dmd.emp_no, dept_no
from
department_max_date dmd
inner join dept_emp de
on dmd.dept_from_date=de.from_date
and dmd.dept_to_date=de.to_date
and dmd.emp_no=de.emp_no;
SELECT
dept_no,dept_name,manager, count(*)
from v_full_departments
inner join department_people using (dept_no)
group by dept_no;
# with rollup;
DROP TABLE department_max_date;
DROP TABLE department_people;
end //
drop function if exists employees_usage //
drop procedure if exists employees_help //
CREATE FUNCTION employees_usage ()
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN
'
== USAGE ==
====================
PROCEDURE show_departments()
shows the departments with the manager and
number of employees per department
FUNCTION current_manager (dept_id)
Shows who is the manager of a given departmennt
FUNCTION emp_name (emp_id)
Shows name and surname of a given employee
FUNCTION emp_dept_id (emp_id)
Shows the current department of given employee
';
END //
create procedure employees_help()
deterministic
begin
select employees_usage() as info;
end//
delimiter ;
Raw
schema7.sql
-- Sample employee database
-- See changelog table for details
-- Copyright (C) 2007,2008, MySQL AB
--
-- Original data created by Fusheng Wang and Carlo Zaniolo
-- http://www.cs.aau.dk/TimeCenter/software.htm
-- http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip
--
-- Current schema by Giuseppe Maxia
-- Data conversion from XML to relational by Patrick Crews
--
-- This work is licensed under the
-- Creative Commons Attribution-Share Alike 3.0 Unported License.
-- To view a copy of this license, visit
-- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to
-- Creative Commons, 171 Second Street, Suite 300, San Francisco,
-- California, 94105, USA.
--
-- DISCLAIMER
-- To the best of our knowledge, this data is fabricated, and
-- it does not correspond to real people.
-- Any similarity to existing people is purely coincidental.
--
USE employees;
SELECT 'TESTING INSTALLATION' as 'INFO';
DROP TABLE IF EXISTS expected_values, found_values;
CREATE TABLE expected_values (
table_name varchar(30) not null primary key,
recs int not null,
crc_sha varchar(100) not null,
crc_md5 varchar(100) not null
);
CREATE TABLE found_values LIKE expected_values;
INSERT INTO `expected_values` VALUES
('employees', 300024,'4d4aa689914d8fd41db7e45c2168e7dcb9697359',
'4ec56ab5ba37218d187cf6ab09ce1aa1'),
('departments', 9,'4b315afa0e35ca6649df897b958345bcb3d2b764',
'd1af5e170d2d1591d776d5638d71fc5f'),
('dept_manager', 24,'9687a7d6f93ca8847388a42a6d8d93982a841c6c',
'8720e2f0853ac9096b689c14664f847e'),
('dept_emp', 331603, 'd95ab9fe07df0865f592574b3b33b9c741d9fd1b',
'ccf6fe516f990bdaa49713fc478701b7'),
('titles', 443308,'d12d5f746b88f07e69b9e36675b6067abb01b60e',
'bfa016c472df68e70a03facafa1bc0a8'),
('salaries', 2844047,'b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f',
'fd220654e95aea1b169624ffe3fca934');
SELECT table_name, recs AS expected_records, crc_md5 AS expected_crc FROM expected_values;
DROP TABLE IF EXISTS tchecksum;
CREATE TABLE tchecksum (chk char(100));
SET @crc= '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc,
emp_no,birth_date,first_name,last_name,gender,hire_date))
FROM employees ORDER BY emp_no;
INSERT INTO found_values VALUES ('employees', (SELECT COUNT(*) FROM employees), @crc,@crc);
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,dept_name))
FROM departments ORDER BY dept_no;
INSERT INTO found_values values ('departments', (SELECT COUNT(*) FROM departments), @crc,@crc);
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,emp_no, from_date,to_date))
FROM dept_manager ORDER BY dept_no,emp_no;
INSERT INTO found_values values ('dept_manager', (SELECT COUNT(*) FROM dept_manager), @crc,@crc);
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,emp_no, from_date,to_date))
FROM dept_emp ORDER BY dept_no,emp_no;
INSERT INTO found_values values ('dept_emp', (SELECT COUNT(*) FROM dept_emp), @crc,@crc);
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no, title, from_date,to_date))
FROM titles order by emp_no,title,from_date;
INSERT INTO found_values values ('titles', (SELECT COUNT(*) FROM titles), @crc,@crc);
SET @crc = '';
INSERT INTO tchecksum
SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no, salary, from_date,to_date))
FROM salaries order by emp_no,from_date,to_date;
INSERT INTO found_values values ('salaries', (SELECT COUNT(*) FROM salaries), @crc,@crc);
DROP TABLE tchecksum;
SELECT table_name, recs as 'found_records ', crc_md5 as found_crc from found_values;
SELECT
e.table_name,
IF(e.recs=f.recs,'OK', 'not ok') AS records_match,
IF(e.crc_md5=f.crc_md5,'ok','not ok') AS crc_match
from
expected_values e INNER JOIN found_values f USING (table_name);
set @crc_fail=(select count(*) from expected_values e inner join found_values f on (e.table_name=f.table_name) where f.crc_md5 != e.crc_md5);
set @count_fail=(select count(*) from expected_values e inner join found_values f on (e.table_name=f.table_name) where f.recs != e.recs);
select timediff(
now(),
(select create_time from information_schema.tables where table_schema='employees' and table_name='expected_values')
) as computation_time;
DROP TABLE expected_values,found_values;
select 'CRC' as summary, if(@crc_fail = 0, "OK", "FAIL" ) as 'result'
union all
select 'count', if(@count_fail = 0, "OK", "FAIL" );
Raw
sqoop.sql
create database userdb;
-- Drop table emp if exists
DROP TABLE emp;
-- Drop table emp_add if exists
DROP TABLE emp_add
-- Drop table emp_contact if exists
DROP TABLE emp_contact;
-- Creates table emp
CREATE TABLE emp (
id int,
nameString,
deg String,
salary Double,
dept String
);
-- Creates table BSEFMCG
CREATE TABLE emp_add (
id int,
hno,String,
street string,
city String
);
-- Creates table empH to contain manipulated exported data
CREATE TABLE emp_contact (
id int,
phon varchar,
email string
);
-- Creates table BSEFMCGH to contain manipulated exported data
<!-------------导入demo--------------------------------->
导入语法
$ sqoop import (generic-args) (import-args)
$ sqoop-import (generic-args) (import-args)
下面的命令用于从MySQL数据库服务器中的emp表导入HDFS。
$ sqoop import \
--connect jdbc:mysql://localhost/userdb \
--username root \
--table emp --m 1
---------------------------------------------------------
在导入表数据到HDFS使用Sqoop导入工具，我们可以指定目标目录。
以下是指定目标目录选项的Sqoop导入命令的语法。
--target-dir <new or exist directory in HDFS>
下面的命令是用来导入emp_add表数据到'/queryresult'目录。
$ sqoop import \
--connect jdbc:mysql://localhost/userdb \
--username root \
--table emp_add \
--m 1 \ <!--the query can be executed once and imported serially, by specifying a single map task with -m 1:->
--target-dir /queryresult
"where"子句的一个子集。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。
下面的命令用来导入emp_add表数据的子集。子集查询检索员工ID和地址，居住城市为：Secunderabad
$ sqoop import \
--connect jdbc:mysql://localhost/userdb \
--username root \
--table emp_add \
--m 1 \
--where “city =’sec-bad’” \
--target-dir /wherequery
------------------------------------------------
增量导入
增量导入是仅导入新添加的表中的行的技术。它需要添加‘incremental’, ‘check-column’, 和 ‘last-value’选项来执行增量导入。
下面的语法用于Sqoop导入命令增量选项。
--incremental <mode>
--check-column <column name>
--last value <last check column value>
让我们假设新添加的数据转换成emp表如下：
1206, satish p, grp des, 20000, GR
$ sqoop import \
--connect jdbc:mysql://localhost/userdb \
--username root \
--table emp \
--m 1 \
--incremental append \
--check-column id \
-last value 1205
----------------------------------------------------------------------------
以下语法用于导入所有表。
$ sqoop import-all-tables (generic-args) (import-args)
$ sqoop-import-all-tables (generic-args) (import-args)
$ sqoop import \
--connect jdbc:mysql://localhost/userdb \
--username root
-------------------------------------------------
以下是export命令语法。
$ sqoop export (generic-args) (export-args)
$ sqoop-export (generic-args) (export-args)
下面的命令是用来导出表数据（这是在HDFS emp_data文件）到MySQL数据库服务器DB数据库的employee表中。
$ sqoop export \
--connect jdbc:mysql://localhost/db \
--username root \
--table employee \
--export-dir /emp/emp_data
-----------------------------------------------------------------------------
Sqoop作业创建并保存导入和导出命令。它指定参数来识别并调用已保存的工作。这种重新调用或重新执行用在增量导入，可以从RDBMS表到HDFS导入更新的行。
语法
以下是创建Sqoop作业的语法。
$ sqoop job (generic-args) (job-args)
[-- [subtool-name] (subtool-args)]
$ sqoop-job (generic-args) (job-args)
[-- [subtool-name] (subtool-args)
创建作业(--create)
在这里，我们创建一个名为myjob，这可以从RDBMS表的数据导入到HDFS作业。
下面的命令用于创建一个从DB数据库的employee表导入到HDFS文件的作业。
$ sqoop job --create myjob \
--import \
--connect jdbc:mysql://localhost/db \
--username root \
--table employee --m 1
验证作业 (--list)
‘--list’ 参数是用来验证保存的作业。下面的命令用来验证保存Sqoop作业的列表。
$ sqoop job --list
检查作业(--show)
‘--show’ 参数用于检查或验证特定的工作，及其详细信息。以下命令和样本输出用来验证一个名为myjob的作业。
$ sqoop job --show myjob
执行作业 (--exec)
‘--exec’ 选项用于执行保存的作业。下面的命令用于执行保存的作业称为myjob。
$ sqoop job --exec myjob
--------------------------------------------------------------------------
从面向对象应用程序的观点来看，每一个数据库表具有包含“setter”和“getter”的方法来初始化DAO类对象。
此工具(-codegen)自动生成DAO类。
它产生的DAO类在Java中是基于表的模式结构。在Java定义实例作为导入过程的一部分。
这个工具的主要用途是检查是否遗漏了Java代码。如果是这样，这将创建Java字段之间的缺省定界符的新版本。
语法
以下是Sqoop代码生成命令的语法。
$ sqoop codegen (generic-args) (codegen-args)
$ sqoop-codegen (generic-args) (codegen-args)
让我们以USERDB数据库中的表emp来生成Java代码为例。
下面的命令用来执行该给定的例子。
$ sqoop codegen \
--connect jdbc:mysql://localhost/userdb \
--username root \
--table emp
---------------------------------------------------------------------------
Sqoop “eval”工具。它允许用户执行用户定义的查询，对各自的数据库服务器和预览结果在控制台中。
这样，用户可以期望得到的表数据来导入。
使用eval我们可以评估任何类型的SQL查询可以是DDL或DML语句
以下语法用于Sqoop eval命令。
$ sqoop eval (generic-args) (eval-args)
$ sqoop-eval (generic-args) (eval-args)
选择查询评估计算
使用eval工具，我们可以评估计算任何类型的SQL查询。让我们在选择DB数据库的employee表行限制的一个例子。下面的命令用来评估计算使用SQL查询给定的例子。
$ sqoop eval \
--connect jdbc:mysql://localhost/db \
--username root \
--query “SELECT * FROM employee LIMIT 3”
Sqoop的eval工具可以适用于两个模拟和定义的SQL语句。这意味着，我们可以使用eval的INSERT语句了。下面的命令用于在DB数据库的员工(employee) 表中插入新行。
$ sqoop eval \
--connect jdbc:mysql://localhost/db \
--username root \
-e “INSERT INTO employee VALUES(1207,‘Raju’,‘UI dev’,15000,‘TP’)”
------------------------------------------------------------------------------------------
语法
以下语法用于Sqoop列表数据库命令。
$ sqoop list-databases (generic-args) (list-databases-args)
$ sqoop-list-databases (generic-args) (list-databases-args)
下面的命令用于列出MySQL数据库服务器的所有数据库。
$ sqoop list-databases \
--connect jdbc:mysql://localhost/ \
--username root
--------------------------------------------------------------------
Sqoop的list-tables工具解析并执行针对特定数据库的“SHOW TABLES”查询。此后，它列出了在数据库中存在的表。
语法
以下是使用 Sqoop 的 list-tables 命令的语法。
$ sqoop list-tables (generic-args) (list-tables-args)
$ sqoop-list-tables (generic-args) (list-tables-args)
示例查询
下面的命令用于列出MySQL数据库服务器的USERDB数据库下的所有的表。
$ sqoop list-tables \
--connect jdbc:mysql://localhost/userdb \
--username root
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment