Oracle SQL Mastery: Crafting Tables, Optimizing Queries, and Updating Records

No Related Topics Available

Creating Table

SQL

 create table student(
   name varchar(20),
   rollno int, 
   branch varchar(10)
);

Describe Table

SQL

describe student;

Output

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(20)
 ROLLNO                                             NUMBER(38)
 BRANCH                                             VARCHAR2(10)

Viewing all Table

SQL

select * from tab;

This command list all the table and our created table will be visible at the bottom of the list.

Inserting into Table

SQL

insert into student values('&name', '&rollno', '&branch');

Output

Enter value for name: Anurag
Enter value for rollno: 1
Enter value for branch: CS
old   1: insert into student values('&name', '&rollno', '&branch')
new   1: insert into student values('Anurag', '1', 'CS')

Use / for the re-execution of the previous command.

Similarly, I have added some more student data.

Viewing Data

SQL

select * from student;

Output

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

Viewing specific field

SQL

select name, rollno from student;

Output

NAME                     ROLLNO
-------------------- ----------
Anurag                        1
Abhijeet                      2
Anshuman                      3

Conditional Query

SQL

select * from student where rollno>1;

Output

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

More Conditional Queries

SQL

select * from student where name='Abhijeet';

SQL

select * from student where branch='CS';

SQL

select name from student where branch='IT';

Sorting Records

SQL

select * from student order by rollno;

Output

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

Descending

SQL

select * from student order by rollno desc;

Output

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

Updating Table

Here is our table.

Table

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

Let's suppose I want to change the name of roll no 2 to Prince

Table

update student set name='Prince' where rollno=2;

Now table data will be updated to

Table

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

Alter Table

Altering a table means changing the old schema of the table to a new schema.

Here is our old table schema.

Output

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(20)
 ROLLNO                                             NUMBER(38)
 BRANCH                                             VARCHAR2(10)

Add Column

In this example, I want to add a new column mobile

Table

alter table student add(mobile number(10));

Now our table will look like this

Table

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

The mobile number of every student is now blank. We can update the mobile number of students using the update command.

Updating Column

Table

alter table student modify(name varchar(50));

We have updated our name field from varchar(20) to varchar(50)

Deleting Column

Table

alter table student drop column mobile;

Now, we have deleted the column mobile and now our schema will look like the old schema.