MYSQL DBA: December 2019

Monday, December 30, 2019

TABLE OPERSTION IN ORACLE DATABASE

TABLE OPERTION---ALTERING STRUCTURE

· Introduction

· Renaming a table

· Adding column to a table

· Removing column from a table.

· Increasing width of a columns.

· Decreasing width a columns.

· Changing data types of a columns.

· Copying of a table.

Introduction:-

Let us turn our focus on modifying the existing structure of table. By structure we means column names, data types and constrains.

Renaming a table:-

Now change the table name COLLAGE TO COLLAGES.

SQL> select * from collage;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

rkdf 1 sanjay 12

rkdf 2 salaj 34

oist 3 rahul 23

ptl 4 sunil 45

oist 5 neelesh 43

rkdf 6 ruchi 21

rkdf 7 sarika 49

rkdf 8 satish 32

oist 9 null 31

oist 10 abc

oist 11 xyz

11 rows selected.

SQL> rename collage to collages;

Table renamed.

SQL> select * from collage;

select * from collage

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> desc collages;

Name Null? Type

----------------------------------------------------------------- -------- ----------------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME VARCHAR2(10)

MARKS NUMBER(5)

SQL> desc collage;

ERROR:

ORA-04043: object collage does not exist

Adding column to a table:-

Let us add an extra column in class in collages table

SQL> alter table collages add collage_name varchar2(20);

Table altered.

SQL> alter table collages add (collage_name varchar2(20));

Table altered.

SQL> desc collages;

Name Null? Type

------------------------------------------------------------------------ ----------------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME VARCHAR2(10)

MARKS NUMBER(5)

COLLAGE_NAME VARCHAR2(20)

Notes:-

The new column will be always be added at the end of last column.

We can’t add new columns in between existing columns or at the position.

Removing columns from a table:-

Let us remove a column collage_name from the table.

SQL> alter table collages drop column collage_name;

Table altered.

SQL> desc collages;

Name Null? Type

------------------------------------------------------------------------ ------------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME VARCHAR2(10)

MARKS NUMBER(5)

Increasing width of column:-

Let us now increasing the with column name column to 15 character.

SQL> desc collages;

Name Null? Type

----------------------------------------------------------------- -------- ------------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME VARCHAR2(10)

MARKS NUMBER(5)

SQL> alter table collages modify name varchar2(15);

Table altered

SQL> desc collages;

Name Null? Type

------------------------------------------------------------------ -------- ----------------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME VARCHAR2(15)

MARKS NUMBER(5)

Decreasing width of a column:-

Let us decreasing a width of name column to 10 again.

SQL> alter table collages modify name varchar2(10);

Table altered.

SQL> desc collages;

Name Null? Type

------------------------------------------------------------------ -------- ------------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME VARCHAR2(10)

MARKS NUMBER(5)

Notes:-

Column size can be reduced only in two cases.

· All the record contain NULL values for that columns.

· The size of data which is already present is less than or equal to the new size specified.

Changing data types of a columns:-

We can change data types from CHAR TO VARCHAR2 and vice-versa, that is again we change the data types of name column from varchar2 to char.

The alter table command can be used to change the data types.

SQL> desc collages;

Name Null? Type

------------------------------------------------------------------ -------- -----------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME VARCHAR2(10)

MARKS NUMBER(5)

SQL> alter table collages modify name char(10);

Table altered.

SQL> desc collages;

Name Null? Type

-------------------------------------------------------------------------------- ------------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME CHAR(10)

MARKS NUMBER(5)

Notes:-

We can change the data types from char to varchar2 and vice-versa provided the new size is more than or equal to the size of the existing data values.

Changing number data type to char:-

SQL> desc collages;

Name Null? Type

-------------------------------------------------------------------------- ------------------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME CHAR(10)

MARKS

SQL> alter table collages modify rollno char(4);

alter table collages modify rollno char(4)

ERROR at line 1:

ORA-01439: column to be modified must be empty to change datatype

Notes:-

We can not change the data types from NUMBER TO CHAR/VARCHAR2 and vice-versa.

First we need to empty the data values for the column and then modify it to the new data types.

SQL> update collages set rollno=null;

11 rows updated.

SQL> alter table collages modify rollno char(4);

Table altered.

SQL> desc collages;

Name Null? Type

--------------------------------------------------------------------------- -----------------------------

COLLAGE VARCHAR2(20)

ROLLNO CHAR(4)

NAME CHAR(10)

MARKS NUMBER(5)

Copying a table:-

A table has two component: structure and record. Structure refer to column name, data types and constraints. Record refer to the data which is available in the table.

To copy structure as well as record we can use the CREATE TABLE command with SELECT AS clause.

SQL> create table employee(id number(5) primary key,

2 name varchar2(10) not null,

3 address varchar2(50));

Table created.

SQL> insert into employee values(&p_id,'&p_name','&p_addrss');

Enter value for p_id: 1

Enter value for p_name: satish

Enter value for p_addrss: patna

old 1: insert into employee values(&p_id,'&p_name','&p_addrss')

new 1: insert into employee values(1,'satish','patna')

1 row created.

SQL> /

Enter value for p_id: 2

Enter value for p_name: alam

Enter value for p_addrss: gaya

old 1: insert into employee values(&p_id,'&p_name','&p_addrss')

new 1: insert into employee values(2,'alam','gaya')

1 row created.

SQL> commit;

Commit complete.

SQL> create table employee_new as select * from employee;

Table created.

SQL> desc employee_new;

Name Null? Type

--------------------------------------------------------------------------- -----------------------

ID NUMBER(5)

NAME NOT NULL VARCHAR2(10)

ADDRESS VARCHAR2(50)

SQL> select * from employee_new;

ID NAME ADDRESS

---------- ---------- --------------------------------------------------

1 satish patna

2 alam gaya

Copying a table specifying different column names:-

We can use the aliasing concept to specifying different column names for the new table.

SQL> desc employee;

Name Null? Type

-------------------------------------------------------------------------- ---------------------

ID NOT NULL NUMBER(5)

NAME NOT NULL VARCHAR2(10)

ADDRESS VARCHAR2(50)

SQL> create table em_diff as select id as empno,name as emp_name,address as emp_address from employee;

Table created.

SQL> desc em_diff;

Name Null? Type

----------------------------------------------------------------- -------- --------------

EMPNO NUMBER(5)

EMP_NAME NOT NULL VARCHAR2(10)

EMP_ADDRESS VARCHAR2(50)

Copying table structure only not record:-

We can just copy structure excluding record by using WHERE clause with a condition which always evaluates to FALSE for every record in base table.

SQL> create table emp_structure as select * from employee where 1=2;

Table created.

SQL> desc emp_structure;

Name Null? Type

--------------------------------------------------------------------------- -------------------------------

ID NUMBER(5)

NAME NOT NULL VARCHAR2(10)

ADDRESS VARCHAR2(50)

SQL> select * from emp_structure;

no rows selected

THANK YOU

SORTING IN ORACLE DATABASE

SORTING

· Introduction

· Sorting on single columns.

· Sorting on multiple columns.

· Sorting on a columns with null values.

· Combining WHERE and ORDER BY clause.

· Sorting on DATE columns.

INTRODUCTION:-

Sorting refer to arrange record in a specified sequence may be alphabetically or by values.

This sorting can be achieved by the ‘ORDER BY’ clause of select statement.

SORTING ON SINGLE COLUMNS:-

SQL> desc collage;

Name Null? Type

---------------------------------------------------------------- -------- --------------------

COLLAGE VARCHAR2(20)

ROLLNO NUMBER

NAME VARCHAR2(10)

MARKS NUMBER(5)

SQL> select * from collage;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

rkdf 1 sanjay 12

rkdf 2 salaj 34

oist 3 rahul 23

ptl 4 sunil 45

oist 5 neelesh 43

rkdf 6 ruchi 21

rkdf 7 sarika 49

rkdf 8 satish 32

8 rows selected.

Arranging record by collage in ascending order:-

SQL> select * from collage order by collage;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

oist 5 neelesh 43

oist 3 rahul 23

ptl 4 sunil 45

rkdf 8 satish 32

rkdf 1 sanjay 12

rkdf 6 ruchi 21

rkdf 7 sarika 49

rkdf 2 salaj 34

Ascending order is implies by default whenever we use “ORDER BY” clause.

SQL> select * from collage order by collage asc;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

oist 5 neelesh 43

oist 3 rahul 23

ptl 4 sunil 45

rkdf 8 satish 32

rkdf 1 sanjay 12

rkdf 6 ruchi 21

rkdf 7 sarika 49

rkdf 2 salaj 34

8 rows selected.

Sorting on multiple column:-

Now we want to arrange record first by collage in ascending order and then follow by mark in descending order.

This mean that student will be show in order of their descending marks within a collage such that the highest score display first followed by the next highest score.

SQL> select * from collage order by collage asc,marks desc;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

oist 5 neelesh 43

oist 3 rahul 23

ptl 4 sunil 45

rkdf 7 sarika 49

rkdf 2 salaj 34

rkdf 8 satish 32

rkdf 6 ruchi 21

rkdf 1 sanjay 12

8 rows selected.

Notes:-

“ASC” IMPLIES ACCENDING ORDER.

“DESC” IMPLIES DECENDING ORDER.

“ASC” CAN’T REPLACE D BY “ACCENDING ORDER”.

“DESC” CANT’T REPLACED BY “DESCENDING ORDER”.

The statement will arrange the record displaying the lowest scores first followed by the next lowest and so on irrespective of collage.

SQL> select * from collage order by marks;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

rkdf 1 sanjay 12

rkdf 6 ruchi 21

oist 3 rahul 23

rkdf 8 satish 32

rkdf 2 salaj 34

oist 5 neelesh 43

ptl 4 sunil 45

rkdf 7 sarika 49

8 rows selected.

The statement will arrange the record displaying the highest score first followed by the next highest and so on irrespective of collage.

SQL> select * from collage order by marks desc;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

rkdf 7 sarika 49

ptl 4 sunil 45

oist 5 neelesh 43

rkdf 2 salaj 34

rkdf 8 satish 32

oist 3 rahul 23

rkdf 6 ruchi 21

rkdf 1 sanjay 12

8 rows selected.

Sorting on a columns with null values:-

Null indicate an unknown values and therefore record with NULL values are listed at the end while sorting in ascending order.

SQL> select * from collage order by marks;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

rkdf 1 sanjay 12

rkdf 6 ruchi 21

oist 3 rahul 23

oist 9 31

rkdf 8 satish 32

rkdf 2 salaj 34

oist 5 neelesh 43

ptl 4 sunil 45

rkdf 7 sarika 49

oist 10 abc

oist 11 xyz

11 rows selected.

Notes:-

Student with null marks are display at the end.

Display the record with null values on top:-

We can display the student with null values on top using the “ORDER BY NULLS FIRST”

SQL> select * from collage order by marks nulls first;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

oist 10 abc

oist 11 xyz

rkdf 1 sanjay 12

rkdf 6 ruchi 21

oist 3 rahul 23

oist 9 31

rkdf 8 satish 32

rkdf 2 salaj 34

oist 5 neelesh 43

ptl 4 sunil 45

rkdf 7 sarika 49

11 rows selected.

Notes:-

· The default null are show at last. We can also specify the nulls last in “order by”.

· The nulls first feature is available from oracle9i onwards only. If you have earlier version of oracle then using null first will produce error.

Combining where and order by clause:-

SQL is highly structure language and require the WHERE clause to appears before the ORDER BY clause.

SQL> select * from collage where collage ='oist' order by marks desc;

COLLAGE ROLLNO NAME MARKS

-------------------- ---------- ---------- ----------

oist 10 abc

oist 11 xyz

oist 5 neelesh 43

oist 9 31

oist 3 rahul 23

Notes:-

“marks desc” has displayed the students with NULLS marks on top.

Sorting on date columns:-

Where we have a columns named hiredate which indicate the date of joining the company.

Now we want to display the employees in descending order of joining date.

SQL> select * from emp order by hiredate desc;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7839 KING PRESIDENT 17-NOV-81 5000 10

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7369 SMITH CLERK 7902 17-DEC-80 800 20

14 rows selected.

Notes:-

Do not use TO_CHAR() built in function for date column for DATE column in order by clause because in convert DATE data types to text and the entire sorting will be wrong.

SQL> select * from emp order by to_char(hiredate,'dd/mm/yyyy');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7839 KING PRESIDENT 17-NOV-81 5000 10

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

14 rows selected.

Thank you

Monday, December 30, 2019

TABLE OPERSTION IN ORACLE DATABASE

SORTING IN ORACLE DATABASE

HOW TO TO CALCULATE TABLE SIZE IN MB AND CREATE EXCEL SHEET IN MYSQL 5.7 USING CENTOS 7