These are questions that I have colleted from many
jobs. I hoped these will help any of you in preparing before person to person
or group job interview for .NETFramework and SQL SERVER
Databases. 　
Q. Revisiting Basic Syntax of SQL?
CREATE TABLE Color Table
(code Varchar(2),
ColorValue Varchar (16)
)
INSERT INTO Color Table (code,
colorvalue) VALUE ('b1', 'brown')
DELETE FROM Color Table WHERE code =
'B1'
UPDATE Color Table SET colorvalue =
'black' where code ='b1'
DROP TABLE table Name
(CASCADE|RESTRICT)
GRAND SELECT ON Color Table TO
SHIVKOIRALA WITH GRANT OPTION
REVOKE SELECT, INSERT, UPDATE
(ColorCode) ON ColorTable FROM SHIVKOIRALA
COMMIT [WORK]
ROLLBACK [WORK]
Select * from Person
Address
Select Addressline1, city from
person.Address
Select Addressline1, city from
person.Address Where city = 'SAIGON'
Q. What are "GRANT" and "REVOKE"
statement?
Ans: GRANT statement grants rights to
the objects (Table). While REVOKE does the vice-versa
of it, it removeds rights from the
object.
Q. What is Casscade and Restric in DROP table in
SQL?
Twist: What is "ON DELETE CASSCADE" and
"ON DELETE RESTRICT"?
RESTRICT: specifies that table should
not be dropped if any dependencies (i.e. triggers, stored procedure, primary
key, foreing key etc)exist. So if there are dependencies then error is
generated and the object is dropped.
CASCADE: Specifies that even if there
dependencies go ahead and drop. That means drop the dependencies first and then
the main object also.　So if table has stored Procedures and keys(Primary and
secondary keys) they are drop first and then the table is finally
dropped.
Q. How to import table using "INSERT"
statement?
Ans: I have made a new temporary color
table which is flourished using the belows SQL. Structures of both of the table
should be the same in order that SQL execute properly.
INSERT INTO TempcolorTable
SELECT Code, colorvalue
FROM ColorTable
Q. What is a DLL, DML, and DCL concept in RDBMS
World?
Ans:
DLL: (Data definition language) defines
your database structure. CREATE and ALTER are DLL
statements as they affect the way your
database structure organized.
DML: (Data Manipulation Language) lets
you do basic functionalities like INSERT, UPDATE, DELETE, and MODIFY data in
database.
DCL: (Data Control Language) controls
your DML, and DLL Statements so that your data is proctected and has
consistency.　 COMITT and ROLLBACK are DCL control statements. DCL gurantees
ACID fundatmentals of a transaction.
Q.What are diffeferent types of Joins in
SQL?
Ans: INNER JOIN: Inner join show matches
only when they exist in both tables. Example in the below SQL there are two
tables Customers and Orders and the inner join is made customers.cutomerid and
order.customerid.　 So this SQL Statement on ly give you result with customers
who has orders. If the customer does not have order it will not display that
record.
SELECT Customers.*, Orders.* FROM
Customers INNER JOIN Orders ON
Customers.customerid=Orders.customerid
Q. LEFT OUTER JOIN
Ans: Left join will display all records
in left table of SQL statement.　 In SQL below customers with or with out
orders will be displayed. Order data for customers without Orders appears as
NULL values. For example, you want to determinethe amount ordered by each
customer and you need to see who has not ordered anything as well. You can also
see LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN if you switch the
side of each table.
SELECT Customers.*, Orders.* FROM
Customers LEFT OUTER JOIN Orders ON
Customers.customerid=Orders.customerid
Q. RIGHT OUTER JOIN
Ans: RIGHT join will display all records
in right table of SQL statement.　 In SQL below all Orders with or without
matching customers records will be displayed.　Customer data for Orders
without Customers appears as NULL values. For example, you want to determine if
there are any orders in the data with undefined CustomerID values(says, after a
conversion or sometthing like it). You can also see RIGHT OUTER JOIN as a
mirror image of the LEFT OUTER JOIN if you switch the side of each
table.
SELECT Customers.*, Orders.* FROM
Customers RIGHT OUTER JOIN Orders ON
Customers.customerid=Orders.customerid
Q. What is "CROSS JOIN"?
Ans: "CROSS JOIN" or "CARTESIAN PRODUCT"
combines all rows from both tables. Number of rows will be product of the
number of rows in each table. In real life scenario I can not imagine where we
will want to use a Cartesian product. But there are scenarios where we would
like permutation and combineation probably Cartesian would be the easiest way
to achieve it.
Q. You want to select the first record in a given set of
rows?
Select top 1 * FROM
Sales.person
Q:How do you sort in SQL?
Ans:
Using "ORDER BY" clause, you either sort
data in ascending manner or descending manner.
Select * From Sales.Saleperson order by
salepersonid asc
Select * FROM Sales.saleperson order by
salepersonid desc
Q. How do you select unique rows using
SQL?
Ans: Using the "DISTINCT" clause, for
example if you fire the below SQL in "ADventureWorks", first SQL will give you
distinct value for cities, while order will give you disctinct
rows.
Select distinct city from
person.address
Slect distinct * from
person.address
Q. Can you name some aggregate functions in SQL
Server?
Some of them which every interviewer
will expect:
AVG: Computes the average of a specific set
of values, which can be an expressionlist or set of data records in a
table.
SUM: Returns the sum of a specific set of
values, which can be an expressions list or set of data records in a
table.
COUNT: Computers the number of data records
in a table.
MAX: Returns the maximum value from a
specific set of values, which can an expression list or a set of data records
in a table.
MIN: Returns the minimum value from a
specific set of values, which can be an expression or a set of data records in
a table
Q. What is the default "SORT" order for a
SQL?
Ans: ASCENDING
Q. What is a Self-join?
Ans: If you want two instance s of the
same table you can use self join.
Q. What's the different between DELETE and
TRUNCATE?
Ans: DELETE Table syntax logs the
deletes thus making the delete operation slow.
TRUNCATE Table does not log any
information but it logs informationabout deallocation of data pages of the
table. So TRUNCATE table is faster as compared to DELETE table.
DELETE table rolled back while TRUNCATE
can not be
DELETE table can have criteria while
TRUNCATE can not
TRUNCATE table ca not have
triggers.
Q. Slect address which between '1/1/2007' and
1/4/2007'?
Ans:
SELECT * FROM Person.Address where
modified date between '1/1/2007' and '1/4/2007'
Q. What are wildcard operators in SQL?
Ans: There are basically two types of
operators:
"%" operator (Percentage
Operator)
"%" operator searches for one to many
occurences. So when you fire a query using "%" SQL server searches for one or
many occurences.
"_" Operator (Underscore
operator)
"_" operator is the character defined at
that point. I have sample fiere in a query
Select Addressline1 from person.address
where addressline1 like '_h%'
so all data where second letter is "h"
is returned
Q. What's the difference between "UNION" and "UNION
ALL"
Ans: UNION SQL Syntax is used to select
information from two tables. But it selects only distinct records from both
tables, while UNION ALL slect all records from both of the tables.
To explain it practically below are one
fire"UNION" and one "UNION ALL" in the person Addres in
AdventureWorks:
Select * From
person.address
UNION
Select * from person.address :this
return 19614 rows ( that's mean it removes all duplicates)
Select * From
person.address
UNION ALL
Select * From person.address: this
return 39228 rows("UNION ALL" does not check for duplicates so returns double
the record show above)
Q. What are cursors and what are the situations you will use
them?
Ans: SQL Statements are good for st at a
time operation. So it is good at handling set of data. But there are scenarios
where you want to update row depending on certain criteria. You will loop
through all rows and update data accordingly.　 There's where cursors come
into picture.
NOTE: There are 5%of interviews have
asked questions about Cursors.
Q. What are the steps to create a
cursor?
Ans:
Below are the basic steps to execute a
cursor
·ã€€ã€€ã€€ã€€ Declare
·ã€€ã€€ã€€ã€€ Open
·ã€€ã€€ã€€ã€€ Fetch
·ã€€ã€€ã€€ã€€ Operation
·ã€€ã€€ã€€ã€€ Close and Deallocate
Below is a sample of TSQL display record
which have "@Provinceid" equal "7"
DECLARE @Provinceid int
--Declare Cursor
DECLARE @Provincecursor CURSOR
FOR
SELECT Stateprovincedid
FROM Person.Address
--Open cursor
OPEN provinceCursor
--Fetch data from cursor in to
variable
FETCH NEXT FROM
provincecursor
INTO @Provinceid
WHILE @@FETCH_STATUS = 0
BEGIN
--Do operation according to row
value
If @Provinceid = 7
begin
Print @Provinceid
end
--Fetch the next cursor
FETCH NEXT FROM
provincecursor
INTO @Provinceid
END
--Finally do not forget to close and
deallocate the cursor
CLOSE provincecursor
DEALLOCATE Provincecursor
Q. What are the different Cursor
type?
Ans:
Cursor types are assigned when we
declare a cursor
DECLARE Cursor_name CURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC |
FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS |
OPIMISTIC]
[TYPE_WARNING]
FOR select_Statement
[FOR UPDATE [OF
column_list]]
STATIC: Static cursor is a fixed
snapshot of a set of rows. This fixed snapshot is stored in a temporary
database. As the cursor is using private snapshot any changes to the set of
rows external will not be visible in the cursor while browsing through it. You
can define a static cursor using "STATIC" keyword.
DECLARE cursorname CURSOR
STATIC
FOR SELECT * FROM Tablename
WHERE column1 = 2
KEYSET: the key values of the row s are
saved in tempdb. For instance let's say the cursor has fetch the following
below data. so only the "Supplierid" will be stored in the database. Any new
inserts happening is not reflected in the cursor. But any updates in the
key-set values are reflected in the cursor. Because the cursor is identifield
by key values you can also absolutely fetch them using "FETCH ABSOLUTE 12 FROM
mycursor"
|
SupplieID
|
Supplier Name
|
|
17
|
Evan and Evan limited
|
|
18
|
Han Brothers
|
|
19
|
European Supplier
|
|
20
|
Stocker
|
|
21
|
New supplier
|
DYNAMIC: In DYNAMIC cursor you can see
any kind of changes happening i.e. either inserting new records or changes in
the existing and even deletes. That's Dynamic cursor are slow and have least
performance.
FORWARD_ONLY: As the name suggest they
only move forward and only a one time fetch is done. In every fetch the cursor
evaluated. That means any changes to the data are known, until you have
specified "STATIC" or "KEYSET"
FAST_FORWARD: These types of cursor are
forward only and read_only and in every fetch they are not re-evaluated again.
This makes them a good choice in increase performance.
Q. Waht are "GLOBAL" and "LOCAL"
cursor?
Cursor are global for a connection. By
default cursor are global. That means you can declare a cursor in one stored
procedure and access it outside also. Local Cursor are accessible only inside
the object (Which can be a store procedure, trigger, or a function). You can
declare a cursor as "Local" or "Global" in the "DECLARE" cursor syntax. Refer
the "DECLARE" statement of the cursor in the previous statement.