EBK DATABASE SYSTEMS: DESIGN, IMPLEMENT
12th Edition
ISBN: 8220101450739
Author: Morris
Publisher: YUZU
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 7, Problem 13P
Write the two SQL command sequences required to:
a. Create a temporary table named TEMP_1 whose structure is composed of the EMP_2 attributes EMP_NUM and EMP_PCT.
b. Copy the matching EMP_2 values into the TEMP_1 table.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
The Employee database contains a page displaying summary information, including
EMPNO, ENAME, JOB, HIREDATE, SAL and DEPTNO columns from the EMP
table. Create a PL/SQL block with scalar variables to retrieve this data and then
display it on screen. An initialized variable should provide the EMPNO value. Test
the block using the employee number 7369.
A. Write SQL statements to create a table named STUDENT, with the following columns:
StdID: character field of width 10, null is not allowed
AdvID: character field of width 10, null is not allowed
B. Given that the STUDENT table is created in part (a), write a SQL statement to insert a constraint to STUDENT table created in part (a) to designate StdID as the primary key (Do not recreate another STUDENT since it was already done in part a).
Write a PL/SQL script to print all the details of the
Jobs table using a cursor. Your query must include
%RowType (to access the column names and
datatypes of jobs table), and a cursor attribute to
print the total number of rows returned.
DEPARTMENTS
LOCATIONS
DEPARTMENT_ID
* DEPARTMENT_NAME
F
P * LOCATION_ID
STREET_ADDRESS
POSTAL_C
MANAGER_ID
LOCATION_ID
JOB_HISTORY
PF* EMPLOYEE_ID
* START_DATE
CITY
P
END DATE
F * JOB_ID
F
STATE_PROVINCE
F
COUNTRY_ID
DEPARTMENT_ID
EMPLOYEES
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
U * EMAIL
PHONE_NUMBER
HIRE_DATE
* JOB_ID
P.
COUNTRIES
P
COUNTRY_ID
COUNTRY_NAME
F
JOBS
REGION_ID
P * JOB_ID
* JOB_TITLE
MIN_SALARY
MAX_SALARY
SALARY
COMMISSION_PCT
REGIONS
IF
MANAGER_ID
P * REGION_ID
REGION_NAME
F
DEPARTMENT_ID
Chapter 7 Solutions
EBK DATABASE SYSTEMS: DESIGN, IMPLEMENT
Ch. 7 - Prob. 1RQCh. 7 - Explain why the following command would create an...Ch. 7 - Prob. 3RQCh. 7 - Explain why it might be more appropriate to...Ch. 7 - What is the difference between a column constraint...Ch. 7 - What are referential constraint actions?Ch. 7 - Rewrite the following WHERE clause without the use...Ch. 7 - Explain the difference between an ORDER BY clause...Ch. 7 - Explain why the following two commands produce...Ch. 7 - What is the difference between the COUNT aggregate...
Ch. 7 - Prob. 11RQCh. 7 - Prob. 12RQCh. 7 - Write the SQL code that will create the table...Ch. 7 - Having created the table structure in Problem 1,...Ch. 7 - Prob. 3PCh. 7 - Prob. 4PCh. 7 - Write the SQL code to change the job code to 501...Ch. 7 - Write the SQL code to delete the row for William...Ch. 7 - Write the SQL code that will restore the data to...Ch. 7 - Write the SQL code to create a copy of EMP_1,...Ch. 7 - Write the SQL code to change the EMP_PCT value to...Ch. 7 - Using a single command sequence, write the SQL...Ch. 7 - Prob. 11PCh. 7 - Write the SQL code that will change the PROJ_NUM...Ch. 7 - Write the two SQL command sequences required to:...Ch. 7 - Prob. 14PCh. 7 - Prob. 15PCh. 7 - Using the EMPLOYEE, JOB, and PROJECT tables in the...Ch. 7 - Write the SQL code that will produce the same...Ch. 7 - Write the SQL code to find the average bonus...Ch. 7 - Prob. 19PCh. 7 - Prob. 20PCh. 7 - Write the SQL code to calculate the ASSIGN_CHARGE...Ch. 7 - Using the data in the ASSIGNMENT table, write the...Ch. 7 - Prob. 23PCh. 7 - Write the SQL code to generate the total hours...Ch. 7 - Prob. 25PCh. 7 - Prob. 26PCh. 7 - Prob. 27PCh. 7 - Generate a listing of all purchases made by the...Ch. 7 - Using the output shown in Figure P7.29 as your...Ch. 7 - Prob. 30PCh. 7 - Prob. 31PCh. 7 - Use a query to compute the average purchase amount...Ch. 7 - Prob. 33PCh. 7 - Prob. 34PCh. 7 - Prob. 35PCh. 7 - Prob. 36PCh. 7 - Prob. 37PCh. 7 - Using the results of the query created in Problem...Ch. 7 - Create a query to find the balance characteristics...Ch. 7 - Prob. 40PCh. 7 - Prob. 41PCh. 7 - Prob. 42PCh. 7 - Prob. 43PCh. 7 - Prob. 44PCh. 7 - Write a query to display the SKU (stock keeping...Ch. 7 - Prob. 46PCh. 7 - Prob. 47PCh. 7 - Prob. 48PCh. 7 - Prob. 49PCh. 7 - Prob. 50PCh. 7 - Prob. 51PCh. 7 - Prob. 52PCh. 7 - Prob. 53PCh. 7 - Prob. 54PCh. 7 - Prob. 55PCh. 7 - Prob. 56PCh. 7 - Prob. 57PCh. 7 - Prob. 58PCh. 7 - Prob. 59PCh. 7 - Prob. 60PCh. 7 - Prob. 61PCh. 7 - Prob. 62PCh. 7 - Prob. 63PCh. 7 - Prob. 64PCh. 7 - Prob. 65PCh. 7 - Prob. 66PCh. 7 - Prob. 67PCh. 7 - Prob. 68PCh. 7 - Prob. 69PCh. 7 - Prob. 70PCh. 7 - Prob. 71PCh. 7 - Prob. 72PCh. 7 - Prob. 73PCh. 7 - Prob. 74PCh. 7 - Prob. 75PCh. 7 - Prob. 76PCh. 7 - Prob. 77PCh. 7 - Prob. 78PCh. 7 - Prob. 79PCh. 7 - Prob. 80PCh. 7 - Prob. 81PCh. 7 - Prob. 82PCh. 7 - Prob. 83PCh. 7 - Prob. 84PCh. 7 - Prob. 85PCh. 7 - Prob. 86PCh. 7 - Prob. 87PCh. 7 - Prob. 88PCh. 7 - Prob. 89PCh. 7 - Prob. 90PCh. 7 - Prob. 91PCh. 7 - Prob. 92PCh. 7 - Prob. 93PCh. 7 - Prob. 94PCh. 7 - Prob. 95PCh. 7 - Write the SQL code to create the table structures...Ch. 7 - The following tables provide a very small portion...Ch. 7 - Write the SQL command to save the rows inserted in...Ch. 7 - Write the SQL command to change the movie year for...Ch. 7 - Prob. 100CCh. 7 - Prob. 101CCh. 7 - Prob. 102CCh. 7 - Prob. 103CCh. 7 - Prob. 104CCh. 7 - Prob. 105CCh. 7 - Prob. 106CCh. 7 - Prob. 107CCh. 7 - Prob. 108CCh. 7 - Prob. 109CCh. 7 - Prob. 110CCh. 7 - Prob. 111CCh. 7 - Prob. 112CCh. 7 - Prob. 113CCh. 7 - Prob. 114CCh. 7 - Prob. 115CCh. 7 - Prob. 116CCh. 7 - Prob. 117CCh. 7 - Prob. 118CCh. 7 - Prob. 119CCh. 7 - Prob. 120CCh. 7 - Prob. 121CCh. 7 - Prob. 122CCh. 7 - Prob. 123CCh. 7 - Prob. 124CCh. 7 - Write a query to display the movie number, movie...
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem.)arrow_forwardWrite the SQL code that will restore the data to its original status: that is, the table should contain the data that existed before you made the changes in Problems 5 and 6.arrow_forwardProblem 1 and 2 are based on the following query: SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX FROM EMPLOYEE WHERE EMP_SEX = 'F' AND EMP_AREACODE = '615' ORDER BY EMP_LNAME, EMP_FNAME; What indexes should you create? Write the required SQL commands.arrow_forward
- Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. (You may assume that the table will be restored to its condition preceding this question.)arrow_forwardDelete the OWNER_INDEX 3 index from the OWNER table.arrow_forwardProblems 2224 are based on the following query: SELECT P_CODE, P_DESCRIPT, P_PRICE, P.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = 'NY' AND V_AREACODE = '212' ORDER BY P_PRICE; Write the commands required to create the indexes you recommended in Problem 22.arrow_forward
- Create a PL/SQL block to retrieve and display information for all employees. Display the employee ID, manager ID, date of hire, and if any of the employee territories is in the region "Southern". CREATE TABLE Employees ( EmployeeID NUMBER NOT NULL, LastName VARCHAR2(20) NOT NULL, FirstName VARCHAR2(10) NOT NULL, Title VARCHAR2(30), TitleOfCourtesy VARCHAR2(25), BirthDate DATE, HireDate DATE, Address VARCHAR2(60), City VARCHAR2(15), Region VARCHAR2(15), PostalCode VARCHAR2(10), Country VARCHAR2(15), HomePhone VARCHAR2(24), Extension VARCHAR2(4), Photo LONG RAW, Notes VARCHAR2(600), ReportsTo NUMBER, PhotoPath VARCHAR2(255), CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID), CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES Employees(EmployeeID) ) / CREATE TABLE EmployeeTerritories ( EmployeeID NUMBER NOT NULL, TerritoryID VARCHAR2(20) NOT NULL, CONSTRAINT PK_EmpTerritories PRIMARY KEY (EmployeeID, TerritoryID), CONSTRAINT FK_EmpTerri_Employees FOREIGN KEY (EmployeeID)…arrow_forwardCreate the employees table and populate the table with data (Script is provided here with) (a) Write a complete PL/SQL Cursor to retrieve the following information -Fetch row number, Employee Id, First Name of the employees - Select all the employees with Employee no <115 - Use the complete cycle of declaring, opening, fetching, and closing a cursor, including use of cursor attributes - Format the output as shown below pe Output Just FETCHED row 1- Employee ID: 110 - First an: Lauren Just FETCHED zow 2- Employee ID: 111 - First Sae: Liss Just FETCHED row 3- Employee ID: 112- Firet Same Deve Just FETCHED w 4- Eployee ID: 113- Fire Sae: Steve Just FETCHED zow - Exployee ID: 114-Fizet a: Marg (b) Modify your program to use the CURSOR FOR LOOP to achieve the same output Just FETCHED zow - Student ID: 110- First ane Lauren Just FETCHED zow 2- Student ID: 111- First ane Line Just FETCHED row 3- Student ID: 112- First an Deve Just FETCHED w 4-Student ID: 113- Firet Jane Steve Just FETCHED zow…arrow_forwarduse oracle sql developper or Oracle sql developper problem in picture the database CREATE TABLE MEMBERSHIP( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY,MEM_FNAME VARCHAR(30) NOT NULL,MEM_LNAME VARCHAR(30) NOT NULL,MEM_STREET VARCHAR(15),MEM_CITY VARCHAR(10),MEM_STATE CHAR(2),MEM_ZIP CHAR(5),MEM_BALANCE NUMBER (2)); ALTER TABLE MEMBERSHIPMODIFY MEM_STREET VARCHAR(25);CREATE TABLE RENTAL( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY,RENT_DATE DATE,MEM_NUM CHAR(3),CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP); CREATE TABLE PRICE(PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY,PRICE_DESC VARCHAR(20),PRICE_RENTFEE NUMBER (3,1),PRICE_DAILYATFEE NUMBER(3,1)); CREATE TABLE MOVIE(MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY,MOVIE_NAME VARCHAR(30) NOT NULL,MOVIE_YEAR CHAR(4),MOVIE_COST NUMBER(5,2),MOVIE_GENRE VARCHAR(15),PRICE_CODE CHAR(1),CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES…arrow_forward
- You are working with a database table that contains invoice data. The table includes columns for invoice_id and invoice_date. You want to remove duplicate entries for invoice_date. You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the invoice_date column.arrow_forwardWrite a simple PL/SQL function which has the following definition. Refer to the "EMP" below for appropriate column names CREATE TABLE emp ( empno number(4) CONSTRAINT pk_emp PRIMARY KEY, ename varchar2(10) NOT NULL, job varchar2(10), mgr number(4) referneces emp ON DELETE CASCADE, hiredate date, sal number(6), comm number(6), deptno number(3), CONSTRAINT fk_emp_deptno FOREIGN KEY (deptno) REFERENCE dept ON DELETE CASCADE) ------------------------------------------------------------------- Function name: Count_Emp Purpose: To count the number of managers from the EMP table Input parameter: employee job type ( is the employee an emp or mgr) Output parameter: None Input/Output parameters: None Return Value: an integer value representing the total number of managers. Precondition: None Postcondition: the employee table is unchanged. The output of the function is the number of managers in the emp table Use % TYPE where possible.arrow_forwardWrite Create Table SQL statements based on the above ERD. All tables must have primary keys. All tables must have appropriate foreign key constraints. Foreign keys must have the same name, datatype and size of the primary key that they refer to The following columns datatypes and sizes must be used: bid,authid / number(4) title,sname,fname / varchar2(30) sellingprice, payrate / number(6,2) Table Column Type Range author sname & fname Unique book title Not Null book sellingprice Check not negative allocation payrate Check 1 to 79.99arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SQL Basics for Beginners | Learn SQL | SQL Tutorial for Beginners | Edureka; Author: edureka;https://www.youtube.com/watch?v=zbMHLJ0dY4w;License: Standard YouTube License, CC-BY