KEMBAR78
Data Base Lab 4 | PDF | Sql | Databases
Scribd
Upload
38 views2 pages

Data Base Lab 4

The document outlines a database lab exercise involving the creation and manipulation of two tables: 'orders' and 'PERSONS'. It includes SQL commands for inserting data, selecting data with various join operations, and querying specific information based on conditions. Additionally, it demonstrates the use of outer joins and filtering on hire dates in employee records.

Uploaded by

rowen prather
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
38 views2 pages

Data Base Lab 4

The document outlines a database lab exercise involving the creation and manipulation of two tables: 'orders' and 'PERSONS'. It includes SQL commands for inserting data, selecting data with various join operations, and querying specific information based on conditions. Additionally, it demonstrates the use of outer joins and filtering on hire dates in employee records.

Uploaded by

rowen prather
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Data base lab 4

1. Create table orders (O_Id int primary key, Orderno int, P_Id int);
2. insert all
a. into orders(O_Id int primary key, Orderno int, P_Id int)values ( 1, 77895, 3)
b. into orders(O_Id int primary key, Orderno int, P_Id int)values ( 2, 44678 ,3)
c. into orders(O_Id int primary key, Orderno int, P_Id int)values ( 3 ,22456 ,1)
d. into orders(O_Id int primary key, Orderno int, P_Id int)values ( 4 ,24562, 1)
e. into orders(O_Id int primary key, Orderno int, P_Id int)values ( 5, 34764, 15)
select * from orders;
3. create table PERSONS (p_Id int, Lastname varchar(10), Firstname varchar(10), Address
varchar(20),city varchar(10));
4. inset all
a. into PERSONS (p_Id int, Lastname varchar(10), Firstname varchar(10), Address
varchar(20),city varchar(10)) values ( 1 ,Hansen ,Ola, Timoteivn 10, Sandnes)
b. into PERSONS (p_Id int, Lastname varchar(10), Firstname varchar(10), Address
varchar(20),city varchar(10)) values ( 2 ,Svendson ,Tove, Borgn 23, Sandnes)
c. into PERSONS (p_Id int, Lastname varchar(10), Firstname varchar(10), Address
varchar(20),city varchar(10)) values ( 3 ,Pettersen ,Kari ,Storgt 20, Stavanger)
d. select * from persons
5. select column_name(s) from table1 left join table2 on
table1.column_name=table2.column_name;
6. select persons.lastname, persons.firstname, orders.o_id
from persons
fulll outer join orders
on persons.p_id = orders.p_id
7. select persons.lastname, persons.firstname, orders.o_id
from persons
fulll outer join orders
on persons.p_id = orders.p_id
order by persons.firstame;
8. select orders.o_id, persons.lastname, persons.firstname
from orders
right outer join persons
on orders.p_id = persons.p_id;
9. select orders.o_id, persons.lastname, persons.firstname
from orders
right outer join persons
n orders.p_id = persons.p_id
order by persons.lastname desc;
10. Select ename, dname from employee E, dept D where E.deptno -= D.deptno;
11. Select R.ename, D.dname, D.deptno, D.loc from employee E, dept D where
E.deptno=D.deptno
12. Select E.ename, E.sal,S.grade from emp E, salgrade S Where E.sal between S.losal and
S.hisal
13. Select emp.ename as ‘employee’, emp.empno as ‘empno’, emp.mgr as ‘mgrno’,
M.ename as ‘manager’ from emp left outer join emp M . N emo.mgr= m.empno;
14. Select ename from emo where mgr=’null’;
15. Select ename, hiredate from emp where (year(hiredate) = ‘1981%’ and month(hiredate) =
‘feb%’);

You might also like