Practical 1: SQL for Beginners - Data Manipulation and Retrieval
2025, March 17
This document is intended to help you understand the practical actions performed by each part of the SQL code performed in this practical.
This explanation is designed for beginners with no prior knowledge of SQL, so that when you read the actual code later, you understand exactly what each part is meant to do.
1. Data Modification and Retrieval
UPDATE Statement
- What It Does:
TheUPDATEcommand is used to change existing data in a table. In our example, it sets the department number (deptno) to a null value for the employee whose name matches a specific condition. - How It Works:
Imagine you have a list of employees. If you want to “erase” the department information for the employee named 'KING', you run anUPDATEcommand that tells the database, “For every employee with the name 'KING', remove (or set to null) the department number.”
SELECT Statement
- What It Does: The
SELECTstatement is used to retrieve data from one or more tables. - How It Works: Think of
SELECTas asking the database, “Show me these pieces of information.” You can ask for all the data (all columns and rows) or just specific columns, and you can also choose to show only rows that meet certain conditions.
2. Filtering and Pattern Matching
WHERE Clause
- What It Does:
TheWHEREclause filters the data by specifying conditions that the rows must meet. - How It Works:
It’s like using a filter on a spreadsheet. For example, if you only want to see employees whose salary is greater than a specific amount, you would include aWHEREclause to check that condition.
LIKE Operator with Wildcards
- What It Does:
TheLIKEoperator allows you to search for patterns within text. - How It Works:
- The
%symbol represents any sequence of characters (it’s like saying “anything goes here”). - The
_symbol represents any single character.
For example,ename like 'M_R_IN'means you are looking for names that start with "M," have any single character in the second spot, then "R," another single character, and end with "IN" (which would match "MARTIN").
- The
3. Sorting Results
ORDER BY Clause
- What It Does:
TheORDER BYclause arranges the results in a specific order—either ascending (smallest to largest, or alphabetically) or descending (largest to smallest, or reverse alphabetical). - How It Works:
Imagine a list of employees. If you want to see them ordered by their salary or by their names, you would useORDER BYto specify that order. You can even order by a column's number (for example, order by the second column in the result).
4. Grouping and Aggregation
GROUP BY Clause
- What It Does:
GROUP BYcollects rows that have the same values in specified columns into summary rows—like grouping employees by department. - How It Works:
Once the rows are grouped, you can use aggregate functions to calculate totals, averages, counts, etc., for each group.
Aggregate Functions
- SUM, AVG, MAX, MIN, COUNT:
These functions perform calculations on sets of rows.- SUM: Adds up all the values in a column.
- AVG: Calculates the average value.
- MAX: Finds the highest value.
- MIN: Finds the lowest value.
- COUNT: Counts the number of rows.
- How It Works:
For example, if you group employees by department, you might want to know the total salary expense (SUM), the average salary (AVG), or how many employees there are (COUNT) in each department.
HAVING Clause
- What It Does:
TheHAVINGclause filters groups based on aggregate function results, similar to howWHEREfilters individual rows. - How It Works:
For example, after grouping employees by department, you can useHAVINGto show only those departments where the average salary is above 2000.
5. Joining Tables
Inner Join
- What It Does:
An inner join combines rows from two tables only when there is a match in both tables based on a related column. - How It Works:
Imagine two lists—one for employees and one for departments. An inner join will show only the employees who belong to a department that exists in the department list.
Outer Joins (Left, Right, Full)
- Left Outer Join:
Returns all rows from the left table and the matched rows from the right table. If there’s no match, the right side will show null. - Right Outer Join:
Returns all rows from the right table and the matched rows from the left table. - Full Outer Join:
Returns rows when there is a match in either table, showing nulls where no match exists. - How It Works:
These joins help you see all data from one or both tables, even if some records don’t have a corresponding match in the other table.
Natural Join
- What It Does:
A natural join automatically joins tables based on columns with the same name and compatible data types in both tables. - How It Works:
It’s like a shortcut where you don’t have to specify the join condition manually; the database figures it out based on common column names.
Self Join
- What It Does:
A self join is when you join a table with itself. This is useful for finding relationships within the same table, such as linking employees with their managers. - How It Works:
For example, if each employee record contains a manager’s employee number, you can join the table to itself to create a list that shows each employee alongside their manager’s name.
6. Subqueries
Scalar Subqueries
- What It Does:
A subquery is a query nested inside another query. Scalar subqueries return a single value that is then used by the outer query. - How It Works:
Think of it as a mini-question that provides an answer used in a bigger question. For example, you might want to compare each employee's salary to the highest salary in the table. The subquery finds the maximum salary, and then the outer query retrieves all rows that match this value.
Putting It All Together
When you see the code, you're looking at a set of SQL operations that together allow you to change, retrieve, summarize, and combine data from one or more tables. Each part of the code is a building block that, when combined, makes powerful data manipulation and retrieval possible:
- Data is being modified (with
UPDATE), - Filtered (using
WHEREandLIKE), - Sorted (with
ORDER BY), - Grouped (using
GROUP BYalong with aggregate functions andHAVING), - Joined (using various types of joins like inner, outer, and self joins), and
- Queried with nested questions (via subqueries),
SQL> update emp set deptno = null where ename like 'KING';
1 row updated.
SQL> clear scree
SQL> update emp set deptno = null where ename like 'KING';
1 row updated.
SQL> select *from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7566 JONES MANAGER 7839 02-APR-81 3375 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
14 rows selected.
SQL> select * from emp where ename like '%R';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
SQL> select * from emp where ename like 'M%R';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1700 10
SQL> select * from emp where ename like 'M_R_IN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
SQL> select * from emp where ename = 'M_R_IN';
no rows selected
SQL> select * from emp order by sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7566 JONES MANAGER 7839 02-APR-81 3375 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7839 KING PRESIDENT 17-NOV-81 5400
14 rows selected.
SQL> select * from emp order by sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7566 JONES MANAGER 7839 02-APR-81 3375 20
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7369 SMITH CLERK 7902 17-DEC-80 1200 20
14 rows selected.
SQL> select * from emp order by 2 desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7934 MILLER CLERK 7782 23-JAN-82 1700 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7839 KING PRESIDENT 17-NOV-81 5400
7566 JONES MANAGER 7839 02-APR-81 3375 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
14 rows selected.
SQL> select * from emp order by deptno, sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1700 10
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7566 JONES MANAGER 7839 02-APR-81 3375 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7839 KING PRESIDENT 17-NOV-81 5400
14 rows selected.
SQL> select * from emp order by deptno desc, sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7566 JONES MANAGER 7839 02-APR-81 3375 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7934 MILLER CLERK 7782 23-JAN-82 1700 10
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
14 rows selected.
SQL> select * from emp order by sal where deptno = 30;
select * from emp order by sal where deptno = 30
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from emp where deptno = 30 order by sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
6 rows selected.
SQL> select deptno, sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
5400
30 11800
10 4550
20 12875
SQL> select deptno, sum(sal), avg(sal), max(sal), min(sal), count(*) from emp group by deptno;
DEPTNO SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL) COUNT(*)
---------- ---------- ---------- ---------- ---------- ----------
5400 5400 5400 5400 1
30 11800 1966.66667 3250 1350 6
10 4550 2275 2850 1700 2
20 12875 2575 3400 1200 5
SQL> select deptno, count(*) from emp where sal >2000 group by deptno;
DEPTNO COUNT(*)
---------- ----------
1
30 1
10 1
20 3
SQL> select deptno, count(*) from emp group by deptno having avg(sal)>2000;
DEPTNO COUNT(*)
---------- ----------
1
10 2
20 5
SQL> select deptno, job , sum(sal) from emp group by deptno, job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
PRESIDENT 5400
30 MANAGER 3250
10 MANAGER 2850
20 MANAGER 3375
20 ANALYST 6800
20 CLERK 2700
30 SALESMAN 7200
30 CLERK 1350
10 CLERK 1700
9 rows selected.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7566 JONES MANAGER 7839 02-APR-81 3375 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
14 rows selected.
SQL> select * from emp where sal>2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7566 JONES MANAGER 7839 02-APR-81 3375 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
6 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7566 JONES MANAGER 7839 02-APR-81 3375 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select ename, dname from emp inner join dept on emp.deptno = dept.deptno;
ENAME DNAME
---------- --------------
BLAKE SALES
CLARK ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
SMITH RESEARCH
ALLEN SALES
WARD SALES
MARTIN SALES
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
---------- --------------
JAMES SALES
MILLER ACCOUNTING
13 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7566 JONES MANAGER 7839 02-APR-81 3375 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
14 rows selected.
SQL> select ename, dname from emp inner join dept on emp.deptno = dept.deptno;
ENAME DNAME
---------- --------------
BLAKE SALES
CLARK ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
SMITH RESEARCH
ALLEN SALES
WARD SALES
MARTIN SALES
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
---------- --------------
JAMES SALES
MILLER ACCOUNTING
13 rows selected.
SQL> select ename, dname from emp left outer join dept on emp.deptno = dept.deptno;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
SMITH RESEARCH
ADAMS RESEARCH
BLAKE SALES
ALLEN SALES
WARD SALES
MARTIN SALES
ENAME DNAME
---------- --------------
TURNER SALES
JAMES SALES
KING
14 rows selected.
SQL> select ename, dname from emp right outer join dept on emp.deptno = dept.deptno;
ENAME DNAME
---------- --------------
BLAKE SALES
CLARK ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
SMITH RESEARCH
ALLEN SALES
WARD SALES
MARTIN SALES
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
---------- --------------
JAMES SALES
MILLER ACCOUNTING
OPERATIONS
14 rows selected.
SQL> select ename, dname from emp full outer join dept on emp.deptno = dept.deptno;
ENAME DNAME
---------- --------------
KING
BLAKE SALES
CLARK ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
SMITH RESEARCH
ALLEN SALES
WARD SALES
MARTIN SALES
TURNER SALES
ENAME DNAME
---------- --------------
ADAMS RESEARCH
JAMES SALES
MILLER ACCOUNTING
OPERATIONS
15 rows selected.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7566 JONES MANAGER 7839 02-APR-81 3375 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
14 rows selected.
SQL> select w.ename worker, m.ename manager from emp w inner join emp m where w.mgr = m.empno;
select w.ename worker, m.ename manager from emp w inner join emp m where w.mgr = m.empno
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> select w.ename worker, m.ename manager from emp w inner join emp m on w.mgr = m.empno;
WORKER MANAGER
---------- ----------
BLAKE KING
CLARK KING
JONES KING
ALLEN BLAKE
WARD BLAKE
MARTIN BLAKE
TURNER BLAKE
JAMES BLAKE
MILLER CLARK
SCOTT JONES
FORD JONES
WORKER MANAGER
---------- ----------
ADAMS SCOTT
SMITH FORD
13 rows selected.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7566 JONES MANAGER 7839 02-APR-81 3375 20
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
7369 SMITH CLERK 7902 17-DEC-80 1200 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2000 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1650 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1650 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1900 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1500 20
7900 JAMES CLERK 7698 03-DEC-81 1350 30
7934 MILLER CLERK 7782 23-JAN-82 1700 10
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select ename, dname from emp, dept;
ENAME DNAME
---------- --------------
KING ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
JONES ACCOUNTING
SCOTT ACCOUNTING
FORD ACCOUNTING
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
MARTIN ACCOUNTING
TURNER ACCOUNTING
ENAME DNAME
---------- --------------
ADAMS ACCOUNTING
JAMES ACCOUNTING
MILLER ACCOUNTING
KING RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
SMITH RESEARCH
ALLEN RESEARCH
ENAME DNAME
---------- --------------
WARD RESEARCH
MARTIN RESEARCH
TURNER RESEARCH
ADAMS RESEARCH
JAMES RESEARCH
MILLER RESEARCH
KING SALES
BLAKE SALES
CLARK SALES
JONES SALES
SCOTT SALES
ENAME DNAME
---------- --------------
FORD SALES
SMITH SALES
ALLEN SALES
WARD SALES
MARTIN SALES
TURNER SALES
ADAMS SALES
JAMES SALES
MILLER SALES
KING OPERATIONS
BLAKE OPERATIONS
ENAME DNAME
---------- --------------
CLARK OPERATIONS
JONES OPERATIONS
SCOTT OPERATIONS
FORD OPERATIONS
SMITH OPERATIONS
ALLEN OPERATIONS
WARD OPERATIONS
MARTIN OPERATIONS
TURNER OPERATIONS
ADAMS OPERATIONS
JAMES OPERATIONS
ENAME DNAME
---------- --------------
MILLER OPERATIONS
56 rows selected.
SQL> select ename, dname from emp natural join dept;
ENAME DNAME
---------- --------------
BLAKE SALES
CLARK ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
SMITH RESEARCH
ALLEN SALES
WARD SALES
MARTIN SALES
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
---------- --------------
JAMES SALES
MILLER ACCOUNTING
13 rows selected.
SQL> select * from student;
ROLL NAME CITY
---------- ---------- ----------
1 Roshan Bangluru
2 Srijan Dhanbad
SQL> select dname, name from dept natural join student;
DNAME NAME
-------------- ----------
ACCOUNTING Roshan
RESEARCH Roshan
SALES Roshan
OPERATIONS Roshan
ACCOUNTING Srijan
RESEARCH Srijan
SALES Srijan
OPERATIONS Srijan
8 rows selected.
SQL> select d1.dname, d2.dname from dept d1 natural join dept d2;
select d1.dname, d2.dname from dept d1 natural join dept d2
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
SQL> select max(sal) from emp;
MAX(SAL)
----------
5400
SQL> select * from emp where sal = (select max(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5400
SQL> select * from emp e where sal = (select max(sal) from emp f where e.deptno = f.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 3250 30
7782 CLARK MANAGER 7839 09-JUN-81 2850 10
7788 SCOTT ANALYST 7566 19-APR-87 3400 20
7902 FORD ANALYST 7566 03-DEC-81 3400 20
SQL> select w.ename worker, m.ename manager from emp w inner join emp m on w.mgr = m.empno;
WORKER MANAGER
---------- ----------
BLAKE KING
CLARK KING
JONES KING
ALLEN BLAKE
WARD BLAKE
MARTIN BLAKE