Question

Use the following tables to answer Q5 and Q6. Tourist (tno, Name, phone, age,loc) Vehicle (Vno, model, mileage, owner_name, owner_ph)) Rented (tno, Vno) 5. (i)Write an SQL query, to create the table vehicle with following constraints: Vno should begin with second letter ‘v’; Models are one among Thar, X3. Mileage should be above 18 and below 25. No two records can have same phone (ii) Write an SQL query to retrieve the name of the owner who does owns X3 and has rented it to a tourist above the age of 60. (iii) SELECT model, count (*) FROM Vehicle, Rented, Tourist WHERE Tourist.tno=Rented.tno AND Rented.Vno=Vehicle.Vno AND age >20 GROUP BY model HAVING count(*)>2; Interpret the above given SQL in simple English words. (Output is NOT expected) [C01, L3] 6. Following are set of operations. Will all the operations succeed - if yes, write which command (INSERT, DELETE, DROP, UPDATE, ALTER ADD, ALTER DROP, ALTER MODIFY, CREATE, SELECT)(command name not the query) you will use for each operation. Justify your choice of command. (i)Retrieve the names of the tourist whose phone is 123 (1 MARK) (ii)New column date is added in rented table, have to insert values into the newly created column for existing records

Answer 1

/*(i)Write an SQL query, to create the table vehicle with following constraints: Vno should begin with second letter ‘v’;
 Models are one among Thar, X3. 
 Mileage should be above 18 and below 25.
 No two records can have same phone (3 MARKS)*/


CREATE TABLE Vehicle (
    vno varchar Check (vno LIKE '_v%') Primary Key,
    Model varchar CONSTRAINT chk_model CHECK (Model IN ('Thar', 'X3')),
    Mileage integer CONSTRAINT chk_mileage CHECK (Mileage >18 and Mileage<25),
    owner_name varchar(50),
    owner_ph integer Unique
);

CREATE TABLE Vehicle (
    vno varchar Check (vno LIKE '_v%') Primary Key,
    Model varchar CONSTRAINT chk_model CHECK (Model IN ('Thar', 'X3')),
    Mileage integer CONSTRAINT chk_mileage CHECK (Mileage >18 and Mileage<25),
    owner_name varchar(50),
    owner_ph integer Unique
);

CREATE TABLE Tourist (
    tno varchar Primary Key,
    Name varchar(50),
    phone integer,
    age integer,
    loc varchar
);

CREATE TABLE Rented (
    vno varchar Check (vno LIKE '_v%') Primary Key,
    tno varchar
);

Insert into Vehicle values('Av123','X3',20,'Mr.Kapoor',2456879999);
Insert into Vehicle values('Cv124','Thar',23,'Mr.das',447775558888);
select * from Vehicle;

Insert into Tourist values('t123','Mr Datta',123,61,'Delhi');
Insert into Tourist values('t567','Mrs Kumar',587456,54,'Mumbai');
select * from Tourist;

Insert into Rented values('Av123','t123');
Insert into Rented values('Cv124','t567');
select * from Rented;

SELECT owner_name From Vehicle v
 Inner Join Rented r on r.Vno = r.Vno Inner Join Tourist t on t.tno = r.tno
 WHERE v.Model=='X3' and t.age>60;

(iii) SELECT model, count (*) FROM Vehicle, Rented, Tourist WHERE Tourist.tno=Rented.tno AND Rented.Vno=Vehicle.Vno AND age >20 GROUP BY model HAVING count(*)>2;
Interpret the above given SQL in simple English words. (Output is NOT expected) (3 MARKS)

The above sql statement is about displaying the model name and count of each model which has rented to the tourist with age greater than 20 and model count is greater than 2.

i)Retrieve the names of the tourist whose phone is 123 (1 MARK)

SELECT Name From Tourist where phone==123;

to retrieve any row from any table we need to use select command.
(ii)New column date is added in rented table, have to insert values into the newly created column for existing records
ALTER Table Rented ADD date date null;

We use ALTER ADD to add new column to the existing table.

UPDATE Rented SET date = '17-10-2020';

inserting value only to the newly added column we use update command.