GTBIT SQL QUERY

ITPROFESSIONALSROCKS.BLOGSPOT.COM
---2 QUESTION--

·         Create a database named ‘ Employee’.

·         Use the database ‘Employee’ and create a table ‘Emp’ with attributes ‘ename’,’ecity’,’salary’,’enumber’,’eaddress’,’depttname’.

·         Create another table ‘Company’ with attributes ‘cname’,’ ccity’,’numberof emp’,’empnumber’ in the database ‘Employee’.

create database employee
use employee
create table emp
(
ename varchar(20),ecity varchar(20),salary float,enumber int,eaddres varchar(40),deptname varchar(20));
Create table  Company
( cname varchar(20), ccity varchar(20),numberofemp int,empnumber int )
insert into emp values('hemant','delhi',500000,98993142,'karol bagh','techsolution');
insert into emp values('rajiv','delhi',300000,99999234,'moti nagar','finanace');
insert into emp values('raji','delhi',300000,99999234,'moti nagar','manager','finanace');
insert into Company values('TCS','GURGAON',5000,500);
INSERT INTO Company VALUES('MICROSOFT','GURGAON',10000,300);
INSERT INTO Company VALUES('MICROSOFT','noida',12000,300);

select *from emp
select *from company
-------3 quest----·         Find the names of all employees who live in Delhi.
·         Increase the salary of all employees by Rs. 5,000.
·         Find the company names where the number of employees is greater than 10,000.
·         Change the Company City to Gurgaon where the Company name is ‘TCS’.
SOLUTION:
select ename from emp where ecity='delhi'
update emp set salary=salary+5000
select cname from company where numberofemp>=10000
update company set ccity='noida' where cname='TCS'
select *from company
-------4 question--------------------------

• Add an attribute named ‘ Designation’ to the table ‘Emp’.
• Modify the table ‘Emp’, Change the datatype of ‘salary’ attribute to float.
• Drop the attribute ‘depttname’ from the table ‘emp’.
• Delete the entries from the table ‘ Company’ where the number of employees are less than 500.

ALTER TABLE emp
ADD designation varchar(20)
alter table emp
add deptname varchar(20)
alter table emp alter column salary float;
alter table emp drop column deptname;
delete from company where numberofemp<5000
-----------5 question------------------

• Find the names of all employees who live in ‘ Gurgaon’ and whose salary is between Rs. 20,000 and Rs. 30,000.
• Find the names of all employees whose names begin with either letter ‘A’ or ‘B’.
• Find the company names where the company city is ‘Delhi’ and the number of employees is not between 5000 and 10,000.
• Find the names of all companies that do not end with letter ‘A’.

select ename from emp
where ecity='delhi'and salary between 200000 and 500000
select ename from emp
where ename like 'a%'or ename like 'b%'
select cname from company
where ccity='delhi'and numberofemp not between 5000 and 10000
select cname from company
where cname not like '%a'
----------------6 question----------------------

• Find the sum and average of salaries of all employees in computer science department.
• Find the number of all employees in company ‘TCS’.

Find the maximum and the minimum salary in the HR department( WE HAVE TAKEN TECHSOLUTION)
select sum(salary)from emp where deptname ='techsolution'
select *from emp
select avg(salary)from emp where deptname ='techsolution'
select numberoemp from company where cname='TCS'
select max(salary)from emp where deptname='techsolution'
select min(salary)from emp where deptname='techsolution'
-----------------7 question---------------

--• List all employee names in descending order.
--• Find number of employees in each department where number of employees is greater than 5.
--• List all the department names where average salary of a department is Rs.10,000.
select ename from emp order by ename desc
select *from emp
select *from company
select count(*)from emp where(select count(deptname)from emp)>2
select deptname from emp where (select avg(salary)from emp)=405000
-----------------------8 question---(WE ARE NOT SURE ABOUT 8 QUESTION SO PLEASE VERIFY IT BEFORE COPYING)
-- Alter table ‘Emp’ and make ‘enumber’ as the primary key.
--Alter table ‘Company’ and add the foreign key constraint.
---• Add a check constraint in the table ‘Emp’ such that salary has the value between 0 and Rs.1,00,000.
ALTER TABLE emp ADD PRIMARY KEY (enumber)
ALTER TABLE company ADD FOREIGN KEY (enumber) REFERENCES emp(enumber)
ALTER TABLE emp add check(salary between 0 and 10000)
----------------9 question----
---• Rename the name of database to ‘Employee1’.
-------• Rename the name of  table ‘Emp’ to ‘Emp1’.
--------• Change the name of the attribute ‘ename’ to ‘empname’.

 sp_renamedb employee ,employee1
sp_rename emp,emp1
SELECT ename AS empname
FROM emp1
select *from emp1
select *from company
select *from emp1