I am very new to Sqlite3 programming and am having difficulty with the following

Question

I am very new to Sqlite3 programming and am having difficulty with the following problem. Any help would be greatly appreciated.

Problem - Sqlite3

Think of information you can store as a list. Each list should contain records of information. Each record should have attributes. With that in mind, perform the following:

**********************************************************************************************************************************

Write a file in Vi that is called create.sql. It must have Sqlite3 code which creates 5 tables. Make sure that you have a drop table first so that data isn't created twice. Also, another program called list.sql should list all of the data you have stored when create.sql is run.

************************************************************************************************************************************

Explanation / Answer

create.sql

********************************

DROP TABLE IF EXISTS people;

CREATE TABLE people (

person_id integer PRIMARY KEY,

first_name text,

last_name text,

address_id integer,

FOREIGN KEY (address_id) REFERENCES addresses (address_id)

);

DROP TABLE IF EXISTS addresses;

CREATE TABLE addresses (

address_id integer PRIMARY KEY,

house_no text,

street text,

city text,

postal_code text,

country text

);

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (

contact_id integer PRIMARY KEY,

first_name text NOT NULL,

last_name text NOT NULL,

email text NOT NULL UNIQUE,

phone text NOT NULL UNIQUE

);

DROP TABLE IF EXISTS groups;

CREATE TABLE groups (

group_id integer PRIMARY KEY,

name text NOT NULL

);

DROP TABLE IF EXISTS contact_groups;

CREATE TABLE contact_groups (

contact_id integer,

group_id integer,

PRIMARY KEY (contact_id, group_id),

FOREIGN KEY (contact_id) REFERENCES contacts (contact_id)

ON DELETE CASCADE ON UPDATE NO ACTION,

FOREIGN KEY (group_id) REFERENCES groups (group_id)

ON DELETE CASCADE ON UPDATE NO ACTION

);

list.sql

**************************

SELECT * FROM people;

SELECT * FROM addresses;

SELECT * FROM contact;

SELECT * FROM groups;

SELECT * FROM contact_groups;

NOTE: I have created random tables. These tables might not represent any database relationship.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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