# 資料庫系統概論 - HW1
> The process of creating the “lego” databases (can be screenshot and/or SQL/non-SQL statements with text explanation) (8pts)
```
$ brew install postgresql@15
$ brew services start postgresql@15
$ createdb lego
$ psql lego
```
After creating the database, use the following SQL statement to define the schema of database.
```sql
create table part_categories (
id integer primary key,
name varchar(100)
);
create table themes (
id integer primary key,
name varchar(100),
parent_id integer
);
create table sets (
set_num varchar(20) primary key,
name varchar(100),
year integer,
theme_id integer,
num_parts integer,
foreign key (theme_id) references themes(id)
);
create table parts (
part_num varchar(20) primary key,
name varchar(1000),
part_cat_id integer,
foreign key (part_cat_id) references part_categories(id)
);
create table inventories (
id integer primary key,
version integer,
set_num varchar(20),
foreign key (set_num) references sets(set_num)
);
create table colors (
id integer primary key,
name varchar(50),
rgb char(6),
is_trans char(1)
);
create table inventory_sets (
inventory_id integer,
set_num varchar(20),
quantity integer,
primary key(inventory_id, set_num),
foreign key(inventory_id) references inventories(id),
foreign key(set_num) references sets(set_num)
);
create table inventory_parts (
inventory_id integer,
part_num varchar(20),
color_id integer,
quantity integer,
is_spare char(1),
primary key(inventory_id,color_id, part_num, is_spare),
foreign key(inventory_id) references inventories(id),
foreign key(color_id) references colors(id)
);
```
> The process of importing eight required .csv files into lego database(32pts)
For the importing of data from csv file, use the following command for every csv files.
```sql
\copy <tablename>(<column1>, <column2>, ...)
from '/path/to/csv/files' with delimiter ',' CSV HEADER;
```
Then the data can be queried now.
Remark: For outputing the result to a csv file, use the following command.
```sql
Copy (<query>) To '/path/to/csv/files'
With CSV DELIMITER ',' HEADER;
```
> The SQL statements and output results of 4a (10pts)
```sql
select sets.name as sets_name, themes.name as theme_name
from sets, themes
where sets.year=2017 and sets.theme_id=themes.id;
```
[result](https://drive.google.com/file/d/16Tg2eyWlIlmeKSMD4TO1PU9mcD3QcLir/view?usp=drive_link)
```
Size: 296 x 2
```
```csvpreview {header="true"}
sets_name theme_name
Assembly Square Modular Buildings
Carousel Creator
Creative Builder Box Classic
Creative Box Classic
Blue Creative Box Classic
Red Creative Box Classic
Green Creative Box Classic
```
> The SQL statements and output results of 4b (10pts)
```sql
select year, count(year)
from sets
group by year
order by count(year) desc;
```
[result](https://drive.google.com/file/d/1MUx0Js_ZPMhpKz1KRJ4yTsgokWCAi9ir/view?usp=drive_link)
```
Size: 66 x 2
```
```csvpreview {header="true"}
year count
2014 713
2015 665
2012 615
2016 596
2013 593
2011 503
2002 447
```
> The SQL statements and output results of 4c (10pts)
```sql
select themes.id as themes_id,
themes.name as themes_name,
sets.name as sets_name into tmpc
from themes, sets
where themes.id=sets.theme_id
order by themes.name;
select themes.name
from (
select themes_id, count(themes_id)
from tmpc
group by themes_id
order by count(themes_id) desc
) as T, themes
where T.themes_id=themes.id
order by T.count desc
limit 1;
```
[result](https://drive.google.com/file/d/1yr19YQWrpf_zvY9USDjPu08Z4njPJaIf/view?usp=drive_link)
```
Size: 1 x 1
```
```csvpreview {header="true"}
name
Gear
```
> The SQL statements and output results of 4d (10pts)
```sql
select themes.name, avg(sets.num_parts)
from themes, sets
where themes.id=sets.theme_id
group by themes.name, sets.theme_id
order by avg(sets.num_parts);
```
[result](https://drive.google.com/file/d/1LB8zQvgObRRPiEZUoCrc36tldG3E1_F4/view?usp=drive_link)
```
Size: 575 x 2
```
```csvpreview {header="true"}
name avg
Wooden Box Set -1
Mindstorms 0
Train 0
Samsonite 0
Key Chain 0.1818181818
Technic 1
Imperial Guards 1
Supplemental 1.8
Power Functions 1.882352941
```
> The SQL statements and output results of 4e (10pts)
```sql
create view tmpe as
(select distinct part_num, color_id
from inventory_partsorder by part_num, color_id)
select colors.name, count(tmpe.color_id)
from colors, tmpe
where colors.id=tmpe.color_id
group by colors.name
order by count(tmpe.color_id) desc
limit 10;
```
[result](https://drive.google.com/file/d/1Em_GozrmNFperQlkoPSywSq_sh7uZYZH/view?usp=drive_link)
```
Size: 10 x 1
```
```csvpreview {header="true"}
name
White
Black
Yellow
Red
[No Color]
Blue
Light Bluish Gray
Dark Bluish Gray
Light Gray
Tan
```
> The SQL statements and output results of 4f (10pts)
```sql
select themes.name as themes_name,
themes.id as themes_id
into tmpf
from themes;
select themes.name as themes_name,
themes.id as themes_id,
colors.name as colors_name,
sum(inventory_parts.quantity) into tmp
from colors, inventory_parts, inventories, sets, themes
where colors.id=inventory_parts.color_id
and inventory_parts.inventory_id=inventories.id
and inventories.set_num=sets.set_num
and sets.theme_id=themes.id
group by themes.id, colors.name
order by themes.id, sum(inventory_parts.quantity) desc;
select themes_id, themes_name, colors_name, sum into tmpf_2
from tmp as r1
where sum = (
select max(r2.sum)
from tmp as r2
where r1.themes_id = r2.themes_id
);
select tmpf.themes_name, tmpf_2.colors_name
from tmpf_2
right outer join tmpf
on tmpf.themes_id=tmpf_2.themes_id
order by tmpf.themes_id;
```
[result](https://drive.google.com/file/d/1fXLddWggVwYXBJMsUqzWT0jDgEl96b2_/view?usp=drive_link)
```
Size: 626 x 2
```
```csvpreview {header="true"}
themes_name colors_name
Technic Black
Arctic Technic Light Gray
Competition Black
Expert Builder Light Gray
Model Black
Airport Black
Construction Black
Farm Black
Fire Black
Harbor Black
```
Sign in with Wallet
Connect another wallet