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

Find temp tables

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

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

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.