Basic SQL Interview Questions

What is SQL?

What are the different types of SQL statements?
  DDL, DML, DCL, TCL
What is the difference between WHERE and HAVING?
What is a primary key?
What is a foreign key?
What is the difference between INNER JOIN and LEFT JOIN?
What is the difference between DELETE, TRUNCATE, and DROP?
What is normalization? Explain its types.
What is denormalization?
What are constraints in SQL?
    NOT NULL, UNIQUE, CHECK, etc.

Intermediate SQL Interview Questions

What is a subquery? How is it different from a JOIN?
What are indexes? What types are there?
What are aggregate functions in SQL?
SUM(), AVG(), COUNT(), etc.
Explain GROUP BY and ORDER BY.
What is the difference between UNION and UNION ALL?
What are views? How are they useful?
What is a stored procedure?
What is a trigger in SQL?
Explain window functions with examples.
What is the difference between correlated and non-correlated subqueries?

Advanced SQL Interview Questions

How do you optimize SQL queries for performance?
What is a CTE (Common Table Expression)? How is it used?
Explain indexing strategies.
How does SQL handle transactions?
ACID properties
What is a deadlock in SQL? How do you resolve it?
What are recursive queries? Give an example.
Explain RANK(), DENSE_RANK(), and ROW_NUMBER().
What is the difference between EXISTS and IN?
What does EXPLAIN PLAN do?
What is the difference between horizontal and vertical partitioning?

Practical Scenario-Based Questions

Write a SQL query to find the second highest salary.
Write a query to find duplicate records in a table.
Write a query to transpose rows to columns.
How do you update one table based on data from another table?
How do you retrieve the Nth highest value from a column?

What is SQL?

SQL (Structured Query Language) is a standard programming language used to communicate with and manage data in relational database management systems (RDBMS) like MySQL, PostgreSQL, Microsoft SQL Server, and Oracle.

What SQL is used for:

SQL allows you to:

• Query data from a database (SELECT)

• Insert new records (INSERT)

• Update existing records (UPDATE)

• Delete records (DELETE)

• Create and modify database structures like tables and indexes (CREATE, ALTER, DROP)

• Control access to data (GRANT, REVOKE)

What are the different types of SQL statements?

SQL statements are categorized into five main types, each serving a specific purpose in managing and manipulating relational databases:

1. Data Definition Language (DDL)

These statements define the structure of the database objects like tables, indexes, and views.

Statements      Purpose

CREATE        Creates a new table, view, or database

ALTER           Modifies an existing object

DROP            Deletes an object

TRUNCATE   Removes all records from a table

RENAME      Renames a database object

2. Data Manipulation Language (DML)

These statements are used to manipulate data within tables.

Statements      Purpose

SELECT       Retrieves data

INSERT           Adds new records

UPDATE         Modifies existing records

DELETE         Removes records

3. Data Control Language (DCL)

These statements manage access to the data in the database.

Statements      Purpose

GRANT         Gives user access privileges

REVOKE       Withdraws access privileges

4. Transaction Control Language (TCL)

These statements manage changes made by DML statements and control transaction behavior.

Statements      Purpose

COMMIT       Saves all changes made in the transaction

ROOLBACK   Undoes changes if something goes wrong

SAVEPOINT    Sets a point to roll back to later

SET TRANSACTION Defines transaction properties

5. Data Query Language (DQL)

Some categorize SELECT separately as DQL since it's used strictly for querying (not changing) data.

Statements      Purpose

SELECT         Retrieves data from tables

What is the difference between WHERE and HAVING?

The main difference between WHERE and HAVING in SQL is when and how they are used to filter data.

WHERE

• Filters rows before any grouping or aggregation.

• Used with SELECT, UPDATE, DELETE statements.

• Cannot be used with aggregate functions (like SUM(), AVG(), etc.)

Example:

sql

SELECT * FROM employees
WHERE department = 'Sales';

Filters individual rows where department = 'Sales'.

HAVING

• Filters groups after aggregation (GROUP BY).

• Used only with GROUP BY or aggregate functions.

• Can use aggregate functions like COUNT(), SUM().

Example:

sql

SELECT department, COUNT(*) as total
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Filters departments with more than 5 employees (after grouping).

Summary Table:
Feature
WHERE
HAVING
Filters
   Before grouping

After grouping
Used with
   All types of queries
Only with GROUP BY / aggregates
Aggregates
   Cannot use aggregate functions    
Can use aggregate functions
Execution
   Row-level filtering
Group-level filtering

What is a primary key?

A primary key is a column or set of columns in a database table that uniquely 
identifies each row in that table.

Key Characteristics of a Primary Key:

1. Uniqueness: Each value must be unique across the table.

2. Not Null: A primary key column cannot contain NULL values.

3. Only one per table: A table can have only one primary key, but it can consist of multiple columns (called a composite key).

Example:

sql

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

Here, student_id is the primary key, ensuring every student has a unique ID.

Composite Primary Key Example:
sql

CREATE TABLE course_enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

This ensures that each student can enroll in a course only once — the 
combination must be unique.
Why Primary Keys Are Important:
They prevent duplicate records.
They establish relationships between tables (e.g., foreign keys).
They improve data integrity and query performance.

What is a foreign key?

A foreign key is a column (or set of columns) in one table that links to the primary key 
in another table. It is used to establish a relationship between two tables in a 
relational database.

Key Characteristics of a Foreign Key:
Maintains referential integrity between related tables.
The values in a foreign key column must match values in 
the referenced primary key column.
A foreign key can contain duplicate values and NULLs (unless specified otherwise).
Example:
SQL

-- Parent table
CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(100)
);

-- Child table with foreign key
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

In this example:
departments.dept_id is the primary key.
employees.dept_id is a foreign key pointing to departments.dept_id.
Why Use Foreign Keys?
To link related data across tables.
To ensure data consistency 
(e.g., you can't assign an employee to a non-existent department).
To help define relationships in normalized databases 
(e.g., one-to-many, many-to-many).

What is the difference between INNER JOIN and LEFT JOIN?

Use an inner join when you want to retrieve only the rows where there is a match in both tables based on the join condition. Use a left join when you want to retrieve all rows from the left table and the matched rows from the right table, with NULL values for non-matching rows.

INNER JOIN 
  Returns only matching rows from both tables. 
 If there's no match, the row is excluded from the result.

LEFT JOIN (or LEFT OUTER JOIN) 
Returns all rows from the left table and matched rows from the right table. 
 If there's no match, columns from the right table return NULL.

What is the difference between DELETE, TRUNCATE, and DROP?

The DROP command in SQL removes the table from the database, the DELETE command removes one or more records from the table, and the TRUNCATE command removes all the rows from the existing table.

1. DELETE 
 Removes specific rows from a table using a WHERE clause.
 Can be rolled back if used within a transaction. 
 Triggers (if defined) will fire. 
 Table structure remains intact.

SQL
DELETE FROM employees WHERE dept_id = 5;

 2. TRUNCATE 
 Removes all rows from a table very quickly. 
 Cannot be rolled back in many databases (especially outside of transactions).
 Triggers do not fire. 
 Table structure and schema stay intact. 
 Resets auto-increment counters (in most RDBMS).

SQL
TRUNCATE TABLE employees;

 3. DROP 
 Completely deletes the table from the database. 
 Removes data, structure, constraints, indexes, and triggers. 
 Cannot be rolled back. 
 Table is no longer usable unless recreated.

SQL 
DROP TABLE employees;

What is normalization? Explain its types.

How does the garbage collector work in PHP?

 In PHP, the Garbage Collector (GC) is responsible for automatically manages memory, freeing up resources that are no longer in use by your scripts, preventing memory leaks. PHP primarily uses reference counting, but it also has a cycle collector to deal with more complex scenarios like circular references.

Freeing objects that are no longer needed prevents memory leaks.

The GC uses a counting mechanism to determine the elements to drop. If no references point to a particular object (i.e., $counter = 0), then this object is eligible for cleanup.

1. Reference Counting

Every PHP variable (zval) holds a refcount—an internal counter that tracks how many symbols (variable names or properties) refer to it.

• Every variable in PHP has a reference count.

• When you assign a variable or pass it to a function, the reference count increases.

• When the reference count drops to zero, PHP frees the memory.

Example:

$a = new stdClass(); // refcount = 1
$b = $a;             // refcount = 2
unset($a);           // refcount = 1
unset($b);           // refcount = 0 → object is destroyed

2.  Circular References Problem

Reference counting alone can't detect circular references — when two or more objects reference each other, even if nothing else does.

Example:

$a = new stdClass();
$b = new stdClass();
$a->ref = $b;
$b->ref = $a;
unset($a);
unset($b);
// Objects still exist because of mutual references → memory leak!

3. Cycle Collector (Since PHP 5.3+)
To solve this, PHP introduced a cycle collector:



It periodically scans memory for circular references.


It finds groups of objects that reference each other but are otherwise unreachable.


It breaks these cycles and frees the memory.
4. Triggering and Controlling GC

You can manually interact with the GC using these functions:
gc_enable();       // Enable garbage collection (default is on)
gc_disable();      // Disable garbage collection
gc_collect_cycles(); // Force collection of cycles
gc_enabled();      // Check if GC is enabled

5. Performance Considerations
GC adds some overhead, but it’s essential for long-running scripts (e.g., daemons, workers).
For short scripts, reference counting alone is usually enough.

Drupal Service for IP Blocking and Whitelisting

 Here's a simple PHP OOP-based class that you can use in a custom Drupal 10 module to implement IP blocking and whitelisting, along with a form to manage IPs.

 1. Define the PHP Class (IPManager.php)

Put this in your module's src/Service/IPManager.php.

PHP

namespace Drupal\your_module\Service;

use Symfony\Component\HttpFoundation\RequestStack;

class IPManager {

  protected $requestStack;

  // Define allowed and blocked IPs for simplicity.
  protected $whitelist = ['127.0.0.1']; // Add your trusted IPs here.
  protected $blacklist = ['192.168.1.10']; // Add blocked IPs here.

  public function __construct(RequestStack $request_stack) {
    $this->requestStack = $request_stack;
  }

  public function getClientIp() {
    return $this->requestStack->getCurrentRequest()->getClientIp();
  }

  public function isWhitelisted(): bool {
    return in_array($this->getClientIp(), $this->whitelist);
  }

  public function isBlacklisted(): bool {
    return in_array($this->getClientIp(), $this->blacklist);
  }

  public function checkAccess(): bool {
    if ($this->isWhitelisted()) {
      return true;
    }
    if ($this->isBlacklisted()) {
      return false;
    }
    return true; // Default allow
  }

}

2. Register the Service (your_module.services.yml)

YAML

services:
  your_module.ip_manager:
    class: Drupal\your_module\Service\IPManager
    arguments: ['@request_stack']

 3. Create a Simple Form to Manage IPs

Put this in src/Form/IPAccessForm.php:

PHP

namespace Drupal\your_module\Form;

use Drupal\Core\Form\FormBase;
use Drupal\Core\Form\FormStateInterface;

class IPAccessForm extends FormBase {

  public function getFormId() {
    return 'ip_access_form';
  }

  public function buildForm(array $form, FormStateInterface $form_state) {
    $form['whitelist'] = [
      '#type' => 'textarea',
      '#title' => $this->t('Whitelisted IPs'),
      '#default_value' => '127.0.0.1',
      '#description' => $this->t('Enter one IP per line.'),
    ];

    $form['blacklist'] = [
      '#type' => 'textarea',
      '#title' => $this->t('Blacklisted IPs'),
      '#default_value' => '192.168.1.10',
      '#description' => $this->t('Enter one IP per line.'),
    ];

    $form['submit'] = [
      '#type' => 'submit',
      '#value' => $this->t('Save IPs'),
    ];

    return $form;
  }

  public function submitForm(array &$form, FormStateInterface $form_state) {
    // For demo purposes, just show a message. Save to config for real use.
    $whitelist = $form_state->getValue('whitelist');
    $blacklist = $form_state->getValue('blacklist');

    \Drupal::messenger()->addStatus($this->t('Saved IP settings.'));
  }
}



4. Route and Menu
your_module.routing.yml:
YAML

your_module.ip_access_form:
  path: '/admin/config/ip-access'
  defaults:
    _form: '\Drupal\your_module\Form\IPAccessForm'
    _title: 'IP Access Settings'
  requirements:
    _permission: 'administer site configuration'


5. Use the IP Check in a Controller or Event Subscriber
Example use in a controller:
PHP

$ipManager = \Drupal::service('your_module.ip_manager');
if (!$ipManager->checkAccess()) {
  throw new \Symfony\Component\HttpKernel\Exception\AccessDeniedHttpException();
}