SQL Database Overview
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.
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.
In this exercise, you'll only need information from the
'public' schema, which is specified as the column
table_schema of the
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
## 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:
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.