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'

Read More

download DBMS practical 6
answers for the dbms1 practical 6

--Q1
create table Student(
sno varchar(30) primary key,
stName varchar(30),
address varchar(30),
phone int,
course varchar(30)
);


--Q2
insert into Student values('ST001','Nilusha','Galle',0992333333,'IT')
insert into Student values('ST002','Shan','Colombo',0112345345,'IT')
insert into Student values('ST003','Janani','Kandy',0812222222,'IS')


--Q4
select *
from Student


--Q5
alter table Student
add age int


--Q6
alter table Student
add last_name varchar(30)


update Student
set last_name='Perera'
where sno='ST001'


update Student
set last_name='Fernando'
where sno='ST002'


update Student
set last_name='Alwita'
where sno='ST003'


--Q7
alter table Student
add constraint chk_crs  check (course in ('IT','IS','CSN'))


--Q8
insert into Student values ('ST004','Heshan','Colombo-03',0112789658,'BM','De Silva')


--Q11
alter table Student
drop column age


--Q12


update Student
set phone=0112456789
where sno='ST002'


update Student
set address='Karapitiya'
where sno='ST001'




--Q13
delete
from Student
where sno='ST003'


--Exercise 2
drop table dept
create table dept(
deptno char(3) primary key,
deptname varchar(36),
location varchar(36)
);


create table emp_1(
empno char(6),
firstname varchar(50),
lastname varchar(50),
workdept char(3),
phoneno int,
sex char(1),
birthdate datetime,
salary float,
constraint FK_dno foreign key(workdept)
references dept(deptno)


);






--Q1
insert into dept values('D01','Acount','Malabe')


--Q2
insert into emp_1 values('E001','Nilu','Perera','D01',011345678,'F',1987-10-12,10000)




--Q3
insert into emp_1 values('E002','Saman','De Silva','D02',033226007,'F',1974-05-17,17000)


--Q5
alter table emp_1
drop constraint FK_dno


--Q6
insert into emp_1 values('E002','Saman','De Silva','D02',033226007,'F',1974-05-17,17000)

Read More