2. Create a table named sizeTvpe with the following attributes, data types, and constraints: a. ID, integer, 7 characters, not null, auto increment b. size, variable character, 20 characters, not null c. primary key is the ID field 3. Update table product, row with description as "tiramisu classico" so the size = '3 ounce' versus '3 oz 4. Insert into table şizeType, column size the distinct character portion of table product, column size using the SUBSTRING_INDEX function, the arguments passed should be column size, an explicit space and the value -1. Example SUBSTRING INDEX(,'", -1). 5. Alter table product, add column şizeTypeld as integer, 7 characters, not null 6. Update table product, set column şizeTypeld equal to the corresponding size in table sizeType In my sizeType table I have the following: 1 ounce | 2| pound 13 count

2. Create a table named sizeTvpe with the following attributes, data types, and constraints: a. ID, integer, 7 characters, not null, auto increment b. size, variable character, 20 characters, not null c. primary key is the ID field 3. Update table product, row with description as "tiramisu classico" so the size = '3 ounce' versus '3 oz 4. Insert into table şizeType, column size the distinct character portion of table product, column size using the SUBSTRING_INDEX function, the arguments passed should be column size, an explicit space and the value -1. Example SUBSTRING INDEX(,'", -1). 5. Alter table product, add column şizeTypeld as integer, 7 characters, not null 6. Update table product, set column şizeTypeld equal to the corresponding size in table sizeType In my sizeType table I have the following: 1 ounce | 2| pound 13 count

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter6: Additional Database Objects

Section: Chapter Questions

Problem 6HOA: A new table has been requested to support tracking automated emails sent to customers. Create the...

See similar textbooks

Similar questions

Create a new table containing these four columns from the existing BOOKS table: ISBN, Cost, Retail, and Category. The name of the ISBN column should be ID, and the other columns should keep their original names. Name the new table BOOK_PRICING.
Create a new table containing these four columns: Emp#, Lastname, Firstname, and Job_class. The table name should be EMPLOYEES. The Job_class column should be able to store character strings up to a maximum length of four, but the column values shouldn’t be padded if the value has less than four characters. The Emp# column contains a numeric ID and should allow a five-digit number. Use column sizes you consider suitable for the Firstname and Lastname columns.
Create a new table containing the category code and description for the categories of books sold by JustLee Books. The table should be called CATEGORY, and the columns should be CatCode and CatDesc. The CatCode column should store a maximum of 2 characters, and the CatDesc column should store a maximum of 10 characters.
SQL: Add a table-level PRIMARY KEY constraint to the EMPLOYEE table using the ID column. The constraint should be named at creation. (Which means you'll need to drop the table, then recreate.) Create a PRIMARY KEY constraint on the DEPARTMENT table using the ID column. The constraint should be named at creation. (Which means you'll need to drop the table, then recreate.) Add a foreign key reference on the EMPLOYEE table that will ensure that the employee is not assigned to a nonexistent department. Confirm that the constraints were added by querying USER_CONSTRAINTS. Note the types and names of the constraints and include them in your statement comments. Display the object names and types from the USER_OBJECTS data dictionary view for EMPLOYEE and DEPARTMENT tables. You will want to format the columns for readability. Provide comments that include the new tables and indexes created. Modify the EMPLOYEE table by adding a SALARY column of NUMBER data type, precision 7. Confirm…
1. Create the My Employee table with the following fields: Not Null ID Last Name First Name Userid Salary 1 2 3 4 5 2. Add the constraint Primary Key (ID) to the table. 3. Insert the following data to the table: ID Last Name First Name Ali Number(4) Varchar(25) Varchar(25) Varchar(8) Number(9,2) AlChameri Alanizi Hejazi Turki AlKamel Rayen Decham Mohamed Fahhed Userid raptel bdancs bbiri cnewman aropebur Salary 795 860 1100 750 1550 I 4. Select all the data in the table. 5. Change the Last Name of employee 4 to Turkish. 6. Change the Salary to 1000 for all employees with a salary less than 900
CREATE TABLE PRODUCT (PRODUCT_ID INTEGER PRIMARY KEY, "NAME" TEXT NOT NULL, PRICE REAL NOT NULL); CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER PRIMARY KEY, "NAME" TEXT NOT NULL); CREATE TABLE PRODUCT_CATEGORY (PRODUCT_ID INTEGER, CATEGORY_ID INTEGER, PRIMARY KEY (PRODUCT_ID, CATEGORY_ID), FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID), FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY (CATEGORY_ID)); Please write a SQL SELECT statement (a query) which will return the PRODUCT_ID and PRICE of the second highest priced product for *every category*. If there is a tie for the second highest price in a category, all of the tied products should be returned. The columns in your query results should be: CATEGORY_ID, PRODUCT_ID, PRICE
Two tables are created: Horse with columns: ID - integer, primary key RegisteredName - variable-length string Student with columns: ID - integer, primary key FirstName - variable-length string LastName - variable-length string Create the LessonSchedule table with columns: HorseID - integer with range 0 to 65 thousand, not NULL, foreign key references Horse(ID) StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID) LessonDateTime - date/time, not NULL Primary key is (HorseID, LessonDateTime) If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically. If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically. Notes: Table and column names are case sensitive in the auto-grader. Your SQL code does not display any results in Develop mode. Use Submit mode to test your code.
Two tables are created: Horse with columns: ID - integer, primary key RegisteredName - variable-length string Student with columns: ID - integer, primary key FirstName - variable-length string LastName - variable-length string Create the LessonSchedule table with columns: HorseID - integer with range 0 to 65 thousand, not NULL, foreign key references Horse(ID) StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID) LessonDateTime - date/time, not NULL Primary key is (HorseID, LessonDateTime) If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically. If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.
Assignment 1: 1. Create table customer with four attributes number, name, address and telephone number. Each customer has a unique number and the name can be up to 14 characters. 2. Add a new attribute ssn to this table. Each customer must have an ssn and it is unique. 3. Redefine the column address to become fixed length with size=20. 4. Drop the column telephone number 5. Rename the table customer to customer_new. 6. Add a birth date attribute. All customer should be born after Dec 10 2000.
Q1: Create the following related tables using appropriate data field specification: (Hint: Define all constraints in column level) Products Product Id Product Name Customers Customer Id Customer_Name Pro_id Q2: Modify the Products table to add a new field with name "Product date" which Contains a unique constraint. (Hint: solve this question by one command only). Q3: Modify the Products table to remove the unique constraint which added in Q2. Notes: Write the question number for each answer. Save your answers on notepad as .txt file and upload it on Elearning. Good Luck
1. ID Last Name First Name Create the My Employee table with the following fields: Not Null Number(4) Varchar(25) Varchar(25) Varchar(8) Number(9,2) Userid Salary 2. Add the constraint Primary Key (ID) to the table. 3. Insert the following data to the table: ID Last Name First Name Ali 1 2 3 4 5 AlChameri Alanizi Hejazi Turki AlKamel Rayen Decham Mohamed Fahhed 4. Select all the data in the table. Userid raptel bdancs bbiri cnewman aropebur I Salary 795 860 1100 750 1550
A new table has been requested to support tracking automated emails sent to customers. Create the table and add data as described below.• Tablename: email_log• Columns: emailid (numeric), emaildate (datetime), customer# (numeric)• Primary key: emailid column, define as an Identity Column• Add the following data rows and display resulting rows (if any errors occur, explain why the error is expected)1. Emaildate ¼ current date, customer# ¼ 10072. Emailid ¼ specify to use the column default value, emaildate ¼ current date, customer# ¼ 10083. Emailid ¼ 25, emaildate ¼ current date, customer# ¼ 1009 (refer to the tables in the JustLee Books database).