Introduction to SQL: Master the Basics of Data Manipulation and Retrieval

Question 1

Craft an SQL query that retrieves the names of customers with more than two orders. The results should be grouped by customer name and sorted in descending order based on the total number of orders.

Accepted Answer

SELECT customer_name, COUNT(*) AS order_count FROM orders GROUP BY customer_name HAVING order_count > 2 ORDER BY order_count DESC

Answer

SELECT customer_name, SUM(order_count) AS total_orders FROM orders GROUP BY customer_name ORDER BY total_orders DESC

Answer

SELECT customer_name, order_count FROM orders GROUP BY customer_name ORDER BY customer_name DESC

Answer

SELECT customer_name, order_count FROM orders GROUP BY customer_name HAVING order_count > 2

Question 2

Which SQL statement will increase the 'quantity_on_hand' by 10 for products with 'product_id' greater than 50 in the 'Inventory' table?

Accepted Answer

UPDATE Inventory SET quantity_on_hand = quantity_on_hand + 10 WHERE product_id > 50

Answer

SELECT * FROM Inventory WHERE quantity_on_hand > 50

Answer

INSERT INTO Inventory (product_id, quantity_on_hand) VALUES (51, 10)

Answer

DELETE FROM Inventory WHERE product_id > 50

Question 3

In SQL, which join type displays all records from the left table and only matching records from the right table?

Accepted Answer

LEFT JOIN

Answer

INNER JOIN

Question 4

Create an SQL statement to generate a new table called 'OrderSummary' with three columns: 'order_id' (integer), 'customer_id' (integer), and 'total_amount' (decimal).

Accepted Answer

CREATE TABLE OrderSummary (order_id INTEGER, customer_id INTEGER, total_amount DECIMAL(10,2));

Answer

INSERT INTO OrderSummary (order_id, customer_id, total_amount) VALUES (1, 2, 100.00);

Answer

SELECT * FROM Orders WHERE order_id > 10 INTO OrderSummary;

Question 5

What is the primary function of the 'WHERE' clause in SQL?

Accepted Answer

To filter rows from a table based on specified conditions

Answer

To group the results of a query

Answer

To sort the results of a query

Answer

To create a new table from the results of a query

Question 6

In an SQL table, what is the distinction between a primary key and a foreign key?

Accepted Answer

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

Answer

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

Answer

Both primary keys and foreign keys can reference other tables.

Answer

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

Question 7

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

Accepted Answer

To sort the results of a query in ascending or descending order

Answer

To group the results of a query

Answer

To filter rows from a table based on specified conditions

Answer

To create a new table from the results of a query

Question 8

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

Accepted Answer

Improved performance for data retrieval queries

Answer

Ensuring data integrity

Answer

Establishing relationships between tables

Answer

Reducing the size of the database

Question 9

In SQL, which operator combines multiple conditions in a WHERE clause, allowing rows to be selected if any of the conditions are true?

Accepted Answer

OR

Answer

AND

Answer

XOR

Answer

NOT

Question 10

How do indexes enhance query performance in SQL? Describe the process of creating and maintaining indexes.

Accepted Answer

Indexes provide direct access to rows based on indexed column values, reducing query time.

Accepted Answer

Indexes can be created with 'CREATE INDEX' and managed with 'ALTER INDEX' and 'DROP INDEX' commands.

Answer

Indexes physically reorder data for faster query execution.

Answer

Indexes are automatically managed by the database system.

Question 11

Which SQL command retrieves data from a table?

Accepted Answer

SELECT

Answer

UPDATE

Answer

DELETE

Answer

INSERT

Question 12

Which operator is used for value comparison in SQL queries?

Accepted Answer

=

Answer

+

Answer

*

Answer

/

Question 13

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

Accepted Answer

Uniquely identifying each row in the table

Answer

Storing the most crucial data in the table

Answer

Enhancing data integrity

Answer

Improving query performance

Question 14

Which data type appropriately stores a date and time value?

Accepted Answer

DATE

Answer

INT

Answer

VARCHAR

Answer

CHAR

Question 15

Explain the difference between a WHERE clause and a HAVING clause in an SQL query.

Accepted Answer

A WHERE clause filters rows before grouping, while a HAVING clause filters groups after formation.

Answer

A HAVING clause filters rows before grouping, while a WHERE clause filters groups after formation.

Answer

Both WHERE and HAVING clauses filter rows before grouping.

Answer

Both WHERE and HAVING clauses filter groups after formation.

Question 16

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

Accepted Answer

Accelerating data retrieval

Answer

Enhancing data integrity

Answer

Minimizing storage space

Answer

Improving data security

Question 17

Which SQL command is used to modify data in a table?

Accepted Answer

UPDATE

Answer

INSERT

Answer

DELETE

Answer

SELECT

Question 18

What is the primary purpose of using a transaction in SQL?

Accepted Answer

Ensuring data integrity in the event of system failures

Answer

Enhancing query performance

Answer

Minimizing storage space

Answer

Improving data security

Question 19

Which of the following is NOT a valid data type in SQL?

Accepted Answer

Array

Answer

Integer

Answer

String

Answer

Boolean

Question 20

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

Accepted Answer

SELECT

Answer

UPDATE

Answer

INSERT

Answer

DELETE

Question 21

Which of the following is the correct syntax for creating a table in SQL?

Accepted Answer

CREATE TABLE table_name (column_name data_type, ...);

Answer

CREATE A TABLE table_name (column_name data_type, ...);

Answer

CREATE Table table_name (column_name data_type, ...);

Question 22

Which of the following SQL operators is used for retrieving unique rows from a table?

Accepted Answer

DISTINCT

Answer

SINGLE

Answer

INDIVIDUAL

Answer

UNIQUE

Question 23

Which of the following SQL commands is used to modify existing data in a table?

Accepted Answer

UPDATE

Answer

EDIT

Answer

MODIFY

Answer

CHANGE

Question 24

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

Accepted Answer

To filter rows based on specified conditions

Answer

To sort rows in ascending order

Answer

To join multiple tables together

Answer

To group rows by common values

Question 25

Which of the following is an example of a popular relational database management system (RDBMS)?

Accepted Answer

MySQL

Answer

CSV file

Answer

Google Sheets

Answer

Excel

Question 26

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

Accepted Answer

A primary key uniquely identifies each row, while a foreign key establishes a relationship with another table.

Answer

A primary key can be null, while a foreign key cannot.

Answer

A primary key is used for sorting data, while a foreign key is used for filtering.

Question 27

Which of the following SQL functions returns the maximum value in a set of rows?

Accepted Answer

MAX()

Answer

SUM()

Answer

MIN()

Answer

AVG()

Question 28

What is the primary benefit of using an index in an SQL table?

Accepted Answer

To improve query performance by organizing data for faster access

Answer

To prevent duplicate rows from being inserted

Answer

To encrypt the data in the table

Question 29

What is the purpose of the JOIN keyword in an SQL statement?

Accepted Answer

To combine rows from two or more tables based on related columns

Answer

To delete rows from a table

Answer

To create a new table from existing tables