Computer Science 5 Marks Each

“IT Tech ”Company conducts workshops for employees of organizations. The company requires data of workshops that are organize(d) As a database administrator you have decided that:

Name of the Table: WORKSHOP

Attributes of Table ‘Workshop ’are as follows.

Field	Type	Constraint
Workshopld	integer	Primary Key
Title	Varchar(50)
DateWorkshop	Date
NumSpeakers	Integer

Table : WORKSHOP

WorkshopId	Title	Date Workshop	NumSpeakers
1001	Robotics	2020-02-21	3
1002	AI	2020-05-24	4
1003	Humanoids	2020-07-15	3
1004	Block Chain	2020-04-25	5
1005	Cloud	2020-03-03	4

(a) Which attribute can be the primary key.

(i) Title (ii) WorkshopID

(iii) NumSpeaker (iv) DateWorkshop

(b) Which command will display the structure of the table:

(i) Use workshop (ii) Desc

(iii) Desc workshop (iv) Show tables

(c) Command to display all the record in descending order of number of speakers.

(i) Select * from Workshop reverse by NumSpeakers;

(ii) Select * from workshop order by descNumSpeakers;

(iii) Select * from workshop order by NumSpeakersdesc;

(iv) Select * from workshop group by NumSpeakersdesc;

(d) What will be the cardinality and degree of the table: WORKSHOP

(i) Cardinality = 5, Degree =4

(ii) Cardinality = 4, Degree =5

(iii) Cardinality = 6, Degree =4

(iv) Cardinality = 5, Degree =5

(e) Choose the right command which will delete the record of 1004 WorkshopId

(i) Delete * from workshop where workshopid==1004;

(ii) Delete from workshop where workshopid=1004;

(iii) Drop from workshop where workshopid=1004;

(iv) Delete table from workshop where workshopid=1004;

Consider the SchoolBus table given below:

Rtno	Area_covered	Capacity	Noofstudents	Distance	T ransporter	Charges
1	Vasantkunj	100	120	10	Shivamtravels	100000
2	HauzKhas	80	80	10	Anand travels	85000
3	Pitampura	60	55	30	Anand travels	60000
4	Rohini	100	90	35	Anand travels	100000
5	Yamuna Vihar	50	60	20	Bhalla Co.	58000
6	Krishna Nagar	70	80	30	Yadav Co.	80000
7	Vasundhara	100	110	20	Yadav Co.	100000
8	Paschim Vihar	40	40	20	Speed travels	55000

(a) State the command that will give the output as:
Area_covered
Yamuna Vihar
Krishna Nagar
Vasundhara
(i) select area_covered from schoolbus where transporter Yadav Co.'and transporter='Bhalla Co.';
(ii) select area_covered from schoolbus where not transporter-Yadav Co.'and transporter=Bhaila Co.';
(iii) select area_covered from schoolbus where transporter - Yadav Co.'
OR transporter'Bhalla Co.';
(iv) select area_covered from schoolbus where transporter IN("Yadav co.", -Bhalla co."):
Choose the correct option:
(i) Both (i) and (ii). (ii) Both (iii) and (iv).
(iii) Any of the options (i), (ii) and (iv) (iv) Only (iii)
(b) What will be the output of the following command?
Select * from schoolbus where distance=20 order by charges;

(c) Ravi has given the following command to obtain the Longest distance Select max(distance) from schoolbus where group by transporter;
But he is not getting the desired result.
Help him by writing the correct command.
(i) select max(distance) from schoolbus where group by transporter;
(ii) select transporter, max(distance) from schoolbus group by distance;
(iii) select transporter, max(distance) group by transporter from schoolbus;
(iv) select transporter, max(distance) from schoolbus group by transporter;
(d) Help Saumya to write the command to display the name of the transporter who is having lowest capacity in his schoolbus?
(i) select transporter.min(capacity) from schoolbus;
(ii) select transporter.max(capacity) from schoolbus:
(iii) select transporter.min(capacity) from schoolbus group by transporter:
(iv) select transporter.maximum(capacity)
(e) Choose the appropriate primary key.
(i) Transporter (ii) Area_covered (iii) Rtno (iv) Charges

Consider the table LOANS given below:

Table: LOANS

AccNo	Name	Loan Amt	EMI	IntRate	Start Date	Interest
1001	R.K. Gupta	300000	36	12.00	19-07-2009	1200
1002	S. P. Shanna	500000	48	10.00	22-03-2008	1800
1003	K.P. Jain	300000	36	NULL	08-03-2007	1600
1004	M.P. Yadav	800000	60	10.00	06-12-2008	2250
1005	S.P. Sinlia	200000	36	12.50	03-01-2010	4500
1006	P. Shanna	700000	60	12.50	05-06-2008	3500
1007	K.S. Dhall	500000	48	NULL	05-03-2008	3800

(a) State the command that will give the output as:

Name

S. P. Shanna

K.S. Dhall

M.P. Yadav

P. Shanna

K.S. Dhall

(i) Select name from LOANS where Name >‘D ’;

(ii) Select name from LOANS where AccNo >1001;

(iii) Select name from LOANS where Loan_Amt >= 500000;

(iv) Select name from LOANS where Loan_Amt >500000 order Loan_AmtDESC;

(b) What will be the output of the following command:

Select name, EMI from LOANS where Loan_Amt >500000 and Int_Rate is NULL;

(c) John has given the following command to display the count of all loan holders whose name ends with “Sharma ”:

Select count() from LOANS where Name like ‘Sharma%’;

But he is not getting the desired result. Help him by finding out the correct command fromthe followings:

(i) Select count(*) from LOANS where name Like ‘Sharma%’;

(ii) Select count(*) from LOANS where name Like ‘%Sharma ’;

(iii) Select count( ) from LOANS where name like ‘%sharma ’;

(iv) Select count(Name) from LOANS where name ‘Sharma%’;

(d) State the command to display the maximum Loan_Amt and Cust_Name :-

(i) Select Max(Cust_Name, Loan_Amt) from LOANS;

(ii) Select Cust_Name, max(LoanAmt) from LOANS;

(iii) Select Cust_Name, max(Loan_Amount) from LOANS;

(iv) Select Cust_name, max(Loan_Amt) from LOANS;

(e) Help Ramesh to display the count of all loan holders whose interest is NULL.

(i) Select Count(*) from LOANS where Int_Rate is NULL.

(ii) Select Count() from LOANS where Interest is NULL.

(iii) Select Count(*) from LOANS where Int_rate = NULL;

(iv) Select Count( ) from LOANS where Interest = NULL;