SECTION I In this section, assume you have received a SR (Service Request) from
ID: 3872360 • Letter: S
Question
SECTION I
In this section, assume you have received a SR (Service Request) from your manager or developers on your team to create some tables for a project you are working on. Extract information from the details below to create tables.
SR1: Write SQL DDL commands to create a table called FIRST_LAB
TABLE NAME: FIRST_LAB
Column Name
Data Type
Bytes
ID
NUMBER
10
NAME
VARCHAR2
200
ADDRESS
VARCHAR2
400
TELEPHONE
VARCHAR2
10
USERNAME
VARCHAR2
24
SR2: Write SQL DDL commands to create a table called TAX_TAB
TABLE NAME: TAX_TAB
Column name
Data Type
Bytes
EMPNO
NUMBER
5
TAX_RATE
NUMBER
10, 2
MARRITAL_SATUS
CHAR
1
SR3: Write SQL DDL commands to create a table called TAX_TAB
TABLE NAME: EMP_TAB:
Column name
Data Type
Bytes
Constraint
EMPNO
INTEGER
10
NOT NULL
NAME
VARCHAR2
200
NOT NULL
SAL
NUMBERL
10,3
NOT NUL
FYI: Getting Information about Your Database
Oracle keeps meta-data about your database in some system tables called the database dictionary. One of such handy data dictionary tables is called USER_TABLES, which tracks the tables in your schema:
SQL> select table_name from user_tables;
COMMENT:
table_name is a column in a data dictionary table called user_tables. We did not create it. It was created when the database was created.
SECTION II
DDL: CREATE, ALTER, DROP, ADD, MODIFY, RENAME, TRUNCATE
DML: DELETE, UPDATE, INSERT and sometimes SELECT
DCL: GRANT, REVOLK
QUERY: SELECT
NOTE: A Primary Key uniquely identifies a record. A Primary Key is by default a NOT NULL, an index is created and cannot hold duplicate values.
FOLLOW INSTRUCTIONS CAREFULLY
DBA:
Below, you will see 3 blocks of SQL DDL commands. The SQL commands have some minor errors. Study the commands carefully, correct the errors and run them to create the three tables
1: CHAVERAN_EMPLOYEE
2: AMAZON_CUSTOMERS
3: CUSTOMER_ORDERS
QUESTION:
What error did you receive during the creation of the table CUSTOMER_ORDERS? HINT: Before you can reference a table, that table MUST have a primary or unique key. Any idea why you have the error now? Fix it with an ALTER TABLE command. See example in LESSON 4, page 2
FOLLOW INSTRUCTIONS CAREFULLY
Creating TABLES and CONSTRAINTS
You receive email from the development team to create a table as seen below:
The Request:
Write SQL DDL commands to create the tables below.
TABLE NAME: PAY_TAB
Column name
Data Type/Bytes
Constraint Name and Type
Null?
EMPID
NUMBER(7)
pk_paytab - PRIMARY KEY
FNAME
VARCHAR2(25)
LNAME
VARCHAR2(25)
PAY_PERIOD
DATE
TABLE NAME: LAB_DEPT
Column name
Data Type/Bytes
Constraint Name and Type
Null?
DEPTID
NUMBER(7)
pk_labdept - PRIMARY KEY
NAME
VARCHAR2(25)
LOCATION
VARCHAR2(25)
NOT NULL
TABLE NAME: LAB_EMP
Column name
Data Type/Bytes
Constraint Name and Type
Null?
EMPNO
NUMBER(7)
NOT NULL
LAST_NAME
VARCHAR2(25)
FIRST_NAME
VARCHAR2(25)
DEPTID
NUMBER(7)
fk_labemp - FOREIGN KEY
Referencing table LAB_DEPT
TESTING your REFERENTIAL INTEGRITY between LAB_DEPT and LAB_EMP
When you perform a select statement, you should see the output below.
PAUSE! We will be creating more tables. But first, verify the tables you now have in your schema by running the SQL commands below:
SQL> select * from tab; OR
SQL> select table_name from user_tables;
SR: Look at the physical structure of the BANK_ACCOUNTS table below. The ACCOUNT_TYPE table will store data about the type of account..
TABLE NAME: BANK_ACCOUNTS
Column name
Data Type/Bytes
Constraint Name and Type
Null?
ACCOUNT_ID
NUMBER(30)
pk_bkaccts - PRIMARY K
BANK_NAME
VARCHAR2(30)
NOT NULL
ST_ADDRESS
VARCHAR2(30)
NOT NULL
VARCHAR2(15)
NOT NULL
CHAR(2)
NOT NULL
NUMBER(5)
NOT NULL
TABLE NAME: ACCOUNT_TYPE
Column name
Data Type/Bytes
Constraint Name and Type
Null?
ACCOUNT_ID
NUMBER(30)
fk_accttype - FOREIGN K
CHAR(30)
CHAR(30)
CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column to allow only certain values for that particular column.
SR:
Look at the request below. Write SQL DDL commands to create the table PERSONNEL and define a CHECK constraint on the "P_ID" column. The CHECK constraint should specify that the column "P_ID" must include only integers greater than 0.
HINT: CHECK (P_ID>0)
TABLE NAME: PERSONNEL
Column name
Data Type/Bytes
Constraint Name and Type
Null?
P_ID
INTEGER(20)
ck_pid - CHECK Constraint
VARCHAR2(55)
VARCHAR2(55)
VARCHAR2(100)
VARCHAR2(70)
NOTE: Test the table by inserting a record with the value for the P_ID column as 0
SR:
Write SQL DDL commands to create the table ORDER_STATUS and define the ORDER_STATUS_ID field as DEFAULT with value ‘Order Placed’.
HINT: DEFAULT 'Order placed' NOT NULL
TABLE NAME: ORDER_STATUS
Column name
Data Type/Bytes
Special Instructions
Null?
ORDER_STATUS_ID
INTEGER
VARCHAR2(20)
DEFAULT ‘Order Placed’
VARCHAR2(55)
VARCHAR2(100)
VARCHAR2(70)
NOTE: Test the table by inserting the record below.
insert into order_status (order_status_id) values (2 );
SQL> select * from order_status;
REQUEST:
A new developer has just been hired and works with you, a skilled DBA at Imo Tech Solutions The developer has sent you the DDLs below to create tables. Study the SQL commands very well.
QUESTION:
What is wrong with the SQL statement the developer sent to you?
CONGRATULATIONS on your FIRST LAB
HOMEWORK: Continue this part on your Home Database
Study the ERD on page 7 and implement it physically in your database.
Each table has its corresponding fields/columns, for example the STUDENT table has Name, StudentNumber, Class, Major Fields
THINGS TO DO:
Identify the primary key and foreign keys in each relation by yourself
Write appropriate SQL DDL statements to define the database. Don’t forget to add primary and foreign keys in your relations
Choose or decide on your own data types for the fields in each table
Save the SQL DDL scripts inside a folder on your computer and run them as scripts while connected to your database
Column Name
Data Type
Bytes
ID
NUMBER
10
NAME
VARCHAR2
200
ADDRESS
VARCHAR2
400
TELEPHONE
VARCHAR2
10
USERNAME
VARCHAR2
24
Explanation / Answer
Need to store the data in byte we need to execute below query and set the setting from char to byte for particiular session
Query 1 : ALTER session SET nls_length_semantics=byte;
Query 2 :
Create table FIRST_LAB (
ID NUMBER (10 ),
NAME VARCHAR2 (20 ),
ADDRESS VARCHAR2 (400 ),
TELEPHONE VARCHAR2 (10 ),
USERNAME VARCHAR2 (24 )
);
ORA-12899: value too large for column "SQL_IBBZQLHYSOLYQBLIJPRMOXJUA"."FIRST_LAB"."NAME" (actual: 32, maximum: 20) ORA-06512: at "SYS.DBMS_SQL", line 1721
insert into FIRST_LAB values ( 101, 'sdfds','dfdsafdsfasdfasdfads','123456789','fdsafdsafdsafafsa' )
insert into FIRST_LAB values ( 101, 'sdfds','dfdsafdsfasdfasdfadsdfdsafdsafdsafsaf','123456789','fdsafdsafdsafafsa' )
Query 3:
Create table TAX_TAB (
EMPNO NUMBER (5),
TAX_RATE NUMBER (10),
MARRITAL_SATUS CHAR (1)
)
Query 4:
-----------
CREATE TABLE EMP_TAB(
EMPNO INTEGER(10) NOT NULL,
NAME VARCHAR2(200) NOT NULL,
SAL NUMBERL(10,3) NOT NULL
)
Query 5:
----------
Error : Syntax error: comma was not added at the end of each column. please check the correct query below
create table CHAVERAN_EMPLOYEE (
EMPLOYEE_NO NUMBER(2),
EMPLOYEE_NAME varchar2(40),
DESIGNATION varchar(40),
PLACE_OF_WORK varchar2(40) ,
DATE_OF_BRITH date);
Query 6:
----------
Error : Syntex error : need to provide the length for first column. at last column semicolon was appended which was not correct. Please check the corrected query below
create table AMAZON_CUSTOMERS (
CUSTOMER_ID number(2),
CUSTOMER_NAME VARCHAR2(40),
CUSTMER_ADDRESS VARCHAR2(40)
)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.