SQL Query Note there are no Primary or foreign keys to be available to use in these sql querys Child Table toy table with a data Mapping table. CHILDChild_id Child_name1 Lucy2 Linus3 Ted4 Bill5 Fred6 Charlie7 Tom Mike sally------------------------------ ToyToy_id Toy_name1 ball2 kite3 tennis4 football5 basketball6 boat7 hockey8 sail9 horse marbles--------------------------------------------------Mapping TableChild_id Toy_ID1 11 21 32 42 42 74 74 17 26 76 7Questions: Need to confirm if I am on the right track and need help with 3 41: List “All” children and All their toys.A) SELECT * child_name , toy_name FROM MAPPING FULL OUTER JOIN TOY ON child.child_ID = toys.toys_ID FULL OUTER JOIN ON CHILD Child.Child.ID = Toy.Toy.id;or SELECT * Child, Toy;or SELECT * child_name, toy_name FROM CHILD INNER JOIN TOYS ON child.child_ID = Toys.Toys_ID;'(but I think I need the Mapping table)_______________________________________________________________________2: Sum the # of Toys for each ChildSELECT * child.child_id sum(child_ID) as ‘ Total Toys’ FROM Mapping Table JOIN_______________________________________________________________________3: List all children with 3 or more toysNOT SURE_______________________________________________________________________4: List all children with 3 toysNot sure
Language (or Software): SQL
Browser (if necessary): IE
: List “All” children and All their toys.
A) SELECT * child_name , toy_name
FROM MAPPING FULL OUTER JOIN CHILD
ON child.child_ID = toys.toys_ID
FULL OUTER JOIN ON TOY
ON child.child_ID = toys.toys_ID;
hithanks for using justAnswer.comare you using SQL server or any other
Just regular sql at the command level
hilet me check this when you need this
hiwhen you need these answers
any time today is fine
hiok you check these by 9pm
hifor the second oneis it sum the # XXXXX Toys for each Childor is it Number of Toys for each child
actually its both
1) sum the # XXXXX toys for each child.. whixh is really the same you just worded it differently.
To Clarify. I need the number of toys (the count) for each child. How many toys does each child have
hiok i will do this
hihere is the answer link belowhttp://ge.tt/6mS2kZJ/v/0check them firsthope this helps
For the first question:
1: List "All" children and All their toys.
Can you provide he sql which list only the children with toys
2. I am not sure if this is correct ie
select CHILD.Child_name, count(Mapping Table.Toy_ID) From Mapping Table,CHILD where Mapping Table.Child_id=CHILD.Child_id group by Mapping Table.Child_id;
...Mapping Table, CHILD
you have a , after Mapping Table and then begin with the CHILD table do you mean a period .
hihi1. try this select CHILD.Child_name, Toy.Toy_name From CHILD,Toy, Mapping Table where Mapping Table.Child_id=CHILD.Child_id AND Mapping Table.Toy_id=Toy.Toy_id;2. Here select from two tables Mapping Table and CHILDyou want to count the number of toys for each childso get child name from the child table and number of toys from mapping table for each child and join on child id from mapping table and child table hope this helps
thank you I have to go to a meeting but as soon as I get back I will review.
Thank you for your help much appreciated
hiyou check it if you are satisfied, don't forget to click on Accept buttoni will be offline after 3 hoursif you need anything more, i will contact after coming back
I am sure its fine I wanted to review tommorrow is their a time limit. Meaning is it okay to spend tommorrow reviewing and then sign off. I
I just wanyted o followup on your syntax and your comments...............................
try this select CHILD.Child_name, Toy.Toy_name From CHILD,Toy, Mapping Table where Mapping Table.Child_id=CHILD.Child_id AND Mapping Table.Toy_id=Toy.Toy_id;2. Here select from two tables Mapping Table and CHILDyou want to count the number of toys for each childso get child name from the child table and number of toys from mapping table for each child and join on child id from mapping table and child table hope this helps.............................................................
1) You substiute WHERE for ON.
I assume they are the same???
2) You don't use INNER JOIN or OUTER JOIN when writing the SQL pe your comments and Join on child_id from Mapping table ... ir the SQL statement INNER JOIN and OUTER JOIN not necessary?
hi i will re change it again
Okay great. it would be good to see how different the same sql querys would look for questions 1 - 4 using ON and inner outer and full JOINS
hii will do like this
hiyou check after 4 hoursi will send it
okay thanks again
hihere is the link belowhttp://ge.tt/9j5z6cJ/v/0hope this helps3 and 4 better to use these queries for the output
I just need to confirm that we can wrtite the SQL for question 1 and Question #2 two different ways and get the same output results. Question #3 and #4 should remain the same
Thanks again fro your help
1: List"All" children and All their toys.Answer 1A:SELECT * Child, Toy;
SELECT CHILD.Child_name, Toy.Toy_name FROM CHILD, Toy, Mapping Table WHERE Mapping Table, Child_id = CHILD.Child_id AND Mapping Table.Toy_id = Toy.Toy_id;
SELECTCHILD.Child_name, Toy.Toy_name FROM CHILD LEFT OUTERJOIN Mapping Table ONCHILD.Child_id INNER JOIN Toy ON Toy.Toy_id = Mapping Table.Toy_id
2: : Sumthe # XXXXX Toys for each Child
Anwer 2A:SELECTCHILD.Child_name, COUNT(MappingTable.Toy_ID)FROMMapping Table, CHILD WHEREMapping Table.Child_id = CHILD.Child_id GROUP BY MappingTable.Child_id;
SELECTCHILD.Child_name, COUNT(Mapping Table.Toy_ID) FROMMapping Table INNER JOINCHILD ON MappingTable.Child_id = CHILD.Child_id GROUP BY Mapping Table.Child_id;
hihere is the answer link below http://ge.tt/6Dlr0eJ/v/0hope this helps
Master of Computer Application plus 5+ year experience in Software development