SQL

ALTER TABLE TABELNAME CHANGE

• NEWCOLNAME NEWDATATYPE

• NEWCOLNAME OLDDATATYPE

• OLDCOLNAME NEWCOLNAME NEWDATATYPE

• OLDCOLNAME NEWCOLNAME OLDDATATYPE

What is def in the following statement?

CREATE TRIGGER abc (...) (...) ON def FOR EACH ROW ghi;

• trigger name

• table name

• trigger statement

The salary of managers has been increased by 20%. Retrieve the empid, existing salary as "Current Salary", increased salary as "New Salary" and the difference of existing and increased salary as "Incremented Amount". Round off the increased salary up to two decimal places.

• select empid,round(salary,0) as "Current Salary", round(salary*1.20,2) as "New Salary",round(salary*0.20,2) as "Incremented Amount" from empdetails where Designation='Manager';

• select empid,round(salary,2) as "Current Salary", round(salary*0.20,2) as "New Salary",round(salary*0.20,2) as "Incremented Amount" from empdetails where Designation='Manager';

• select empid,round(salary,2) as "Current Salary", round(salary*1.20,2) as "New Salary",round(salary*0.20,2) as "Incremented Amount" from empdetails where Designation='Manager';

• select empid,round(salary,2) as "Current Salary", round(salary*1.20,2) as "New Salary",round(salary*1.20,2) as "Incremented Amount" from empdetails where Designation='Manager';

Display the itemcode of those items where the difference of quantity on hand and reorder level is more than 50. Convert the result of difference to positive values.

• Select itemcode from item where abs(Qtyonhand-reorderlevel)>=50;

• Select itemcode from item where round(Qtyonhand-reorderlevel)>=50;

• Select itemcode from item where Qtyonhand-reorderlevel>50;

• Select itemcode from item where abs(Qtyonhand-reorderlevel)>50;

Retrieve the itemcode and average of quantity available in retail outlets where the average of quantity available is less than 75.

• select itemcode,avg(qtyavailable) "Average Quantity" from retailstock group by itemcode having avg(qtyavailable)>75;

• select itemcode,avg(qtyavailable) "Average Quantity" from retailstock group by itemcode having count(qtyavailable)<75;

• select itemcode,avg(qtyavailable) "Average Quantity" from retailstock group by itemcode having avg(qtyavailable)<75;

• select itemcode,avg(qtyavailable) "Average Quantity" from retailstock group by itemcode having avg(qtyavailable)<=75;

Display pymtmode, and total number of payments for those payments which were paid before the year 2015 and total number of payments should be more than 1.

• select pymtmode,sum(pymtmode) as PYMTCOUNT from orders where extract(year from pymtdate)<2015 group by pymtmode having count(pymtmode)>1;

• select pymtmode,count(pymtmode) as PYMTCOUNT from orders where extract(year from pymtdate)<2015 group by pymtmode having count(pymtmode)>1;

• select pymtmode,count(pymtmode) as PYMTCOUNT from orders where extract(year from pymtdate)<2015 group by pymtmode having count(pymtmode)>=1;

• select pymtmode,count(pymtmode) as PYMTCOUNT from orders where extract(year from pymtdate)<=2015 group by pymtmode having count(pymtmode)>1;

Retrieve the itemcode, descr and unit price of the items which have 'Shirt' or 'Skirt' in their desc and their category is 'B'.

• select Itemcode,Descr,Price from Item where (Descr LIKE '%Shirt%' or Descr LIKE '%Skirt%') and Category='B';

• select Itemcode,Descr,Price from Item where (Descr LIKE '%Shirt%' or Descr LIKE '%Skirt%') and Category<='B';

• select Itemcode,Descr,Price from Item where (Descr LIKE '%Shirt%' or Descr LIKE '%Skirt%') and Category<>'B';

• select Itemcode,Descr,Price from Item where (Descr LIKE '%Shirt%' or Descr LIKE '%Skirt%') and Category lIKE 'B';

Retrieve the quotation id and sname of the quotations which have been either 'Accepted' or 'Rejected'

• select quotationid,sname from quotation where qstatus in ('Accepted','Rejected');

• select quotationid from quotation where qstatus in ('Accepted','Rejected');

• select quotation,sname from quotation where qstatus in ('Accepted','Rejected');

• select quotationid,sname from quotation where qstatus in ('Accepted' or 'Rejected');

Retrieve the different item types.

• select itemtype from item order by itemtype desc;

• select itemtype from item order by name;

• select itemtype from item;

• select distinct itemtype from item;

The management of EasyShop wants to identify the customer and his/her spouse who is also a customer. How do you create this relationship?

• select a.custName,b.custName as spouse from customer a,customer b where a.custID<>b.custID and a.address=b.address

• select a.custName,b.custName as spouse from customer a,customer b where a.custID=b.custID and a.address=b.address

• select a.custName,b.custName as spouse from customer a,customer b where a.custID=b.custID and a.address<>b.address

• select a.custName,b.custName as spouse from customer a,customer b where a.custID<>b.custID and a.address<>b.address