Interview Questions on SQL Server Programming - Part 1


1.      What are the basic hardware components of a client/server system?
a.       Client
b.      Server (e.g., file server, db server)
c.       Middle servers (web server or application server)
d.      Network
2.      What are the basic software components of a client/server system?
a.       Server software (e.g., SQL Server, Oracle)
b.      Application software or client software
c.       Data access API (e.g., ADO.NET for SQL interface)
3.      Contrast RDBMS with other data models such as:
a.       File system
b.      Other database systems (hierarchical, network, and OO)
4.      Major History of ANSI-SQL
a.       SQL – 1989-SQL1 (like DB2, not stringent)
b.      SQL – 1992-SQL2 (three levels of compliance – entry, intermediate, and full)
c.       SQL – 1999-SQL3 (dropped the 3 levels, introduced a core specifications  plus 9 packages for market niches, also support for Objects)
d.      SQL – 2003-SQL4 (XML, standardized sequences, identity columns)
5.      What are the major clauses of DML?
a.       Select
b.      Insert
c.       Update
d.      Delete
6.      What are the major clauses of DDL?
a.       Create
b.      Alter
c.       Drop
7.      What are the major clauses of DCL (Data Control Language)?
a.       GRANT
b.      REVOKE
8.      What are the major clauses of TCL (Transaction Control Language)?
a.       Commit
b.      Rollback
c.       Save Point
9.      Some basic syntax
--A statement that creates a new database
CREATE DATABASE AP

--A statement that creates a new table
CREATE TABLE Invoice
(     InvoiceID INT NOT NULL IDENTITY PRIMARY KEY,
      VendorID INT NOT NULL REFERENCES Vendors(VendorID)
)

--A statement that adds a new column to the table
ALTER TABLE Invoice
ADD BalanceDue MONEY NOT NULL

--A statement that deletes the new column
ALTER TABLE Invoice
DROP COLUMN BalanceDue

--A statement that creates an index on the table
CREATE INDEX IX_Invoice_VendorID
ON Invoice(VendorID)
10.  How to query a single table?
11.  How to join data from two or more tables?
12.  How to add, update, and delete data in a table?
13.  How to work with views?
14.  How to work with stored procedures, triggers, and user-defined functions?
15.  Common Data access models
a.       .NET applicationàADO.NETàSQL Server
                                                  i.      Dataset(Data Table)-Data Adapter-Command-Connection-SQL Server
b.      Java applicationàJDBCàJava DriversàSQL Server
c.       VB6 applicationàADOàOLE DBàSQL Server
16.  How to enable remote connections?
17.  How to attach a database?
18.  How to detach a database?
19.  How to back up a database?
20.  How to restore a database?
21.  How to set the compatibility level for a database?
22.  How to use SQLCMD to run a SQL Script that attaches databases?
a.       Need 2 files. File 1 – the .bat control file as below

@ECHO off
:: attach.bat batch file for
:: Murach's SQL Server 2008 For Developers
:: company: Mike Murach & Associates, Inc.
:: date:    Sept 8, 2008
::
:: Uses SQLCMD utility to run a SQL script that attaches
:: five sample databases.
::
:: Uses Windows Authentication mode, which is the
:: default for SQL Server installed on Windows
:: versions NT or above.

ECHO Attempting to attach databases...
sqlcmd -S localhost -E /i attach.sql
ECHO.
ECHO If no error message is shown, then the databases attached correctly.
ECHO.
PAUSE

b.      File 2 – the .sql SQL Server script file - attach.sql

CREATE DATABASE AP
  ON PRIMARY (FILENAME = 'C:\Murach\SQL Server 2008\Databases\AP.mdf')
  LOG ON (FILENAME =     'C:\Murach\SQL Server 2008\Databases\AP_log.ldf')
  FOR ATTACH
GO

CREATE DATABASE AP_AllObjects
  ON PRIMARY (FILENAME = 'C:\Murach\SQL Server 2008\Databases\AP_AllObjects.mdf')
  LOG ON (FILENAME =     'C:\Murach\SQL Server 2008\Databases\AP_AllObjects_log.ldf')
  FOR ATTACH
GO

CREATE DATABASE Examples
  ON PRIMARY (FILENAME = 'C:\Murach\SQL Server 2008\Databases\Examples.mdf')
  LOG ON (FILENAME =     'C:\Murach\SQL Server 2008\Databases\Examples_log.ldf')
  FOR ATTACH
GO

CREATE DATABASE ProductOrders
  ON PRIMARY (FILENAME = 'C:\Murach\SQL Server 2008\Databases\ProductOrders.mdf')
  LOG ON (FILENAME =     'C:\Murach\SQL Server 2008\Databases\ProductOrders_log.ldf')
  FOR ATTACH
GO
23.  What is predictate? Sargable predicate vs. non-sargable predicate? Example for each? Why sargable matters?
a.       A predicate can be evaluated as T/F/Unknown
b.      Sargable predicate – the condition can take advantage of index seek

SELECT BR.OzBookingID
FROM Booking.Request BR INNER JOIN Cruise.Detail CD
ON CD.CruiseCode = BR.CruiseCode
WHERE SUBSTRING(CruiseName,1,6) = 'Hawaii'--Non-Sargable!!!

SELECT BR.OzBookingID
FROM Booking.Request BR INNER JOIN Cruise.Detail CD
ON CD.CruiseCode = BR.CruiseCode
WHERE CruiseName LIKE 'Hawaii%' --sargable!!!
24.  What is the preferred ways of column alias?
a.       AS alias
b.      Not alias = theColumn
25.  The style parameter for the CONVERT function
a.       DateTime/Small Datetime

select convert(char(10),getdate(),1)  --03/12/13, no century
select convert(char(10),getdate(),101)--03/12/2013, with century

b.      Float and real

0 (default)
A maximum of 6 digits. Use in scientific notation, when appropriate.
1
Always 8 digits. Always use in scientific notation.
2
Always 16 digits. Always use in scientific notation.

c.       Money/Small money
0 (default)
No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1
Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2
No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.
126
Equivalent to style 2 when converting to char(n) or varchar(n)

USE AP

SELECT 'Invoice: #' + InvoiceNumber
    + ', dated ' + CONVERT(char(8), PaymentDate, 1)
    + ' for $' + CONVERT(varchar(9), PaymentTotal, 1)
FROM Invoices
-- Invoice: #989319-457, dated 05/07/08 for $3,813.33

26.  DISTINCT vs. ALL (All is the default, often omitted)
27.  Have to use Order By if using TOP
28.  TOP WITH TIES – Additional rows will be returned if they are tied with the last row!

USE AP

SELECT TOP 5 WITH TIES VendorID, InvoiceDate
FROM Invoices
ORDER BY InvoiceDate

/* - returning 6 rows, the additional 6th row is tied with the fifth row.
VendorID  InvoiceDate
122  2008-04-08 00:00:00
123  2008-04-10 00:00:00
123  2008-04-13 00:00:00
123  2008-04-16 00:00:00
123  2008-04-16 00:00:00
123  2008-04-16 00:00:00*/

29.  Order of precedence of operators: NOT, AND, OR
30.  Differences for the wildcard symbols:
a.       [ ] – any single in the bracket                    LIKE ‘[DOP]’
b.      _ - any single character                              LIKE ‘COMPU_ER%’
c.       [-] – any single in the range                       LIKE ‘N[A-J]’
31.  Use Full-Text Search instead of LIKE if ever possible for performance reason (cannot create an index on the wildcard pattern for the LIKE operator).
32.  How to work with tables from different databases
The syntax of a fully·qualified object name
linked_server.database.schema.object

A join with fully-qualified table names
SELECT VendorName, CUstLaatName, CUstFirstName,
    VendorS tate AS State, VendorCity AS City
    FROM DBServer.AP.dbo.Vendors AS Vendors
   JOIN DBServer.ProductOrders.dbo.CUstomers AS CUBtomers
   ON Vendors.VendorZipCode = CUstomera.CUstZip
   ORDBR BY State, City
The same join with partially-qualified table names
SELECT VendorName, CUstLaatName, CUstFirstName,
VendorS tate AS State, VendorCity AS City
FROM Vendors
JOIN ProductOrders .. Customers AS CUstomers
ON Vendors.VendorZipCode = CUstomers.CUstZip
ORDBR BY State, City

If the server or database name is the same as the current server or database name, or if
the schema name is dbo or the name of the users default schema. You can omit that part
of the name to create a partially-qualified object name. If the omitted name falls between
two other parts of the name, code two periods to indicate that the name is omitted.
33.  Add and drop a linked server

USB master;
BXEC sp_ addlinkedserver
@server =' DBServer' ,
@srvproduct = ‘ ’,
@provider_'SQLNCLI ' ,
@datasrc = ' localhost\SqlBxpress'

sp_dropserver [ @server = ] 'server'
     [ , [ @droplogins = ] { 'droplogins' | NULL} ]

--The following example removes the remote server ACCOUNTS and all associated remote logins from the local instance of SQL Server.

sp_dropserver 'ACCOUNTS', 'droplogins';

34.  Self-join must use table alias or correlation name.
35.  Explicit and implicit inner join syntax
a. Explicit – use JOIN and ON
                                                  i.      Separate join condition and search condition
b. Implicit – use WHERE
                                                  i.      Prior to SQL-92
                                                ii.      Theta syntax
                                              iii.      If no WHERE, it is a CROSS JOIN
36.  Explicit and Implicit outer join
a. Explicit – LEFT, RIGHT, FULL
b. Implicit – LEFT (*=) AND RIGHT (=*)
                                                  i.      FOR SQL SERVER 2000 AND EARLIER
                                                ii.      DEPRECATED IN 2005
37.  Explicit and Implicit CROSS JOIN
a. EXPLICIT –CROSS JOIN
b. IMPLICIT – SELECT * FROM TABLE1, TABLE2 (NO WHERE)
38.  The SET operator: UNION(ALL), INTERSECT, EXCEPT
39.  THE ORDER OF OPERATION:
a. FROM ...WHERE …GROUP BY…HAVING…SELECT …ORDER BY
b. Be aware of the mix of single column and the aggregates in SELECT, use GROUP BY
40.  Having vs. WHERE
a. Having has to use columns in SELECT, whereas WHERE can use columns NOT in SELECT
b. Having can use aggregate functions, WHERE cannot use aggregates
c.  If no aggregate function, it is OK to use either Having or WHERE for the search condition