Oracle Database SQL Free Sample Questions

It displays all cities in descending order, within which the last names are further sorted in descending order.

It fails because a column alias cannot be used in the ORDER BY clause.

It fails because a column number and a column alias cannot be used together in the ORDER BY clause.

It displays all cities in ascending order, within which the last names are further sorted in descending order.

finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

listing of customers who do not have a credit limit and were born before 1980

finding the number of customers, in each city, whose marital status is 'married'.

listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.

During execution, the Oracle server may read data from storage if the required data is not already in memory.

During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.

During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

It best supports relational databases.

It is used to define encapsulation and polymorphism for a relational table.

It guarantees atomicity, consistency, isolation, and durability (ACID) features.

It provides independence for logical data structures being manipulated from the underlying physical data storage.

It requires that data be contained in hierarchical data storage.

The ORDER BY Clause can be used in a subquery.

A subquery can be used in the FROM clause of a SELECT statement.

If a subquery returns NULL, the main query may still return rows.

A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.

Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.

It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

It enables sessions to store a query block permanently in memory and use it to create complex queries.

It enables sessions to store the results of a query permanently.

You can use a column alias in the GROUP BY clause.

Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.

The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.

Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.

If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

SELECT last_name, city FROM members WHERE state - MO' AND state ='MI';

SELECT last_name, city FROM members WHERE state LIKE 'M%';

SELECT last_name, city FROM members WHERE state IN ('MO', 'Ml');

SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

INSERT INTO transaction VALUES ('AT, DEFAULT, TO_DATE(DEFAULT+10));

INSERT INTO transaction VALUES ('AT, DEFAULT, TO_DATE('SYSDATE+10'));

INSERT INTO transaction (trn_ID, end_date) VALUES ('AT, '10-DEC-2014');

INSERT INTO transaction (trn_ID, start_date, end_date) VALUES ('AT,, '10-DEC-2014');