CREATE TABLE store_reps( rep_ID NUMBER(5), last VARCHAR2(15), first VARCHAR2(10), comm CHAR(1)); **SQL Command Modification and Table Creation****Objective:** Modify the SQL command and create a new table with specific requirements.**SQL Command:**```sqlCREATE TABLE store_reps ( rep_ID NUMBER(5), last VARCHAR2(15), first VARCHAR2(10), comm CHAR(1));```**Modifications Required:**1. **Primary Key:** Make `rep_ID` the primary key.2. **Default Value:** Assign a default value 'Y' to the `comm` column.3. **Value Restriction:** Restrict `comm` column to accept only 'Y' or 'N'.**New Table Creation:** - **Table Name:** `REP_CONTRACTS`**Columns and Requirements:**- **name:** A character field that can contain up to 15 characters.- **quarter:** A character field that can contain up to 3 characters.- **rep_ID:** A number field that can contain integers up to 5 digits.**Keys and Constraints:**- **Composite Primary Key:** Consist of `rep_ID` and `quarter`.- **Foreign Key Constraint:** Link the two tables using `rep_ID`.Use these guidelines to ensure accuracy and maintain data integrity across the database tables.

Solution Making rep_ID primary key assigning Y as the default value to the comm column. CREATE…

Modify the following SQL command to make the changes below. CREATE TABLE store_reps ( rep_ID NUMBER(5), last VARCHAR2(15), first VARCHAR2(10), comm CHAR(1) ); • make the rep_ID primary key. • assign a default value Y to the comm column. • make the comm column to accept only Y or N. Create a new table named REP_CONTRACTS containing the columns listed below. Make a composite primary key including the rep_ID and quarter. In addition, create a foreign key constraint to link two tables. • name: A character field that can contain up to 15 characters. • quarter: A character field that can contain up to 3 characters. • rep_ID: A number field that can contains integers up to 5 digits.

Modify the following SQL command to make the changes below. CREATE TABLE store_reps ( rep_ID NUMBER(5), last VARCHAR2(15), first VARCHAR2(10), comm CHAR(1) ); • make the rep_ID primary key. • assign a default value Y to the comm column. • make the comm column to accept only Y or N. Create a new table named REP_CONTRACTS containing the columns listed below. Make a composite primary key including the rep_ID and quarter. In addition, create a foreign key constraint to link two tables. • name: A character field that can contain up to 15 characters. • quarter: A character field that can contain up to 3 characters. • rep_ID: A number field that can contains integers up to 5 digits.

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

CREATE TABLE test( test_id INTEGER PRIMARY KEY, test_name varchar2 (255) ,t_date DATE, t_result varchar2(255));ALTER TABLE test ADD (t_time varchar2(1));INSERT INTO test values ('5454','corona','monday','11:15PM','positive'); (6567,'corona','sunday','10:50AM','negative');
1. a) Create a table bank_master Column Name Datatype Varchar2(4) Varchar2(30) Varchar2(30) Number (7) b) Insert the records using the following table values Constraint accountno Not Null name address amount accountno name Address amount 101 Mohamed Burka 9000 102 Nasser Nizwa 8000 103 Khamiz Sohar 8200 104 Murshid Burka 7800 106 Mustasim Sohar 9500 a) Create a table bank_details Column Name Datatype Varchar2(4) number(4) date Constraint Recurrent_acno Not Null ac_no Transaction_date Fixed_deposit Number(8) b) Insert the records using the following table values Recurrent_acno | ac_no Transaction_date Fixed_deposit С101 101 12.05.2002 10000 C102 102 13.01.2020 80000 C103 103 14.02.2021 50000 C101 104 13.05.2020 40000 C102 105 15.06.2019 null
Why does the order of the operands (tables) matter in an EXCEPT (MINUS) query but not in a UNION query?
CREATE TABLE DOCTOR(DOC_ID NUMBER(3),DOC_NAME VARCHAR2(9),DATEHIRED DATE,SALPERMON NUMBER(12),AREA VARCHAR2(20),SUPERVISOR_ID NUMBER(3),CHGPERAPPT NUMBER(3),ANNUAL_BONUS NUMBER(5),CONSTRAINT DOCTOR_DOC_ID_PK PRIMARY KEY (DOC_ID)); INSERT INTO DOCTOR VALUES(432, 'Harrison' , TO_DATE('05-DEC-94'), 12000,'Pediatrics', 100, 75, 4500); INSERT INTO DOCTOR VALUES(509, 'Vester' , TO_DATE('09-JAN-00'), 8100,'Pediatrics', 432, 40, null); INSERT INTO DOCTOR VALUES(389, 'Lewis' , TO_DATE('21-JAN-96'), 10000, 'Pediatrics', 432, 40, 2250); INSERT INTO DOCTOR VALUES(504, 'Cotner' , TO_DATE('16-JUN-98'), 11500,'Neurology', 289, 85, 7500); INSERT INTO DOCTOR VALUES(235, 'Smith' , TO_DATE('22-JUN-98'), 4550, 'Family Practice', 100, 25, 2250); INSERT INTO DOCTOR VALUES(356, 'James' , TO_DATE('01-AUG-98'), 7950,'Neurology', 289, 80, 6500); INSERT INTO DOCTOR VALUES(558, 'James' , TO_DATE('02-MAY-95'), 9800, 'Orthopedics', 876, 85, 7700); INSERT INTO DOCTOR VALUES(876, 'Robertson' , TO_DATE('02-MAR-95'),…
Alter the given table by adding new column of "Job" in it and assign the sequence values to the column AS WELL. EMPLOYEE ID LAST NAME 149 Zotkey 174 Abel 176 Taylor ANNSAL HIRE_DATE 126000 29-JAN-00 132000 11-MAY-96 103200 24-MAR-98 NOTE: ASSUME 100 RECORDS IN THE TABLE. YOU NEED TO CREATE PROCEDURE FOR IT. USE ANY LOOP TO FILL THE ENTERIES ALONG WITH DML OPERATION.
Using MySQL, create a table named CUST_MYSQL with the same fields as in Problem 16, except, use the AUTO_INCREMENT feature for the CUST_NUM field.
Develop recurrence relations for the last 3 columns of table 1. Use the recurrence relation you developed to complete the table for these months: • Month 59 • Month 60 • Month 61 • Month119 • Month 120 • Month 121 Show your calculations and completed table.
I need the output of the queries create table Accounts (account_no number primary key, client_name varchar2(200),Gender varchar2(20),address Varchar2(200)); create table loans (branch_id number, bank_name varchar2(200),Loan number, loan_Status varchar2(200),account_no number references Accounts(account_no) ON DELETE CASCADE); 1. delete from accounts where account_no= 101; 2. select * from accounts a where exists (select 1 from loans l where l.account_no= a.account_no) 3. select * from accounts a where exists (select 1 from loans l where l.account_no= a.account_no and status = 'accepted') 4. create or replace view view1 as select client_name , address,l.bank_name,l.loan_Status from accounts a, loans l where a.account_no = l.account_no Step 2 5.create or replace view view2 as select client_name , address,l.bank_name,l.loan_Status from accounts a, loans l where a.account_no = l.account_no and address = 'Karachi' 6. create or replace view view3 as select client_name ,…
Issue a SELECT statement that displays NEXTVAL for MY_FIRST_SEQ three times. Because the value isn’t being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the third SELECT?(refer to the tables in the JustLee Books database).
Order of operands matters in EXCEPT (MINUS) queries but not in UNION queries?
List all products with their highest unit price, i.e. not discounted.the database is
Create a new table maned “Hiking” that includes TripID, NumPersons from Reservation table and Trip Type, Trip Name columns from Trip table where Trip type is “Hiking” .