Skip to main content
HomeAbout SQLLearn SQL

SQL Database Overview Tutorial

In this tutorial, you'll learn about databases in SQL.
Oct 2020  · 3 min read

A database models real-life entities like professors and universities by storing them in tables. Each table contains data from a single entity type. This reduces redundancy by storing entities only once. For example, there only needs to be one row of data containing a certain company's details. Lastly, a database can be used to model the relationship between entities.

SQL is Structured Query Language, which is a computer language for storing, manipulating, and retrieving data stored in a relational database.

SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres, and SQL Server use SQL as their standard database language.

PostgreSQL Database

information_schema is a meta-database that holds information about your current database. It's not PostgreSQL specific and also available in other database management systems like MySQL or SQL Server. information_schema has multiple tables you can query with the known SELECT * FROM syntax:

  • tables: information about all tables in your current database
  • columns: information about all columns in all of the tables in your current database
  SELECT table_schema, table_name
  FROM information_schema.tables;
      table_schema    |              table_name
   -------------------+------------------------------
   pg_catalog         | pg_statistic
   pg_catalog         | pg_type
   pg_catalog         | pg_policy
   pg_catalog         | pg_authid
   pg_catalog         | pg_shadow
   public             | university_professors
   pg_catalog         | pg_settings
  ... 

Commands in SQL

Data Definition Language (DDL)

  • CREATE: It creates a new table, a view of a table.
  • ALTER: It modifies the existing table.
  • DROP: It deletes the entire table or other objects in the database.

Data Manipulation Language (DML)

  • SELECT: It extracts certain records from one or more tables.
  • INSERT: It creates a record in the existing table.
  • UPDATE: It modifies the existing record of the table.
  • DELETE: It deletes the records in the table and even delete the complete table.

Looking at the Columns of a Certain Table

information_schema also holds information about columns in the "columns" table. Once you know the name of a table, you can query its columns by accessing the "columns" table.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'pg_config';


 table_name | column_name | data_type
 -----------+-------------+-----------
 pg_config  | name        | text
 pg_config  | setting     | text 

Here, you see that the system table pg_config has only two columns – supposedly for storing name-value pairs.

Interactive Example

In this exercise, you'll only need information from the 'public' schema, which is specified as the column table_schema of the tables and columns tables. The 'public' schema holds information about user-defined tables and databases. The other types of table_schema hold system information – here, we are only interested in user-defined stuff.

You will get information on all table names in the current database while limiting your query to the 'public' table_schema.

## Query the right table in information_schema
SELECT table_name
FROM information_schema.tables
## Specify the correct table_schema value
WHERE table_schema = 'public';

When we run above code, it produces the following result:

query result

Try it for yourself.

To learn more about relational databases in SQL, please see this video from our course Introduction to Relational Databases in SQL.

This content is taken from DataCamp’s Introduction to Relational Databases in SQL course by Timo Grossenbacher.

Check out our Introduction to Indexing in SQL tutorial.

SQL courses

Introduction to SQL

Beginner
2 hr
265.8K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

A duck codes in DuckDB

DuckDB makes SQL a first-class citizen on DataCamp Workspace

In this blog post, we list out all recent improvements that make it seamless and efficient to query data with SQL, all without leaving the tool.
Filip Schouwenaars's photo

Filip Schouwenaars

The 80 Top SQL Interview Questions and Answers for Beginners & Intermediate Practitioners

This article provides a comprehensive overview of 80 essential SQL questions and answers for job hunters, hiring managers, and recruiters, covering both general topics and technical questions.
Elena Kosourova's photo

Elena Kosourova

33 min

SQL Window Functions Cheat Sheet

With this SQL Window Functions cheat sheet, you'll have a handy reference guide to the various types of window functions in SQL.
Richie Cotton's photo

Richie Cotton

10 min

How to Use SQL in pandas Using pandasql Queries

Unleash the power of SQL within pandas and learn when and how to use SQL queries in pandas using the pandasql library for seamless integration.
Elena Kosourova's photo

Elena Kosourova

8 min

How to Practice SQL Using any Dataset with Workspace

Learn how DataCamp Workspace optimizes the experience of working with Jupyter notebooks and SQL. Discover how to effortlessly write SQL queries, connect to databases, analyze CSV files, and leverage the power of AI assistance
Richie Cotton's photo

Richie Cotton

9 min

See MoreSee More