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),
    CONSTRAINT fk_HOD 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,
    CONSTRAINT fk_dept 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,
    CONSTRAINT fk_program FOREIGN KEY (program_id) REFERENCES PROGRAM(program_id)
);
describe student;

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 (1, 'John Doe', '123 Main St', TO_DATE('2020-01-15', 'YYYY-MM-DD'), 10);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES (2, 'Jane Smith', '456 Oak St', TO_DATE('2019-08-10', 'YYYY-MM-DD'), 20);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES (3, 'Jim Brown', '789 Pine St', TO_DATE('2018-05-22', 'YYYY-MM-DD'), 30);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES (4, 'Emily White', '101 Maple St', TO_DATE('2021-02-18', 'YYYY-MM-DD'), 40);

INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id) VALUES (5, 'Michael Green', '202 Birch St', TO_DATE('2022-09-11', 'YYYY-MM-DD'), 50);

(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 (10, 'Computer Science', 1);

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id) VALUES (20, 'Electrical Engineering', 2);

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

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

INSERT INTO DEPARTMENT (dept_id, dept_name, HOD_id) VALUES (50, 'Chemical Engineering', 5);

(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 (101, 'BCA', 10);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES (102, 'MCA', 10);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES (201, 'B.E. Electrical', 20);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES (301, 'B.E. Mechanical', 30);

INSERT INTO PROGRAM (program_id, program_name, dept_id) VALUES (401, 'B.E. Civil', 40);

(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', 'Alice Johnson', '123 Cherry Ln', TO_DATE('2000-05-14', 'YYYY-MM-DD'), 101, 3);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S002', 'Bob Anderson', '456 Walnut Ave', TO_DATE('2001-03-22', 'YYYY-MM-DD'), 102, 1);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S003', 'Charlie Davis', '789 Elm St', TO_DATE('1999-12-30', 'YYYY-MM-DD'), 201, 5);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S004', 'Diana Harris', '101 Cedar Rd', TO_DATE('2002-07-19', 'YYYY-MM-DD'), 301, 2);

INSERT INTO STUDENT (student_reg_no, student_name, student_address, student_date_of_birth, program_id, semester) VALUES ('S005', 'Ethan Miller', '202 Ash Blvd', TO_DATE('2000-11-10', 'YYYY-MM-DD'), 401, 4);

(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;

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;

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 * FROM STUDENT ORDER BY student_name ASC;
SELECT * FROM STUDENT ORDER BY student_name DESC;

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.

Example: Select all programs along with their respective department names

SELECT p.program_name, d.dept_name
FROM PROGRAM p
INNER JOIN DEPARTMENT d ON p.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.

Example: Select all departments and their programs, including departments without programs (LEFT JOIN)

SELECT d.dept_name, p.program_name
FROM DEPARTMENT d
LEFT JOIN PROGRAM p ON d.dept_id = p.dept_id;

Example for JOIN Statements

Create two tables as using the following query;\

create table jnc_employees(
	emp_id int primary key, 
	emp_name varchar(30), 
	emp_dept varchar(30)
);

create table jnc_depts(
	dept_id int primary key, 
	dept_name varchar(30), 
	dept_location varchar(30)
);

Insert the values for both the tables using the queries and use the symbol ‘/’ to continue insertion into the table

insert into jnc_employees (emp_id, emp_name, emp_dept) 
	values (&newemp_id, '&newemp_name', '&newemp_dept');

insert into jnc_depts (dept_id, dept_name, dept_location) 
	values (&newdept_id, '&newdept_name', '&newdept_loc');

Following is the screenshot of the SQL Plus command prompt.

SQL> insert into jnc_employees (emp_id, emp_name, emp_dept) values (&newemp_id, '&newemp_name', '&newemp_dept');
Enter value for newemp_id: 1
Enter value for newemp_name: srinivas
Enter value for newemp_dept: BCA
old   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (&newemp_id, '&newemp_name', '&newemp_dept')
new   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (1, 'srinivas', 'bca')

1 row created.

SQL> /
Enter value for newemp_id: 2
Enter value for newemp_name: roopa rani
Enter value for newemp_dept: hindi
old   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (&newemp_id, '&newemp_name', '&newemp_dept')
new   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (2, 'roopa', 'hindi')

1 row created.

SQL> /
Enter value for newemp_id: 3
Enter value for newemp_name: ruby peter
Enter value for newemp_dept: BCA
old   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (&newemp_id, '&newemp_name', '&newemp_dept')
new   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (3, 'ruby', 'bca')

1 row created.

SQL> /
Enter value for newemp_id: 4
Enter value for newemp_name: jeevan
Enter value for newemp_dept: physics
old   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (&newemp_id, '&newemp_name', '&newemp_dept')
new   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (4, 'jeevan', 'physics')

1 row created.

SQL> /
Enter value for newemp_id: 10
Enter value for newemp_name: reema
Enter value for newemp_dept: botany
old   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (&newemp_id, '&newemp_name', '&newemp_dept')
new   1: insert into jnc_employees (emp_id, emp_name, emp_dept) values (10, 'reema', 'botany')

1 row created.
SQL> create table jnc_depts(dept_id int primary key, dept_name varchar(20), dept_location varchar2(20));

Table created.
EMP_IDEMP_NAMEEMP_DEPT
1srinivasBCA
2roopahindi
3rubyBCA
4jeevanphysics
10reemabotany
SQL> insert into jnc_depts (dept_id, dept_name, dept_location) values (&newdept_id, '&newdept_name', '&newdept_loc');
Enter value for newdept_id: 101
Enter value for newdept_name: BCA
Enter value for newdept_loc: cs lab
old   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (&newdept_id, '&newdept_name', '&newdept_loc')
new   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (101, 'bca', 'cs lab')

1 row created.

SQL> /
Enter value for newdept_id: 102
Enter value for newdept_name: physics
Enter value for newdept_loc: physics lab
old   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (&newdept_id, '&newdept_name', '&newdept_loc')
new   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (102, 'physics', 'physics lab')

1 row created.

SQL> /
Enter value for newdept_id: 103
Enter value for newdept_name: hindi
Enter value for newdept_loc: ncc
old   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (&newdept_id, '&newdept_name', '&newdept_loc')
new   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (103, 'hindi', 'ncc')

1 row created.

SQL> /
Enter value for newdept_id: 104
Enter value for newdept_name: commerce
Enter value for newdept_loc: management block
old   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (&newdept_id, '&newdept_name', '&newdept_loc')
new   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (104, 'commerce', 'management block')

1 row created.

SQL> /
Enter value for newdept_id: 105
Enter value for newdept_name: electronics
Enter value for newdept_loc: electronics lab
old   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (&newdept_id, '&newdept_name', '&newdept_loc')
new   1: insert into jnc_depts (dept_id, dept_name, dept_location) values (105, 'electronics', 'electronics lab')

1 row created.
DEPT_IDDEPT_NAMEDEPT_LOCATION
101BCAcs lab
102physicsphysics lab
103hindincc office
104commercemanagement block
105electronicselectronics lab

Inner Join

SELECT jnc_employees.emp_name, jnc_employees.emp_dept, jnc_depts.dept_name FROM jnc_employees INNER JOIN jnc_depts ON jnc_employees.emp_dept = jnc_depts.dept_name;

Left Join

A left join returns all records from the left table (jnc_employees), and the matched records from the right table (jnc_depts). The result is NULL from the right side, if there is no match.

SELECT jnc_employees.emp_name, jnc_employees.emp_dept, jnc_depts.dept_name 
FROM 
jnc_employees 
LEFT JOIN 
jnc_depts 
ON 
jnc_employees.emp_dept = jnc_depts.dept_name;

Right Join

A right join returns all records from the right table (jnc_depts), and the matched records from the left table (jnc_employees). The result is NULL from the left side, when there is no match.

SELECT jnc_employees.emp_name, jnc_employees.emp_dept, jnc_depts.dept_name
 FROM jnc_employees
 RIGHT JOIN
 jnc_depts
 ON
 jnc_employees.emp_dept = jnc_depts.dept_name;

Full Join

SELECT jnc_employees.emp_name, jnc_employees.emp_dept, jnc_depts.dept_name FROM jnc_employees 
FULL JOIN jnc_depts ON jnc_employees.emp_dept = jnc_depts.dept_name;

Self Join

/* Following query display two columns of employee name and one column having department name */
SELECT A.EMP_NAME AS emp_name1, B.EMP_NAME AS emp_name2, A.emp_dept
FROM jnc_employees A, jnc_employees B
WHERE A.emp_dept = B.emp_dept
ORDER BY A.emp_dept;

/* Following query display two columns of same employee name and one column having department name */
SELECT A.EMP_NAME AS emp_name1, B.EMP_NAME AS emp_name2, A.emp_dept
FROM jnc_employees A, jnc_employees B
WHERE A.emp_name = B.emp_name
ORDER BY A.emp_dept;

/* Following query display two columns of employee names where employee id are not equal, and one column having department name as same for the employees */
SELECT A.EMP_NAME AS emp_name1, B.EMP_NAME AS emp_name2, A.emp_dept
FROM jnc_employees A, jnc_employees B
WHERE A.emp_id <> B.emp_id
AND A.emp_dept = B.emp_dept
ORDER BY A.emp_dept;

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 = 'John Doe'
    )
);

Alter the structure of the table by adding a new column

We can alter the structure of the table by adding one or more columns to the existing table.
For example: We can add a new column “Salary” in the table “Faculty”
To add a new column “salary” to the FACULTY table and then insert values into this new column, you will need to follow these steps:
Alter the FACULTY table to add the new column.
Update the FACULTY table to insert values into the new column.

ALTER TABLE FACULTY
ADD salary DECIMAL(10, 2);
DESCRIBE FACULTY;

UPDATE FACULTY
SET salary = 70000.00
WHERE faculty_id = '1';

UPDATE FACULTY
SET salary = 75000.00
WHERE faculty_id = '2';

UPDATE FACULTY
SET salary = 80000.00
WHERE faculty_id = '3';

UPDATE FACULTY
SET salary = 65000.00
WHERE faculty_id = '4';

UPDATE FACULTY
SET salary = 72000.00
WHERE faculty_id = '5';

SELECT * FROM FACULTY;

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;

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

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<br>MODIFY faculty_date_of_joining DATE DEFAULT SYSDATE;</p>

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

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;

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

ALTER TABLE FACULTY<br>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;

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;