PLAY READ BLOG

Assess your experience with PostgreSQL

I estimate it would take you 48 steps in 11 minutes to Assess your experience with PostgreSQL.
What are possible ways to create a new database in a PostgreSQL system?
1. From psql execute 'NEW DATABASE'

2. From psql execute 'CREATE DATABASE'

3. Run the command line tool 'createdb'

4. Either the first or second option above

5. Either the second or third option above
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
There can be multiple databases in one schema.
  • True
  • False
What is output by the SQL below?

DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a INT, b INT, c INT);
INSERT INTO foo VALUES (1,2,3);
INSERT INTO foo SELECT * FROM foo;
INSERT INTO foo SELECT * FROM foo;
SELECT sum(b) FROM foo;
1. 1

2. 2

3. 8

4. 16
  • 1.
  • 2.
  • 3.
  • 4.
What is output by the SQL below?

DROP SCHEMA IF EXISTS foo CASCADE;
DROP SCHEMA IF EXISTS koo CASCADE;
CREATE SCHEMA foo;
CREATE SCHEMA koo;
CREATE TABLE koo.foo (a INT, b INT, c INT);
CREATE TABLE foo.foo (a INT, b INT, c INT);
INSERT INTO foo.foo VALUES (1,2,3);
INSERT INTO koo.foo SELECT * FROM koo.foo;
INSERT INTO koo.foo SELECT * FROM koo.foo;
SELECT sum(b) FROM foo.foo;
1. 0

2. 2

3. 8

4. 16
  • 1.
  • 2.
  • 3.
  • 4.
How is the schema search path found in PostgreSQL?
1. show search_path;

2. show search path;

3. select * from search_path

4. select * from paths where name = 'search'
  • 1.
  • 2.
  • 3.
  • 4.
Which are valid ways to get a list of databases in an instance?
1. select datname from pg_database;

2. psql -l

3. show databases;

4. The first two options above
  • 1.
  • 2.
  • 3.
  • 4.
Which are valid ways to get a list of schemas in an database?
1. select nspname from pg_namespace;

2. \dn

3. psql -s

4. The first two options above
  • 1.
  • 2.
  • 3.
  • 4.
Which are valid ways to get a list of tables in an database?
1. select tablename from pg_tables;

2. \dt *.*

3. select relname, relkind from pg_class where relkind = 'r';

4. All three options above
  • 1.
  • 2.
  • 3.
  • 4.
After a PostgreSQL database cluster is created the character encoding is fixed for the life of the database cluster.

initdb --encoding=UTF8
  • True
  • False
After a database is created the character encoding is fixed for the life of the database.

CREATE DATABASE foo ENCODING 'UTF8'
  • True
  • False
Which are valid ways to find the encoding parameters of existing databases?
1. select * from pg_database;

2. psql -l

3. show pg_encoding;

4. The first two options above

5. All three options above
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
Which database(s) are created immediately by the initdb command when a PostgreSQL database cluster is created?
1. template0

2. template1

3. postgres

4. The first two above

5. All three above
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
Checksums are enabled on data pages by default in PostgreSQL
  • True
  • False
The code below will prevent access to the 'demo' database for all users including super users.

alter database demo with ALLOW_CONNECTIONS false;
  • True
  • False
At what level can configuration parameters be set?
1. system

2. database

3. role

4. The first two above

5. All three above
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
Assuming the code below was already run by a superuser, what happens when user george tries to create a table in the rawdata schema?

CREATE ROLE engineers;
CREATE ROLE george WITH LOGIN;
GRANT engineers TO george;
GRANT ALL ON SCHEMA rawdata TO engineers;
1. table successfully created

2. george does not have access to the schema

3. the schema is not found

4. the table will be accidentally created in the public schema
  • 1.
  • 2.
  • 3.
  • 4.
A line in pg_hba.conf with only tab characters will be ignored by postgres
  • True
  • False
Which of the following are valid comment symbols in pg_hba.conf?
1. //

2. /* */

3. #

4. All of the above
  • 1.
  • 2.
  • 3.
  • 4.
The last record in the pg_hba.conf file that is a match with the incoming connection will be the one that is used to perform authenticate.
  • True
  • False
If authentication fails, the pga_hba.conf file will be searched for the next matching record to attempt the next authentication attempt as fall-through.
  • True
  • False
Which of the following is the default isolation level in PostgreSQL?
1. Read Uncommitted

2. Read Committed

3. Repeatable Read

4. Serializable
  • 1.
  • 2.
  • 3.
  • 4.
What behavior in PostgreSQL is different between Read Comitted and Read Uncommitted isolation levels?
1. Dirty reads are possible in Read Uncommitted

2. Phantom reads are possible in Read Uncommitted

3. Both A and B above

4. There is no difference
  • 1.
  • 2.
  • 3.
  • 4.
With isolation level Repeatable Read, its possible within the same transaction to re-execute a query with a search condition and find a different set of rows returned.
  • True
  • False
Get Started   

Or consider 78 other intentions.