Basic SQL commands

Introduction

Use the following commands to create a database, create a table, insert data, and select data etc in Oracle Live SQL https://livesql.oracle.com.

note

If you get ORA-00955: name is already used by an existing object error, you can use the following command to delete the table.

DROP TABLE table_name;

Create

-- creating a table
CREATE TABLE tejas_emp(
    emp_id number(5), 
    name varchar(30), 
    salary number(10)
    );
DESC tejas_emp;

Alter

-- creating a table
CREATE TABLE tejas_emp(
    emp_id number(5), 
    name varchar(30), 
    salary number(10)
    );
DESC tejas_emp;
-- adding columns
ALTER TABLE tejas_emp ADD(
    department varchar(30),
    contact_no number(10)
);
DESC tejas_emp;
-- dropping contact_no column
ALTER TABLE tejas_emp DROP COLUMN contact_no;
DESC tejas_emp;
-- renaming department -> dep
ALTER TABLE tejas_emp MODIFY(department char(20));
DESC tejas_emp;

Truncate

-- creating a table
CREATE TABLE tejas_emp(
    emp_id number(5), 
    name varchar(30), 
    salary number(10)
    );
DESC tejas_emp;
-- adding some data
INSERT INTO tejas_emp VALUES(231,'Himesh',20000);
INSERT INTO tejas_emp VALUES(233,'Raj',4000);
SELECT * FROM tejas_emp;
-- truncating the table
TRUNCATE TABLE tejas_emp;
-- checking if structure still exists
DESC tejas_emp;

Drop

-- creating a table
CREATE TABLE tejas_emp(
    emp_id number(5), 
    name varchar(30), 
    salary number(10)
    );
DESC tejas_emp;
-- droping the table
DROP TABLE tejas_emp;
DESC tejas_emp;

Insert

-- creating a table
CREATE TABLE tejas_emp(
    emp_id number(5), 
    name varchar(30), 
    salary number(10)
    );
DESC tejas_emp;
-- adding some data
INSERT INTO tejas_emp VALUES(231,'Himesh',20000);
INSERT INTO tejas_emp VALUES(233,'Raj',4000);
INSERT INTO tejas_emp VALUES(235,'Mohan',12000);
SELECT * FROM tejas_emp;

Update

-- creating a table
CREATE TABLE tejas_students(
    std_id number(5), 
    name varchar(30), 
    marks number(10),
    status varchar(4)
    );
DESC tejas_students;
-- adding some data
INSERT INTO tejas_students VALUES(231,'Himesh',20,'');
INSERT INTO tejas_students VALUES(233,'Raj',32,'');
INSERT INTO tejas_students VALUES(235,'Mohan',65,'');
INSERT INTO tejas_students VALUES(236,'Ayush',75,'');
SELECT * FROM tejas_students;
-- updating status of student
UPDATE tejas_students SET status = 'PASS' WHERE marks >= 40;
UPDATE tejas_students SET status = 'FAIL' WHERE marks < 40;
SELECT * FROM tejas_students;

Delete

-- creating a table
CREATE TABLE tejas_students(
    std_id number(5), 
    name varchar(30), 
    marks number(10)
    );
DESC tejas_students;
-- adding some data
INSERT INTO tejas_students VALUES(231,'Himesh',20);
INSERT INTO tejas_students VALUES(233,'Raj',32);
INSERT INTO tejas_students VALUES(235,'Mohan',65);
INSERT INTO tejas_students VALUES(236,'Ayush',75);
SELECT * FROM tejas_students;
-- deleting students below 40 marks
DELETE FROM tejas_students WHERE(marks < 40);
SELECT * FROM tejas_students;