Refer to the database Er diagram:-DEPARTMENTSdepartment_iddepartment_namemanager_idlocation_idLOCATIONSHRlocation_idstreet_addresspostal_codecitystate_provincecountry_idJOB_HISTORYemployee_idstart_dateΕMPLOYESend_datejob_iddepartment_idemployee_idfirst_namelast_nameemailCOUNTRIEScountry_idcountry_nameregion_idJOBSjob_idjob_titlemin_salarymax_salaryphone_numberhire_datejob_idsalarycommission_pctmanager_iddepartment_idREGIONSregion_idregion_name 1. Write a query that displays the employee number and last name ofall employees who work in a department with any employee whoselast name contains a "s". Use subquery2. The HR department needs a report that displays the last name,department number, and job ID of all employees whose working indepartment 90 and 100 and their department located in location ID1700.Use subquery

1. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a "s". Use subquery 2. The HR department needs a report that displays the last name, department number, and job ID of all employees whose working in department 90 and 100 and their department located in location ID 1700.Use subquery

1. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a "s". Use subquery 2. The HR department needs a report that displays the last name, department number, and job ID of all employees whose working in department 90 and 100 and their department located in location ID 1700.Use subquery

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: You should create a query that is based on the table Reservations table and that shows the guests…

A: Structured Query Language, abbreviated as SQL, which usually refers to the one it is a…

Q: The query should list all employees whose Position begins with the word greenhouse and whose weekly…

A: These question answer is as follows,

Q: Write a query to list the book number, title, and number of times each book has been checked out…

A: Query The query for viewing book number, title and the number of times each book had been checked…

Q: Write a query to display the first name, last name, phone number, title, and department number of…

A: Actually, A database is a collection of information that is organized so that it can be easily…

Q: Write a query to display the author ID, first and last name for all authors who have never written a…

A: The below query will return the author ID, first name, and last name for all authors who have never…

Q: Task 4: Create a view named PROPERTY_OWNERS. It consists of the property ID, office number, square…

A: Answer is given below .

Q: Write a query to display the author ID, first name, last name, and year of birth for all authors.…

A: A database may be defined in one of these ways it's an organized series of established facts or…

Q: Write a SELECT statement that uses a correlated subquery to return one row per customer,…

A: SYNTAX - SELECT cust.first_name, cust.last_name, o.order_id, o.order_date, o.total_quantityFROM…

Q: . Write a query to display the subject and the number of books in each subject. Sort the results by…

A: SQL has a few implicit capabilities and they are called as aggregate function.SQL contains five…

Q: Using Pubs: USE MICROSOFT SQL SERVER Create a query listing all the books that Barnum’s purchased.…

A: I attached your answer below.

Q: Write a query to display the first name, last name, and email address of employees hired from May 1,…

A: Here we have to write 2 queries whose details are as follows:Write a query to display the first…

Q: Write a query to display the book number, title and cost of books that have the lowest cost of any…

A: Query: In general, the term query describes a question or request that is made by a user or another…

Q: FROM SALESPERSON WHERE SPNUM= (SELECT SPNUM FROM CUSTOMER WHERE CUSTNUM=772): What is this subquery…

A: Given subquery SELECT SPNUM FROM CUSTOMER WHERE CUSTNUM=772

Q: 1) Display the Name of the Product and the corresponding name of the supplier of that Product.…

A: Structured Query Language (SQL) allows use of JOIN operation to obtain information from two or more…

Q: Student: studentNO, firstName, lastName, studentProgram Professor: professorID, professorProgram,…

A: SQL: SQL stands for Structured Query Language. It is used to acess and manipulate the databases.…

Q: 3. Write a query that retrieves the following results (note the column names and order of results):

A: The required result needs to fetch below columns : Pub_id -> from publisher table Publisher's…

Q: Write a query to display the rental number, rental date, movie title, and detail fee for each movie…

A: According to the given schema, first create a table Movies and insert some records in the table.…

Q: 7) Write a query to display Unique order dates from the shipper named “Speedy Express”.

A: According to Bartleby Guidelines we need to answer only one question so I have answered first…

Q: 2) Create a SELECT statement that returns the count, average, max and min of the invoices submitted…

A: SQL: SQL is defined as Standard Query Language it is mainly used for the manipulation of the…

Q: Write a query to list the author last name, author first name and the book title and cost for each…

A: Solution: FROM: It is used to get data from the table WHERE: It is used to list the data where given…

Q: 10. Produce a list of each customer number and the third and fourth digits of his or her zip code.…

A: The query for a list of each customer number and the third and fourth digits of his or her zipcode…

Q: Not all Global Fast Foods staff members have a manager. Create a query that displays the employee…

A: To create a query that display the employee last name and 9999 in the manager ID column.

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

Refer to the database Er diagram:-
DEPARTMENTS
department_id
department_name
manager_id
location_id
LOCATIONS
HR
location_id
street_address
postal_code
city
state_province
country_id
JOB_HISTORY
employee_id
start_date
ΕMPLOYES
end_date
job_id
department_id
employee_id
first_name
last_name
email
COUNTRIES
country_id
country_name
region_id
JOBS
job_id
job_title
min_salary
max_salary
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id
REGIONS
region_id
region_name

1. Write a query that displays the employee number and last name of
all employees who work in a department with any employee whose
last name contains a "s". Use subquery
2. The HR department needs a report that displays the last name,
department number, and job ID of all employees whose working in
department 90 and 100 and their department located in location ID
1700.Use subquery

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!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps with 1 images

SEE SOLUTION Check out a sample Q&A here

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.

Similar questions

Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 99675, 53845, and 44035. Sort the output by employee number and salary “from” date.
Write a query to list the book title, checkout date, checkout due date and checkout in date for each book.
What are the most notable differences between abstract classes and interfaces?
ERD:- PK CHECKOUT Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check_Due Date Check_In_Date PK BOOK Book Num Book Title Book_Year Book_Cost Book_Subject FK1 Pat_ID -H- PATRON PK Pat ID Pat_FName Pat_LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au_FName Au_LName Au_BirthYear
4) Display the firstname, lastname of the employees and the customerID that the employee served. Arrange the results by the last name. 5) Write a query to the Name of the Product and the corresponding name of the supplier of that Product for all the products that start with a “P”. 6) Write a SQL query to output the product name and the quantity of the Orders of that product for those orders with more than 70 quantity. (hint: don’t have to use GROUP BY) I need help figuring out the SQL quereys for the questions above using table joining method
If a subquery is used in the FROM clause of a query, how are the subquery’s results referenced in other clauses of the query?
Task 3: The InstantRide Finance team wants to collect the price and discount information with the driver names for each travel in the system. You need to return the TRAVEL_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME, TRAVEL_PRICE, and TRAVEL_DISCOUNT information from the TRAVELS and DRIVERS tables combined over DRIVER_ID field with the ON keyword. Task: Calculate each user's price and discount information. (SQL Database Test)
5. Which orders were placed on or before April 1, 2009? List each order number and orderdate. Write this query in two different ways.
Given a small company database tables below. Create the database and tables for the company and answer all the questions below. LINE CUSTOMER VENDOR INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE Cus CODE CUS_LNAME CUS_FNAME CUS_INITIAL cus AREACODE CUS_PHONE CUS_BALANCE V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER 1000 3 23100-HB 0.05 10010 Ramas Afred 015 844-2573 21225 Bryson, Ine. Smithson 015 223-3234 TN 1006 4 89-WRE-Q 256.00 10011 Dunne Leona 713 804-1238 21220 SuperLoo, Inc. Flushing 04 215-8006 FL N 1 13-Q2/P2 2 14.00 1007 10012 Smith Kathy 015 804-2205 345.88 21231 DSE Supply Singh 815 228-3246 TN 1007 254778-2T 4.00 10013 Olowski Paul 815 804-2100 530.75 21344 Gomez Bros. Ortega 815 889-2546 KY N 1008 1 PVC23DRT 5.87 10014 Orlando Myron le15 222-1872 22507 Dome Supply Smith 01 e78-1419 GA IN 1008 2 WR3/TT3 3 110.05 10015 OBrian Amy 713 442-3381 23110 Randsets Ltd. Anderson 001 078-3008 GA 1008 3 23100-HB 0.05 10010 Brown James 015 297-1228 221.19 24004…
Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 300 or have the title “CLERK I.” Sort the output by last name and then by first name
Q1. Please write a query statement from emp table to display employee's empno, ename, length of ename, number of 'S',and number of 'L'. SQL> SELECT empno AS "EmpNo", ename AS "Emp Name", ... AS "Name Length", .... AS "# Of S", .... AS "# Of L"FROM emp; [IMAGE below] Material to answer the question above: Material: https://drive.google.com/file/d/14OwWDIybjAIeuHCO7McupV08gJs9nB-8/view?usp=sharing https://drive.google.com/file/d/1npRH8JoJ6GYBzPU3iNjLXluvCyhr2tMu/view?usp=sharing
b. Write a query to get the average salary and the total number of employees who work in the History department (in that order). c. Write a query to get the number of employees working in the History department with a job title of Head of Department.