ITP4903 Lab 1-5 Ans

Lab1 Structured Query Language – DBMS

Task       Answer
10 EMPID will be formatted with 6 digit width with preceding zeros. Salary with a
floating dollar sign.
14 INSERT INTO Employee VALUES (111, 'Chan','Jacky','03-AUG- 54',85000);

15 SELECT fname, dob FROM Employee where lname = 'Cheung';
16 CREATE TABLE Student (
std_id CHAR(9),
last_name VARCHAR2(20) NOT NULL,
first_name VARCHAR2(20) NOT NULL,
course NUMBER(8),
CONSTRAINT student_pk PRIMARY KEY (std_id)
)

Lab2  Structured Query Language – DDL

2 CREATE TABLE Item (
itemId NUMBER(8) NOT NULL,
itemDesc VARCHAR2(20) NULL,
category VARCHAR2(15) NULL,
CONSTRAINT item_itemid_pk PRIMARY KEY (itemid),
CONSTRAINT category_cc CHECK (category IN ('M', 'W', 'C'))
);
CREATE TABLE Color (
color VARCHAR2(20) NOT NULL,
CONSTRAINT color_color_pk PRIMARY KEY (color)
);
CREATE TABLE Inventory (
invId NUMBER(10) NOT NULL,
itemId NUMBER(8) NOT NULL,
itemSize VARCHAR2(10) NULL,
color VARCHAR2(20) NOT NULL,
currPrice NUMBER(6,2) NOT NULL,
qoh NUMBER(4) NOT NULL,
CONSTRAINT inventory_invid_pk PRIMARY KEY (invId),
CONSTRAINT inventory_itemid_fk FOREIGN KEY (itemId)
REFERENCES items (itemid),
CONSTRAINT inventory_color_fk FOREIGN KEY (color)
REFERENCES color (color),
CONSTRAINT currPrice_cc CHECK(currPrice BETWEEN 5 AND 1000)
);
3 ALTER TABLE Item ADD (unitPrice NUMBER(5,2) NULL);
4 ALTER TABLE Inventory ADD (inStockDate DATE NULL);
5 ALTER TABLE Item MODIFY (unitPrice NUMBER(6,2));
6 ALTER TABLE Item MODIFY (category VARCHAR2(30));
7 ALTER TABLE Inventory DROP CONSTRAINT currPrice_cc;
8 ALTER TABLE Item DROP (unitPrice);

Lab3  Structured Query Language – DDL

2 CREATE TABLE Publisher (
publisherID CHAR(2) NOT NULL,
publisherName VARCHAR2(50) NOT NULL,
address VARCHAR2(250) NULL,
CONSTRAINT publisher_pk PRIMARY KEY (publisherID)
);
CREATE TABLE Category (
catID CHAR(2) NOT NULL,
category VARCHAR2(50) NULL,
CONSTRAINT category_pk PRIMARY KEY (catID)
);
CREATE TABLE Book (
bookID NUMBER(2) NOT NULL,
title VARCHAR2(50) NOT NULL,
catID CHAR(2) NULL,
copyrightYear NUMBER(2) NULL,
isbnNumber VARCHAR2(50) NULL,
publisherID CHAR(2) NULL,
purchasePrice NUMBER(4,2) NULL,
coverType VARCHAR2(10) NULL,
datePurchased DATE DEFAULT SYSDATE,
pages NUMBER(4) NULL,
CONSTRAINT book_pk PRIMARY KEY (bookID),
CONSTRAINT book_fk FOREIGN KEY (publisherID)
REFERENCES Publisher (PublisherID)
);
CREATE TABLE Author (
authorID CHAR(2) NOT NULL,
firstName VARCHAR2(50) NOT NULL,
lastName VARCHAR2(50) NOT NULL,
qualification VARCHAR2(10) NULL,
gender CHAR(1) NULL,
CONSTRAINT author_pk PRIMARY KEY (authorID),
CONSTRAINT author_gender_chk CHECK (gender IN ('M', 'm', 'F', 'f'))
);
CREATE TABLE BookAuthor (
bookID NUMBER(2) NOT NULL,
authorID CHAR(2) NOT NULL,
CONSTRAINT bookauthor_pk PRIMARY KEY (bookID, authorID),
CONSTRAINT bookauthor_fk1 FOREIGN KEY (BookID)
REFERENCES Book (BookID),
CONSTRAINT bookauthor_fk2 FOREIGN KEY(AuthorID)
REFERENCES Author (AuthorID)
);
3 INSERT INTO Publisher VALUES ('NE', 'New 2000 Publishing', 'Hong Kong');
INSERT INTO Publisher VALUES ('SA', 'South Asia Limited', 'Singapore');
INSERT INTO Publisher VALUES ('OR', 'Oriental Publishing', 'Taiwan');
INSERT INTO Category VALUES ('PS', 'Fiction');
INSERT INTO Category VALUES ('BU', 'Business');
INSERT INTO Category VALUES ('RO', 'Relaxation');
INSERT INTO Category VALUES ('HE', 'Technical');
INSERT INTO Book VALUES
(1, 'Dirk Luchte', 'PS', 93, NULL,'NE', 23.50,'Hard',
23-NOV- 93', 1012);
INSERT INTO Book VALUES
(2,'Planning Your Career', 'BU', 90, NULL, 'SA', 22.95, 'Hard',
23-DEC- 94 ', 395);
INSERT INTO Book VALUES
(3,' Diamonds ','RO', 94, NULL, 'OR', 9.95, 'Paperback',
12-JAN- 94', 593);
INSERT INTO Book VALUES
(4,'Techniques of Transportation', 'HE', 93, NULL,'OR', 22.95, 'Hard',
27-OCT- 93', 236);
INSERT INTO Book VALUES
(5,'My Family', 'PS', 93, NULL, 'NE', 17.95, 'Paperback',
13-Jul- 93', 226);
INSERT INTO Author VALUES ('CA', 'Laura', 'Callahan', NULL, 'f');
INSERT INTO Author VALUES ('DA', 'Nancy', 'Davolio', 'B.A.', 'f');
INSERT INTO Author VALUES ('FU', 'Andrew', 'Fuller', 'Ph.D.', 'm');
INSERT INTO Author VALUES ('LE', 'Janet', 'Leverling', 'M.A.', 'f');
INSERT INTO Author VALUES ('PE', 'Margaret', 'Peacock', NULL, 'f');
INSERT INTO BookAuthor VALUES (3,'LE');
INSERT INTO BookAuthor VALUES (1,'CA');
INSERT INTO BookAuthor VALUES (5,'FU');
INSERT INTO BookAuthor VALUES (2,'DA');
INSERT INTO BookAuthor VALUES (4,'PE');
4 INSERT INTO Author VALUES ('01', 'Peter', 'Chan', 'HD', 'm');
INSERT INTO Author VALUES ('02', 'Peter', 'Chan', 'HD', 'n');
restricted to ‘M, ‘m’, ‘F’, ‘f’ only and it does not allow ‘n’
5 ALTER TABLE Publisher ADD
(phoneNumber VARCHAR2(8) NULL);
6 ALTER TABLE Publisher MODIFY
(phoneNumber NUMBER(8));
7 ALTER TABLE Book MODIFY
(purchasePrice default 0.0);
8 ALTER TABLE Book ADD
CONSTRAINT book_catid_fk FOREIGN KEY (catID)
REFERENCES Category (catID);
9 ALTER TABLE Book DROP
CONSTRAINT book_fk;
10 UPDATE Author
SET qualification = 'M.A.'
WHERE firstName = 'Laura';
UPDATE Author
SET qualification = NULL
WHERE firstName = 'Janet';
11 UPDATE Book
SET purchasePrice = purchasePrice * (1 - 0.15);
12 DELETE Publisher
WHERE publisherName = 'Oriental Publishing';
13 -- Referentail integrity constraint violated. BookAuthor.AuthorId is a foreign
DELETE from BookAuthor
WHERE authorID = 'PE';
DELETE from Author
WHERE firstName = 'Margaret';
14 UPDATE Book
SET datePurchased = SYSDATE;

Lab4  Structured Query Language – DML

2 SELECT * FROM Dept;
3 SELECT * FROM Emp;
4 SELECT * FROM Salgrade;
5 SELECT ename, sal, deptno
FROM Emp
WHERE sal BETWEEN 1000 AND 2000
ORDER BY sal DESC;
6 SELECT DISTINCT job
FROM Emp;
7 SELECT *
FROM Emp
WHERE deptno IN (10, 20);
8 SELECT ename, job, deptno
FROM Emp
WHERE job = 'CLERK' AND
deptno = 20;
9 SELECT ename
FROM Emp
WHERE ename LIKE '%AR%' OR
ename LIKE '%ES%';
10 SELECT ename, hiredate
FROM Emp
WHERE hiredate LIKE '%87';
11 SELECT ename, hiredate
FROM Emp
WHERE hiredate NOT LIKE '%87';
12 SELECT ename, hiredate
FROM Emp
WHERE hiredate LIKE '01-MAY%';
13 SELECT ename
FROM Emp
WHERE mgr IS NULL;
14 SELECT empno, mgr
FROM Emp
ORDER BY mgr ASC, empno ASC;

Lab5  Structured Query Language – DML

2 SELECT COUNT(empno)

FROM Emp;

3 SELECT COUNT(deptno)

FROM Dept;

4 SELECT MIN(sal)

FROM Emp;

5 SELECT COUNT(DISTINCT job)

FROM Emp;

6 SELECT COUNT(empno)

FROM Emp

WHERE job = 'CLERK';

7 SELECT DISTINCT job SELECT job

FROM Emp OR FROM Emp

ORDER BY job ASC; GROUP BY job

ORDER BY job ASC;

8 SELECT deptno, AVG(sal)

FROM Emp

WHERE job = 'CLERK'

GROUP BY deptno;

9 SELECT job, MIN(sal)

FROM Emp

GROUP BY job

ORDER BY MIN(sal) DESC;

10 SELECT job, AVG(sal)

FROM Emp

GROUP BY job;

11 SELECT job, AVG(sal)

FROM Emp

GROUP BY job

HAVING AVG(sal) >= 3000;

12 SELECT deptno, AVG(sal)

FROM Emp

GROUP BY deptno

ORDER BY deptno ASC;

13 SELECT deptno, COUNT(empno)

FROM Emp

GROUP BY deptno;

14 SELECT deptno, COUNT(empno)

FROM Emp

WHERE job = 'CLERK'

GROUP BY deptno;