Question

im currently working on an sql database homework for a fundamentals class but im not too sure if my results are alright and would like to compare results, the assignment is as follows:

O4pk,fk evaluate de data models, the specifications and table contents: equipment_tb Assigned_Equipment_tb workers_tb pk serial_number int pk,fk serial number int pk worker_id int, Surrogate brand char(10) worker id int Lastname char(100) model char(15) assigned_date date Firstname char(50) processor char(15) department int processor_velocity number (3,2) email char(200) memory char(7) hard disk char(6) table specifications Questions based on Oracle Apex equipment_tb 1. write a script in sql to drop, create, and populate the column Name data type null or not comments tables for this case in an oracle database: serial number int not pk brand char(10) not valid: "Dell", "HP", "Lenovo" model char(15) not 2. write an instruction to add a column to save an processor char(15) null employee phone number taking into consideration that processor_velocity number (3,2) not between: 3.0 and 3.5 a table is already created and has values in it. memory char(7) not hard disk char(6) not 3. write an instruction to create a view called workers_tb worker_equipment_v. that shows the equipment column Name data type null or not comments values and the worker values has assigned that worker_id int, Surrogate not pk equipment. Lastname char(100) not 4. write instructions to create a function named Firstname char(50) not Brand_Model_fun in which you concatenate the brand department int Null and the model to present in the following format char(200) not (Brand:Model). assigned_equipment_tb 5. write the instructions to create a view called column Name data type null or not comments computer_brand_model_v, that shows the serial number int not pk, fk serial_number and the result of the function: worker id int not pk, fk Brand_Model_fun assigned_date date not Table Values equipment_tb values processor serialnumber brand model processor velocity memory hard disk 00000001 Dell Optiplex 7040 intel i7-6700 3.6 32.0 gb 3.0 TB 00000002 Optiplex 7040 intel i7-6700 3.6 3.0 TB assigned_equipment_tb values seri imber worker_id assigned_date 00000001 15-Jan-20 00000002 2 21-Jan-20 worker_tb values worker id Lastname firstname department email 1 Johnson william's Robb [email protected] 2 Rivera Lopez Joel 2 [email protected] email Dell 1

Answer 1

SOLUTION

The solution has been executed in Oracle Live SQL Schema.

QUESTION 1:

--DROP TABLE --

DROP TABLE ASSIGNED_EQUIPMENT_TB;

DROP TABLE WORKERS_TB;

DROP TABLE EQUIPMENT_TB;

--CREATE TABLE --

CREATE TABLE EQUIPMENT_TB(SERIAL_NUMBER INT PRIMARY KEY, BRAND CHAR(10) NOT NULL, MODEL CHAR(15) NOT NULL, PROCESSOR CHAR(15),

PROCESSOR_VELOCITY NUMBER(3,2) NOT NULL, MEMORY CHAR(7) NOT NULL, HARD_DISK CHAR(6) NOT NULL);

CREATE TABLE WORKERS_TB(WORKER_ID INT PRIMARY KEY, LASTNAME CHAR(100) NOT NULL, FIRSTNAME CHAR(50) NOT NULL, DEPARTMENT INT, EMAIL CHAR(200) NOT NULL);

CREATE TABLE ASSIGNED_EQIUPMENT_TB(SERIAL_NUMBER INT, WORKER_ID INT, ASSIGNED_DATE DATE,

CONSTRAINT PK_SERIAL_WORKER PRIMARY KEY(SERIAL_NUMBER, WORKER_ID),

CONSTRAINT FK_SERIAL_NUMBER FOREIGN KEY(SERIAL_NUMBER) REFERENCES EQUIPMENT_TB(SERIAL_NUMBER),

CONSTRAINT FK_WORKER_ID FOREIGN KEY(WORKER_ID) REFERENCES WORKERS_TB(WORKER_ID));

-- INSERT DATA --

INSERT INTO EQUIPMENT_TB VALUES(00000001, 'DELL', 'OPTIPLEX 7040', 'INTEL I7-6700', 3.6, '32.0 GB', '3.0 TB');

INSERT INTO EQUIPMENT_TB VALUES(00000002, 'DELL', 'OPTIPLEX 7040', 'INTEL I7-6700', 3.6, ' ', '3.0 TB');

INSERT INTO WORKERS_TB VALUES(1, 'JOHNSON WILLIAMS', 'ROBB', 1, '[email protected]');

INSERT INTO WORKERS_TB VALUES(2, 'RIVERA LOPEZ', 'JOEL', 2, '[email protected]');

INSERT INTO ASSIGNED_EQIUPMENT_TB VALUES(00000001, 1, TO_DATE('15-JAN-20'));

INSERT INTO ASSIGNED_EQIUPMENT_TB VALUES(00000002, 2, TO_DATE('21-JAN-20'));

-- DISPLAY DATA --

SELECT * FROM EQUIPMENT_TB;

SELECT * FROM WORKERS_TB;

SELECT * FROM ASSIGNED_EQIUPMENT_TB;

QUESTION 2:

-- ADD PHONE NUMBER COLUMN TO WORKERS_TB TABLE--

ALTER TABLE WORKERS_TB ADD PHONE_NUMBER CHAR(12);

--CHECK TABLE STRUCTURE --

SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='WORKERS_TB';

QUESTION 3:

--CREATE VIEW WORKER_EQUIPMENT_V --

CREATE VIEW WORKER_EQUIPMENT_V AS SELECT A.SERIAL_NUMBER, E.BRAND, E.MODEL, E.PROCESSOR, E.MEMORY, TRIM(W.LASTNAME)||', '|| TRIM(W.FIRSTNAME) AS "ASSIGNED TO", DEPARTMENT

FROM ASSIGNED_EQIUPMENT_TB A, WORKERS_TB W, EQUIPMENT_TB E

WHERE (A.WORKER_ID=W.WORKER_ID) AND (A.SERIAL_NUMBER = E.SERIAL_NUMBER);

--DISPLAY VIEW DATA --

SELECT * FROM WORKER_EQUIPMENT_V

QUESTION 4:

--CREATE FUNCTION BRAND_MODEL_FN--

CREATE OR REPLACE FUNCTION BRAND_MODEL_FN(BRAND CHAR, MODEL CHAR)

RETURN VARCHAR2 IS

    BRAND_MODEL VARCHAR2(50);

BEGIN

    BRAND_MODEL := CONCAT(CONCAT(TRIM(BRAND), ':'), MODEL);

    RETURN BRAND_MODEL;

END;

/

--TEST FUNCTION BRAND_MODEL_FN --

SELECT BRAND_MODEL_FN(BRAND,MODEL) FROM EQUIPMENT_TB;

QUESTION 5:

--CREATE VIEW COMPANY_BRAND_MODEL_V --

CREATE VIEW COMPANY_BRAND_MODEL_V AS SELECT SERIAL_NUMBER, BRAND_MODEL_FN(BRAND,MODEL) AS "BRAND MODEL" FROM EQUIPMENT_TB;

--DISPLAY VIEW DATA --

SELECT * FROM COMPANY_BRAND_MODEL_V;

OUTPUT

SQL Worksheet Q Clear Find 1 --DROP TABLE DROP TABLE ASSIGNED_EQUIPMENT_TB; DROP TABLE WORKERS_TB; DROP TABLE EQUIPMENT_TB; --CREATE TABLE CREATE TABLE EQUIPMENT_TB (SERIAL_NUMBER INT PRIMARY KEY, BRAND CHAR(10) NOT NULL, MODEL CHAR (15) NOT NULL, PROCESSOR CHAR(15), PROCESSOR_VELOCITY NUMBER (3,2) NOT NULL, MEMORY CHAR (7) NOT NULL, HARD_DISK CHAR(6) NOT NULL); CREATE TABLE WORKERS_TB (WORKER_ID INT PRIMARY KEY, LASTNAME CHAR(100) NOT NULL, FIRSTNAME CHAR(50) NOT NULL, DEPARTMENT INT, EMAIL CHAR (200) NOT NULL); CREATE TABLE ASSIGNED_EQIUPMENT_TB (SERIAL_NUMBER INT, WORKER_ID INT, ASSIGNED_DATE DATE, CONSTRAINT PK_SERIAL_WORKER PRIMARY KEY(SERIAL_NUMBER, WORKER_ID), CONSTRAINT FK_SERIAL_NUMBER FOREIGN KEY(SERIAL_NUMBER) REFERENCES EQUIPMENT_TB (SERIAL_NUMBER), CONSTRAINT FK_WORKER_ID FOREIGN KEY(WORKER_ID) REFERENCES WORKERS_TB (WORKER_ID)); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 -- INSERT DATA -- INSERT INTO EQUIPMENT_TB VALUES (00000001, DELL', 'OPTIPLEX 7040', INSERT INTO EQUIPMENT_TB VALUES (00000002, 'DELL', 'OPTIPLEX 7040', 'INTEL I7-6700', 3.6, '32.0 GB', '3.0 TB'); 'INTEL I7-6700', 3.6, '', '3.0 TB'); INSERT INTO WORKERS_TB VALUES (1, 'JOHNSON WILLIAMS', 'ROBB', 1, '[email protected]'); INSERT INTO WORKERSTB VALUES (2, 'RIVERA LOPEZ', 'JOEL', 2, '[email protected]'); INSERT INTO ASSIGNED_EQIUPMENT_TB VALUES (00000001, 1, TO_DATE('15-JAN-20')); INSERT INTO ASSIGNED_EQIUPMENT_TB VALUES (00000002, 2, TO_DATE('21-JAN-20')); -- DISPLAY DATA -- SELECT * FROM EQUIPMENT_TB; SELECT * FROM WORKERS_TB; SELECT * FROM ASSIGNED_EQIUPMENT_TB; Resize Code Editor SERIAL_NUMBER BRAND MODEL PROCESSOR PROCESSOR_VELOCITY MEMORY HARD DISK 1 DELL OPTIPLEX 7040 INTEL I7-6700 3.6 32.0 GB 3.0 TB DELL OPTIPLEX 7040 INTEL I7-6700 3.6 3.0 TB

SQL Worksheet Clear BeFind Actions A Save Run 24 25 26 27 28 29 30 INSERT INTO ASSIGNED_EQIUPMENT_TB VALUES ( 00000001, 1, TO_DATE('15-JAN-20')); INSERT INTO ASSIGNED_EQIUPMENT_TB VALUES (00000002, 2, TO_DATE('21-JAN-20')); -- DISPLAY DATA --| SELECT * FROM EQUIPMENT_TB; SELECT * FROM WORKERS_TB; SELECT * FROM ASSIGNED_EQIUPMENT_TB; SERIAL_NUMBER BRAND MODEL PROCESSOR PROCESSOR_VELOCITY MEMORY HARD DISK 1 DELL OPTIPLEX 7040 INTEL I7-6700 3.6 32.0 GB 3.0 TB 2 DELL OPTIPLEX 7040 INTEL I7-6700 3.6 3.0 TB Download CSV 2 rows selected. WORKER_ID LASTNAME FIRSTNAME DEPARTMENT EMAIL PHONE_NUMBER 1 JOHNSON WILLIAMS ROBB 1 W. [email protected] 2 RIVERA LOPEZ JOEL 2 [email protected] Download CSV 2 rows selected. SERIAL_NUMBER WORKER_ID ASSIGNED DATE 2 2 21-JAN-20 1 1 15-JAN-20 Download CSV 2 rows selected.

SQL Worksheet Clear Find Actions A Save 21 -- ADD PHONE NUMBER COLUMN TO WORKERS TB TABLE-- ALTER TABLE WORKERS_TB ADD PHONE_NUMBER CHAR(12); --CHECK TABLE STRUCTURE -- SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='WORKERS_TB'; --CREATE VIEW WORKER_EQUIPMENT_V -- CREATE VIEW WORKER_EQUIPMENT_V AS SELECT A.SERIAL_NUMBER, E. BRAND, E.MODEL, E.PROCESSOR, E.MEMORY, TRIM(W.LASTNAME) ||', '|| TRIM(W.FIRSTNAME) AS "ASSIGNED TO", DEPARTMENT FROM ASSIGNED_EQIUPMENT_TB A, WORKERS_TB W, EQUIPMENT_TB E WHERE (A. WORKER_ID=W.WORKER_ID) AND (A.SERIAL_NUMBER = E.SERIAL_NUMBER); --DISPLAY VIEW DATA -- SELECT * FROM WORKER_EQUIPMENT_V; 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 --CREATE FUNCTION BRAND_MODEL_FN-- CREATE OR REPLACE FUNCTION BRAND_MODEL_FN (BRAND CHAR, MODEL CHAR) RETURN VARCHAR2 IS BRAND_MODEL VARCHAR2(50); BEGIN BRAND_MODEL := CONCAT(CONCAT (TRIM(BRAND), ':'), MODEL); RETURN BRAND_MODEL; END; --TEST FUNCTION BRAND_MODEL_FN -- SELECT BRAND_MODEL_EN(BRAND, MODEL) FROM EQUIPMENT_TB; --CREATE VIEW COMPANY_BRAND_MODEL_V -- CREATE VIEW COMPANY_BRAND_MODEL_V AS SELECT SERIAL_NUMBER, BRAND_MODEL_FN (BRAND, MODEL) AS "BRAND MODEL" FROM EQUIPMENT_TB; --DISPLAY VIEW DATA SELECT * FROM COMPANY_BRAND_MODEL_V;

SQL Worksheet Q Clear 1 2 3 4 5 SELECT * FROM WORKER_EQUIPMENT_V; SELECT BRAND_MODEL_EN(BRAND, MODEL) FROM EQUIPMENT_TB; SELECT * FROM COMPANY_BRAND_MODEL_V; SERIAL_NUMBER BRAND MODEL PROCESSOR MEMORY ASSIGNED TO DEPARTMENT 1 DELL OPTIPLEX 7040 INTEL I7-6700 32.0 GB JOHNSON WILLIAMS, ROBB 1 2 DELL OPTIPLEX 7840 INTEL I7-6700 RIVERA LOPEZ, JOEL 2 Download CSV 2 rows selected. BRAND_MODEL_FN(BRAND, MODEL) DELL:OPTIPLEX 7040 DELL:OPTIPLEX 7040 Download csv 2 rows selected. SERIAL_NUMBER BRAND MODEL 1 DELL:OPTIPLEX 7040 2 DELL:OPTIPLEX 7040 Download CSV 2 rows selected.

HENCE THE SOLUTION...!