Assess Your PostgreSQL Skills

Answer Question #12490 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

Answer Question #12496 There can be multiple databases in one schema.

Answer Question #12499 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

Answer Question #12504 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

Answer Question #12509 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'

Answer Question #12514 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

Answer Question #12519 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

Answer Question #12524 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

Answer Question #12529 After a PostgreSQL database cluster is created the character encoding is fixed for the life of the database cluster. initdb --encoding=UTF8

Answer Question #12532 After a database is created the character encoding is fixed for the life of the database. CREATE DATABASE foo ENCODING 'UTF8'

Answer Question #12535 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

Answer Question #12541 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

Answer Question #12547 Checksums are enabled on data pages by default in PostgreSQL

Answer Question #12550 The code below will prevent access to the 'demo' database for all users including super users. alter database demo with ALLOW_CONNECTIONS false;

Answer Question #12553 At what level can configuration parameters be set? 1. system 2. database 3. role 4. The first two above 5. All three above

Answer Question #12559 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

Answer Question #12564 A line in pg_hba.conf with only tab characters will be ignored by postgres

Answer Question #12567 Which of the following are valid comment symbols in pg_hba.conf? 1. // 2. /* */ 3. # 4. All of the above

Answer Question #12572 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.

Answer Question #12575 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.

Answer Question #12578 Which of the following is the default isolation level in PostgreSQL? 1. Read Uncommitted 2. Read Committed 3. Repeatable Read 4. Serializable

Answer Question #12583 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

Answer Question #12588 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.