SQL: Sample Queries

Some random queries for newbies from across the Google Globe:

1. Get all employee details from the employee table
1
Select * from employee

2. Get First_Name,Last_Name from employee table
1
select first_name, Last_Name from employee

3. Get First_Name from employee table using alias name “Employee Name”
1
select first_name Employee Name from employee

4. Get First_Name from employee table in upper case
1
select upper(FIRST_NAME) from EMPLOYEE

5. Get First_Name from employee table in lower case
1
select lower(FIRST_NAME) from EMPLOYEE

6. Get unique DEPARTMENT from employee table
1
select distinct DEPARTMENT from EMPLOYEE

7. Select first 3 characters of FIRST_NAME from EMPLOYEE
Oracle equivalent of SQL Server SUBSTRING is SUBSTR, Query:
1
select substr(FIRST_NAME,0,3) from employee

SQL Server equivalent of Oracle SUBSTR is SUBSTRING, Query:
1
select substring(FIRST_NAME,1,3) from employee

MySQL Server equivalent of Oracle SUBSTR is SUBSTRING. In MySQL start position is 1, Query:
1
select substring(FIRST_NAME,1,3) from employee

8. Get position of 'o' in name 'John' from employee table
Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query:
1
select instr(FIRST_NAME,'o') from employee where first_name='John'

SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query:
1
select CHARINDEX('o',FIRST_NAME,0) from employee where first_name='John'

MySQL Server Equivalent of Oracle INSTR is LOCATE, Query:
1
select LOCATE('o',FIRST_NAME) from employee where first_name='John'

9. Get FIRST_NAME from employee table after removing white spaces from right side
1
select RTRIM(FIRST_NAME) from employee

10. Get FIRST_NAME from employee table after removing white spaces from left side
1
select LTRIM(FIRST_NAME) from employee

11. Get length of FIRST_NAME from employee table
Oracle equivalent of SQL Server Len is Length, Query:
1
select length(FIRST_NAME) from employee

SQL Server equivalent of Oracle,MYSQL Length is Len, Query:
1
select len(FIRST_NAME) from employee

MYSQL equivalent of SQL Server Len is Length, Query:
1
select length(FIRST_NAME) from employee

12. Get First_Name from employee table after replacing 'o' with '$'
1
select REPLACE(FIRST_NAME,'o','$') from employee

13. Get First_Name and Last_Name as single column from employee table separated by a '_'
Oracle Equivalent of MySQL concat is '||', Query:
1
select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE

SQL Server Equivalent of MySQL concat is '+', Query:
1
select FIRST_NAME + '_' +LAST_NAME from EMPLOYEE

MySQL Equivalent of Oracle '||' is concat, Query:
1
Select concat(FIRST_NAME,'_',LAST_NAME) from EMPLOYEE

14. Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table
Oracle Query:
1
2
3
4
select FIRST_NAME,
to_char(joining_date,'YYYY') JoinYear ,
to_char(joining_date,'Mon'),
to_char(joining_date,'dd') from EMPLOYEE

SQL Server Query:
1
2
3
4
select 
SUBSTRING (convert(varchar,joining_date,103),7,4),
SUBSTRING (convert(varchar,joining_date,100),1,3),
SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE

MySQL Query:
1
select year(joining_date),month(joining_date), DAY(joining_date) from EMPLOYEE

15. How can you create an empty table from an existing table?
1
select * into studentcopy from student where 1=2
In the above, we are copying student table to another table with the same structure with no rows copied.

16. How to fetch common records from two tables?
1
Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam

17. How to fetch alternate records from a table?

To display even numbers:
1
2
3
Select studentId from 
   (Select rowno, studentId from student)
where mod(rowno,2)=0

To display odd numbers:
1
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1

18. How to select unique records from a table:
1
Select DISTINCT StudentID, StudentName from Student

19. What are the commands used to fetch first 5 characters of the string?

1
Select SUBSTRING(StudentName,1,5) as studentname from student
and
1
Select RIGHT(Studentname,5) as studentname from student

20. LIKE operator is used for pattern matching, and it can be used as:

a. % – Matches zero or more characters.
1
Select * from Student where studentname like 'a%'

b. _(Underscore) – Matching exactly one character.
1
Select * from Student where studentname like 'jig_'

21. Return all FOREIGN keys inside Database

1
2
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY'




Subscribe to: Posts (Atom)