Master Database Queries and SQL for Biology Research

Question 1

Which SQL statement retrieves specific rows from a table based on a given condition?

Accepted Answer

SELECT column_name(s) FROM table_name WHERE condition;

Answer

INSERT INTO table_name (column_list) VALUES (value_list);

Answer

UPDATE table_name SET column_name = value WHERE condition;

Question 2

Select the SQL command used to retrieve specific rows from a table based on specified conditions:

Accepted Answer

SELECT

Answer

UPDATE

Answer

DELETE

Answer

INSERT

Question 3

Which SQL statement is specifically designed to add new records to a table?

Accepted Answer

INSERT

Answer

SELECT

Answer

UPDATE

Answer

DELETE

Question 4

In SQL, which operator is used to check if two values are equal?

Accepted Answer

=

Answer

<

Answer

>

Answer

!=

Question 5

What is the primary purpose of a primary key in a database?

Accepted Answer

To maintain the integrity and uniqueness of data

Answer

To increase the speed of data retrieval

Answer

To enforce relationships between tables

Answer

To store additional information about records

Question 6

Which SQL function is specifically designed to find the maximum value within a set of data?

Accepted Answer

MAX()

Answer

MIN()

Answer

AVG()

Answer

SUM()

Question 7

Explain the difference between an INNER JOIN and an OUTER JOIN in SQL:

Accepted Answer

INNER JOIN retrieves matching rows, while OUTER JOIN retrieves all rows

Answer

INNER JOIN retrieves all rows, while OUTER JOIN retrieves matching rows

Answer

INNER JOIN is faster than OUTER JOIN

Answer

OUTER JOIN is faster than INNER JOIN

Question 8

Which SQL statement is used to modify existing records in a table?

Accepted Answer

UPDATE

Answer

SELECT

Answer

DELETE

Answer

INSERT

Question 9

What is the primary purpose of an index in a database?

Accepted Answer

To improve the speed and efficiency of data retrieval

Answer

To maintain data integrity

Answer

To enhance data security

Answer

To store duplicate data

Question 10

Which SQL command is specifically used to delete rows from a table?

Accepted Answer

DELETE

Answer

SELECT

Answer

UPDATE

Answer

INSERT

Question 11

What is the primary purpose of a foreign key in a database?

Accepted Answer

To enforce referential integrity between tables

Answer

To improve the performance of data retrieval

Answer

To store additional information about records

Answer

To create a unique index

Question 12

In SQL, which command is used to retrieve all records from a table?

Accepted Answer

SELECT *

Answer

SELECT ALL

Answer

GET ALL

Question 13

Consider the following table:

| id | name | age |
|---|---|---| 
| 1 | John | 20 | 
| 2 | Mary | 25 | 
| 3 | Bob | 30 |

Which SQL query would retrieve the name of the student with id 2?

Accepted Answer

SELECT name FROM Students WHERE id = 2

Answer

SELECT * FROM Students WHERE id EQ 2

Answer

GET name FROM Students WHERE id = 2

Question 14

What is the purpose of the 'ORDER BY' clause in an SQL query?

Accepted Answer

To sort the results in ascending or descending order

Answer

To group the results by a specific column

Answer

To filter the results based on a specific condition

Question 15

Which SQL function is used to calculate the average value of a column?

Accepted Answer

AVG()

Answer

MEAN()

Answer

SUM()

Question 16

What is the key difference between an INNER JOIN and an OUTER JOIN in SQL?

Accepted Answer

INNER JOIN returns only matching rows from both tables, while OUTER JOIN returns all rows from one table and matching rows from the other.

Answer

OUTER JOIN returns only matching rows from both tables, while INNER JOIN returns all rows from one table and matching rows from the other.

Answer

INNER JOIN and OUTER JOIN return the same results.

Question 17

Which SQL statement is used to modify an existing record in a database table?

Accepted Answer

UPDATE

Answer

MODIFY

Answer

CHANGE

Question 18

What is the purpose of using indexes in a SQL database?

Accepted Answer

To enhance query performance by quickly locating data

Answer

To ensure data integrity by enforcing unique values

Answer

To reduce the overall size of the database

Question 19

Which of the following SQL data types is commonly used to store dates and times?

Accepted Answer

DATETIME

Answer

VARCHAR

Answer

INT

Question 20

In SQL, which operator combines multiple conditions to retrieve records that satisfy all the specified criteria?

Accepted Answer

AND

Answer

OR

Answer

NOT

Answer

XOR

Question 21

Which of the following is the correct syntax to create a table named 'Students' with three columns: 'ID', 'name', and 'age'?

Accepted Answer

CREATE TABLE Students (ID INT, name VARCHAR(255), age INT)

Answer

CREATE TABLE Students (ID VARCHAR(255), name INT, age VARCHAR(255)

Answer

CREATE TABLE Students (name VARCHAR(255), ID INT, age INT)

Answer

CREATE TABLE Students (age INT, ID VARCHAR(255), name VARCHAR(255))

Question 22

Which SQL command is used to retrieve specific columns from a table?

Accepted Answer

SELECT

Answer

INSERT

Answer

UPDATE

Answer

DELETE

Question 23

What is the primary purpose of the WHERE clause in an SQL query?

Accepted Answer

To filter the rows to be returned

Answer

To specify the columns to be retrieved

Answer

To sort the result set

Answer

To group the result set

Question 24

What is the purpose of a primary key in a database table?

Accepted Answer

To uniquely identify each row

Answer

To contain essential information

Answer

To sort the data

Answer

To store the date and time of record creation

Question 25

Which of the following SQL functions is used to calculate the average value of a column?

Accepted Answer

AVG()

Answer

SUM()

Answer

COUNT()

Answer

MIN()

Question 26

What is the primary purpose of a foreign key in a database?

Accepted Answer

To enforce relationships between tables

Answer

To store additional information about a row

Answer

To index a table for faster retrieval

Answer

To create a unique constraint on a column

Question 27

Which data type is not supported by SQL?

Accepted Answer

JSON

Answer

Integer

Answer

String

Answer

Boolean

Question 28

What is the primary purpose of the 'SELECT' statement in SQL?

Accepted Answer

To retrieve data from a table

Answer

To create a new table

Answer

To update data in a table

Answer

To delete data from a table

Question 29

What is the key difference between an INNER JOIN and an OUTER JOIN?

Accepted Answer

An INNER JOIN returns only matching rows, while an OUTER JOIN returns all rows from both tables.

Answer

An OUTER JOIN returns only non-matching rows, while an INNER JOIN returns all rows from both tables.

Answer

An INNER JOIN returns all rows from both tables, while an OUTER JOIN returns only matching rows.

Answer

There is no difference between an INNER JOIN and an OUTER JOIN.

Question 30

What is the purpose of using a subquery in SQL?

Accepted Answer

To embed one query within another query

Answer

To create a new table

Answer

To update data in a table

Answer

To delete data from a table

Question 31

What is the primary responsibility of a database management system (DBMS)?

Accepted Answer

To manage and control access to a database

Answer

To perform data analysis and reporting

Answer

To provide a graphical user interface for database interaction

Answer

To develop and deploy database applications

Question 32

What is the primary purpose of the WHERE clause in an SQL query?

Accepted Answer

To filter rows based on specified conditions

Answer

To specify the table to be queried

Answer

To sort the results

Answer

To limit the number of rows returned

Question 33

What is the primary distinction between a primary key and a foreign key in an SQL database?

Accepted Answer

A primary key uniquely identifies each row in a table, while a foreign key references the primary key of another table

Answer

A foreign key uniquely identifies each row in a table, while a primary key references the foreign key of another table

Answer

There is no fundamental difference between a primary key and a foreign key

Answer

Both primary keys and foreign keys are used to enforce data integrity

Question 34

What is the primary purpose of an index in an SQL database?

Accepted Answer

To enhance query performance by creating a sorted copy of the data

Answer

To guarantee that data is stored in a specific sequence

Answer

To prevent the storage of duplicate data

Answer

To encrypt data for security reasons

Question 35

Which SQL statement is used to establish a new database?

Accepted Answer

CREATE DATABASE

Answer

CREATE TABLE

Answer

INSERT INTO

Answer

SELECT * FROM

Question 36

What is the purpose of a transaction in SQL?

Accepted Answer

To combine a set of database operations into a single unit of work

Answer

To guarantee data consistency at all times

Answer

To enhance database performance

Answer

To protect data from unauthorized access

Question 37

What is the primary purpose of the GROUP BY clause in an SQL query?

Accepted Answer

To group rows that share similar values in one or more columns

Answer

To sort the results based on one or more columns

Answer

To filter rows based on specified conditions

Answer

To restrict the number of rows returned

Question 38

What is the primary purpose of using a NOT NULL constraint in SQL database design?

Accepted Answer

To prevent null values from being inserted into a specific column.

Answer

To enhance database performance by reducing row processing.

Answer

To restrict NOT NULL constraints to numeric data types only.

Answer

To automatically apply NOT NULL constraints to all table columns.

Question 39

Which SQL command is used to retrieve data from a database?

Accepted Answer

SELECT

Answer

INSERT

Answer

UPDATE

Answer

DELETE

Question 40

What is the primary purpose of the 'WHERE' clause in an SQL query?

Accepted Answer

To filter the results based on specified conditions

Answer

To sort the results in ascending or descending order

Answer

To group the results by a specified column

Answer

To join two or more tables together

Question 41

What is the key difference between a 'primary key' and a 'foreign key' in an SQL database?

Accepted Answer

A primary key is unique for each row, while a foreign key references a primary key in another table

Answer

A foreign key is unique for each row, while a primary key references a foreign key in another table

Answer

Both primary keys and foreign keys are unique for each row

Answer

There is no difference between a primary key and a foreign key

Question 42

Which SQL command is used to create a new database?

Accepted Answer

CREATE DATABASE

Answer

CREATE TABLE

Answer

SELECT

Answer

INSERT

Question 43

What is the main purpose of the 'ORDER BY' clause in an SQL query?

Accepted Answer

To sort the results in ascending or descending order

Answer

To filter the results based on specific conditions

Answer

To group the results by a specified column

Answer

To join two or more tables together

Question 44

Which SQL function is used to calculate the average value of a specific column?

Accepted Answer

AVG

Answer

SUM

Answer

COUNT

Answer

MAX

Question 45

What is the purpose of the 'JOIN' statement in SQL?

Accepted Answer

To combine rows from two or more tables based on a common column

Answer

To filter the results based on specific conditions

Answer

To sort the results in ascending or descending order

Answer

To group the results by a specified column

Question 46

Which SQL command is used to update data within a database?

Accepted Answer

UPDATE

Answer

INSERT

Answer

SELECT

Answer

DELETE

Question 47

What is the main benefit of using an 'index' in an SQL database?

Accepted Answer

It helps to improve the performance of queries by providing a faster way to locate data

Answer

It ensures that data values are unique, maintaining data integrity

Answer

It groups data together based on a common column

Answer

It allows you to join two or more tables together

Question 48

Which SQL command is primarily used to retrieve data from a database?

Accepted Answer

SELECT

Answer

INSERT INTO

Answer

DELETE

Answer

UPDATE

Question 49

In an SQL statement, which clause is used to specify conditions for filtering the retrieved data?

Accepted Answer

WHERE

Answer

ORDER BY

Answer

GROUP BY

Answer

ON

Question 50

What is the primary purpose of a primary key in a database table?

Accepted Answer

To uniquely identify each row of data

Answer

To make data sorting more efficient

Answer

To store the most important data

Answer

To reference data in other tables

Question 51

Which SQL keyword is used to combine rows from multiple tables based on a common field?

Accepted Answer

JOIN

Answer

INTERSECT

Answer

UNION

Answer

EXCEPT

Question 52

In SQL, what is the output of an aggregation function like SUM() or COUNT()?

Accepted Answer

A single value representing the result of the aggregation

Answer

A list of all values used in the aggregation

Answer

An error

Answer

A new table containing the aggregated data

Question 53

Which of the following is a significant benefit of using a database management system (DBMS)?

Accepted Answer

Improved data security and integrity

Answer

Slower data retrieval

Answer

Increased data redundancy

Answer

Limited data accessibility

Question 54

What is the primary purpose of a foreign key in a database table?

Accepted Answer

To establish a relationship between the table and another table

Answer

To ensure that data is entered correctly

Answer

To store metadata about the table

Answer

To uniquely identify each row in the table

Question 55

What is the fundamental difference between a logical database model and a physical database model?

Accepted Answer

A logical model defines the data structure, while a physical model describes how the data is physically stored

Answer

A logical model is more detailed than a physical model