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 lic
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 above table
Write SELECT statements that implement the following queries.
(1) Find the registration numbers of trucks, that have capacity in a range between 100
inclusive and 500 inclusive.
(2) Find the first and the last names of all drivers who are on leave.
(3) Find the registration numbers of all trucks that have not been used for any trips.
(4) Find the registration numbers of all trucks together with total number of trips the trucks
have been used for. There is no need to include the truck that have not been used for
any trips.
(5) Find the names of all departure cities together with the names of all destination cities.
Step by step
Solved in 2 steps with 1 images
