SQL Foundations I Module 3 Public

Required Evidence

One query will use a natural join

One query will use a natural join. Submit a link to show your work. More Info

Write an INNER JOIN query, with USING and ON

Three queries shall use an inner join, with a mix of USING and ON clauses. Submit a link to show your work. More Info

Write three queries using outer joins

Three queries shall use outer joins. Submit a link to show your work. More Info

Write three queries using different SET operators

Three queries shall demonstrate the different Set operators. Submit a link to show your work. More Info

Write 4 queries with different subqueries

Four queries will use subqueries for different reasons (ie, one query might be a correlated subquery to find duplicate values while another query might find a local maximum value). Submit a link to show your work. More Info

Use WHERE, ORDER BY, GROUP BY clauses

All queries will use different predicates (WHERE clause), sort on different attributes (ORDER BY clause), and use a mix of single-row and aggregate functions. Submit a link to show your work. More Info

Badge Overview

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

MODULE 3: FROM (multi-table joins), SETs and Subqueries

Learning Objectives

Students will know how to combine multiple tables in a single query
Students will understand the different types of joins and which is the most appropriate for a given task
Students will know the difference between a join condition (used in the JOIN clause) and a predicate (used in the WHERE clause)
Students will understand the different Set operators (MINUS, INTERSECT, UNION)
Students will understand how and when to use subqueries
Students will have mastered the basics of SQL

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 (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;

One query will use a natural join
Three queries shall use an inner join, with a mix of USING and ON clauses
Three queries shall use outer joins
Four of the above queries will use a mix of single-row and aggregate functions
Three queries shall demonstrate the different Set operators
Four queries will use subqueries for different reasons (ie, one query might be a correlated subquery to find duplicate values while another query might find a local maximum value)
All queries will use different predicates (WHERE clause), sort on different attributes (ORDER BY clause), and use a mix of single-row and aggregate functions

The student shall provide seven queries (fewer can be accepted if some statements are nested) that meet the above criteria, all indicating which objective they are demonstrating.