In this tutorial, we will continue the previous one and learn some advanced Queries.

Replication

In this, we will create a table from another table. For example, in the previous tutorial, we created a table named student. In this, we create a new table student1 from the table student.

create table student1 as select * from student;

Table student1 also contains all the data from Table student.

Similarly, we can create student2 from student1.

create table student2 as select * from student1;

Now we have 3 tables:

1. student
2. student1
3. student2

Deleting Entire Table

When we delete the entire table, data from the table and the structure of the table will get deleted.

Deleting student2

drop table student2;

Now if we want to view the data in the student2 table it will show an error.

>> select * from student2;
select * from student2
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Deleting Data

Deleting all the data will not affect the table schema and we can add new data to the later.

For example, I want to run the delete query on the student1 table.

Here is the student1 data before running the query.

>> select * from student1;
NAME                                                   ROLLNO BRANCH
-------------------------------------------------- ---------- ----------
Anurag                                                      1 CS
Prince                                                      2 IT
Anshuman                                                    3 CS

Delete Query

delete from student1;

student1 table after running the delete query.

>> select * from student1;

no rows selected

Conditional Delete Query

In this, we can delete a particular student or students by using the where clause.

Note: Entries of the table are case-sensitive.

For example: "abhishek" and "Abhishek" are two different values.

Data before deletion

NAME                                                   ROLLNO BRANCH
-------------------------------------------------- ---------- ----------
Anurag                                                      1 CS
Prince                                                      2 IT
Anshuman                                                    3 CS

Deleting the record of "Anurag" from the student table.

delete from student where name='Anurag';

Data after deletion

NAME                                                   ROLLNO BRANCH
-------------------------------------------------- ---------- ----------
Prince                                                      2 IT
Anshuman                                                    3 CS

And Condition

We can apply multiple conditions using and in our query. If all the conditions are true then the query will execute.

delete from student where name='Anurag' and rollno=1;

This query will delete all the records that have the name "Anurag" and rollno=1.

OR Condition

delete from student where name='Anurag' or rollno=2;

This query will delete all the records that have the name "Anurag" or rollno=2.