Querying and Transformation | Database Management System (DBMS) - Software Development PDF Download

Introduction

In the world of data management, Database Management Systems (DBMS) play a crucial role. A DBMS allows users to store, retrieve, and manipulate data efficiently. One of the key aspects of working with a DBMS is querying and transforming data. In this article, we will explore the concepts of querying and transformation in DBMS, and provide simple examples and code snippets to help you understand these concepts better.

Understanding Queries in DBMS

Queries in DBMS are requests made by users to retrieve, modify, or delete data stored in a database. These queries are written in a specific language called Structured Query Language (SQL), which is a standard language for interacting with DBMS.

Basic Query Structure

A basic SQL query consists of the following components:

• SELECT: Specifies the columns to retrieve from a table.
• FROM: Specifies the table(s) from which to retrieve data.
• WHERE: Filters the data based on specific conditions.
• ORDER BY: Sorts the retrieved data based on specified columns.
• GROUP BY: Groups the data based on specified columns.
• HAVING: Filters the grouped data based on conditions.

SELECT Statement and Retrieving Data

The SELECT statement is used to retrieve data from one or more tables in a database. Here's a simple example that retrieves all columns from a table called "employees":

SELECT * FROM employees;

Code Explanation

• The "SELECT" keyword is followed by an asterisk (*), which means all columns will be retrieved.
• The "FROM" keyword specifies the table name "employees" from which the data will be retrieved.

Filtering Data with WHERE Clause

The WHERE clause is used to filter data based on specific conditions. Here's an example that retrieves employees whose salary is greater than 5000:

SELECT * FROM employees WHERE salary > 5000;

Code Explanation

• The WHERE clause filters the data based on the condition "salary > 5000".
• Only the rows that satisfy the condition will be retrieved.

Sorting Data with ORDER BY Clause

The ORDER BY clause is used to sort the retrieved data based on specified columns. Here's an example that retrieves employees' names and salaries sorted in ascending order of salary:

SELECT name, salary FROM employees ORDER BY salary ASC;

Code Explanation

• The ORDER BY clause is followed by the column name "salary" and the keyword "ASC" to indicate ascending order.
• The result will display employee names and salaries sorted from the lowest salary to the highest.

Aggregating Data with GROUP BY Clause

The GROUP BY clause is used to group the retrieved data based on specified columns. Here's an example that retrieves the total salary for each department:

SELECT department, SUM(salary) FROM employees GROUP BY department;

Code Explanation

• The GROUP BY clause groups the data based on the "department" column.
• The SUM() function calculates the total salary for each department.
• The result will display the department name and the corresponding total salary.

Transforming Data with UPDATE Statement

The UPDATE statement is used to modify existing data in a table. Here's an example that increases the salary of an employee with the ID 101:

UPDATE employees SET salary = salary + 1000 WHERE id = 101;

Code Explanation

• The UPDATE statement updates the "salary" column in the "employees" table.
• The SET keyword is followed by the column name and the new value.
• The WHERE clause specifies the condition to identify the specific employee.

Modifying Data with INSERT Statement

The INSERT statement is used to insert new data into a table. Here's an example that adds a new employee to the "employees" table:

INSERT INTO employees (name, age, salary) VALUES ('John Doe', 30, 6000);

Code Explanation

• The INSERT INTO statement specifies the table name and the columns to insert data into.
• The VALUES keyword is followed by the actual values to be inserted.

Deleting Data with DELETE Statement

The DELETE statement is used to delete data from a table. Here's an example that deletes an employee with the ID 101:

DELETE FROM employees WHERE id = 101;

Code Explanation

• The DELETE FROM statement specifies the table name from which to delete data.
• The WHERE clause specifies the condition to identify the specific employee.

Sample Problems and Solutions

Problem 1: Retrieve the names of all employees who are older than 40.

SELECT name FROM employees WHERE age > 40;

Problem 2: Calculate the average salary of all employees.

SELECT AVG(salary) FROM employees;

Problem 3: Update the salary of all employees in the Sales department by 10%.

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

Conclusion

In this article, we covered the basic concepts of querying and transforming data in a DBMS. We explored how to retrieve data, filter it based on conditions, sort it, aggregate it, and perform data modifications. By understanding these fundamental concepts and using simple SQL statements, you can effectively work with a DBMS and manipulate data to meet your specific requirements.
Remember to practice these concepts and explore more advanced features of DBMS to enhance your database management skills.