please consider the above image. This problem involves a Information Technology

Question

please consider the above image.

This problem involves a Information Technology course, Information System Design and the current topic invlovling this problem is constucting SQL database. Please construct the database syntax in SQL for the following queries. So far we are using the structure below to solve.

Select

From

Where

Please find the Queries below. Instructions in bold following the query is where a specific statement is to be used to contruct the SQL Tables. Please advise if you need additional information and do not provide just anything, please.

A. Queries I Single Table Queires

1. Find all information on employees

2. Find the first and last names of employees

3. Find unique titles in the employee database

5. Find the employee number, start date and monthly salary of each employee

6. Find the employee number of all employees who make between $60000 and $71000 (both inclusive)

7. Find all information about employees whose last name or first name is Demeyer

8. Find employee numbers, start and end date of all employees who are Senior Engineers, Assistant Engineers, Engineers, or Technique Leaders (Use the IN statement)

9. Find employee numbers, start and end date of all employees whose name is not Facello, Zielinski, Haddadi, Berztiss or Demeyer

B. Query II Pattern Matching

9. Find all employees who have the letters ‘lin’ in their last name

10. Find all employees whose last names end in ‘lin’

11. Find all employees whose last names begin with ‘Mc’ and end with ‘lin’

12. Find salaries of employees by ascending order, and descending order of start date

C. Query III Agregate Functions

13. Find the maximum, minimum and average salaries

14. Find the number of employees born after January 01, 1965

15. Find the department number, and number of managers in departments that have more than 3 managers

titles emp_no INT (11) ? title VARGAR(50) from_date DATE to-date DATE PRIMARY ? emp.INT(11) emp no dept_no CHAR(4 from-date DATE toda DATE em ployees emp_no INT (11) birt -date DATE first-name VARCHAR( 14) salaries emp-no INT (11) salary INT(11) fromdate DATE - dept no CHAR(4) dept name VARCHAR(40) PRIMARY emp no dept _no - to-date DATE last-name VARCHAR(16) gender ENUM(W,F) PRIMARY PRIMARY dept_name hire_date DATE emp no | dept-manager dept no CHAR(4) PRIMARY ? emp-no INT(11) from _da DATE to-date DATE PRIMARY emp no dept no

Explanation / Answer

A. Queries I Single Table Queries

1.

Select * from employees;

2.

Select first_name,last_name from employees;

3.

Select distinct title from titles;

5.

Select emp_no,from_date,salary from salaries;

6.

Select emp_no from salaries where salary between 60000 and 71000;

7.

Select * from employees where first_name = 'Demeyer' or last_name = 'Demeyer';

8.

Select emp_no ,from_date, to_date from titles where title IN('Senior Engineer' , 'Assistant Engineer' , ' Engineer' , 'Technique Leader');

9.

Select emp_no, from_date, to_date from employees where first_name NOT IN('Facello', 'Zielinski', 'Haddadi', 'Berztiss' ,'Demeyer');

B. Query II Pattern Matching

9.

Select * from employees where last_name LIKE '%lin%';

10.

Select * from employees where last_name LIKE '%lin';

11.

Select * from employees where last_name LIKE 'Mc%lin';

12.

Select salary from salaries order by salary asc, from_date desc;

C. Query III Agregate Functions

13.

Select max(salary), min(salary) , avg(salary) from salaries;

14.

Select count(emp_no) from employees where birth_date > 'January 01,1965' ;

15.

Select dept_no,count(emp_no) from dept_manager group by dept_no having count(emp_no) > 3;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.