CLASS 11 Data Query Language (DQL) in SQL: SELECT Queries

Data Query Language (DQL) in SQL: SELECT Queries

The SELECT statement is the primary means of querying data from a database in SQL. It allows you to specify which columns of data you want to retrieve, from which tables, and under what conditions. Here's a breakdown of some of the components and operators used in SELECT queries:

1. SELECT and FROM

• SELECT: Specifies the columns to be retrieved from the database.
• FROM: Specifies the table from which to retrieve the data.

Basic Syntax:

sql
SELECT column1, column2, ...
FROM table_name;

Example:

sql
SELECT first_name, last_name
FROM students;

This query retrieves the first_name and last_name columns from the students table.

2. WHERE with Relational Operators

The WHERE clause is used to filter records based on specific conditions. Relational operators are commonly used in the WHERE clause to define these conditions.

Relational Operators:

• =: Equals
• != or <>: Not equal
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to

Syntax:

sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

sql
SELECT first_name, last_name
FROM students
WHERE grade_level >= 10;

This query retrieves the first_name and last_name of students who are in grade 10 or higher.

3. BETWEEN

The BETWEEN operator is used to filter the result set within a certain range. It is inclusive, meaning the values at both ends of the range are included.

Syntax:

sql
SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example:

sql
SELECT first_name, last_name
FROM students
WHERE grade_level BETWEEN 9 AND 12;

This query retrieves the first_name and last_name of students who are in grades 9 through 12.

4. Logical Operators: AND, OR, NOT

Logical operators are used to combine multiple conditions in the WHERE clause.

• AND: Combines multiple conditions; all conditions must be true.
• OR: Combines multiple conditions; at least one condition must be true.
• NOT: Reverses the result of a condition.

Syntax:

sql
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

Example:

sql
SELECT first_name, last_name
FROM students
WHERE grade_level >= 10 AND last_name = 'Smith';

This query retrieves the first_name and last_name of students who are in grade 10 or higher and have the last name 'Smith'.

Using OR:

sql
SELECT first_name, last_name
FROM students
WHERE grade_level = 10 OR grade_level = 12;

This query retrieves the first_name and last_name of students who are in either grade 10 or grade 12.

Using NOT:

sql
SELECT first_name, last_name
FROM students
WHERE NOT grade_level = 9;

This query retrieves the first_name and last_name of students who are not in grade 9.

5. IS NULL and IS NOT NULL

The IS NULL and IS NOT NULL operators are used to filter records with or without null (missing) values.

Syntax:

sql
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

Example of IS NULL:

sql
SELECT first_name, last_name
FROM students
WHERE email IS NULL;

This query retrieves the first_name and last_name of students who do not have an email address (i.e., their email column is NULL).

Example of IS NOT NULL:

sql
SELECT first_name, last_name
FROM students
WHERE email IS NOT NULL;

This query retrieves the first_name and last_name of students who have an email address (i.e., their email column is not NULL).