Review of additional issues DISTINCT 1. Select title codes and descriptions of the titles that are...

Transcribed Image Text:

Review of additional issues DISTINCT 1. Select title codes and descriptions of the titles that are actually used by the employees of the company (see also outer joins). OUTER JOIN 1. Select title codes and descriptions of the titles that are actually used by the employees of the company (see also distinct). 2. For each department, list the code and name, followed by the names of employees working in the department (if any). 3. For the task 6 of SQL Review 4, for the employee who is not supervised by anyone, assign NULL as the supervisor's ID and retrieve the needed data. CREATE TABLE Title ( Title Code varchar(255) not null Primary Key, Title Discription varchar(255), Salary Number(11)); insert into Title (Title_Code, Title_Discription, Salary) values (T1', 'Accountant", "10000"); insert into Title (Title Code, Title Discription, Salary) values (T2', 'Analyst", "20000'); insert into Title (Title_Code, Title_Discription, Salary) values (T3", "Programmer", "30000"), insert into Title (Title Code, Title Discription, Salary) values (T4', 'DBA', '40000"); insert into Title (Title Code, Title_Discription, Salary) values (T5', 'Manager', '50000"); CREATE TABLE Department ( dep_Code number(11) not null Primary Key, dept Name varchar(255), location varchar(255), Dept Type varchar(255)); insert into Department(dep_Code,dept_name,location, Dept Type) values ('001', 'Computer Center', 'Boston', 'IT'); insert into Department(dep_Code,dept_name,location, Dept Type) values ('002', 'Budget', 'New York', 'Business"); insert into Department(dep_Code,dept_name.location, Dept Type) values Dept Type) values insert into Department(dep_Code,dept_name.location, Dept Type) values ('005', 'Purchasing', 'New York', 'Business'); ('003', 'Marketing', 'Boston', 'Marketing'); insert into Department(dep_Code,dept_name,location, ('004', 'Database Support', 'Cleveland', 'IT'); CREATE TABLE Employee ( ID number(11) not null Primary Key, emp Name varchar(255), empl Type varchar(255), dept Code number(11) not null, null, title Code varchar(255) not constraint fk_dep_Code foreign key (dep_Code) references Department(dep_Code). Constraint fk_title_Code foreign key (title_Code) references Title(title_Code)); insert into Employee(ID, emp_name,empl_Type,dept Code,title_Code) values (1', 'John', 'Full-time', '002', 'T1'); insert into Employee(ID, emp_name,empl_ Type,dept Code,title_Code) values (2, 'Adam', 'Consultant', '001', 'T3'); insert into Employee(ID, emp_name,empl_Type,dept Code,title_Code) values (3', 'Mary', 'Part-time', '004', 'T4"); insert into Employee(ID, emp_name,empl_Type,dept Code,title_Code) values (4', 'Peter', 'Full-time', '003', 'T2'); insert into Employee(ID, emp_name,empl Type,dept Code,title_Code) values ('5', 'Scott', 'Consultant', '002', 'T1'); insert Employee(ID, emp_name,empl_Type,dept Code,title_Code) values ('6'. "Susan', 'Full-time', '005', 'T5'); insert into Employee(ID, emp_name,empl Type,dept Code,title_Code) values (7', 'Alex', 'Part-time', '004", "T2"); Review of additional issues DISTINCT 1. Select title codes and descriptions of the titles that are actually used by the employees of the company (see also outer joins). OUTER JOIN 1. Select title codes and descriptions of the titles that are actually used by the employees of the company (see also distinct). 2. For each department, list the code and name, followed by the names of employees working in the department (if any). 3. For the task 6 of SQL Review 4, for the employee who is not supervised by anyone, assign NULL as the supervisor's ID and retrieve the needed data. CREATE TABLE Title ( Title Code varchar(255) not null Primary Key, Title Discription varchar(255), Salary Number(11)); insert into Title (Title_Code, Title_Discription, Salary) values (T1', 'Accountant", "10000"); insert into Title (Title Code, Title Discription, Salary) values (T2', 'Analyst", "20000'); insert into Title (Title_Code, Title_Discription, Salary) values (T3", "Programmer", "30000"), insert into Title (Title Code, Title Discription, Salary) values (T4', 'DBA', '40000"); insert into Title (Title Code, Title_Discription, Salary) values (T5', 'Manager', '50000"); CREATE TABLE Department ( dep_Code number(11) not null Primary Key, dept Name varchar(255), location varchar(255), Dept Type varchar(255)); insert into Department(dep_Code,dept_name,location, Dept Type) values ('001', 'Computer Center', 'Boston', 'IT'); insert into Department(dep_Code,dept_name,location, Dept Type) values ('002', 'Budget', 'New York', 'Business"); insert into Department(dep_Code,dept_name.location, Dept Type) values Dept Type) values insert into Department(dep_Code,dept_name.location, Dept Type) values ('005', 'Purchasing', 'New York', 'Business'); ('003', 'Marketing', 'Boston', 'Marketing'); insert into Department(dep_Code,dept_name,location, ('004', 'Database Support', 'Cleveland', 'IT'); CREATE TABLE Employee ( ID number(11) not null Primary Key, emp Name varchar(255), empl Type varchar(255), dept Code number(11) not null, null, title Code varchar(255) not constraint fk_dep_Code foreign key (dep_Code) references Department(dep_Code). Constraint fk_title_Code foreign key (title_Code) references Title(title_Code)); insert into Employee(ID, emp_name,empl_Type,dept Code,title_Code) values (1', 'John', 'Full-time', '002', 'T1'); insert into Employee(ID, emp_name,empl_ Type,dept Code,title_Code) values (2, 'Adam', 'Consultant', '001', 'T3'); insert into Employee(ID, emp_name,empl_Type,dept Code,title_Code) values (3', 'Mary', 'Part-time', '004', 'T4"); insert into Employee(ID, emp_name,empl_Type,dept Code,title_Code) values (4', 'Peter', 'Full-time', '003', 'T2'); insert into Employee(ID, emp_name,empl Type,dept Code,title_Code) values ('5', 'Scott', 'Consultant', '002', 'T1'); insert Employee(ID, emp_name,empl_Type,dept Code,title_Code) values ('6'. "Susan', 'Full-time', '005', 'T5'); insert into Employee(ID, emp_name,empl Type,dept Code,title_Code) values (7', 'Alex', 'Part-time', '004", "T2");

Answer rating: 100% (QA)

DISTINCT From this query we will get the distinct entries of title codes description ... View the full answer