The 'Grand Test SQL' quiz assesses knowledge of SQL queries in an Oracle environment, focusing on query syntax, data manipulation, and ANSI compliance. It tests practical skills essential for database management and IT roles.
Where customer = 'LESLIE';
Where customer = 'LESLIE' and order_amt < 2700;
Where customer = 'LESLIE' or order_amt > 2700;
Where customer = 'LESLIE' and order_amt > 2700;
Rate this question:
DEPTNO
JOB
LOC
Sum(A.SAL)
Rate this question:
True
False
Rate this question:
6
13
30
60
Rate this question:
True
False
Rate this question:
Select
From
Where
Having
Rate this question:
. where product_name = 'GAS GRILL' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';
Where product_type = 'APPLIANCE' and product_name = 'GAS GRILL' and qtr_end_date ='31-JAN-2001' or '30-JUN-2001';
. where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';
Where product_name = 'GAS GRILL' and qtr_end_date = '01-JAN-2001' or '30-JUN-2001';
Rate this question:
. select * from dual;
Update emp set empno = 6543 where ename = 'SMITHERS';
Set define ?
Create table employees (empid varchar2(10) primary key);
Rate this question:
Abs( )
Ceil( )
Floor( )
Round( )
Rate this question:
Select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 group by region, prod_type, period;
Select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 order by region, prod_type, period;
Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000;
Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) < 100000;
Rate this question:
True
False
Rate this question:
Oracle returns zero as the result
Oracle returns EMPTY as the result.
Oracle returns NULL as the result
Oracle returns an error as the result
Rate this question:
Select trim(trailing '-dog' from ename) as ename from emp;
Select rpad(ename, 10, '-dog') as ename from emp;
Select substr(ename, 1, 10) as ename from emp;
Select lpad(ename, 10, '-dog') as ename from emp;
Rate this question:
A single-row subquery
A multirow subquery
A from clause subquery
A multicolumn subquery
Rate this question:
Because outer join operations permit NULL values from one of the tables, you do not have to specify equality comparisons to join those tables.
In outer join statements on tables A and B, you specify the right outer join when you want all of table A's rows, even when no corresponding record exists in table B.
In outer join statements on tables A and B, you specify the left outer join when you want all of table B's rows, even when no corresponding record exists in table A.
Even though outer join operations permit NULL values from one of the tables, you still need to specify equality comparisons to join those tables.
Rate this question:
The row for LACKEY in the JOB table already existed with JOB_WAGE set to 35.
A default clause on the JOB_WAGE column defined when the table was created specified the value when the row was inserted
The values clause in the insert statement contained a hidden value that was added when the row was added.
The only possible explanation is that a later update statement issued against the JOB table added the JOB_WAGE value.
Rate this question:
Set the VARCHAR2 column to NULL for all rows.
Create a temporary location for VARCHAR2 data.
Copy the column records from the temporary location back to the main table.
Nothing; the statement is executed automatically.
Tables
Sequences
Indexes
Views
Rate this question:
Ceil( )
Floor( )
.round( )
Trunc( )
Rate this question:
Full outer join
Left outer join
Right outer join
Equijoin
Rate this question:
select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';
Select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU') and product = 'TOYS');
Select sum(profit) from profits where region = ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';
Select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU') , product = 'TOYS';
Rate this question:
Column clause
Table clause
The DUAL table
The where clause
Rate this question:
Select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.prod_id = b.prod_id and prod_name = 'WIDGET';
Select p.prod_id, p.prod_name, b.box_loc fromproduct p, storage_box b where prod_name = 'WIDGET';
Select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and p.prod_name = 'WIDGET';
Select prod_id, prod_name, box_loc from product, storage_box where stor_box_num = stor_box_num and prod_name = 'WIDGET'
Rate this question:
Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S';
Select empno, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S';
Select empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';
Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';
Rate this question:
Select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'(+);
Select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET';
Select p.prod_id, p.prod_name, b.box_loc from product p right outer join storage_box b where b.stor_box_num = p.stor_box_num (+) and p.prod_name = 'WIDGET';
Select p.prod_id, p.prod_name, b.box_loc from product p full outer join storage_box b on p.stor_box_num = b.stor_box_num where b.stor_box_num is NULL;
Rate this question:
ALLEN
MARTIN
TURNER
WARD
Rate this question:
One
Two
Three
NULL
Rate this question:
Ceil( )
Floor( )
Round( )
Trunc( )
Rate this question:
.Two
Three
Four
Five
Rate this question:
Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empno where a.state = 'TEXAS';
. select e.ename, a.street_address, a.city, a.state, a.post_code from emp e left outer join addr a on e.empno = a.empno where a.state = 'TEXAS'
Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno where a.state = 'TEXAS';
Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a where e.empno = a.empno (+) and a.state = 'TEXAS';
Rate this question:
Copy column records to a temporary storage location
Set the NUMBER column to NULL for all rows.
Create a temporary location for NUMBER data.
Copy column records from the temporary location back to the main table.
Rate this question:
Create table cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));
Create table my_cats as select * from cats where owner = 'ME';
Create temp_cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));
Create table cats-over-5-lbs as select c_name, c_weight from cats where c_weight > 5;
Rate this question:
14
-14
168
-168
Rate this question:
5392845.324
871039453.1
75439289.34
60079829.25
Rate this question:
Select deptno, job, sum(sal) from emp group by job, deptno;
Select sum(sal), deptno, job from emp group by job, deptno;
Select deptno, job, sum(sal) from emp;
Select deptno, sum(sal), job from emp group by job, deptno;
Rate this question:
Select product.id, product.name, storage.loc from product, storage where product.box# = storage.box#;
Select product.id, product.name, storage.loc from product join storage on product.box# = storage.box#;
Select product.id, product.name, storage.loc from product natural join storage on product.box# = storage.box#;
Select product.id, product.name, storage.loc from product natural join storage;
You can change data in Oracle using select statements.
You can remove data from Oracle using select statements.
You can create a table with the contents of another using select statements.
You can truncate tables using select statements
ALLEN
MARTIN
TURNER
WARD
Rate this question:
Select p.prod_name, p.profit from (select prod_name, profit from profits order by profit desc) where rownum
Select p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) subq where p.prod_name = subq.prod_name;
Select prod_name, profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum
Select prod_name, profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum
Rate this question:
Quiz Review Timeline (Updated): Mar 21, 2023 +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
Wait!
Here's an interesting quiz for you.