LS180 Database Foundations

SQL can be thought of as comprising of three separate sub-languages, each concerned with a specific aspect of manipulating or interacting with a database. The three sub-languages are?

- DDL: Data Definition Language - DML: Data Manipulation Language - DCL: Data Control Language

Data Manipulation Statements can be categorized into four different types :

- INSERT statements - These add new data into a database table - SELECT statements - Also referred to as Queries; these retrieve existing data from database tables. We've worked with this type a bit already. - UPDATE statements - These update existing data in a database table. - DELETE statements - These delete existing data from a database table. The actions performed by these four types of statement are sometimes also referred to as CRUD operations.

Why learn SQL?

- Relation databases have become so widespread - Creating a well-designed database is like laying the foundation of a house, and learning SQl and relational database concepts will help you build your applications on a strong foundation. - Since databases are such a key part of almost all web applications, understanding the language of databases and how they work is a vital step towards becoming a well-rounded web-developer.

Common Alter Table Commands

- Renaming tables/columns - Changing column datatype - Add/Remove constraints - Add/Remove columns - Dropping a table

Normalization is a deep topic, and there are complex sets of rules which dictate the extent to which a database is judged to be normalized. These rule-sets, known as 'normal forms', are beyond the scope of this book; for now there are two important things to remember:

- The reason for normalization is to reduce data redundancy and improve data integrity. - The mechanism for carrying out normalization is arranging data in multiple tables and defining relationships between them.

SQL Statement

A SQL command used to access/use the database or the data within that database via the SQL language.

Entity Relationship Diagram (ERD)

A graphical representation of entities and their relationships to each other, and is a commonly used tool within database design.

At a high level, what is database design?

A process that involves defining entities to represent different sorts of data and designing relationships between those entities.

Relation (Databases)

A set of individual but related data entries; analogous to a database table.

Structured Query Language (SQL)

A special purpose language used to mainpulate the structure and values of datasets stored in a relation database. It is described as a special purpose language because it is typically used only for a very specific purpose: interacting with relational databases. This can be contrasted with a general-purpose programming language, such as Ruby.

What is NULL in SQL?

A special value that represents an unknown value. This also means that its behavior in a variety of situations is also unknown. On a practical level though, what this means is that we can't simply treat NULL as we would any other value. We couldn't, for example, have a WHERE clause in the form WHERE column_name = NULL. When identifying NULL values we must instead use the IS NULL comparison predicate. When identifying NULL values we must use IS NULL rather than a normal comparison operator.

Relational Database

A structured collection of data that follows the relational model.

DML: Data Manipulation Language

A sub-language of SQL which incorporates the various key words, clauses and syntax used to write Data Manipulation Statements. Used for accessing and manipulating data in the database. (SELECT, INSERT, UPDATE, DELETE)

SQL query

A subset of a "SQL Statement". A query is a way to search, or lookup data within a database, as opposed to updating or changing data.

What is Referential Integrity?

A topic that's extremely important when dealing with table relationships. This is a concept used when discussing relational data which states that table relationships must always be consistent. Different RDBMSes might enforce referential integrity rules differently, but the concept is the same.

What is a Primary Key?

A unique identifier for a row of data. In order to act as a unique identifier, a column must contain some data, and that data should be unique to each row. If you're thinking that those requirements sound a lot like our NOT NULL and UNIQUE constraints, you'd be right; in fact, making a column a PRIMARY KEY is essentially equivalent to adding NOT NULL and UNIQUE constraints to that column.

What is the syntax for adding multiple columns?

ALTER TABLE table_name ADD COLUMN column1_name, ADD COLUMN column2_name;

What is the ON DELETE clause?

Adding this clause, and setting it to CASCADE basically means that if the row being referenced is deleted, the row referencing it is also deleted. There are alternatives to CASCADE such as SET NULL or SET DEFAULT which instead of deleting the referencing row will set a new value in the appropriate column for that row.

What is a Foreign Key and how is it implemented in SQL?

Allows us to associate a row in one table to a row in another table. This is done by setting a column in one table as a Foreign Key and having that column reference another table's Primary Key column. Creating this relationship is done using the REFERENCES keyword in this form: In general terms you can think of this reference as creating a connection between rows in different tables.

How many Primary Keys can a table have?

Although any column in a table can have UNIQUE and NOT NULL constraints applied to them, each table can have only one Primary Key.

What are acceptable Literals for the booleans true and false in SQL?

Although in PostgreSQL boolean values display as t or f in the results of a SELECT query, t and f are not valid literal boolean values unless used in single quote marks: 't', 'f'. Other acceptable literals are true or false without quote marks; or 't', 'true', 'y', 'yes', 'on', '1' with quote marks for true, and 'f', 'false', 'n', 'no', 'off', '0' with quote marks for false.

Is it possible to delete specific values in row?

Although it's not possible to delete specific values within a row, we can approximate this by using NULL. You may remember in an earlier chapter we explained that NULL is a special value which actually represents an unknown value. By using an UPDATE statement to SET a specific value to NULL, although not deleting it as such, we are effectively removing that value. - Unlike with a WHERE clause, with our SET clause we can use = with NULL since it's not being used as comparison operator in this situation. - If a column has a NOT NULL constraint, then it's not possible to set its value to NULL. An error will be thrown.

BE CAUTIOUS when using UPDATE and DELETE statements! Here's how...

Although we've demonstrated how to update or delete all the rows in a table, the vast majority of the time this is probably not what you want to do. If you are about to run an UPDATE or DELETE statement that doesn't have a WHERE clause, be certain that you want to actually affect the entire table. Even if you are using a WHERE clause in your UPDATE or DELETE statements it's sensible to be a bit cautious. It's typical to first do a SELECT to verify which rows you are targeting. You can then issue the UPDATE or DELETE with the same modifiers, being confident that you will only affect the rows that you intend to. It's rare to just issue an UPDATE or DELETE command without verifying first, and probably not a good idea. This is especially the case with DELETE since you will remove the entire row from the table.

Keywords LIKE vs SIMILAR TO

An alternative to LIKE for pattern matching is to use SIMILAR TO. It works in much the same way as LIKE, except that it compares the target column to a Regex (Regular Expression) pattern.

Wild Card Characters % and underscore _

As well as the % character, the underscore _ can also be used as a wildcard with LIKE, however _ stands in for only a single character whereas % stands in for any number of characters.

Comparison Pedicates

Behave much like comparison operators but have special syntax. Examples include BETWEEN, NOT BETWEEN, IS DISTINCT FROM, IS NOT DISTINCT FROM. We won't discuss these in this book, though there are two important ones which we will briefly cover: IS NULL and IS NOT NULL.

Logical Operators

Can be used to provide more flexibility to your expressions. There are three logical operators: AND, OR, NOT. NOT is less commonly used than the other two, so we won't cover it here. The AND and OR operators allow you to combine multiple conditions in a single expression.

operator precedence in PostgreSQL

Certain operators (e.g. AND) have higher precedence than others (e.g. OR). When writing queries that combine a number of these operators, it is advisable to use parentheses to group them together in order to avoid any potential precedence issues.

SQL Data Types

Classifies particular values that are allowed for that column.

Data

Concerned with the contents of a database. These are the actual values associated with specific rows and columns in a database table.

Schema

Concerned with the structure of a database. This structure is defined by things such as the names of tables and table columns, the data types of those columns and any constraints that they may have.

What is normalization?

Converting poorly structured tables into two or more well-structured tables that have relationships between them. Each table should only have 1 "theme". Splitting up data in this way helps to remove duplication and improve data integrity. Also helps to minimize or eliminate the possible occurrences of update, insertion and deletion anomalies.

ORDER BY

Displays the results of a query in a particular sort order. Unless specified, ASC (ascending) order is the default. - For boolean values, false comes before true in ascending order - Sort order between two of the same values is arbitrary. - You can fine tune your ordering with the ORDER BY clause by specifying the sort direction, either ascending or descending, using the keywords ASC or DESC. - You can fine tune your ordering even further by returning results ordered by more than one column. This is done by having comma-separated expressions in the ORDER BY clause.

Alter Table Syntax

Existing tables can be altered with an ALTER TABLE statement. An ALTER TABLE statement is part of DDL, and is for altering a table schema only

Removing a constraint from a SQL Table or Column

For most types of constraint, the same syntax can be used for both column and table constraints:

What is the Three State Boolean problem or the Three Valued-logic problem?

Generally you want to avoid boolean columns being able to have NULL values, since booleans, by their nature, should only have two states true and false. Throwing NULL into the mix creates three possible states.

When inserting new rows of data and we don't include values for unspecified columns, what does SQL put as the value?

If our INSERT statement specifies both columns and values but we don't specify a particular column, SQL will try to insert null into that column by default.

Rows in Databases

If we think of columns as giving structure to our table, then we can think of rows (sometimes referred to as 'tuples') as actually containing the data. Each row in a table is an individual entity, which can perhaps be seen as the logical equivalent of a record, and has a corresponding value for each column in the table. The rows and columns work together. It is the intersection of the structure provided by our columns and the data in our rows that create the structured data that we need.

Identifiers and Key Words

In a SQL statement such as SELECT enabled, full_name FROM users; there are identifiers and keywords. The identifiers, such as enabled, full_name, and users, identify tables or columns within a table. The keywords, such as SELECT and FROM, tell PostgreSQL to do something specific. Since SQL is not a case-sensitive language, the case differences in our example can't be used by PostgreSQL to differentiate between identifiers and keywords. Instead it assumes that anything which is not a keyword (or operator, or function) is an identifier and so treats it as such. What do we do if we want to use an identifier that is the same as a keyword? For example, we might have a column called year, which is actually a reserved word in PostgreSQL. If used in certain SQL statements, depending on the context, this identifier would cause an error. Generally it's best to try and avoid naming columns the same as keywords for this very reason. If it's unavoidable however, you can double quote the identifier in your statement, like so "year". PostgreSQL then knows to treat it specifically as an identifier rather than as a keyword.

Use of the = sign in SQL

In the WHERE clause of SQL queries, = is treated as an 'equality' operator, in that it compares things. In most programming languages, a single = operator is used solely for assignment, but not so in SQL.

What is referential integrity in a relationship?

Is assurance that a column value within a record must reference an existing value; if it doesn't then an error is thrown. In other words, SQL won't allow you to add a value to the Foreign ey column of a table i the Primary Key column of the table it is referencing does not already contain that value.

Popular convention for naming Primary Keys.

It is common practice for that Primary Key to be a column named id. If you look at the other tables we've defined for our database, most of them have an id column. While a column of any name can serve as the primary key, using a column named id is useful for mnemonic reasons and so is a popular convention.

Schema changes and the effects on the data in a table.

It is important to consider how schema changes will affect the data in a table. Adding an extra column to a table will add an extra column to all existing rows, just as it would in a spreadsheet. Deleting a column means all data in that column will be lost forever. Before jumping in to alter your table, take extra time to think about how the change will affect your existing data.

Determining what to do in situations where you delete a row that is referenced by another row is an important design decision, and is part of which design concept?

It is part of the concept of maintaining referential integrity. If we don't set such clauses we leave the decision of what to do up to the RDBMS we are using.

Delete all Rows

It's rare that you will want to delete all the rows in a table. If you did want to do this however, it can be done with a very simple statement. Just as with UPDATE, the WHERE clause in a DELETE statement is optional. If omitted, all the rows in the table will be deleted.

DISTINCT

On the subject of data quality issues, a common one that you might encounter is duplicate data in your tables. There are situations where you might need to formulate queries in a certain way in order to deal with duplication. One way to do that is with the DISTINCT clause.

Key difference to understand between UPDATE vs DELETE

One key difference to keep in mind between how UPDATE works and how DELETE works: with UPDATE you can update one or more columns within one or more rows by using the SET clause; with DELETE you can only delete one or more entire rows, and not particular pieces of data from within those rows.

An Error to be aware of when using aggregate functions and GROUP BY.

One thing to be aware of when using aggregate functions, is that if you include columns in the column list alongside the function then those columns must also be included in a GROUP BY clause. For example, both of the following statements would return an error:

How does the database know how to interpret SQL statements?

Part of the answer here is the syntax that we use to issue the statement, and we'll look a lot more closely at this syntax later in this book. Another important concept to understand here though is the roles that schema and data play in a database, and how the relationship between the two is what gives the database its power. Schema and data work together in order to let us interact with a database in useful ways. Schema without data would just be a bunch of empty tables and if we had data without schema, we'd be back to the idea of unstructured data we discussed in the opening chapter of this book. Combining schema and data provides us with structured data that we can then interact with in various ways.

Being able to uniquely identify a row of data involved two sides of the same coin. What are those two sides?

Primary Key column is only half the story when it comes to creating relationships between tables. The other half of this story is the Primary Key's partner, the Foreign Key.

What are entities in database design?

Represents a real world object, or a set of data that we want to model within our database; we can often identify these as the major nouns of the system we're modeling. For the purposes of this book we're going to try and keep things simple and draw a direct correlation between an entity and a single table of data; in a real database however, the data for a single entity might be stored in more than one table.

DELETE Statement Syntax

Sometimes simply updating the data in a row isn't enough to fix a particular data discrepancy, and you need to remove that row altogether. The DELETE statement is used to remove entire rows from a database table. The form of a delete statement is somewhat similar to UPDATE: As with UPDATE, the WHERE clause in a DELETE statement is used to target specific rows.

GROUP BY

Sometimes you need to combine data results together to form more meaningful information.

ORDER BY "identifier" DESC

Start from highest value and go to lowest value

ORDER BY "identifier" ASC (or omitted)

Start from lowest value and go to highest value

String Matching Operators

String, or pattern, matching allows you to add flexibility to your conditional expressions in another way, by searching for a sub-set of the data within a column. For instance, let's say we wanted to find all users with the last name Smith. We can't directly check if full_name is equal to Smith, since Smith is only part of the entire name. We need a way to look at a substring within the entire name. As the name suggests, string matching can only be done when the data type of the column is a string type. It is most often carried out using the LIKE operator. When using string matching it's important to make your search terms specific enough for the data that you want to retrieve. In the picture example, our WHERE clause looks very much like other WHERE clauses we've seen so far in this book, except where the = operator would normally be, we use the LIKE operator instead. Also notice the use of the % character in the value that we want to match against; this is a wildcard character. By putting % just before Smith we are saying: Match all users that have a full name with any number of characters followed by "Smith"

SELECT Querying Summary

The SELECT statement is probably the most commonly used statement in SQL. It is one of the most important and frequently used features of the SQL language. Every database-backed application will need to present data to users in some way. In order to meet the many and varied data presentation requirements of different applications, developers need to be able to query data in very specific ways. In this chapter we've explored the SELECT statement syntax, and looked at a few ways in which we can fine-tune our queries, such as using operators or ordering results.

Update All Rows

The UPDATE 5 response tells us how many rows had the value for the enabled column set to false by our UPDATE statement. This includes the row where enabled already had a value of false. Updating all the rows in a table like this is fairly unusual. In general, you'll update specific rows based on some criteria by including a WHERE clause.

Database building analogy

The database is also part of the schema. We can perhaps think of it in terms of the foundations and the 'outer shell' of a building, with the tables in the database being various rooms. The rooms are perhaps different sizes, have different things in them, and some of them might be connected to each other, but they all exist within the same building.

The Problem that structured data aims to solve?

The limitations of unstructured data. Storing data in an unstructured way might work if you only have a small amount, but once the amount of data starts to grow it soon becomes unwieldy.

Database Spreadsheet Analogy

The spreadsheet as a whole can be thought of as a database, and the worksheets within the spreadsheet can be used to describe tables within a database. A table, in the context of a database, can be defined as a list of individual but related data entries (the table rows), each of which store values for a set of defined, shared attributes (the table columns).

Adding a column constraint vs table constraint.

The syntax for adding constraints can vary depending on the type of constraint we're adding. Some types of constraint are considered 'table constraints' (even if they apply to a specific column) and others, such as NOT NULL are considered 'column constraints'.

A Bit About CRUD

The term CRUD is a commonly used acronym in the database world. The letters in CRUD stand for the words CREATE, READ, UPDATE, and DELETE. These four words are analogous to our INSERT, SELECT, UPDATE and DELETE statements, and we can think of these statements as performing their equivalent CRUD operations. Web applications whose main purpose is to provide an interface to perform these operations are often referred to as 'CRUD apps'.

Database naming conventions

There are some conventions you should be aware of when naming a database. As a guideline, always try to keep database names self-descriptive. A descriptive name is especially helpful if you end up having a lot of databases. A database containing information about Employees could be named 'employees' or 'employee_database'. A less descriptive name might be 'emp' or 'records'. Also, database names should be written in snake_case, that is, lowercase with words separated by underscores.

What are relationships between entities in database design?

These are the associations that link entities together and help to describe how they are related to one another.

Caution with The DROP DATABASE and dropdb commands!!!

These commands should be used with extreme care, as their actions are permanent and cannot be reversed. When these commands are issued, all data and schema related to the database is deleted. It's best to take a minute and think carefully before issuing these commands.

SQL String Functions

These functions as their name suggests, perform some sort of operation on values whose data type is String. Some examples are:

SQL Date/ Time Functions

These functions for the most part, perform operations on date and time data. Many of the date/ time functions take time or timestamp inputs. Our last_login column, for example, has a data type of timestamp and so data in that column can act as an argument to such functions:

SQL Aggregate Functions

These functions perform aggregation; that is, they compute a single result from a set of input values.

Comparison Operators

These operators are used to compare one value to another. Often these values are numerical, but other data types can also be compared. Examples of comparison operator would be 'less than' < or 'not equal to' <> (both of which we've already encountered). Within the expression of a WHERE clause, the comparison operator is placed in between the two things being compared; i.e. the column name and the specific value to be compared against the values in that column.

What are keys in database design?

They are a special type of constraint used to establish relationships and uniqueness. They can be used to identify a specific row in the current table, or to refer to a specific row in another table.

What is meant by SQL being a declarative language?

This means that it describes what needs to be done, but does not detail how to accomplish this objective. In practice, this means that the same query might be executed differently on an identical dataset based on a variety of conditions. The SQL server for the most part abstracts these details away from the user, although there are ways to see how a specific query will be executed by the system. This is most commonly utilized when the performance of a query does not meet an application's requirements. For the most part, the SQL database engine selects the most efficient way to execute a query, but in some circumstances a few hints from a human user can improve performance dramatically.

One-to-One Relationship

This relationship between two entities exists when a particular entity instance exists in one table, and it can have only one associated entity instance in another table. Example: A user can have only one address, and an address belongs to only one user. In the database world, this sort of relationship is implemented like this: the id that is the PRIMARY KEY of the users table is used as both the FOREIGN KEY and PRIMARY KEY of the addresses table.

UPDATE Statement Syntax

This statement can be read as, "Set column(s) to these values in a table when an expression evaluates to true". We can specify any table in our database to update and specify any number of columns within that table. The WHERE clause in the above syntax example is optional. If omitted, PostgreSQL will update every row in the target table, so before executing such a query be sure that this is actually what you want to do. Even when using a WHERE clause care must be taken to ensure that it is restrictive or specific enough to target only the rows that you want to modify. You can always test your WHERE clause in a SELECT statement to check which rows are being targeted, before then using it in an UPDATE statement.

Keys and Constraints

Though optional when defining a Column in SQL, they are extremely useful! One of the key functions of a database is to maintain the integrity and quality of the data that it is storing. Keys and Constraints are rules that define what data values are allowed in certain columns. They are an important database concept and are part of a database's schema definition. Defining Keys and Constraints is part of the database design process and ensures that the data within a database is reliable and maintains its integrity. Constraints can apply to a specific column, an entire table, more than one table, or an entire schema.

LIMIT and OFFSTET

To retrieve a portion of rows returned by a query, you use the LIMIT and OFFSET clauses. The following illustrates the syntax of these clauses: In this syntax: The row_count determines the number of rows that will be returned. The OFFSET clause skips the offset rows before beginning to return the rows. The OFFSET clause is optional so you can skip it. If you use both LIMIT and OFFSET clauses the OFFSET skips offset rows first before the LIMIT constrains the number of rows.

What is the syntax for updating multiple columns using SET?

UPDATE table_name SET column1_name = value1, column2_name = value2;

Select Query Syntax

Used to access, or query, data in various ways. Querying data forms the Read part of our CRUD operations, and is arguably the most common operation in database-backed applications.

DDL: Data Definition Language

Used to define the structure (or schema) of a database and the tables and columns within it. It is focused on defining the characteristics of the database and its tables and columns. This includes creating /deleting databases and tables, or altering and changing finer details about your database, such as table names and column data types. But it does not deal with the data within a database. (CREATE, ALTER, DROP)

DCL: Data Control Language

Used to determine what various users are allowed to do when interacting with a database. Is tasked with controlling the rights and access roles of the users interacting with a database or table. (GRANT, REVOKE)

Update Specific Rows

Using a WHERE clause lets us update only the specific rows that meet the condition(s) set in that clause. As long as WHERE clause is specific enough, we can even update a single user as well.

We know that we want to split the data for our application across multiple tables, but how do we decide what those tables should be and what relationships should exist between them?

When answering questions such as these it is often useful to zoom out and think at a higher level of abstraction, and this is where the process of database design comes in.

What to do when using ORDER BY on same values?

When using ORDER BY and the values you wanted to sort are the same, they will be arbitrarily ordered so in this case it would be a good idea to add additional criteria to sort by using other columns.

Insertion Statement Syntax

When using an INSERT statement, we have to provide three key pieces of information: 1.) The table name we wish to store data in. 2.) The names of the columns we're adding data to. 3.) The values we wish to store in the columns listed directly after the table name. When inserting data into a table, you may specify all the columns from the table, just a few of them, or none at all. Depending on how your table is structured, and how your data row is ordered, not specifying columns can sometimes lead to unexpected results or errors, so it is generally best to specify which columns you want to insert data into. When specifying columns, for each column specified you must supply a value for it in the VALUES clause, otherwise you'll get an error back. If you don't specify a column for data insertion, then null or a default value will be added to the record you wish to store instead.

Schema and DCL

When we've previously spoken about creating the schema for a database, we've explained that we use one of the SQL sub-languages, DDL, to do this. There are actually parts of a database's schema that are controlled and managed by another of SQL's sub-language, DCL (Data Control Language).

A couple of use cases for LIMIT and OFFSET

When working with large datasets, a common requirement is to only display one portion of the data at a time. Displaying portions of data as separate 'pages' is a user interface pattern used in many web applications, generally referred to as 'pagination'. An example of this can be seen in the Launch School forum pages, where twelve forum posts are displayed on the first 'page' and you need to navigate to the next 'page' to see the next twelve. As well as specific use cases such as pagination, LIMIT can also be useful in development when testing our queries. We can use LIMIT to get a preview or taste of what data is available or would be returned rather than returning the entire dataset. This is especially useful during development when forming your queries and getting an understanding of the dataset and data quality.

In order to be able to issue commands to a database you must first do what?

You must first connect to the database!

What syntax to use when converting data types if there is no implicit conversion from the old type to the new type?

You need to add a USING clause to the statement, with an expression that specifies how to compute the new column value from the old. Sometimes these expressions can be quite complex, but a simple form would be something like:

Mastering DDL vs DML Syntax

You probably don't need to memorize all of the specific syntax we've covered in this set of chapters. The syntax for DDL is generally only used at the outset when first creating your database and its tables, which is much less often than when you actually work with the data in those tables. As long as you have a clear picture of how schema works, you can always refer back to this book or to the official documentation if you need to check on a particular piece of syntax. Over the next few chapters however, you should familiarize yourself as much as possible with the DML syntax which we will cover. The bulk of the time you spend working with databases will be spent manipulating data, so it is important to be as fluent as possible with the relevant commands and clauses.

How do you actually implement the relationships between entities in a table schema?

You use keys.

What if we want to see more detailed information about a particular table, such as the names of its columns, and the column data types and properties? What PSQL meta-command should we use?

\d "table_name"

Table column definitions consist of...?

a column name, a data type, and optional constraints

The Relational model (databases)

defines a set of relations (which we can think of as analogous to tables) and describes the relationships, or connections, between them in order to determine how the data stored in them can interact.

A relational database management system, or RDBMS

essentially a software application, or system, for managing relational databases. Allows a user, or another application, to interact with a database by issuing commands using syntax that conforms to a certain set of conventions or standards.

In Database architecture this is generally referred to as "client-server" architecture. What is "Client-Server" architecture?

issuing a request, or declaration, and receive a response in return. An analogy for this would be a sushi restaurant where a chef in the center is preparing sushi and people walk up from their tables to order and then to pick up their food. You can think of the chef as the server, the food as the data and the customer as the client. With a database, we connect to the server, such as a PostgreSQL server, using a client, like a PostgreSQL Client. The client transmits commands to the server and the server sends the result or data back.

What is psql?

psql is a PostgreSQL interactive console, or a terminal-based front-end to PostgreSQL. It allows you to write queries in SQL syntax, issue those queries to a PostgreSQL database, and see the results of those queries in the terminal window. In that sense the psql console is essentially a REPL; you may already be familiar with other REPLs, such as IRB.

SQL Statement always terminate by using what character?

semi-colon is used to terminate.

The \dt meta-command

useful for quickly viewing a list of all the tables in a database, but the information it provides is fairly limited.