SQL with Oracle

Open SQLPLUS from CMD and type following credentials

username: system
password: akbar123

Or else type following command in CMD or RUM prompt
sqlplus /  as  sysdba


Example: How to create a table name "student"
CREATE TABLE student(
 id NUMBER,
 firstname VARCHAR2(40),
 fathername VARCHAR2(40),
 surname VARCHAR2(40),
 class NUMBER,
 address VARCHAR2(50)
);



Example: How to insert the new students records
INSERT INTO student(id, firstname, fathername, surname, class, address)  VALUES(101, 'Aleena', 'Akbar', 'Bagwan', 7, 'Mumbai');

INSERT INTO student(id, firstname, fathername, surname, class, address)  VALUES  (102, 'Aliza', 'Akbar', 'Bagwan', 5, 'Mumbai');

INSERT INTO student(id, firstname, fathername, surname, class, address)  VALUES  (103, 'Alishba', 'Akbar', 'Bagwan', 0, 'Mumbai');

INSERT INTO student(id, firstname, fathername, surname, class, address)VALUES  (104, 'Dibyanka', 'Sunil', 'Thakur', 7, 'Pume');

INSERT INTO student(id, firstname, fathername, surname, class, address)VALUES  (105, 'Priyanka', 'Sandeep', 'Jadhav', 4, 'Pume');



Example: How to update a student record
UPDATE student SET address = 'Melbourne' WHERE id = 102;  


Example: How to delete a student record
DELETE FROM student WHERE id = 105;

Example: How to delete all students records
DELETE FROM student;


Example of ROLLBACK
INSERT INTO student(id, firstname, fathername, surname, class, address)  VALUES(101, 'Aleena', 'Akbar', 'Bagwan', 7, 'Mumbai');

INSERT INTO student(id, firstname, fathername, surname, class, address)  VALUES  (102, 'Aliza', 'Akbar', 'Bagwan', 5, 'Mumbai');

SELECT * FROM student
savepoint a;
SELECT * FROM student
DELETE FROM student;
SELECT * FROM student
rollback to a;
commit;
SELECT * FROM student


Example of TRUNCATE (Note: Difference between TRUNCATE and DELETE is with TRUNCATE we can't ROLLBACK)
TRUNCATE TABLE student;  


Example of AND operator
SELECT * FROM student WHERE class>5 AND firstname='Aleena';

Example of OR operator
SELECT * FROM student WHERE class>5 OR firstname='Aleena';

Example of LIKE operator
SELECT * FROM student WHERE firstname LIKE 'Ali%';
SELECT * FROM student WHERE firstname LIKE '%n%';


Example of BETWEEN operator
SELECT * FROM student WHERE class BETWEEN 5 AND 7;

Example of IN operator
SELECT * FROM student WHERE class IN(5, 8, 9);

Example of ORDER BY ASCENDING and DESCENDING CLAUSE
SELECT * FROM student ORDER BY firstname ASC;
SELECT * FROM student ORDER BY firstname DESC;

Example of AS operator
SELECT firstname AS aapkanaam, surname AS aapkasurname FROM student;
SELECT s.firstname, s.surname FROM student s;