Oracle Database Express Edition 11g Release 2
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;
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');