Hey Guys,
I'm trying to get to get my head around SQL ... hehe
This very question and I understand what i want to do in my head but I just cant get it to work
Question: List the post code, order number, order date, and garment descriptions for all items associated with Ms Brown.
I have these colums
describe jmcust
COLNAME COLDESC
c_no INTEGER PRIMARY KEY
c_name CHAR(20) NOT NULL
c_house_no INTEGER NOT NULL
c_post_code CHAR(9) NOT NULL
Number of rows = 4
describe dress_order
COLNAME COLDESC
order_no INTEGER PRIMARY KEY
cust_no INTEGER REFERENCES jmcust
order_date DATE NOT NULL
completed CHAR(1)
Number of rows = 4
describe order_line
COLNAME COLDESC
order_ref INTEGER NOT NULL REFERENCES dress_order
line_no INTEGER NOT NULL
ol_style INTEGER NOT NULL
ol_size INTEGER NOT NULL
ol_material INTEGER REFERENCES material
PRIMARY KEY (order_ref, line_no)
FOREIGN KEY (ol_style, ol_size) REFERENCES quantities
Number of rows = 7
describe garment
COLNAME COLDESC
style_no INTEGER PRIMARY KEY
description CHAR(20) NOT NULL
labour_cost REAL NOT NULL
notions CHAR(50)
Number of rows = 4
describe quantities
COLNAME COLDESC
-- size_q is the UK dress size for the item in question. For example
-- size 8,10,12,14,16 are all valid dress sizes
-- quantity is how many linear feet are needed to make the particular
-- item. Material comes in a roll, and you get that many feet off the
-- roll to make the item.
style_q INTEGER NOT NULL REFERENCES garment
size_q INTEGER NOT NULL
quantity REAL NOT NULL
PRIMARY KEY ( style_q, size_q )
Number of rows = 9
describe dressmaker
COLNAME COLDESC
d_no INTEGER PRIMARY KEY
d_name CHAR(20) NOT NULL
d_house_no INTEGER NOT NULL
d_post_code CHAR(8) NOT NULL
Number of rows = 4
describe construction
COLNAME COLDESC
maker INTEGER NOT NULL REFERENCES dressmaker
order_ref INTEGER NOT NULL
line_ref INTEGER NOT NULL
start_date DATE NOT NULL
finish_date DATE
PRIMARY KEY ( maker, order_ref, line_ref )
FOREIGN KEY ( order_ref, line_ref ) REFERENCES order_line
Number of rows = 7
describe material
COLNAME COLDESC
material_no INTEGER PRIMARY KEY
fabric CHAR(20) NOT NULL
colour CHAR(20) NOT NULL
pattern CHAR(20) NOT NULL
cost REAL NOT NULL
I'm trying to get to get my head around SQL ... hehe
This very question and I understand what i want to do in my head but I just cant get it to work
Question: List the post code, order number, order date, and garment descriptions for all items associated with Ms Brown.
I have these colums
describe jmcust
COLNAME COLDESC
c_no INTEGER PRIMARY KEY
c_name CHAR(20) NOT NULL
c_house_no INTEGER NOT NULL
c_post_code CHAR(9) NOT NULL
Number of rows = 4
describe dress_order
COLNAME COLDESC
order_no INTEGER PRIMARY KEY
cust_no INTEGER REFERENCES jmcust
order_date DATE NOT NULL
completed CHAR(1)
Number of rows = 4
describe order_line
COLNAME COLDESC
order_ref INTEGER NOT NULL REFERENCES dress_order
line_no INTEGER NOT NULL
ol_style INTEGER NOT NULL
ol_size INTEGER NOT NULL
ol_material INTEGER REFERENCES material
PRIMARY KEY (order_ref, line_no)
FOREIGN KEY (ol_style, ol_size) REFERENCES quantities
Number of rows = 7
describe garment
COLNAME COLDESC
style_no INTEGER PRIMARY KEY
description CHAR(20) NOT NULL
labour_cost REAL NOT NULL
notions CHAR(50)
Number of rows = 4
describe quantities
COLNAME COLDESC
-- size_q is the UK dress size for the item in question. For example
-- size 8,10,12,14,16 are all valid dress sizes
-- quantity is how many linear feet are needed to make the particular
-- item. Material comes in a roll, and you get that many feet off the
-- roll to make the item.
style_q INTEGER NOT NULL REFERENCES garment
size_q INTEGER NOT NULL
quantity REAL NOT NULL
PRIMARY KEY ( style_q, size_q )
Number of rows = 9
describe dressmaker
COLNAME COLDESC
d_no INTEGER PRIMARY KEY
d_name CHAR(20) NOT NULL
d_house_no INTEGER NOT NULL
d_post_code CHAR(8) NOT NULL
Number of rows = 4
describe construction
COLNAME COLDESC
maker INTEGER NOT NULL REFERENCES dressmaker
order_ref INTEGER NOT NULL
line_ref INTEGER NOT NULL
start_date DATE NOT NULL
finish_date DATE
PRIMARY KEY ( maker, order_ref, line_ref )
FOREIGN KEY ( order_ref, line_ref ) REFERENCES order_line
Number of rows = 7
describe material
COLNAME COLDESC
material_no INTEGER PRIMARY KEY
fabric CHAR(20) NOT NULL
colour CHAR(20) NOT NULL
pattern CHAR(20) NOT NULL
cost REAL NOT NULL