SQL OR DBMS SQL QUERIES 3

Table Structure

Book
(BookID, Title, Author, Publishers, Price, DistID)
Distributors
(DistID, Name, City)

i) Display the details of books whose price is more than the average price of all books.

Select * from book where price > ( select avg(price) from book)

ii) Display the details of books written by ‘Groff’ and published by ‘TMGH’

select * from book where Author=’Groff’ and publisher=’TMGH’

iii) Create a view to show Title, Author, Publisher and Distributor’s Name & name this view as ShowDetails.

create view ShowDetails (Title, Author, Publisher, Distributor)
as select title, author, publisher, name
from book , distributor
where book.distid = distributor.distid.

SQL OR DBMS SQL QUERIES 2

Table Structure

Emp

(Empid, Ename, CompID, salary, Joindate, Gender, City)

Company

(CompID, CompName, City)

 i) Give name of companies located in those cities where TATA is located.

 Select companyName from company

where city in (select city from company where companyName = ‘TATA’)

 

 ii) Give the name of the employees living in the same city where their company is located.

Select ename from Emp e, company c

 where e.city=c.city

 

iii) Update salary of employee ‘Raj’ by giving him the salary of ‘Radha’ working in same company.

update Emp set salary = (select salary from Emp where Ename = ‘Radha’)

where Ename = ‘Raj’

and compid = (Select compid from emp where ename=’Radha’)

iv) Display how many male and female members have joined in January 2004.

 

Select count(ename) from emp

group by gender having to_char(joindate,’mon-yyyy’)=’jan-2004’

v) Display the total number of companies located in each city.

Select count(compid) from company group by city

SQL OR DBMS SQL QUERIES 1

Table Structure

Sales_Header

(orderno, orderdate, customerID, salesmanid, paymentStatus, TransactionType, paymentdate)

Sales_detail

(Orderno, ProductID, Qty, Rate, Amt)

i) Display the order, which were issued in first Quarter of Current year

Select orderno from sales_header where to_char(orderDate,’mm’)<4 and to_char(orderDate,’yy’) = to_char(sysdate,’yy’)

ii) Display the order number, order date, customer id and order amount for orders having value of 500 Rs.

select orderno,orderdate,cutomerid, amt from sales_header as h, sales_detail as d

where h.orderno = d.orderno and amt>=500

iii) Display the order detail where RIN001 soap is sold for Min of 50 Rs

select h.* from sales_header as h, sales_detail as d

where h.orderno = d.orderno and productid=’RIN001’ and amt>=50

iv) Display the order collected by Executive no ‘S120’

select orderno, amt from sales_header as h, sales_detail as d

where h.orderno = d.orderno and h.salesmanid = ‘S120’

v) Display the unpaid order in ascending order of Order no

select orderno, orderdate from sales_header

where paymentstatus = ‘unpaid’ order by orderno

vi) Assign the privileges to see the data from both tables to ‘Raj’

Grant select on sales_header, sales_details to ‘Raj’

vii) Display the various types of product sold to customers.

Select distinct productid from sales_header as h, sales_details as d where h.orderno = d.orderno

viii) Display all credit transaction, with their payment status done in Dec. 2003

select * from sales_header where transactiontype=’credit’ and paymentstatus = ‘paid’ and to_char(paymentdate,’mon-yyyy’) = ‘dec-2003’

xi) Display the details of total cash transaction done by each sales executives.

Select * , sum(amt) from sales_header as h, sales_detail as d where h.orderno = d.orderno and transactiontype = ‘cash’ and paymentstatus = ‘paid’ group by salesmanid

SQL OR DBMS SQL QUERIES

QCreate a table calling_card with the attributes company_name, card_number,
starting_value, value_left and pin_number
Assumptions:
o Attribute company_name may have upto 25 characters
o Attributes starting_value and value_left are measured in rupees and paisa
o Card_number may have up to 15 digits
o Pin_number is always 12 characters long

------->ANSWER

SQL>
CREATE TABLE CALLING_CARD_743
2 (
3 COMPANY_NAME VARCHAR(25),
4 CARD_NO VARCHAR(15),
5 START_VALUE NUMBER(4,2),
6 VALUE_LEFT NUMBER(4,2),
7 PIN_NO CHAR(12)
8 );

Table created.

Q Rewrite the CREATE TABLE command with attributes card_number identified as the

primary key and pin_number identified as unique.

--------->ANWSER

SQL> 

CREATE TABLE CALLING_CARD_743

  2 (

  3 COMPANY_NAME VARCHAR(25),

  4 CARD_NO VARCHAR(15) PRIMARY KEY,

  5 START_VALUE NUMBER(4,2),

  6 VALUE_LEFT NUMBER(4,2),

  7 PIN_NO CHAR(12) UNIQUE

  8 );

Table created.

QRewrite the CREATE TABLE command using named constraints.

 --------->ANWSER

SQL> DROP TABLE CALLING_CARD_743

  2 ;

Table dropped.

SQL> CREATE TABLE CALLING_CARD_743

  2 (

  3 COMPANY_NAME VARCHAR(25),

  4 CARD_NUMBER VARCHAR(15) CONSTRAINT CCARD_NUM_PK_743 PRIMARY KEY,

  5 STARTING_VALUE NUMBER(4,2),

  6 VALUE_LEFT NUMBER(4,2),

  7 PIN_NUMBER CHAR(12) CONSTRAINT CCPIN_NUM_U_743 UNIQUE

  8 );

Table created