UNIVERSITY OF TECHNOLOGY, JAMAICA

DATABASE Administration Lab

ID#: 1403605
Name: Marc-Anthony Latty
GROUP: Bachelor OF Science IN Computing
Week: 1
Lab Report: 1



Exercise 2 – Creating a Customer TABLE

2.1
SQL Server VARCHAR stores 8000 bytes AND IS an available datatype while Oracle VARCHAR AND Varchar2 are reserved BY Oracle TO support distinction BETWEEN NULL AND empty string.

2.2
Dates IN SQL Server are IN the format : ‘YYYY/MM/DD’ while Oracle presents DATE IN : ‘DAY/MONTH/YEAR’ format

2.3
Solution:

--ORACLE

CREATE TABLE customer_1403605(
    custID NUMBER(10),
    firstName VARCHAR(255),
    lastName VARCHAR(255),
    dob DATE,
    address VARCHAR(255),
    phone VARCHAR(11)
);

--SQL Server

CREATE TABLE customer_1403605(
    custID INT(10),
    firstName VARCHAR(255),
    lastName VARCHAR(255),
    dob DATE,
    address VARCHAR(255),
    phone VARCHAR(11)
);

Observation:

Oracle uses a NUMBER datatype TO hold NUMERIC VALUES while SQL Server uses INT.
The commands are the same FOR creating a TABLE.

2.4
Solution:

--Oracle
ALTER TABLE customer_1403605 ADD CONSTRAINT customer_1403605_pk PRIMARY KEY (custID);

--SQL Server
ALTER TABLE customer_1403605 ADD CONSTRAINT customer_1403605_pk PRIMARY KEY (custID);

Observation:
The commands are the same FOR BOTH environments.


Exercise 3 – Interacting WITH DATA (IN BOTH Environments)

3.1
Solution:

--Oracle
INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(1,'Bill','Beannie',TO_DATE('1990/9/10','YYYY/MM/DD'), '10 Mango Drive', '8761234567');

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(2,'Billy','Grace',TO_DATE('1991/02/12','YYYY/MM/DD'), '11 Mango Drive', '8761265746');

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(3, 'Michael','James',TO_DATE('1993/11/12','YYYY/MM/DD'), '12 Mango Drive', '8763546537');

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(4, 'MArc','Latty',TO_DATE('1996/01/17','YYYY/MM/DD'), '13 Mango Drive', '8768033388');

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(5, 'Kat','Wallace',TO_DATE('2003/02/17','YYYY/MM/DD'), '14 Mango Drive', '8768574632');

--SQL Server

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(1,'Bill','Beannie','1990/9/10', '10 Mango Drive', '8761234567');

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(2,'Billy','Grace','1991/02/12', '11 Mango Drive', '8761265746');

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(3, 'Michael','James','1993/11/12', '12 Mango Drive', '8763546537');

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(4, 'MArc','Latty','1996/01/17', '13 Mango Drive', '8768033388');

INSERT INTO customer_1403605 (custID, firstName, lastName, dob, address, phone) VALUES
(5, 'Kat','Wallace','2003/02/17', '14 Mango Drive', '8768574632');

Observations:

DATE IN ORACLE requires you TO USE the TO_DATE FUNCTION, while IN SQL Server you could enter it AS a string literal.

3.2
Solution:

--Oracle

commit;

SELECT * FROM customer_1403605;


3.3
Solution:
SELECT * FROM customer_1402620 WHERE custid = MAX(custid);

3.4
Solution:
SELECT * FROM customer_1402620 WHERE custid = MIN(custid);

3.5
Solution:
SELECT * FROM customer_1402620 WHERE custid = (SELECT MAX(custid) FROM customer_1402620) OR custid = (SELECT MIN(custid) FROM customer_1402620);

3.6
Solution:
DELETE FROM customer_1402620 WHERE custid = (SELECT MAX(custid) FROM customer_1402620);

3.7
Solution:
DELETE FROM customer_1402620 WHERE custid = (SELECT MIN(custid) FROM customer_1402620);

3.8
Solution:
ROLLBACK;

Observation:
The LAST TRANSACTION was undone.

3.9
Solution:
ALTER TABLE customer_1402620 ADD gender CHAR(1);

3.10
Solution:
UPDATE customer_1402620 SET gender='M' WHERE custid IN (SELECT custid FROM
(SELECT custid FROM customer_1402620 ORDER BY custid ASC) WHERE rownum <= 4);



3.11
Solution:
UPDATE customer_1402620 SET gender=’F’ WHERE gender IS NULL;

3.12
Solution:
ALTER TABLE customer_1402620 MODIFY firstName varchar2(100);

Exercise 4 – Searching the System TABLES (Oracle ONLY)

4.0
Example 1
Solution:
SELECT TABLE_NAME FROM All_Tables WHERE TABLE_NAME LIKE ‘%CUSTOMER_1402620%’ GROUP BY TABLE_NAME;

Example 2
Solution:
SELECT TABLE_NAME, COUNT(column_name) FROM all_tab_columns WHERE TABLE_NAME LIKE ‘%CUSTOMER_1402620%’ GROUP BY TABLE_NAME;

4.1
Solution:
DESC USER_TABLES;

Observations:
the DESC command RETURNS the COLUMN definitions;

4.2
Solution:
SELECT * FROM USER_TABLES WHERE TABLE_NAME LIKE ‘%CUSTOMER_1402620%’;

Observation:
You have TO USE uppercase since Oracle stores ALL TABLE names IN UPPER CASE.

4.3
Observation:
The dictionary TABLE has a TABLE_NAME AND comments FIELD

4.4
Solution:
SELECT COUNT(TABLE_NAME) FROM dict;

Observation:
Total amount OF TABLES: 2553

4.5
Solution:
SELECT constraint_name, constraint_type, STATUS, TABLE_NAME FROM user_constraints ;
Observation:
It shows ALL the constraints that are ON TABLES AS well AS their meta-DATA

4.6
Solution:
DESC ALL_OBJECTS;

FIELDS:

OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY
NAMESPACE
EDITION_NAME

DESC ALL_VIEWS;

FIELDS:

 OWNER
 VIEW_NAME
 TEXT_LENGTH
 TEXT
 TYPE_TEXT_LENGTH
 TYPE_TEXT
 OID_TEXT_LENGTH
 OID_TEXT
 VIEW_TYPE_OWNER
 VIEW_TYPE
 SUPERVIEW_NAME
 EDITIONING_VIEW
 READ_ONLY

DESC DBA_ERRORS;

FIELDS:

OWNER
NAME
TYPE
SEQUENCE
LINE
POSITION
TEXT
ATTRIBUTE
MESSAGE_NUMBER