Data Query Language

 Data Query Language (DQL)

Introduction to Data Query Language (DQL)

Data Query Language (DQL) constitutes a subset of commands within a Database Management System (DBMS) primarily utilized for retrieving or querying data from databases. DQL commands facilitate fetching, filtering, and extracting specific information based on user-defined criteria.

DQL Commands Overview

DQL encompasses commands like SELECT, the fundamental command for fetching data from database tables.

Basic Components of a SELECT Statement

SELECT

The SELECT clause is a fundamental command in SQL used to retrieve specific attributes or columns from a database table. It specifies the columns that should be included in the result set. For instance:

             SELECT column1, column2 FROM table_name;

This command retrieves column1 and column2 from the specified table_name.

FROM

The FROM clause is used to specify the table or tables from which the data is to be retrieved. It takes the table name as an argument and defines the source of the data for the query. For example:

            SELECT column1, column2 FROM table_name;

Here, table_name represents the table from which the column1 and column2 data will be retrieved.

WHERE

The WHERE clause is used to filter rows based on specific conditions defined within the query. It acts as a filter by specifying conditions that must be met for a row to be included in the result set. For example:

           SELECT column1, column2 FROM table_name WHERE condition;

The condition in the WHERE clause defines the criteria that the rows must satisfy to be included in the result. Only rows that meet the specified condition will be selected and displayed in the output.

These three clauses, SELECT, FROM, and WHERE, are essential components of a basic SQL SELECT statement used to retrieve specific data from a database table based on defined conditions and criteria.

Example Tables:

Consider two sample tables within a database: employees and departments.

Table: employees

emp_id	emp_name	emp_salary	emp_department
1	John	50000	HR
2	Sarah	60000	Sales
3	Emily	55000	Marketing

Table: departments

dept_id	dept_name
1	HR
2	Sales
3	Marketing

Basic SELECT Command:

  Example 1:          SELECT * FROM employees;

This SQL command retrieves all records and columns from the employees table.

SELECT with Specific Columns:

  Example 2:             SELECT emp_name, emp_salary 

                                  FROM employees;

This query fetches only the emp_name and emp_salary columns from the employees table.

emp_name	emp_salary
John	50000
Sarah	60000
Emily	55000

Retrieving Data with Conditions:

  Example 3:     SELECT emp_name, emp_salary 

                          FROM employees 

                          WHERE emp_salary > 55000;

This query retrieves names and salaries of employees earning more than $55,000 per annum.

emp_name	emp_salary
Sarah	60000
Emily	55000

Joining Tables for Information Retrieval:

  Example 4:          SELECT e.emp_name, d.dept_name 

                                FROM employees e 

                                INNER JOIN departments d ON e.emp_department = d.dept_name;

Here, a join operation fetches employee names and their corresponding department names by linking the employees and departments tables.

emp_name	dept_name
John	HR
Sarah	Sales
Emily	Marketing

Aggregate Functions with DQL:

  Example 5:   SELECT emp_department, AVG(emp_salary) AS average_salary 

                        FROM employees 

                        GROUP BY emp_department;

This query calculates the average salary within each department, grouping data by emp_department.

emp_department	average_salary
HR	50000
Sales	60000
Marketing	55000

Example 6:

        SELECT emp_id, emp_name, emp_salary, departments.dept_name AS department         FROM employees         JOIN departments ON employees.emp_department = departments.dept_name;

emp_id	emp_name	emp_salary	department
1	John	50000	HR
2	Sarah	60000	Sales
3	Emily	55000	Marketing

Example 7: SELECT with WHERE clause

        SELECT * FROM employees         WHERE emp_department = 'Sales';

emp_id	emp_name	emp_salary	emp_department
2	Sarah	60000	Sales

Example 8: SELECT with ORDER BY clause

SELECT * FROM employees ORDER BY emp_salary DESC;

emp_id	emp_name	emp_salary	emp_department
2	Sarah	60000	Sales
3	Emily	55000	Marketing
1	John	50000	HR

Example 9: Aggregation using GROUP BY and COUNT

SELECT emp_department, COUNT(emp_id) AS total_employees FROM employees GROUP BY emp_department;

emp_department	total_employees
HR	1
Sales	1
Marketing	1

Another Examples

Table: students

student_id	student_name	student_age	student_grade
1	Yohanes Abreham	18	A
2	Sara Solomon	20	B
3	Elias Kefale	19	A
4	Michael Dufera	21	B
5	Yohanes Belay	22	C
6	Amanuel Heliso	20	A

SQL - LIKE Clause

Example 1: Using % wildcard

            SELECT *             FROM students             WHERE student_name LIKE 'Yohanes%';

Result - Filtered Table: Students (Using % wildcard)

student_id	student_name	student_age	student_grade
1	Yohanes Abreham	18	A
5	Yohanes Belay	22	C

Example 2: Using _ wildcard

        SELECT *         FROM students         WHERE student_name LIKE 'Sara _olomon';

Result - Filtered Table: students (Using _ wildcard)

student_id	student_name	student_age	student_grade
2	Sara Solomon	20	B

SQL - ORDER BY Clause

Example: Sorting by student_age in Ascending Order

        SELECT *         FROM students         ORDER BY student_age ASC;

Result - Table: students (Ordered by student_age - Ascending)

student_id	student_name	student_age	student_grade
1	Yohanes Abreham	18	A
3	Elias Kefale	19	A
2	Sara Solomon	20	B
6	Amanuel Heliso	20	A
4	Michael Dufera	21	B
5	Yohanes Belay	22	C

Using the DISTINCT Keyword

Example: Retrieving Unique Student Grades

        SELECT DISTINCT student_grade         FROM students;

Result - Unique Student Grades

student_grade
A
B
C

SQL Delete Attribute Statement

Example: Removing Student Record with ID 4

        DELETE FROM students         WHERE student_id = 4;

Result - Updated Table: students (Record Deleted)

student_id	student_name	student_age	student_grade
1	Yohanes Abreham	18	A
2	Sara Solomon	20	B
3	Elias Kefale	19	A
5	Yohanes Belay	22	C
6	Amanuel Heliso	20	A

SQL Update Attribute Statement

Example: Updating Student Grade for ID 6

        UPDATE students         SET student_grade = 'A+'         WHERE student_id = 6;

Result - Updated Table: students (Grade Updated)

student_id	student_name	student_age	student_grade
1	Yohanes Abreham	18	A
2	Sara Solomon	20	B
3	Elias Kefale	19	A
4	Michael Dufera	21	B
5	Yohanes Belay	22	C
6	Amanuel Heliso	20	A+

SQL - INSERT Query to Insert Data

INSERT INTO students (student_id, student_name, student_age, student_grade) VALUES    (7, 'Selam Tesfaye', 23, 'B'),             (8, 'Betelhem Getachew', 19, 'A'),              (9, 'Daniel Yohannes', 20, 'B'),             (10, 'Hanna Meles', 21, 'C'),             (11, 'Samuel Girma', 22, 'A');

Result - Updated Table: students

student_id	student_name	student_age	student_grade
1	Yohanes Abreham	18	A
2	Sara Solomon	20	B
3	Elias Kefale	19	A
4	Michael Dufera	21	B
5	Yohanes Belay	22	C
6	Amanuel Heliso	20	A
7	Selam Tesfaye	23	B
8	Betelhem Getachew	19	A
9	Daniel Yohannes	20	B
10	Hanna Meles	21	C
11	Samuel Girma	22	A

OR

INSERT INTO students VALUES (7, 'Tomas Mulugeta', 19, 'B');

INSERT INTO students VALUES (8, 'Hannah Teshome', 20, 'A');

INSERT INTO students VALUES (9, 'Daniel Hailu', 22, 'C');

INSERT INTO students VALUES (10, 'Rebecca Gebre', 21, 'B');

INSERT INTO students VALUES (11, 'Helen Kidane', 18, 'A');

Result - Updated 'students' Table after INSERT

student_id	student_name	student_age	student_grade
1	Yohanes Abreham	18	A
2	Sara Solomon	20	B
3	Elias Kefale	19	A
4	Michael Dufera	21	B
5	Yohanes Belay	22	C
6	Amanuel Heliso	20	A
7	Tomas Mulugeta	19	B
8	Hannah Teshome	20	A
9	Daniel Hailu	22	C
10	Rebecca Gebre	21	B
11	Helen Kidane	18	A