The Primary Key
A primary key is a column that makes each row of data in the table unique in a relational database.
UNION and UNION ALL
UNION
returns the results of two queries minus the duplicate rows.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, Union eliminate duplicate rows.
INTERSECT
INTERSECT
returns only the rows found by both queries.
MINUS
Minus
returns the rows from the first query that were not present in the second.
What is Normalization?
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table).
Different types of joins:
1)
Equi-Joins
SELECT O.ORDEREDON, O.NAME, O.PARTNUM,
P.PARTNUM, P.DESCRIPTION
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
2)
Non-Equi-Joins
SELECT O.NAME, O.PARTNUM, P.PARTNUM,
O.QUANTITY * P.PRICE TOTAL
FROM ORDERS O, PART P
3)Right Outer Join:
SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE,
O.NAME, O.PARTNUM
FROM PART P
RIGHT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54
4)Left Outer Join:
SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE,
O.NAME, O.PARTNUM
FROM PART P
LEFT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54
5)
Joining a Table to Itself(Self Join):
SELECT *
FROM TABLE1, TABLE1
Sub Queries:
SELECT p.product_name FROM product p WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);
Correlated Subqueries:
SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT DESCRIPTION
FROM PART P
WHERE P.PARTNUM = O.PARTNUM)
Same As
SELECT O.ORDEREDON, O.NAME,
O.PARTNUM, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P
WHERE P.PARTNUM = O.PARTNUM
AND P.DESCRIPTION = 'ROAD BIKE'
Using EXISTS, ANY, and ALL
The usage of the keywords
EXISTS
,
ANY
, and
ALL
is not intuitively obvious to
the casual
observer.
EXISTS
takes a subquery as an argument and returns
TRUE
if the
subquery returns anything and
FALSE
if the result set is empty. For example
SELECT NAME, ORDEREDON
FROM ORDERS
WHERE EXISTS
(SELECT *
FROM ORDERS
WHERE NAME ='TRUE WHEEL')
Differences between Stored procedures and User defined functions 1) Stored procedures may or may not return values
But function should return value
2)
Stored procedure cannot be used in the select/where/having
clause
But function can be called from select/where/having clause 3)Temporary table (derived) cannot be created on function.
But it can be created in stored procedures
4)Stored procedure can call the user defined functions ,
But the function cannot call the stored procedures.5) Stored procedures can have input and output parameters. But the function can have only input parameters.
6) But the function can have only input parameters.
But the function
can do only select operation.
Execution plan of stored procedure and function is already created.
Defining Database Structures (DDL)
New Term Data Definition Language, DDL, is the part of SQL that allows a database user to create and
restructure database objects, such as the creation or the deletion of a table. The main DDL commands discussed during following hours include the following: CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX ALTER INDEX DROP INDEX
Manipulating Data (DML)
New Term Data Manipulation Language, DML, is the part of SQL used to manipulate data within
objects of
a relational database.
There are three basic DML commands: INSERT
UPDATE DELETE
Selecting Data (DQL)
Though comprised of only one command, Data Query Language (DQL) is the most concentrated focus of
SQL for a relational database user. The command is as follows: SELECT
This command, accompanied by many options and clauses, is used to compose queries against a relational database. Queries, from simple to complex, from vague to specific, can be easily created. The SELECT command is discussed in exhilarating detail during Hours 7 through 16.
Transactional Control Commands
In addition to the previously introduced categories of commands, there are commands that allow the user to
manage database transactions.
� COMMIT Used to save database transactions � ROLLBACK Used to undo database transactions
� SAVEPOINT Creates points within groups of transactions in which to ROLLBACK � SET TRANSACTION Places a name on a transaction
Creating a Table from an Existing Table
CREATE TABLE PRODUCTS_TMP AS SELECT * FROM PRODUCTS_TBL;
The ALTER TABLE Command
ALTER TABLE TABLE_NAME [MODIFY] [COLUMN COLUMN_NAME][DATATYPE|NULL NOT NULL] [RESTRICT|CASCADE]
[DROP] [CONSTRAINT CONSTRAINT_NAME] [ADD] [COLUMN] COLUMN DEFINITION
DROP TABLE PRODUCTS_USER1.TMP;
Foreign Key Constraints:
A foreign key is a column in a child table that references a primary key in the parent tableType of constraints
Primary key constraint
Unique key constraint
Foreign key constraint
Not null constraint
Check constraint
Normalizing a Database:
Normalization is a process of reducing redundancies of data in adatabase
1NF: There should not be repeating row in table(dividing a table in to multiple table) 1)There are no columns with repeated or similar data
2)Each data item cannot be broken down any further 3)Each row is unique i.e. it has a primary key 4)Each field has an unique name
2NF:
A 2NF relation is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key.3NF: In order to be in Third Normal Form, a relation must first fulfill the requirements to be in Second Normal Form. Additionally, all attributes that are not dependent upon the primary key must be eliminated.
The COMMIT command is the transactional command used to save changes invoked by a transaction to the
Database
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without
rolling back the entire transaction
Find nth max row
SELECT TOP 1 salary FROM (
SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a ORDER BY salary
EXISTS Condition
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
The EXISTS condition can be used in any valid SQL statement - select, insert, update, or
delete.
Example #1
Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS
condition:
SELECT *
FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);
This select statement will return all records from the suppliers table where there is at least one
record in the orders table with the same supplier_id.
Example #2 - NOT EXISTS
The EXISTS condition can also be combined with the NOT operator.
For example,
SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
This will return all records from the suppliers table where there are no records in the orders
table for the given supplier_id.
The following is an example of a delete statement that utilizes the EXISTS condition:
DELETE FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);
Example #4 - UPDATE Statement
The following is an example of an update statement that utilizes the EXISTS condition:
UPDATE suppliers
SET supplier_name =( SELECT customers.name
FROM customers
WHERE customers.customer_id =
suppliers.supplier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);
Example #5 - INSERT Statement
The following is an example of an insert statement that utilizes the EXISTS condition:
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
Structured
Query
Language (
SQL
)
Correlated Subqueries
SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT DESCRIPTION
FROM PART P
ANY
compared the output of the following subquery to each row in the query,
returning
TRUE
for each row of the query that has a result from the subquery.
ALL
returns
TRUE
only if all the results of a subquery meet the condition.
Oddly
enough,
ALL
is used most commonly as a double negative, as in this query:
binary large object (
BLOB
)
What is an Index?
An index is a performance-tuning method of allowing faster retrieval of records.
The syntax for creating a index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
UNIQUE indicates that the combination of values in the indexed columns must be unique.
COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The
statistics are then used by the optimizer to choose a "plan of execution" when SQL statements
are executed.
For example:
CREATE INDEX supplier_idx
ON supplier (supplier_name);
In this example, we've created an index on the supplier table called supplier_idx. It consists of
only one field - the supplier_name field.
We could also create an index with more than one field as in the example below:
CREATE INDEX supplier_idx
ON supplier (supplier_name, city);
SQL CREATE VIEW Statement
In SQL, a view is a virtual table(which does not consumes disk space) based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Modifying Data in a View
As you have learned, by creating a view on one or more physical tables within a
database, you can create a virtual table for use throughout an SQL script or a
database
application. After the view has been created using the
CREATE VIEW...SELECT
statement,
you can update, insert, or delete view data using the
UPDATE
,
INSERT
, and
DELETE
CREATE VIEW LATE_PAYMENT AS
2 SELECT * FROM BILLS;
View created.
SQL> UPDATE LATE_PAYMENT
2 SET AMOUNT = AMOUNT * 1.10;
Following is a list of the most common restrictions you will encounter while
working with views:
You cannot use
DELETE
statements on multiple table views.
l
You cannot use the
INSERT
statement unless all
NOT NULL
columns used in the
underlying table are included in the view. This restriction applies because the
SQL processor does not know which values to insert into the
NOT NULL
columns.
lIf you do insert or update records through a join view, all records that are
updated must belong to the same physical table.
l
If you use the
DISTINCT
clause to create a view, you cannot update or insert
records within that view.
l
You cannot update a virtual column (a column that is the result of an expression
or function)
A view from a single table can be updated where as a view from multiple tables can not be updated.
create unique index unique_id_name on BILLS(ACCOUNT_ID, NAME)
Clustered index:
When a clustered index is used, the
physical arrangement of the data within a table is modified. Using a clustered
index
usually results in faster data retrieval than using a traditional, nonclustered
index.
create unique clustered index id_index
on BANK_ACCOUNTS(ACCOUNT_ID)
Temporary Tables
temporary tables, which are simply
tables that exist temporarily within a database and are automatically dropped
when
the user logs out or their database connection ends.
create table #albums (
2> artist char(30),
3> album_name char(50),
4> media_type int)
drop table #albums
select * from #albums
Cursors
Database cursors enable you to select a group of data, scroll through the group
of
records (often called a recordset), and examine each individual line of data as
the
cursor points to it. You can use a combination of local variables and a cursor to
individually examine each record and perform any external operation needed
before
moving on to the next record
1. Create the cursor.
2. Open the cursor for use within the procedure or application.
3. Fetch a record's data one row at a time until you have reached the end of the
cursor's records.
4. Close the cursor when you are finished with it.
5. Deallocate the cursor to completely discard it.
DECLARE 2 3 v_employeeID employee.id%TYPE; 4 v_FirstName employee.first_name%TYPE; 5 v_LastName employee.last_name%TYPE; 6 7
8 v_city employee.city%TYPE := 'Vancouver';
9 10
11 CURSOR c_employee IS
12 SELECT id, first_name, last_name FROM employee WHERE city = v_city ; 13 BEGIN 14 15 16 OPEN c_employee; 17 LOOP 18
19 FETCH c_employee INTO v_employeeID, v_FirstName, v_LastName;
20 DBMS_OUTPUT.put_line(v_employeeID);
21 DBMS_OUTPUT.put_line(v_FirstName);
22 DBMS_OUTPUT.put_line(v_LastName);
23
24 EXIT WHEN c_employee%NOTFOUND;
25 END LOOP;
26 27
28 CLOSE c_employee;
29 END;
How does one eliminate duplicates rows from a table?
Method 1:
SQL> DELETE FROM table_name A WHERE ROWID > ( 2 SELECT min(rowid) FROM table_name B 3 WHERE A.key_values = B.key_values);
Method 2:
SQL> create table table_name2 as select distinct * from table_name1; SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;
Generate New Guid (uniqueidentifier) in
SQL Server
INSERT INTO
Employees
(EmployeeID, Name, Phone)
VALUES
(NEWID(), 'John Kris', '99-99999')
Stored Procedures
The concept of stored procedures is an important one for the professional
database programmer to master. Stored procedures are functions that contain
potentially large groupings of SQL statements.
Exec push_return_to_hst(29020048695,2010,9,0);
Create procedure push_return_to_hst (tin number, yr number, mon number, qtr
number,
typ char) as
BEGIN
if typ = '100' then
begin
insert into hst_web_ret_100 (select * from web_ret_100 where TIN_NO=tin
and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate);
delete from web_ret_100 where TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate;
end;
else
if typ = '105' then
begin
insert into hst_web_ret_105 (select * from web_ret_105 where TIN_NO=tin
and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate );
delete from web_ret_105 where TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate;
end;
else
if typ = '120P2' then
begin
insert into hst_web_ret_120_p2 (select * from web_ret_120_p2 where
TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate);
delete from web_ret_120_p2 where TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate;
end;
else
if typ = '120P3' then
begin
insert into hst_web_ret_120_p3 (select * from web_ret_120_p3 where
TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate);
delete from web_ret_120_p3 where TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate;
end;
else
if typ = '120P4' then
begin
insert into hst_web_ret_120_p4 (select * from web_ret_120_p4 where
TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate);
delete from web_ret_120_p4 where TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate;
end;
else
begin
insert into hst_web_ret_kteg (select * from web_ret_kteg where
TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate);
delete from web_ret_kteg where TIN_NO=tin and PERD_YEAR=yr and
PERD_MONTH=mon and QUARTER_CODE=qtr and STATUS='SBM'
and RET_DATE<sysdate;
end;
end if;
end if;
end if;
end if;
end if;
end if;
commit;
END push_return_to_hst;
Triggers
A trigger is essentially a special type of stored procedure that can be executed in
response to one of three conditions:
l
An
UPDATE
lAn
INSERT
lA
DELETE
CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column[, column]...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}]...
ON [schema.]table
[[REFERENCING { OLD [AS] old [NEW [AS] new]
| NEW [AS] new [OLD [AS] old]}]
FOR EACH ROW
[WHEN (condition)] ]
CREATE OR REPLACE TRIGGER Print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab FOR EACH ROW
WHEN (new.Empno > 0) DECLARE
sal_diff number; BEGIN
sal_diff := :new.sal - :old.sal;
dbms_output.put('Old salary: ' || :old.sal); dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff); END;
Restrictions on Using Triggers
You must observe the following restrictions when you use triggers:
lTriggers cannot be created on temporary tables.
l
Triggers must be created on tables in the current database.
lTriggers cannot be created on views.
l
When a table is dropped, all triggers associated with that table are
automatically dropped with it.
Difference between Truncate and Delete
• TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
• You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .
• You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.
• A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
Types of Indexes
Non-clustered
The data is present in random order, but the logical ordering is specified by the index.
The data rows may be randomly spread throughout the table. The non-clustered index tree
contains the index keys in sorted order, with the leaf level of the index containing the
pointer to the page and the row number in the data page. In non-clustered index:
• The physical order of the rows is not the same as the index order.
• Typically created on column used in JOIN, WHERE, and ORDER BY clauses. • Good for tables whose values may be modified frequently.
Microsoft SQL Server
creates non-clustered indexes by default when CREATE INDEX
command is given. There can be more than one non-clustered index on a database table.
There can be as many as 249 nonclustered indexes per table in SQL Server 2005 and 999
nonclustered indexes per table in SQL Server 2008. It also creates a clustered index on a
primary key by default.
[1]Clustered
Clustering alters the data block into a certain distinct order to match the index, resulting in
the row data being stored in order. Therefore, only one clustered index can be created on a
given database table. Clustered indices can greatly increase overall speed of retrieval, but
usually only where the data is accessed sequentially in the same or reverse order of the
clustered index, or when a range of items is selected.
SQL Function Types
This section lists the different types of SQL functions.The
"SQL Functions Overview"
provides
an explanation of each function:
Table 3-1 SQL Function Types
Number Functions
CEIL
MOD
FLOOR
ROUND - Number Function
TRUNC
Character Functions
CHR
LTRIM
TRANSLATE
CONCAT
REPLACE
TRIM
INITCAP
RPAD
UCASE See
UPPER
LCASE See
LOWER
ROUND - Date
Function
UPPER
LOWER
SUBSTR
USER
LPAD
SUBSTRB
Character Functions Returning Number Values
ASCII
INSTR
LENGTHB
BIT_LENGTH (See
LENGTH
)
INSTRB
OCTET_LENGTH
(See
LENGTH
)
CHAR_LENGTH (See
LENGTH
)
LENGTH
POSITION
Date Functions
ADD_MONTHS
HOUR
SYSDATE
CURDATE
LAST_DAY
TIMESTAMPADD
CURRENT_DATE
MINUTE
TIMESTAMPDIFF
CURRENT_TIME
MONTH
TRUNC
CURRENT_TIMESTAMP
MONTHNAME
WEEK
CURTIME
MONTHS_BETWEEN YEAR
DAYNAME
NEXT_DAY
DAYOFMONTH
NOW
DAYOFWEEK
ROUND - Date
Function
DAYOFYEAR
SECOND
CAST
TO_CHAR
TO_DATE
CONVERT
TO_NUMBER
Other Functions
CASE
GREATEST
LOCATE
DATABASE
IFNULL (See
CASE
and
NVL
)
NVL
DECODE
INTERVAL
SUBSTR
EXTRACT
LEAST
USER
Grouping Functions
AVG
STDDEV
COUNT
SUM
MAX
VARIANCE
MIN
SQL
AUTO INCREMENT
Field
Syntax for SQL Server
The following SQL statement defines the "P_Id" column to be an auto-increment primary key field in the "Persons" table:CREATE TABLE Persons (
P_Id int PRIMARY KEY IDENTITY, LastName varchar(255) NOT NULL, FirstName varchar(255),
Address varchar(255), City varchar(255) )
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
By default, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.
To specify that the "P_Id" column should start at value 10 and increment by 5, change the identity to
IDENTITY(10,5).
To insert a new record into the "Persons" table, we will not have to specify a value for the "P_Id" column (a unique value will be added automatically):
VALUES ('Lars','Monsen')
The SQL statement above would insert a new record into the "Persons" table. The "P_Id" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".
Syntax for Oracle
In Oracle the code is a little bit more tricky.You will have to create an auto-increment field with the sequence object (this object generates a number sequence). Use the following CREATE SEQUENCE syntax: CREATE SEQUENCE seq_person
MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10
The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.
To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):
INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen')
The SQL statement above would insert a new record into the "Persons" table. The "P_Id" column would be assigned the next number from the seq_person sequence. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".
Calling the stored procedure
oCmd.CommandText = "push_return_to_hst";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add("tin", OracleType.Number).Value = tin_no; oCmd.Parameters.Add("yr", OracleType.Number).Value = year; oCmd.Parameters.Add("mon", OracleType.Number).Value = month; oCmd.Parameters.Add("qtr", OracleType.Number).Value = quarter; oCmd.Parameters.Add("typ", OracleType.VarChar).Value = ret_type; oCmd.Connection = oCn;
oCn.Open();
oCmd.ExecuteNonQuery();
oCmd.Parameters[""].Direction = ParameterDirection.Output; public enum ParameterDirection
{
// Summary:
// The parameter is an input parameter.
Input = 1, //
// Summary:
// The parameter is an output parameter.
Output = 2, //
// Summary:
// The parameter is capable of both input and output.
InputOutput = 3, //
// Summary:
// The parameter represents a return value from an operation such as a stored
// procedure, built-in function, or user-defined function.
ReturnValue = 6, }
Calling a function
cmd.CommandText = "select datbasename.dbo.functionname(@param1) as functionresult"
cmd.CommandType = CommandType.text
cmd.Parameters.AddWithValue("param1", "param1val")
Can we use Truncate command on a table which is referenced by FOREIGN KEY? - No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? - Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
What is a Join in SQL Server? - Join actually puts data from two or more tables into a single result set.
Can you explain the types of Joins that we can have with Sql Server? - There are three types of joins: Inner Join, Outer Join, Cross Join(Self Join).
What are the authentication modes in SQL Server? - Windows mode and mixed mode (SQL & Windows).
What is Cascading referential integrity constraint?
Cascading referential integrity constraints allow you to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys point.
Primary key column does not accept any null values, where as a unique key column accept one null value.
What are the 2 types of Temporary Tables in SQL Server? 1. Local Temporary Tables
2. Global Temporary Tables
What is the difference between Local and Global Temporary Tables? Local Temporary Tables:
1. Prefixed with a single pound sign (#).
2. Local temporary tables are visible to that session of SQL Server which has created it. 3. Local temporary tables are automatically dropped, when the session that created the temporary tables is closed.
Global Temporary Tables:
1. Prefixed with two pound signs (#).
2. Global temporary tables are visible to all the SQL server sessions.
3. Global temporary tables are also automatically dropped, when the session that created the temporary tables is closed.
Can you create foreign key constraints on temporary tables? No.
How many Clustered and Non Clustered Indexes can you have per table?
Clustered Index - Only one Clustered Index per table. A clustered index contains all of the data for a table in the index, sorted by the index key. Phone Book is an example for
Clustered Index.
Non Clustered Index - You can have multiple Non Clustered Indexes per table. Index at the back of a book is an example for Non Clustered Index.
What are the two types of Triggers in SQL Server?
1. After Triggers : Fired after Insert, Update and Delete operations on a table.
2. Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.
Can you create a view based on other views?
Yes, you can create a view based on other views. Usually we create views based on tables, but it also possible to create views based on views.
A view is nothing more than compiled SQL query.
What are indexed views? Or
What are materialized views?
When you create an index on a view, the data gets physically stored in the view. So, when we issue a select query against an indexed view, the data is retrieved from the index without having to go to the underlying table, which will make the select statement to work slightly faster. However, the disadvantage is, INSERT, UPDATE and DELETE operations will
become a little slow, because every time you insert or delete a row from the underlying table, the view index needs to be updated. Inshort, DML operations will have negative impact on performance
How will you copy the structure of a table without copying the data?
create table NEWTable AS select * from OLDTABLE where 1=2 Can we generate a trigger for two tables?
No.
What is difference between SET & SELECT?
SET : set statement is used to the update query. ex: update tablename set name='xxx' where no=101.
SELECT : the select statement is used to display the all data's in the table ex: select * from tablename.
Get top two records without Top keyword
Select * from tableName where rownum<3 order by columnName;
What is de-normalization and when would you go for it?
As the name indicates, de-normalization is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced
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.
Define candidate 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.
CREATE INDEX myIndex ON myTable (myColumn)
What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
What are the steps you will take to improve performance of a poor performing query?
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
MDF file will contains the data,LDF file contains the log
What are the different ways of moving data/databases between servers and databases in SQL Server?
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are:
BACKUP/RESTORE,
Detaching and attaching databases,
Replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO,
Which is more faster - in or exists?
Can a primary key contain more than one columns?
YesHow to get the row values as comma
separated by a query
SELECT ENAME ||''' '||HIREDATE||''' '||SAL FROM EMP
How to get first 5 Records then next 5
records till end of row count in SQL
-Oracle
Select * from emp where rownum<6;thenSelect * from emp where rownum<11 MINUS select * from emp where rownum <6;and so on
Can you use a commit statement within a database trigger?
No.Difference between decode and case.
in which case we are using case and in which case we are using decode? with an example.
Decode is the sql functions for the replacement of If- Then -else logic and case is similar to
If-Then-else logic except that we can make logical comparison of columns involved in the
case structures.
Ex: select case snum when snum > 10 then 'High' when snum>5 then 'Low' end from sales..
Ex: select decode(snum,10,'high',5,'low') from sales... that is we cannot make logical comparison of columns in Decode() functions
SQL Query to Convert Columns into Rows
name, year, value ---john, 1991, 1000 john, 1992, 2000 john, 1993, 3000 jack, 1991, 1500
jack, 1992, 1200 jack, 1993, 1340 mary, 1991, 1250 mary, 1992, 2323 mary, 1993, 8700 and so on
I want to perform a sql query to return results like this: year, john, Jack, mary ...
1991, 1000, 1500 1250 1992, 2000, 1200, 2323 1993, 3000, 1340, 8700 select year,
2 max( decode( name, 'john', value, null ) ) "JOHN", 3 max( decode( name, 'jack', value, null ) ) "JACK", 4 max( decode( name, 'mary', value, null ) ) "MARY" 5 from
6 (
7 select name, year, value 8 from t1
9 )
10 group by year ;
nvarchar data type is using to store Unicode data, The "N" in NVARCHAR means uNicode
wild cards in sql
_ for single character substitution and % for multi-character substitution
Difference between "VARCHAR" and "VARCHAR2" datatypes?
VARCHAR is an abbreviation for variable-length character string.
Both are related to varible length but difference that varchar stores only 2000 byte character of data and varchar2 stores 4000 byte charecter of data .
What is Deadlock?
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.
Which date function is used to find the difference between two dates? select datediff (dd,'2-06-2007','7-06-2007')
How to retrieving the data from 11th column to n th column in a table?
Select * from tableName where rownum between startValue and endValue.
What is output of following query Select 2 from employee?
This query displays the value 2 for all rows in a specified table. Difference between Stored Procedure and Trigger?
Triggers get invoked automatically when the event specified in it gets fulfilled, it can't return any value.So user have no control over it's invocation.Whereas stored procedure can be called as per application requirement and can get output parameters.
What are two methods of retrieving SQL? 1-select
2-using cursour
What is diffrence between Co-related sub query and nested sub query?
Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
For example,
Correlated Subquery:
select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)
Nested Subquery:
select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
What is SQLPlus?
SQLPlus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.
How to display duplicate rows in a table? select * from emp
having count(empid)>1
I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated.
SELECT NAME,COUNT(NAME) FROM TABLE1 GROUP BY NAME HAVING COUNT(NAME) > 1
Why do we use cache and no cache in sequences? What is the benefit we have in using them?
CACHE or NOCACHE
CACHE is used to control the efficiency of the sequence. The default for CREATE
SEQUENCE is
CACHE 20. This means that Rdb will update the root file only when 20 calls to NEXTVAL
have
been performed. The larger the cache the fewer times the database needs to be updated.
Using the NOCACHE clause disables caching. In this case each NEXTVAL reference will
cause
an update to the database root file
Whats the back end processes when we type "Select * from Table"?
First it will look into the System Global Area (SGA) weather the query is been exectued earlier.
If it exist, it would retrive the same output present in memory.
If not the query we typed is complied and the resulting parse tree and excution plan is been stored in SGA. Then query gets executed and output is given to the application.
Can a view be updated/inserted/deleted? If Yes, then under what conditions? A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
How do we select the employees detail from the table who were joined in the last 5yrs? select * from employees
where year(hiredate) >= year(getdate())-5 What's an SQL injection?
SQL Injection is when form data contains an SQL escape sequence and injects a new
SQL query to be run.
In order to avoid SQL Injection, pass on the required column data in the form of parameters prefixed with @. In this way the whole SQL Query becomes parametrised
and is treated as a single command.Any invalid command(Query) will not be executed by the server hence.
Why do stored procedures reduce network traffic ?
When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
Procedure is a already compiled query,its execution plan is already created when you compile it.
What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.
What are the purposes and advantages stored
procedure?
• Large queries can be avoided
• Reduces network traffic since they need not be recompiled
• Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
Determine when to use stored procedure to complete
SQL Server tasks?
• If a large piece of code needs to be performed repeatedly, stored procedures are ideal
• When hundreds of lines of SQL code need to be sent; it is better to use stored procedure through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
• When security is required.
What are the different types of Storage Procedure?
1. Temporary Stored Procedures - SQL Server supports two types of temporary procedures: local and global. A local temporary
procedure is visible only to the connection that created it. A global
temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be created by any user.
2. System stored procedures are created and stored in the
master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name
master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)
3. Automatically Executing Stored Procedures - One or more
stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system
administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.
4. User stored procedure.
How will you handle Errors in Sql Stored Procedure?
INSERT NonFatal VALUES (@Column2) IF @@ERROR <>0
BEGIN
PRINT 'Error Occured' END
How will know whether the SQL statements are executed?
When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or
procedure. If no value is specified on RETURN, a stored procedure returns the value 0. The stored procedures return a value of 0 when no errors were encountered. Any nonzero value indicates an error occurred.
Why one should not prefix user stored procedures with sp_?
It is strongly recommended that you do not create any stored
procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
0. The stored procedure in the master database.
1. The stored procedure based on any qualifiers provided (database name or owner).
2. The stored procedure using dbo as the owner, if one is not specified. Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the
database name.
How will you handle Errors in Sql Stored Procedure?
INSERT NonFatal VALUES (@Column2) IF @@ERROR <>0
BEGIN
END
Difference between view and stored procedure?
Views can have only select statements (create, update, truncate, delete statements are not allowed) Views cannot have "select into", "Group by" "Having", "Order by".
What is a Function & what are the different user defined functions?
Function is a saved Transact-SQL routine that returns a value.
User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.
0. Scalar Functions
Functions are scalar-valued if the RETURNS clause specified one of the scalar data types
1. Inline Table-valued Functions
If the RETURNS clause specifies TABLE with no accompanying column list, the function is an inline function.
2. Multi-statement Table-valued Functions
If the RETURNS clause specifies a TABLE type with columns and their data types, the function is a multi-statement table-valued function.
How can I enforce to use particular index?
You can use index hint (index=) after the table name. SELECT au_lname FROM authors (index=aunmind).
Types of Triggers
o
AFTER triggers (Only Tables)
o
INSTEAD OF triggers (Tables or Views)
what is the main difference between after trigger and instead trigger?
After Trigger fires an operation after any
insert,update,delete operations performed on a table.
Instead Of Trigger fires an operation instead of
performing
user specified operation.
u can create instead of trigger on views but u can not
create after triggers on views
How many types of triggers are there in Sql Server 2005?
There are two types of triggers
• Data Definition language (DDL) triggers
DML triggers (implementation) will run when INSERT, UPDATE, or DELETE
statements modify data in a specified table or view.
DDL triggers will run in response to DDL events that occur on the server such as
creating, altering, or dropping an object, are used for database administration tasks
What are the different modes of firing triggers?
After Trigger: An AFTER trigger fires after SQL Server completes all actions
successfully
Instead of Triggers: An INSTEAD OF trigger causes SQL Server to execute the code in
the trigger instead of the operation that caused the trigger to fire.
How many types of cursors are there in SQL Server?Answ er # 3
There are 4 types of cursor.
1) Static: Cursor can move to any
record but the changes on
the data can't be seen.
2) Dynamic: Most resource
extensive. Cursor can move
anywhere and all the changes on
the data can be viewed.
3) forward-only: Cursor moves one
step forward. Can't move
backwards.
4) Key setdriven: Only Updated
data can be viewed. Deleted
and Inserted data cannot be
viewed.
What is user-defined function? Explain its types i.e.
scalar and Inline table value user-defined function.
User defined functions are created and defined by the user. They are created as per users needs. They may or may not accept parameters. They can be used to create joins and simple to invoke as compared to stored procedures
Types:
Scalar user defined: returns values as one of the scalar data types. Text, timestamp, image data types
are not supported. It may or may not accept parameters.
Inline table value user defined : it returns a table data type. These functions can pass parameters to the
