Colman's Profile

My profile | Relational Database & SQL | Javascript, JQuery | Business Analysis

SQL (Structured Query Language)

SQL is a standard language for accessing and manipulating databases.


SQL Queries example (Bytescout)

 

Some tips about SQL Query

1. To get the table names of a particular database:

For MS SQL Server older version:

SELECT * FROM SYSOBJECTS

WHERE xtype = 'U';

 For MS SQL Server 2005 or later:

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE';

 

2. Example about handling NULL value:

SELECT Item, Price *
(QtyInStock + IFNULL(QtyOnOrder, 0))
FROM Orders
 
3. Analytical query using DENSE_RANK 
 
SELECT eno, dno, salary,
DENSE_RANK() OVER (PARTITION BY dno ORDER BY salary) AS ranking
FROM employee;
 

4. Analytical query using FIRST_VALUE

SELECT eno, dno, salary,

       FIRST_VALUE(salary) IGNORE NULLS
         OVER (PARTITION BY dno ORDER BY salary) AS lowest_salary_in_dept
FROM   employee;
 

5. Analytical query using LAST_VALUE

  SELECT eno, dno, salary,

       LAST_VALUE(salary) IGNORE NULLS
         OVER (PARTITION BY dno ORDER BY salary) AS highest_salary_in_dept
FROM   employee;