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.