MVCC allows each transaction to see a snapshot of data, preventing readers from blocking writers and vice versa.
Incorrect! Try again.
3What is the default TCP port that PostgreSQL listens on?
A.8080
B.3306
C.5432
D.1521
Correct Answer: 5432
Explanation:
Port 5432 is the standard default port assigned to PostgreSQL.
Incorrect! Try again.
4What is the name of the interactive terminal-based command-line tool for PostgreSQL?
A.pg_terminal
B.pgAdmin
C.psql
D.sqlcmd
Correct Answer: psql
Explanation:
psql is the primary terminal-based front-end to PostgreSQL allowing users to type queries interactively.
Incorrect! Try again.
5Which configuration file is primarily responsible for controlling client authentication in PostgreSQL?
A.pg_ident.conf
B.config.json
C.postgresql.conf
D.pg_hba.conf
Correct Answer: pg_hba.conf
Explanation:
pg_hba.conf (Host Based Authentication) controls which hosts are allowed to connect, how clients are authenticated, and which PostgreSQL user names they can use.
Incorrect! Try again.
6What is the default superuser account created during PostgreSQL installation?
A.root
B.postgres
C.admin
D.sa
Correct Answer: postgres
Explanation:
The default operating system user and database superuser created by the PostgreSQL installer is named 'postgres'.
Incorrect! Try again.
7Which command inside the psql interface is used to list all databases?
A.Both A and B
B.SHOW DATABASES;
C.\l
D.\list
Correct Answer: Both A and B
Explanation:
In psql, \l is a shortcut for \list; both commands display the list of databases on the server.
Incorrect! Try again.
8Which data type in PostgreSQL is best suited for storing JSON data with indexing capabilities?
A.TEXT
B.VARCHAR
C.JSON
D.JSONB
Correct Answer: JSONB
Explanation:
JSONB stores data in a decomposed binary format, which is slower to input but faster to process and supports indexing.
Incorrect! Try again.
9Which SQL statement is used to create a new database in PostgreSQL?
A.NEW DATABASE name;
B.CREATE DATABASE name;
C.INIT DATABASE name;
D.MAKE DATABASE name;
Correct Answer: CREATE DATABASE name;
Explanation:
CREATE DATABASE is the standard SQL command to instantiate a new database container.
Incorrect! Try again.
10What does the acronym WAL stand for in the context of PostgreSQL architecture?
A.Wide-Area Latency
B.Web Application Layer
C.Write-After Log
D.Write-Ahead Logging
Correct Answer: Write-Ahead Logging
Explanation:
WAL (Write-Ahead Logging) is a standard method for ensuring data integrity by logging changes before writing them to the main database files.
Incorrect! Try again.
11Which command allows you to connect to a specific database from within the psql prompt?
A.\connect dbname
B.Both A and B
C.\c dbname
D.\use dbname
Correct Answer: Both A and B
Explanation:
\c is the shorthand for \connect; both are used to switch the current database connection in psql.
Incorrect! Try again.
12To remove a table and all its data from the database, which command is used?
A.ERASE TABLE
B.DELETE TABLE
C.REMOVE TABLE
D.DROP TABLE
Correct Answer: DROP TABLE
Explanation:
DROP TABLE removes the table definition and all associated data permanently.
Incorrect! Try again.
13Which PostgreSQL data type is an auto-incrementing integer typically used for primary keys?
A.SERIAL
B.INT
C.NUMERIC
D.AUTO_INT
Correct Answer: SERIAL
Explanation:
The SERIAL pseudo-type automatically creates a sequence and assigns the next value to the column, useful for IDs.
Incorrect! Try again.
14What is the purpose of the 'TRUNCATE' command?
A.To delete specific rows based on a condition
B.To delete all rows quickly without logging individual row deletions
C.To drop the table structure
D.To minify the database size
Correct Answer: To delete all rows quickly without logging individual row deletions
Explanation:
TRUNCATE empties a table much faster than DELETE because it does not scan the table or vacuum it, but it cannot use a WHERE clause.
Incorrect! Try again.
15Which constraint ensures that a column cannot contain NULL values?
A.UNIQUE
B.PRIMARY KEY
C.NOT NULL
D.CHECK
Correct Answer: NOT NULL
Explanation:
The NOT NULL constraint strictly enforces that a column must have a value for every row.
Incorrect! Try again.
16How do you rename an existing table 'users' to 'customers' in PostgreSQL?
A.UPDATE TABLE users SET NAME = customers;
B.MODIFY TABLE users RENAME customers;
C.RENAME TABLE users TO customers;
D.ALTER TABLE users RENAME TO customers;
Correct Answer: ALTER TABLE users RENAME TO customers;
Explanation:
The ALTER TABLE command combined with RENAME TO is the syntax used to change a table's name.
Incorrect! Try again.
17Which operator is used for pattern matching with wildcards in a WHERE clause?
A.=
B.MATCH
C.LIKE
D.SAME
Correct Answer: LIKE
Explanation:
The LIKE operator is used to search for a specified pattern in a column.
Incorrect! Try again.
18In the context of the LIKE operator, what does the '%' wildcard represent?
A.A NULL value
B.Exactly one character
C.Zero or more characters
D.A numeric digit
Correct Answer: Zero or more characters
Explanation:
The percent sign (%) represents zero, one, or multiple characters in a string comparison.
Incorrect! Try again.
19Which SQL clause is used to filter records that meet a specified condition?
A.GROUP BY
B.WHERE
C.ORDER BY
D.HAVING
Correct Answer: WHERE
Explanation:
The WHERE clause is used to extract only those records that fulfill a specified condition.
Incorrect! Try again.
20What is the correct syntax to insert a new record into the 'students' table?
A.UPDATE students ADD (1, 'John');
B.INSERT students SET id=1, name='John';
C.ADD TO students VALUES (1, 'John');
D.INSERT INTO students (id, name) VALUES (1, 'John');
Correct Answer: INSERT INTO students (id, name) VALUES (1, 'John');
Explanation:
Standard SQL syntax for adding data is INSERT INTO table_name (columns) VALUES (values).
Incorrect! Try again.
21How can you retrieve all columns from the 'employees' table?
A.FETCH employees;
B.SELECT ALL FROM employees;
C.GET * FROM employees;
D.SELECT * FROM employees;
Correct Answer: SELECT * FROM employees;
Explanation:
The asterisk (*) is a wildcard character in SQL meaning 'all columns'.
Incorrect! Try again.
22Which clause allows you to limit the number of rows returned by a query?
A.ROWNUM
B.LIMIT
C.STOP
D.TOP
Correct Answer: LIMIT
Explanation:
PostgreSQL uses the LIMIT clause to specify the maximum number of records to return.
Incorrect! Try again.
23Which command allows you to skip a specific number of rows before returning the result set?
A.SKIP
B.OFFSET
C.JUMP
D.NEXT
Correct Answer: OFFSET
Explanation:
The OFFSET clause is used to skip the specified number of rows before beginning to return rows.
Incorrect! Try again.
24What is the correct syntax to update the email of a user with id 5?
A.MODIFY users SET email = 'new@test.com' WHERE id = 5;
B.CHANGE users email = 'new@test.com' WHERE id = 5;
C.UPDATE users SET email = 'new@test.com' WHERE id = 5;
D.SET users.email = 'new@test.com' WHERE id = 5;
Correct Answer: UPDATE users SET email = 'new@test.com' WHERE id = 5;
Explanation:
The UPDATE statement uses the SET clause to modify values and WHERE to identify rows.
Incorrect! Try again.
25What happens if you run a DELETE FROM table_name command without a WHERE clause?
A.It deletes all rows in the table.
B.It deletes the table structure.
C.It deletes the first row.
D.It throws a syntax error.
Correct Answer: It deletes all rows in the table.
Explanation:
Without a WHERE clause, the DELETE command applies to all rows in the table, effectively clearing the data.
Incorrect! Try again.
26Which keyword is used to return data immediately after an INSERT, UPDATE, or DELETE operation in PostgreSQL?
A.RETURNING
B.RETURN
C.OUTPUT
D.BACK
Correct Answer: RETURNING
Explanation:
The RETURNING clause causes the command to return the value of modified rows, often used to get the ID of a newly inserted row.
Incorrect! Try again.
27Which PostgreSQL data type stores both date and time?
A.TIME
B.TIMESTAMP
C.DATE
D.DATETIME
Correct Answer: TIMESTAMP
Explanation:
TIMESTAMP stores both date and time. PostgreSQL also supports TIMESTAMP WITH TIME ZONE.
Incorrect! Try again.
28Which command is used to modify the structure of an existing table, such as adding a column?
A.ALTER TABLE
B.MODIFY TABLE
C.CHANGE TABLE
D.UPDATE TABLE
Correct Answer: ALTER TABLE
Explanation:
ALTER TABLE is the DDL command used to add, drop, or modify columns in an existing table.
Incorrect! Try again.
29To ensure a column's value is unique across the entire table, which constraint should be used?
A.UNIQUE
B.PRIMARY
C.SINGLE
D.DISTINCT
Correct Answer: UNIQUE
Explanation:
The UNIQUE constraint ensures that all values in a column are different.
Incorrect! Try again.
30Which operator is used to combine string values in PostgreSQL?
A.||
B.&
C.CONCAT()
D.+
Correct Answer: ||
Explanation:
The double pipe (||) is the standard concatenation operator in PostgreSQL, though the CONCAT function also exists.
Incorrect! Try again.
31How do you sort the result set in descending order?
A.ORDER BY column_name DESC
B.ORDER BY column_name ASC
C.SORT BY column_name DESC
D.GROUP BY column_name DESC
Correct Answer: ORDER BY column_name DESC
Explanation:
The ORDER BY clause with the DESC keyword sorts the records in descending order.
Incorrect! Try again.
32Which function is used to count the number of rows in a selection?
A.SUM()
B.COUNT()
C.ADD()
D.TOTAL()
Correct Answer: COUNT()
Explanation:
COUNT() returns the number of rows that match a specified criterion.
Incorrect! Try again.
33What is the purpose of the DISTINCT keyword in a SELECT statement?
A.To remove duplicate values from the result set
B.To filter null values
C.To limit results
D.To sort results
Correct Answer: To remove duplicate values from the result set
Explanation:
SELECT DISTINCT returns only distinct (different) values.
Incorrect! Try again.
34Which of the following is a correct command to add a new column 'age' of type integer to table 'people'?
A.INSERT COLUMN age INTEGER INTO people;
B.ALTER TABLE people ADD COLUMN age INTEGER;
C.UPDATE TABLE people ADD age INTEGER;
D.ALTER people ADD age INTEGER;
Correct Answer: ALTER TABLE people ADD COLUMN age INTEGER;
Explanation:
The correct syntax is ALTER TABLE table_name ADD COLUMN column_name data_type.
Incorrect! Try again.
35What does the psql command '\d table_name' do?
A.Downloads the table
B.Describes the table structure
C.Deletes the table
D.Duplicates the table
Correct Answer: Describes the table structure
Explanation:
\d stands for describe; it shows columns, types, and modifiers of the specified table.
Incorrect! Try again.
36Which logical operator is used to display a record if any of the conditions separated by it are true?
A.AND
B.XOR
C.NOT
D.OR
Correct Answer: OR
Explanation:
The OR operator displays a record if any of the conditions listed are true.
Incorrect! Try again.
37How do you check for a NULL value in a WHERE clause?
A.IS NULL
B.== NULL
C.EQUALS NULL
D.= NULL
Correct Answer: IS NULL
Explanation:
You cannot compare NULL with '=', you must use 'IS NULL' or 'IS NOT NULL'.
Incorrect! Try again.
38Which PostgreSQL tool is a popular open-source GUI for database management?
A.Workbench
B.phpMyAdmin
C.Compass
D.pgAdmin
Correct Answer: pgAdmin
Explanation:
pgAdmin is the most popular and feature-rich Open Source administration and development platform for PostgreSQL.
Incorrect! Try again.
39What does the PRIMARY KEY constraint imply?
A.NOT NULL only
B.Both UNIQUE and NOT NULL
C.UNIQUE only
D.Just an index
Correct Answer: Both UNIQUE and NOT NULL
Explanation:
A Primary Key uniquely identifies each record, so it cannot contain duplicates (UNIQUE) and cannot contain NULLs.
Incorrect! Try again.
40Which command allows you to quit the psql terminal?
A.logout
B.exit
C.quit
D.\q
Correct Answer: \q
Explanation:
\q is the specific command in psql to quit/exit the interactive shell.
Incorrect! Try again.
41Which clause is used to filter groups of rows created by GROUP BY?
A.LIMIT
B.WHERE
C.FILTER
D.HAVING
Correct Answer: HAVING
Explanation:
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
Incorrect! Try again.
42Which statement is used to delete an entire database?
A.TRUNCATE DATABASE name;
B.DROP DATABASE name;
C.REMOVE DATABASE name;
D.DELETE DATABASE name;
Correct Answer: DROP DATABASE name;
Explanation:
DROP DATABASE is the command to permanently remove a database and all its contents.
Incorrect! Try again.
43What is the purpose of the 'IN' operator?
A.To check for NULL
B.To search text patterns
C.To specify a range
D.To specify multiple possible values for a column
Correct Answer: To specify multiple possible values for a column
Explanation:
The IN operator allows you to specify multiple values in a WHERE clause (shorthand for multiple OR conditions).
Incorrect! Try again.
44Which data type would be most appropriate for a True/False value?
A.VARCHAR(1)
B.BOOLEAN
C.INT
D.BINARY
Correct Answer: BOOLEAN
Explanation:
PostgreSQL supports a native BOOLEAN data type for storing true, false, or null states.
Incorrect! Try again.
45What does the BETWEEN operator select?
A.Values within a given range
B.Values that are null
C.Values that match a list
D.Values that are distinct
Correct Answer: Values within a given range
Explanation:
The BETWEEN operator selects values within a given range strictly. The values can be numbers, text, or dates.
Incorrect! Try again.
46To create a foreign key, which table is modified?
A.The child table
B.The parent table
C.Both tables
D.The system table
Correct Answer: The child table
Explanation:
The Foreign Key constraint is defined on the child table to reference the Primary Key in the parent table.
Incorrect! Try again.
47Which command is used to remove a specific column from a table?
A.ALTER TABLE ... REMOVE COLUMN ...
B.ALTER TABLE ... DELETE COLUMN ...
C.DROP COLUMN ... FROM ...
D.ALTER TABLE ... DROP COLUMN ...
Correct Answer: ALTER TABLE ... DROP COLUMN ...
Explanation:
The correct syntax to delete a column is using ALTER TABLE combined with DROP COLUMN.
Incorrect! Try again.
48Which basic SQL command is NOT part of CRUD operations?
A.SELECT
B.CREATE
C.UPDATE
D.INSERT
Correct Answer: CREATE
Explanation:
CRUD stands for Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE). CREATE is a DDL (Data Definition Language) command, not a DML (Data Manipulation) operation representing the 'C' in CRUD data manipulation.
Incorrect! Try again.
49What is the default sort order if ASC or DESC is not specified in ORDER BY?
A.Random
B.Descending
C.Insertion Order
D.Ascending
Correct Answer: Ascending
Explanation:
By default, SQL sorts results in Ascending (ASC) order.
Incorrect! Try again.
50Which character is used to denote a parameter/variable placeholder in a prepared statement in psql (e.g., $1, $2)?
A.$
B.?
C.@
D.:
Correct Answer: $
Explanation:
PostgreSQL uses positional parameters indicated by 1, $2) in prepared statements/queries.