Question 13Given the following relation:Book (BookID, Author, ISBN, Title)Write a PL/SQL block of code that performs the following tasks:i. Read a Book id provided by user at run time and store it in a variable.ii. Fetch the Author and title of the book having the entered id and store them inside two variablesiii. Display the fetched book author and title.

Here we have to write sql commands for the following three tasks. So, these commands are as…

Answered: Book (BookID, Author, ISBN, Title)

Engineering

Computer Science

Book (BookID, Author, ISBN, Title)

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter12: Subqueries And Merge Statements

Section: Chapter Questions

Problem 19MC

See similar textbooks

Similar questions

Given the following relation: Movie (MovieID, Title, Genre, Year) Write a PL/SQL block of code that performs the following tasks: Read a movie id provided by user at run time and store it in a variable. Fetch the Genre and title of the movie having the entered id and store them inside two variables Display the fetched movie Genre and title.
Given the following relation: Book (BookID, Author, ISBN, Title) Write a PL/SQL block of code that performs the following tasks: Read a Book id provided by user at run time and store it in a variable. Fetch the Author and title of the book having the entered id and store them inside two variables Display the fetched book author and title.
. a) Write an SQL query to find the manufacturer and model for all aircraft models with capacity at least 300. b) write a relational algebra expression which is equivalent to the above query. c) Write an SQL query to find the names of the airlines that have at least 10 Boeing 737 in their fleet. d) What is the result of the natural join of the following two tables: Airlines and Fleet
Exercise 3: Procedural SQLWrite the following PSM functions or proceduresa) Take a price as argument and return the model number of the PC whose price isclosest.b) Take a maker and model as arguments, and return the price of whatever type ofproduct that model is.
ASSUME 6.2 IS DONE Write a SQL statement to get the list of all the tasks that started after2022-05-10. Include all the columns from the task table. Write a SQL statement that returns a list of the team members with alltheir tasks. Include the team member’s name and the description ofthe task in the results. Write a SQL statement to get a list of the tasks in the database,arranged by duration from largest to smallest. Include all the columnsfrom the Task table.
(a) What SQL statement would you use to create the table ”staff”, including specifying the primary key? (b) What SQL statement would you use to find out how many lecturers there are in the School of Computing? (c) What SQL statement would you use to list the lecturers of the modules COMP1121 and COMP1211? (d) Write an SQL statement, using the inner join construct, that will, for the lecturer John Smith, list in alphabetical order the students taking any module which he teaches. (e) What information does the output from the following SQL query provide? SELECT Lecturer, COUNT(DISTINCT Student) FROM class INNER JOIN staff ON class.Module = staff.Module WHERE Grade < 40 GROUP BY Lecturer; (f) Write an SQL query which would obtain the following information: for each lecturer, what is the average grade obtained by students across all of the modules which they have taught?
Let's wa(k through a review of basic SQL syntax, using as an example the database that was mentionedearlier. This database has the following simple structure(* indicates a primary key):Courses: CourseID*, CourseName, TeacherIDTeachers: TeacherID*, TeacherNameStudents; StudentID*, StudentNameStudentCourses: CourseID*, StudentID*Using the above table, implement the following queries.Query 1: Student Enrollment Query 2: Teacher Class Size
Provide all the SQL statements that are required for the following transactions to be executed. Transaction 01: A residential customer (Bob Builder) of 53 Drip Drive, Dripwater (post code: 1267) has an account with the water company at the same address. The meter was installed on 30th October 2019 and has the serial number DDWC4763. This customer does not have any outstanding balance for the service at this address. Transaction 02: On 23rd July, 2020, the meter at 53 Drip Drive, Dripwater was read by Karen Karpenter (a meter reader). The reading the last time this meter was read was 123580 kL, at this reading it was 123683 kL. The bill was created and sent on the same day. Transaction 03: On 2nd August 2020, Bob Builder settled the account in full for consumption at 53 Drip Drive, Dripwater using PayPal.
Given the following SQL statement, which statement is most accurate?SELECT customer# FROM customersJOIN orders USING(customer#)WHERE shipdate-orderdate IN(SELECT MAX(shipdate-orderdate) FROM ordersWHERE shipdate IS NULL);a. The SELECT statement fails and returns an Oracle error message.b. The outer query displays no rows in its results because the subquery passes a NULLvalue to the outer query.c. The customer number is displayed for customers whose orders haven’t yet shipped.d. The customer numbers of all customers who haven’t placed an order are displayed
(a) Write the sql syntax to create a table name customer with the following restrictions;Customer(cid, cname, address, age, occupation, salary, payment_mode) Where cid is the primary key. cname and occupation do not accept null values.Payment_mode accepts only cash or credit entries.The default address is “Flacq”.customer is at least 18 years old.Salary is above Rs 25000. (b) Consider the schema of Customer table in part(a) and write SQL statements for the following queries. i. Using SQL, write the command to add a new column in the customer table with the following data definition; (Column “gender” of char data type and field size of 1). ii. Write the SQL query to display the cid and gender of all employees. iii. Write the SQL code to list all supervisors whose salary is greater than Rs 30000. iv. Write the SQL query to set the salary of all administrators to Rs 50 000. v. write the SQL query to display details of customers whose names start with character 'e'. vi. Write the SQL code…
(a) Write the sql syntax to create a table name customer with the following restrictions;Customer(cid, cname, address, age, occupation, salary, payment_mode) Where cid is the primary key. cname and occupation do not accept null values.Payment_mode accepts only cash or credit entries.The default address is “Flacq”.customer is at least 18 years old.Salary is above Rs 25000. (b) Consider the schema of Customer table in part(a) and write SQL statements for the following queries. i. Using SQL, write the command to add a new column in the customer table with the following data definition; (Column “gender” of char data type and field size of 1). ii. Write the SQL query to display the cid and gender of all employees.iii. Write the SQL code to list all supervisors whose salary is greater than Rs 30000.
Write an SQL statement that will insert an order in the orders table with the following data: cutomerID=3, orderDate = current date, shipAmount=20, taxAmount=23.56, shipDate=a week after current date, shipAddressID=3, cardType=2, cardNumber=4123123412341234, cardExpires=05/2024, billingAddressID=3 Write an SQL statement to delete all orders that are created between 2017-06-01 and 2017-06-07.

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

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

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

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

SEE MORE TEXTBOOKS