Question 1

is used to combine rows from two or more tables based on a related column between them (usually a primary key and a foreign key).

Accepted Answer

SQL JOIN

Question 2

Why Do We Use JOIN?

Accepted Answer

In a relational database

Question 3

is stored in multiple tables

Accepted Answer

Data

Question 4

are connected using keys.

Accepted Answer

Tables

Question 5

allows us to retrieve related data in a single query.

Accepted Answer

JOIN

Question 6

Types of SQL JOIN

Accepted Answer

Types of SQL JOIN

Question 7

Matching rows only

Accepted Answer

INNER JOIN

Question 8

All left + matching right

Accepted Answer

LEFT JOIN

Question 9

All right + matching left

Accepted Answer

RIGHT JOIN

Question 10

All rows from both tables

Accepted Answer

FULL JOIN

Question 11

All possible row combinations

Accepted Answer

CROSS JOIN

Question 12

Basic Structure of JOIN:

Accepted Answer

SELECT column 1, column,2 
FROM table1
JOIN table2
ON table1.column = table2.column;

Question 13

Returns only the rows where there is a match in both tables based on a related column.

Accepted Answer

INNER JOIN

Question 14

SELECT
 a. account_number,
 s.firstName,
 s.lastName,
 s.course,
 a.active
FROM Accounts a
INNER JOIN students s
             ON a.student_id = s.id;

Accepted Answer

INNER JOIN

Question 15

Returns all rows from the left table
Returns matching rows from the right table
f there is no match → NULL values are shown

Accepted Answer

LEFT JOIN

Question 16

Returns all rows from the right table
• Returns matching rows from the left table
• If there is no match → NULL values appear in left table columns

Accepted Answer

RIGHT JOIN

Question 17

Returns all matching rows
• Returns all unmatched rows from the left table
• Returns all unmatched rows from the right table
• Displays NULL when no match exists
“FULL JOIN = LEFT JOIN + RIGHT JOIN combined

Accepted Answer

FULL JOIN (FULL OUTER JOIN)

Question 18

is a SELECT statement that is written inside another SQL statement. It is also called _____ AND _____

Accepted Answer

Subquery -  inner query - Nested query

Question 19

Types of Subqueries

Accepted Answer

Types of Subqueries

Question 20

returns a single column and a single row, that is, a single value.

Accepted Answer

Scalar subquery

Question 21

returns multiple columns, but only a single row

Accepted Answer

Row subquery

Question 22

returns one or more columns and multiple rows.

Accepted Answer

Table subquery

Question 23

is a database object that improves the speed of data retrieval operations on a table.

Accepted Answer

Index

Question 24

• Instead of scanning the whole table.
• The database quickly locates the data.

Accepted Answer

Index

Question 25

The database checks every row (Full Table Scan)

Accepted Answer

Without index:

Question 26

The database directly jumps to the correct location.

Accepted Answer

With index:

Question 27

automatically have indexes

Accepted Answer

Primary Keys

Question 28

INSERT, UPDATE, DELETE

Accepted Answer

Too many indexes can slow down

Question 29

determines the physical order of data in a table.
The table rows are stored in sorted order based on the indexed column.

Accepted Answer

Clustered Index

Question 30

Only one clustered index per table
• Physically sorts the table
• Usually created on the primary key
• Fast for range queries

Accepted Answer

Key Characteristics of Clustered Index

Question 31

creates a separate structure that stores indexed column values and pointers to actual table rows.

Accepted Answer

Non-Clustered Index

Question 32

is a virtual table based on the result of a
SELECT query

Accepted Answer

View

Question 33

It does not store data physically.
• Instead, it stores the query definition.

Accepted Answer

View

Question 34

When you query a View, the database executes the
stored SELECT statement

Accepted Answer

View

DATABASE - 2

Advanced SQL

"Know" box contains:
Time elapsed:
Retries: