During this course we are going to use Oracle Express as our DMBS. You will need
ID: 3561690 • Letter: D
Question
During this course we are going to use Oracle Express as our DMBS. You will need to start by downloading Oracle Express from the Internet. Once you have Oracle Express installed and configured, the next step you will need to accomplish is the creation of a users account. Next login as the newly created user and create all the relations (tables) you will need to complete the exercises for chapters 1 - 13. You will find an SQL script for creating these tables and inserting the needed data attached to this assignment. Hands-On Assignments: Lesson 1 Show the structure of the DEPT table. Select all data from the DEPT table. Show the structure of the EMP table. Create a query to display the name, job, hire date, and employee number for each employee with the employee number appearing first. Create a query to display unique jobs from the EMP table. Create a query to display the employee number, employee name, job, and hire date. Name the columns: Emp #, Employee, Job, and Hire Date. Create a query that displays the employee name concatenated with the job title, separated by a comma and space, name the column "Employee and Title". Create a query to display all the data from the EMP table. Separate each column by a comma. Lesson 2 Create a query to display the name and salary of employees earning more than $2850. Create a query to display the employee name and department number for each employee number 7566. Create a query to display the employee name and salary for each employee whose salary is not in the range of $1500 and $2850. Create a query to display the employee name, job and start date of employees hired between February 20, 1981 and May 1, 1981. Order the query in ascending order by start date. Create a query that displays the name and department number of all employees in departments 10 and 30 in alphabetical order. Create a query to list the names and salary of employees who earn more than $1500 and are in department 10 or 30. Label the columns Employee and Monthly Salary, respectively. Display the name and hire date of every employee who was hired in 1982. Display the name and job title of all employees who do not have a manger. Display the name, salary, and commission for all employees who earn commissions. Sort in descending order of salary and commission. Display the names of all employees where the third letter of their name is an 'A'. Display the name of all employees who have two 'L's in their name and are in department 30 or their manager is 7782. Display the name, job, salary for all employees whose job is Clerk or Analyst and their salary is not equal to $1000, #3000, or $5000. Display the name, salary, and commission for all employees whose commission amount is greater than their salary by 10%. Submit a single SQL file with a comment header similar to the following: /* * CIST 308 * Your Name * Date * Assignment */ Your SQL file documentation must include an explanation for each SQL command. You will also need to place a semicolon after each SQL statement, a requirement for SQL files containing multiple SQL statements. Attach your SQL document to this assignment by midnight, the last day of the academic week.
Explanation / Answer
Hi , I have answered half as per the points.....
1.Show the structure of the DEPT table
desc dept;
2.Select all data from the DEPT table
select * from dept;
3.Show the structure of the EMP table
select * from emp;
4.Create a query to display the name, job, hire date, and employee number for each employee with the employee number appearing first
select emp_number,name,job,hire_date from emp;
5.Create a query to display unique jobs from the EMP table.
select distinct jobs from emp;
6.Create a query to display the employee number, employee name, job, and hire date. Name the columns: Emp #, Employee, Job, and Hire Date
select emp_number as Emp#,employee_name as Employee,job as job,hire_date as hire_date
7.Create a query that displays the employee name concatenated with the job title, separated by a comma and space, name the column "Employee and Title".
select employee_name||','||job_title|| as "Employee and Title" from emp;
8.Create a query to display all the data from the EMP table. Separate each column by a comma.
select employee_number||','||employee_name||','||job||','||deptno||to_char(hire_date,'dd-mon-yy') from emp;
9.Create a query to display the name and salary of employees earning more than $2850
select employee_name,salary from emp
where salary>2850;
10.Create a query to display the employee name and department number for each employee number 7566
select employee_name,deptno from emp
where employee_number=7566;
11.Create a query to display the employee name and salary for each employee whose salary is not in the range of $1500 and $2850.
select employee_name , salary from emp
where salary not between 1500 and 2850;
12.Create a query to display the employee name, job and start date of employees hired between February 20, 1981 and May 1, 1981. Order the query in ascending order by start date
select employee_name,job,hire_date
from emp
where trunc(hire_date) between '20-FEB-1981' and '01-MAY-1981'
ORDER BY HIRE_DATE;
13.Create a query that displays the name and department number of all employees in departments 10 and 30 in alphabetical order.
SELECT EMPLOYEE_NAME,DEPTNO FROM EMP
WHERE DEPTNO IN (10,30)
ORDER BY EMPLOYEE_NAME;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.