Duplicate table or copy of a table
==================================
Using create and select command we can create a duplicate table or a copy of a
table.
ex:
create table employee as select * from emp;
create table employee as select eid,ename,esal from emp;
create table employee as select * from emp where deptno=10;
create table employee as select * from emp where deptno IN(10,20,30);
create table employee as select * from emp where ename like 'A%';
create table employee as select * from emp where esal between 5000 and 40000;
create table employee as select * from emp where esal>5000 AND esal<40000;
create table employee as select * from emp where deptno <> 10;
cl scr
=======
It is used to clear the output screen of sql command prompt.
ex:
cl scr
DDL commands
===========
Create - (tables)
Alter - (columns)
Drop - (tables)
Truncate - (rows/records)
Rename - (tables)
Alter command
---------------
Using alter command we can perform following activities very easily.
i)Adding a new column
ii)Droping a existing column
iii)Modifying a column
iv)Renaming a column
i)Adding a new column
----------------------
Using alter command we can add new column to database table.
syntax:
alter table table_name ADD (col_name datatype(size));
ex:
alter table student ADD (city varchar2(10));
alter table student ADD (pincode number(8));
alter table student ADD (city varchar2(10),pincode number(8));
ii)Droping a existing column
------------------------------
Using alter command we can drop a existing column from the database table.
syntax:
alter table table_name DROP (col_name);
ex:
alter table student DROP (city);
alter table student DROP (pincode);
alter table student DROP (city,pincode);
iii)Modifying a column
------------------------
Using alter command we can modify a column.
We can change the size of a column only if existing values are fit into the new
size.
ex:
desc student;
alter table student MODIFY (sadd varchar2(15));
desc student;
We can change the datatype of a column only if that column is empty.
ex:
desc student;
alter table student MODIFY (sadd number(10));
desc student;
iv)Renaming a column
----------------------
Using alter command we can rename a column.
syntax:
alter table table_name RENAME column old_name to new_name;
ex:
alter table student rename column sadd to city;
alter table emp rename esal to dailywages;
alter table emp rename job to designation;
Drop command
-------------
It is used to drop the table from the database.
syntax:
drop table <table_name>;
ex:
drop table emp;
drop table student;
drop table dept;
Truncate command
-----------------
Truncate command is used to delete the records permanently from database table.
syntax:
truncate table <table_name>;
ex:
truncate table student;
truncate table emp;
truncate table dept;
Q)What is the difference between delete and truncate command?
delete truncate
----------- -----------
It deletes the records temperory. It deletes the records permanently.
We can rollback the data. We can't rollback the data.
Where clause can be used. Where clause can't be used.
It is a DML command. It is a DDL command.
Rename command
---------------
It is used to rename the table name.
syntax:
rename <old_name> to <new_name>;
ex:
rename student to students;
rename emp to employees;
rename dept to department;
Functions
=============
Functions are used to manipulate the data items and give the result.
We have two types of functions.
1)Group functions / Multiple Rows functions
2)Scalar functions / Single Row functions
1)Group functions
-----------------
Group functions are applicable for multiple rows.
We have following list of group functions.
ex:
sum(),avg(),max(),min(),count(*) and count(expression).
Q)Write a query to display sum of salary of each employee?
select sum(esal) from emp;
Q)Write a query to display average salary of each employee ?
select avg(esal) from emp;
Q)Write a query to display highest salary from emp table?
select max(esal) from emp;
Q)Write a query to display lowest or least salary from emp table?
select min(esal) from emp;
Q)What is the difference between count(*) and count(expression) ?
count(*)
---------
It will return number of records present in a database table.
It will include null records.
ex:
select count(*) from emp; // 6
select count(*) from dept; // 4
select count(*) from student; // 3
count(expression)
----------------
It will return number values are present in a database table column.
It will not include null values.
ex:
select count(eid) from emp; // 6
select count(comm) from emp; // 5
Interview question
------------------
userlist table
---------------
drop table userlist;
create table userlist (uname varchar2(10),pwd varchar2(10));
insert into userlist values('raja','rani');
insert into userlist values('king','kingdom');
commit;
Q)Write a query to check login credentials?
select count(*) from userlist where uname='raja' and pwd='rani'; // 1
select count(*) from userlist where uname='raja' and pwd='rani2';// 0
Dual table
===========
Dual table is a dummy table which is used to perform arithmetic operations and to
see the current system date.
A dual table contains 1 row and 1 column.
ex:
select 10+20 from dual;
select 10*2 from dual;
select sysdate from dual;
select current_date from dual;
2)Scalar Functions
-------------------
Scalar functions are applicable for single row.
We have following list of scalar functions.
i) Character functions
ii)Number functions
iii)Date functions
iv)Conversion functions
i) Character functions
-------------------------
We have following list of character functions.
upper()
--------
It will convert lower case to upper case.
ex:
select upper('oracle') from dual;
select upper('oracle') as UPPER from dual;
lower()
-------
It will convert upper case to lower case.
ex:
select lower('ORACLE') from dual;
select lower('ORACLE') as LOWER from dual;
initcap()
--------
It will display the text in init cap.
ex:
select initcap('oracle training') from dual;
select initcap('oracle training') as INITCAP from dual;
LPAD()
-------
It is used to pad the characters at left side.
ex:
select LPAD('oracle',10,'z') from dual; //zzzzoracle
RPAD
-----
It is used to pad the characters at right side.
ex:
select RPAD('oracle',10,'z') from dual; //oraclezzzz
LTRIM()
-------
It is used to trim the characters from left side.
ex:
select LTRIM('zzoraclezz','z') from dual; // oraclezz
RTRIM()
-------
It is used to trim the characters from right side.
ex:
select LTRIM('zzoraclezz','z') from dual; // zzoracle
trim()
------
It is used to trim the characters from both the sides.
ex:
select trim('z' from 'zzoraclezz') from dual;
concat()
------
It is used to concatinate the characters.
ex:
select concat('mega','star') from dual;
select concat(concat('mega','star'),'chiru') from dual;
ii)Number functions
--------------------
We have following list of number functions.
abs()
------
It will return absolute value.
ex:
select abs(-10) from dual; // 10
select abs(-12.34) from dual; // 12.34
power(A,B)
-----------
It will return power value.
ex:
select power(2,3) from dual; // 2*2*2 = 8
select power(5,2) from dual; // 25
sqrt()
------
It will return square root.
ex:
select sqrt(25) from dual; // 5
select sqrt(26) from dual; // 5.099
ceil()
------
It will return ceil value.
ex:
select ceil(10.5) from dual; //11
select ceil(8.2) from dual; // 9
floor()
-------
It will return floor value.
ex:
select floor(10.5) from dual; //10
select floor(8.2) from dual; // 8
round()
-------
It will return nearest value.
ex:
select round(10.5) from dual; //11
select round(8.2) from dual; // 8
trunc()
--------
It is used to remove the decimals.
ex:
select trunc(10.56) from dual; // 10
select trunc(115.34) from dual; // 115
greatest()
---------
It will return greatest value.
ex:
select greatest(101,203,401) from dual; //401
least()
------
It will return least value.
ex:
select least(101,204,401) from dual; // 101
Working with Date values
=======================
Every database software support different date patterns.
ex:
Oracle - dd-MMM-yy
MySQL - yyyy-MM-dd
We need to insert date values inthe pattern which is supported by database
software.
ex:
drop table emp1;
create table emp1 (eid number(3),ename varchar2(10), edoj date);
insert into emp1 values (501,'Alan','23-MAY-23');
insert into emp1 values (502,'Jose',sysdate);
insert into emp1 values (503,'Kelvin',current_date);
commit;
iii) Date functions
-----------------
We have following list of date functions.
ADD_MONTHS()
--------------
It is used to add the months in a given date.
ex:
select ADD_MONTHS(sysdate,4) from dual; //23-SEP-23
MONTHS_BETWEEN()
---------------
It will return number of months between two given dates.
ex:
select MONTHS_BETWEEN('01-JAN-23','01-MAY-23') from dual; //-4
select abs(MONTHS_BETWEEN('01-JAN-23','01-MAY-23')) from dual; //4
select MONTHS_BETWEEN('01-JAN-23','15-MAY-23') from dual; // -4.45
NEXT_DAY()
--------
It will return the next date of a given day in a week.
ex:
select NEXT_DAY(sysdate,'sunday') from dual; //28-MAY-23
select NEXT_DAY(sysdate,'tuesday') from dual; //30-MAY-23
LAST_DAY()
--------
It will return last date of a month.
ex:
select last_day(sysdate) from dual; //31-MAY-23
select last_day('14-FEB-23') from dual; // 28-FEB-23