SQL Frequently Asked Questions

Q. SQL objects

SQL objects are schemas, journals, catalogs, tables, aliases, views, indexes, constraints, triggers, sequences, stored procedures, user-defined functions, user-defined types, and SQL packages. SQL creates and maintains these objects as system objects.

Schemas A schema provides a logical grouping of SQL objects. A schema consists of a library, a journal, a journal receiver, a catalog, and, optionally, a data dictionary.

Journals and journal receivers A journal and a journal receiver are used to record changes to tables and views in the database.

Catalogs An SQL catalog is a collection of tables and views that describe tables, views, indexes, packages, procedures, functions, files, sequences, triggers, and constraints.

Tables, rows, and columns A table is a two-dimensional arrangement of data that consists of rows and columns.

Aliases An alias is an alternate name for a table or view.

Views A view appears like a table to an application program. However, a view contains no data and only logically represents one or more tables over which it is created.

Indexes An SQL index is a subset of the data in the columns of a table that are logically arranged in either ascending or descending order.

Constraints A constraint is a rule enforced by the database manager to limit the values that can be inserted, deleted, or updated in a table.

Triggers A trigger is a set of actions that runs automatically whenever a specified event occurs to a specified table or view.

Stored procedures A stored procedure is a program that can be called with the SQL CALL statement.

Sequences A sequence is a data area object that provides a quick and easy way of generating unique numbers.

User-defined functions A user-defined function is a program that can be called like any built-in functions.

User-defined types A user-defined type is a distinct data type that you can define independently of the data types that are provided by the database management system.

SQL packages An SQL package is an object that contains the control structure produced when the SQL statements in an application program are bound to a remote relational database management system (DBMS).

Q. Differnce between oracle and sql server

1. Oracle runs on many platforms, SQL on Windows only
2. Oracle includes IFS (Internet File System), Java integration, SQL is more of a pure database
3. Oracle requires client install and setup (Not difficult, but very UNIX-like for Windows users)
4. SQL is #1 in Cost/Performance and overall Performance, although Oracle will refute that
5. Replication is much easier in SQL (I have been at clients where even the Oracle consultant couldn't get it working w/oracle)
6. Failover support in SQL is much, much easier
7. JDBC support is much better in Oracle, although Microsoft is working on it
8. ODBC support in both
9. SQL is ANSI-SQL '92 compliant, making it easier to convert to another ANSI compliant database, theoretically anyway (truth is every database has proprietary extensions). Oracle is generally more proprietary and their main goal is to keep their customers locked-in.
10. SQL natively supports ODBC, OLEDB, XML, XML Query, XML updates. Oracle natively supports proprietary connections, JDBC. Not sure about XML support though.
11. SQL Server is much easier to administrate, with GUI and command- line tools. Most of Oracle is command-line (Back in SQL 6.5 days I had a customer who was so proud that after a day's worth of work he had managed to script his database. I showed him how it was a 3 click operation in SQL ;-)
12. Oracle requires add-ons for transaction monitors, failover, etc. SQL has COM+, uses NT clustering and generally has everything built-in
13. SQL Analysis Services is included (A very powerful OLAP server). For Oracle it is a separate purchase.
14. Oracle will be used especially in large database. But if we use sql server in such a environment, the data processing will become very slower. Oracle database very closely supports Java rather than Sql server.
15. Oracle 7 was RDBMS, i.e it was a relation database and the one's after that i.e. 8 onwards Oracle introduced the concept of OODBMS. Which stands for Object Oriented Database Management Systems. With every major release Oracle has initiated a lot of changes for the better. Like a. (1) Getting PL/SQL closer to ANSI SQL standards b. (2) Automatic Management of Undo from 9i onwards c. (3) Introduction of special Grouping operators for queries d. (4) ISQL env for easier query processing e. (5) RAC (I dunno much about it) f. (6) AS(Application Server, integrated into the database)
16. SQL server GUI is easy to work. SQL serever is easy to maintain. Orcal data procssing is very good compared to SQL server17. Oracle is like apple and Sql Server is like Orange. For a Poor man Orange is better and for a rich man apple is better.

Q. What is Normal form? Different types of Normal forms?

Q. Advantages of SQL?
a) 1. It is portable.
2. It is non procedure language.
3. It is not a case sencitive.
4. There is no controle structures.
5. It is simple english like structure.
6. Easy to learn.

Q. what is DDL? and What are DDL commands?
a) DDL is data definition language which is used to create, alter, drop the data base objects such as tables, views, indexes, snap shots and sequences.
DDL commands are create, alter, drop.

Q. what is DML? and What are DML commands?
a) DML is data manipulation language. DML commands are insert, delete, update, select, commit, rollback, savepoint, truncate.

Q. what is DCL? and What are DCL commands?
a) DCL is data control language. DCL commands are grant, revoke, lock table.

Q. what are constraints in SQL?
a) 1. unique 2. not null 3. primary key 4. check 5. foreign key
null/not null: To represent blank ness or there is no blank
unique: To maintain unique values(no duplicates)
primary key: Unique + not null
Check: To validate a column ex: salary between 2000 and 6000
Foreign key: which refer one colum value with another column value of another tables primary key

Q. What is composit key?
a) It is compination of 2 or more columns. A composit key can be a primary key.

Q. What is syntax for create table?
a) 1. create table tableName (col1 datatype, col2 datatype........................);
2. syntax with constraints
create table tableName (col1 datatype [constraint ], col2 ...................................)

Q. What is syntax for alter table?
a) Alter table tableName [add/modify] (col name...............)

Q. Alter command with respect to constraints?
a) Alter table tableName [add/modify/drop] [enable/disable] (column name constraint constraint name constraint type)

Q. How to delete a column using alter command?
a) It is impossible using alter command.

Q) How to delete table?
a) Drop table tableName

Q) What is the syntax for insert data into table?
a) 1. full insertion
insert into tableName values (val1, val2........................val n)
2. partial insertion
insert into tableName (col1, col2, col3............col n) values (val1, val2, val3...............val n)
3. paramatric partial insertion
insert into tableName (col1, col2, col3...........col n) values (&val1, &val2, &val3........&val n)

Q) How to remove data from table?
a) Delete from tableName [where ]

Q) How to update data?
a) update tableName column = or [where ]

Q) How to remove all records at a time?
a) truncate table table name

Q. is inner querries better or joins better?
a) joins are better they will take less comparisons.

Q. Why join?
a) Which produces meaningful out put from two or more tables.

Q. Different types of joins?
a) 1. equi join 2. non equi join
1. equi join: To get meaning ful data from two or more tables when a common column among the two tables.
2. non equi join: To get meaning ful data from two or more tables with out having any common column such type of joins are called non equi joins

Q. Difference between inner join and outer join?
a) if a row doesn't satisfy the join condition then the row will not appear in the query result for inner join. The missing rows can be returned if an outer join operator is used.

Q. Difference between left outer join and right outer join?
a) Left outer join will display all the data from left side table and matched data from the right side table.
Right outer join will display all the data from the right side table and matched data from the left side table

Q. what is self join?
a) A table which joins it self.

Q. What is a subquery?
a) Placing a select statement in the where clause of another statement the. statement which is used in the where clause is called inner querry and other is treated as outer querry.

Q. What is a view?
a) View is nothing but select statement. There is no physical existence for view. Views are masks placed upon tables. view are updatable and dynamic.

Q. What are the advantages of view?
a) 1. Simplify the querries.
2. Provides data security.
3. Reducing the data redundency.
4. We can fetch the data from 1 or more tables.

Q. Syntax for creating a view?
a) create view as select statement

Q. What is a forced view?
a) A view which is created with out base table.

Q. Syntax for creating a forced view?
a) create or replace forceview viewName as Query

Q. Is it possible to update a view?
a) yes if it dont have join condition, group function, group by clause, distinct clause

Q. Is it possible to add data to a view?
a) yes if it dont have join condition, group function, group by clause, distinct clause.

Q. How to create a squence?
a) To generate sequence numbers.
syn:- create sequence increment by start with value [min value, max value]

Q. How to insert sequence value into a table?
a) insert into tablename values( sequenceName.nextValue,................);

Q. How to get current value of a sequence?
a) select sequence.currval from dual;

Q. How to alter a sequence?
a) Alter sequence incremented by value maxValue value , minValue value

Q. How to delete a sequence?
a) Drop sequence sequenceName

Q. What is an index?
a) 1. Indexes are used to speed up the retrival operations on a table.
2. Indexes are also used to issue that no duplicate values enter into a column.
3. We may also create an index on multiple columns.
4. There is no limit for inxexes on a table.

Q. Syntax for index?
a) create [unique] index indexName on tableName (column list)

Q. How to remove an index?
a) Drop index indexNname

Q. What is a synonym?
a) A synonym is another name for a table or view. These can be created as public or private.
Public synonyms can be used by any user of the data base.
Private synonyms can be used only by the oner and by any users that have been granted suffitient privilizes.
There is no physical existence for synonyms.

Q. Syntax for synonyms?
a) create [public/private] synonym for tableName

Q. How to drop synonym?
a) Drop synonym synonymName

Q. What is a snapshot?
a) It is a read only table. If we remove actual table then also it will exist.

Q. Syntax for creating a snapshot?
a) create snapshot as query

Q. How to drop snap shot?
a) Drop snapshot

Q. What is a transaction?
a) The number of operations between two successive commits and two successive role backs.

Q. Syntax for commit?
a) commit

Q. Syntax for rollback?
a) It is used to cancle the transaction
Rollback [work] to [save point] save point name

Q. What is savepoint?
a) It is used to devide a transaction into small portions.
syn:
savePoint

Q. What is a role?
a) A role is a group of previlizes.

Q. What is a prevelize?
a) A kind of accessability on a pirticular databas objects.

Q. Different types of Roles?
a) 1. connect 2. Resource 3. DBA 4. Imp_Full_Database 5. exp_edit_patals

Q. How to grant Roles to a user?
a) grant [connect, resource, dba, imp_full_database, exp_edit_patals] to

Q. How to remove Roles for a user?
a) revoke roles from

Q. Different types of previlizes?
a. select, insert, update, delete, alter, index, references, all

Q. How to grant previlizes on a table to a user?
a) grant previlizes on table name to user name.
ex:- grant select on emp to batch1

Q. How to remove previlizes on a table from a user?
a) Revoke previlizes on table from users;
ex:- Revoke select on emp from batch1.

Q. different types of indexes?
Q. Difference between oracle and mysql?
Q. Difference between oracle and sqlserver?
Q. Difference between normal server and webserver?
Q. Write SQL query to retrieve employees who are in valid department?
Q. Write a query to retrieve names which are starting with 'L'?
Q. How to get employee name & manager name who are working under manager?
columns are empno, name, manager.
Q. Write a query to find second maximum salary?
Q. How to insert a row between 1 and 3?