PROGRAM 1:(TEAM)
-------------------
create table [Link](Name varchar(20),
Id integer(3),
Domain varchar(20),
Salary Integer(10),
Gender char(2));
insert into [Link] values("Harika",539,"SQL",60000,"F");
insert into [Link] values("Arshiya",509,"Power",50000,"F");
insert into [Link] values("Saba",540,"SQL",60000,"F");
insert into [Link] values("Manasa",510,"SQL",50000,"F");
insert into [Link] values("Sana",523,"SQL",70000,"F");
insert into [Link] values("Mounika",NULL,"SQL",NULL,"F");
select * from [Link];
Alter table [Link] add column Phone_Number Integer(10);
select * from [Link] where Name like "%r%";
select * from [Link] where salary between 10000 and 50000;
select * from [Link] where salary>50000 and id>523;
select * from [Link] where salary>=50000 or id>523;
select * from [Link] where not salary>50000;
select name,id from [Link] where name in("harika","Arshiya","Saba");
select distinct * from [Link];
select * from [Link] order by Id desc;
select * from [Link] where id is not null;
-------------------------------------------------------------------------
---------------------------
PROGRAM 2:(STUDENT)
-----------------
create table [Link](
Rollno integer(3) unique primary key,
Stu_name varchar(20),
Class char(5),
Gender char(1));
insert into [Link] values(101,"Ravi","VIII","M");
insert into [Link] values(102,"Kumar","XI","M");
insert into [Link] values(103,"Laxmi","VII","F");
insert into [Link] values(104,"Ramya","VIII","F");
insert into [Link] values(105,"Rahul","X","M");
insert into [Link] values(106,"Mounika","VIII","F");
insert into [Link] values(107,"Lahari","X","F");
insert into [Link] values(108,"Kavya","XI","F");
insert into [Link] values(109,"Usha","VII","F");
insert into [Link] values(110,"Ramesh","XI","M");
select * from [Link];
select Rollno,Stu_name from [Link] where Class = "VIII";
select count(*) from [Link];
select min(rollno) from [Link];
select max(rollno) from [Link];
select avg(rollno) from [Link];
select sum(rollno) from [Link];
-------------------------------------------------------------------------
--------------------------
PROGRAM 2:(EMPLOYEE)
-----------------
create table [Link](
Eid integer(3),
Ename varchar(10),
Dno integer(3),
Esalary integer(10),
Age integer(2),
Phone_Number integer(20));
insert into [Link] values(101,"John",2,35000,50,24576);
insert into [Link] values(102,"Hari",3,40000,40,67328);
insert into [Link] values(103,"Henry",7,45000,40,87654);
insert into [Link] values(104,"Sam",5,50000,30,76542);
insert into [Link] values(105,"David",1,55000,45,98765);
insert into [Link] values(106,"Henry",2,35000,35,25432);
insert into [Link] values(107,"Smith",4,45000,32,67343);
insert into [Link] values(108,"Hari",6,55000,27,34678);
select * from [Link];
select Eid as id, Ename as name from [Link];
select * from [Link] order by eid;
-------------------------------------------------------------------------
-----------
PROGRAM 4:(DEPARTMENT)
-----------------
create table [Link](
Did integer(3),
Dname varchar(10),
Dno integer(3),
Dlocation varchar(10),
Pno integer(4));
insert into [Link] values(108,"Admin",1,"Hyd",44);
insert into [Link] values(101,"Accounts",2,"Bombay",11);
insert into [Link] values(100,"Packaging",3,"Mumbai",44);
insert into [Link] values(120,"Accounts",4,"Hyd",33);
insert into [Link] values(97,"Research",5,"Banglore",22);
insert into [Link] values(122,"Operation",6,"Chennai",44);
insert into [Link] values(107,"Packaging",7,"Mumbai",55);
select * from [Link];
desc [Link];
-------------------------------------------------------------------------
------------
PROGRAM 5:(PROJECT)
-----------------
create table [Link](
Pno integer(3),
Pname varchar(5),
Duration varchar(10),
Pid integer(4));
insert into [Link] values(11,"A","9 Months",102);
insert into [Link] values(22,"B","6 Months",97);
insert into [Link] values(33,"C","24 Months",120);
insert into [Link] values(44,"D","10 Months",107);
insert into [Link] values(55,"E","8 Months",108);
select * from [Link];
-------------------------------------------------------------------------
----------------
PROGRAM 6:(JOINS)
------------------
/*Joins*/
select [Link], [Link]
from [Link] inner join [Link] on
[Link]=[Link];
select [Link],[Link]
from [Link] left join [Link] on
[Link]=[Link];
select [Link],[Link]
from [Link] right join [Link] on
[Link]=[Link];
select Dno, Ename from [Link]
union
select Dno, dname from [Link];
select Dno,Ename from [Link]
union all
select Dno,Dname from [Link];
select eid, ename
from [Link]
group by eid;
select eid, esalary
from [Link]
group by esalary
having eid > 103;
select Dname,Dno
from [Link]
where Dno = any
(select Dno
from [Link]
where eid=103);
-----------------------------------------------------------------------
PROGRAM 7:(ALIAS)
---------------------
create table [Link](
Eid integer(3),
Ename varchar(10),
Dno integer(3),
Esalary integer(10),
Age integer(2),
Phone_Number integer(20));
insert into [Link] values(101,"John",2,35000,50,24576);
insert into [Link] values(102,"Hari",3,40000,40,67328);
insert into [Link] values(103,"Henry",7,45000,40,87654);
insert into [Link] values(104,"Sam",5,50000,30,76542);
insert into [Link] values(105,"David",1,55000,45,98765);
insert into [Link] values(106,"Henry",2,35000,35,25432);
insert into [Link] values(107,"Smith",4,45000,32,67343);
insert into [Link] values(108,"Hari",6,55000,27,34678);
select Eid as id, Ename as name from [Link];
select [Link], [Link], [Link], [Link]
from [Link] as emp, [Link] as depart
where [Link]=[Link];
-------------------------------------------------------------------------
--------
PROGRAM 8:(CONSTRAINTS)
-----------------
create table [Link](
Id integer(10) primary key unique,
Name varchar(20) NOT NULL,
Age integer(3) NOT NULL check (age>=18),
Salary decimal(18,2) default 5000.00);
insert into [Link] values(1,"Kumar",36,30000);
insert into [Link] values(2,"Ashok",17,40000);
insert into [Link] values(3,"Madhuri",20,20000);
insert into [Link] values(4,"Lahari",27,26000);
insert into [Link] values(5,"vinai",20,60000);
insert into [Link] values(6,"sreenivasa",27,30000);
select * from [Link];
select * from [Link], [Link] where [Link]=[Link];
FOREIGN KEY
---------------------
create table [Link](
sid integer(10),
id integer(10) unique,
sname varchar(10));
insert into [Link] values(123,1,"clothes");
insert into [Link] values(456,2,"textiles");
insert into [Link] values(678,3,"laptop");
insert into [Link] values(876,4,"vegetabes");
insert into [Link] values(346,5,"grocery");
insert into [Link] values(988,6,"mobiles");
select * from [Link];
-------------------------------------------------------------------------
----------
PROGRAM 9:(INDEX AND VIEWS)
-------------------
create table [Link](number integer(10));
insert into [Link] values(9876543);
insert into [Link] values(8739);
insert into [Link] values(987632);
insert into [Link] values(987623);
drop table [Link];
rollback;
truncate table [Link];
commit;
create table hr.sample1(name varchar(10),id integer(5));
insert into hr.sample1 values("asdsf",23);
insert into hr.sample1 values("asdfsf",22);
grant insert,select on hr.sample1 to "asdsf";
create index idx_employee
on [Link](ename);
create view hr.view_employee as
select Ename, Eid
from [Link]
where Dno= 2;
select * from hr.View_employee;
create or replace view hr.view_employee as
select Did,Dname,Dlocation
from [Link]
where Dno in (2,3);
drop view hr.view_employee;
---------------------------------------------------------------------
PROGRAM 10:(STORED PROCEDURE)
----------------------------------
create procedure test
AS
select * from [Link]
go;
-------------------------------------------------------------------------
-