SQL Interview Questions and Answers Part6

Q: How do you save the inserted transaction?
A: COMMIT;
Q: Change the "last_name" column value from “Smith” to “Judd” where the
"employee id" is 100.
A: SQL > UPDATE employee
SET lastname = 'Judd'
WHERE empid = 100;
Q: Delete all the employee records from the "employee" table using the delete
command and the truncate command.
A: SQL > DELETE FROM employee;
OR
SQL > TRUNCATE TABLE employee;
Q: How do you undo a transaction?
A: ROLLBACK;
Q: What is the difference between the delete statement and the truncate
statement?
A: Notice that the TRUNCATE command is a DDL statement and all DDL statements
have commit inclusive. That is why the ROLLBACK action after truncation does not
work. Also, if you want to delete all records from a table, you should use the
TRUNCATE statement. It will change the table watermark. The table watermark is an
address that indicates a last location of a record in a table. On the DELETE
statement the watermark will not change. But using the TRUNCATE statement will
change the watermark to the beginning of the table.
Q: Copy the “EMP” table to another table and name the new table "employee." In
the new employee table use the employee name, job, commission and department
number.
A: SQL > CREATE TABLE employee
AS SELECT ename, job, comm, deptno
FROM emp;
Q: Add a salary column to the employee table.
A: SQL > ALTER TABLE employee
ADD (salary NUMBER(8,2));
Q: Modify the "ename" column size from varchar10 to varchar15.
A: SQL > ALTER TABLE employee
MODIFY (ename VARCHAR2(15));
Q: Rename the "employee" table to the "iself_employee" table.
A: SQL > RENAME employee TO iself_employee;
Q: Create a view to display the employee names of the “Accounting” department
only.
A: SQL > CREATE VIEW employee_name
AS SELECT ename
FROM iself_employee
WHERE deptno = 10;
Q: Why do you use the view?
A: You use view to present rows and columns of a table in the way you want.
You may use it for security reason. For example, you may eliminate some rows and
columns that are very sensitive information. These changes are transparent to a
user.
Q: How do you compile the view?
A: SQL > ALTER VIEW employee_name COMPILE;
Q: How do you delete the view?
A: SQL > DROP VIEW employee_name;
Q: Create an index on the employee table on the ename column only and name it
employee_indx.
A: SQL > CREATE INDEX employee_indx
ON employee (ename);
Q: Reorganize the “employee_indx” index table.
A: SQL > ALTER INDEX employee_ indx REBUILD;
Q: Drop the employee_ename index table.
A: SQL > DROP INDEX employee_indx;
Q: Create a user with username “newuser” and password "newpass." Its default
tablespace should be the "iself_data" tablespace.
A: SQL > CREATE USER newuser IDENTIFIED BY by newpass
DEFAULT TABLESPACE iself_data;
Q: Grant the resource and connect roles to newuser.
A: SQL > GRANT resource, connect TO newuser;
Q: Change the newuser password to "mypass".
A: SQL > ALTER USER newuser IDENTIFIED BY mypass;
Q: Can the above new user access to any other user tables?
A: No.
Q: What is a public synonym?
A: It is a synonym that public users can use. We create public synonym so that
the users don’t need to type schema name to a table when they query the table.
Creating a public synonym does not mean that oracle users can access to that
table or object. Still the owner of the object has to grant access to a user on
its table.
Q: What is the syntax to create a public synonym?
A: SQL > CREATE PUBLIC SYNONYM employees FOR iself.iself_employee;
Q: What is the difference between public and private synonym?
A: The private synonym is only for the user who owns or created the synonym,
but the public synonym can be used by every users.
Q: Create and drop a private synonym.
A: SQL > CREATE SYNONYM emp_table FOR iself.iself_employee;
To drop:
SQL > DROP SYNONYM emp_table;
Q: Revoke an object privilege on a table from a user.
A: SQL > REVOKE UPDATE, SELECT ON employee FROM newuser;
Q: What does the LIST or ‘L’ command line editor?
A: It lists the current SQL statement that was typed in the Oracle buffer.
Q: What does the INSERT or ‘I’ command line editor?
A: It inserts a command in the Oracle buffer after the current active line
that was indicated with an *.
Q: What does the DEL or ‘D’ command line editor?
A: It deletes the current active line in the Oracle Buffer.
Q: How do you change a string in the Oracle Buffer?
A: First, mark the line as a current active line and then type the‘del’
command.
Q: How do you save the SQL script in the Oracle Buffer?
A: SQL> save c:\myscript.sql
Q: How do you open the SQL Script into the Oracle Buffer?
A: SQL> get c:\myscript.sql
Q: How do you use the notepad editor?
A: Just type: the ed command to open the default editor.
Q: What is afiedt.buf?
A: The "afiedt.buf" file is a place that into which SQL *PLUS stores the most
recently executed SQL statement.
Q: How do you change your text editor in the SQLPLUS tool?
A: Issue the define_editor='your editor' statement from the SQL *PLUS prompt.
Q: What does the ed command in the SQLPLUS tool?
A: We use the "ed" command, to open your default word editor.
Q: Can you have multiple SQL statements in the afiedt.buf file?
A: No. You can only use one SQL statement at a time.
Q: How do you use the notepad editor as an independent tool in the SQLPLUS
utility?
A: Just open your notepad editor outside of your SQLPLUS.
Q: How do you execute or run a SQL script?
A: SQL> run c:\myscript.sql or start c:\myscript
Q: What is the SQL ANSI statement?
A: It is some standard roles that provided by American National Standards
Institute.
Q: What is the difference between the SQL ANSI statement and Original Oracle
statement?
A: The Original Oracle statements are not follow the role of American National
Standards Institute.
Q: Is the SET command a SQL statement?
A: No.
Q: How do you change your workstation’s page size or line size?
A: SQL > SET LINESIZE 100 PAGESIZE 55
Q: What does the JOIN syntax in the Oracle SQL (DML) statement?
A: It does innor join using the ON clause.
SQL > SELECT ename, dept.deptno, dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
AND dname <> 'SALES'
/
Q: What is the difference between the JOIN syntax and the NATURAL JOIN syntax?
A: In the NATURAL JOIN syntax, you don't need the ON clause if the column’s
names are the same.
Q: What does the USING clause in the Oracle SQL statement?
A: It joins two tables and in the USING clause the join column names must be
the same.
Q: What is the advantage of the NATURAL JOIN syntax?
A: It is less typing.
Q: What does the CROSS JOIN syntax in the Oracle SQL statement?
A: We can use the Oracle9i ANSI standard CROSS JOIN syntax with no WHERE
clause to create a Cartesian product .
Q: What does the IN clause in the Oracle SQL statement?
A: The IN clause in the Oracle SQL statement is an equivalent of the OR
condition in the SQL statement.
Q: What do the OUTER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER
JOIN syntax in the Oracle SQL statement?
A: We use the OUTER option when we want all records that have exact match plus
those records that have no match.
Q: How can you perform the FULL OUTER JOIN syntax using the Original Oracle
syntax?
A: Although it is possible but it is very difficult to perform the full outer
join using the original Oracle syntax.
Q: When do you use the WITH … AS clause in the SQL statement?
A: If we have a query which it needs to process the same sub-query several
times, we should consider using the WITH …AS clause in our statement.
Q: How does the WITH … AS clause help your performance?
A: The query will create a temporary table to query it over and over.
Q: Write a query to list all the department names that their total paid
salaries are more than 1/3 of the total salary of the company.
A: SQL > WITH summary_totals AS
(SELECT dname,
SUM (sal) AS totals
FROM emp NATURAL JOIN dept
GROUP BY dname)
SELECT dname, totals
FROM summary_totals
WHERE totals > (SELECT SUM(totals)*1/3
FROM summary_totals)
ORDER BY totals DESC
SQL >/
Q: What are the multiple columns in the SQL statement? Where or how do you use
them?
A: We use multiple columns to match the multiple columns returned from the
sub-query.
Q: Write a SQL statement to query the name of all employees who earn the
maximum salary in their department using the multiple columns syntax.
A: SQL > SELECT deptno, ename, job, sal
FROM emp
WHERE (deptno, sal) IN
(SELECT deptno, MAX (sal)
FROM emp
GROUP BY deptno)
/
Q: What is the inline view in the Oracle SQL statement?
A: If we have a sub-query in a FROM clause in the Oracle SQL statement, is
called an inline view.
Q: Write a SQL statement to query all of the employee names, jobs, and salaries
where their salary is more than 10% of the total company paid salary.
A: SQL > SELECT ename, job, sal
FROM (SELECT ename, job, sal
FROM emp
WHERE sal > (SELECT SUM (sal) * .1
FROM emp)
ORDER BY 3)
/
Q: What does the MERGE statement in the SQL statement?
A: We use the MERGE statement to merge one table into another table.
Q: Can you update, insert, or delete any records while you are using the MERGE
statement?
A: Yes.
Q: What is a Materialized View?
A: A materialized view (MVIEW) is a replica of a target master from a single
point in time.
Q: What are the Materialized View types?
A: Read-Only Materialized Views
Updatable Materialized Views
Sub-query Materialized Views
Row-id vs. Primary Key Materialized Views