SQL 101: Introduction to Database Queries | Mark Florian | Skillshare

SQL 101: Introduction to Database Queries

Mark Florian, Software Developer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
12 Videos (1h 29m)
    • Trailer

      0:55
    • What is SQL?

      6:27
    • Installing SQLite Manager and creating our database

      3:34
    • Creating tables and inserting data into tables

      Lesson3_CreateInsertData.txt
      6:01
    • Single Table Queries (Simple)

      Lesson4_Exercises_Simple.txt
      9:24
    • Solutions: Single Table Queries (Simple)

      Lesson4_Solutions_Simple.txt
      5:16
    • Single Table Queries (Advanced)

      Lesson5_Exercises.txt
      9:52
    • Solutions: Single Table Queries (Advanced)

      Lesson5_Solutions.txt
      7:08
    • Joins

      Lesson6_Examples.txt
      Lesson6_Exercises.txt
      13:38
    • Solutions: Joins

      Lesson6_Solutions.txt
      9:13
    • Subqueries

      Lesson7_Examples.txt
      Lesson7_Exercises.txt
      7:43
    • Solutions: Subqueries

      Lesson7_Solutions.txt
      10:09
23 students are watching this class

Project Description

Create and query a database using SQLite

SQL Fundamentals

  1. Student/classroom relationships

    Extending from the relationship that we saw between Student, Address, and StudentAddressMapping, try to think of another relationship that we can create for Students.

Setting Up Our Environments

  1. Installing SQLite Manager
    1. Install SQLite Manager (https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/)
    2. Restart Firefox
    3. Open SQLite Manager (Tools -> SQLite Manager)
    4. Navigate to the ‘Execute SQL’ tab
    5. Download CreateScript.txt from Skillshare class page
    6. Copy and paste text from CreateScript.txt into the SQL editor window
    7. Click ‘Run SQL’

Creating Data

  1. Create some data

    Use what you learned in this unit to create your own table and populate that table with some data. Try to create a table that makes sense in our Classroom database so that you can relate it to other tables that already exist.

Single Table Queries

  1. Queries for single table (simple)

    1. Get the first and last name of every student

    2. Get the first name and birthday of every student who takes more than 4 classes

    3. Get the first name of every student whose last name ends with 'n'

    4. Get all the information on any student whose last name is either 'Simpson' or 'Star'

    5. Count the number of students who have the string 'an' somewhere in their first or last name

  2. Queries for single table (advanced)

    1. Count the number of total classes that Students with the last name 'Simpson' take

    2. Get a list of unique last names

    3. Get a list of unique last names in alphabetical order

    4. Get the first and last name of students who take more than 5 classes

    5. Get the first and last name of the 3 youngest Students

    6. Get a list of unique last names along with the number of times each last name appears in the Student table

    7. Get a list of last names that appear more than once in the Student table

  3. Query your table

    Try and come up with 2-5 queries to run on your database that involve the new table(s) that you have created. These queries should involve statements from both the Simple and Advanced lessons.

Joins

  1. Join in your tables

    Use your new knowledge of joins to construct 2-5 queries showing relationships between our set of Classroom tables and the new table(s) that you have created.

Subqueries

  1. Convert joins to subqueries

    Take the joins you created in your last project step and try to convert them into equivalent subqueries.

Attached Files