Database fundamentals pratice

 As starting preparation for another technical role. I have started brushing my basic skills.

DB is also core of our business application. so starting practice basic sql 

Main application component for same

> DB User

> DB Schema

> DB Data

This is these 3 steps for Oracle or any other RDBMS for basic SQL practice -

User Creation -

-- script to create 

DROP user learning;

CREATE USER learning IDENTIFIED BY learning;

GRANT CONNECT TO learning;

GRANT CONNECT, RESOURCE, DBA TO learning;

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO learning;

GRANT UNLIMITED TABLESPACE TO learning;

-- Schema creation - DDL 

Drop table dept cascade constraints

Create or replace  table dept( deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno) )

Drop table emp cascade constraints;

create  or replace table emp

( empno number(4,0),

 ename varchar2(10), 

job varchar2(9), 

mgr number(4,0), 

hiredate date,

sal number(7,2), 

comm number(7,2),

deptno number(2,0), 

constraint pk_emp primary key (empno) 

);

DML 

--Insert row into DEPT table using named columns.

insert into DEPT (DEPTNO, DNAME, LOC) values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept  values(20, 'RESEARCH', 'DALLAS');

insert into dept  values(30, 'SALES', 'CHICAGO');

insert into dept  values(40, 'OPERATIONS', 'BOSTON');

Pasted from <https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html> 

insert into emp  values(   7839, 'KING', 'PRESIDENT', null,   to_date('17-11-1981','dd-mm-yyyy'),   5000, null, 10  );

insert into emp  values(   7698, 'BLAKE', 'MANAGER', 7839,   to_date('1-5-1981','dd-mm-yyyy'),   2850, null, 30  );

insert into emp  values(   7782, 'CLARK', 'MANAGER', 7839,   to_date('9-6-1981','dd-mm-yyyy'),   2450, null, 10  );

insert into emp  values(   7566, 'JONES', 'MANAGER', 7839,   to_date('2-4-1981','dd-mm-yyyy'),   2975, null, 20  );