CLR Quiz

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

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

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

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

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

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

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

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

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

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