Examples of SQL or JSON to Chart

Database SQL Examples
demo SELECT *
FROM payment
demo SELECT vendor, sum(amount) tot_pay, count(amount) num_pay, avg(amount) avg_pay
FROM payment
GROUP BY vendor
demo SELECT lname, fname, sum(price) as total_price, count(*) as num_of_courses
FROM student join class on ssn=stu_ssn
                         join course using(course_id)
GROUP BY lname, fname
demods SELECT stu_lname, stu_fname, sum(price) total_cost, count(*) courses_taken
FROM roster
WHERE stu_lname is not null
GROUP BY stu_lname, stu_fname
demods SELECT inst_lname, inst_fname, count(stu_id) as number_of_students
FROM roster
WHERE inst_lname is not null
GROUP BY inst_lname, inst_fname
demodw SELECT program, avg(grade) as avg_grade
FROM student join grade using(student_id)
GROUP BY program
demodw SELECT concat(program,school_year) as year, avg(grade) as avg_grade
FROM student join grade using(student_id)
GROUP BY concat(program,school_year)
ORDER BY 1
demodw SELECT fname, lname, count(assignment_id), avg(grade)
FROM student join grade using(student_id)
GROUP BY lname, fname
demodw SELECT sex, avg(grade) as avg_grade
FROM student join grade using(student_id)
WHERE program='graduate'
GROUP BY sex
demodw SELECT lname, fname, avg(grade) as avg_grade
FROM instructor join grade using(instructor_id)
GROUP BY lname, fname
demodw SELECT state_name, state_code, sum(gross_pay) payroll, sum(tax) tax_collected
FROM state join payment using(state_code)
GROUP BY state_name, state_code
demodw Query average grade by department:
SELECT department, avg(grade) as avg_grade
FROM course join grade using(course_id)
GROUP BY department
demodw Drill Down by course description:
SELECT description, avg(grade) as avg_grade
FROM course join grade using(course_id)
WHERE department='web development'
GROUP BY description
demodw Drill Down by sex:
SELECT sex, avg(grade) as avg_grade
FROM course join grade using(course_id) join student using(student_id)
WHERE department ='web development'
   AND description='xml for web development'
GROUP BY sex
demodw Drill Down by last name:
SELECT lname, fname, avg(grade) as avg_grade, count(grade) as count_grade
FROM course join grade using(course_id) join student using(student_id)
WHERE department ='web development'
   AND description='xml for web development'
   AND sex='M'
GROUP BY lname, lname
ORDER BY avg_grade desc
JSON JSON Example
Weather [
  {"city":"New York", "region":"NE", "population":8.3,"avgTemp":55,"rain":50,"snow":25},
  {"city":"Los Angeles", "region":"West", "population":3.9,"avgTemp":66,"rain":13,"snow":0 },
  {"city":"Chicago", "region":"Central","population":2.7,"avgTemp":50,"rain":37,"snow":37 },
  {"city":"Houston", "region":"South", "population":2.1,"avgTemp":70,"rain":50,"snow":0.1},
  {"city":"Philadelphia", "region":"NE", "population":1.6,"avgTemp":56,"rain":42,"snow":22 },
  {"city":"Phoenix", "region":"South", "population":1.5,"avgTemp":75,"rain":8 ,"snow":0 },
  {"city":"San Antonio", "region":"South", "population":1.4,"avgTemp":70,"rain":32,"snow":0.7},
  {"city":"San Diego", "region":"West", "population":1.3,"avgTemp":64,"rain":10,"snow":0 },
  {"city":"Dallas", "region":"South", "population":1.2,"avgTemp":67,"rain":38,"snow":1.5},
  {"city":"San Jose", "region":"South", "population":1.0,"avgTemp":61,"rain":14,"snow":0 },
  {"city":"Austin", "region":"South", "population":0.9,"avgTemp":70,"rain":34,"snow":0.6},
  {"city":"San Francisco","region":"West", "population":0.9,"avgTemp":58,"rain":21,"snow":0 }
]
Pay & Tax
by State 		[
  {"name":"Eric Katz", "sex":"M","state":"NY","gross":63000,"tax":19530},
  {"name":"Eric Katz", "sex":"M","state":"PA","gross":13000,"tax":4030 },
  {"name":"George P", "sex":"M","state":"CT","gross":45000,"tax":14400},
  {"name":"George P", "sex":"M","state":"NJ","gross":25000,"tax":8000 },
  {"name":"Marc Paller","sex":"M","state":"CT","gross":78000,"tax":26520},
  {"name":"Sam Sultan", "sex":"M","state":"NY","gross":75000,"tax":26250},
  {"name":"Sue Martin", "sex":"F","state":"NJ","gross":18000,"tax":5940 },
  {"name":"Sue Martin", "sex":"F","state":"NY","gross":59000,"tax":19470}
]
Covid-19
2020-11-23 		[
  {"country":"USA", "continent":"North America","pop":331002651, "case":12259959,"death":256830, "percent":2.09},
  {"country":"India", "continent":"Asia", "pop":1389004385,"case":9139865, "death":133738, "percent":1.46},
  {"country":"Brazil", "continent":"South America","pop":212559417, "case":6071401, "death":169183, "percent":2.79},
  {"country":"France", "continent":"Europe", "pop":65273511, "case":2191180, "death":48807, "percent":2.23},
  {"country":"Russia", "continent":"Asia", "pop":145934462, "case":2096749, "death":36192, "percent":1.73},
  {"country":"Spain", "continent":"Europe", "pop":46754778, "case":1556730, "death":42619, "percent":2.74},
  {"country":"UK", "continent":"Europe", "pop":67886011, "case":1515812, "death":55121, "percent":3.64},
  {"country":"Italy", "continent":"Europe", "pop":60461826, "case":1408868, "death":50453, "percent":3.58},
  {"country":"Argentina", "continent":"South America","pop":45195774, "case":1379366, "death":37002, "percent":2.68},
  {"country":"Columbia", "continent":"South America","pop":50882891, "case":1248417, "death":35287, "percent":2.83},
  {"country":"Mexico", "continent":"North America","pop":128932753, "case":1941875, "death":101676, "percent":5.24},
  {"country":"Peru", "continent":"South America","pop":32971854, "case":948081, "death":35549, "percent":3.75},
  {"country":"Germany", "continent":"Europe", "pop":83783942, "case":939427, "death":14192, "percent":1.51},
  {"country":"Poland", "continent":"Europe", "pop":37846611, "case":876333, "death":13774, "percent":1.57},
  {"country":"Iran", "continent":"Asia", "pop":83992949, "case":866821, "death":45255, "percent":5.22},
  {"country":"South Africa","continent":"Africa", "pop":59308690, "case":767679, "death":20903, "percent":2.72},
  {"country":"Ukraine", "continent":"Europe", "pop":43733762, "case":653442, "death":11423, "percent":1.75},
  {"country":"Belgium", "continent":"Europe", "pop":11589623, "case":558779, "death":15618, "percent":2.80},
  {"country":"Chile", "continent":"South America","pop":19116201, "case":542080, "death":15106, "percent":2.79},
  {"country":"Iraq", "continent":"Asia", "pop":40222493, "case":537457, "death":11996, "percent":2.23},
  {"country":"Indonesia", "continent":"Asia", "pop":273523615, "case":502110, "death":16002, "percent":3.19},
  {"country":"Netherlands", "continent":"Europe", "pop":17134872, "case":497563, "death":9021, "percent":1.81},
  {"country":"Czechia", "continent":"Europe", "pop":10708981, "case":492263, "death":7196, "percent":1.46},
  {"country":"Bangladesh", "continent":"Asia", "pop":164689383, "case":449760, "death":6416, "percent":1.43},
  {"country":"Turkey", "continent":"Asia", "pop":84339067, "case":446822, "death":12358, "percent":2.77}
]