NAME : ARYAN GUPTA

ROLL NO. : 717043

B.C.A. SEM-III
SUBJECT : SQL Server
Queries

2018-2019

SUBMITTED TO-
 MR. AMIT
SRIVASTAVA

BASIC QUERIES

 Creating Table

USE NPGCBCA3

CREATE TABLE CUSTOMERS(

CUSTOMERID INT PRIMARY KEY,
CUSTOMERNAME VARCHAR(20),
CONTACTNAME VARCHAR(20),
ADDRESS VARCHAR(20),
CITY VARCHAR(10),
POSTALCODE VARCHAR(20),
COUNTRY VARCHAR(20));

INSERT INTO CUSTOMERS VALUES(1,’MUKUND

KACKER’,’MUKU’,’NISHATGANJ’,’LUCKNOW’,’226007’,’INDIA’);
INSERT INTO CUSTOMERS VALUES(2,’APURVA
JAISWAL’,’APOORV’,’HAZRATGANJ’,’KANPUR’,’33456’,’JAKATA’);
INSERT INTO CUSTOMERS VALUES(3,’ARYAN
GUPTA’,’ARYAN’,’ALIGANJ’,’AGRA’,’6575’,’INDONESIA’);
INSERT INTO CUSTOMERS VALUES(4,’SUYASH
MIHIR’,’SUYASH’,’CHOWK’,’BARABANKI’,’9876’,’SPAIN’);
INSERT INTO CUSTOMERS VALUES(5,’PARASJAIN’,’PARAS’,’VIKAS
NAGAR’,’LUCKNOW’,’7543’,’RUSSIA’);

 AND OPERATOR :-

SELECT * FROM CUSTOMERS WHERE CITY=‘LUCKNOW’ AND COUNTRY=‘INDIA’;

 OR OPERATOR :-

SELECT * FROM CUSTOMERS WHERE CITY=‘KANPUR’ OR COUNTRY =‘JAKATA’;

  NOT OPERATOR:-

SELECT * FROM CUSTOMERS WHERE NOT COUNTRY=‘JAKATA’;

 ORDER BY :-

SELECT * FROM CUSTOMERS ORDER BY CUSTOMERID DESC;

 SELECT TOP:-

SELECT TOP 3 CUSTOMERNAME FROM CUSTOMERS;

 MIN OPERATOR :-

SELECT MIN (CUSTOMERID) FROM CUSTOMERS ;

  MAX OPERATOR:-

SELECT MAX(CUSTOMERID) FROM CUSTOMERS;

COUNT OPERATOR:-

SELECT COUNT(CITY) FROM CUSTOMERS WHERE CITY=‘LUCKNOW’;

 AVG OPERATOR :-

SELECT AVG(CUSTOMERID) FROM CUSTOMERS WHERE COUNTRY= ‘INDIA’;

 SUM OPERATOR :-

SELECT SUM(CUSTOMERID) FROM CUSTOMERS WHERE CITY=‘LUCKNOW’;

 LIKE OPERATOR:-

SELECT * FROM CUSTOMERS WHERE CUSTOMERNAME LIKE ‘M%’;

SELECT * FROM CUSTOMERS WHERE CUSTOMERNAME LIKE ‘%R’;

 BETWEEN OPERATOR :-

SELECT * FROM CUSTOMER WHERE CUSTOMERID BETWEEN 2 AND 4;

 NOT BETWEEN :-

SELECT * FROM CUSTOMERS WHERE CUSTOMERID NOT BETWEEN 2 AND 4;

 IN OPERATOR : -

SELECT * FROM CUSTOMERS WHERE COUNTRY IN (‘JAKATA’,’RUSSIA’,’INDONESIA’);

  NOT OPERATOR:-

SELECT * FROM CUSTOMERS WHERE COUNTRY NOT IN(‘JAKATA’,’RUSSIA’,;INDONESIA’);

--Creating Database

create database bca3

use bca3

create table employee

(emp_name varchar(20),
street varchar(20),
city varchar(20));

create table works

(emp_name varchar(20),
company_name varchar(20),
salary int);

create table company

(company_name varchar(20),
city varchar(20));

create table managers

(emp_name varchar(20),
manager_name varchar(20));

insert into employee values('abhay','azad nagar street','kanpur');

insert into employee values('varun','vineet nagar street','meerut');
insert into employee values('ashok','ekta nagar strret','amethi');
insert into employee values('vivek','khurram nagar street','lucknow');
insert into employee values('gaurav','taltalla street','kolkata');
insert into employee values('hari','dadar street','mumbai');
insert into employee values('sunil','chowk street','delhi');
insert into employee values('haseeb','shuvi street','banglore');
insert into employee values('tanay','suri street','chennai');
insert into employee values('saurabh','sebastian street','goa');

insert into works values('abhay','sbi',70000);

insert into works values('varun','pnb',50000);
insert into works values('ashok','syndicate',37000);
insert into works values('vivek','sbi',65000);
insert into works values('gaurav','union',60000);
insert into works values('arun','pnb',55000);
insert into works values('sunil','sbi',45000);
insert into works values('haseeb','union',20000);
insert into works values('tanay','sbi ',57000);
insert into works values('saurabh','icici',40000);

insert into company values('sbi','delhi');

insert into company values('pnb','banglore');
insert into company values('syndicate','goa');
insert into company values('union','lucknow');
insert into company values('icici','mumbai');

insert into managers values('abhay','vijay');

insert into managers values('varun','sanjay');
insert into managers values('ashok','prakash');
insert into managers values('vivek','om');
insert into managers values('gaurav','shankar');
insert into managers values('arun','vikrant');
insert into managers values('sunil','anurag');
insert into managers values('haseeb','veeresh');
insert into managers values('tanay','argha');
insert into managers values('saurabh','prateek');

--Queries

 Order by query

select * from works order by salary;

 Group by Query-

select count(emp_name), company_name

from works
group by company_name;
  between query-

select emp_name from works

where salary between 40000 and 80000;

 into query-

select * into hello

from employee
where city='kanpur';

select * from hello;

 having query-

select count(emp_name), company_name

from works
group by company_name
having count(emp_name)>1;
  union query-

select company_name from company

union
select city from employee

 union all query-

select company_name from company

union all
select city from employee ;
  like query for ‘%’-
select emp_name
from works
where emp_name like 'a%';

 like query for ‘_’-

select emp_name
from employee
where emp_name like '_a%';
  query for in-

select city
from company
where city in (select city from employee);

 alias query-

select emp_name
from employee as e;

 exists query-

select company_name
from company
where exists
(select emp_name from works where salary=55000);
  query for cartesian product-

select * from employee,company

VIEWS

 query for creating a view-

create view report as

select emp_name, company_name
from works
where salary>50000;

 query for viewing a view-

select * from report

 query for updating a view-

update report
set company_name='tcs'
from works
where salary >50000;

select * from report

 query to display user tables-

select * from sys.objects where type='u'

--Creating Database

create database Question_1

use Question_1

create table employee(

emp_no numeric(3),
name varchar(20),
skill varchar(20),
pay_rate numeric(10)
);

create table position (

posting_no numeric(4),
skill varchar(20));
create table duty(
posting_no numeric(4),
emp_no numeric(3),
day_ varchar(20),
shift varchar(10)
);

insert into employee values (101,'Aryan','Designing',100000000);

insert into employee values (102,'Apoorv','PPT',2000000);
insert into employee values (103,'Paras','P.R.',90000);
insert into employee values (104,'Suyash','Public Speaking',100000000);
insert into employee values (123,'Negi','PPT',800000);
insert into employee values (133,'XYZ','P.R.',20000);

insert into position values (201,'Designing');

insert into position values (202,'PPT');
insert into position values (203,'P.R.');
insert into position values (204,'Public Speaking');
insert into position values (205,'Designing');
insert into position values (206,'PPT');
insert into position values (207,'P.R.');
insert into position values (208,'Public Speaking');
insert into position values (209,'Designing');
insert into position values (210,'PPT');
insert into position values (211,'P.R.');
insert into position values (212,'Public Speaking');

insert into duty values (210,102,'April 24 1986','Day');

insert into duty values (208,104,'May 12 1986','Day');
insert into duty values (206,123,'April 30 1986','Day');
insert into duty values (203,133,'June 14 1990','Night');
insert into duty values (201,101,'April 29 1992','Night');

--Queries

 calculation in select

select Ta=pay_rate*0.1 from employee ;

  creating table with select into

Select * into TopLvl from employee

where pay_rate>100000;

select * from TopLvl

 group by using expression

select Ta=pay_rate*0.1 from employee

group by (pay_rate*0.1);

--TA of two employees grouped Together

 having without aggregate functions

select emp_no, name

from employee
Group by emp_no,name
having emp_no like '%3%';
 String Functions

 Difference

select DIFFERENCE('Juicy','Jucy');

 LTrim

SELECT LTRIM(' SQL Tutorial');

 Nchar - unicode to Char

SELECT NCHAR(2650) AS NumberCodeToUnicode;

 PathIndex Pattern in a string

SELECT PATINDEX('%schools%', 'W3Schools.com');

 QuoteName

SELECT QUOTENAME('abcdef','"');
  replace function

SELECT REPLACE('Hello Aryan','Aryan','Apurva');

 Replicate

Select REPLICATE(name,2)
from employee;

 Reverse

Select REVERSE(name)
from employee;

 Soundex Value

SELECT SOUNDEX('Juice'), SOUNDEX('Banana');

  STR

SELECT STR(185.476, 6, 2);

 STUFF

SELECT STUFF('SQL Tutorial!', 13, 1, ' is fun!');

FUNCTIONS CREATION
 Function along with If Else

create function TACalculator(@salary int)

returns int
as
begin
declare @ta int;
if @salary>100000
set @ta=@salary/10;
else
set @ta=0;

return @ta;
end

--function calling

select dbo.TACalculator(pay_rate) from employee;

  Function along with WHILE LOOP

create function Prime(@number int)

returns int
as
begin
declare @ct int;
set @ct=0;
declare @i int;
set @i=2;
while(@i<@number)
begin
declare @temp int;
set @temp=@number / @i;
if (@temp*@i)=@number
set @ct=@ct+1;
set @i=@i+1 ;
end
if @ct=0
return 1;
return 0 ;
end

 if else

if((select dbo.Prime(13))=1)
print 'Prime';
else
print 'Not Prime';
 STORED PRODECURES

use Question_1;

 Insert

CREATE PROCEDURE [dbo].[employee_insert]

@ENO numeric(3),@ENAME varchar(20), @ESKILL varchar(20),
@payrate numeric(10)
AS
BEGIN
INSERT INTO employee (emp_no,name,skill,pay_rate)
VALUES (@ENO,@ENAME,@ESKILL,@payrate)
END

 Executing

exec employee_insert 456,'OM','AI',300000 ;

select * from Employee

 SELECT

create procedure [dbo].[View_employee]

@emp_no varchar(10)
As
 Begin
select * from Employee
where emp_no=@emp_no ;
end;

 Executing

exec dbo.View_employee '123456';

 UPDATE

create procedure [dbo].[Update_Rec]

@emp_no varchar(10),
@payrate int
as
begin
update Employee
set pay_rate=@payrate
where emp_no=@emp_no ;
end;

 Executing

exec dbo.Update_Rec '123456',2000 ;

select * from Employee;

 DELETE
create procedure Delete_rec
@emp_no varchar(10)
as
begin
DELETE FROM Employee
where emp_no=@emp_no;
end;

exec dbo.Delete_rec 456;

select * from Employee
  View Procedures

select * from sys.objects where type='p'

TRIGGERS

create table employee_audit(

emp_no numeric(3),
name varchar(20),
skill varchar(20),
pay_rate numeric(10),
audit_action varchar(20),
audit_time varchar(20));

 INSERT TRIGGERS

create trigger insert_employee_trig on Employee

FOR INSERT
AS
declare @emp_no varchar(10);
declare @name varchar(20);
declare @skill varchar(20);
declare @payrate int ;
declare @audit_action varchar(20);
declare @audit_time datetime ;

select @emp_no= i.emp_no from inserted i ;

select @name= i.name from inserted i ;
select @skill= i.skill from inserted i ;
select @payrate= i.payrate from inserted i ;
set @audit_action ='INSERTION IN EMPLOYEE'
 set @audit_time= getdate();

insert into employee_audit values

(@emp_no,@name,@skill,@payrate,@audit_action,@audit_time);
GO

INSERT INTO eMPLOYEE VALUES(456,'Hiten','Designing',2000);

select * from Employee_Audit;

 DELETE TRIGGER

create trigger delete_employee_trig on Employee

FOR DELETE
AS
declare @emp_no varchar(10);
declare @name varchar(20);
declare @skill varchar(20);
declare @payrate int ;
declare @audit_action varchar(20);
declare @audit_time datetime ;

select @emp_no= i.emp_no from deleted i ;

select @name= i.name from deleted i ;
select @skill= i.skill from deleted i ;
select @payrate= i.pay_rate from deleted i ;
set @audit_action ='DELETION IN EMPLOYEE'
set @audit_time= getdate();
insert into employee_audit values
(@emp_no,@name,@skill,@payrate,@audit_action,@audit_time);
GO

Delete from eMPLOYEE

where emp_no ='456';
select * from Employee_Audit;

 UPDATE

create trigger update_employee_trig on Employee

FOR UPDATE
AS
declare @emp_no varchar(10);
declare @name varchar(20);
declare @skill varchar(20);
declare @payrate int ;
declare @audit_action varchar(20);
declare @audit_time datetime ;

select @emp_no= i.emp_no from inserted i ;

select @name= i.name from inserted i ;
select @skill= i.skill from inserted i ;
select @payrate= i.pay_rate from inserted i ;
set @audit_action ='UPDATE IN EMPLOYEE'
set @audit_time= getdate();

insert into employee_audit values

(@emp_no,@name,@skill,@payrate,@audit_action,@audit_time);
GO

UPDATE employee
set name='Raghav'
where emp_no=123;

select * from Employee_audit ;

 View Triggers

select * from sys.objects where type='tr'

 Instead of Triggers
CREATE TABLE Student
(Id int Primary Key,
Name nvarchar(30),
Gender nvarchar(10),
SectionId int)

CREATE TABLE Section

(SecId int Primary Key,
SecName nvarchar(20))

Insert into Section values (1,'Sec1')

Insert into Section values (2,'Sec2')
Insert into Section values (3,'Sec3')
Insert into Section values (4,'Sec4')

Insert into Student values (1,'Satya1', 'Male', 3)

Insert into Student values (2,'Satya2', 'Male', 2)
Insert into Student values (3,'Satya3', 'Female', 1)
Insert into Student values (4,'Satya4', 'Male', 4)
Insert into Student values (5,'Satya5', 'Female', 1)
Insert into Student values (6,'Satya6', 'Male', 3)

Create view ViewStudentDetails

as Select Id, Name, Gender, SecName from Student
join Section on Student.SectionId = Section.SecId

 Instead of Insert Trigger

Create trigger TR_ViewStudentDetails_InsteadOfInsert

on ViewStudentDetails
Instead Of Insert
as
Begin
Declare @SecId int

Select @SecId = SecId

from Section
join inserted
on inserted.SecName = Section.SecName

if(@SecId is null)
Begin
Raiserror('Invalid Section Name. Statement Terminated OK', 16, 1)
return
End

Insert into Student(Id, Name, Gender, SectionId)

Select Id, Name, Gender, @SecId
from inserted
End

Insert into ViewStudentDetails values(7, 'Satya7', 'Female', 'sec4')

Insert into ViewStudentDetails values(7, 'Satya7', 'Female', 'sec5')

 Instead of Update

CREATE TABLE tblEmployee1

(
Id int Primary Key,
Name nvarchar(30),
Gender nvarchar(10),
DepartmentId int
)

CREATE TABLE tblDepartment1

(
DeptId int Primary Key,
DeptName nvarchar(20)
)

Insert into tblDepartment1 values (1,'Blog')

Insert into tblDepartment1 values (2,'Article')
Insert into tblDepartment1 values (3,'Resource')
Insert into tblDepartment1 values (4,'Book')

Insert into tblEmployee1 values (1,'Satya1', 'Male', 3)

Insert into tblEmployee1 values (2,'Satya2', 'Male', 2)
Insert into tblEmployee1 values (3,'Satya3', 'Female', 1)
Insert into tblEmployee1 values (4,'Satya4', 'Male', 4)
Insert into tblEmployee1 values (5,'Satya5', 'Female', 1)
Insert into tblEmployee1 values (6,'Satya6', 'Male', 3)

select * from tblDepartment1

select * from tblEmployee1

Create view ViewEmployeeDetails1

as
Select Id, Name, Gender, DeptName
from tblEmployee1
join tblDepartment1
on tblEmployee1.DepartmentId = tblDepartment1.DeptId

Update ViewEmployeeDetails1
set Name = 'Satya1', DeptName = 'Blog'
where Id = 1
Update ViewEmployeeDetails1
set DeptName = 'Blog'
where Id = 1

Update tblDepartment1 set DeptName = 'Resource' where DeptId = 3

select * from tblDepartment1

Create Trigger tr_ViewEmployeeDetails1_InsteadOfUpdate --the instead of

trigger
on ViewEmployeeDetails1
instead of update
as
Begin
if(Update(Id))
Begin
Raiserror('Id cannot be changed', 16, 1)
Return
End

if(Update(DeptName))
Begin
Declare @DeptId int

Select @DeptId = DeptId

from tblDepartment1
join inserted
on inserted.DeptName = tblDepartment1.DeptName

if(@DeptId is NULL )
Begin
Raiserror('Invalid Department Name', 16, 1)
 Return
End

Update tblEmployee1 set DepartmentId = @DeptId

from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End

if(Update(Gender))
Begin
Update tblEmployee1 set Gender = inserted.Gender
from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End

if(Update(Name))
Begin
Update tblEmployee1 set Name = inserted.Name
from inserted
join tblEmployee1
on tblEmployee1.Id = inserted.id
End
End

Update ViewEmployeeDetails1
set Name = 'Satya11', Gender = 'Female', DeptName = 'Blog'
where Id = 1

Update ViewEmployeeDetails1
set Name = 'Satya11', Gender = 'Female', DeptName = 'BlogN'
where Id = 1

Update ViewEmployeeDetails1
set Name = 'Satya11', Gender = 'Female', DeptName = 'BlogN' , id=11
where Id = 1
  Instead of Delete

Create Trigger tr_ViewEmployeeDetails1_InsteadOfDelete

on ViewEmployeeDetails1
instead of delete
as
Begin
Declare @Id int
Select @Id = tblEmployee1.Id
from tblEmployee1
join deleted
on deleted.Id = tblEmployee1.Id
if(@Id is NULL )
Begin
Raiserror('Invalid Employee ID or Employee ID not Exists', 16, 1)
Return
End
else
Delete tblEmployee1
from tblEmployee1
join deleted
on tblEmployee1.Id = deleted.Id
End

Delete from ViewEmployeeDetails1 where Id = 1

Delete from ViewEmployeeDetails1 where Id = 12

 JOINS

create table salesman (s_id int, s_name varchar(30), city varchar(30),

commission float);

insert into salesman values(5001,'James Hoog','New York',0.15)

insert into salesman values(5002,'Nsil Knite','Paris',0.13)
insert into salesman values(5005,'Pit Alex','London',0.11)
insert into salesman values(5006,'McLyon','Paris',0.14)
insert into salesman values(5003,'Lauson Hen','London',0.12)
insert into salesman values(5007,'Paul Adam','Rome',0.13)

create table customer (c_id int,c_name varchar(30),city varchar(30),grade

int, s_id int)

insert into customer values(3001,'Nick Rimando','New York',100,5001)

insert into customer values(3004,'Fabian John','Paris',300,5006)
insert into customer values(3007,'Brad Davis','New York',200,5001)
insert into customer values(3009,'Geoff Camero','Berlin',100,5003)
insert into customer values(3008,'Julian Green','London',300,5002)
insert into customer values(3003,'Jozy Altidor','Moscow',200,5007)
insert into customer values(3005,'Graham Zusi','California',200,5002)

create table orders (ord_no int, purch_amt float, ord_date varchar(11), c_id
varchar(30), s_id varchar(30))

insert into orders values(70001,150.5,'2012-10-05',3005,5002)

insert into orders values(70009,270.65,'2012-09-10',3001,5005)
insert into orders values(70002,65.25,'2012-10-05',3002,5001)
insert into orders values(70004,110.4,'2012-08-17',3009,5003)
insert into orders values(70007,948.5,'2012-09-10',3005,5002)
insert into orders values(70003,2480.4,'2012-10-10',3009,5003)
insert into orders values(70012,250.45,'2012-06-27',3008,5002)
insert into orders values(70011,75.23,'2012-08-17',3003,5007)
insert into orders values(70013,3045.6,'2012-04-25',3002,5001)

 Normal Join

--Prepare a list with salesman name, customer name, and their cities for the
salesman and customer who belong to the same city

select s.s_name "Salesman", c.c_name "Customer",c.city from salesman s,

customer c where s.city=c.city
--prepare a list with order no, purchase amount customer name and their
cities for those orders which order amount between 500 and 2000

select o.ord_no "Order No", o.purch_amt "Purchase Amount", c.c_name

"Customer", c.city "City" from orders o, customer c where o.c_id=c.c_id and
o.purch_amt between 500 and 2000

 Inner Join

--prepapre a list for whicj salesman are working for which customer along
with city and commissions earned by the salesman

select c.c_name "Customer", c.city, s.s_name "Salesman", s.commission

from customer c inner join salesman s on c.s_id=s.s_id
-- Make a list of customers who appointed a salesman for their jobs wh0 gets
a commission above 12%

select c.c_name "customer", c.city, s.s_name "Salesman", s.commission

from customer c inner join salesman s on c.s_id=s.s_id
where s.commission >0.12

-- Display the details of an order i.e. order number, order date, amount of
order, customer and salesman name and commission of the salesman for an order

select a.ord_no "order no.", a.ord_date "order date", a.purch_amt "purchase

amount", b.c_name "customer", b.grade, c.s_name "salesman", c.commission
from orders a inner join customer b on a.c_id=b.c_id
inner join salesman c on a.s_id=c.s_id
  Left Outer Join

-- Make a report with customer name, city, order no, date, amount, salesman
name and commission to find either any of the existing custoers have placed
no order or placed one or more orders by their salesmanor by own

select a.c_name, a.city, b.ord_no, b.ord_date, b.purch_amt "order amount",

c.s_name, c.commission
from customer a left outer join orders b on a.c_id=b.c_id
left outer join salesman c on c.s_id=b.s_id

 Right Outer Join

-- Display the list of salesman who works eother for one or more customers or
not yet join under any of the customer wo placed either one or more orders or
no orderto theiir suppier

select a.c_name, a.city, a.grade, b.s_id "salesman", c.ord_no, c.ord_date,

c.purch_amt
from customer a right outer join salesman b on a.s_id = b.s_id
right outer join orders c on c.c_id = a.c_id

 Cross Join

-- Make a cartesian product between salesaman and customer i.e. each slaeman
will appear for all customer and vice versa for that customer who belongs to
a city

select * from salesman a cross join customer where a.city is not null

 Full Outer Join

-- make a report with customer name, city, order no, purchasae amount for
only those customers on the list who must gave a grade and placed one or more
orders or which order(s) have been placed ny the customer who is niether in
the list nor have a grade

select a.c_name, a.city, b.ord_no, b.ord_date, b.purch_amt "order amount"

from customer a full outer join orders b on a.c_id = b.c_id
where a.grade is not null

-- make a list for the salesman who either wotk for one or more customers
oryet to join any of the customer. The customer may have placed, either one
or more orders on or above amount 2000 and must have a grade, or he may not
have placed any order to the associated supplier

select a.c_name, a.city, a.grade, b.s_name "salesman", c.ord_no, c.ord_date,

c.purch_amt
from customer a right outer join salesman b on b.s_id=a.s_id
left outer join orders c on c.c_id=a.c_id
where c.purch_amt >=2000 and a.grade is not null

 WHile Loop

DECLARE @site_value INT;

SET @site_value = 0;

WHILE @site_value <= 10

BEGIN
 PRINT 'Inside WHILE LOOP';
SET @site_value = @site_value + 1;
END;

PRINT 'Done WHILE LOOP';

GO

 While Loop as For Loop

DECLARE @cnt INT;

set @cnt=0;

WHILE @cnt < 10

BEGIN
PRINT 'Inside simulated FOR LOOP';
SET @cnt = @cnt + 1;
END;

PRINT 'Done simulated FOR LOOP';

GO

 do while loop

DECLARE @X INT;
set @x=1;
WAY:
PRINT @X;
SET @X= @x + 1;
IF @X<=10 GOTO WAY;

 repeat until loop

DECLARE @X INT;
set @x = 11
WAY:
PRINT @X;
SET @X = @x +1;
IF NOT(@X >20) GOTO WAY;

QUESTIONS
 Creating database –
create database assignment
use assignment

create table table1(employee_id int primary key ,first_name

varchar(50),last_name varchar(50),email varchar(50),phone_number
varchar(50),hire_date varchar(50),job_id varchar(50),salary
float,commission_pct float, manager_id int,department_id int)

insert into table1 values(100,'Steven','King','SKING','151234567','2003-06-

17','AD_PRES','24000',0,0,90)
insert into table1 values(101,'Neena','Kochhar','NKOCHHAR','425475445','2005-
09-21','AD_VP','17000.00',0.00, 100, 90)
insert into table1 values(102 ,'Lex','De
Haan','LDEHAAN','515.123.4569','2001-01-13','AD_VP','17000.00',0.00,100,90)
insert into table1 values(103,'Alexander','Hunold','AHUNOLD
','590.423.4567','2006-01-03','IT_PROG',9000.00,0.00,102,60)
insert into table1 values(104,'Bruce','Ernst','BERNST','590.423.4568','2007-
05-21','IT_PROG',6000.00,0.00,103,60)
insert into table1
values(105,'David','Austin','DAUSTIN','590.423.4569','2005-06-
25','IT_PROG',4800.00,0.00,103,60)
insert into table1
values(106,'Valli','Pataballa','VPATABAL','590.423.4560','2006-02-
05','IT_PROG',4800.00,0.00,103,60)
insert into table1
values(107,'Diana','Lorentz','DLORENTZ','590.423.5567','2007-02-
07','IT_PROG',4200.00,0.00,103,60)
insert into table1
values(108,'Nancy','Greenberg','NGREENBE','515.124.4569','2002-08-
17','FI_MGR',12000.00,0.00,101,100)
insert into table1
values(109,'Daniel','Faviet','DFAVIET','515.124.4169','2002-08-
16','FI_ACCOUNT', 9000.00,0.00,108,100)
insert into table1 values(110,'John','Chen','JCHEN','515.124.4269','2005-09-
28','FI_ACCOUNT',8200.00,0.00,108,100)
insert into employees
values(163,'Danielle','Greene','DGREENE','011.44.1346.229268','2007-03-
19','SA_REP',9500.00,0.15,147,80)

 Write a query to display the name (first name and last name) for those
employees who gets more salary than the employee whose ID is 163.

SELECT first_name, last_name

FROM employees
WHERE salary >
( SELECT salary
FROM employees
WHERE employee_id=163
);

 Write a query to display the name ( first name and last name ),
salary, department id, job id for those employees who works in
the same designation as the employee works whose id is 169.

SELECT first_name, last_name, salary, department_id, job_id

FROM employees
WHERE job_id =
( SELECT job_id
FROM employees
WHERE employee_id=169
);

 Write a query to display the name ( first name and last name ),
salary, department id for those employees who earn such amount of
salary which is the smallest salary of any of the departments.

SELECT first_name, last_name, salary, department_id

FROM employees
WHERE salary IN
( SELECT MIN(salary)
FROM employees
GROUP BY department_id
);

 Write a query to display the employee id, employee name (first

name and last name ) for all employees who earn more than the
average salary.

SELECT employee_id, first_name,last_name

FROM employees
WHERE salary >
( SELECT AVG(salary)
FROM employees
);
  Write a query to display the employee name ( first name and last
name ), employee id and salary of all employees who report to
Payam.

SELECT first_name, last_name, employee_id, salary

FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE first_name = 'Payam'
);

 Write a query to display the department number, name ( first name

and last name ), job and department name for all employees in the
Finance department.

SELECT first_name, last_name, employee_id, salary

FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE first_name = 'Payam'
);

 Display all the information of an employee whose id is any of the

number 107, 159 and 100

SELECT *
FROM employees
WHERE employee_id IN (107,105,100);
  Write a query to display all the information of the employees whose
salary is within the range 1000 and 3000.

SELECT * FROM employees

WHERE salary BETWEEN 1000 and 3000;

 Write a query to display all the information of the employees whose

salary is within the range of smallest salary and 2500.

SELECT *
FROM employees
WHERE salary BETWEEN
(SELECT MIN(salary)
FROM employees) AND 2500;

 Write a query to display all the information for those employees whose
id is any id who earn the second highest salary.

SELECT *
FROM employees
WHERE employee_id IN
(SELECT employee_id
FROM employees
WHERE salary =
(SELECT MAX(salary)
FROM employees
WHERE salary <
(SELECT MAX(salary)
FROM employees)));
  Write a query to display the employee number and name( first name and
last name ) for all employees who work in a department with any
employee whose name contains a T.

SELECT employee_id, first_name, last_name

FROM employees
WHERE department_id IN
( SELECT department_id
FROM employees
WHERE first_name LIKE '%T%' );

 Write a query to display the employee number, name( first name and last
name ), and salary for all employees who earn more than the average
salary and who work in a department with any employee with a J in their
name.

SELECT employee_id, first_name , salary

FROM employees
WHERE salary >
(SELECT AVG (salary)
FROM employees )
AND department_id IN
( SELECT department_id
FROM employees
WHERE first_name LIKE '%J%');

 Write a query to display the employee number, name( first name and last
name ) and job title for all employees whose salary is smaller than any
salary of those employees whose job title is MK_MAN. Exclude Job title
MK_MAN

SELECT employee_id,first_name,last_name, job_id

FROM employees
WHERE salary < ANY
( SELECT salary
FROM employees
WHERE job_id = 'MK_MAN' )
AND job_id <> 'MK_MAN' ;
  Write a query to display the employee number, name( first name and last
name ) and job title for all employees whose salary is more than any
salary of those employees whose job title is PU_MAN. Exclude job title
PU_MAN.

SELECT employee_id, first_name, last_name, job_id

FROM employees
WHERE salary > ALL
( SELECT salary s
FROM employees
WHERE job_id = 'PU_MAN' )
AND job_id <> 'PU_MAN' ;

 Write a query to display the employee number, name( first name and last
name ) and job title for all employees whose salary is more than any
average salary of any department.

SELECT employee_id, first_name, last_name, job_id

FROM employees
WHERE salary > ALL
( SELECT AVG(salary)
FROM employees
GROUP BY department_id
);
  Write a query to display the employee name( first name and last name )
and department for all employees for any existence of those employees
whose salary is more than 3700.

SELECT first_name, last_name, department_id

FROM employees
WHERE EXISTS
(SELECT *
FROM employees
WHERE salary >3700 );

 Write a query to display the department id and the total salary for
those departments which contains at least one employee.

SELECT departments.department_id, result1.total_amt

FROM departments,
( SELECT employees.department_id, SUM(employees.salary) total_amt
FROM employees
GROUP BY department_id) result1
WHERE result1.department_id = departments.department_id;

 Write a query to display the employee id, name ( first name and last
name ) and the job id column with a modified title SALESMAN for those
employees whose job title is ST_MAN and DEVELOPER for whose job title
is IT_PROG.

SELECT employee_id, first_name, last_name,

CASE job_id
WHEN 'ST_MAN' THEN 'SALESMAN'
WHEN 'IT_PROG' THEN 'DEVELOPER'
ELSE job_id
END AS designation, salary
FROM employees;

 Write a query to display the employee id, name ( first name and last
name ), salary and the SalaryStatus column with a title HIGH and LOW
respectively for those employees whose salary is more than and less
than the average salary of all employees.

SELECT employee_id, first_name, last_name, salary,

CASE WHEN salary >=
(SELECT AVG(salary)
FROM employees) THEN 'HIGH'
ELSE 'LOW'
END AS SalaryStatus
FROM employees;
 Creating database-
create database bca
use bca

create table employee

(emp_name varchar(20),
street varchar(20),
city varchar(20));

create table works

(emp_name varchar(20),
company_name varchar(20),
salary int);

create table company

(company_name varchar(20),
city varchar(20));

create table managers

(emp_name varchar(20),
manager_name varchar(20));

insert into employee values('abhay','azad nagar street','kanpur');

insert into employee values('varun','vineet nagar street','meerut');
insert into employee values('ashok','ekta nagar strret','amethi');
insert into employee values('vivek','khurram nagar street','lucknow');
insert into employee values('gaurav','taltalla street','kolkata');
insert into employee values('hari','dadar street','mumbai');
insert into employee values('sunil','chowk street','delhi');
insert into employee values('haseeb','shuvi street','banglore');
insert into employee values('tanay','suri street','chennai');
insert into employee values('saurabh','sebastian street','goa');

insert into works values('abhay','sbi',70000);

insert into works values('varun','pnb',50000);
insert into works values('ashok','syndicate',37000);
insert into works values('vivek','sbi',65000);
insert into works values('gaurav','sbi',60000);
insert into works values('arun','pnb',55000);
insert into works values('sunil','sbi',45000);
insert into works values('haseeb','union',20000);
insert into works values('tanay','sbi ',57000);
insert into works values('saurabh','icici',40000);

insert into company values('sbi','delhi');

insert into company values('pnb','banglore');
insert into company values('syndicate','goa');
insert into company values('union','lucknow');
insert into company values('icici','mumbai');

insert into managers values('abhay','vijay');

insert into managers values('varun','sanjay');
insert into managers values('ashok','prakash');
insert into managers values('vivek','om');
insert into managers values('gaurav','shankar');
insert into managers values('arun','vikrant');
insert into managers values('sunil','anurag');
insert into managers values('haseeb','veeresh');
insert into managers values('tanay','argha');
insert into managers values('saurabh','prateek');

select * from employee

drop table employee

select * from works

drop table works

select * from company

drop table company

select * from managers

drop table managers

queries-

 (find name of all employees who work for sbi.)

select emp_name from works as w where w.company_name='sbi';

 (find name & city of residence of all employees who work for sbi.)

select w.emp_name, e.city from works as w, employee as e where w.company_name='sbi' and

e.emp_name=w.emp_name;
 (find all employees in database who do not work for sbi.)

select emp_name from works as w

where w.company_name<>'sbi';

 (modify the database so that hari now lives in pune.)

update employee
set city = 'pune',street='meenar street'
where emp_name='hari';

 (delete all tuples in the work relation for employees in sbi.)

delete from works

where company_name='sbi';
Creating database-
Create database bca1
Use bca1

create table employee1

(emp_no varchar(10) primary key,
emp_name varchar(20),
skill varchar(20),
payrate int);

create table position

(posting_no varchar(20) primary key,
skill varchar(20));

create table duty_allocation

(posting_no varchar(20),
emp_no varchar(10),
day@ varchar(15),
shift varchar(15));

insert into employee1 values(123456,'akhil','marketting',7000);

insert into employee1 values(123461,'anil','sales',10000);
insert into employee1 values(123582,'karan','hr',15000);
insert into employee1 values(123543,'suresh','web designing',8000);
insert into employee1 values(123890,'harun','hr',20000);
insert into employee1 values(123753,'negi','ppt',80000);
insert into employee1 values(133854,'xyz','p.r.',40000);
insert into employee1 values(111764,'selena','designing',30000);
insert into employee1 values(151545,'taylor','ppt',60000);
insert into employee1 values(145832,'jessica','p.r.',55000);
insert into employee1 values(134762,'gal','public speaking',200000);

insert into position values(101,'marketting');

insert into position values(102,'sales');
insert into position values(103,'hr');
insert into position values(104,'web designing');
insert into position values(105,'hr');
insert into position values(106,'p.r.');
insert into position values(107,'public speaking');
insert into position values(108,'designing');
insert into position values(109,'ppt');
insert into position values(110,'p.r.');
insert into position values(111,'public speaking');

insert into duty_allocation values(101,123456,'1986-04-14','day');

insert into duty_allocation values(102,123461,'1986-04-14','night');
insert into duty_allocation values(103,123582,'1986-04-16','day');
insert into duty_allocation values(104,123543,'1986-04-11','night');
insert into duty_allocation values(105,123890,'1986-06-14','night');
insert into duty_allocation values(106,123753,'1986-04-11','night');
insert into duty_allocation values(107,133854,'1986-07-19','night');
insert into duty_allocation values(108,111764,'1986-02-14','day');
insert into duty_allocation values(109,151545,'1986-04-24','day');
insert into duty_allocation values(110,145832,'1986-05-13','day');
insert into duty_allocation values(111,134762,'1986-09-30','night');
select * from employee1
drop table employee1

select * from position

drop table position

select * from duty_allocation

drop table duty_allocation

queries-

 (get duty allocation details for employee=123461 for april 1986.)

select * from duty_allocation

where emp_no=123461 and day@ like '1986-04-__';

 (find the shift details for employee xyz.)

select e.emp_name,d.emp_no,d.shift from employee1 e, duty_allocation d

where d.emp_no=(select emp_no from employee1 where emp_name='xyz') and e.emp_name='xyz';

 (get employee whose payrate>=payrate of xyz.)

select emp_name from employee1

where payrate>(select payrate from employee1 where emp_name='xyz');
 (compute all pairs of posting no. requiring the same detail.)

select p1.posting_no,p2.posting_no from position p1,position p2

where p1.skill=p2.skill and p2.posting_no <>p1.posting_no ;

 (find the employees eligible to fill a position.)

select e.emp_no,e.emp_name,p.skill,p.posting_no from employee1 e,position p

where e.skill=p.skill;