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)
/
0 comments:
Post a Comment