Write an SQL statement to display the last name, first name, and email of any owners of cats. Use a subquery.
The following table schema for the BREED table shows a new table to be added to the pet
BREED (BreedName, MinWeight, MaxWeight, AverageLifeExpectancy)
Assume that PetBreed in PET_3 is a foreign key that matches the primary key BreedName in BREED and that we have the referential Integrity constraint:
PetBreed in PET_3 exist in BreedName in BREED
If needed, you may also assume that a similar referential integrity constraint exists between PET and BREED and between PET_2 and BREED. The BREED table data are shown in Figure 3-30.
Want to see the full answer?
Check out a sample textbook solutionChapter 3 Solutions
EBK DATABASE CONCEPTS
Additional Engineering Textbook Solutions
C++ How to Program (10th Edition)
Modern Database Management (12th Edition)
Absolute Java (6th Edition)
C How to Program (8th Edition)
Computer Science: An Overview (13th Edition) (What's New in Computer Science)
Digital Fundamentals (11th Edition)
- Write appropriate SQL DDL statements for declaring the database "Employee", assign admin privilege to user "AMAL" Write appropriate SQL DDL statements for declaring the table PERSONS. Specify all constraints: Field Null key PersonID No PRIMARY No LastName varchar(30) FirstName varchar(30) No Address varchar(50) YES City varchar(30) YES Type intarrow_forwardWorksOn Database: Employee (EmployeeNumber, EmployeeName, BirthDate, Title, Salary, DepartmentNumber) Project (ProjectNumber, ProjectName, Budget, DepartmentNumber) Department (DepartmentNumber, DepartmentName, ManagerNumber) WorksOn (EmployeeNumber, ProjectNumber, Responsibility, Hours) Questions: 1) Write an SQL query that returns the project number and name for projects with a budget no greater than $1,050,000. 2) Write an SQL query that returns all works on records where hours worked is less than 5 and the responsibility is 'Manager'. 3) Write an SQL query that returns the employees (number and name only) who have a title of 'EE' or 'SA' and make more than $50,000. 4) Write an SQL query that returns the employees (name and salary only) in department 'D1' ordered by decreasing salary. 5) Write an SQL query that returns the departments (all fields) ordered by descending department name.arrow_forwardQuestion 11 Write SQL statements to answer the following question based on the ERD below. Note: Display only the attribute(s) required in the question. You need to provide complete answers, writing just keywords like SELECT, FROM, WHERE will receive 0 point. Question: List course name, section ID, and the average student grade (as GPA) in that section, for each section that was offered in 'Spring 2010' and order the result in descending order of section ID. STUDENT PK G_number FirstName LastName Address REGISTRATION PK FK G_number PK FK Sectionid DateEnrolled Grade 140 (DC) ALT+N+F10 (Mac) COURSE PK CourseID PK CourseName Course Desc CourseDept F SECTION SectionID FK Course D Semester Year Է t + 78v ABC > 5 600arrow_forward
- Schema: Employees (EmpID int, Name varchar(50), Age int, Salary float, DepartmentID int) Departments (DepartmentID int, DepartmentName varchar(50), ManagerID int) Write a query to find the names of all employees who work in the "Sales" department and earn a salary greater than $50,000 per year, sorted by their age in ascending order. In MS SQL pleasearrow_forwardBelow is part of a Student database. The primary keys are highlighted in bold. Student (studNo, studName, address, mobileNo) Registration (studNo, courseNo, regDate, semester, session) Course (courseNo, courseName, creditHour, level) Project (projNo, projName, courseNo) Assignment (projectNo, studNo, startDate, dueDate, hoursSpent) QUESTION 1. Write a PL/SQL FUNCTION that calculates the cost each student has to pay for all courses taken. In the photo is example the cost detail for each course. 2. Write a PL/SQL Procedure to call the function you created in question 1 . Allow the user to enter the student ID (studno). SECOND PHOTO IS THE EXAMPLE OF EXPECTED OUTPUTarrow_forwardInstructions Write SQL queries to solve the following problems. We will be using the “university” database. All queries should involve only the instructor table. After you are done, save your work as .sql file and turn in the .sql file into Moodle for grading. Write a SQL statement to display all columns using the SQL asterisk (*) wild-card character. Write a SQL statement to display ID and name of the instructors (ID appears first). Write a SQL statement to display department names. Write a SQL statement to display unique department names. (expect 7 rows returned) Write a SQL statement to display all info about instructors having a salary greater than 75000. (expect 6 rows returned) Write a SQL statement to display department name, instructor name, and salary for instructors that have a salary less than 70000. Sort the results in ascending order by department name. (expect 4 rows returned) Write a SQL statement to display name, department name, and salary for instructors that have a…arrow_forward
- In SQL This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text):• Customers: stores customer’s data• Products: stores a list of scale model cars• ProductLines: stores a list of product line categories• Orders: stores sales orders placed by customers• OrderDetails: stores sales order line items for each sales order• Payments: stores payments made by customers based on their accounts• Employees: stores all employee information as well as the organization structuresuch as who reports to whom• Offices: stores sales office data Write SQL code for the following:We want to add a new sale order for the customer (customerNumber = 145) in thedatabase. The steps of adding a sale order are described as follows:(1) Get latest sale order number from “orders” table, and use the next sale ordernumber as the new sale order number(2) Insert a new sale order into “orders” table for the customer (customerNumber =145). For this order, the…arrow_forwardPlease help with the following: Write following SQL Queries:A simple database composed of the following tables: PATIENT, DOCTOR, DRUG, and PRESCRIPTION, List all the rows in which the prescription dates occur on or after November 14. How many doctors provide prescriptions with 30 tablets in total? List doctor’s details where speciality is either Dermatology or Neurology. (Use INoperator). List the results by the first name in ascending order and the last name indescending order Find all patients rows whose first names include ‘George’ (Assume case sensitivity). List number of patients that do not have a phone number. How many drugs with price < 40$? How many prescriptions are generated for each patient, limit the listing to patients who have more than 2 prescriptions? List the drug codes and prices (rounded to one decimal place). How many doctors prescribe the drug code KO15? List all drugs with their expiration date (one year from the date of manufacturer)…arrow_forwardGiven 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.arrow_forward
- Write the SQL code that will produce the same information that was shown in Problem 2, but sorted by the employee’s last name. The results of running that query are shown in Figure P7.3.arrow_forwardDatabase course: Write SQL queries to do the following A. List names of IT students. B. List titles of courses of type E. C. Display the results of student with ID 201006 D. List student names and the courses they took. E. List total credits that student with ID 201002.arrow_forwardSubject: DatabaseWrite the SQL statements for following modification tasks: a) Add constraint to the table BORROWER named borrower_cardno_pk to create the attribute name as the primary key. b) Delete the column city from the table BORROWER c) Modify the column of "phone" in table BORROWER, change the data type into type of VARCHAR2 with length of 20.arrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education