SQL QUERY practical-5


                                    PRACTICAL-5


CREATE TABLE SALESMEN(SNUM VARCHAR2(6) PRIMARY KEY CHECK(SNUM LIKE 'S%'), SNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), COMM NUMBER(5,2));

INSERT INTO SALESMEN VALUES('S1001','PIYUSH','LONDON',0.12);
INSERT INTO SALESMEN VALUES('S1002','NIRAJ','SAN JOSE',0.13);
INSERT INTO SALESMEN VALUES('S1003','MITI','LONDON',0.11);
INSERT INTO SALESMEN VALUES('S1004','RAJESH','BARCELONA',0.15);
INSERT INTO SALESMEN VALUES('S1005','HARESH','NEW YORK',0.10);
INSERT INTO SALESMEN VALUES('S1006','RAM','BOMBAY',0.10);
INSERT INTO SALESMEN VALUES('S1007','NEHAL','DELHI',0.09);


CREATE TABLE CUSTOMER(CNUM VARCHAR2(6) PRIMARY KEY CHECK(CNUM LIKE 'C%'), CNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), RATING NUMBER(5), SNUM VARCHAR2(6));

INSERT INTO CUSTOMER VALUES('C2001','HARDIK','LONDON',100,'S1001');
INSERT INTO CUSTOMER VALUES('C2002','GEETA','ROME',200,'S1003');
INSERT INTO CUSTOMER VALUES('C2003','KAVISH','SAN JOSE',200,'S1002');
INSERT INTO CUSTOMER VALUES('C2004','DHRUV','BERLINE',300,'S1002');
INSERT INTO CUSTOMER VALUES('C2005','PRATHAM','LONDON',100,'S1001');
INSERT INTO CUSTOMER VALUES('C2006','VYOMESH','SAN JOSE',300,'S1007');
INSERT INTO CUSTOMER VALUES('C2007','KIRIT','ROME',100,'S1004');
 

CREATE TABLE ORDERED(ONUM VARCHAR2(6) PRIMARY KEY CHECK(ONUM LIKE 'O%'), ATM NUMBER(10,2) NOT NULL, ODATE DATE, CNUM VARCHAR2(6), SNUM VARCHAR2(6));

INSERT INTO ORDERED VALUES('O3001',18.69,'10-MAR-90','C2008','S1007');
INSERT INTO ORDERED VALUES('O3003',767.19,'10-MAR-90','C2001','S1007');
INSERT INTO ORDERED VALUES('O3002',1900.10,'03-OCT-90','C2007','S1004');
INSERT INTO ORDERED VALUES('O3005',5160.45,'04-OCT-90','C2003','S1002');
INSERT INTO ORDERED VALUES('O3006',1098.16,'10-MAR-90','C2008','S1007');
INSERT INTO ORDERED VALUES('O3009',1713.23,'10-APRIL-90','C2002','S1003');
INSERT INTO ORDERED VALUES('O3007',75.75,'10-APRIL-90','C2004','S1002');
INSERT INTO ORDERED VALUES('O3008',4723.00,'10-MAY-90','C2006','S1001');
INSERT INTO ORDERED VALUES('O3010',1309.95,'10-MAY-90','C2004','S1002');
INSERT INTO ORDERED VALUES('O3011',9891.88,'10-JUNE-90','C2006','S1001');


SELECT


WRITE A SELECT COMMAND THAT PRODUCES THE RATING FOLLOWED BY THE NAME OF EACH CUSTOMER IN SAN JOSE.
ANSWER:
Select cname,rating from customer where city=’san jose’ order by cname;
DISPLAY SNUM VALUES OF ALL SALESMEN WITHOUT ANY REPEAT.
ANSWER:
Select distinct snum from salesman; 

SPECIAL OPERATORS


DISPLAY ALL SALESMEN THAT WERE LOCATED IN EITHER BARCELONA OR LONDON(USE IN KEYWORD).
ANSWER:
Select * from salesman where city in(‘bercelona’,’london’);


DISPLAY ALL SALESMEN WITH COMMISSION BETWEEN 0.10 AND 0.12.
ANSWER:
Select * from salesman where comm between 0.10 and 0.12;


LIKE OPERATORS


LIST ALL THE CUSTOMERS WHOSE NAME’S THIRD LATTER IS ‘R’.
ANSWER:
Select * from customer where cname like ‘__r%’;


LIST ALL SALESMEN WHOSE SNAME START WITH LETTER ‘P’ AND END LETTER IS ‘H’.
ANSWER:
Select * from salesman where sname like’p%’ and sname like’%h’;


NULL OPERATORS


FIND ALL RECORDS IN CUSTOMER TABLE WITH NULL VALUES IN THE CITY COLUMN.
ANSWER:
Select * from customer where city=NULL;


WRITE A TWO QUERIES THAT WILL PRODUCE ALL ORDERS TAKEN ON OCTOBER 3RD OR 4TH ,1990 (USE BETWEEN OPERATOR)
ANSWER:
Select * from ordered where odate between ‘3-oct-1990’ and ‘4-oct-1990’; 


WRITE A QUERY THAT SELECTS ALL ORDERS WITHOUT ZEROS OR NULLS IN AMT FIELD.
ANSWER:
Select * from ordered where atm!=NULL or atm!=0;


FUNCTIONS


TO COUNT THE NUMBERS OF SALESMEN WITHOUT DUPLICATION IN THE ORDERS TABLES.
ANSWER:
Select count(distinct snum) from salesman;


COUNT THE RATING OF CUSTOMERS (WITH NULL AND WITHOUT NULL).
ANSWER:
Select count(rating) from customer;


FIND THE LARGEST ORDER TAKEN BY EACH SALESPERSON.(HINT: USE GROUP BY)
ANSWER:
Select snum,max(round(atm)) “maximum amount” from ordered group by snum;


FIND THE LARGEST ORDER TAKEN BY EACH SALESPERSON ON EACH DATE.
ANSWER:
Select snum,odate,max(round(atm)) “maximum amount” from ordered group by snum,odate;


FIND OUT WHICH DAY HAD THE HIGHER TOTAL AMOUNT ORDERED.
ANSWER:
select odate,max(atm) from ordered group by odate;


WRITE A QUERY THAT COUNTS THE NUMBER OF DIFFERENT NON-NULL CITY IN THE CUSTOMER TABLE.
ANSWER:
Select count(distinct city) from customer;


DISPLAY ALL THE INFORMATION IN DESCENDING ORDERS(USE COLUMN CNUM).
ANSWER:
Select * from customer order by cnum desc;


DISPLAY SNAME AND COMM. FROM SALESMEN IN DESCENDING ORDER(IN PLACE OF COLUMN NAME USE COLUMN NUMBER).
ANSWER:
Select sname,comm from salesman order by rownum desc;


ASSUME EACH SALESPERSON HAS A 0.12 COMMISSION. WRITE A QUERY ON THE ORDERS TABLE THAT WILL PRODUCE THE ORDER NUMBER,THE SALESPERSON NUMBER AND THE AMOUNT OF THE SALESPERSON’S COMMISSION FOR THAT ORDER.
ANSWER:
Select o.onum,s.snum,s.comm from ordered o,salesman s where o.snum=s.snum;


WRITE A QUERY ON THE CUSTOMERS TABLE THAT WILL FIND THE HIGHEST RATING IN EACH CITY. PUT THE OUTPUT IN THIS FORM.
FOR THE CITY (CITY) , THE HIGHEST RATING IS: (RATING).
ANSWER:
select city “ciy”,max(rating) “the highest rating is” from customer group by city;


WRITE A QUERY THAT TOTALS THE ORDERS FOR EACH DAY AND PLACES THE RESULTS IN DESCENDING ORDER.
ANSWER:
select snum(atm),odate rom ordered group by sum(atm) desc;


JOIN


SHOW THE NAMES OF ALL CUSTOMERS MATCHED WITH THE SALESMEN SERVING THEM.
ANSWER:
select c.cname,s.sname from customer c,salesman s where c,snum=s.snum;

WRITE A QUERY THAT LISTS EACH ORDER NUMBER FOLLOWED BY THE NAME OF THE CUSTOMER WHO MADE THE ORDER.
ANSWER:
select o.onum,c.cnum from ordered o,customer c where o.cnum=c.cnum;
 
WRITE A QUERY THAT GIVES THE NAMES OF BOTH THE SALESPERSON AND THE CUSTOMER FOR EACH ORDER AFTER THE ORDER NUMBER.
ANSWER:
select o.onum,c.cname,s,sname from ordered o,salesman s,customer c where o.snum=s.snum and o.cnum=c.cnum;


WRITE A QUERY THAT PRODUCES ALL CUSTOMERS SERVICED BY SALESMEN WITH A COMMISSION ABOVE 0.12. OUTPUT THE CUSTOMER’S NAME, THE SALESPERSON’S NAME AND THE SALESPERSON’S RATE OF COMMISSION.
ANSWER:
Select s.sname,c.cname,s.comm from salesman s,customer c where c.snum=s.snum and s.comm>0.12;

OTHERS WRITE A QUERY THAT CALCULATES THE AMOUNT OF THE SALESPERSON’S COMMISSION ON EACH ORDER BY A CUSTOMER WITH A RATING ABOVE 100.
ANSWER: 
select 0.atm*s.comm from ordered o,salesman s,customer c where o.cnum=c.cnum and o.snum=s.snum and c.rating>100;
  

FIND ALL CUSTOMERS WITH ORDERS ON 3RD OCTOBER 1990 USING CORRELATE SUB QUERY.
ANSWER:
select cname from ordered o, customer c where o.cnum c.cnum and to_char(odate,’dd-mon-yyyy’)=’03-oct-1990’;


FIND ALL CUSTOMERS HAVING RATING GREATER THAN ANY CUSTOMER IN ‘ROME’.
ANSWER:
select * from customer where rating>(select max(rating) from customer where city=’rome’);


CREATE ANOTHER TABLE LONDON_STAFF HAVING SAME STRUCTURE AS SALESMEN TABLE.
ANSWER:
create table London_staff(snum,sname,city,comm) as select snum,sname,cty,comm. From salesman;


DELETE ALL SALESMEN WHO HAVE AT LEAST ONE CUSTOMER WITH A RATING OF 100 FROM SALESMEN TABLE.
ANSWER:
Delete from salesman where snum in(select snum from customer where rating=100); 

Comments

Post a Comment

Popular posts from this blog

SQL QUERY FOR PRIMARY AND FOREIGN KEY

Practical-3 1. Create table Account_key Column name Data Type Size Attributes Acc_no Varchar2 5 Primary key / first letter must start with ‘A’ Name Varchar2 30 NOT NULL City Varchar2 20 NOT NULL Balance Number 10,2 Balance >=500 Loan_taken Varchar2 3 Values(‘NO’,’YES’) ANS . create table account_key(acc_no varchar2(5) primary key check(acc_no like 'A%'),name varchar2(30) not null,city varchar2(20) not null,balance number(10,2) check(balance>=500),loan_taken varchar2(3) check(loan_taken='yes' or loan_taken='no')); acc_no Name City Balance loan_taken A001 Patel Jigar Mehsana 50000 YES A002 Patel Ramesh Mehsana 50000 YES A003 Dave Hardik Ahmeda
Read more

SQL QUERY

                                Prac tical-2 1. Retrieve specified information for the account holder who are not in ‘Ahmedabad’. Ans: select * from account where city not in ‘Ahemdabad’; 2. Retrieve specified information for the account holder who are not in ‘Ahmedabad or ‘Vadodara’. Ans: select * from account where city not in('Ahemadabad', 'Vadodara'); 3. Retrieve those records of Account holder whose balance between is 50000 and 100000. Ans: select * from account where balance between 50000 and   1000000; .4. Retrieve those records of Account holder whose balance not between is 50000 and 100000. Ans: select * from account where balance not between 50000 and 100000; 5. Display only those records whose amount is 5000, 25000, 30000. Ans: select * from Transaction where amount in (5000, 25000, 30000); 6. Display only those records whose amount not in 5000, 25000, 30000. Ans:   select * from Transaction where Amt not in (5
Read more

Practicals of RollBack, Commit and Cursor programs

Image
                                                                 Practical-10 AIM :- RollBack   , Commit and Cursor bases Programs. 1. Analyze output of following scripts: A.      Begin                               Insert into new_salary values ('6', '6000');                               Update new_salary set salary=4000 where id=4;                               Commit;                               Rollback;                 End;               / Ans:-                B.   Begin                                 Insert into new_salary values (8, 8000);                Update new_salary set salary=50000 where id=5;                Savepoint   first;                    Rollback to savepoint first;        End;        /               Ans:- 2. Write pl/sql blocks that first insert the new record into table salary. Update the salary of employee name ‘Riya’ and ‘Rima’ by 7000/- then check the total salary   which does
Read more

Programs Based on Exception Handling and Trigger.

                                                       PRACTICAL-11 Aim :-   Programs Based on Exception Handling and Trigger. 1. Table: STUDENT (sr_no, name, address). Write a pl/sql code to retrieve the data from                student table whose sr.no is 02ce33. if     data not found then show the message “data not                found “  (Use EXCEPTION in pl/sql code). Ans :- declare no int; nm varchar2(20); begin no:=&no; select NAME into nm from student where ROLLNO=no; dbms_output.put_line('Name of student is '||nm); exception when NO_DATA_FOUND then dbms_output.put_line('No data found!!!'); end; / 2. Give Example of “TOO_MANY_ROWS” Exception.     Give Example of Unnamed system Exception.(Give Example of Primary key constraint   violation) Ans:- declare nm varchar2(20); begin select name into nm from student; dbms_output.put_line('Name of student is '||nm);   exception when TOO_MANY_R
Read more