Fill This Form To Receive Instant Help
Homework answers / question archive / Experiment 2: Manipulate the Data in Table Goal 1
1. Master all kinds of data operation about basic table in GUI.
2. Familiar with SQL statements for data insertion, modification and deletion of basic tables.
3. Master the SQL statement of data query.
4. Master the basic knowledge of SQL query performance analysis.
5. Understand TPC-H benchmark database.
Finish the following queries about the database university with SQL statement.?4-6?
Hint: in order to compare it more clearly, you’d better create a bigger table.
The TPC-H database is designed as following:?referring to the file tcp-ds-v2.17.3.docx??
Discription?
PART Table |
|
|
Column Name |
Datatype Requirements |
Comment |
P_PARTKEY |
identifier |
SF*200,000 are populated |
P_NAME |
variable text, size 55 |
|
P_MFGR |
fixed text, size 25 |
|
P_BRAND |
fixed text, size 10 |
|
P_TYPE |
variable text, size 25 |
|
P_SIZE |
integer |
|
P_CONTAINER |
fixed text, size 10 |
|
P_RETAILPRICE |
decimal |
|
P_COMMENT |
variable text, size 23 |
|
Primary Key: P_PARTKEY |
||
|
|
|
SUPPLIER Table |
|
|
Column Name |
Datatype Requirements |
Comment |
S_SUPPKEY |
identifier |
SF*10,000 are populated |
S_NAME |
fixed text, size 25 |
|
S_ADDRESS |
variable text, size 40 |
|
S_NATIONKEY |
Identifier |
Foreign Key to N_NATIONKEY |
S_PHONE |
fixed text, size 15 |
|
S_ACCTBAL |
decimal |
|
S_COMMENT |
variable text, size 101 |
|
Primary Key: S_SUPPKEY |
||
|
|
|
PARTSUPP Table |
|
|
Column Name |
Datatype Requirements |
Comment |
PS_PARTKEY |
Identifier |
Foreign Key to P_PARTKEY |
PS_SUPPKEY |
Identifier |
Foreign Key to S_SUPPKEY |
PS_AVAILQTY |
integer |
|
PS_SUPPLYCOST |
Decimal |
|
PS_COMMENT |
variable text, size 199 |
|
Primary Key: PS_PARTKEY, PS_SUPPKEY |
||
CUSTOMER Table |
||
Column Name |
Datatype Requirements |
Comment |
C_CUSTKEY |
Identifier |
SF*150,000 are populated |
C_NAME |
variable text, size 25 |
|
C_ADDRESS |
variable text, size 40 |
|
C_NATIONKEY |
Identifier |
Foreign Key to N_NATIONKEY |
C_PHONE |
fixed text, size 15 |
|
C_ACCTBAL |
Decimal |
|
C_MKTSEGMENT |
fixed text, size 10 |
|
C_COMMENT |
variable text, size 117 |
|
Primary Key: C_CUSTKEY |
|
|
|
|
|
ORDERS Table |
|
|
Column Name |
Datatype Requirements |
Comment |
O_ORDERKEY |
Identifier |
SF*1,500,000 are sparsely populated |
O_CUSTKEY |
Identifier |
Foreign Key to C_CUSTKEY |
O_ORDERSTATUS |
fixed text, size 1 |
|
O_TOTALPRICE |
Decimal |
|
O_ORDERDATE |
Date |
|
O_ORDERPRIORITY |
fixed text, size 15 |
|
O_CLERK |
fixed text, size 15 |
|
O_SHIPPRIORITY |
Integer |
|
O_COMMENT |
variable text, size 79 |
|
Primary Key: O_ORDERKEY |
||
|
|
|
|
||
LINEITEM Table |
|
|
Column Name |
Datatype Requirements |
Comment |
L_ORDERKEY |
identifier |
Foreign Key to O_ORDERKEY |
L_PARTKEY |
identifier |
Foreign key to P_PARTKEY, first part of the compound Foreign Key to (PS_PARTKEY, PS_SUPPKEY) with L_SUPPKEY |
L_SUPPKEY |
Identifier |
Foreign key to S_SUPPKEY, second part of the compound Foreign Key to (PS_PARTKEY, PS_SUPPKEY) with L_PARTKEY |
L_LINENUMBER |
integer |
|
L_QUANTITY |
decimal |
|
L_EXTENDEDPRICE |
decimal |
|
L_DISCOUNT |
decimal |
|
L_TAX |
decimal |
|
L_RETURNFLAG |
fixed text, size 1 |
|
L_LINESTATUS |
fixed text, size 1 |
|
L_SHIPDATE |
date |
|
L_COMMITDATE |
date |
|
L_RECEIPTDATE |
date |
|
L_SHIPINSTRUCT |
fixed text, size 25 |
|
L_SHIPMODE |
fixed text, size 10 |
|
L_COMMENT |
variable text size 44 |
|
Primary Key: L_ORDERKEY, L_LINENUMBER |
||
|
|
|
NATION Table |
|
|
Column Name |
Datatype Requirements |
Comment |
N_NATIONKEY |
identifier |
25 nations are populated |
N_NAME |
fixed text, size 25 |
|
N_REGIONKEY |
identifier |
Foreign Key to R_REGIONKEY |
N_COMMENT |
variable text, size 152 |
|
Primary Key: N_NATIONKEY |
||
|
|
|
REGION Table |
|
|
Column Name |
Datatype Requirements |
Comment |
R_REGIONKEY |
identifier |
5 regions are populated |
R_NAME |
fixed text, size 25 |
|
R_COMMENT |
variable text, size 152 |
|
Primary Key: R_REGIONKEY |
|