IMPORTANT SQL QUESTIONS

1)      List various data types available in oracle. Explain it.

2)      What is constraint? List various constraints. Explain Primary Key.

3)      Explain referential integrity constraint. (F.K.)

4)      Explain business constraint.( check constraint )

5)      Explain various types of joins in oracle.

6)      Explain index and its types in detail.

7)      What is view? Explain views in detail.

8)      Explain sequence with example.

9)      Explain synonym.

10)  Explain group by and having with example.

11)  Explain union, intersect and minus.

12)  List various types of functions available in oracle. Explain aggregate function(group functions : avg,min,max,count,sum,greatest,least).

13)  Explain string functions. (lower, upper, initcap, substr, length, ltrim, rtrim, lpad, rpad, instr, soundex)

14)  Explain numeric functions. (abs,ceil,floor,mod,power,round,sign,sqrt,trunc,cos,sin,tan,log,ln)

15)  Explain date functions. (add_months, last_day, months_between, next_day)

16)  Explain conversion functions. (to_number, to_date, to_char)

17)  List various collection types. Explain any one. (Varray, Nested table, PL/SQL table).

18)  Explain grant and revoke. Explain role.

Read More

ORACLE ARCHITECTURE

1)      State E.F.Codds’s 12 rules of RDBMS.

2)      What is database? Explain processes.

3)      Explain database support processes (or server processes).

4)      List and explain oracle background processes.

5)      Define blocks, segments and extents.

6)      Explain control files and redo log files.

7)      Explain types of tablespace in oracle.

8)      Explain initialization parameter (or init.ora) file.

9)      The logical and physical structure of oracle database.

10)  List and explain responsibilities of DBA.

11)  Explain oracle instance.

12)  Explain oracle memory structure.

13)  Explain SGA with its components.

14)  Explain PGA.

15)  Explain difference between DDL and DML.

16)  Explain E-R diagram and types of relationship.

17)  What is Entity? Explain type of relationship between entities with diagram.

18)  What is transaction? Explain with a facility to undo the changes.

19)  Explain SQL*Loader.

20)  Explain IMPORT & EXPORT facility in oracle.

Read More

The SQL SELECT Statement

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT column_name(s) FROM table_name

and

SELECT * FROM table_name

Note: SQL is not case sensitive. SELECT is the same as select.

---

An SQL SELECT Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the content of the columns named "LastName" and "FirstName" from the table above.

We use the following SELECT statement:

SELECT LastName,FirstName FROM Persons

The result-set will look like this:

LastName	FirstName
Hansen	Ola
Svendson	Tove
Pettersen	Kari

SQL - Select All (*)

"SELECT (*)" is a shortcut that can be used to select all table columns rather than listing each of them by name. Unfortunately, going this route doesn't allow for you to alter the presentation of the results.

SQL Select All Query:

USE mydatabase;

SELECT *
FROM orders;

SQL Orders Table Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4

SQL - Selecting Data

The (*) query statement should be used with caution. Using this against our little tutorial database will surely do no harm, but using this query against an extremely large database may not be the best practice. Large databases may have web services or applications attached to them, so frequently updating and accessing large quantities data may temporarily lock a table for fractions of a second or more. If this disruption happens to occur just as some piece of data is being updated, you may experience data corruption.

Taking every precaution to avoid data corruption is in your best interest as a new SQL programmer. Corrupted data may be lost and never recovered, and it can lead to even more corruption inside a database. The best habits are to be as precise as possible, and in the case of select statements, this often means selecting minimal amounts of data when possible.

At this point, you should feel comfortable with SELECT and how to look into your database and see actual data rows residing inside of tables. This knowledge will prove invaluable as your SQL skills develop beyond the basics and as you begin to tackle larger, more advanced SQL projects.

---

 SELECT * Example

Now we want to select all the columns from the "Persons" table.

We use the following SELECT statement: 

SELECT * FROM Persons

Tip: The asterisk (*) is a quick way of selecting all columns!

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Read More