Manipulations in SQL

Data manipulation is a crucial aspect of managing and maintaining the integrity of the data stored in tables. SQL (Structured Query Language) provides powerful statements for inserting, updating, and deleting data from database tables.

SQL is a powerful language for working with data in databases. It allows you to create, query, modify, and control data in a structured and efficient way. Here we will focus on the data manipulation aspect of SQL, which involves inserting, updating, and deleting data in tables.

Writing basic queries in SQL -

Extracting data from a table is very basic and essential part of sql

SELECT statement to retrieve data from a database. For example, to retrieve all data from a table called ’employees’, you would write: SELECT * FROM employees.

The asterisk (*) is a wildcard character that represents all columns.

Now lets learn how to manipulate data in SQL-

SQL supports three primary data manipulation operations:

1. INSERT - This operation allows you to add new records or rows to a table.

2. UPDATE - The UPDATE operation enables you to modify existing records in a table.

3. DELETE - The DELETE operation allows you to remove records from a table.

Operations (insert, update, delete)

INSERT - We use insert operation when needs to add new record in a existing table.

Eg- lets take an example of table named movie.

Here we need to add few records

This is the basic syntax -

INSERT INTO movies (movie_id, movie_name, release_year, rental_rate)

VALUES ('1', 'avenger','2022' ,'16.66');

The second line of code is where you will add the values for the rows.

Here we need to insert values according to data type when we created a table "movies"

if we declared data type int as rental rate we will put value something int data type otherwise we will get error while running query.

This is the output :

Insert data from another table:

lets say we have a another table called film now we need to copy rows from film to movie so we will use this basic concept :

INSERT INTO movies (movie_id, movie_name, release_year, rental_rate)

SELECT film_id, title, release_year, rental_rate

FROM film;

UPDATE - Updating data that already exists in a table is another prolific SQL operation. We use the UPDATE statement to change already-existing rows in a table.

For your database to have correct and current information, you must be able to edit existing records. For this, SQL offers the UPDATE statement. Let’s look at some code samples to demonstrate how to fully utilize seamless data updates.

Lets say we want to update movie name to 'Kalki'

Basic syntax :

UPDATE movies

SET movie_name = 'kalki'

WHERE release_year = 2022;

We can also update two or more values simoultansly.

Lets take an example we need to update movie_name and rental_rate now

Here syntax will be:

UPDATE  movies

SET movie_name = 'Barbie' , rental_rate = 20.00

WHERE release_year = 2022;

In this example I had a two entries same of same release_year thats why both entries got updated.

Lets take an example of conditional update where we have increase rental prices by $1 for each movie.

Here syntax will be:

UPDATE  movies

SET rental_rate = rental_rate +1

WHERE movie_id = 3;

DELETE - The Delete operation is crucial for managing your database by removing record thats are no longer needed. SQL provides the DELETE statement for this purpose.

Basic Data Deletion: The DELETE statement is used to remove records from a table based on certain conditions. It allows you to specify which rows you want to delete.

The basic syntax for the DELETE statement is as follows:

DELETE FROM table_name

WHERE condition

The table_name is the name of the table where you want to delete the data. The WHERE clause is optional, but it helps to filter the rows that you want to delete based on a condition. If you omit the WHERE clause, all the rows in the table will be deleted.

For example, suppose we want to delete the row with rental_rate = 17.99 from the movies ’s table. We can use the following DELETE statement:

DELETE FROM movies

WHERE rental_rate = 17.99;

Now we can see that row with rental_rate 17.99 got deleted.

Now lets delete multiple rows

Deleting Multiple Rows

You can also delete multiple rows that match a specific condition. For example, let’s delete all movies with a release_year of 2022:

DELETE FROM movies

WHERE release_year = 2022;

In this example, all rows in movies where release_year is 2022 will get deleted:

Delete whole table:

When you need to delete whole table all rows and coulmn then we need to use

DROP Table movies;

Conclusion

In this blog, we explored the essential data manipulation operations in SQL: INSERT, UPDATE, and DELETE statements. The INSERT statement allows us to add new data into a table, while the UPDATE statement enables us to modify existing records. On the other hand, the DELETE statement allows us to remove specific records from a table based on specified conditions.

By mastering these data manipulation operations, you gain the ability to efficiently manage and maintain data in your relational databases. As you continue to work with SQL, remember to practice and experiment with various data manipulation scenarios to solidify your understanding and proficiency. Happy querying!