(Tutorial) SQL Database Overview – DataCamp


A database fashions actual-life entities like professors and universities by storing them in tables. Each desk comprises knowledge from a single entity sort. This reduces redundancy by storing entities solely as soon as. For instance, there solely must be one row of information containing a sure firm’s particulars. Lastly, a database can be utilized to mannequin the connection between entities.

SQL is Structured Query Language, which is a pc language for storing, manipulating, and retrieving knowledge saved in a relational database.

SQL is the usual 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 commonplace database language.

PostgreSQL Database

information_schema is a meta-database that holds details about your present database. It’s not PostgreSQL particular and in addition accessible in different database administration methods like MySQL or SQL Server. information_schema has a number of tables you may question with the recognized SELECT * FROM syntax:

  • tables: details about all tables in your present database
  • columns: details about all columns in the entire tables in your present 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 brand new desk, a view of a desk.
  • ALTER: It modifies the prevailing desk.
  • DROP: It deletes your complete desk or different objects within the database.

Data Manipulation Language (DML)

  • SELECT: It extracts sure information from a number of tables.
  • INSERT: It creates a report within the current desk.
  • UPDATE: It modifies the prevailing report of the desk.
  • DELETE: It deletes the information within the desk and even delete the entire desk.

Looking on the Columns of a Certain Table

information_schema additionally holds details about columns within the “columns” desk. Once you already know the identify of a desk, you may question its columns by accessing the “columns” desk.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'pg_config';
 table_name | column_name | data_type
 pg_config  | identify        | textual content
 pg_config  | setting     | textual content

Here, you see that the system desk pg_config has solely two columns – supposedly for storing identify-worth pairs.

Interactive Example

In this train, you will solely want info from the 'public' schema, which is specified because the column table_schema of the tables and columns tables. The 'public' schema holds details about person-outlined tables and databases. The different varieties of table_schema maintain system info – right here, we’re solely interested by person-outlined stuff.

You will get info on all desk names within the present database whereas limiting your question to the 'public' table_schema.

## Query the appropriate desk in information_schema
SELECT table_name
FROM information_schema.tables
## Specify the proper table_schema worth
WHERE table_schema = 'public';

When we run above code, it produces the next end result:

Try it for your self.

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

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


Source hyperlink

Write a comment