Oracle MySQL & PL/SQL Quick Notes

Database
oracle-mysql-plsql-quick-notes-featured
oracle-mysql-plsql-quick-notes-blog

Hi Guys, We come across few queries in oracle and pl/sql on daily development tasks which we forget/wont remember correctly sometime. This post keeps track on those items. Keep visiting this post for the regular updates.

This can be used to refresh for your oracle and pl/sql interviews as well. Feel free to add your thoughts in the comments section to keep the post updated.

Table of Contents

1. How to view the procedure full source code?

Run the below query to get the procedure name, note without privileges this query will run but returns nothing even if the procedure exist.

select text from user_source where name = 'YOUR_PROCEDURE_NAME' order by line;

2. How to use reserved column name in select query:

Sometime without knowing we would have used the reserved keywords as column names like DATE, DESC ETC.

To select that from the table use that inside the double quotes with the same case as you created.

If you created as DESC then use like this,

SELECT "DESC" FROM SAMPLESCHEMA.SAMPLETABLE;

 3. How to get the list of user created stored procedures ?

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('PROCEDURE');

To get function or package

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PACKAGE');

To get all the procedures (both system and user)

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE');

4. How to view the user created sequence details like max number increment by etc.?

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME='MY_SEQUENCE';

5. How to get all the table row count of a given schema?

SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from schema.'||table_name)),'/ROWSET/ROW/C')) count FROM USER_TABLES;

If you want to get the count only for few tables then give in the where condition,

select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from schema.'|| table_name)),'/ROWSET/ROW/C')) count from user_tables where table_name in ('mytesttable','mytesttable1');

6. How to get all the tables which has given column names?

SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%USER%';

For cluster, tablespace and other stuffs you can also use

SELECT * FROM USER_ALL_TABLES;

In MYSQL:

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%cate%' AND TABLE_SCHEMA='my_db_schema';

7. How to get only the year from the sysdate() ?

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

FOR YOUR COLUMN:

SELECT EXTRACT (YEAR FROM VALIDITY_PERIOD) FROM TESTTABLE;

FOR THE GIVEN INPUT:

SELECT EXTRACT(YEAR FROM TO_DATE('27/12/2016','DD/MM/YYYY')) FROM DUAL;

Extract year with between in oracle sample

SELECT * FROM TESTTABLE WHERE EXTRACT(YEAR FROM VALIDITY_PERIOD) BETWEEN EXTRACT(YEAR FROM SYSDATE) AND EXTRACT(YEAR FROM TO_DATE('20/12/2020','DD/MM/YYYY');

8. How to get all the table names and it’s row counts for the given database schema ?

select table_name, table_rows from information_schema.tables where table_schema = 'czc1';

9. How to enable safe update in Mysql Work bench ?

SET SQL_SAFE_UPDATES = 0;

10. Query to update .jpg to .webp for all the queries

UPDATE `dbschema`.`user` SET `last_name` =CONCAT((select SUBSTRING_INDEX(`last_name`,'.', 1) ),'.webp') WHERE (`user_id` > '0');

11. How to order by desc which contains both number and string  (number on top) ?

Select * from table order by convert(`high_light_text`, decimal) desc;

Here high_light_text may contain the texts like 50% OFF, SUPER DEAL etc. in that case it show 50% OFF on top.

12. How to find out the duplicates in the same table ?

select c1.coupon_home_url, c1.coupon_title ct, count(*) FROM couponz c1 where c1.coupon_home_url = 'www.bata.in' group by c1.coupon_home_url, c1.coupon_title having count(*) > 0;

13. How to delete the duplicate from the same table in mysql ?

delete c1 from couponz c1, couponz c2 WHERE c1.coupon_id < c2.coupon_id AND c1.coupon_title = c2.coupon_title and c1.coupon_home_url = 'www.bata.in';

14. Copying one column values to another column in the same table after some concatenation ?

update store set store_logo_new_column = concat('https://ngdeveloper.com/logos/',store_logo_old_column) where store_id = 1;

15. How to debug “cannot add foreign key constraint” issue in MySQL Workbench ?

When you are trying to add foreign key constraints, you may come across this error. Do remember foreign key constraints can be added only to INNODB engines.

Run this query and find the “LATEST FOREIGN KEY ERROR” section for the exact / more detailed error details.

SHOW ENGINE INNODB STATUS;

16. How to remove duplicates from the same table ?

Point 13 also solves the same problem, but mysql really takes long time to perform deleting the duplicates in the same table, and if you run performing the same operation through java/any other programming language, deleting the duplicates takes really so long time than usual.

So I am posting this solution, which worked very nicely for me to remove the duplicates with some conditions on the same tables.

16.1 Create a temp table.

create temporary table coupon_temp_table (id int);

16.2 Copy the unique id values which needs to be removed 

insert  coupon_temp_table
        (id)
select  coupon_id
from    coupon c
where   exists
        (
        select  *
        from    coupon c1
        where   c1.coupon_title = c.coupon_title
                and c1.store_store_id = c.store_store_id
                and c1.coupon_end_date = c.coupon_end_date
                and c1.coupon_type = c.coupon_type
                and c1.coupon_status = c.coupon_status
                and c1.coupon_code = c.coupon_code
                and c1.coupon_id > c.coupon_id
        );

Here based on the 6 conditions (coupon title, coupon end date, coupon type, coupon status, coupon code) it filters out the duplicates and takes the one and inserts to temp table.

16.3 Remove the duplicates

DELETE FROM coupon WHERE coupon_id IN (SELECT id FROM coupon_temp_table);

This removes all the duplicates.

17. How to update bit column in mysql ? (boolean value)

SET SQL_SAFE_UPDATES = 0;
-- update store set `store_is_active` = 1 where store_is_active is null;
update store set `store_is_active` = 1;
SET SQL_SAFE_UPDATES = 1;

18. How to alter the table in oracle (query example)

ALTER TABLE coupons ADD is_featured char(1) default 'N';

19. How to create the table from another oracle table ?

create table coupons_bk as (select * from coupons);

20. How to create the view from another view in oracle ?

create view coupons_external_bk as (select * from coupons_external);

21. How to grant the access from source to destinations in Oracle ?

For the table, we need to provide the grant access this way, here from JAVADOMAIN we are granting the access for NGDEVELOPER.

grant ALTER, DELETE, INDEX, INSERT, UPDATE, SELECT, REFERENCES on "JAVADOMAIN"."POSTS" to "NGDEVELOPER"

Grant is different for sequences and the example is this, the main difference is only alter and select can be granted for the sequence.

grant ALTER, SELECT on "JAVADOMAIN"."POST_SEQ" to "NGDEVELOPER"

22. How to find out the list of columns by column name like in Oracle?

NGDEVELOPER is the username here referred as the owner for the table.

SELECT OWNER, TABLE_NAME, COLUMN_NAME from all_tab_columns WHERE column_name LIKE '%post%' and owner = "NGDEVELOPER";

In MySQL:

SELECT * FROM information_schema.`COLUMNS` C WHERE TABLE_SCHEMA = 'ng_coupons' AND C.column_name like '%post%';

23. How to create the sequence in Oracle ?

CREATE SEQUENCE POST_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999'

25. How to query the list of tables matching the given column name ?

select * from all_tab_columns where column_name like '%post%' and table_name like 'NGDEV%';

26. How to get the total number of columns in the given table in oracle ?

select count(*) from user_tab_columns where lower(table_name)='coupons';

27. Spooling or downloading the file as csv from the query results in Oracle ?

spool D:/Ngdeveloper/coupons-loaded-Sep9-2020.csv
select /*csv*/ coupon coupon_title, coupon_store, to_char(coupon_expiry_dt,'HH24:MI:SS') || 'IST' time from
ngdeveloper.coupons
where
coupon_store in ('amazon','flipkart') AND
coupon_status = 'PENDING'
order by coupon_expiry_dt asc;
spool off;

28. How to add the date in the where column oracle example ?

updated_dt – date column in the coupons table. We can pass the date to the where condition the below way,

select * from coupons where trunc(updated_dt)=to_date(''19-11-2020,'dd-mm-yyyy');

29. How to show multiple columns with the single group by column in oracle example ?

coupon_type is the only column used for the group by, where as the multiple columns like coupon_id, coupon_type and updated_dt is used in the select columns.

But we can’t use like coupon_id, updated_dt, but we can use max(coupon_id), max(updated_dt) like this, and this actually works in most of the scenarios. if this can also be available in the multiple rows then you may wont get the result like how you want, so check and modify the queries accordingly.

select max(coupon_id), coupon_type, count(coupon_type), max(updated_dt) from coupons group by coupon_type;

30. How to update all values of the column with lower case values of same / another column in mysql?

UPDATE opr SET opr_slug = LOWER(opr);

31. How to display all the constraints of the given table in Oracle ?

Please note that table_name must be given in uppercase, otherwise it wont work.

select * from user_constraints where table_name = 'NGDEV_POSTS';

32. How to list the total tables count of the database / schema in MySQL ?

SELECT count(*) AS TOTALTABLES  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'ngdev02';

// prints the count like 147

Leave a comment

Leave a Reply