Working with SQL Plus

Display the list of tables

SELECT table_name FROM user_tables;

SQL Query for Creating the table “Faculty”

CREATE TABLE FACULTY (
    faculty_id VARCHAR(10) PRIMARY KEY,
    faculty_name VARCHAR(30),
    faculty_address VARCHAR(200),
    faculty_date_of_joining DATE,
    dept_id INT
);
describe faculty;

SQL Query for Creating the table “Department”

CREATE TABLE DEPARTMENT (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100),
    HOD_id VARCHAR(10),
    FOREIGN KEY (HOD_id) REFERENCES FACULTY(faculty_id)
);
describe department;

SQL Query for Creating the table “Program”

CREATE TABLE PROGRAM (
    program_id VARCHAR(10) PRIMARY KEY,
    program_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(dept_id)
);
describe program;

SQL Query for Creating the table “Student”

CREATE TABLE STUDENT (
    student_reg_no VARCHAR(20) PRIMARY KEY,
    student_name VARCHAR(30),
    student_address VARCHAR(200),
    student_date_of_birth DATE,
    program_id VARCHAR(10),
    semester INT,
    FOREIGN KEY (program_id) REFERENCES PROGRAM(program_id)
);
describe student;

SQL Query for Creating the table “Faculty_Dependent”

CREATE TABLE FACULTY_DEPENDENT(
    faculty_id VARCHAR(10),
    dependent_name VARCHAR(30),
    dependent_dob DATE,
    dependent_contact VARCHAR(10),
    dependent_address VARCHAR(300),
    dependent_relation VARCHAR(20)
);

Describe Faculty_Dependent;

SQL Query to insert values into the table “Faculty”

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F001','Amit Sharma','Bangalore','15-JAN-2015',1);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F002','Priya Iyer','Mumbai','12-MAR-2016',2);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F003','Rajesh Kumar','Delhi','10-JUL-2014',3);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F004','Anita Nair','Chennai','25-SEP-2017',4);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F005','Suresh Reddy','Pune','30-DEC-2018',5);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F006','Meera Joshi','Hyderabad','18-FEB-2019',6);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F007','Arjun Banerjee','Kolkata','01-MAY-2016',7);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F008','Shalini Verma','Lucknow','09-NOV-2020',8);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F009','Vikram Singh','Jaipur','19-AUG-2021',9);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES ('F010','Kavita Patel','Surat','11-APR-2022',10);

(OPTIONAL) SQL commands to adjust the output format/display

set linesize 150
COLUMN faculty_id FORMAT A10
COLUMN faculty_name FORMAT A20
COLUMN faculty_address FORMAT A30
COLUMN faculty_date_of_joining FORMAT A15
COLUMN dept_id FORMAT 99999
 
select * from faculty;

SQL Query to insert values into the table “Department”

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (1,'Computer Science','F001');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (2,'Electronics','F002');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (3,'Mechanical','F003');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (4,'Civil','F004');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (5,'IT','F005');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (6,'Biotech','F006');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (7,'Mathematics','F007');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (8,'Physics','F008');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (9,'Chemistry','F009');

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id)  VALUES (10,'English','F010');

(OPTIONAL) SQL commands to adjust the output format/display

COLUMN dept_id FORMAT 99999
COLUMN dept_name FORMAT A30
COLUMN HOD_id FORMAT A10
 
SELECT * FROM DEPARTMENT;

SQL Query to insert values into the table “Program”

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P001','BCA',1);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P002','BSc Electronics',2);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P003','BTech Mechanical',3);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P004','BTech Civil',4);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P005','BSc IT',5);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P006','BSc Biotech',6);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P007','BSc Maths',7);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P008','BSc Physics',8);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P009','BSc Chemistry',9);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES ('P010','BA English',10);

(OPTIONAL) SQL commands to adjust the output format/display

COLUMN program_id FORMAT A10
COLUMN program_name FORMAT A20
COLUMN dept_id FORMAT 99999
 
SELECT * FROM PROGRAM;

SQL Query to insert values into the table “Student”

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S001','Amit Kumar','Bangalore','01-JAN-2002','P001',1);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S002','Priya Sharma','Mumbai','15-FEB-2001','P002',2);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S003','Raj Patel','Delhi','20-MAR-2000','P003',3);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S004','Neha Gupta','Chennai','25-APR-2002','P004',1);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S005','Vikram Singh','Pune','30-MAY-2001','P005',2);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S006','Anjali Nair','Hyderabad','05-JUN-2000','P006',3);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S007','Ravi Verma','Kolkata','10-JUL-2002','P007',1);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S008','Sneha Reddy','Lucknow','15-AUG-2001','P008',2);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S009','Arjun Mehta','Jaipur','20-SEP-2000','P009',3);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S010','Kavita Joshi','Surat','25-OCT-2002','P010',1);

(OPTIONAL) SQL commands to adjust the output format/display

COLUMN student_reg_no FORMAT A10
COLUMN student_name FORMAT A20
COLUMN student_address FORMAT A30
COLUMN student_date_of_birth FORMAT A15
COLUMN program_cd FORMAT A10
COLUMN semester FORMAT 99999
 
SELECT * FROM STUDENT;

SQL Query to insert values into the table “Faculty_Dependent”

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F001','Ananya Sharma','05-MAY-2010','9876543210','Bangalore','Daughter');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F002','Rohan Iyer','12-JUN-2012','9876543211','Mumbai','Son');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F003','Ishita Kumar','23-MAR-2013','9876543212','Delhi','Daughter');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F004','Aditya Nair','14-JUL-2011','9876543213','Chennai','Son');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F005','Sneha Reddy','09-AUG-2014','9876543214','Pune','Daughter');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F006','Karan Joshi','19-SEP-2015','9876543215','Hyderabad','Son');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F007','Mira Banerjee','28-OCT-2016','9876543216','Kolkata','Daughter');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F008','Aryan Verma','06-NOV-2017','9876543217','Lucknow','Son');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F009','Diya Singh','15-DEC-2018','9876543218','Jaipur','Daughter');

INSERT INTO FACULTY_DEPENDENT (faculty_id,dependent_name,dependent_dob,dependent_contact,dependent_address,dependent_relation) VALUES ('F010','Kabir Patel','24-JAN-2019','9876543219','Surat','Son');

(OPTIONAL) SQL commands to adjust the output format/display

COLUMN faculty_id FORMAT A10
COLUMN dependent_name FORMAT A15
COLUMN dependent_dob FORMAT A12
COLUMN dependent_contact FORMAT A12
COLUMN dependent_address FORMAT A40
COLUMN dependent_relation FORMAT A15

SELECT * FROM FACULTY_DEPENDENT;

Alter the structure of the table by adding a new column “salary” for the table “Faculty”

ALTER TABLE FACULTY ADD salary NUMBER(10,2);
describe faculty

Select Queries with WHERE Clause

The WHERE clause is used to filter records that meet certain conditions.

SELECT * FROM FACULTY WHERE faculty_date_of_joining > TO_DATE('2020-01-01', 'YYYY-MM-DD');

select * from student where student_reg_no='S003';

select * from student where semester=3;

UPDATE the table using the WHERE Clause: 

UPDATE FACULTY SET salary = 60000 WHERE dept_id = 1;
UPDATE FACULTY SET salary = 55000 WHERE dept_id = 2;
UPDATE FACULTY SET salary = 50000 WHERE dept_id > 2;

Select * from FACULTY;

Select Queries with SORT Using ORDER BY Clause

The ORDER BY clause is used to sort the result set by one or more columns.

Example:

Select all students and sort them by name in ascending order and descending order

Select faculty_id, faculty_name in ascending and descending order by salary.

SELECT * FROM STUDENT ORDER BY student_name ASC;
SELECT * FROM STUDENT ORDER BY student_name DESC;

SELECT faculty_id, faculty_name, salary FROM FACULTY ORDER BY salary DESC;
SELECT faculty_id, faculty_name, salary FROM FACULTY ORDER BY salary ASC;

Select Queries with INNER JOIN

The INNER JOIN clause is used to combine rows from two or more tables based on a related column between them.

Right now, all our joins will show the same result because the data in our FACULTY and DEPARTMENT tables is perfectly matched — every dept_id in FACULTY exists in DEPARTMENT and vice versa.

To make the join results different, we need to insert some unmatched records in either table.

-- Faculty with dept_id that does NOT exist in DEPARTMENT
INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id)
VALUES ('F999', 'Guest Faculty', 'New Delhi', '01-JAN-2020', 99);

-- Department with no faculty assigned
INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id) VALUES (88, 'Astrophysics', NULL);

COMMIT;

Inner Join

-- INNER JOIN (Faculty and Department)

SELECT f.faculty_id, f.faculty_name, d.dept_name
FROM FACULTY f
INNER JOIN DEPARTMENT d ON f.dept_id = d.dept_id;

Select Queries with OUTER JOIN

The OUTER JOIN (LEFT, RIGHT, or FULL) is used to combine rows from two or more tables, but includes all rows from one table and the matched rows from the other table.

LEFT JOIN

-- LEFT OUTER JOIN (All Faculty, matching Departments)

SELECT f.faculty_id, f.faculty_name, d.dept_name
FROM FACULTY f
LEFT JOIN DEPARTMENT d ON f.dept_id = d.dept_id;

Right Join

-- RIGHT OUTER JOIN (All Departments, matching Faculty)

SELECT f.faculty_id, f.faculty_name, d.dept_name
FROM FACULTY f
RIGHT JOIN DEPARTMENT d ON f.dept_id = d.dept_id;

Self Join

-- SELF JOIN via DEPARTMENT to show Faculty with their HODs

SELECT f.faculty_id,
       f.faculty_name AS FacultyName,
       h.faculty_name AS HODName,
       d.dept_name
FROM FACULTY f
JOIN DEPARTMENT d 
    ON f.dept_id = d.dept_id
JOIN FACULTY h 
    ON d.HOD_id = h.faculty_id
ORDER BY d.dept_name;

Select Queries with Subqueries

A subquery is a query within another query.
Example: Select all students enrolled in the department of the faculty member ‘John Doe’

SELECT student_name, student_address
FROM STUDENT
WHERE program_id IN (
    SELECT program_id
    FROM PROGRAM
    WHERE dept_id = (
        SELECT dept_id
        FROM FACULTY
        WHERE faculty_name = 'Amit Sharma'
    )
);

Alter the structure of the table by removing a column

We can remove an existing column by executing the query ALTER TABLE table_name DROP COLUMN column_name;

Select Queries with Built-in Numerical Functions

Numerical functions perform operations on numeric data.
Example: Select the average salary of all faculty members

SELECT AVG(salary) AS average_salary FROM FACULTY;

SELECT 
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM 
    FACULTY;

Select Queries with Built-in String Functions

String functions perform operations on string data.
Example: UPPER: Converts a string to uppercase and LOWER: Converts a string to lowercase.

SELECT UPPER(student_name) AS uppercase_name FROM STUDENT;
SELECT student_name, LOWER(student_name) AS lowercase_name FROM STUDENT;

Select Queries with Built-in Date Functions

Date functions perform operations on date data.
Example: Select the year of joining of each faculty member

SELECT faculty_name, EXTRACT(YEAR FROM faculty_date_of_joining) AS joining_year FROM FACULTY;

Alter table structure by changing existing columns

Example 1: Modify a Column’s Data Type
Suppose you want to change the data type of the faculty_address column from VARCHAR(200) to VARCHAR(250):

ALTER TABLE FACULTY
MODIFY faculty_address VARCHAR(250);

-- Step 1: Add a new column with the desired data type
ALTER TABLE student ADD new_semester VARCHAR(5);

-- Step 2: Convert and copy the data from the old column to the new column
UPDATE student SET new_semester = TO_CHAR(semester);

-- Step 3: Drop the old column
ALTER TABLE student DROP COLUMN semester;
Select * from student;

-- Step 4: Rename the new column to the original column name
ALTER TABLE student RENAME COLUMN new_semester TO semester;
Select * from student;

Example 2: Modify a Column’s Length
If you want to increase the length of the faculty_name column from VARCHAR(30) to VARCHAR(50):

ALTER TABLE FACULTY
MODIFY faculty_name VARCHAR(50);

Example 3: Add a Default Value to a Column
Suppose you want to set a default value for the faculty_date_of_joining column, such that if no date is provided, the system date is used:

ALTER TABLE FACULTY MODIFY faculty_date_of_joining DATE DEFAULT SYSDATE;

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, dept_id) VALUES (12, 'Michael Clark', '20 Beach St', 50);

Select * from Faculty;

Example 4: Rename a Column
If you want to rename a column, for example, changing dept_id to department_id:

ALTER TABLE FACULTY
RENAME COLUMN salary TO faculty_salary;
Describe Faculty;

Example 5: Modify a Column to NOT NULL
To change a column so that it does not allow NULL values, you can use:

The following INSERT query will generate an error because the value for faculty_name, which is declared NOT NULL, is not given.

ERROR at line 1:
ORA-00947: not enough values

ALTER TABLE FACULTY MODIFY faculty_name VARCHAR(50) NOT NULL;

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, dept_id) VALUES (13, '20 Beach St', 50);

Example 6: Add a column update values and Drop the column Column
If you need to drop a column from a table, such as removing the faculty_address column:

ALTER TABLE FACULTY
DROP COLUMN faculty_address;

Enable and Disable Constraints

SELECT constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'STUDENT';
CONSTRAINT_NAME CStatus
SYS_xxxxxxxxxxPEnabled
SYS_yyyyyyyyyyREnabled

Disable the constraint (Primary Key) using the following query

ALTER TABLE STUDENT DISABLE CONSTRAINT SYS_xxxxxxxxxx;

Add duplicate rows and try to enable the constraints; it will generate an error.

*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.SYS_xxxxxxxxxx) – primary key violated

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S001','Amit Kumar','Bangalore','01-JAN-2002','P001',1);


ALTER TABLE STUDENT ENABLE CONSTRAINT SYS_xxxxxxxxxx;

Delete the duplicate rows and then enable the constraints.

delete from student where student_reg_no='S001';

ALTER TABLE STUDENT ENABLE CONSTRAINT SYS_xxxxxxxxxx;

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S001','Amit Kumar','Bangalore','01-JAN-2002','P001',1);

As the constraints are enabled, if you try to add any duplicate rows, it will give an error.

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S001','Amit Kumar','Bangalore','01-JAN-2002','P001',1);
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_xxxxxxxxxx) violated

Delete Tables Permanently

DROP TABLE faculty_dependent CASCADE CONSTRAINTS PURGE;
DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;
DROP TABLE PROGRAM CASCADE CONSTRAINTS PURGE;
DROP TABLE DEPARTMENT CASCADE CONSTRAINTS PURGE;
DROP TABLE FACULTY CASCADE CONSTRAINTS PURGE;