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
2. The following SELECT statement executes successfully:
SELECT * FROM job_grades;
True/False
3. 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.
4. Your first task is to determine the structure of the DEPARTMENTS table and its contents.
5. You need to determine the structure of the EMPLOYEES table.
6. 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
7. The HR department needs a query to display all unique job codes from the EMPLOYEES Table
8. 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.
9. 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.
2. Create a report that displays the last name and department number for employee number 176.
3. Display the last name and salary for all employees whose salary is not in the $5,000–$12,000 range.
4. 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.
5. Display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name.
6. 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.
7. The HR department needs a report that displays the last name and hires date for all employees who were hired in 1994.
8. Create a report to display the last name and job title of all employees who do not have a manager.
9. Display the last name, salary, and commission for all employees who earn commissions. Sort data in descending order of salary and commissions?
10. 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.
11. 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
12. Display all employee last names in which the third letter of the name is a.
13. Display the last names of all employees who have both an a and an e in their last names.
14. 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.
15. 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
0 comments:
Post a Comment