Tuesday, July 6, 2010

SQL (Quick Interview Guide)



1) There are two products:
a) Oracle 9i Database—Stores all your data
b) Oracle 9i Application Server—Runs all our applications like
1) Portals or Websites
2) Java Transactional Applications
3) Business Intelligence Applications

2) Oracle 9i is Object Relational Database Management system(ORDBMS). It supports 10,000 of concurrent users and 512 petabytes of data. It can handle any type of data including text, spatial, image, sound, video.

3) Database is developed using System Development life cycle:
a) Strategy & Analysis—Study & Analyze the business requirements and build a model.
b) Design—Design a database based on model
c) Build & Document—Create tables and supporting objects & develop user documentation, help & Operations manual.
d) Transition—Test the database and make any modifications if required.
e) Production—Rollout the system to users & Monitor its performance.

4) Pieces of Information is called data.

5) Database is an organized collection of information. To manage database we need DBMS. There are 4 main types of databases: Hierarchical, networking, relational, object relational.

6) Oracle 7 is RDBMS and Oracle 8,8i,9i are ORDBMS. Dr. E.F.Codd proposed the relational model for database systems in 1970.

7)
a) Entity: A thing of Significance about which information need to be known. Eg: Table.
b) Attribute: Something that describes or qualifies an entity. Eg: Column
c) Relationship between entities. Eg: Employee & Department.
d) UID: Unique Identifier is any combination of attributes or relationships or both that serves to distinguish occurrences of an entity.

8) With an RDBMS you can relate data in one table with data in another table by using primary & foreign key relationships. Foreign key is a column or set of columns that refer to a primary key in the same table or another table.

9) SQL is a structured query language or ANSI standard language. SQL statements are used to create, access and modify the database.

10) Various SQL Statements are:
a) Data Retrieval: Select
b) DML: Insert, Update, Delete
c) DDL: Create, Alter, Drop, Rename, Truncate
d) Transaction Control: Commit, Rollback, Savepoint
e) DCL: Grant, Revoke
f) Session Control: Alter Session, Set Role
g) System Control: Alter System
h) Embedded SQL Statements: Open, Close, Fetch & Execute.

11) Select statement can do the following:
a) Projection: Choose the columns in a table.
b) Selection: Choose the rows in a table.
c) Joining: Brings data together that is stored in different tables by creating link between them

12) Arithmetic Operators +, -, *, / are used in any clause of SQL Statement except in from clause. Oracle 9i ignores blank spaces before & after Arithmetic operators.

13) iSQL*Plus is a web enabled version of SQL*Plus. It uses 3 Tier architectural model comprising Client Tier, Middle Tier and Database Tier

14) A Null is a value that is unavailable, unassigned, unknown, or inapplicable. Any Arithmetic operation performed on null results in null.

15) Column Aliases that contains spaces or special characters (# or $) must enclose in double quotation marks. Aliases always appear in uppercase unless enclosed within double quotation marks.

16) We can link columns, arithmetic expressions or constant values using the concatenation operator( )

17) A literal value is a character, number, or a date that is included in Select Statement and is not a column name or a column alias. Date and Character literals must be enclosed in ‘ ’ , number literals need not.

18) Distinct Keyword is always used immediately after Select clause to eliminate duplicate rows in the result.

19) Difference between SQL & iSQL*Plus:

SQL
a) It is a command language for communicating with oracle server from any tool or application
b) ANSI standard SQL
c) Manipulates Data and Table definitions in Database

iSQL*Plusa) It is an oracle tool that recognize and submits SQL Statements to oracle server for Execution and contain its own command language
b) Oracle Proprietry
c) Does not allow manipulation of values in Database
d) Runs on a browser
e) Centrally Loaded, does not have to be implemented on each machine

20) We can restrict the rows returned by the query using Where Clause

21) Comparison Operators =, >, <, >=, <=, <> are used in where clause to compare one expression to another value or expression. Other comparison conditions are BETWEEN…AND…, IN, LIKE, IS NULL. In LIKE condition Escape option is used to match characters %, &, _ present in column values.

22) Logical conditions AND, OR, NOT are used in where clause to join two conditions to produce a single result. Order of Precedence is NOT,AND & OR.

23) In Order by Clause Null values are displayed in last for ascending sort & first in descending sort. By default we have ascending sort. We can also use column alias in order by clause.

24) SQL Functions Sometimes takes Arguments and Always Returns a Value.

Single Row Functions: Operates on single row always & returns one result per row.

1) Character Functions:
a) Case Manipulation Functions: LOWER, UPPER, INITCAP
b) Character Manipulation Functions:
CONCAT
SUBSTR
LENGTH
INSTR
LPAD RPAD
TRIM
REPLACE
TRANSLATE

2) Number Functions:
ROUND
TRUNC
MOD

3) Date Functions:
ROUND
TRUNC
SYSDATE
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
• date+number=date(Adds no. of days to a date)
• date-number=date(Substracts no. of days from a date)
• date-date= Number of days(Substracts one date from another)
• date+number/24=date(Add no. of hours to a date)

4) Conversion Functions:
TO_NUMBER
TO_DATE
TO_CHAR with Dates (YY,MM,DY,DD,DAY)
TO_CHAR with Numbers (9,0,$,L, ., , )

5) General Functions:
NVL (Converts Null value to Actual Value)
NVL2 (If expr1 is not null, Return expr2 else expr3)
NULLIF (Compares 2 expr & returns null if they are
equal, or 1st expr if they are not equal)
COALESCE (Returns 1st not null Expr in the Expr list)

• Multiple Row Functions: Manipulate Group of Rows to give one result per group of rows.

25) If-Then-Else logic is implemented within a SQL Statements using
a) CASE Expressions
b) Decode Functions.

26) Comparison between Joins:

Oracle Proprietary
a) Equijoin
b) Outer Join
c) Self Join
d) Non-Equijoin
e) Cartesian Product

SQL 1999
a) Natural or Inner Join
b) Left or Right or Full outer Join
c) Join ON
d) Join USING
e) Cross Join

Note: To join ‘n’ Tables Together, we need a minimum of ‘n-1’ join condition.

27) Group Functions: Operates on set of rows to give one result per group. They ignore null values. Below are few of them
AVG
COUNT(*, expr, Distinct expr)
MAX
MIN
STDDEV
SUM
VARIANCE
Note: We cannot use group functions in where clause but we can use it in Select, Having, Order by clause. Group Functions can be nested to a depth of Two while Single Row Functions are nested to any level.

28) When using the Group By clause, make sure that all columns in the Select list that are not group functions are included in the Group By clause. Also you cannot use column alias in Group By clause. By default columns included in Group By clause are sorted in ascending order.

29) Having clause is used to restrict Groups. Having clause can precede the Group By clause but it is recommended that you place the Group By clause first because it is more logical. Also we can use Group By clause without Having Clause but we cannot use Having Clause without Group By.

30) The inner query is called subquery. It returns a value that is used by the outer query or the main query. We can place the subquery in a number of SQL clauses like Where, Having & From Clause.

31) Single Row Subquery: It returns single row & uses single row operators like <, >, <=, >=, <>
Multiple Row Subquery: It returns more than one row & uses multiple row operators like IN, ANY, ALL
Note: ANY: More than the minimum, ALL: More than the maximum

32) Substitution Variables: It is a Variable used to store values temporarily and values are substituted when statement runs. Values for the variables are supplied by user using ‘&’ or
predefined using DEFINE command. Value is present for the variable only till Session.
&: User is prompted every time the command is executed
&&: It is used if you want to reuse the variable value without prompting the user each time

33) Command Variables are used for sql report generation

34) Collection of DML Statements that form a Logical Unit of work is called as Transaction. In other words Transaction begins with first DML Statements & ends when,
a) Commit or Rollback Statement is issued
b) DDL Statement is issued
c) DCL Statement is issued
d) User Exits the session
e) Machine Fails or System Crashes

35) We can use subquery in Insert Statement, Set Clause of Update Statement & Delete statement.

36) DML Statements always Generates Rollback Information whereas DDL & DCL Cannot, Because DDL & DCL Statements are Automatically Committed while DML Statements need to be explicitly committed by user.

37) Default Keyword is used to set the default value for column. If default value is not specified for column then it sets to null if user do not enter any value.

38) Merge Statement is used to Update or Insert a row conditionally into a table, thus avoiding multiple Update Statement

39) Locks: This are the Mechanism which Prevents destructive interaction of 2 different users Transactions accessing the same Resource. There are 2 types of Locking in Oracle.
a) Implicit Locking ( Obtained Automatically by oracle )
b) Explicity Locking( Manually using commands )

• Implicit Locking
a) Share Lock: It is obtained at the table level during DML Operations. With Share lock mode, several transactions can acquire share locks on the same resource.
b) Exclusive Lock: It is acquired for each row modified by DML Statement. It prevents the row from being changed by other transactions until the transaction is committed or rolled back.

40) Savepoint: It is used as a Marker and created to Rollback the Informations upto that Marker point. This are not the database objects, hence are not stored in data dictionary.

41) Schema: It is a collection of Objects. Schema objects are logical structures that directly refer to the data in a database. Schema objects includes Tables, View, Sequences, Indexes, Synonyms, Clusters, Database Links, Stored Procedures.

42) Table & Column Names
a) Must begin with a letter i.e. alphabet
b) Must be 1 to 30 character long
c) Must contain only A-Z, a-z, 0-9, _, $, #,%
d) Must not duplicate any Oracle Reserved word
e) Must not duplicate the name of another object owned by the same user
f) Are Case Insensitive
Note: Table can have at the most 1000 columns.

43) There is another collection of tables & views in the Oracle Database known as the Data Dictionary. This collection is created and maintained by the Oracle Server and contains metadata i.e. data about data.

44) Three main Schemas of Oracle 9i Database are
a) SYSTEM
b) SCOTT (Users Schema)
c) SYS
• SYS Schema contains
1) All data dictionaries in the form of views
2) Oracle supplied procedures, functions, Packages
3) Table like AUDIT_ACTIONS, DUAL, STMT_AUDIT_OPTION_MAP, SYSTEM_PRIVILEGE_MAP, TABLE_PRIVILEGE_MAP. Etc.

There are 4 main Categories of Data Dictionary views
a) USER_: Contains information about objects owned by user
b) ALL_: Contains information about all of the tables accessible to the user
c) DBA_: Accessed by only DBA person
d) V$: Dynamic performance views, database server performance, memory & locking.

45) Some of the Data-types are,
a) VARCHAR2 (Max. Size = 4000 bytes)
b) CHAR (Max. Size = 2000 bytes)
c) NUMBER ( Size = 1 to 38)
d) DATE
e) LONG (2 GB)
f) LONG RAW (2 GB)
g) RAW ( 2000 bytes)
h) CLOB (4 GB)
i) BLOB (4 GB)
j) BFILE (4 GB)
k) Timestamp With Local Time Zone
l) Interval Year to Month
m) Interval Day to Second

46) We can Add, Modify & Drop columns using Alter Table Statement. The new column always becomes the last column in the table. The table must have atleast one column after it is altered

47) We can set one or more columns as unused using SET UNUSED option in ‘Alter Table’ Statement so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped i.e. it does not occupy disk space, hence response time is faster if we execute Drop clause. Structure as well as data of unused column is inaccessible.

48) Difference between Delete & Truncate Statements:
a) Delete Statement can be Rolled back whereas Truncate cannot
b) Deleting rows from a table will not remove disk space whereas Truncating a table remove all disk space
c) Structure remains same in both the statement
d) Truncate statement is faster than Delete because
1) Truncate is DDL statement & generates no Rollback information
2) Truncating table does not fire Delete triggers of the table

49) Constraints: This are used,
a) To prevent invalid data entry into tables
b) Enforce rules on the data in a table whenever a row is inserted, updated or deleted from that table
c) Prevents deletion of a table if there are dependencies from other table
d) Provides rules for Oracle tools such as Oracle Developer

Constraints are Defined at Table Level & Column Level. They are,
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

50) Not Null Constraint is defined only at column level & not table level. The composite Unique & Primary key are defined at Table level. A table can have one Primary key constraint but several Unique key constraint.

51) Syntax for Column level constraint:
column CONSTRAINT constraint_name constraint_type
Syntax for Table level constraint:
CONSTRAINT constraint_name constraint_type(column_name)

52) We can Add constraint, Add Not Null Constraint using Modify Clause, Drop Constraint, Drop Cascade Constraint, Enable Constraint, Disable Constraint using Alter Table Statement. But we cant modify the structure of existing constraint.

53) View: It is a Logical Representation of Subsets of data from a table. A view contains no data of its own but is like a window through which data from tables can be viewed or changed.

There are 2 types of Views:
a) Simple View: It is one that,
1) Derives data from only one table
2) Contains no functions of groups of data
3) Can perform DML operations through view into the table

b) Complex View: It is one that,
1) Derives data from many tables
2) Contains functions or groups of data
3) Does not allow DML operations through the view.

54) While Creating a view,
a) It does not contain an Order By Clause. Order By clause is specified when we retrieve the data from view.
b) With Check Option Constraint constraint_name specifies that only rows accessible to the view can be inserted or updated.
c) With Read Only Option Ensures that no DML Operations can be performed on view.

55) We cannot perform Delete Operation on view if it contains,
a) Group functions
b) A Group By clause
c) Distinct Keyword
d) Pseudocolumn Rownum keyword

56) We cannot Modify the view if it contains,
a) Group functions
b) A Group By clause
c) Distinct Keyword
d) Pseudocolumn Rownum keyword
e) Columns defined by expression(Eg: sal*12)

57) We cannot Insert through view if it contains,
a) Group functions
b) A Group By clause
c) Distinct Keyword
d) Pseudocolumn Rownum keyword
e) Columns defined by expression(Eg: sal*12)
f) Not Null columns in the Base tables that are not selected by view.

58) Inline View: It is created by placing subquery in From Clause and giving that subquery an alias. It is used to perform Top-N-Analysis.

59) Sequence: It is Database object & is created to generate a primary key value. Sequence values are referenced by Pseudo-columns Nextval & Currval. This columns are used in Select, Insert & Update Statement, But they are not used in following contexts:
a) Select List of a view
b) Select statement with Distinct Keyword, Group By,Having & Order By clause
c) Subquery in Select, Update, Delete statement
d) Default expression in a Create Table or Alter Table statement.

Note: We can Prevent Oracle from using an Index & do full Table Scan by Adding Expressions or Hints in SQL Queries.

60) Nocache Option is used while creating a sequence to view the next available sequence value without incrementing it, because if the sequence is cached values in the memory are lost if system crashes.

61) START WITH Options cannot be changed using Alter Sequence.

62) Index: It is a schema object that can speed up the retrieval of rows using a pointer. If index is not created on column, whole table scan occurs. There are types of Indexes:
a) Unique Index: Created automatically by oracle server for columns in a table with Primary Key & Unique Key Constraint. Name of the index is same as that of constraint.
b) Non-Unique Index: Created Explicitly by User especially for Foreign Key column

63) We should create an index if,
a) Columns contains a wide range of values
b) Column contains large number of null values
c) One or more columns are frequently used together in a Where clause or join condition.
d) Table is large and most queries are expected to retrieve less than 2-4% of rows.

64) Function Based index is based on Expression or Functions. Dropping a Table will also drops all its Related Non-Unique Indexes.

65) Synonym: It is created to provide an alternative name to database Objects. DBA can create a public synonym accessible to all users. Only DBA can drop public synonym.

66) Privileges are the right to Execute particular type of SQL statements. There are 2 types of privileges:
a) System Privileges: Gaining Access to the database
Eg: Create User, Create Table, Create Procedure, Select Any Table, Create Session.etc.

b) Object Privileges: Manipulating the content of the objects in the database.
Eg: Alter, Select, Insert, Update, Delete, Execute, Index & References.

67) Role: It is a named group of related privileges that can be granted to the user. Grant Statement is used to allocate System & Object Privileges to user. ‘With Grant Option’ gives user the authority to grant same privileges to any other user.
Revoke Statement is used to take out privileges granted to the user. If user A revokes the privileges from user B then privileges granted to user C & D are also revoked.

68) A database link is a pointer that defines a one way communication path from an Oracle database server to remote database server. One way means users of Database A can accesss database B only if link of B is stored in A, Similarly users of Database B can access database A only if link of A is stored in B. We cannot Grant Privileges to Remote objects.

69) SET Operators: It combines the results of two or more component queries. Queries containing SET operators are called compound queries.
a) UNION: All distinct rows selected by either query
b) UNION ALL: All rows selected by either query, including duplicates
c) INTERSECT: All distinct rows selected by both queries
d) MINUS: All distinct rows that are selected by 1st Select Statement and not selected in 2nd Select Statement.

70) CHR is a SQL Function that converts an ASCII code to its corresponding Character. Whereas ASCII Function is used to convert Character to ASCII Value.

71) There are only 2 Objects which can be created by same name in same schema Table & Trigger

72) Rowid: It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.

73) We can Execute Operating System Commands from SQL Prompt using ! or HOST, however HOST perform Variable Substitution(i.e.&,&&) while ! Cannot.

74) Some of the Important Data Dictionaries are,

a) USER_CATALOG
b) USER_COL_PRIVS
c) USER_CONSTRAINTS
d) USER_CONS_COLUMNS
e) USER_DB_LINKS
f) USER_DEPENDENCIES
g) USER_ERRORS
h) USER_EXTENTS
i) USER_INDEXES
j) USER_IND_COLUMNS
k) USER_JOBS
l) USER_LIBRARIES
m) USER_OBJECTS
n) USER_ROLE_PRIVS
o) USER_SEGMENTS
p) USER_SEQUENCES
q) USER_SNAPSHOTS
r) USER_SYS_PRIVS
s) USER_TABLES
t) USER_TABLESPACES
u) USER_TAB_COLUMNS
v) USER_TAB_PRIVS
w) USER_TRIGGERS
x) USER_USERS
y) USER_VARRAYS
z) USER_VIEWS

Que: How can a SQL Script be Executed Automatically as soon as SQL*Plus is Invoked?
Ans: When SQL*Plus Starts up, it will look for a global login script called ‘glogin.sql’, if found this script will be executed. Thereafter it will try to find a local login script called ‘login.sql’, if found it will execute it. Both the Scripts are found in C:\Oracle\Ora90\sqlplus\admin\glogin.sql

Note: DBA can get a Grant over Dynamic Performance tables like v$statname, v$session, v$sesstat by running the script C:\Oracle\Ora90\sqlplus\admin\plustrce.sql

Note: Path for Superb Help: C:\Oracle\Ora90\sqlplus\admin\iplus\

Que: How to Display Number Values in Words?
Ans: Select Sal, (to_char(to_date(Sal,'j'),'jsp')) from emp;
Here ‘j’ means Julian Day, the number of days since January 1,4712 BC & sp means to spell out.

75) Partitioning: It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.
They are of following Types:
a) Range Partitioning
b) Hash Partitioning
c) List Partitioning
d) Composite Range-Hash Partitioning

76) Cluster: A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.

77) Difference between Nested Subquery & Correlated Subquery:

Nested Subquerya) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query
b) Outer query is driver by Inner Query

Correlated Subquerya) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query
b) Inner Query is Driven by Outer Query

78) Difference between Translate & Replace:
Translate function converts each character in String with specified one whereas
Replace function replaces part of the string in continuity by another sub-string.

2 comments:

  1. Wow. This one is the best guide I have ever found to learn all the basic concepts of SQL. This post will not only help to just revise the concepts but also a novice can get quick information about all the terms and is able to learn the basic meaning too.

    ReplyDelete
  2. Thanks for posting this blog i, in truth, loved it and located up a few unique blogs about oracle........go to our internet web page associated with oracle fusion procurement all modules schooling
    regarding
    Oracle Fusion procurement Coaching
    Oracle Fusion procurement Training Institute

    ReplyDelete

Note: Only a member of this blog may post a comment.