SharePoint 2010: Video References

Videos:  SharePoint 2010

1. Interview Questions - SharePoint 2010  
2. Interview Questions 2
3. Interview Questions - All

Oracle 11g: Insert Statement

===>  SQL Queries:  Use & to Prompt Value

insert into departments
(
  department_id,
  department_name,
  location_id
)

values
(&department_id,'&department_name',&locationid)

===>  SQL Queries:  Copying rows from another table

insert into sales_reps(id_name,salary,commission_pct)
select employee_id,last_name,salary,commission_pct
from employees
where job_id like '%REP%'

Oracle 11g: Creating A Table By Using SubQuery

===>  SQL Queries

create table dept80
as
  select employee_id,
         last_name,
         salary*12 ANNSAL,
         hire_date
  from employees
  where department_id = 80;
 
select * from dept80; 

Results:
create table succeeded.
EMPLOYEE_ID   LAST_NAME   ANNSAL          HIRE_DATE                
---------------------- ------------------------- ---------------
145                    Russell                   168000                 01-OCT-96                
146                    Partners                  162000                 05-JAN-97                
147                    Errazuriz                 144000                 10-MAR-97                
148                    Cambrault                 132000                 15-OCT-99                
149                    Zlotkey                   126000                 29-JAN-00                
150                    Tucker                    120000                 30-JAN-97                
151                    Bernstein                 114000                 24-MAR-97                
152                    Hall                      108000                 20-AUG-97                
153                    Olsen                     96000                  30-MAR-98                
154                    Cambrault                 90000                  09-DEC-98                
155                    Tuvault                   84000                  23-NOV-99                
156                    King                      120000                 30-JAN-96                
157                    Sully                     114000                 04-MAR-96                
158                    McEwen                    108000                 01-AUG-96                
159                    Smith                     96000                  10-MAR-97                
160                    Doran                     90000                  15-DEC-97                
161                    Sewall                    84000                  03-NOV-98                
162                    Vishney                   126000                 11-NOV-97                
163                    Greene                    114000                 19-MAR-99                
164                    Marvins                   86400                  24-JAN-00                
165                    Lee                       81600                  23-FEB-00                
166                    Ande                      76800                  24-MAR-00                
167                    Banda                     74400                  21-APR-00                
168                    Ozer                      138000                 11-MAR-97                
169                    Bloom                     120000                 23-MAR-98                
170                    Fox                       115200                 24-JAN-98                
171                    Smith                     88800                  23-FEB-99                
172                    Bates                     87600                  24-MAR-99                
173                    Kumar                     73200                  21-APR-00                
174                    Abel                      132000                 11-MAY-96                
175                    Hutton                    105600                 19-MAR-97                
176                    Taylor                    103200                 24-MAR-98                
177                    Livingston                100800                 23-APR-98                
179                    Johnson                   74400                  04-JAN-00                

34 rows selected

Oracle 11g: Creating Constraints

Types Of Constraints

• not null
• primary key
• foreign key
• check
• unique

===>  SQL Queries: Column Level

create table employee_column_level
(
  employee_id number(6) CONSTRAINT emp_emp_level_id_pk primary key,
  first_name varchar2(20)
);







create table employee_table_level
(
  employee_id number(6),
  first_name varchar2(20),
  CONSTRAINT emp_emp_table_id_pk primary key (employee_id)
);

Oracle 11g: Create Tables

Naming Rules

• MUST begin with a Character
• Length : 1-30 characters long
• Can only contains:  A-Z, a-z, $,_,#,0-9
• CANNOT be duplicate
• CANNOT be oracle reserved words.

Must Have

• CREATE TABLE privilege
• a storage area.

If referring to other User's table, please use Users as prefix before table name: 
Select * from userA.employees

===>  SQL Queries:  Creating New Table & Use DEFAULT
create table dept
(
  deptno number(2),
  dname varchar2(14),
  loc  varchar2(13),
  create_date DATE default sysdate
);

describe dept;

Results

create table succeeded.

describe dept;
Name                           Null     Type                                                   
------------------------------ -------- ---------------
DEPTNO                                NUMBER(2)               
DNAME                                 VARCHAR2(14)          
LOC                                       VARCHAR2(13)                                                      
CREATE_DATE                    DATE                                           

Oracle 11g: Synonyms

Why use Synonym?

• create easy reference
• shorten lengthy object name

===>  SQL Queries:  Create a Synonym
create synonym d_sum
for dept_sum_vu;

Results
create synonym succeeded.


===>  SQL Queries:  Drop a Synonym
drop synonym d_sum;

Results

drop synonym d_sum succeeded.

Oracle 11g: Indexes

How Indexes Being Created?

• Automatically
• When we created Primary Key 
• Manually

Guidelines To Create Index

• A column has wide range values
• A column has large number of null values
• One or more columns are frequently used together (Clause/Join Condition) 
• Large Table

===>  SQL Queries:  Create New Index
create index emp_last_name_idx
on employees(last_name);

Results
create index succeeded.


===> SQL Queries:  Removing an Index
drop index emp_last_name_idx;


Results:

drop index emp_last_name_idx succeeded.

Oracle 11g: Sequences

===>  SQL Queries:  Creating A Sequence


create sequence dept_deptid_seq
                increment by 10
                start with 120
                maxvalue 9999
                nocache
                nocycle;

results

create sequence succeeded.

===> SQL Queries:  Using A Sequence

• NEXTVAL [nextvalue]
• CURRVAL[currentvalue]
• NEXTVAL must be issued first before CURRVAL has value

insert into departments(department_id,department_name,location_id)
values (dept_deptid_seq.NEXTVAL,'Support',2500);

select dept_deptid_seq.CURRVAL
from dual;

===>  SQL Queries:  Alter a Sequence

alter sequence dept_deptid_seq
               increment by 20
               maxvalue 999999
               nocache
               nocycle

Rules on modifying the sequence:

• Must have the privilege to Alter
• Only future sequence nos are affected
• To restart the sequence no, the sequence must be dropped and recreated

Results :
alter sequence dept_deptid_seq succeeded.

===>  SQL Queries:  Removing  a Sequence

drop sequence dept_deptid_seq;

Results

drop sequence dept_deptid_seq succeeded.

Oracle 11g: Clauses For View

===>SQL Queries:  WITH CHECK OPTION[make sure that the DML operations performed stay in the domain of the view]

create or replace view empvu20
as
select *
from employees
where department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;

select * from empvu20;

Results :
create or replace view succeeded.
EMPLOYEE_ID   FIRST_NAME  LAST_NAME   EMAIL   PHONE_NUMBER  HIRE_DATE              JOB_ID     SALARY  COMMISSION_PCT   MANAGER_ID   DEPARTMENT_ID         
---------------------- -------------------- --------------------
201  Michael   Hartstein  MHARTSTE  888.122  17-FEB-96  MK_MAN    3000      100  20   
202  Pat          Fay          PFAY        603.123   17-AUG-97    MK_REP     6000     201    20 



===>SQL Queries:  WITH READ ONLY[No DML Operation can be done through the view.]

create or replace view empvu10
(employee_number,employee_name,job_title)
as
select employee_id,
       last_name,
       job_id
from employees
where department_id = 10
WITH READ ONLY;

SELECT * FROM empvu10;

Results:
create or replace view succeeded.
EMPLOYEE_NUMBER    EMPLOYEE_NAME       JOB_TITLE 
---------------------- ------------------------- ----------
200                    Whalen                    AD_ASST   

1 rows selected

Oracle 11g: Rules For Performing DML Operations On a View

• can always perfrorm DML operations om simple views
• cannot remove a row, if view contains:
• Group Function
• Group By
• Distinct
• RowNum
• cannot add data thru view, if view contains:
• Group Function
• Group By
• Distinct
• RowNum
• Parent table that has columns - not null 
• cannot modify data in view, if view contains
• Group Function
• Group By
• Distinct
• RowNum
• Expressions (case when, decode)