SQL interview Questions and Answers IV

What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE

• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

• TRUNCATE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log.

• TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

• TRUNCATE cannot be rolled back.

• TRUNCATE is DDL Command.

• TRUNCATE Resets identity of the table

DELETE

• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

• If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

• DELETE Can be used with or without a WHERE clause

• DELETE Activates Triggers.

• DELETE can be rolled back.

• DELETE is DML Command.

• DELETE does not reset identity of the table.

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Yes. Because Transact‐SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

Name 3 ways to get an accurate count of the number of records in a table?

• SELECT * FROM table1

• SELECT COUNT(*) FROM table1

• SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid Difference between stored procedure and function?

1>Procedure can return zero or n values whereas function can return one value which is mandatory.

2>Procedures can have input, output parameters for it whereas functions can have only input parameters.

3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.

4>Functions can be called from procedure whereas procedures cannot be called from function.

5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6>we can go for transaction management in procedure whereas we can’t go in function.

7>Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

8>Error handling is restricted. RAISERROR and @@ERROR are invalid from inside User Defined Functions.

CREATE FUNCTION dbo.Function1



    (



    /*



    @parameter1 datatype = default value,



    @parameter2 datatype



    */



    )



 RETURNS /* datatype */



 AS



    BEGIN



     /* sql statement ... */



    RETURN /* value */



    END

Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

• AVG() – Returns the average value

• COUNT() – Returns the number of rows

• FIRST() – Returns the first value

• LAST() – Returns the last value

• MAX() – Returns the largest value

• MIN() – Returns the smallest value

• SUM() – Returns the sum

Table : “Orders”

Table : “Orders”

1. Average value of the “OrderPrice” fields.

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

2. The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer=’Nilsen’

3. The FIRST() function returns the first value of the selected column

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

Workaround if FIRST() function is not supported:

SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1

The LAST() function returns the last value of the selected column.

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

Workaround if LAST() function is not supported:

SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

5. The MAX() function returns the largest value of the selected column.

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

6. The MIN() function returns the smallest value of the selected column.

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

7. The SUM() function returns the total sum of a numeric column.

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

GROUP BY Statement

The GROUP BY statement is used in conjunction (Join together) with the aggregate functions to group the result-set by one or more columns. – find the total sum (total order) of each customer.

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

GROUP BY More Than One Column

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY customer,OrderDate

HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. – find if any of the customers have a total order of less than 2000.

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000

Scalar functions

SQL scalar functions return a single value, based on the input value.

• UCASE() – Converts a field to upper case

• LCASE() – Converts a field to lower case

• MID() – Extract characters from a text field

• LEN() – Returns the length of a text field

• ROUND() – Rounds a numeric field to the number of decimals specified

• NOW() – Returns the current system date and time

• FORMAT() – Formats how a field is to be displayed

ROUND (): SELECT ROUND(UnitPrice,0) as UnitPrice FROM Products

The ROUND () function is used to round a numeric field to the number of decimals specified.

Convert 12.20 to 12.

NOW (): SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

The NOW() function returns the current system date and time.

Result: Carlsberg || 10.45 || 10/7/2008 11:25:02 AM

FORMAT (): used to format how a field is to be displayed.

SELECT ProductName, UnitPrice, FORMAT(Now(),’YYYY-MM-DD’) as PerDate FROM Products

TOP Clause: used to specify the number of records to return.

SELECT TOP 2 * FROM Products

Result: Return first two Rows in the Table

TOP PERCENT: Select only 50% of the records in the table above.

SELECT TOP 50 PERCENT * FROM Persons

Result: Return 50% Rows in the Table. If a table contains 100 rows, it will return 50 rows.

LIKE Operator: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SELECT * FROM Persons WHERE City LIKE ‘s%’ Result: Returns rows where the City Name starts with ‘S’

SELECT * FROM Persons WHERE City LIKE ‘%s’ Result: Returns rows where the City Name ends with ‘S’

SELECT * FROM Persons WHERE City LIKE ‘%s%’ Result: Returns rows where the City Name contains ‘S’

SELECT * FROM Persons WHERE City NOT LIKE ‘%s%’ Result: Returns rows where the City Name Not contains ‘S’

Note: The “%” sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

IN Operator: specify multiple values in a WHERE clause.

SELECT * FROM Persons WHERE LastName IN (‘Hansen’,’Pettersen’)

BETWEEN Operator: It selects a range of data between two values. The values can be numbers, text, or dates.

SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Petersen’

SELECT * FROM Persons WHERE Amount BETWEEN 1000 AND 5000

Note: Won’t consider about last parameter passed(Take Record from 1000 up to 4999)

SELECT * FROM Persons WHERE LastName NOT BETWEEN ‘Hansen’ AND ‘Petersen’

AND & OR Operators:

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

SELECT * FROM Persons WHERE FirstName=’Tove’ AND LastName=’Svendson’

SELECT * FROM Persons WHERE FirstName=’Tove’ OR FirstName=’Ola’

Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).

Now we want to select only the persons with the last name equal to “Svendson” AND the first name equal to “Tove” OR to “Ola”:

SELECT * FROM Persons WHERE LastName=’Svendson’ AND (FirstName=’Tove’ OR FirstName=’Ola’)

ORDER BY Keyword: The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sort the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword.

SELECT * FROM Persons ORDER BY LastName DESC

INSERT INTO Statement: used to insert a new row in a table.

INSERT INTO Persons VALUES (4,’Nilsen’, ‘Johan’, ‘Bakken 2’, ‘Stavanger’)

UPDATE Statement: used to update existing records in a table.

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

UPDATE Persons SET Address=’Nissestien 67′, City=’Sandnes’ WHERE LastName=’Tjessem’ AND FirstName=’Jakob’

DELETE Statement: used to delete rows in a table.

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

DELETE FROM Persons WHERE LastName=’Tjessem’ AND FirstName=’Jakob’

Delete All Rows: It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

Note: Be very careful when deleting records. You cannot undo this statement!

DELETE FROM table_name

JOIN: The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys.

Different SQL JOINs:

• JOIN: Return rows when there is at least one match in both tables

• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

• FULL JOIN: Return rows when there is a match in one of the tables

INNER JOIN: return rows when there is at least one match in both tables.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

LEFT JOIN: The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders

ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

RIGHT JOIN: returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

FULL JOIN: list all the persons and their orders, and all the orders with their persons.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

UNION: The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA

SELECT INTO: The SELECT INTO statement selects data from one table and inserts it into a different table. The SELECT INTO statement is most often used to create backup copies of tables.

SELECT * INTO Persons_Backup FROM Persons

Note: We can also use the IN clause to copy the table into another database:

SELECT * INTO Persons_Backup IN ‘Backup.mdb’ FROM Persons

Note: We can also copy only a few fields into the new table:

SELECT LastName, FirstName INTO Persons_Backup FROM Persons

SELECT INTO – Joined Tables: Selecting data from more than one table is also possible.

The following example creates a “Persons_Order_Backup” table contains data from the two tables “Persons” and “Orders”:

SELECT Persons. LastName, Orders. OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id

DROP INDEX, DROP TABLE, and DROP DATABASE

The DROP INDEX statement is used to delete an index in a table:

DROP INDEX table_name.index_name

The DROP TABLE statement is used to delete a table.

DROP TABLE table_name

The DROP DATABASE statement is used to delete a database:

DROP DATABASE database_name

TRUNCATE TABLE: only want to delete the data inside the table, and not the table itself?

TRUNCATE TABLE table_name

ALTER TABLE: is used to add, delete, or modify columns in an existing table.

• ALTER TABLE Persons ADD DateOfBirth date

• ALTER TABLE Persons ALTER COLUMN DateOfBirth year

• ALTER TABLE Persons DROP COLUMN DateOfBirth

Backup And Restore:

BACKUP DATABASE [AdventureWorks] TO DISK=N’\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak’

— Full File Backup

BACKUP DATABASE AdventureWorks

FILE=’AdventureWorks_Data’

TO DISK = N’C:\Backup\AdventureWorks.bak’

— Differential File Backup

BACKUP DATABASE AdventureWorks

FILE=’AdventureWorks_Data’

TO DISK = N’C:\Backup\AdventureWorks.bak’

WITH DIFFERENTIAL

RESTORE FILELISTONLY FROM DISK = ‘D:BackUpYourBaackUpFile.bak’

Or

RESTORE DATABASE YourDB

FROM DISK = ‘D:BackUpYourBaackUpFile.bak’

WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,

MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’

/*If there is no error in statement before database will be in multiuser

mode.If error occurs please execute following command it will convert

database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER

GO

SQL Server comes with the following data types for storing a date or a date/time value in the database:

• DATE – format YYYY-MM-DD

• DATETIME – format: YYYY-MM-DD HH:MM:SS

• SMALLDATETIME – format: YYYY-MM-DD HH:MM:SS

• TIMESTAMP – format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!

SQL NULL Values

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

NULL values are treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values.

Note: It is not possible to compare NULL and 0; they are not equivalent.

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

What is BIT datatype and what’s the information that can be stored inside a bit column? Bit datatype is used to store Boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL

What is De-normalization and when would you go for it? As the name indicates, de-normalization is the reverse process of normalization. It’s the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

Define Candidate key, Alternate key, and composite key?

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.

If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

Explain different isolation levels? An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, and Repeatable Read, serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

What’s the maximum size of a row? 8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. Check out SQL Server books online for the page titled: “Maximum Capacity Specifications”.

What’s the maximum number of columns you can create in a table?

• 1024 for fixed-length columns in both all-pages-locked (APL) and data-only- locked (DOL) tables

• 254 for variable-length columns in an APL table

• 1024 for variable-length columns in an DOL table

The maximum size of a column depends on:

• Whether the table includes any variable- or fixed-length columns.

• The logical page size of the database. For example, in a database with 2K logical pages, the maximum size of a column in an APL table can be as large as a single row, about 1962 bytes, less the row format overheads. Similarly, for a 4K page, the maximum size of a column in a APL table can be as large as 4010 bytes, less the row format overheads. See Table 0-1 for more information.

• If you attempt to create a table with a fixed-length column that is greater than the limits of the logical page size, create table issues an error message.

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What are constraints? Explain different types of constraints? Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach? Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two type- Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and its row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What is a deadlock and what is a live lock? How will you go about resolving deadlocks? Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and “Minimizing Deadlocks” in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

What is database replication? What are the different types of replication you can set up in SQL Server? Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

• Snapshot replication

• Transactional replication (with immediate updating subscribers, with queued updating subscribers)

• Merge replication

See SQL Server books online for in-depth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.

What is the wildcard character in SQL? Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’.

What connections does Microsoft SQL Server support? Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and passwords).

Which one is trusted and which one is un-trusted? Windows Authentication is trusted because the username and password are checked with the Active Directory; the SQL Server authentication is un-trusted, since SQL Server is the only verifier participating in the transaction.

Nth Maximum: Table: employee

1 harry 3500.0000

2 jack 2500.0000

3 john 2500.0000

4 xavier 5500.0000

5 steven 7500.0000

6 susana 2400.0000

• Select * From Employee E1 Where (N-1) = (Select Count (Distinct (E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)

• select * from Employee a where N=(select distinct(b.empid)from Employee b where a.empid=b.empid)

Second Maximum:

Select min(Salary) from (select top 2 Salary from employee order by Salary desc) M

//Here M is alias name

Table Size: sp_spaceused emp

Optional parameters to my stored procedures

CREATE PROCEDURE xxx

@param1 VARCHAR (32) = NULL,

@param2 INT = NULL

AS

BEGIN

SET NOCOUNT ON

SELECT Param1 = COALESCE

(

@param1,

‘@param1 was empty’

)

SELECT Param2 = COALESCE

(

RTRIM(@param2),

‘@param2 was empty’

)

END

EXEC dbo.foo @param1=’bar’, @param2=4

EXEC dbo.foo @param1=’bar’