Can I please get DML for the below DDL? create table Country( code char(2) NOT N
ID: 3830128 • Letter: C
Question
Can I please get DML for the below DDL?
create table Country(
code char(2) NOT NULL PRIMARY KEY,
Name varchar(100) NOT NULL,
);
create table Member(
member_id INTEGER PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
title varchar(100) NOT NULL,
live_in varchar(50) REFERENCES Accommodation(name),
Country_code char(2) NOT NULL REFERENCES Country(code)
ON DELETE CASCADE
ON UPDATE CASCADE
);
create table Athlete(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id),
event EVENT(sport_name)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
create table Official(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
create table Staff(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
Create table Books(
book_time timestamp NOT NULL,
member_id integer NOT NULL REFERENCES Member(member_id),
start_time timestamp NOT NULL REFERENCES Journey(start_time),
start_date date NOT NULL REFERENCES Journey(start_date),
byStaff integer NOT NULL REFERENCES Staff(id),
vehicle char(8) NOT NULL REFERENCES Journey(code),
PRIMARY KEY(member_id, start_time, start_date, vehicle)
);
create table Journey(
start_time time NOT NULL,
start_date date NOT NULL,
nbooked integer NOT NULL,
departure varchar(100) NOT NULL REFERENCES Place(name),
arrival varchar(100) NOT NULL REFERENCES Place(name),
code char(8) NOT NULL REFERENCES Vehicle(code),
PRIMARY KEY( start_time, start_data, code)
);
create table Vehicle(
code char(8) PRIMARY KEY,
capacity varchar(100) NOT NULL
);
create table Place(
name varchar(50) PRIMARY KEY,
address varchar(100) NOT NULL,
longitude varchar(100) NOT NULL,
latitude varchar(100) NOT NULL
);
create table SportVenue(
name varchar(50) NOT NULL REFERENCES Place(name)
);
create table Accommodation(
name varchar(50) NOT NULL REFERENCES Place(name)
);
create table Event(
name varchar(100) PRIMARY KEY,
result_type varchar(100) NOT NULL,
event_time time NOT NULL,
event_date date NOT NULL,
sport_name varchar(50) NOT NULL REFERENCES Sport(name),
);
create table Participates(
medal varchar(6) DEFAULT ‘NULL’ CHECK(medal IN ’gold’, ’silver’, ‘bronze’, ‘NULL’),
result varchar(100) NOT NULL,
id integer REFERENCES Athlete(id),
sport_name REFERENCES Event(name),
PRIMARY KEY( id, sport_name)
);
create table Runs(
role varchar(100) NOT NULL,
id integer REFERENCES Official(id),
sport_name REFERENCES Event(name),
PRIMARY KEY( id, sport_name)
);
create table Sport(
name varchar(50) PRIMARY KEY
);
Explanation / Answer
Hi,
Please find the DML for the queries given.
I have taken dummy data to insert and retrieve values wherever necessary.
=====================================================================================
1)
create table Country(
code char(2) NOT NULL PRIMARY KEY,
Name varchar(100) NOT NULL,
);
DML queries:-
INSERT:-
INSERT INTO Country VALUES(‘IN’,’INDIA’);
INSERT INTO Country VALUES(‘CA’,’CANADA’);
INSERT INTO Country VALUES(‘AU’,’AUSTRALIA’);
SELECT:-
SELECT * FROM Country;
====================================================================================
DDL 2
Create table Member
(
member_id INTEGER PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
title varchar(100) NOT NULL,
live_in varchar(50) REFERENCES Accommodation(name),
Country_code char(2) NOT NULL REFERENCES Country(code)
ON DELETE CASCADE
ON UPDATE CASCADE
);
DML queries:-
INSERT:-
INSERT INTO Member VALUES(3,’SAM’,’ARNOLD’,’MANAGER’,’ABCD’,’CA’);
INSERT INTO Member VALUES(1,’SAM’,’LOUIS’,’SR.MANAGER’,’CDEF’,’CA’);
INSERT INTO Member VALUES(2,’JAMES’,’CHOR’,’TRAINEE’,’ABCD’,’IN’);
SELECT:-
SELECT * FROM MEMBER WHERE TITLE LIKE “%MANAGER%”;
=====================================================================================
3)
create table Athlete
(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id),
event EVENT(sport_name)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
DML queries:-
INSERT:-
INSERT INTO Athlete VALUES (1,’CRICKET’);
INSERT INTO Athlete VALUES (2,’FOOTBALL’);
INSERT INTO Athlete VALUES (3,’FOOTBALL’);
SELECT:-
SELECT * FROM Athlete where id=1;
====================================================================================
4)
create table Official
(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
DML queries:-
INSERT:-
INSERT INTO table VALUES(1);
INSERT INTO table VALUES(2);
INSERT INTO table VALUES(3);
SELECT:-
SELECT * FROM Official;
=====================================================================================
5)
create table Staff
(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
DML queries:-
INSERT:-
INSERT INTO Staff VALUES (1);
INSERT INTO Staff VALUES (2);
INSERT INTO Staff VALUES (3);
SELECT:-
SELECT * FROM Staff;
=====================================================================================
6)
Create table Books
(
book_time timestamp NOT NULL,
member_id integer NOT NULL REFERENCES Member(member_id),
start_time timestamp NOT NULL REFERENCES Journey(start_time),
start_date date NOT NULL REFERENCES Journey(start_date),
byStaff integer NOT NULL REFERENCES Staff(id),
vehicle char(8) NOT NULL REFERENCES Journey(code),
PRIMARY KEY (member_id, start_time, start_date, vehicle)
);
DML queries:-
INSERT:-
INSERT INTO Books VALUES (‘18-MAR-17 11.00.00.000000’,1,’ 18-MAR-17 11.00.00.000000’,’ ‘18-03-2017’,1,’CAR’);
INSERT INTO Books VALUES (‘18-APR-1711.00.00.000000’,3,’ 18-APR-17 11.00.00.000000’,’ ‘18-04-2017’,3,’BUS’);
INSERT INTO Books VALUES (‘18-APR-1711.00.00.000001’,3,’ 18-APR-17 11.00.00.000001’,’ ‘18-04-2017’,3,’BUS’);
SELECT:-
SELECT * FROM books where date=’18-04-2017’
6)
create table Journey
(
start_time time NOT NULL,
start_date date NOT NULL,
nbooked integer NOT NULL,
departure varchar(100) NOT NULL REFERENCES Place(name),
arrival varchar(100) NOT NULL REFERENCES Place(name),
code char(8) NOT NULL REFERENCES Vehicle(code),
PRIMARY KEY( start_time, start_date, code)
);
DML queries:-
INSERT:-
INSERT INTO Journey VALUES(‘18-MAR-17 11.00.00.000000’,’18-03-2017’,1,’ABCD’,’CDEF’,’CAR’);
INSERT INTO Journey VALUES(‘18-MAR-17 11.00.00.000001’,’18-04-2017’,1,’ABCD’,’QRST’,’BUS’);
INSERT INTO Journey VALUES(‘18-MAR-17 11.00.00.000002’,’18-04-2017’,1,’ABCD’,’CDEF’,’TEMPO’);
SELCECT:-
SELECT * FROM Journey WHERE code =’BUS’ AND start_date =’18-04-2017’;
7)
create table Vehicle
(
code char(8) PRIMARY KEY,
capacity varchar(100) NOT NULL
);
DML queries:-
INSERT:-
INSERT INTO Vehicle VALUES (‘BUS’,’25’);
INSERT INTO Vehicle VALUES (‘CAR’,’4’);
INSERT INTO Vehicle VALUES (‘TEMPO’,’25’);
SELECT:-
SELECT * FROM Vehicle WHERE capacity >=’25’;
=====================================================================================
8)
create table Place
(
name varchar(50) PRIMARY KEY,
address varchar(100) NOT NULL,
longitude varchar(100) NOT NULL,
latitude varchar(100) NOT NULL
);
DML queries:-
INSERT:-
INSERT INTO Place VALUES (‘ABCD’,’42,SREET 5,LANE 1’,’HJSHGJSHG’,’FJKHGFH’);
INSERT INTO Place VALUES (‘CDEF’,’41,SREET 5,LANE 1’,’HJSHGJGDSGG”,”FJASGGFH’);
INSERT INTO Place VALUES (‘QRST’,’45,SREET 7,LANE 7’,’YUUIIJGDSGG”,”ALJFGFGFH’);
SELECT:-
SELECT *FROM PLACES WHERE ADDRESS LIKE ”%LANE 1”;
====================================================================================
9)
create table SportVenue
(
name varchar(50) NOT NULL REFERENCES Place(name)
);
DML queries:-
INSERT:-
INSERT INTO SportVenue VALUES(‘ABCD’);
INSERT INTO SportVenue VALUES(‘CDEF’);
INSERT INTO SportVenue VALUES(‘QRST’);
=====================================================================================
10)
create table Accommodation
(
name varchar(50) NOT NULL REFERENCES Place(name)
);
DML queries:-
INSERT:-
INSERT INTO Accommodation VALUES(‘ABCD’);
INSERT INTO Accommodation VALUES(‘CDEF’);
INSERT INTO Accommodation VALUES(‘QRST’);
=====================================================================================
11)
create table Event(
name varchar(100) PRIMARY KEY,
result_type varchar(100) NOT NULL,
event_time time NOT NULL,
event_date date NOT NULL,
sport_name varchar(50) NOT NULL REFERENCES Sport(name),
);
DML queries:-
INSERT:-
INSERT INTO Event VALUES (‘PLAY FOOTBALL’,’WIN’,’11.00.11’,’19-03-2017’,’FOOTBALL’);
INSERT INTO Event VALUES (‘PLAY CRICKET’,’WIN’,’11.00.11’,’21-04-2017’,’CRICKET’);
SELECT:-
SELECT event_date AS DATEOF, name where sport name=’CRICKET’;
================================================================================
12)
create table Participates
(
medal varchar(6) DEFAULT ‘NULL’ CHECK(medal IN ’gold’, ’silver’, ‘bronze’, ‘NULL’),
result varchar(100) NOT NULL,
id integer REFERENCES Athlete(id),
sport_name REFERENCES Event(name),
PRIMARY KEY( id, sport_name)
);
DML queries:-
INSERT:-
INSERT INTO Participates VALUES WHERE (‘gold’,’1’,1,’FOOTBALL’);
INSERT INTO Participates VALUES WHERE (‘silver’,’2’,3,’FOOTBALL’);
SELECT:-
SELECT id,result from Participates WHERE sport_name=’FOOTBALL’;
=====================================================================================
13)
create table Runs
(
role varchar(100) NOT NULL,
id integer REFERENCES Official(id),
sport_name REFERENCES Event(name),
PRIMARY KEY( id, sport_name)
);
DML queries:-
INSERT:-
INSERT INTO Runs VALUES WHERE (‘goal keeper,’1’,FOOTBALL’);
INSERT INTO Runs VALUES WHERE (‘striker,’2’,FOOTBALL’);
=====================================================================================
14)
create table Sport
(
name varchar(50) PRIMARY KEY
);
DML queries:-
INSERT:-
INSERT INTO Sport VALUES WHERE (‘FOOTBALL’);
INSERT INTO Sport VALUES WHERE (‘CRICKET’);
=================================================================================
=================================================================================
NOTE:- I HAVE USED INSERT AND SELECT DML STATEMENTS FOR THE ABOVE 14 DDL STATEMENTS.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.