Start Learning Oracle

Video Content

Oracle Database Express Edition 11g Release 2

Instant client Downloads

SQL Developer 18.2

Oracle Help

http://dba.fyicenter.com/faq/oracle/Create-a-New-Tablespace.html

Installation the Oracle Database and client, open tnsnames.ora file and edit connection string.

I installed oracle in "C:/oracle" directory and my tnsnames.ora file is located in "C:/oracle/app/oracle/product/11.2/server/network/ADMIN/tnsnames.ora" directory.

Change host to your computer name.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mycomputer_name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Oracle tablespace is a logical storage unit.
Each tablesspace consists of one or more datafiles.

SELECT DISTINCT TABLESPACE_NAME, FILE_ID, BYTES
FROM USER_FREE_SPACE;

SELECT TABLESPACE_NAME, FILE_NAME, BYTES
FROM DBA_DATA_FILES;

Data files location on my PC:
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\TBS_PERM_01.DAT

SELECT TABLESPACE_NAME, STATUS, CONTENTS
FROM USER_TABLESPACES;

CREATE TABLESPACE TBS_PERM_02
DATAFILE 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\TBS_PERM_02.DAT' SIZE 10M;

CREATE TEMPORARY TABLESPACE TBS_TEMP_02
TEMPFILE 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\TBS_TEMP_02.DBF' SIZE 10M;

DROP TABLESPACE TBS_PERM_02;

DROP TABLESPACE TBS_TEMP_02;

CREATE USER julesv
  IDENTIFIED BY Aspirin9
  DEFAULT TABLESPACE TBS_PERM_02
  TEMPORARY TABLESPACE TBS_TEMP_02
  QUOTA 10M on tbs_perm_02;

SELECT * FROM ALL_USERS;

SELECT table_name FROM ALL_tables;

CREATE TABLE patients(
patientid NUMBER(11) NOT NULL,
lastname VARCHAR2(20),
firstname VARCHAR2(20),
email VARCHAR2(100),
phone VARCHAR2(30),
age NUMBER(4),
ssn VARCHAR2(11) NOT NULL,
admission_date DATE,
gender VARCHAR2(6),
active NUMBER(4),
CONSTRAINT pk_patients PRIMARY KEY (patientid)
);

CREATE sequence patients_seq
start with 1
increment by 1
nomaxvalue;

SELECT sequence_name from user_sequences;

INSERT into patients
(patientid, lastname, firstname, email, phone, age, ssn, admission_date, gender, active)
VALUES
(patients_seq.nextval,'Smith', 'John','johns@gmail.com','727-123-6789',45,'123-45-6789','07-JUL-18','male',1)

SQL> SELECT constraint_name FROM user_constraints WHERE UPPER(table_name) = UPPER('patients');

SQL> SELECT constraint_name
FROM user_constraints
WHERE
UPPER(table_name) = UPPER('patients')
AND CONSTRAINT_TYPE = 'P';  

login as johnsmith and try to SELECT from patients;

GRANT SELECT on sergey_s.patients to johnsmith;

GRANT SELECT, INSERT, UPDATE, DELETE ON sergey_s.patients TO johnsmith;

GRANT CONNECT, RESOURCE, DBA TO julesv;

View table structure

DESC students

DESC student_course

DESC courses

DESC teachers

DESC schedule

SELECT studentid, firstname, lastname from students ORDER BY lastname;

SELECT courseid, coursename from courses ORDER BY coursename;

SELECT courseid, coursename, hours, cost
FROM courses
ORDER BY cost, coursename 

DESC student_course;

ALTER TABLE student_course MODIFY paid NUMBER(2);

ALTER TABLE student_course modify paid varchar2(3);

SELECT studentid, firstname, lastname from students ORDER BY lastname

INSERT into students
(studentid, lastname, firstname, email, phone, age, gender)
VALUES
(students_seq.nextval, 'Roberts', 'Cindy', 'webbm@yahoo.com','212-234-5555',25, 'female');

SELECT studentid, courseid from student_course;

SELECT courseid, coursename, cost from courses ORDER BY coursename

SELECT age, COUNT(*) FROM students GROUP BY age ORDER BY age

SELECT firstname, lastname, age FROM students WHERE age in (SELECT MAX(age) FROM students)

SELECT firstname, lastname, age FROM students WHERE age in (Select MIN(age) FROM students)

SELECT firstname, lastname, age FROM students WHERE age < (SELECT AVG(age) as AVG_age FROM students) ORDER BY age DESC

SELECT firstname, lastname FROM students WHERE SUBSTR(firstname, 1, 1)='M'"

SELECT firstname, lastname FROM students WHERE INSTR(firstname, 'M' )=1

SELECT firstname, lastname FROM students ORDER BY LENGTH(firstname)

SELECT firstname, COUNT(*) FROM students GROUP BY firstname HAVING COUNT(*) > 1

all students that have courses and all courses that have students

SELECT s.studentid, s.firstname, s.lastname, c.coursename
from students s INNER JOIN student_course sc
ON s.studentid=sc.studentid INNER JOIN courses c
ON sc.courseid=c.courseid ORDER BY coursename;

all students and courses that have students

SELECT s.studentid, s.firstname, s.lastname, c.coursename
FROM students s
LEFT OUTER JOIN
student_course sc
ON s.studentid=sc.studentid
LEFT OUTER JOIN courses c
ON sc.courseid=c.courseid ORDER BY coursename;

all courses and students that have courses

SELECT s.studentid, s.firstname, s.lastname, c.coursename
FROM students s
RIGHT OUTER JOIN
student_course sc
ON s.studentid=sc.studentid
RIGHT OUTER JOIN courses c
ON sc.courseid=c.courseid
ORDER BY coursename;

SELECT s.lastname, s.firstname, s.age, c.coursename
FROM
(SELECT * FROM students WHERE age < 26) s
LEFT OUTER JOIN student_course sc
ON s.studentid=sc.studentid
LEFT OUTER JOIN courses c
ON sc.courseid=c.courseid;

How many courses each student has.

SELECT s.studentid, s.lastname, s.age,
COUNT(c.courseid)
FROM students s INNER JOIN student_course sc
ON s.studentid=sc.studentid
INNER JOIN courses c
ON sc.courseid=c.courseid
GROUP BY s.studentid, s.lastname, s.age
 ORDER BY s.lastname;

SELECT s.studentid, s.lastname, s.age, COUNT(c.courseid)
FROM students s
INNER JOIN student_course sc
ON s.studentid=sc.studentid
INNER JOIN courses c
ON sc.courseid=c.courseid
GROUP BY s.studentid, s.lastname, s.age
having s.age > 24
ORDER BY s.lastname; 

SELECT s.firstname, s.lastname, c.coursename, sc.paid
FROM students s
INNER JOIN student_course sc
ON s.studentid=sc.studentid
INNER JOIN courses c
ON sc.courseid=c.courseid
and sc.paid='Yes'
ORDER BY coursename; 

SELECT s.firstname,  s.lastname, c.coursename, sc.paid
FROM students s
INNER JOIN student_course sc
ON s.studentid=sc.studentid
INNER JOIN courses c
ON sc.courseid=c.courseid and sc.paid='No'
ORDER BY coursename;

SELECT teacherid, firstname, lastname FROM teachers ORDER BY lastname;

UPDATE teachers set firstname='James' WHERE teacherid=22;

SELECT * FROM schedule

INSERT into schedule
(scheduleid, courseid, teacherid, startdate, enddate)
VALUES
(schedule_seq.nextval, 23, 25, '01-SEP-18','30-JAN-18');

INSERT into courses
(courseid, coursename, hours, cost)
VALUES
(courses_seq.nextval, 'SQL', 200, 2000);

INSERT into teachers
(teacherid, lastname, firstname, email, phone, hiredate, rate)
VALUES
(teachers_seq.nextval, 'Bennett','Robin','robinbw@yahoo.com','718-777-9876','01-MAR-18', 9);

update teachers set email='cindyr@yahoo.com' WHERE lastname='Roberts'

teachers - courses

SELECT t.teacherid, t.firstname, t.lastname, c.coursename
FROM teachers t
INNER JOIN schedule sc
ON t.teacherid = sc.teacherid
INNER JOIN courses c ON
 sc.courseid=c.courseid
 ORDER BY t.lastname, c.coursename;

All teachers and courses that have teachers

SELECT t.teacherid, t.firstname, t.lastname, c.coursename
FROM teachers t
LEFT OUTER JOIN schedule sc
ON t.teacherid = sc.teacherid
LEFT OUTER JOIN courses c
ON sc.courseid=c.courseid
ORDER BY t.lastname, c.coursename;

all courses and teachers that have courses

SELECT t.teacherid, t.firstname, t.lastname, c.coursename
FROM teachers t
RIGHT OUTER JOIN schedule sc
ON t.teacherid = sc.teacherid
RIGHT OUTER JOIN courses c ON
 sc.courseid=c.courseid
 ORDER BY t.lastname, c.coursename;

How many courses each teacher teaches?

SELECT t.firstname, t.lastname,
COUNT(sc.courseid)
FROM teachers t
INNER JOIN schedule sc
ON t.teacherid = sc.teacherid
GROUP BY t.firstname, t.lastname;

How many students each teacher has?

SELECT t.firstname, t.lastname,
COUNT(st.studentid)
FROM teachers t
INNER JOIN schedule sc
ON t.teacherid = sc.teacherid
INNER JOIN student_course st
ON sc.courseid=st.courseid
GROUP BY t.firstname, t.lastname; 

How many teachers each student has?

SELECT s.firstname, s.lastname,
COUNT(t.teacherid)
FROM students s
INNER JOIN student_course sc
ON s.studentid=sc.studentid
INNER JOIN schedule d
ON sc.courseid=d.courseid
INNER JOIN teachers t
ON d.teacherid=t.teacherid
GROUP BY s.firstname, s.lastname
ORDER BY s.lastname; 

INSERT into student_course
(studentcourseid, studentid, courseid, paid)
VALUES
(std_courses_seq.nextval, 22, 22, 'Yes');

INSERT into teachers
(teacherid, lastname, firstname, email, phone, hiredate, rate)
VALUES (teachers_seq.nextval, 'Woods','Jesika', 'jesikaw@gmail.com','727-987-1234',
 to_date('07-JUN-18:12:00:00PM', 'dd-MON-yy:hh:mi:ssam'),9)

SELECT firstname, lastname,
TO_CHAR(hiredate, 'mm/dd/yyyy HH24:MI:SS')
FROM teachers
WHERE lastname='Woods'; 

Which teachers teach JavaScript:

SELECT firstname, lastname, phone
FROM teachers
WHERE teacherid in
(SELECT teacherid
FROM schedule sc
INNER JOIN courses c
ON sc.courseid=c.courseid
and coursename='JavaScript');

Computer Programming, Web programming Tips