Oracle sql online test

1. A group function produces ______.

2. You need to analyze how long your orders to be shipped from the date that the order is placed. To do this you must create a report that displays the customer number, date order, date shipped and the number of months in whole numbers from the time the order is placed to the time the order is shipped. Which statement produces the
required results?

3. Your company will be granting workers a $150 salary increase. You need to evaluate results of the increase from the worker table prior to the actual modification. You do not want to store in the results in the current database. Which of the following is untrue?

4. You create the sal table with this command

CREATE TABLE sal (purchase-no NUMBER(9)
CONSTRAINT sale-purchase-no-pk PRIMARY KEY,
costumer-id NUMBER(9) CONSTRAINT sale-customer-id-nl NOT NULL);

Which index or indexes are created for this table?

5. The emp table contains these columns:
Last_name Varchar2 (25)
First_name Varchar2 (25)
Salary Number7, 2
You need to display the names of employees on more than an average salary of all
employees. Evaluate the SQL statement.
SELECT, LAST_NAME, FIRST_NAME from emp where salary< avg(salary);

Which change should you make to achieve the desired results?

6. You want of display the details of all employees whose last names is john. But you are not sure in which case last names are stored. Which statement will list all the employees whose last name is john?

7. What command should be used to execute a script file named create_table.sql from the SQL Plus environment?

8. You need to create a report to display the ship date and order totals of your order table. If the order has not been shipped your report must display not shipped. If the total is not available your report must say not available. In the order table the ship date column has a data type of date the total column has a data type of number. Which statement do you use to create this report?

9. You need to store currency data and you know that data will always have two digits to the right of the decimal points. However the number of digits to the left of the decimal place will vary greatly. Which data type would be most appropriate to store the data?

10. How would you add a foreign key constraint on the dept_no column in the EMP table. Referring to the ID column in the DEPT table?

11. Examine the WORKER table below:

NAME NULL TYPE
WORKER ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
PHONE NOT NULL VARCHAR2(9)
ADDRESS VARCHAR2(50)
POSITION UPDATE

There are hundreds of records in the EMPLOYEE TABLE. You need to modify the phone column to hold only number values. Which statement will modify the data type appropriately?

12. You have decided to permanently remove all the data from the student_masters table and you need the table structure in the future. Which single command performs this?

13. Examine the following command:

CREATE TABLE pay_master
(employee-id Number(9)
CONSTRAINT pay-pk PRIMARY KEY,
2000-pay NUMBER(8,2)
manager-name VARCHAR2(25)
CONSTRAINT mgr-name-nn. NOT NULL,
pay-96 NUMBER(8,2));

Which two lines of this command will return an error?

14. The emp table contains these columns:
FIRST-NAME VARCHER2(25)
COMISSION NUMBER(3,2)
Evaluate this SQL statement
SELECT first-name,commission FROM emp WHERE commission= (SELECT comission FROM emp WHERE UPPER(first-name)= ‘smith’)
Which statement will cause this statement to fail?

15. You query a database with the following command:

SELECT dept_no, AVG (MONTHS_BETWEEN (SYSDATE, hire_date)) FROM worker_company
WHERE AVG (MONTHS_BETWEEN (SYSDATE, hire_date)) > 60
GROUP BY dept_no
ORDER BY AVG (MONTHS_BETWEEN (SYSDATE, hire_date) )

Where does the statement cause an error?