SQL Foundations I Module 2 Public

Required Evidence

rearrange columns and use aliases

Two queries will demonstrate rearranging columns and assigning column aliases. Provide a link to your work. More Info

Use AND, OR and comparison operators in WHERE

Four queries will utilize various predicates involving multiple AND and OR clauses (a minimum of three total), with a variety of comparison operators. Provide a link to your work. More Info

Use single-row functions in query

Four queries will demonstrate the use of single-row functions (each query must use a different function). Provide a link to your work. More Info

Use aggregate functions in query

Four queries will utilize aggregate functions (each query must use a different function). Provide a link to your work. More Info

Use HAVING clause with GROUP BY

Two queries will filter aggregate results with a HAVING clause. Provide a link to your work. More Info

Badge Overview

http://onyx.csit.parkland.edu/~csc191/modules/module2/index.html

MODULE 2: SELECT, WHERE, GROUP BY (including functions), ORDER BY

Learning Objectives

select columns, rearrange the way columns appear on the output, assign column aliases, how to use ‘*’ (aka “star”) as a shorthand for “all columns”, and print arbitrary text.
use filters in the WHERE clause are called predicates, that predicates are based on boolean logic (evaluating to either TRUE or FALSE), and how to use various operators (=, !=, <, >, LIKE, BETWEEN, AND, OR). Students will be able to use precedence to order AND/OR clauses, effecting different outcomes of the query.
order the rows returned by the query via the ORDER BY clause; they will be able to sort on multiple columns, and use column positional shortcuts
explain the difference between single-row functions and aggregate (or “multi-row”) functions
choose from a pool of single-row functions to accomplish a given task (ie, upper, lower, concatenate, ifnull/nvl, truncate/round, simple arithmetic operations, substring, etc)
choose from a pool of aggregate functions to accomplish a given task (ie, min, max, average, count, sum, etc) - this includes proper use of the GROUP BY clause
explain the advantages of using a HAVING clause to filter aggregate results

Evidence
To show that the objectives have been met, students will generate queries of their own that demonstrate their understanding of the material. The tables will be based on the Konagora SQL Sandbox, Chapter 3 (http://edu.konagora.com/SQLsandbox.php), and links to the queries using a baseline SQL Fiddle shall be provided by the student (one link for each query).
Example: select * from movies;

Two queries will demonstrate rearranging columns and assigning column aliases
Four queries will utilize various predicates involving multiple AND and OR clauses (a minimum of three total), with a variety of comparison operators
Four queries will demonstrate the use of single-row functions (each query must use a different function)
Four queries will utilize aggregate functions (each query must use a different function)
Two queries will filter aggregate results with a HAVING clause
Three of the above queries shall sort data

The student shall provide between four and eight queries that meet the above criteria, all indicating which objective they are demonstrating.

SQL Foundations I Module 2 Public

Intro to SQL

SELECT, WHERE, GROUP BY (including functions), ORDER BY

Required Evidence

rearrange columns and use aliases

Two queries will demonstrate rearranging columns and assigning column aliases. Provide a link to your work. More Info

Use AND, OR and comparison operators in WHERE

Four queries will utilize various predicates involving multiple AND and OR clauses (a minimum of three total), with a variety of comparison operators. Provide a link to your work. More Info

Use single-row functions in query

Four queries will demonstrate the use of single-row functions (each query must use a different function). Provide a link to your work. More Info

Use aggregate functions in query

Four queries will utilize aggregate functions (each query must use a different function). Provide a link to your work. More Info

Use HAVING clause with GROUP BY

Two queries will filter aggregate results with a HAVING clause. Provide a link to your work. More Info

Use ORDER BY to sort data

Three of the above queries shall sort data. Provide a link to your work. More Info

Badge Overview

Badge Experts

Charles Schultz

sacrophyte

Francisco Cruz

FranciscoCruz11

Jacob Uden

jaco

Jay Fisher

jmfisher

Matt Purnell

mpurnell1

Randy McCarthy

randyiv

Summer Lewis

slewis52

Badge Learners

Jasmine Cai

jcai2

Mangai Neelavannan

Mangai

Sharon Dow

Sdow3