Schema used in the exercises below.
| *********************************************************** |
Database Workshop for LHC online/offline developers |
SQL Exercises, Tuesday 25 January 2005 |
| *********************************************************** |
- Please connect to the Training database using SQL*Plus that can be found by:
Start->All Programs->Oracle Tools->Oracle Basics 9i->SQL Plus
- Use the following parameters:
User Name: oradbNN (where NN is the number of your computer)
Password: oracle
Host String: trainingdb
- Run the following commands so you can see better the queries output:
SET LINE 200
SET PAGESIZE 200
- You can open a Notepad to save the commands for later use and copy/paste to SQL*Plus.
1.
Create a table PROJECT with the following columns:
PRONO NUMBER(4) -- project's number
PRONAME VARCHAR2(10) -- project's name
STARTDATE DATE -- date of project's start
DESCRIPTION VARCHAR2(50) -- project's description
Don't allow the following columns to have NULL values: STARTDATE, PRONAME.
Create a table TEAM according to the description:
TMNO NUMBER(4) -- team's number
TMNAME VARCHAR2(10) NOT NULL -- team's name
In the creation statement define primary key TEAM_PK on the TMNO column.
2.
Describe the tables' structure.
3.
Insert two records for projects: - project called 'MATRIX', beginning at the current date and with number 100; - project called 'ABC', beginning a day after the current date, with number 200 and description 'Temporary'. Select the contents of the table.
4.
Update the description of the project 100 to 'Experimental project'. Select the contents of the table.
5.
Remove the 'Temporary' project (assuming that projects are identified by their numbers) and select the contents of the PROJECT table.
6.
To the PROJECT table add a column TMNO NUMBER(4) for a team which works on the project. See the structure of PROJECT table and its data.
7.
Now add the primary key PRO_PK on the PRONO column. Again describe the PROJECT table.
8.
Define the TMNO column of PROJECT table as a foreign key named PRO_TM_FK to the TEAM table.
9.
Add a new column PROCODE VARCHAR2(3) NOT NULL to the PROJECT table and set 'XXX' as the default value for it. Check the contents of PROJECT table. See all constraints defined for the PROJECT table in the USER_CONSTRAINTS table.
10.
Remove all the created tables.
Schema used in the exercises below.
11. ( || and NVL() operators, columns' pseudonyms )
* Select employees' names concatenated with their managers' numbers according to the following format:
"<employee's name> is supervised by <supervisor's number>"; for employees without supervisors
return 'NO SUPERVISOR' in the place of the supervisor's number.
Give your own title to the created column, e.g. "Employees and supervisors".
12. ( WHERE clause )
* Show the names and salaries of the employees who earn less then 3000.
13. ( BETWEEN, IN and count operator )
* Show the number of employees hired between 01.01.1980 and 01.01.1982 and working in the department 10 or 20;
use BETWEEN and IN clauses.
* Show name, number, job and salary of MANAGERs and ANALYSTs who earn at least 2000 but no more than 3000;
use BETWEEN and IN clauses.
14. ( DISTINCT, ORDER BY, ASC/DESC operators )
* Select the different jobs of employees and show them in ascending order. * Select employees' names, jobs and hiredates and order them first by job (ascending), then by hiredates (descending).
15. ( LIKE, NOT operator )
* Select names and the depratments' numbers of employees starting with 'M' or with 'C' and who aren't from the department 30 nor 20.
16. ( transactions )
* See epmloyees, their salaries, jobs and departments' numbers from two different sessions.
* In one of those sessions, update salaries of employees working in the department 10
rising them by 100 for everybody in this department except 'PRESIDENT'.
* See epmloyees, their salaries, jobs and departments' numbers from the same session where you made update
and then from another one.
* Commit and again see the data from both sessions.
* Undo (prermanently) the changes in the EMP table via updating back the salaries.
17. ( EQUIJOIN and ALIASING, functions )
* Select names of employees and their departments' numbers and names, order by departments' numbers;
use aliases while joining tables and select the departments' names in lowercase and call this column "Department name".
18. ( EQUIJOIN, IS (NOT) NULL, GROUP BY clause, aggregation functions )
* Select the number of employees being supervised by others (having managers) and group them by departments;
follow the format: "Number of employees being supervised in department <department's number> is <number of employees>".
* Select departements' identificators, names, the number of employees in each of them
and the minimum and maximum salary in each department; take into account only the departments having employees and
follow the format: "Department <dept. number> named <dept. name> counts <number> employees with salaries: minimum
<minimum salary>, maximum <maximum salary>".
19. ( GROUP BY, HAVING clause, aggregation functions )
* Select departments' names and sum of salaries of employees in each department,
choose only departments which have at least 4 employees.
* Select departments' names, locations and their number of clerks ( job named CLERK );
select only departments having at least two clerks.
20. ( OUTERJOIN )
* Select names of departments and employees working there; construct the query to take into account all departments
regardless their employees' number; sort the data by departments' names.
21. ( Functions )
* Select the current date and time in the format "2 digits for a day/2 digits for a month/4 digits for a year hours:minutes:seconds"
and the name of the day of week; give your own name to these two columns.
* Select the current date truncated to the year and the next Monday after the 1st February 2005.
* Select departments' numbers and their numbers converted to letters according to the following schema:
10 to 'A', 20 to 'B', 30 to 'C', others to 'D'. Use DECODE() function.
22. ( SELFJOINS, OUTERJOINS )
* Select the names of employees and their managers; for those having no managers select 'NOT ASSIGNED';
give the "Employee" and "Manager" names for appropriate columns.
23. ( Subqueries, hierachical queries )
* Show employees (their names, departments' numbers and jobs) having the same kind of job as JAMES
(assuming there is only one employee named JAMES).
* Show employees (their names, departments and hiredates) who work in the same department as 'KING'
and started in the same year (assuming there is only one employee named KING).
* Show names, jobs and departments' numbers of employees who supervised (are bosses of) at least 2 other employees.
* Show hierarchy of employees starting from 'PRESIDENT'; show the numbers, names of employees and numbers of
their supervisors and level in the hierarchy (pseudo-column LEVEL).
24. ( Correlated subqueries )
* For each employee show his/her name, department's number, salary and the average salary of his/her department;
try to use a correlated subquery.
25. ( Inline views )
* For each employee show his/her name, department's number, salary and the average salary of his/her department;
use inline view instead a correlated subquery is it was in the previous exercise.
* Show the names, departments' numbers and salaries of employees whose salaries are among the 2 highest;
use inline view.
26. ( Views )
* Create a view named DEPT_V with such information/columns' names concerning each department: name, location,
the number of employees (emp_count), the minimum, avarage and maximum salary (min_sal, avg_sal, max_sal appropriately).
Make sure the view show all departments, event those not having any employee.
* Describe this view and show its contents.
* For each employee show his/her name, department's name, salary and the average salary of his/her department
using the created view.
* Show the view in the USER_VIEWS (Select at least its name and definition. Use DESC to find out the names
of the columns to select.)
* Drop the DEPT_V view.
27. ( Materialized views )
* Create a view named DEPT_MV with such information/columns' names concerning each department: name, location,
the number of employees (emp_count), the minimum, avarage and maximum salary (min_sal, avg_sal, max_sal appropriately);
specify this view to be populated with data immediately, that may be used for query rewrite and to be refreshed
every hour. Make sure the view show all departments, event those not having any employee.
* Describe this view and show its contents.
* For each employee show his/her name, department number, salary and the average salary of his/her department;
use the created view.
* Check if the view exists in the USER_VIEWS and USER_MVIEWS (select at least its name and definition).
* Drop the DEPT_MV view.
28. ( Granting/revoking privilages and synonyms )
Please make pairs of users to do this exercices.
* User A:
create a synonym for your EMP table, name it EMP_<user_number>, e.g. if you are oradb40, choose the name EMP_40.
* User B:
try to select data from the EMP table of user A.
* User A:
grant select privilage to the user B, use the synonym you have created.
* User B:
try to update the hiredate of employee 'JAMES', in the EMP table of A user, to the current date.
* User B:
try to select data from the EMP table of A user.
* User A:
grant update privilege on the salary (SAL) column to the user B.
* User B:
again try to update the hiredate of the employee 'JAMES', in the EMP table of A user, to the current date;
try to update the salary of the employee 'JAMES' in the EMP table of user A by adding 1000;
rollback the changes.
* User A:
revoke the privilages granted to user B.
* User B:
check if you can still see data of A user.
* User A:
show all user synonyms.
* User A:
drop the synonym created.
29. ( Partitioning and indexes, sequences )
* Create a table PROJECT_R with the following columns:
PRONO NUMBER(4) -- project's number
PRONAME VARCHAR2(10) -- project's name
STARTDATE DATE -- date of project's start
DESCRIPTION VARCHAR2(50) -- project's description
PHASE VARCHAR2(2) -- status of a project
Don't allow the following columns to have NULL values: STARTDATE, PRONAME, PHASE.
Define a check constraint (PHASE_CK) for the PHASE column to allow only values: DS (design), DV (development), TS (tests).
In the creation statement define also the primary key PRO_PK on the PRONO column.
Define the PROJECT_R table to be partitioned by startdate
(set 5 partitions dividing at every one year as follows:
the first partition should have dates less than 01-01-2001,
the second partition should have dates less than 01-01-2002, ...
the last one shouldn't have the upper limit).
* Show the table's info from the user_objects (show object_name, subobject_name, object_type).
* Create a local partitioned index of the partitioned PROJECT_R table (on the column startdate).
Don't specify attributes of each index partition - let the default to be applied by Oracle server.
The index should be named projectr_startdate_indx_loc and created in the training_indx tablespace.
* Show the index's info from the user_objects (show object_name, subobject_name, object_type).
* Create a global index on the STARTDATE column of the PROJECT_R table (remember that the same column cannot
be indexed by more than one index).
The index should be named projectr_startdate_indx_gl and created in the training_indx tablespace.
* Show the index's info from the user_objects (show object_name, subobject_name, object_type).
* Create a bitmap index on the PHASE column of the PROJECT_R table.
The index should be named projectr_phase_indx_bitmap and created in the training_indx tablespace.
* Show the index's info from the user_objects (show object_name, subobject_name, object_type).
* In user_indexes check properties of all indexes with names starting from 'PROJECTR' (check indexes' names and types).
* Create a sequence named PROJECTR_SEQ that will serve as a source of projects' numbers. This sequence should
be incremented by 1, starting with 1, without maximum value defined and non-cycled.
* Insert one row to the PROJECT_R table using this sequence.
* Remove all the created objects (PROJECT_R, projectr_startdate_indx_loc, projectr_startdate_indx_gl,
projectr_phase_indx_bitmap, PROJECTR_SEQ).
30. ( Multi-dimensional aggregation )
* Show the number of employees in each department holding different posts/positions, together with the total
number of all employees in each department and the total number of employees at all.
* Show the number of employees in each department holding different posts/positions, together with the total
number of all employees in each department and the number of employees holding different positions (regardless
of their departments) and total number of employees at all.
* Repeat the last select but using UNION operator.
tables - PROJECT, TEAM, PRJECT_R
views - DEPT_V, DEPT_MV
synonym - EMP_<user_number>
seqence - PROJECTR_SEQ
and revoked all the privilages granted on your EMP table to another user during the 28 exercise.