Sunday, September 25, 2011

Query transactions by date

You work for a supermarket that uses a SQL Server database to store line items from point of sales (POS) transactions. The POS processes 150,000 transactions every day. The application requires a clustered index on the TransactionID column. You need to create a table that supports an efficient reporting solution that queries the transactions by date.
What will you do to achieve this goal?

A. Place a nonclustered index on the date column.
B. Add a unique clustered index on the date column.
C. Map each partition to a filegroup, with each filegroup accessing a different physical drive.
D. Create a partitioning scheme that partitions the data by date.
E. Both A and D
F. None

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Saturday, September 24, 2011

Data type precedence

Do you remember data type precedence?
Which of the following data types has the highest data type precedence in SQL Server 2008?

A. DATETIME2
B. XML
C. BIGINT
D. UNIQUEIDENTIFIER
E. DATETIME
F. NTEXT

Answer:[B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

SQL Server quiz

You're managing a SQL Server computer that was installed using default settings. After a power failure, the SQL Server (MSSQLSERVER) service on your database server does not start. You need to find out the cause of the problem. Which action should you perform?

A. In Event Viewer, view the system log or the application log
B. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file.
C. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
D. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.
E. Both A and C
F. Both B and C

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Sunday, September 18, 2011

Local subprogram

DECLARE curContacts IS
  SELECT FirstName, LastName FROM CONTACTS;
  vFormatName VARCHAR2(50);
  FUNCTION FormatName(vFirstName IN VARCHAR2, vLastName IN VARCHAR2)
    RETURN VARCHAR2 IS
  BEGIN
    RETURN vFirstName || ' ' || vLastName;
  END FormatName
BEGIN
  FOR vContact IN curContacts LOOP
    vFormatName := FormatName(vContact.FirstName, vContact.LastName);
    INSERT INTO temp_table(char_col)
    VALUES(vFormatName);
  END LOOP;
  COMMIT;
END;

Saturday, September 10, 2011

CREATE TABLE in Access Database

This script creates a table tblCategory with ID is primary key and auto number,  CategoryName  VARCHAR(255)

CREATE TABLE [tblCategory] (
    [ID] AUTOINCREMENT,
    [CategoryName] TEXT(255),
    [ParentID] LONG,
    [GroupID] LONG,
    CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])
);