(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.
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.
In this train, you will solely want info from the
'public' schema, which is specified because the column
table_schema of the
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
## 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:
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.