Question 2: SQL Please see the following tables. Solve the questions with nested queries and aggregation operators. Tables used in this note: Sailors(sid: integer, sname: string, rating: integer, age: real); Boats(bid: integer, bname: string, color: string); Reserves(sid: integer, bid: integer, day: date). 1. Find the average age of sailors for each rating level that has at least two sailors. 3. Find the names and age of sailors who have reserved only blue boats and present them in an ascending order of age.

Question 2: SQL Please see the following tables. Solve the questions with nested queries and aggregation operators. Tables used in this note: Sailors(sid: integer, sname: string, rating: integer, age: real); Boats(bid: integer, bname: string, color: string); Reserves(sid: integer, bid: integer, day: date). 1. Find the average age of sailors for each rating level that has at least two sailors. 3. Find the names and age of sailors who have reserved only blue boats and present them in an ascending order of age.

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

Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. (You may assume that the table will be restored to its condition preceding this question.)
Write PL/SQL coomands for the following question5. Table student( name,age,inter_mark,entrance_rank,address,contact_no,ID,branch)Admission( college_name,mode( pay/free/star/NRI), date,ID)a) Write a query to display the students who are all admitted on starmode branchwiseb) Display the college under which maximum NRI student admittedc) Display the college which got minimum free students and atmost one NRI studentd) Display the student who got second maximum rank admission detailse) Display the students category wise admission detailsf) Write a plsql routine to check a perfect square number is the given number.
student(sid, sname, sex, age, year, gpa) dept(dname, numphds) prof(pname, dname) course(cno, cname, dname) major(dname, sid) section(dname, cno, sectno, pname) enroll(sid, grade, dname, cno, sectno) Write the following SQL queries for the schema above: Print the course names, course numbers and section numbers of all classes with less than six students enrolled in them. Print the name(s) and sid(s) of the student(s) enrolled in the most classes. Print the names of departments that have one or more majors who are under 18 years old. Print the names and majors of students who are taking one of the College Geometry courses. For those departments that have no majors taking a College Geometry course, print the department name and the number of PhD students in the department. Print the names of students who are taking both a Computer Sciences course and a Mathematics course. For each department that has one or more majors with a GPA under 1.0, print the name of the…
Write following queries in SQLplus Give the maximum, average and minimum working hours of each employee Give name of employees who work for more than 1 property Give total hours worked by employee Cory (last name) Danny, manager, wants to see the impact of 12% salary increase. Increase all workers salary by 12%
Given the following schema, answer the following questions USERS Attribute Data Type USER_ID NUMBER(3) USER_NAME VARCHAR2(20) USER_TYPE VARCHAR2(10) USER_LIMIT NUMBER(1) BOOKS Attribute Data Type BOOK_ID NUMBER(5) BOOK_TITLE VARCHAR2(25) BOOK_YEAR VARCHAR2(4) Write the SQL code that will create the tables structures for the tables named Books, and Users. Identify and include any PK and FK for both tables.
True or False below: Given two relations named A and B, if we q-JOIN A and B, the maximum number of attributes in the result relation could be (the number of attributes of A) times(*) the number of attributes of B). * in an SQL SELECT statement means “all the tuples”. In relational algebra, Cartesian Product (X) can be implemented using the q-JOIN operator. Cartesian Product ISA q-JOIN where q-condition always evaluates to true. SQL is based on Predicate Calculus. A query in SQL always specifies what should be included in the query result.
Question 5 You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, country, and postal_code. You want to check for postal codes that are greater than 7 characters long. You write the SQL query below. Add a LENGTH function that will return any postal codes that are greater than 7 characters long. 1 2 3 4 5 SELECT * FROM customer WHERE RunReset What is the last name of the customer that appears in row 10 of your query result? 1 point
Write SQL statements to answer the following questions using Assignment 3’s schema (tables below). You can add more data to the tables if you want, just follow the PK and FK rules. Accounts are not Loans and Loans are not Accounts. NOTE I already have Answers for part 1 BELOW PART 1 IS FOR ONLY REFRENCE TO ANSWER QUESTIONS 6-11 6- Find Customer ID, Customer name and the number of loans for each Customer. 7- Find Loan number and Customer Id of the loan with the lowest amount. 8- Create a view called Gary_Branch_V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Gary. 9- For each Customer in Hopkins, find the balance in their account(s). 10- Find how many different accounts each customer has at each Branch. The output should be a list of Customer ID and for each Customer ID, the number of accounts for this customer by Branch ID. 11- Find the branch with the highest or largest Average loan amount. List the Branch ID, Branch…
Consider the following schema: Suppliers (sid: integer, sname: string, address: string) Parts (pid: integer, pname: string, color: string) Catalog (sid: integer, pid: integer, cost: real) sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form the key for Catalog. The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL queries for the following: (a) Find the names of suppliers who supply some red part.(b) Find the sids of suppliers who supply some red or green part.(c) Find the sids of suppliers who supply some red part or are at 221 Packer Ave.(d) Find the sids of suppliers who supply some red part and some green part.(e) Find the sids of suppliers who supply every part.(f) Find the sids of suppliers who supply every red part.(g) Find the sids of suppliers who supply every red or green part.(h) Find the sids of suppliers who supply every red part or supply every green…

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

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

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

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

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

A Guide to SQL

Computer Science