DATABASE SQL PLUS

Lab Exercises First: - Create the following tables: 1. CREATE TABLE cards ( card_id number (10) primary key NOT NULL, status number (3) NOT NULL, created_at date NOT NULL, suspended_at date DEFAULT NULL NULL, last_used at date DEFAULT NULL NULL, last_used location varchar2 (255) DEFAULT NULL 2. CREATE TABLE departments department_id number (10) primary key NOT NULL, department name varchar2 (255) NOT NULL ); 3. CREATE TABLE employees employee_id number (10) primary key NOT NULL, name varchar2 (255) NOT NULL, gender varchar2 (255) NOT NULL, date of birth date NOT NULL, position varchar2 (255) NOT NULL, department_id number (10) NOT NULL REFERENCES departments (department_id), card_id number (10) NOT NULL REFERENCES cards (card_id) CREATE TABLE access_log access_log_id number (10) primary key NOT NULL, card_id number (10) NOT NULL REFERENCES cards (card_id), timestamp date NOT NULL, status varchar2 (100) NOT NULL, where_used varchar2 (255) NOT NULL ); Then insert the following data in the previous tables: INSERT INTO cards values (57, 1, to_date(*2018-01-01 15:01:20", "yyyy-mm-da HH24:MI:SS'), NULL, to date 2018-12-21 17:00:00', 'YYYY----dd HH24:MI:SS"). Server Room) INSERT INTO cards values (58, 1, to_date / 2018-01-01 15:03:30", "yyyy-mm-dd HH24:MI:SS'), NULL, to_date('2018-12-21 17:00:00', 'YYYY----dd 124:MI:55'), Elevators'); INSERT INTO cards values (62, 1, to_date('2018-01-01 15:19:15', 'yyyy-mm-da HH24:MI:SSH), NULL, to_date('2018-12-21 17:13:00', 'YYYY----dd HH24:MI:55'), *Cafeteria'); INSERT INTO cards values (65, 1, to date("2018-01-04 08:30:13", "yyyy-mm-da HH24:MI:SS'), NULL, to date('2018-12-21 17:00:00', 'yyyy-me-dd 1824:M:SS'), Main Entrance'); INSERT INTO cards values (66, 1, to date('2018-01-04 08:30:13", "yyyy-mm-da HH24:MI:SS'), NULL, to date('2018-12-21 17:00:00', yyyy-mm-dd 1824:MI:SS). Main Entrance'); INSERT INTO cards values (69, 1, to_date('2018-05-25 15:18:39", "yyyy-sm-dd HH24:MI:SS'), NULL, NULL, "); INSERT INTO cards values (70, 1, to date('2018-05-25 15:18:39', 'yyyy-mm-dd HH24:MI:SS'), NULL, NULL, "); INSERT INTO departments (department_id, department_name) SELECT 1, 'Finance' FROM dual UNION ALL SELECT 2, 'IT' FROM dual UNION ALL SELECT 3, HR' FROM dual INSERT INTO employees (employee_id, name, gender, date_of_birth, position, department_id, card id) SELECT 1, Jasmine Barnes! 'Female', to_date(*1980-12-22", "yyyy-mm-dd), Manager', 3, 58 FROM dual UNION ALL SELECT 2, Kimberly Lewis', 'Female', to_date("1981-03-12', 'yyyy-mm-dd). *Recruiter', 3, 57 FROM dual UNION ALL SELECT 3, 'Megan Welch', 'Female', to_date("1982-11-06', 'Yyyy-mm-dd'). Graphic Designer', 2, 62 FROM dual UNION ALL SELECT 4. Coby Rojas', 'Male', to_date("1979-05-25", "yyyy-am-dd'). *Auditor', 1, 55 FROM dual UNION ALL SELECT 5, Shawn Gardner', 'Male', to_date("1986-05-25', 'yyyy-mm-dd'). Security Guard', 2, 62 FROM dual UNION ALL SELECT 6, 'John Jones', 'Male', to_date("1990-12-11', 'yyyy-am-dd). Project Manager', 1,65 FROM dual UNION ALL SELECT 7. Krystal Prince', 'Female', to_date("1987-11-01", "yyyy-mm-dd'). Social Media Specialist', 2, 57 FROM dual UNION ALL SELECT 8, Stephen Johnson', 'Male', to_date('1978-12-12', 'yyyy-mm-dd'), Operations Coordinator', 1, 58 FROM dual UNION ALL SELECT 9, Michael Davis', 'Male', to date("1985-12-14', 'yyyy-mm-dd'). System Administrator', 2, 65 FROM dual UNION ALL SELECT 10, Evan Martinez', 'Male', to date (1986-01-23", "yyyy-mm-dd'). 'System Administrator', 3, 58 FROM dual: INSERT INTO access_log (access_log_id, card_id, timestamp, status, where used) SELECT 1, 57, to date('2018-12-01 13:32:13', 'yyyy-in-dd HH24:MI:SS'). 'approved', 'Server Room' FROM dual UNION ALL SELECT 2, 58, to date('2018-12-04 22:58:35", yyyy-m-dd 1924:MI:SS'). 'approved', 'Cafeteria' E'ROM dual UNION ALL SELECT 3, 58, to date('2018-12-10 05:43:55', 'Yyyy-mm-dd H24:MI:SS'). denied', 'Finance Department' FROM dual UNION ALL SELECT 4, 57, to_date('2018-12-14 01:36:23', 'yyyy-mm-dd 1924:MI:SS), 'approved', 'Elevators' FROM dual UNION ALL SELECT 5, 57, to date('2018-12-17 23:42:41', 'yyyy-na-da HH24:MI:SS'), 'approved', 'Cafeteria' FROM dual UNION ALL SELECT 6, 57, to date('2018-12-21 17:00:00', yyyy-an-dd 1824:MI:SS), 'approved', 'Server Room' FROM dual UNION ALL SELECT 7, 58, to_date('2018-12-01 15:44:40', 'yyyy-me-dd HH24:MI:SS'), approved', 'Main Entrance FROM dual UNION ALL SELECT 8, 58, to_date('2018-12-05 21:06:26, yyyy-an-dd 1824:MI:SS), 'approved', 'Main Entrance FROM dual UNION ALL SELECT 9, 58, to_date('2018-12-10 06:24:12', 'yyyy-na-dd 1824:MI:SS'), approved', 'Main Entrance FROM dual UNION ALL SELECT 10, 58, to date('2018-12-14 07:38:58', 'yyyy-me-dd HH24:MI:SS'), 'approved', 'Main Entrance FROM dual UNION ALL SELECT 11, 58, to_date('2018-12-18 13:09:03", "yyyy-se-dd HH24:M:SS'). denied', 'Server Room' FROM dual UNION ALL SELECT 12, 58, to date('2018-12-21 17:00:00', 'yyyy-me-dd HH24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 13, 57, to date('2018-12-02 18:07:00', 'yyyy-na-dd HH24:MI:SS'), denied', 'IT Department' FROM dual UNION ALL SELECT 14, 57, to date('2018-12-05 21:51:20', 'yyyy-m-dd HH24:MI:SS'), 'approved', 'Cafeteria' FROM dual UNION ALL SELECT 15, 57, to_date('2018-12-10 22:33:03', 'yyyy-mm-dd 1824:M:SS'). denied', 'Finance Department FROM dual UNION ALL SELECT 16, 57, to date('2018-12-14 20:39:24', 'yyyy-me-dd H24:M:SS'), denied', 'IT Department' FROM dual UNION ALL SELECT 17, 57, to_date('2018-12-18 13:34:30', 'yyyy-mm-dd HA24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 18, 58, to date('2018-12-21 17:00:00', 'yyyy-m-da HH24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 19, 62, to date(2018-12-02 22:23:10', 'yyyy-se-dd HH24:MI:SS'). 'approved', 'Elevators' FROM dual UNION ALL SELECT 20, 65, to date 2018-12-06 01:36:48', 'YYYY--a-dd 9:24:MI:SS'), denied', 'Finance Department. FROM dual UNION ALL SELECT 21, 62, to_date('2018-12-11 12:52:33', 'yyyy-me-dd 1824:MI:SS'), 'approved', 'IT Department' FROM dual UNION ALL SELECT 22, 62, to_date '2018-12-14 23:21:21", "yyyy-se-dd HH24:MI:55'). 'approved', 'Cafeteria' FROM dual UNION ALL SELECT 24, 62, to_date('2018-05-16 14:25:32", "yyyy-me-dd 1824:MI:SS'), approved', 'Elevators' FROM dual UNION ALL SELECT 25, 62, to_date('2018-12-03 09:38:52", "yyyy-na-dd HH24:MI:SS"). denied', 'Finance Department' FROM dual UNION ALL SELECT 26, 65, to date('2018-12-06 08:41:22', 'yyyy-se-dd 1824:MI:SS'), 'approved', 'Main Entrance' FROM dual UNION ALL SELECT 27, 65, to_date('2018-12-11 13:33:57', 'yyyy-na-dd HH24:MI:SS'), 'approved', 'Main Entrance' FROM dual UNION ALL SELECT 28, 65, to date('2018-12-15 06:40:05', 'yyyy-mm-dd 1824:MI:SS'), denied', 'Finance Department' FROM dual Exercises Q1.Create a view name emp_view based on employee number, employee name, and department number from employees table. Change the heading of the employee name to EMPLOYEE. Then display the contents of the emp_view. Q2.select the name and the text from the data dictionary. Q3. a. Create a view name dept1 that contains the employee number,employee name, and department number for all employees in department 1. Label the view column EMPLOYEE_ID,EMPLOYEE, AND DEPARTMENT_ID. Do not allow an employee to be reassigned to another department through the view. b. Display the structure and the contents of the dept1 view. c. Attempt to reassign 'John Jones' to department 3. Q4.Create a view name CdLastUsed that dispalys LL OF THE CARDS THAT WAS LAST USED IN locations where the second character in these locations is the letter 'a'. Q5. Create a view name Copy_Access that returns all the access for the employee named 'Coby Rojas'. Q6. Create a view name Suc_Access that display how many times the 'Server Room' was accessed after working hours, knowing that the business day finishes at 5:00 PM Q7. Create a view name Card_Never_Used to display all the card information for cards that have never been used. Q8. Create a view name emp_Info to display to the employee number, employee name, card number, the date the card was created, and the name of the department in which he works. 9. Create non-unique index on the foreign key column (department_id) in the employees table. 10. Display the indexes and uniqueness that exist in the data dictionary for the Employees table