SQL Tutorial: Introduction to Relational Databases in SQL

welcome to this course on introduction to relational databases my name is Timo gross Bakker and I work as a data journalist in Switzerland in this course you'll see why using relational databases has many advantages over using flat files like CSV or excel sheets you'll learn how to create such databases and bring into force their most prominent features let me tell you a little story first as a data journalist I tried to uncover corruption Mis conduct another newsworthy stuff with data a couple of years ago I researched secondary employment of Swiss University professors it turns out a lot of them have more than one side job besides their university duty being paid by big companies like banks and insurances so I discovered more than 1500 external employments and visualized them in an interactive graphic shown on the left for this story I had to compile data from various sources with varying quality also I had to account for certain specialities for example now the professor can work for different universities or that a third-party company can have multiple professors working for them in order to analyze the data I needed to make sure its quality was good and stayed good throughout the process that's why I stored my data in a database whose quite complex design you can see in the right graphic all these rectangles were turned into database tables but why did I use a database a database models real-life entities like professors and universities by storing them in tables each table only contains data from a single entity type that this reduces redundancy by storing entities only once for example there only needs to be one row of data containing the details of a certain company lastly a database can be used to model relationships between entities you can define exactly how entities relate to each other for instance a professor can work at multiple universities and companies while a company can employ more than one professor throughout this course you will actually work with the same real-life data used during my investigation you'll start from a single table of data and build a full-blown relational database from it column by column table by table by doing so you'll get to know constraints keys and referential integrity these three concepts help preserve data quality and databases by the end of the course you'll know how to use them in order to get going you'll just need a basic understanding of sequel which can also be used to build and maintain databases not just for clearing data I've already created a single Postgres sequel database table containing all the raw data for this course in the next few exercises I want you to have a look at that table for that you'll need to retrieve your sequel knowledge and query the information schema database which is available in Postgres sequel by default information schema is actually some sort of meta database that holds information about your current database it's not Postgres sequel specific and also available in other database management systems like MySQL or sequel server this information schema database holds various information in different tables for example in the tables 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 here for example you see that the system table Peachy config has only two columns supposedly for storing name value pairs okay let's have a look at your