Observe the following table CANDIDATE carefully and write the name of the RDBMS operation out of (i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT, which has been used to produce the output as shown in RESULT ? Also, find the Degree and Cardinality of the RESULT.

TABLE: CANDIDATE

RESULT

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables
TABLE : BOOK

TABLE: MEMBER

(i) To display all details from table MEMBER in descending order of ISSUEDATE.

(ii) To display the BNO and BNAME of all Fiction Type books from the table BOOK

(iii) To display the TYPE and number of books in each TYPE from the table BOOK

(iv) To display all MNAME and ISSUEDATE of those members from table MEMBER who have books issued (i.e ISSUEDATE) in the year 2017.

(v) SELECT MAX(ISSUEDATE) FROM MEMBER;

(vi) SELECT DISTINCT TYPE FROM BOOK;

(vii) SELECT A.CODE,BNAME,MNO,MNAME FROM BOOK A, MEMBER B
WHERE A.CODE=B.CODE ;

(viii) SELECT BNAME FROM BOOK
WHERE TYPE NOT IN (“FICTION”, “COMIC”);

Write SQL queries for (i) to (iv) and write outputs for SQL queries (v) to (viii), which are based on the table given below:

Table: TRAINS

Table:PASSENGERS

(i)  To display details of all Trains which Start from New Delhi.

(ii) To display the PNR, PNAME, GENDER and AGE of all Passengers whose AGE is below 50.

(iii) To display total number of MALE and FEMALE Passengers.

(iv) To display details of all Passengers travelling in Trains whose TNO is 12015.

(v) SELECT MAX (TRAVELDATE), MIN(TRAVELDATE) FROM PASSENGERS WHERE GENDER = ‘FEMALE’;

(vi) SELECT END, COUNT(*) FROM TRAINS GROUP BY END HAVING COUNT(*)>1;

(vii) SELECT DISTINCT TRAVELDATE FROM PASSENGERS;

(viii) SELECT TNAME, PNAME FROM TRAINS T, PASSENGERS P WHERE T.TNO=P.TNO AND AGE BETWEEN 50 AND 60;

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.
Table: ACCOUNT

Table: TRANSACT

(i) To display details of all transactions of TYPE Deposit from Table TRANSACT.

(ii) To display the ANO and AMOUNT of all Deposits and Withdrawals done in the month of October 2017 from table TRANSACT.

(iii) To display the last date of transaction (DOT) from the table TRANSACT for the Accounts having ANO as 103.

(iv) To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and TRANSACT who have done transactions less than or equal to 3000.

(v) SELECT ANO, ANAME FROM ACCOUNT
WHERE ADDRESS NOT IN (‘CHENNAI’, ‘BANGALORE’);

(vi) SELECT DISTINCT ANO FROM TRANSACT;

(vii) SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT
GROUP BY ANO HAVING COUNT(*)> 1;

(viii) SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT
WHERE DOT <= ‘2017-06-01’;

Observe the following tables VIDEO and MEMBER carefully and write the name of the RDBMS operation out of (i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT, which has been used to produce the output as shown below, Also, find the Degree and Cardinality of the final result.
TABLE: VIDEO

TABLE: MEMBER

FINAL RESULT

Observe the following table MEMBER carefully and write the name of the RDBMS operation out of (i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT, which has been used to produce the output as shown in RESULT. Also, find the Degree and Cardinality of the RESULT.

MEMBER

RESULT

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables
DVD

MEMBER

(i) To display all details from the table MEMBER in descending order of ISSUEDATE.

(ii) To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD

(iii) To display the DTYPE and number of DVDs in each DTYPE from the table DVD

(iv) To display all NAME and ISSUEDATE of those members from the table MEMBER who
have DVDs issued (i.e ISSUEDATE) in the year 2017

(v) SELECT MIN(ISSUEDATE) FROM MEMBER;

(vi) SELECT DISTINCT DTYPE FROM DVD;

(vii) SELECT D.DCODE,NAME,DTITLE
FROM DVD D, MEMBER M WHERE D.DCODE=M.DCODE ;

(viii) SELECT DTITLE FROM DVD
WHERE DTYPE NOT IN ( ” Folk ” , ” Classical ” );

Observe the following PARTICIPANTS and EVENTS tables carefully and write the name of the RDBMS operation which will be used to produce the output as shown in RESULT ? Also, find the Degree and Cardinality of the result.

RESULT

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables

Table: VEHICLE

Note: PERKM is Freight Charges per kilometer

Table: TRAVEL

Note:

• Km is Kilometers travelled
• NOP is number of passengers travelled in vehicle

(i) To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO.

(ii) To display the CNAME of all the customers from the table TRAVEL who are traveling by vehicle with code V01 or V02.

(iii) To display the CNO and CNAME of those customers from the table TRAVEL who travelled between ’2015-12-31’ and ‘2015-05-01’.

(iv) To display all the details from table TRAVEL for the customers, who have travel distance more than 120 KM in ascending order of NOP.

(v) SELECT COUNT(*),VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT(*)>1;

(vi) SELECT DISTINCT VCODE FROM TRAVEL;

(vii) SELECT A.VCODE,CNAME,VEHICLETYPE
FROM TRAVEL A,VEHICLE B
WHERE A.VCODE=B.VCODE AND KM<90;

(viii) SELECT CNAME,KM*PERKM
FROM TRAVEL A,VEHICLE B
WHERE A.VCODE=B.VCODE AND A.VCODE=’V05′;

Observe the following table carefully and write the names of the most appropriate columns, which can be considered as (i) candidate keys and (ii) primary key.

Consider the following DEPT and EMPLOYEE tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii).
Table: DEPT

Table: EMPLOYEE

Note: DOJ refers to date of joining and DOB refers to date of Birth of employees.

(i) To display Eno, Name, Gender from the table EMPLOYEE in ascending order of Eno.

(ii) To display the Name of all the MALE employees from the table EMPLOYEE.

(iii) To display the Eno and Name of those employees from the table EMPLOYEE w ho are born between ‘1987-01-01’ and ‘1991-12-01’.

(iv) To count and display FEMALE employees who have joined after ‘1986-01-01’.

(v) SELECT COUNT(*),DCODE FROM EMPLOYEE GROUP BY DCODE HAVING COUNT(*)>1;

(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;

(vii) SELECT NAME, DEPARTMENT FROM EMPLOYEE E, DEPT D WHERE E.DCODE=D.DCODE AND EN0<1003;

(viii) SELECT MAX(DOJ), MIN(DOB) FROM EMPLOYEE;