BASIC INTERVIEW QUESTION ON SELECT STATEMENT

Which Version of Oracle are you using? 

Oracle 10 g version or  Oracle 11 g version.

What is the meaning of i, g and c in oracle version? 

I - Stands For Internet
G - Stands For Grid
C - Stands For Cloud

What is Schema in Oracle?

Schema is a logical structures of data or schema objects.

What is Schema Objects?

Schema means user, schema objects means when the object is created by the user it means the schema object like table,view,synonym,sequence, index etc.

What is dual table in oracle?

• Dual table is a dummy table in database
• Its used to process our own data.
• The owner of the dual table is sys
• Data type is varchar2
• Default Colomn name is Dummy
• Data Name is X.

What are all the Schema objects available in oracle?

Oracle having many Schema objects Some of them are table, view, synonym, sequence, index etc

What is SQL?

• SQL Stands for Structuted Query Language
• SQL is comment line Its used to interact with the database

What is Data Dictionary table in oracle?

• Its Contains all information about the Structure and object of the database 
• Its created and maintained by database
• The Data  Stored in data dictionary table are also called as meta Data.

How to select data from other schema?

SELECT * From Schema_name.user_tables;
Now Schema Name is HR Means
SELECT * FROM HR.user_tables;

How to Select Unique Records from the particular column? or How To select distinct records from the particular column.

To SELECT the unique or Distinct of particular records from the table means
SELECT DISTINCT column Name from table_name;

How to view the structure of the Table?

Describe Table_name;
Desc Table_name;

Which Data Dictionary table contain Table information?

User_tables Contains table information
Select * from user_tables; --Table_information

Which Data Dictionary Table contain information about all the objects in database?

All_objects Contains all the objects in database
Select * from all_objects; -- object_type

iSQL*Plus commands access the database

FALSE

The following SELECT statement executes successfully: SELECT last_name, job_id, salary AS Sal FROM   employees;

Yes The Query is successfully Executed.

The following SELECT statement executes successfully:
SELECT * FROM   job_grades;

There is no table in the name of job_grades. So its not executed.If the table name created named as job_grades means it will be executed.

There are four coding errors in this statement. Can you identify them? 
SELECT employee_id, last_name sal x 12  ANNUAL SALARY FROM employees;

1.After the last_name Comma is Missing.
2.There is no column name is sal.
3.The symbol of multiplication is *
4.Alias name having spaces without ""

Show the structure of the DEPARTMENTS table. Select all data from the table.

DESC DEPARTMENTS;
SELECT * FROM Departments;

Show the structure of the EMPLOYEES table. Create a query to display the last name, job code, hire date, and  employee number for each employee, with employee number appearing first.Provide an alias STARTDATE for the  HIRE_DATE column. Save your SQL statement to a file named lab1_7.sql.

DESC EMPLOYEES;
SELECT Employee_id, Last_name, Job_id, Hire_Date "STARTDATE" FROM Employees;

Create a query to display unique job codes from the EMPLOYEES table.

SELECT DISTINCT job_id FROM employees;

Copy the statement from lab1_7.sql into the iSQL*Plus Edit window. Name the column headings Emp #, Employee, Job, and  Hire Date, respectively. Run your query again. 

SELECT employee_id "EMP # ",First_name "Employee", job_id "Job", Hire_Date FROM employees;

Display the last name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title.

SELECT last_name || ',' || job_id "Employee and Title" FROM employees;

Create a query to display all the data from the EMPLOYEES table. Separate each column by a comma. Name the column THE_OUTPUT. 

SELECT  Employee_id || ',' || First_Name || ',' ||Last_name || ',' ||Email || ',' ||Phone_number || ',' ||Hire_date || ',' ||job_id || ',' ||salary || ',' ||Commission_pct || ',' ||Manager_id || ',' ||Department_id "THE OUTPUT" FROM Employees;