CREATE TABLE EMPLOYEE( ENUM DECIMAL(12) NOT NULL,/* Employee number */ FNAME VARCHAR(50) NOT NULL,/* First name */ LNAME VARCHAR(50) NOT NULL,/* Last name */ DOB DATE NULL,/* Date of birth */ CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(ENUM) ); CREATE TABLE DRIVER( ENUM DECIMAL(12) NOT NULL,/* Employee number */ LNUM DECIMAL(8) NOT NULL,/* Driving license number */ STATUS VARCHAR(10) NOT NULL,/* Driver status */ CONSTRAINT DRIVER_PKEY PRIMARY KEY(ENUM), CONSTRAINT DRIVER_UNIQUE UNIQUE(LNUM), CONSTRAINT DRIVER_FKEY FOREIGN KEY(ENUM) REFERENCES EMPLOYEE(ENUM), CONSTRAINT DRIVER_STATUS CHECK (
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
CREATE TABLE EMPLOYEE(
ENUM DECIMAL(12) NOT NULL,/* Employee number */
FNAME VARCHAR(50) NOT NULL,/* First name */
LNAME VARCHAR(50) NOT NULL,/* Last name */
DOB DATE NULL,/* Date of birth */
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(ENUM) );
CREATE TABLE DRIVER(
ENUM DECIMAL(12) NOT NULL,/* Employee number */
LNUM DECIMAL(8) NOT NULL,/* Driving license number */
STATUS VARCHAR(10) NOT NULL,/* Driver status */
CONSTRAINT DRIVER_PKEY PRIMARY KEY(ENUM),
CONSTRAINT DRIVER_UNIQUE UNIQUE(LNUM),
CONSTRAINT DRIVER_FKEY FOREIGN KEY(ENUM) REFERENCES EMPLOYEE(ENUM),
CONSTRAINT DRIVER_STATUS CHECK (
STATUS IN ('AVAILABLE', 'BUSY', 'ON LEAVE')) );
CREATE TABLE TRUCK(
REGNUM VARCHAR(10) NOT NULL,/* Registration number */
CAPACITY DECIMAL(7) NOT NULL,/* Capacity */
WEIGHT DECIMAL(7) NOT NULL,/* Weight */
STATUS VARCHAR(10) NOT NULL,/* Present status */
CONSTRAINT TRUCK_PKEY PRIMARY KEY(REGNUM),
CONSTRAINT TRUCK_STATUS CHECK
( STATUS IN ('AVAILABLE', 'USED', 'MAINTAINED')),
CONSTRAINT TRUCK_WEIGHT CHECK
( WEIGHT > 0.0 AND WEIGHT < 500 ),
CONSTRAINT TRUCK_CAPACITY CHECK
( CAPACITY > 0.0 AND CAPACITY < 100 ) );
CREATE TABLE TRIP(
TNUM DECIMAL(10) NOT NULL,/* Trip number */
LNUM DECIMAL(8) NOT NULL,/* Driving license number */
REGNUM VARCHAR(10) NOT NULL,/* Truck registration number */
TDATE DATE NOT NULL,/* Trip date */
CONSTRAINT TRIP_PKEY PRIMARY KEY (TNUM),
CONSTRAINT TRIP_CKEY UNIQUE (LNUM, REGNUM, TDATE),
CONSTRAINT TRIP_FKEY1 FOREIGN KEY (LNUM) REFERENCES DRIVER(LNUM),
CONSTRAINT TRIP_FKEY2 FOREIGN KEY (REGNUM) REFERENCES TRUCK(REGNUM) );
CREATE TABLE TRIPLEG(
TNUM DECIMAL(10) NOT NULL,/* Trip number */
LEGNUM DECIMAL(2) NOT NULL,/* Leg number */
DEPARTURE VARCHAR(30) NOT NULL,/* Departure city */
DESTINATION VARCHAR(30) NOT NULL,/* Destination city */
CONSTRAINT TRIPLEG_PKEY PRIMARY KEY (TNUM, LEGNUM),
CONSTRAINT TRIPLEG_UNIQUE UNIQUE(TNUM, DEPARTURE, DESTINATION),
CONSTRAINT TRIPLEG_FKEY1 FOREIGN KEY (TNUM) REFERENCES TRIP(TNUM) );
Refer to the above table
Write SQL statements that implement the following structural modifications of a
and data manipulations on the contents of a database. Note, that implementation of some
of the tasks listed below may need more than one SQL statement.
(1) We would like to add to a database information about the skills possessed by each
driver. Assume, that a driver has many skills and a skill can be possessed by many
drivers. It may happen that some skills are not possessed by any driver. The types of
the new columns are up to you.
(2) Delete from a database information about the second and third leg of a trip 36.
(3) Change a date of trip number 26 to the next day after the present date of trip 26.
(4) Assume that a trip number 25 has 3 legs. A trip number 25 started in Sydney and it
ended in Perth. Add one more leg to a trip 25 such that after update the trip ends in
the same city it started from.
(5) Assume that a trip 20 has 2 legs and that a destination of the first leg is Sydney.
Update information about a trip 20 such that after update a destination of the first leg
is Perth.
Step by step
Solved in 2 steps with 1 images
