[
NOTE:-
Database Name:- Practicedb
Employee_detail (eno , ename , title , salary , dno )
Project ( pno , pname , budget , dno )
Dept ( dno , dname , mgreno )
1. Create tables with the following name and given attributes as given above.
Solution:-
è Create database practicedb;
è Use practicedb;
è Create table employee_detail (eno int , ename varchar(35) , title varchar(35) , salary int , dno int );
è Create table project (pno int , pname varcahr(35) , budget int , dno int );
è Create table dept (dno int , dname varchar(35) , mgreno int );
è Desc employee_detail;
è Desc project;
è Desc dept;
2. Insert values in the table as suggested above.
Solution:-
è Insert into employee_detail values (101,”Netra”,”DG”,45000,1001) , (102,”Durga”,”prog”,47000,1002),(103,”Suraj”,”NWA”,40000,1003),
(104,”Rajani”,”EE”,25000,1004),(105,”Milan”,”mgr”,50000,1005),
(106,”Anita”,”PM”,70000,1006),(107,”DK”,”SA”,65000,1007),
(108,”Darsan”,”DBA”,75000,1008),(109,”Sushila”,”clerk”,80000,1009);
è Select * from employee_detail;
è Insert into project values(101,”sweeper”,500000,1001),(102,”mine”,950000,1002),
(103,”Geronimo”,800000,1003),(104,”tantric”,150000,1004),(105,”Sherpa”,250000,1005),(106,”mechi”,300000,1006),(107,”Godzilla”,755000,1007),(108,”giraffe”,555555,1008);
è Insert into dept values(1001,”consulting”,101),(1002,”net”,102),(1003,”Devmnt”,1003),
(104,”Admin”,1004),(105,”mgnt”,1005),(106,”Tech_sup”,1006),(107,”recpt”,1007) ;
è 1. Select * from project ;
è 2. Select * from dept ;
3. Add a column “Address” in employee_detail.
Solution:-
è Alter table employee_detail add Address varchar(45) ;
è Desc employee_detail ;
4. List all the project that has budget greater than 2,00,000.
Solution:-
è Select * from project
Where budget > 200000 ;
5. Count the project whose budget is greater than 2,00,000.
Solution:-
è Select count ( * ) from project
Where budget > 250000 ;
6. Return the name of the employee_detail whose salary is maximum and minimum.
Solution:-
è Select max ( salary ) from employee_detail ;
è Select min ( salary ) from employee_detail ;
7. Get the names of the employee_detail whose name start with the latter ‘N’ .
Solution:-
è Select ename from employee_detail
Where ename like ’N%’ ;
8. Write an SQL query that returns the employee name , department number & employee title .
Solution:-
è Select ename , dno , title
From employee_detail ;
9. Write a SQL query that returns the employee name with a salary greater than 35,000.
Solution:-
è Select ename from employee_detail
Where salary > 35000 ;
10. Write an SQL query that returns the project number and project name for project with a budget greater than Rs. 1,00,000 .
Solution:-
è Select pno , pname from project
Where budget > 100000 ;
11. Write an SQL query that name of the employee with maximum salary and title is ‘prog’.
Solution:-
è Select max ( salary )
from employee_detail
where title = “ prog “;
12. Write and SQL query that returns the employee (number and name only) who have a title of ‘EE’ or ‘SA’ and make more than Rs. 35,000.
Solution:-
è Select eno , ename from employee_detail
Where title=”EE” OR “SA” AND salary > 35000 ;
13. Write an SQL query that returns the employee numbers and salaries of all employee in the ‘Consulting’ department ordered by descending salary.
Solution:-
è Select eno, salary from employee_detail, dept
Where employee_detail.dno = dept.dno AND dept.dname=”consulting”
Order by salary desc ;
14. Write an SQL query that returns the employee name , title and department name.
Solution:-
è Select employee_detail.ename, employee_detail.title, Dept.dname
From employee_detail, dept
Where employee_detail.dno = dept.dno ;
15. Write an SQL query the arrange the salary of employee in ‘Ascending’ and ‘Descending’ order.
Solution:-
è Select salary from employee_detail
Order by salary asc ;
è Select salary from employee_detail
Order by salary desc ;
16. Return the employee name and title who works in dno=1001 (I would like to see the use of SUBQUERY here).
Solution:-
è Select ename, title from employee_detail
Where dno=(select dno from employee_detail Where dno=1001);
17. Create a view table with project name and budget from budget table.
Solution:-
è Create view budget as
Select pname , budget From project ;
è Select * from budget ;
