Question
List department name, employee id, and employee name for all employees in department name order. Repeat for department #10 only. List the course ID, course
- List department name, employee id, and employee name for all employees in department name order.
- Repeat for department #10 only.
- List the course ID, course name, section, instructor name, day, time, and room for all course sections.
- List the course ID, course name, section, student ID, and student name for CRN 1003. Display the list in ascending order of student last and first names.
- Use a UNION to display a single list of department numbers and names and employee numbers and names. Choose appropriate column headings and sort it by name in ascending order.
- Create a view to select employee ID, employee name, hire date, and department number.
- Use the view in a select statement listing all rows and columns.
- Use the view you just created in a select statement joined with the department table to list employee id, employee name, department name, and hire date.
- Drop your view.
- Select the department number, department name, location, and employee name using an inner join between departments and employees.
- Repeat but this time use an outer join so all departments are included in the results.
- List the employee no, employee name, and manager name for each employee with a manager. Use a self-join.
- Repeat with an outer join so even employees with no manager appear on the list.
DROP TABLE EMP; DROP TABLE DEPT;
CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT_DEPTNO PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP_EMPNO PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_EMP_DEPTNO REFERENCES DEPT(DEPTNO));
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-2015',1950,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALES',7698,'20-FEB-2015',2600,3000.79,30); INSERT INTO EMP VALUES (7521,'WARD','SALES',7698,'22-FEB-2014',2950,5051.85,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-2014',7975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALES',7698,'28-SEP-2014',2250,2853.51,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-2015',7850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-2014',8450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-2016',6000,NULL,20); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-2014',10500,NULL,10); INSERT INTO EMP VALUES (7844,'TURNER','SALES',7698,'8-SEP-2015',2500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-2016',1700,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-2014',2250,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-2015',6000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'01-JAN-2017',2000,NULL,10);
COMMIT;
DROP TABLE registration; DROP TABLE sections; DROP TABLE courses; DROP TABLE students; DROP TABLE instructors;
CREATE TABLE courses ( cid varchar2(9) NOT NULL, cname varchar2(50) NOT NULL, credits number(1) DEFAULT 3, prereq varchar2(9), CONSTRAINT pk_courses PRIMARY KEY (cid) );
INSERT INTO courses VALUES ('IS 201','Java Programming',3,null); INSERT INTO courses VALUES ('IS 202','C++ Programming',3,'IS 201'); INSERT INTO courses VALUES ('IS 301','Web Design',3,null); INSERT INTO courses VALUES ('IS 331','Business Applications',3,null); INSERT INTO courses VALUES ('IS 401','Database Design',3,'IS 331'); INSERT INTO courses VALUES ('IS 413','SQL Programming',3,'IS 401');
CREATE TABLE students ( sid char(9) NOT NULL, lname varchar(30) NOT NULL , fname varchar2(30) NOT NULL , gender char(1) NOT NULL , addr varchar2(50) NOT NULL , city varchar2(20) NOT NULL , state char(2) NOT NULL , zip varchar2(10) NOT NULL , phone varchar2(14) NULL , birthdate date NULL , tuitionRate number(7, 2) NOT NULL , creditsEarned number(3) NOT NULL , CONSTRAINT pk_students PRIMARY KEY (sid) );
INSERT INTO students VALUES ('100000001','Lee','George','M','15 Merchant Street','Honolulu','HI','96818','808-524-3333','01-MAY-1965',5000.00,47); INSERT INTO students VALUES ('100000002','Yamamoto','Bill','M','3432 Birch Street','Honolulu','HI','96814','808-522-2212','03-JUN-1958',5000.00,12); INSERT INTO students VALUES ('100000003','Carver','Hillary','F','22 Aardvark Avenue','Washington','DC','10101','800-212-3246','23-AUG-1991',5000.00,69); INSERT INTO students VALUES ('100000004','King','Linda','F','341 Kaapahu Road','Paauilo','HI','96776',NULL,'01-SEP-1998',4399.00,6); INSERT INTO students VALUES ('100000005','Rollings','Willie','M','1221 Ala Moana Blvd','Honolulu','HI','96814',NULL,NULL,4888.00,0); INSERT INTO students VALUES ('100000006','Alexander','Wanda','F','93-123 Old Mill Road','Honokaa','HI','96727','808-776-2313','02-OCT-1997',5000.00,99); INSERT INTO students VALUES ('100000007','Carver','Bill','M','33 Richards Street','Honolulu','HI','96813',NULL,'22-OCT-1990',5000.00,0); INSERT INTO students VALUES ('100000008','DeLuz','Bob','M','102 Orleans Ave','San Francisco','CA','97745','808-555-3324','01-MAR-1998',5000.00,14); INSERT INTO students VALUES ('100000009','Lee','Lisa','F','45 Fong Avenue','San Francisco','CA','97767','808-333-3432','21-APR-1997',5000.00,26); INSERT INTO students VALUES ('100000010','Garcia','Sherrie','F','2 S. Beretania','Honolulu','HI','96817','808-663-4453','03-DEC-1997',5000.00,29); INSERT INTO students VALUES ('100000011','Kamaka','Oscar','M','34 Kapolani Blvd','Honolulu','HI','96813','808-533-3332','12-FEB-1998',5000.00,0);
CREATE TABLE instructors ( inId char(9) NOT NULL, iLname varchar2(30) NOT NULL, iFname varchar2(30) NOT NULL, rank varchar2(10) NOT NULL, office varchar2(10) NULL, phone varchar2(20) NULL, salary number(8,2) DEFAULT 0, CONSTRAINT pk_instructors PRIMARY KEY (inID) );
INSERT INTO instructors VALUES ('200000001','Souza','Edward','Lecturer','LM101','808-533-4241',5000.00); INSERT INTO instructors VALUES ('200000002','Tenzer','Laurie','Associate','LM102','808-533-4244',5000.00); INSERT INTO instructors VALUES ('200000003','Otake','Bill','Assistant','MR101','808-533-4247',5800.00);
CREATE TABLE sections ( crn char(4) NOT NULL, cid varchar2(9) NOT NULL, section char DEFAULT 'A', inId char(9) NOT NULL, days varchar2(10) DEFAULT 'TBA', time varchar2(16) DEFAULT 'TBA', room varchar2(10) NULL, CONSTRAINT pk_sections PRIMARY KEY (crn), CONSTRAINT fk_inid_sections FOREIGN KEY (inid) REFERENCES instructors(inid), CONSTRAINT fk_cid_sections FOREIGN KEY (cid) REFERENCES courses(cid) );
INSERT INTO sections VALUES ('1000','IS 201','A','200000003','MWF','08:00 - 08:50','CL100'); INSERT INTO sections VALUES ('1001','IS 201','B','200000003','MWF','09:00 - 09:50','CL100'); INSERT INTO sections VALUES ('1002','IS 201','C','200000001','TTh','08:00 - 09:15','CL102'); INSERT INTO sections VALUES ('1003','IS 301','A','200000002','TTh','09:30 - 10:45','CL340'); INSERT INTO sections VALUES ('1004','IS 301','B','200000002','MWF','09:00 - 09:50','CL340'); INSERT INTO sections VALUES ('1005','IS 413','A','200000001','MWF','09:00 - 09:50','CL230');
CREATE TABLE registration ( crn char(4) NOT NULL, sid char(9) NOT NULL, CONSTRAINT pk_registration PRIMARY KEY (crn,sid), CONSTRAINT fk_crn_registration FOREIGN KEY (crn) references sections(crn), CONSTRAINT fk_sid_registration FOREIGN KEY (sid) references students(sid) );
INSERT INTO registration VALUES ('1000','100000001'); INSERT INTO registration VALUES ('1003','100000001'); INSERT INTO registration VALUES ('1005','100000001'); INSERT INTO registration VALUES ('1001','100000002'); INSERT INTO registration VALUES ('1004','100000002'); INSERT INTO registration VALUES ('1005','100000003'); INSERT INTO registration VALUES ('1002','100000004'); INSERT INTO registration VALUES ('1003','100000004'); INSERT INTO registration VALUES ('1005','100000004'); INSERT INTO registration VALUES ('1000','100000005'); INSERT INTO registration VALUES ('1003','100000005'); INSERT INTO registration VALUES ('1002','100000008'); INSERT INTO registration VALUES ('1004','100000008'); INSERT INTO registration VALUES ('1002','100000009'); INSERT INTO registration VALUES ('1005','100000009'); INSERT INTO registration VALUES ('1002','100000010'); INSERT INTO registration VALUES ('1005','100000010'); INSERT INTO registration VALUES ('1000','100000011'); INSERT INTO registration VALUES ('1003','100000011'); INSERT INTO registration VALUES ('1005','100000011'); commit;
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started