SQL Tutorial

Table (Entity)

It is 2-dimensional (Columns and Rows)

Columns (Attributes or Fields)

Rows (Records or Tuples)
Step1: Login to the User Terminal

Login

User Name: hr

Password : hr
Display the list of table’s availbale to the user?

SQL> SELECT * FROM tab;

Display the structure of the employees table?

SQL> DESCRIBE employees;

SQL> DESC employees;

SELECT statement capabilities

PROJECTION

SELECTION

JOIN

Projection:

Working on Columns

SELECT statement Syntax:

SELECT * FROM <table name>;



Note:

SELECT –> we have to specify the column list

FROM   –> we have to specify the table name

*  –> Denotes all columns
SQL> SELECT * FROM regions;

SELECT statement Syntax:

SELECT col_name1, column_name2….   FROM <table name>;



Note:

SELECT –> we have to specify the column list

FROM –> we have to specify the table name
Each column name should be separated by comma (It is mandatory else it results in column alias)

SQL> SELECT region_id, region_name  FROM regions;

SQL> SELECT employee_id, salary FROM employees;

SQL> DESC jobs;

SQL> SELECT job_id, job_title FROM jobs;
Arithmetic Expressions:

We can create arithmetic expressions with numeric & date data types

Arithmetic Opearators available

Addition (+)

Subtraction (-)

Multiplication (*)

Division (/)
SQL> SELECT salary, salary+100, salary-100, salary*100, salary/100 FROM employees;



Note:    Salary +100 is an arithmetic expression

It is temporary for display purpose only



Operator Precedence

Use parenthesis to overcome operator precedence

SQL> SELECT salary, salary+100*10, (salary+100)*10

FROM employees;

SQL> SELECT employee_id, job_id, commission_pct  FROM employees;



NULL Value

Undefined or Unassigned or Unknown or Inapplicable

Not equal to space or zero

NULL Values IN Arithmetic Expressions

Note:  Arithmetic expressions containing a NULL value evaluate to NULL

NULL + 100   –> NULL

NULL * 100  –>  NULL

NULL – 100  –>  NULL

NULL / 100   –>  NULL
SQL> SELECT employee_id, salary, commission_pct, commission_pct+100 FROM employees;

SQL> SELECT last_name, job_id, salary, salary*12 FROM employees;



Column Alias

Renaming a column name temporarily

Mainly useful with expressions
SQL> SELECT last_name, job_id, salary, salary*12 as annsal FROM employees;



Note: 

Here annsal is the column alias

AS keyword is optional

Column alias are also called as Indents

SQL> SELECT last_name, job_id, salary, salary*12 annsal  FROM employees;

SQL> SELECT last_name, job_id, salary, salary*12 “annsal”   FROM employees;

SQL> SELECT last_name, job_id, salary, salary*12 “ann sal”  FROM employees;

SQL> SELECT last_name, job_id, salary, salary*12 ann_sal  FROM employees;

SQL> SELECT last_name, job_id, salary, salary*12 “ann_sal”  FROM employees;



Concatenation Operator

Links columns or character strings to other columns

Is represented by two vertical bars (||)
Linking column with a column

SQL> SELECT first_name, last_name, last_name||first_name “Full_name”  FROM employees;



String

Means literal

A literal may be a character or number or date

Character & Date literals should be enclosed in single quotation marks

SQL> SELECT employee_id || ‘ salary is ‘ || salary  FROM employees;

SQL> SELECT last_name || ‘ his’s ‘ || last_name  FROM employees;



Alternate Quote Operator

Represented by q

We can use any delimiter (Delimiter is [] )

Introduced from oracle 10g
SQL> SELECT last_name ||q'[his’s]’|| salary FROM employees;

SQL> SELECT department_id FROM employees;

Distinct

Used to display unique values in a column

Distinct on a Single Column

SQL> SELECT DISTINCT department_id  FROM employees;

SQL> SELECT department_id, job_id  FROM employees;

Distinct on a Multiple Columns

SQL> SELECT DISTINCT department_id, job_id  FROM employees;

Quiz:

  1. What is table?
  2. What is column?
  3. What is row?
  4. What are the capabilities of select statement?
  5. What is projection?
  6. Explain the select syntax for projection for all columns or particular columns?
  7. What are arithmetic expressions?
  8. Which columns support Arithmetic expressions?
  9. What is the arithmetic expressions can be performed on columns?
  10. What is column alias?
  11. What is the advantage of column alias?
  12. What is concatenation operator?
  13. What is the usage of distinct with columns?
  14. What is the usage of alternate quote operator?



Practice 1: Test your knowledge:

  1. The following SELECT statement executes successfully:

SELECT last_name, job_id, salary AS Sal  FROM employees;

True/False

  1. The following SELECT statement executes successfully:

SELECT *  FROM job_grades;

True/False

  1. There are four coding errors in the following statement. Can you identify them?

SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees;

You have been hired as a SQL programmer for XYZ Corporation. Your first task is to create some reports

based on data from the Human Resources tables.

  1. Your first task is to determine the structure of the DEPARTMENTS table and its contents.
  2. You need to determine the structure of the EMPLOYEES table.
  3. The HR department wants a query to display the last name, job code, hire date, and employee number

for each employee, with the employee number appearing first. Provide an alias STARTDATE for the HIRE_DATE column

  1. The HR department needs a query to display all unique job codes from the EMPLOYEES Table
  2. The HR department has requested a report of all employees and their job IDs. Display the last name

concatenated with the job ID (separated by a comma and space) and name the column Employee and Title.

  1. To familiarize yourself with the data in the EMPLOYEES table, create a query to display all the data

from the EMPLOYEES table. Separate each column output with a comma. Name the column THE_OUTPUT.
SELECTION (FILTERING rows)

WHERE clause

Used to filter the rows

Syntax: Condition: column name operator value

Operator

Relational Operators

Logical Operators

Other Operators

Relational Operators: >, >=, <, <=, =, !=

Working on Numeric Data Type

SQL> SELECT employee_id, salary, department_id  FROM employees WHERE department_id=30;

SQL> SELECT employee_id, salary, department_id FROM employees WHERE department_id < 30;

SQL> SELECT employee_id,salary,department_id FROM employees WHERE department_id <=30;

SQL> SELECT employee_id,salary,department_id FROM employees WHERE department_id != 30;

SQL> SELECT employee_id,salary,department_id  FROM employees WHERE department_id > 100;

SQL> SELECT employee_id,salary,department_id  FROM employees WHERE department_id >= 100;



Working on Character Data Type

SQL> SELECT employee_id, salary FROM employees WHERE last_name=’Zlotkey’;

Note:

Characters are case sensitive

Dates are format sensitive (Default format is DD-MON-RR)

Characters & dates should be enclosed in single quotes

Working on Date Data Type

SQL> SELECT last_name,hire_date

FROM employees  WHERE hire_date= ’21-JUN-07′;

BETWEEN Condition

Use the BETWEEN condition to display rows based on a range of values

Range means lower value followed by upper value

Use with numeric or date data types

BETWEEN Condition with NUMERIC data type

SQL> SELECT last_name, salary  FROM employees  WHERE salary BETWEEN 15000 AND 25000;

BETWEEN Condition with DATE data type

SQL> SELECT last_name, hire_date  FROM employees WHERE hire_date BETWEEN ’01-JAN-97′ and ’31-DEC-07′;

IN Condition Example

Use the IN membership condition to test for values in a list

Can be used with Character or Numeric or Date data types

IN Condition with NUMERIC data type

SQL> SELECT last_name,salary,department_id

FROM employees WHERE department_id IN (10, 20);

IN Condition with CHARACTER data type

SQL> SELECT last_name,salary,department_id

FROM employees WHERE last_name in (‘Whalen’,’Fay’);

IN Condition with DATE data type

SQL> SELECT last_name,salary,hire_date

FROM employees WHERE hire_date in (’21-JUN-07′,’13-JAN-08′);

LIKE Condition

Used for Searching the string

Options

Percentile(%) and Underscore(_)

Percentile (%)

Indicates zero or many characters

Underscore (_)

Indicates single character

Displaying last name letter starting with G?

SQL> SELECT last_name,salary FROM employees WHERE last_name like ‘G%’;

Displaying last name letter ending with e?

SQL> SELECT last_name,salary FROM employees WHERE last_name like ‘%e’;

Displaying last name containing the letter e?

SQL> SELECT last_name,salary FROM employees WHERE last_name like ‘%e%’;

Displaying employees joined in the year 07?

SQL> SELECT last_name, salary, hire_date FROM employees WHERE hire_date like ‘%07’;

Displaying last name of the employees starting with letter A , second character may be

anything but the third character should be e?

SQL> SELECT last_name, salary FROM employees WHERE last_name like ‘A_e%’;

Working with NULL values

SQL> SELECT salary,commission_pct FROM employees WHERE commission_pct=NULL;



Note:

It displays no rows

Using the NULL Conditions

Test for NULLs with the IS NULL or IS NOT NULL operator

SQL> SELECT salary, commission_pct FROM employees WHERE commission_pct IS NULL;

SQL> SELECT salary, commission_pct FROM employees

WHERE commission_pct IS NOT NULL;

LOGICAL Operators:

AND , OR & NOT

AND

All conditions should be true

Logical Operator AND Example:

SQL> SELECT department_id, salary FROM employees WHERE department_id > 100 AND salary > 10000;

Logical Operator OR Example:

At least one condition should be true

SQL> SELECT department_id, salary FROM employees WHERE department_id > 100 or salary > 10000;

Logical Operator NOT Example:

NOT

If input is true it gives output as false & vice-versa

SQL>SELECT department_id, salary FROM employees WHERE department_id NOT IN (10, 20, 30, 40, 50, 60, 70, 80, 90, 100);

SQL> SELECT department_id, salary FROM employees;

ORDER BY Clause:

Last clause in SELECT statement

Arranges data in ascending or descending order

By default it sorts the data in the ascending order

Used with NUMERIC or DATE data types

Sort the data on column alias, column expression or column position

Sort one single or multiple columns

Sorting on single column (Ascending order)

SQL> SELECT department_id, salary FROM employees ORDER BY department_id;

Sorting on single column (Descending order)

SQL> SELECT department_id, salary FROM employees ORDER BY department_id desc;

Sorting on multiple columns (Ascending order)

SQL> SELECT department_id, salary FROM employees ORDER BY department_id, salary;

Sorting on multiple columns (Descending order)

SQL> SELECT department_id, salary FROM employees ORDER BY department_id desc, salary desc;

Sorting on column position

SQL> SELECT department_id, salary FROM employees ORDER BY 1;

Sorting on arithmetic expression

SQL> SELECT employee_id, job_id, salary, salary*12 FROM employees ORDER BY salary*12;

Sorting on column alias

SQL> SELECT employee_id, job_id, salary, salary*12 annsal FROM employees ORDER BY annsal;

SQL> SELECT employee_id,salary,department_id FROM employees WHERE department_id=20;

Substitution Variable

To pass the value at run time

We have 2 options

Single Ampersand (&) & Double Ampersand (&&)

Single Ampersand

Asks value to enter every time

Double Ampersand

Asks value to enter only once

SET VERIFY OFF

SET VERIFY ON

DEFINE

Command used to assign a value to a variable

Undefine no

Quiz:

  1. What is selection?
  2. What is the syntax of WHERE clause?
  3. What are the operators supported in WHERE clause?
  4. What are relational operators?
  5. What are logical operators?
  6. What is BETWEEN operators?
  7. What is IN operator?
  8. What is LIKE operator?
  9. What is IS NULL operator?
  10. What are substitution variables?
  11. What is single ampersand?
  12. What is double ampersand?
  13. What is order by clause?
  14. What is define command?
  15. What is undefined command?

Practice:

The HR department needs your assistance with creating some queries.

  1. Because of budget issues, the HR department needs a report that displays the last name and salary of

employees earning more than $12,000.

  1. Create a report that displays the last name and department number for employee number 176.
  2. Display the last name and salary for all employees whose salary is not in the $5,000–$12,000 range.
  3. Create a report to display the last name, job ID, and start date for the employees whose last names

are Matos and Taylor. Order the query in ascending order by start date.

  1. Display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name.
  2. Display the last name and salary of employees who earn between $5,000 and $12,000, and are in department 20 or 50. Label the columns Employee and Monthly Salary, respectively.
  3. The HR department needs a report that displays the last name and hires date for all employees who were hired in 1994.
  4. Create a report to display the last name and job title of all employees who do not have a manager.
  5. Display the last name, salary, and commission for all employees who earn commissions. Sort data in descending order of salary and commissions?
  6. Members of the HR department want to have more flexibility with the queries that you are writing.

They would like a report that displays the last name and salary of employees who earn more than an

amount that the user specifies after a prompt.

  1. The HR department wants to run reports based on a manager. Create a query that prompts the user for a manager ID and generates the employee ID, last name, salary, and department for that manager’s employees. The HR department wants the ability to sort the report on a selected column. You can test the data with the following values:

Manager ID = 103, sorted by employee last name

Manager ID = 201, sorted by salary

Manager ID = 124, sorted by employee ID

  1. Display all employee last names in which the third letter of the name is a.
  2. Display the last names of all employees who have both an a and an e in their last names.
  3. Display the last name, job, and salary for all employees whose job is either that of a sales representative or a stock clerk, and whose salary is not equal to $2,500, $3,500, or $7,000.
  4. Display the last name, salary, and commission for all employees whose commission amount is 20%.

Single Row Functions

Single Row Functions

Manipulate data items

Accept arguments and return one value

Act on each row that is returned

Return one result per row

May modify the data type Can be nested

Accept arguments that can be a column or an expression

Syntax: function_name [(arg1, arg2,…)]

SQL> SELECT employee_id,last_name,salary FROM employees WHERE department_id=20;

SQL> SELECT employee_id,last_name,salary FROM employees WHERE last_name=’fay’;

SQL> SELECT employee_id,last_name,salary FROM employees WHERE last_name=’FAY’;

SQL> SELECT employee_id,last_name,salary FROM employees WHERE last_name=’Fay’;

Case-Conversion Functions

These functions convert the case for character strings:

LOWER

Converts to lower case

UPPER

Converts to upper case

INITCAP

Converts first letter to capital remaining letters to lower case

SQL> SELECT employee_id, last_name, LOWER (last_name), UPPER (last_name),INITCAP (salary)

FROM employees WHERE department_id=20;

LOWER

SQL> SELECT employee_id, last_name, salary FROM employees WHERE LOWER (last_name)=’fay’;

UPPER

SQL> SELECT employee_id, last_name, salary FROM employees WHERE UPPER (last_name)=’FAY’;

INITCAP

SQL> SELECT employee_id, last_name, salary FROM employees WHERE INITCAP (last_name)=’Fay’;

CONCATENATION OPERATOR (||)

SQL> SELECT last_name||first_name||salary FROM employees WHERE department_id=20;



Note:

There is not limitation for concatenation

CONCAT:

It is a single row function

We can concatenate maximum two columns

Can be used with any data type

SQL> SELECT CONCAT (first_name, last_name) FROM employees WHERE department_id=20;

LENGTH

Counts the number of characters

Works on Character or Numeric or Date data types

LENGTH on chacter data type

SQL> SELECT last_name, LENGTH (last_name) FROM employees WHERE department_id=20;

LENGTH on date data type

SQL> SELECT last_name, LENGTH (hire_date) FROM employees WHERE department_id=20;

LENGTH on numeric data type

SQL> SELECT last_name, salary, LENGTH (salary) FROM employees WHERE department_id=20;

SUBSTR:

It is used to display a part of the string

SQL> SELECT last_name, SUBSTR (last_name,1,3) FROM employees;



Note:

SUBSTR is used to display a part of the string

In the above example

1 is the starting position

3 is the no of characters to be displayed from 1st position
SQL> SELECT last_name, SUBSTR (last_name,1,5) FROM employees;

SQL> SELECT last_name, SUBSTR (last_name,2,5) FROM employees;

INSTR:

To display the numerical position of the character

We can display the first occurrence or second occurrence and so on

INSTR displaying the first occurrence of letter a

SQL> SELECT last_name, INSTR (last_name,’a’) FROM employees WHERE department_id IN (20, 30);

INSTR displaying the second occurrence of letter a

SQL> SELECT last_name, INSTR (last_name,’a’,1,2) FROM employees WHERE department_id IN (20,30);

Note:

1, 2 –> 2nd occurrence

1,3  –> 3rd occurrence

LPAD

Used for right justification

SQL> SELECT salary, LPAD (salary,6,’*’) FROM employees WHERE department_id IN (20,30);

RPAD

Used for left justification

SQL> SELECT salary,RPAD(salary,6,’*’) FROM employees WHERE department_id IN (20,30);

TRIM

Removing Leading or Trailing Characters or both but not the middle characters

Dual

It is a dummy table

It contains only one column & one row

It is owned by SYS user

We should not perform DML operations on dual table

SQL> SELECT TRIM (‘h’ FROM ‘helloworld’) FROM dual;

SQL> SELECT TRIM (‘h’ FROM ‘elloworldh’) FROM dual;

SQL> SELECT TRIM (‘h’ FROM ‘helloworldh’) FROM dual;

SQL> SELECT TRIM (‘h’ FROM ‘hellohworldh’) FROM dual;



REPLACE:

Replace the character with another character

SQL> SELECT REPLACE (‘hell’,’h’,’b’) FROM dual;

SQL> SELECT REPLACE (‘hellh’,’h’,’b’) FROM dual;

SQL> SELECT REPLACE (‘hehll’,’h’,’b’) FROM dual;

MOD

Returns remainder of division

SQL> SELECT MOD (10,2) FROM dual;

SYSDATE

It is a function which returns date & time

SQL> SELECT SYSDATE, SYSDATE+1, SYSDATE-1 FROM dual;

SQL> SELECT SYSDATE, hire_date, (SYSDATE-hire_date) “Days” FROM employees WHERE department_id=30;

SQL> SELECT SYSDATE, hire_date, ROUND (SYSDATE-hire_date) “Days” FROM employees WHERE  department_id=30;

SQL> SELECT SYSDATE, hire_date, ROUND (SYSDATE-hire_date)/7 “Weeks” FROM employees WHERE department_id=30;

SQL> SELECT SYSDATE, hire_date, ROUND ((SYSDATE-hire_date)/7) “Weeks” FROM employees WHERE department_id=30;

SQL> SELECT SYSDATE, hire_date, ROUND ((SYSDATE-hire_date)/365) “Years” FROM employees WHERE department_id=30;

SQL> SELECT SYSDATE, hire_date, ROUND (MONTHS_BETWEEN(SYSDATE,hire_date)) “Months” FROM employees WHERE department_id=30;

SQL> SELECT SYSDATE, ADD_MONTHS (SYSDATE,1),ADD_MONTHS(SYSDATE,-1) FROM dual;

SQL> SELECT SYSDATE, LAST_DAY (SYSDATE) FROM dual;

SQL> SELECT SYSDATE, NEXT_DAY (SYSDATE,’Tue’) FROM dual;

SQL> SELECT SYSDATE, ROUND (SYSDATE,’month’) FROM dual;

SQL> SELECT SYSDATE, ROUND (SYSDATE,’year’) FROM dual;

SQL> SELECT SYSDATE, TRUNC (SYSDATE,’month’) FROM dual;

SQL> SELECT SYSDATE, TRUNC (SYSDATE,’year’) FROM dual;

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,’MONTH-Month-month’) FROM dual;

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,’yyyy-yyy-yy-y’) “Year” FROM dual;

SQL> SELECT SYSDATE, TO_CHAR (SYSDATE,’month-mon-mm’) “Month” FROM dual;

SQL> SELECT SYSDATE, TO_CHAR (SYSDATE,’day-dy-dd’) “Day” FROM dual;

SQL> SELECT SYSDATE, TO_CHAR (SYSDATE,’HH24:MI:SS’) “Time” FROM dual;

SQL> SELECT SYSDATE, TO_CHAR (SYSDATE,’HH:MI:SS’) “Time” FROM dual;

SQL> SELECT SYSDATE, TO_CHAR (SYSDATE,’ww-w’) “Week” FROM dual;

SQL> SELECT SYSDATE, TO_CHAR (SYSDATE,’Q’) “Quarter” FROM dual;

SQL> SELECT hire_date, TO_CHAR (hire_date,’fmdd-mon-yyyy’) FROM employees;

SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,’dd-ddspth’) FROM dual;

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,’dd “of” month’) FROM dual;

SQL> SELECT salary,TO_CHAR(salary,’$99,99999.99′) FROM employees WHERE department_id=30;

SQL> SELECT employee_id,hire_date FROM employees WHERE hire_date < ’17-AUG-97′;

SQL> SELECT employee_id,hire_date FROM employees WHERE hire_date < to_date (’97-17-aug’,’rr-dd-mon’);



Nesting Functions

Function within a function

Can be nested to any level

SQL> SELECT CONCAT (first_name,last_name), LENGTH(CONCAT(first_name,last_name)) FROM employees WHERE department_id=20;

General Functions

Used to handle the NULL values

NVL

We can pass maximum 2 arguments

If arg1 is NULL it will be replaced by second expression(NOT NULL value)

SQL> SELECT NVL (NULL, 0) FROM dual;

SQL> SELECT commission_pct, NVL (commission_pct,0) FROM employees WHERE department_id =50;

NVL2

We can pass maximum 3 arguments

If arg1 is NULL it displays arg3

If arg1 is NOT NULL it displays arg2

SQL> SELECT NVL2 (NULL,1,2) FROM dual;

SQL> SELECT NVL2 (1,5, NULL) FROM dual;

NULLIF

We can pass maximum 2 arguments

If both arguments are equal it display NULL value

If both arguments are NOT equal it displays arg1

SQL> SELECT NULLIF (1,1) FROM dual;

SQL> SELECT NULLIF (1,0) FROM dual;

COALESCE:

No limitation on arguments

Make sure the last argument is NOT NULL

It always displays the first NOT NULL argument

SQL> SELECT COALESCE (NULL, NULL, 1) FROM dual;

SQL> SELECT COALESCE (NULL,NULL,NULL,NULL,1) FROM dual;

SQL> SELECT COALESCE (NULL,2,1) FROM dual;

SQL> SELECT COALESCE (7,8,NULL) FROM dual;

Group Functions

SQL> SELECT MAX (salary), MIN (salary), AVG (salary), SUM (salary) FROM employees;

SQL> SELECT MIN (hire_date), MAX (hire_date)  FROM employees;

SQL> SELECT COUNT (*) FROM employees;

SQL> SELECT COUNT (commission_pct) FROM employees;

SQL> SELECT COUNT (distinct commission_pct) FROM employees;

SQL> SELECT AVG (commission_pct) FROM employees;

SQL> SELECT AVG (NVL (commission_pct,0)) FROM employees;

SQL> SELECT MAX (salary) FROM employees;

SQL> SELECT MAX (salary) FROM employees GROUP BY department_id;

SQL> SELECT department_id, job_id, SUM (salary) FROM employees GROUP BY department_id,job_id;

SQL> SELECT department_id, SUM (salary) FROM employees WHERE SUM (salary) > 10000 GROUP BY department_id;

SQL> SELECT department_id, SUM (salary) FROM employees GROUP BY department_id HAVING SUM (salary) > 10000;

SQL> SELECT MAX (SUM (salary)) FROM employees GROUP BY department_id;

SQL> SELECT MAX (SUM (salary)) FROM employees;

SQL> SELECT MAX (SUM (salary)) FROM employees GROUP BY department_id;

DDL Commands:

SQL> DROP TABLE demo PURGE;

SQL>

CREATE TABLE demo

(

fname CHAR

);

SQL> DESC demo;

SQL> INSERT INTO demo (fname) VALUES (‘r’);

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> ROLLBACK;

SQL> SELECT * FROM demo;

SQL> INSERT INTO demo (fname) VALUES (‘rr’);

SQL> SELECT * FROM demo;

SQL> DROP TABLE demo PURGE;

SQL>

CREATE TABLE demo

(

fname CHAR(5)

);

SQL> DESC demo;

SQL> INSERT INTO demo (fname) VALUES (‘tommy’);

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> INSERT INTO demo (fname) VALUES (‘s’);

SQL> COMMIT;

SQL> SELECT fname,length(fname) “Lenth” FROM demo;

SQL> DROP TABLE demo PURGE;

SQL>

CREATE TABLE demo

(

fname VARCHAR2(5)

);

SQL> DESC demo;

SQL> INSERT INTO demo (fname) VALUES (‘s’);

SQL> INSERT INTO demo (fname) VALUES (‘tommy’);

SQL> COMMIT;

SQL> SELECT fname,LENGTH(fname) “Length” FROM demo;

SQL> DROP TABLE demo PURGE;

SQL>

CREATE TABLE demo

(

salary NUMBER

);

SQL> DESC demo;

SQL> INSERT INTO demo (salary) VALUES (989898);

SQL> INSERT INTO demo (salary) VALUES (9999999999999999999);

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> DROP TABLE demo PURGE;

SQL>

CREATE TABLE demo

(

salary NUMBER(4)

);

SQL> INSERT INTO demo (salary) VALUES (1234);

SQL> INSERT INTO demo (salary) VALUES (12345);

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> DROP TABLE demo PURGE;

SQL>

CREATE TABLE demo

(

salary NUMBER(7,2)

);

SQL> DESC demo;

SQL> INSERT INTO demo (salary) VALUES (98987);

SQL> INSERT INTO demo (salary) VALUES (99999.99999999999999);

SQL> INSERT INTO demo (salary) VALUES (89898.8888888888888);

SQL> COMMIT;

SQL> SELECT salary,length(salary) FROM demo;

SQL> DROP TABLE demo PURGE;

SQL>

CREATE TABLE demo

(

empno NUMBER(4),

ename VARCHAR2(30)

);

SQL> DESC demo;

SQL> INSERT INTO demo (empno,ename) VALUES (1,’sam’);

SQL> INSERT INTO demo (empno) VALUES (2);

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> INSERT INTO demo (empno,ename) VALUES (3,NULL);

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> INSERT INTO demo VALUES (4,’raj’);

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> INSERT INTO demo VALUES (‘khan’,5);

SQL> DROP TABLE demo;

SQL>

CREATE TABLE demo

(

empno NUMBER(4),

ename VARCHAR2(30) DEFAULT ‘universal’

);

SQL> DESC demo;

SQL> INSERT INTO demo (empno,ename) VALUES (1,’sam’);

SQL> INSERT INTO demo (empno) VALUES (2);

SQL> INSERT INTO demo (empno,ename) VALUES (3,NULL);

SQL> INSERT INTO demo (empno,ename) VALUES (4,default);

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> DROP TABLE demo;

SQL> DESC user_recyclebin;

SQL> SELECT object_name,original_name FROM user_recyclebin;

SQL> FLASHBACK TABLE demo to before DROP;

SQL> SELECT object_name,original_name FROM user_recyclebin;

SQL> DROP TABLE demo PURGE;

SQL> SELECT object_name,original_name FROM user_recyclebin;

SQL>

CREATE TABLE demo

(

empno NUMBER(4),

ename VARCHAR2(20)

);

SQL> DESC demo;

SQL> ALTER TABLE demo add salary NUMBER(4);

SQL> DESC demo;

SQL> ALTER TABLE demo rename column empno to employee_id;

SQL> DESC demo;

SQL> ALTER TABLE demo DROP column ename;

SQL> DESC demo;

SQL> ALTER TABLE demo ADD COLUMN job VARCHAR2 (20);

SQL> DESC demo;

SQL> ALTER TABLE demo set unused (job,salary);

SQL> DESC demo;

SQL> ALTER TABLE demo DROP unused columns;

SQL> ALTER TABLE demo add (job VARCHAR2(20),hire_date date, salary NUMBER(5));

SQL> DESC demo;

SQL> ALTER TABLE demo MODIFY job VARCHAR2 (50);

SQL> DESC demo;

SQL> ALTER TABLE demo MODIFY (job VARCHAR2(50), salary NUMBER(10));

SQL> DESC demo;

SQL> RENAME demo TO demo_new;

SQL> DESC demo;

SQL> DESC demo_new;

SQL> DROP TABLE demo_new PURGE;

SQL>

CREATE TABLE demo_emp

(

empno NUMBER(4) NOT NULL,

ename VARCHAR2(20)

);

SQL> DESC demo_emp;

SQL> INSERT INTO demo_emp VALUES (1,’sam’);

SQL> COMMIT;

SQL> SELECT * FROM demo_emp;

SQL> INSERT INTO demo_emp VALUES (NULL,’raj’);

SQL> INSERT INTO demo_emp (ename) VALUES (‘singh’);

SQL> COMMIT;

SQL> SELECT * FROM demo_emp;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DEMO_EMP’;

SQL> DROP TABLE demo_emp PURGE;

SQL>

CREATE TABLE demo_emp

(

empno NUMBER(4) constraint demo_emp_eno_uq unique,

ename VARCHAR2(20)

);

SQL> DESC demo_emp;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DEMO_EMP’;

SQL> INSERT INTO demo_emp VALUES (1,’sam’);

SQL> INSERT INTO demo_emp VALUES (1,’raj’);

SQL> INSERT INTO demo_emp VALUES (NULL,’singh’);

SQL> INSERT INTO demo_emp VALUES (NULL,’raju’);

SQL> COMMIT;

SQL> SELECT * FROM demo_emp;

SQL> DROP TABLE demo_emp PURGE;

SQL>

CREATE TABLE demo_emp

(

empno NUMBER(4),

ename VARCHAR2(20),

constraint demo_emp_uq unique(empno)

);

SQL> DESC demo_emp;

SQL> INSERT INTO demo_emp VALUES (1,’sam’);

SQL> INSERT INTO demo_emp VALUES (1,’raj’);

SQL> INSERT INTO demo_emp VALUES (NULL,’singh’);

SQL> INSERT INTO demo_emp VALUES (NULL,’raju’);

SQL> COMMIT;

SQL> SELECT * FROM demo_emp;

SQL> DROP TABLE demo_emp PURGE;

SQL>

CREATE TABLE demo_emp

(

empno NUMBER(4),

ename VARCHAR2(20),

constraint demo_emp_eno_uq unique(empno,ename)

);

SQL> DESC demo_emp;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DEMO_EMP’;

SQL> INSERT INTO demo_emp VALUES (1,’sam’);

SQL> INSERT INTO demo_emp VALUES (1,’raj’);

SQL> COMMIT;

SQL> SELECT * FROM demo_emp;

SQL> INSERT INTO demo_emp VALUES (1,’raj’);

SQL> INSERT INTO demo_emp VALUES (NULL,’singh’);

SQL> INSERT INTO demo_emp VALUES (NULL,’singh’);

SQL> INSERT INTO demo_emp VALUES (NULL,’raju’);

SQL> INSERT INTO demo_emp VALUES (NULL,NULL);

SQL> INSERT INTO demo_emp VALUES (NULL,NULL);

SQL> COMMIT;

SQL> SELECT * FROM demo_emp;

SQL> DROP TABLE demo_emp PURGE;

SQL> DROP TABLE demo_dept PURGE;

SQL>

CREATE TABLE demo_dept

(

deptno NUMBER(4) constraint demo_dept_dno PRIMARY KEY,

dname VARCHAR2(20)

);

SQL> DESC demo_dept;

SQL> INSERT INTO demo_dept VALUES (10,’IT’);

SQL> INSERT INTO demo_dept VALUES (20,’SALES’);

SQL> INSERT INTO demo_dept VALUES (10,’IT’);

SQL> INSERT INTO demo_dept VALUES (NULL,’sales’);

SQL> SELECT * FROM demo_dept;

SQL> DROP TABLE demo_emp PURGE;

SQL>

CREATE TABLE demo_emp

(

empno NUMBER(4) constraint demo_emp_eno PRIMARY KEY,

ename VARCHAR2(20),

deptno NUMBER(4) REFERENCES demo_dept(deptno)

);

SQL> DESC demo_emp;

SQL> DROP TABLE demo_emp PURGE;

SQL> DROP TABLE demo_emp PURGE;

SQL>

CREATE TABLE demo_emp

(

empno NUMBER(4) constraint demo_emp_eno PRIMARY KEY,

ename VARCHAR2(20),

deptno NUMBER(4),

constraint demo_emp_fk foreign key(deptno) REFERENCES demo_dept(deptno)

);

SQL> DESC demo_emp;

SQL> SELECT * FROM demo_dept;

SQL> INSERT INTO demo_emp VALUES (1,’raj’,10);

SQL> INSERT INTO demo_emp VALUES (2,’raju’,20);

SQL> COMMIT;

SQL> SELECT * FROM demo_emp;

SQL> INSERT INTO demo_emp VALUES (3,’raja’,30);

SQL> INSERT INTO demo_emp VALUES (3,’raja’,NULL);

SQL> COMMIT;

SQL> SELECT * FROM demo_emp;

SQL>

CREATE TABLE doom

(

empno NUMBER(4),

sal NUMBER constraint doom_sal_ck check (sal>2000)

);

SQL> DESC doom;

SQL> INSERT INTO doom VALUES (1,2001);

SQL> INSERT INTO doom VALUES (1,2000);

SQL> COMMIT;

SQL> SELECT * FROM doom;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DOOM’;

SQL> ALTER TABLE doom disable constraint DOOM_SAL_CK;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DOOM’;

SQL> ALTER TABLE doom enable constraint DOOM_SAL_CK;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DOOM’;

SQL> ALTER TABLE doom DROP constraint DOOM_SAL_CK;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DOOM’;

SQL> ALTER TABLE doom add constraint doom_sal_ck check (sal>2000);

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DOOM’;

SQL> DROP TABLE demo PURGE;

SQL>

CREATE TABLE demo

(

empno NUMBER(4),

ename VARCHAR2(10),

job VARCHAR2(10),

salary NUMBER(5)

);

SQL> DESC demo;

SQL> ALTER TABLE demo modify empno not NULL;

SQL> DESC demo;

SQL> ALTER TABLE demo MODIFY empno NULL;

SQL> DESC demo;

SQL> ALTER TABLE demo ADD CONSTRAINT de_eno_pk PRIMARY KEY(empno);

SQL> DESC demo;

SQL> DROP TABLE demo PURGE;

SQL> CREATE TABLE demo AS SELECT * FROM employees;

SQL> DESC employees;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’EMPLOYEES’;

SQL> SELECT TABLE_name,column_name,constraint_name FROM user_cons_columns WHERE TABLE_name=’DEMO’;

SQL> CREATE TABLE demo1 AS SELECT employee_id empno,salary*12 annsal FROM demo;

SQL> DESC demo1;

SQL> ALTER TABLE demo1 add job VARCHAR2(20) default ‘sales’;

SQL> DESC demo1;

SQL> DROP TABLE demo PURGE;

SQL> DROP TABLE demo1 PURGE;

SQL>

CREATE TABLE demo

(

empno NUMBER(4),

Hdate date

);

SQL> INSERT INTO demo VALUES (1,SYSDATE);

SQL> INSERT INTO demo VALUES (2,to_date(‘2011-january-19′,’yyyy-month-dd’));

SQL> COMMIT;

SQL> SELECT * FROM demo;

SQL> DROP TABLE demo PURGE;

SQL> CREATE TABLE demo AS SELECT * FROM employees;

SQL> SELECT salary FROM demo WHERE department_id=20;

SQL> UPDATE demo SET salary=15000 WHERE department_id=20;

SQL> COMMIT;

SQL> SELECT salary FROM demo WHERE department_id=20;

SQL> UPDATE demo SET salary=50000 WHERE department_id=20;

SQL> SELECT salary FROM demo WHERE department_id=20;

SQL> ROLLBACK;

SQL> SELECT salary FROM demo WHERE department_id=20;

SQL> DROP TABLE demo PURGE;

SQL> CREATE TABLE demo AS SELECT * FROM employees;

SQL> SELECT COUNT(*) FROM demo;

SQL> SELECT COUNT(*) FROM demo WHERE department_id=50;

SQL> DELETE demo WHERE department_id=50;

SQL> SELECT COUNT(*) FROM demo;

SQL> DELETE demo;

SQL> SELECT COUNT(*) FROM demo;

SQL> ROLLBACK;

SQL> SELECT COUNT(*) FROM demo;

SQL> TRUNCATE TABLE demo;

SQL> SELECT COUNT(*) FROM demo;

SQL> ROLLBACK;

SQL> SELECT COUNT(*) FROM demo;

SQL> CREATE TABLE emp1

(

empno NUMBER(4),

ename VARCHAR2(10)

);

SQL>

CREATE TABLE emp1_copy

(

empno NUMBER(4),

ename VARCHAR2(10)

);

SQL> INSERT INTO emp1 VALUES (1,’star’);

SQL> INSERT INTO emp1 VALUES (2,’raj’);

SQL> COMMIT;

SQL> SELECT * FROM emp1;

SQL> SELECT * FROM emp1_copy;

SQL>

MERGE INTO emp1_copy e1 USING emp1 e ON (e.empno=e1.empno) WHEN MATCHED THEN

UPDATE SET e1.ename=e.ename WHEN NOT MATCHED THEN INSERT VALUES (e.empno,e.ename);

SQL> SELECT * FROM emp1_copy;

SQL> COMMIT;

SQL> SELECT * FROM emp1;

SQL> UPDATE emp1 SET ename=’kumar’ WHERE empno=1;

SQL> COMMIT;

SQL> SELECT * FROM emp1;

SQL> SELECT * FROM emp1_copy;

SQL>

MERGE INTO emp1_copy e1 USING emp1 e ON (e.empno=e1.empno) WHEN MATCHED THEN UPDATE SET e1.ename=e.ename WHEN NOT MATCHED THEN INSERT VALUES (e.empno,e.ename);

SQL> SELECT * FROM emp1;

SQL> SELECT * FROM emp1_copy;

Joins

SQL> DROP TABLE tom_emp PURGE;

SQL> DROP TABLE tom_dept PURGE;

SQL>

CREATE TABLE tom_dept

(

deptno NUMBER(4) PRIMARY KEY,

dname VARCHAR2(20)

);

SQL> DESC tom_dept;

SQL> INSERT INTO tom_dept VALUES (10,’acc’);

SQL> INSERT INTO tom_dept VALUES (20,’finance’);

SQL> INSERT INTO tom_dept VALUES (30,’sales’);

SQL> COMMIT;

SQL> SELECT * FROM tom_dept;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(20),

deptno NUMBER(4) REFERENCES tom_dept(deptno)

);

SQL> DESC tom_emp;

SQL> DROP TABLE tom_emp PURGE;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(20),

deptno NUMBER(4) REFERENCES tom_dept(deptno)

);

SQL> INSERT INTO tom_emp VALUES (1,’raj’,10);

SQL> INSERT INTO tom_emp VALUES (2,’ram’,20);

SQL> INSERT INTO tom_emp VALUES (3,’joy’,NULL);

SQL> COMMIT;

SQL> SELECT * FROM tom_emp;

SQL> SELECT * FROM tom_dept;

SQL> SELECT * FROM tom_emp;

SQL> SELECT e.empno,e.ename,e.deptno,d.deptno,d.dname FROM tom_emp e joIN tom_dept d

ON (e.deptno=d.deptno);

SQL> SELECT e.empno,e.ename,e.deptno,d.deptno,d.dname FROM tom_emp e left join tom_dept d ON (e.deptno=d.deptno);

SQL> SELECT e.empno,e.ename,e.deptno,d.deptno,d.dname FROM tom_emp e right join tom_dept d ON (e.deptno=d.deptno);

SQL> SELECT e.empno,e.ename,e.deptno,d.deptno,d.dname FROM tom_emp e full join tom_dept d ON (e.deptno=d.deptno);

SQL> DELETE tom_dept;

SQL> DELETE tom_emp;

SQL> DELETE tom_dept;

SQL> DROP TABLE tom_emp PURGE;

SQL> DROP TABLE tom_dept PURGE;

SQL>

CREATE TABLE tom_dept

(

deptno NUMBER(4) PRIMARY KEY,

dname VARCHAR2(20)

);

SQL> DESC tom_dept;

SQL> INSERT INTO tom_dept VALUES (10,’acc’);

SQL> INSERT INTO tom_dept VALUES (20,’finance’);

SQL> INSERT INTO tom_dept VALUES (30,’sales’);

SQL> COMMIT;

SQL> SELECT * FROM tom_dept;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(20),

deptno NUMBER(4) REFERENCES tom_dept(deptno) on DELETE set NULL

);

SQL> INSERT INTO tom_emp VALUES (1,’raj’,10);

SQL> INSERT INTO tom_emp VALUES (2,’ram’,10);

SQL> INSERT INTO tom_emp VALUES (3,’mahi’,20);

SQL> INSERT INTO tom_emp VALUES (4,’rahul’,20);

SQL> INSERT INTO tom_emp VALUES (5,’abhi’,NULL);

SQL> SELECT * FROM tom_emp;

SQL> SELECT * FROM tom_dept;

SQL> DELETE tom_dept WHERE deptno=10;

SQL> SELECT * FROM tom_emp;

SQL> SELECT * FROM tom_dept;

SQL> DROP TABLE tom_emp PURGE;

SQL> DROP TABLE tom_dept PURGE;

SQL>

CREATE TABLE tom_dept

(

deptno NUMBER(4) PRIMARY KEY,

dname VARCHAR2(20)

);

SQL> INSERT INTO tom_dept VALUES (10,’acc’);

SQL> INSERT INTO tom_dept VALUES (20,’finance’);

SQL> INSERT INTO tom_dept VALUES (30,’sales’);

SQL> COMMIT;

SQL> SELECT * FROM tom_dept;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(20),

deptno NUMBER(4) REFERENCES tom_dept(deptno) on DELETE set NULL

);

SQL> INSERT INTO tom_emp VALUES (1,’raj’,10);

SQL> INSERT INTO tom_emp VALUES (2,’ram’,10);

SQL> INSERT INTO tom_emp VALUES (3,’mahi’,20);

SQL> INSERT INTO tom_emp VALUES (4,’rahul’,20);

SQL> INSERT INTO tom_emp VALUES (5,’abhi’,NULL);

SQL> COMMIT;

SQL> SELECT * FROM tom_emp;

SQL> DELETE tom_dept WHERE deptno=10;

SQL> SELECT * FROM tom_dept;

SQL> SELECT * FROM tom_emp;

SQL> DROP TABLE tom_emp PURGE;

SQL> DROP TABLE tom_dept PURGE;

SQL> DROP TABLE tom_salgrade PURGE;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(10),

sal NUMBER(10)

);

SQL> INSERT INTO tom_emp VALUES (1,’raj’,3000);

SQL> INSERT INTO tom_emp VALUES (2,’rahul’,5000);

SQL> INSERT INTO tom_emp VALUES (3,’ramu’,6000);

SQL> INSERT INTO tom_emp VALUES (4,’VARma’,8000);

SQL> COMMIT;

SQL> SELECT * FROM tom_emp;

SQL>

CREATE TABLE tom_salgrade

(

MIN_sal NUMBER(10),

MAX_sal NUMBER(10),

grade CHAR(1)

);

SQL> INSERT INTO tom_salgrade VALUES (1000,3000,’a’);

SQL> INSERT INTO tom_salgrade VALUES (3001,6000,’b’);

SQL> INSERT INTO tom_salgrade VALUES (6001,9000,’c’);

SQL> COMMIT;

SQL> SELECT * FROM tom_salgrade;

SQL> SELECT e.empno,e.ename,e.sal,s.min_sal,s.max_sal,s.grade FROM tom_emp e join tom_salgrade s ON e.sal between s.min_sal AND s.max_sal;

Note:

* The above join is called as NON-EQUI join

Joining 3 TABLES

SQL>

CREATE TABLE tom_dept

(

deptno NUMBER(4) PRIMARY KEY,

dname VARCHAR2(20)

);

SQL> INSERT INTO tom_dept VALUES (10,’acc’);

SQL> INSERT INTO tom_dept VALUES (20,’finance’);

SQL> INSERT INTO tom_dept VALUES (30,’sales’);

SQL> COMMIT;

SQL> SELECT * FROM tom_dept;

SQL> DROP TABLE tom_emp PURGE;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(10),

sal NUMBER(10),

deptno NUMBER(4) REFERENCES tom_dept(deptno)

);

SQL> INSERT INTO tom_emp VALUES (1,’raj’,3000,10);

SQL> INSERT INTO tom_emp VALUES (2,’rahul’,5000,10);

SQL> INSERT INTO tom_emp VALUES (3,’ramu’,6000,20);

SQL> INSERT INTO tom_emp VALUES (4,’VARma’,8000,30);

SQL> COMMIT;

SQL> SELECT * FROM tom_emp;

SQL> DROP TABLE tom_salgrade PURGE;

SQL>

CREATE TABLE tom_salgrade

(

MIN_sal NUMBER(10),

MAX_sal NUMBER(10),

grade CHAR(1)

);

SQL> INSERT INTO tom_salgrade VALUES (1000,3000,’a’);

SQL> INSERT INTO tom_salgrade VALUES (3001,6000,’b’);

SQL> INSERT INTO tom_salgrade VALUES (6001,9000,’c’);

SQL> COMMIT;

SQL> SELECT * FROM tom_salgrade;

SQL> SELECT e.empno,e.ename,e.sal,s.min_sal, s.max_sal,s.grade, e.deptno,d.dname

FROM tom_emp e JOIN tom_salgrade s ON e.sal between s.min_sal AND s.max_sal JOIN tom_dept d ON d.deptno=e.deptno;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(20),

sal NUMBER(10),

mgr NUMBER(4) REFERENCES tom_emp(empno)

);

SQL> INSERT INTO tom_emp VALUES (1,’king’,70000,NULL);

SQL> INSERT INTO tom_emp VALUES (2,’smith’,50000,1);

SQL> INSERT INTO tom_emp VALUES (3,’allen’,40000,2);

SQL> INSERT INTO tom_emp VALUES (4,’joy’,30000,1);

SQL> COMMIT;

SQL> SELECT * FROM tom_emp;

SQL> SELECT w.ename,w.mgr,m.ename,m.empno FROM tom_emp w join tom_emp m

ON (w.mgr=m.empno);

NATURAL join (Single Column)

* It joins two TABLEs based on the common columns in both the TABLEs

SQL> DROP TABLE tom_emp PURGE;

SQL> DROP TABLE tom_dept PURGE;

SQL>

CREATE TABLE tom_dept

(

deptno NUMBER(4) PRIMARY KEY,

dname VARCHAR2(20)

);

SQL> INSERT INTO tom_dept VALUES (10,’acc’);

SQL> INSERT INTO tom_dept VALUES (20,’finance’);

SQL> INSERT INTO tom_dept VALUES (30,’sales’);

SQL> COMMIT;

SQL> SELECT * FROM tom_dept;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(10),

sal NUMBER(10),

deptno NUMBER(4) REFERENCES tom_dept(deptno)

);

SQL> INSERT INTO tom_emp VALUES (1,’raj’,3000,10);

SQL> INSERT INTO tom_emp VALUES (2,’rahul’,5000,10);

SQL> INSERT INTO tom_emp VALUES (3,’ramu’,6000,20);

SQL> INSERT INTO tom_emp VALUES (4,’VARma’,8000,30);

SQL> COMMIT;

SQL> SELECT * FROM tom_emp;

SQL> SELECT empno,ename,sal,deptno,dname

FROM tom_emp NATURAL JOIN tom_dept;

NATURAL join (Multiple Columns)

SQL> DROP TABLE tom_emp PURGE;

SQL> DROP TABLE tom_dept PURGE;

SQL>

CREATE TABLE tom_dept

(

deptno NUMBER(4) PRIMARY KEY,

dname VARCHAR2(20),

empno NUMBER(4)

);

SQL> INSERT INTO tom_dept VALUES (10,’acc’,1);

SQL> INSERT INTO tom_dept VALUES (20,’finance’,2);

SQL> INSERT INTO tom_dept VALUES (30,’sales’,3);

SQL> COMMIT;

SQL> SELECT * FROM tom_dept;

SQL>

CREATE TABLE tom_emp

(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(10),

sal NUMBER(10),

deptno NUMBER(4) REFERENCES tom_dept(deptno)

);

SQL> INSERT INTO tom_emp VALUES (1,’raj’,3000,10);

SQL> INSERT INTO tom_emp VALUES (2,’rahul’,5000,10);

SQL> INSERT INTO tom_emp VALUES (3,’ramu’,6000,20);

SQL> INSERT INTO tom_emp VALUES (4,’VARma’,8000,30);

SQL> COMMIT;

SQL> SELECT * FROM tom_emp;

CROSS JOIN

SELECT d.empno,d.deptno,dname FROM tom_emp CROSS JOIN tom_dept d;

**********

PRIVILEGES

**********

User Creation Syntax:

CREATE USER <user_name> IDENTIFIED BY <passwORd>;

User PasswORd ChanINg Syntax:

ALTER USER <user_name> IDENTIFIED BY <new_passwORd>;

Grant Privilege Syntax:

GRANT <privilege1,privilege2…> TO <user_name>;

–> GivINg the permission to the user

SQL> CREATE user tom identified by tom;

User CREATEd.

SQL> conn tom/tom;

ERROR:

ORA-01045: user TOM lacks CREATE SESSION privilege; logon denied

WarnINg: You are no longer connected to ORACLE.

SQL> conn / as sysdba

Connected.

SQL> grant CREATE session to tom;

Grant succeeded.

SQL> conn tom/tom;

Connected.

SQL> show user

USER is “TOM”

SQL> SELECT * FROM tab;

no rows SELECTed

SQL> CREATE TABLE tom

2 (

3 empno NUMBER(4)

4 );

CREATE TABLE tom

*

ERROR at lINe 1:

ORA-01031: INsufficient privileges

SQL> SELECT * FROM session_privs;

PRIVILEGE

—————————————-

CREATE SESSION

SQL> conn / as sysdba

Connected.

SQL> grant CREATE TABLE to tom;

Grant succeeded.

SQL> conn tom/tom;

Connected.

SQL> SELECT * FROM session_privs;

PRIVILEGE

—————————————-

CREATE SESSION

CREATE TABLE

SQL> CREATE TABLE tom (empno NUMBER(4) );

CREATE TABLE tom

*

ERROR at lINe 1:

ORA-01950: no privileges on TABLEspace ‘USERS’

SQL> conn / as sysdba

Connected.

SQL> ALTER user tom quota 10m on users;

User ALTERed.

SQL> conn tom/tom

Connected.

SQL> SELECT * FROM session_privs;

PRIVILEGE

—————————————-

CREATE SESSION

CREATE TABLE

SQL> CREATE TABLE tom (empno NUMBER(4));

Table created.

SQL> DROP TABLE tom;

Table dropped.
Note:

Owner who has Created the TABLE has the DROP privileges by default on the TABLES CREATED by him Even the dba has the DROP privilege
REVOKING PRIVILEGES Syntax:

REVOKE <privilege1,privilege…> FROM <user_name>;

–> Taking away the permissions FROM the user

SQL> conn / as sysdba

Connected.

SQL> show user

USER is “SYS”

SQL> revoke CREATE TABLE FROM tom;

Revoke succeeded.

SQL> conn tom/tom;

Connected.
SQL> SELECT * FROM session_privs;

PRIVILEGE

—————————————-

CREATE SESSION

SQL> conn / as sysdba

Connected.

SQL> grant CREATE TABLE to public;

Grant succeeded.

Note:

Public means every user

SQL> conn tom/tom;

Connected.
SQL> SELECT * FROM session_privs;

PRIVILEGE

—————————————-

CREATE SESSION

CREATE TABLE

SQL> conn / as sysdba

Connected.

SQL> revoke CREATE TABLE FROM public;

Revoke succeeded.

SQL> conn tom/tom;

Connected.

SQL> SELECT * FROM session_privs;

PRIVILEGE

—————————————-

CREATE SESSION
***********************

Object Level Privileges

***********************

SQL> conn / as sysdba

Connected.

SQL> grant CREATE TABLE, ALTER any TABLE to tom;

Grant succeeded.

SQL> conn tom/tom

Connected.

SQL> SELECT * FROM session_privs;

PRIVILEGE

—————————————-

CREATE SESSION

CREATE TABLE

ALTER ANY TABLE

SQL> CREATE TABLE demo

2 (

3 empno NUMBER(4),

4 ename VARCHAR2(20)

5 );

Table created.

SQL> conn / as sysdba

Connected.

SQL> CREATE user tom1 identified by tom1;

User created.

SQL> grant CREATE session to tom1;

Grant succeeded.

SQL> conn tom/tom

Connected.

SQL> grant SELECT on demo to tom1;

Grant succeeded.

SQL> grant INSERT,update,DELETE on demo to tom1;

Grant succeeded.

SQL> conn tom1/tom1;

Connected.

SQL> SELECT * FROM session_privs;

PRIVILEGE

—————————————-

CREATE SESSION
SQL> SELECT * FROM tom.demo;

no rows Selected

SQL> INSERT INTO tom.demo VALUES (1,’raj’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> conn tom/tom

Connected.

SQL> show user

USER is “TOM”

SQL> SELECT * FROM demo;

EMPNO ENAME

———- ——————–

1          raj

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: