DBMS Laboratory Exercise 7- week 10

Database Management Systems I

Laboratory Exercise 7- week 10

Joins

---

Try out the following queries and write down what each query generates

1. select name from sys.databases
2. use DBMSI

select * from sys.tables

3. select * from sys.databases
4. select * from INFORMATION_SCHEMA.COLUMNS 

Use DBMS1 database and answer the following questions.

1. Make a list of customer names and the respective states they live in.
2. Display the stock details with the corresponding manufacture’s name.
3. Prepare a list of manufacturers that supply kickboards.
4. Find the number of customers live in each state. Display the name of the state and the number of customers.
5. Make a list of customers who have placed orders that weigh greater than 180. Display the customer’s First name along with the shipping weight.
6. Who are the customers that have ordered more than 100 items?
7. Find the orders which are having a total price greater than $20,000. Prepare a list containing order number, the first name of the customer who has placed the order and the total price.
8. Display the First name of the customers who have ordered swimcaps.

ANSWERS

--Q2

select *

from sys.tables

--Q3

select *

from sys.databases

--Q4

select *

from INFORMATION_SCHEMA.COLUMNS

--Q1

select *

from customer

select *

from state

select c.fname,c.lname,s.sname

from customer c,state s

where c.state=s.code;

--Q2

select * 

from stock

select *

from manufact

select s.stock_num,s.manu_code,s.description,s.unit_price,s.unit,m.manu_name

from stock s,manufact m

where s.manu_code=m.manu_code

--Q3

select * 

from manufact

select *

from stock

select m.manu_code,m.manu_name,s.description,s.unit_price

from manufact m,stock s

where m.manu_code=s.manu_code and s.description='kick board'

--Q4

select *

from customer

select *

from state

select s.sname,COUNT(fname) as no_of_customers

from customer c,state s

where c.state=s.code

group by s.sname

--Q5

select *

from customer

select *

from orders

select c.fname,o.ship_weight

from customer c,orders o

where c.customer_num=o.customer_num and o.ship_weight>180

--Q6

select *

from orders

select *

from customer

select *

from items

select o.customer_num,i.order_num

from items i,customer c, orders o

where c.customer_num=o.customer_num and i.order_num=o.order_num

group by i.order_num,o.customer_num

having SUM(i.quantity)>100

--Q7

select *

from orders

select *

from customer

select *

from items

select i.order_num,c.fname,sum(i.total_price) as total_price

from customer c,orders o,items i

where c.customer_num=o.customer_num and o.order_num=i.order_num

group by i.order_num,c.fname

having SUM(i.total_price)>20000

--Q8

select *

from customer

select *

from orders

select *

from items

select *

from stock

select distinct c.fname,s.description

from stock s,customer c,items i,orders o

where s.stock_num=i.stock_num and i.order_num=o.order_num and o.customer_num=c.customer_num and s.description='swimcap'