testing - Two questions about SQL based on an ERD diagram. (novice level) -

-

i've attached picture shows erd diagram these 2 questions apply to.

the erd optometry doctors , patients. it's coursework i'm having trouble understanding these two.

any sql savvy people out there willing me out corresponding sql statements? thank you. this erd

1. list of patient’s name , doctor treats patient, date of last eyetype result, last glasses type , brand, show these patients live either chicago or san diego. order date , inside on patient’s name

  1. a list shows per each brand of glasses total count of every patient wears corresponding brand. show results total amount of patients more 25.

your sql query start, there few things aware of:

  1. how tables related? how can join them in query (how connect?)?

  2. in select statement, think it's asking 'glass type' rather 'eye type'

  3. select statement missing from, indicating tables you're selecting - add joins need make relate tables

  4. where clause looks good

  5. order missing date (though 'date' isn't specified - start date or end date?)

anyway, putting gets this:

select name_patient, name_doctor, type, brand, start_date, end_date patients inner join doctors on patients.patientnumber = doctors.patientnumber inner join type_of_glasses on patients.patientnumber = type_of_glasses.patientnumber city = 'chicago' or 'san diego' order start_date, end_date, name_patient;

now you're ready try number 2. think 'count' goes 'group by' , condition 'having > 25' , not 'where'

edited:

really close on 2. need add join condition (on ...) , edit count in select line wrap field in parentheses. group tells query groups you're counting, here you're grouping brand , counting how many patients have brand.

select count(name_patient), brand  patients inner join type_of_glasses  on patients.patientnumber = type_of_glasses.patientnumber group brand having count(name_patient) > 25

Comments

Post a Comment

Popular posts from this blog

ruby - Trying to change last to "x"s to 23 -

-
i have been playing around irb , have string looks this: irb(main):072:0> puts ["[\"4354 5432 5432 xxxxx\", \"6547 6547 8543 xxxxx\", \"2344 6543 6674 xxxxx\", \"2346 6236 7543 xxxxx\", \"1273 5585 5587 xxxxx\"]"] => nil irb(main):073:0> what want gsub last 2 "x" s of each 17 digit combination, 23 . what i've tried far (i've been using rubular): a.gsub(/\d[x]/,"23") <= grabs "x" s i'm close a.gsub(/\w[x{2}]/,"23") <= grabs "x" s grabs two digits each combination. is there easier way i'm not understanding? a.gsub(/\d{4} \d{4} \d{4} xxx\kxx/, '23') \d{4} = 4 digits what \k drop matched far. can use don't replace entire number, still able validate have in desired format.
Read more

jquery - Clone last and append item to closest class -

-
this bare-bones code on page: <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <di...
Read more

css - Can I use the :after pseudo-element on an input field? -

-
i trying use :after css pseudo-element on input field, not work. if use span , works ok. <style type="text/css"> .mystyle:after {content:url(smiley.gif);} .mystyle {color:red;} </style> this works (puts smiley after "buu!" , before "some more") <span class="mystyle">buuu!</span>a more this not work - colors somevalue in red, there no smiley. <input class="mystyle" type="text" value="somevalue"> what doing wrong? should use pseudo-selector? note: cannot add span around input , because being generated third-party control. :after , :before not supported in internet explorer 7 , under, on elements. it's not meant used on replaced elements such form elements (inputs) , image elements . in other words it's impossible pure css. however if using jquery can use $(".mystyle").after("add smiley here"); api docs on .after to ...
Read more