My SQL WORKBENCH **Please note that you may actually use as many tables as you need. You are not limited to use only two tables. Example: SELECT * FROM DEPARTMENTS; SELECT * FROM EMPLOYEES; Now join departments and employees tables. SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID; NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A Cartesian Product joins all rows of the first table with all rows of the second table. That is if the first table has n rows and the second table has m rows, the output will have n*m rows. The following query will produce a Cartesian Product: SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS; **When table names are long, qualifying column names might be very time consuming. Usually people prefer to use table aliases for this purpose. Example: List the name of all employees who work for 'Purchasing' department SELECT E.FIRST_NAME "EMPLOYEE NAME" FROM EMPLOYEES E, DEPARTMENT D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND UPPER(D.DEPARTMENT_NAME) = 'PURCHASING'; **Table joins can be performed on columns that are not primary or foreign keys as well. SELECT E.FIRST_NAME FROM EMPLOYEES A, EMPLOYEES B WHERE A.SALARY = B.SALARY AND A.EMPLOYEE_ID != B.EMPLOYEE_ID; **All these joins are called equijoin because they use an equality to form the join. It is also possible to form nonequijoins. These are joins that use an operator (+) than other. Normally if a row does not satisfy a join condition, it will be left out of the result. You want to see rows of a table that do not satisfy the join condition as well, you have to perform an outer join. Outer join is performed by placing a (+) sign on the right hand side of one of the attribute of join condition. The (+) sign makes it possible to join a 'NULL' row of the table it is close to with rows from the other table. In other words (+) will allow NULL rows on the side that is placed. Syntax of an outer join: SELECT table1.column11, table1.column12, ..., table2.column21, table2.column22 FROM table1, table2 WHERE table1.column11(+) = table2.column21(+); **Please be careful, you can use only one (+) in the join condition. Outer Examples: Example1: Display last name of all employees with their department information (department id and department name) including those employees who does not work for any department. SELECT e.last_name,d.department_id,d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+); Example2: Display last name of all employees with their department information (department id and department name) including those departments that they do not have any employee. SELECT e.last_name,d.department_id,d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id; SELF JOIN Joining a Table to Itself: Sometimes you need to join a able to itself. To find the name of each employee's manager, you need to join the EMPLOYEES table to itself. Example:Find the name of the managers for all employees. SELECT e.last_name,m.last_name FROM employees e, employees m WHERE .manager_id=m.employee_id; SQL> desc locations Name Null? Type ----------------------------------------- -------- ---------------------------- LOCATION_ID NOT NULL NUMBER(6) DEPARTMENT_ID NOT NULL NUMBER(6) ADDRESS VARCHAR2(25) CITY NOT NULL VARCHAR2(25) STATE NOT NULL VARCHAR2(25) COUNTRY VARCHAR2(20) EXERCISES PART A: 4-Display employees' last name and employee number along with their manager's last name and manager number. Label the columns Employee, Emp#, Manager Mgr#, respectively. 5-Display the last name, department number,department name and region name for all employees who work in Europe. 6-What is the name of the manager for employee WINSTON (first name). 7-Display last name of employees whose manager is KING. 8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and Jobs) 9-Display all employees including King, who has no manager. Order the results by the employee number. 10-Display the name and hire date of any employee hired after employee Davis. PART B: Will the following select statements work? If the queries don’t work, explain why and then correct them. a-) SELECT NAME, EMPLOYEE_ID, SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE EMPLOYEE_ID = EMPLOYEE_ID AND NAME LIKE '%MITH'; b-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE NAME LIKE '%MITH'; c-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE E.EMPLOYEE_ID = EP.EMPLOYEE_ID AND E.NAME LIKE '%MITH'; d-) SELECT ENAME, COUNT(*) FROM EMP WHERE ENAME = '&NAME' ORDER BY ENAME; e-) SELECT DEPT_ID, COUNT(*) FROM EMP GROUP BY DEPT_ID HAVING ENAME='ALIYE'; f-) SELECT F_NAME, L_NAME WHERE F_NAME LIKE '%A' FROM EMP, DEPT; g-) SELECT NAME, SALARY FROM DEPT, EMP WHERE DEPT.ID = EMP.ID AND SUM(SALARY);
My SQL WORKBENCH **Please note that you may actually use as many tables as you need. You are not limited to use only two tables. Example: SELECT * FROM DEPARTMENTS; SELECT * FROM EMPLOYEES; Now join departments and employees tables. SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID; NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A Cartesian Product joins all rows of the first table with all rows of the second table. That is if the first table has n rows and the second table has m rows, the output will have n*m rows. The following query will produce a Cartesian Product: SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS; **When table names are long, qualifying column names might be very time consuming. Usually people prefer to use table aliases for this purpose. Example: List the name of all employees who work for 'Purchasing' department SELECT E.FIRST_NAME "EMPLOYEE NAME" FROM EMPLOYEES E, DEPARTMENT D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND UPPER(D.DEPARTMENT_NAME) = 'PURCHASING'; **Table joins can be performed on columns that are not primary or foreign keys as well. SELECT E.FIRST_NAME FROM EMPLOYEES A, EMPLOYEES B WHERE A.SALARY = B.SALARY AND A.EMPLOYEE_ID != B.EMPLOYEE_ID; **All these joins are called equijoin because they use an equality to form the join. It is also possible to form nonequijoins. These are joins that use an operator (+) than other. Normally if a row does not satisfy a join condition, it will be left out of the result. You want to see rows of a table that do not satisfy the join condition as well, you have to perform an outer join. Outer join is performed by placing a (+) sign on the right hand side of one of the attribute of join condition. The (+) sign makes it possible to join a 'NULL' row of the table it is close to with rows from the other table. In other words (+) will allow NULL rows on the side that is placed. Syntax of an outer join: SELECT table1.column11, table1.column12, ..., table2.column21, table2.column22 FROM table1, table2 WHERE table1.column11(+) = table2.column21(+); **Please be careful, you can use only one (+) in the join condition. Outer Examples: Example1: Display last name of all employees with their department information (department id and department name) including those employees who does not work for any department. SELECT e.last_name,d.department_id,d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+); Example2: Display last name of all employees with their department information (department id and department name) including those departments that they do not have any employee. SELECT e.last_name,d.department_id,d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id; SELF JOIN Joining a Table to Itself: Sometimes you need to join a able to itself. To find the name of each employee's manager, you need to join the EMPLOYEES table to itself. Example:Find the name of the managers for all employees. SELECT e.last_name,m.last_name FROM employees e, employees m WHERE .manager_id=m.employee_id; SQL> desc locations Name Null? Type ----------------------------------------- -------- ---------------------------- LOCATION_ID NOT NULL NUMBER(6) DEPARTMENT_ID NOT NULL NUMBER(6) ADDRESS VARCHAR2(25) CITY NOT NULL VARCHAR2(25) STATE NOT NULL VARCHAR2(25) COUNTRY VARCHAR2(20) EXERCISES PART A: 4-Display employees' last name and employee number along with their manager's last name and manager number. Label the columns Employee, Emp#, Manager Mgr#, respectively. 5-Display the last name, department number,department name and region name for all employees who work in Europe. 6-What is the name of the manager for employee WINSTON (first name). 7-Display last name of employees whose manager is KING. 8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and Jobs) 9-Display all employees including King, who has no manager. Order the results by the employee number. 10-Display the name and hire date of any employee hired after employee Davis. PART B: Will the following select statements work? If the queries don’t work, explain why and then correct them. a-) SELECT NAME, EMPLOYEE_ID, SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE EMPLOYEE_ID = EMPLOYEE_ID AND NAME LIKE '%MITH'; b-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE NAME LIKE '%MITH'; c-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE E.EMPLOYEE_ID = EP.EMPLOYEE_ID AND E.NAME LIKE '%MITH'; d-) SELECT ENAME, COUNT(*) FROM EMP WHERE ENAME = '&NAME' ORDER BY ENAME; e-) SELECT DEPT_ID, COUNT(*) FROM EMP GROUP BY DEPT_ID HAVING ENAME='ALIYE'; f-) SELECT F_NAME, L_NAME WHERE F_NAME LIKE '%A' FROM EMP, DEPT; g-) SELECT NAME, SALARY FROM DEPT, EMP WHERE DEPT.ID = EMP.ID AND SUM(SALARY);
Database Systems: Design, Implementation, & Management
12th Edition
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Carlos Coronel, Steven Morris
Chapter8: Advanced Sql
Section: Chapter Questions
Problem 3P
Related questions
Concept explainers
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Question
My SQL WORKBENCH
**Please note that you may actually use as many tables as you need. You are not limited to use only two
tables.
Example:
SELECT *
FROM DEPARTMENTS;
SELECT *
FROM EMPLOYEES;
Now join departments and employees tables.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;
NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A Cartesian
Product joins all rows of the first table with all rows of the second table.
That is if the first table has n rows and the second table has m rows, the output will have n*m rows.
The following query will produce a Cartesian Product:
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS;
**When table names are long, qualifying column names might be very time consuming. Usually people
prefer to use table aliases for this purpose.
Example: List the name of all employees who work for 'Purchasing' department
SELECT E.FIRST_NAME "EMPLOYEE NAME"
FROM EMPLOYEES E, DEPARTMENT D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
UPPER(D.DEPARTMENT_NAME) = 'PURCHASING';
**Table joins can be performed on columns that are not primary or foreign keys as well.
SELECT E.FIRST_NAME
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.SALARY = B.SALARY AND A.EMPLOYEE_ID != B.EMPLOYEE_ID;
tables.
Example:
SELECT *
FROM DEPARTMENTS;
SELECT *
FROM EMPLOYEES;
Now join departments and employees tables.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;
NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A Cartesian
Product joins all rows of the first table with all rows of the second table.
That is if the first table has n rows and the second table has m rows, the output will have n*m rows.
The following query will produce a Cartesian Product:
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS;
**When table names are long, qualifying column names might be very time consuming. Usually people
prefer to use table aliases for this purpose.
Example: List the name of all employees who work for 'Purchasing' department
SELECT E.FIRST_NAME "EMPLOYEE NAME"
FROM EMPLOYEES E, DEPARTMENT D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
UPPER(D.DEPARTMENT_NAME) = 'PURCHASING';
**Table joins can be performed on columns that are not primary or foreign keys as well.
SELECT E.FIRST_NAME
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.SALARY = B.SALARY AND A.EMPLOYEE_ID != B.EMPLOYEE_ID;
**All these joins are called equijoin because they use an equality to form the join. It is also possible to form
nonequijoins. These are joins that use an operator (+) than other.
Normally if a row does not satisfy a join condition, it will be left out of the result. You want to see rows of a
table that do not satisfy the join condition as well, you have to perform an outer join. Outer join is performed
by placing a (+) sign on the right hand side of one of the attribute of join condition.
The (+) sign makes it possible to join a 'NULL' row of the table it is close to with rows from the other
table. In other words (+) will allow NULL rows on the side that is placed.
Syntax of an outer join:
SELECT table1.column11, table1.column12, ..., table2.column21, table2.column22
FROM table1, table2
WHERE table1.column11(+) = table2.column21(+);
**Please be careful, you can use only one (+) in the join condition.
Outer Examples:
Example1: Display last name of all employees with their department information (department id and department
name) including those employees who does not work for any department.
SELECT e.last_name,d.department_id,d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+);
Example2: Display last name of all employees with their department information (department id and
department name) including those departments that they do not have any employee.
SELECT e.last_name,d.department_id,d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id;
SELF JOIN
Joining a Table to Itself:
Sometimes you need to join a able to itself.
To find the name of each employee's manager, you need to join the EMPLOYEES table to itself.
Example:Find the name of the managers for all employees.
SELECT e.last_name,m.last_name
FROM employees e, employees m
WHERE .manager_id=m.employee_id;
SQL> desc locations Name Null? Type
----------------------------------------- --------
----------------------------
LOCATION_ID NOT NULL NUMBER(6)
DEPARTMENT_ID NOT NULL NUMBER(6)
ADDRESS VARCHAR2(25)
CITY NOT NULL VARCHAR2(25)
STATE NOT NULL VARCHAR2(25)
COUNTRY VARCHAR2(20)
nonequijoins. These are joins that use an operator (+) than other.
Normally if a row does not satisfy a join condition, it will be left out of the result. You want to see rows of a
table that do not satisfy the join condition as well, you have to perform an outer join. Outer join is performed
by placing a (+) sign on the right hand side of one of the attribute of join condition.
The (+) sign makes it possible to join a 'NULL' row of the table it is close to with rows from the other
table. In other words (+) will allow NULL rows on the side that is placed.
Syntax of an outer join:
SELECT table1.column11, table1.column12, ..., table2.column21, table2.column22
FROM table1, table2
WHERE table1.column11(+) = table2.column21(+);
**Please be careful, you can use only one (+) in the join condition.
Outer Examples:
Example1: Display last name of all employees with their department information (department id and department
name) including those employees who does not work for any department.
SELECT e.last_name,d.department_id,d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+);
Example2: Display last name of all employees with their department information (department id and
department name) including those departments that they do not have any employee.
SELECT e.last_name,d.department_id,d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id;
SELF JOIN
Joining a Table to Itself:
Sometimes you need to join a able to itself.
To find the name of each employee's manager, you need to join the EMPLOYEES table to itself.
Example:Find the name of the managers for all employees.
SELECT e.last_name,m.last_name
FROM employees e, employees m
WHERE .manager_id=m.employee_id;
SQL> desc locations Name Null? Type
----------------------------------------- --------
----------------------------
LOCATION_ID NOT NULL NUMBER(6)
DEPARTMENT_ID NOT NULL NUMBER(6)
ADDRESS VARCHAR2(25)
CITY NOT NULL VARCHAR2(25)
STATE NOT NULL VARCHAR2(25)
COUNTRY VARCHAR2(20)
EXERCISES
PART A:
PART A:
4-Display employees' last name and employee number along with their manager's last name and manager number.
Label the columns Employee, Emp#, Manager Mgr#, respectively.
5-Display the last name, department number,department name and region name for all employees who work in
Europe.
6-What is the name of the manager for employee WINSTON (first name).
7-Display last name of employees whose manager is KING.
8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and
Jobs)
9-Display all employees including King, who has no manager. Order the results by the employee number.
10-Display the name and hire date of any employee hired after employee Davis.
PART B:
Will the following select statements work? If the queries don’t work, explain why and then correct them.
a-) SELECT NAME, EMPLOYEE_ID, SALARY
FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP
WHERE EMPLOYEE_ID = EMPLOYEE_ID AND NAME LIKE '%MITH';
b-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY
FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP
WHERE NAME LIKE '%MITH';
c-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY
FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP
WHERE E.EMPLOYEE_ID = EP.EMPLOYEE_ID AND E.NAME LIKE '%MITH';
d-) SELECT ENAME, COUNT(*)
FROM EMP
WHERE ENAME = '&NAME'
ORDER BY ENAME;
e-) SELECT DEPT_ID, COUNT(*)
FROM EMP
GROUP BY DEPT_ID
HAVING ENAME='ALIYE';
f-) SELECT F_NAME, L_NAME
WHERE F_NAME LIKE '%A'
FROM EMP, DEPT;
g-) SELECT NAME, SALARY
FROM DEPT, EMP
WHERE DEPT.ID = EMP.ID AND SUM(SALARY);
Label the columns Employee, Emp#, Manager Mgr#, respectively.
5-Display the last name, department number,department name and region name for all employees who work in
Europe.
6-What is the name of the manager for employee WINSTON (first name).
7-Display last name of employees whose manager is KING.
8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and
Jobs)
9-Display all employees including King, who has no manager. Order the results by the employee number.
10-Display the name and hire date of any employee hired after employee Davis.
PART B:
Will the following select statements work? If the queries don’t work, explain why and then correct them.
a-) SELECT NAME, EMPLOYEE_ID, SALARY
FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP
WHERE EMPLOYEE_ID = EMPLOYEE_ID AND NAME LIKE '%MITH';
b-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY
FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP
WHERE NAME LIKE '%MITH';
c-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY
FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP
WHERE E.EMPLOYEE_ID = EP.EMPLOYEE_ID AND E.NAME LIKE '%MITH';
d-) SELECT ENAME, COUNT(*)
FROM EMP
WHERE ENAME = '&NAME'
ORDER BY ENAME;
e-) SELECT DEPT_ID, COUNT(*)
FROM EMP
GROUP BY DEPT_ID
HAVING ENAME='ALIYE';
f-) SELECT F_NAME, L_NAME
WHERE F_NAME LIKE '%A'
FROM EMP, DEPT;
g-) SELECT NAME, SALARY
FROM DEPT, EMP
WHERE DEPT.ID = EMP.ID AND SUM(SALARY);
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 3 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr