sql playground
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

164 lines
4.0 KiB

-- continuing on previous database
-- some random thing to start class
SELECT question, COUNT(*), AVG(score)
FROM nss
GROUP BY question
ORDER BY AVG(score);
-- "explanatory factors"
-- runs that and avg score together
SELECT question, COUNT(*), AVG(score)
FROM nss;
SELECT question, COUNT(*), AVG(score)
FROM nss
GROUP BY question
ORDER BY AVG(score);
-- close but not exact. this is the "real average"
SELECT question, COUNT(*), AVG(score), SUM(score * response) / SUM(response)
FROM nss
GROUP BY question
ORDER BY AVG(score);
-- some stuff for institution
SELECT institution, COUNT(*), AVG(score), SUM(score * response) / SUM(response) as real_avg
FROM nss
GROUP BY institution;
-- some stuff by level
SELECT level, COUNT(*), AVG(score), SUM(score * response) / SUM(response) as real_avg
FROM nss
GROUP BY level
ORDER BY AVG(score);
-- lots more stuff
SELECT institution, question, COUNT(*), AVG(score), SUM(score * response) / SUM(response) as real_avg
FROM nss
GROUP BY institution, question
ORDER BY AVG(score) DESC;
SELECT institution, subject, COUNT(*), AVG(score), SUM(score * response) / SUM(response) as real_avg
FROM nss
GROUP BY institution, subject
ORDER BY AVG(score) DESC;
-- q6: Show the percentage of students who A_STRONGLY_AGREE to question 22 for the subject '(8) Computer Science', and show the same figure for '(H) Creative Arts and Design'
SELECT subject, ROUND(SUM(A_STRONGLY_AGREE * response)/SUM(response),0) AS pct_STRONGLY_AGREE, ROUND(AVG(A_STRONGLY_AGREE),0) as non_weighted
FROM nss
WHERE question = 'Q22'
AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
GROUP BY subject;
-- q7: Show the average scores for question 'Q22' for each institution that includes 'Manchester' in the name
SELECT institution, ROUND(SUM(score * response)/SUM(response)) as real_avg
FROM nss
WHERE question='Q22'
AND (institution LIKE '%Manchester%')
GROUP BY institution;
-- q8: Show the institution, sample size, and number of computing students for instititions in Manchester for 'Q01'
-- first part
SELECT institution, SUM(sample)
FROM nss
WHERE question='Q01'
AND (institution LIKE '%Manchester%')
GROUP BY institution;
-- now just CS
SELECT institution, SUM(sample) AS comp
FROM nss
WHERE question='Q01'
AND (institution LIKE '%Manchester%')
AND subject = '(8) Computer Science'
GROUP BY institution;
-- alright, the answer wants both. Now what?
-- fact: this is not possible with one query, must connect two queries
-- diverges to talk about homework.
-- q5.1a
CREATE VIEW e51a AS
SELECT vendorID, vendorname
FROM vendor;
-- These are from the book.
-- Best to test and then save as view
-- q5.1c
CREATE VIEW e51c AS
SELECT productid, productname, productprice
FROM product
WHERE productprice >= 100;
-- q5.1 ?
-- display pid and pname of the cheapest product
-- find price of cheapest product
-- find that product by price and show the information about it
SELECT productid, productname
FROM product
WHERE productprice = (
SELECT MIN(productprice)
FROM product
);
-- q5.1m
-- find the average price
-- show products with price below average
CREATE VIEW e51m AS
SELECT productid, productname, productprice, vendorid
FROM product
WHERE productprice < (
SELECT AVG(productprice)
FROM product
);
-- q5.1n
-- show the ID of the product that has been sold in the highest quantity
SELECT productid, noofitems
FROM soldvia
WHERE noofitems = (
SELECT MAX(noofitems)
FROM soldvia
);
-- note - this only finds one thing
-- find total of each product
SELECT productid, SUM(noofitems)
FROM soldvia
GROUP BY productid;
-- find maximum quantity
SELECT MAX(noofitems)
FROM soldvia;
-- his example
SELECT MAX(t.sumquant)
FROM (
SELECT productid, SUM(noofitems) AS sumquant
FROM soldvia
GROUP BY productid
) AS t;
-- all in one query
-- CREATE VIEW e51n AS -- aparently you can't create a subview here?
SELECT productid, SUM(noofitems)
FROM soldvia
GROUP BY productid
HAVING SUM(noofitems) = (
SELECT MAX(t.sumquant)
FROM (
SELECT productid, SUM(noofitems) AS sumquant
FROM soldvia
GROUP BY productid
) AS t
);