Question

Use the following tables to answer Q5 and Q6. Tourist (tno, Name, phone, age,loc) Vehicle (Vno,...

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

Homework Answers

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.

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
EMPLOYEE Field Name EMP_ID EMP_LNAME EMP_MI EMP_FNAME EMP_SEX EMP_AGE EMP_SALARY EMP_HIREDATE DEPT_CODE Table: DEPARTMENT DEPT_CODE BIOL...
EMPLOYEE Field Name EMP_ID EMP_LNAME EMP_MI EMP_FNAME EMP_SEX EMP_AGE EMP_SALARY EMP_HIREDATE DEPT_CODE Table: DEPARTMENT DEPT_CODE BIOL CPTR HIST MATH RELB Data Type Text Text Text Text Text Number Currency Date/Time Text DEPT_TITLE Biology Computer Science History Mathematics Religion    Field Name DEPT_CODE DEPT_TITLE Data Type Text Text INSTRUCTIONS Use SQL commands to create the tables and enter the data shown above using MS Access. Write the following SQL statement given below: 1. 2. 3. 4. 5. 6. 7. 8. 9....
Lab 5 Queries with Multiple Tables In this lab, we do queries more than one table....
Lab 5 Queries with Multiple Tables In this lab, we do queries more than one table. SQL provides two different techniques for querying data from multiple tables: • The SQL subquery • The SQL join As you will learn, although both work with multiple tables, they are used for slightly different purposes. We used WMCRM database which is what we created in Lab 4. Here is the summary of the database schema (where schema is used in its meaning of...
Description In this project you will practice what we have learned in class about Design, ER...
Description In this project you will practice what we have learned in class about Design, ER Diagrams, Relational Models, DDL, SQL, CRUD (Create, Update, Delete) operations, associated queries, and mock data population. The goal is to create a realistic professional database/development experience. This assignment will describe the requirements for the database as you might receive them. You will need to fill in the details as you work on it. You will find that your work may be iterative, and you...
What topics are covered in the following article? Please answer within 5 hours. It is extremely...
What topics are covered in the following article? Please answer within 5 hours. It is extremely urgent!!!!!!!!!!!!!!!!!!!!!!!! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BIOETHICS. Bioethics as a field is relatively new, emerging only in the late 1960s, though many of the questions it addresses are as old as medicine itself. When Hippocrates wrote his now famous dictum Primum non nocere (First, do no harm), he was grappling with one of the core issues still facing human medicine, namely, the role and duty of the physician....
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT
Active Questions
  • Six men and six women are to be divided into three different groups, each consisting of...
    asked 8 minutes ago
  • Determine the effects of the following on a cash basis taxpayer’s gross income for 2018 and...
    asked 33 minutes ago
  • XYZ is a calendar-year corporation that began business on January 1, 2017. For 2018, it reported...
    asked 1 hour ago
  • Although aging and death are realities that all individuals have to deal with at some point...
    asked 1 hour ago
  • Implement the following functions with AVR assembly language 1) 2-byte addition (i.e, addition on 16-bit numbers)...
    asked 1 hour ago
  • Assume you have a chemical compound (HA) that is a weak acid which changes color upon...
    asked 2 hours ago
  • A company has two divisions. The first division consists of project management and generated $4,523,367 of...
    asked 2 hours ago
  • Practice Quiz 1 Use the following information to answer questions 1 through 5.                              &
    asked 2 hours ago
  • 3) The corrosion of iron is an electrochemical process that involves the standard reduction potentials given...
    asked 2 hours ago
  • Type or paste question here In 2015, the United States is still recovering from the recession....
    asked 2 hours ago
  • Failures of a critical machine part due to cyclical vibration has a gamma distribution with a...
    asked 3 hours ago
  • f(x)=ln(1+2x), a=4,n=3,3.7<=x<=4.3 (b) Use Taylor's Inequality to estimate the accuracy of the approximation f  Tn(x) when x...
    asked 3 hours ago