SQL Queries

SQL Queries


Query for find n th highest salary

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM dbo.empsalary
ORDER BY salary DESC) a
ORDER BY salary

To find the size of database

SELECT * FROM SYSFILE
USE URDBNAME
EXEC SP_HELPDB
EXEC SP_SPACEUSED

Create table from existing table

CREATE TABLE tblname (colnm) AS SELECT colnm FROM existingtblname

Precondition: table should already exist in select clause, number of column should be same and data type should be same.

To copy only structure of table not data

CREATE TABLE tblname AS SELECT *FROM existingtblnm WHERE (any false condition)
Example: Create table tblname as select * from existingtblnm where 1=2

To insert record into table from existing table

Precondition: both the table exist and data type must same
INSERT INTO table1 AS SELECT * FROM table2

Rename table

RENAME oldtblnm TO newtblnm

Drop table

DROP tablenm

To identify how many tables are there

SELECT *FROM TAB

To see the fields of table

DESC tablename

DUAL

In build table in oracle, only 1 row and 1 column,for getting output on screen of operations or system date
Example: select 2*2 from dual, select sysdate from dual

For viewing tables, views, SP,function from database

SELECT name,xtype FROM sysobjects WHERE xtype in (‘u’,’v’,’p’,’fn’) AND name NOT LIKE ‘dt%’ ORDER BY xtype