Project Title: Delivery Management
System (Oracle 19c SQL)
How to create user (LOGIN WITH DBA PRIVILEGES)
1.system/tiger
2.Conn
3.SELECT USERNAME FROM DBA_USERS;
4.CREATE USER PANKAJ IDENTIFIED BY tiger;
5.GRANT DBA TO USER;
6.Grant succeeded.
.
.
.
.
.
.
.
.
SET PAGESIZE 50;
.
.
.
.
.
.
.
SET LINESIZE 150;
1 .The project Name is Delivery Management System
● DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME, FLASHBACK)
● DML (INSERT, UPDATE, DELETE, INSERT ALL)
● DQL / DRL (SELECT Queries)
● TCL (COMMIT, ROLLBACK)
● DCL (GRANT, REVOKE)
● CRUD Implementation
2. DDL (Data Definition Language)
2.1 Create Tables
EMP
CREATE TABLE EMP (
EMPNO NUMBER(4), ENAME VARCHAR2(50) NOT NULL,
JOB VARCHAR2(20) CHECK (JOB IN ('COURIER', 'DISPATCHER',
'MANAGER')),
SAL NUMBER(7,2) CHECK (SAL > 0),
HIREDATE DATE DEFAULT SYSDATE,
DEPTNO NUMBER(2)
);
DEPT
CREATE TABLE DEPT (
DEPTNO NUMBER(2) PRIMARY KEY,
DNAME VARCHAR2(50) NOT NULL UNIQUE,
LOC VARCHAR2(50) NOT NULL
);
DELIVERIES
CREATE TABLE DELIVERIES (
ORDER_ID NUMBER(6), EMPNO NUMBER(4),
DELIVERY_DATE DATE DEFAULT SYSDATE,
STATUS VARCHAR2(15) CHECK (STATUS IN ('PENDING', 'DELIVERED',
'CANCELLED')),
LOCATION VARCHAR2(100),
CUSTOMER_NAME VARCHAR2(50)
);
SELECT * FROM TAB;
2.2 ALTER TABLE
ALTER TABLE EMP ADD PHONE VARCHAR2(15);
.
.
.
.
.
.
DESC EMP;
2.3 RENAME TABLE
RENAME EMP TO EMPLOYEES;
.
.
.
.
.
.
SELECT * FROM TAB;
2.4 TRUNCATE TABLE
INSERTING SOME DETAILS TO DELIVERIES
INSERT INTO DELIVERIES (ORDER_ID, EMPNO, DELIVERY_DATE, STATUS,
LOCATION, CUSTOMER_NAME)
VALUES (101, 2001, SYSDATE, 'PENDING', 'Mumbai', 'Rajesh Sharma');
INSERT INTO DELIVERIES (ORDER_ID, EMPNO, DELIVERY_DATE, STATUS,
LOCATION, CUSTOMER_NAME)
VALUES (102, 2002, SYSDATE - 1, 'DELIVERED', 'Pune', 'Anita Verma');
INSERT INTO DELIVERIES (ORDER_ID, EMPNO, DELIVERY_DATE, STATUS,
LOCATION, CUSTOMER_NAME)
VALUES (103, 2003, SYSDATE - 2, 'CANCELLED', 'Hyderabad', 'Sunil Kumar');
INSERT INTO DELIVERIES (ORDER_ID, EMPNO, DELIVERY_DATE, STATUS,
LOCATION, CUSTOMER_NAME)
VALUES (104, 2004, SYSDATE, 'PENDING', 'Bangalore', 'Meena Rao');
INSERT INTO DELIVERIES (ORDER_ID, EMPNO, DELIVERY_DATE, STATUS,
LOCATION, CUSTOMER_NAME)
VALUES (105, 2005, SYSDATE - 3, 'DELIVERED', 'Chennai', 'Vikram Singh');
COMMIT;
.
.
.
.
.
SELECT * FROM DELIVERIES;
.
.
.
.
.
TRUNCATE TABLE DELIVERIES;
.
.
.
.
.
.
.
SELECT * FROM DELIVERIES;
2.5 DROP TABLE
DROP TABLE DEPT;
.
.
.
.
.
.
.
SELECT * FROM TAB;
2.6 RECYCLEBIN
.
.
SELECT * FROM TAB;
.
.
.
.
.
.
SHOW RECYCLEBIN;
2.7 FLASHBACK
FLASHBACK TABLE DEPT TO BEFORE DROP;
.
.
.
.
.
2.8 PURGE
PURGE TABLE DEPT;
.
.
.
.
.
.
.
SHOW RECYCLEBIN;
3. DML (Data Manipulation Language)
3.1 INSERT DATA
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, SAL, DEPTNO) VALUES
(1001, 'John Doe', 'COURIER', 2500, 30);
.
.
.
.
.
.
SELECT * FROM EMPLOYEES;
3.2 INSERT ALL
INSERT ALL
INSERT ALL
INTO EMPLOYEES (EMPNO, ENAME, JOB, SAL, DEPTNO) VALUES (1002,
'Alice Smith', 'DISPATCHER', 3200, 20)
INTO EMPLOYEES (EMPNO, ENAME, JOB, SAL, DEPTNO) VALUES (1003,
'Bob Johnson', 'MANAGER', 5000, 10)
SELECT * FROM DUAL;
.
.
.
.
.
SELECT * FROM EMPLOYEES;
3.3 UPDATE DATA
UPDATE EMPLOYEES SET SAL = 2800 WHERE EMPNO = 1001;
.
.
.
.
SELECT * FROM EMPLOYEES;
3.4 DELETE DATA
DELETE FROM EMPLOYEES WHERE EMPNO = 1001;
.
.
.
.
.
SELECT * FROM EMPLOYEES;
4. DQL / DRL (Data Query / Data Retrieval Language)
4.1 SELECT Queries
SELECT * FROM EMPLOYEES;
SELECT * FROM DELIVERIES WHERE STATUS = 'PENDING';
5. TCL (Transaction Control Language)
5.1 COMMIT
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, SAL, DEPTNO) VALUES
(1004, 'David Green', 'COURIER', 2600, 30);
COMMIT;
.
.
.
.
SELECT * FROM EMPLOYEES;
5.2 ROLLBACK
DELETE FROM EMPLOYEES WHERE EMPNO = 1004;
ROLLBACK;
.
.
.
.
.
SELECT * FROM EMPLOYEES;
6. DCL (Data Control Language)
6.1 GRANT
GRANT SELECT, INSERT ON EMPLOYEES TO PANKAJ;
6.2 REVOKE
REVOKE INSERT ON EMPLOYEES FROM PANKAJ;
7. CRUD-Based Delivery Management System
7.1 Create
CREATE TABLE DELIVERIES (
ORDER_ID NUMBER(6) PRIMARY KEY,
EMPNO NUMBER(4),
DELIVERY_DATE DATE DEFAULT SYSDATE,
STATUS VARCHAR2(15) CHECK (STATUS IN ('PENDING', 'DELIVERED',
'CANCELLED')),
LOCATION VARCHAR2(100),
CUSTOMER_NAME VARCHAR2(50)
);
.
.
.
.
.
.
.
.
INSERT INTO DELIVERIES (ORDER_ID, EMPNO, STATUS, LOCATION,
CUSTOMER_NAME)
VALUES (5001, 1001, 'PENDING', 'Downtown NY', 'Michael Brown');
.
.
.
.
.
.
SELECT * FROM DELIVERIES;
7.2 Read
SELECT * FROM DELIVERIES WHERE STATUS = 'PENDING';
7.3 Update
UPDATE DELIVERIES SET STATUS = 'DELIVERED' WHERE ORDER_ID = 5001;
7.4 Delete
DELETE FROM DELIVERIES WHERE ORDER_ID = 5001;
HOW TO DROP THE USER
1)Exit;
2)Login with DBA access
3)I.e. system/tiger
4)DROP USER PANKAJ CASECADE;
