Exam Sql Server

Examen SQL Server

1. What's the equivalent of Oracle's DUAL table in SQL Server?

There is no equivalent system table in SQL Server for Oracle's DUAL table. To select constants or literals in Oracle, the DUAL table is used as follows:

SELECT SYSDATE AS [CurrentDateTime] FROM DUAL

In SQL Server, the equivalent of this statement is as follows:

SELECT GETDATE() AS [CurrentDateTime]Basically to select constants or literals in SQL Server, just use the SELECT statement without the FROM clause.
2. How can I return records from one table if it exists on another table?

There are three ways of returning the records from a table that exist in another table based on a key or ID, namely by using the IN, EXISTS and INNER JOIN.

Using the Northwind database, to determine theCustomers with Orders, here's how it is done using the IN method:

SELECT * FROM [dbo].[Customers]
WHERE [CustomerID] IN (SELECT [CustomerID] FROM [dbo].[Orders])

Here's how the query will look like using the EXISTS method:

SELECT * FROM [dbo].[Customers] C
WHERE EXISTS (SELECT 'X' FROM [dbo].[Orders] O
WHERE C.[CustomerID] = O.[CustomerID])

Lastly, here's how the querywill look like using the INNER JOIN method:

SELECT DISTINCT C.*
FROM [dbo].[Customers] C INNER JOIN [dbo].[Orders] O
ON C.[CustomerID] = O.[CustomerID]

The DISTINCT clause in the INNER JOIN method is necessary especially since a customer can have multiple orders in the [dbo].[Orders] table. Without the DISTINCT clause, it will return multiple records for the same customer.

All thesethree methods will return 89 records. Of the three methods, the slowest method in terms of performance is the IN method. Between the EXISTS and the INNER JOIN methods, the EXISTS method has a slight advantage over the INNER JOIN basically because of the DISTINCT clause which slows down its performance.


3. How can I return records from one table that does not exist in another table?There are three ways of returning the records from a table that does not exist in another table based on a key or ID, namely by using the NOT IN, NOT EXISTS and LEFT OUTER JOIN.

Using the Northwind database, to determine the Customers without any Orders, here's how it is done using the NOT IN method:

SELECT *
FROM [dbo].[Customers]
WHERE [CustomerID] NOT IN (SELECT [CustomerID] FROM[dbo].[Orders])

Here's how the query will look like using the NOT EXISTS method:

SELECT *
FROM [dbo].[Customers] C
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Orders] O
WHERE C.[CustomerID] = O.[CustomerID])

Lastly, here's how the query will look like using the LEFT OUTER JOIN method:

SELECT *
FROM [dbo].[Customers] C LEFT OUTER JOIN [dbo].[Orders] O
ON C.[CustomerID] =O.[CustomerID]
WHERE O.[CustomerID] IS NULL

Of the three methods, the method that gives the least performance is the NOT IN method while the method that gives the best performance is the LEFT OUTER JOIN method. For small tables, the difference in performance among the three methods is negligible but for tables with a lot of records the difference in performance is noticeable.

If the tablesbeing compared have an index on the column that is being joined (in this case the [CustomerID] of both [dbo].[Customers] and [dbo].[Orders] tables), then the LEFT OUTER JOIN is a better choice than the NOT EXISTS. On the other hand, if only the second table has an index on the column that is being checked for existence (in this case the [dbo].[Orders] table), then the NOT EXISTS may provide abetter performance.

4. How can I identify duplicate records in a table?

To identify duplicate records in a table, you will use the GROUP BY and HAVING clause of the SELECT statement. Let's say you have a table of Accounts and you want to determine the duplicate records based on the first name, last name and date of birth. Your SQL statement will look as follows:

SELECT [FirstName],...