Oracle PL/SQL/Table Joins/Table Join Basics — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 09:54, 26 мая 2010
Содержание
- 1 Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
- 2 Adding Ordering to the Query Containing the GROUP BY
- 3 Add the USING clause
- 4 Get Categories and Products (with Alternate Join Syntax)
- 5 Get Categories and Products (with Joins)
- 6 How Many Featured Products By Department with JOINs
- 7 How Many Products By Department with JOINs
- 8 Joining table to use between ... and clause
- 9 Join table using
- 10 Join with a subquery
- 11 Nested Multi-Table Equi-Joins
- 12 Non-Equi Joins
- 13 Supplying Table Aliases
- 14 The query shows that the join is performed with the other WHERE conditions
- 15 Used a GROUP BY in a query with no ordering or analytical function
Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
Hit a key to continue
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
SQL>
SQL> SELECT e.empno, e.ename, j.jobtitle, e.orig_salary,
2 RANK() OVER(ORDER BY e.orig_salary desc) rankorder
3 FROM employee e, job j
4 WHERE e.orig_salary < 43000
5 AND e.empno = j.empno
6 ORDER BY orig_salary desc;
Hit a key to continue
EMPNO ENAME JOBTITLE ORIG_SALARY RANKORDER
---------- ---------- ---------- ----------- ----------
105 Robert Accountant 31000 1
123 James Dediator 23000 2
108 Jode Developer 21000 3
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
SQL>
Adding Ordering to the Query Containing the GROUP BY
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
Hit a key to continue
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL> -- Adding Ordering to the Query Containing the GROUP BY
SQL>
SQL>
SQL> SELECT j.jobtitle, COUNT(*), MAX(orig_salary) maxsalary,
2 MIN(orig_salary) minsalary
3 FROM employee e, job j
4 WHERE e.orig_salary < 43000
5 AND e.empno = j.empno
6 GROUP BY j.jobtitle
7 ORDER BY maxsalary;
Hit a key to continue
JOBTITLE COUNT(*) MAXSALARY MINSALARY
---------- ---------- ---------- ----------
Developer 1 21000 21000
Dediator 1 23000 23000
Accountant 1 31000 31000
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
Add the USING clause
SQL>
SQL> create table department(
2 dept_no integer primary key
3 ,dept_name varchar(20) not null
4 ,mgr_no integer
5 );
Table created.
SQL>
SQL> insert into department(dept_no, dept_name, mgr_no)values(1, "Design", 1);
1 row created.
SQL> insert into department(dept_no, dept_name, mgr_no)values(2, "Sales", 1);
1 row created.
SQL> insert into department(dept_no, dept_name, mgr_no)values(3, "Development", 1);
1 row created.
SQL>
SQL> create table employee(
2 emp_no integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,salary number(3)
14 ,birthdate date
15 ,hiredate date
16 ,title varchar2(20)
17 ,dept_no integer
18 ,mgr integer
19 ,region number
20 ,division number
21 ,total_sales number
22 );
Table created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created.
SQL>
SQL>
SQL> select dept_name, lastname
2 from department inner join employee
3 using (dept_no)
4 /
DEPT_NAME LASTNAME
-------------------- --------------------
Sales Anderson
Sales Last
Design Wash
Design Bush
Design Will
Sales Pete
Development Roke
Development Horry
Development Bar
9 rows selected.
SQL>
SQL>
SQL> drop table department;
Table dropped.
SQL> drop table employee;
Table dropped.
SQL> --
Get Categories and Products (with Alternate Join Syntax)
SQL>
SQL>
SQL>
SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(1000) NOT NULL,
5 Price NUMBER NULL,
6 ImagePath VARCHAR(50) NULL,
7 soldout NUMBER(1,0) NULL,
8 Promotion NUMBER(1,0) NULL);
Table created.
SQL>
SQL> CREATE SEQUENCE ProductIDSeq;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product
3 FOR EACH ROW
4 BEGIN
5 SELECT ProductIDSeq.NEXTVAL
6 INTO :NEW.ProductID FROM DUAL;
7 END;
8 /
Trigger created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler", "Long",14.99, "ruler.jpg", 0, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard", "keyboard",3.75, "keyboard.jpg", 0, 0);
1 row created.
SQL>
SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL,
3 CategoryID INT NOT NULL,
4 PRIMARY KEY (ProductID, CategoryID)
5 );
Table created.
SQL>
SQL>
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY,
3 DepartmentID INT,
4 Name VARCHAR(50),
5 Description VARCHAR (200) NULL);
Table created.
SQL>
SQL> CREATE SEQUENCE CategoryIDSeq;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category
3 FOR EACH ROW
4 BEGIN
5 SELECT CategoryIDSeq.NEXTVAL
6 INTO :NEW.CategoryID FROM DUAL;
7 END;
8 /
Trigger created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Local", "In town");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Remote", "Telecommute");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (2, "Masks", "By bits");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wireless", "Not connected");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wired", "Connected");
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT C.Name as "Category Name", P.Name as "Product Name"
2 FROM Product P, ProductCategory PC, Category C
3 WHERE P.ProductID = PC.ProductID AND PC.CategoryID = C.CategoryID
4 ORDER BY C.Name, P.Name;
Category Name
--------------------------------------------------
Product Name
--------------------------------------------------
Local
PC
Local
Ruler
Masks
Desk
Masks
Keyboard
Masks
Pen
Masks
Ruler
Remote
Mouse
Wireless
Keyboard
8 rows selected.
SQL>
SQL>
SQL> drop table Product;
Table dropped.
SQL> drop table ProductCategory;
Table dropped.
SQL> drop table Category;
Table dropped.
SQL> drop sequence CategoryIDSeq;
Sequence dropped.
SQL> drop sequence ProductIDSeq;
Sequence dropped.
SQL>
Get Categories and Products (with Joins)
SQL>
SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(1000) NOT NULL,
5 Price NUMBER NULL,
6 ImagePath VARCHAR(50) NULL,
7 soldout NUMBER(1,0) NULL,
8 Promotion NUMBER(1,0) NULL);
Table created.
SQL>
SQL> CREATE SEQUENCE ProductIDSeq;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product
3 FOR EACH ROW
4 BEGIN
5 SELECT ProductIDSeq.NEXTVAL
6 INTO :NEW.ProductID FROM DUAL;
7 END;
8 /
Trigger created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Ruler", "Long",14.99, "ruler.jpg", 0, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard", "keyboard",3.75, "keyboard.jpg", 0, 0);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL,
3 CategoryID INT NOT NULL,
4 PRIMARY KEY (ProductID, CategoryID)
5 );
Table created.
SQL>
SQL>
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY,
3 DepartmentID INT NOT NULL,
4 Name VARCHAR(50) NOT NULL,
5 Description VARCHAR (200) NULL);
Table created.
SQL>
SQL> CREATE SEQUENCE CategoryIDSeq;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category
3 FOR EACH ROW
4 BEGIN
5 SELECT CategoryIDSeq.NEXTVAL
6 INTO :NEW.CategoryID FROM DUAL;
7 END;
8 /
Trigger created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Local", "In town");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Remote", "Telecommute");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (2, "Masks", "By bits");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wireless", "Not connected");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wired", "Connected");
1 row created.
SQL>
SQL>
SQL> SELECT C.Name as "Category Name", P.Name as "Product Name"
2 FROM Product P
3 INNER JOIN ProductCategory PC ON P.ProductID = PC.ProductID
4 INNER JOIN Category C ON PC.CategoryID = C.CategoryID
5 ORDER BY C.Name, P.Name;
Category Name
--------------------------------------------------
Product Name
--------------------------------------------------
Local
PC
Local
Ruler
Masks
Desk
Masks
Keyboard
Masks
Pen
Masks
Ruler
Remote
Mouse
Wireless
Keyboard
8 rows selected.
SQL>
SQL>
SQL>
SQL> drop table Product;
Table dropped.
SQL> drop table ProductCategory;
Table dropped.
SQL> drop table Category;
Table dropped.
SQL> drop sequence CategoryIDSeq;
Sequence dropped.
SQL> drop sequence ProductIDSeq;
Sequence dropped.
How Many Featured Products By Department with JOINs
SQL>
SQL>
SQL> CREATE TABLE Department (
2 DepartmentID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(200) NULL);
SQL>
SQL> CREATE SEQUENCE DepartmentIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger
2 BEFORE INSERT ON Department
3 FOR EACH ROW
4 BEGIN
5 SELECT DepartmentIDSeq.NEXTVAL
6 INTO :NEW.DepartmentID FROM DUAL;
7 END;
8 /
SQL>
SQL> INSERT INTO Department (Name, Description)
2 VALUES ("Software", "Coding");
SQL> INSERT INTO Department (Name, Description)
2 VALUES ("Hardware", "Building");
SQL> INSERT INTO Department (Name, Description)
2 VALUES ("QA", "Testing");
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY,
3 DepartmentID INT NOT NULL,
4 Name VARCHAR(50) NOT NULL,
5 Description VARCHAR (200) NULL);
SQL>
SQL> CREATE SEQUENCE CategoryIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category
3 FOR EACH ROW
4 BEGIN
5 SELECT CategoryIDSeq.NEXTVAL
6 INTO :NEW.CategoryID FROM DUAL;
7 END;
8 /
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, "Local", "In town");
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, "Remote", "Telecommute");
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (2, "Masks", "By bits");
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, "Wireless", "Not connected");
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, "Wired", "Connected");
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(1000) NOT NULL,
5 Price NUMBER NULL,
6 ImagePath VARCHAR(50) NULL,
7 soldout NUMBER(1,0) NULL,
8 Promotion NUMBER(1,0) NULL);
SQL>
SQL> CREATE SEQUENCE ProductIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product
3 FOR EACH ROW
4 BEGIN
5 SELECT ProductIDSeq.NEXTVAL
6 INTO :NEW.ProductID FROM DUAL;
7 END;
8 /
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler","Long",14.99, "ruler.jpg", 0, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard","keyboard",3.75, "keyboard.jpg", 0, 0);
SQL>
SQL>
SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL,
3 CategoryID INT NOT NULL,
4 PRIMARY KEY (ProductID, CategoryID)
5 );
SQL>
SQL>
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);
SQL>
SQL>
SQL> SELECT Department.Name AS "Department",
2 COUNT(Product.Name) AS "Featured Products"
3 FROM Product
4 INNER JOIN ProductCategory
5 ON Product.ProductID = ProductCategory.ProductID
6 INNER JOIN Category
7 ON ProductCategory.CategoryID = Category.CategoryID
8 INNER JOIN Department
9 ON Category.DepartmentID = Department.DepartmentID
10 WHERE Product.Promotion = 1
11 GROUP BY Department.Name
12 ORDER BY Department.Name;
Hardware
1
Software
1
SQL>
SQL>
SQL>
SQL> drop table department;
SQL> drop sequence DepartmentIDSeq;
SQL> drop table Product;
SQL> drop table ProductCategory;
SQL> drop table Category;
SQL> drop sequence CategoryIDSeq;
SQL> drop sequence ProductIDSeq;
How Many Products By Department with JOINs
SQL>
SQL>
SQL> CREATE TABLE Department (
2 DepartmentID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(200) NULL);
SQL>
SQL> CREATE SEQUENCE DepartmentIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger
2 BEFORE INSERT ON Department
3 FOR EACH ROW
4 BEGIN
5 SELECT DepartmentIDSeq.NEXTVAL
6 INTO :NEW.DepartmentID FROM DUAL;
7 END;
8 /
SQL>
SQL> INSERT INTO Department (Name, Description)
2 VALUES ("Software", "Coding");
SQL> INSERT INTO Department (Name, Description)
2 VALUES ("Hardware", "Building");
SQL> INSERT INTO Department (Name, Description)
2 VALUES ("QA", "Testing");
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY,
3 DepartmentID INT NOT NULL,
4 Name VARCHAR(50) NOT NULL,
5 Description VARCHAR (200) NULL);
SQL>
SQL> CREATE SEQUENCE CategoryIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category
3 FOR EACH ROW
4 BEGIN
5 SELECT CategoryIDSeq.NEXTVAL
6 INTO :NEW.CategoryID FROM DUAL;
7 END;
8 /
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, "Local", "In town");
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, "Remote", "Telecommute");
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (2, "Masks", "By bits");
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, "Wireless", "Not connected");
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, "Wired", "Connected");
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(1000) NOT NULL,
5 Price NUMBER NULL,
6 ImagePath VARCHAR(50) NULL,
7 soldout NUMBER(1,0) NULL,
8 Promotion NUMBER(1,0) NULL);
SQL>
SQL> CREATE SEQUENCE ProductIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product
3 FOR EACH ROW
4 BEGIN
5 SELECT ProductIDSeq.NEXTVAL
6 INTO :NEW.ProductID FROM DUAL;
7 END;
8 /
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Ruler","Long",14.99, "ruler.jpg", 0, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Keyboard","keyboard",3.75, "keyboard.jpg", 0, 0);
SQL>
SQL>
SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL,
3 CategoryID INT NOT NULL,
4 PRIMARY KEY (ProductID, CategoryID)
5 );
SQL>
SQL>
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);
SQL>
SQL> SELECT Department.Name AS "Department",
2 COUNT(Product.Name) AS "Products"
3 FROM Product
4 INNER JOIN ProductCategory
5 ON Product.ProductID = ProductCategory.ProductID
6 INNER JOIN Category
7 ON ProductCategory.CategoryID = Category.CategoryID
8 INNER JOIN Department
9 ON Category.DepartmentID = Department.DepartmentID
10 GROUP BY Department.Name
11 ORDER BY Department.Name;
Hardware
4
QA
1
Software
3
SQL>
SQL>
SQL> drop table department;
SQL> drop sequence DepartmentIDSeq;
SQL> drop table Product;
SQL> drop table ProductCategory;
SQL> drop table Category;
SQL> drop sequence CategoryIDSeq;
SQL> drop sequence ProductIDSeq;
Joining table to use between ... and clause
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> create table salary
2 ( grade NUMBER(2) constraint S_PK
3 primary key
4 , lowerlimit NUMBER(6,2)
5 , upperlimit NUMBER(6,2)
6 , bonus NUMBER(6,2)
7 , constraint S_LO_UP_CHK check
8 (lowerlimit <= upperlimit)
9 ) ;
Table created.
SQL>
SQL> insert into salary values (1, 700,1200, 0);
1 row created.
SQL> insert into salary values (2, 1201,1400, 50);
1 row created.
SQL> insert into salary values (3, 1401,2000, 100);
1 row created.
SQL> insert into salary values (4, 2001,3000, 200);
1 row created.
SQL> insert into salary values (5, 3001,9999, 500);
1 row created.
SQL>
SQL> select e.ename emp
2 , 12*e.sal+s.bonus total_salary
3 from emp e
4 , salary s
5 where e.sal between s.lowerlimit
6 and s.upperlimit;
EMP TOTAL_SALARY
-------- ------------
Tom 9600
Ana 13200
Jane 9600
Wil 15050
Mary 15050
Mike 15650
Jack 19300
Take 18100
Jane 35900
Black 34400
Chris 29600
Smart 36200
Fake 36200
Peter 60500
14 rows selected.
SQL> drop table emp;
Table dropped.
SQL> drop table salary;
Table dropped.
Join table using
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL> create table history
2 ( empno NUMBER(4)
3 , beginyear NUMBER(4)
4 , begindate DATE
5 , enddate DATE
6 , deptno NUMBER(2)
7 , sal NUMBER(6,2)
8 , comments VARCHAR2(60)
9 , constraint H_PK primary key(empno,begindate)
10 , constraint H_BEG_END check (begindate < enddate)
11 ) ;
Table created.
SQL>
SQL>
SQL> alter session set NLS_DATE_FORMAT="DD-MM-YYYY";
Session altered.
SQL>
SQL> insert into history values (1,2000,"01-01-2000","01-02-2000",40, 950,"");
1 row created.
SQL> insert into history values (1,2000,"01-02-2000", NULL ,20, 800,"restarted");
1 row created.
SQL> insert into history values (2,2009,"01-11-2009", NULL ,30,1600,"just hired");
1 row created.
SQL> insert into history values (3,1987,"01-08-1987","01-01-1989",30,1000,"On training");
1 row created.
SQL> insert into history values (3,2000,"01-02-2000", NULL ,30,1250,"");
1 row created.
SQL>
SQL> select e.ename, e.bdate
2 , h.deptno, h.sal
3 from emp e
4 join
5 history h
6 using (empno)
7 where e.job = "Manager";
no rows selected
SQL>
SQL> drop table history;
Table dropped.
SQL> drop table emp;
Table dropped.
Join with a subquery
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL> select e.ename, e.init, e.sal
2 from emp e
3 join
4 (select x.deptno
5 , avg(x.sal) avg_sal
6 from emp x
7 group by x.deptno) g
8 using (deptno)
9 where e.sal > g.avg_sal;
ENAME INIT SAL
-------- ----- ----------
Peter CC 5000
Jane JM 2975
Fake MG 3000
Smart SCJ 3000
Jack JAM 1600
Black R 2850
6 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Nested Multi-Table Equi-Joins
SQL>
SQL>
SQL> CREATE TABLE empExam (
2 empID INT NOT NULL,
3 ExamID INT NOT NULL,
4 Mark INT,
5 Taken SMALLINT,
6 Comments VARCHAR(255),
7 CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));
Table created.
SQL>
SQL>
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,"Satisfactory");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,"Good result");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,"Hard");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,"Simple");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE Exam (
2 ExamID INT NOT NULL PRIMARY KEY,
3 CourseID INT NOT NULL,
4 InstructorID INT NOT NULL,
5 SustainedOn DATE,
6 Comments VARCHAR(255));
SQL>
SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor");
1 row created.
SQL>
SQL> SELECT empExam.ExamID,
2 empExam.Mark,
3 Exam.SustainedOn,
4 emp.Name
5 FROM Exam
6 INNER JOIN (emp
7 INNER JOIN empExam ON emp.empID = empExam.empID)
8 ON Exam.ExamID = empExam.ExamID
9 ORDER BY empExam.ExamID;
EXAMID MARK SUSTAINED
---------- ---------- ---------
NAME
--------------------------------------------------
1 55 12-MAR-03
Tom
2 73 13-MAR-03
Tom
3 44 11-MAR-03
Jack
5 39 19-MAR-03
Jack
4 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table empExam;
Table dropped.
SQL> drop table exam;
Table dropped.
Non-Equi Joins
SQL>
SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY,
3 Comments VARCHAR(50),
4 Capacity INT);
Table created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,"Languages Room 1",75);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY,
3 CourseID INT NOT NULL,
4 InstructorID INT NOT NULL,
5 RoomID INT NOT NULL,
6 Time VARCHAR(50));
Table created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00");
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00");
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00");
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00");
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (5,5,2,9,"Tue 14:00-16:00");
1 row created.
SQL>
SQL>
SQL> SELECT Room.RoomID, Class.Time
2 FROM Room JOIN Class ON Room.RoomID <> Class.RoomID;
ROOMID TIME
---------- --------------------------------------------------
1 Mon 09:00-11:00
2 Mon 09:00-11:00
3 Mon 09:00-11:00
4 Mon 09:00-11:00
5 Mon 09:00-11:00
1 Mon 11:00-12:00, Thu 09:00-11:00
2 Mon 11:00-12:00, Thu 09:00-11:00
3 Mon 11:00-12:00, Thu 09:00-11:00
4 Mon 11:00-12:00, Thu 09:00-11:00
1 Mon 14:00-16:00
2 Mon 14:00-16:00
4 Mon 14:00-16:00
5 Mon 14:00-16:00
1 Tue 10:00-12:00, Thu 14:00-15:00
3 Tue 10:00-12:00, Thu 14:00-15:00
4 Tue 10:00-12:00, Thu 14:00-15:00
5 Tue 10:00-12:00, Thu 14:00-15:00
1 Tue 14:00-16:00
2 Tue 14:00-16:00
3 Tue 14:00-16:00
4 Tue 14:00-16:00
5 Tue 14:00-16:00
22 rows selected.
SQL>
SQL>
SQL> drop table room;
Table dropped.
SQL> drop table class;
Table dropped.
Supplying Table Aliases
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL>
SQL> -- Supplying Table Aliases
SQL>
SQL> SELECT emp.ename, dept.dname
2 FROM emp, dept
3 WHERE emp.deptno = dept.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
14 rows selected.
SQL>
SQL>
SQL>
SQL> drop table dept;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
The query shows that the join is performed with the other WHERE conditions
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values (101,"Jason",to_date("19960725","YYYYMMDD"), 34000, 44000 "W")
3 /
values (101,"Jason",to_date("19960725","YYYYMMDD"), 34000, 44000 "W")
*
ERROR at line 2:
ORA-00917: missing comma
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
Hit a key to continue
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- the query shows that the join is performed with the other WHERE conditions:
SQL>
SQL> SELECT e.empno, e.ename, j.jobtitle, e.orig_salary
2 FROM employee e, job j
3 WHERE e.orig_salary < 43000
4 AND e.empno = j.empno;
Hit a key to continue
EMPNO ENAME JOBTITLE ORIG_SALARY
---------- ---------- ---------- -----------
123 James Dediator 23000
105 Robert Accountant 31000
108 Jode Developer 21000
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
SQL>
Used a GROUP BY in a query with no ordering or analytical function
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
Hit a key to continue
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- Used a GROUP BY in a query with no ordering or analytical function:
SQL>
SQL> SELECT j.jobtitle, COUNT(*), MAX(orig_salary) maxsalary,
2 MIN(orig_salary) minsalary
3 FROM employee e, job j
4 WHERE e.orig_salary < 43000
5 AND e.empno = j.empno
6 GROUP BY j.jobtitle;
Hit a key to continue
JOBTITLE COUNT(*) MAXSALARY MINSALARY
---------- ---------- ---------- ----------
Dediator 1 23000 23000
Accountant 1 31000 31000
Developer 1 21000 21000
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>