In this article I would like to give the best and quality content which is useful for all the database engineers and software engineers. The most common SQL queries with multiple examples. These queries are useful queries for database engineers and programmers. Before that i would like to give the importance of SQL in 2020 and then i would like to start with most common SQL queries.
What we need to cover in this story?
- Why SQL is important in 2020?
- What are most common SQL Queries ?
Why SQL is important in 2020?
As we all know that the year 2020 is epidemic year and due to lock-down we require to move most of the operations business in work from home. We can easily work on SQL if we have enough knowledge about the SQL in 2020. Due to following Benefits SQL is one of the most favorite language in 2020.
- Speed : User can retrieve large amount of data fast using SQL queries.
- Less Code : User can use very limited amount of code using SQL.
- Fixed Syntax : The syntax for SQL is fixed and standard so it will be easy for user to write code in SQL.
- Portable : SQL is portable language and user can use SQL in laptop,PC,Servers and some mobile phones.
- Direct communication with database : Through SQL user can communicate with the database.
Most Common SQL queries in 2020 :
In this section i would like to give you most common sql queries in 2020. These queries are not only useful for the interview purpose but also you can use those queries in real life industry examples. These examples are very useful to the database developers,programmers or SQL developers.
Example 1 : How to display the 1 to 25 numbers using SQL?
Query:
Select level as “Numbers” from dual connect by level <=25;
Tip: The example contains the hierarchical queries.
Example 2 : How to remove duplicate rows from the specified table?
Query:
Delete FROM Student_Data WHERE ROWID <>(Select max (rowid) from Student_Data b where rollno=b.rollno);
Tip : The example contains use of ROWID and aggregate functions.
Example 3 : What is Query to find out count of duplicate rows?
Query :
Select Customer_id, count (Customer_id) from Customer
Group by Customer_id
Having count (Customer_id)>1
Order by count (Customer_id) desc;
Tip : We need to know about group by and order by clauses.
Example 4 : What is query to find fifth highest salary of Employee?
Query:
Select * from Employee a Where 5= (Select Count (distinct Salary) from Employee where a.salary<=b.salary;
Tip : We require to know Count function and distinct keyword to resolve this query.
Example 5 : How to create replica of exactly same table?
Query:
Create Table Employees_1 as select * from Employees;
Tip : You need to know the syntax of the query.
Example 6: How to create replica without data of exactly same table?
Query:
Create Table Employees_1 as select * from Employees where 1=2;
Tip : You need to know the condition true=false.
Example 7:What is query to get number of Weekends of current month?
Answer:
SELECT count (*) AS Weekends FROM
(SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt
FROM Dual CONNECT BY LEVEL <= last_day (SYSDATE) — TRUNC (SYSDATE,’mm’) +1)
Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’);
Tip : You should know about SQL hierarchical queries.
Example 8: How to fetch last day of previous month in oracle?
Query:
Select LAST_DAY (ADD_MONTHS (SYSDATE,-1)) from dual;
Tip : The Last_day function is a catch.
Example 9: Write query to find the repeated characters from your name?
Answer:
Select regexp_count (‘AmitA’,’A’) as Repeated_character from dual;
Tip : You must know the function named regexp_count
Example 10: How to get DDL of table in Oracle?
Query:
Select dbms_metadata.get_ddl (TABLE,’table_name’) from dual;
Tip : You must need to know about get_ddl method of package named dbms_metadata.
Example 11 : Write query to find the repeated characters from your name?
Query:
Select regexp_count (‘Pradnya’,’a’) as Repeated_character from dual;
Tip : You must need to know about regexp_count
Example 12 : How to display departmentwise and monthwise maximum salary?
Query:
Select Department_no, TO_CHAR (Hire_date,’Mon’) as Month from Employee group by Department_no, TO_CHAR (Hire_date,’mon’);
Tip : To_char and group by are the catch.
Example 13 : How to get DDL of table in Oracle?
Query :
Select dbms_metadata.get_ddl (TABLE,’table_name’) from dual;
Tip : You must know about dbms_metadata.get_ddl method in detail
Example 14 : How to convert seconds in to time format?
Query:
SELECT
TO_CHAR (TRUNC (2700/3600),’FM9900′) || ‘:’ ||
TO_CHAR (TRUNC (MOD (2700, 3600)/60),’FM00′) || ‘:’ ||
TO_CHAR (MOD (2700, 60),’FM00′)
FROM DUAL;
Where 2700 is seconds.
Output:
00:45:00
Tip : There are multiple ways to do this . The one formal way showed in above query
Example 15 : How to calculate number of rows in table without using count function?
Query:
Select table_name, num_rows from user_tables where table_name=’Employee’;
Tip: User needs to use the system tables for the same. So using user_tables user will get the number of rows in the table.
Example 16 : How to fetch common records from two different tables which has not any joining condition.
Query:
Select * from Table1
Intersect
Select * from Table2;
Tip: Use Intersect keyword for fetching common records.
Example 17 : Display 4 to 7 records from Employee table.
Query:
Select * from (Select rownum as ‘No_of_Row’, E.* from Employee E)
Where No_of_Row between 4 and 7;
Example 18 :Display 10 to 15 records from Employee table.
Query:
Select * from (Select rownum as ‘No_of_Row’, E.* from Employee E)
Where No_of_Row between 10 and 15;
Example 19 : What is query to fetch last record of table?
Answer :
Select * from Customer where Rowid= select max(Rowid) from Customer;
These are above most important queries in year 2020. I hope this article is very useful article to you to get the queries handy and resolve the issues as fast as possible.
Thank you so much Sir!