Creating a table in SQL using Oracle 11G

Question

I am new to learning SQL and have been struggling to create a table for an assignment. These are the requirements:

Create a new table to track the Library location.

LIBRARY (lib_id, lib_name, lib_address, lib_city, lib_state, lib_zip)
LIB_ID is the library id – it is an auto generated number. (you should create a sequence number called lib_id_seq, start with 1001 and increment by 1.)
LIB_ID is the primary key.
LIB_NAME, LIB_ADDRESS, and LIB_CITY is between 1 and 35 characters.
LIB_STATE is 2 characters – default to TX.
LIB_ZIP is 5 numbers. Check for one of the following zip codes – 75081, 75080, 75082, 75079, 75078

And this is what I have written out so far:

CREATE TABLE LIBRARY
(
  LIB_ID INT(4),
  LIB_ADDRESS VARCHAR(35),
  LIB_CITY VARCHAR(35),
  LIB_STATE VARCHAR(2) DEFAULT ‘TX’,
  LIB_ZIP INT(5) CHECK (Frequency IN ('75078', ‘75079', '75080', '75081', ‘75082’))
  PRIMARY KEY(LIB_ID)
);

CREATE SEQUENCE LIB_ID_SEQ
START WITH 1001
INCREMENT BY 1;

I keep getting errors, but am not sure what I need to fix.

Well you tagged 3 different DBMS and the syntax for all these things will be different. And of course you said you are getting errors. If you share the error message we don't have to guess. — Sean Lange
– Sean Lange, Commented Oct 25, 2016 at 20:23
Sorry, just clicked on the recommended tags. This was written in Oracle 11G — Nikkita Kumar
– Nikkita Kumar, Commented Oct 25, 2016 at 20:27
Where in the Oracle manual did you find the syntax int(4)?. Also ’ and ‘ are invalid in SQL. You need to use a straight single quote ' — user330315
– user330315, Commented Oct 25, 2016 at 20:31
Don't keep us in suspense any longer....just share the error messages so we don't have to read your mind. — Sean Lange
– Sean Lange, Commented Oct 25, 2016 at 20:37
Rule of thumb: unless you plan on doing math with it, don't store it as a number. Zip in this case I wouldn't' store as int, Id' store as varchar2. not only could you have 5-4 notation, but you could deal with other country zip's which would have non-numeric values. — xQbert
– xQbert, Commented Oct 25, 2016 at 20:40

xQbert · Accepted Answer · 2016-10-25 20:50:51Z

1

For oracle (Kid Tested unsure if SO approved)...

use varchar2 instead of varchar
use Number instead of int
added constraint syntax (named them)
adjusted apostrophe's (Removed) instead of whatever the heck you had in some of them :P (It's a numeric field shouldn't be using text apostrophes!)
personally I wouldn't name a table library as that's a reserved word
I woudln't use a numeric Zip code as we will never do math on a zipcode.

. .

CREATE TABLE LIBRARY (
LIB_ID Number(4),
LIB_ADDRESS VARCHAR2(35),
LIB_CITY VARCHAR2(35),
LIB_STATE VARCHAR2(2) DEFAULT 'TX',
LIB_ZIP NUMBER(5),
CONSTRAINT Lib_ZIP_CON CHECK (LIB_ZIP IN (75078, 75079, 75080, 75081, 75082)),
CONSTRAINT LIB_ID_PK PRIMARY KEY(LIB_ID)
);

CREATE SEQUENCE LIB_ID_SEQ
START WITH 1001
INCREMENT BY 1;

edited Oct 25, 2016 at 20:50

answered Oct 25, 2016 at 20:37

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xQbert Over a year ago

I didn't setup the sequence, but you can see the TX was defaulted and that I don't have a 2nd record (this is due to the check constraint being violated on zipcode.

Teja · Accepted Answer · 2016-10-25 20:27:20Z

0

This works for SQL Server. You need to modify the syntax accordingly for the concerned db.

CREATE TABLE LIBRARY

(
LIB_ID INTEGER PRIMARY KEY,

LIB_ADDRESS VARCHAR(35),

LIB_CITY VARCHAR(35),

LIB_STATE VARCHAR(2) DEFAULT 'TX',

LIB_ZIP INTEGER, 

CHECK( LIB_ZIP IN ('75078', '75079', '75080', '75081', '75082') )

);


CREATE SEQUENCE LIB_ID_SEQ

START WITH 1001

INCREMENT BY 1;

For learning how to create tables and constraints check this link on w3schools as you seem to be a beginner.

http://www.w3schools.com/sql/sql_primarykey.asp

answered Oct 25, 2016 at 20:27

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Collectives™ on Stack Overflow

Creating a table in SQL using Oracle 11G

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related