Oracle PLSQL ORACLE TRAINER videos

Top-Ten-Queries

Top Ten Sql Queries

1.Q. How is the primary key different from a unique key?
A. Both the primary and unique keys uniquely identify a record in a database table. main difference is that you can have

more than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, where

as the unique key allows null value.

2.Q. What is the SQL syntax for sorting, and which is the default order?

A. Syntax:
The basic syntax of ORDER BY clause which would be used to sort result in ascending or descending order is as follows:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

The default sorting order is ascending.

select from Employees order by Last_name

select from employees order by hire_date Desc

For descending order simply desc

Following is an example, which would sort the result in ascending order by NAME and SALARY:
You can use More than one column in order by clause.

SQL> SELECT * FROM Employees
ORDER BY last_name, SALARY;



3. How to find third highest or second maximum salary of an Employee

Rownum is defined for ORACLE, while Top is defined for MS SQL Both rownum and top functions the same,
i.e of selecting the top N tuples according to the query, where N is the number specified when both rownum .

Select Rownum as rank, last_name, salary from ( Select last_name, salary from employees order by sal desc) where rownum<=3;
So the above query is made for finding the three highest earners.

4. 1.What is the differance between Having & Where ?

Where Clause Is used For filtering Rows & Having Clause is for Filter Groups.
For Example to view the details of Employees who is working in department_id=50

select employee_id,salary,department_id
from employees
where department_id=50

Output :- EMPLOYEE_ID SALARY DEPARTMENT_ID
----------- ---------- -------------
198 2600 50
199 2600 50
120 8000 50
121 8200 50
122 7900 50
123 6500 50
124 5800 50
125 3200 50
126 2700 50
127 2400 50
128 2200 50


Group By Clause Example

select department_id,sum(salary)
from employees group by department_id

DEPARTMENT_ID SUM(SALARY)
------------- -----------
100 51600
30 24900
7000
20 19000
70 10000
90 58000
110 20300
50 156400
40 6500
80 304500
10 4400

DEPARTMENT_ID SUM(SALARY)
------------- -----------
60 28800

12 rows selected.


5. Describe all aggregate Functions with example.

Aggregate functions return a single result row based on groups of rows, rather than on single rows.

select sum(salary) from employees
where department_id=40

select avg(salary) from employees
where department_id=40

select min(salary) from employees
where department_id=40

select max(salary) from employees
where department_id=40



6. What is the differance between truncate & delete command ?

The delete command is used to remove rows from a table. a where clause can be used to only remove some rows.

Truncate removes all records from a table and we can not be rolled back and no triggers will fired on truncate command. delete keeps

record into recycle bin(Buffer).

SQL> delete from employee where employee_id=124;

1 row deleted.


SQL> truncate table employee;

Table truncated.


7. How to convert salary in words.

SQL> select to_char(to_date(salary,'j'),'jsp') from employees;


Output :-

TO_CHAR(TO_DATE(SALARY,'J'),'JSP')
------------------------------------------------
two thousand six hundred
two thousand six hundred
four thousand four hundred

8. What is the differance between Uniq key and primary key.?

You can have more than one unique key per table but you can have onle one primary key per table.it creates an index for primary key automatically.

Sql does not allows null value for primary key but unique key has null value in a table.

9. To find max salary from each department.

select department_id ,max(salary) from employees
group by department_id


10. Write a sql query to display current date & time.
select sysdate,systimestamp from dual.


11. How to view table comments

SELECT comments
FROM user_tab_comments
WHERE table_name='DEPARTMENTS'


12. How to add a comment to a Table.

COMMENT ON VIEW V3
IS 'Employee Information data '



13. Write a sql query that list the names employees whose first_name starts with s or k

select employee_id,last_name,first_name from employees
where regexp_like(first_name,'^s','i');

EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ------------------------- ------------------
203 Mavris Susan
205 Higgins Shelley
100 King Steven
116 Baida Shelli
117 Tobias Sigal
123 Vollman Shanta
128 Markle Steven
138 Stiles Stephen
161 Sewall Sarath
166 Ande Sundar
173 Kumar Sundita

EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ------------------------- ------------------
192 Bell Sarah
194 McCain Samuel


14. How can i create en empty table with same structure of employees?

create table temp as select * from employees where 1=2;



15. Display the last name, salary, and commission for all employees who earn commissions. Sort

data in descending order of salary and commissions.

SELECT last_name, salary, commission_pct

FROM employees

WHERE commission_pct IS NOT NULL

ORDER BY salary DESC, commission_pct DESC;





Post a Comment

Links to this post

To find square and cube roots of a number without using the sqrt and pow functions?

#include<stdio.h>
#include<conio.h>
int sqrt(int num);
int cuberoot(int num);
void main()
{
clrscr();
int num ,s,c;
printf("enter the number");
scanf("%d",&num);
s=num*num;
printf("\n%d",s);
c=num*num*num;
printf("%d",c);
getch();
}
int sqrt(int num)
{
return(num * num);
}
int cuberoot (int num)
{
return(num * num * num);
}

Renaming an Oracle table column

Renaming an Oracle table column.

SQL> desc emp1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------

 EM_NO                                     NOT NULL NUMBER
 EMP_NAME                                           VARCHAR2(50)
 EMP_DEPT                                           VARCHAR2(50)
 EMP_SALARY                                         NUMBER


SQL> alter table emp1 rename column em_no to emp_no;

Table altered.

SQL> desc emp1
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------

 EMP_NO                                    NOT NULL NUMBER
 EMP_NAME                                           VARCHAR2(50)
 EMP_DEPT                                           VARCHAR2(50)
 EMP_SALARY                                         NUMBER

Merging Rows in Oracle

Merging Rows


Merging Rows

In Oracle The MERGE statement inserts or updates rows in one table by using data from another table. Each row is inserted or updated in the target table depending on an equijoin condition.

This example matches the employee_id in the tab_new table to the employee_id in the employees table. If a match is found, the row is updated to match the row in the employees table.
If the row is not found, it is inserted into the tab_new table.

The complete example for using MERGE in a PL/SQL block is shown on the next notes page.


Now let us create new table tab_new.

SQL> create table tab_new as select employee_id eid,first_name fname,last_name lname from employees;

Table created.

Describe the structure of tab_new table.

SQL> desc tab_new;

Name Null? Type
----------------------------------------- -------- -----------------
EID NUMBER(6)
FNAME VARCHAR2(20)
LNAME NOT NULL VARCHAR2(25)

Delete all records from tab_new table.

SQL> truncate table tab_new;

Table truncated.
--------
After deleting records from tab_new , now you can insert new records using in tabl_new table with the follwing command.

SQL> insert into tab_new values(101,'shweta','Deshpande');

1 row created.

SQL> insert into tab_new values(102,'shalina','khan');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into tab_new values(103,'rafat','shaikh');

1 row created.

SQL> commit;

Commit complete.

Now view all the records from tab_new table.

SQL> select * from tab_new;

EID FNAME LNAME
---------- -------------------- -------------------------
101 shweta Deshpande
102 shalina khan
103 rafat shaikh


Now write an Annonymous block to inserts or updates rows in one table by using merge statement.

SQL> merge into tab_new t
2 using employees e
3 on (t.eid=e.employee_id)
4 when matched then
5 update set
6 t.fname=e.first_name,
7 t.lname=e.last_name
8 when not matched then
9 insert values (e.employee_id,e.first_name,e.last_name)
10 /

107 rows merged.

You can see that If a match is found, the row is updated to match the row in the employees table. If the row is not found, it is inserted into the tab_new table
SQL> select * from tab_new;

EID FNAME LNAME
---------- -------------------- -------------------------
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
198 Donald OConnell
199 Douglas Grant
:
:
:
:
107 rows inserted

MONTHS_BETWEEN,ADD_MONTHS

MONTHS_BETWEEN(date1, date2):
Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative.
The noninteger part of the result represents a portion of the month.


For Example:-


SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) Experiance in Months
from employees
/

ADD_MONTHS(date, n): Adds n number of calendar months to date.
The value of n must be an integer and can be negative.
For example We want to display review date of an employee.six-month review date.


select ADD_MONTHS (hire_date, 6) REVIEW from employees;

Spool command in oracle

Spool Of/On

Sends standard output, informational messages, error messages, and/or warning messages generated by the execution to the file.

If file-name does not exist, it is created. If file-name already exist, it is overwritten..

if a directory does not exist, file-name is created in the current directory.

Example

spool on to 'output.txt';

spool on to 'c:\students\output.txt';

Where condition In Sql

SQL Where Condition

1. Get employee details from employees table whose employee name is “King”

Select * from EMPLOYEES where last_NAME='King'

2. Get employee details from employees  table whose employee name are “John” and “Roy”

Select * from EMPLOYEES where FIRST_NAME in ('King','Kochhar')

3. Get employee details from employee table whose employee name are not “John” and “Roy”

Select * from EMPLOYEES  where Last_NAME not in ('King','Kochhar')

Like Clause In Sql

The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in combination with the LIKE 

operator:The percent sign (%)

The underscore (_)

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in 

combinations.

Syntax:
The basic syntax of % and _ is as follows:
Example:-


SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'
You can combine N number of conditions using AND or OR operators. Here,
 XXXX could be any numeric or string value.

Table Aliase

Table aliases help to keep SQL code smaller, therefore using less memory.

Table aliases can be up to 30 characters in length, but shorter aliases are better than longer ones.
If a table alias is used for a particular table name in the FROM clause, then that table alias must be substituted for the table name throughout the SELECT statement.
Table aliases should be meaningful.

The table alias is valid for only the current SELECT statement.


SELECT e.employee_id, e.last_name,
       d.location_id, department_id
FROM   employees e JOIN departments d
USING (department_id) ;

CREATE SEQUENCE

In Oracle you can create an autonumber field by using sequences.

A Sequence is an object in oracle that is used to generate a number sequence.
This can be useful when we need to create unique number to act as a primary key.

The syntax to create a sequence in oracle is:
Create sequence sequence_name
Minvalue value
Maxvalue value
Start with value
Increment By Value;

For Example:-


Create sequence se1
Minvalue 100
Maxvalue 9999
Start with 100
Increment by 1
Nocache ;

This would create object a sequence called seq1. First sequence would be use as is 100 and increment by 1.

What is a cross Join

What is a cross join ? Cross join is defined as the cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table. Example :- SELECT last_name, department_name FROM employees CROSS JOIN departments ;

To_char Function in sql, Convert Numbers Into words

1.Create a query that displays the employees ’ last names and commission amounts. If anemployee does not earn commission, show  “No Commission.” Label the column   COMM

Query is as follows

SQL> SELECT last_name,
NVL(TO_CHAR(commission_pct), 'No Commission') COMM
FROM employees;

2. How to convert number into words
Example :-

 SELECT TO_CHAR (TO_DATE (260050, 'j'), 'jsp') FROM DUAL;


SQL> /

TO_CHAR(TO_DATE(260050,'J'),'JSP
--------------------------------
two hundred sixty thousand fifty

HR SCHEMA

SQL RENAME Command

SQL RENAME Command

The SQL RENAME command is used to change the name of the table or a database object.

If you change the object's name any reference to the old name will be affected. You have to manually change the old name to the new name in every reference.
Syntax to rename a table

RENAME old_table_name To new_table_name;

For Example: To change the name of the table employees to my_employee, the query would be like

RENAME employees  TO my_emloyee;

Select statement

Select Clause In sql

SQL (Structured Query Language) is used to modify and access data or information from a storage area called database.

This beginner sql tutorial website teaches you the basics of SQL and how to write SQL queries. I will be sharing my

knowledge on SQL and help you learn SQL better. The sql concepts discussed in this tutorial can be applied to most of

database systems. The syntax used to explain the concepts is similar to the one used in Oracle database.

Some Of the most important Sql Commands are as follwes:

SQL SELECT Statement

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data

from a table in the database. A query may retrieve information from specified columns or from all of the columns in the

table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole

query is called SQL SELECT Statement.

Note: SQL is not case sensitive. SELECT is the same as select.

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

Table-name is the name of the table from which the information is retrieved.
    column_list includes one or more columns from which data is retrieved.

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SELECT * Example

Now we want to select all the columns from the "Employees" table.

SQL> select * from employees;

Now we want to select the content of the columns named

"Employee_id", "LastName" and "FirstName" from the table above.

SQL> select employee_id,first_name,last_name from employees;


Where Clause:-

SQL HAVING Clause

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group

functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

For Example: If you want to select the department that has total salary paid for its employees more than 25000, the sql

query would be like;

SELECT departmet_id, SUM (salary)
FROM employees
GROUP BY departmets
HAVING SUM(salary) > 25000

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement,
the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped

based on the GROUP BY clause. Finally, any conditions on the group functions in the HAVING clause are applied to the

grouped rows before the final output is displayed.
SQL ORDER BY

The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts

query results in ascending order by default.

For Example: If you want to sort the employee table by salary of the employee, the sql query would be.

SELECT first_name, salary FROM employee ORDER BY salary;



The query first sorts the result according to name and then displays it.

You can also use more than one column in the ORDER BY clause.
he query first sorts the result according to name and then displays it.

You can also use more than one column in the ORDER BY clause.                                                                                                          



Alter Table :-
SQL ALTER TABLE Statement

The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is

used to perform the following functions.

1) Add, drop, modify table columns
2) Add and drop constraints
3) Enable and Disable constraints

Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employees  ADD experience number(3);

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employees  table,
the query would be like


ALTER TABLE employees DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employees  table, the query would be like

ALTER TABLE employees MODIFY salary number(15,2);


SQL RENAME Command

The SQL RENAME command is used to change the name of the table or a database object.

If you change the object's name any reference to the old name will be affected. You have to manually change the old name to the new name in every reference.
Syntax to rename a table

RENAME old_table_name To new_table_name;

For Example: To change the name of the table employees to my_employee, the query would be like

RENAME employees  TO my_emloyee;



The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem, however,
 sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name

SELECT DISTINCT City FROM emp_info;


SQL WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion

SELECT * FROM Emp_info
WHERE City='Mumbai'
Use Quotes Around Text Fields
SQL uses single quotes around text values (most database systems will also accept double quotes).

However, numeric values should not be enclosed in quotes....

Display the last name and department number of all employees in departments 20 and 50 in

alphabetical order by name.

SELECT last_name, department_id

FROM employees

WHERE department_id IN (20, 50)

ORDER BY last_name;

Substr Function In Sql

Substr: The oracle substr function allows you to extract a substring .

SQL>  select substr('santosh chaurasia',2,6) from dual;

SUBSTR
------
antosh

sql> select substr('priyaseth',4) from dual;
SUBSTR
------
antosh

SQL> select substr('avinashkare',-4) from dual;
SQL> select instr('nikita_sharma','k') from dual;

INSTR('NIKITA_SHARMA','K')
--------------------------
                         3
SQL> select instr('america','e') from dual;
The instr function in sql used to find the starting locatin of a pattern in a string.
The syntax for instr(str) is as follows.
instr(str,pattern,[starting position,[nth locatin]])

INSTR('AMERICA','E')
--------------------
                   3
SQL>  select instr('nikitasony','n',2) from dual;-- for 2nd occurance

INSTR('NIKITASONY','N',2)
-------------------------
                        9
SQL> select lpad('vikram',10,'*') from dual;

LPAD('VIKR
----------
****vikram

SQL> select rpad('nasim',10,'*') from dual;

RPAD('NASI
----------
nasim*****

Count Function SQL

Count Function:-  This function is used to count the number of rows returned in a statement.
Syntax:- The syntax for sql count is:

Select count(expression) from tabls where conditions;
sql > select count(department_id)" No_of_employees"  from employees;

 No_of_employees
----------------
             107

What is View in sql

How to Create SQL View

Introduce to SQL View

SQL View is a virtual table which is used to encapsulate a complex queries. After creating SQL view, you can treat a view as a table and

manipulate data on it with only some restrictions. When the data in table changes, the data in view which is dependent on table changes also.

View does not occupied the physical space as table does. The syntax of creating view as follows:


CREATE VIEW SALARY_REC
AS
SELECT e.last_name,
e.salary,
d.department_name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id

Output:

SELECT * FROM SALARY_REC


OConnell 2860 Shipping
Grant 2860 Shipping
Whalen 4840 Administration
Hartstein 14300 Marketing
Fay 6600 Marketing
Mavris 7150 Human Resources
Baer 11000 Public Relations
Higgins 13200 Accounting
Gietz 9130 Accounting
Sullivan 2750 Shipping
Geoni 3080 Shipping
Sarchand 4620 Shipping
Bull 4510 Shipping

IN Condition in sql

An In_condition is a membership condition. it tests a value for membership in a list of values or
subquery.

The Condition defined using in Operator is also known as the membership condition.
In works with values of all data types..

When we using character,dates or non alpha -numeric strings,the list items must be enclosed with single quotation marks For example('Delhi','Mumbai') as shown in second example

SQL> select employee_id,job_id,salary
  2  from employees
  3  where employee_id in(100,102,107);

EMPLOYEE_ID JOB_ID         SALARY
----------- ---------- ----------
        100 AD_PRES         16000
        102 AD_VP           17000
        107 IT_PROG          4200

In the query above ,oracle would select only those records where the employee_id equal to either 100,102,107.
Example 2:in the query below ,oracle would select only those records where job_id is equal to 'IT_PROG','SA_REP'.
remember that this items are enclosed with single quotation marks since they are non-numeric-strings.

SQL> select employee_id,last_name,job_id
  2  from employees
  3  where job_id in('IT_PROG','SA_REP');

EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        103 Hunold                    IT_PROG
        104 Ernst                     IT_PROG
        105 Austin                    IT_PROG
        106 Pataballa                 IT_PROG
        107 Lorentz                   IT_PROG
        150 Tucker                    SA_REP
        151 Bernstein                 SA_REP
        152 Hall                      SA_REP
        153 Olsen                     SA_REP
        154 Cambrault                 SA_REP
        155 Tuvault                   SA_REP

EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        156 King                      SA_REP
        157 Sully                     SA_REP
        158 McEwen                    SA_REP
        159 Smith                     SA_REP
        160 Doran                     SA_REP
        161 Sewall                    SA_REP
        162 Vishney                   SA_REP
        163 Greene                    SA_REP
        164 Marvins                   SA_REP
        165 Lee                       SA_REP
        166 Ande                      SA_REP

EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        167 Banda                     SA_REP
        168 Ozer                      SA_REP
        169 Bloom                     SA_REP
        170 Fox                       SA_REP
        171 Smith                     SA_REP
        172 Bates                     SA_REP
        173 Kumar                     SA_REP
        174 Abel                      SA_REP
        175 Hutton                    SA_REP
        176 Taylor                    SA_REP
        177 Livingston                SA_REP

EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        178 Grant                     SA_REP
        179 Johnson                   SA_REP

35 rows selected.

Truncate function in sql

1. Get the last day of the month.

sql> SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"

    FROM DUAL;

2.  Get the first day of the Year

sql> SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;

Conversion Function in Sql

Conversion function

Implicit conversion:-

A varchar2 or char value can be implicitly converted to
Number or date type value by oracle.
Similarly, a number or data type can be automatically
Converted to character data by oracle server.
For example the below select queries .both the queries will
Give the same output because oracle internally treats 10000
And '10000' are same.

Query-1
select employee_id,first_name,last_name,salary
From employees
Where salary > 10000;


Query-2

YYYY Full year in numbers
YEAR Year spelled out (in English)
MM Two-digit value for month
MONTH Full name of the month
MON Three-letter abbreviation of the month
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month

To_char functions
Element Result
9 Represents a number
0 Forces a zero to be displayed
$ Places a floating dollar sign
L Uses the floating local currency symbol
. Prints a decimal point
, Prints a comma as thousands indicator


select employee_id,first_name,last_name,salary
from employees
where salary > '10000';

Explicit data type conversion are single row function which are capable of

typecasting column value,literal or expression.
To_char,To_number,To_date are three functions which perform
change of data types.
1. To_char()
This function is used to typecast a numeric value or date to character value with

a format model.
Syntax
To_char(number1,[format],[nls_paramete])
Consider the below select query.The query format the Hire_date & salary columns of

employees table using To_char function.
select first_name,To_char(hire_date,'Month DD,YYYY') hire_date,To_char

(salary,'$99999.99') salary
from employees
where rownum < 5;

select to_char(sysdate,'Month') from dual;

Elements Of the Date Model Function.







The To_number function converts a character value to a numeric data type.
if the string being converted contains non numeric characters, the function return an error.
Syntax
To_number(string1,[format],[nls_parameter])

SELECT TO_NUMBER('129.24', '9G999D99')
FROM DUAL;
TO_NUMBER('129.24','9G999D99')
------------------------------
129.24

To_date Function

This function takes character values as input & return formatted date
Equivalent of the same.
To_date function allows users to enter a date in any format, then it converts the entry
Into the default format by oracle server.

Syntax:-To_date(strin1,[format_mask],[nls_language])

SELECT TO_DATE('July 15, 1987, 09:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL
/

How To Add Comments on a Table

Add a Comments on a Table in Oracle

Use the comment statement to add to te data dictinary a comment about a table or table column,view.

To insert an explanatory remark on Employees table,you might issue the following statement.

COMMENT ON TABLE employees
IS 'Employee Information Mumbai '
/

To insert an explanatory remark on salary column of the Employees table,you might issue the following statement.

comment on column employee.salary is 'Salary of an employees';
/
How to get the table comments through sql statement.



select table_name, comments
from user_tab_comments
where table_name = 'EMPLOYEES'

To drop comment , issue follwing statement.

comment on column employees.salary is '';

Basic Examples in sql

1. How to find third highest or second maximum salary of an Employee

Rownum is defined for ORACLE, while Top is defined for MS SQL Both rownum and top functions the same,
i.e of selecting the top N tuples according to the query, where N is the number specified when both rownum .

Select Rownum as rank, last_name, salary from ( Select last_name, salary from employees order by sal desc) where rownum<=3;
So the above query is made for finding the three highest earners.

2. 1.What is the differance between Having & Where ?


Where Clause Is used For filtering Rows & Having Clause is for Filter Groups.
For Example to view the details of Employees who is working in department_id=50

select employee_id,salary,department_id
from employees
where department_id=50

Output :- EMPLOYEE_ID SALARY DEPARTMENT_ID
----------- ---------- -------------
198 2600 50
199 2600 50
120 8000 50
121 8200 50
122 7900 50
123 6500 50
124 5800 50
125 3200 50
126 2700 50
127 2400 50
128 2200 50


Group By Clause Example

select department_id,sum(salary)
from employees group by department_id

DEPARTMENT_ID SUM(SALARY)
------------- -----------
100 51600
30 24900
7000
20 19000
70 10000
90 58000
110 20300
50 156400
40 6500
80 304500
10 4400

DEPARTMENT_ID SUM(SALARY)
------------- -----------
60 28800

12 rows selected.


3. Describe all aggregate Functions with example.

Aggregate functions return a single result row based on groups of rows, rather than on single rows.

select sum(salary) from employees
where department_id=40

select avg(salary) from employees
where department_id=40

select min(salary) from employees
where department_id=40

select max(salary) from employees
where department_id=40



4. What is the differance between truncate & delete command ?

The delete command is used to remove rows from a table. a where clause can be used to only remove some rows.

Truncate removes all records from a table and we can not be rolled back and no triggers will fired on truncate command. delete keeps

record into recycle bin(Buffer).

SQL> delete from employee where employee_id=124;

1 row deleted.


SQL> truncate table employee;

Table truncated.


5. How to convert salary in words.

SQL> select to_char(to_date(salary,'j'),'jsp') from employees;


Output :-

TO_CHAR(TO_DATE(SALARY,'J'),'JSP')
------------------------------------------------
two thousand six hundred
two thousand six hundred
four thousand four hundred
thirteen thousand
six thousand
six thousand five hundred
ten thousand
twelve thousand
eight thousand three hundred
twenty-four thousand
seventeen thousand


6. What is the differance between Uniq key and primary key.?

You can have more than one unique key per table but you can have onle one primary key per table.it creates an index for primary key automatically.

Sql does not allows null value for primary key but unique key has null value in a table.

7. To find max salary from each department.

select department_id ,max(salary) from employees
group by department_id


8. Write a sql query to display current date & time.
select sysdate,systimestamp from dual.


9. How to view table comments

SELECT comments
FROM user_tab_comments
WHERE table_name='DEPARTMENTS'


10. How to add a comment to a Table.

COMMENT ON VIEW V3
IS 'Employee Information data '



11. Write a sql query that list the names employees whose first_name starts with s or k

select employee_id,last_name,first_name from employees
where regexp_like(first_name,'^s','i');

EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ------------------------- ------------------
203 Mavris Susan
205 Higgins Shelley
100 King Steven
116 Baida Shelli
117 Tobias Sigal
123 Vollman Shanta
128 Markle Steven
138 Stiles Stephen
161 Sewall Sarath
166 Ande Sundar
173 Kumar Sundita

EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ------------------------- ------------------
192 Bell Sarah
194 McCain Samuel


12. How can i create en empty table with same structure of employees?

create table temp as select * from employees where 1=2;



13. What is DCL in  SQL?


Data Control Language is used to manage user access to an oracle.
This  category is  for operations that include REVOKE, ALTER USER and GRANT ?

For  example:-
Commit
Rollback
Savepoint

Grant
Revoke