Code Greener -

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]

Selecting Random Records for Auditing

PL/SQL

SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        ORDER BY
                dbms_random.value
        )
WHERE rownum <= 1000

http://www.orafaq.com/wiki/SQL_FAQ#Can_one_retrieve_only_the_Nth_row_from_a_table.3F

http://www.orafaq.com/wiki/SQL_FAQ#Can_one_retrieve_only_rows_X_to_Y_from_a_table.3F

Data Types in SQL

http://developer.mimer.com/documentation/Mimer_SQL_Reference_Manual/Syntax_Rules4.html

CAST and CONVERT -
http://msdn.microsoft.com/en-us/library/ms191530.aspx

Data types can be converted either implicitly or explicitly:

Implicit conversions are not visible to the user.
SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.
Explicit conversions use the CAST or CONVERT functions.
The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. For example, the following CAST function converts the numeric value of $157.27 into a character string of '157.27':
Copy
CAST ( $157.27 AS VARCHAR(10) )
Use CAST instead of CONVERT if you want Transact-SQL program code to comply with ISO. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#i9118

http://www.thesmartcodes.com/how-to-convert-datatypes-in-sql/

DECLARE @firstname AS VARCHAR(30)
DECLARE @lastname AS VARCHAR(30)
DECLARE @LEVEL AS VARCHAR(30)
DECLARE @pay_amount AS DECIMAL(18, 2)
DECLARE @pay_date AS DATETIME
SET @firstname = ‘Subhash’
SET @lastname = ‘Pande’
SET @LEVEL = ‘First’
SET @pay_amount = 1009.30
SET @pay_date = GETDATE()
SELECT @firstname AS First_Name ,
@lastname AS Last_Name ,
@level AS Levels ,
( CONVERT(VARCHAR, @pay_amount) + ‘ ‘ + ‘$’ ) AS Pay_Amount ,
CONVERT(VARCHAR, @pay_date, 106) AS PayDate

JOINS - SQL

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid

Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)

SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_2685-SQL-Server-Joins-Explained-Logical-Joins.html

http://www.w3schools.com/Sql/sql_join.asp

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 table

CURSORS

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors. Here is an example:

If you have to give a flat hike to your employees using the following criteria:

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.

http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

DBCC Commands

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc.

http://www.sql-server-performance.com/tips/dbcc_commands_p1.aspx

Making a Poor Performing Query more efficient.

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

Download the white paper on performance tuning SQL Server from Microsoft web site. Don't forget to check out sql-server-performance.com

http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

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

For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"

Whar 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 types. Clustered indexes and non-clustered indexes. When you craete 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 it's 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 t ime, 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.

SOURCE - http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

DELETE TABLE VS. TRUNCATE TABLE

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

SOURCE - http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

IDENTITY

IDENTITY columns and timestamp columns can't have defaults bound to them.

SOURCE - http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

Candidate Key, Primary Key, Alternate Key, 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.

Primary Key vs. Unique Key

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.

Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

SOURCE - http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

Relationships

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.

SOURCE - http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

Denormalization vs. Normalization

Denormalization 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.

Aggregation and Partitioning Data

select ename, deptno, sal,
  sum(sal) over   --overall running total, no partition
    (order by deptno, ename) running_total,
  sum(sal) over   --running total by deptno w/ partition
    (partition by deptno
     order by ename) department_total,
  avg(sal)  over  --avg for the dept
    (order by deptno) avg_sal,
  avg(sal) over   --running average
    (order by deptno, ename) running_avg
from emp
order by deptno, ename;

Result set is located here: http://dbaforums.org/oracle/index.php?s=&showtopic=20307&view=findpost&p=60489

Top N

http://www.petefreitag.com/item/59.cfm
Microsoft SQL Server

SELECT TOP 10 column FROM table

PostgreSQL and MySQL

SELECT column FROM table
LIMIT 10

Oracle

SELECT column FROM table
WHERE ROWNUM <= 10

Sybase

SET rowcount 10
SELECT column FROM table

Firebird

SELECT FIRST 10 column 
FROM table

Hierarchical Queries

http://www.techrepublic.com/article/understand-oracle-10gs-new-hierarchical-query-options/5800412

Oracle 10g adds new pseudo columns to the SQL Hierarchical Queries that use START WITH . . . CONNECT BY . . . clauses. For more than a decade, Oracle SQL has had the ability to cause a query to follow a hierarchical relationship. For instance, you could specify a starting condition and continue to child rows using one or more connection conditions.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm

START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.

The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP exists in the data. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which rows contain the loop. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information.
In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,
```
... PRIOR expr = expr
or
... expr = PRIOR expr
```
If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. For example:
```
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and 
           PRIOR account_mgr_id = customer_id ...
```
PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.

Both the CONNECT BY condition and the PRIOR expression can take the form of an uncorrelated subquery. However, the PRIOR expression cannot refer to a sequence. That is, CURRVAL and NEXTVAL are not valid PRIOR expressions.
You can further refine a hierarchical query by using the CONNECT_BY_ROOT operator to qualify a column in the select list. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries by returning not only the immediate parent row but all ancestor rows in the hierarchy.

Creating a Pivot or Crosstab Query in SQL - It is possible...

Robert Vollman of

http://thinkoracle.blogspot.com/2005/09/pivot-and-crosstab-queries.html as did his Collegue

http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::
F4950_P8_DISPLAYID,F4950_P8_CRITERIA:766825833740
addressed this question:

A simple pivot query is accomplished by basically doing the following:
1. Add some kind of count or row number to your query, if necessary for the grouping
2. Then use your (revised) original query as a sub-query
3. Use "decode" to turn rows into columns (ie. a "sparse" matrix).
4. Use "max" to "squash" the multiple rows you moved to columns, into single rows. Don't forget to group by.
(Note: it gets more complicated if you don't know how many columns you'll need).

Notes about Triggers vs. CHECK

A Trigger can be used to create or modify data. Primarily, a trigger can be used to validate business rules.

Business rule defines and can restrict data to meet a particular business practice.
For example: I could state that all patient records must contain a home telephone number.

Triggers and CHECK constrains are similar but have a few key differences. The trigger can base decisions on other columns, tables, and databases. However, it can only validate data that is changed after the time that you create the trigger. It cannot evaluate data that existed prior to applying the trigger.

http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder

Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.

Also check out books online for 'inserted table', 'deleted table' and COLUMNS_UPDATED()

There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.

Add customer records to two tables simultaneously.

Create Procedure AddCustomerRecord

(@FirstName varchar(20),

@LastName varchar(40),

@Address varchar(150),

@City varchar(45),

@StateorProvince char(2)

@PostalCode varchar(10),

@PhoneNumber varchar(10),

@Year smallint,

@Make varchar(30),

@Model varchar(30),

@Color varchar(30),

@LicensePlate# varchar(10))

DECLARE @CustID int

--insert a new customer row

INSERT INTO Customer

(FirstName, LastName, Address, City, StateorProvince, @PostalCode, @PhoneNumber);

VALUES

(@FirstName, @LastName, @Address, @City, @StateorProvince, @PostalCode, @PhoneNumber);

--save the new CustomerID number that was auto-generated

Select @CustID = @@identity;

--insert a new vehicle row

(@Year, @Make, @Model, @Color, @ LicensePlate@, Null, @CustID);

GO;

@@identity is a Transact-SQL Variable...

You would call the procedure like this

Execute AddCustomerVehicle 'Erika', 'Last', '123 Penny Lane', 'St. Louis', 'Mo', '20115', '3137770986', '2011', 'Jeep', 'Cherokee', 'Kiwi', '78ghMAN';

Stored Procedure (Multi-Step) SQL

CREATE PROCEDURE PartsInventbyState

(@ PartInt, @BeginDate smalldatetime, @EndDate smalldatetime, @State char(2))

IF @BeginDate < '2009-01-01'

RETURN -1;

--working table

CREATE TABLE @temp

(Invoice_No int);

--return a list of all Invoice_Nos that used the given part between the specified dates

INSERT INTO #temp

(JobTicketID);

Select inv.Invoice_No

FROM Invoice_No As inv

JOIN Invoice_NoDetail invd ON inv.Invoice_No = invd.Invoice_No

JOIN PartUsed As pu On invd.Invoice_No = pu.Invoice_No

AND invd.SKU = pu.SKU

AND pu.PartID = @PART

WHERE inv.StartDate BETWEEN @BeginDate AND @EndDate;

--Join the temporary table and return the vehicles owned by customers who live in a state

Select c.LastName, c.FirstName, v.VehicleYear, v.Make, v.Model,

FROM Customer AS c

JOIN Vehicle AS v ON c.CustomerID = v.CustomerID

JOIN Invoice_No As inv ON c.CustomerID = inv.CustomerID

JOIN #temp ON inv.Invoice_No = #temp.Invoice_No

WHERE c.StateOrProvince = @State;

--remove the temporary table

Drop TABLE #temp;

SQL Procedure holding year variable

Create Procedure ProcessA

(@expiration int)

SELECT c.FirstName, c.LastName, v.expirationofpolicy_yr, v.Make, v.Model

FROM Customer As c

JOIN Vehicle As v

ON c.CustomerID = v.CustomerID

WHERE v.expirationofpolicy_yr = @expiration

ORDER BY c.LastName, c.FirstName, v.expirationofpolicy_yr

----------------------------------------------------

EXECUTE ProcessA 2010

Business reason for running the simple procedure above– I want to know every single policy in 2010 and compare it with a couple other reporting systems to verify that various communications have been sent out. This procedure would have been written as a stop-gap until the remainder of the steps could be added.