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 distinct itemtype from item;

select itemtype from item order by itemtype desc;

select itemtype from item order by name;

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

Create the Shopper table with appropriate data types for the attributes described below. Column Name Constraints DESCRIPTION Shopperid PRIMARY KEY Unique id of the shopper. For ex. 2001, 2003 ShopperName NOT NULL Name of the Shopper. It cannot be more than 20 characters. Gender should be only 'Male' or 'Female' Gender of the Shopper. It cannot be more than 6 characters. MobileN o NOT NULL Mobile number of the Shopper in numeric form. Characters like brackets and hyphens are not allowed. Address Address of the Shopper. It cannot be more than 50 characters. For ex. Allen Street, New York

create table shopper(shopperid number(4) primary key, shopperName varchar(20) not null, gender varchar(6), Mobileno number not null, Address varchar(50), CONSTRAINT CHK_Gender check(GENDER in ('Male', 'Female')));

create table shopper(shopperid number(4) primary key, shopperName varchar(20) not null, gender varchar(6), Mobileno number not null, Address varchar(50), GENDER in ('Male', 'Female'));

create table shopper(shopperid number(4) primary key, shopperName varchar(20) not null, gender varchar(10), Mobileno number not null, Address varchar(50), check(GENDER in ('Male', 'Female')));

create table shopper(shopperid number(4) primary key, shopperName varchar(25) not null, gender varchar(10), Mobileno number not null, Address varchar(50), check(GENDER in ('Male', 'Female')));

Create the Article table as per specification below. Column Name Data Type Constraints DESCRIPTION ArCode CHAR(5) PRIMARY KEY; Must begin with character 'A' Unique code of the article e.g. A1001, A1004 ArName VARCHAR2(30) NOT NULL Article Name Rate NUMBER(8,2) Rate of the article. For ex. 5000.0 Quantity NUMBER(4) Greater than or equal to 0; Default Value is 0 Quantity availability of the article. For ex. 20 Class CHAR(1) Can be A, B or C Class of the article

create table article(ArCode char(5) primary key,ArName varchar2(30) Not null, rate number(8,2),Quantity number(4) default 0,class char(1),check(ArCode like 'A%'),check (Quantity>=0),check(Class in('A','B','C'))

create table article(ArCode char(5) primary key,ArName varchar2(30) Not null, rate number(8,2),Quantity number(4) default 0,class char(1),check(ArCode like 'A%'),check (Quantity>=0),check(Class in('A','B',))

create table article(ArCode char(5) primary key,ArName varchar2(30) Not null, rate number(8,2),Quantity number(4) default 0,class char(1),check(ArCode like '%A%'),check (Quantity>=0),check(Class in('A','B','C'))

create table article(ArCode char(5) primary key,ArName varchar2(30) Not null, rate number(8,2),Quantity number(4) default 0,class char(1),check(ArCode like 'A'),check (Quantity>=0),check(Class in('A','B','C'))

Sort on Grouped Columns For each distinct Grouped Columns Calculate aggregate function Add row to result

For each distinct Grouped Columns Calculate aggregate function Add row to result

Sort on Grouped Columns For each distinct Grouped Columns Calculate aggregate function

Sort on Grouped Columns Calculate aggregate function Add row to result

SELECT THE TRUE STATEMENT

Order By cannot be used on columns on which Grouping is not being done. SELECT Dept, SUM(Salary) FROM Employee GROUP BY Dept ORDER BY Designation;

Aggregate functions can be used in WHERE clause. SELECT Id, EName, Salary, Dept FROM Employee E1 WHERE Salary = MAX(Salary);

Aggregate functions can be used in WHERE clause even if GROUP BY is used. SELECT DEPT, SUM(Salary) FROM Employee WHERE SUM(Salary) > 90000 GROUP BY DEPT

Nested aggregate function can be used in SELECT clause without GROUP BY clause. SELECT MAX(AVG(Salary)) FROM Employee;

Display the month and number of quotations received in each month.

select to_char(qdate,'Month') "MONTH", count(quotationid) "QUOTATIONCOUNT" from quotation group by to_char(qdate,'Month');

select to_char(qdate,'Month') "MONTH", count(quotationid) "QUOTATIONCOUNT" from quotation;

select to_char(qdate,'Month') "MONTH", count(quotationid) "QUOTATIONCOUNT" from quotation order by to_char(qdate,'Month');

select to_char(qdate,'Month') "MONTH", count(quotationid) "QUOTATIONCOUNT" from quotation group by to_char(qdate,'Month') group by;

Retrieve the orderid and the number of months between pymtdate and orderdate for all orders where the number of months is more than 0.1 and the payment has been done. Round off the number of months up to two decimal places.

select orderid,round(months_between(pymtdate,orderdate),2) "No of Months" from orders where round(months_between(pymtdate,orderdate),2)>0.1;

select orderid,round(months_between(pymtdate,orderdate),0) "No of Months" from orders where round(months_between(pymtdate,orderdate),2)>0.1;

select orderid,round(months_between(pymtdate,orderdate),2) "No of Months" from orders where round(months_between(pymtdate,orderdate),2)<0.1;

select orderid,round(months_between(pymtdate,orderdate),2) "No of Months" from orders where round(months_between(pymtdate,orderdate),2)>1;

Write a query to display the sale id, no. of months between the saledate and today's date as "MONTH_AGED" for all the sales. Show positive values for no. of months and round it to 1 decimal places.

select saleid, round(abs(months_between(sldate,sysdate)),1) "MONTH_AGED" from sale;

select saleid, round(abs(months_between(sldate,sysdate)),1) "MONTH" from sale;

select saleid, round(abs(months_between(sldate,sysdate)),2) "MONTH_AGED" from sale;

select saleid, round(abs(months_between(sldate,sysdate)),0) "MONTH_AGED" from sale;

Display the concatenated value of Salesman Name and his location. Use constant string ' is from ' for concatenation (contains single space at beginning and end).

select concat(concat(sname,' is from '),location) "RESULT" from salesman;

select concat(sname,' is from '),location) "RESULT" from salesman;

select concat(concat(sname,' is from ',location) "RESULT" from salesman;

select concat(concat(sname,' is from '),location) from salesman;

Retrieve the itemtype , category of item, and average price rounded to two decimal places of FMCG, Computer type items. The average price must be less than 2000.

select itemtype,category,round(avg(price),2) "Average item price" from item where itemtype IN('Computer','FMCG') group by itemtype,category having round(avg(price),2)<2000;

select itemtype,category,round(avg(price),2) "Average item price" from item where itemtype IN('Computer','FMCG') group by itemtype,category having round(avg(price),2)<=2000;

select itemtype,category,round(avg(price),2) "Average item price" from item where itemtype IN('Computer','FMCG') group by itemtype,category having round(avg(price),1)<2000;

select itemtype,category,round(avg(price),2) "Average item price" from item where itemtype IN('Computer','FMCG') group by itemtype,category having round(avg(price),0)<2000;

Identify the average salary of the jobs 'MANAGER' and 'ANALYST'. Display job , average salary if the identified average salary is more than 1500.

select job, avg(sal) "Average Salary" from emp where job in('MANAGER','ANALYST') group by job having avg(sal)>1500;

select job, avg(sal) "Average Salary" from emp where job in('MANAGER','ANALYST') group by job having avg(sal)>=1500;

select job, avg(sal) "Average Salary" from emp where job in('MANAGER') group by job having avg(sal)>1500;

select job, avg(sal) "Average Salary" from emp where job in('MANAGER','ANALYST') order by job having avg(sal)>1500;

which of the examples are valid subqueries

subquery in select ,where,having and from

subquery in select,where and from

Where can subqueries be used? (Choose all that apply)

The WHERE clause in SELECT as well as all DML statements

The HAVING clause in the SELECT statement

The FROM clause in the SELECT statement

Field names in the SELECT statement

The GROUP BY clause in the SELECT statement

Independent sub query is the one where

the inner query has no reference to the outer query

the inner and the outer query are from two different tables

the inner and outer queries are for the same table but with different condition

Sale Table SALEID SID SLDATE 1001 1 01-JAN-14 1002 5 02-JAN-14 1003 4 01-FEB-14 1004 1 01-MAR-14 1005 2 01-FEB-14 1006 1 01-JUN-15 Display the sale id and date for most recent sale.

SELECT SALEID,SLDATE FROM SALE WHERE SLDATE=(SELECT MAX(SLDATE) FROM SALE);

SELECT SALEID,SLDATE FROM SALE WHERE SLDATE=(SELECT MIN(SLDATE) FROM SALE);

SELECT SALEID,SLDATE FROM SALE WHERE SLDATE=(SELECT AVG(SLDATE) FROM SALE);

Salesman Table SID SNAME LOCATION 1 Peter London 2 Michael Paris 3 John Mumbai 4 Harry Chicago 5 Kevin London 6 Alex Chicago Sale Table SALEID SID SLDATE 1001 1 01-JAN-14 1002 5 02-JAN-14 1003 4 01-FEB-14 1004 1 01-MAR-14 1005 2 01-FEB-14 1006 1 01-JUN-15 Display the names of salesmen who have made at least 2 sales. (SELECT ALL THAT APPLY)

SELECT SNAME FROM SALESMAN S WHERE EXISTS (SELECT COUNT(SID) FROM SALE S1 WHERE S.SID=S1.SID HAVING COUNT(SID)>=2);

SELECT SNAME FROM SALESMAN S,SALE S1 WHERE S.SID=S1.SID GROUP BY SNAME HAVING COUNT(S1.SID)>=2;

SELECT SNAME FROM SALESMAN S WHERE EXISTS (SELECT COUNT(SID) FROM SALE S1 HAVING COUNT(SID)>=2);

SQL

Authored by Muralidhara Srirama

Computers

University - Professional Development

Used 49+ times

AI Actions

Add similar questions

Adjust reading levels

Convert to real-world scenario

Translate activity

More...

Content View

Student View

28 questions

Show all answers

MULTIPLE SELECT QUESTION

1 min • 1 pt

ALTER TABLE TABELNAME CHANGE

NEWCOLNAME NEWDATATYPE

NEWCOLNAME OLDDATATYPE

OLDCOLNAME NEWCOLNAME NEWDATATYPE

OLDCOLNAME NEWCOLNAME OLDDATATYPE

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

What is def in the following statement?
CREATE TRIGGER abc (...) (...) ON def FOR EACH ROW ghi;

trigger name

table name

trigger statement

MULTIPLE CHOICE QUESTION

1 min • 1 pt

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';

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

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;

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

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;

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

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;

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

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';

Access all questions and much more by creating a free account

Create resources

Host any resource

Get auto-graded reports

Continue with Google

Continue with Email

Continue with Classlink

Continue with Clever

or continue with

Microsoft

Apple

Others

Already have an account?

Similar Resources on Wayground

25 questions

Basics of Web Technology

Quiz

•

University

24 questions

A+ - 16C - Configure SOHO Router Security

Quiz

•

Professional Development

24 questions

Swift Property Observers

Quiz

•

University

25 questions

PYTHON PROGRAMMING STRING Q6

Quiz

•

University

25 questions

BDA-UNIT-2

Quiz

•

University

25 questions

Module 1.1: Common Threats

Quiz

•

11th Grade - University

25 questions

Angular 4

Quiz

•

University

23 questions

Sec+ Extra Study

Quiz

•

Professional Development

Popular Resources on Wayground

15 questions

Fractions on a Number Line

Quiz

•

3rd Grade

20 questions

Equivalent Fractions

Quiz

•

3rd Grade

25 questions

Multiplication Facts

Quiz

•

5th Grade

$fractions$

22 questions

fractions

Quiz

•

3rd Grade

20 questions

Main Idea and Details

Quiz

•

5th Grade

20 questions

Context Clues

Quiz

•

6th Grade

15 questions

Equivalent Fractions

Quiz

•

4th Grade

20 questions

Figurative Language Review

Quiz

•

6th Grade

Discover more resources for Computers

30 questions

Quiz 1 Review

Quiz

•

University

SQL

ALTER TABLE TABELNAME CHANGE

What is def in the following statement? CREATE TRIGGER abc (...) (...) ON def FOR EACH ROW ghi;

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.

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.

Retrieve the itemcode and average of quantity available in retail outlets where the average of quantity available is less than 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.

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

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

Retrieve the different item types.

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

Access all questions and much more by creating a free account

Similar Resources on Wayground

Popular Resources on Wayground

Discover more resources for Computers

What is def in the following statement?
CREATE TRIGGER abc (...) (...) ON def FOR EACH ROW ghi;