PostgreSQL

Question 1

What is PostgreSQL also commonly referred to as?
a) PostSQL
b) Postgres
c) PostRelational
d) PGSQL

Answer 1

b) Postgres

Question 2

What is the primary function of a database schema?
a) To store the entire database data
b) To define constraints for organizing and storing data
c) To run queries efficiently
d) To manage user permissions

Answer 2

b) To define constraints for organizing and storing data

Question 3

What is the purpose of the id column in the university table?
a) To store the university’s name
b) To serve as the primary key
c) To manage foreign key relationships
d) To store timestamp data

Answer 3

b) To serve as the primary key

Question 4

Which of the following is an example of a surrogate key?
a) A university name
b) A uniquely generated numeric ID
c) A natural column like ‘email’
d) A timestamp value

Answer 4

b) A uniquely generated numeric ID

Question 5

What type of relationship exists between the nasa_group table and the astronauts table?
a) One-to-many
b) Many-to-one
c) Many-to-many
d) One-to-one

Answer 5

b) Many-to-one

Question 6

Which SQL statement is used to insert data into a table?
a) ADD
b) INSERT
c) UPDATE
d) INCLUDE

Answer 6

b) INSERT

Question 7

What does the FOREIGN KEY constraint ensure in a relational database?
a) It prevents duplicate data entries
b) It ensures data integrity by referencing another table’s column
c) It acts as a unique identifier for a table
d) It improves query performance

Answer 7

b) It ensures data integrity by referencing another table’s column

Question 8

In the missions table, what is the purpose of the start_date and end_date columns?
a) To track mission durations using the timestamp data type
b) To count the number of missions
c) To define relationships between tables
d) To store integer values of mission durations

Answer 8

a) To track mission durations using the timestamp data type

Question 9

What is the primary purpose of the astronaut_missions table?
a) To store duplicate mission data
b) To create a bridge table for many-to-many relationships
c) To hold all data about astronauts
d) To store primary keys for both tables

Answer 9

b) To create a bridge table for many-to-many relationships

Question 10

What is the first normal form (1NF) in database normalization?
a) Eliminating redundant data
b) Eliminating partial dependencies
c) Ensuring schema is in 3NF
d) Adding foreign key constraints

Answer 10

a) Eliminating redundant data

Question 11

What is one benefit of storing university data in its own table?
a) It increases the size of the database
b) It reduces redundancy and errors
c) It simplifies the astronaut data
d) It creates duplicate data

Answer 11

b) It reduces redundancy and errors

Question 12

Which SQL keyword is used to combine rows from two or more tables based on a related column?
a) SELECT
b) JOIN
c) UNION
d) INTERSECT

Answer 12

b) JOIN

Question 13

What is the result of a SELECT query without a WHERE clause on a large database?
a) Improved query performance
b) Unrestricted retrieval of all data
c) Data filtering based on conditions
d) Reduced query execution time

Answer 13

b) Unrestricted retrieval of all data

Question 14

Why is it important to use a WHERE clause in SQL queries?
a) To improve JOIN syntax
b) To specify the output table
c) To restrict the amount of data returned
d) To eliminate foreign keys

Answer 14

c) To restrict the amount of data returned

Question 15

In SQL, what does the LIMIT clause do?
a) Limits the number of database connections
b) Restricts the number of rows returned in a result set
c) Filters rows based on conditions
d) Specifies the columns to be displayed

Answer 15

b) Restricts the number of rows returned in a result set

Question 16

What is the function of an alias in SQL queries?
a) To delete duplicate rows
b) To rename tables or columns for easier referencing
c) To enforce primary key constraints
d) To modify the database schema

Answer 16

b) To rename tables or columns for easier referencing

Question 17

What kind of key is used in the astronauts table for name?
a) Surrogate key
b) Natural key
c) Foreign key
d) Composite key

Answer 17

b) Natural key

Question 18

What would happen if a row in the astronauts table references a non-existent university_id in the university table?
a) The row would be added without error
b) An error would occur due to foreign key constraints
c) The data would be ignored
d) The university_id would be updated automatically

Answer 18

b) An error would occur due to foreign key constraints

Question 19

What does the INNER JOIN keyword do in a SQL query?
a) Returns all rows from both tables
b) Returns rows where there is a match in both tables
c) Returns only unique rows from a table
d) Returns rows from one table only

Answer 19

b) Returns rows where there is a match in both tables

Question 20

Which column type is best for storing precise date and time information?
a) VARCHAR
b) TIMESTAMP
c) INT
d) DATE

Answer 20

b) TIMESTAMP

Question 21

What is the process of creating a database schema called?
a) Data aggregation
b) Normalization
c) Denormalization
d) Data analysis

Answer 21

b) Normalization

Question 22

In database terms, what is a ‘transitive dependency’?
a) A column depending on a non-key column
b) A column depending on a primary key
c) A foreign key constraint violation
d) An example of a surrogate key

Answer 22

a) A column depending on a non-key column

Question 23

What is one drawback of having a natural key as the primary key?
a) It eliminates the need for JOINs
b) It may require updating in multiple tables if the value changes
c) It adds complexity to surrogate key creation
d) It simplifies query optimization

Answer 23

b) It may require updating in multiple tables if the value changes

Question 24

Why might database administrators denormalize a schema?
a) To save disk space
b) To eliminate JOIN operations and improve performance
c) To enforce all foreign key constraints
d) To achieve third normal form (3NF)

Answer 24

b) To eliminate JOIN operations and improve performance

Question 25

What SQL statement would you use to view all rows and columns of the `university` table? a) SELECT * FROM university; b) DISPLAY university; c) SHOW university; d) VIEW university;

Answer 25

a) SELECT * FROM university;

Question 26

What is the purpose of the PostgreSQL dependency added to the pom.xml file? a) To enable connection pooling in Java b) To allow Maven to access the PostgreSQL JDBC driver c) To provide an ORM framework for PostgreSQL d) To define the schema of the database

Answer 26

b) To allow Maven to access the PostgreSQL JDBC driver

Question 27

What is the main role of the `PostgresConn` class? a) To manage user authentication b) To handle connections to the PostgreSQL database c) To define the database schema d) To execute queries directly

Answer 27

b) To handle connections to the PostgreSQL database

Question 28

In the `PostgresConn` class, which method is used to establish a connection to the database? a) connectToDatabase b) connectToPostgres c) initializeConnection d) establishDatabaseConnection

Answer 28

b) connectToPostgres

Question 29

What is the primary responsibility of the `AstronautPostgresDAL` class? a) To manage the user interface for the application b) To provide a data access layer for interacting with the database c) To initialize the database schema d) To configure environment variables for the database

Answer 29

b) To provide a data access layer for interacting with the database

Question 30

Which of the following is used to ensure a secure database query in the `AstronautPostgresDAL` class? a) Prepared statements b) Raw SQL queries c) String concatenation d) Hardcoded queries

Answer 30

a) Prepared statements

Question 31

What is a key advantage of using a data access layer (DAL)? a) It improves the graphical interface of the application b) It abstracts database operations from business logic c) It eliminates the need for database connections d) It generates random queries for the database

Answer 31

b) It abstracts database operations from business logic

Question 32

In the `PostgresConn` class, what does the `closePostgresConnection` method do? a) Closes the Java application b) Releases the database connection back to the connection pool c) Resets the database schema d) Deletes the connection object

Answer 32

b) Releases the database connection back to the connection pool

Question 33

Which library is imported to handle SQL exceptions in the code? a) java.util.Exception b) java.sql.SQLException c) java.io.IOException d) java.lang.RuntimeException

Answer 33

b) java.sql.SQLException

Question 34

In the `AstronautPostgresDAL` class, what type of object does the `getGeminiRoster` method return? a) An ArrayList of integers b) A List of strings c) A HashMap of key-value pairs d) A single string

Answer 34

b) A List of strings

Question 35

What does the `getGeminiRoster` method use to limit the number of rows returned by the query? a) A WHERE clause b) A LIMIT clause c) An ORDER BY clause d) A GROUP BY clause

Answer 35

b) A LIMIT clause

Question 36

How does the `GeminiAstronautsRDBMS` class obtain database credentials? a) From a configuration file b) From Java constants c) From environment variables d) From the main method arguments

Answer 36

c) From environment variables

Question 37

What is the result of running the `GeminiAstronautsRDBMS` code without defining environment variables? a) The application retrieves default credentials b) The application fails with a NullPointerException c) The database connection is automatically established d) An SQL injection attack occurs

Answer 37

b) The application fails with a NullPointerException

Question 38

What is the role of the `missionStatement` in the `getMissionAstronauts` method? a) To define a mission object in Java b) To prepare and execute a parameterized SQL query c) To perform error handling for SQL queries d) To generate a random mission name

Answer 38

b) To prepare and execute a parameterized SQL query

Question 39

What does the `getMissionAstronauts` method use to ensure unique identifiers in the query result set? a) INNER JOIN clauses b) The AS keyword to create column aliases c) GROUP BY clauses d) DISTINCT keywords

Answer 39

b) The AS keyword to create column aliases

Question 40

In the `GeminiAstronautsRDBMS` class, what is the purpose of using a `Set` for random missions? a) To store duplicate mission numbers b) To prevent duplicate mission numbers c) To sort mission numbers in ascending order d) To map missions to astronaut names

Answer 40

b) To prevent duplicate mission numbers

Question 41

What is the main function of the `getMissionAstronauts` method? a) To generate a random set of astronaut missions b) To query the database for astronaut and mission details c) To initialize the database schema for missions d) To update the mission status in the database

Answer 41

b) To query the database for astronaut and mission details

Question 42

Which method is used to bind a parameter value to a prepared statement in Java? a) setParameterValue b) bindValue c) setString d) setQueryParameter

Answer 42

c) setString

Question 43

What does the code `postgres.getConn().createStatement()` do in the `getGeminiRoster` method? a) Initializes the Postgres connection b) Executes a database update c) Creates a SQL statement object for querying d) Closes the database connection

Answer 43

c) Creates a SQL statement object for querying

Question 44

What happens if duplicate random numbers are generated for missions in the `GeminiAstronautsRDBMS` class? a) The duplicates are added to the `Set` b) The duplicates overwrite existing values c) The duplicates are ignored by the `Set` d) The application throws an exception

Answer 44

c) The duplicates are ignored by the `Set`

Question 45

Why is it important to close database connections after use? a) To speed up query execution b) To free up resources in the database connection pool c) To reset the database schema d) To prevent SQL injection attacks

Answer 45

b) To free up resources in the database connection pool

Question 46

What is the role of the `Random` object in the `GeminiAstronautsRDBMS` class? a) To generate database queries b) To simulate astronaut data c) To generate random mission numbers d) To handle exceptions

Answer 46

c) To generate random mission numbers

Question 47

What is the expected output of the `GeminiAstronautsRDBMS` program if all configurations are correct? a) A list of random mission names b) An error message about missing credentials c) The full roster of Gemini astronauts and mission details d) A list of missions without astronaut names

Answer 47

c) The full roster of Gemini astronauts and mission details

Question 48

What is the purpose of the `StringBuilder` object in the `GeminiAstronautsRDBMS` class? a) To parse mission data from the database b) To concatenate strings for mission names c) To store a list of astronaut names d) To handle SQL exceptions

Answer 48

b) To concatenate strings for mission names

Question 49

Which database table handles the many-to-many relationship between astronauts and missions? a) astronauts b) missions c) astronaut_missions d) mission_details

Answer 49

c) astronaut_missions

Question 50

What happens when the `getMissionAstronauts` method encounters an SQL error? a) It logs the error and rethrows it b) It returns an empty list and prints the error stack trace c) It terminates the application d) It retries the query automatically

Answer 50

b) It returns an empty list and prints the error stack trace