CREATE TABLE EMP
(EMPNO DECIMAL(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR DECIMAL(4),
HIREDATE DATE,
SAL DECIMAL(7,2),
COMM DECIMAL(7,2),
DEPTNO DECIMAL(2));
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
CREATE TABLE DEPT
(DEPTNO DECIMAL(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) );
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
CREATE TABLE BONUS
(
ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL DECIMAL,
COMM DECIMAL
);
CREATE TABLE SALGRADE
( GRADE DECIMAL,
LOSAL DECIMAL,
HISAL DECIMAL );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
1. Display those who are not managers?
2. Display those employees whose salary is more than 3000 after giving 20% increment?
3. Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while joined the company before 31-DEC-82?
4. Find the nearest Saturday after Current date?
5. Display the common jobs from department number 10 and 20?
6. Display the names of employees from department number 10 with salary greater than of ALL employee working in other departments?
7. Display the employee names truncating last 3 characters.
8. Display the names of employees whose name is exactly five characters in length.
9. Display the average salary drawn by managers
10. Display the various jobs and total number of employees working in each job group?
11. Display name,salary,Hra,pf,da,TotalSalary for each employee.
The out put should be in the order of total salary ,hra 15% of salary ,DA 10% of salary .pf 5% salary Total Salary will be (salary+hra+da)-pf?
12. Display empno, ename, deptno from emp table. Instead of display department numbers display the related department name (Use decode function)?
13. Display the job code instead of job description:
a. Clerk--CLRK
b. Salesman—SLMN
c. Manager—MNGR
d. Analyst—ALST
e. President—PRDT
14. In a table with 50,000 records, that contains credit card numbers as xxxx-xxxx-xxxx with varchar2 datatype, I have a requirement wherein I don’t need the hyphen (-) and rather I need the entire card number together. How would you frame an SQL Query to make the required change?
15. What will be the output for the following queries?
Query 1. SELECT * FROM EMPLOYEE WHERE Salary <> NULL;
Query 2. SELECT * FROM EMPLOYEE WHERE Salary = NULL;
A. Query 1 will give last 4 rows as output (excluding null value).
B. Query 2 will give first row as output (only record containing null value)
C. Query 1 and Query 2 both will give the same result
D. Can’t say
16. What will be the output of the following query?
DELETE FROM DEPT
WHERE DEPARTMENT_NAME ='FINANCE'
OR 1=1;
WHERE DEPARTMENT_NAME ='FINANCE'
OR 1=1;
a. All Departments other than those named "FINANCE" will be deleted
b. All Departments in the table will be deleted
c. Only the Departments named "FINANCE" will be deleted
d. No records will be deleted from the table
e. This statement will throw a syntax-related error
17. Given the table mass_table:
Weight |
5.67 |
34.567 |
365.253 |
34 |
null |
Write a query that produces the below output
Weight | kg | gms |
5.67 | 5 | 67 |
34.567 | 34 | 567 |
365.253 | 365 | 253 |
34 | 34 | 0 |
18. Given a table Employee having the columns EmpName and EmpId, what will be the result of the SQL query below:
Select EmpName from Employee order by 2 desc;
a. It will list all the records ordered by empName
b. b. It will throw an error while execution
c. c. It will list all the records ordered by empid
d. d. It will return null
19. What is wrong in the following code?
DECLARE
c_id := 1;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
Select name, address INTO c_name, c_addr
From customers
Where id= c_id;
END;
a. You cannot use the SELECT INTO statement of SQL to assign values to PL/SQL variables
b. The SELECT INTO statement here is wrong. It should be: SELECT c_name, c_address INTO name, addr
c. The WHERE statement is wrong. It should be: WHERE id := c_id;
d. The variable c_id should be declared as a type-compatible variable as –c_id customers.id%type :=1;
20. What would be the output of the following code?
DECLARE
num number;
fn number;
FUNCTION fx(x number)
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fx(x-1);
END IF;
RETURN f;
END;
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fx(x-1);
END IF;
RETURN f;
END;
BEGIN
num:= 5;
fn := fx(num);
dbms_output.put_line(fn);
END;
num:= 5;
fn := fx(num);
dbms_output.put_line(fn);
END;
a. 1
b. 5
c. 10
d. 125
--------------------------------------------------
1. Which of the following is true concerning a procedure?
a. You do not create them with SQL.
b. They do not need to have a unique name
c. They include procedural and SQL statements
d. They are the same thing as a function
2. A CASE SQL statement is which of the following?
a. A way to establish an IF-THEN-ELSE in SQL
b. A way to establish a loop in SQL
c. A way to establish a data definition in SQL
d. All of the above
3. Which of the following is one of the basic approaches for joining tables?
a. Subqueries
b. Union Join
c. Natural join
d. All of the above
4. Which of the following statements is true concerning subqueries
a. Involves the use of an inner and outer query
b. Cannot return the same result as a query that is not a subquery
c. Does not start with the word SELECT
d. All of the above
5. Which of the following is a correlated subquery
a. Uses the result of an inner query to determine the processing of an outer query.
b. Uses the result of an outer query to determine the processing of an inner query.
c. Uses the result of an inner query to determine the processing of an inner query.
d. Uses the result of an outer query to determine the processing of an outer query.
6. SQL views can be used to hide:
a. columns and rows only.
b. complicated SQL syntax only.
c. both of the above can be hidden by an SQL view.
d. None of the above is correct.
7. In a sequence, _________________ is used to specify no integers are to be stored:
a. NOCACHE
b. ORDER
c. NOORDER
d. NOCYCLE
8. In a view, WITH CHECK OPTION:
a. Specifies that only the rows that would be retrieved by the subquery can be inserted
b. Specifies that only the rows that would be retrieved by the subquery can be updated
c. Specifies that only the rows that would be retrieved by the subquery can be deleted
d. Specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted
9. Oracle database automatically creates an index for the primary key of a table and for columns included in a unique constraint:
a. True
b. False
10. Trim removes a set of characters from the left of a string:
a. True
b. False
11. Which of the following way is correct to add x months to a month?
a. SELECT ADD_MONTHS('01-JAN-2005', 13) FROM dual
b. S ELECT ADDMONTHS('01-JAN-2005', 13) FROM dual;
c. SELECT ADD MONTHS('01-JAN-2005', 13) FROM dual;
d. SELECT MONTHS_ADD(13,'01-JAN-2005') FROM dual;
12. Which of the following gets the largest integer less than or equal to x?
a. TRUNC
b. CEIL(x)
c. FLOOR(x)
d. ACOS(x)
13. What is UNISTR(x) used for?
a. Converts the characters in x to the national language character set
b. Converts the characters in x to the national language character set (VARCHAR)
c. Converts the characters in x to the national language character set (CHAR)
d. Converts the characters in x to the national language character set (NCHAR)
14. Which of the following Metacharacter Matches the position at the start of the string?
a. ^
b. #
c. ?
d. *
15. What is the difference between an "empty" value and a "null" value?
a. They both are the same.
b. A null value is treated as a empty string in Oracle
c. You cannot query a null value
d. An empty string is treated as a null value in Oracle
16. What is the syntax for disabling a trigger?
a. DISABLE TRIGGER trigger_name;
b. ALTER TRIGGER trigger_name DISABLE;
c. Both a an b can be used
d. ALTER TRIGGER DISABLE trigger_name;
17. What is the meaning of + sign in a condition like where consumer.consumer _id = orders.consumer _id(+);
a. indicates that, if a consumer _id value in the consumer table does not exist in the orders table, all fields in the orders table will display as in the result set.
b. indicates that, if a consumer _id value in the consumer table exists in the orders table, all fields in the orders table will display as in the result set.
c. indicates that, if a consumer _id value in the consumer table does not exist in the orders table, all fields in the orders table will display as default value in the result set.
d. indicates that, if a consumer _id value in the consumer table exists in the orders table, all fields in the orders table will display as default value in the result set.
18. The HAVING clause is used in combination with the ORDER BY clause:
a. True
b. False
19. What are the uses of synonyms?
a. Mask the real name and owner of an object.
b. Provide public access to an object
c. Provide location transparency for tables, views or program units of a remote database
d. A & B
e. A & C
f. All of the above
20. The pre-defined exception CASE_NOT_FOUND is raised when:
a. None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
b. PL/SQL has an internal problem.
c. A cursor fetches value in a variable having incompatible data type.
d. None of the above.
