DATABASE DESIGN
joelgisele
3 years ago
2
files (2)Practice.sql
PROJECTfall2023.docx
Practice.sql
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);
PROJECTfall2023.docx
|
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
image6.png
image7.png
image8.png
image9.png
image10.png
image11.png
image12.png
image13.png
image14.png
image15.png
image16.png
image17.png
image18.png
image19.png
image20.png
image21.png
image22.png
image1.png
image2.png
image3.png
image4.png
image5.png
- Assignment
- peace, prosperity and the state
- MGT334.12
- The Common Driveway Case
- need a 400-500 words reading reflection
- Health Psychology Assignment
- Fire Prevention Unit VIII Project
- will send a copy of mod 3 lasa 2
- Read Chapter 20 in Argument Today then read Cruz’s “College Affordability: Damned If You Go, Damned If You Don’t” and Motoko’s “Literacy Debate: Online, R U Really Reading?”
- Law Research Paper
