Creating Database.docx

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



SELECT TOP:-

SELECT TOP 3 CUSTOMERNAME



FROM CUSTOMERS;

MIN OPERATOR :-

SELECT MIN (CUSTOMERID) FROM CUSTOMERS ;

DESC;



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 insert insert insert insert insert insert insert insert insert

into into into into into into into into into into

employee employee employee employee employee employee employee employee employee employee

insert insert insert insert insert insert insert

into into into into into into into

works works works works works works works

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

values('abhay','sbi',70000); values('varun','pnb',50000); values('ashok','syndicate',37000); values('vivek','sbi',65000); values('gaurav','union',60000); values('arun','pnb',55000); 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 insert insert insert insert

into into into into into

company company company company company

insert insert insert insert insert insert insert insert insert insert

into into into into into into into into into into

managers managers managers managers managers managers managers managers managers managers

values('sbi','delhi'); values('pnb','banglore'); values('syndicate','goa'); values('union','lucknow'); values('icici','mumbai'); values('abhay','vijay'); values('varun','sanjay'); values('ashok','prakash'); values('vivek','om'); values('gaurav','shankar'); values('arun','vikrant'); values('sunil','anurag'); values('haseeb','veeresh'); values('tanay','argha'); 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 queryselect 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 tablesselect * 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 insert insert insert insert insert

into into into into into into

employee employee employee employee employee employee

values values values values values values

(101,'Aryan','Designing',100000000); (102,'Apoorv','PPT',2000000); (103,'Paras','P.R.',90000); (104,'Suyash','Public Speaking',100000000); (123,'Negi','PPT',800000); (133,'XYZ','P.R.',20000);

insert insert insert insert insert insert insert insert insert insert insert insert

into into into into into into into into into into into into

position position position position position position position position position position position position

values values values values values values values values values values values values

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

insert insert insert insert insert

into into into into into

duty duty duty duty duty

values values values values values

(210,102,'April 24 1986','Day'); (208,104,'May 12 1986','Day'); (206,123,'April 30 1986','Day'); (203,133,'June 14 1990','Night'); (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 declare declare declare declare declare

@emp_no varchar(10); @name varchar(20); @skill varchar(20); @payrate int ; @audit_action varchar(20); @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 Insert Insert Insert

into into into into

Section Section Section Section

values values values values

(1,'Sec1') (2,'Sec2') (3,'Sec3') (4,'Sec4')

Insert Insert Insert Insert Insert Insert

into into into into into into

Student Student Student Student Student Student

values values values values values values

(1,'Satya1', (2,'Satya2', (3,'Satya3', (4,'Satya4', (5,'Satya5', (6,'Satya6',

'Male', 3) 'Male', 2) 'Female', 1) 'Male', 4) 'Female', 1) '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 Insert Insert Insert

into into into into

tblDepartment1 tblDepartment1 tblDepartment1 tblDepartment1

Insert Insert Insert Insert Insert Insert

into into into into into into

tblEmployee1 tblEmployee1 tblEmployee1 tblEmployee1 tblEmployee1 tblEmployee1

values values values values

values values values values values values

(1,'Blog') (2,'Article') (3,'Resource') (4,'Book')

(1,'Satya1', (2,'Satya2', (3,'Satya3', (4,'Satya4', (5,'Satya5', (6,'Satya6',

'Male', 3) 'Male', 2) 'Female', 1) 'Male', 4) 'Female', 1) '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 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)

--the instead of

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 insert insert insert insert insert

into into into into into into

salesman salesman salesman salesman salesman salesman

values(5001,'James Hoog','New York',0.15) values(5002,'Nsil Knite','Paris',0.13) values(5005,'Pit Alex','London',0.11) values(5006,'McLyon','Paris',0.14) values(5003,'Lauson Hen','London',0.12) 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 insert insert insert insert insert insert

into into into into into into into

customer customer customer customer customer customer customer

values(3001,'Nick Rimando','New York',100,5001) values(3004,'Fabian John','Paris',300,5006) values(3007,'Brad Davis','New York',200,5001) values(3009,'Geoff Camero','Berlin',100,5003) values(3008,'Julian Green','London',300,5002) values(3003,'Jozy Altidor','Moscow',200,5007) 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 insert insert insert insert insert insert insert insert



into into into into into into into into into

orders orders orders orders orders orders orders orders orders

values(70001,150.5,'2012-10-05',3005,5002) values(70009,270.65,'2012-09-10',3001,5005) values(70002,65.25,'2012-10-05',3002,5001) values(70004,110.4,'2012-08-17',3009,5003) values(70007,948.5,'2012-09-10',3005,5002) values(70003,2480.4,'2012-10-10',3009,5003) values(70012,250.45,'2012-06-27',3008,5002) values(70011,75.23,'2012-08-17',3003,5007) 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-0617','AD_PRES','24000',0,0,90)

insert into table1 values(101,'Neena','Kochhar','NKOCHHAR','425475445','200509-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','200705-21','IT_PROG',6000.00,0.00,103,60) insert into table1 values(105,'David','Austin','DAUSTIN','590.423.4569','2005-0625','IT_PROG',4800.00,0.00,103,60) insert into table1 values(106,'Valli','Pataballa','VPATABAL','590.423.4560','2006-0205','IT_PROG',4800.00,0.00,103,60) insert into table1 values(107,'Diana','Lorentz','DLORENTZ','590.423.5567','2007-0207','IT_PROG',4200.00,0.00,103,60) insert into table1 values(108,'Nancy','Greenberg','NGREENBE','515.124.4569','2002-0817','FI_MGR',12000.00,0.00,101,100) insert into table1 values(109,'Daniel','Faviet','DFAVIET','515.124.4169','2002-0816','FI_ACCOUNT', 9000.00,0.00,108,100) insert into table1 values(110,'John','Chen','JCHEN','515.124.4269','2005-0928','FI_ACCOUNT',8200.00,0.00,108,100) insert into employees values(163,'Danielle','Greene','DGREENE','011.44.1346.229268','2007-0319','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, FROM employees;



salary

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 databasecreate 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 insert insert insert insert insert insert insert insert insert

into into into into into into into into into into

employee employee employee employee employee employee employee employee employee employee

insert insert insert insert insert insert insert insert insert insert

into into into into into into into into into into

works works works works works works works works works works

insert insert insert insert insert

into into into into into

company company company company company

insert insert insert insert insert insert

into into into into into into

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

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

managers managers managers managers managers managers

values('sbi','delhi'); values('pnb','banglore'); values('syndicate','goa'); values('union','lucknow'); values('icici','mumbai'); values('abhay','vijay'); values('varun','sanjay'); values('ashok','prakash'); values('vivek','om'); values('gaurav','shankar'); values('arun','vikrant');

insert insert insert insert

into into into into

managers managers managers managers

values('sunil','anurag'); values('haseeb','veeresh'); values('tanay','argha'); 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 databaseCreate 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 insert insert insert insert insert insert insert insert insert insert

into into into into into into into into into into into

employee1 employee1 employee1 employee1 employee1 employee1 employee1 employee1 employee1 employee1 employee1

insert insert insert insert insert insert insert insert insert insert insert

into into into into into into into into into into into

position position position position position position position position position position position

insert insert insert insert insert insert insert insert insert insert insert

into into into into into into into into into into into

values(123456,'akhil','marketting',7000); values(123461,'anil','sales',10000); values(123582,'karan','hr',15000); values(123543,'suresh','web designing',8000); values(123890,'harun','hr',20000); values(123753,'negi','ppt',80000); values(133854,'xyz','p.r.',40000); values(111764,'selena','designing',30000); values(151545,'taylor','ppt',60000); values(145832,'jessica','p.r.',55000); values(134762,'gal','public speaking',200000); values(101,'marketting'); values(102,'sales'); values(103,'hr'); values(104,'web designing'); values(105,'hr'); values(106,'p.r.'); values(107,'public speaking'); values(108,'designing'); values(109,'ppt'); values(110,'p.r.'); values(111,'public speaking');

duty_allocation duty_allocation duty_allocation duty_allocation duty_allocation duty_allocation duty_allocation duty_allocation duty_allocation duty_allocation duty_allocation

values(101,123456,'1986-04-14','day'); values(102,123461,'1986-04-14','night'); values(103,123582,'1986-04-16','day'); values(104,123543,'1986-04-11','night'); values(105,123890,'1986-06-14','night'); values(106,123753,'1986-04-11','night'); values(107,133854,'1986-07-19','night'); values(108,111764,'1986-02-14','day'); values(109,151545,'1986-04-24','day'); values(110,145832,'1986-05-13','day'); 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;