Querying a database

Intermediate SQL

Jasmin Ludolf

Data Science Content Developer, DataCamp

Course roadmap

 

  • Querying databases
  • Count and view specified records
  • Understand query execution and style
  • Filtering
  • Aggregate functions
  • Sorting and grouping

PostgreSQL Logo

Intermediate SQL

Our films database

Schema showing the four tables in the films database including field names and data types

Intermediate SQL

COUNT()

  • COUNT()
  • Counts the number of records with a value in a field
  • Use an alias for clarity
SELECT COUNT(birthdate) AS count_birthdates
FROM people;

|count_birthdates|
|----------------|
|6152            |
Intermediate SQL

COUNT() multiple fields

SELECT COUNT(name) AS count_names, COUNT(birthdate) AS count_birthdates
FROM people;

|count_names|count_birthdates|
|-----------|----------------|
|6397       |6152            |
Intermediate SQL

Using * with COUNT()

  • COUNT(field_name) counts values in a field
  • COUNT(*) counts records in a table
  • * represents all fields
SELECT COUNT(*) AS total_records
FROM people;

|total_records|
|-------------|
|8397         |
Intermediate SQL

DISTINCT

  • DISTINCT removes duplicates to return only unique values
SELECT language
FROM films;

|language |
|---------|
|Danish   |
|Danish   |
|Greek    |
|Greek    |
|Greek    |
  • Which languages are in our films table?

 

SELECT DISTINCT language
FROM films;

|language |
|---------|
|Danish   |
|Greek    |
Intermediate SQL

COUNT() with DISTINCT

  • Combine COUNT() with DISTINCT to count unique values
SELECT COUNT(DISTINCT birthdate) AS count_distinct_birthdates
FROM people;

|count_distinct_birthdates|
|-------------------------|
|5398                     |
  • COUNT() includes duplicates
  • DISTINCT excludes duplicates
Intermediate SQL

Let's practice!

Intermediate SQL

Preparing Video For Download...