Write the commands using SQL Developer following the instructions below. Copy an

Question

Write the commands using SQL Developer following the instructions below. Copy and paste your query code into the submission box. This assignment should be completed by July 25th..

Create a table called EMPLOYEES with the following column. (If you already have an EMPLOYEES table, drop it.)

SSN, a number with 9 digits

lastname, to hold text up to 25 characters in length

firstname, to hold text up to 25 characters in length

MI, a fixed length single character

hiredate, a date with a default value of SYSDATE

address, to hold text up to 24 characters in length

city, to hold text up to 20 characters and a default value of 'Columbia'

state, a fixed length character field with two characters with a default value of 'SC'

zip, a number with 5 digits

Drop the column MI from the EMPLOYEES table

Add a column middlename to the EMPLOYEES table with the same data type as firstname

Create a table called WRITERS with the first and last names of the authors and the titles of the books they wrote. Get the data for the table from the existing tables in the database.

Delete the table WRITERS from the database permanently.

Explanation / Answer

Create a table called EMPLOYEES with the following column. (If you already have an EMPLOYEES table, drop it.)

SSN, a number with 9 digits

lastname, to hold text up to 25 characters in length

firstname, to hold text up to 25 characters in length

MI, a fixed length single character

hiredate, a date with a default value of SYSDATE

address, to hold text up to 24 characters in length

city, to hold text up to 20 characters and a default value of 'Columbia'

state, a fixed length character field with two characters with a default value of 'SC'

zip, a number with 5 digits:

CREATE TABLE EMPLOYEES
(
SSN NUMBER(9),
lastname VARCHAR2(25),
firstname VARCHAR2(25),
MI char,
hiredate date default sysdate,
address VARCHAR2(24),
city VARCHAR2(20) default 'Columbia',
state CHAR(2) default 'SC',
zip NUMBER(5)
);

Note: for fix length character I have used char, for variable length character we use varchar2.

Here sysdate is used to generate defaut date, your system date.

==========================================================================

Drop the column MI from the EMPLOYEES table

ALTER TABLE EMPLOYEES
DROP COLUMN MI;

Note: for any existing table if we want to remove a particular column, we have to use alter statement as shown above.

=============================================================================

Add a column middlename to the EMPLOYEES table with the same data type as firstname

ALTER TABLE EMPLOYEES
ADD(middlename VARCHAR2(25));

Note: Again, we need to add a new column in our table hence we are using alter statement.

=============================================================================

Create a table called WRITERS with the first and last names of the authors and the titles of the books they wrote. Get the data for the table from the existing tables in the database.

if you need to create table and add the records from the exisitng table then we will construct our query as:

CREATE TABLE WRITERS AS SELECT firstname,lastname,title
FROM authors;

_________________________________________________________________________________

if we just want to create a table the the following query will work.

CREATE TABLE WRITERS

(

firstname varchar2(25),

lastname varchar2(25),

title varchar2(50)

);

=============================================================================

Delete the table WRITERS from the database permanently

DROP TABLE WRITERS;

inorder to delete a table permanently we use the above drop statement

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.