Database Knowledge

CLR Quiz

2011-11-25T23:58:00.001-08:00

You're developing an application which has a feature to read data from a spreadsheet, performs some calculations, and returns the data to a SQL Server computer. You decide to use CLR function and need to register the assembly with SQL Server by using the CREATE ASSEMBLY statement and the least privileged security permission set. Which permission set should you use?

A. Default
B. SAFE
C. EXTERNAL_ACCESS
D. UNSAFE
E. Both B and C
F. None of the above

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

Login failed

2011-11-13T00:48:00.001-08:00

You're developing a website with 5 members. A member needs access to a SQL Server database that is located on a server named SQL1. You create a login named member1 by using the following Transact-SQL statement. CREATE LOGIN member1 WITH PASSWORD = 'member$1'. The member reports that when he logs in, he receives the following error message: "Login failed. The user is not associated with a trusted SQL Server connection." You need to resolve the error and allow him to access to SQL1. What should you do?

A. Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
B. Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode.
C. Give the login access to a specific database by using the CREATE USER Transact-SQL statement.
D. Ensure that the login name is created with square brackets ([]).
E. Both A and C
F. None of the above

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

NOSQL Databases

2011-11-12T23:13:00.000-08:00

In computing, NoSQL (sometimes expanded to "not only SQL") is a broad class of database management systems that differ from the classic model of the relational database management system (RDBMS) in some significant ways. These data stores may not require fixed table schemas, usually avoid join operations, and typically scale horizontally.
Academic researchers typically refer to these databases as structured storage, a term that includes classic relational databases as a subset.
Source: wiki

LIST OF NOSQL DATABASES

SQL Quiz

2011-10-22T20:26:00.000-07:00

You discovered that the schema changes that were recently made to your SQL Server database have caused your applications (website, mobile, or desktop apps) to stop functioning. It is unclear who made the changes. You need to implement a mechanism that will track schema changes in your database. What should you do?

A. Implement a stored procedure that writes data about schema changes to a log table.
B. Implement DDL AFTER triggers that write user and schema information to a log table.
C. Implement a DML INSTEAD OF trigger that writes data about schema changes to a log table.
D. Implement a DML AFTER trigger that writes data about schema changes to a log table.
E. Both A and C
F. None of the above

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

Review SQL2K5 Features

2011-10-16T00:32:00.001-07:00

I'm sure you might have been forgot something or you haven't used all of them yet.
EN_TSQL

Add new column as NOT NULL to existing table

2011-10-15T23:33:00.000-07:00

You are modifying a table named tblEmployee in a SQL Server database. You want to add a new column named JobTitle to the tblEmployee table. The table currently contains data. The HR Department has not yet created a job title for each employee. JobTitle is a required value for each employee. You want to add this new column by using the least amount of effort. What should you do?

A. Define the new column as NULL. Update the JobTitle column to the same value as 'Undefined'. Modify the JobTitle column to be NOT NULL.
B. Define the new column as NOT NULL with a default value of 'Undefined.'
C. Define the new column as NULL. Use application logic to enforce the data constraint.
D. Define the new column as NULL with a default value of 'Undefined.'
E. Both A and B
F. None of the above

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

Return a resultset in stored procedure with SYS_REFCURSOR

2011-10-15T22:06:00.000-07:00

The following stored procedure is used to check user login information. If user info is correct, update LAST_LOGIN_DATE

CREATE OR REPLACE
PROCEDURE CHECK_LOGIN
  (I_USER_ID VARCHAR2,
    I_PWD VARCHAR2,
    O_RS OUT SYS_REFCURSOR
  )
IS
 vCount INTEGER:=0; 
BEGIN  
   SELECT COUNT(*) INTO vCount 
 FROM USER
        WHERE USER_ID = I_USER_ID AND PWD = ORA_HASH(I_PWD);
   IF (vCount > 0) THEN
  BEGIN
   UPDATE USER
   SET LAST_LOGIN_DATE = SYSDATE
   WHERE USER_ID = I_USER_ID;
     
   OPEN O_RS FOR
   SELECT USER_ID, USER_NAME, USER_STATUS, EMAIL, WEBSITE
   FROM USER
   WHERE USER_ID = I_USER_ID AND PWD = ORA_HASH(I_PWD);
  END;  
 ELSE
  OPEN O_RS FOR
  SELECT USER_ID, USER_NAME, USER_STATUS, EMAIL, WEBSITE
                FROM USER
  WHERE 1 = 2;   
 END IF;
END CHECK_LOGIN;

Create View Quiz

2011-10-05T09:03:00.000-07:00

You are creating a view to join the tblEmployee and tblDepartment tables in a SQL Server database. You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to accomplish this goal by using the least possible amount of overhead. What should you do?

A. Create CHECK constraints on the tables.
B. Create a DDL trigger to roll back any changes to the tables if the changes affect the
columns in the view.
C. Create the view, specifying the WITH SCHEMABINDING option.
D. Create the view, specifying the WITH CHECK option.
E. Both C and D
F. None of the above

Answer: [C]
Highlight to find out the answer.
Come here to know more about CREATE VIEW
Visit here to practice your SQL Server skills.

OpenERP: get configuration of hr.department

2011-10-02T08:09:00.000-07:00

Screenshot of hr.departments.form in front-end.

You can find back-end text with the following query

SELECT arch 
FROM ir_ui_view
WHERE name = 'hr.department.form'

OpenERP: modify hr_department

2011-10-02T03:05:00.000-07:00

Assume that you want to add a field description in hr_department table instead of using custom filed and also add this field into edit form of hr_department, follow these steps:

1. Add description field in hr_department:

ALTER TABLE hr_department
   ADD COLUMN description text;

2. Modify form view

Find temp tables

2011-10-02T01:12:00.000-07:00

Local temporary tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances.
Local temporary table name starts with hash ("#") sign.
If you want to find all local temporary tables in current connction, you can use the following script

SELECT *
FROM tempdb.sys.objects
WHERE name LIKE '#%';

OpenERP: hr_department scripts

2011-10-02T00:35:00.000-07:00

Main fields: hr_department(id, name, parent_id, company_id, manager_id)
Screenshot:

CREATE TABLE hr_department
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  note text, -- Note
  parent_id integer, -- Parent Department
  "name" character varying(64) NOT NULL, -- Department Name
  company_id integer, -- Company
  manager_id integer, -- Manager
  CONSTRAINT hr_department_pkey PRIMARY KEY (id),
  CONSTRAINT hr_department_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES res_company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT hr_department_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT hr_department_manager_id_fkey FOREIGN KEY (manager_id)
      REFERENCES hr_employee (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT hr_department_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES hr_department (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT hr_department_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE hr_department OWNER TO "admin";
COMMENT ON TABLE hr_department IS 'hr.department';
COMMENT ON COLUMN hr_department.note IS 'Note';
COMMENT ON COLUMN hr_department.parent_id IS 'Parent Department';
COMMENT ON COLUMN hr_department."name" IS 'Department Name';
COMMENT ON COLUMN hr_department.company_id IS 'Company';
COMMENT ON COLUMN hr_department.manager_id IS 'Manager';


CREATE INDEX hr_department_company_id_index
  ON hr_department
  USING btree
  (company_id);

CREATE INDEX hr_department_parent_id_index
  ON hr_department
  USING btree
  (parent_id);

Trigger quiz

2011-10-01T21:52:00.000-07:00

Your company uses a SQL Server database to manage data on e-Commerse site. This database contains a trigger named tgInsertOrder, which fires when order data is inserted into the tblOrder table. The trigger is responsible for ensuring that a customer exists in the tblCustomer table before data is inserted into the tblOrder table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort.
Which T-SQL statements can you use to achieve this goal?

A. ALTER TABLE tblOrder DISABLE TRIGGER tgInsertOrder
B. DROP TRIGGER tgInsertOrder
C. DISABLE TRIGGER tgInsertOrder ON tblOrder
D. ALTER TRIGGER tgInsertOrder ON tblOrder NOT FOR REPLICATION
E. Both A & C
F. None of the above

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

Query transactions by date

2011-09-25T05:25:00.001-07:00

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.

Data type precedence

2011-09-24T21:38:00.000-07:00

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

2011-09-24T21:29:00.000-07:00

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.

Local subprogram

2011-09-18T04:34:00.000-07:00

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;

CREATE TABLE in Access Database

2011-09-10T02:23:00.000-07:00

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])
);

PL/SQL quiz

2011-07-29T22:22:00.000-07:00

The employee table contains these columns:
LAST_NAME VARCHAR2(50)
FIRST_NAME VARCHAR2(50)
SALARY NUMBER(8, 2)
You need to display the names of employees on more than an average salary of all employees.
Evaluate the SQL statement.
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE SALARY < AVG(SALARY);
Which change should you make to achieve the desired results?

A. Change the function in the WHERE clause.
B. Move the function to the SELECT clause and add a GROUP clause.
C. Move the function to the SELECT clause and add a GROUP BY clause and a HAVING clause.
D. Use a sub query in the where clause to compare the average salary value.

Answer: [D]

DBCC OPENTRAN

2011-07-29T01:34:00.000-07:00

You manage a database named LTDB. Today, you received a warning that the drive on which the LTDB log file is located is near capacity. Although the transaction log is backed up every five minutes, you observed that it is steadily growing. You think that an uncommitted transaction might be the cause and you want to investigate. You need to identify both the server process ID and the start time of the oldest active transaction in LTDB. What should you do?

A. Connect to the LTDB database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
B. Connect to the master database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
C. In SQL Server Management Studio, open the Activity Monitor. Select the Process Info page and apply the following filter settings.
Database = LTDB, Open Transactions = Yes, View the Process ID and Last Batch columns.
D. Open a query window. Connect to the master database. Execute the following statement
SELECT TOP 1 spid, last_batch
FROM sys.sysprocesses WHERE dbid = db_id('LTDB') AND open_tran > 0
ORDER BY last_batch

Answer:[A]
Highlight to find out the answer

My DB2 Connection string

2011-06-08T09:18:00.000-07:00

Provider=DB2OLEDB;User ID=admin;Password=xxxxx;Initial Catalog=SAMPLE;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=MyComputerName;Network Port=50000;Package Collection=Library;Default Schema=Library;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=Library;DBMS Platform=DB2/NT;Defer Prepare=False;Persist Security Info=True;Connection Pooling=False;Derive Parameters=False;

This connection string is the same with production server.

Total security in a PostgreSQL database - StumbleUpon

2011-05-22T23:26:00.000-07:00

Total security in a PostgreSQL database - StumbleUpon: "- Sent using Google Toolbar"

My OpenERP services setting

2011-05-08T05:35:00.000-07:00

1. OpenERP Server 6.0 (Automatic)
2. OpenERP Web 6.0 (Automatic)
3. postgresql-9.0 - PostgreSQL Server 9.0 (Automatic)

My Oracle services setting

2011-05-08T05:32:00.000-07:00

1. OracleDBConsoleMyERP (Automatic)
2. OracleJobSchedulerMYERP (Disabled)
3. OracleOraDb11g_home1TNSListener (Automatic)
4. OracleServiceMYERP (Automatic)

XOR operator

2011-05-06T22:58:00.000-07:00

What will the output of the following scripts?
DECLARE @A INT, @B INT
SELECT @A = 12, @B = 15
SET @A = @A^@B
SET @B = @B^@A
SET @A = @A^@B
SELECT @A, @B

A. 12,15
B. Error occurs: no operator ^
C. Error occurs: incorrect syntax
D. 15,12
E. 13,14

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