Consider the following PL/SQL script: SET SERVEROUTPUT ON BEGIN INSERT INTO COURSE (course_no, description, created_by, created-date, modified_by, modified_date) VALUES (course_no_seq.nextval, ‘database development’, USER, SYSDATE); COMMIT DBMS_OUTPUT.PUT_LINE (‘One course has been added’); END; Assumptions: All the COURSE table fields have check constraints defined on them indicating that none can be null. Explain why the script does not execute successfully. Add a user-defined exception to the script, so that the error generated by the INSERT statement is handled. Assign the code 2290 to this exception

Consider the following PL/SQL script: SET SERVEROUTPUT ON BEGIN INSERT INTO COURSE (course_no, description, created_by, created-date, modified_by, modified_date) VALUES (course_no_seq.nextval, ‘database development’, USER, SYSDATE); COMMIT DBMS_OUTPUT.PUT_LINE (‘One course has been added’); END; Assumptions: All the COURSE table fields have check constraints defined on them indicating that none can be null. Explain why the script does not execute successfully. Add a user-defined exception to the script, so that the error generated by the INSERT statement is handled. Assign the code 2290 to this exception

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter3: Creating Tables

Section: Chapter Questions

Problem 4CAT

See similar textbooks

Similar questions

In Sql These are some database from a set of tables insert into AccountHolder(AccountID, CustomerID, AccountType)values (55003, 115, 'Savings'),(44001, 101, 'Chequing'),(55006, 109, 'Savings'),(44002, 102, 'Chequing'),(44003, 113, 'Chequing'),(55007, 108, 'Savings'),(44004, 103, 'Chequing'),(44005, 110, 'Chequing'),(55005, 112, 'Savings'),(44006, 114, 'Chequing'),(55001, 111, 'Savings'),(44007, 107, 'Chequing'),(44008, 104, 'Chequing'),(55004, 106, 'Savings'),(44009, 105, 'Chequing'),(44010, 113, 'Chequing'),(55002, 107, 'Savings'),(44011, 110, 'Chequing'),(44012, 104, 'Chequing') select * from AccountHolder insert into Branch(LocationID, BranchName, Address, Type, DepositSum, LoanSum)values (1, 'Boulevard Branch', '3268 Wade Boulevard', 'Rural', 171848.88, 37681.72),(2, 'Avenue Branch', '4814 Dare Avenue', 'Rural', 232707.42, 68324.56),(3, 'Street Branch', '208 Ferry Street', 'Commercial', 375010.75, 44665.08),(4, 'Road Branch', '468 Zulauf Road', 'Commercial', 496001.41,…
Using the film database you have, answer the following queries in your schema then write them down in this paper. Question two: a) Convert the following for-cursor into: open, fetch, close one: DECLARE Cursor c1 is select table_name from user_tables; BEGIN FOR tables IN c1 LOOP DBMS_OUT.PUT_LINE(tables.table_name); END LOOP; END;
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
The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.
One of the TAL Distributors database's denormalized tables is as follows: The Customer table is no longer in third normal form due to which column or fields (CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum, RepName)? In this example, what is the denormalized table's normal form?
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.
Make at least 10 sql statements from the table script given (try to make a little complex script) .please make scripts as related to given categories below:(script to make table is given below) create database kapil; create table Books(Book_id varchar (30)NOT NULL Primary key,Book_name varchar (80),Edition_no varchar (20),category_835757 varchar(20),price decimal(5,2));INSERT into booksvalues (1,'Specs','12th','Philosophy',35.11);INSERT into booksvalues (2,'Oxford','25th','Dictionary',20.12);INSERT into booksvalues (3,'Revolution','9th','Novel',10.15);INSERT into booksvalues (4,'Ghost in wires','8th','Crime',13.52);INSERT into booksvalues (5,'Burned','4th','Encyclopedia',15.22); create table Reader(Userid_757 varchar(50) NOt NULL Primary key,Last_name varchar(100),First_name varchar (200),Phone varchar(100),Email varchar(200),Book_id int);INsert into readervalues('U513','Mahara','Kapil',647-555-1151,'Phantomconflicted.com',003);INSERT into readervalues…
use 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…
Consider the following database tables. Employee (empNo, empName, street, city) Works (empNo, companyName, joiningDate, salary, deptNo) Employee salary (empNo, basic, total_deduction, net_salary, gross_salary) Note that empNo is generated using a sequence Write a PL/SQL code to calculate and then print the total gross salary of first 10 records in Employee table.
You will need to add a CREATE DATABASE and USE database statement at the beginning of the script in the file of the Sample Database in order for SQL Script in the Sample Database to execute/run correctly If you discover any ambiguity between the written Metadata and the actual table definitions, the path that I recommend is to use the SHOW TABLES Statement and the DESCRIBE Table Statement to eliminate any ambiguity between the written Metadata and the actual Metadata. CustOrders.sql download Aggregation Part of the Exercise . Select the maximum price of any orders in the items_ordered table. Hint: Select the maximum price only. . Select the average price of all of the orders that were purchased in the month of Dec. . Return the total number of rows in the orders table? . For all of the tents that were ordered in the orders table, what is the price of the lowest tent? Hint: Your query should return the price only. Group By Exercises . How many people are…
The main aim of your project is to create a database schema consists of 3 tables suggested by yourself and simulate it through a graphical application program: Part 1: SQL By using sql-plus environment create your database tables: 1- Your tables must be relationally complete. 2- Make three Sequences (One for each table). 3- Make any Materialized view. 4- Insert some records to your tables. (use the created sequences to generate numbers to be inserted into a fields of your tables).
Blank 1 Add your answer Question 18 Examine this executable section of a PL/SQL block: BEGIN FOR worker_record IN pay_cursor LOOP Worker id table(worker_id):= Worker record.last_name; END LOOP; CLOSE salary_cursor; END; Why does this section cause an error? Use the editor to format your answer