SELECT Major,
Median,Unemployed
FROM recent_grads
WHERE
Median > 10000 OR
Unemployed <= 1000
LIMIT 20
SELECT Major_category,
Major,
Unemployment_rate
FROM recent_grads
WHERE
Major_category = 'Engineering' OR
Major_category = 'Physical Sciences'
ORDER BY Unemployment_rate
SELECT Major,
ShareWomen,Unemployment_rate
FROM recent_grads
WHERE ShareWomen > 0.3
AND Unemployment_rate < 0.1
ORDER BY ShareWomen DESC
SELECT COUNT(Major)
FROM recent_grads
WHERE ShareWomen < 0.5
SELECT MIN(ShareWomen)
FROM recent_grads;
SELECT Major, MIN(ShareWomen)
FROM recent_grads
Write a query that returns the Engineering major with the lowest median salary.
We only want the Major, Major_category, and MIN(Median) columns in the result.
SELECT Major, Major_category, MIN(Median)
FROM recent_grads
WHERE Major_category='Engineering'
The final two aggregation functions we'll look at are SUM() and AVG(). Applying the SUM() function will add all of the values in a column while AVG() will compute the average. Lastly, the TOTAL() function also returns the sum as a floating point value (even if the column contains integers). The TOTAL() function should be used when working with a column containing floating point values. You can read more here.
This time around, we're going to skip showing sample code since these functions are used the same way as COUNT(), MIN(), and MAX(). This is good practice working with new functions, as SQL contains many functions that you'll end up using down the road that you haven't been taught explicitly.
Write a query that computes the sum of the Total column. - Return only the total number of students integer value.
Select SUM(Total)
from recent_grads
SELECT Major, Major_category
FROM recent_grads
SELECT MIN(Median),
MAX(Median),
SUM(Total)
FROM recent_grads
Write a query that computes the average of the Total column, the minimum of the Men column, and the maximum of the Women column, in that specific order.
Make sure that all of the aggregate functions are capitalized (SUM() not sum(), etc), so our results match yours.
SELECT AVG(Total),
MIN(Men),
MAX(Women)
FROM recent_grads
it's helpful to be able to specify custom names for the columns in our results.
We can do just that using the AS operator:
SELECT COUNT(*)
as num_students
FROM recent_grads
SELECT COUNT(*)
as "Total Students"
FROM recent_grads
Even better, we can drop AS entirely and just add the name next to the original column:
SElECT COUNT(*)
"Total Students"
FROM recent_grads
we can reference renamed columns when writing longer queries to make our code more compact:
SELECT Major m,
Major_category mc,
Unemployment_rate ur
FROM recent_grads
WHERE (mc = 'Engineering') AND
(ur > 0.04 and ur < 0.08)
ORDER BY ur DESC
Write a query that returns, in the following order:
the number of rows as Number of Students
the maximum value of Unemployment_rate as Highest Unemployment Rate
SELECT COUNT(*) "Number of Students",
MAX (Unemployment_rate) 'Highest Unemployment Rate'
FROM recent_grads
We can return all of the unique values in a column using the DISTINCT statement
SELECT DISTINCT
Major_category
FROM recent_grads
SELECT DISTINCT
Major, Major_category
FROM recent_grads
limit 5
SELECT COUNT(DISTINCT(
Major_category))
unique_major_categories
FROM recent_grads
Write a query that returns the number of unique values in the Major, Major_category, and Major_code columns. Use the following aliases in the following order:
For the unique value count of the Major column, use the alias unique_majors.
For the unique value count of the Major_category column, use the alias unique_major_categories.
For the unique value count of the Major_code column, use the alias unique_major_codes.
SELECT
COUNT(DISTINCT(Major))
unique_majors,
COUNT(DISTINCT(Major_category))
unique_major_categories,
COUNT(DISTINCT(Major_code))
unique_major_codes
FROM recent_grads
Write a query that computes the difference between the 25th and 75th percentile of salaries for all majors.
Return the Major column first, using the default column name.
Return the Major_category column second, using the default column name.
Return the compute difference between the 25th and 75th percentile third, using the alias quartile_spread.
Order the results from lowest to highest and only return the first 20 results.
SELECT Major,
Major_category,
(P75th-P25th) quartile_spread
FROM recent_grads
ORDER BY quartile_spread
LIMIT 20
To illustrate, we can find the total number of people employed in each major category with the following query:
SELECT
SUM(Employed)
FROM recent_grads
GROUP BY Major_category
SELET Major_category,
SUM(Employed)
FROM rencent_grads
GROUP BY Major_category
Use the SELECT statement to select the following columns and aggregates in a query:
Major_category
AVG(ShareWomen)
Use the GROUP BY statement to group the query by the Major_category column.
SELECT Major_category,AVG(ShareWomen)
FROM recent_grads
GROUP BY Major_category
For each major category, find the percentage of graduates who are employed.
Use the SELECT statement to select the following columns and aggregates in your query:
Major_category
AVG(Employed) / AVG(Total) as share_employed
Use the GROUP BY statement to group the query by the Major_category column.
SELECT Major_category,
AVG(Employed)/AVG(Total) AS
share_employed
FROM recent_grads
GROUP BY Major_category
We can't use the WHERE clause to do this because share_employed isn't a column in recent_grads; it's actually a virtual column generated by the GROUP BY statement.
SELECT Major_category,
AVG(Employed) / AVG(Total) AS
share_employed
FROM recent_grads
GROUP BY Major_category
HAVING share_employed > .8
Find all of the major categories where the share of graduates with low-wage jobs is greater than .1.
Use the SELECT statement to select the following columns and aggregates in a query:
Major_category
AVG(Low_wage_jobs) / AVG(Total) as share_low_wage
Use the GROUP BY statement to group the query by the Major_category column.
Use the HAVING statement to restrict the selection to rows where share_low_wage is greater than .1.
SELECT Major_category,
AVG(Low_wage_jobs)/AVG(Total) AS
share_low_wage
FROM recent_grads
GROUP BY Major_category
HAVING share_low_wage >.1
Write a SQL query that returns the following columns of recent_grads (in the same order):
ShareWomen rounded to 4 decimal places
Major_category
Limit the results to 10 rows.
SELECT ROUND(ShareWomen,4),
Major_category
FROM recent_grads
limit 10
SELECT Major_category,
ROUND(AVG(Employed) / AVG(Total), 3)
AS share_employed
FROM recent_grads
GROUP BY Major_category
HAVING share_employed > .8;
Use the SELECT statement to select the following columns and aggregates in a query:
Major_category
AVG(College_jobs) / AVG(Total) as share_degree_jobs
Use the ROUND function to round share_degree_jobs to 3 decimal places.
Group the query by the Major_category column.
Only select rows where share_degree_jobs is less than .3.
SELECT Major_category,
ROUND(AVG(College_jobs)/AVG(Total),3) AS
share_degree_jobs
FROM recent_grads
GROUP BY Major_category
HAVING share_degree_jobs < .3
Write a query that divides the sum of the Women column by the sum of the Total column, aliased as SW.
Group the results by Major_category and order by SW.
The results should only contain the Major_category and SW columns, in that order.
SELECT Major_category,
Cast(SUM(Women) as Float)/Cast(SUM(Total) as Float) SW
FROM recent_grads
GROUP BY Major_category
ORDER BY SW
网友评论