FIGURE P3.1 THE CH03_STORECO DATABASE TABLES Table name: EMPLOYEE Database name: Ch03_StoreCo EMP_CODE EMP_TITLE | EMP_LNAME | EMP_FNAME | EMP_INITIAL | EMP_DOB | STORE_CODE 1 Mr. 2 Ms. Williamson Ratula John W 21-May-84 3 Nancy 09-Feb-89 2 3 Ms. Greenboro Lottie 4 Mrs. Rumpersfro Jennie 5 Mr. Smith Robert 6 Mr. Renselaer Cary 7 Mr. Ogallo Roberto RSLAS 02-Oct-81 4 01-Jun-91 5 23-Nov-79 3 25-Dec-85 1 31-Jul-82 3 8 Ms. Johnsson Elizabeth I 10-Sep-88 1 9 Mr. Eindsmar Jack W 19-Apr-75 2 10 Mrs. Jones Rose R 06-Mar-86 4 11 Mr. Broderick Tom 21-Oct-92 3 12 Mr. Washington Alan Y 08-Sep-94 2 13 Mr. Smith Peter N 25-Aug-84 3 14 Ms. Smith Sherry H 25-May-86 4 15 Mr. Olenko Howard U 24-May-84 5 16 Mr. Archialo Barry V 03-Sep-80 5 17 Ms. Grimaldo Jeanine K 12-Nov-90 4 18 Mr. Rosenberg Andrew D 24-Jan-91 4 19 Mr. Rosten Peter F 03-Oct-88 4 20 Mr. Mckee Robert S 06-Mar-90 1 21 Ms. Baumann Jennifer A 11-Dec-94 3 Table name: STORE STORE_CODE STORE_NAME STORE_YTD_SALES REGION_CODE EMP_CODE 1 Access Junction 1003455.76 2 8 2 Database Corner 1421987.39 2 12 3 Tuple Charge 986783.22 1 4 Attribute Alley 944568.56 2 3 5 Primary Key Point 2930098.45 1 15 Table name: REGION REGION_CODE REGION_DESCRIPT 1 East 2 West

For this problem create a (temporary) table called instructor_course_nums. Write a procedure that accepts an instructor ID as input. The procedure calculates the total number of course sections taught by that instructor, and adds a tuple to the temporary table consisting of the instructors ID number, name, and total courses taught - call these attributes: ID, name, and tot_courses. If the instructor already has an entry in the table,then the procedure makes sure the total number of courses taught in the temporary table is up-to-date. You must name your procedure: Written in PostgreSQL

Write queries in SQL to answer each of the following questions: 1. Find all students in Comp. Sci. dept 2. Find all students with total credits > 100 3. Find all students who took course in Spring 2010 (Remove duplicates please) 4. Find all courses taken by the student whose ID is 76543 and also find his name create table student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID) ); create table takes (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year) ); insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102'); insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32'); insert into student values ('19991', 'Brandt', 'History', '80'); insert into student values ('23121', 'Chavez', 'Finance', '110'); insert into student values ('44553', 'Peltier', 'Physics', '56'); insert into student values…

Lab #9 – Joining Data From Multiple Tables List the order number and order date for every order that was placed by Mary Nelson and that contains an order line for an iron. Use an INTERSECT operator.   Lab #10 – Sub Queries Find the sales rep number, last name, and first name for every sales rep who represents at least one customer with a credit limit of $2000.     List the order number and order date for every order that was placed by Mary Nelson and that contains an order line for an iron.       Down below are the tables ORDER_LINE ORLN_ORDER_NUMBER               ORLN_PART_NUMBER ORLN_NUMBER_ORDERED          ORLN_QUOTED_PRICE   ORDER_TABLE ORDR_ORDER_NUMBER               ORDR_ORDER_DATE               ORDR_CUSTOMER_NUMBER   CUSTOMER CUST_NUMBER CUST_LAST         CUST_FIRST        CUST_STREET     CUST_CITY          CUST_STATE        CUST_ZIP_CODE               CUST_BALANCE CUST_CREDIT_LIMIT       CUST_SALES_REP_NUM               PART PART_NUMBER  PART_DESCRIPTION         UNITS_ON_HAND…