https://www.youtube.com/watch?v=nX1YZ9HiaWw&list=PL3HCtAHf9xFelx51jf-b9vfCBgZkVcS35&index=4&t=3s
Solve the following :
1.Retrieve the birth date and address of the employee(s) whose name is 'John B.Smith'.
2.Retrieve the name and address of all employees who work for the 'Research' department.
3.For every project located in 'Stafford',list the project number,the controlling department number,and the department manager's last name,address,and birth date.
4.Retrieve the names of all employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
5.List the names of all employees who have a dependent with the same first name as themselves.
6.Find the names of all employees who are directly supervised by 'Franklin Wong'.
7.Make a list of all project numbers for project that involve an employee whose last name is 'Smith',either as a worker or as a manager of the department that controls the project.
8.for each employee ,retrive the employee's first and last name and the first and last name of his or her immediate supervisor.
9.select all employees ssn and all combinations of employees ssn and department dname in the database.
10.Retrieve the salary of every employee and all distinct salary values.
11.Retrieve all employees whose address is in Houston, Texas.
12.Find all employees who were born during the 1950s.
13.Show the resulting salaries if every employee working on the 'ProductX' project is given a 10 percent raise.
14.Retrieve all employees in department 5 whose salary is between 30000 and 40000.
15.Retrieve a list of employees and the projects they are working on, ordered by department and,within each department, ordered alphabetically by last name, then first name.
16.Find the names of employees who work on all the projects controlled by department number 5.
17.List the names of all employees with two or more dependents.
18.Retrieve the names of employees who have no dependents.
19.List the names of managers who have atleast one dependent.
20.Retrieve the names of all employees who donot have supervisers.
21.Retrieve the name of each employees who has a dependent with the same first name and is the same sex as the employee.
22.Retrieve the social security numbers of all employees who work on project number 1,2, or 3.
23.Find the sum of salaries of all employees,the maximum salary,the minimum salary,and the average salary.
24.Find the sum of the salaries of all employees of the 'Research' department,as well as the maximum salary,the minimum salary,and the average salary in this department.
25.Retrieve the total number of employees in the company.
26.Retrieve the number of employees in the 'Research' department.
27.Count the number of distinct salary values in the database.
28.For each department, retrieve the department number, the number of employees in the department and their average salary.
29.For each project, retrieve the project number, the project name, and the number of employees who work on that project.
30.For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.
31.For each project, retrieve the project number,the project name, and the number of employees from department 5 who work on the project.
32.For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than 40000.
33. Display all employees names along with their department names.
34. Display all employees names along with their dependent names.
35.List the names of employees along with the names of their supervisors using aliases.
36.Display names of the department and mane of manager for all the departments.
37.List the departments of each female employee along with her name.
38.List all employee names and also the names of the department they manage if they happen to manage a dept.
39.Retrieve the names of employee who work on all projects that 'john' work on.
40.For each project, list the project name and total hours(by all employee) spent on that project.
41.Display the name and total number of hours worked by an employee who is working on maximium number of projects among all the employee.
42.Display the names of all employees and also no.of hours, project names that they work on if they happen to work on any project(use outer join).
43.List the employee name, project name on which they work and the department they belong to for all the employees using alias names for the resulting columns.
44.List all the departments that contain at least one occurence of 'C' in their names.
45.List the projects that are controlled by one department.
46.List the managers of the controlling department for all the projects.
47.List the location of the controlling departments for all the projects.
Syntax for creating :
create table employee(fname varchar2(20),minit varchar2(1),lname varchar2(20),ssn number(10) primary key,bdate date,address varchar2(40),sex varchar2(1),salary number(5),super_ssn number(9),dno number(1));
Insert into employee values('&fname','&minit','&lname',&ssn,'&bdate','&address','&sex',&salary,&super_ssn,&dno)
create table department(dname varchar2(13),dnum number(1) primary key,mgr_ssn number(9),mgr_start_date date);
insert into department values('&dname',&dnum,&mgr_ssn,'&mgr_start_date');
Create table Dept_locations(dnum number(1),dlocation varchar2(10),primary key(dnum,dlocation) );
insert into Dept_locations values(&dnum,'&dlocation');
create table Works_on(essn number(10),pno number(2),hours number(3,1),primary key(essn,pno));
insert into works_on values(&essn,&pno,&hours);
create table project(pname varchar2(20),pnum number(2) primary key,plocations varchar2(10),dnum number(1));
insert into project values('&pname',&pnum,'&ploc',&dnum);
create table dependent(essn number(9),dependent_name varchar2(10),sex char(1),bdate date,realationship varchar2(10),primary key(essn,dependent_name));
insert into dependent values(&essn,'&dependent_name','&sex','&bdate','&realationship');
alter table employee add foreign key(super_ssn) references employee ;
alter table employee add foreign key(dno) references department ;
alter table department add foreign key(mgr_ssn) references employee ;
alter table works_on add foreign key(essn) references employee ;
alter table works_on add foreign key(pno) references project ;
alter table project add foreign key(dnum) references department ;
alter table dependent add foreign key(essn) references employee ;
