4. Write two queries to list the ID of all the authors that do not haveanybooks.a) Using a join:b) Using a subquery: mysql> DESC bookauthor;mysql> DESC author;mysql> DESC book;| Field| Туре| Field| Туре| FieldТур| int(11| varchaiauthor_order | int(11royaltyshare | decima:author_idisbn| author_id | int(1:| ssnlastname| firstname | varch:| phone| address| city| state| zip| varch:| varch:isbnvarI name| typepub_idI price| advanceI ytd_salesI pubdatevarvar| varch:| varch:| varch:| varch:| varch:| intdec| decintmysql> DESC bookeditor;| dat| Field| Туреmysql> DESC editor;mysql> DESC publisher;editor_id | int(11)| varchar(1| Fieldisbn| Field| Туреeditor_idssnpub_id | int(11)| varchar(40)varchar(40)varchar(20)varchar(2)lastnamenamefirstnameaddress| phoneeditor_positionsalary| citystate

The requirement is to fetch the author_id from author table for the records where there is no…

Answered: 4. Write two queries to list the ID of…

4. Write two queries to list the ID of all the authors that do not have any books. a) Using a join: b) Using a 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: Build two SQL queries that combine film and category information. List the film’s title, film’s…

A: Creating Film table: CREATE table FILM ( filmID NUMBER(3) NOT NULL, filmTitle VARCHAR2(50)…

Q: Display the names of the books sold by the bookstore which were published by a publisher from New…

A: As per our guidelines, we are supposed to answer only 1st three parts. Kindly repost the remaining…

Q: Write the T-SQL scripts for the following tasks: Write a query to select the employees whose…

A: Please refer to the following step for the complete solution of the problem above.

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: Some rows of a User table are shown below: User ucode name phone scode 7 Alex 847 - 3902…

A: Names and phone numbers are among the details about different users that are included in the User…

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: Use select statements to create queries that answer the following questions. Only provide the…

A: Queries: 1) SELECT ItemDescription, Price FROM Flower WHERE ItemDescription LIKE '%Bouquet%' AND…

Q: 11. Many queries search by the number of days to ship (number of days between the order and…

A: According to the information given:- We given to define the queries search by the number of days to…

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: I need help on writing statements for these, I'm having trouble with the subqueries.

A: Here's the SELECT statement using a subquery with the IN keyword:SELECT DISTINCT LastName, FirstName…

Q: D d. COMMIT nis clause in SQL join is based on all bles that have the same name.

A: ANSWER IS D NATURAL JOIN

Q: Write a query to display all vendors sorted by vendor name. Write a query to display the product…

A: SQL queries are a set of instructions given to the database to retrieve specific data. SQL was…

Q: how is duplicate column prefixed when duplicate column appear in Join Query? a) Table name followed…

A: The question is asking about the way to prefix a duplicate column when it appears in a Join Query in…

Q: how to write a query using a subquery to list InvoiceNumber, InvoiceDate for those products with a…

A: A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within…

Q: List the owner number and last name for all owners located in the city of Seattle. Your query should…

A: Since no name is given so i am assuming the name of the table is OWNER

Concept explainers

Multiple Table

In a database, a table is used to store data in the form of rows and columns. Rows are aligned horizontally and identified by one or more values in a specific column. Columns are vertically aligned and identified by a unique column name.

Question

4. Write two queries to list the ID of all the authors that do not have
any
books.
a) Using a join:
b) Using a subquery:

mysql> DESC bookauthor;
mysql> DESC author;
mysql> DESC book;
| Field
| Туре
| Field
| Туре
| Field
Тур
| int(11
| varchai
author_order | int(11
royaltyshare | decima:
author_id
isbn
| author_id | int(1:
| ssn
lastname
| firstname | varch:
| phone
| address
| city
| state
| zip
| varch:
| varch:
isbn
var
I name
| type
pub_id
I price
| advance
I ytd_sales
I pubdate
var
var
| varch:
| varch:
| varch:
| varch:
| varch:
| int
dec
| dec
int
mysql> DESC bookeditor;
| dat
| Field
| Туре
mysql> DESC editor;
mysql> DESC publisher;
editor_id | int(11)
| varchar(1
| Field
isbn
| Field
| Туре
editor_id
ssn
pub_id | int(11)
| varchar(40)
varchar(40)
varchar(20)
varchar(2)
lastname
name
firstname
address
| phone
editor_position
salary
| city
state

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

Requirement:

VIEW

a) Result using join query:

VIEW

b) Result using subquery

VIEW

Sample Output:

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 4 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

which constraint ensure the values in a column are all different? a) Foreign Key b) Restrict c) Unique d) Cascade
The given SQL creates a Movie table and inserts some movies. The SELECT statement selects all movies. Press the Run button to produce a result table. Verify the result table displays five movies. Modify the SELECT statement to only select movies released after October 31, 2015: SELECT*FROM Movie WHERE ReleaseDate >'2015-10-31'; Then run the SQL again and verify the new query returns only three movies, all with release dates after October 31, 2015. CREATE TABLE Movie ( ID INT, Title VARCHAR(100), Rating VARCHAR(5), ReleaseDate DATE); INSERT INTO Movie VALUES (1, 'Rogue One: A Star Wars Story', 'PG-13', '2016-12-10'), (2, 'Hidden Figures', 'PG', '2017-01-06'), (3, 'Toy Story', 'G', '1995-11-22'), (4, 'Avengers: Endgame', 'PG-13', '2019-04-26'), (5, 'The Godfather', 'R', '1972-03-14'); -- Modify the SELECT statement:SELECT *FROM Movie;Where ReleaseDate > '2015-10-31';
Do a SQL query using their data format to see who hasn't attended any classes there. Please avoid using set operations and subqueries (use an outer join).
From the following query, identify the value of City: INSERT INTO Customers ( CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (‘Cardinal,”Tom B. Erichsen”,”Skagen 21”, “Stavanger,” “4006Norway’);
What is the main difference between the set operators and the join?
Write queries in SQL to answer each of the following questions: 1. Find all students in Comp. Sci. dept 2. Find all students with total credits > 100 3. Find all students who took course in Spring 2010 (Remove duplicates please) 4. Find all courses taken by the student whose ID is 76543 and also find his name create table student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID) ); create table takes (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year) ); insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102'); insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32'); insert into student values ('19991', 'Brandt', 'History', '80'); insert into student values ('23121', 'Chavez', 'Finance', '110'); insert into student values ('44553', 'Peltier', 'Physics', '56'); insert into student values…
Write a SQL query to find the customer# of those customers that purchased at least once in July 2020 but NOT in August 2020. In addition to the customer#, show the ISBN and title of the book(s) they purchased in July 2020. You may use only one query. No creating any temporary tables or subqueries. You may use only joins. SUBMIT a second solution in addition to your Level 1.
What is the difference between a subquery and a JOIN?
Write a query that returns the Name of ALL Customers (based on CUS_FNAME and CUS_LNAME in CUSTOMER table, call this CUSTOMER_NAME), Phone number of ALL Customers (based on CUS_AREACODE and CUS_PHONE in CUSTOMER table, call this CUSTOMER_PHONE), for those haven’t flied before (based on the record in CHARTER table).
The given SQL creates a Song table and inserts some songs. The first SELECT statement selects songs released after 1992. The second SELECT statement selects the release year for song with ID 800. Create a third query that combines the two existing queries. The first SELECT should be the outer query, and the second SELECT should be the subquery. The ORDER BY clause should appear after the subquery. Run your solution and verify the new query returns a result table with five rows, all with release years after 1992. CREATE TABLE Song (ID INT,Title VARCHAR(60),Artist VARCHAR(60),ReleaseYear INT,Genre VARCHAR(20),PRIMARY KEY (ID)); INSERT INTO Song VALUES(100, 'Hey Jude', 'Beatles', 1968, 'pop rock'),(200, 'You Belong With Me', 'Taylor Swift', 2008, 'country pop'),(300, 'You\'re Still the One', 'Shania Twain', 1998, 'country pop'),(400, 'Need You Now', 'Lady Antebellum', 2011, 'country pop'),(500, 'You\'ve Lost That Lovin\' Feeling', 'The Righteous Brothers', 1964, 'R&B'),(600, 'That\'s…
Hello, can you please assit me with this. I am struggling to get the code to run correctly without errors: 1. Write a query using a join that displays Department names (not Department_IDs) Employees’ IDs, last & first names Sort your results by department name and employee last and first name. NOTE: INNER JOIN is OK here. NOTE: I suggest you use table aliases.*/ /*2. Write a query using a join that displays Department names (no DEPARTMENT_IDs). Job titles (not Job_IDs) Employees’ IDs, last & first names, Sort your results by department name, job title, and employee last and first name. NOTE: INNER JOIN is OK here. NOTE: I suggest you use table aliases.*/ /*3. Write a query using a self-join to display Employees’ IDs and names along with Their manager’s ID and name. Use column aliases so that it is clear which columns are for the Manager and which columns are for the Employee.…
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