Here I am giving few good questions from Oracle Sql domain.Please do try these. Answer and explanation is provided
1) 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?
A. Select order, shipdate “Not shipped”,total “Not available” FROM order;
B. Select order, NVL (shipdate ‘Not shipped’),NVL (total, “Not available”) FROM order;
C. Select order, NVL (TO_CHAR (shipdate), ‘Not shipped’),NVL (TO_CHAR (total), ‘Not available’) FROM order;
D. Select order, TO_CHAR (shipdate, ‘Not shipped’) TO_CHAR (total, ‘Not available’) FROM order;
Explanation:Answer C shows correct syntax of command NVL
2) 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?
A. Select last name, first nam FROM emp WHERE last name= ‘john’;
B. Select last name, first name FROM emp WHERE UPPER (last name)= ‘john’;
C. Select last name, first name FROM emp WHERE last name=UPPER (‘john’);
D. Select last name, first name. FROM emp WHERE LOWER (last name)= ‘john’;
Explanation:Select last name, first name FROM emp WHERE LOWER (last name)= ‘john’
Answer D shows all records with last name john because function LOWER returns the
column value passed as x into all lowercase
3)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
A. SELECT custid, orderate, shipdate, ROUND(MONTHS_BETWEEN(shipdate,orderate)) “Time Taken”FROM order;
B. SELECT custid, orderate, shipdate, ROUND(DAYS_BETWEEN(shipdate,orderate))/30 FROM order;
C. SELECT custid, orderate, shipdate,ROUND OFF (shipdate-orderate) “Time Taken” FROM order;
D. SELECT custid, orderate, shipdate, MONTHS_BETWEEN (shipdate,orderate) “Time Taken” FROM order
Explanation:Answer A shows the number of months (rounded to integer) between the date of order and the date of shipment.
4)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?
A. Change the function in the Where clause.
B. Move the function to the select clause and add a group clause.
C. Use a sub query in the where clause to compare the average salary value.
D. Move the function to the select clause and add a group by clause and a having clause.
Explanation:Answer C shows the correct way to change query, because function AVG can not be used in WHERE clause.
5)The emp table contains these columns:
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?
A. smith has a null commission resolution.
B. smith has a zero commission resolution.
C. There is no employee with the first name smith.
D. The first name values in the data base are in the lower case.
Answer A is correct because if smith has a null commission expression in WHERE clause will cause error.
6) 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-nk NOT NULL);
Which index or indexes are created for this table?
A. No indexes are created for this table.
B. An index is created for purchase_no column.
C. An index is created for the customer_no column.
D. An index is created for each column.
Answer B is correct because index will be created for PRIMARY KEY column automatically during table creation. Also index is created for UNIQUE constraint, but this table creation statement does not include any UNIQUE constraint.
7) 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?
A. Use the ALTER TABLE command with the ADD clause in the DEPT table.
B. Use the ALTER TABLE command with the ADD clause on the EMP table.
C. Use the ALTER TABLE command with the MODIFY clause on the DEPT table.
D. Use the ALTER TABLE command with the MODIFY clause on the EMP table.
E. This task cannot be accomplished.
Answer B is correct because constraint will be created for EMP table using ALTER TABLE command.
8) Examine the structure of student_masters table:
Name Null Type
STU ID NOT NULL NUMBER(3)
Currently the table is empty. You have decided that null values should not be allowed for the NAME column. Which statement restricts NULL values from being entered into column?
A. ALTER TABLE student_masters ADD CONSTRAINT name(NOT NULL);
B. ALTER TABLE student_masters ADD CONSTRAINT NOT NULL (name);
C. ALTER TABLE student_masters MODIFY CONSTRAINT name(NOT NULL);
D. ALTER TABLE student_masters MODIFY(name varcher2(25) NOT NULL);
Answer D is correct because this query add NOT NULL constraint to student_masters table
9) 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?
A. DROP TABLE student_masters;
B. TRUNCATE TABLE student_masters;
C. DELETE* FROM student_masters;
D. TRUNCATE TABLE student_masters KEEP STRUCTURE;
E. DELETE* FROM student_masters KEEP STRUCTURE.
Answer B is correct because after truncating table you delete all data and keep table and its structure for future use. Also command TRUNCATE reset highwatermark level to zero for table.
10) 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?
Answer A is correct because by default NUMBER is NUMBER(L, P) type, which is always stored as variable-length data, where 1 byte is used to store the exponent, 1 byte is used to store for every two significant digits of the number’s mantissa, and 1 byte is used for negative numbers if the number of significant digits is less than 38 bytes.