Hands-On 01(SQL)
Q:
Query the employee
names and their salaries from the employee table.
Q:
Do the above query and
use an “as” clause for the “salary” column aliases or column headings.
Q:
Repeat the previous
query and have “Full Name” for the ename’s column heading and “Salary”
for the “sal” column heading.
Q:
What is the result of
100 + NULL?
Q:
Query the employee names
with their commissions.
Q:
Use the (NVL) the null
value function to assign zero to any null value in the commission column for the
previous query.
Q:
Concatenate the
customers last name and first name separated by comma.
Q:
Query
the employees name sorted by ascending order.
Q:
Query the employees name
sorted by descending order.
Q:
Query the employee
information whose employee number is 7788.
Q:
Query the employees name
whose names start with the letter “M.”
Q:
Query the employees name
whose names end with the letter “R.”
Q:
Query the employees name
whose salaries between 2000 and 3000 dollars.
Q:
Query the employees name and their department name using the
“decode” function. Check if
the "department no" is 10 then print "accounting",
else if the "department no" is 20 then print "research," or
if the "department no" is 30 then print "sales".
Anything else print "others."
Hands-On 02(SQL)
Q:
What is an ambiguous
column?
Q:
How can you resolve an ambiguous column problem?
Q:
What is a Cartesian Product?
Q:
How can you avoid a Cartesian product?
Q:
What is an inner join or equijoin?
Q:
What is an outer join?
Q:
What is a selfjoin?
Q:
Query all the employee name and their department including all the
department with no employees.
Q:
Query the managers name with their employees sorted by the managers name.
Q:
Query the department number and their total, average, min, and max
salaries for each department.
Q:
Query the department no and their total salaries that have more than 5
employees working in their department.
Q:
Query the employees name that work for the Research or Sales department
(the department number 20 or 30).
Q:
Query the employees name that work in the "accounting"
department. Assuming the department
number is unknown.
Q:
Query the employees name and use the runtime variable to substitute the
department number? Then run it for following department no 10, 20, and 30.
Q:
query the customers name which have more than four orders.
Hands-On 03(SQL)
Q:
Create an employee table
that contains five columns:
(employee
id, last name, first name, phone number and department number)
The
last name and first name should be not null.
Make
a check constraint to check the department number is between 9 and 100.
Make
a primary constraint on the employee ID column.
Make
a foreign key on the department number column.
Use
the "delete cascade" to delete all records if parent gets deleted.
Use
the "phone number" as a unique key.
Q:
Create a composite index
on the employee table that contains two index columns (last name and first
name).
Q:
Query the tables that
you as a user own.
Q:
Query the index tables
that belongs to the employee table and owns by the iself user.
Q:
Change the size of the
"column_name" to 30 characters.
Q:
Query the indexes column
of the employee table.
Q:
Insert a record into the
"employee" table using column names.
Q:
Insert a record using
the column position format.
Q:
How do you save the
inserted transaction?
Q:
Change the "lastname"
column from “Smith” to “Judd” where the "employee id" is 100.
Q:
Delete all the employee
records from the "employee" table using the delete command and the
truncate command.
Q:
What is the difference
between the delete statement and the truncate statement?
Hands-On 04(SQL)
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.
Q:
Add a salary column to
the employee table.
Q:
Modify the "ename" column size from varchar10 to varchar15.
Q:
Rename the "employee" to "iself_employee" table.
Q:
Create a view to display the employees name of the “Accounting”
department only.
Q:
Why do you use the view?
Q:
How do you compile the view?
Q:
How do you delete the view?
Q:
Create an index on the employee table on the ename column only and name
it employee_indx.
Q:
Reorganize the “employee_indx” index table.
Q:
Drop the employee_ename index table.
Q:
Create a user with username “newuser” and password "newpass."
Its default tablespace should be the "iself_data" tablespace.
Q:
Grant the resource and connect roles to newuser.
Q:
Change the newuser password to "mypass".
Q:
Can the new user access to any other user tables?
Q:
Create
a public synonym
Q:
What is the difference
between public and private synonym?
Q:
Create and drop a private synonym.
Q:
Revoke an object privilege on a table from a user.
Hands-On
05(PL/SQL)
Q:
How do you use the command line editor which it is native to SQL*PLUS?
- How do you use the following
command?
- "L" command
- "I" command
- “Del” command
- "C" command
- SAVE command
- GET command
Hands-On
06(PL/SQL)
Q:
How do you use the notepad editor?