Sql

akirru

Newcomer
Hey Guys,

I'm trying to get to get my head around SQL :eek: ... 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
 
this is what I have so far


SELECT DISTINCT surname,forenames,position
FROM employee JOIN jobhistory ON employee.empno = jobhistory.empno JOIN empcourse ON empcourse.empno = jobhistory.empno
WHERE courseno IN (SELECT courseno FROM empcourse
WHERE empno = (SELECT empno
FROM employee
WHERE surname = 'Roberts'
AND forenames = 'Robert'))
AND enddate is NULL
AND position ! = 'Analyst Programmer'
 
Have a look at subqueries and temporary tables/views. Attempting to do overly complex things in a single statement is very rarely the best way to go about things in SQL.
 
ignore the word describe before the colum names ... exampl : describe jmcust .. should just be jmcust (the colum name)
 
I agree with Zaphod: use views/subqueries to narrow things down a bit at a time.

And make a schematic.
 
Zaphod said:
Have a look at subqueries and temporary tables/views. Attempting to do overly complex things in a single statement is very rarely the best way to go about things in SQL.

whenever i do anything with SQL (usually MySQL) first thing is to take a day or two to "picture" how will databse work.... i dont even make notes, i just try to figuere out how to make work with making really long queries.....

yes, it is possible to have everything done by single query, but (IMO) i will always like to put my DB and tables and then think about the rest..... DB is underlaying everything and if i screw there its much more work later to get information out of it then it should be....

dunno how to describe this... but i try to "visualize" DB and its purpoise.... so i rather have 3 independent tables then one with shitload of info.....





(sorry if this doesnt make too much sense, i am just into one of thse "visualisations"... for employment agency and they want complete redo of what they already have... now i have to think the way to implement old data into new tables AND add new fields..... weeee....fun...):devilish:
 
Assuming Mrs. Brown has a customer number of 1 and your using ANSI compliant SQL :

Code:
SELECT 
jmcust.c_no, jmcust.c_post_code, dress_order.order_date, 
dress_order. order_no, dressmaker.d_post_code, garment.description,
garment.labour_cost, quantities.quantity, material.fabric,
material.colour, material.pattern, material.cost
FROM  jmcust INNER JOIN
dress_order ON jmcust.c_no = dress_order.cust_no INNER JOIN
order_line ON dress_order.order_no = order_line.order_ref INNER JOIN
construction INNER JOIN dressmaker ON construction.maker = dressmaker.d_no 
ON order_line.order_ref = construction.order_ref AND 
order_line.line_no = construction.line_ref INNER JOIN
material ON order_line.ol_material = material.material_no INNER JOIN
quantities ON order_line.ol_style = quantities.style_q AND 
order_line.ol_size = quantities.size_q INNER JOIN
garment ON quantities.style_q = garment.style_no
WHERE jmcust.c_no = 1

Not sure how many of the columns you wanted returning, but this should cover everything regarding garment description.
 
Back
Top