Tema nr.
10
Observaţie!
Scrieţi rezolvarea direct în acest document!
1. Using the column information for the animals table below, name constraints where applicable
at the table level, otherwise name them at the column level. Define the primary key
(animal_id). The license_tag_number must be unique. The admit_date and vaccination_date
columns cannot contain null values.
animal_id NUMBER(6)
name VARCHAR2(25)
license_tag_number NUMBER(10)
admit_date DATE
adoption_id NUMBER(5),
vaccination_date DATE
ALTER TABLE d_animals
ADD PRIMARY KEY (animal_id)
ALTER TABLE d_animals
MODIFY (admit_date DATE CONSTRAINT just_not_null NOT NULL);
ALTER TABLE d_animals
MODIFY (vaccination_date DATE CONSTRAINT just_not_null2 NOT NULL);
2. Create the animals table. Write the syntax you will use to create the table.
CREATE TABLE d_animals
(
animal_id NUMBER(6),
name VARCHAR2(25),
license_tag_number NUMBER(10),
admit_date DATE,
adoption_id NUMBER(5),
vaccination_date DATE
);
3. Enter one row into the table. Execute a SELECT * statement to verify your input. Refer to
the graphic below for input.
ANIMAL_ID NAME LICENSE_TAG ADMIT_DATE ADOPTION_ID VACCINATION_D
_NUMBER ATE
101 Spot 35540 10-OCT-04 205 12-OCT_04
INSERT INTO d_animals
(animal_id,name,license_tag_number,admit_date,adoption_id,vaccination_date)
VALUES (101, 'Spot', 35540,'10-OCT-04',205, '12-OCT-04');
4. Write the syntax to create a foreign key (adoption_id) in the animals table that has a
corresponding primary- key reference in the adoptions table. Show both the column-level and
table-level syntax. Note that because you have not actually created an adoptions table, no
adoption_id primary key exists, so the foreign key cannot be added to the animals table.
CREATE TABLE d_adoption AS SELECT * FROM d_animals
ALTER TABLE d_adoption
ADD PRIMARY KEY (adoption_id)
ALTER TABLE d_animals
ADD CONSTRAINT fk_d_adoption
FOREIGN KEY (adoption_id)
REFERENCES d_adoption(adoption_id);
5. Since the tables are copies of the original tables, the integrity rules are not passed onto the
new tables; only the column datatype definitions remain. You will need to add a PRIMARY
KEY constraint to the copy_d_clients table. Name the primary key copy_d_clients_pk . What
is the syntax you used to create the PRIMARY KEY constraint to the copy_d_clients.table?
CREATE TABLE copy_d_clients AS SELECT * FROM d_clients
ALTER TABLE copy_d_clients
ADD CONSTRAINT copy_d_clients_pk
PRIMARY KEY (client_number)
6. Create a FOREIGN KEY constraint in the copy_d_events table. Name the foreign key
copy_d_events_fk. This key references the copy_d_clients table client_number column.
What is the syntax you used to create the FOREIGN KEY constraint in the copy_d_events
table?
ALTER TABLE copy_d_clients
ADD PRIMARY KEY (client_number)
ALTER TABLE copy_d_events
ADD CONSTRAINT copy_d_events_fk
FOREIGN KEY (client_number)
REFERENCES copy_d_clients(client_number);
7. Create a simple view called view_d_songs that contains the ID, title and artist from the DJ on
Demand table for each “New Age” type code. In the subquery, use the alias “Song Title” for
the title column.
CREATE VIEW view_d_songs AS
SELECT id, title, artist
FROM d_songs;
SELECT id, artist, title AS "Song Title"
FROM view_d_songs;
8. SELECT * FROM view_d_songs. What was returned?
9. REPLACE view_d_songs. Add type_code to the column list. Use aliases for all columns.
CREATE OR REPLACE VIEW view_d_songs AS
SELECT id, title, artist, type_code
FROM d_songs;
SELECT id AS "PRIMERO ID", artist AS "EL ARTISTO", title AS "EL CANCION", type_code
AS "LA TIPOLOJIA DEL CODE"
FROM view_d_songs;
10. It is company policy that only upper-level management be allowed access to individual
employee salaries. The department managers, however, need to know the minimum,
maximum, and average salaries, grouped by department. Use the Oracle database to prepare a
view that displays the needed information for department managers.
SELECT MAX(salary), MIN(salary), AVG(salary)
FROM employees
GROUP BY department_id;
11. Create a query that selects the last name and salary from the Oracle database. Rank the
salaries from highest to lowest for the top three employees (tip: you can use an inline view
and the ROWNUM pseudocolumn).
SELECT last_name, salary
FROM employees WHERE ROWNUM <= 3;
12. Create a new table called my_departments and add all columns and all rows to it using a
subquery from the Oracle departments table. Do a SELECT * from my_departments to
confirm that you have all the columns and rows.
CREATE TABLE my_departments AS SELECT * FROM departments
13. To view any constraints that may affect the my_departments table, DESCRIBE
my_departments to check if any constraints were carried over from the departments table. If
there are constraints on my_departments, use an ALTER TABLE command to DISABLE all
constraints on my_departments.
ALTER TABLE my_departments
DROP CONSTRAINT SYS_C0012614602;
14. Create a view called view_my_departments that includes: department_id and
department_name.
CREATE VIEW view_my_departments AS
SELECT department_id, department_name
FROM my_departments;
15. Add the following data to the my_departments table using view_my_departments.
department_id department_name
105 Advertising
120 Custodial
130 Planning
INSERT INTO view_my_departments(department_name, department_id)
VALUES ('Advertising', 105);
INSERT INTO view_my_departments(department_name, department_id)
VALUES ('Custodial', 120);
INSERT INTO view_my_departments(department_name, department_id)
VALUES ('Planning', 130);
16. Create or enable the department_id column as the primary key.
ALTER TABLE my_departments
ADD PRIMARY KEY (department_id);
17. Enter a new department named Human Resources into the my_departments table using
view_my_departments. Do not add a new department ID.
INSERT INTO my_departments(department_id, department_name, manager_id, location_id)
VALUES (200, 'Human Resource', 207, 1700);
18. Add the Human Resources department, department ID 220, to my_departments using
view_my_departments.
INSERT INTO view_my_departments(department_name, department_id)
VALUES ('Human Resources', 220);
19. Verify that the new additions to my_departments were added using view_my_departments.
SELECT * FROM
my_departments
20. Create a table called “students.” You can decide which columns belong in that taable and
what datatypes these columns require. (The students may create a table with different
columns; however, the important piece that must be there is the student_id column with a
numeric datatype. This column length must allow the sequence to fit, e.g. a column length of
4 with a sequence that starts with 1 and goes to 10000000 will not work after student #9999
is entered.)
CREATE TABLE students
(
student_id NUMBER(4),
first_name VARCHAR2(25),
last_name VARCHAR2(25)
);
21. Create a sequence called student_id_seq so that you can assign unique student_id numbers
for all students that you add to your table.
CREATE SEQUENCE student_id_seq
MINVALUE 1
MAXVALUE 10000
START WITH 1
INCREMENT BY 1
NOCACHE;
22. Now write the code to add students to your STUDENTS table, using your sequence
“database object.”
INSERT INTO STUDENTS (student_id)
VALUES(student_id_seq.nextval);
