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

Create table in mysql

click here to download DBMS 1 practical 5
answers for dbms 1 practical 5


create table dept_1 (
dno int primary key,
dname varchar(30)
);




create table emp (
eno int primary key,
nic varchar(30),
ename varchar(30),
address varchar(30),
dnum int
references dept_1(dno)
);




create table project_1(
pno int primary key,
pname varchar(30),
location varchar(30),
);


create table worked_on(
eno int,
pno int,
hired_date datetime,
houres int,
primary key (eno,pno),
constraint worked_on_fk1 foreign key (eno)
references emp,
constraint worked_on_fk2 foreign key (pno)
references project_1


);






insert into dept_1 values(5,'IT')
insert into dept_1 values(6,'IS')
insert into dept_1 values(7,'CSN')
insert into dept_1 values(8,'IT')




insert into emp values(100,'901250087V','Smith,John B','731 Fondren Houston',5)
insert into emp values(101,'895290452V','Wrong, Franklin T.','638 Voss Houston',5)
insert into emp values(102,'923859070V','Zelaya,f Alicia J.','33321 Castle Spring',4)
insert into emp values(103,'918257988V','Wallace, Jennifer S','291 Berry Bellaria',4)
insert into emp values(104,'904850045V','Narayan, Remesh K.','975 Fire Oak Humble',5)
insert into emp values(105,'889950600V','English, Joyce A.','5631 Rice Houston',1)
insert into emp values(106,'915850123V','Jabbar, Ahamad V','980 Dallas Housr',1)




insert into project_1 values(1,'Road Construction','Bellaire')
insert into project_1 values(2,'Power plant','Sugarland')
insert into project_1 values(3,'Reproduction','Houston')
insert into project_1 values(4,'Recycling','Stafford')




insert into worked_on values(100,1,12-04-2011,12)
insert into worked_on values(101,2,25-02-2011,1)
insert into worked_on values(102,3,15-05-2011,20)
insert into worked_on values(103,4,02-06-2011,50)
insert into worked_on values(104,5,08-06-2011,5)
insert into worked_on values(105,6,12-07-2011,3)
insert into worked_on values(106,7,20-07-2011,40)
insert into worked_on values(100,1,18-05-2011,1)
insert into worked_on values(100,1,01-07-2011,11)
insert into worked_on values(100,1,27-06-2011,1)

Read More

Database management systems DBMS

download practical 3 question DBMS 1
answers for dbms 1 practical 3

--Q1
select *
from customer
order by customer_num desc;


--Q2
select *
from customer
where lname like '[A-G]%'
order by state,city;


--Q3
select stock_num,manu_code,unit_price as old_price,unit_price*115/100 as new_price
from stock
where manu_code='ANZ';


--Q4
select *
from items;


select COUNT(*)
from items;


--Q5
select *
from customer


select COUNT(*)
from customer;


--Q6
select *
from orders;


select AVG(ship_charge)as average
from orders;


--Q7
select MAX(ship_charge)as max, MIN(ship_charge)as min
from orders;


--Q8
select COUNT(*)
from stock
where description='baseball gloves';


--Q9


select *
from stock;


select AVG(unit_price)
from stock
where description='tennis racquet';


--Q10
select stock_num,manu_code,unit_price as old_price,unit_price*115/100 as new_price,
round(unit_price*115/100,1)
from stock
where manu_code='ANZ';


--Q11
select  sum(unit_price) as sum
from stock;


--Q12
select MAX(quantity) as highest_quantity_ordered
from items;


--Q13
select *
from stock;


select COUNT(distinct manu_code)
from stock;

Read More

Database management systems

download practical 2 question

select *
from customer;


--Q2
select fname,lname
from customer
where state='FL';


--Q3
select address1,address2
from customer
where customer_num='16';


--Q4
select fname,lname
from customer
where state='CA' and city like 'Redwood%';


--Q5
select *
from manufact
where manu_code='NRG';


--Q6
select *
from stock;


select unit_price,description,manu_code
from stock
where manu_code='HRO' or manu_code= 'HSK';


--Q7
select *
from orders;


--Q8
select *
from stock
where unit_price between 100 and 250;


--Q9
select customer_num,fname,lname
from customer
where city is NULL;


--Q10
select *
from orders
where backlog is NULL;


--Q11
select *
from orders
where order_num in (5, 8 ,10);


--Q12
select fname,lname,city,phone
from customer
where state in ('FL','NJ','NY','MA');


--Q13
select *
from manufact
where manu_code like 'H%';


--Q14
select *
from manufact
where manu_name like '[A-N]%';


--Q15
select fname,lname
from customer
where lname like '[D-H]%' or 'R%'

Read More