Read a SQL Server table into a list using Python + pyodbc

import pyodbc as p
server = 'ServerName'
database = 'DatabaseName'
userid = 'UserName'
pwd = 'UserPassword'

connStr = ( r'DRIVER={SQL Server};SERVER=' +
            server + ';DATABASE=' + database + ';' +
            'UID=' + userid + ';PWD='+pwd+';')        
lst = []
conn = p.connect(connStr)
dbCursor = conn.cursor()
sql = ('SELECT ColumnName AS FieldValue FROM tblTableName') 
dbCursor = conn.cursor()
dbCursor.execute(sql)
for row in dbCursor:
    lst.append(row.FieldValue)        
conn.close()
print lst

Shrinking Truncate Log File

USE YourDatabaseName
GO
--Run this script to get your TransactionLogName
SELECT * FROM sys.sysfiles
GO
DBCC SHRINKFILE(TransactionLogName, 1)
BACKUP LOG YourDatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TransactionLogName, 1)
GO

How to kill session remotely

1. Download PsTools at this address http://technet.microsoft.com/en-us/sysinternals/bb896649.aspx and then run Windows command prompt at the folder containing PsTools
2. Run psexec command: D:\PsTools> psexec \\RemoteMachineName_Or_IPAddress -u UserName -p Password cmd
Wait a moment to allow to connect to remote server. A new command prompt will appear like that:
C:\WINDOWS\system32>
3. Run: qwinsta to get session ID need to log off: C:\WINDOWS\system32>qwinsta
A list of sessions will display, choose one you need to kill
4. Run: C:\WINDOWS\system32>logoff SessionID /v

Create linked server to Access 2007 accdb file

EXEC master.dbo.sp_addlinkedserver @server = N'AccessLinkedServerName', @srvproduct=N'Access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'D:\Projects\MyAccessDB.accdb'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AccessLinkedServerName', @locallogin = NULL , @useself = N'False'

SET NUMERIC ROUNDABORT

What will be the output of the following scripts?

SET NUMERIC_ROUNDABORT ON
SET ARITHABORT ON
GO
DECLARE @c NUMERIC(5, 2),
   @a NUMERIC(5, 4), 
   @b NUMERIC(5, 4)
SET @a = 1.1234
SET @b = 1.1234 
SELECT @c = @a + @b
SELECT @c

A. 2.24
B. 2.25
C. Error message: Arithmetic overflow error converting numeric to data type numeric.
D. 2.2468

Answer:[C]
Highlight to find out the answer.

Get numbers from 1 to 999 with a query

WITH tblDigit(d) AS (
   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
   SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
   SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
   SELECT 0)
SELECT t1.d + t2.d * 10 + t3.d*100 AS Number
FROM   tblDigit t1, tblDigit t2, tblDigit t3
WHERE t1.d + t2.d + t3.d >0
ORDER BY t1.d + t2.d*10 + t3.d*100

Defragment indexes

You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragment the indexes in an online manner. What command should you use?

A. DBCC DBREINDEX
B. DBCC INDEXDEFRAG
C. ALTER INDEX with the REORGANIZE option
D. ALTER INDEX with the LOB_COMPACTION option set to OFF

Answer:[C]
Highlight to find out the answer.

Logical join

Do you know the difference between logical join and physical join?
What are not logical join?

A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. MERGE JOIN
E. SELF JOIN
F. CROSS JOIN

Answer:[D]
Highlight to find out the answer.

Page size again

Do you remember pages in SQL Server?
Your SQL Server database log file is 5MB. How many pages are allocated to it?

A. 0
B. 640
C. 320
D. 80
E. It depends

Answer:[A]
Highlight to find out the answer.

Page size

Do you remember pages in SQL Server?
In SQL Server, the page size is _____

A. 1KB
B. 8KB
C. 16KB
D. 64KB
E. 125KB
F. 256KB

Answer:[B]
Highlight to find out the answer.

Find output

How many rows will be returned in the following scripts?
DECLARE @Test TABLE(TestValue CHAR(1))
INSERT INTO @Test
SELECT 'A'UNION SELECT 'B'
UNION SELECT 'C'UNION SELECT 'D'
UNION SELECT 'E'
SELECT DISTINCT CAST(5 * RAND(CHECKSUM(NEWID())) + 1 as INT) AS RandomValue
FROM @Test

A. 1
B. 2
C. 3
D. 4
E. 5
F. It depends

Answer:[F]
Highlight to find out the answer.

Find output

How many rows will be returned in the following scripts?
DECLARE @Test TABLE(TestValue CHAR(1))
INSERT INTO @Test
SELECT 'A'UNION SELECT 'B'
UNION SELECT 'C'UNION SELECT 'D'
UNION SELECT 'E'
SELECT DISTINCT CAST(5 * RAND() + 1 as INT) AS RandomValue
FROM @Test

A. 1
B. 2
C. 3
D. 4
E. 5
F. It depends

Answer:[A]
Highlight to find out the answer.

Access To MySQL

Access To MySQL: "Access to MySQL is a small program that will convert Microsoft Access Databases to MySQL.

* Wizard interface.
* Transfer data directly from one server to another.
* Create a dump file.
* Select tables to transfer.
* Select fields to transfer.
* Transfer password protected databases.
* Supports both shared security and user-level security.
* Optional transfer of indexes.
* Optional transfer of records.
* Optional transfer of default values in field definitions.
* Identifies and transfers auto number field types.
* Command line interface.
* Easy install, uninstall and upgrade.

- Sent using Google Toolbar"

Find output

How many rows will be returned in the following scripts?

DECLARE @DBMS TABLE(DBMS VARCHAR(15))
INSERT INTO @DBMS
SELECT 'SQL Server'
UNION SELECT 'MySQL'
UNION SELECT 'DB2'
UNION SELECT 'Oracle'
UNION SELECT 'PostgreSQL'
UNION SELECT 'SQLite'
UNION SELECT 'Access'
UNION SELECT 'EnterpriseDB'
DECLARE @DBMSValue VARCHAR
SET @DBMSValue = 'SQL'
SELECT * FROM @DBMS WHERE DBMS LIKE '%' + @DBMSValue + '%'

A. 0
B. 2
C. 4
D. 6
E. 8
F. Error will be generated

Answer:[D]
Highlight to find out the answer.

Find output

What will be the output of the following scripts?

CREATE TABLE #tblTest(TestValue VARCHAR(2) COLLATE French_CI_AI) 
DECLARE @Var1 CHAR(1), @Var2 CHAR(1)
SET @Var1='A'

WHILE @Var1 <= 'Z' 
BEGIN
 SET @Var2='A'
 WHILE @Var2 <= 'Z'
 BEGIN
  INSERT INTO #tblTest SELECT @Var1 + @Var2
  SET @Var2 = CHAR(ASCII(@Var2) + 1)
 END
 SET @Var1 = CHAR(ASCII(@Var1) + 1)
END

SELECT DISTINCT TestValue FROM #tblTest
WHERE TestValue LIKE '_'

DROP TABLE #tblTest

A. 0
B. 1
C. 26
D. 676 = 26*26
E. It depends on tempdb collation
F. Error is generated

Answer:[A]
Highlight to find out the answer.

Get all valid collations

You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL Server collations.
SELECT *
FROM fn_helpcollations()

Get all database sizes on server

DECLARE @DBSize TABLE([DatabaseName] [nvarchar](75) NOT NULL,
      [Size] [decimal] NOT NULL,
      [Name] [nvarchar](75) NOT NULL,
      [Filename] [nvarchar](90) NOT NULL);
DECLARE @FixedDrive TABLE ([Drive] [char](1) NOT NULL,
      [MBFree] [decimal] NOT NULL);

INSERT INTO @DBSize
EXEC sp_MSforeachdb 'SELECT ''?'' as DatabaseName, 
      CASE WHEN [?]..sysfiles.size * 8 / 1024 = 0 THEN 1 
       ELSE [?]..sysfiles.size * 8 / 1024 END AS size, 
      [?]..sysfiles.name, 
      [?]..sysfiles.filename 
     FROM [?]..sysfiles';

INSERT INTO @FixedDrive
EXEC xp_fixeddrives;

SELECT RTRIM(CAST(DatabaseName AS VARCHAR(75))) DatabaseName,
        Drive,Filename, Cast(Size AS INT) Size,
        Cast(MBFree AS VARCHAR(10)) MB_Free
FROM @DBSize t1
 INNER JOIN @FixedDrive t2 ON LEFT(t1.Filename, 1) = t2.Drive
GROUP BY DatabaseName, Drive, MBFree, Filename, Cast(Size AS INT)
ORDER BY Drive, Size DESC;

SELECT Drive [Total Space Used],
        Cast(Sum(Size) AS VARCHAR(10)) [Total Size],
        Cast(MBFree AS VARCHAR(10)) [MB Free]
FROM @DBSize t1
        INNER JOIN @FixedDrive t2 ON LEFT(t1.Filename, 1) = t2.Drive
GROUP BY Drive, MBFree;

Create database from mdf file

USE [master]

GO

CREATE DATABASE [TestDB] ON

( FILENAME = N'D:\temp\testdb.mdf' )

FOR ATTACH

SQL Injection Prevention Cheat Sheet - OWASP

SQL Injection Prevention Cheat Sheet - OWASP: "SQL Injection Prevention Cheat Sheet

- Sent using Google Toolbar"

Get the last day of the current month

This query runs only in SQLite.
SELECT date('now','start of month','+1 month','-1 day');

Read file content

Load file content
SELECT LOAD_FILE("D:/Temp/test.txt");
Copy a file to another one
SELECT LOAD_FILE("D:/Temp/test.txt") INTO OUTFILE "D:/Temp/test2.txt"

Database Design - Introduction

Database Design - Introduction: "Database design with UML and SQL, 3rd edition

Also available on tomjewett.com: color tutorial, demo application, and video; for Web accessibility resources and consulting, please see The Enabled Web.

- Sent using Google Toolbar"

Ten of the Biggest Mistakes Developers Make With Databases — Developer.com

Ten of the Biggest Mistakes Developers Make With Databases — Developer.com: "Ten of the Biggest Mistakes Developers Make With Databases

- Sent using Google Toolbar"

OpenXML

What will be the output of the following scripts?

DECLARE @xml XML,@hdoc INT
SELECT @xml = '<myxml>
<value id = "1" name = "SQL Server">
 <category>2</category>
</value>
<value id = "2" name = "Oracle">
 <category>3</category>
</value>
</myxml>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT DISTINCT category 
FROM OPENXML (@hdoc, '/myxml/value',1)
WITH (id INT, name VARCHAR(10), category INT)
EXEC sp_xml_removedocument @hdoc

A. NULL
B. 3,2
C. 2,3
D. Error: Incorrect syntax near /value

Answer:[A]
Highlight to find out the answer.

Get user connections

Sometimes you need to determine who is connecting to the database by using which applications, so I think you can use this script.

SELECT t1.session_id, t1.local_tcp_port
           , t2.login_name, t2.host_name, t2.program_name
FROM sys.dm_exec_connections t1  
     INNER JOIN sys.dm_exec_sessions t2 on t1.session_id = t2.session_id

Delete duplicate records

Use this technique with small table only such as this example.^_^

DECLARE @Test TABLE (id int identity(1,1), a int, b int, c int)
INSERT INTO @Test(a,b,c)
SELECT 1,2,3 
UNION ALL SELECT 1,2,3 
UNION ALL SELECT 1,2,3
UNION ALL SELECT 2,3,4
UNION ALL SELECT 2,3,4
UNION ALL SELECT 3,4,5
UNION ALL SELECT 4,5,6
UNION ALL SELECT 4,5,6
UNION ALL SELECT 4,5,6
DELETE t2
FROM @Test t1
   INNER JOIN @Test t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c
      AND t2.id > t1.id;
SELECT * FROM @Test

SQL Server quiz

You are creating a stored procedure that will delete data from the Contact table in a SQL Server 2005 database. The stored procedure includes the following Transact-SQL statement to handle any errors that occur.

BEGIN TRY 
 BEGIN TRAN
 DELETE FROM Person.Contact 
 WHERE ContactID = @ContactID 
 COMMIT TRAN
END TRY
BEGIN CATCH 
 DECLARE @ErrorMessage nvarchar(2000) 
 DECLARE @ErrorSeverity int 
 DECLARE @ErrorState int 
 SELECT @ErrorMessage = ERROR_MESSAGE(), 
  @ErrorSeverity = ERROR_SEVERITY(), 
  @ErrorState = ERROR_STATE() 
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) 
END CATCH;

You test the stored procedure and discover that it leaves open transactions. You need to modify the stored procedure so that it properly handles the open transactions. What should you do?

A. Add a COMMIT TRAN command to the CATCH block.
B. Remove the COMMIT TRAN command from the TRY block.
C. Add a ROLLBACK TRAN command to the CATCH block.
D. Add a ROLLBACK TRAN command to the TRY block.

Answer:[C]
Highlight to find out the answer.

#Oracle #quiz

How many times does the following loop execute?
FOR iYear IN REVERSE  12 .. 1
LOOP
   CalculateSales(iYear);
END LOOP

A. 12
B. 11
C. 0
D. Error will occur

Answer:[C]
Highlight to find out the answer.

Generate a thousand daily dates starting today

CREATE TABLE test.ints(i tinyint);
INSERT INTO test.ints VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 
SELECT CURDATE() + INTERVAL t1.i*100 + t2.i*10 + t3.i DAY AS Date
FROM test.ints AS t1 
  JOIN test.ints AS t2
  JOIN test.ints AS t3
WHERE ( t1.i*100 + t2.i*10 + t3.i ) < 1000
ORDER BY Date; 

Cross-database foreign keys

What will be the output of the following scripts?

CREATE DATABASE db1;
CREATE DATABASE db2;
USE db1;
CREATE TABLE tb1
(
 AutoID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FKID INT NOT NULL
);
USE db2;
CREATE TABLE tb2
(
 AutoID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FKID INT NOT NULL,
 FOREIGN KEY (FKID) REFERENCES db1.dbo.tb1(AutoID)
);

A. The scripts run successfully
B. Error occurs and mean that wrong referrer: db1.dbo.tb1
C. Error occurs and mean that cross database foreign key references are not supported
D. Error shows that wrong syntax when creating foreign key

Answer:[C]
Highlight to find out the answer.

#SQLServer #quiz

A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an error. You investigate the error. You discover that in one of the databases, a developer has accidentally deleted some data in a table that is critical for transaction processing. The database uses the full recovery model. You need to restore the table. You need to achieve this goal without affecting the availability of other data in the database. What should you do?

A. Back up the current transaction log. Restore the database with a different name and stop at the point just before the data loss. Copy the table back into the original database.
B. Back up the current transaction log. Restore the database to the point just before the data loss.
C. Restore the database from the existing backup files to a time just before the data loss.
D. Restore the database to the point of the last full backup.

Answer:[A]
Highlight to find out the answer.

Oracle quiz

Rewrite the following IF statements so that you do not use the IF statement to set the value of no_revenue. What is the difference between the two statements?

IF total_sales <= 0 THEN
   no_revenue := TRUE;
ELSE
   no_revenue := FALSE;
END IF;

Answer:

no_revenue := NVL (total_sales, 1) <= 0;

Highlight to find out the answer.

Get jobs and their schedules

SELECT t1.name AS [Job Name]
  , t1.description AS [Job Description]
  , CASE t1.enabled WHEN 1 THEN 'Yes' ELSE 'No' END AS [Enabled]
  , CASE t3.freq_type
    WHEN  1 THEN 'Once'
    WHEN  4 THEN 'Daily'
    WHEN  8 THEN 'Weekly'
    WHEN 16 THEN 'Monthly'
    WHEN 32 THEN 'Monthly relative'
    WHEN 64 THEN 'When SQL Server Agent starts'
    WHEN 128 THEN 'Start whenever the CPU(s) become idle' END as Occurs
  , t3.active_start_time StartTime
  , t1.date_created AS [Date Created]
  , t1.date_modified AS [Date Modified]  
FROM msdb.dbo.sysjobs t1
 INNER JOIN msdb.dbo.sysjobschedules t2 ON t1.job_id = t2.job_id
 INNER JOIN msdb.dbo.sysschedules t3 ON t2.schedule_id = t3.schedule_id

Convert columns to rows

Sometimes you need to convert columns to rows to get data you need.
For example, this query is used to get which customers buy which services.

DECLARE @CustomerService TABLE(CustomerID INT
       , Service1 CHAR(1)
       , Service2 CHAR(1)
       , Service3 CHAR(1)
       )
INSERT INTO @CustomerService VALUES(1,'Y','Y','N')
INSERT INTO @CustomerService VALUES(2,'N','N','Y')
INSERT INTO @CustomerService VALUES(3,'Y','N','N')

SELECT CustomerID, [Service], ServiceValue
FROM 
   (SELECT CustomerID
   , Service1
   , Service2
   , Service3
 FROM @CustomerService) pvt
UNPIVOT
   (ServiceValue FOR [Service] IN 
   (Service1
  , Service2
  , Service3)
) A
WHERE ServiceValue = 'Y'

Get permissions of columns

SELECT *
FROM DBA_COL_PRIVS

SELECT *
FROM USER_COL_PRIVS

SELECT *
FROM ALL_COL_PRIVS

Dynamic management view

You are managing a database that contains a table with several indexes. You notice that data modification performance has degraded over time. You suspect that some of the indexes are unused. You need to identify which indexes were not used by any queries since the last time SQL Server 2005 started. Which dynamic management view should you use?

A. sys.dm_fts_index_population
B. sys.dm_exec_query_stats
C. sys.dm_db_index_usage_stats
D. sys.dm_db_index_physical_stats

Answer: [C]
Highlight to find out the answer.

#Oracle #quiz

Examine the structure of CONTACT table.
CONTACT(CONTACT_ID NOT NULL NUMBER(3)
, NAME NOT NULL VARCHAR2(25)
, PHONE NOT NULL VARCHAR2(9)
, ADDRESS VARCHAR2(50))
There are hundred records in the contact table. You need to modify the Phone
column to hold only numeric value. Which statement will modify the data type of
the Phone column?

A. ALTER TABLE CONTACT MODIFY PHONE NUMBER(9)
B. ALTER CONTACT TABLE MODIFY COLUMN PHONE NUMBER(9);
C. You can not modify a VARCHAR2 data type to a NUMBER data type.
D. You cannot modify the data type of a column if there is data in the column.

Answer: [D]
Highlight to find out the answer.

Oracle quiz

You need to analyze how long your employees are working for your company from the
date that they are hired to the end of quarter 1/2010. To do this you must create
a report that displays the employee id, employee name, hire date, and the number
of months in whole numbers from the hired date to 03/31/2010. Which statement
produces the required results?

A. SELECT empid, empname, hired_date, ROUND(MONTHS_BETWEEN
('03/31/2010',hired_date)) "Time Taken" FROM emp;
B. SELECT empid, empname, hired_date,ROUND(DAYS_BETWEEN
('03/31/2010',hired_date))/30 FROM emp;
C. SELECT empid, empname, hired_date,
ROUND OFF('03/31/2010'-hired_date) "Time Taken" FROM emp;
D. SELECT empid, empname, hired_date, MONTHS_BETWEEN('03/31/2010',hired_date)
"Time Taken" FROM emp;

Answer:[A]
Highlight to find out the answer.

Check SMTP support SSL or TLS

Open Windows Command Line, type: telnet your.exchange.server.address 25
When connecting, type: ehlo your.hostname
You should now see few lines.
If there is STARTTLS line, then it means, that TLS is available.

#Oracle error handling

DECLARE
 vErrorCode NUMBER;
 vErrorMsg VARCHAR2(200);
 vCurrentUser VARCHAR2(8);
 vInfo VARCHAR2(100)
BEGIN
 /*Some code processes*/
EXCEPTION
 WHEN OTHERS THEN
  vErrorCode:= SQLCODE;
  vErrorMsg := SQLERRM;
  vCurrentUser := USER;
  vInfo := 'Error encountered on ' || TO_CHAR(SYSDATE) || ' by database user ' || vCurrentUser;
  INSERT INTO LOG_TABLE(CODE, MSG, INFO)
  VALUES(vErrorCode, vErrorMsg, vInfo);
END;

#Oracle quiz

The contact table contains these columns:
First_Name VARCHAR2(25)
Sales_Commission NUMBER(3,2)
Evaluate this SQL statement
SELECT first_name,commission
FROM Contact
WHERE commission= (SELECT Sales_Comission FROM Contact
WHERE UPPER(first_name)= 'Bill')
Which statement below will cause this statement to fail?

A. Bill has a null salescommission resolution.
B. Bill has a zero sales commission resolution.
C. There is no contact with the first name Bill.
D. The first name values in the database are in the lower case.

Answer: [A]
Highlight to find out the answer.

#DBCC CHECK...

A power failure occurs on the storage area network (SAN) where your SQL Server 2005
database server is located. You need to check the allocation as well as the structural and logical integrity of all databases, including their system catalogs. What should you do?

A. Execute DBCC CHECKFILEGROUP for each filegroup.
B. Execute DBCC CHECKCATALOG.
C. Execute DBCC CHECKDB.
D. Execute DBCC CHECKTABLE for each table.

Answer:[C]
Highlight to find out the answer.

Pligg message table

This table contains messages sent to users.
CREATE TABLE `pligg_messages` (
`idMsg` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`body` text COLLATE utf8_unicode_ci,
`sender` int(11) NOT NULL DEFAULT '0',
`receiver` int(11) NOT NULL DEFAULT '0',
`senderLevel` int(11) NOT NULL DEFAULT '0',
`readed` int(11) NOT NULL DEFAULT '0',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`idMsg`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Which is true?

Which statement about SQL is true?

A. Null values are displayed last in the ascending sequences.
B. Data values are displayed in descending order by default.
C. You cannot specify a column alias in an ORDER BY clause.
D. You cannot sort query results by a column that is not included in the SELECT list.
E. The results are sorted by the first column in the SELECT list, if the ORDER BY
clause is not provided.

Answer:[A]
Highlight to find out the answer.

Database snapshot

Which system database can you create a database snapshot in SQL Server 2005?

A. master
B. model
C. msdb
D. tempdb
E. All system databases
F. None

Answer:[C]
Highlight to find out the answer.

Modify existing table

Examine the structure of contact table:
CONTACT
(
CONTID NOT NULL NUMBER(3),
NAME VARCHAR2(25),
ADDRESS VARCHAR2(50),
BIRTHDATE DATE
)
Currently the table is empty. You have decided that null values should not be
allowed for the NAME column. Which statement restricts NULL values from being
entered into column?

A. ALTER TABLE CONTACT ADD CONSTRAINT NAME(NOT NULL);
B. ALTER TABLE CONTACT ADD CONSTRAINT NOT NULL (NAME);
C. ALTER TABLE CONTACT MODIFY CONSTRAINT NAME(NOT NULL);
D. ALTER TABLE CONTACT MODIFY(NAME VARCHAR2(25) NOT NULL);

Answer:[D]
Highlight to find out the answer.

Add a foreign key

How would you add a foreign key constraint on the dept_no column in the EMP
table. Referring to the ID column in the DEPT table?

A. Use the ALTER TABLE command with the ADD clause in the DEPT table.
B. Use the ALTER TABLE command with the ADD clause on the EMP table.
C. Use the ALTER TABLE command with the MODIFY clause on the DEPT table.
D. Use the ALTER TABLE command with the MODIFY clause on the EMP table.
E. This task cannot be accomplished.

Answer:[B]
Highlight to find out the answer.

ROLE

Evaluate the SQL statement.
CREATE ROLE manager;
CREATE ROLE clerk;
CREATE ROLE warehouse;
CREATE USER john IDENTIFIED BY john123;
GRANT warehouse TO clerk;
GRANT clerk TO manager;
GRANT warehouse TO john;
GRANT manager TO john
/
How many roles will user john have access to?

A. 0
B. 1
C. 2
D. 3

Answer:[D]
Highlight to find out the answer.

RTRIM

What will be the output of the following script?
SELECT RTRIM('TestxxXXxxx','x') FROM dual;

A. Test
B. TestxxXX
C. TestXX
D. An error occurs: "The RTRIM function requires 1 argument(s)."

Answer:[B]
Highlight to find out the answer.
Prentice Hall Presents Oracle Software - 4 Cd-Roms

Job quiz

You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Saturday and other jobs run at the beginning of every month. You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?

A. Create a job schedule that runs every Saturday. Assign weekly tasks to this schedule. Create a second schedule that runs on the first day of every month. Assign monthly tasks to this schedule.
B. Create a job for each task that runs once a day. Use a T-SQL statement to check the date and day of the week. If the day is either a Saturday or the first day of the month, execute the code.
C. Create a job schedule that runs once a day. Assign jobs to this job schedule. If the day is either a Saturday or the first day of the month, execute the jobs.
D. Create a job for each task that runs once a week on Saturday. Add a second job schedule that runs the job on the first of the month.

Answer: [A]
Highlight to find out the answer.

Transaction quiz

What will be the output of the following scripts (SQL Server 2005 or 2008)?
CREATE TABLE #Test(a TINYINT)
GO
BEGIN TRY
BEGIN TRAN OuterTran
BEGIN TRAN InnerTran
INSERT INTO #Test VALUES(1)
COMMIT TRAN InnerTran
INSERT INTO #Test VALUES(256) --Error occur here
COMMIT TRAN OuterTran
END TRY
BEGIN CATCH
ROLLBACK WORK
END CATCH
SELECT * FROM #Test

A. 1
B. Error occurs since there no ROLLBACK WORK command in T-SQL
C. Error occurs in the scripts
D. No record returns

Answer:[D]
Highlight to find out the answer.

Get Pligg version

SELECT data
FROM pligg_misc_data
WHERE name = 'pligg_version'

Pligg Comment table

Go to Pligg website to learn more.
CREATE TABLE `pligg_comments` (
`comment_id` int(20) NOT NULL auto_increment,
`comment_randkey` int(11) NOT NULL default '0',
`comment_parent` int(20) default '0',
`comment_link_id` int(20) NOT NULL default '0',
`comment_user_id` int(20) NOT NULL default '0',
`comment_date` datetime NOT NULL,
`comment_karma` smallint(6) NOT NULL default '0',
`comment_content` text collate utf8_unicode_ci,
`comment_votes` int(20) NOT NULL default '0',
`comment_status` enum('discard','moderated','published') character set utf8 NOT NULL default 'published',
PRIMARY KEY (`comment_id`),
UNIQUE KEY `comments_randkey` (`comment_randkey`,`comment_link_id`,`comment_user_id`,`comment_parent`),
KEY `comment_link_id` (`comment_link_id`,`comment_parent`,`comment_date`),
KEY `comment_link_id_2` (`comment_link_id`,`comment_date`),
KEY `comment_date` (`comment_date`),
KEY `comment_parent` (`comment_parent`,`comment_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Pligg Category table

Go to Pligg website to learn more.
CREATE TABLE `pligg_categories` (
`category__auto_id` int(11) NOT NULL auto_increment,
`category_lang` varchar(2) collate utf8_unicode_ci default 'en',
`category_id` int(11) NOT NULL default '0',
`category_parent` int(11) NOT NULL default '0',
`category_name` varchar(64) collate utf8_unicode_ci default NULL,
`category_safe_name` varchar(64) collate utf8_unicode_ci default NULL,
`rgt` int(11) NOT NULL default '0',
`lft` int(11) NOT NULL default '0',
`category_enabled` int(11) NOT NULL default '1',
`category_order` int(11) NOT NULL default '0',
`category_desc` varchar(255) collate utf8_unicode_ci default NULL,
`category_keywords` varchar(255) collate utf8_unicode_ci default NULL,
`category_author_level` enum('normal','admin','god') character set utf8 NOT NULL default 'normal',
`category_author_group` varchar(255) collate utf8_unicode_ci default NULL,
`category_votes` varchar(4) character set utf8 NOT NULL default '',
PRIMARY KEY (`category__auto_id`),
KEY `category_id` (`category_id`),
KEY `category_parent` (`category_parent`),
KEY `category_safe_name` (`category_safe_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

TABLESAMPLE

Do you know TABLESAMPLE in SQL Server 2005?
Assume that your tblEmployee in SQL Server 2005 table contains more than 10 records.
Which querie(s) below return exact 10 records?
1. SELECT EmployeeID, EmployeeName
FROM tblEmployee TABLESAMPLE (10 ROWS)
2. SELECT TOP 10 EmployeeID, EmployeeName
FROM tblEmployee

A. Query 1
B. Query 2
C. Both queries
D. No query return exact 10 records

Answer:[B]
Highlight to find out the answer.

Cross join

What will be the output of the following scripts?
DECLARE @Test1 TABLE(a INT)
DECLARE @Test2 TABLE(a INT)
INSERT INTO @Test1
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
INSERT INTO @Test2
SELECT a FROM @Test1
SELECT COUNT(*) FROM @Test1, @Test2

A. 5
B. 10
C. 25
D. An error will be occurred.

Answer:[C]
Highlight to find out the answer.

LOWER, UPPER

You want to display the details of all employees whose first names is Bill. But you are not sure in which case first names are stored. Which statement will list all the employees whose first name is Bill?
A. Select last_name, first_name FROM emp WHERE first_name= 'bill';
B. Select last_name, first_name FROM emp WHERE UPPER (first_name)= 'bill';
C. Select last_name, first_name FROM emp WHERE first_name = UPPER ('bill');
D. Select last_name, first_name FROM emp WHERE LOWER(first_name)= 'bill';

Answer:[D]
Highlight to find out the answer.

NVL

You need to create a report to display the issue date and total items issued of your warehouseissue table. If the item has not been issued your report must display not issued. If the total is not available your report must say not available. In the warehouseissue table the issue date column has a data type of date the total column has a data type of number. Which statement do you use to create this report?

A. Select itemid, issuedate "Not issued", total "Not available" FROM warehouseissue;
B. Select itemid, NVL(issuedate, 'Not issued'), NVL (total, "Not available") FROM warehouseissue;
C. Select itemid, NVL(TO_CHAR(issuedate), 'Not issued'), NVL(TO_CHAR(total), 'Not available') FROM warehouseissue;
D. Select itemid, TO_CHAR(issuedate, 'Not issued') TO_CHAR(total, 'Not available') FROM warehouseissue;

Answer:[C]
Highlight to find out the answer.

Love Quotes on Happiness

Where ur pleasure is, there is ur treasure: where ur treasure, there ur heart; where ur heart, there ur happiness. ~Saint Augustine

Get disabled constraints

SELECT OWNER,TABLE_NAME,
        CONSTRAINT_NAME,
        DECODE(CONSTRAINT_TYPE,
           'C','Check',
           'P','Primary Key',
           'U','Unique',
           'R','Foreign Key',
           'V','With Check Option') AS CONSTRAINT_TYPE
FROM dba_constraints
WHERE STATUS = 'DISABLED'
ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME

Get tables without indexes

SELECT OWNER, TABLE_NAME
FROM (SELECT OWNER, TABLE_NAME
    FROM   dba_tables
    WHERE OWNER NOT IN ('SYS','SYSTEM')
    MINUS
    SELECT TABLE_OWNER, TABLE_NAME
    FROM dba_indexes
    WHERE OWNER NOT IN ('SYS','SYSTEM'))
ORDER BY OWNER,TABLE_NAME;

Bitwise with AND & OR

Do you remember bitwise with AND and OR?
DECLARE @a INT,@b INT,@c INT,@d INT
SELECT @a = 2, @b = 3, @c = 5
SET @d = (@a & @b)| @c
SELECT @d

A. 2
B. 3
C. 5
D. 7
E. 8
F. 10

Answer:[D]
Highlight to find out the answer.

Explanation:
@a    = 010
@b    = 011
-----------
@a&@b = 010
@c    = 101
-----------
@d    = 111

CAST with DATETIME2

What will be the output of the following script in SQL Server 2008?
SELECT CAST(0 AS DATETIME2 )

A. 1900-01-01 00:00:00.0000000
B. 0001-01-01 00:00:00.0000000
C. 1753-01-01 00:00:00.0000000
D. An error will be generated: "Explicit conversion from data type int to datetime2 is not allowed."
E. An error will be generated:"Type DATETIME2 is not a defined system type."

Answer:[D]
Highlight to find out the answer.

@@IDENTITY

What will be the value of @@IDENTITY in the last row?
DECLARE @Test TABLE(a INT IDENTITY(1,1), b TINYINT)
INSERT INTO @Test(b) VALUES(1)
INSERT INTO @Test(b) VALUES(256) --Error is generated here, arithmetic overflow
INSERT INTO @Test(b) VALUES(255)
SELECT @@IDENTITY

A. NULL
B. 2
C. 3
D. 1

Answer:[C]
Highlight to find out the answer.

MS Access & MS SQL data type

Do you know the equivalent data types between MS Access and MSSQL?
What is the equivalent of MSSQL 'text' data type in MS Access?

A. memo
B. text
C. nvarchar
D. OLE Object
E. Attachment
F. char

Answer:[A]
Highlight to find out the answer.

Data Type Precedence

Do you remember data type precedence in SQL Server 2005?
Which of these data types have the highest and lowest precedence in sequence?

A. sql_variant, bit
B. sql_variant, binary
C. user-defined data type, char
D. user-defined data type, binary
E. ntext, bit
F. ntext, binary

Answer:[D]
Highlight to find out the answer.

String data type

We need to define a column that will contain user-defined values. Their length will vary from 10 to 15 characters using English letters, both upper and lower case mixed with numeric digits, with an even distribution of lengths.

Which of these data types will be more efficient?

A. char(15)
B. varchar(15)
C. nchar(15)
D. nvarchar(15)
E. varchar(max)
F. nvarchar(max)

Answer:[B]
Highlight to find out the answer.

INT and SMALLINT

What will be the output of the following scripts?
DECLARE @a INT
DECLARE @b SMALLINT
SET @a = POWER(2,15)
SET @b = @a
SELECT @a

A. 32768
B. 32767
C. An error will be generated
D. NULL

Answer:[C]
Highlight to find out the answer.

NULLIF

Do you remember NULLIF function?
What would be the output of the following scripts?
DECLARE @Test TABLE (a INT, b INT)
INSERT @Test VALUES(1,NULL)
INSERT @Test VALUES(NULL,2)
INSERT @Test VALUES(0,3)
INSERT @Test VALUES(NULL,4)
INSERT @Test VALUES(5,0)
SELECT SUM(NULLIF(COALESCE(a,b), 0.00))
FROM @Test

A. 14
B. 6
C. 9
D. 12
E. 8
F. 7

Answer:[D]
Highlight to find out the answer.

LEN with NVARCHAR(MAX)

What will be the output of the following scripts if you run them in SQL Server 2005 Management Studio?
DECLARE @STR NVARCHAR(MAX)
SET @STR = REPLICATE('1',8001)
SELECT LEN(@STR)

A. 8001
B. 8000
C. An error will be generated since the nvarchar(max) data types are invalid for local variables.
D. An error will be generated since the maximum of LEN function is only 8000

Answer:[B]
Highlight to find out the answer.

LEN with NTEXT

What will be the output of the following scripts if you run them in SQL Server Management Studio?
DECLARE @STR NTEXT
SET @STR = REPLICATE('1',8001)
SELECT LEN(@STR)

A. 8001
B. 8000
C. An error will be generated since the ntext data types are invalid for local variables.
D. An error will be generated since the maximum of LEN function is only 8000

Answer:[C]
Highlight to find out the answer.

COALESCE function

Do you remember COALESCE function?
What would be the output of the following scripts?
CREATE TABLE #Test(a INT NULL, b INT NULL, c INT NULL)
INSERT INTO #Test(a,b,c) VALUES(1,1,NULL)
INSERT INTO #Test(a,b,c) VALUES(NULL,2,NULL)
INSERT INTO #Test(a,b,c) VALUES(1,NULL,3)
SELECT COALESCE(a,b,c) FROM #Test

A. 1,2,3
B. 1,NULL,1
C. 1,2,1
D. NULL,NULL,NULL
E. NULL,2,3
F. 1,NULL,3

Answer:[C]
Highlight to find out the answer.

Find output

What will be the output of the following statements?
DECLARE @iRet INT
SELECT @iRet = ISNULL(@iRet,'1') + MyValue
FROM (SELECT '1' MyValue UNION SELECT '1' UNION SELECT '2') t1
SELECT @iRet

A. An error will be generated
B. '112'
C. 4
D. '1112'

Answer:[C]
Highlight to find out the answer.

I'm an SQL Guy

 ____
/  __)
\(~oo
_\ o/_
/  \/  \
/ / SQL\ \
\ | GUY/_/
\|___(_/
| |\  \
| |/  /
|_/__/
(__[__)

TRUNCATE TABLE

Which of the following is NOT true about the TRUNCATE TABLE command?

A. Removes all records from the table.
B. Resets the identity column to the seed of the column.
C. Delete triggers are executed.
D. It cannot be used on tables referenced by foreign tables.

Answer:[C]
Highlight to find out the answer.

Extended stored procedure or not

What will be the output of the following statements in SQL Server 2005?
SELECT OBJECTPROPERTY(OBJECT_ID('sp_executesql'),'IsExtendedProc')
SELECT OBJECTPROPERTY(OBJECT_ID('xp_grantlogin'),'IsExtendedProc')

A. 1, 1
B. 1, 0
C. 0, 1
D. 0, 0

Answer:[B]
Highlight to find out the answer.

FOR XML

The following query returns data as XML
SELECT ContactID, FirstName, LastName, Phone
FROM Person.Contact
ORDER BY ContactID
FOR XML AUTO,ELEMENTS
However, there're some NULL values in Phone column and it makes some Phone tag disappeared in returned XML. You need to modify this query so that all tags appear in the XML document. What should you do?

A. Add an XML index to the table that contains the product data.
B. Add the XSINIL argument to the ELEMENTS directive in the query.
C. Add a HAVING clause to the query.
D. Add the replace value of clause to the query.

Answer:[B]
Highlight to find out the answer.

Import data

When deploying the applications, you need to import data into SQL Server 2005. Data is contained in text files such as employees, contacts, customers, etc. Columns in the data are separated by semicolons. What should you do?

A. Use the bcp command, specifying a semicolon as the field terminator
B. Use the bcp command with the default arguments
C. Use BULK INSERT with the default arguments
D. Use OPENQUERY with the default arguments

Answer:[A]
Highlight to find out the answer.

Which network protocols below Microsoft SQL Server 2005 does not support?

A. Named Pipes
B. TCP/IP
C. Multiprotocol
D. Shared Memory
E. Virtual Interface Adapter (VIA)

Answer:[C]
Highlight to find out the answer.

ISNUMERIC function

Do you remember ISNUMERIC() built-in function?
What would be the output of the following script?

DECLARE @Year VARCHAR(10)
SET @Year = '2,010.'
SELECT ISNUMERIC(@Year)

A. 0
B. 1
C. True
D. False
E. An error will be generated.

Answer:[B]
Highlight to find out the answer.

Remove spaces

Your SQL Server database contains a lot of columns with data type nchar. Many of them contain preceding or trailing spaces. You need to implement mechanism to get the data without leading and trailing spaces. Your solution must be available for reuse in T-SQL statements and views. What should you do?

A. Create DML triggers that query the inserted and deleted tables
B. Create a stored procedure that calls LTRIM and RTRIM built-in function
C. Call TRIM built-in function
D. Create a function that calls RTRIM and LTRIM built-in function

Answer:[D]
Highlight to find out the answer.

Port number

What are the default port numbers of SQL Server and MySQL in sequence?

A. 3306 and 5432
B. 1433 and 3306
C. 1433 and 5432
D. 3306 and 1433
E. 5432 and 1433
F. 5432 and 3306

Answer:[B]
Explanation: SQL Server:1433, MySQL:3306, PostgreSQL: 5432
Highlight to find out the answer.

Reference to another server

You have two SQL Server 2005 computers. One server is SQL1 and another is SQL2.
You need to access SQL2 from SQL1. You have enough permissions on the two servers.
What should you do?

A. Join the two servers by using four-part syntax: server.database.schema.table
B. Reference SQL2 by using an alias
C. Add SQL2 as a remote server to SQL1
D. Add SQL2 as a linked server to SQL1

Answer:[D]
Highlight to find out the answer.

INT data type

Do you remember INT data type?
What's the maximum value can an INT data type hold?

A. 2,147,483,647
B. 2,147,483,648
C. 4,294,967,295
D. 4,294,967,296

Answer:[A]
Highlight to find out the answer.

Add a new column to an existing table

Which of the following statements is the correct syntax for adding a new column called Gender into a table called dbo.Contact that already contains data?

A. ALTER TABLE dbo.Contact ADD COLUMN Gender CHAR(1) NOT NULL
B. ALTER TABLE dbo.Contact ADD COLUMN Gender CHAR(1) NULL
C. ALTER TABLE dbo.Contact ADD Gender CHAR(1) NOT NULL
D. ALTER TABLE dbo.Contact ADD Gender CHAR(1) NULL

Answer:[D]
Highlight to find out the answer.

Find output

What is the result of the following batch?

DECLARE @Test TABLE(a INT IDENTITY, b VARCHAR(5))
DECLARE @sRet VARCHAR(255)
SET @sRet = 'Happy'
SELECT @sRet = ISNULL(b, '!') FROM @Test ORDER BY a
INSERT @Test VALUES('Happy')
INSERT @Test VALUES('Lunar')
INSERT @Test VALUES('New')
INSERT @Test VALUES('Year')
SELECT @sRet = @sRet + ' ' + b FROM @Test ORDER BY a
SELECT @sRet

A. NULL
B. ! Happy Lunar New Year
C. An error will be generated
D. Happy Happy Lunar New Year
E. Happy Lunar New Year

Answer:[D]
Highlight to find out the answer.

@@ERROR

What would be the output of this code when you open a new query window and execute it?

SELECT @@ERROR
SELECT @@ERROR

A. 0,0
B. 1,1
C. 0,1
D. 1,0

Answer:[A]
Highlight to find out the answer.

Find weak login passwords in your server

This is the script I posted on sqlservercentral.com last year.
DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))
--Define weak password list
--Use @@Name if users password contain their name
INSERT INTO @WeakPwdList(WeakPwd)
SELECT ''
UNION SELECT '123'
UNION SELECT '1234'
UNION SELECT '12345'
UNION SELECT 'abc'
UNION SELECT 'default'
UNION SELECT 'guest'
UNION SELECT '123456'
UNION SELECT '@@Name123'
UNION SELECT '@@Name'
UNION SELECT '@@Name@@Name'
UNION SELECT 'admin'
UNION SELECT 'Administrator'
UNION SELECT 'admin123'
-- SELECT * FROM @WeakPwdList
SELECT t1.name [Login Name], REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]
FROM sys.sql_logins t1
INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)

Reserved Keywords again

Which of the following is a reserved keyword in SQL Server 2005 and therefore cannot be used as an object name or as a column name in a table unless delimited?

A. ADD
B. SUBTRACT
C. MINUS
D. MULTIPLY
E. DIVIDE
F. None of the above is a reserved keyword in SQL Server 2005

Answer:[A]
Highlight to find out the answer.

Data Type Precedence

Do you remember data type precedence?
Which of the following data types has the highest data type precedence?

A. SMALLDATETIME
B. SMALLMONEY
C. SMALLINT
D. CHAR

Answer:[A]
Highlight to find out the answer. Try to answer first before visiting the link above.

DATETIME data type

Do you remember DATETIME data type?
What will be the result of the following statement?

SELECT CAST(-1 AS DATETIME)

A. 1900-01-01 00:00:00.000
B. 1899-01-01 00:00:00.000
C. 1899-12-31 00:00:00.000
D. 1752-01-01 00:00:00.000
E. 1752-12-31 00:00:00.000
F. The statement will generate an error. Only positive integer values can be converted to a DATETIME data type.

Answer:[B]
Highlight to find out the answer.

Reserved Keywords

Which of the following is NOT a reserved keyword in SQL Server 2005 and therefore can be used as an object name or as a column name in a table without using delimiters.

A. FUNCTION
B. PROCEDURE
C. RETURN
E. RETURNS
E. EXECUTE
F. None of the above. All of these are reserved keywords in SQL Server 2005.

Answer:[E]
Explanation: Although RETURNS is used in user defined function it's not a reserved keyword.
Highlight to find out the answer.

Find output

What will be the result of the following SELECT statement?

SELECT 2010/02/16 + 4

A. 2010/02/20
B. 2000/06/16
C. 66
D. An error is generated.

Answer:[C]
Highlight to find out the answer.

public role

Do you remember the public role in SQL Server?
Which of the following is NOT a valid description of the public role?

A. The public role captures all default permissions for users in a database.
B. The public role cannot be dropped.
C. The public role is contained in every database, including msdb, tempdb, model, and all user databases except in the master database for security purposes.
D. The public role cannot have users, groups, or roles assigned to it.

Answer:[C]
Highlight to find out the answer.

Division by zero?

What will be the value of @A in the following statement?

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
DECLARE @A INT
SET @A = 2010/0

A. 0
B. NULL
C. Infinity
D. An error is generated.

Answer:[B]
Explanation: When both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error.
Highlight to find out the answer.

Using GETDATE in function?

Can you use GETDATE() function in MSSQL2K, MSSQL2K5, and MSSQL2K8?
Which MSSQL version of the following function definition will generate an error?

CREATE FUNCTION [dbo].[fnCheckDate] ()
RETURNS DATETIME
AS
BEGIN
DECLARE @Today DATETIME
SET @Today = GETDATE()
SET @Today = DATEADD(DD, DATEDIFF(DD, 0, @Today), 0)
RETURN DATEADD(DD, 1, @Today)
END

A. SQL Server 2000
B. SQL Server 2005
C. SQL Server 2008
D. No error is generated. The function will be created for all versions.
E. Error will be generated for all versions.

Answer:[A]
Explanation:The GETDATE() function, and any non-deterministic functions, are not allowed in a user-defined function of SQL Server 2000.
Highlight to find out the answer.

Trigger tables

How many system tables can you use in a trigger for INSERT, UPDATE, and DELETE?
To identify records that were updated in a table from a trigger, which of the following tables need to be investigated?

A. Inserted system table
B. Updated system table
C. Deleted system table
D. Inserted and Deleted system tables

Answer:[D]
Highlight to find out the answer.

SMALLDATETIME data type

Which of the following is NOT true about the SMALLDATETIME data type?

A. SQL Server stores SMALLDATETIME values as two 2-byte integers.
B. The first 2 bytes store the number of days after January 1, 1900
C. The second byte store the number of seconds since midnight.
D. Dates range from January 1, 1900 through June 6, 2079.

Answer:[C]
Explanation: SMALLDATETIME data type doesn't contain second part.
Highlight to find out the answer.

CREATE FUNCTION

Which part of the following function definition will generate an error?

CREATE FUNCTION dbo.CreateContact(
@FirstName VARCHAR(50),
@LastName VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @ContactName VARCHAR(20)

SET @ContactName = LEFT(@FirstName, 1) + @LastName

INSERT INTO dbo.Contact(ContactName, FirstName, LastName )
VALUES (@ContactName, @FirstName, @LastName )

RETURN SCOPE_IDENTITY()
END


A. SET @ContactName = LEFT(@FirstName, 1) + @LastName
B. INSERT INTO dbo.Contact
C. RETURN SCOPE_IDENTITY()
D. No error is generated. The function will be created and compiled.

Answer:[B]
Highlight to find out the answer.

CAST with SMALLDATETIME

What will be the result of the following statement?

SELECT CAST(-1 AS SMALLDATETIME)


A. 1900-01-01 00:00:00.000
B. 1899-01-01 00:00:00.000
C. 1899-12-31 00:00:00.000
D. 1752-01-01 00:00:00.000
E. 1752-12-31 00:00:00.000
F. The system will generate an error. Arithmetic overflow error converting expression to data type smalldatetime.

Answer:[F]
Highlight to find out the answer.

Reserved Keywords

Which of the following is NOT a reserved keyword in SQL Server and therefore can be used as an object name or as a column name in a table without using delimiters?

A. CREATE
B. GOTO
C. GO
D. BEGIN
E. END
F. None of the above. All of these are reserved keywords in SQL Server.

Answer:[C]
Highlight to find out the answer.

Fixed database role

Do you recognize fixed database roles and fixed server roles?
Which of the following fixed database roles can add or remove user IDs?

A. db_accessadmin
B. db_securityadmin
C. db_setupadmin
D. db_sysadmin

Answer:[A]
Highlight to find out the answer.

DATEADD

Do you remember DATEADD and DATEDIFF functions?
What does the following statement do?

SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))

A. Return the current system date unchanged.
B. Return just the date part of the current system date removing the time.
C. Return the integer value of the current system date.
D. The statement will generate an error. The second parameter of the DATEDIFF function should be a DATETIME data type.

Answer:[B]
Highlight to find out the answer.

Find output

What will be the output of the following statement?

SELECT LEN(CAST(LEFT('026-100', 3) AS INT))

A. 2
B. 3
C. 7
D. Statement will generate an error.

Answer:[A]
Highlight to find out the answer.

securityadmin fixed server role

Which of the following is NOT a valid description of the securityadmin fixed server role?

A. Manages server logins.
B. Adds, removes and configures linked servers.
C. Has permission to execute the sp_password stored procedure for all users other than members of the sysadmin role.
D. Can read the error log.

Answer:[B]
Highlight to find out the answer.

Numeric data type

Which of the following data types has the least data type precedence?

A. BIGINT
B. FLOAT
C. DECIMAL
D. MONEY
E. REAL
F. NUMERIC

Answer: [A]
Highlight to find out the answer.

DATETIME Conversion

What will be the value of @Today in the following script?

DECLARE @Today VARCHAR(10)
SET @Today = '02/02/2010'
SET @Today = @Today + 8

A. 02/10/2010
B. 10/02/2010
C. 02/02/2018
D. An error is generated.

Answer:[D]
Highlight to find out the answer.

BIT data type

Do you remember BIT data type?
What will be the value of the @Bit variables in the following?

DECLARE @Bit BIT
SET @Bit = -1

A. 0
B. -1
C. 1
D. An error will be encountered. Only 1, 0 and NULL values are allowed for a bit data type.

Answer:[C]
Highlight to find out the answer.

SMALLDATETIME data type

Do you remember SMALLDATETIME data type?
What is the maximum date value that can be stored in a SMALLDATETIME data type?

A. June 6, 2079
B. July 6, 2079
C. December 31, 2079
D. December 31, 9999

Answer:[A]
Highlight to find out the answer.

Format date with CONVERT()

Do you remember the CONVERT function for date/time value?
Which of the following scripts return the date value as YYYY/MM/DD?

A. SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
B. SELECT CONVERT(VARCHAR(10), GETDATE(), 102)
C. SELECT CONVERT(VARCHAR(10), GETDATE(), 111)
D. SELECT CONVERT(VARCHAR(10), GETDATE(), 112)

Answer:[C]
Highlight to find out the answer.

Fixed server role?

Do you remember fixed server roles?
Which of the following is NOT a fixed server role?

A. setupadmin
B. diskadmin
C. securityadmin
D. accessadmin

Answer:[D]
Explanation: setupadmin, diskadmin and securityadmin are all valid fixed server roles. On the other hand, accessadmin is not a valid fixed server role.
Highlight to find out the answer.

Find output

Do you remember the STR function?
What will be the result of the following statement?

SELECT STR(-2010.87, 6, 1)

A. -2011
B. -2010.9
C. -2010.8
D. -2010

Answer:[A]
Highlight to find out the answer.

Find output

What will be the output of the following statement?
SELECT CHARINDEX('in', 'finished testing', 0)
A. 0
B. 2
C. NULL
D. Statement will generate an error. The CHARDINDEX function only accepts 2 parameters.

Answer:[B]
Highlight to find out the answer.

True or False

Have you ever used varchar(max) or varbinary(max) in your database?
The maximum sizes of varchar(max) and varbinary(max) is the same in SQL Server 2005?

A. True
B. False

Answer:[A]
Explanation: 2^31-1

Highlight to find out the answer.

MySQL Quiz

What is the maximum length of a table name, a database name, index name, constraint name, stored procedure name, or a field name in MySQL?

A. 256
B. 50
C. 64
D. 128

Answer: C
Highlight to find out the answer.

SQL Server Quiz

Which of the following is NOT true about the PRIMARY KEY constraint?

A. Can be of more than 1 column.
B. Always created as CLUSTERED.
C. Enforces data uniqueness by creating a unique index for the primary key columns.
D. Column that participates in the PRIMARY KEY constraint cannot accept NULL values.

Answer: [B]
Highlight to find out the answer.

Display fragmentation info on data and indice of a table

Use DBCC SHOWCONTIG statement on heavily modified tables or tables that contain imported data or tables that make the query performance very poor.

DECLARE @TableId INT
SET @TableId = OBJECT_ID('Your table name')
DBCC SHOWCONTIG(@TableId);

Use a DEFAULT for several columns

Sometimes you need to set a default value for multiple columns. Why don't you create a DEFAULT and bind it to them instead of setting default value one by one.
CREATE TABLE tblTest1(Status varchar(10));
GO
CREATE TABLE tblTest2(Status varchar(10));
GO
CREATE DEFAULT dfStatus AS 'Active';
GO
-- Bind default
EXEC sp_bindefault N'dfStatus', N'tblTest1.Status';
EXEC sp_bindefault N'dfStatus', N'tblTest2.Status';
-- Unind default
EXEC sp_unbindefault N'tblTest1.Status';
EXEC sp_unbindefault N'tblTest2.Status';

Get Oracle system events

SELECT EVENT,
 TOTAL_WAITS,
 TOTAL_TIMEOUTS,
 TIME_WAITED,
 ROUND(AVERAGE_WAIT,2) AVAREAGE_WAIT
FROM v$system_event
ORDER BY TOTAL_WAITS

Get all running queries in MySQL

Sometimes you need to determine which queries are running too long in MySQL db, this script may help you:
SHOW PROCESSLIST; (get first 100 characters of the query)
OR
SHOW FULL PROCESSLIST; for more detail in the query

Get spaced used of all tables

DECLARE @TableName sysname
DECLARE @SpaceUsed TABLE(TableName sysname,
   Rows INT,
   Reserved VARCHAR(50),
   Data VARCHAR(50),
   IndexSize VARCHAR(50),
   Unused VARCHAR(50))
DECLARE Cur CURSOR FOR
 SELECT name
 FROM sysobjects
 WHERE type = 'U'
OPEN Cur
FETCH FROM Cur
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO @SpaceUsed
 EXEC sp_spaceused @TableName
 FETCH NEXT FROM Cur
 INTO @TableName
END
CLOSE Cur
DEALLOCATE Cur
SELECT * FROM @SpaceUsed

Full text search with FORMSOF

Find all employees where their notes contains any form of the word graduate

SELECT * FROM Employees
WHERE CONTAINS (Notes, 'FORMSOF(INFLECTIONAL, "graduate")')

PIVOT with MySQL

DROP TABLE IF EXISTS tbl_sales_revenue;
CREATE TABLE tbl_sales_revenue(dept_id int, year int, revenue double);
INSERT INTO tbl_sales_revenue(dept_id, year, revenue)
VALUES
 (1,2007,1200),
 (1,2008,1000),
 (1,2009,2000),
 (2,2007,3000),
 (2,2008,4000),
 (2,2009,5000),
 (3,2007,500),
 (3,2008,1200),
 (3,2009,1500);
 
SELECT
 IFNULL(dept_id,'Total') AS 'Department',
 a.2007, a.2008, a.2009,
 a.2007 + a.2008 + a.2009 AS Total
FROM (
 SELECT
   dept_id,
   SUM(IF(year=2007, revenue, 0)) As '2007',
   SUM(IF(year=2008, revenue, 0)) As '2008',
   SUM(IF(year=2009, revenue, 0)) As '2009'
 FROM tbl_sales_revenue
 GROUP BY dept_id WITH ROLLUP
 ) AS a;