USING SQLUsing the Henry Books database, for every author listed in the author table, list each book written and sold at Henry Books, if any. Display the author number as ‘Author Number’, concatenate the author name as first name last name as ‘Author Name’, and book code as ‘Book Code’. Order by author number in ascending order. Be sure all rows are displayed. Henry Books Table Relationshipsinventorybook_code CHAR(4) (FK)branch_num INT(11) (FK)on_hand INT(11)bookbook_code VARCHAR(4)wroteauthorbranchtitle VARCHAR(40)-book_code CHAR(4) (FK)author_num INT(11)branch_num INT(11)publisher_code CHAR(3) (FK)author_num INT(11) (FK)author_last VARCHAR(12)branch_name VARCHAR(50)type VARCHAR(3sequence INT(11)author_first VARCHAR(10)branch_location VARCHAR(50)price DECIMAL(4,2)num_employees INT(2)paperback CHAR(1)publisher1publisher_code VARCHAR(3)publisher_name VARCHAR(25)city VARCHAR(20)

USING SQL Using the Henry Books database, for every author listed in the author table, list each book written and sold at Henry Books, if any. Display the author number as ‘Author Number’, concatenate the author name as first name last name as ‘Author Name’, and book code as ‘Book Code’. Order by author number in ascending order. Be sure all rows are displayed.

USING SQL Using the Henry Books database, for every author listed in the author table, list each book written and sold at Henry Books, if any. Display the author number as ‘Author Number’, concatenate the author name as first name last name as ‘Author Name’, and book code as ‘Book Code’. Order by author number in ascending order. Be sure all rows are displayed.

CMPTR

3rd Edition

ISBN:9781337681872

Author:PINARD

Publisher:PINARD

Chapter18: Maintaining And Querying A Database

Section: Chapter Questions

Problem 4QY

See similar textbooks

Similar questions

A unique identifier for a data row that consists of more than one field is commonly called a: primary plus key composite primary key foreign key none of the above
Use the following DBDL that contains 4 entities in 3NF to create a Crow's Foot ERD (use the name of entities and attributes mentioned in DBDL) . titleAuthor [ titleCode , authorID, sequence ] FK titleCode -> Title FK authorID -> Author Author [ authorID, authorFName, authorLName ] Title [ pubCode, titleCode, titleName, price ] FK pubCode -> Publisher Publisher [ pubCode, pubCompany]
Create table EMP and DEPT with the below mentioned structureStructure for EMP tableEmpIDNUMBER(4)DeptIDVARCHAR2(10)EmpNameCHAR(10)JobCHAR(10)HireDate DATESalary NUMBER(7, 2)Commission NUMBER (7, 2)Structure for DEPT tableDeptId VARCHAR2(10)Deptname VARCHAR2(20)No_of_Faculties NUMBER(2)In table EMP : EmpID should be the Primary Key and DeptID should be the foreign key.In table DEPT : DeptId should be the primary key. a) Find out the number of employees having “manager” as job.b) Display only the jobs with maximum salary greater than or equal to 3000c) Find all those employees whose job does not start with ‘M’. Find the names of the employees who are Managers and their date of joining is after “02-Jan-2006”.
Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions: Transaction SQL Statement Retrieve the flight number, departure and arrival airports of all British Airways. Retrieve the name of every passenger together with their flight number and the associated company. Retrieve all flights departed from all airports in London. Retrieve the ticket numbers…
Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions:1. Retrieve all rows in Airport table for all the airports in London city.2. Retrieve all British and German passengers.3. Retrieve all names of all the passengers.4. Retrieve the flight number, departure and arrival airports of all British Airways.5. Retrieve the name of every passenger together with their…
Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions: Transaction SQL Statement Retrieve all rows in Airport table for all the airports in London city. Retrieve all British and German passengers. Retrieve all names of all the passengers. Retrieve the flight number, departure and arrival airports of all British Airways. Retrieve the name of…
Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions: please if you could give all the sql statements needed i will appreciate that, and i promise to put thump up Transaction SQL Statement Retrieve all rows in Airport table for all the airports in London city. Retrieve all British and German passengers. Retrieve all names of all the passengers.…
1- Display contact First name and contact Last name for customers after sorting the customersby their contact Last name in ascending order.2- selects the order line items (orderNumber, orderlinenumber) from the “orderdetails” table.Also, display the subtotal (quantityOrdered * priceEach) for each line item and sorts the resultset based on the subtotal descending .3- Display unique last names from employees table ordered by last name.4- Display customerNumber, customerName and creditLimit for getting the top five customerswho have the highest creditLimit.5- Find customer Name, customer country, sales representative employee number fromcustomers who do not have a sales representative.6- For each order status, display status and count of orders have this status.7- For Each order number in order details table, Display order number, sum of quantity ordered(itemsCount) and sum of (priceEach * quantityOrdered) as total. With condition “total”greater than 1000.8- Display product code, product…
Assume you have a vehicle table with a surrogate key vehicle_id column and VIN (Vehicle Identification Number) column and some other columns. You want to designate a unique index on the vehicle_id and VIN columns because together they filter on the joining column with the highest cardinality (or degree of uniqueness). Write the syntax to add a unique index on the vehicle_id and VIN columns triggered by joins on the surrogate key column?
Write SQL code for the following design schema of three tables. The tables should have the following properties: sales table has a primary key on SalesNumber and it is set to AUTO-INCREMENT.products table has a primary key on number and it is set to AUTO-INCREMENT, they is an unique index on prodidsalesdetails table has a primary key on number and it is set to AUTO-INCREMENT, they is an index on prodid. They is a one-to-many relationship between the salesdetails.SalesNumber andsales.SalesNumber fields. There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields. There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table. Primary and foreign keys should be implemented in the appropriate tables. Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all…
Create LessonSchedule table with FK constraints in mysql database Two tables are created: Horse with columns: ID - integer, primary key RegisteredName - variable-length string Student with columns: ID - integer, primary key FirstName - variable-length string LastName - variable-length string Create the LessonSchedule table with columns: HorseID - integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID) StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID) LessonDateTime - date/time, not NULL, partial primary key If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically. If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.
Db&__Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions:1. Retrieve all rows in Airport table for all the airports in London city.2. Retrieve all British and German passengers.3. Retrieve all names of all the passengers...