1. Create the table MEMBER with the specifications listed below: Column Name MEM
ID: 638082 • Letter: 1
Question
1. Create the table MEMBER with the specifications listed below:
Column
Name
MEMBER
_ID
LAST_
NAME
FIRST_
NAME
ADDRESS
CITY
PHONE
JOIN_
DATE
Data Type
NUMBER
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
DATE
Length
10
25
25
50
30
15
Create the table as specified above.
Run the SQL describe member; and display the results.
SQL to modify MEMBER_ID to add a Not Null Constraint.
Run the SQL describe member; and display the results.
SQL to alter MEMBER_ID to be the primary key for the table.
SQL to modify ADDRESS to increase the field length from 50 to 100.
Run the SQL drop table member;
USE SQL DDL to create the table, NOT the table creation wizard.
Column
Name
MEMBER
_ID
LAST_
NAME
FIRST_
NAME
ADDRESS
CITY
PHONE
JOIN_
DATE
Data Type
NUMBER
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
DATE
Length
10
25
25
50
30
15
Explanation / Answer
1)
The CREATE statement is used to create a table.
CREATE TABLE member
(
MEMBER_ID number(10,
LAST_NAME varchar2(25),
FIRST_NAME varchar2(25),
ADDRESS varchar2(50),
CITY varchar2(30),
PHONE varchar2(15),
JOIN_DATE
);
After executing the query, the following message is displayed.
Table created.
If the table is not created due to an error, it displays the details of the error.
The desc command is used to display the information about the columns of the created table.
desc member;
2)
To change the structure of the table, the ALTER statement is used.
ALTER TABLE member
MODIFY COLUMN MEMBER_ID number(10) NOT NULL;
The desc command is used to display the information about the columns of the created table.
desc member;
3)
To create a primary key for the existing table, the SQL query should be in the following form:
ALTER TABLE members
ADD PRIMARY KEY (MEMBER_ID);
The desc command is used to display the information about the columns of the created table.
desc member;
4)
ALTER TABLE member
MODIFY COLUMN ADDRESS varchar2(100);
To display the results, the following query should be used:
desc member;
5)
The DROP TABLE statement is used to delete the table.
To drop a table, the following query should be used:
DROP TABLE member;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.