ORACLE : ROWNUM and ROWID

ROWID is the physical location or address of row.It is permanent. It is the fastest means of accessing data.

ROWNUM is the sequential number which is assigned to each row during query execution. It basically represents the sequential order in which the rows are fetched. It is temporary.

Lets see below the illustration of ROWNUM and ROWID .

ROWNUM and ROWID

SQL- CAST and CONVERT

Q What is the difference between CAST and CONVERT in SQL Server ?

      CAST: It is used to convert from one data type to another.CAST is ANSI
standard which means that it can also be used in other database applications.

Syntax:  
     CAST ( expression AS data_type [ ( length ) ] )

Cast_Illustration

CONVERT:Like CAST, CONVERT is also used to convert from one data type to another.It is specific to SQL Server.One major advantage of CONVERT is that it has style parameter which can be used for formatting date.

       The syntax of the CONVERT function are in a different order from the CAST function but are almost the same. It does not have AS keyword

Syntax:  

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Convert_Illustration

       Now you will wonder when should we use CAST and when should we use CONVERT. It is very simple. Use CAST when you need to convert from one data type to another as it is a ANSI Standard. Use CONVERT when you want to take advantage of style functionality.

NOTE: Formatting is not possible on DATE datatype or DATETIME data type.First you need to convert to varchar or nvarchar and then apply style. If you use style format in DATE datatype, CONVERT function will ignore style parameter. You can see this in below snapshot. Second query is returning same result as first one because style parameter is ignored.

    

   

SQL- Scenario Based Questions PART- 2

   Consider below Employee table and Department table for answering the questions:-

Employee Table:

EMPLOYEE TABLE

Department Table:

DEPARTMENT TABLE

Q1.Write a SQL query to display Employee Name , Department Name and Location

SELECT EmpName,DeptName,Location FROM Employee E

INNER JOIN Department D 

ON E.DeptNo=D.DeptNo

Q2.Write a SQL query to display total number of Employee and maximum salary under each Department Name .

SELECT D.DeptName,
       Max(Salary)As MaxSalary,
       COUNT(EmpName) As TotEmp
FROM Department D
LEFT OUTER JOIN Employee E
ON E.DeptNo=D.DeptNo
GROUP BY D.DeptName

Q2.Write a SQL query to list maximum salary, minimum salary and average salary designationwise, for department 200 and display only those records which has average salary greater than 10000.

SELECT E.Designation,
       Max(Salary)As MaxSalary,
       Min(Salary)As MinSalary,
       AVG(Salary) As AvgSalary
FROM Employee E
INNER JOIN Department D
ON E.DeptNo=D.DeptNo
WHERE D.DeptNo=200
GROUP BY E.Designation
HAVING AVG(Salary) > 10000

If you like this post, please do share by clicking on G+1 Button below :

SQL- Learn to upload flat file into SQL Server 2008 step by step :

Step 1: Following below is the sample flat file which need to be uploaded in SQL Server.

Flat File

Step 2: Open SQL Server 2008 and go to Database -->System Databases --> tempdb and then right click on the tempdb and go to Tasks --> Import Data

Step 3: On clicking Import Data as shown in previous step, SQL Server Import Export Wizard pops up. Click Next.

SQL Import Export Wizard

Step4 : On Clicking NEXT another window pops up. Do the following below and as shown in pic:
            Select the Data Source as "Flat File Source"
            Browse the Flat file.
            Set Header Row delimeter as "Tab"(depends how your flat file is delimited)
            Tick the check box if first row of flat file contains column names.

SQL Questions PART-3

Q How many columns can a view support ?

       A view can support maximum of 1024 columns.

Q What is the difference between Primary Key and Unique Key ?
    There can be only one one Primary Key per table but there can be more than one unique key per table. Primary Key does not allow NULL  but Unique Key can allow one NULL . Primary Key creates Clustered Index on the column by default where as Unique Key creates non clustered index on the column by default.

Q How to count number of columns in a table in SQL Server ?
      Following query will return number of columns in a table.
SELECT COUNT(*) AS ColumnCount
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName'

Q Up to what level, subqueries can be nested in SQL Server?
     Subqueries can be nested up to 32 level in SQL Server.