51: Modify the
"ename" column size from varchar10 to varchar15.
A: SQL> ALTER TABLE employee
MODIFY (ename VARCHAR2(15));
52: Rename the
"employee" table to the "iself_employee" table.
A: SQL> RENAME employee TO iself_employee;
53: 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;
54: 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.
55: How do you
compile the view?
A: SQL> ALTER VIEW employee_name COMPILE;
56: How do you
delete the view?
A: SQL> DROP VIEW employee_name;
57: 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);
58: Reorganize
the “employee_indx” index table.
A: SQL> ALTER INDEX employee_ indx
REBUILD;
59: Drop the
employee_ename index table.
A: SQL> DROP INDEX employee_indx;
60: 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;
61: Grant the
resource and connect roles to newuser.
A: SQL> GRANT resource, connect TO newuser;
62: Change the
newuser password to "mypass".
A: SQL> ALTER USER newuser IDENTIFIED BY mypass;
63: Can the
above new user access to any other user tables?
A: No.
64: 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.
65: What is the
syntax to create a public synonym?
A: SQL> CREATE PUBLIC SYNONYM employees FOR
iself.iself_employee;
66: 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.
67: Create and
drop a private synonym.
A: SQL> CREATE SYNONYM emp_table FOR
iself.iself_employee;
To drop:
SQL> DROP SYNONYM emp_table;
68: Revoke an
object privilege on a table from a user.
A: SQL> REVOKE UPDATE, SELECT ON employee FROM
newuser;
69: What does
the LIST or ‘L’ command line editor?
A: It lists the
current SQL statement that was typed in the Oracle buffer.
70: 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 *.
71: What does
the DEL or ‘D’ command line editor?
A: It deletes
the current active line in the Oracle Buffer.
72: 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.
73: How do you
save the SQL script in the Oracle Buffer?
A: SQL> save
c:\myscript.sql
74: How do you
open the SQL Script into the Oracle Buffer?
A: SQL> get
c:\myscript.sql
75: How do you
use the notepad editor?
A: Just type:
the ed command to open the default editor.
76: What is
afiedt.buf?
A: The
"afiedt.buf" file is a place that into which SQL*PLUS
stores the most recently executed SQL statement.
77: How do you
change your text editor in the SQLPLUS tool?
A: Issue the
define_editor='your editor' statement from the SQL*PLUS
prompt.
78: What does
the ed command in the SQLPLUS tool?
A: We use the
"ed" command, to open your default word editor.
79: Can you
have multiple SQL statements in the afiedt.buf file?
A: No. You can
only use one SQL statement at a time.
80: 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.
81: How do you
execute or run a SQL script?
A: SQL> run
c:\myscript.sql or start c:\myscript
82: What is the
SQL ANSI statement?
A: It is
some standard roles that provided by American
National Standards Institute.
83: 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.
84: Is the SET
command a SQL statement?
A: No.
85: How do you
change your workstation’s page size or line size?
A: SQL> SET LINESIZE 100 PAGESIZE 55
86: 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'
/
FROM emp JOIN dept
ON emp.deptno = dept.deptno
AND dname <> 'SALES'
/
87: 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.
88: 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.
89: What is the
advantage of the NATURAL JOIN syntax?
A: It is less
typing.
90: 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.
91: 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.
92: 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.
93: 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.
94: 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.
95: How does
the WITH … AS clause help your performance?
A: The query
will create a temporary table to query it over and over.
96: 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,
(SELECT dname,
SUM(sal) AS totals
FROM emp NATURAL JOIN dept
FROM emp NATURAL JOIN dept
GROUP BY dname)
SELECT dname, totals
SELECT dname, totals
FROM summary_totals
WHERE totals > (SELECT SUM(totals)*1/3
WHERE totals > (SELECT SUM(totals)*1/3
FROM summary_totals)
ORDER BY totals DESC
ORDER BY totals DESC
SQL>/
97: 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.
98: 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)
/
FROM emp
WHERE (deptno, sal) IN
(SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno)
/
99: 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.
100: 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)FROM (SELECT ename, job, sal
FROM emp
WHERE sal > (SELECT SUM(sal) * .1
ORDER BY 3)
/
No comments:
Post a Comment