DATABASE DESIGN

DROP TABLE CRUISE_PASSENGER CASCADE CONSTRAINTS; DROP TABLE PASSENGER CASCADE CONSTRAINTS; DROP TABLE CRUISE_PORT CASCADE CONSTRAINTS; DROP TABLE PORT CASCADE CONSTRAINTS; DROP TABLE CRUISE CASCADE CONSTRAINTS; DROP TABLE SAILOR CASCADE CONSTRAINTS; DROP TABLE SHIP CASCADE CONSTRAINTS; CREATE TABLE SHIP( ship_number INT PRIMARY KEY, ship_name VARCHAR2(300), weight NUMBER, year_built DATE, passenger_capacity INT ); CREATE TABLE SAILOR( sailor_id INT PRIMARY KEY, name VARCHAR2(300), date_of_birth DATE, nationality VARCHAR2(70), supervisor_id INT, FOREIGN KEY (supervisor_id) REFERENCES SAILOR(sailor_id) ); CREATE TABLE CRUISE( cruise_serial_number INT PRIMARY KEY, sailing_date DATE, return_date DATE, departure_port VARCHAR2(300), ship_number INT, FOREIGN KEY (ship_number) REFERENCES SHIP(ship_number) ); CREATE TABLE PORT( port_name VARCHAR2(300), country VARCHAR2(100), population INT, passport_required VARCHAR2(3), docking_fee NUMBER(10, 2), PRIMARY KEY (port_name, country) ); CREATE TABLE CRUISE_PORT ( cruise_serial_number INT, port_name VARCHAR2(300), country VARCHAR2(100), PRIMARY KEY (cruise_serial_number, port_name, country), FOREIGN KEY (cruise_serial_number) REFERENCES CRUISE(cruise_serial_number), FOREIGN KEY (port_name, country) REFERENCES PORT(port_name, country) ); CREATE TABLE PASSENGER( passenger_number INT PRIMARY KEY, name VARCHAR2(300), home_address VARCHAR2(300), nationality VARCHAR2(70), date_of_birth DATE ); CREATE TABLE CRUISE_PASSENGER( cruise_serial_number INT, passenger_number INT, payment_amount NUMBER, satisfaction_rating INT, PRIMARY KEY (cruise_serial_number, passenger_number), FOREIGN KEY (cruise_serial_number) REFERENCES CRUISE(cruise_serial_number), FOREIGN KEY (passenger_number) REFERENCES PASSENGER(passenger_number) ); INSERT INTO SHIP VALUES (1, 'Ship A', 5000, TO_DATE('2000-01-01', 'YYYY-MM-DD'), 200); INSERT INTO SHIP VALUES (2, 'Ship B', 6000, TO_DATE('2005-01-01', 'YYYY-MM-DD'), 250); INSERT INTO SAILOR VALUES (1, 'John Sailor', TO_DATE('1980-05-15', 'YYYY-MM-DD'), 'American', NULL); INSERT INTO SAILOR VALUES (2, 'Jane Sailor', TO_DATE('1985-08-20', 'YYYY-MM-DD'), 'British', 1); INSERT INTO CRUISE VALUES (101, TO_DATE('2023-06-01', 'YYYY-MM-DD'), TO_DATE('2023-06-15', 'YYYY-MM-DD'), 'Port A', 1); INSERT INTO CRUISE VALUES (102, TO_DATE('2023-07-01', 'YYYY-MM-DD'), TO_DATE('2023-07-15', 'YYYY-MM-DD'), 'Port B', 2); INSERT INTO PORT VALUES ('Port A', 'Country A', 1000000, 'Yes', 1500.00); INSERT INTO PORT VALUES ('Port B', 'Country B', 800000, 'No', 1200.00); INSERT INTO CRUISE_PORT VALUES (101, 'Port A', 'Country A'); INSERT INTO CRUISE_PORT VALUES (102, 'Port B', 'Country B'); INSERT INTO PASSENGER VALUES (1, 'Alice Passenger', '123 Main St', 'Canadian', TO_DATE('1990-03-10', 'YYYY-MM-DD')); INSERT INTO PASSENGER VALUES (2, 'Bob Passenger', '456 Oak St', 'German', TO_DATE('1988-12-05', 'YYYY-MM-DD')); INSERT INTO CRUISE_PASSENGER VALUES (101, 1, 1200.00, 4); INSERT INTO CRUISE_PASSENGER VALUES (102, 2, 1500.00, 5);

SQL statements (Queries) Write a minimum of 15 different queries following guidelines below: Checklist: • Write questions that the query should answer. • Write an SQL statement to answer the question. • Must demonstrate the use of the following: 1. Arithmetic Operators 2. Distinct 3. Concatenation 4. Comparison operators. 5. BETWEEN … AND 6. AND 7. OR 8. NOT 9. IN 10. LIKE 11. Multi table JOIN 12. SORT 13. Arithmetic functions (SUM, COUNT, AVG, MIN, MAX etc) · Provide screen captures as evidence of completing the tasks

1. Show all records( ALL ROWS AND COLUMNS) from Employee.

SELECT * FROM EMPLOYEE;

2. Show any 3 columns from the Employee table

SELECT EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE;

3. Show any 3 columns from the Employee table. Change the column aliases for any 2 of those columns

SELECT EMP_FNAME as "First Name", EMP_HIREDATE, JOB_CODE as Code

FROM EMPLOYEE;

select EMP_NUM, ASSIGN_CHG_HR, ASSIGN_HOURS , ASSIGN_CHG_HR * ASSIGN_HOURS

from assignment

4. [Hint: Computed columns] Use the assignments table to compute the wage for all employees. Show columns Employee number, charge and hours.

5. [Hint: Computed columns, column alias] Use the assignments table to compute the wage for all employees. Show columns Employee number, charge and hours. Call the new computed column WAGE. Rename the ASSIGN_CHG_HR -> Rate, call ASSIGN_HOURS -> Hours worked

Select 8+2*5 from DUAL;

Select (8+2)*5 from DUAL;

Select 4 +5^ 2 * 3 from DUAL;

6. [Hint: Date Arithmetic] Display all employee hiredates and hiredates plus 5 (probation period).

SELECT EMP_HIREDATE, EMP_HIREDATE + 5 FROM EMPLOYEE

7. Show today’s system date

select sysdate from dual

8.[Hint: list unique values] Show all employee numbers who have been assigned jobs (remove the duplicate values ) or (show each employee number only once)

Select distinct EMP_NUM from assignment

Alter table Employee add CONSTRAINT FK_emp_Job foreign key (JOB_CODE) references Job;

Alter table Employee drop CONSTRAINT SYS_C00112881029

Select * from USER_CONS_COLUMNS

9) Join Employee with Job showing the employee number, name, job code and the description of all the jobs assigned to them.

select EMP_NUM, EMP_FNAME, JOB_CODE,JOB_DES

from JOB NATURAL JOIN EMPLOYEE

10) [Hint:Join and then order By clause- ASC/DESC] Join Employee with Job showing the employee number, name, job code and the description of all the jobs assigned to them. Sort the data in ascending order of Job_code

select EMP_NUM, EMP_FNAME, JOB_CODE,JOB_DES

from JOB NATURAL JOIN EMPLOYEE

ORDER BY JOB_CODE asc

11) 10) [Hint:Join and then order By clause- ASC/DESC] Join Employee with Job showing the employee number, name, job code and the description of all the jobs assigned to them. Sort the data in DESCENDING order of Job_code

select EMP_NUM, EMP_FNAME, JOB_CODE,JOB_DES

from JOB NATURAL JOIN EMPLOYEE

ORDER BY JOB_CODE desc

12. [Hint: Computed columns, column alias, order by] Use the assignments table to compute the wage for all employees. Call the new computed column WAGE. Show columns Employee number, charge and hours. Sort the derived column showing the lowest wage first.

select EMP_NUM, ASSIGN_CHG_HR, ASSIGN_HOURS , ASSIGN_CHG_HR * ASSIGN_HOURS as wage

from assignment order by wage

13. [Hint: Computed columns, column alias, order by] Use the assignments table to compute the wage for all employees. Call the new computed column WAGE. Show columns Employee number, charge and hours. Sort on EMP_NUM in ascending order and then the derived column showing the highest wage first.

select EMP_NUM, ASSIGN_CHG_HR, ASSIGN_HOURS , ASSIGN_CHG_HR * ASSIGN_HOURS as wage

from assignment order by EMP_NUM asc, WAGE desc

14. Show any 4 columns from the Employee table for employee number 110

SELECT EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE

WHERE EMP_NUM=110;

15. Show any 4 columns from the Employee table EXCEPT for employee number 110

SELECT EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE

WHERE EMP_NUM<>110;

16. Show any 4 columns from the Employee table for all employee numbers less than or equal 110

SELECT EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE

WHERE EMP_NUM <= 110;

17. Show EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE from the Employee table for all that were hired on December 1st 1996 and after.

SELECT EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE

WHERE EMP_HIREDATE >= '12/01/1996';

18. Show any 4 columns from the Employee table for employee numbers 101 OR 109 OR 110

SELECT EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE

WHERE EMP_NUM =101 or EMP_NUM =109 or EMP_NUM =110;

19. Show EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE from the Employee table for all emp_num less than 106 and who were hired before January 1st 2000.

SELECT EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE

WHERE EMP_NUM <106 AND EMP_HIREDATE <= '01/01/2000';

20. Show any 4 columns from the Employee table EXCEPT for employee number 110

SELECT EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE

WHERE NOT (EMP_NUM =110);

21. [ Hint: Range BETWEEN operator] Show the employee number and the hours assigned for all employees that have assigned hours from 3 to 5.

Select EMP_NUM, ASSIGN_HOURS from assignment where ASSIGN_HOURS between 3 and 5

22. [ Hint: Range BETWEEN operator] Show the employee number and the hire dates for all employees that have been hired from dec 1st 1996 to the end of the year 2000.

select EMP_NUM, emp_hiredate from Employee where emp_hiredate between '12/1/1996' and '12/31/2000'

23. { Hint: use IN operator instead of OR] Show any columns EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE from the Employee table for employee numbers 101 OR 109 OR 110

SELECT EMP_NUM, EMP_FNAME, EMP_HIREDATE, JOB_CODE

FROM EMPLOYEE

WHERE EMP_NUM IN (101 ,109,110);

24. { Hint: use IN operator instead of OR] Show all columns from the job table displaying only the jobs 'Programmer' or 'Electrical Engineer' or 'Civil Engineer' or ‘Bio Technician'.

SELECT *

FROM JOB

WHERE JOB_DES IN ('Programmer' , 'Electrical Engineer' , 'Civil Engineer' , 'Bio Technician');

25. { Hint: use LIKE operator WILDCARDS % , _ ] Show all columns from the job table displaying only the jobs ending with Engineer.

SELECT * FROM JOB WHERE JOB_DES LIKE '%Engineer'

26. { Hint: use LIKE operator WILDCARDS % , _ ] Show all columns from the employee table displaying only the last names starting with ‘Jo’

SELECT *

FROM Employee

WHERE EMP_LNAME LIKE 'Jo%'

27. { Hint: use LIKE operator WILDCARDS % , _ ] Show all columns from the employee table displaying only the First names Maria and Larry using the underscore wildcard character.

SELECT * FROM Employee WHERE EMP_FNAME like ' _ar __'

28. [Hint: Function- COUNT] Count how many employees are recorded in the employee table.

SELECT COUNT(EMP_NUM)

FROM EMPLOYEE

29. [Hint: Function- MIN] Show the lowest pay rate from the assignment table

SELECT MIN(ASSIGN_CHG_HR)

FROM ASSIGNMENT

30. [Hint: Function- MAX] Show the highest pay rate from the assignment table

SELECT MAX(ASSIGN_CHG_HR)

FROM ASSIGNMENT

31. [Hint: Function- SUM] Compute the total balance due for all the projects.

SELECT SUM(PROJ_BALANCE)

FROM PROJECT

32.[Hint: Function- AVG] Compute the average number of years the employees have been with the company

SELECT AVG(EMP_YEARS)

FROM EMPLOYEE

33.[Hint: Function- AVG] Compute the average number of years the employees have been with the company since 1st January 1997

SELECT AVG(EMP_YEARS)

FROM EMPLOYEE

Where EMP_HIREDATE> '1/1/1997'

34. .[Hint: String Function- concatenation ||] Concatenate first with last name to show the full name.

SELECT EMP_FNAME || EMP_LNAME

FROM EMPLOYEE

35. .[Hint: String Function- concatenation ||] Concatenate first with last name separated by a space to show the full name.

SELECT EMP_FNAME || ' ' ||EMP_LNAME

FROM EMPLOYEE

36.[Hint: String Function- concatenation ||] Concatenate first , initial and last name separated by spaces to show the full name. Use column alias to call it “Full Name”

SELECT EMP_FNAME || ' '|| EMP_initial ||'. '||EMP_LNAMe as “FULL NAME”

FROM EMPLOYEE