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

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

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

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…
In an OUTER JOIN query, the outer join operator (+) is placed after which table?
Inconsistencies:Although update queries can help, you still need human intervention to remove inconsistent values. T to F?
Instructorld 1 • Courseld CourseCode 9630 MATH109 Course CourseName Linear Algebra Capacity 175 4964 PHILAT Intro to Philosophy 50 4253 MATH19 Number Theory 150 1896 HIST66 American History 25 8048 HIST58 World History 125 2133 Instructor Instructorld InstructorName Rank Department Del Day Associate Professor Math Rob Ruiz Associate Professor Philosophy Aya Diaz Associate Professor History Note: Both tables may not be necessary to complete this level. Select the rows returned by the query below. SELECT CourseName, Capacity, InstructorId FROM Course C WHERE Capacity > (SELECT AVG (Capacity) FROM Course WHERE InstructorId = c.InstructorId); CourseName Linear Algebra Capacity Instructorld 175 1 Intro to Philosophy 50 2 150 Number Theory American History 25 World History 125 2133
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. Repeat the exercise from the prior step using the ANY operator in the query instead of the ALL operator.
Add a foreign key constraint to the movie table: the column prod_id is the foreign key, referencing the column id from the production table (use sql) following is movie and production table