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

Related questions

Q: (d) Descendants by gender: For all persons who have a descendant, compute the number of descendants…

A: COUNT The number of rows that match a certain criterion are returned by the COUNT() function. AVG…

Q: The DBA LOBS Dictionary view provides a description of LOBS contained in all tables. What TABLE NAME…

A: Hey there, I am writing the required solution of the questin mentioned above. Please do find the…

Q: Procedure Name: Update_Emp_Job Purpose: To update an employee to a new job Input Parameters: 1)…

A: to create an Update stored procedure by inserting a UPDATE SQL query into the procedure itself.

Q: How does the CONCAT_WS function provide advantages over the traditional CONCAT function in SQL?

A: CONCAT_WS function is an SQL function that is used to add two or more strings together with the…

Q: How does the COALESCE function compare with the ISNULL function when handling NULL values in SQL

A: The COALESCE and ISNULL functions are both used to handle NULL values in SQL. However, they have…

Q: this is a database table Create table SPORTSCARS{ S_id int not null, S_YEAR NUMBER(4,0), S_Make…

A: Task : Given the SQL query. The task is to convert it into XML and XSL.

Q: Task 6: Add the following record to the OWNER table: INSERT INTO OWNER VALUES('SA100', 'Sam',…

A: Attached the Procedure to add the following record to the OWNER table: INSERT INTO OWNER…

Q: Using the corrected code from Step 28, include the Select into a temporary table named temptable.…

A: The query in the step 28 results in a table of values. Now,this table has to be stored in a…

Q: Create an Oracle sequence to generate values for volunteer numbers. The sequence should startwith…

A: sequence is used to generate a unique value in a primary key column. After creating sequence…

Q: Write a select statement for the following: Tables sec1313_words1 and sec1313_words2 contains…

A: Question from Mysql:

Q: plz help fix the following use oracle plz create table volunteer( Vol_ID int NOT NULL, name…

A: Problem: Fixing the given query in Oracle.

Q: The Car Maintenance team also wants to store the actual maintenance operations in the database. The…

A: DELETE CASCADE: - When we constitute a foreign key using this option, it deletes the referencing…

Q: Write a query to create a report that displays the count of the number of tables in the SQ library.…

A: The question is to write SQL statement for the given query.

Q: Create a table named 'EMPLOYEE' with the required fields (empno int, first_name varchar(16),…

A: I have provided a detailed answer in the Explanation section.Explanation:Here are the SQL queries to…

Q: Alter the given table by adding new column of "Job" in it and assign the sequence values to the…

A: Objective: Given a relation that stores record about employees along with attributes EMPLOYEE_ID,…

Q: CREATE TABLE sales ( "year SMALLINT NOT NULL, "quarter' TINYINT NOT NULL, region VARCHAR (50) NOT…

A: CREATE TABLE sales( 'year' SMALLINT NOT NULL, 'quarter' TINYINT NOT NULL, region varchar(50) NOT…

Q: i am still receiving the following error ERROR 1072 (42000) at line 9: Key column 'phone_id'…

A: Answer for the issue has been given below with explanation

Q: Refer to the CREATE TABLE statement: CREATE TABLE Country ( ); Name VARCHAR (50), Population INT,…

A: The question is asking to complete an SQL INSERT INTO statement to add a new row into the 'Country'…

Q: What advantages does the ISNULL function offer when dealing with missing data in SQL?

A: In the database management , handling NULL values is a challenge that SQL developers encounter…

Q: ULL, Title VARCHAR2(30), TitleOfCourtesy VARCHAR2(25), BirthDate DATE, HireDate DATE, Address…

A: Dear Student, The required query with explanation is given below -

Q: CREATE TABLE sales ( SalesNumber INT(10) AUTO_INCREMENT PRIMARY KEY, SalesDate DATE,…

A: An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as…

Q: Write a procedure named prc_cust_add to add a new customer to the CUST_MYSQL table. Use the…

A: Note: - Assuming the table CUST_MYSQL is empty because the database is not provided. To create a…

Concept explainers

Basic SQL Queries

Structured Query Language is the full form of SQL. It is a high-level language used to communicate with a relational database. It is used for creating, storing and accessing contents of a relational database. SQL is used to write the queries which are executed using database engines like Oracle, SQL Server and so forth.

Question

100%

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:**
```sql
CREATE 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.

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps with 6 images

SEE SOLUTION Check out a sample Q&A here

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

Why does the order of the operands (tables) matter in an EXCEPT (MINUS) query but not in a UNION query?
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.
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).
List all products with their highest unit price, i.e. not discounted.the database is
Correct Query from table given.
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
To obtain the structure of an Oracle table, the command to use is: a) STRUCTURE [TableName]. b) DESCRIBE [TableName]. c) DESCRIBE STRUCTURE [TableName]. d) DESC TABLE [TableName].
databse programming
which type of joins is not supported by SQL Inner joins Right Joins Full Joins left joins
I am trying to get rid of duplicate rows in SQL. This is the current code I'm using. Please help me to write a code to create a new table without the duplicates. Select EmployeeID, Username, FirstName, LastName, RoleName, RoleDescription, Count(*) as '# of Duplicates' From [dbo].[vw_MyAccessInfo] Group by EmployeeID, Username, FirstName, LastName, RoleName,RoleDescription Having Count(*) > 1 Select * from [dbo].[vw_MyAccessInfo] Select distinct * from [dbo].[vw_MyAccessInfo]
A summarize table can be edited in Transform Data? True False
Hi triggers Update and using Xampp and this table: CREATE TABLE MEMBER(STUDENT_ID CHAR(6) NOT NULL,CLUB_CODE VARCHAR(4) NOT NULL,MEMBER_DATE DATE NOT NULL,MEMBER_POSITION VARCHAR(15) NOT NULL,MEMBER_ATTENDANCE CHAR(2) NOT NULL,PRIMARY KEY (STUDENT_ID,CLUB_CODE),FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT (STUDENT_ID) ON DELETE RESTRICT ON UPDATE CASCADE,FOREIGN KEY (CLUB_CODE) REFERENCES CLUB (CLUB_CODE) ON DELETE RESTRICT ON UPDATE CASCADE); INSERT INTO MEMBER VALUES('100027','C201','2020-03-25','Secretary','12'),('100027','C202','2020-04-11','Member','11'),('100032','C201','2020-02-23','President','12'),('100050','C201','2020-03-25','Member','10'),('100132','C202','2020-04-11','Treasurer','10'),('100074','C203','2020-04-09','President','12'),('100074','C204','2020-04-25','Treasurer','12'),('100051','C204','2020-04-25','Member','9'),('100064','C207','2020-04-25','Vice…